קורס אקסל – פונקציות לוגיות

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

ישנם מספר רב של פונקציית לוגיות, כגון SWITCH או IFS – (פונקציות חדשות שנכנסו באקסל 2016 – 365).

או פונקציות לוגיות נוספות כגון: IF, AND, OR, IFERROR, NOT, FALSE, ISNA, IFNA  – ועוד..

נציג כעת את מבנה הפונקציה החשובה מכולן: פונקציית התנאי – IF ,
ולאחריה שתי פונקציות, חשובות גם כן, פונקציית AND ופונקציית OR.

פונקציית IF

פונקציית IF היא פונקציית תנאי – בה שלושה ארגומנטים פשוטים:

1. Logical test

תפקידו לבדוק האם מתקיימים התנאים שהגדרנו – אם הם מתקיימים, כבר בשלב זה, היכן שרשומה באיור המילה 'לוגי' – ירשם: 'TRUE'

2. Value if true

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

3. Value if false

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

נדגים בצורה פשוטה:

לפנינו טבלה בה שלושה תלמידים קיבלו ציון. המורה מחליט לתת תוספת של 5 נקודות לכל מי שקיבל ציון נמוך מ- 80.

הוספנו עמודה – C – בה רשמנו את נוסחת התנאי.

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

בארגומנט השני רשמנו שאם התנאי יתקיים  – שהפונקציה תוסיף 5 נקודות.

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

פונקציית AND ופונקציית OR

שתי פונקציות חשובות נוספות הן פונקציית AND ופונקציית OR – פונקציות אלו חשובות מכיוון שבמקרים רבים אנו רוצים שכמה נתונים יחד יתקיימו (נוסחת AND) או לפחות חלק מהן (נוסחת OR).

ניתן לשלב אותם בתוך פונקציה אחרת, או שהן עומדות בפני עצמן – בניגוד לפונקציית ה- IF – פונקציות אלה "יודעות" להחזיר 2 תשובות בלבד:

TREU – אם הנתונים שביקשנו מתקיימים.

FALSE – אם הנתונים שביקשנו אינם מתקיימים.

בדוגמה, ביקשנו לראות האם הציון בשני המקצועות גבוה מ – 80.

בפונקציית AND אנו דורשים ששניהם יקיימו את התנאי, ואילו בפונקציית OR מספיק שאחד מהתנאים מתקיים. בשתי הנוסחאות יש מספר ארגומנטים בתפקוד זהה – וזהו התחביר שלהם: נוסחת AND: AND(B3>80,C3>80)= נוסחת OR: OR(B7>80,C7>80)=

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

פונקציות טקסט

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

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

ישנם מספר רב של פונקציית טקסט, כגון CONCAT (פונקציה חדשה שנכנסה באקסל 2016 –  365 והחליפה את נוסחת CONCATENATE המורכבת יותר).

או פונקציות טקסט נוספות כגון: FIND, LEFT, RIGHT, MID, LEN, REPT, SEARCH, REPLACE, PROPER  – ועוד..

2 דוגמאות לנוסחאות טקסט שמשתמשים בהן לעיתים קרובות (מבין אלו שעובדים באקסל עם נתונים טקסטואליים באופן שוטף) הם: נוסחת ה -LEN- שבודקת כמה אותיות יש בתא, ונוסחת -TRIM- שמסירה רווחים מיותרים. התחביר שלהם פשוט ביותר: הנוסחה הראשונה: =LEN() והנוסחה השנייה =TRIM()

דוגמה לנוסחה חשובה נוספת היא –
נוסחה לבדיקת זהות בין 2 מחרוזות טקסט: EXACT.

בדוגמה שלפנינו, רשמנו 2 מחרוזות טקסט, שבמבט ראשוני ניתן לחשוב ששניהן זהות – אך כפי שניתן לראות – נוסחת EXACT החזירה תשובת: 'TRUE' רק לשורה הראשונה – דוד.

במקרה של חיים חסרה האות י' ; במקרה של משה ישנו רווח לפני האות מ' ; ובמקרה של ישראל ישנו רווח אחרי האות ל'.

נוסחה שמשתמשים בה רבות היא נוסחת: CONCATENATE נוסחה זו, כמו נוסחאות רבות אחרות, עברה שדרוג באקסל 2016 – 365, וכעת היא קצרה יותר, ומאפשרת שרשור טווח נתונים– ונקראת: CONCAT.

התחביר של נוסחת CONCATENATE הוא:  CONCATENATE(E5," ",F5)=

חשוב!

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

הערה: ישנה דרך קלה יותר לשרשר טקסט – ע"י שימוש באופרטור השרשור: &

פונקציות תאריך ושעה

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

תאריך

תובנה זו חשובה ע"מ להצליח להבין איך כשאנו מחשבים הפרשי תאריכים, לדוגמא: ה – 15/02/2017 פחות ה – 14/02/2017 , אנו מקבלים את התוצאה: 1, הרי אם נכניס לאקסל את הנתונים: יום רביעי פחות יום שלישי לא נקבל תוצאה: יום אחד, אלא נקבל תשובה: "שגיאה בערך".

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

נכניס שלושה תאריכים ונגדיר את התאים כמספר ולא כתאריך.

ניתן לראות בדוגמה, את התאריך ה- 14/01/1900 – ולהבין כעת, כיצד כל תאריך קיבל את המספר שאנו נתקלים בו לעיתים קרובות – האקסל מחשב את התאריכים החל מה – 01/01/1900  ולכן ה – 15/02/2017 פחות ה – 14/02/2017 , נותן את התוצאה: 1 – כי 42,781 פחות 42,780 שווה: 1.

