לְהִצטַיֵן

נוסחאות ופונקציות של Excel

Excel Formulas Functions

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



מהי נוסחה?

נוסחה ב- Excel היא ביטוי שמחזיר תוצאה ספציפית. לדוגמה:

 
=1+2 // returns 3

דוגמא לנוסחה בסיסית - 1 + 3 = 3





 
=6/3 // returns 2

דוגמא לנוסחה בסיסית - 6 /3 = 2

הערה: כל הנוסחאות ב- Excel חייבות להתחיל בסימן שווים (=).



הפניות לתאים

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

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

 
=A1+A2+A3 // returns 9

נוסחה עם הפניות לתאים

שימו לב מכיוון שאנו משתמשים בהפניות לתאים עבור A1, A2 ו- A3, ניתן לשנות ערכים אלה בכל עת ו- C1 עדיין תציג תוצאה מדויקת.

כל הנוסחאות מחזירות תוצאה

כל הנוסחאות ב- Excel מחזירות תוצאה, גם כאשר התוצאה היא שגיאה. להלן נוסחה משמשת לחישוב אחוז השינוי. הנוסחה מחזירה תוצאה נכונה ב- D2 ו- D3, אך מחזירה #DIV/0! שגיאה ב- D4, מכיוון B4 ריק:

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

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

העתק והדבק נוסחאות

היופי שבהפניות לתאים הוא שהם מתעדכנים אוטומטית כאשר נוסחה מועתקת למיקום חדש. זה אומר שאתה לא צריך להזין את אותה נוסחה בסיסית שוב ושוב. במסך למטה, הנוסחה ב- E1 הועתקה ללוח עם Control + C:

פורמולה ב- E1 הועתקה ללוח

להלן: הנוסחה המודבקת לתא E2 עם Control + V. שים לב שהפניות לתאים השתנו:

נוסחה ב- E1 הודבקה ל- E2

אותה נוסחה הודבקה ל- E3. כתובות הסלולר מתעדכנות שוב:

נוסחה ב- E1 הודבקה ל- E3

הפניות יחסיות ומוחלטות

הפניות לתאים לעיל נקראות קרוב משפחה הפניות. המשמעות היא שההתייחסות היא יחסית לתא בו הוא חי. הנוסחה ב- E1 לעיל היא:

 
=B1+C1+D1 // formula in E1

פשוטו כמשמעו, פירוש הדבר הוא 'תא 3 עמודות שמאלי' + 'תא 2 עמודות שמאלי' + 'תא 1 עמוד שמאלי'. לכן, כאשר הנוסחה מועתקת לתא E2, היא ממשיכה לפעול באותו אופן.

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

 
=A1 // relative reference =$A // absolute reference

לדוגמה, במסך למטה, נרצה להכפיל כל ערך בעמודה D ב -10, המוזן ב- A1. על ידי שימוש בהתייחסות מוחלטת עבור A1, אנו 'נועלים' את ההפניה כך שלא ישתנה כאשר הנוסחה מועתקת ל- E2 ו- E3:

דוגמא ייחוס מוחלטת

להלן הנוסחאות הסופיות ב- E1, E2 ו- E3:

 
=D1*$A // formula in E1 =D2*$A // formula in E2 =D3*$A // formula in E3

שימו לב להתייחסות לעדכוני D1 בעת העתקת הנוסחה, אך ההפניה ל- A1 לעולם לא משתנה. כעת אנו יכולים לשנות בקלות את הערך ב- A1, וכל שלוש הנוסחאות מחשבות מחדש. מתחת לערך ב- A1 השתנה מ -10 ל -12:

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

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

טיפ: אתה יכול לעבור בין תחביר יחסי ומוחלט עם ה מפתח F4 .

כיצד להזין נוסחה

כדי להזין נוסחה:

  1. בחר תא
  2. הזן סימן שווה (=)
  3. הקלד את הנוסחה ולחץ על Enter.

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

הזנת נוסחה עם הפניות של נקודה ולחיצה

כל הנוסחאות ב- Excel חייבות להתחיל בסימן שווים (=). אין סימן שווים, אין נוסחה:

שכחת הזנת סימן שוויון פירושו אין נוסחה, רק טקסט

כיצד לשנות נוסחה

לעריכת נוסחה, יש לך 3 אפשרויות:

  1. בחר את התא, ערוך ב- שורת נוסחה
  2. לחץ פעמיים על התא, ערוך ישירות
  3. בחר את התא, הקש F2 , לערוך ישירות

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

וִידֵאוֹ: 20 טיפים להזנת נוסחאות

מהי פונקציה?

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

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

 
= SUM (1,2,3) // returns 6 = SUM (A1:A3) // returns A1+A2+A3

ה הפונקציה AVERAGE , כפי שהיית מצפה, מחזיר את הממוצע של הפניות הנתונות:

 
= AVERAGE (1,2,3) // returns 2

