סוגיה:

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

פתרון מוצע:

ניתן לגשת למשימה זו בכמה שיטות.

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

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

להלן תיאור בתמונות שלב אחר שלב של הגדרות תזמון המשימות:

1) ניגש לתזמון משימות (Task Scheduler) שנמצא ב-Accessories:

 2) כעת ניתן להיכנס אל תזמון המשימות:

3) הדרך הפשוטה להגדיר משימה חדשה הינה בעזרת אשף משימות. נגדיר שם למשימה וניתן גם להוסיף תיאור:

הגדרת שם משימה:

4) נמשיך עם סדר פעולות הגדרת המשימה של האשף:

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

הפעלת משימת התזמון:

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

סיום ההגדרות:

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

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

מעתה, תרוץ סריקת אאוטלוק כל יום בשעה 12:45.

אקסל: פונקציית IF

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

הנוסחה IF מאפשרת לשאול כל שאלה בנוגע פיסת מידע ומחזירה "אמת" או "שקר" תוצאה.
- שאל אם A1 = 0, או אולי אנו מעוניינים אם הערך מתחיל עם "H" וכו '
- אם הנוסחאות ניתן מקונן עד 7 לולאות פנימיות ב-Excel 2003 ו 64 ב-Excel 2007. למעשה, יותר מ 3-5 קינון עשוי להיראות מורכבמדי כדי לנהל ולתחזק.
- אם הנוסחה מחזירה 1 או 0 (אמת או שקר - פונקציה בוליאנית) והוא יכול לשמש כפרמטר נוסחאות אחרות
תחביר:

= IF (הבדיקה ערך לוגי, אם זה נכון, אם הערך FALSE) 

= IF (AND (X1 = 9, Y2> 100), "גרין", "אדום")
= IF ((X1 = 9) * (Y2> 100), "גרין", "אדום")

  "*" היא דרך אחרת לומר, "+" משמש או
- ו = כל התנאים שיש לקיים כדי לקבל את "הערך האמיתי IF"
- OR = זה טוב במצב מספיק אחד הוא מילא לקבל את "הערך האמיתי IF"
במבנה נתונים, IF מאפשר לבנות עמודות דגל המבדילות בין התנאים הנדרשים.

אקסל: פונקציית SUMIF

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

תחביר:

= SUMIF (, מגוון קריטריונים, סכום טווח)
= COUNTIF (טווח, קריטריונים)

דוגמאות:
 vlookup
כדי לסכם את העמודה "המחיר" עבור כל השורות עם סדרה = "S7", אנא השתמש הבאות:

= SUMIF (B1: B11, "S7", I1: I11)

והתוצאה היא 130 10 19 = 159

כדי לספור את מספר השורות "דני" מופיע בטור "מהנדס", ניתן להשתמש ב-:

= COUNTIF (E1: E11, "דני")

והתוצאה היא 3.

בשתי הפונקציות, ניתן להשתמש בהפניה ישירה לתא שבו הקריטריונים נמצאים. לדוגמה, במקום "S7" ניתן לכתוב B7, B10, או B11.

אקסל: פונקציית VLOOKUP

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

תחביר:

= VLOOKUP (בדיקת ערך, מערך הטבלה, NUM מדד col, טווח בדיקה)

ערך בדיקה, חייב להיות בעמודה הראשונה (משמאל) של טווח הטבלה
דוגמאות:

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

= VLOOKUP ("S7", B1: F11, 5, 0)

התוצאה היא Y557
הנוסחה מחפשת את הערך "S7" בעמודה B, ומחזירה את הערך באותה שורה אבל בעמודה 5 מימין בעמודה B (כולל), כלומר העמודה F

הערה: במקום לכתוב "S7" בנוסחה, ניתן לכוון אותה ישירות לתא שבו "S7" ממוקם (B7) כדלקמן:

= VLOOKUP ($ B7, B1: F11, 5, 0)

כמו כן, התוצאה היא Y557 ולא Y558 או X126 וזה כי הנוסחה מחזירה את הערך עבור הפריט המתאים הראשון.

 

אקסל: פונקציית HLOOKUP

 אותו רעיון כמו VLOOKUP, אבל הפונקציה HLOOKUP מספקת מבט אופקי

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

