לְהִצטַיֵן

19 טיפים לנוסחאות IF מקוננות

19 Tips Nested If Formulas

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



אבל IF אחד מוביל לעתים קרובות לאחר, וברגע שאתה משלב יותר מכמה IFs, הנוסחאות שלך יכולות להתחיל להיראות כמו פרנקנשטיין קטנים :)

האם IFs מקוננים הם רעים? האם הם נחוצים לפעמים? מה האלטרנטיבות?





המשך לקרוא כדי ללמוד את התשובות לשאלות אלה ועוד ...

1. IF בסיסי

לפני שנדבר על IF מקונן, בואו נסקור במהירות את מבנה ה- IF הבסיסי:



 
= IF (test,[true],[false])

הפונקציה IF מפעילה בדיקה ומבצעת פעולות שונות בהתאם לתוצאה אם ​​היא נכונה או לא נכונה.

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

לשם המחשה, כאן אנו משתמשים ב- IF כדי לבדוק ציונים ולחשב את 'Pass' לציונים של 65 לפחות:

פונקציית IF בסיסית - החזרה

תא D3 בדוגמה מכיל נוסחה זו:

 
= IF (C3>=65,'Pass')

מה שאפשר לקרוא כך: אם הציון ב- C3 הוא לפחות 65, החזר 'Pass'.

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

 
= IF (C3>=65,'Pass','Fail')

פונקציית IF בסיסית - עם ערך מוסף עבור false

וִידֵאוֹ: כיצד לבנות נוסחאות לוגיות .

2. מה המשמעות של קינון

קינון פירושו פשוט לשלב נוסחאות, אחת בתוך השנייה, כך שנוסחה אחת תטפל בתוצאה של אחרת. לדוגמה, הנה נוסחה שבה הפונקציה TODAY מקוננת בתוך הפונקציה MONTH:

 
= MONTH ( TODAY ())

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

3. IF מקונן פשוט

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

לשם המחשה, להלן הרחבתי את הנוסחה המקורית לעבור/נכשלת לעיל כדי להתמודד עם תוצאות 'לא שלמות' על ידי הוספת פונקציה IF, והקניית IF אחת בתוך השנייה:

IF מקונן בסיסי

 
= IF (C3='','Incomplete', IF (C3>=65,'Pass','Fail'))

IF החיצוני פועל ראשון ובודק אם C3 ריק. אם כן, IF החיצוני מחזיר 'לא שלם', וה- IF הפנימי לעולם אינו פועל.

אם הציון הוא לא ריק , IF החיצוני מחזיר FALSE, ופונקציית IF המקורית מופעלת.

למד IFs מקונן עם אימון וידאו ברור ותמציתי .

4. IF מקונן לסולמות

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

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

ציון כיתה מַצָב
0-63 ו <64
64-72 ד <73
73-84 ג <85
85-94 ב <95
95-100 ל

כשהתנאים מובנים בבירור, אנו יכולים להזין את הצהרת IF הראשונה:

להצטיין ביום בשבוע בהתבסס על תאריך
 
= IF (C5<64,'F')

זה מטפל ב- 'F'. כעת, כדי לטפל ב- 'D', עלינו להוסיף תנאי נוסף:

 
= IF (C5<64,'F', IF (C5<73,'D'))

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

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C')))

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

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C', IF (C5<95,'B','A'))))

להלן נוסחת ה- IF המקוננת הסופית בפעולה:

הושלמה דוגמת IF מקוננת לחישוב ציונים

וִידֵאוֹ: כיצד להפוך IF מקונן להקצות ציונים

5. IFs מקוננים יש זרימה לוגית

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

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

הזרימה הלוגית של IF מקונן

6. השתמש ב- Evaluate כדי לצפות בזרימה הלוגית

ב- Windows, אתה יכול להשתמש ב- להעריך תכונה לצפיה ב- Excel פותר את הנוסחאות שלך, שלב אחר שלב. זוהי דרך מצוינת 'לראות' את הזרימה הלוגית של נוסחאות מורכבות יותר ולפתור בעיות כאשר הדברים אינם פועלים כפי שאתה מצפה. המסך שלהלן מציג את חלון הערכה פתוח ומוכן לדרך. בכל פעם שאתה לוחץ על כפתור הערך, 'השלב הבא' בנוסחה נפתר. תוכל למצוא הערכה בכרטיסייה נוסחאות של הסרט (Alt M, V).

שימוש ב- Evaluate כדי לעבור את ה- IF המקונן שמקצה ציונים

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

7. השתמש ב- F9 לאיתור תוצאות בדיקה

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

השתמש ב- F9 בדוק אם מקונן מקצה ציונים

השתמש ב- Control + Z (Command + Z) ב- Mac כדי לבטל את F9. תוכל גם ללחוץ על Esc כדי לצאת מעורך הנוסחאות ללא כל שינוי.

