הפונקציה VSTACK מיועדת לאחד מערכים מרובים למערך אנכי אחד (V = Vertical).
זוהי פונקציה פשוטה ועם זאת גמישה, שמאפשרת למשתמשי הקצה להזין נתונים בדרך קלה, ולאחד אותם מאוחר יותר לצורך הניתוח.
אם בעבר הזנת מידע בגיליונות נפרדים עבור כל חודש נחשבה טעות, וכדי לנתח את המידע, היה צריך להעבירו לטבלה רציפה אחת,
עכשיו באמצעות הפונקציה VSTACK, ניתן להזין את המידע בדרך פשוטה וקלה בטבלאות ואפילו בגיליונות נפרדים,
ובאמצעות הפונקציה ליצור טבלה שמרכזת את כל המידע לטבלה רציפה אחת, שאותה נוכל לנתח בקלות.
תחביר
=VSTACK(Array1, Array2…)
כאשר Array1, Array2 וכו' מציינים את המערכים שאותם נרצה לחבר.
המערכים לא חייבים להיות שווים בגודלם.
דוגמאות פרקטיות:
לפנינו קובץ שמכיל לשונית עבור כל מחלקה:
ובכל לשונית מס' העובד, המחלקה והתפקיד, במבנה הבא:
אני רוצה לאחד את כל הלשוניות ללשונית אחת. לצורך כך אשתמש בפונקציה VSTACK:
=VSTACK(הנהלה!A1:C11,ייצור!A2:C8,'שיווק ומכירות'!A2:C9)
כלומר – פניתי לגיליון ההנהלה, לטווח A1:C11, שם נמצאים הנתונים הרלוונטיים (לקחתי גם את שורת הכותרת),
ואז לגיליון 'ייצור', לתאים A2:C8 שם נמצאים נתוני מחלקת ייצור ולבסוף לגיליון 'שיווק ומכירות', לתאים A2:C9 (ללא שורת הכותרת) שם נמצאים הנתונים הרלוונטיים.
ואכן, קיבלתי את הטבלה המאוחדת, כפי שרציתי:
שימו לב שעבור הטבלה הראשונה לקחתי את הנתונים החל מהשורה הראשונה, כדי לכלול את הכותרת, ובלשוניות האחרות, לקחתי את הנתונים החל משורה 2.
מה קורה אם נוספים נתונים?
במקרה הזה צריך לקחת מראש טווח נתונים גדול יותר, שיקח בחשבון שינוי במספר העובדים, כך:
=VSTACK(הנהלה!A1:C20,ייצור!A2:C20,'שיווק ומכירות'!A2:C20)
וזו התוצאה שהתקבלה:
אז אנחנו רואים שיש לנו בעיה, כי עבור כל שורה שאין בה נתונים, אקסל מציג את הערך 0
איך פותרים את הבעיה?
עם פונקציית הקסם FILTER שמסננת את התאים הריקים:
=FILTER(VSTACK(הנהלה!A1:C20,ייצור!A2:C20,'שיווק ומכירות'!A2:C20),
VSTACK(הנהלה!A1:A20,ייצור!A2:A20,'שיווק ומכירות'!A2:A20)<>"")
וזו התוצאה שהתקבלה:
שימו לב שעבור טווח האיחוד (ה-VSTACK הראשון), לקחתי את כל שלוש העמודות, אבל עבור הבדיקה, לקחתי רק את עמודה A, שמציינת את מספר העובד.
טבלאות חכמות
אם תבחרו לעבוד עם טבלאות חכמות, יהיה לכם אפילו יותר קל, משום שלא תצטרכו לקחת טווח גדול יותר מראש, והטבלה המאוחדת תגדל, עם כל שינוי בטבלאות המקור.
קיצור הפונקציה
בדוגמה שלפנינו, עברנו גיליון גיליון והפנינו אל הטבלה המתאימה, ומכיוון שיש לנו בסך הכל שלוש טבלאות, זו לא עבודה גדולה.
עם זאת, אקסל מאפשר לנו לקצר את הפונקציה באמצעות 'הפניה תלת מימדית' או '3D reference',
שבה אנחנו מסמנים רק את הגיליון הראשון והאחרון, ואקסל יודע להתייחס לכל מה שנמצא ביניהם.
איך עושים זאת?
נתחיל בכתיבת הפונקציה כרגיל:
=VSTACK(
וכשנגיע לחלק שמתייחס להפניות, נסמן בלחיצה על שם הלשונית את הגיליון הראשון, נלחץ על shift,
נסמן בלחיצה על שם הלשונית את הגיליון האחרון, כך שכל הגיליונות יסומנו, כפי שאפשר לראות בצילום המסך:
כעת נסמן את טווח הנתונים, וכך נראית הפונקציה:
=VSTACK('הנהלה:שיווק ומכירות'!A2:C20)
הסיבה שלא סימנתי את הכותרות, אלא רק את הנתונים היא שאני לא רוצה שהכותרות יחזרו על עצמן עבור כל טבלה,
ומכיוון שאורך הטבלה עלול להשתנות בין המחלקות השונות, סימנתי טווח גדול יותר מזה שבאמת מכיל את הנתונים.
הערה – שיטת עבודה זו פחות מתאימה לטבלאות חכמות
כמה תיקונים לסיום
ראשית, נסנן החוצה את כל האפסים, בדיוק כמו שעשינו קודם:
=FILTER(VSTACK('הנהלה:שיווק ומכירות'!A2:C20),VSTACK('הנהלה:שיווק ומכירות'!A2:A20)<>"")
ועכשיו נוסיף את הכותרות.
במקרה הזה, נראה לי שהדרך הקלה ביותר היא פשוט להדביק אותן, אבל אם בכל זאת תרצו להוסיף אותן בפונקציה,
תעטפו את כל הפונקציה ב VSTACK נוסף, שמצרף את הכותרות אל התוצאה שקיבלתם קודם:
=VSTACK(הנהלה!A1:C1,FILTER(VSTACK('הנהלה:שיווק ומכירות'!A2:C20),VSTACK('הנהלה:שיווק ומכירות'!A2:A20)<>""))
הסיבה לכך שהוספתי את הכותרות רק אחרי שהחלתי את ה FILTER,ולא בתוך ה VSTACK הפנימי היא שהפונקציה FILTER דורשת שמספר התוצאות של ה TRUE וה FALSE
שמציינים איזו רשומה תכנס לטווח החדש ואיזו לא, צריך להיות באותו הגודל של המערך, ואם נוסיף את הכותרת,
אז יצרנו מערכים שיש ביניהם הבדל בכמות הרשומות (האחת כוללת את הכותרת והשנייה לא).
ואם לא הבנתם את ההסבר (מודה שהוא מורכב), אז כמו שאמרתי – לא יקרה שום דבר אם תבחרו בהעתקה והדבקה של הכותרות באופן ידני (:
ומה יקרה אם יתווספו לנו מחלקות?
מכיוון שהמערך התלת מימדי לוקח את כל הגיליונות שבין גיליון 'הנהלה' לגיליון 'שיווק ומכירות',
אז כל עוד נקפיד להכניס גיליונות נתונים חדשים בין שני הגיליונות האלה, הם יתווספו באופן אוטומטי לחישוב!