מבט כולל על נוסחאות ב- Excel

מבט כולל על נוסחאות ב- Excel

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

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

לדוגמה, הנוסחה הבאה מכפילה 2 ב- 3 ולאחר מכן מוסיפה 5 לתוצאה, כדי לקבל את התשובה, 11.

‎=2*3+5

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

  • ‎=A1+A2+A3    סיכום הערכים שבתאים A1, ‏A2 ו- A3.

  • ‎=SUM(A1:A10)‎    שימוש בפונקציה SUM כדי להחזיר את סכום הערכים בתאים A1 עד A10.

  • ‏‏‎=TODAY()   ‎‏‏ החזרת התאריך הנוכחי.

  • ‎=UPPER("hello")‎     המרת הטקסט "hello" ל- "HELLO" באמצעות הפונקציה UPPER.

  • ‎=IF(A1>0)‎    שימוש בפונקציה IF כדי לבדוק את התא A1 ולקבוע אם הוא מכיל ערך הגדול מ- 0.

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

חלקי נוסחה   

חלקי נוסחה

1. פונקציות: הפונקציה PI()‎ מחזירה את הערך של פאי‏: ‎3.142...‎

2. הפניות: A2 מחזיר את הערך שבתא A2.

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

4. אופרטורים: האופרטור ^ מעלה מספר בחזקה, והאופרטור * (כוכבית) מכפיל מספרים.

קבוע הוא ערך שאינו מחושב; הוא נשאר תמיד אותו הדבר. לדוגמה, התאריך 9/10/2008, המספר 210 והטקסט "רווח רבעוני" הם כולם קבועים. ביטוי או ערך שנובע מביטוי אינם קבועים. בעת שימוש בקבועים בנוסחה במקום בהפניות לתאים (לדוגמה ‎=30+70+110), התוצאה תשתנה רק אם תשנה את הנוסחה. באופן כללי, מומלץ להציב קבועים בתאים בודדים שבהם ניתן לשנותם בקלות בעת הצורך, ולאחר לכלול בנוסחאות הפניות לתאים אלה.

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

סוגי האופרטורים. ישנם ארבעה סוגים שונים של אופרטורים לחישוב: אריתמטי, השוואה, שרשור טקסט והפניה.

  • אופרטורים אריתמטיים

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

    אופרטור אריתמטי

    משמעות

    דוגמה

    + (סימן חיבור)

    חיבור

    ‎‎=3+3

    – (סימן חיסור)

    חיסור
    שלילה

    ‎=3–3
    =-3

    * (כוכבית)

    כפל

    ‎=3*3

    / (קו נטוי)

    חילוק

    ‎=3/3

    % (סימן אחוזים)

    אחוז

    30%

    ^

    העלאה בחזקה

    ‎=3^3

  • אופרטורים של השוואה

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

    אופרטור השוואה

    משמעות

    דוגמה

    = (סימן שוויון)

    שווה ל

    ‎=A1=B1

    ‎>‎ (סימן גדול מ)

    גדול מ

    ‎=A1>B1

    ‎<‎ (סימן קטן מ)

    קטן מ

    ‎=A1<B1

    ‎>=‎ (סימן גדול מ או שווה ל)

    גדול מ או שווה ל

    ‎=A1>=B1

    ‎<=‎ (סימן קטן מ או שווה ל)

    קטן מ או שווה ל

    ‎=A1<=B1

    <> (סימן שונה מ)

    שונה מ

    ‎=A1<>B1

  • אופרטור שרשור טקסט

    השתמש באופרטור תו אמפרסנד (&) כדי לשרשר (לצרף) מחרוזת טקסט אחת או יותר כדי ליצור קטע טקסט בודד.

    אופרטור טקסט

    משמעות

    דוגמה

    & (תו אמפרסנד)

    חיבור או שרשור של שני ערכים כדי ליצור ערך טקסט רציף אחד

    התוצאה של אופרטור שרשור טקסט="North"&"wind" היא "Northwind".
    כאשר התא A1 מאכלס את "שם משפחה" ותא B1 מאכלס את "שם פרטי", תוצאת הנוסחה ‎=A1&", "&B1‎ תהיה "שם משפחה, שם פרטי".

  • אופרטורים של הפניה

    שלב טווחים של תאים עבור חישובים בעזרת האופרטורים הבאים.

    אופרטור הפניה

    משמעות

    דוגמה

    : (נקודתיים)

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

    B5:B15

    , (פסיק)

    אופרטור איחוד, המחבר מספר רב של הפניות להפניה אחת

    ‎=SUM(B5:B15,D5:D15)‎

    (רווח)

    אופרטור חיתוך, המייצר הפניה אחת לתאים המשותפים לשתי הפניות

    B7:D7 C6:C8

