קורס אקסל אונליין למתקדמים ומומחים br> Excel Pros> שיעור מס' 2 | נוסחת מערך פשוטה
מאמר זה כולל:
סרטון שיעור מס' 2 - קורס אקסל מתקדמים ומומחים
נוסחה פשוטה המקבילה ל-Countif/Sumif ע"י שימוש בפונקציית Sum
מטרת קורס אקסל למתקדמים ומומחים Excel Pros:
*קורס אקסל זה מיועד למתשתמשי אקסל בעלי ידע מתקדם באקסל.
מומלץ לאנשי כספים, אנליסטים ומנהלי פרוייקטים.
מטרת הקורס היא להיכנס לעומק לנושא פונקציות ונוסחאות מערך, לראות כיצד ניתן לפתור בעיות מסויימת באקסל שלא ניתן לפתור ללא הבנה בנושא נוסחאות מערך וללא שימוש בקוד VBA.
בנוסף נכיר כלים לניתוח נתונים ותרחישים באקסל, נכיר פונקציות הזמינות ב Microsoft 365, וניגע ברמת היכירות בתוספים Power Query, Power pivot.
- נראה דוגמאות מגוונות ומעניינות לפתרון בעיות מורכבות באקסל.
- נבין את ההבדל בין גרסאות 365,2019 לגרסאות אקסל מוקדמות יותר.
- נכיר כלים לניתוח תחזיות, תרחישים וביצוע אופטימיזציה.
- היכירות ראשונים עם הכלים Power Query, Power Pivot
חשוב לציין שקורס זה אינו מכסה לעומק נושאים כמו Power Query, Power Pivot - Power BI באקסל.
אלו אכן נושאים חשובים לכל משתמש אקסל השואף להיות מקצוען אמיתי בתוכנה, אך דורשים קורס נפרד העוסק ב Power BI.
עבור נושאים אלו יוקדש קורס אקסל נפרד.
ידע מקדים מומלץ עבור קורס אקסל למתקדמים ומומחים - Excel Pros :
הצופים בקורס זה צריכים להכיר ולדעת לעבוד בצורה טובה עם פונקציות אקסל כמו:
Sumif, Sumifs, Vlookup, Index, Match, Concatenate, Find, Search
קורס אקסל אונליין למתקדמים ומומחים Excel Pros | שיעור מס' 2 - נוסחת מערך פשוטה | סרטון הקורס
שיעור מס' 2: דוגמה לנוסחת מערך פשוטה - בניית נוסחאות הזהות לפונקציות Sumif, Countif
בשיעור מס' 1 הבנו מהו מערך, וראינו דוגמאות למערך דינמי באקסל 365, 2019 ומערך שאינו דינמי.
בשיעור זה נראה שאפשר להפעיל תנאי לוגי על מערך ולקבל את התוצאות True, False עבור כל אחד מערכי המערך בהתאמה.
נראה גם שאת התוצאות True, False ניתן להמיר למספרים 1,0 (בהתאמה) ומשם כבר קל יותר להמשיך עם חישובים שונים לפי הצורך ומטרת החישוב.
דוגמה מס' 1:
נניח ואנחנו כותבים באקסל את הנוסחה הבאה 20 > 30 = התוצאה תהיה False וזה מפני ש-30 לא קטן מ-20 (או להפך)
ניתן באקסל לבצע את אותה הבדיקה עבור מערך.
לפנינו המערך הבא: {10;25;15;30} ואנו מעוניינים לבדוק עבור כל אחד מהערכים במערך האם הוא קטן מ-20.
החשוב שנבצע יהיה:
Formula: {30;15;25;10}<20
Result: {False,True,False,True}
אנו רואים שעבור הערכת הערך הראשון במערך - 30, אנו מקבלים False וזה מפני שהוא אינו קטן מ-20, עבור הערך השני - 15 אני מקבלים True מפני שהוא אכן קטן מ-20.
דוגמה מס' 2:
אנו ממשיכים עם המערך {10;25;15;30}, רק שהפעם אנו מעוניינים לספור כמה ערכים קטנים מ-20 קיימים במערך.
ראינו בדוגמה הראשונה איך ניתן לקבל מערך של תוצאות הבדיקה - {False,True,False,True}.
בשלב זה כל שנשאר לעשות זה לספור כמה True יש במערך.
יהיה קל יותר לעשות זאת עם המספרים 1,0 במקום עם הטקסטים True, False.
כדי לעבור המערכים True, False למספרים 1,0 יש לבצע פעולת חישוב כלשהי על המערך.
נכפיל את המערך ב-1 כדי לא לשנות את הערכים אלא רק להמיר אותם ל-0,1.
Fromula: ({10;25;15;30}<20)*1
Result: {1,0,1,0}
כדי לדעת כמה ערכים קטנים מ-20 קיימים במערך שלנו, כל שנשאר לעשות הוא לסכום אותו ע"י שימוש בפונקציית Sum פשוטה:
Formula: Sum(({10;25;15;30}<20)*1) ⇒ Sum({1,0,1,0}) = 2
יש לישם לב לכך שבגרסאות הקודמות ל-2019 ושאינן 365 יש ללחוץ **
CTRL+SHIFT+ENTER
כדי לקבל את התוצאה
לאחר חישוב הסכום קיבלנו שקיימים שני ערכים הקטנים מ-2.
דוגמה מס' 3:
לפנינו טבלת אקסל המכילה נתוני הזמנות הכוללים: מספר הזמנה, תאריך, כמות, ומחיר (ראו תמונה מס' 1)
טווח הטבלה הוא A11:D43
תמונה מס' 1
אנו מעוניינים לדעת כמה הזמנות יש בהן הכמות גדולה מ-600.
הנוסחה תהיה: SUM((C11:C43>600)*1), * ניתן לראות את שלבי החישוב בתמונת מס' 2, 3, 4.
יש לישם לב לכך שבגרסאות הקודמות ל-2019 ושאינן 365 יש ללחוץ **
CTRL+SHIFT+ENTER
כדי לקבל את התוצאה
תמונה מס' 2
ניתן לראות דוגמה הממחישה כיצד החישוב מתבצע בעמודות הגיליון ולא ישירות בשורת הנוסחאות.
שלב 1:
תמונה מס' 3
שלב 2:
תמונה מס' 4
שלב 3:
תמונה מס' 5:
דוגמה מס' 4:
אנו ממשיכים עם המערך מדוגמה מס' 1 {10;25;15;30}, רק שהפעם אנו מעוניינים לדעת מהו סכום הערכים הקטנים מ-20.
כבר ראינו בדוגמאות 1,2 איך לבדוק את התנאי ואיך להמיר את התוצאות ל-1,0.
כל שנשאר עכשיו הוא במערך עצמו במקום ב-1.
Formula: Sum(({10;25;15;30}<20)*{10;25;15;30}) ⇒ Sum({1,0,1,0}*{10;25;15;30}) ⇒ Sum({10,0,15,0}) = 25
יש לישם לב לכך שבגרסאות הקודמות ל-2019 ושאינן 365 יש ללחוץ **
CTRL+SHIFT+ENTER
כדי לקבל את התוצאה
הכפלת הערכים בין המערכים השונים נעשית בהתאמה לכן כל ערך הקטן מ-20 (המקיים את התנאי) מוכפל ב-1 ולכן שומר על ערכו. כל ערך הגדול מ-20 (לא מקיים את התנאי) מוכפל ב-0 ולכן ערכו מתאפס ולא נסכם בחישוב הסופי של הסכום.
קורס אקסל מתקדם שיעורים נוספים
קורסים נוספים באקסל של Excel-iT
קורס אקסל מתקדם פונקציות מקטגורית טקסט - קורס אונליין חינם כולל קבצי תרגול
סדנה אונליין בנושא Vlookup - סדנה חינם באורך מלא כולל קבצי תרגול