וִידֵאוֹ: כיצד לאתר באגים בנוסחה באמצעות F9

8. דע את הגבולות שלך

ל- Excel יש מגבלות עד כמה אתה יכול לקנן פונקציות IF. עד ל- Excel 2007, Excel אפשרה עד 7 רמות של תקנות IF מקוננות. ב- Excel 2007+, Excel מאפשר עד 64 רמות.

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

9. התאם סוגריים כמו מקצוען

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

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

סוגריים של נוסחה תואמים צבע אך קשה לראותם

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

למרבה הצער, ההעזה היא תכונה המיועדת ל- Windows בלבד. אם אתה משתמש ב- Excel ב- Mac לעריכת נוסחאות מורכבות, לפעמים הגיוני להעתיק את הנוסחה לעורך טקסט טוב ( רנגלר טקסט הוא בחינם ומעולה) כדי להשיג סוגים טובים יותר להתאמת סוגריים. Wrangler טקסט יהבהב כאשר סוגריים תואמים, ותוכל להשתמש ב- Command + B כדי לבחור את כל הטקסט הכלול בסוגריים. אתה יכול להדביק את הנוסחה בחזרה ל- Excel לאחר שיישר את העניינים.

10. השתמש בחלון קצה המסך כדי לנווט ולבחור

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

נווט ובחר ארגומנטים של נוסחה עם קצה המסך

אתה יכול לראות אותי משתמש הרבה בחלון קצה המסך בסרטון הזה: כיצד לבנות IF מקונן .

11. הקפידו על טקסט ומספרים

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

 
= IF (A1='100','Pass','Fail')

האם ציון המבחן ב- A1 בֶּאֱמֶת טקסט ולא מספר? לא? אז אל תשתמש במרכאות סביב המספר. אחרת, המבחן הלוגי יחזיר FALSE גם כאשר הערך הוא ציון חולף, מכיוון ש- '100 'אינו זהה ל -100. אם ציון המבחן הוא מספרי, השתמש בזה:

 
= IF (A1=100,'Pass','Fail')

12. הוספת מעברי שורות הופכים את IFs המקוננים לקלים לקריאה

כאשר אתה עובד עם נוסחה המכילה רמות רבות של IFs מקוננים, זה יכול להיות מסובך לשמור על הדברים. מכיוון של- Excel לא אכפת מ'מרווח לבן 'בנוסחאות (כלומר רווחים נוספים או מעברי שורות), תוכל לשפר מאוד את הקריאות של ifs מקוננים על ידי הוספת מעברי שורות.

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

קשה לקרוא קריאות IFs מקוננות ללא הפסקות שורות

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

מעברי שורות הופכים את IFs מקוננים לקלים יותר לקריאה

אתה יכול להוסיף מעברי שורות ב- Windows באמצעות Alt + Enter, ב- Mac, השתמש ב- Control + Option + Return.

וִידֵאוֹ: כיצד להקל על קריאת IF מקונן .

13. הגבל IFs עם AND ו- OR

IFs מקוננים הם רבי עוצמה, אך הם מסתבכים במהירות כאשר מוסיפים רמות נוספות. אחת הדרכים להימנע מרמות נוספות היא שימוש ב- IF בשילוב עם הפונקציות AND ו- OR. פונקציות אלה מחזירות תוצאה פשוטה של ​​TRUE/FALSE הפועלת בצורה מושלמת בתוך IF, כך שתוכל להשתמש בהן כדי להרחיב את ההיגיון של IF יחיד.

לדוגמה, בבעיה למטה, נרצה לשים 'x' בעמודה D כדי לסמן שורות שבהן הצבע הוא 'אדום' והגודל 'קטן'.

IF עם הפונקציה AND פשוט יותר משני IFs מקוננים

נוכל לכתוב את הנוסחה עם שני תקני IF מקוננים כך:

 
= IF (B6='red', IF (C6='small','x',''),'')

עם זאת, על ידי החלפת הבדיקה בפונקציה AND, נוכל לפשט את הנוסחה:

 
= IF ( AND (B6='red',C6='small'),'x','')

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

 
= IF ( AND ( OR (B4='red',B4='blue'),C4='small'),'x','')

כל זה הָיָה יָכוֹל ייעשה עם IFs מקוננים, אך הנוסחה תהפוך במהירות למורכבת יותר.

וִידֵאוֹ: אם זה או זה

14. החלף IFs מקוננים ב- VLOOKUP

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

 
= IF (E3='red',100, IF (E3='blue',200, IF (E3='green',300, IF (E3='orange',400,500))))

אנו יכולים להחליף אותו בקלות ב- VLOOKUP (הרבה יותר פשוט):

 
= VLOOKUP (E3,B3:C7,2,0)

מקונן אם מול VLOOKUP

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

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

