נוסחאות אקסל: כל פונקציה עם גיליון חי
נוסחאות ופונקציות של אקסל מוסברות על גיליונות שאפשר לערוך: VLOOKUP, XLOOKUP, IF, SUMIF, COUNTIF, תאריכים, טקסט ומערכים דינמיים. שנו מספר או נוסחה והגיליון יחושב מחדש בדפדפן.
להתחיל את הקורסיסודות הנוסחאות
- SUMהקלידו =SUM(B2:B6) מתחת לעמודת מספרים כדי לחבר אותם, או לחצו Alt+= ו-AutoSum יכתוב את הנוסחה בשבילכם. סכמו שורות, תאים נפרדים וגיליונות אחרים בגיליונות חיים שאפשר לערוך.
- חיסורבאקסל אין פונקציית SUBTRACT: הקלידו =B2-C2 כדי לחסר תא אחד מתא אחר. חסרו עמודה שלמה, כמה תאים בבת אחת, אחוז או תאריך, בגיליונות חיים שאפשר לערוך.
- כפל וחילוקכופלים באקסל עם כוכבית, =B2*C2, ומחלקים עם לוכסן, =B2/C2. הכפילו עמודה במספר אחד, השתמשו ב-PRODUCT ועצרו שגיאות #DIV/0!, בגיליונות חיים שאפשר לערוך.
- AVERAGE=AVERAGE(B2:B7) מחברת את המספרים ב-B2:B7 ומחלקת בכמות שלהם. למדו איך תאים ריקים ואפסים משנים את התוצאה, איך מתעלמים מאפסים ואיך מחשבים ממוצע של 3 הגבוהים.
- COUNT ו-COUNTA=COUNT(B2:B8) סופרת את התאים שמכילים מספרים, =COUNTA(B2:B8) סופרת כל תא שאינו ריק, ו-=COUNTBLANK(B2:B8) סופרת את הריקים. ראו את שלושתן בגיליון שאפשר לערוך.
- הפניה מוחלטתהפניה מוחלטת כמו $E$1 נשארת אותו דבר כשמעתיקים נוסחה, ואילו הפניה יחסית כמו E1 זזה יחד איתה. לחצו F4 כדי להוסיף את סימני הדולר. ראו את ההבדל בגיליונות שאפשר לערוך.
- אחוזיםנוסחת האחוזים באקסל היא =חלק/שלם, למשל =B2/C2, כשהתא מעוצב כאחוז. אחוז מתוך סכום כולל, אחוז ממספר, הוספה או הורדה של אחוז, בגיליונות חיים.
- שינוי באחוזיםנוסחת השינוי באחוזים באקסל היא =(חדש-ישן)/ישן, למשל =(C2-B2)/B2, בעיצוב אחוזים. תוצאה שלילית היא ירידה. גיליונות חיים מכסים שינוי מחודש לחודש, התחלה מאפס ונקודות אחוז.
לוגיקה
- IF=IF(B2>=50,"Pass","Fail") בודקת אם B2 הוא 50 או יותר ומחזירה Pass אם כן ו-Fail אם לא. למדו את התחביר של IF, את IF עם טקסט, IF עם חישוב, IF לתא ריק, ואת הטעויות שגורמות ל-IF להחזיר תוצאה שגויה.
- IF מקונן=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","F"))) שמה IF אחת בתוך אחרת כדי לבחור בין יותר משתי תוצאות. למדו איך קוראים IF מקונן, למה סדר התנאים חשוב, ומתי IFS או טבלת חיפוש הן הבחירה הטובה יותר.
- IFS=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"F") בודקת כל תנאי לפי הסדר ומחזירה את הערך שמשויך לראשון שהוא TRUE. למדו את התחביר של IFS, את ברירת המחדל עם TRUE, למה IFS מחזירה #N/A, ואיך היא משתווה ל-IF מקונן.
- AND, OR, NOT=AND(B2>=10,B2<=20) מחזירה TRUE רק כשכל התנאים מתקיימים, ו-=OR(B2="North",B2="South") מחזירה TRUE כשלפחות אחד מתקיים. למדו את AND, OR, NOT ו-XOR לבד ובתוך IF, איך בודקים אם מספר נמצא בין שני ערכים, ואיך כותבים AND ו-OR בנוסחאות מערך.
- IFERROR=IFERROR(B2/C2,0) מחזירה את B2/C2, או 0 כשהחילוק נותן שגיאה. למדו את IFERROR עם VLOOKUP, החזרת תא ריק במקום שגיאה, למה IFNA היא הבחירה הטובה יותר לחיפושים, ולמה הסתרה של כל שגיאה יכולה להסתיר טעויות אמיתיות.
- SWITCH=SWITCH(B2,"N","North","S","South","Unknown") משווה את B2 לכל ערך בתורו ומחזירה את התוצאה שמשויכת להתאמה המדויקת הראשונה, או Unknown כששום דבר לא מתאים. למדו את התחביר של SWITCH, את ערך ברירת המחדל, את התבנית SWITCH(TRUE,...) ומתי עדיף IFS או IF מקונן.
- ISBLANK, ISNUMBER=ISBLANK(B2) מחזירה TRUE כש-B2 ריק, ו-=ISNUMBER(B2) מחזירה TRUE כש-B2 מכיל מספר. למדו את ISBLANK, ISNUMBER, ISTEXT, ISERROR, ISNA, ISEVEN ו-ISODD, למה נוסחה שמחזירה "" אינה ריקה, ואיך ISNUMBER(SEARCH()) בודקת אם תא מכיל טקסט.
חיפוש
- VLOOKUP=VLOOKUP(F2,A2:D6,3,FALSE) מחפשת את F2 בעמודה הראשונה של A2:D6 ומחזירה את הערך מהעמודה השלישית באותה שורה. התאמה מדויקת ומשוערת, תיקון #N/A, גיליון אחר, שני קריטריונים.
- XLOOKUP=XLOOKUP(F2,A2:A6,C2:C6) מחפשת את F2 ב-A2:A6 ומחזירה את הערך באותה שורה של C2:C6. טקסט ל"לא נמצא", כמה עמודות בבת אחת, חיפוש שמאלה, ההתאמה האחרונה, התאמה משוערת ותווים כלליים.
- INDEX MATCH=INDEX(C2:C6,MATCH(F2,A2:A6,0)) מוצאת את השורה של F2 בעמודה A ומחזירה את הערך מאותה שורה בעמודה C. היא מחפשת שמאלה, עושה חיפושים דו־כיווניים ועובדת בכל גרסה של אקסל.
- INDEX=INDEX(A2:C6,3,2) מחזירה את הערך בשורה השלישית ובעמודה השנייה של A2:C6. השתמשו בה לפריט ה-n ברשימה, לשורה או לעמודה שלמה, ולערך שבמיקום ש-MATCH מצאה.
- MATCH=MATCH(E2,A2:A6,0) מחזירה את המיקום של E2 בתוך A2:A6: 4 אם הוא הפריט הרביעי. סוגי התאמה 0, 1 ומינוס 1, תווים כלליים, התאמה רגישה לאותיות ובדיקה אם ערך נמצא ברשימה.
- HLOOKUP=HLOOKUP("Mar",A1:E3,2,FALSE) מחפשת את Mar בשורה הראשונה של A1:E3 ומחזירה את הערך מהשורה השנייה באותה עמודה. התאמה מדויקת ומשוערת, ומתי XLOOKUP היא הבחירה הטובה יותר.
- XMATCH=XMATCH(E2,A2:A6) מחזירה את המיקום של E2 ב-A2:A6, עם התאמה מדויקת כברירת מחדל. היא יכולה גם למצוא את הערך הקטן הבא או הגדול הבא בלי מיון, לחפש מלמטה ולהשתמש בתווים כלליים.
- חיפוש עם כמה קריטריונים=XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7) מחזירה את הערך מהשורה שבה עמודה A מתאימה ל-E2 ועמודה B מתאימה ל-F2. גרסת INDEX MATCH, עמודת עזר ל-VLOOKUP, ו-FILTER לכל ההתאמות.
- VLOOKUP מול XLOOKUPXLOOKUP עושה כל מה ש-VLOOKUP עושה, עם התאמה מדויקת כברירת מחדל, בלי מספר עמודה, עם חיפוש שמאלה וארגומנט ל"לא נמצא". VLOOKUP היא עדיין הבחירה כשקובץ צריך להיפתח ב-Excel 2019 ומטה.
- INDIRECT=INDIRECT("C"&E2) קוראת את התא שהכתובת שלו בנויה כטקסט: עמודה C, השורה שב-E2. השתמשו בה כדי לבחור גיליון לפי שם מתוך תא, לבנות טווחים ממספרים וליצור רשימות נפתחות תלויות.
- OFFSET=OFFSET(A1,3,2) מחזירה את התא שנמצא 3 שורות למטה ו-2 עמודות הלאה מ-A1. עם גובה היא מחזירה טווח שלם, וכך מסכמים את N השורות האחרונות או בונים ממוצע מתגלגל.
- CHOOSE=CHOOSE(B2,"Low","Medium","High") מחזירה Low כש-B2 הוא 1, Medium כשהוא 2 ו-High כשהוא 3. מיפוי מספרים לשמות, בחירת טווח לסיכום, החלפת IF מקונן, ובחירת עמודות עם CHOOSECOLS.
ספירה וסכימה לפי תנאי
- COUNTIF=COUNTIF(B2:B7,"North") סופרת את התאים ב-B2:B7 שמכילים North. ספירה לפי טקסט, מספרים, תווים כלליים, תאים ריקים ותאריכים, ומציאת כפילויות, בגיליונות שאפשר לערוך.
- COUNTIFS=COUNTIFS(A2:A7,"North",C2:C7,">50") סופרת את השורות שבהן האזור הוא North והמכירות מעל 50. ספירה בין שני מספרים או שני תאריכים, עם לוגיקת OR ועם תאים ריקים, בגיליונות חיים.
- SUMIF=SUMIF(A2:A7,"North",C2:C7) מחברת את הערכים ב-C2:C7 בשורות שבהן עמודה A היא North. סכום אם גדול מ, אם הטקסט מכיל, לפי תאריך ומגיליון אחר, בגיליונות שאפשר לערוך.
- SUMIFS=SUMIFS(C2:C7,A2:A7,"North",B2:B7,"Apple") מחברת את המכירות ב-C2:C7 שבהן האזור הוא North והמוצר הוא Apple. טווחי תאריכים, לוגיקת OR ומסננים אופציונליים, בגיליונות חיים.
- AVERAGEIF=AVERAGEIF(A2:A7,"North",C2:C7) מחשבת את הממוצע של הערכים ב-C2:C7 בשורות שבהן עמודה A היא North. AVERAGEIFS לכמה תנאים, ממוצע שמתעלם מאפסים, תיקון #DIV/0!, ו-MAXIFS ו-MINIFS.
- ספירת תאים עם טקסט=COUNTIF(A2:A8,"*") סופרת את התאים ב-A2:A8 שמכילים טקסט, ומדלגת על מספרים, תאריכים ותאים ריקים. ספירת תאים שמכילים מילה מסוימת, והחזרת ערך אם תא מכיל טקסט.
- COUNTIF לא ריק=COUNTIF(B2:B8,"<>") סופרת את התאים ב-B2:B8 שאינם ריקים, בדיוק כמו COUNTA. הוספת תנאים נוספים עם COUNTIFS, וטיפול בתאים שרק נראים ריקים.
- ספירת ערכים ייחודיים=COUNTA(UNIQUE(A2:A9)) סופרת כמה ערכים שונים יש ב-A2:A9. בגרסאות ישנות של אקסל השתמשו ב-=SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9)). ספירת ערכים שמופיעים פעם אחת, ספירה עם תנאי, ודילוג על תאים ריקים.
- SUMPRODUCT=SUMPRODUCT(B2:B6,C2:C6) מכפילה כל כמות במחיר שלה ומחברת את התוצאות. עם תנאים כמו (A2:A7="North")*C2:C7 היא מסכמת וסופרת איפה ש-SUMIFS לא יכולה: לפי חודש, עמודה מול עמודה, עם OR.
- SUBTOTAL=SUBTOTAL(9,C2:C8) מחברת את C2:C8 כמו SUM, אבל מתעלמת משורות SUBTOTAL אחרות בטווח ומשורות שמוסתרות על ידי מסנן. מספרי הפונקציה 9 ו-109, ספירת שורות גלויות, ו-AGGREGATE לשגיאות.
- ממוצע משוקלל=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5) הוא ממוצע משוקלל: כל ערך מוכפל במשקל שלו, המכפלות מתחברות, והסכום מחולק בסכום המשקלים. ציונים, ממוצע לפי נקודות זכות ומחירים לפי כמות.
טקסט
- שרשור`=A2&" "&B2` מחברת את הטקסט שב-A2 ושב-B2 עם רווח ביניהם. CONCATENATE ו-CONCAT עושות את אותה עבודה; TEXT שומרת על מספרים ותאריכים קריאים כשמחברים אותם.
- TEXTJOIN`=TEXTJOIN(", ",TRUE,A2:A6)` מחברת את כל התאים של A2:A6 לטקסט אחד, עם פסיק ורווח בין הפריטים ובלי התאים הריקים. הוסיפו FILTER כדי לחבר רק את השורות שעומדות בתנאי.
- פיצול טקסט`=TEXTBEFORE(A2," ")` מחזירה את השם הפרטי מתוך `Ana Silva` ו-`=TEXTAFTER(A2," ")` את שם המשפחה. TEXTSPLIT מפצלת תא לכמה עמודות בבת אחת; LEFT, MID ו-FIND עושות את אותו הדבר בגרסאות ישנות.
- LEFT, RIGHT, MID`=LEFT(A2,3)` מחזירה את 3 התווים הראשונים של A2, `=RIGHT(A2,2)` את 2 האחרונים, ו-`=MID(A2,5,4)` ארבעה תווים החל מהתו החמישי. שלבו אותן עם FIND ו-LEN כשהאורך משתנה.
- FIND ו-SEARCH`=SEARCH("apple",A2)` מחזירה את המיקום שבו `apple` מתחיל ב-A2, בלי תלות בגודל האותיות. FIND עושה את אותו הדבר אבל רגישה לגודל האותיות. שתיהן מחזירות #VALUE! כשהטקסט חסר, ו-ISNUMBER הופכת את זה לבדיקת "אם התא מכיל".
- SUBSTITUTE, REPLACE`=SUBSTITUTE(A2,"-","")` מסירה כל מקף מ-A2: SUBSTITUTE מחליפה טקסט לפי התוכן שלו. REPLACE מחליפה לפי מיקום: `=REPLACE(A2,1,3,"XYZ")` כותבת על 3 התווים הראשונים.
- TRIM`=TRIM(A2)` מסירה את הרווחים שלפני הטקסט ב-A2 ואחריו, והופכת רצפים של רווחים בין מילים לרווח אחד. SUBSTITUTE מסירה כל רווח, או את הרווחים הקשיחים ש-TRIM מפספסת.
- UPPER, LOWER, PROPER`=UPPER(A2)` הופכת את כל האותיות ב-A2 לגדולות, `=LOWER(A2)` הופכת את כולן לקטנות, ו-`=PROPER(A2)` מגדילה את האות הראשונה של כל מילה. בשביל האות הראשונה של הטקסט בלבד, שלבו את UPPER, LEFT ו-MID.
- LEN`=LEN(A2)` מחזירה את מספר התווים ב-A2, כולל רווחים וסימני פיסוק. עם TRIM ו-SUBSTITUTE היא סופרת גם מילים, ועם SUM היא סופרת את התווים בטווח שלם.
- TEXT`=TEXT(A2,"mmm d, yyyy")` הופכת את התאריך שב-A2 לטקסט כמו `Mar 15, 2026`, ו-`=TEXT(B2,"$#,##0.00")` הופכת את 1250.5 ל-`$1,250.50`. התוצאה היא טקסט, אז השתמשו בה לתוויות ולא לחישובים נוספים.
- טקסט למספר`=VALUE(A2)` הופכת מספר שמאוחסן כטקסט, כמו `'120`, למספר 120. שני סימני מינוס, `=--A2`, עושים את אותו הדבר, NUMBERVALUE מטפלת בפסיק כמפריד עשרוני, והמר למספר מתקן את התאים במקום.
- ירידת שורה בתאהקישו Alt+Enter תוך כדי הקלדה בתא כדי להתחיל בו שורה חדשה (Control+Option+Return ב-Mac). בנוסחה, `CHAR(10)` היא ירידת השורה: `=A2&CHAR(10)&B2` שמה את B2 בשורה שנייה, שמוצגת ברגע שגלישת טקסט מופעלת.
- אפסים מוביליםאקסל מוחק אפסים מובילים כי `00742` הוא המספר 742. שמרו אותם עם תבנית מספר מותאמת אישית כמו `00000`, עם גרש (`'00742`) או עם תבנית טקסט, או הוסיפו אותם עם `=TEXT(A2,"00000")`.
- תווים כללייםבקריטריונים של אקסל, `*` מייצג כל מספר של תווים ו-`?` תו אחד בדיוק: `=COUNTIF(A2:A7,"*apple*")` סופרת את התאים שמכילים `apple`. `~` הופך תו כללי בחזרה לתו רגיל.
תאריכים ושעות
- חישוב גיל=DATEDIF(B2,TODAY(),"Y") מחזירה את הגיל בשנים שלמות של מי שנולד בתאריך שב-B2. חישוב גיל בתאריך מסוים, בשנים, חודשים וימים, ובלי DATEDIF.
- DATEDIF=DATEDIF(A2,B2,"M") סופרת את החודשים המלאים בין תאריך ההתחלה ב-A2 לתאריך הסיום ב-B2. היחידות Y, M, D, YM, MD ו-YD, למה DATEDIF חסרה ברשימת הפונקציות, ושגיאת #NUM!.
- ימים בין תאריכים=B2-A2 מחזירה את מספר הימים בין התאריך ב-A2 לתאריך המאוחר יותר ב-B2. ספירת ימים עם DAYS, ספירה שכוללת את שני התאריכים, ושבועות, חודשים, שנים או ימי עבודה.
- יום בשבוע=TEXT(A2,"dddd") מחזירה את שם היום של התאריך ב-A2, למשל Monday, ו-=WEEKDAY(A2) מחזירה אותו כמספר. שמות קצרים, סוגי ההחזרה של WEEKDAY ובדיקת סוף שבוע.
- TODAY ו-NOW=TODAY() מחזירה את התאריך של היום ו-=NOW() את התאריך והשעה הנוכחיים, ושתיהן מתעדכנות בכל פעם שהגיליון מחושב מחדש. ספירת ימים עד תאריך, והכנסת תאריך שלא משתנה עם Ctrl+;.
- הוספת ימים וחודשים=A2+30 מחזירה את התאריך שחל 30 ימים אחרי A2. להוספת חודשים השתמשו ב-=EDATE(A2,3), לסוף חודש ב-=EOMONTH(A2,0), ולשנים ב-EDATE עם 12 חודשים לכל שנה.
- NETWORKDAYS ו-WORKDAY=NETWORKDAYS(A2,B2) סופרת את ימי העבודה (שני עד שישי) מ-A2 עד B2, כולל שני התאריכים. =WORKDAY(A2,10) מחזירה את התאריך שחל 10 ימי עבודה אחרי A2. שתיהן יכולות לדלג על רשימת חגים.
- DATE, YEAR, MONTH, DAY=DATE(2026,3,15) מחזירה את התאריך 15 במרץ 2026 משנה, חודש ויום. YEAR, MONTH ו-DAY מפרקות תאריך לחלקים, ו-DATE מגלגלת את חודש 13 לשנה הבאה.
- חישובי זמן=B2-A2 מחזירה את הזמן בין שעת התחלה ב-A2 לשעת סיום ב-B2: עצבו אותה כ-h:mm כדי לראות 8:30, או הכפילו ב-24 כדי לקבל 8.5 שעות. משמרות שעוברות את חצות, סכומים מעל 24 שעות ושכר לפי שעות עבודה.
- מספר שבוע=WEEKNUM(A2) מחזירה את מספר השבוע של התאריך ב-A2, כשהשבועות מתחילים ביום ראשון. =ISOWEEKNUM(A2) מחזירה את שבוע ה-ISO שמשמש באירופה, שבו השבועות מתחילים ביום שני. תאריך תחילת שבוע ותאריך ממספר שבוע.
מתמטיקה וסטטיסטיקה
- ROUND=ROUND(A2,2) מעגלת את המספר ב-A2 לשתי ספרות אחרי הנקודה, ו-=ROUND(A2,0) למספר השלם הקרוב. מספר ספרות שלילי מעגל לעשרות, למאות ולאלפים; MROUND מעגלת לכל כפולה.
- ROUNDUP / ROUNDDOWN=ROUNDUP(A2,0) תמיד מעגלת הרחק מאפס, ולכן 2.1 הופך ל-3, ו-=ROUNDDOWN(A2,0) תמיד מעגלת לכיוון אפס, ולכן 2.9 הופך ל-2. CEILING ו-FLOOR מעגלות למעלה או למטה לכפולה, ו-INT ו-TRUNC משמיטות ספרות עשרוניות.
- סטיית תקן=STDEV.S(B2:B9) נותנת את סטיית התקן של מדגם ו-=STDEV.P(B2:B9) של אוכלוסייה שלמה. השתמשו ב-STDEV.S אלא אם הנתונים שלכם הם כל הערכים שקיימים. VAR.S ו-VAR.P נותנות את השונות.
- RANK=RANK.EQ(B2,$B$2:$B$7) נותנת את המיקום של B2 בין הערכים ב-B2:B7, כשהגדול ביותר מדורג 1. הוסיפו 1 כארגומנט שלישי כדי לדרג את הקטן ביותר ראשון. ערכים בתיקו חולקים דירוג; COUNTIFS מדרגת בתוך קבוצה.
- מספרים אקראיים=RANDBETWEEN(1,100) מחזירה מספר שלם אקראי מ-1 עד 100, ו-=RAND() מספר עשרוני אקראי מ-0 ועד 1. RANDARRAY ממלאת טווח שלם, INDEX עם RANDBETWEEN בוחרת פריט אקראי, והדבקה מיוחדת > ערכים מקפיאה את התוצאות.
- MOD ו-ABS=MOD(A2,B2) מחזירה את השארית אחרי חלוקה של A2 ב-B2, ולכן =MOD(17,5) היא 2. =ABS(A2) מחזירה מספר בלי הסימן שלו, ולכן =ABS(B2-C2) היא ההפרש בין שני ערכים, לא משנה איזה מהם גדול יותר.
- PMT=PMT(B2/12,B3*12,-B1) מחזירה את ההחזר החודשי על הלוואה של B1 בריבית השנתית שב-B2 לאורך B3 שנים. חלקו את הריבית ב-12, הכפילו את השנים ב-12, ושימו מינוס לפני סכום ההלוואה כדי לקבל החזר חיובי.
- NPV ו-IRR=NPV(E2,B3:B5)+B2 מהוונת את תזרימי המזומנים העתידיים בריבית שב-E2 ומוסיפה את ההשקעה הראשונית שב-B2, ש-NPV לא אמורה להוון. =IRR(B2:B5) מחזירה את הריבית שבה ה-NPV הזה הוא אפס. XNPV ו-XIRR מקבלות תאריכים אמיתיים.
- CAGR=(B2/A2)^(1/C2)-1 נותנת את שיעור הצמיחה השנתי המורכב מערך התחלתי ב-A2 לערך סופי ב-B2 לאורך C2 שנים. =RRI(C2,A2,B2) מחזירה את אותו שיעור. עצבו את התא כאחוז.
מערכים דינמיים
- FILTER=FILTER(A2:C7,B2:B7="North") מחזירה כל שורה של A2:C7 שהאזור שלה הוא North, והתוצאה מתעדכנת כשהנתונים משתנים. כמה קריטריונים עם * ו-+, if_empty, #CALC! ומיון התוצאה.
- UNIQUE=UNIQUE(B2:B8) מחזירה כל ערך של B2:B8 פעם אחת, בסדר שבו הוא מופיע לראשונה, ומתעדכנת כשהרשימה משתנה. שורות ייחודיות, exactly_once, רשימה ייחודית ממוינת, ספירת ערכים ייחודיים ושימוש בתוצאה כמקור לרשימה נפתחת.
- SORT ו-SORTBY=SORT(A2:C7,3,-1) מחזירה את הטבלה A2:C7 ממוינת לפי העמודה השלישית שלה, מהגדול לקטן, וממיינת אותה מחדש כשהנתונים משתנים. SORTBY ממיינת לפי כל טווח, כולל כמה עמודות וסדר מותאם אישית.
- SEQUENCE=SEQUENCE(5) מחזירה את המספרים 1 עד 5 לאורך עמודה, ו-=SEQUENCE(3,4) ממלאת 3 שורות על 4 עמודות. הוסיפו התחלה וצעד לכל סדרה, כולל תאריכים, מספור שורות שגדל עם הרשימה ולוח שנה חודשי.
- TRANSPOSE=TRANSPOSE(A1:D3) הופכת את השורות של A1:D3 לעמודות ונשארת מקושרת למקור. להעתקה חד־פעמית, השתמשו בהדבקה מיוחדת > החלף שורות ועמודות. TOCOL עורמת רשת שלמה לעמודה אחת.
- LET=LET(total,SUM(B2:B6),IF(total>500,total*0.9,total)) מחשבת את הסכום פעם אחת, קוראת לו total ומשתמשת בשם פעמיים. LET הופכת נוסחאות ארוכות לקצרות יותר, קריאות יותר ומהירות יותר, כי כל חלק עם שם מחושב רק פעם אחת.
- LAMBDA=LAMBDA(price,price*1.2)(B2) מגדירה פונקציה קטנה עם קלט אחד, price, וקוראת לה על B2. שמרו LAMBDA במנהל השמות כדי להשתמש בה כמו בפונקציה מובנית, או העבירו אותה ל-MAP, BYROW, SCAN ו-REDUCE.
שגיאות ופתרונות
- שגיאת #SPILL!#SPILL! פירושה שלנוסחה שמחזירה כמה ערכים אין מקום לשים אותם: תא בטווח השפיכה שלה אינו ריק. נקו את התאים שעומדים בדרך והתוצאה מופיעה.
- שגיאת #VALUE!#VALUE! פירושה שנוסחה קיבלה ערך מהסוג הלא נכון, לרוב טקסט במקום שבו היא צריכה מספר: =B2+C2 נכשלת כש-C2 מכיל "n/a" או רווח. SUM מתעלמת מטקסט, ולכן =SUM(B2:C2) עובדת.
- שגיאת #NAME?#NAME? פירושה שאקסל לא מזהה מילה בנוסחה: פונקציה עם שגיאת כתיב כמו =SUMM(B2:B6), טקסט בלי מירכאות, נקודתיים חסרות בטווח, שם שלא הוגדר, או פונקציה שאין בגרסת האקסל שלכם.
- שגיאת #REF!#REF! פירושה שנוסחה מפנה לתא שכבר לא קיים, בדרך כלל כי שורה, עמודה או גיליון שהיא השתמשה בהם נמחקו: =B2*C2 הופכת ל-=B2*#REF!. היא מופיעה גם כש-VLOOKUP או INDEX מבקשות עמודה או שורה מחוץ לטווח שלהן.
- שגיאת #N/A#N/A פירושה שחיפוש לא מצא את הערך שהוא חיפש. בדקו שגיאות הקלדה, רווחים מיותרים וטווח טבלה שזז כשהנוסחה מולאה למטה, ואז השתמשו ב-IFNA כדי להציג הודעה לערכים שבאמת חסרים.
- שגיאת #DIV/0!#DIV/0! מופיעה כשנוסחה מחלקת באפס או בתא ריק, כמו =B2/C2 כש-C2 ריק. =IF(C2=0,"",B2/C2) מציגה תא ריק במקום, וגם AVERAGE של טווח בלי מספרים מחזירה אותה.
- הפניה מעגליתהפניה מעגלית היא נוסחה שמפנה לתא של עצמה, ישירות או דרך נוסחאות אחרות, כמו =SUM(B2:B7) שמוקלדת ב-B7. אקסל מזהיר, מציג 0, ומציג את התא תחת נוסחאות > בדיקת שגיאות > הפניות מעגליות.
- נוסחה לא מחשבתאם אקסל מציג את הנוסחה במקום התוצאה, התא מעוצב כטקסט, הנוסחה מתחילה בגרש או ברווח, או שהצג נוסחאות מופעל. אם התוצאות לא מתעדכנות, החישוב מוגדר לידני: נוסחאות > אפשרויות חישוב > אוטומטי.
כלי נתונים
- הסרת כפילויותבחרו את הנתונים ולחצו על נתונים > הסר כפילויות כדי למחוק שורות חוזרות במקום, או השתמשו ב-=UNIQUE(A2:A9) כדי לקבל עותק נקי ולשמור את המקור. מציאה, סימון וספירה של כפילויות, והסרה לפי שתי עמודות.
- הדגשת כפילויותבחרו את התאים ובחרו בית > עיצוב מותנה > כללי סימון תאים > ערכים כפולים. לשורות שלמות, לעותק השני בלבד או להתאמות בין שתי עמודות, השתמשו בכלל עם נוסחה כמו =COUNTIF($A$2:$A$9,A2)>1.
- עיצוב מותנהעיצוב מותנה צובע תא כשתנאי מתקיים. השתמשו ב-בית > עיצוב מותנה לכללים מוכנים, או ב-כלל חדש > השתמש בנוסחה עם כלל כמו =$C2>100 כדי לצבוע שורות שלמות, תאריכים שעברו והתאמות טקסט.
- רשימה נפתחתבחרו את התאים, עברו ל-נתונים > אימות נתונים, בחרו רשימה, והקלידו את הפריטים (North,South,East) או בחרו טווח כמקור. אחר כך הפכו את הרשימה לדינמית עם UNIQUE, לתלויה ברשימה אחרת, וחפשו את הפריט שנבחר.
- השוואת שתי עמודותכדי להשוות שתי עמודות שורה אחר שורה, השתמשו ב-=A2=B2 (או ב-EXACT לרגישות לאותיות). כדי למצוא ערכים בעמודה אחת שחסרים בשנייה, השתמשו ב-COUNTIF, MATCH או XLOOKUP, והדגישו את ההבדלים עם עיצוב מותנה.
- טבלת צירטבלת ציר מקבצת את השורות של טבלה לפי קטגוריה ומסכמת מספר לכל אחת, בלי נוסחאות: הוספה > PivotTable, ואז גוררים שדות לשורות ולערכים. כאן הצעדים, הסבר על ארבעת האזורים, ואותו סיכום שבנוי עם נוסחאות.
