כיצד קנים מרובים אם פונקציות ב - Excel

01 of 06

כיצד מקוננות פונקציות IF

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

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

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

קינון אם פונקציות של מורה

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

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

= IF (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7))

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

1. החלק הראשון, D7, בודק אם שכר העובד הוא פחות מ 30,000 $
2. אם כן, החלק האמצעי, $ D $ 3 * D7 , מכפיל את השכר בשיעור הניכוי של 6%
3. אם לא, הפונקציה IF השנייה: אם (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7) בוחנת שני תנאים נוספים:
   • D7> = 50000 , בודק אם שכר של עובד גדול או שווה ל 50,000 $
   • אם זה, $ D $ 5 * D7 מכפיל את השכר בשיעור הניכוי של 10%
   • אם לא, $ D $ 4 * D7 מכפיל את השכר בשיעור הניכוי של 8%

הזנת נתוני המדריך

הכנס את הנתונים לתאים C1 עד E6 בגליון עבודה של Excel, כפי שמוצג בתמונה למעלה.

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

עבור אלה שאינם מרגישים כמו הקלדה, הנתונים וההוראות להעתקתו ל- Excel זמינים בקישור זה.

הערה: ההוראות להעתקת הנתונים אינן כוללות שלבי עיצוב בגליון העבודה.

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

02 מתוך 06

הפעלת הפונקציה IF מקוננת

למרות שניתן פשוט להזין את הנוסחה המלאה

= IF (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7))

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

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

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

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

שלבי הדרכה

1. לחץ על התא E7 כדי להפוך אותו לתא פעיל. - המיקום עבור נוסחת IF מקוננות.
2. לחץ על הכרטיסייה נוסחאות של הסרט
3. לחץ על הסמל Logical פתח את הרשימה הנפתחת של הפונקציה.
4. לחץ על IF ברשימה כדי להציג את תיבת הדו-שיח של הפונקציה.

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

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

אפשרות קיצור דרך של מורה

כדי להמשיך בדוגמה זו, תוכל

• הזן את הטיעונים לתוך תיבת הדו-שיח כפי שמוצג בתמונה לעיל ולאחר מכן לקפוץ לשלב הסופי המכסה העתקת הנוסחה לשורות 7 עד 10;
• או בצע את חמשת הדפים הבאים המספקים הוראות מפורטות והסברים להזנת שלושת הטיעונים.

03 מתוך 06

הזנת ארגומנט Logical_test

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

כדי להשוות בין שני ערכים, Logical_test משתמש במפעיל השוואה בין הערכים.

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

• פחות מ -30,000 דולר
• בין $ 30 ל $ 49,999 $
• 50,000 $ או יותר

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

ההשוואה הראשונה תהיה בין השכר השנתי של העובד, הממוקם בתא D, עם משכורת סף של 30,000 $.

מאחר שהמטרה היא לקבוע אם D7 הוא פחות מ 30,000 $, מפעיל פחות מ "<" משמש בין הערכים.

שלבי הדרכה

1. לחץ על קו Logical_test בתיבת הדו-שיח
2. לחץ על תא D7 כדי להוסיף הפניה זו התא לקו Logical_test
3. לחץ על המקש פחות מ "<" במקלדת
4. הקלד 30000 אחרי פחות מסמל
5. הבדיקה הלוגית הושלמה צריך לקרוא: D7 <30000

הערה: אל תזין את סימן הדולר ($) או מפריד פסיק (,) עם 30000.

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

04 מתוך 06

הזנת ארגומנט Value_if_true

הארגומנט Value_if_true מציין את הפונקציה IF מה לעשות כאשר Logical_test נכון.

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

בדוגמה זו, כאשר הנתונים בתא D7 הוא פחות מ 30,000 $. Excel מכפיל את השכר השנתי של העובד בתא D7 בשיעור הניכוי של 6% הממוקם בתא D3.

היחס בין היחידות היחסיות לעומת התאים המוחלטים

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

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

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

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

הוספת סימני הדולר מתבצעת בקלות על ידי לחיצה על המקש F4 במקלדת לאחר הזנת התא לתוך תיבת הדו - שיח.

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

שלבי הדרכה

1. לחץ על השורה Value_if_true בתיבת הדו-שיח
2. לחץ על תא D3 בגליון העבודה כדי להוסיף הפניה זו התא לקו Value_if_true
3. לחץ על המקש F4 במקלדת כדי להפוך את D3 לתא מוחלט של התא ($ D $ 3)
4. לחץ על מקש הכוכבית ( * ) במקלדת - הכוכבית היא סמל הכפל ב- Excel
5. לחץ על תא D7 כדי להוסיף הפניה זו התא לקו Value_if_true
6. השורה Value_if_true הושלמה: $ D $ 3 * D7

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

05 מתוך 06

הזנת פונקציית IF מקוננת כארגומנט Value_if_false

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

בדרך זו, התוצאות הבאות מתרחשות:

• הארגומנט Logical_test בתפקוד ה- IF המקונן ( D7> = 50000 ) בודק את כל המשכורות שאינן פחות מ -30,000 דולר.
• עבור משכורות אלה גדול או שווה ל 50,000 $ את הטענה Value_if_true להכפיל אותם בשיעור הניכוי של 10% ממוקם בתא D5.
• עבור משכורות הנותרים - אלה כי הם יותר מ 30,000 $ אבל פחות מ 50,000 $ - הטיעון Value_if_false מכפיל אותם בשיעור הניכוי של 8% הממוקם בתא D4.

שלבי הדרכה

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

הערה: פונקציות מקוננות אינן מתחילות עם סימן שווה - אלא עם שם הפונקציה.

1. לחץ על השורה Value_if_false בתיבת הדו-שיח
2. הזן את הפונקציה IF הבאה
   אם (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7)
3. לחץ על אישור כדי להשלים את הפונקציה IF ולסגור את תיבת הדו-שיח
4. הערך של 3,678.96 $ צריך להופיע בתא E7 *
5. כאשר אתה לוחץ על התא E7, את הפונקציה המלאה
   = IF (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7))
   מופיע בסרגל הנוסחה מעל גליון העבודה

* מאז ר הולט מרוויח יותר מ 30,000 $ אבל פחות מ 50,000 $ בשנה, הנוסחה $ 45,987 * 8% משמש לחישוב הניכוי השנתי שלו.

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

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

06 מתוך 06

העתקת פונקציות IF מקוננות באמצעות ידית מילוי

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

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

אחת הדרכים פשוטות להעתיק נוסחאות ב- Excel היא עם ידית מילוי.

שלבי הדרכה

1. לחץ על התא E7 כדי להפוך אותו לתא פעיל .
2. מניחים את מצביע העכבר מעל הריבוע השחור בפינה הימנית התחתונה של התא הפעיל. המצביע ישתנה לסימן פלוס "+".
3. לחץ על לחצן העכבר השמאלי וגרור את ידית המילוי עד לתא E11.
4. שחרר את לחצן העכבר. תאים E8 עד E11 יהיו מלאים בתוצאות הנוסחה כפי שמוצג בתמונה לעיל.