פונקציית IF באקסל. דוגמאות (עם מספר תנאים)

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

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

פונקציית IF באקסל (מידע כללי)

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

1. בדוק את כל העמודה A עבור מספרים זוגיים.
2. אם נמצא מספר זוגי, הוסף ערכים כאלה ואחרים.
3. אם מספר זוגי לא נמצא, הצג את הכיתוב "לא נמצא".
4. בדוק אם המספר המתקבל זוגי. 
5. אם כן, הוסף אותו לכל המספרים הזוגיים שנבחרו בפסקה 1.

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

תחביר של הפונקציה IF עם תנאי אחד

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

=IF (logical_expression, value_if_true, value_if_false)

בואו נסתכל על התחביר ביתר פירוט:

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

הנה דוגמה לבהירות.

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

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

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

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

תחביר פונקציית IF עם מספר תנאים

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

כדי להבהיר את זה, הנה התחביר.

=IF(logical_expression, value_if_true, IF(logical_expression, value_if_true, value_if_false))

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

הנה דוגמה.

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

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

כיצד להרחיב את פונקציונליות ה-IF באמצעות אופרטורים AND ו-OR

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

פונקציית IF עם תנאי AND

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

פונקציית IF עם מצב "OR".

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

הנה כמה דרכים להשתמש בפונקציות И и OR כארגומנט פונקציה IF.

השוואת נתונים בשתי טבלאות

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

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

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

השולחן שלנו נראה כך.

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

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

בצורה של נוסחה לעיצוב, אנו כותבים את הפונקציה =COUNTIF (טווח להשוואה; התא הראשון של הטבלה הראשונה)=0. הטבלה עם התכונות של מעבד המזון השני משמשת כטווח השוואה.

עליך לוודא שהכתובות הן מוחלטות (עם סימן דולר לפני שמות השורה והעמודות). הוסף =0 אחרי הנוסחה כדי ש-Excel יחפש ערכים מדויקים.

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

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

פונקציית SUMIF באקסל

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

1. הוסף שורה עם סך ההכנסה של כל המוכרים ולחץ על התא שיכיל את התוצאה לאחר הזנת הנוסחה. 
2. אנו מוצאים את כפתור fx, שנמצא ליד השורה עבור נוסחאות. לאחר מכן, יופיע חלון שבו תוכל למצוא את הפונקציה הדרושה דרך החיפוש. לאחר בחירת המפעיל, עליך ללחוץ על כפתור "אישור". אבל קלט ידני תמיד אפשרי.
   

   

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

   

4. הטיעון הראשון הוא טווח. כאן אתה מזין את התאים שאתה רוצה לבדוק אם הם עומדים בקריטריונים. אם מדברים עלינו, אלו תפקידים של עובדים. הזן את הטווח D4:D18. או פשוט בחר את התאים המעניינים.
5. בשדה "קריטריונים", הזן את המיקום. במקרה שלנו - "מוכר". כטווח הסיכום, אנו מציינים את התאים שבהם רשומים המשכורות של העובדים (זה נעשה גם ידנית וגם בוחרים אותם עם העכבר). לחץ על "אישור", ונקבל את השכר המחושב המוגמר של כל העובדים המוכרים.

מסכים שזה מאוד נוח. לא כך?

פונקציית SUMIFS באקסל

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

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

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

הבה נסתכל על הטיעונים העיקריים ביתר פירוט:

1. טווח סיכום. תאים לסיכום.
2. טווח מצב 1 – הטווח שייבדק על עמידה בקריטריון מסוים. 
3. תנאי 1 הוא התנאי עצמו.
4. טווח קריטריונים 2 הוא הטווח השני שייבדק מול הקריטריון.
5. תנאי 2 הוא התנאי השני.

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

פונקציית COUNTIF באקסל

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

1. ראשית, הוסף שורה המכילה את מספר המוכרים. לאחר מכן, עליך ללחוץ על התא שבו תוצג התוצאה.
2. לאחר מכן, עליך ללחוץ על כפתור "הוסף פונקציה", אשר ניתן למצוא בכרטיסייה "נוסחאות". יופיע חלון עם רשימה של קטגוריות. עלינו לבחור את הפריט "רשימה אלפביתית מלאה". ברשימה, אנו מעוניינים בנוסחה COUNTIF. לאחר שנבחר בו, עלינו ללחוץ על כפתור "אישור".
   

   

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

פונקציית COUNTSLIM באקסל

דומה לנוסחה SUMMESLIMN, נוסחה זו סופרת את מספר התאים התואמים למספר תנאים. התחביר דומה אך מעט שונה מהנוסחה SUMMESLIMN:

1. טווח מצב 1. זהו הטווח שייבחן מול הקריטריון הראשון.
2. תנאי 1. ישירות הקריטריון הראשון.
3. טווח מצב 2. זהו הטווח שייבחן מול הקריטריון השני. 
4. מצב 2.
5. תנאי טווח 3.

וכן הלאה.

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

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

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

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