הם היו שם הרבה זמן (מאקסל 2003) , השתפרו פלאים באקסל 2007, אבל לי משום מה לקח זמן לאהוב אותם… העדפתי לעבוד עם אובייקט Range הישן והטוב.
היום נתחיל בסדרת פוסטים על טבלאות אקסל שבהחלט עושות את החיים קלים לפעמים.

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

שימו לב שאקסל יצר מאחורי הקלעים בעצם אובייקט טבלה בשם Table1 (זה יהיה חשוב אחר כך כשנרצה לפנות אליה).

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

* יצירת Pivot או גרף מהטבלה – כאשר נעשה זאת, לא נצטרך לעדכן את הטווח כאשר הטבלה תגדל !
* כלי הסרת הכפילויות .
*   הפיכה לטווח .- הפיכת הטבלה בחזרה לטווח.
* סגנונות טבלה – ניתן להוסיף ולהסיר את שורת הכותרת, הסיכום, ועוד כמה אופציות.
* עיצובים – ניתן לעצב את הטבלה באחד ממגוון עיצבוים נחמדים בלחיצת כפתור או להמציא עיצוב משלך.

תכונות נוספות של טבלאות:

כשאנחנו גוללים עם העכבר, העמודות משתנות מהאות שלהם לשם העמודות של הטבלה!

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

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

הפנייה מנוסחאות -אז איך פונים לטבלאות מתוך נוסאות אקסל ?

בעזרת סינטאקס חדש ב2007 (שונה קצת ב2010) נוכל לפנות למקומות שונים בטבלה:

כאשר אני מקיש "=" ואחריו את שם הטבלה שלנו ואז "]" , יפתח לי חלון אפשרויות (תמונה ראשונה מעל מתוך השלוש) .
בחירה באחת העמודות של הטבלה שלנו, תתן לנו הפניה לטווח העמודה (תמונה 2) , או את אחת מהאפשרויות במתחילות ב# שמאפשרות לבחור את: כל הטבלה,רק נתונים, רק כותרות, רק שורת סיכום …

טיפ : בהפנייה מחוברת חיצוניות נצטרך להקפיד לפנות כך:
WorkbookName!TableName
ולא כך:
[WorkbookName.xls[!TableName

הסיבה לכך היא כדי שאקסל יוכל להבדיל בין טבלה בשם Table1 לבין טווח באותו השם.

אגב בהצלחה שיהיה אם אתם משתמשים בשמות ועמודות בעברית... (זה קצת מעצבן )

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

ברגע שלחצתי "]=" מתוך הטבלה, נפתחה לי רשימה של העמודות. בחרתי Sales ואז המשכתי עם "/" ושוב [ כדי לבחור את Workings Days.
פשוט וקל !

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

הוספת שורת סיכום :

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

בפוסטים הבאים בנושא –

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

משתמשים בטבלאות ? יש לכם טריקם מעניינים ? הגיבו !

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

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

אבל מה קורה כאשר אנחנו רוצים שקטע קוד מסויים ירוץ כל יום בשעה 23:00 בלילה ? או כל 15 דקות ?
או, טוב ששאלתם.

הפקודה שבה נשתמש היא Application.OnTime. נוכל להשתמש בה בצורה הפשוטה הבאה:

Sub ScheduleRun()
    Application.OnTime TimeValue("23:00:00"), "Macro1"
End Sub

בעצם אמרנו לאקסל, תריץ את המאקרו/סאב Macro1  כל יום בשעה 23:00.

עכשיו נניח שאנחנו רוצים מאקרו שירוץ עוד 15 דקות מעכשיו ?

Sub ScheduleRun()
    Application.OnTime Now + TimeValue("00:00:15"), "Macro1"
End Sub

פה בעצם אנחנו "מכוונים" את המאקרו לרוץ עוד רבע שעה מעכשיו (שימוש בפונקציה NOW המובנית).

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

Sub Macro1()
    Application.OnTime Now + TimeValue("00:00:15"), "Macro1"

    'Now do some magic
End Sub

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

רגע !!! אבל איך עוצרים את זה ??

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

Sub Macro1()
    Range("NextRun").Value = Now + TimeValue("00:00:15")
    Application.OnTime Range("NextRun").Value, "Macro1"

    ' Do some magic
End Sub

Sub CancelScheduleRun()
    Application.OnTime Range("NextRun").Value,"Macro1", _
       Schedule:=False
End Sub

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

לסיכום, יצרתי דוגמא נחמדה שממחישה את הנושא:
OnTime Example

נשמח לשמוע תגובות או רעיונות נוספם בנושא !

שלום,

בגרסאות קודמות של אקסל, הייתה לנו דרך למשוך מידע מהאינטרנט דרך טבלאות HTML. הדבר היה מסורבל וחשוף לטעויות. היום נדבר על איך באקסל 2007 ואקסל 2010, ניתן למשוך נתונים מהאינטרנט בקלות רבה ולעצב אותם כאוות נפשנו בעזרת פורמט הXML.

ניקח דוגמא של שערי מטבע. מצאתי באינטרנט אתר שנותן את שערי המטבע שלו בפורמט XML  (לא כלכך קשה למצוא זאת בימינו)
http://rss.timegenie.com/forex.xml .

נתחיל:

1. נלך לטאב מפתחים -> קבוצה בשם "XML" -> ונלחץ על "מקור".
2. בטאב שנפתח בצד נבחר "מפות XML" -> "הוספה" -> ונוסיף את הקישור. נוספה לנו בצד מפת הXML שהאקסל קרא ממקור הנתונים, המפה מראה לנו אילו נתונים קיימים במקור הנתונים ואת ההיררכיה שלהם.
3. נגרור את התיקיה "Forex" לתא C4 , יופיעו לנו הכותרות של העמודות, עכשיו עמוד בתא C5 ונלחץ כפתור ימני -> "רענן" :

זהו ! יש לנו נתוני שערי מטבע בלחיצת כפתור ,שימו לב כמה קל זה היה.

בואו ננקה קצת את הגיליון - נמחק את עמודות Text,URL,Title (האמת היא שיכולנו להתחמק מהם אם היינו גוררים רק את תיקיית Data).

עכשיו מה שיפה פה זה שאקסל יצר בעצם טבלה (ListObject) בצורה אוטומטית. הדבר יקל עלינו כאשר נרצה לפנות אליה בקוד, נשתמש בזה ונוסיף את קוד הVBA  הבא כדי לרענן את הנתונים:

Sub RefreshFxRates()
    Dim FxTable As ListObject

    Set FxTable = ThisWorkbook.Sheets(1).ListObjects("Table 5")
    FxTable.XmlMap.DataBinding.Refresh
End Sub

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

זהו להפעם, אהבתם ? נשמח לתגובות!