הבלוג של מני: אקסל לעזרת ה...."פייסבוק"

אקסל לעזרת ה...."פייסבוק"

אני מנהל ב"פייסבוק" דף אוהדים. בדף האוהדים אני מעלה מדי מספר ימים פוסטים בנושאים הקשורים למה שאני מלמד: אנגלית, מתמטיקה, אקסל....

במקביל לנהול דף האוהדים ב"פייסבוק" עצמו אני גם מנהל מעקב משלי על דף האוהדים. יש לי טבלת נתונים ב"אקסל" ובה, עבור כל פוסט שאני מעלה, אני שומר את הפרטים הבאים:

מס. הפוסט, תאריך ההעלאה, תאור/נושא הפוסט, כמה אנשים צפו בו, כמה אנשים עשו לו "לייק" וגם רשימה שמית של כל מי שעשה "לייק" לפוסט.

תמונה מס. 1: כך נראית טבלת הנתונים הגולמית

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

למשל,

1. כמה פוסטים העליתי לאחר תאריך מסוים (למשל: 10/06/2014)

2. כמה פוסטים העליתי בתקופה מסוימת (למשל, בחודש מאי 2014:

01/05/2014-31/05/2014)

3. כמה "לייקים" עשה לי מבקר מסוים בדף (למשל: שרונה מלר) בכל התקופה

4. כמה "לייקים" עשה לי מבקר מסוים בדף (למשל: פרנץ פושקש) בתקופה מסוימת (למשל, בחודש מרץ: 01/03/2014-31/03/2014)

את כל הסטטיסטיקות הנ"ל (ועוד רבות אחרות), "פייסבוק" לא יכול לספק לי ולכן אני נעזר ב"אקסל".

בכל 4 הדוגמאות, נשתמש בנוסחאות מערך (Array Formulae) ובטבלה (Table).

כבר הסברתי בעבר מהי נוסחת מערך וכיצד משתמשים בה.

ראה למשל כאן:

א. נוסחת מערך לעזרת המורה

http://meniporat.blogspot.co.il/2013/03/blog-post_2483.html

ב. נוסחת מערך – ספירה ללא ספירה

http://meniporat.blogspot.co.il/2014/01/blog-post_4224.html

כעקרון, נוסחת מערך משמשת אותנו כאשר אנו רוצים לבצע פעולה על מספר תאים (תחום תאים) ולא על תא בודד.

ראשית, נהפוך את טבלת הנתונים לטבלת "אקסל" (נבחר תא כלשהו בטבלת הנתונים ונקיש: CTRL+T).

אחד היתרונות של טבלת "אקסל" הוא שהיא גדלה באופן דינמי: כל שורה חדשה –מתווספת באופן אוטומטי לטבלה. אם נוסחא מתייחסת לכל שורות הטבלה - אזי הוספת השורה אינה כרוכה בשינויים בנוסחא (מה שלא קורה כשמדובר בנוסחא המתייחסת לתחום תאים: כאן אנו נדרשים לשנות את הנוסחא בגין כל שורה חדשה שנוספת לתחום, כדי שתיכלל בנוסחא).

כך נראית טבלת הנתונים לאחר שהפכנו אותה לטבלת "אקסל".

תמונה מס. 2: כך נראים הנתונים לאחר שהפכנו אותם לטבלת "אקסל"

אפשר לראות שבטבלה, "אקסל" צובע כל שורה לסירוגין בצבע אחר וגם מאפשר (אוטומטית) מיון וסינון בראש כל עמודה.

אקסל נתן לטבלה החדשה את השם: Table1, אך אנו שינינו את שמה ל: FBTable.

כדי להתייחס לכל התאים בעמודה מסוימת, למשל בעמודה: Likers, אין צורך לדעת מהי השורה האחרונה בטבלה. מספיק לציין: FBTable[Likere]

ועכשיו, לשליפת החתכים הרצויים:

1. כמה פוסטים העליתי לדף האוהדים שלי לאחר 10/06/2014

תמונה מס. 3: כמה פוסטים העליתי אחרי 10/06/2014 – נוסחא

נוסחת המערך לשליפת המידע הרצוי:

{=SUM(IF(FBTable[Date]>I60,1,0))}

נוסחת המערך בתא J60, משתמשת בתאריך המבוקש (10/06/2014 המופיע כפרמטר בתא I60, במקום להקליד אותו בתוך הנוסחא).

והתשובה: 12

תמונה מס. 4: כמה פוסטים העליתי אחרי 10/06/2014 - תשובה

מכיון שזו נוסחת מערך, היא בודקת כל תא ותא בעמודה Date שבטבלה: FBTable, האם ערכו גבוה מתאריך הפרמטר בתא I60. אם התנאי מתקיים, מוסיפה 1. אחרת (התנאי לא מתקיים) מוסיפה 0. לאחר מכן, סוכמת את כל הערכים שענו על התנאי באמצעות הפונקציה: SUM. התוצאה: מספר התאים שהתאריך שבהם גבוה מתאריך הפרמטר.

