В скорошната си статия говорих за именовани диапазони в Excel. Докато изследваше именовани диапазони, се появи темата за динамичните диапазони. Така че в тази статия ще обясня как можете да направите динамичен диапазон в Excel.
Какво е Dynamic Named Range в Excel?
Нормалният именен диапазон е статичен. Ако дефинирате С2: С10 като Позиция, артикул винаги ще се отнася за C2: C10, докато и освен ако не го редактирате ръчно. На изображението по -долу броим празни места вВещ списък. Той показва 2. Ако беше динамичен, щеше да покаже 0.
Динамичен диапазон от имена е диапазон от имена, който се разширява и свива според данните. Например, ако имате списък с елементи в диапазон C2: C10 и го наименувайте Елементи, трябва да се разшири до C2: C11 ако добавите нов елемент в обхвата и трябва да се свие, ако намалите, когато изтриете както по -горе.
Как да създадете динамичен диапазон от имена
Създайте именовани диапазони с помощта на таблици в Excel
Да, таблиците на Excel могат да създават динамични именови диапазони. Те ще направят всяка колона в таблица с имена диапазон, която е силно динамична.
Но има един недостатък на имената на таблици, че не можете да ги използвате при проверка на данни и условно форматиране. Но там могат да се използват конкретни именовани диапазони.
Използвайте формулата INDIRECT и COUNTA
За да направим диапазон от имена динамичен, можем да използваме INDIRECT и COUNTA функции
. Как? Да видим.
Общата формула, която трябва да бъде написана в се отнася за: раздел
= INDIRECT ("$ startCell: $ndingColumnLetter $" & COUNTA ($ columnLetter: $ columnLetter))
Горната формула може да изглежда сложна, но всъщност е лесна. Нека видим с пример.
Основната идея е да се определи последната използвана клетка.
Пример за динамичен диапазон
В горния пример имахме статичен диапазон на име Item в диапазон C2: C10. Нека го направим динамичен.
-
- Отворете Мениджър на имена, като натиснете CTRL+F3.
- Сега, ако име вече съществува в диапазона, щракнете върху него и след това щракнете върху редактиране. В противен случай щракнете върху Ново.
- Назовете го Item.
- В Отнася се за: Раздел напишете под формулата.
= INDIRECT ("$ C2: $ C $" & COUNTA ($ C: $ C))
- Натиснете бутона OK.
И това е направено. Сега, когато въведете Item в полето за име или във всяка формула, той ще се позовава на C2 за последната използвана клетка в диапазона.
Внимание: Никоя клетка не трябва да бъде празна между диапазона. В противен случай диапазонът ще бъде намален с броя на празни клетки.
Как работи?
Както казах, единственият въпрос е намирането на последна използвана клетка. За този пример не трябва да има празни клетки между тях. Защо? Ще знаете.
Функцията INDIRECT в excel преобразува текст в диапазон. = INDIRECT (“$ C $ 2: $ C $ 9”) ще се отнася до абсолютния диапазон $ C $ 2: $ C $ 10. Просто трябва динамично да намерим последния номер на ред (9).
Тъй като всички клетки имат някаква стойност в диапазон C2: C10, можем да използваме функцията COUNTA, за да намерим последния ред.
Така,= ИНДИКРЕКТНО("$ C2: $ C $" и тази част поправя началния ред и колоната и COUNTA($ C: $ C) динамично изчислява последния използван ред.
Така че да, ето как можете да направите най -ефективните динамични именовани диапазони, които ще работят с всяка формула и функционалност на Excel. Не е необходимо да редактирате отново своя именен диапазон, когато променяте данни.
Свали файл:
Динамични именовани диапазони в ExcelКак да използвате именовани диапазони в Excel
17 невероятни функции на таблици в Excel
Популярни статии:
50 преки пътища в Excel за повишаване на вашата производителност
Как да използвате функцията VLOOKUP в Excel
Как да използвате функцията COUNTIF в Excel
Как да използвате функцията SUMIF в Excel