תחביר:

= HLOOKUP (בדיקת ערך, מערך הטבלה, מדד NUM שורה, טווח בדיקה)

ערך בדיקה, חייב להיות בשורה הראשונה של טווח הטבלה

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

=HLOOKUP("מספר חלק", A1: F11, 5, 0)

התוצאה היא X124

 

אקסל: פונקציית SUMPRODUCT

1. השימוש הבסיסי הוא להכפיל ערכים בין שתי עמודות או יותר
2. יכולת מתקדמת היא לסכום או לספור בהתאם לתנאים מרובים. שימוש זה יכול להוות חלופה עבור טבלת ציר (Pivot table).


תחביר:

= SUMPRODUCT (array1, array2, array3, ...)

אפשרות 1:  אם ברצונך לקבל מספר רב של "קניות" ב "עלות"

= SUMPRODUCT (H2: H11, I2: I11)

התוצאה תהיה:. 1 * 7 +8 * 25 + 1 * 130 + ... 1 * 19 = 1522


אפשרות 2: אם ברצונך לקבל סכום "קניות" של כל סדרת "S7" כאשר "דני" הוא מהנדס

=SUMPRODUCT ((E2: E11 = "דני") * (B2: B11 = "S7"), (H2: H11))

התוצאה תהיה: 2 +1 = 3


אפשרות 3: אם אתה רוצה לסכם את "כמות" מוכפל "עלות", ולאחר מכן להשתמש הבאות:

=SUMPRODUCT  ((E2: E11 = "דני") * (B2: B11 = "S7"), (I2: I11) * (H2: H11))

התוצאה תהיה: 2 * 130 +1 * 19 = 279


אפשרות 4: אם אתה רוצה לספור את השורות שם = הסדרה "S7" מהנדס = "דני"

=SUMPRODUCT ((E2: E11 = "דני") * (B2: B11 = "S7"))

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

אקסל: פונקציית ISERROR

הפונקציה ISERROR חוקרת אם הערך של תא (= פרמטר) מכיל שגיאה ומחזירה אמת / שקר בהתאם

פונקציה זו שימושית בעת השימוש VLOOKUP נוסחאות
הפונקציה ISERROR שייכת למשפחת של פונקציות "האם" (כמו ISNUMBER, ISBLANK וכו ')

דוגמהל-ISERROR בשימוש עם IF ו-VLOOKUP:

מהי הסדרה של מכונת "A7"?

= IF (ISERROR (VLOOKUP ("A7", A1: B11, 2,0)), "לא נמצא", VLOOKUP ("A7", A1: B11, 2,0))

התוצאה היא S1
ראשית VLOOKUP מחושב - ערך A7 נמצא בעמודה A, ולכן הפונקציה VLOOKUP לא החזירה שגיאה, כלומר הפונקציה ISERROR החזירה אתהערך FALSE (= אין שגיאה) ולכן אם הנוסחה החזירה שחיפשנו
אילו VLOOKUP חיפשה A77 במקום A7, הפונקציה ISERROR תחזיר TRUE, כלומר לא "הצליח" למצוא את הערך ולכן יש "שגיאה".

 

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

excel_2010_status_bar_1

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

1) אפשרויות תצוגה

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

excel_2010_status_bar_2

2) מצב upload
כאשר מצב זה לחוץ, מראה את סטטוס העלאת הנתונים, למשל בעת שמירת קובץ, או פתיחת קובץ חדש.

excel_2010_status_bar_3

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

excel_2010_status_bar_4

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

5) מצב בחירה
האפשרות Selection Mode מציג אחת מאפשריות בחירת התאים הבאות:
• בחירה מרובה- כאשר לוחצים F8, כדי להרחיב את בחירת התאים ע"י שימוש בחצים.
• תוספת לבחירה- כאשר לוחצים F8+Shift כדי להוסיף תאים לבחירה ע"י החצים.
האפשרות Page Number מציגה את מספר העמוד במיקום בגיליון הנבחר, ואת סך העמודים בגיליון כולו. עבור מצבי תצוגה פריסת עמודים ופריסת הדפסה (Page layout, Print preview).

