באפשרותך להשתמש בנוסחאות ובפונקציות ברשימות או בספריות כדי לחשב נתונים במגוון דרכים. על-ידי הוספת עמודה לרשימה או לספריה, באפשרותך ליצור נוסחה הכוללת נתונים מעמודות אחרות ולבצע פונקציות לחישוב תאריכים ופעמים, כדי לבצע משוואות מתמטיות או לטפל בטקסט. לדוגמה, ברשימת משימות, באפשרותך להשתמש ב- עמודה כדי לחשב את מספר הימים שנדרש להשלמת כל פעילות, בהתבסס על העמודות תאריך התחלה ותאריך השלמה.
הערה: מאמר זה מתאר את המושגים הבסיסיים הקשורים לשימוש בנוסחאות ובפונקציות. לקבלת מידע ספציפי אודות פונקציה מסוימת, עיין במאמר אודות פונקציה זו.
במאמר זה
מבט כולל על נוסחאות
נוסחאות הן משוואות המבצעות חישובים על ערכים ברשימה או בספריה. נוסחה מתחילה בסימן שוויון (=). לדוגמה, הנוסחה הבאה מכפילה את התוצאה 2 ב- 3 ולאחר מכן מוסיפה 5 לתוצאה.
=5+2*3
באפשרותך להשתמש בנוסחה בנוסחה מחושבת עמודה לחשב ערכי ברירת מחדל עבור עמודה. נוסחה יכולה להכיל פונקציות, עמודה, אופרטורים ו קבועים, כמו בדוגמה הבאה.
=PI()*[Result]^2
|
רכיב |
תיאור |
|---|---|
|
פונקציה |
הפונקציה () PI מחזירה את הערך של pi: 3.141592654. |
|
Reference (או עמודה שם) |
[Result] מייצג את הערך בתיבת הדו-עמודה עבור השורה הנוכחית. |
|
קבוע |
מספרים או ערכי טקסט שהוזנו ישירות בנוסחה, כגון 2. |
|
אופרטור |
האופרטור * (כוכבית) מכפיל, ואופרטור ^ (caret) מעלה מספר לעוצמה. |
נוסחה עשויה להשתמש באחד או יותר מהרכיבים מהטבלה הקודמת. להלן כמה דוגמאות של נוסחאות (לפי סדר המורכבות).
נוסחאות פשוטות (כגון =128+345)
הנוסחאות הבאות מכילות קבועים ואופרטורים.
|
דוגמה |
תיאור |
|---|---|
|
=128+345 |
הוספת 128 ו- 345 |
|
=5^2 |
ריבועים 5 |
נוסחאות המכילות עמודה (כגון =[Revenue] >[Cost])
הנוסחאות הבאות מפנות לעמודות אחרות באותה רשימה או ספריה.
|
דוגמה |
תיאור |
|---|---|
|
=[Revenue] |
שימוש בערך בתיבת הדו-עמודה. |
|
=[Revenue]*10/100 |
10% מהערך בתיבת הדו-עמודה. |
|
=[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 אם העלות גדולה מההכנסות. אחרת, הפונקציה מחזירה אישור. |
|
=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.
עם הפניות, באפשרותך להשתמש בנתונים הכלולים בעמודות שונות של רשימה או ספריה בנוסחה אחת או יותר. ניתן להפנות לעמודות מסוגי הנתונים הבאים בנוסחה: שורת טקסט בודדת, מספר, מטבע, תאריך ושעה, בחירה, כן/לא ומחושבת.
השתמש בשם התצוגה של עמודה כדי להפנות אליו בנוסחה. אם השם כולל רווח או תו מיוחד, עליך להקיף את השם בסוגריים מרובעים ([ ]). הפניות הן לא תלויות רישיות. לדוגמה, באפשרותך להפנות אל הנוסחה מחיר יחידה עמודה בנוסחה בשם [Unit Price] או [unit price].
הערות:
-
לא ניתן להפנות לערך בשורה שונה מהשורה הנוכחית.
-
לא ניתן להפנות לערך ברשימה או בספריה אחרת.
-
לא ניתן להפנות אל המזהה של שורה עבור שורה שנוספה לאחרונה. המזהה עדיין אינו קיים בעת ביצוע החישוב.
-
לא ניתן להפנות עמודה בנוסחה היוצרת ערך ברירת מחדל עבור עמודה.
שימוש בקבועים בנוסחה
קבוע הוא ערך שלא מחושב. לדוגמה, התאריך 9/10/2008, המספר 210 והטקסט "רווח רבעוני" הם כולם קבועים. קבועים יכולים להיות מסוגי הנתונים הבאים:
-
מחרוזת (דוגמה: =[שם משפחה] = "Smith")
קבועי מחרוזת מוקפים במרכאות ועשויים לכלול עד 255 תווים.
-
מספר (דוגמה: =[Cost] >= 29.99)
קבועים מספריים יכולים לכלול מקומות עשרוניים ועשויים להיות חיוביים או שליליים.
-
תאריך (דוגמה: =[Date] > DATE(2007,7,1))
קבועי תאריך דורשים את השימוש בפונקציה DATE(year,month,day).
-
Boolean (דוגמה: =IF([Cost]>[Revenue], "Loss", "No Loss")
כן ולא הם קבועים בוליאניים. באפשרותך להשתמש בהם בביטויים מותנים. בדוגמה שלעיל, אם Cost גדול מ- Revenue, הפונקציה IF מחזירה כן, והנוסחה מחזירה את המחרוזת "Loss". אם Cost שווה ל- Revenue או קטן מ- Revenue, הפונקציה מחזירה לא והנוסחה מחזירה את המחרוזת "No Loss".
שימוש באופרטורים לחישוב בנוסחה
אופרטורים מציינים את סוג החישוב שברצונך לבצע ברכיבים של נוסחה. רשימות וספריות תומכות בשלושה סוגים שונים של אופרטורים לחישוב: אריתמטי, השוואה וטקסט.
אופרטורים אריתמטיים
השתמש באופרטורים האריתמטיים הבאים כדי לבצע פעולות מתמטיות בסיסיות כגון חיבור, חיסור או כפל; כדי לשלב מספרים; או כדי להפיק תוצאות מספריות.
|
אופרטור אריתמטי |
משמעות (לדוגמה) |
|---|---|
|
+ (סימן חיבור) |
חיבור (3+3) |
|
– (סימן חיסור) |
חיסור (3-1) שלשליות (–1) |
|
* (כוכבית) |
כפל (3*3) |
|
/ (קו נטוי) |
חטיבה (3/3) |
|
% (סימן אחוזים) |
אחוז (20%) |
|
^ |
מעריכים (3^2) |
אופרטורים של השוואה
באפשרותך להשוות בין שני ערכים בעזרת האופרטורים הבאים. כאשר שני ערכים מושווים באמצעות אופרטורים אלה, התוצאה היא ערך לוגי של כן או לא.
|
אופרטור השוואה |
משמעות (לדוגמה) |
|---|---|
|
= (סימן שוויון) |
שווה ל- (A=B) |
|
> (סימן גדול מ) |
גדול מ- (A>B) |
|
< (סימן קטן מ) |
קטן מ- (A<B) |
|
>= (סימן גדול מ או שווה ל) |
גדול או שווה ל( A>=B) |
|
<= (סימן קטן מ או שווה ל) |
קטן או שווה ל( A<=B) |
|
<> (סימן שונה מ) |
לא שווה ל- (A<>B) |
אופרטור טקסט
השתמש בא ampersand (&) כדי לצרף או לשרשור מחרוזות טקסט אחת או יותר כדי להפיק קטע טקסט בודד.
|
אופרטור טקסט |
משמעות (לדוגמה) |
|---|---|
|
& (תו אמפרסנד) |
חיבור או חיבור, שני ערכים כדי להפיק ערך טקסט רציף אחד ("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])
