אקסל: פונקציית IF
נשתמש בפנוקצית IF כאשר נרצה לקבוע תנאי, שאם הוא קיים נרצה שהתא יכיל מידע מסוים ואם לא – מידע אחר.
הנוסחה IF מאפשרת לשאול כל שאלה בנוגע פיסת מידע ומחזירה "אמת" או "שקר" תוצאה.
- שאל אם A1 = 0, או אולי אנו מעוניינים אם הערך מתחיל עם "H" וכו '
- אם הנוסחאות ניתן מקונן עד 7 לולאות פנימיות ב-Excel 2003 ו 64 ב-Excel 2007. למעשה, יותר מ 3-5 קינון עשוי להיראות מורכבמדי כדי לנהל ולתחזק.
- אם הנוסחה מחזירה 1 או 0 (אמת או שקר - פונקציה בוליאנית) והוא יכול לשמש כפרמטר נוסחאות אחרות
תחביר:
= IF (הבדיקה ערך לוגי, אם זה נכון, אם הערך FALSE)
= IF (AND (X1 = 9, Y2> 100), "גרין", "אדום")
= IF ((X1 = 9) * (Y2> 100), "גרין", "אדום")
"*" היא דרך אחרת לומר, "+" משמש או
- ו = כל התנאים שיש לקיים כדי לקבל את "הערך האמיתי IF"
- OR = זה טוב במצב מספיק אחד הוא מילא לקבל את "הערך האמיתי IF"
במבנה נתונים, IF מאפשר לבנות עמודות דגל המבדילות בין התנאים הנדרשים.
אקסל: פונקציית SUMIF
בפונקציה SUMIF נשתמש כאשר נרצה לסכום טווח מסוים לפי קריטריון ספציפי.
תחביר:
= SUMIF (, מגוון קריטריונים, סכום טווח)
= COUNTIF (טווח, קריטריונים)
דוגמאות:
כדי לסכם את העמודה "המחיר" עבור כל השורות עם סדרה = "S7", אנא השתמש הבאות:
= SUMIF (B1: B11, "S7", I1: I11)
והתוצאה היא 130 10 19 = 159
כדי לספור את מספר השורות "דני" מופיע בטור "מהנדס", ניתן להשתמש ב-:
= COUNTIF (E1: E11, "דני")
בשתי הפונקציות, ניתן להשתמש בהפניה ישירה לתא שבו הקריטריונים נמצאים. לדוגמה, במקום "S7" ניתן לכתוב B7, B10, או B11.
אקסל: פונקציית VLOOKUP
נשתמש בפונקציה VLOOKUP כאשר נרצה להצליב מידע – לחפש ערך מסוים בשורה מסוימת ולקבל ערך שנמצא באותה שורה אך בעמודה אחרת.
תחביר:
= VLOOKUP (בדיקת ערך, מערך הטבלה, NUM מדד col, טווח בדיקה)
ערך בדיקה, חייב להיות בעמודה הראשונה (משמאל) של טווח הטבלה
דוגמאות:
כדי למצוא את "מספר חלק" בו משתמשים במכונה "S7", ניתן לכתוב:
= VLOOKUP ("S7", B1: F11, 5, 0)
התוצאה היא Y557
הנוסחה מחפשת את הערך "S7" בעמודה B, ומחזירה את הערך באותה שורה אבל בעמודה 5 מימין בעמודה B (כולל), כלומר העמודה F
הערה: במקום לכתוב "S7" בנוסחה, ניתן לכוון אותה ישירות לתא שבו "S7" ממוקם (B7) כדלקמן:
= VLOOKUP ($ B7, B1: F11, 5, 0)
כמו כן, התוצאה היא Y557 ולא Y558 או X126 וזה כי הנוסחה מחזירה את הערך עבור הפריט המתאים הראשון.
אקסל: פונקציית HLOOKUP
אותו רעיון כמו VLOOKUP, אבל הפונקציה HLOOKUP מספקת מבט אופקי
הפונקציה מחפשת ערך בשורה העליונה של טבלה או מערך של ערכים ולאחר מכן מחזיר את הערך באותה עמודה.
תחביר:
= HLOOKUP (בדיקת ערך, מערך הטבלה, מדד NUM שורה, טווח בדיקה)
ערך בדיקה, חייב להיות בשורה הראשונה של טווח הטבלה
דוגמאות:
כדי למצוא את"מספר חלק" ולהחזיר את הערך בשורה 5 ניתן להשתמש ב:
=HLOOKUP("מספר חלק", A1: F11, 5, 0)
התוצאה היא X124
אקסל: פונקציית SUMPRODUCT
1. השימוש הבסיסי הוא להכפיל ערכים בין שתי עמודות או יותר
2. יכולת מתקדמת היא לסכום או לספור בהתאם לתנאים מרובים. שימוש זה יכול להוות חלופה עבור טבלת ציר (Pivot table).
תחביר:
= SUMPRODUCT (array1, array2, array3, ...)
אפשרות 1: אם ברצונך לקבל מספר רב של "קניות" ב "עלות"
= SUMPRODUCT (H2: H11, I2: I11)
התוצאה תהיה:. 1 * 7 +8 * 25 + 1 * 130 + ... 1 * 19 = 1522
אפשרות 2: אם ברצונך לקבל סכום "קניות" של כל סדרת "S7" כאשר "דני" הוא מהנדס
=SUMPRODUCT ((E2: E11 = "דני") * (B2: B11 = "S7"), (H2: H11))
התוצאה תהיה: 2 +1 = 3
אפשרות 3: אם אתה רוצה לסכם את "כמות" מוכפל "עלות", ולאחר מכן להשתמש הבאות:
=SUMPRODUCT ((E2: E11 = "דני") * (B2: B11 = "S7"), (I2: I11) * (H2: H11))
התוצאה תהיה: 2 * 130 +1 * 19 = 279
אפשרות 4: אם אתה רוצה לספור את השורות שם = הסדרה "S7" מהנדס = "דני"
=SUMPRODUCT ((E2: E11 = "דני") * (B2: B11 = "S7"))
התוצאה תהיה: 2
באפשרות 4, הנוסחה מבצעת ספירה ולא סכום, מכיוון שכל המרכיבים של הנוסחא הינם תנאים, כלומר כמה פעצים התנאי מתקיימים.
אקסל: פונקציית ISERROR
הפונקציה ISERROR חוקרת אם הערך של תא (= פרמטר) מכיל שגיאה ומחזירה אמת / שקר בהתאם
פונקציה זו שימושית בעת השימוש VLOOKUP נוסחאות
הפונקציה ISERROR שייכת למשפחת של פונקציות "האם" (כמו ISNUMBER, ISBLANK וכו ')
דוגמהל-ISERROR בשימוש עם IF ו-VLOOKUP:
מהי הסדרה של מכונת "A7"?
= IF (ISERROR (VLOOKUP ("A7", A1: B11, 2,0)), "לא נמצא", VLOOKUP ("A7", A1: B11, 2,0))
התוצאה היא S1
ראשית VLOOKUP מחושב - ערך A7 נמצא בעמודה A, ולכן הפונקציה VLOOKUP לא החזירה שגיאה, כלומר הפונקציה ISERROR החזירה אתהערך FALSE (= אין שגיאה) ולכן אם הנוסחה החזירה שחיפשנו
אילו VLOOKUP חיפשה A77 במקום A7, הפונקציה ISERROR תחזיר TRUE, כלומר לא "הצליח" למצוא את הערך ולכן יש "שגיאה".