Трик #42

Ако трябва постоянно да актуализирате и добавяте данни или ако работите с диаграми и обобщени таблици, ще ви трябват динамични наименувани диапазони, които растат и се свиват въз основа на вашите данни. За да разберете как работят динамичните наименувани диапазони, първо трябва да сте запознати с функцията OFFSET на Excel, ако още не сте запознати с нея. Функцията OFFSET е свързана с функциите за справка и търсене на Excel.

Нека започнем с прост динамичен наименуван диапазон, който ще се разшири надолу с една колона, но само докато има записи в тази колона. Например, ако има 10 последователни реда с данни в колона A, динамичният наименуван диапазон ще обхваща диапазона A1:A10. За да създадете основен динамичен наименуван диапазон, направете следното.

Изберете командата Insert → Name → Assign (Insert → Name → Define) и в полето Name (Names in workbook) въведете MyRange. В полето Формула (Отнася се за) въведете следната формула: =OFFSET($A$1;0;0;COUNTA($A$l:$A$100);l), в българската версия на Excel =OFFSET($A$1;0;0;COUNTA($A$1:$A$100);1). Сега щракнете върху бутона Добавяне, след което щракнете върху бутона OK.

За съжаление, динамичните наименувани диапазони не могат да бъдат проверени в полето за стандартно име отляво на лентата с формули. Можете обаче да щракнете в полето за име, да въведете името MyRange и да натиснете Enter. Excel автоматично ще маркира диапазона. Разбира се, можете също да използвате диалоговия прозорец Go To, като изберете командата Edit → Go To (пряк път Ctrl / Apple + G). В полето Reference въведете MyRange и щракнете върху бутона OK.

В динамично имедиапазон, който създадохте в предишния пример, функцията COUNTA заема мястото на аргумента Height на функцията OFFSET.

В следващия пример ще използваме динамичен наименуван диапазон, за да дефинираме таблица с данни, която трябва да бъде динамична. За целта в полето Формула (Отнася се за) въведете следната формула: =OFFSET($A$1;0;0;COUNTA($A$1:$A$100);COUNTA($1:$1)), в българската версия на Excel =OFFSET($A$1;0;0;COUNTA($A$1:$A$100);COUNTA($1:$1)). Сега динамичният наименуван диапазон ще разшири толкова записи, колкото има в колона A и толкова редове, колкото има заглавия в ред 1. Ако сте сигурни, че броят на колоните в таблицата с данни няма да се промени, можете да замените втората функция COUNTA с постоянно число, например 10.

Единственият проблем с използването на динамичен наименуван диапазон за таблица с данни е, че се приема, че колона А определя максималната дължина на таблицата. Най-често това е така, но понякога най-дългата колона може да бъде друга колона в таблицата. Можете да преодолеете този проблем, като използвате функцията Excel MAX (MAX), която връща най-голямото число в диапазон от клетки. Като пример, създайте таблица като на фиг. 3.4.

OFFSET

Ориз. 3.4. Таблица с динамични данни и диалогов прозорец за именуване

Ред 1 съхранява COUNTA функции, които препращат към съответната колона и по този начин връщат броя записи във всяка колона. Функцията MAX ще се използва като аргумент за височина на функцията OFFSET. Това гарантира, че динамичният наименуван диапазон за тази таблица винаги ще се разширява толкова клетки, колкото има в най-дългата колона в таблицата. Със сигурностКато алтернатива можете да скриете ред 1, тъй като потребителят изобщо не трябва да го вижда.

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

В следващия пример списъкът с числа в колона A също съдържа празни клетки. Това означава, че ако се опитате да използвате функцията COUNT или COUNTA, динамичният наименуван диапазон ще приключи преди последната клетка с данни. Разгледайте фиг. 3.5.

трик

Ориз. 3.5. Диалогов диапазон от номера и именуване

В този случай, въпреки че последното число в диапазона всъщност е в ред 10, динамичният диапазон се простира само надолу до шестия ред. Причината за това се крие във функцията COUNT, която брои стойностите в клетки от A1 до A100. Тъй като в списъка има само шест числови стойности, диапазонът съдържа само шест реда.

За да преодолеете този проблем, използвайте функцията MATCH на Excel. Функцията MATCH връща относителната позиция на елемент от масив, който съответства на определена стойност в определен ред. Например, ако приложите следната функция MATCH: =MATCH(6,$A$1:$A$100,0), в българската версия на Excel =MATCH(6,$A$1:$A$100,0), към същия набор от числа като на фиг. 3-5, ще върне числото 10, представляващо ред 10 от колона A. Ще върне 10, защото сте казали на функцията да намери числото 6 в диапазона A1:A100.

Очевидно, когато използвате функцията MATCH в динамичен наименуван диапазон, последното число в диапазона вероятно не е известно предварително. По този начин трябва да зададетефункция за търсене на число, което е твърде голямо и никога няма да се появи в диапазона, и промяна на последния му аргумент от 0 на 1.

