محدوده دینامیکی با اندازه خودکار

آیا جداولی با داده در اکسل دارید که اندازه آنها قابل تغییر باشد، یعنی تعداد سطرها (ستون ها) در طول کار کم یا زیاد شود؟ اگر اندازه جدول "شناور" است، باید دائماً این لحظه را کنترل کرده و آن را اصلاح کنید:

• پیوندهایی در فرمول های گزارش که به جدول ما اشاره دارد
• محدوده اولیه جداول محوری که مطابق جدول ما ساخته شده اند
• محدوده اولیه نمودارها مطابق جدول ما ساخته شده است
• محدوده برای کرکره هایی که از جدول ما به عنوان منبع داده استفاده می کنند

همه اینها در مجموع به شما اجازه نمی دهد خسته شوید 😉

ایجاد یک محدوده پویا "لاستیک" بسیار راحت تر و صحیح تر خواهد بود، که به طور خودکار اندازه را با تعداد واقعی ردیف ها و ستون های داده تنظیم می کند. برای پیاده سازی این، راه های مختلفی وجود دارد.

روش 1. میز هوشمند

محدوده سلول های خود را برجسته کرده و از برگه انتخاب کنید صفحه اصلی – فرمت به عنوان جدول (صفحه اصلی – قالب به عنوان جدول):

اگر به طرح راه راه که به عنوان جلوه جانبی به جدول اضافه می شود نیاز ندارید، می توانید آن را در زبانه ای که ظاهر می شود خاموش کنید. سازنده (طراحی). هر جدولی که به این روش ایجاد می‌شود، نامی دریافت می‌کند که می‌تواند با یک نام راحت‌تر در همان مکان روی برگه جایگزین شود. سازنده (طراحی) در این زمینه نام جدول (نام جدول).

اکنون می‌توانیم از پیوندهای پویا به «جدول هوشمند» خود استفاده کنیم:

• جدول 1 - پیوند به کل جدول به جز ردیف سرصفحه (A2:D5)
• جدول 1[#همه] – پیوند به کل جدول (A1:D5)
• جدول 1 [پیتر] - ارجاع به یک ستون محدوده بدون اولین هدر سلول (C2:C5)
• جدول 1[#Headers] - پیوند به "هدر" با نام ستون ها (A1:D1)

چنین مراجعی در فرمول ها عالی عمل می کنند، به عنوان مثال:

= مبلغ (جدول 1 [مسکو]) - محاسبه مبلغ برای ستون "مسکو"

or

=VPR(F5;جدول 1;3;0) - در جدول مربوط به ماه را از سلول F5 جستجو کنید و جمع سن پترزبورگ را برای آن صادر کنید (VLOOKUP چیست؟)

چنین پیوندهایی را می توان در هنگام ایجاد جداول محوری با انتخاب روی برگه با موفقیت استفاده کرد درج - جدول محوری (درج - جدول محوری) و وارد کردن نام جدول هوشمند به عنوان منبع داده:

اگر قطعه ای از چنین جدولی را انتخاب کنید (مثلاً دو ستون اول) و نموداری از هر نوع ایجاد کنید، هنگام اضافه کردن خطوط جدید، آنها به طور خودکار به نمودار اضافه می شوند.

هنگام ایجاد لیست های کشویی، نمی توان از پیوندهای مستقیم به عناصر جدول هوشمند استفاده کرد، اما می توانید به راحتی با استفاده از یک ترفند تاکتیکی از این محدودیت عبور کنید - از تابع استفاده کنید. غیر مستقیم (غیر مستقیم)، که متن را به پیوند تبدیل می کند:

آن ها پیوند به یک جدول هوشمند در قالب یک رشته متن (در علامت نقل قول!) به یک پیوند کامل تبدیل می شود و لیست کشویی معمولاً آن را درک می کند.

روش 2: محدوده نامگذاری شده پویا

اگر به دلایلی تبدیل داده‌های خود به یک جدول هوشمند نامطلوب است، می‌توانید از روش کمی پیچیده‌تر، اما بسیار ظریف‌تر و همه‌کاره‌تر استفاده کنید - یک محدوده با نام پویا در اکسل ایجاد کنید که به جدول ما اشاره دارد. سپس، مانند جدول هوشمند، می توانید آزادانه از نام محدوده ایجاد شده در هر فرمول، گزارش، نمودار و غیره استفاده کنید. اجازه دهید با یک مثال ساده شروع کنیم:

کار: یک محدوده با نام پویا ایجاد کنید که به لیستی از شهرها اشاره می کند و هنگام افزودن شهرهای جدید یا حذف آنها به طور خودکار بزرگ و کوچک می شود.

ما به دو تابع داخلی اکسل که در هر نسخه موجود است نیاز خواهیم داشت POICPOZ (همخوانی داشتن) برای تعیین آخرین سلول محدوده، و شاخص (فهرست مطالب) برای ایجاد یک لینک پویا

یافتن آخرین سلول با استفاده از MATCH

MATCH(مقدار_جستجو، محدوده، نوع_تطابق) – تابعی که یک مقدار معین را در یک محدوده (ردیف یا ستونی) جستجو می کند و شماره ترتیبی سلولی را که در آن پیدا شده است برمی گرداند. به عنوان مثال، فرمول 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" - آخرین حرف الفبا. از آنجایی که اکسل در واقع کدهای کاراکتر را هنگام جستجو با هم مقایسه می کند، هر متنی در جدول ما از نظر فنی "کوچکتر" از خط طولانی "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy" خواهد بود:

با استفاده از INDEX یک پیوند ایجاد کنید

اکنون که موقعیت آخرین عنصر غیر خالی در جدول را می دانیم، باقی مانده است که پیوندی به کل محدوده خود ایجاد کنیم. برای این کار از تابع استفاده می کنیم:

INDEX (محدوده؛ ردیف_تعداد؛ ستون_تعداد)

محتویات سلول را از محدوده به تعداد ردیف و ستون نشان می دهد، به عنوان مثال، تابع =INDEX(A1:D5;3;4) در جدول ما با شهرها و ماه های روش قبلی 1240 می دهد - محتوا. از سطر 3 و ستون 4، یعنی سلول های D3. اگر فقط یک ستون وجود داشته باشد، می توان شماره آن را حذف کرد، یعنی فرمول INDEX(A2:A6;3) در آخرین اسکرین شات "سامارا" را نشان می دهد.

و یک تفاوت کاملاً واضح وجود دارد: اگر 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 برای پیوند دادن جداول و مقادیر جستجو
• نحوه ایجاد یک لیست کشویی با تکمیل خودکار
• چگونه یک جدول محوری برای تجزیه و تحلیل حجم زیادی از داده ها ایجاد کنیم