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

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

מהי נוסחה?

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

 
=1+2 // returns 3

 
=6/3 // returns 2

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

הפניות לתאים

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

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

 
=A1+A2+A3 // returns 9

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

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

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

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

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

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

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

אותה נוסחה הודבקה ל- 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 עם התייחסות מוחלטת , ניתן לשנות את הערך בכל עת וכל הנוסחאות הקשורות יעודכנו באופן מיידי.

טיפ: אתה יכול לעבור בין תחביר יחסי ומוחלט עם ה מפתח 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

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

 
= YEARFRAC (start_date,end_date,[basis])

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

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

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

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

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

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

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

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

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

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

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

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

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

 
= YEARFRAC (B1, TODAY ())

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

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

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

הערות:

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

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

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

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

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

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

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

סדר פעולות

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

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

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

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

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

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

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

מה הלאה?

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

• 29 טיפים לעבודה עם נוסחאות ופונקציות ( גרסת וידאו כאן )
• 500 דוגמאות לנוסחה עם הסברים מלאים
• 101 פונקציות Excel חשובות
• מדריך לכל פונקציות האקסל (עבודה בעיצומה)
• שגיאות בנוסחה של Excel (דוגמאות ותיקונים)
• קריטריונים לנוסחה - 50 דוגמאות
• נוסחאות לעיצוב מותנה
• כיצד להשתמש ב- F9 לניפוי באגים של נוסחה (וִידֵאוֹ)
• שגיאות ותיקונים בנוסחת Excel (וִידֵאוֹ)