ראה גם: 23 דברים שכדאי לדעת על VLOOKUP

וִידֵאוֹ: כיצד להשתמש ב- VLOOKUP

וִידֵאוֹ: מדוע VLOOKUP עדיף על IF מקונן

15. בחר בחירה

הפונקציה CHOOSE יכולה לספק פתרון אלגנטי כאשר עליך למפות מספרים פשוטים ורציפים (1,2,3 וכו ') לערכים שרירותיים.

בדוגמה להלן, CHOOSE משמש ליצירת קיצורים מותאמים אישית של יום חול:

מקונן IF מול הפונקציה CHOOSE

בטוח שאתה הָיָה יָכוֹל השתמש ב- IF מקונן ארוך ומסובך כדי לעשות את אותו הדבר, אך אנא אל תעשה זאת :)

16. השתמש ב- IFS במקום IFs מקונן

אם אתה משתמש ב- Excel 2016 באמצעות Office 365, יש פונקציה חדשה שבה תוכל להשתמש במקום IFs מקונן: פונקציית IFS. הפונקציה IFS מספקת מבנה מיוחד להערכת תנאים מרובים לְלֹא קינון:

פונקציית IFS - מספר תנאים ללא קינון

הנוסחה המשמשת למעלה נראית כך:

 
= IFS (D5<60,'F',D5<70,'D',D5<80,'C',D5<90,'B',D5>=90,'A')

שימו לב שיש לנו רק זוג סוגריים אחד!

מה קורה כאשר אתה פותח גיליון אלקטרוני המשתמש בפונקציית IFS בגרסה ישנה יותר של Excel? ב- Excel 2013 וב- 2010 (ואני מאמין ל- Excel 2007, אך אינני יכול לבדוק) תראה '_xlfn'. מצורף ל- IFS בתא. הערך שחושב בעבר עדיין יהיה שם, אבל אם משהו גורם לנוסחה לחשב מחדש, תראה שגיאת #NAME. למיקרוסופט יש מידע נוסף כאן .

17. מקס אאוט

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

פונקציית MAX נותנת לך דרך חכמה לעשות זאת ללא IF בשום מקום באופק:

 
= MAX (calculation,0)

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

אני אוהב את הבנייה הזו כיוון שהיא פשוט כל כך פשוט . עיין במאמר זה למאמר מלא .

18. טעויות מלכודת עם IFERROR

שימוש קלאסי ב- IF הוא ללכוד שגיאות ולספק תוצאה נוספת כאשר נזרקת שגיאה, כך:

 
= IF ( ISERROR (formula),error_result,formula)

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

ב- Excel 2007 הוצגה הפונקציה IFERROR, המאפשרת לך ללכוד שגיאות בצורה הרבה יותר אלגנטית:

 
= IFERROR (formula,error_result)

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

19. השתמש בהיגיון בוליאני

אתה יכול גם לפעמים להימנע ממקורות IF מקוננים באמצעות מה שנקרא 'לוגיקה בוליאנית'. המילה בוליאני מתייחסת לערכי TRUE/FALSE. למרות שאקסל מציגה את המילים TRUE ו- FALSE בתאים, בפנים, Excel מתייחס ל- TRUE כ- 1 ו- FALSE כאפס. אתה יכול להשתמש בעובדה זו כדי לכתוב נוסחאות חכמות ומהירות מאוד. לדוגמה, בדוגמה VLOOKUP למעלה, יש לנו נוסחת IF מקוננת שנראית כך:

 
= IF (E3='red',100, IF (E3='blue',200, IF (E3='green',300, IF (E3='orange',400,500))))

באמצעות לוגיקה בוליאנית, תוכל לשכתב את הנוסחה כך:

 
=(E3='red')*100+(E3='blue')*200+(E3='green')*300+(E3='orange')*400+(E3='purple')*500

כל ביטוי מבצע בדיקה, ולאחר מכן מכפיל את תוצאות הבדיקה ב'ערך אם נכון '. מכיוון שהבדיקות מחזירות TRUE או FALSE (1 או 0), התוצאות FALSE מבטלות את עצמן למעשה את הנוסחה.

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

וִידֵאוֹ: כיצד להשתמש בהיגיון בוליאני בנוסחאות אקסל

מתי אתה צריך IF מקונן?

עם כל האפשרויות האלה להימנעות ממקורות IF מקוננים, אתה עשוי לתהות מתי הגיוני להשתמש ב- IF מקונן?

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

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

חישוב סטטוס החשבונית עם IF מקונן

במקרה זה, IF מקונן הוא פתרון בסדר גמור.

המחשבות שלך?

מה איתך? האם אתה IF-ster? האם אתה נמנע מ- IFs מקונן? האם IFs מקוננים הם רעים? שתף את המחשבות שלך למטה.

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


^