עשה ואל תעשה באקסל: מהדורה מס' 1
עשה ואל תעשה באקסל
אנחנו רגילים למצוא שורת סיכום בסופה של טבלת נתונים. מי בכל החליט שהסיכום צריך לבוא בסוף? יש אלטרנטיבה.
בנוסף, האם אתם נתקלים בזמני רענון ארוכים במיוחד באקסלים עמוסי נוסחאות? הנה כמה טיפים כדי שלא תצטרחו לחכות כל כך הרבה.
על כל זאת ועוד במהדורה הראשונה של עשה ואל תעשה באקסל
מיקום שורת הסיכומים
מה יותר טבעי מלראות בסוף טבלת נתונים שורת סיכום? התרגלנו לפורמט שבו שורת הסיכום מופיעה איפשהו למטה, בסוף הדוח. אנחנו לוקחים את הדבר כמובן מאליו, אך האם תהיתם אי פעם אם זהו הפורמט האידיאלי? לדוגמא, מה קורה אם הדוח הינו תקופתי ובכל תקופה יש מספר שונה של שורות? האם צריך לעדכן את נוסחאות הסיכום בכל פעם שמעדכנים את הדוח? גם אם לא, יש את הסיכון ששורת נוסחאות הסיכום פשוט תידרס כאשר מעדכנים את הנתונים. אגב, מה לגבי ממשקים עם דוחות אחרים? במקרה בו יש דוח שניזון משורת הסיכומים או בקרה שמופעלת על שורה זו, צריך לעדכן בכל פעם את מיקומה החדש של השורה עם כל עדכון של הדוח. בקיצור, מצב לא אופטימלי שקיים אך ורק כיוון שהתרגלנו לכך ששורת הסיכומים מופיעה תמיד בסוף.
לפיכך, רעיון מהפכני: מה אם נמקם את שורת הסיכומים בהתחלה? כלומר, שורת הסיכומים תופיע בראש טבלת הנתונים, הנוסחאות ירוצו מראש על מספר שורות גדול מהנדרש הכולל אקסטרה למקרה שנוסיף שורות נתונים חדשות, ומיקומה לא ישתנה בכל פעם שמעדכנים את הדוח. כמו כן, שורת הסיכומים תהיה גלויה מיד עם פתיחת הדוח, ולא נצטרך לגלול למטה כדי להגיע ל-bottom line. שימו לב שאפילו המושג bottom line מרמז על כך שהתכל'ס אמור להימצע ב-bottom. לאנשי מקצוע יצירתיים, אני מציע לעבור לאלתר למושג top line, הן בשיח המקצועי והן בחוברות העבודה שלכם.
שכפול שורת נוסחאות למספר גדול של שורות
האם בכל פעם שאתם פותחים דוח אקסל או משנים בו משהו קטן אתם נדרשים להמתין שניות ארוכות ואף דקות לעדכון הנוסחאות? אם זה המצב, כנראה ששיכפלתם את אותו החישוב למספר שורות גדול במיוחד. לעתים, אין ברירה אלא להשתמש בשיטה זו, למשל כאשר אנחנו באמת רוצים שכל עדכון קטן ישפיע ויתעדכן online. אך במצבים רבים אחרים, ניתן לקבע את החישוב ולמזער משמעותית את זמן ההמתנה לעדכון הדוח.
דוגמא: נניח שבעמודות A:K ישנם נתונים (טקסט, שאתם משכפלים ידנית ממקום אחר או מייבאים בעזרת קישור לקובץ csv וכד'). בעמודות L:X ישנם חישובים שרצים ברמת השורה (למשל, הכפלה של מחיר בכמות וכד'). נניח שהנוסחאות אמורות לרוץ על 400,000 שורות של נתונים. במקום לשכפל את הנוסחאות ב-L:X 400,000 פעם ולהשאירן במצב של חישוב מתמיד, להלן הצעה:
לאחר שכפול הנוסחאות, העתיקו אותן שוב, ואז תדביקו אותן כערכים, אך מהשורה השניה ואילך בלבד. כעט, יש לכם 399,999 שורות של נתונים סטטיים ושורה דינמית אחת בלבד עם נוסחאות, כדי שתוכלו להשתמש בה בפעם הבאה שנדרש עדכון של הנתונים. כלומר, הנתונים זמינים, ובו בעת האקסל לא נדרש למאות אלפי חישובים online. כשתרצו לעדכן את הנתונים או החישובים, פשוט בצעו את הפעולות הנ"ל פעם נוספת. אף יותר אפקטיבי לעשות כל זאת במקרו:
Range(“L1:X1”).Select
Selection.Copy
Range(“L2:X400000”).Select
ActiveSheet.Paste
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ניתן בנוסף לשים את הנוסחא הבאה בתא שאיננו חלק מהנתונים או הנוסחאות (נניח בתא "W1”):
("CountA(“A1:A1000000=
וכך לקבל את מספר השורות האמיתי של הנתונים. לאחר מכן (ובהנחה שהנתונים מתחילים בשורה מס' 1), אפשר להשתמש בערך שהתקבל במאקרו כדי לא לשכפל נוסחאות ללא צורך (שורה 3 במאקרו):
Range(“L2:X” & Range(“W1”)).Select
ניתן לשלב את המאקרו יחד עם מאקרו אחר לרענון החיבור עם ה-CSV או ליצור עבורו כפתור מיוחד וחהפעיל במידת הצורך. אפשר גם להפעיל יחד עם פתיחת האקסל באופן אוטומטי (אפרט איך ניתן לעשות זאת בפוסט נפרד).
בהצלחה!
