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

סיכום אקסל  סיכום 1 קורס האוניברסיטה הפתוחה

מטרת האקסל:

א.      יצירת טבלאות

ב.      גיליון אלקטרוני (חישובים ופונקציות)

ג.        תוכנה סטטיסטית.

התייחסות כללית

• תא
  • כל מלבן נקרא תא
  • לכל תא יש שם (עמודה+ שורה)
  • שם התא שנמצאים בו נמצא גם בפינה השמאלית עליונה למטה

• שורת הנוסחאות נמצאת למעלה:     שורת הנוסחאות =

• באופן אוטומטי יש שלושה גליונות- אפשר להוסיף עוד מספר רב של גליונות
  • מינימום גליונות =1

כתיבת נוסחה

• למשל תרגיל 1/ עמודה G תא G3

=C3+D3+E3+F3

• דגש:
  • הסימן "= " הוא מחוייב המציאות ויהיה על זה דגש במבחן

• • הסימן " + " זהו אופרטור מתמטי
  • הסימן V (או enter ) פירושו אישור
  • הסימן X מוחק

כתובת יחסית= מונח

• נניח שסימנתי תא להעתקה (למשל מהתרגיל הראשון/ את מה שרשמתי בתא G3 גררתי לאורך כל העמודה עד G9)
• כשהאקסל רואה את הסימן: " = " הוא יודע שמדובר בנוסחה
• ואז כשאני גורר (גרירת התא), הוא גורר יחסית לנקודת היציאה
  • כלומר
    • כשאני גורר לשורה חדשה, הוא מתאים שורה
    • כשאני גורר לעמודה חדשה, הוא מתאים לעמודה.

הסבר כיצד להפוך כתובת יחסית לכתובת קבועה

• מוסיפים את סימן ה $ לפני החלק בכתובת שרוצים שיהפוך להיות קבוע
  • קיבוע שורה: c$10
  • קיבוע עמודה: $c10
  • קיבוע תא: $c$10

• • אפשר לכתוב $ גם בעזרת F4

שימוש בתא עזר

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

אפשרות לתת שם לתת שם לתא

• לוקחים תא ונותנים לו שם
• (משל בתרגיל 2 תא K11)
• הוספה/שם/ הגדרה: ורושמים למשל:  קריטריון_מעבר
  • לשים לב שאסור שיהיה רווחים.
• היתרונות של נתינת שם לתא הוא
  • שאפשר לכתוב את השם שלו בחישובים השונים מבלי צורך למצוא אותו,
  • וכן גם שדרך הלחיץ העליון השמאלה אפשר להגיע אליו מיד
• למשל שימוש בשם קריטריון _מעבר נמצא כבר בתא M3

הפונקציות השונות

• (אפשר לפתוח דרך fx, וגם דרך הוספה / פונקציה' וגם דרך SHIFT+F3)

כתיבת פונקציה

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

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

חלקי הפונקציה

• (ארגומנטים) שם הפונקציה =
• בארגומנטים הכוונה לאיזה תאים אני מעוניין להפעיל את הפונקציה
• לדוגמה פונקצית 1: SUM
• (ארגומנטים) SUM=

• (באופן כללי כל מחרוזת צריכה גרשיים: "  "

הנוסחאות עצמן

1) SUM-

חישוב סה"כ ניקוד מצטבר

• נמצאת במתמטיקה טריגונומטריה
• הפונקציה מסכמת את כל הערכים הנתונים לפונקציה כארגומנטים (יש לה לחצן כלים å)

• ניסוח:  (ארגומנטים) SUM=

בהתייחס לתרגיל 1 תא H3

• • בארגומנטים יכול להיות:
    • • c3,d3,e3,f3
      • וגם c3:f3
• במבחן נדרש לרשום: =SUM (C3:F3)

• • אפשר לכתוב את הפונקציה בשתי צורות
    • לכתוב ישירות בתא או למעלה בשורות הכתובת של התא
    • או דרך Fx

2) AVERAGE -

חישוב ממוצע הציונים

• נמצאת במתמטיקה וטריגונומטריה
• הפונקציה יודעת לזהות תאים רקים ולא מחשבת אותם בממוצע (למשל שורה 5 ו 8)
  • (כלומר היא מחזירה את הערך הממוצע של קבוצת תאים)

• • בהתייחס לתרגיל 1 תא I3

• • AVERAGE(C3:F3)=

3) MAX

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

• • בהתייחס לתא C11

• • =MAX(c3:c9)

4) MIN

• חישוב הציון הנמוך
  • (מחזירה את הערך הקטן ביותר מתוך קבוצת נתונים)
• נמצא בסטטיסטיקה

• • בהתייחס לתא C12

• • = MIN(D3:D9)

5) IF

