מערכים דינמיים באקסל

מהם מערכים דינמיים

בספטמבר 2018 פרסמה מיקרוסופט עדכון שמוסיף כלי חדש לחלוטין ל-Microsoft Excel: מערכים דינמיים ו-7 פונקציות חדשות לעבודה איתם. הדברים האלה, ללא הגזמה, משנים באופן קיצוני את כל הטכניקה הרגילה של עבודה עם נוסחאות ופונקציות ומדאיגים, פשוטו כמשמעו, כל משתמש.

שקול דוגמה פשוטה כדי להסביר את המהות.

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

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

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

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

עכשיו הכל שונה.

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

זה לא קסם, אלא המערכים הדינמיים החדשים שיש ל-Microsoft Excel כעת. ברוכים הבאים לעולם החדש 🙂

תכונות של עבודה עם מערכים דינמיים

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

ניסיון למחוק תא "ילד" אחד או יותר לא יוביל לשום דבר - אקסל מיד יחשב מחדש וימלא אותם.

יחד עם זאת, אנו יכולים להתייחס בבטחה לתאי "ילד" אלה בנוסחאות אחרות:

אם תעתיק את התא הראשון של מערך (לדוגמה, מ-G4 ל-F8), אז המערך כולו (ההפניות שלו) ינוע באותו כיוון כמו בנוסחאות רגילות:

אם אנחנו צריכים להזיז את המערך, אז זה יספיק לנוע (עם העכבר או שילוב של Ctrl+X, Ctrl+V), שוב, רק התא הראשי הראשון G4 - אחריו, הוא יועבר למקום חדש וכל המערך שלנו יורחב שוב.

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

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

שגיאות מערך דינמי

אבל מה קורה אם אין מספיק מקום להרחיב את המערך, או אם יש תאים שכבר תפוסים על ידי נתונים אחרים בדרכו? הכירו סוג חדש ביסודו של שגיאות באקסל - #לְהַעֲבִיר! (#לשפוך!):

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

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

מערכים דינמיים וטבלאות חכמות

אם המערך הדינמי מצביע על טבלה "חכמה" שנוצרה על ידי קיצור מקלדת Ctrl+T או על ידי בית - עיצוב כטבלה (בית - עיצוב כטבלה), אז הוא גם יירש את האיכות העיקרית שלו - התאמה אוטומטית.

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

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

מערכים דינמיים ותכונות נוספות של Excel

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

לא ממש.

מערכים דינמיים הם לא רק עוד כלי באקסל. כעת הם משובצים בלב (או במוח) של Microsoft Excel - מנוע החישוב שלה. המשמעות היא שנוסחאות ופונקציות אחרות של Excel המוכרות לנו כעת תומכות גם בעבודה עם מערכים דינמיים. בואו נסתכל על כמה דוגמאות כדי לתת לכם מושג על עומק השינויים שחלו.

לְשַׁרבֵּב

כדי להעביר טווח (החלפת שורות ועמודות) ל- Microsoft Excel תמיד הייתה פונקציה מובנית TRANSP (לְשַׁרבֵּב). עם זאת, כדי להשתמש בו, תחילה עליך לבחור נכון את הטווח עבור התוצאות (לדוגמה, אם הקלט היה טווח של 5×3, אז אתה חייב לבחור 3×5), ולאחר מכן להזין את הפונקציה וללחוץ על קוֹמבִּינַצִיָה Ctrl+משמרת+זן, כי זה יכול לעבוד רק במצב נוסחת מערך.

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

לוח הכפל

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

הדבקה והמרת מארז

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

מסקנה למעלה 3

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

אם אתה רוצה שהתוצאות ימוקם לא בעמודה, אלא בשורה, אז מספיק להחליף את הנקודותיים (מפריד שורות) בנוסחה זו בנקודה-פסיק (מפריד אלמנטים בתוך שורה אחת). בגרסה האנגלית של Excel, מפרידים אלה הם נקודות פסיק ופסיקים, בהתאמה.

VLOOKUP מחלץ מספר עמודות בבת אחת

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

פונקציית OFFSET מחזירה מערך דינמי

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

עכשיו הבעיה הזו היא בעבר. ראה כיצד כעת, באמצעות נוסחה בודדת ומערך דינמי המוחזר על ידי OFFSET, תוכל לחלץ את כל השורות עבור מוצר נתון מכל טבלה ממוינת:

בואו נסתכל על הטיעונים שלה:

