אקסל – מתי טבלת ציר לא עוזרת במציאת פתרון

אקסל – מתי טבלת ציר לא עוזרת במציאת פתרון

נניח שיש לנו טבלת נתונים בשם: Cities ובה שמות ערים. אנו רוצים לדעת כמה פעמים מופיעה כל עיר בטבלה.

תמונה מס.1 : טבלת הערים Cities

   

שיטה א:  טבלת ציר (Pivot Table)

הפתרון הפשוט ביותר הוא, כמובן, בעזרת טבלת ציר

   

תמונה מס.2 : מציאת מספר המופעים של כל עיר בטבלת ציר

   

 

טבלת הציר מאפשרת לנו:
1. לקבל רשימה יחודית של שמות כל הערים בטבלה (עמודה City Name)

2. לקבל את מספר המופעים של כל עיר (עמודה Occurrences),

למשל: New York = 3, Chicago = 6, Houston = 1 

שיטה ב: נוסחת מערך (Array Formula)

שיטה זו פחות טובה מהראשונה, כי היא מאפשרת להציג בכל פעם מספר מופעים עבור עיר אחת בלבד וגם דורשת יותר עבודה, בהכנת רשימת אימות נתונים (Data Validation)

ראשית, יצרנו בתא C1 רשימת אימות נתונים, ממנה נוכל לבחור בכל פעם עיר אחת מהרשימה היחודית של הערים בטבלה.

שנית, בנינו את נוסחת המערך הבאה בתא  C7    

                        תמונה מס.3 : נוסחת מערך לספירת מספר מופעי המחרוזת בתא C1 בטבלת Cities

הסבר:

הנוסחה מחפשת בטבלת Cities )בעזרת הפונקציה: FIND) את הערך שמוצג בתא C1 (שם העיר שנבחר מרשימת אימות הנתונים) וסופרת את מספר הפעמים שהוא מופיע בטבלה.

הפונקציה ISNUMBER מתעלמת מערכים לא-נומריים (במקרים שבהם הערך המבוקש לא נמצא בתא מתאי הטבלה). התווים: -- הופכים את הערך TRUE לערך נומרי (=1).

בסיום הקלדת הנוסחה, יש להקיש  CTRL+SHIFT+ENTER כדי להפוך את הנוסחה לנוסחת מערך.

 תמונה מס.4: מציאת מספר המופעים של כל עיר בעזרת נוסחה. חיפוש הערך בתא C1 בתוך הטבלה Cities

ברור לחלוטין, כי במקרה שלנו השיטה הראשונה, בעזרת טבלת ציר, עדיפה.

אבל, האם זה אומר שתמיד טבלת ציר עדיפה על נוסחה?

קחו, למשל, את הטבלה הבאה (Fruit):

                                                                    תמונה מס.5: טבלת Fruit

                     

אפשר לראות שבכל תא מופיע יותר מאשר פרי אחד.

אם נרצה לבנות טבלת ציר מטבלת  Fruit נקבל תוצאות שגויות, גם אם נפצל את הערכים בתאי הטבלה בעזרת טקסט לעמודות (Text to Columns)

אז איך בכל זאת נוכל לדעת כמה פעמים מופיע כל פרי בטבלה?

נשתמש בנוסחת המערך שהצגנו כשיטה השניה בפתרון הבעיה הקודמת: טבלת Cities

1. נגדיר את שמות  הפירות ברשימת אימות בתא C1

2. בתא C5 נקליד את נוסחת המערך הזהה לנוסחה בסעיף הקודם, מלבד שם הטבלה: Fruit במקום Cities 

                                              תמונה מס.6: פתרון בעית טבלת Fruit בעזרת נוסחת מערך

לפי הנוסחה שלנו, אפשר לראות שהפרי apple מופיע 3 פעמים בטבלה. 

בכל פעם נבחר פרי אחר מתוך רשימת אימות הנתונים (C1) ועבור כל פרי נקבל את מספר המופעים שלו בטבלה  

Fruit

מכאן ברור ששימוש בטבלת ציר לא היה מסייע לפתרון הבעיה 😓😓😓