נוסחא

[of1964]

נוסחא

מצב קובץ

תודה

 

[iyyi]

SUMIF מכל הגיליונות

כדי לעשות אתה צריך לכתוב נוסחה שמביאה את שמות הגיליונות שבחוברת העבודה.
לאקסל אין פונקציה מובנית לעשות זאת, אך יש את פונקציות GET השונות שמביאות מידע. את הפונקציות צריך לשים בתוך "שם" (Named Range) כדי להשתמש בהן.
בהצעתי התבססתי על הקישור הזה
לשם SheetNames ניתנה הנוסחה הבאה:
=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

SheetNames הוא מערך של שמות הגיליונות שניתן לחלץ אותם בעזרת פונקציית INDEX.
הסיכום המותנה (SUMIF) מכל הגיליונות נעשה בתא B3 בעזרת נוסחת המערך הבאה:
=SUM(IFERROR(SUMIF(INDIRECT("'"&IFERROR(INDEX(SheetNames,ZZ$2:ZZ$100),"")&"'!"&"D5"),A19,INDIRECT("'"&INDEX(SheetNames,ZZ$2:ZZ$100)&"'!"&"E5")),0))

יש לסיים את הכנסתה עם צירוף המקשים Ctrl+Shift+Enter
הבסיס לנוסחה נלקח מהקישור הזה, עם שינויים.

כמה הערות:

1. השימוש במקטע ה-INDEX של ZZ2:ZZ100 נועד להביא את שמות הגיליונות מהגיליון השני ועד הגיליון ה-100 (בהנחה שאין לך יותר מזה). הבחירה בעמודה ZZ היא למנוע מצב שיהיו נתונים בתחום התאים.
2. ההתניה במקרה שלך היא פשוטה - לחפש בתא אחד ולהביא את הנתון שלצידו, אך אותה נוסחה יכולה לעבוד על קבוצת תאים. נניח שצריך לחפש את תאריכים בגיליונות בעמודה D, אז יהיה צריך להחליף את ה-D5 שבנוסחה בתחום - למשל D2100. כנ"ל לגבי הנתונים המסוכמים בעמודה E
3. צריך לשמור את הקובץ כסוג xlsm בגלל השימוש בפונקציית ה-GET.

מצורף קובץ הדגמה

 

[iyyi]

ניתן לקצר את נוסחת ה-SUMIF

אפשר לקצר את הנוסחה לזה:
=SUM(SUMIF(INDIRECT("'"&INDEX(SheetNames,)&"'!"&"D5"),A3,INDIRECT("'"&INDEX(SheetNames,)&"'!"&"E5")))

ולסיים ב-Ctrl+Shift+Enter
מתברר שאפשר לוותר על החלק שלעמודה ZZ.
אתה צריך לשים לב שבגיליון הסיכום לא יהיו נתונים בתחום המסוכם (אצלך זה בתאים D5:E5)
כמו כן, לא הזכרתי שאתה צריך לאפשר הפעלת מקרו (למרות שאין קוד מקרו, השימוש בפונקציית GET מבחינת האקסל זה מקרו)

 

[of1964]