2. כמה פוסטים העליתי בתקופה: 01/05/2014-31/05/2014

תמונה מס. 5: כמה פוסטים העליתי בתקופה 01/05/2014-31/05/2014 - נוסחא

נוסחת המערך לשליפת המידע הרצוי:

{=SUM(IF(FBTable[Date]>=H63,IF(FBTable[Date]<=I63,1,0),0))}

גם כאן נעשית הבדיקה על העמודה Date שבטבלה שלנו, אבל כעת אנו בודקים קיום שני תנאים (בעזרת IF מקונן): שהתאריך אינו נמוך מתאריך ההתחלה (01/05/2014) ושהתאריך אינו גבוה מתאריך הסיום (31/05/2014)

והתוצאה: 9

תמונה מס. 6: כמה פוסטים העליתי בתקופה 01/05/2014-31/05/2014 - תוצאה

3. כמה "לייקים" עשתה לי שרונה מלר בכל התקופה

תמונה מס. 7: כמה "לייקים" עשתה לי שרונה מלר בכל התקופה - נוסחא

נוסחת המערך לשליפת המידע הרצוי:

{=SUM(IF(IFERROR(FIND(I66,FBTable[Likers]),0),1,0))}

אנו משתמשים בפונקציה FIND כדי למצוא מחרוזת תוים בתוך תא. המחרוזת לחפוש נתונה כפרמטר (בתא: I66).

הפונקציה FIND מחפשת את המחרוזת בכל העמודה Likers בטבלה (FBTable[Likers])– העמודה שבה מופיעים שמות האנשים שעשו לי "לייק".

אם המחרוזת לא נמצאת, "אקסל" – מחזיר שגיאה. לכן, כדי למנוע את הודעת השגיאה, אנו "עוטפים" את פונקצית החפוש ב- IFERROR: אם המחרוזת המבוקשת לא נמצאה, נקבל את הערך: 0 ונמנע הודעת שגיאה.

אם נסמן מתוך הנוסחא את הפונקציה המורכבת: IFERROR(FIND(I66,FBTable[Likers],0) ונקיש F9 - נקבל מערך המכיל אפסים או מספרים.

0 – פרושו: כל תא בעמודה Likers שבו לא נמצאה המחרוזת

ומספר- פרושו: בכל תא שנמצאה בו המחרוזת, המספר מייצג את מספר התו בתא שבו נמצאה המחרוזת.

תמונה מס. 8: בהקשת F9 על הנוסחא – נקבל מערך ובו מיקום המחרוזת בתאי העמודה

אם נבדוק במערך הנ"ל - נגלה שיש בו 9 מספרים השונים מ-0, כלומר: המחרוזת נמצאה ב-9 תאים בעמודה.

תמונה מס. 9: המחרוזת מופיעה 9 פעמים במערך (השוה לתמונה מס.8)

למשל, אפשר לראות שבמקום הראשון במערך מופיע הערך 1:

ופרוש הדבר: המחרוזת "שרונה מלר" נמצאה בתו הראשון בתא הראשון בעמודת Likers (תא F2)

תמונה מס. 10: המחרוזת "שרונה מלר" נמצאה בתא F2 (התא הראשון בעמודה Likers)

דוגמא נוספת: במקום הרביעי במערך - נמצא המספר 24 (ראה תמונה 8,9). אם נבדוק את השורה הרביעית בעמודת Likers של הטבלה - נראה שהמחרוזת "שרונה מלר" מתחילה בתו ה-24 של התא הרביעי בעמודה (תא F5).

תמונה מס. 11: המחרוזת "שרונה מלר" נמצאה בתא F5 (התא הרביעי בעמודה Likers)

פונקצית ה- IF ה"עוטפת" את ה- IFERROR – הופכת כל מספר ל-1.

והפונקציה ה"חיצונית" (SUM) סוכמת את כל ה-1-ים והתוצאה: 9.

התשובה: 9

תמונה מס. 12: כמה "לייקים" עשתה לי שרונה מלר בכל התקופה – תוצאה

4. כמה "לייקים" עשה לי פרנץ פושקש בחודש מרץ 2014:

(01/03/2014-31/03/2014)

תמונה מס. 13: כמה "לייקים" עשה פרנץ פושקש ב: 01/03/2014-31/03/2014 - נוסחא

נוסחת המערך לשליפת המידע הרצוי:

{=SUM(IF(IFERROR(FIND(I70,FBTable[Likers]),0),
IF(FBTable[Date]>=H71,IF(FBTable[Date]<=I71,1))))}

הנוסחא דומה לנוסחא בשאלה 3, אבל כאן רוצים לדעת מספר "לייקים" של מבקר בתקופה מסוימת. לכן, על ה-IF המקורי בשאלה 3 (ה"עוטף" את ה IFERROR בחפוש המחרוזת) נוסיף עוד 2 IF-ים: האחד, שיתנה רק פוסטים שתאריך העלאתם אינו נמוך מ- 01/03/2014 והשני, שיתנה רק פוסטים שתאריך העלאתם אינו גבוה מ- 31/03/2014