באפשרותך להשתמש בנוסחאות ובפונקציות ברשימות או בספריות כדי לחשב נתונים במגוון דרכים. על-ידי הוספת עמודה מחושבת לרשימה או לספריה, באפשרותך ליצור נוסחה הכוללת נתונים מעמודות אחרות ומבצעת פונקציות לחישוב תאריכים ושעות, לביצוע משוואות מתמטיות או לטיפול בטקסט. לדוגמה, ברשימת משימות, באפשרותך להשתמש בעמודה כדי לחשב את מספר הימים שנדרש להשלמת כל פעילות, בהתבסס על העמודות תאריך התחלה ותאריך השלמה.
הערה: מאמר זה מתאר את המושגים הבסיסיים הקשורים לשימוש בנוסחאות ובפונקציות. לקבלת מידע ספציפי אודות פונקציה מסוימת, עיין במאמר אודות פונקציה זו.
במאמר זה
מבט כולל על נוסחאות
נוסחאות הן משוואות המבצעות חישובים בערכים ברשימה או בספריה. נוסחה מתחילה בסימן שוויון (=). לדוגמה, הנוסחה הבאה מכפילה את 2 ב- 3 ולאחר מכן מוסיפה 5 לתוצאה.
=5+2*3
באפשרותך להשתמש בנוסחה בעמודה מחושבת ולחשב ערכי ברירת מחדל עבור עמודה. נוסחה יכולה להכיל פונקציות, הפניות לעמודות, אופרטורים ו קבועים, כמו בדוגמה הבאה.
=PI()*[Result]^2
רכיב |
תיאור |
---|---|
פונקציה |
הפונקציה PI() מחזירה את הערך של pi: 3.141592654. |
הפניה (או שם עמודה) |
[Result] מייצג את הערך בעמודה תוצאה עבור השורה הנוכחית. |
קבוע |
מספרים או ערכי טקסט שהוזנו ישירות בנוסחה, כגון 2. |
אופרטור |
האופרטור * (כוכבית) מכפיל, האופרטור ^ (תו ^) מעלה מספר בעוצמה. |
נוסחה עשויה להשתמש באחד או יותר מהרכיבים מהטבלה הקודמת. להלן כמה דוגמאות של נוסחאות (לפי סדר המורכבות).
נוסחאות פשוטות (כגון =128+345)
הנוסחאות הבאות מכילות קבועים אופרטורים.
דוגמה |
תיאור |
---|---|
=128+345 |
הוספת 128 ו- 345 |
=5^2 |
ריבועים 5 |
נוסחאות המכילות הפניות לעמודות (כגון =[Revenue] >[Cost])
הנוסחאות הבאות מפנות לעמודות אחרות באותה רשימה או ספריה.
דוגמה |
תיאור |
---|---|
=[הכנסה] |
שימוש בערך בעמודה הכנסה. |
=[Revenue]*10/100 |
10% מהערך בעמודה הכנסה. |
=[Revenue] > [Cost] |
החזרת Yes אם הערך בעמודה Revenue גדול מהערך בעמודה Cost. |
נוסחאות שנקראות פונקציות (כגון =AVERAGE(1, 2, 3, 4, 5))
הנוסחאות הבאות קוראות לפונקציות מוכללות.
דוגמה |
תיאור |
---|---|
=AVERAGE(1, 2, 3, 4, 5) |
הפונקציה מחזירה את הממוצע של קבוצת ערכים. |
=MAX([Q1], [Q2], [Q3], [Q4]) |
הפונקציה מחזירה את הערך הגדול ביותר בקבוצה של ערכים. |
=IF([Cost]>[Revenue], "Not OK", "OK") |
החזרת Not OK אם העלות גדולה מהכנסות. אחרת, החזרת OK. |
=DAY("15-Apr-2008") |
הפונקציה מחזירה את חלק היום של תאריך. נוסחה זו מחזירה את המספר 15. |
נוסחאות עם פונקציות מקוננות (כגון =SUM(IF([A]>[B], [A]-[B], 10), [C]))
הנוסחאות הבאות מציינות פונקציה אחת או יותר כארגומנטים של פונקציה.
דוגמה |
תיאור |
---|---|
=SUM(IF([A]>[B], [A]-[B], 10), [C]) |
הפונקציה IF מחזירה את ההפרש בין הערכים בעמודות A ו- B, או 10. הפונקציה SUM מחברת את הערך המוחזר של הפונקציה IF ואת הערך בעמודה C. |
=DEGREES(PI()) |
הפונקציה PI מחזירה את המספר 3.141592654. הפונקציה DEGREES ממירה ערך שצוין ברדיאנים למעלות. נוסחה זו מחזירה את הערך 180. |
=ISNUMBER(FIND("BD",[Column1])) |
הפונקציה FIND מחפש את המחרוזת BD בעמודה1 ומחזירה את המיקום ההתחלתי של המחרוזת. היא מחזירה ערך שגיאה אם המחרוזת לא נמצאה. הפונקציה ISNUMBER מחזירה כן אם הפונקציה FIND החזירה ערך מספרי. אחרת, היא מחזירה 'לא'. |
מבט כולל על פונקציות
פונקציות הן נוסחאות מוגדרות מראש המבצעות חישובים תוך שימוש בערכים ספציפיים, הנקראים ארגומנטים, בסדר או במבנה מסוים. ניתן להשתמש בפונקציות כדי לבצע חישובים פשוטים או מורכבים. לדוגמה, המופע הבא של הפונקציה ROUND מעגל מספר בעמודה עלות לשני מקומות עשרוניים.
=ROUND([Cost], 2)
אוצר המילים הבא שימושי כאשר אתה לומדת פונקציות ונוסחאות:
מבנה המבנה של פונקציה מתחיל בסימן שוויון (=), ואחריו שם הפונקציה, תו סוגריים פותח, הארגומנטים עבור הפונקציה כשהם מופרדים באמצעות פסיקים ותארי סוגריים סוגרים.
שם הפונקציה זהו השם של פונקציה הנתמכת על-ידי רשימות או ספריות. כל פונקציה מקבלת מספר מסוים של ארגומנטים, מעבדת אותם ומחזירה ערך.
ארגומנטים ארגומנטים יכולים להיות מספרים, טקסט, ערכים לוגיים כגון True או False, או הפניות לעמודות. הארגומנט שאתה מייעד חייב להפיק ערך חוקי עבור ארגומנט זה. ארגומנטים יכולים להיות גם קבועים, נוסחאות או פונקציות נוספות.
במקרים מסוימים, ייתכן שיהיה עליך להשתמש בפונקציה כאחד מהארגומנטים של פונקציה אחרת. לדוגמה, הנוסחה הבאה משתמשת בפונקציה AVERAGE מקוננת משווה את התוצאה לסכום של שני ערכי עמודות.
=AVERAGE([Cost1], SUM([Cost2]+[Discount]))
החזרות חוקיות כאשר פונקציה משמשת כארגומנט, היא חייבת להחזיר את אותו סוג ערך שבו משתמש הארגומנט. לדוגמה, אם הארגומנט משתמש ב'כן' או ב'לא', הפונקציה המקוננת חייבת להחזיר 'כן' או 'לא'. אם לא, הרשימה או הספריה מציגות #VALUE! ערך שגיאה.
מגבלות רמת קינון נוסחה יכולה להכיל עד שמונה רמות של פונקציות מקוננות. כאשר הפונקציה B משמשת כארגומנט בפונקציה A, הפונקציה B היא פונקציה ברמה שניה. בדוגמה שלעיל, הפונקציה SUM היא פונקציה ברמה שניה מכיוון שהיא ארגומנט של הפונקציה AVERAGE. פונקציה המקוננת בתוך הפונקציה SUM תהיה פונקציה ברמה שלישית וכן הלאה.
הערות:
-
רשימות וספריות אינן תומכות בפונקציות RAND ו- NOW.
-
הפונקציות TODAY ו- ME אינן נתמכות בעמודות מחושבות, אך נתמכות בהגדרת ערך ברירת המחדל של עמודה.
שימוש בהפניות לעמודות בנוסחה
הפניה מזהה תא בשורה הנוכחית ומציינת לרשימה או לספריה שבה יש לחפש את הערכים או הנתונים שבהם ברצונך להשתמש בנוסחה. לדוגמה, [עלות] מפנה לערך בעמודה עלות בשורה הנוכחית. אם העמודה Cost מכילה את הערך 100 עבור השורה הנוכחית, הפונקציה =[Cost]*3 מחזירה 300.
עם הפניות, באפשרותך להשתמש בנתונים הכלולים בעמודות שונות של רשימה או ספריה בנוסחה אחת או יותר. ניתן להפנות לעמודות מסוגי הנתונים הבאים בנוסחה: שורת טקסט בודדת, מספר, מטבע, תאריך ושעה, בחירה, כן/לא ומחושב.
השתמש בשם התצוגה של העמודה כדי להפנות אליה בנוסחה. אם השם כולל רווח או תו מיוחד, עליך לתחום את השם בסוגריים מרובעים ([ ]). הפניות אינן תלויות רישיות. לדוגמה, באפשרותך להפנות לעמודה מחיר יחידה בנוסחה בתור [מחיר יחידה] או [מחיר יחידה].
הערות:
-
אין באפשרותך להפנות לערך בשורה שאינה השורה הנוכחית.
-
אין באפשרותך להפנות לערך ברשימה או בספריה אחרת.
-
אין באפשרותך להפנות אל המזהה של שורה עבור שורה חדשה שנוספה. המזהה אינו קיים עדיין בעת ביצוע החישוב.
-
אין באפשרותך להפנות לעמודה אחרת בנוסחה הגורמת ליצירת ערך ברירת מחדל עבור עמודה.
שימוש בקבועים בנוסחה
קבוע הוא ערך שאינו מחושב. לדוגמה, התאריך 9/10/2008, המספר 210 והטקסט "רווח רבעוני" הם כולם קבועים. קבועים יכולים להיות מסוגי הנתונים הבאים:
-
מחרוזת (דוגמה: =[Last Name] = "Smith")
קבועי מחרוזת מוקפים במרכאות ועשויים לכלול עד 255 תווים.
-
מספר (דוגמה: =[Cost] >= 29.99)
קבועים מספריים יכולים לכלול מקומות עשרוניים ועשויים להיות חיוביים או שליליים.
-
תאריך (דוגמה: =[Date] > DATE(2007,7,1))
קבועי תאריך דורשים שימוש בפונקציה DATE(year,month,day).
-
בוליאני (דוגמה: =IF([Cost]>[Revenue], "Loss", "No Loss")
כן ולא הם קבועים בוליאניים. באפשרותך להשתמש בהם בביטויים מותנים. בדוגמה שלעיל, אם Cost גדול מ- Revenue, הפונקציה IF מחזירה Yes והנוסחה מחזירה את המחרוזת "Loss". אם Cost שווה להכנסות או קטן מהכנסות, הפונקציה מחזירה לא והנוסחה מחזירה את המחרוזת "No Loss".
שימוש באופרטורים לחישוב בנוסחה
אופרטורים מציינים את סוג החישוב שברצונך לבצע ברכיבים של נוסחה. רשימות וספריות תומכות בשלושה סוגים שונים של אופרטורים לחישוב: אריתמטי, השוואה וטקסט.
אופרטורים אריתמטיים
השתמש באופרטורים האריתמטיים הבאים כדי לבצע פעולות מתמטיות בסיסיות כגון חיבור, חיסור או כפל; כדי לשלב מספרים; או כדי להפיק תוצאות מספריות.
אופרטור אריתמטי |
משמעות (לדוגמה) |
---|---|
+ (סימן חיבור) |
תוספת (3+3) |
– (סימן חיסור) |
חיסור (3–1)שלילה (–1) |
* (כוכבית) |
כפל (3*3) |
/ (קו נטוי) |
חטיבה (3/3) |
% (סימן אחוזים) |
אחוזים (20%) |
^ |
מעריכה (3^2) |
אופרטורים של השוואה
באפשרותך להשוות בין שני ערכים בעזרת האופרטורים הבאים. כאשר שני ערכים מושווים באמצעות אופרטורים אלה, התוצאה היא ערך לוגי של כן או לא.
אופרטור השוואה |
משמעות (לדוגמה) |
---|---|
= (סימן שוויון) |
שווה ל- (A=B) |
> (סימן גדול מ) |
גדול מ- (>B) |
< (סימן קטן מ) |
קטן מ- (<B) |
>= (סימן גדול מ או שווה ל) |
גדול או שווה ל- (>=B) |
<= (סימן קטן מ או שווה ל) |
קטן או שווה ל- (<=B) |
<> (סימן שונה מ) |
שונה מ- (<>B) |
אופרטור טקסט
השתמש באמפרסנד (&) כדי לצרף או לשרשר מחרוזת טקסט אחת או יותר כדי ליצור קטע טקסט יחיד.
אופרטור טקסט |
משמעות (לדוגמה) |
---|---|
& (תו אמפרסנד) |
חיבור או שרשור של שני ערכים כדי להפיק ערך טקסט רציף אחד ("North"&"wind") |
סדר שבו רשימה או ספריה מבצעת פעולות בנוסחה
נוסחאות מחשבות ערכים בסדר מסוים. נוסחה עשויה להתחיל בסימן שוויון (=). אחרי סימן השוויון מופיעים הרכיבים שיש לחשב (האופרנדים), המופרדים באמצעות אופרטורים של חישוב. רשימות וספריות מחשבות את הנוסחה משמאל לימין, בהתאם בסדר ספציפי עבור כל אופרטור בנוסחה.
סדר קדימות לגבי אופרטורים
אם אתה משלב כמה אופרטורים בנוסחה בודדת, רשימות וספריות מבצעות את הפעולות בסדר המוצג בטבלה הבאה. אם נוסחה מכילה אופרטורים בעלי אותה קדימות — לדוגמה, אם נוסחה מכילה אופרטור כפל וגם אופרטור חילוק - רשימות וספריות מעריךות את האופרטורים משמאל לימין.
אופרטור |
תיאור |
---|---|
– |
שלילה (כמו –1) |
% |
אחוז |
^ |
העלאה בחזקה |
* וגם / |
כפל וחילוק |
+ וגם – |
חיבור וחיסור |
& |
שרשור (חיבור שתי מחרוזות טקסט) |
= < > <= >= <> |
השוואה |
שימוש בסוגריים
כדי לשנות את סדר החישוב, הקף בסוגריים את חלק הנוסחה שיש לחשב תחילה. לדוגמה, הנוסחה הבאה מפיקה 11 מכיוון שרשימה או ספריה מחשבת כפל לפני חיבור. הנוסחה מכפילה את 2 ב- 3 ולאחר מכן מוסיפה 5 לתוצאה.
=5+2*3
לעומת זאת, אם אתה משתמש בסוגריים כדי לשנות את התחביר, הרשימה או הספריה מוסיפה את 5 ו- 2 ולאחר מכן מכפילה את התוצאה ב- 3 כדי להפיק 21.
=(5+2)*3
בדוגמה שלהלן, הסוגריים מסביב לחלק הראשון של הנוסחה כופה על הרשימה או הספריה לחשב תחילה את [Cost]+25 ולאחר מכן לחלק את התוצאה סכום הערכים בעמודות EC1 ו- EC2.
=([Cost]+25)/SUM([EC1]+[EC2])