הפונקציה IF – נוסחאות מקוננות והימנעות משגיאות

הפונקציה IF – נוסחאות מקוננות והימנעות משגיאות

הפונקציה IF מאפשרת לך לבצע השוואה לוגית בין ערך לבין מה שאתה מצפה על-ידי בדיקת תנאי והחזרת תוצאה אם הערך הוא True או False.

  • =IF([אם]משהו הוא True, בצע פעולה כלשהי. אם לא, בצע פעולה אחרת)

כך שמשפט IF יכול לקבל שתי תוצאות. התוצאה הראשונה מתקבלת אם ההשוואה שלך היא True, והתוצאה השניה מתקבלת אם ההשוואה שלך היא False.

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

* המונח "קינון" מתייחס לצירוף מספר פונקציות יחדיו בנוסחה אחת.

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

תחביר

‎IF(logical_test, value_if_true, [value_if_false]) ‎

לדוגמה:

  • ‎‎=IF(A2>B2,"Over Budget","OK") ‎‎

  • ‎=IF(A2=B2,B4-A4,"")‎

שם ארגומנט

תיאור

logical_test   

(נדרש)

התנאי שברצונך לבדוק.

value_if_true   

(נדרש)

הערך שברצונך שיוחזר אם התוצאה של logical_test היא TRUE.

value_if_false   

(אופציונלי)

הערך שברצונך שיוחזר אם התוצאה של logical_test היא FALSE.

הערות

למרות ש- Excel מאפשר לקנן עד 64 פונקציות IF שונות, לא מומלץ לעשות זאת. מדוע?

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

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

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

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

דוגמאות

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

משפט IF מקונן מורכב - הנוסחה ב- E2 היא ‎=IF(B2>97,"A+",IF(B2>93,"A",IF(B2>89,"A-",IF(B2>87,"B+",IF(B2>83,"B",IF(B2>79,"B-",IF(B2>77,"C+",IF(B2>73,"C",IF(B2>69,"C-",IF(B2>57,"D+",IF(B2>53,"D",IF(B2>49,"D-","F"))))))))))))‎
  • ‎=IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))‎

    משפט IF מקונן מורכב זה פועל לפי לוגיקה פשוטה:

  1. אם הציון במבחן (בתא D2) גדול מ- 89, התלמיד מקבל ציון A

  2. אם הציון במבחן גדול מ- 79, התלמיד מקבל ציון B

  3. אם הציון במבחן גדול מ- 69, התלמיד מקבל ציון C

  4. אם הציון במבחן גדול מ- 59, התלמיד מקבל ציון D

  5. אחרת, התלמיד מקבל ציון F

דוגמה מסוימת זו הנה בטוחה יחסית מכיוון שלא סביר להניח שהמתאם בין הציונים במבחנים לציונים המיוצגים על-ידי אותיות ישתנה, לכן היא אינה דורשת תחזוקה רבה. אבל בוא נחשוב רגע - מה אם עליך לפלח את הציונים לפי הקטגוריות A+‎,‏ A ו- A-‎ (וכן הלאה)? כעת עליך לכתוב מחדש את משפט ה- IF הכולל ארבעה תנאים למשפט בן 12 תנאים! כך תיראה הנוסחה שלך עכשיו:

  • ‎=IF(B2>97,"A+",IF(B2>93,"A",IF(B2>89,"A-",IF(B2>87,"B+",IF(B2>83,"B",IF(B2>79,"B-", IF(B2>77,"C+",IF(B2>73,"C",IF(B2>69,"C-",IF(B2>57,"D+",IF(B2>53,"D",IF(B2>49,"D-","F"))))))))))))‎

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

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

דוגמאות נוספות

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

הנוסחה בתא D9 היא ‎IF(C9>15000,20%,IF(C9>12500,17.5%,IF(C9>10000,15%,IF(C9>7500,12.5%,IF(C9>5000,10%,0)))))‎
  • ‎=IF(C9>15000,20%,IF(C9>12500,17.5%,IF(C9>10000,15%,IF(C9>7500,12.5%,IF(C9>5000,10%,0)))))‎

