
כדי לתרגם ערכי טקסט למספרים ולסכם את התוצאה, ניתן להשתמש בנוסחת 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 .
הסופר דייב בראנס