מהן נוסחאות ופונקציות ב- Excel וכיצד להשתמש בהן?

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

הנה מבט מהיר על הנושאים הנדונים כאן:

• מהי פורמולה?
• כתיבת נוסחאות אקסל
• עריכת פורמולה
• העתק / הדבק נוסחה
• הסתר נוסחאות ב- Excel
• נוסחאות Excel של עדיפות מפעיל
• מהן פונקציות ב- Excel?
• הפונקציות החשובות ביותר

מהי פורמולה?

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

נוסחאות Excel יכולות להיות מהסוגים הבאים:

כתיבת נוסחאות אקסל:

על מנת לכתוב נוסחה לתא גיליון של Excel, תוכל לעשות את הדברים הבאים:

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

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

עריכת נוסחה:

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

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

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

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

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

כפי שניתן לראות בתמונה, הנוסחה נכתבה במקור ב- A3 ואז גררתי אותה לאורך B3 ו- C3 כדי לחשב את סכום B1, B2 ו- C1, C2 מבלי לרשום באופן בלעדי את כתובות התאים.

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

הסתר נוסחאות ב- Excel:

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

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

עדיפות המפעיל של נוסחאות Excel:

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

כפי שניתן לראות בדוגמה שלעיל, יש לי נוסחה המורכבת מסוגר. אז בהתאם לכללי BODMAS, Excel ימצא תחילה את ההבדל בין A2 ל- B1 ואז הוא מוסיף את התוצאה עם A1.

מה האם 'פונקציות' ב- Excel?

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

נוסחאות ב- Excel מחולקות לקטגוריות הבאות:

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

פונקציות Excel החשובות ביותר:

להלן כמה מתפקידי Excel החשובים ביותר יחד עם התיאורים והדוגמאות שלהם.

תַאֲרִיך:

אחת מפונקציות התאריך החשובות והנפוצות ביותר ב- Excel היא פונקציית DATE. התחביר של זה הוא כדלקמן:

DATE (שנה, חודש, יום)

פונקציה זו מחזירה מספר המייצג את התאריך הנתון בתבנית זמן התאריך של MS Excel. ניתן להשתמש בפונקציה DATE באופן הבא:

דוגמא:

1. = DATE (2019,11,7)
2. = DATE (2019,11,7) -7 (מחזיר את התאריך הנוכחי - שבעה ימים)

יְוֹם:

פונקציה זו מחזירה את ערך היום של החודש (1-31). התחביר של זה הוא כדלקמן:

יום (מספר סידורי)

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

דוגמא:

1. = יום (A7)
2. = יום (היום ())
3. = DAY (DATE (2019, 11,8))

חוֹדֶשׁ:

בדיוק כמו הפונקציה DAY, Excel מספק פונקציה אחרת כלומר פונקציית MONTH כדי לאחזר את החודש מתאריך ספציפי. התחביר הוא כדלקמן:

MONTH (מספר_סידורי)

דוגמא:

1. = חודש (היום ())
2. = חודש (DATE (2019, 11,8))

למה משמשת מסגרת האביב

אֲחוּזִים:

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

אחוז = (חלק / שלם) x 100

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

• בחר בתא שבו ברצונך להציג את התוצאה
• הקלד '='
• לאחר מכן, הקלד את הנוסחה כ- A1 / A2 ולחץ על Enter
• מקבוצת המספרים של כרטיסיית הבית בחר בסמל '%'

אם:

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

IF (logical_test, value_if_true, value_if_false)

כאן, מבחן הגיון הוא התנאי שיש לבדוק

דוגמא:

• הזן את הערכים שיש להשוות
• בחר בתא שיציג את הפלט
• הקלד את שורת הנוסחה '= IF (A1 = A2,' כן ',' לא ')' ולחץ על Enter

VLOOKUP:

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

VLOOKUP (lookup_value, table_array, col_index, num_range, lookup)

איפה,

ערך בדיקה הוא הערך שיש לחפש

מערך שולחן היא הטבלה שיש לחפש

col_index הוא העמודה שממנה יש לאחזר את הערך

טווח_תצוגה (אופציונלי) מחזיר TRUE למשך כ. התאמה ו- FALSE להתאמה מדויקת

דוגמא:

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

מס הכנסה:

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

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

סְכוּם:

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

SUM (מספר 1, מספר 2, ...)

מוסיף אליו את כל המספרים שצוינו כפרמטר.

דוגמא:

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

רבית דרבית:

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

FV (קצב, nper, pmt, pv, סוג)

על מנת לחשב את התעריף, יהיה עליכם לחלק את התעריף השנתי למספר התקופות, כלומר שיעור / תקופות שנתיות. מספר התקופות או nper מחושב על ידי הכפלת המונח (מספר שנים) עם התקופות כלומר מונח * תקופות. pmt מייצג תשלום תקופתי ויכול להיות כל ערך כולל אפס.

שקול את הדוגמה הבאה:

בדוגמה שלעיל, חישבתי את הריבית המורכבת עבור $ 500 בשיעור של 10% למשך 5 שנים והנחתי שערך התשלום התקופתי הוא 0. שימו לב שהשתמשתי במשמעות -B1, 500 דולר נלקחו ממני.

מְמוּצָע:

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

ממוצע (מספר 1, מספר 2, ...)

דוגמא:

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

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

לספור:

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

COUNT (ערך 1, ערך 2, ...)

דוגמא:

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

עָגוֹל:

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

ROUND (מספר, מספר_ספרות)

דוגמא:

מציאת ציון:

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

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

= IF (G19> 90, 'A', (IF (G19> 75, 'B', IF (G19> 60, 'C', IF (G19> 40, 'D', 'F'))))

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

דַרגָה:

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

RANK (נ'צ, מספר, סדר)

דוגמא:

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

COUNTIF:

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

COUNTIF (טווח, קריטריונים)

דוגמא:

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

אינדקס:

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

INDEX (מערך, שורה_מספר, עמודה_מספר) אוֹ

INDEX (הפניה,שורה_מספר, טור_עמוד, אזור_מספר)

פונקציית האינדקס פועלת באופן הבא עבור ה- מַעֲרָך טופס:

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

פונקציית האינדקס פועלת באופן הבא עבור ה- התייחסות טופס:

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

דוגמא:

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

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

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

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

כדי לקבל ידע מעמיק על כל טכנולוגיות המגמה יחד עם היישומים השונים שלה, אתה יכול להירשם לשידור חי עם תמיכה 24/7 וגישה לכל החיים.