למשל חישוב האם התלמיד עבר או נכשל על פי קריטריון (מעל 75 עובר)

• נמצאת בקטיגורית לוגיקה
• הפונקציה מחזירה ערך מסוים כאשר התנאי הנבון הוא TRUE וערך אחר כאשר התנאי הנבחן הוא FALSE
• פונקצית IF (בהתייחס לתרגיל הראשון לתא K3)

75 <I3	תנאי
"עבר"	מתקיים
"נכשל"	לא מתקיים

• (בפונקציות 1-4 היינו צריכים לתת את טווח הארגומנטים)
• למבחן
• ("נכשל","עבר",IF(I3>75

• תנאי: I3>75  
• מתקיים= "עבר"
• לא מתקיים= "נכשל"
• (במחשב קיים באג של תצוגה)

• בהתייחס לתרגיל 2 תא M3 אחרי שהגדרנו את תא K11 – כקריטריון מעבר
  • תא M3

D3>קריטריון_מעבר	תנאי
D3+D3*$K$13	מתקיים
D3	לא מתקיים

• • בהתייחס למבחן ולממן
    • השיטה לעבוד עם תוספת אחוזים/ מעמ' וכד' היא בצורה הזאת:
      • D3+D3*$K$13
      • {ולא בצורה של (למשל מע"מ) 100*1.17}

6 ) RANK

למשל דירוג תלמידים על פי ממוצע הציונים, כך שהתלמיד בעל הממוצע הגבוה ידורג כמספר 1, והתלמיד בעל הממוצע הנמוך כמספר 7 (בסדר עולה). הדירוג מתבצע ביחס לממוצע שאר התלמידים ברשימה

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

* בהתייחס לתרגיל 2 תא N3

H3	ערך לדרוג
$H$3:$H[א1] $9צריך $  כי בגרירה האקסל ישנה את זה.	טווח
0	איך לדרג

• בנוגע ל"איך לדרג" (0 או מספר אחר)
  • הכוונה מזווית הראיה של הדירוג
    • 0 (או כלום) פירושו ש 1 זה הכי גבוהה ו7 (או מספר אחר) הכי נמוך
    • מספר אחר פירושו ש 1 זה הכי נמוך ו 7 זה הכי גבוה

• • • =RANK(H3,$H$3:$H$9,0)

7) AND

• הפונקציה נמצאת בקטיגורית לוגי
• הפונקציה מחזריה TRUE  אם כל הארגומנטים בנוסחה הם TRUE ו מחזירה FALSE   אם לפחות אחר מהארגומנטים הוא FALSE

• בהתייחס לתרגיל 2 תא N3

c3>$N$12	חשבון
D3>$N$13	אנגלית
E3>$N$14	היסטוריה
F3>$N$15	תנך

• • • במבחן צריך לדעת לרשום כך:
• =AND(C3>$N$12,D3>$N$13,E3>$N$14,F3>$N$15)

8) OR

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

• הפונקציה נמצאת בקטיגורית לוגי
• הפונקציה מחזירה FALSE אם כל הארגומנטים שלה הם FALSE ומחזירה TRUE אם לפחות אחד מהם הוא TRUE

• בהתייחס לתרגיל 2 תא P3

c3>$N$12	חשבון
D3>$N$13	אנגלית
E3>$N$14	היסטוריה
F3>$N$15	תנך

• • • במבחן צריך לדעת לרשום
• =OR(C3>$N$12,D3>$N$13,E3>$N$14,F3>$N$15)

9) NOT

חישוב האם קיבל התלמיד ציונים נמוכים בכל המקצועות

• הפונקציה נמצאת בקטיגורית לוגי
• היא מחזירה את הערך false  עבור ארגומנט true וההפך

• בהתייחס לתרגיל 2 תא Q3
  • • P3

• • • במבחן צריך לדעת לרשום
      • NOT(P3)

• • באופן כללי:   NOT(NOT(P3))=P3

10) COUNTIF

למשל חישוב סה"כ התלמידים שנכשלו

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

• • (הפונקציות הקודמות חישוב מספרים
  • בפונקציה זאת מחשבים גם מילים)

• בהתייחס לתרגיל 2 תא C13

K3:K9	טווח
"נכשל"	קריטריון

