IF מקונן הוא IF בתוך IF אחר, ומשתמשים בו כשיש יותר משתי תוצאות אפשריות. =IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","F"))) נותנת A ל-90 ומעלה, B ל-80 עד 89, C ל-70 עד 79, ו-F מתחת ל-70.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Grade |
| 2 | Ana | 94 | A |
| 3 | Ben | 81 | B |
| 4 | Chloe | 70 | C |
| 5 | Dan | 65 | F |
| 6 | Eve | 88 | B |
| 7 | Finn | 90 | A |
לחצו על C2 והסתכלו בשורת הנוסחאות: שלוש פונקציות IF, ושלושה סוגריים סוגרים בסוף. שנו את הציון של Dan ב-B5 ל-75 והציון שלו משתנה מ-F ל-C.
איך קוראים IF מקונן
אקסל קורא את הנוסחה מההתחלה ועוצר בבדיקה הראשונה שהיא TRUE:
=IF(B2>=90, "A",
IF(B2>=80, "B",
IF(B2>=70, "C",
"F")))
- האם הציון 90 או יותר? אז A, ושום דבר אחר לא נבדק.
- אחרת, האם הוא 80 או יותר? אז B. הבדיקה הזו לא צריכה לומר "ומתחת ל-90", כי ציון של 90 ומעלה אף פעם לא מגיע אליה.
- אחרת, האם הוא 70 או יותר? אז C.
- אחרת F, ה-value_if_false של ה-IF האחרונה.
כל IF פנימית יושבת במקום של value_if_false של זו שלפניה. אקסל מאפשר עד 64 רמות, אבל קשה לבדוק בעין נוסחה עם יותר מארבע או חמש. אקסל מקבל ירידות שורה בתוך נוסחה, כך שאפשר לסדר נוסחה ארוכה ככה בשורת הנוסחאות: לחצו Alt+Enter (ב-Windows) או Control+Option+Return (במק) לפני כל IF.
למה סדר התנאים חשוב
מכיוון שאקסל עוצר בבדיקה הראשונה שהיא TRUE, הספים צריכים ללכת מהגבוה לנמוך כשמשתמשים ב->=. בעמודה D יש את אותן שלוש בדיקות בסדר הפוך:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Score | Right order | Wrong order |
| 2 | Ana | 94 | A | C |
| 3 | Ben | 81 | B | C |
| 4 | Chloe | 70 | C | C |
| 5 | Dan | 65 | F | F |
| 6 | Eve | 88 | B | C |
בעמודה D כל מי שיש לו 70 ומעלה מקבל C: ציון של 94 עובר את הבדיקה הראשונה, B2>=70, ובדיקות ה-B וה-A אף פעם לא מגיעות. אם אתם מעדיפים להתחיל מהרמה הנמוכה, הפכו את האופרטורים: =IF(B2<70,"F",IF(B2<80,"C",IF(B2<90,"B","A"))) נותנת את אותם ציונים כמו עמודה C.
IF מקונן עם טקסט
הבדיקות יכולות להשוות גם טקסט. כאן דמי המשלוח תלויים באזור, וכל אזור שלא הוזכר מקבל את הערך האחרון:
| A | B | C | |
|---|---|---|---|
| 1 | Order | Region | Fee |
| 2 | 1001 | North | $5.00 |
| 3 | 1002 | South | $7.00 |
| 4 | 1003 | West | $9.00 |
| 5 | 1004 | East | $6.00 |
| 6 | 1005 | Islands | $9.00 |
West ו-Islands לא מתאימים לאף אחת משלוש הבדיקות ומקבלים את הערך האחרון, $9.00. כשכל בדיקה משווה את אותו תא לערך קבוע, כמו כאן, SWITCH כותבת את אותו כלל עם כל אזור פעם אחת: =SWITCH(B2,"North",5,"South",7,"East",6,9). ראו את העמוד של SWITCH.
IF מקונן עם AND
IF מקונן יכול לשלב את הרמות שלו עם AND או OR כשרמה אחת תלויה בשני תאים. נציג עם מכירות של 2,000 ומעלה ולפחות 3 שנות ותק מקבל 10%, כל אחד אחר מעל 2,000 מקבל 5%, והשאר לא מקבלים כלום:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Sales | Years | Rate |
| 2 | Ana | 2400 | 4 | 10% |
| 3 | Ben | 2100 | 1 | 5% |
| 4 | Chloe | 1500 | 6 | 0% |
| 5 | Dan | 3000 | 3 | 10% |
| 6 | Eve | 900 | 2 | 0% |
Ana ו-Dan זכאים ל-10%, ל-Ben יש את המכירות אבל לא את הוותק והוא מקבל 5%, ו-Chloe ו-Eve מקבלות 0%. גם כאן הסדר חשוב: הבדיקה המחמירה יותר באה ראשונה.
IFS: אותו דבר בלי קינון
ב-Excel 2019, Excel 2021 וב-Microsoft 365, IFS מקבלת את הבדיקות והתוצאות בזוגות, בלי IF פנימית ועם סוגר סוגר אחד. TRUE כבדיקה האחרונה פועלת כ"כל השאר":
=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"F")
היא קוראת את התנאים באותו סדר ועוצרת בראשון שהוא TRUE, ולכן כלל הסדר שלמעלה עדיין חל. העמוד של IFS מסביר אותה, כולל ה-#N/A שהיא מחזירה כששום בדיקה לא מתאימה. ב-Excel 2016 ומטה IFS לא זמינה, וקובץ שמשתמש בה מציג שם #NAME?.
תרגול: עמלה בשלוש רמות
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Sales | Commission |
| 2 | Ana | $6,200 | |
| 3 | Ben | $2,400 | |
| 4 | Chloe | $600 |
תורכם: ב-C2, שלמו 10% מהמכירות ב-B2 כשהן 5000 או יותר, 5% כשהן 1000 או יותר, ו-0 אחרת. הנוסחה מועתקת למטה עד C4.
טבלת חיפוש במקום הרבה IF
כשהרמות הן מספרים ויש יותר משלוש או ארבע מהן, שמרו את הספים בטבלה קטנה וחפשו בה. הטבלה ממוינת מהסף הנמוך ביותר כלפי מעלה, והתאמה משוערת (TRUE כארגומנט האחרון) מחזירה את השורה של הסף הגדול ביותר שאינו מעל הציון:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Score | Grade | Min score | Grade | |
| 2 | Ana | 94 | A | 0 | F | |
| 3 | Ben | 81 | B | 70 | C | |
| 4 | Chloe | 70 | C | 80 | B | |
| 5 | Dan | 65 | F | 90 | A | |
| 6 | Eve | 88 | B |
התוצאות זהות ל-IF המקונן שבראש העמוד. כדי להעביר את רמת ה-B ל-85, שנו את E4 ל-85: אף נוסחה לא משתנה, וכל ציון מתעדכן. עם XLOOKUP אותו חיפוש הוא =XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1), כאשר -1 פירושו "התאמה מדויקת או הערך הקטן הבא"; אז הטבלה לא צריכה להיות ממוינת.
נסו בעצמכם: טבלת הרמות מוכנה, כתבו את החיפוש.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Score | Grade | Min score | Grade | |
| 2 | Ana | 86 | 0 | F | ||
| 3 | 70 | C | ||||
| 4 | 80 | B | ||||
| 5 | 90 | A |
תורכם: ב-C2, החזירו את הציון עבור הנקודות ב-B2 מטבלת הרמות ב-E2:F5.
שאלות נפוצות
איך כותבים כמה תנאי IF באקסל?
שימו את ה-IF הבאה בארגומנט value_if_false של הקודמת: =IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","F"))). אקסל בודק את התנאים מהראשון לאחרון ועוצר בראשון שהוא TRUE.
כמה פונקציות IF אפשר לקנן באקסל?
עד 64 רמות ב-Excel 2007 ואילך. הרבה לפני המגבלה הזו, נוסחה הופכת לקשה לקריאה ולבדיקה; עם יותר משלוש או ארבע רמות, טבלת חיפוש עם VLOOKUP או XLOOKUP בהתאמה משוערת קלה יותר לתחזוקה.
למה ה-IF המקונן שלי מחזיר תוצאה שגויה?
בדרך כלל כי התנאים בסדר הלא נכון. עם בדיקות >=, התחילו מהסף הגבוה ביותר: אם B2>=70 בא ראשון, ציון של 95 עוצר שם ומקבל את התוצאה של רמת ה-70.
במה אפשר להשתמש במקום IF מקונן באקסל?
IFS ב-Excel 2019 ואילך (=IFS(B2>=90,"A",B2>=80,"B",TRUE,"F")), SWITCH כשמשווים ערך אחד לערכים קבועים, וטבלת חיפוש עם =VLOOKUP(B2,$E$2:$F$5,2,TRUE) לטווחים מספריים.
