כיצד להשתמש בהצהרות IF מרובות ב-Microsoft Excel

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

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

• = (שווה ל)

• (גדול מ)

• = (גדול או שווה ל)

• < (קטן מ)
• <= (קטן או שווה ל)
• <> (לא שווה ל)

הבנת התחביר של הצהרת ה-IF של Excel היא צעד מכריע לקראת לימוד השימוש בו כראוי. הפורמט של הפונקציה הוא IF(logical_test, value_if_true, [value_if_false]) כאשר הסוגריים המרובעים מציינים שהארגומנט האחרון הזה הוא אופציונלי. אלה קלים לפירוש: ה מבחן הגיון פירושו ההצהרה שאתה רוצה לבדוק, value_if_true הוא המקום למה שאתה רוצה ש-Excel יראה אם ​​הבדיקה נכונה, וכן [value_if_false] הוא המקום האופציונלי עבור מה שאתה רוצה ש-Excel יציג אם הבדיקה היא שקר.

זה מספיק בשבילך כדי להשתמש בנוסחת האקסל להצהרות אם-אז, שניתן להדגים באמצעות הדוגמה "מעל תקציב/בתוך תקציב" מקודם. תאר לעצמך שיש לך את הסכום שהוצא על ידי מחלקות שונות בעמודה B ואת הסכום המתוקצב בעמודה C, כאשר הערכים מתחילים בשורה השנייה. בתא D2, תקליד =IF(B2>C2, "מעל תקציב", "בתוך תקציב") כדי להחזיר את התוצאה שאתה מחפש (שים לב שאתה צריך להשתמש במרכאות כדי לגרום לה להחזיר את הטקסט בפנים). החלק הראשון אומר "אם הערך ב-B2 גדול מהערך ב-C2" (אם ההוצאה גדולה מהתקציב), החלק השני אומר "אז תחזיר את הטקסט מעל התקציב והחלק השלישי אומר "אם לא, אז תחזור בגבולות התקציב."

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

כדי להשתמש במשפטי IF מרובים בצורה נכונה, חשבו על ההיגיון של מה שאתם מבקשים מ-Excel לעשות. בעיקרון, לאחר התנאי שלך (הארגומנט הראשון בפונקציה), אתה יכול להוסיף עוד משפט IF לתוך value_if_true אוֹ value_if_false רווחים, כדי לומר לאקסל מה לעשות הלאה. תחשוב על זה כמו לגרום ל-Excel לבדוק את התא מול קריטריון שני אם התא המדובר לא עומדים בקריטריון הראשון (או אכן, אם הוא אכן עומד בקריטריון הראשון), ותוכלו להמשיך בתהליך זה לאורך זמן זְמַן. במילים אחרות, אתה הופך את "אם זה, אז זה" ל"אם זה ו זה, אז זה" או "אם לא זה אלא זה, אז זה."

תארו לעצמכם שעשיתם לתלמידים מבחן שבו ציון של 85 אחוזים ומעלה הוא A, בין 70 ל-85 אחוזים הוא B, בין 55 ל-70 אחוז הוא C, בין 40 אחוז ל-55 אחוז הוא D וכל דבר נמוך יותר הוא לְהִכָּשֵׁל. הציונים נמצאים בעמודה B, משורה 2 עד שורה 11. אתה יכול להשתמש בפונקציית Excel IF שנבנתה בקפידה עם מספר תנאים כדי לסווג את ציוני התלמידים.

החלק הראשון קל (עבור ציון ב-B2, בדוגמה זו): =IF(B2>=85, "A") אומר לאקסל להחזיר A אם התלמיד קיבל ציון 85 ומעלה. קינון מאפשר לך להוסיף ציונים אחרים, תוך שימוש בארגומנט השלישי (מה לעשות אם התנאי שקרי) כדי להוסיף תנאים נוספים: =IF(B2>=85, "A", IF(B2>=70, "B")), שאומר לאקסל להחזיר A עבור ציונים של 85 ומעלה, אך אם לא, החזר B אם הציון הוא מעל 70. המשך בדרך זו להשלמת הנוסחה, תוך שימוש בארגומנט השלישי להצהרת IF חדשה בכל פעם: =IF(B2>=85, "A", IF(B2>=70, "B",IF(B2>=55, "C", IF(B2>=40, "D", "Fail")) ))

