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

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

Как да се отличи да направите автоматична промяна гама

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

Генериране на доклада за добива на нашия инвестиционен обект, както е показано по-долу:

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

1. Изберете инструмент "Формула" - "Някои имена" - "Присвояване на име".
2. Попълнете в диалоговия прозорец "Създаване на име", както е показано на. Моля, имайте предвид, че в "Range:" Ние използваме функцията = OFFSET, а в един от нейните параметри използвайте функцията = брои. Пример: = отместване (!! Sheet1 $ B $ 2; 0; 0; COUNT (Sheet1 $ B :! $ B) 1)
   
3. Преместване на курсора в клетката и въведете име на функция D2 = SUM "Приходи" в неговите параметри.

Сега, постепенно запълване на клетката, в колона Б, можем да проследим как покритието на клетки, наречени "доход".

OFFSET функция в Excel

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

Функция = OFFSET дефинира ни граници в зависимост от броя на пълни клетки в колона Б. 5 = ПРЕДОТВРАТЯВАНЕ параметри функция (начална клетка; компенсира редове размер; компенсира колони; размер на размера на височина от порядъка на ширина):

1. "Основно клетка" - показва горната лява клетка, от която диапазон динамично ще се разшири в посока надолу надясно (ако е необходимо).
2. "Отместване по линия" - параметър определя колко трябва да се смени диапазона от вертикалата от първоначална клетка (първи параметър). Стойностите може да бъде нула или отрицателна.
3. "Изместване на колона" - параметър определя колко трябва да се измести хоризонтално от първоначална клетка. Стойностите може дори да бъде нула или отрицателна.
4. "Размери на височина" - броя на клетките, към който искате да се увеличи обхвата на височина. В действителност, името говори за себе си.
5. "Ширина размер" - на броя на клетките, с което трябва да се увеличи в ширина от началната клетка.

Последните две функции са незадължителен параметър. Ако те не са пълни, гамата ще се състои от първата клетка. Например: OFFSET = (А1 0 0) - е клетка А1 и параметър OFFSET = (А1; 2; 0) се отнася до A3.

Сега помислете функцията: = сметка, която се посочва в четвъртия параметър на функцията: = офсет.

Какво определя функция COUNT

Функция = COUNT ($ B: $ B) автоматично се брои броя пълни клетки в колона Б.

По този начин, ние сме с помощта на резултата = () и = OFFSET () автоматизиране на процеса на формиране на име на банда за "доход", което го прави динамичен. Сега ние отново да погледнем нашата формула, която ни се дава името "доход": = OFFSET (Sheet1 $ B $ 2; 0; 0; COUNT (Sheet1 $ B $ :! B); 1!!!)

Прочетете тази формула трябва да бъде, както следва: Първият параметър показва, че нашата гама започва да се променя автоматично в клетка В2. Следните два параметъра имат стойност 0; 0 - това означава, че динамичният обхват не се измества по отношение на първоначалната клетка В2. Но това само увеличава вертикалния размер, както е видно от 4-ия параметър. Това е COUNT връща редица е равен на броя на пълни клетки в колона Б. Следователно, броят на клетки в вертикална диапазон е равен на броя, който ще даде функция броене. Ширината на групата се срещаме най-новия 5-ти параметър, където е номер 1.

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

Динамични Графики в Excel

Ние имаме динамичен име, сега се създаде динамична графика за типа на отчета:

Използвайки нашия динамичен име на "доход" ние бяхме в състояние да се създаде автоматично се променя динамично схема, която се допълва и показва нови данни в доклада.