שעה

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

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

בהתבוננות על שתי השורות העליונות, שמתחת לארגומנט (עיין אשף הפונקציות), ניתן כבר כעת להבין שכל שנייה מקבלת מספר – כלומר האקסל מתחיל לספור מחצות 00:00:00 ועד השנייה לפני חצות הבא.

כדי לחדד את ההבנה נביט בדוגמה.

נעיין בשתי השורות התחתונות של הטבלה – ניתן לראות שהשנייה הראשונה שאחרי חצות קיבלה את הערך המספרי 0.00001

ואילו האחרונה שלפני חצות קיבלה את הערך המספרי 0.99999
נבין מכאן, שלמעשה מה שהאקסל עשה הוא:
לחלק את השעה ב 24 שעות שיש ביממה.

כעת, אם נביט בעמודה השלישית שהוספנו, נראה שישנה דרך קלה להמיר את השעות למספרים שמוכרים לנו – כעת ניתן גם להבין כיצד – כל מה שעשינו היה להכפיל את העמודה של השעה ב 24 – וקיבלנו 8 ולא 08:00:00 ; 7 ולא 07:00:00 וכו'.

הערה: מבחינה מתמטית 08:00 פחות 07:00 שווה 01:00 ולא 1.

ראינו לעיל, בכרטיסיית הנוסחאות > בקבוצת ספריית פונקציות – מגוון רחב של סוגי פונקציות, > הפקודה השביעית היא: תאריך ושעה.
ישנם מספר רב של פונקציית תאריך ושעה. חלקן נועדו לעזור למשתמש בחישוב הפרש זמנים, לדוגמה – DAYS או להקל על המשתמש בהמרת הזמנים כמו זו שהראינו לעיל – TIMEVALUE.
חלקן נועדו לחישוב ימים, שעות, שניות, תאריכים. אך רוב הנוסחאות מאפשרות להגדיר זמן מדויק – לדוגמא: חישוב איזה יום מימות השבוע – היה בתאריך מסוים – WEEKDAY. או לדוגמה- חישוב מספר ימי העבודה בהורדת החופשות  – WORKDAY ועוד…

פונקציות אגירה מותנית

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

נבין זאת ע"י הדוגמה הבאה:

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

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

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

נוסחה זו נקראת:  AVERAGEIF.

נוסחת AVERAGEIF, בניגוד לנוסחת הספירה – COUNTIF, מורכבת משלושה ארגומנטים. – ניצור את הנוסחה מתחת לנתונים – כלומר בתא D20 – ונסביר מה תפקיד כל ארגומנט:

א"כ, ניתן לראות בדוגמה שישנם שלושה ארגומנטים פשוטים:

1. Range

תפקידו להגדיר מהו הטווח ממנו ניקח את הקריטריון

2. Criteria

בארגומנט זה נכתוב מהו הקריטריון עליו ברצוננו לבצע את החישוב

3. Average Range

בארגומנט זה נסמן מהו הטווח בו נמצאים הערכים המספריים, מהו אנו רוצים לבצע ממוצע.

ניתן לראות, עוד לפני לחיצה על האישור שקיבלנו תוצאה: 4,225.176  – זה ממוצע השכר של מורה בכיר שהשתתף בכנס.

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

ניתן לבצע חישובי ספירה COUNTIF – בנוסחה זו מוגדרים שני הארגומנטים הראשונים בלבד

וזהו תחביר הנוסחה:

=COUNTIF (A3:A19,"מורה בכיר")

ניתן גם לבצע סיכום של הנתונים לפי הקריטריון, נוסחת: SUMIF , והתחביר יהיה זהה לנוסחת AVERAGEIF.

כמו"כ, ניתן לבצע סיכום/ ממוצע/ ספירה כשאנו רוצים יותר מקריטריון אחד: SUMIFS, COUNTIFS, AVERAGEIFS  – וכאמור באקסל 2016 – 365 – ניתן לבצע גם מינימום ומקסימום כשאנו רוצים יותר מקריטריון אחד : MAXIFS, MINIFS.

איחוד –פונקציית CONCATENATE

פונקציה שמשתמשים בה רבות היא פונקציית: CONCATENATE פונקציה זו, כמו נוסחאות רבות אחרות, עברה שדרוג באקסל 2016 – 365, וכעת היא קצרה יותר, ומאפשרת שרשור טווח נתונים– ונקראת: CONCAT.

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

התחביר של פונקציית CONCATENATE הוא:  CONCATENATE(E5," ",F5)=

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

חשוב!

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

ישנה דרך קלה יותר לשרשר טקסט – ע"י שימוש באופרטור השרשור: &

שרשור

אם ישנם נתונים הנמצאים בתאים נפרדים וארצה לחבר ביניהם אוכל השתמש באופרטור &, ע"פ אותו התחביר של פונקציית CONCATENATE לדוגמא, אם ארצה לחבר לתא אחד בעמודה C את השם הפרטי ואת שם המשפחה: התחביר יהיה:

באותו האופן ניתן לרשום נוסחאות ולשלב אותם בתחביר השרשור, וכמור בסעיף פונקציית: CONCATENATE לקנן כל פונקציה ליצירת משפט הכולל חישוב:

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

ניתן לראות שהכנסנו את פונקציית ה Average לשרשור הטקסט.

כאמור, נשתמש באותו התחביר לפונקציית ה CONCATENATE או ה CONCAT – רק שאת סימן השרשור: & יחליף הפסיק:

=CONCAT(A2," ","קיבל את הציון"," ",AVERAGE(B2:C2)," ","בממוצע")

הידעת??

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