פונקציית SUMIF באקסל וסיכום לפי מספר תנאים

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

היום נדבר על אחת מפונקציות כאלה - SUMMESLIMN.

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

פונקציית SUMIFS תיאור מפורט

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

אם תשלב אותם, תקבל פונקציה נוספת - SUMMESLI, שבודקת את הנתונים מול קריטריונים שצוינו על ידי המשתמש ומסכמת רק את המספרים העומדים בקריטריונים הללו. אם אנחנו מדברים על הגרסה האנגלית של Excel, אז פונקציה זו נקראת SUMIF. במילים פשוטות, השם -שפת הוא תרגום ישיר של השם בשפה האנגלית. ניתן להשתמש בפונקציה זו למגוון מטרות. בפרט, זה יכול לשמש כחלופה VPR, כלומר, לרשום

ההבדל העיקרי בין הפונקציה SUMMESLIMN  מהפונקציה הרגילה SUMMESLI הוא שמשתמשים בכמה קריטריונים. התחביר שלו די מורכב ממבט ראשון, אבל בבדיקה מעמיקה יותר מתברר שההיגיון של הפונקציה הזו פשוט מאוד. ראשית עליך לבחור את הטווח שבו הנתונים ייבדקו, ולאחר מכן להגדיר את התנאים לעמידה בהם יתבצע הניתוח. וניתוח כזה יכול להתבצע עבור מספר גדול למדי של תנאים.

התחביר עצמו הוא:

SUMIFS(sum_range, condition_range1, condition1, [condition_range2, condition2], …)

במקומות מתאימים יש צורך לשים מערכים של תאים המתאימים למקרה מסוים. 

בואו נסתכל על הטיעונים ביתר פירוט:

1. Sum_range. טיעון זה, כמו גם הטווח של תנאי 1 ותנאי 1, נדרש. זוהי קבוצה של תאים שצריך לסכם.
2. טווח_מצב1. זה הטווח שבו התנאי ייבדק. זה משויך לארגומנט הבא - תנאי 1. סיכום הערכים המתאימים לקריטריון מתבצע בתוך התאים שצוינו בטיעון הקודם.
3. מצב 1. טיעון זה מפרט את הקריטריונים שיש לבדוק מולם. ניתן להגדיר, למשל, בצורה זו: "> 32".
4. טווח תנאים 2, תנאי 2... כאן, התנאים הבאים מוגדרים באותו אופן. אם צריך לציין יותר מכמה תנאים, אז מתווספים הארגומנטים טווח תנאי 3 ותנאי 3. התחביר זהה עבור הארגומנטים הבאים.

הפונקציה מאפשרת עיבוד מרבי של עד 127 זוגות של תנאים וטווחים. 

אתה יכול להשתמש בו בכמה תחומים בו-זמנית (אנחנו ניתן רק כמה, הרשימה למעשה עוד ארוכה):

1. ראיית חשבון. לדוגמה, טוב להשתמש בפונקציה SUMMESLIMN ליצירת דוחות סיכום, לפי רבעון עבור הוצאה מעל סכום מסוים, למשל. או ליצור דוח על מוצר אחד מקטגוריית מחיר מסוימת.
2. ניהול מכירות. גם כאן הפונקציה יכולה להיות מאוד שימושית. כך למשל, עומדת בפנינו המשימה לסכם רק את עלות הסחורה שנמכרה ללקוח מסוים בזמן מסוים. ובמצב כזה, הפונקציה SUMMESLIMN יכול להיות מאוד מועיל.
3. חינוך. היום ניתן עוד דוגמאות מעשיות מתחום זה. בפרט, אתה יכול להשתמש בו כדי לקבל סיכום של ציוני תלמידים. ניתן לבחור עבור מקצוע בודד או עבור ציונים בודדים. אדם יכול להגדיר מיד כמה קריטריונים לפיהם תיבחר ההערכה, וזה מאוד נוח ויכול לחסוך הרבה זמן.

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

1. יכולת להגדיר מספר קריטריונים. למה זה יתרון? אתה יכול להשתמש בפונקציה הרגילה SUMMESLI! והכל בגלל שזה נוח. אין צורך לבצע חישובים נפרדים עבור כל אחד מהקריטריונים. ניתן לתכנת את כל הפעולות מראש, אפילו לפני. כיצד תיווצר טבלת הנתונים. זהו חיסכון נהדר בזמן.
2. אוטומציה. העידן המודרני הוא עידן האוטומציה. רק מי שיודע לבצע אוטומציה נכונה של עבודתו יכול להרוויח הרבה. לכן היכולת לשלוט באקסל ובפונקציה SUMMESLIMN במיוחד, הוא כל כך חשוב עבור כל אדם שרוצה לבנות קריירה. ידיעת פונקציה אחת מאפשרת לך לבצע מספר פעולות בבת אחת, כאחת. וכאן אנו עוברים ליתרון הבא של תכונה זו.
3. לחסוך זמן. רק בשל העובדה שפונקציה אחת מבצעת מספר משימות בבת אחת.
4. פַּשְׁטוּת. למרות העובדה שהתחביר די כבד במבט ראשון בגלל הנפח שלו, למעשה, ההיגיון של פונקציה זו הוא פשוט מאוד. תחילה, נבחר טווח של נתונים, ולאחר מכן טווח של ערכים, אשר ייבדק על עמידה בתנאי מסוים. וכמובן, יש לפרט גם את התנאי עצמו. וכך מספר פעמים. למעשה, פונקציה זו מבוססת רק על מבנה לוגי אחד, מה שהופך אותה לפשוטה יותר מהידועה VPR למרות העובדה שניתן להשתמש בו לאותן מטרות, גם תוך התחשבות במספר גדול יותר של קריטריונים. 