לראש הדף

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

  • סדר החישוב

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

  • קדימות של אופרטורים בנוסחאות ב- Excel

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

    אופרטור

    תיאור

    : (נקודתיים)

    (רווח בודד)

    , (פסיק)

    אופרטורים של הפניה

    שלילה (כמו ‎–1)

    %

    אחוז

    ^

    העלאה בחזקה

    * ו- /

    כפל וחילוק

    + וגם –

    חיבור וחיסור

    &

    קישור שתי מחרוזות טקסט (שרשור)

    ‎=
    < >
    <=
    >=
    <>‎

    השוואה

  • שימוש בסוגריים בנוסחאות ב- Excel

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

    ‎=5+2*3

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

    ‎=(5+2)*3

    בדוגמה הבאה, הסוגריים התוחמים את חלקה הראשון של הנוסחה מאלצים את Excel לחשב תחילה את B4+25 ולאחר מכן לחלק את התוצאה בסכום הערכים בתאים D5, ‏E5 ו- F5.

    ‎=(B4+25)/SUM(D5:F5)‎

    לראש הדף

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

הכרטיסיה 'נוסחאות' של Excel ברצועת הכלים
  • תחביר הפונקציה של Excel

    הדוגמה הבאה של הפונקציה ROUND המעגלת מספר בתא A10 מדגימה את התחביר של פונקציה.

    מבנה הפונקציה

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

    2. שם פונקציה. לקבלת רשימה של פונקציות זמינות, לחץ על תא כלשהו והקש SHIFT+F3, כדי להציג את תיבת הדו-שיח הוספת פונקציה.

    נוסחאות Excel - תיבת הדו-שיח 'הוספת פונקציה'

    3. ארגומנטים. ארגומנטים יכולים להיות מספרים, טקסט, ערכים לוגיים כגון TRUE או FALSE, ‏מערכים, ערכי שגיאה כגון ‎#N/A או הפניות לתאים. על הארגומנט המיועד להפיק ערך חוקי עבור אותו ארגומנט. ארגומנטים יכולים להיות גם קבועים, נוסחאות או פונקציות נוספות.

    4. תיאור כלי של ארגומנט. בעת הקלדת הפונקציה, מופיע תיאור כלי עם התחביר והארגומנטים. לדוגמה, כאשר תקליד ‎=ROUND(‎ יופיע תיאור הכלי. תיאורי כלי מופיעים רק עבור פונקציות מוכללות.

    הערה: אין צורך להקליד פונקציות באותיות רישיות, למשל, ‎=ROUND, מאחר ש- Excel יהפוך באופן אוטומטי את שם הפונקציה לאותיות רישיות לאחר שתקיש על Enter. אם תטעה בעיות של שם פונקציה, כמו ‎=SUME(A1:A10)‎ במקום ‎=SUM(A1:A10)‎,‏ Excel יחזיר את השגיאה ‏‏#NAME?‏.

  • הזנת פונקציות של Excel

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

    אשף הפונקציות של Excel

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

    השלמה אוטומטית של נוסחה ב- Excel

  • קינון פונקציות של Excel

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

    פונקציות מקוננות

    1. הפונקציות AVERAGE ו- SUM מקוננות בתוך הפונקציה IF.

    החזרות חוקיות    כאשר פונקציה מקוננת משמשת כארגומנט, עליה להחזיר ערך מאותו סוג שבו משתמש הארגומנט. לדוגמה, אם הארגומנט מחזיר ערך TRUE או FALSE, הפונקציה המקוננת חייבת להחזיר ערך TRUE או FALSE. אחרת, Excel יציג את ערך השגיאה ‎#VALUE!‎.

    מגבלות רמת קינון    נוסחה יכולה להכיל עד שבע רמות של פונקציות מקוננות. כאשר פונקציה אחת (נקרא לה פונקציה B) משמשת כארגומנט בפונקציה אחרת (נקרא לה פונקציה A), פונקציה B פועלת כפונקציה מרמה שנייה. לדוגמה, הפונקציה AVERAGE והפונקציה SUM הן פונקציות מרמה שנייה אם הן משמשות כארגומנטים של הפונקציה IF. פונקציה מקוננת בתוך הפונקציה המקוננת AVERAGE תהיה פונקציה מרמה שלישית, וכן הלאה.

    לראש הדף

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

  • סגנון ההפניה A1

    כברירת מחדל, Excel משתמש בסגנון ההפניה B2, המפנה לעמודות באמצעות אותיות (A עד XFD, סך הכל 16,384 עמודות) ולשורות באמצעות מספרים (1 עד 1,048,576). אותיות ומספרים אלה נקראים כותרות עמודות ושורות. כדי להפנות לתא, הזן את אות העמודה ואחריה את מספר השורה. לדוגמה, B2 מפנה לתא בהצטלבות שבין עמודה B לשורה 2.

    כדי להפנות אל

    השתמש ב

    התא בעמודה A ובשורה 10

    A10

    טווח התאים בעמודה A ובשורות 10 עד 20

    A10:A20

    טווח התאים בשורה 15 ובעמודות B עד E

    B15:E15

    כל התאים בשורה 5

    5:5

    כל התאים בשורות 5 עד 10

    5:10

    כל התאים בעמודה H

    H:H

    כל התאים בעמודות H עד J

    H:J

    טווח התאים בעמודות A עד E ובשורות 10 עד 20

    A10:E20

  • יצירת הפניה לתא או לטווח תאים בגליון עבודה אחר באותה חוברת עבודה

    בדוגמה הבאה, הפונקציה AVERAGE מחשבת את הערך הממוצע עבור הטווח B1:B10 בגליון העבודה ששמו Marketing באותה חוברת עבודה.

    דוגמת הפניה לגיליון

    1. הפניה לגליון העבודה בשם Marketing

    2. הפניה לטווח התאים מ- B1 עד B10

    3. סימן הקריאה (!) מפריד בין ההפניה לגליון עבודה לבין ההפניה לטווח התאים

    הערה: אם גליון העבודה שאליו מתבצעת הפניה כולל רווחים או מספרים, יש להוסיף תווי גרש (') לפני ואחרי שם גליון העבודה, למשל, ‎='123'!A1 או ‎='January Revenue'!A1‎.

  • ההבדל בין הפניות מוחלטות, יחסיות ומעורבות

    1. הפניות יחסיות    הפניה יחסית לתא בנוסחה, כגון A1, מבוססת על המיקום היחסי של התא המכיל את הנוסחה ושל התא שההפניה מפנה אליו. אם המיקום של התא המכיל את הנוסחה משתנה, ההפניה משתנה. אם אתה מעתיק או ממלא את הנוסחה לאורכן של שורות או עמודות, ההפניה מותאמת באופן אוטומטי. כברירת מחדל, נוסחאות חדשות משתמשות בהפניות יחסיות. לדוגמה, אם אתה מעתיק או ממלא הפניה יחסית בתא B2 עד לתא B3, היא משתנה באופן אוטומטי מ- ‎=A1 ל- ‎=A2.

      נוסחה מועתקת עם הפניה יחסית   

      נוסחה מועתקת עם הפניה יחסית

    2. הפניות מוחלטות    הפניה מוחלטת לתא בנוסחה, כגון $A$1, מפנה תמיד לתא במיקום מסוים. אם מיקום התא המכיל את הנוסחה משתנה, ההפניה המוחלטת אינה משתנה. אם אתה מעתיק או ממלא את הנוסחה לאורכן של שורות או עמודות, ההפניה המוחלטת אינה מתאימה את עצמה. נוסחאות חדשות משתמשות בהפניות יחסיות כברירת מחדל, ולכן ייתכן שיהיה עליך להחליף אותן להפניות מוחלטות. לדוגמה, אם אתה מעתיק הפניה מוחלטת בתא B2 עד לתא B3, היא תישאר ללא שינוי בשני התאים: ‎=$A$1.

      נוסחה מועתקת עם הפניה מוחלטת   

      נוסחה מועתקת עם הפניה מוחלטת
    3. הפניות מעורבות    הפניה מעורבת כוללת עמודה מוחלטת ושורה יחסית, או שורה מוחלטת ועמודה יחסית. הפניה מוחלטת לעמודה מוצגת כ- ‎$A1‏, ‎$B1 וכן הלאה. הפניה מוחלטת לשורה מוצגת כ- A$1, ‏B$1 וכן הלאה. אם מיקום התא המכיל את הנוסחה משתנה, ההפניה היחסית משתנה, וההפניה המוחלטת אינה משתנה. אם אתה מעתיק או ממלא את הנוסחה לאורכן של שורות או עמודות, ההפניה היחסית מתאימה את עצמה באופן אוטומטי, וההפניה המוחלטת אינה מותאמת. לדוגמה, אם אתה מעתיק או ממלא הפניה מעורבת מתא A2 לתא B3, היא משתנה בהתאם מ- ‎=A$1 ל- ‎=B$1.

      נוסחה מועתקת עם הפניה מעורבת   

      נוסחה מועתקת עם הפניה מעורבת

  • סגנון הפניה תלת-ממדית

    הפניה נוחה לגליונות עבודה מרובים    אם ברצונך לנתח נתונים באותו תא או טווח של תאים בגליונות עבודה מרובים בחוברת עבודה, השתמש בהפניה תלת-ממדית. הפניה תלת-ממדית כוללת את ההפניה לתא או לטווח, ולפניה טווח של שמות גליונות עבודה. Excel משתמש בכל גליונות העבודה המאוחסנים בין השם הראשון והשם האחרון בהפניה. לדוגמה, ‎=SUM (Sheet2:Sheet13!B5)‎ מחברת את כל הערכים שמכיל התא B5 בכל גליונות העבודה שבין גיליון 2 וגיליון 13, ועד בכלל.

    • באפשרותך להשתמש בהפניות תלת-ממדיות כדי להפנות לתאים בגליונות אחרים, להגדיר שמות וליצור נוסחאות באמצעות הפונקציות הבאות: SUM, ‏AVERAGE, ‏AVERAGEA, ‏COUNT, ‏COUNTA, ‏MAX, ‏MAXA, ‏MIN, ‏MINA, ‏PRODUCT, ‏STDEV.P, ‏STDEV.S, ‏STDEVA, ‏STDEVPA, ‏VAR.P, ‏VAR.S, ‏VARA ו- VARPA.

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

    • לא ניתן להשתמש בהפניות תלת-ממדיות עם אופרטור החיתוך (רווח יחיד) או בנוסחאות המשתמשות בחיתוך משתמע.

    מה קורה כשאתה מעביר, מעתיק, מוסיף או מוחק גליונות עבודה    הדוגמאות הבאות מסבירות מה קורה כשאתה מעביר, מעתיק, מוסיף או מוחק גליונות עבודה הכלולים בהפניה תלת-ממדית. דוגמאות אלה משתמשות בנוסחה ‎=SUM(Sheet2:Sheet6!A2:A5)‎ כדי לחבר את התאים A2 עד A5 בגליונות העבודה 2 עד 6.

    • הוספה או העתקה    בעת הוספה או העתקה של גליונות בין גיליון2 לגיליון6 (נקודות הקצה בדוגמה זו), Excel כולל בחישובים את כל הערכים בתאים A2 עד A5 מהגליונות שחוברו.

    • מחיקה    בעת מחיקה של גליונות בין גיליון2 לגיליון6, Excel מסיר את הערכים שלהם מהחישוב.

    • העברה    בעת העברת גליונות בין גיליון2 לגיליון6 למיקום מחוץ לטווח הגליונות שאליו מתייחסת ההפניה, Excel מסיר את הערכים שלהם מהחישוב.

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

    • מחיקה של נקודת קצה    בעת מחיקה של גיליון2 או גיליון6, Excel מבצע התאמה של החישוב על ידי Excel כדי להתאים את טווח הגליונות ביניהם.

  • הפניות בסגנון R1C1

    באפשרותך גם להשתמש בסגנון הפניה שבו ממוספרות הן השורות והן העמודות בגליון העבודה. סגנון ההפניה R1C1 שימושי לחישוב מיקומי שורות ועמודות בפקודות מאקרו. בסגנון R1C1‏, Excel מציין מיקום תא באמצעות "R" ואחריו מספר שורה ו- "C" ואחריו מספר עמודה.

    הפניה

    משמעות

    R[-2]C

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

    R[2]C[2]‎

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

    R2C2

    הפניה מוחלטת לתא בשורה השניה ובעמודה השניה

    R[-1]‎

    הפניה יחסית לכל השורה מעל התא הפעיל

    R

    הפניה מוחלטת לשורה הנוכחית

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

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

    לראש הדף

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

דוגמה 1

סוג דוגמה

לדוגמה, השימוש בטווחים במקום בשמות

לדוגמה, שימוש בשמות

הפניה

‎=SUM(A16:A20)‎

‎=SUM(Sales)‎

קבוע

‎=PRODUCT(A12,9.5%)‎

‎=PRODUCT(Price,TaxRate)‎

נוסחה

‎=TEXT(VLOOKUP(MAX(A16,A20),A16:B20,2,FALSE),"m/dd/yyyy")‎

‎=TEXT(VLOOKUP(MAX(Sales),SalesInfo,2,FALSE),"m/dd/yyyy")‎

טבלה

A22:B25

‎=PRODUCT(Price,Table1[@Tax Rate])‎

דוגמה 2

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

הערה:  הערה: בנוסחאות שבעמודות C ו- D, השם המוגדר "Sales" מחליף את ההפניה ל(טווח) A9:A13 והשם "SalesInfo" מחליף את הטווח ‏A9:B13. אם אינך יוצר שמות אלה בחוברת העבודה המשמשת לבדיקה, הנוסחאות D2:D3 יחזירו את השגיאה ‎#NAME?‎.

סוג דוגמה

דוגמה, ללא שם

דוגמה, עם שם

נוסחה ותוצאה, עם שם

הפניה

‎'=SUM(A9:A13)‎

‎'=SUM(Sales)‎

‎=SUM(Sales)‎

נוסחה

‎'=TEXT(VLOOKUP(MAX(A9:13),A9:B13,2,FALSE),"m/dd/yyyy")‎

‎'‎=TEXT(VLOOKUP(MAX(Sales),SalesInfo,2,FALSE),"m/dd/yyyy")‎

‎=TEXT(VLOOKUP(MAX(Sales),SalesInfo,2,FALSE),"m/dd/yyyy")‎

מחיר

$995

מכירות

Sale Date

$249

17/3/2011

$399

2/4/2011

$643

23/4/2011

$275

30/4/2011

$447

4/5/2011

  • סוגי שמות

    ישנם כמה סוגים של שמות שניתן ליצור ולהשתמש בהם.

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

    • שם טבלה    שם עבור טבלת Excel, שהיא אוסף של נתונים אודות נושא מסוים, המאוחסנים ברשומות (שורות) ובשדות (עמודות). Excel יוצר שם ברירת מחדל עבור טבלת Excel שהוא "טבלה1", ‏"טבלה2" וכן הלאה בכל פעם שתוסיף טבלת Excel, אך באפשרותך לשנות שמות אלה כדי להעניק להם יותר משמעות.

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

  • יצירה והזנה של שמות

    באפשרותך ליצור שמות באמצעות:

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

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

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

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

    באפשרותך להזין שם על-ידי:

    • הקלדה    הקלדת השם, לדוגמה, כארגומנט לנוסחה.

    • שימוש בהשלמה אוטומטית של נוסחה    השתמש ברשימה הנפתחת של 'השלמה אוטומטית של נוסחה', שבה שמות חוקיים מפורטים באופן אוטומטי עבורך.

    • בחירה מתוך הפקודה 'השתמש בתוך נוסחה    בחר שם מוגדר מתוך רשימה זמינה מהפקודה השתמש בנוסחה הפקודה שמות מוגדרים בכרטיסיה נוסחה .

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

לראש הדף

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

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

שימוש בנוסחת מערך כדי לחשב תוצאה בודדת ותוצאות מרובות

הערה: בעת הזנת נוסחת מערך, ‏Excel מוסיף אוטומטית את הנוסחה בין { } (סוגריים מסולסלים). אם תנסה להזין סוגריים מסולסלים בעצמך, Excel יציג את הנוסחה כטקסט.

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

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

    נוסחת מערך המפיקה תוצאה בודדת

    כשהנוסחה ‎={SUM(B2:D2*B3:D3)}‎ מוזנת כנוסחת מערך, היא מכפילה את המניות והמחיר עבור כל מניה ולאחר מכן מחברת את תוצאות החישובים האלה.

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

    לדוגמה, כאשר נתונה סידרה של נתוני שלוש מכירות (בעמודה B) עבור סידרה של שלושה חודשים (בעמודה A), הפונקציה TREND קובעת את ערכי הקו הישר עבור נתוני המכירות. כדי להציג את כל תוצאות הנוסחה, היא מוזנת לשלושה תאים בעמודה C ‏(C1:C3).

    נוסחת מערך המפיקה תוצאות מרובות

    כאשר הנוסחה ‎=TREND(B1:B3,A1:A3)‎ מוזנת כנוסחת מערך, היא מפיקה שלוש תוצאות נפרדות (22196, 17079 ו- 11962) בהתבסס על שלושת נתוני המכירות ושלושת החודשים.

שימוש בקבועי מערך

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

קבועי מערך יכולים להכיל מספרים, טקסט, ערכים לוגיים (כגון TRUE ו-FALSE) או ערכי שגיאה (כגון ‎#N/A). סוגי ערכים שונים יכולים להימצא באותו קבוע מערך  — לדוגמה, ‎{1,3,4;TRUE,FALSE,TRUE}‎. מספרים בקבועי מערך יכולים להימצא בתבנית מספר שלם, בתבנית מספר עשרוני או בתבנית מדעית. טקסט חייב להיות מוקף במרכאות כפולות — לדוגמה, "Tuesday" ("יום שלישי").

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

כשאתה מעצב קבועי מערך, הקפד:

  • לחתום אותם בתוך סוגריים מסולסלים ( { } ).

  • להפריד ערכים בעמודות שונות באמצעות פסיקים (,). לדוגמה, כדי לייצג את הערכים 10‏, 20‏, 30 ו- 40, הזן ‎{10,20,30,40}‎. קבוע מערך זה מוכר כמערך 1‏-על-‏4 ומקביל להפניה של שורה אחת על 4 עמודות.

  • להפריד בין ערכים בשורות שונות באמצעות תווי נקודה-פסיק (;). לדוגמה, כדי לייצג את הערכים 10‏, 20‏, 30 ו- 40 בשורה אחת ואת 50‏, 60‏, 70 ו- 80 בשורה שמתחתיה, עליך להזין קבוע של מערך 2‏-על-‏4:‏ ‎{‎10,20,30,40;50,60,70,80}‎.

לראש הדף

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

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

    1. בחר את התא או טווח התאים המכיל את הנוסחה.

    2. הקש Delete.

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

    1. בחר את התא או טווח התאים המכיל את הנוסחה.

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

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

      1. לחץ על תא בנוסחת המערך.

      2. בכרטיסיה בית, בקבוצה עריכה, לחץ על חפש ובחר ולאחר מכן לחץ על מעבר אל.

      3. לחץ על מיוחד.

      4. לחץ על מערך נוכחי.

    2. בכרטיסיה בית, בקבוצה לוח לחץ על עותק תמונת לחצן .
      תמונת רצועת הכלים של excel

      קיצור מקשים    באפשרותך גם להקיש CTRL+C.

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

לראש הדף

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

ודא שאתה...

מידע נוסף

התאמת כל הסוגריים הפותחים והסוגרים בנוסחה   

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

שימוש בנקודתיים לציון טווח שאתה מזין בנוסחה   

נקודתיים (:) משמשים להפרדת ההפניה לתא הראשון ולתא האחרון בטווח. לדוגמה, A1:A5.

הזנת כל הארגומנטים הדרושים   

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

אין לקנן יותר מ- 64 פונקציות בנוסחה אחת   

קינון פונקציות בתוך נוסחה מוגבל ל- 64 רמות.

תחימת השמות של חוברת עבודה או גליון עבודה בסימני גרש בודד    

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

הכללת הנתיב לחוברות עבודה חיצוניות   

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

הזנת מספרים ללא עיצוב   

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

לראש הדף

חשוב: ייתכן שהתוצאות המחושבות של נוסחאות ופונקציות מסוימות של גליון העבודה של Excel יהיו שונות מעט בין מחשב Windows המשתמש בארכיטקטורת x86 או x86-64 לבין מחשב Windows RT המשתמש בארכיטקטורת ARM. למד עוד אודות ההבדלים.

האם יש לך שאלה בנושא פונקציה ספציפית?

פרסם שאלה בפורום הקהילה של Excel

עזור לנו לשפר את Excel

האם יש לך הצעות לשיפור הגירסה הבאה של Excel? אם כן, עיין בנושאים תחת User Voice עבור Excel

למידע נוסף

מבט כולל על נוסחאות ב- Excel

כיצד להימנע מנוסחאות שגויות

חיפוש ותיקון שגיאות בנוסחאות

קיצורי מקשים ומקשים ייעודיים ב- Excel

פונקציות של Excel (בסדר אלפביתי)

פונקציות של Excel (לפי קטגוריה)

הרחב את הכישורים שלך
סייר בהדרכה
קבל תכונות חדשות לפני כולם
הצטרף למשתתפי Office Insider

האם מידע זה היה שימושי?

תודה על המשוב!

תודה על המשוב! נראה שכדאי לקשר אותך לאחד מנציגי התמיכה של Office.

×