נוסחה זו מציינת ש- IF‏([אם]C9 גדול מ- 15,000 החזר 20%, IF‏([אם]C9 גדול מ- 12,500 החזר 17.5%, וכן הלאה...

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

עצה: באפשרותך להוסיף מעברי שורה בשורת הנוסחאות כדי להקל על הקריאה של נוסחאות ארוכות. פשוט הקש ALT+ENTER לפני הטקסט שברצונך שיגלוש לשורה חדשה.

להלן דוגמה לתרחיש העמלה עם לוגיקה לא תקינה:

הנוסחה ב- D9 אינה תקינה ‎=IF(C9>5000,10%,IF(C9>7500,12.5%,IF(C9>10000,15%,IF(C9>12500,17.5%,IF(C9>15000,20%,0)))))‎

האם אתה רואה את הבעיה? השווה את הסדר של השוואות ההכנסות לדוגמה הקודמת. מה הכיוון כאן? נכון, הכיוון הוא מלמטה למעלה (‎$5,000 עד ‎$15,000), ולא להיפך. אולם מדוע זה בעייתי? זה בעייתי משום שהנוסחה אינה יכולה לעבור את ההערכה הראשונה לערכים מעל ‎$5,000. נניח שיש לך הכנסה בסכום של ‎$12,500 – משפט IF יחזיר 10% מכיוון שהסכום גדול מ- ‎$5,000, והוא יעצור שם. זה עלול להיות בעייתי מאוד מכיוון שבמקרים רבים לא מבחינים בסוגי השגיאות הללו עד שהן גורמות נזק. אם כך, בידיעה שמשפטי IF מקוננים מורכבים טומנים בחובם כמה מכשולים חמורים, מה ניתן לעשות? ברוב המקרים, ניתן להשתמש בפונקציה VLOOKUP במקום לבנות נוסחה מורכבת עם הפונקציה IF. עם הפונקציה VLOOKUP, תחילה עליך ליצור טבלת הפניה:

הנוסחה בתא D2 היא ‎=VLOOKUP(C2,C5:D17,2,TRUE)‎
  • ‎=VLOOKUP(C2,C5:D17,2,TRUE)‎

נוסחה זו מציינת שיש לחפש את הערך בתא C2 בטווח C5:C17. אם הערך נמצא, החזר את הערך התואם מאותה שורה בעמודה D.

הנוסחה בתא C9 היא ‎=VLOOKUP(B9,B2:C6,2,TRUE)‎
  • ‎=VLOOKUP(B9,B2:C6,2,TRUE)‎

באופן דומה, נוסחה זו מחפשת את הערך בתא B9 בטווח B2:B22. אם הערך נמצא, החזר את הערך התואם מאותה שורה בעמודה C.

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

הפונקציה VLOOKUP מתוארת ביתר פירוט כאן, אך היא בהחלט פשוטה הרבה יותר ממשפט IF מקונן ומורכב, בן 12 רמות! היא גם טומנת בחובה יתרונות נוספים, בולטים פחות:

  • טבלאות הפניה של VLOOKUP גלויות וקל לראות אותן.

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

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

הידעת?

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

  • ‎=IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))‎

ניתן להפוך אותה לפשוטה הרבה יותר עם פונקציית IFS יחידה:

  • ‎=IFS(D2>89,"A",D2>79,"B",D2>69,"C",D2>59,"D",TRUE,"F")‎

הפונקציה IFS נהדרת משום שאינך צריך לדאוג לכל משפטי ה- IF והסוגריים הללו.

הערה: תכונה זו זמינה רק אם יש לך מנוי Office 365. אם אתה מנוי של Office 365, ודא שברשותך הגירסה העדכנית ביותר של Office.

נסה את Office 365 או את הגירסה העדכנית ביותר של Excel

זקוק לעזרה נוספת?

ניתן לשאול תמיד מומחה ב- Excel Tech Community, לקבל תמיכה בקהילת Answers או להציע תכונה חדשה או שיפור ב- Excel User Voice.

נושאים קשורים

וידאו : פונקציות IF מתקדמות
הפונקציה IFS ‏(Office 365,‏ Excel 2016 ואילך)
הפונקציה COUNTIF תחשב ערכים בהתבסס על קריטריון יחיד
הפונקציה COUNTIFS תחשב ערכים בהתבסס על קריטריונים מרובים
הפונקציה SUMIF תסכם ערכים בהתבסס על קריטריון יחיד
הפונקציה SUMIFS תסכם ערכים בהתבסס על קריטריונים מרובים
הפונקציה AND
הפונקציה OR
הפונקציה VLOOKUP
מבט כולל על נוסחאות ב- Excel
כיצד להימנע מנוסחאות שגויות
שימוש בבדיקת שגיאות לזיהוי שגיאות בנוסחאות
פונקציות לוגיות
פונקציות של Excel (בסדר אלפביתי)
פונקציות של Excel (לפי קטגוריה)

הרחב את הכישורים שלך
סייר בהדרכה
קבל תכונות חדשות לפני כולם
הצטרף למשתתפי Office Insider

האם מידע זה היה שימושי?

תודה על המשוב!

תודה על המשוב! נראה שכדאי לקשר אותך לאחד מנציגי התמיכה של Office.

×