• • במבחן צריך לרשום
    • =COUNTIF(K3:K9,("נכשל"

• בהתייחס לתרגיל 2 תא C14

• • אפשרות ראשונה

H3:H9	טווח
<75	קריטריון

• • • ואז במבחן צריך לרשום:
    • =COUNTIF(H3:H9,">75")

• • אפשרות שנייה (לשים לב ל & ולמרכאות)

H3:H9	טווח
<75	קריטריון

• • • ואז במבחן צריך לרשום:
    • =COUNTIF(H3:H9,">"&קריטריון_מעבר)
    • או
    • =COUNTIF(H3:H9,">"&K11)

• • • מצב כזה נקראה שירשור פונקציה

• • בשני המקרים לשים לב למיקומי המרכאות
  • ואותו דבר נכון גם ל SUMIF

11) SUMIF

חישוב סה"כ הניקוד שצברו התלמידים שנכשלו

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

• בהתייחס לתרגיל 2 תא C15

J3:J9	טווח
"נכשל"	קריטריון
G3:G9	מה לסכם

• • במבחן צריך לרשום
    • • =SUMIF(J3:J9,"נכשל",G3:G9)

12) COUNTA

למשל חישוב סה"כ התאים המלאים

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

• בהתייחס לתא c16  עד תא F16

C3:C9

טווח

• • • מבחן צריך לכתוב:
    • =COUNTA(C3:C9)

13) COUNT

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

• בהתייחס לתא C17  עד תא F17

C3:C9

טווח

ההבדל בין COUNT לבין COUNTA

• COUNT  מסכמת כמה תאים עם מספרים יש בטווח (ולא שום דבר אחר)
• COUNTA  מסכמת כמה תאים מלאים נמצאים בטווח (לא מבדילה אם בתא יש  מספר, מחרוזת וכד')

14) ROUND

• הפונקציה נמצאת במתמטיקה טריגונומטריה
• בהתייחס לתא C12  בתרגיל 3 / ריכוז לפי שנים

C11	מספר/ תא לעיגול
0	איך לעגל

• דוגמא להסברת העניין:
  • את המספר 85.6 אפשר לעגל ל

86                     או 90

• העיגול הוא תמיד כלפי מעלה (במקרה זה)
  • • עם נכתוב 0 הוא יעגל ל 86
    • עם נכתוב +1 הוא יעגל ל 85.6
    • עם נכתוב -1   הוא יעגל לשמאל כלומר 90
    • עם נכתוב -2  הוא יעגל לשמאל פעמים כלומר 100

• דוגמא נוספת
  • את המספר 83.4
    • עם נכתוב 0 הוא יעגל ל 83
    • עם נכתוב +1 הוא יעגל ל 83.4
    • עם נכתוב -1  הוא יעגל ל 80
    • עם נכתוב -2  הוא יעגל ל 100

• • • =ROUND(D11,0)

15) MEDIAN

למשל חישוב הציון החציוני שבין ציוני התלמידים

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

C4:C10

הטווח

• דוגמא: תא C13
• =MEDIAN(D4:D10)

16) MODE

למשל חישוב הציון השכיח שבין ציוני התלמידים

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

C4:C10

הטווח

• דוגמא: תא C14
  • (אם לא קיים הוא רושם באנגלית סולמית- לא זמין

• • =MODE(D4:D10)

17) if מקונן

למשל

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

• התייחסות לתא O4

IF

	תנאי
שומר על מגמה	אם אמת
" "	אם לא אמת

• ממלאים קודם שני התאים : (אם אמת, ואם לא אמת) ועבור התנאי פותחים פונקציה חדשה- בהקשר זה AND
• לאחר מכן מתקבל התא של התנאי ב IF

AND

F4>$F$17	תנאי
G4>F17	תנאי
H4>$F$17	תנאי

