שם הכותב: תאריך: 02 יוני 2013

יישומי המחשב בניהול שיעור 12, 2.6.2013, מרצה: ד"ר יוסי רענן

הגרלות:
פונקציית
RAND()  = פונקציה ללא ארגומנטים (כמו פונקציית TODAY).

מייצרת מספר אקראי בין 0 ל-1 ß התפלגות אחידה.

  • פונקציית NOW – משתנה בהתאם לכל עדכון, כך גם פונקציית RAND, משתנה בהתאם לכל עדכון ומאחר וזה רנדומלי.
  • אם, למשל, נכפול את הפונקציה RAND ב-40 – נקבל מספרים בין 0-40
  • אם נציב שני מספרים (A,B) – A  הקטן, B  הגדול. יכול להיות שיהיו גם מספרים שליליים.
  • מהו אורך הקטע בין A ל-B  ß A-B>0

כעת, בפונקציית RAND : 0<RAND *(B-A)<B-A   יצא לנו מספר אקראי בין הגבולות האלה,

אם נוסיף A  לכל האגפים – A< RAND* (B-A)+A<B מספר אקראי בין A ל-B

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

A=-35

B=58

b-a = 93

-35 < rand*93 -35 <58

  • כאשר עושים "העתק הדבק" –  יש לנו אפשרות שנראית כך ß 3 2 1ß העתקת הערכים ללא הנוסחה! כך ניתן לגרום לפונקציה להפסיק להשתנות עם כל עדכון שלנו באקסל.

פונקציית INT(x)

תוציא ממספר X את הערך המספרי הגדול ביותר שהינו הקטן או שווה ל-X (צריך להיות מספר שלם).

למשל – int(4.5)=4

Int(-4.01)=-5

כלומר, מספר חיוביים ß למטה, מספרים שליליים ß למעלה

זהו מין סוג של עיגול שמטרתו להגיע למספר הקטן הקרוב.

  • כעת נוסיף את פונקציית INT  על פונקציית RAND , נוסיף 1+ כדי לקבל  מספרים שלמים ולא עשרוניים.

INT( rand* (B-A)+1)+A)

נקבל מספרים שלמים בין 0 ל-100 –  כאשר a=0, b=100.

לדוגמה:

מספר

A1

סניף

B1

1

חיפה

2

ת"א

3

ראשון לציון

4

קריית גת

5

אשקלון

6

עומר

7

A7

נתיבות

B7

ß עלינו להגריל את הסניף שיזכה, ולהגיע למספר אקראי בין 1 ל-7

A=1

B=7

ׂint( rand(B-A+1)+A)= 7J5

כעת, נרצה לדעת מה הוא סניף 7:

VLOOKUP(J5,A1:B7,2)

  • Rand מעגל כלפי מטה, כדי להגיע גם למספר הקצה המקסימלי נוסף 1.
  • רוצים להגביל בין 40 ל-100: הגבלת ערכים

סרגל נתונים ß אימות נתוניםß בוחרים את הטווח אותו נרצה להגביל ß

–          הגדרות: אפשר – בוחרים אופציה, נבחר כעת מס' עשרוני

נתונים – גם כאן בוחרים באופציה, נבחר כעת בין התא 40 מקובע לבין התא 100 מקובע.

–          הודעת קלט + התראת שגיאה – רשות

אם רשמנו ערך שלא מופיע בטווח – תופיע לנו הודעת שגיאה.

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

  •  הצגת נתונים בסרגל נתונים "בית" ß עיצוב מותנה ß יש לנו ממד של הצגת נתונים

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

נוכל כמובן לקבוע אילו נתונים לראות מסומנים – לדוגמה ערכים בין 0 ל-40. עיצוב מותנה ß "כלל חדש"ß עצב רק תאים המכיליםß ונוכל לבחור "בין לבין", "גדול מ-", "קטן מ-"… ß נרשום את הערכים הרצויים לי ß עיצוב ß בוחרים עיצוב.

חישובים מותניים – גרסת "לייט"

Dcount, Dsum, Daverage – אלו הן פונקציות כבדות, על כן פיתחו לפונקציות אלו פונקציות מקבילות קלילות יותר:

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

  • Range– הטווח של עמודת סניפים
  • Criterion– התא של "תל אביב"