ופונקציות MIN ו- MAX מחזירות ערכי מינימום ומקסימום, בהתאמה:

 
= MIN (1,2,3) // returns 1 = MAX (1,2,3) // returns 3

Excel מכיל מאות פונקציות ספציפיות . כדי להתחיל, ראה 101 פונקציות מפתח של Excel .

טיעוני פונקציה

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

 
=FUNCTIONNAME(argument1,argument2,argument3)

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

 
= COUNTIF (range,criteria) // two arguments

במסך להלן, הטווח הוא A1: A5 והקריטריונים הם 'אדום'. הנוסחה ב- C1 היא:

 
= COUNTIF (A1:A5,'red') // returns 2

COUNTIF דורש שני ארגומנטים, טווח וקריטריונים

וִידֵאוֹ: כיצד להשתמש בפונקציה COUNTIF

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

 
= YEARFRAC (start_date,end_date,[basis])

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

כיצד להזין פונקציה

אם אתה יודע את שם הפונקציה, פשוט התחל להקליד. להלן השלבים:

1. הזן סימן שווים (=) והתחל להקליד. Excel יפרט את הפונקציות התואמות תוך כדי הקלדה:

תוך כדי הקלדה, Excel יציג פונקציות תואמות

כאשר אתה רואה את הפונקציה שאתה רוצה ברשימה, השתמש במקשי החצים כדי לבחור (או פשוט המשך להקליד).

2. הקלד את מקש Tab כדי לקבל פונקציה. Excel ישלים את הפונקציה:

הקש Tab כדי להיכנס לפונקציה שנבחרה

3. מלא את הארגומנטים הנדרשים:

הזן ארגומנטים נדרשים

4. הקש Enter כדי לאשר את הנוסחה:

הקש Enter כדי לאשר ולהיכנס לפונקציה

שילוב פונקציות (קינון)

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

צריך נוסחה לחישוב הגיל הנוכחי ב- B2

ה פונקציית YEARFRAC יחשב שנים עם תאריך התחלה ותאריך סיום:

YEARFRAC תחשב שנים עם תאריך התחלה ותאריך סיום

נוכל להשתמש ב- B1 לתאריך ההתחלה ולאחר מכן להשתמש ב הפונקציה TODAY לספק את תאריך הסיום:

B1 לתאריך ההתחלה, הפונקציה TODAY לאספקת תאריך הסיום

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

 
= YEARFRAC (B1, TODAY ())

פונקציות YEARFRAC ו- TODAY לחישוב הגיל הנוכחי

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

 
= INT ( YEARFRAC (B1, TODAY ()))

YEARFRAC והיום בתוך INT

כאן, הנוסחה המקורית של YEARFRAC מחזירה 20.4 לפונקציית INT, ופונקציית INT מחזירה תוצאה סופית של 20.

הערות:

  1. התאריך הנוכחי בתמונות למעלה הוא 22 בפברואר 2019.
  2. מקונן IF מתפקד הם דוגמה קלאסית של פונקציות קינון .
  3. ה הפונקציה TODAY היא פונקציית Excel נדירה ללא ארגומנטים נדרשים.

מילת מפתח: ניתן להזין את הפלט של כל נוסחה או פונקציה ישירות לנוסחה או לפונקציה אחרת.

מפעילי מתמטיקה

הטבלה שלהלן מציגה את אופרטורי המתמטיקה הסטנדרטיים הזמינים ב- Excel:

סֵמֶל פעולה דוגמא
+ חיבור = 2 + 3 = 5
- חִסוּר = 9-2 = 7
* כֶּפֶל = 6 * 7 = 42
/ חֲלוּקָה = 9/3 = 3
^ אקספונטינציה = 4 ^ 2 = 16
() סוגריים = (2 + 4) / 3 = 2

פעולות לוגיות

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

מַפעִיל מַשְׁמָעוּת דוגמא
= שווה ל = A1 = 10
לא שווה ל = A110
> גדול מ = A1> 100
< פחות מ = A1<100
> = גדול מ או שווה ל = A1> = 75
<= פחות מ או שווה ל = A1<=0

וִידֵאוֹ: כיצד לבנות נוסחאות לוגיות

סדר פעולות

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

  1. סוגריים
  2. מעריכים
  3. כפל וחילוק
  4. חיבור וחיסור
  5. שִׁרשׁוּר
  6. פעולות לוגיות

טיפ: אתה יכול להשתמש ב- להעריך תכונה לצפות באקסל לפתור נוסחאות שלב אחר שלב.

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

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

וִידֵאוֹ: הדבק קיצורי דרך מיוחדים

כיצד לחשב תשלומי ריבית בלבד באקסל

מה הלאה?

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

הסופר דייב בראנס


^