• ואז מתקבל -צריך לדעת למבחן
  • =IF(AND(F4>$F$17,G4>F17,H4>$F$17, ( "שומר על מגמה", " ")

• במבחן כדאי לשרטט קודם את שתי הטבלאות, ורק אז להשלים לביטוי השלם

IF מקונן 1 – ( פונקצית IF בתוך IF)

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

• בהתייחס לתא R3

IF

H3>=92	תנאי
H3 + H3*$P$12	מתקיים
IF	לא מתקיים

IF

H3>=85	תנאי
H3 + H3*$P$14	מתקיים
H3	לא מתקיים

• IF(H3>=92, H3+H3*$P$12,IF(H3>=85,H3+H3*$P$14,H3))

• (אפשר גם לחשב באופן אחר מלמטה למעלה)

IF

H3>=85	תנאי
IF	מתקיים
H3	לא מתקיים

IF

H3>=92	תנאי
H3 + H3*$P$12	מתקיים
H3 + H3*$P$14	לא מתקיים

• =IF(H3>=85, IF(H3>=92, H3 + H3*$P$12, H3 + H3*$P$14),H3)
• (אפשר לעשות עד 7 IF אחד בתוך השני)

IF מקונן 2 (פוקנצית AND ו OR בתוך IF)

למשל חישוב האם התלמיד חזק בהיסטוריה (>93) או בחשבון (>84) וגם בתנך (>70) על פי קריטריונים המוגדרים (בתחתית טבלת המודל)

בנוגע לתא T3 דרך 1 (של המורה) – זוהי שאלה שהיתה במבחן

IF

OR	תנאי
" תלמיד חזק "	מתקיים
" "	לא מתקיים

OR

E3>$N$14	תנאי
AND	תנאי

AND

C3>$N$12	תנאי
F3>$N$15	תנאי

• =IF(OR(E3>$N$14,AND(C3>$N$12, F3>$N$15)), " תלמיד חזק "," ")

בנוגע לתא T3 דרך 2 (שזוהי למעשה דרך 1)

E3>$N$14	תנאי
" תלמיד חזק  "	מתקיים
IF	לא מתקיים

C3>$N$12	תנאי
IF	מתקיים
"  "	לא מתקיים

F3>$N$15	תנאי
"תלמיד חזק "	מתקיים
" "	לא מתקיים

=IF(E3>$N$14," תלמיד חזק  ",IF(C3>$N$12,IF(F3>$N$15,""תלמיד חזק ," "),"  "))

• בגלל עברית זה מתהפך באקסל

=IF(F3>$N$14," תלמיד חזק  ",IF(D3>$N$12,IF(G3>$N$15,"תלמיד חזק"," "),"  "))

18) MATCH

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

• לדוגמא: בהתייחסות לתרגיל 4 שנת 2002 תא  G18

C18	ערך לחיפוש
G3:G9	טווח
0	איך לחפש

• • • • למבחן
  • =MATCH(C18,G3:G9,0)

• • • • במקום C18  היה אפשר לרשום G4 אבל לא כדאי כי אם הוא ישתנה (בנתונים) אז זה יבלבל הכל
      • לפיכך בתור כלל עבודה אין להשתמש בקריטריון בנתון שנמצא בנתונים- אלא יש להשתמש בתא עזר

בנוגע לאיך לחפש

• • • 0 פירושו לחפש מספר מתוך הרשימה- ומה שיהיה במבחן
    • ואז יש שתי אפשרויות:
      • • לחפש מספר הכי קרוב למספר ברשימה (אם לא קיים)
        • +1   פירושו הכי קרוב אשר קטן ממנו
        • -1  פירושו הכי קרוב גדול ממנו
        • אבל בכל מקרה בשני המקרים הנ"ל צריך רשימה ממויינת
    • למבחן תמיד נכתוב 0

• בכדי למצוא את המיקום ברשימה של ממוצע הציונים הגבוה ביותר (תא G19)

Max(H3:H9)	ערך לחיפוש
H3:H9	טווח
0	איך לחפש

• למבחן:
  • =MATCH(MAX(H3:H9),H3:H9,0)

19) INDEX

• נמצא בהפניות ובירור מידע
• הפונקציה הנ"ל נותנת את הערך שמופיע בנקודה המופיעה בין השורה לעמודה

• למשל תתן את הערך X שנבקש ממנה שנשאל מה נמצא  בשורה 2 עמודה 3

	עמוד 3
שורה 2	X

• בנוגע לתא G20

B3:B9	טווח
G19	מספר שורה

• ואז נקבל:

• =INDEX(B3:B9,MATCH(MAX(H3:H9),H3:H9,0))

• כי בתוך G19  יש:

• =INDEX(B3:B9,MATCH(MAX(H3:H9),H3:H9,0))

• הסבר כיצד להתמודד עם שאלה דומה במבחן

• יש לצייר כפי שציירנו בכיתה:

B3:B9	טווח
	מספר שורה

Max(H3:H9)	ערך לחיפוש
H3:H9	טווח
0	איך לחפש

השילוב של …INDEX ו MATCH

• זהו שילוב מאוד נפוץ
  • למשל עבור תא E3 בתרגיל המשתלה
• =INDEX($H$3:$H$11,MATCH(C3,$G$3:$G$11,0))*D3
  • • לזכור של INDEX   יש שני ערכים
    • ול MATCH יש שלושה ערכים שהשלישי הוא תמיד במבחן 0

20) LOOKUP

• יש דרך אחרת (יותר קצרה) לעשות את מה שהפונקציה INDEX ו MATCH  עשו
• הפונקציה נמצאת בהפניות וברור מידע

• בהתייחס לתא G21

MAX(H3:H9)	ערך לחיפוש
H3:H9	טווח
B3:B9	לאן לקשר נתונים

• אבל הרשימה חייבת להיות ממוינת בסדר עולה מקטן לגדול (דרך סימון הנתונים, ואז נתונים/ מיון/ לפי הסדר הרצוי כלומר לפי עמודה H2:H9
• • =LOOKUP(MAX(H3:H9),H3:H9,B3:B9)

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

חזרה לעבודה עם אופיס