= CountIF(b2:b1001,n3)

דוגמה נוספת – נרצה לדעת כמה הכנסות נטו יש מעל 5000 ₪

= CountIF(k2:k1001,>5000)

לא חייב לבנות תא עזר ל-5000 ₪, אלא ניתן לרשום ישר בפונקציה.

2)       SumIF – סכימה של מספרים מסוימים מתוך עמודה, לדוגמה – לסכום רק את המשכורות

  • Range – טווח ממנו נשאב את הנתונים
  • Criteria – קריטריון, מתייחס לנתונים אותם בחרנו בשורה הראשונה
  • Sum_Range – תחום הסיכום, כאשר נרצה קריטריון עבור טווח מסוים אך לעשות סיכום של טווח אחר.

= SumIF(k2:k1001,>5000)

דוגמה נוספת – כמה הכניסו כסף בסניף ת"א ?

Range= b2:b1001, עמודת הסניף

Criteria= N3, תל אביב

Sum range= k2:k1001, עמודת הכנסה

3)       AverageIF –

AverageIF = SumIF / CountIF  : עם אותם קריטריונים

  • Range – טווח ממנו נשאב את הנתונים, תחום לקריטריון
  • Criteria – קריטריון
  • Average_Range – תחום הממוצע

תרגול –  הגשת התרגיל מודל 16.6

מבחן שעתיים, 7 שאלות, חומר פתוח

איך מזהים על מה מדובר בכל שאלה?

בגיליון "עובדים"

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

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

         א.         החברה מעוניינת לשלוח שישה עובדים, שהינם ממחלקת "תכנון" ובתפקיד "עובד", או עובדים בני  40 ומעלה (מכל המחלקות ובכל התפקידים), לכנס מקצועי בארצות הברית.
עובדי המחלקה נבחרים ע"י הנהלת החברה באופן אקראי. ß מרמז לנו על יצירת קריטריונים למיון נתונים, עמודת תאריך לידה = G

מחלקה

תפקיד

תכנון

עובד

Year(today())- year(G2) >=40

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

(הערה: אותו עובד יכול להופיע פעמיים בשליפה). ß מרמז לנו על RAND

סינון מתקדם – הטבלה, סימון קריטריונים, העתק אל ..

כעת, נצטרך להגריל שמות אקראיים :

פונקציית RAND  יכולה להגריל רק מספרים!!!!  נרצה לשלוף מכל הרשימה שנוצרה לנו לפי הסינון המתקדם (עובד ממחלקת תכנון או עובד בן 40 ומעלה) –

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

מספר עובד

שם פרטי

שם משפחה

Vlookup(int(rand()*(max-min+1)+min),H,1)

Rand()- הגרלת מספר עובדים

Max-min+1 = עמודת מספרי עובדים מקובע פחות עמודת מספרי עובדים מקובע

H = עמודת מספר עובד

1= עמודה מספר 1

עכשיו נרצה לשלוף = שוב vlookup, תחת הכותרת שם פרטי

שורה ראשונה = עמודת מספר עובד (ללא כותרת!!) מקוב

שורה שנייה  טבלת הסינון המתקדם, הטווח

שורה שלישית = עמודה 2 (=שם פרטי)

שורה רביעית = 0, כי מדובר בשליפת מילים

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

 

טבלת עזר "נתונים לחישוב שכר"

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

=year(today())-year(h2)

H2= תחילת עבודה

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

חישוב – כמה כסף מגיע לכל עובד? לפי בסיס חודשי + תוספת וותק חודשית*מס' השנים שהוא עובד

