טווח דינמי עם התאמה אוטומטית

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

• קישורים בנוסחאות דוחות המתייחסות לטבלה שלנו
• טווחים ראשוניים של טבלאות ציר הבנויות לפי הטבלה שלנו
• טווחים ראשוניים של תרשימים שנבנו לפי הטבלה שלנו
• טווחים לתפריטים נפתחים המשתמשים בטבלה שלנו כמקור נתונים

כל זה בסך הכל לא יאפשר לכם להשתעמם 😉

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

שיטה 1. שולחן חכם

סמן את טווח התאים שלך ובחר מהכרטיסייה בית - עיצוב כשולחן (בית - עיצוב כשולחן):

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

כעת נוכל להשתמש בקישורים דינמיים ל"טבלה החכמה" שלנו:

• לוח 1 - קישור לטבלה כולה מלבד שורת הכותרת (A2:D5)
• טבלה 1[#הכל] - קישור לכל הטבלה (A1:D5)
• טבלה 1[פיטר] - הפניה לעמודת טווח ללא כותרת התא הראשונה (C2:C5)
• טבלה 1[#Headers] - קישור ל"כותרת" עם שמות העמודות (A1:D1)

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

= SUM (טבלה 1[מוסקבה]) - חישוב הסכום עבור העמודה "מוסקבה"

or

=VPR(F5;לוח 1;3;0) – חפש בטבלה את החודש מתא F5 והפק עבורו את הסכום של סנט פטרסבורג (מה זה VLOOKUP?)

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

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

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

הָהֵן. קישור לטבלה חכמה בצורת מחרוזת טקסט (במרכאות!) הופך לקישור מלא, והרשימה הנפתחת תופסת אותו בדרך כלל.

שיטה 2: טווח בעל שם דינמי

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

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

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

מציאת התא האחרון באמצעות MATCH

MATCH(lookup_value, range, match_type) – פונקציה שמחפשת ערך נתון בטווח (שורה או עמודה) ומחזירה את המספר הסידורי של התא שבו הוא נמצא. לדוגמה, הנוסחה MATCH("March";A1:A5;0) תחזיר את המספר 4 כתוצאה מכך, מכיוון שהמילה "March" ממוקמת בתא הרביעי בעמודה A1:A5. ארגומנט הפונקציה האחרון Match_Type = 0 אומר שאנו מחפשים התאמה מדויקת. אם ארגומנט זה לא צוין, אז הפונקציה תעבור למצב החיפוש עבור הערך הקטן ביותר הקרוב ביותר - זה בדיוק מה שניתן להשתמש בו בהצלחה כדי למצוא את התא התפוס האחרון במערך שלנו.

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

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

לקבלת אחריות, ניתן להשתמש במספר 9E + 307 (9 כפול 10 בחזקת 307, כלומר 9 עם 307 אפסים) – המספר המקסימלי שאקסל יכול לעבוד איתו באופן עקרוני.

אם יש ערכי טקסט בעמודה שלנו, אז בתור המקבילה למספר הגדול ביותר האפשרי, תוכל להכניס את המבנה REPEAT("i", 255) - מחרוזת טקסט המורכבת מ-255 אותיות "i" - האות האחרונה של אלף - בית. מכיוון ש- Excel משווה למעשה קודי תווים בעת חיפוש, כל טקסט בטבלה שלנו יהיה טכני "קטן יותר" מאשר "Yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy," קו ": קו": קו: קו: קו: "קו:" קו: "קו:" קו: ":"

צור קישור באמצעות INDEX

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

INDEX(range; row_num; column_num)

הוא נותן את תוכן התא מהטווח לפי מספר שורה ועמודה, כלומר למשל, הפונקציה =INDEX(A1:D5;3;4) בטבלה שלנו עם ערים וחודשים מהשיטה הקודמת תיתן 1240 – התוכן מהשורה השלישית ומהעמודה הרביעית, כלומר תאים D3. אם יש רק עמודה אחת, אז ניתן להשמיט את המספר שלה, כלומר הנוסחה INDEX(A4:A3;2) תיתן "Samara" בצילום המסך האחרון.

ויש ניואנס אחד לא לגמרי ברור: אם ה-INDEX לא רק נכנס לתא אחרי הסימן =, כרגיל, אלא משמש כחלק הסופי של ההתייחסות לטווח שאחרי המעי הגס, אז הוא כבר לא פונה. התוכן של התא, אבל הכתובת שלו! לפיכך, נוסחה כמו $A$2:INDEX($A$2:$A$100;3) תיתן התייחסות לטווח A2:A4 במוצא.

וכאן נכנסת לתמונה פונקציית MATCH, אותה אנו מכניסים בתוך ה-INDEX כדי לקבוע באופן דינמי את סוף הרשימה:

=$A$2:INDEX($A$2:$A$100; MATCH(REP("I";255);A2:A100))

צור טווח בעל שם

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

נשאר ללחוץ עליו OK וניתן להשתמש בטווח המוכן בכל נוסחאות, רשימות נפתחות או תרשימים.

• שימוש בפונקציה VLOOKUP לקישור טבלאות וערכי חיפוש
• כיצד ליצור רשימה נפתחת של אכלוס אוטומטי
• כיצד ליצור טבלת ציר לניתוח כמות גדולה של נתונים