Как да зададете диапазон от стойности в Excel

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

Автоматична промяна на диапазоните от клетки в Excel

Предимството на автоматичните диапазони в Excel е, че те правят много по-лесно използването на формули. В допълнение, те позволяват значително да се опрости анализа на сложни данни, които съдържат голям брой формули, които включват много функции. Можете да дадете име на този диапазон и след това той ще се актуализира автоматично в зависимост от това какви данни съдържа.

Как да направите автоматична промяна на диапазона в Excel

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

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

1. Ако проблемът бъде решен по този начин, няма да е възможно да се използват клетките, включени в колона B, за други цели.
2. Такава таблица ще консумира много RAM, което ще направи невъзможно използването на документа на слаби компютри.

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

1. Отидете в раздела „Формули“, който се намира в главното меню. Ще има раздел „Дефинирани имена“, където има бутон „Присвояване на име“, върху който трябва да щракнем.
2. След това ще се появи диалогов прозорец, в който трябва да попълните полетата, както е показано на екранната снимка. Важно е да се отбележи, че трябва да приложим функцията = ИЗМЕСТВАНЕ заедно с функцията CHECKза създаване на диапазон с автоматично актуализиране. 
3. След това трябва да използваме функцията SUM, който използва нашия динамичен диапазон като аргумент. 

След като изпълним тези стъпки, можем да видим как покритието на клетките, принадлежащи към диапазона „доход“, се актуализира, докато добавяме нови елементи там.

Функция OFFSET в Excel

Нека да разгледаме функциите, които записахме в полето "диапазон" по-рано. Използване на функцията ИЗХВЪРЛЯНЕ можем да определим размера на диапазона, като се има предвид колко клетки в колона B са запълнени. Аргументите на функцията са както следва:

1. Стартова клетка. С този аргумент потребителят може да посочи коя клетка в диапазона ще се счита за горна лява. Ще докладва надолу и надясно.
2. Диапазон, изместен по редове. Използвайки този диапазон, задаваме броя на клетките, с които трябва да се появи отместване от горната лява клетка на диапазона. Можете да използвате не само положителни стойности, но и нула и минус. В този случай изместването може изобщо да не се случи или ще се извърши в обратна посока.
3. Изместване на диапазона по колони. Този параметър е подобен на предишния, само че ви позволява да зададете степента на хоризонтално изместване на диапазона. Тук също можете да използвате както нулеви, така и отрицателни стойности.
4. Размерът на диапазона във височина. Всъщност заглавието на този аргумент ни изяснява какво означава. Това е броят на клетките, с които диапазонът трябва да се увеличи.
5. Стойността на диапазона по ширина. Аргументът е подобен на предишния, само че се отнася до колоните.

Не е необходимо да указвате последните два аргумента, ако не е необходимо. В този случай стойността на диапазона ще бъде само една клетка. Например, ако посочите формулата =OFFSET(A1;0;0), тази формула ще препраща към същата клетка като тази в първия аргумент. Ако вертикалното отместване е зададено на 2 единици, тогава в този случай клетката ще се отнася до клетка A3. Сега нека опишем подробно какво означава функцията CHECK.

COUNT функция в Excel

Използване на функцията CHECK определяме общо колко клетки в колона B сме попълнили. Тоест, използвайки две функции, ние определяме колко клетки в диапазона са запълнени и въз основа на получената информация определяме размера на диапазона. Следователно крайната формула ще бъде следната: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Нека да разгледаме как правилно да разберем принципа на тази формула. Първият аргумент сочи къде започва нашият динамичен диапазон. В нашия случай това е клетка B2. Допълнителните параметри имат нулеви координати. Това предполага, че не се нуждаем от отмествания спрямо горната лява клетка. Всичко, което попълваме, е вертикалният размер на диапазона, за който използвахме функцията CHECK, който определя броя на клетките, които съдържат някои данни. Четвъртият параметър, който попълнихме, е единицата. Така показваме, че общата ширина на диапазона трябва да бъде една колона.

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

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

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

Динамични диаграми в Excel

И така, в последната стъпка успяхме да създадем динамичен диапазон, чийто размер напълно зависи от това колко запълнени клетки съдържа. Сега, въз основа на тези данни, можете да създавате динамични диаграми, които автоматично ще се променят веднага щом потребителят направи промени или добави допълнителна колона или ред. Последователността на действията в този случай е следната:

1. Избираме нашия диапазон, след което вмъкваме диаграма от типа „Хистограма с групиране“. Можете да намерите този елемент в секцията „Вмъкване“ в секцията „Диаграми – Хистограма“.
2. Щракваме с левия бутон на мишката върху произволна колона на хистограмата, след което функцията =SERIES() ще се покаже в реда на функцията. На екранната снимка можете да видите подробната формула. 
3. След това трябва да се направят някои промени във формулата. Трябва да замените диапазона след „Sheet1!“ към името на гамата. Това ще доведе до следната функция: =РЕД(Лист1!$B$1;;Лист1!доход;1)
4. Сега остава да добавите нов запис към отчета, за да проверите дали графиката се актуализира автоматично или не.

Нека да разгледаме нашата диаграма.

Нека обобщим как го направихме. В предишната стъпка създадохме динамичен диапазон, чийто размер зависи от това колко елемента съдържа. За целта използвахме комбинация от функции CHECK и ИЗХВЪРЛЯНЕ. Наименувахме този диапазон и след това използвахме препратката към това име като диапазон на нашата хистограма. Кой конкретен диапазон да изберете като източник на данни на първия етап не е толкова важно. Основното нещо е да го замените с името на диапазона по-късно. По този начин можете да спестите много RAM.

Именувани диапазони и техните употреби

Нека сега поговорим по-подробно за това как правилно да създаваме именувани диапазони и да ги използваме за изпълнение на задачите, зададени за потребителя на Excel.

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

Всичко започва с именуването на клетката. За да направите това, просто го изберете и след това напишете името, от което се нуждаете, в полето на името му. Важно е да се помни лесно. Има някои ограничения, които трябва да имате предвид при наименуване:

1. Максималната дължина е 255 знака. Това е напълно достатъчно, за да зададете име, което сърцето ви желае.
2. Името не трябва да съдържа интервали. Следователно, ако съдържа няколко думи, тогава е възможно да ги разделите с помощта на знака за подчертаване.

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

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

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

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

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

Друг начин за създаване на именуван диапазон е да го получите от таблица. За да направите това, има специален инструмент, наречен „Създаване от селекция“. Както разбираме, за да го използвате, първо трябва да изберете диапазона, който ще редактираме, и след това да зададете мястото, където имаме заглавията. В резултат на това, въз основа на тези данни, Excel автоматично ще обработи всички данни и автоматично ще бъдат присвоени заглавия.

Ако заглавието съдържа няколко думи, Excel автоматично ще ги раздели с долна черта.

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