=vlookup( *j2*12

  1. שורה ראשונה = מחלקה

שורה שנייה = טבלה לנתוני חישוב שכר, מקובע

שורה שלישית = 2, עמודה 2 של שכר בסיס חודש

שורה רביעית =  0 , שהאקסל לא תיקח נתוני בינים

  1. שורה ראשונה = מחלקה

שורה שנייה = טבלה לנתוני חישוב שכר, מקובע

שורה שלישית=3, עמודה שלישית של תוספת וותק לחודש

       שורה רביעית  = 0

J2 = מספר שנות וותק

למה כפול 12? לכפול חודשים ב-12 ולהגיע לשנה שלמה

טבלת עזר "מסים"

          ד.         החברה מעוניינת להעניק בונוס חד פעמי לעובדיה.

עובדים מהסניפים: ירושלים, חדרה וחיפה שהינם מנכ"לים ומנהלי סניפים זכאים לבונוס בסך 7200 ₪. יתר העובדים מסניפים אלה זכאים לבונוס של 5300 ₪.

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

  1. חשבו בעמודה L, שכותרתה תהיה "בונוס חד פעמי", את סכום הבונוס לכל עובד.
  • עיר: ירושלים או חיפה או חדרה ו-מנכ"לים או מנהלי סניפים  = בונוס 7200 ₪
  • עיר: ירושלים או  חיפה או חדרה = בונוס 5300 ₪
  • השאר = בונוס על פי השכר השנתי (לפי נתוני הבונוס)

פונקציית IF

  1. עליכם לעצב בגופן אדום עם רקע ירוק את סכומי הבונוס הנמוכים מ 6,000 (כולל) ובגופן כחול מודגש את סכומי הבונוס הגבוהים מ 18,000 (כולל).
  1. בגיליון חדש בשם: "התפלגות", צרו גרף עוגה תלת ממדי, שיציג את התפלגות ממוצע הבונוסים החד פעמיים, שניתנו לעובדים בכל מחלקה.
    יש להציג את ערכי הפלחים והאחוזים. עוגה או תרשים כל שהוא ולא התבקשנו ליצור שכיחות, הדרך הקלה ביותר זה להיעזר ביצירת גרפים במקרה בו לא התבקשנו לעשות משהו במיוחד זה ליצור pivot table
  1. לאחר חישוב סך הבונוס שהיא מתכננת להעניק לעובדים, הגיעה החברה למסקנה כי מדובר בעלות גבוהה מדיי עבורה. ß SUM
    החברה מעוניינת שסך הבונוס שיוענק לכל העובדים בחברה, יסתכם ב 1,800,000 ₪ בלבד.

העתיקו את גיליון עובדים לגיליון חדש בשם "בונוס מתוקן" וחשבו בו, מה צריכים להיות אחוזי הבונוס בסניפים שאינם ירושלים, חדרה וחיפה, על מנת שהחברה תעניק לעובדיה סכום זה בלבד.
לחברה חשוב לשמור על הפרש קבוע בין אחוזי הבונוס, כפי שקיים במצב הנוכחי. ß חתירה למטרה בגיליון עבודה חדש עם שינוי אחוזי הבונוס לפי הפרשים קבועים של 1.5 בין כל אחוז (לפי טבלת נתוני בונוסים כספיים), כאשר היעד הוא1.8m

איך נעשה חתירה למטרה עם שמירה על הפרשים של 1.5? נוסיף תא עזר שרשום בו 1.5% – נעשה חתירה למטרה רק לאחוז הראשון אחרי שעשיתי:

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

וככה הלאה…..

 

מרתון 1: נקודות לזכור

  • במידה והנוסחה שעשינו לא יצאה לנו טוב? למחוק את השווה (=) לפני הנוסחה ובמקום לגרור את הנוסחה למטה פשוט להציב מספרים (סתם מספרים) ß אם הנוסחה נכונה ורק חלק קטן לא נכון, לא ירדו לנו כל הנקודות. צריך לרשום נקודת התחלה – וזה עד הנקודה, לא כולל אותה.
  • Vlookup – הטווח תמיד יתחיל מ-0 (אלא אם נאמר אחרת) עד המספר שנאמר לנו +1
  • שכיחות – עד הנקודה כולל אותה, לזכור לסיים את הנוסחה וללחוץ ctel+shift+enter
  • לזכור לקבע תחומים
  • דוגמה – ארצה מספר נפשות באוכלוסייה עם 4 ספרות, כלומר נצטרך להבין שזה :

1000<=x<=1999



× תשע = 81

תואר ראשון
תואר שני
מרצים