סכום ערכי טקסט כמו מספרים

כדי לתרגם ערכי טקסט למספרים ולסכם את התוצאה, ניתן להשתמש בנוסחת INDEX ו- MATCH, ובפונקציה SUM. בדוגמה המוצגת, הנוסחה ב- H5 היא:

 
{= SUM ( INDEX (value, N ( IF (1, MATCH (C5:G5,code,0)))))}

היכן 'קוד' הוא טווח בשם K5: K9, ו'ערך 'הוא הטווח הנקרא L5: L9.

הערה: זהו נוסחת מערך , ויש להזין אותו באמצעות שליטה + shift + enter.

ליבה של נוסחה זו הוא נוסחת INDEX ו- MATCH בסיסית, המשמשת לתרגום ערכי טקסט למספרים כהגדרתם בטבלת חיפוש. לדוגמה, כדי לתרגם 'EX' למספר המתאים, נשתמש ב:

 
= INDEX (value, MATCH ('EX',code,0))

שיחזיר 4.

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

 
= SUM ( INDEX (value, MATCH (C5:G5,code,0)))

לאחר ריצות MATCH, יש לנו מערך עם 5 פריטים:

 
= SUM ( INDEX (value,{2,2,3,2,5}))

אז נראה ש- INDEX צריכה להחזיר 5 תוצאות ל- SUM. עם זאת, אם תנסה זאת, הפונקציה INDEX תחזיר תוצאה אחת בלבד SUM. כדי לגרום ל- INDEX להחזיר מספר תוצאות, עלינו להשתמש ב טריק לא ברור , ועוטפים את MATCH ב- N ו- IF כך:

 
 N ( IF (1, MATCH (C5:G5,code,0)))

זה למעשה מאלץ את INDEX לספק יותר מערך אחד לפונקציית SUM. לאחר ריצות INDEX, יש לנו:

כיצד להכין תרשים עמודות על Excel

 
= SUM ({3,3,2,3,-1})

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