בלוג

טווחים דינמיים ב-Excel: מתי להשתמש ב-OFFSET ומתי בטבלה

עדכנו סכומים ורשימות נפתחות עם הוספת שורות. ראו איך OFFSET ו-COUNTA פועלות, מה קורה כשמזהה חסר ואיך להשתמש בטבלת Excel.

טווח ההפניה המודגש מתרחב כלפי מטה כשמוסיפים שורה לטבלה.

אתם מוסיפים מוצר לרשימה ב-Excel, אבל הסכום והאפשרויות ברשימה הנפתחת נשארים ללא שינוי. אם ההפניה עדיין A2:A10, השורה ה-11 נמצאת מחוץ לטווח. בטווח דינמי Excel מזהה את סוף הרשימה, וכך אין צורך לעדכן את הנוסחאות בכל שינוי.

חישוב גובה ורוחב בעזרת OFFSET

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

תא עמודה A עמודה B עמודה C
שורה 1 商品ID (מזהה מוצר) 商品名 (שם המוצר) 数量 (כמות)
שורה 2 A01 עט 4
שורה 3 A02 מחברת 6
=SUM(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$1000),COUNTA(Sheet1!$A$1:$Z$1)))

התחילו ב-A1 ללא היסט. מספר התאים המלאים בעמודה A קובע את הגובה, ומספר הכותרות בשורה 1 קובע את הרוחב. מתקבל A1:C3, שלוש שורות ושלוש עמודות, עם סכום 10. הוסיפו בשורה 4 את A03, סרגל ו-2. הטווח יהפוך ל-A1:C4 והסכום ל-12. מזהי המוצרים הם טקסט, ומניחים שאין נתונים אחרים בטווחים שנספרים. תיעוד OFFSET של Microsoft

מחקו רק את המזהה ב-A3. בעמודה A ייספרו שלושה תאים: הכותרת ב-A1 והמזהים ב-A2 וב-A4. לכן OFFSET תחזיר את שלוש השורות הראשונות, A1:C3. הכמות 6 משורה 3 עדיין כלולה, ואילו הכמות 2 משורה 4 בחוץ. הסכום חוזר ל-10.

A3 ריק. בטווח A1:C3 של OFFSET נכללת הכמות 6 משורה 3, הכמות 2 משורה 4 בחוץ והסכום הוא 10.
המסגרת הירוקה מסמנת את A1:C3, הטווח ש-OFFSET מחזירה ב-Excel ל-Windows.

גם כותרת ריקה באמצע יכולה להשאיר את העמודות האחרונות מחוץ לטווח. COUNTA כוללת תאים עם נוסחאות שמחזירות מחרוזת ריקה (""). בחוברת קיימת שמרו על רצף הכותרות והמזהים ובדקו תאים ריקים ואת גבולות הספירה. תא כמות ריק לא משנה את הגובה אם קיים מזהה. תיעוד COUNTA של Microsoft

כשהכותרות מתחילות בשורה השנייה

אם בשורה 1 יש שם דוח, התחילו את הרשימה ב-A2. ספרו את הגובה והרוחב החל משורה 2 כדי שהשם לא ייכלל. אחרי הזזת הדוגמה שורה אחת למטה, הטווח לכותרות ולשני המוצרים יהיה A2:C4 והסכום יישאר 10.

=SUM(OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1000),COUNTA(Sheet1!$A$2:$Z$2)))

שימוש בטבלה שמתרחבת עם הוספת שורות

לרשימה חדשה, בחרו את הטווח עם הכותרות ובחרו הוספה ← טבלה. קראו לטבלה Items. הנוסחה הבאה מסכמת את עמודת הכמות מתא מחוץ לטבלה. כשתוסיפו שורות, גם טווח הסכימה יתרחב. מדריך Microsoft להפניות מובנות

=SUM(Items[数量])

סכימת שורות שיש בהן מזהה מוצר

כשתהפכו את A1:C4 לטבלה Items, הנוסחה לעיל תחבר 4 + 6 + 2 ותחזיר 12 גם כאשר A3 ריק. כדי לסכם רק שורות עם מזהה, השתמשו ב-SUMIF עם תנאי שעמודת המזהה אינה ריקה. בדוגמה זו מתקבל 6 (4 + 2 משורות 2 ו-4). תיעוד SUMIF של Microsoft

=SUMIF(Items[商品ID],"<>",Items[数量])

קישור אפשרויות הרשימה הנפתחת לטבלה

לבחירת שמות מוצרים בגיליון אחר, פתחו נוסחאות ← מנהל השמות ב-Excel השולחני. צרו את ItemChoices ברמת החוברת והזינו =Items[商品名] ב-Refers To. באימות נתונים של תא הקלט בחרו רשימה והזינו =ItemChoices בתור מקור. הכותרת אינה מופיעה ושורות חדשות בסוף הטבלה מוסיפות אפשרויות. מלאו שם בכל שורה חדשה כדי להימנע מאפשרויות ריקות. מדריך Microsoft ליצירת רשימה נפתחת