ספירת תאים של נתונים עם פונקציית SUMPRODUCT של Excel

הפונקציה SUMPRODUCT ב- Excel היא פונקציה מאוד מגוונת שתיתן תוצאות שונות בהתאם לארגומנטים שהוזנו.

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

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

01 מתוך 04

SUMPRODUCT לעומת COUNTIF ו- COUNTIFS

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

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

02 מתוך 04

פונקציה תחביר וארגומנטים לספירת תאים

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

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

= SUMPRODUCT ([condition1] * [condition2])

הסבר על אופן הפעולה של תחביר זה מתואר בדוגמה הבאה.

דוגמה: ספירת תאים שעומדים בתנאים מרובים

כפי שמוצג בדוגמה לעיל, SUMPRODUCT משמש כדי למצוא את המספר הכולל של תאים בטווח הנתונים A2 עד B6 המכילים נתונים בין הערכים של 25 ו -75.

03 מתוך 04

הזנת פונקציית SUMPRODUCT

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

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

בתמונה שלמעלה, השלבים הבאים שימשו להזנת SUMPRODUCT לתא B7:

1. לחץ על התא B7 בגליון העבודה - המיקום שבו תוצאות הפונקציה יוצגו
2. הקלד את הנוסחה הבאה בתא E6 של גליון העבודה:

   = SUMPRODUCT ($ A $ 2: $ B $ 6> 25) * ($ A $ 2: $ B $ 6 <75))

3. התשובה 5 צריכה להופיע בתא B7, מכיוון שיש רק חמישה ערכים בטווח - 40, 45, 50, 55 ו -60 - שהם בין 25 ל -75
4. כאשר אתה לוחץ על התא B7 הנוסחה הושלמה = SUMPRODUCT ($ A $ 2: $ B $ 6> 25) * ($ A $ 2: $ B $ 6 <75)) מופיע בסרגל הנוסחה מעל גליון העבודה

04 מתוך 04

לשבור את פונקציית SUMPRODUCT

כאשר התנאים מוגדרים עבור הארגומנטים, SUMPRODUCT מעריך כל אלמנט מערך כנגד המצב ומחזיר ערך בוליאני (TRUE או FALSE).

לצורך החישובים, Excel מקצה ערך 1 עבור אותם רכיבי מערך שהם TRUE וערך 0 עבור רכיבי מערך שהם FALSE.

המקבילים והאפסים בכל מערך מוכפלים זה לזה:

• עם 1 x 1 החזרת ערך של 1
• עם 1 x 0 החזרת ערך של 0

אלה ואפס הם סיכם אז על ידי פונקציה לתת לנו לספור את מספר הערכים העונים על שני התנאים.

או, לחשוב על זה ככה ...

דרך נוספת לחשוב על מה ש- SUMPRODUCT עושה היא לחשוב על סימן הכפל כתנאי.

עם זאת, זה רק כאשר שני התנאים נפגשו - מספרים גדול מ 25 ו פחות מ 75 - כי ערך TRUE (אשר שווה אחד זוכר) מוחזר.

הפונקציה מסכמת את כל הערכים האמיתיים כדי להגיע לתוצאה של 5.