מבט שלישי - קורסי אקסל - השילוב המנצח, שלב נוסחאות ואובייקטים וצור דוחות מורכבים

השילוב המנצח, שלב נוסחאות ואובייקטים וצור דוחות מורכבים - טיפ 229

שלב בין נוסחאות SUMIF ו-OFFSET, רשימת אימות (Validation) ותיבה משולבת (Combo Box)
והחזר סיכום נתונים לפי קריטריון לתאים בדוח שתיצור:
שלב 1: הוסף רשימת אימות (Validation)
בחירת פריט מרשימת אימות (Validation) (ראה תא A14 בתמונה), תאפשר לנוסחאות שהוזנו לתאים
בעמודות C ו-E לזהות את הטקסט ולהחזיר את תוצאות הסיכום מעמודה שמכילה את הקריטריון עבור
חודש נבחר מתיבה משולבת (Combo Box).

בחר בטווח תאים (ראה תמונה בעמוד הקודם, בחר בתאים A14:A17).
מנתונים (Data) בחר אימות (Validation).
בתיבת הדו-שיח אימות נתונים (Data Validation) בחר בכרטיסיה הגדרות (Settings),
ובחר רשימה (List) מתוך אפשר (Allow).
בתיבה מקור (Source) הקש F3. בחר בשם שהוגדר עבור הרשימה (Level3 בדוגמה,
ראה תמונה).
לחץ אישור.

שלב 2: הזן נוסחאות להחזרת סיכום לפי סעיפים מאזניים:
הנוסחה בתא C14:
=SUMIF(DataLevel3,A14,OFFSET(DataLevel3,0,YearNumber+4))*-1
הנוסחה בתא E14
=SUMIF(DataLevel3,A14,OFFSET(DataLevel3,0,YearNumber+3))*-1

הסבר:
נוסחת SUMIF בתא C14 (ראה עמוד קודם) תחזיר סיכום של סעיף מאזני מזומן מעמודת דצמבר 2003
מטבלת מאזני בוחן (ראה תמונה בעמוד הבא). נוסחת SUMIF בתא 14E תחזיר סיכום של סעיף מאזני
מזומן מעמודת דצמבר 2002 מאותה טבלת נתונים.

לנוסחת SUMIF שלוש תיבות ארגומנטים:

תיבת ארגומנטים ראשונה:
הטווח בו תחפש הנוסחה את הקריטריון שהוזן בתיבת הארגומנטים השנייה של נוסחת SUMIF.
בדוגמה, הטווח הוא DataLevel3, שהוא השם שהוגדר לעמודה D בטבלת הנתונים של מאזני הבוחן,
ראה תמונה בעמוד הבא.

תיבת ארגומנטים שנייה:
הקריטריון הוא הפריט שנבחר מרשימת האימות בתא A14, שם הרשימה Level3.

תיבת ארגומנטים שלישית:
העמודה ממנה יסוכמו הנתונים, נוסחת OFFSET מסיטה מעמודה D לעמודה ממנה יסוכמו הנתונים לפי
בחירת חודש מתיבה משולבת (Combo Box), ראה הסבר בהמשך.

כיצד נוסחת OFFSET מסיטה לעמודה ממנה יסוכמו הנתונים:
עמודה 11 (עמודה K) היא מספר העמודה של חודש דצמבר 2003, ומספר העמודה של חודש דצמבר
2002 הוא 10, המרחק מעמודה D לעמודת חודש דצמבר 2003 הוא 7 (11-4) ואילו המרחק מעמודה D
לעמודת חודש דצמבר 2002 הוא 6.

עם בחירת פריט מתיבה משולבת, בדוגמה בחירת 31 בדצמבר 2003, מספר החודש שנבחר הוא 3
ברשימה. המרחק מעמודה D של חודש דצמבר 2003 הוא 7 (ראה חישוב בעמוד הקודם)

בתיבת הארגומנטים השלישית, בתיבת Sum_range, כתובת העמודה ממנה תסכם נוסחת SUMIF
אמורה להיות מרוחקת 7 עמודות מעמודה D שהיא עמודת הבסיס. נוסחת OFFSET תחזיר את התוצאה
7 לארגומנט השלישי של נוסחת SUMIF ותגרום לנוסחה להחזיר סיכום מעמודה זו.