תכונות השימוש בפונקציית SUMIFS

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

1. אתה יכול להשתמש בסוגים אלה של ערכים כתנאים לבחירת תאים כדי להוסיף עוד את הערכים הכלולים בהם: ערכים מספריים, ביטויים בוליאניים, הפניות לתאים וכו'. 
2. אם נבדקים טקסט, ביטויים לוגיים או סימנים מתמטיים, קריטריונים כאלה מפורטים באמצעות מרכאות.
3. לא ניתן להשתמש במונחים של יותר מ-255 תווים.
4. אפשר להשתמש בקריטריונים משוערים לבחירת ערכים באמצעות תווים כלליים. סימן השאלה משמש להחלפת תו בודד, וסימן הכפל (כוכבית) נחוץ כדי להחליף מספר תווים. 
5. ערכים בוליאניים שנמצאים בטווח הסיכום מומרים אוטומטית לערכים מספריים בהתאם לסוג שלהם. לפיכך, הערך "TRUE" הופך לאחד, ו"FALSE" - לאפס. 
6. אם #VALUE! שגיאה מופיעה בתא, המשמעות היא שמספר התאים בטווחי התנאים והסיכום שונה. אתה צריך לוודא שהגדלים של הטיעונים האלה זהים. 

דוגמאות לשימוש בפונקציית SUMIFS

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

טווח דינמי של סיכום מצב

אז נתחיל בדוגמה הראשונה. נניח שיש לנו טבלה המכילה מידע על איך התלמידים מתמודדים עם תכנית הלימודים בנושא מסוים. יש סט ציונים, הביצוע מוערך בסולם של 10 נקודות. המשימה היא למצוא את הציון לבחינה של אותם תלמידים ששם משפחתם מתחיל באות א', והציון המינימלי שלהם הוא 5.

הטבלה נראית כך.

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

נתאר את הטיעונים ביתר פירוט:

1. C3:C14 הוא טווח הסיכום שלנו. במקרה שלנו, זה עולה בקנה אחד עם טווח התנאים. מתוכו ייבחרו נקודות המשמשות לחישוב הסכום, אך רק אלו שנופלות בקריטריונים שלנו.
2. ">5" הוא התנאי הראשון שלנו.
3. B3:B14 הוא טווח הסיכום השני שמעובד כדי להתאים לקריטריון השני. אנו רואים שאין מקריות לטווח הסיכום. מכאן אנו מסיקים שטווח הסיכום וטווח התנאי עשויים להיות זהים או לא. 
4. "A*" הוא הטווח השני, המציין את בחירת הציונים רק עבור אותם תלמידים ששם משפחתם מתחיל ב-A. במקרה שלנו, כוכבית פירושה כל מספר של תווים. 

לאחר חישובים, נקבל את הטבלה הבאה.

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

סיכום סלקטיבי לפי תנאי באקסל

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

הטבלה עצמה נראית כך. 

כדי לקבוע את התוצאה הסופית, אנחנו צריכים נוסחה כזו.

=(СУММЕСЛИМН(D2:D14;A2:A14;»=июнь»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)+(СУММЕСЛИМН(D2:D14;A2:A14;»=август»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)))

כתוצאה מהחישובים שבוצעו על ידי נוסחה זו, אנו מקבלים את התוצאה הבאה.

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

פונקציית SUMIFS לסיכום ערכים על פני מספר תנאים

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

אנו משתמשים בנוסחה הבאה (אך אנו כותבים אותה כנוסחת מערך, כלומר, נזין אותה באמצעות צירוף המקשים CTRL + SHIFT + ENTER).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

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

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

עכשיו בואו נסתכל על הטבלה המכילה את הסכומים.

כפי שאתה יכול לראות, הצלחנו. אין ספק שגם אתה תצליח. הצלחה גדולה בתחום זה. זוהי פונקציה פשוטה מאוד שאדם שזה עתה דרכה רגלו בדרך ללימוד אקסל יכול להבין. ואנחנו כבר יודעים שהפונקציה SUMMESLIMN מאפשר לך להיות אפקטיבי בכל תחום פעילות, מחשבונאות ועד אפילו חינוך. גם אם אתה בונה קריירה בכל תחום אחר שלא תואר לעיל, תכונה זו עדיין תעזור לך להרוויח כסף. זו הסיבה שהיא בעלת ערך.

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