זה נראה מסובך, אז פירוק זה אמור לחזק את הרעיון. שני הטיעונים הראשונים אומרים "אם הציון ב-B2 הוא 85 או יותר, אז התלמיד מקבל A", והשלישי אומר, "אחרת, בדוק את התנאי הבא". ה IF second מוביל להצהרה זו, "(עבור תלמידים שציונים פחות מ-85) אם הציון ב-B2 הוא 70 ומעלה, אז התלמיד מקבל B, ואם לא סמן את התנאי הבא." ה-IF השלישי אומר, "(עבור תלמידים שציונים פחות מ-70) אם הציון ב-B2 הוא 55 ומעלה, התלמיד שקיבל ציון מקבל ג', ואם לא, בדוק התנאי הבא" והרביעי אומר "(לתלמידים בעלי ציון נמוך מ-55) אם הציון ב-B2 גבוה מ-40, התלמיד מקבל D, אם לא, התלמיד נכשל."

בתנאי שתזין את הנוסחה של הצהרת ה-IF שלך כראוי, היא תחזיר את אחת האפשרויות שנתת לאקסל. עם זאת, אם אתה עושה טעות, הנוסחה עשויה להחזיר א 0 אוֹ #שֵׁם? בתא המכיל את הנוסחה שלך.

א 0 שגיאה פירושה שאו value_if_true אוֹ value_if_false הארגומנט ריק. תקן בעיה זו על ידי הקפדה על הזנת ערך בשני הארגומנטים, או לפחות, ב- value_if_true טַעֲנָה. ה value_if_false ניתן להסיר לחלוטין את הארגומנט, והוא יחזיר FALSE אם לא תכניס פסיק שני אחרי ה- value_if_true שדה.

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

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

אי הצבת התנאים בסדר היא טעות נפוצה. בדוגמה של המבחנים המדורגים, אם התחלת עם הציון הנמוך יותר =IF(B2>=40, "D", …" והמשיך לרשום את האחרים בסדר עולה, אפילו ציון של 100 יחזיר D. הסיבה לכך היא שהתנאי הראשון נבדק תחילה, אז אם התחלת עם "אם תא B2 הוא 40 ומעלה, החזר א D" זה יסווג כל ציון של 40 ומעלה כ-D בלי לסמן שום דבר אחר כי הוא עומד בציון הראשון מַצָב. זו דוגמה מושלמת למה אתה צריך לעקוב אחר ההיגיון של הנוסחה שלך עד הסוף.

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

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

האלטרנטיבה הנוחה ביותר לפונקציית IF היא פונקציית IFS, שהיא גרסה של פונקציה שמבצעת את אותה משימה כמו קינון בפונקציה בודדת ויכולה להתמודד עם עד 127 תנאים. לפונקציית IFS יש תחביר פשוט יותר: =IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3]...) וכולי. בעיקרו של דבר, זה עובד כמו הצהרות IF מקוננות, למעט הצהרות IF עוקבות תופסות את הרווח שאחרי ה-"value if true" בלי צורך להעלות את הפונקציה שוב.

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

=IF(B2>=85, "A", IF(B2>=70, "B",IF(B2>=55, "C", IF(B2>=40, "D", "Fail")) ))

פשוט לכתוב

=IFS(B2>=85, "A", B2>=70, "B", B2>=55, "C", B2>=40, "D", TRUE, "Fail")

ה נָכוֹן בנקודה שלפני האחרון אומר "אם אף אחד מהערכים האחרים לא מתקיים, אז החזר את הערך הבא."

תכונה זו זמינה רק ב-Excel 2019 או ב-Excel עבור Office 365.

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

התחביר עבור VLOOKUP הוא =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) ולמידת כל אחד מהרכיבים מראה לך כיצד להשתמש בו. ה lookup_value ארגומנט הוא הערך שברצונך לבדוק, שצריך להיות תא באותה עמודה כמו תחילת הטבלה שברצונך לבדוק אותה מולה, מעליה או מתחתיה. ה table_array הוא טווח התאים המכילים את טבלת המידע שלך, כגון מוצרים והמחירים המתאימים להם, ו col_index_num היא העמודה שאתה רוצה ש-Excel יסתכל בה כדי למצוא את הערך להחזיר, איתה 1 מציין את העמודה השמאלית ביותר, 2 מציין את הבא וכן הלאה. סוף כל סוף, [טווח_חיפוש] הוא אופציונלי, אך הזן "TRUE" אם אתה רוצה ש-Excel יבסס את התשובה על הערך הקרוב ביותר או "FALSE" אם אתה רוצה רק התאמה מדויקת.