סיכום 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  קודם מוצאים את מספר השורה ואחרי זה מתייחסים לטווח- אולם הנתונים צריכים להיות מסודרים (דרך המיון)

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

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

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

כתיבת תגובה

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

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s