Динамичен обхват с авто-размери

Имате ли таблица с данни на Excel, чиито размери могат да се променят, т.е. броя на редовете (колони), може да се увеличи или намали по време на работа? Ако размерът на таблицата "плава", че е необходимо постоянно да следи този въпрос и да се коригира:

Връзки към доклади формули, които се отнасят до нашата трапеза
източник варира обобщени таблици, които са изградени на нашата трапеза
източник варира от парцела на нашата маса
Диапазонът за падащи списъци, които се използват в нашата таблица като източник на данни

Всичко това сума няма да ви омръзне;)

Тя е много по-лесно и по-правилно да се създаде динамична "каучук" гама, която ще се регулира автоматично в размер от действителния брой на реда колони с данни. За да се реализира това, има няколко начина.

Метод 1. Интелигентен маса

Маркирайте диапазон от клетки, а след това кликнете върху раздела Начало - Формат като маса (Home - Формат като таблица):

Ако не се нуждаете от раиран дизайн, който се добавя към таблица страничен ефект, той може да бъде прекратен в раздела Проектиране, който се появява (Design). Всяка таблица така създалата се именуват, които могат да бъдат заменени с по-комфортно там в раздела Design (Дизайн) в името на таблицата (Таблица Name).

Сега можете да използвате динамична връзка към нашия "умен маса":

Такива препратки забележително работят във формулите, например:

= SUM (Таблица 1 [София]) - изчисляване на сумата на колоната "София"

= CDF (F5 Таблица 1, 3, 0) - таблица за справка месеца F5 клетка и доставка Петербург количество от него (това е CDF?)

Тези връзки могат да се използват успешно за създаване на обобщени таблици. избиране в раздела Вмъкване - Обобщение таблица (Insert - Pivot таблица) и въвеждането на интелигентно име на таблица като източник на данни:

Ако изберете парче такава таблица (например, първите две колони) и да се създаде схема на всякакъв вид, тогава Добавяне на нови редове те автоматично се добавят към диаграмата.

Когато създавате списъци с възможност преки връзки с елементи на смарт маса не може да се използва, но лесно можете да получите около това ограничение, като използваше тактическа хитрост - Използвайте функцията INDIRECT (косвено). който превръща текст в линка:

Метод 2. Динамичен наименуван диапазон

Ако конвертирате вашите данни в таблицата умен, по някаква причина не желаете, можете да използвате малко по-трудно, но много по-забележим и универсален метод - да се създаде динамична Excel наименуван диапазон, позовавайки се на нашата маса. След това, както е в случая на интелигентния таблицата, можете да използвате свободно името на създадената гама във всички формули, отчети, графики и т.н. За да започнете, помислете за един прост пример:

Задача. предостави динамичен име граници, които да бъдат посочени в списъка на градовете и автоматично стреч свие по размер, когато добавите нови градове или отстраняването им.

Имаме нужда от две вградени функции на Excel достъпни във всяка една версия - POIKSPOZ (MATCH) за определяне на последната клетка в диапазона и Index (Индекс), за да се създаде динамична връзка.

Търсим най-новата клетка с помощта на MATCH

Същността на този трик е просто. MATCH изброява в търсене на клетка в диапазона от горе на долу и, на теория, трябва да спре, когато установи, близо до най-ниската стойност за целта. Ако зададете стойност за желаната известен повече от всеки наличен в таблицата, мачът ще стигнат до края на масата, нищо няма да се получи и поредния номер на последното запълнена клетката. И ние трябва да го!

Ако нашият набор от номера, но е възможно изискваните стойности показват, броят на които е очевидно по-голяма от всеки наличен в таблицата по-долу:

За да се гарантира, можете да използвате няколко 9Е + 307 (9 умножени по 10 до 307 градуса, т.е. 9 307 нули) - максималния брой, които по принцип могат да работят Excel.

Ако в тази графа, стойността на текст, като еквивалент на максимален брой можете да вмъкнете дизайн да се повтаря (на "I", 255) - текстов низ, състоящ се от 255 писма "I" - последната буква от азбуката. Тъй като Excel Solver, всъщност, сравнява кодовете на герои, всеки текст в таблицата ще бъде технически "по-малко" толкова дълго време "... .I yayayayaya" ред:

Осъществяване на връзка с помощта INDEX

Сега, когато знаем позицията на последното не е празна клетка в таблицата, е необходимо да се генерира линк към цялата ни гама. За да направите това, използвайте функцията:

INDEX (гама ред_номер; колона_номер)

Тя дава съдържанието на клетка в обхвата на броя на ред и броят колона, т.е. като функция = INDEX (А1: D5; 3; 4) на графиката с градовете и месеца на предишния метод ще даде 1,240 - съдържанието на третия ред и четвърти колоната, т.е. клетка D3. Ако една колона е само един, не можете да посочите своя номер, т.е. INDEX формула (А2: А6; 3) ще даде "Самара" последната снимки.

И тук става въпрос във функцията MATCH, която ще се постави в кода за динамично определяне на края на списъка:

= $ A $ 2: INDEX ($ A $ 2: $ $ 100, MATCH (повтор ( "I"; 255); А2: A100))

Създаване наименуван диапазон

Остава да го опаковат всичко в едно устройство. Щракнете върху раздела Формули (формули) и след това върху Наименование Manager (Мениджър). В прозореца, който се отваря, кликнете New (Нов). въведете името ни група и формула в диапазона (справка):