6) סטטוס מצב לחצנים
Fixed Decimal-מציינת כי כל הערכים הנומריים יוצגו ע"י מספר צדימלי עם מספר קבוע של מספרים אחרי הנקודה. כדי להפעיל מצב זה, תחת כרטיסיית File נבחר ב- Options, ובתפריט זה ב- advanced. תחת כותרת editing options, נבחר באפשרות הכנס נקודה צדימלית באופן אוטומטי (insert a decimal point).
overtype- מציין כי יש הכנסה של נתונים ע"י insert.
End Mode- מציין כי יש שימוש בכפתור end במקלדת בשילוב עם החצים כדי לנווט בין התאים.

excel_2010_status_bar_6

7) הרשאות ומידע
מאפשר הגדרות חתימה,  הרשאות וניהול מידע

8) מצב תאים
מציג מידע על מצב עיצוב התאים. 
• Ready - מעיד כל מצב כללי
• Enter - מעיד על מצב הכנסת נתונים
• Edit - מעיד על מצב עיצוב תאים
• Point - מעיד על מצב הכנסת נוסחה

excel_2010_status_bar_7

אקסל 2003

על מנת להריץ מאקרו  באקסל 2003 יש לאפשר הרצתו (enable)

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

הפעלת המאקרו תלויה בהגדרות אבטחה ב"כלים" (Tools).

להלן תיאור בתמונות:

לחיצה על "קובץ" ועל "כלים":

excel_macro_2003_1

כעת יש לעבור להגדרות מרכז אבטחה ולהגדיר את רמת ההגנה:

excel_macro_2003_2

 בסיום יש ללחוץ OK, לשמור את הקובץ, לסגור אותו ולפתוח מחדש.

 

אקסל 2007 ומעלה

על מנת להריץ מאקרו יש לאפשר הרצתו (enable):

macro_enable_excel

 

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

הפעלת המאקרו תלויה בהגדרות אבטחה ב"אפשרויות" (Options).

להלן תיאור בתמונות (אקסל 2010, אותם מסכים כמו באקסל 2007):

לחיצה על "קובץ" ועל "אפשרויות":

excel_macro_enable_01

כעת יש לעבור להגדרות מרכז אבטחה ולהגדיר את רמת ההגנה:

excel_macro_enable_2

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

השיטה המומלצת היא השנייה (המשתמש יידרש לאפשר הפעלת מאקרו עם פתיחת הקובץ) או השלישית (רק אם יש חתימה דיגיטלית):

excel_macro_enable_3

 

בסיום יש ללחוץ OK, לשמור את הקובץ, לסגור אותו ולפתוח מחדש.

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

החל מגרסת 2007 ניתן לשלוט בסרגל הכלים גם דרך קבצי XML. כיצד?

התאמות תפריט סרגל ראשי בעזרת קובץ XML

להלן תיאור בשלבים:

1) לשנות סיומת קובץ ל .ZIP:

excel_2007_ribbon_customize_1

2) כעת יש לפתוח את חבילת ZIP ולקבל את תכולתה בתיקיה:

excel_2007_ribbon_customize_2

3) בתיקיה customUI לפתוח את הקובץ customUI.xml.  ניתן לפתוח אותו בכל EDITOR פשוט כגון NOTEPAD או NOTEPAD++

excel_2007_ribbon_customize_3

4) בקוד שנפתח ניתן להתחיל להזין את תכולת התפריט החדש בסרגל:

excel_2007_ribbon_customize_4

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

עם לחיצה על הכפתור, תופעל פרוצדורה של קוד VBA בהתאם למה שמוגדר ב "onAction". למשל, החבאת שורות תפעיל פרוצדורת Hide_Rows.

סעיף הבא יתאר כיצד מוסיפים תמונה לכפתור - imageMso

5) ניתן לבחור את התמונה הרצויה, למשל, לפי קטלוג שנמצא בקישור הבא:  http://soltechs.net/CustomUI/IMG1.htm

excel_2007_ribbon_customize_5

עם סיום העדכונים של קובץ XML, יש לשמור ולסגור אותו

6) כעת יש לכווץ את הספריות והקבצים חזרה ל ZIP:

excel_2007_ribbon_customize_6

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

excel_2007_ribbon_customize_7

מאמרים נוספים...

  1. 10 טיפים לבניית פתרון באקסל
עמוד 4 מתוך 5