עיצוב מותנה Conditional Formatting

 עיצוב מותנה Conditional Formatting


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

דוגמה, להלן טבלת נתונים של אוכלוסיה לפי גובה ומין-

נרצה להדגיש בצבע ורוד את כל אלו שהם מעל גובה 1.6 בשלב ראשון נסמן את העמודה של הנתונים הרלוונטים-
עמודה C-שהיא מראה את נתוני הגובה,
אח"כ נלך ללשונית HOME ונבחר באפשרות עיצוב מותנה Conditional Formatting:

ואז נבחר באפשרות Highlight Cells Rules
ואז נבחר Greater Then  ונרשום את הערך הרצוי למשל נדגיש את כל הגבהים מעל גובה 1.6 :

להלן התוצאה שהתקבלה-עבור כל האנשים שהם מעל גובה 1.6 נצבעו התאים בוורוד:

ניתן כמובן להשתמש בתנאים אחרים עפ"י הצורך קטן מ-1.6 שווה ל-1.6 או כל ערך מספרי אחר.

טבלת ציר-PIVOT TABLE

טבלת ציר PIVOT TABLE -
מהי טבלת ציר? כללי תצוגה וניתוח המרכז נתונים (בד"כ כמות נתונים גדולה) לתוך טבלה אינטראקטיבית
הנתונים יכולים להיות טקסטואלים או מספריים וניתן לארגן אותם בדרכים שונות על פי הצורך.
לפנינו נתונים המראים ציונים של תלמידים במקצועות השונים -בשלב ראשון נסמן את טבלת הנתונים שלנו:

אח"כ נילחץ על לשונית הוסף-INSERT  ונלחץ על PIVOT TABLE -טבלת ציר:

ואז תיפתח החלונית הבאה  שמראה שהנתונים שבחרנו הם הטבלה לעיל-נלחץ על OK לאישור :

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

ברירת המחדל של טבלת הציר היא סכום העמודה,במקרה זה סכום הציון בהיסטוריה, אבל אין משמעות לסכום הציונים, יותר חשוב הוא מה הממוצע של הציונים ולכן ע"מ לשנות את ההגדרה מסכום לממוצע נלחץ על הכפתור של הסכום ונבחר ב- value fields setting ואז תיפתח חלונית בה נבחר באפשרות של ממוצע:

להלן החלונית שנפתחת-

ניתן לראות כי ממוצע הציונים במקצוע היסטוריה הוא 81.55 

פונקציית MAX

מהי פונקציית מקסימום באקסל MAX ?

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

להלן נתונים של עוצמות רעידות אדמה שנצפו בעולם במדינות שונות בשנים האחרונות (כולל רעידת האדמה האחרונה שהתרחשה ביפן במרץ 2011 ) :

כעת באמצעות פונקציית מקסימום נוכל לדעת באיזה איזור ובאיזה שנה התרחשה רעידת האדמה החזקה ביותר מאז שנת 2004 -נרשום את הפונקציה מקסימום ונסמן את עמודה D שורות 3-11 :

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

עיצוב תאים באקסל -FORMAT CELLS

עיצוב תאים באקסל-איך לעצב תאים באקסל לפי סוג המטבע?
עיצוב התאים באקסל  לפי סוג המטבע


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

נסמן את הסכומים בשקל ולחץ על המקש הימני בעכבר ונבחר באפשרות של עיצוב תאים-FORMAT CELLS :

בקטגוריה נבחר באפשרות של מטבע-Currency  נבחר בסימן-Symbol את סוג המטבע -שקל

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

להלן הנתונים המעוצבים שהתקבלו:

חישוב ערך עתידי של סכום באקסל-הפונקציה FV

חישוב ערך עתידי של סכום באקסל-הפונקציה FV
במידה ונרצה לחשב את הערך העתידי של השקעה (למשל חיסכון/פיקדון בבנק) נשתמש בפונקציית FV-
הנתונים הנדרשים לנוסחה הם:
שיעור הריבית RATE
NPER מספר תשלומים
PMT-סכום ההפקדה/ההשקעה

ניקח לדוגמה תוכנית חיסכון המניבה ריבית שנתית של 12% , ההפקדה היא חודשית,הסכום החודשי הוא 1,000 ש"ח.

 RATE=12% -את הריבית נחלק ל-12 מכוון שהריבית היא במונחים שנתיים וההפקדה היא חודשית.
NPER=12
PMT=1,000

הנוסחה היא -FV-(ריבית,מספר ההפקדות,סכום הפיקדון)
נוסף סימן מינוס לפני הנוסחה כדי לתת ערך חיובי לתוצאה מאחר ומדובר בפיקדון:

מהו הערך העתידי של הפיקדון בו הופקדו 1,000 ש"ח כל חודש במשך שנה בריבית של 12% שנתית ?
התשובה היא  12,683 ש"ח:

לימוד אקסל - Excel -2007-2003-2010: פונקציית IRR -שיעור תשואה פנימי-שת"פ

לימוד אקסל - Excel -2007-2003-2010: פונקציית IRR -שיעור תשואה פנימי-שת"פ: "ראשית מהו IRR ? שיעור תשואה פנימי של פרוייקט הוא מחיר ההון בו יש להוון את תזרימי המזומנים של הפרויקט ע'מ שהערך הנוכחי הנקי שלו י..."

לימוד אקסל - Excel -2007-2003-2010: סכומי ביניים-SUBTOTAL-איך לבצע סכומי ביניים?

לימוד אקסל - Excel -2007-2003-2010: סכומי ביניים-SUBTOTAL-איך לבצע סכומי ביניים?: "סכומי ביניים-SUBTOTAL-איך לבצע סכומי ביניים? כאשר יש לנו מסד נתונים עליו נרצה לבצע סכימה לפי פרמטר מסויים נשתמש בסכומי ביניים. להלן דוגמה..."

לימוד אקסל - Excel -2007-2003-2010: איך מורידים את קווי הרשת באקסל-EXCEL GRID LINES

לימוד אקסל - Excel -2007-2003-2010: איך מורידים את קווי הרשת באקסל-EXCEL GRID LINES: "איך להסיר/להוריד את קווי הרשת באקסל גרסת 2007-2010 -2003? גרסאות 2007-2010 : נלחץ על הלשונית 'פריסת עמוד'-PAGE LAYOUT ונוריד את סימ..."

לימוד אקסל - Excel -2007-2003-2010: פונקציה חודש,יום,שנה YEAR ,MONTH,DAY -EXCEL

לימוד אקסל - Excel -2007-2003-2010: פונקציה חודש,יום,שנה YEAR ,MONTH,DAY -EXCEL: "כאשר יש לנו סידרת תאריכים, ניתן מכל תאריך להגדיר מה החודש, מהו היום בחודש ומהי השנה של כל תאריך ואז להעיזר בתוצאות שהתקבלו למשל לשם מיון ה..."

פונקציה חודש,יום,שנה YEAR ,MONTH,DAY -EXCEL

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

פונקציית YEAR מחזירה את השנה של התאריך, נכתוב את הפונקציה YEAR של  התא הרצוי:

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

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

להלן התוצאה שהתקבלה, העמודה של YEAR מראה את השנים,עמודה של MONTH מאה את החודשים עבור כל תאריך,
העמודה של יום DAY  מראה את היום בחודש עבור כל תאריך:

איך מורידים את קווי הרשת באקסל-EXCEL GRID LINES

איך להסיר/להוריד  את קווי הרשת באקסל גרסת 2007-2010 -2003?

גרסאות 2007-2010 :


נלחץ על הלשונית "פריסת עמוד"-PAGE LAYOUT ונוריד את סימן ה - בקווי הרשת:
להלן הלשונית של פריסת עמוד כאשר ה-  R עדיין מסומן

לאחר הורדת ה-  R מתקבל גיליון ללא קווי רשת-

  גרסת 2003 
ניגש לכלים-אפשרויות-תצוגה ונסיר את סימן ה- R מהאפשרות של קווי הרשת.

סכומי ביניים-SUBTOTAL-איך לבצע סכומי ביניים באקסל?

סכומי ביניים-SUBTOTAL-איך לבצע סכומי ביניים באקסל?

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

בשלב ראשון יש למיין את הנתונים לפי סוג המטבע (ראה הסבר על מיון- SORT & FILTER )
להלן הטבלה שהתקבלה לאחר המיון:

ניגש ללשונית DATA (נתונים) ונבחר באפשרות של סכומי ביניים SUBTOTAL , להלן החלונית שתיפתח:

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

סה"כ 39,000 אירו, 12,000 דולר וכו'.
ניתן לבחור בחלונית האפשרויות במקום סכום באפשרות של ממוצע כדי לחשב ממוצע משוקלל למטבע מסויים,
כמו כן ניתן לבחור באפשרות של מינימום/מקסימום עבור הסכומים.

השלמה אוטומטית אקסל -מילוי תאים אוטומטי באקסל

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

להלן דוגמה להשלמה אוטומטית של מספר סידורי, בשלב ראשון נרשום את הספרה 1 בתא A1 ואת הספרה 2 בתא A2 :

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

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


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

אלו התאריכים שהתקבלו לאחר שסימנו את שני התאריכים (אשר שימשו כתבנית) וגררנו כלפי מטה:

הפרש בין שני תאריכים-חישוב ימים,חישוב חודשים,חישוב שנים

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

לדוגמה להלן רשימה של תאריכי התחלה וסיום של חוזה שכירות :
ע"מ לחשב את מספר הימים שנותרו עד לתום החוזה נרשום נוסחה של תאריך הסיום פחות תאריך ההתחלה ,
כלומר תא C3 פחות תא B3 :

ע"מ לחשב את מספר החודשים שנותרו עד לתום החוזה נרשום נוסחה של חילוק מספר הימים ב-30 ,

כלומר D3 (סה"כ הימים) לחלק במספר הימים שיש בחודש -30 (בממוצע):

ע"מ לחשב את מספר השנים שנותרו עד לתום החוזה נרשום נוסחה של חילוק מספר הימים ב-365 :

שינוי שם הגיליון-שינוי צבע הגיליון

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

איך לשנות את צבע הגיליון/צבע הלשונית?
מקש ימני על שם הגיליון ובחירה באפשרות של שינוי צבע- Tab Color ואז בוחרים בצבע הרצוי למשל אדום:

להלן התוצאה שהתקבלה שם הגיליון "אקסל" צבע הלשונית-אדום:

פונקציית IRR -שיעור תשואה פנימי-שת"פ

ראשית מהו IRR ?

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

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

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


להלן דוגמה לפרויקט בו משקיעים בהתחלה 10,000 ש"ח (נסמן במינוס את הסכום מאחר ומדובר בהשקעה)
לאחר שנה מתקבלות הכנסות של 6,000 ש"ח ולאחר שנתיים מתקבלות הכנסות של 7,000 ש"ח (את הסכומים נסמן בפלוס כי מדובר בהכנסות),
נעמוד מתחת לתזרים בתא B5 ונרשום את הנוסחה (IRR  (B2:B4=


להלן הטבלה של תזרים הפרויקט :

ניתן לראות כי שיעור התשואה הפנימי של הפרויקט הוא 18.88% =IRR .

הדבקה מיוחדת paste special-(חילוק,כפל,חיבור,חיסור)

כאשר יש לנו מסד נתונים עליו נרצה לבצע פעולת חשבון מסוימת וקבועהבאמצעות מספר מסויים נשתמש באפשרות של העתקה והדבקה מיוחדת.
במסגרת הדבקה מיוחדת ניתן להכפיל /לחלק  לחבר או להחסיר כל מספר לפי הצורך.
להלן דוגמה של שימוש בהדבקה מיוחדת של פעולת החילוק,
אם יש ברשותנו רשימה של מספרים אותם נרצה לחלק במספר 1,000 ע"מ להגיע לנתונים באלפי ש"ח ,
במקום לחלק כל תא בנפרד באלף ניתן להעתיק את המספר אלף ולחלק אותו בכל הנתונים ע"י הדבקה מיוחדת.
ראשית נרשום בצד את המספר 1,000 סמוך לרשימת המספרים:

לאחר מכן נעתיק את המספר אלף ונסמן את טווח הנתונים אותו נרצה לחלק במספר אלף (תאים A2:A15 )
נלחץ על המקש הימני ונבחר באפשרות הדבקה מיוחדת  paste special  ונבחר את האופציה של "לחלק"- Divide
להלן החלונית שנפתחת לאחר לחיצה על "הדבקה מיוחדת":

נלחץ OK לאישור,אלו הם המספרים שהתקבלו לאחר החלוקה במספר אלף:

כאמור ניתן להכפיל /לחלק  לחבר או להחסיר כל מספר לפי הצורך.

פונקציית AVERAGE-חישוב ממוצע (הוצאות חודשיות בממוצע)

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

ע"מ לחשב ממוצע חודשי של הוצאות נעמוד על אחד התאים בתחתית הטבלה ונרשום את הנוסחה של   AVERAGE

להלן החלונית שנפתחת דרך ה- FX   

הממוצע החודשי של ההוצאות הוא 377,917 ש"ח.

פונקציה ערך מוחלט - ABS -(השוואת מחירים)

כאשר נרצה להמיר מספר שלילי לערכו המוחלט נשתמש בפונקציית ABS.

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

שימו לב כי מחירו של מוצר ב' (300) נמוך ממחירו של מוצר א' (350) !

הנוסחה של הערך המוחלט תראה כך : (מוצר מחיר ב'-מוצר מחיר א' )ABS=
כאשר נלחץ על ה-FX בשורת המצב תיפתח החלונית הבאה-

כאמור מחירו של מוצר ב' (300) נמוך ממחירו של מוצר א' (350) וההפרש המקורי הוא 50- בסימן שלילי אך לאחר שימוש בפונקציית ABS מתקבל הפרש חיובי של 50 .