Съдържание
Имате ли таблици с данни в Excel, които могат да се преоразмеряват, т.е. броят на редовете (колоните) може да се увеличава или намалява в хода на работа? Ако размерите на таблицата „плават“, тогава ще трябва постоянно да наблюдавате този момент и да го коригирате:
- връзки във формулите за отчети, които препращат към нашата таблица
- начални диапазони на осеви таблици, които са изградени според нашата таблица
- начални диапазони на диаграми, изградени според нашата таблица
- диапазони за падащи менюта, които използват нашата таблица като източник на данни
Всичко това вкупом няма да ви позволи да скучаете 😉
Ще бъде много по-удобно и правилно да създадете динамичен „гумен“ диапазон, който автоматично ще се коригира по размер спрямо действителния брой редове и колони с данни. За да приложите това, има няколко начина.
Метод 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, който препраща към нашата таблица. След това, както в случая на интелигентна таблица, можете свободно да използвате името на създадения диапазон във всякакви формули, отчети, диаграми и т.н. Нека започнем с прост пример:
Task: направете динамичен наименуван диапазон, който ще препраща към списък с градове и автоматично ще се разтяга и свива по размер, когато добавяте нови градове или ги изтривате.
Ще ни трябват две вградени функции на Excel, налични във всяка версия − POICPOZ (СЪВПАДА) за определяне на последната клетка от диапазона и INDEX (ИНДЕКС) за създаване на динамична връзка.
Намиране на последната клетка с помощта на MATCH
MATCH(търсена_стойност, диапазон, тип_съвпадение) – функция, която търси дадена стойност в диапазон (ред или колона) и връща поредния номер на клетката, в която е намерена. Например формулата MATCH(“март”;A1:A5;0) ще върне числото 4 като резултат, тъй като думата “март” се намира в четвъртата клетка в колона A1:A5. Последният аргумент на функцията Match_Type = 0 означава, че търсим точно съвпадение. Ако този аргумент не е посочен, тогава функцията ще премине в режим на търсене на най-близката най-малка стойност – точно това може да се използва успешно за намиране на последната заета клетка в нашия масив.
Същността на трика е проста. MATCH търси клетки в диапазона отгоре надолу и на теория трябва да спре, когато намери най-близката най-малка стойност до дадената. Ако зададете стойност, която очевидно е по-голяма от всяка налична в таблицата като желана стойност, тогава MATCH ще достигне до самия край на таблицата, ще намери нищо и ще даде поредния номер на последната попълнена клетка. И имаме нужда от него!
Ако в нашия масив има само числа, тогава можем да посочим число като желана стойност, която очевидно е по-голяма от която и да е от тези в таблицата:
За гаранция можете да използвате числото 9E + 307 (9 по 10 на степен 307, т.е. 9 с 307 нули) – максималното число, с което Excel принципно може да работи.
Ако в нашата колона има текстови стойности, тогава като еквивалент на възможно най-голямото число можете да вмъкнете конструкцията REPEAT(“i”, 255) – текстов низ, състоящ се от 255 букви “i” – последната буква от азбуката. Тъй като Excel всъщност сравнява кодовете на символите при търсене, всеки текст в нашата таблица технически ще бъде „по -малък“ от такъв дълъг „yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy“ ред:
Генерирайте връзка с помощта на INDEX
Сега, след като знаем позицията на последния непразен елемент в таблицата, остава да оформим връзка към целия ни диапазон. За целта използваме функцията:
ИНДЕКС(диапазон; номер_ред; номер_колона)
Дава съдържанието на клетката от диапазона по номер на ред и колона, т.е. например функцията =INDEX(A1:D5;3;4) в нашата таблица с градове и месеци от предишния метод ще даде 1240 – съдържанието от 3-ти ред и 4-та колона, т.е клетки D3. Ако има само една колона, тогава нейният номер може да бъде пропуснат, т.е. формулата INDEX(A2:A6;3) ще даде „Самара“ в последната екранна снимка.
И има един не съвсем очевиден нюанс: ако ИНДЕКСЪТ не е просто въведен в клетката след знака =, както обикновено, но се използва като крайна част от препратката към диапазона след двоеточието, тогава той вече не се издава съдържанието на клетката, но нейния адрес! Така формула като $A$2:INDEX($A$2:$A$100;3) ще даде препратка към диапазона A2:A4 на изхода.
И тук идва функцията MATCH, която вмъкваме в INDEX, за да определим динамично края на списъка:
=$A$2:ИНДЕКС($A$2:$A$100; MATCH(REP(“I”;255);A2:A100))
Създайте именуван диапазон
Остава да опаковаме всичко в едно цяло. Отворете раздел формула (Формули) И кликнете върху Мениджър на имена (Мениджър на имена). В прозореца, който се отваря, щракнете върху бутона Създаване на (Нова), въведете името на диапазона и формулата в полето Обхват (Референтен):
Остава да кликнете върху OK и готовият диапазон може да се използва във всякакви формули, падащи списъци или диаграми.
- Използване на функцията VLOOKUP за свързване на таблици и стойности за търсене
- Как да създадете автоматично попълващ се падащ списък
- Как да създадете обобщена таблица за анализ на голямо количество данни