В предишния пример казахте на функцията MATCH да намери точно числото 6, нито повече, нито по-малко. Като замените 0 с 1, принуждавате функцията да търси най-голямата стойност, по-малка или равна на указаната. За да направите това, използвайте формулата =MATCH(1E+306,$A$1:$A$100,1), в българската версия на Excel =MATCH(1E+306,$A$1:$A$100,1).

За да създадете динамичен именуван диапазон, който ще се разшири до последния ред, съдържащ число (независимо от наличието на празни клетки преди него), въведете следната формула в полето Формула (Отнася се за) на диалоговия прозорец Дефиниране на име (фиг. 3.6): =OFFSET(Sheet2!$A$1;0;0;MATCH(lE+306;Sheet2!$A$l:$A$100;1);1), (Sheet2!$A$1 ;0;0;МАЧ(1E+306;Лист2!$A$l:$A$100;1);1).

OFFSET

Ориз. 3.6. Динамичен диапазон, разширяващ се до последния запис, съдържащ число

Следващият логически тип динамични наименувани диапазони, който произлиза от този, е диапазон, който се разширява до последния текстов запис, независимо от празните клетки в списък или таблица. За да направите това, модифицирайте функцията MATCH по следния начин: MATCH("*",$A$1:$A$100,-1), в българската версия на Excel MATCH("*",$A$1,$A$100,-1). Такава функция винаги връща номера на реда, съдържащ последния текстов запис в диапазона $A$1:$A$100.

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

За това първовмъкнете два празни реда над списъка. Изберете редове 1 и 2 и изберете Вмъкване → Редове (Вмъкване → Ред). В първия ред (ред 1) въведете следната функция: =MAX(MATCH"*";$A$3:$A$100;-1);MATCH(1E+306;$A$3:$A$100;1)); )). В клетката под клетката с формулата въведете числото 1. Клетката още по-долу, под клетката с номер 1, трябва да съдържа текстовото заглавие на списъка. Числото 1 беше добавено, така че втората функция MATCH да не върне грешка #N/A, ако няма числа в диапазона AZ:A100. Първата функция MATCH винаги ще намира текста - в заглавието.

Име на клетка A1 MaxRow. Изберете командата Insert → Name → Assign (Insert → Name → Define), дайте име на динамичния диапазон, например MyList и в полето Formula (Отнася се за:) въведете следната формула: =OFFSET(Sheet2!$A$3;0;0;MaxRow;1), в българската версия на Excel =CMESCH(Sheet2!$A$3;0;0;MaxRow;1).

Следващият списък изброява типовете динамични наименувани диапазони, които може да намерите за полезни. За тези примери ще трябва да попълните колона A както с текст, така и с числови стойности. Освен това изберете командата Вмъкване → Име → Присвояване (Вмъкване → Име → Дефиниране) и в полето Име (Имена в работната книга) въведете име, състоящо се от една дума (например MyRange). Всичко, което ще се промени, е формулата в полето Формула (Отнася се за).

Разширяване на диапазона до толкова редове, колкото има цифрови записи : В полето Формула (Отнася се за) въведете следното: =OFFSET($A$1;0;0;COUNT($A:$A);1), в българската версия на Excel =OFFSET($A$1;0;0;COUNT($A;$A);1).

Разширете диапазона до толкова редове, колкото има цифрови и текстови записи: В полето Формула (Отнася се за) въведете следното:=OFFSET($A$1;0;0;COUNTA($A:$A);1), в българската версия на Excel =OFFSET($A$1;0;0;COUNTA($A:$A);1).

Разгъване до последния цифров запис : В полето Формула (Отнася се за) въведете следното: =OFFSET($A$1;0;0;MATCH(1E+306;$A:$A)), в българската версия на Excel =OFFSET($A$1;0;0;MATCH(1E+306;$A:$A)). Ако очаквате число, по-голямо от 1E+306 (1 последвано от 306 нули), въведете още по-голямо число.

Разгъване до последния въведен текст : В полето Формула (Отнася се за) въведете следното: =OFFSET($A$1;0;0;MATCH("*",$A:$A;-1)), в българската версия на Excel =OFFSET($A$1,0,0;MATCH("*",$A:$A;-1)).

Разгъване надолу въз основа на стойността в друга клетка : В клетка B1 въведете числото 10 и след това в полето Formula (Refers to) въведете следното: =OFFSET($A$1;0;0;$B$1;1), на български Excel =OFFSET($A$1;0;0;$B$1;1). Сега променете числото в клетка B1 и диапазонът ще се промени съответно.

Разгъване на един ред надолу всеки месец : В полето Формула (Отнася се за) въведете следното: =OFFSET($A$1;0;0,MONTH(TODAY());1), на български Excel =OFFSET($A$1;0;0.MONTH(TODAY));1).

Разгъване надолу с един ред всяка седмица : В полето Формула (Отнася се за) въведете следното: =OFFSET($A$1;0;0;WEEKNUM(TODAY());1), На български Excel =OFFSET($A$1;0;0;WEEKDAY(TODAY());1)

За последната формула трябва да инсталирате Analysis ToolPak. Това може да стане с командата Инструменти → Добавки.