• A1 - תא התחלה (נקודת התייחסות)
• ПОИСКПОЗ(F2;A2:A30;0) – חישוב המעבר מתא ההתחלה למטה – לכרוב שנמצא לראשונה.
• 0 – הזזה של ה"חלון" ימינה ביחס לתא ההתחלה
  
• СЧЁТЕСЛИ(A2:A30;F2) – חישוב גובה ה"חלון" המוחזר – מספר השורות שבהן יש כרוב.
• 4 - גודל ה"חלון" אופקית, כלומר פלט 4 עמודות

פונקציות חדשות עבור מערכים דינמיים

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

• GRADE (סוג) - ממיין את טווח הקלט ומייצר מערך דינמי על הפלט
  
• SORTPO (מיין לפי) - יכול למיין טווח אחד לפי ערכים מהאחר
  
• סנן (לְסַנֵן) – מאחזר שורות מטווח המקור העומדות בתנאים שצוינו
• ייחודי (ייחודי) - מחלץ ערכים ייחודיים מטווח או מסיר כפילויות
  
• SLMASSIVE (RANDARRAY) - יוצר מערך של מספרים אקראיים בגודל נתון
• שִׁליָה (סדר פעולות) - יוצר מערך מרצף של מספרים עם שלב נתון
  

עוד עליהם - קצת מאוחר יותר. הם שווים מאמר נפרד (ולא אחד) ללימוד מתחשב 🙂

מסקנות

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

לסכם את התוצאות, плюсы מערכים דינמיים, אתה יכול לכתוב את הדברים הבאים:

• אתה יכול לשכוח מהשילוב Ctrl+משמרת+זן. Excel לא רואה כעת הבדל בין "נוסחאות רגילות" ל"נוסחאות מערך" ומתייחס אליהן באותה צורה.
• לגבי הפונקציה SUMPRODUCT (SUMPRODUCT), ששימש בעבר להזנת נוסחאות מערך ללא Ctrl+משמרת+זן אתה יכול גם לשכוח - עכשיו זה מספיק קל SUM и זן.
• טבלאות חכמות ופונקציות מוכרות (SUM, IF, VLOOKUP, SUMIFS וכו') תומכים כעת גם באופן מלא או חלקי במערכים דינמיים.
• יש תאימות לאחור: אם תפתח חוברת עבודה עם מערכים דינמיים בגרסה ישנה של אקסל, הם יהפכו לנוסחאות מערך (בסוגריים מסולסלים) וימשיכו לעבוד ב"סגנון הישן".

נמצא מספר כלשהו מינוסים:

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

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

איפה אני יכול להוריד?

ולבסוף, השאלה המרכזית 🙂

מיקרוסופט הכריזה לראשונה והראתה תצוגה מקדימה של מערכים דינמיים באקסל עוד בספטמבר 2018 בכנס להצית. בחודשים הבאים, בוצעה בדיקה יסודית והרצה של תכונות חדשות, תחילה חתולים עובדים של מיקרוסופט עצמה, ולאחר מכן על בודקים מתנדבים מהמעגל של Office Insiders. השנה, העדכון שמוסיף מערכים דינמיים החל להתגלגל בהדרגה למנויי Office 365 רגילים. לדוגמה, קיבלתי אותו רק באוגוסט עם המנוי שלי ל-Office 365 Pro Plus (מיקוד חודשי).

אם ל-Excel שלך עדיין אין מערכים דינמיים, אבל אתה באמת רוצה לעבוד איתם, אז יש את האפשרויות הבאות:

• אם יש לך מנוי ל-Office 365, אתה יכול פשוט לחכות עד שהעדכון הזה יגיע אליך. כמה מהר זה קורה תלויה בתדירות המסירה של עדכונים ל-Office שלך ​​(פעם בשנה, אחת לשישה חודשים, פעם בחודש). אם יש לך מחשב ארגוני, תוכל לבקש ממנהל המערכת שלך להגדיר עדכונים להורדה בתדירות גבוהה יותר.
  
• אתה יכול להצטרף לשורותיהם של אותם מתנדבי מבחן Office Insiders - אז אתה תהיה הראשון לקבל את כל התכונות והפונקציות החדשות (אבל יש סיכוי להגדלת באגי באקסל, כמובן).
• אם אין לך מנוי, אלא גרסה עצמאית של Excel, תצטרך לחכות עד לשחרור הגרסה הבאה של Office ו-Excel ב-2022, לפחות. משתמשים בגרסאות כאלה מקבלים רק עדכוני אבטחה ותיקוני באגים, וכל ה"טובים" החדשים עוברים כעת רק למנויי Office 365. עצוב אבל נכון 🙂
  

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

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