Въведение в динамичните диапазони
Функцията VLOOKUP често се използва за намиране на информация, която се съхранява в таблици в Excel. Така например, ако имаме списък с имената и възрастта на хората:
И тогава можем в близка клетка да използваме функцията VLOOKUP, за да определим възрастта на Пол:
Досега това е сравнително стандарт. Но какво ще стане, ако трябва да добавим още някои имена към списъка? Очевидната мисъл би била да се промени диапазонът във VLOOKUP. В един наистина сложен модел може да има няколко препратки към VLOOKUP. Това означава, че ще трябва да променим всяка референция - ако приемем, че знаем къде се намират.
Excel обаче предлага алтернативен начин - наречен DYNAMIC диапазон. Това е диапазон, който автоматично разширява актуализациите. Това е идеално, ако вашите списъци се разширяват постоянно (например данни за продажбите за месец за месец).
За да настроим динамичен диапазон, трябва да имаме име на диапазон - затова ще наречем нашия AGE_DATA. Подходът за настройване на динамични диапазони се различава между Excel 2007 и по -ранните версии на Excel:
В Excel 2007 кликнете върху „Определяне на име“ под формулите:
В по -ранните версии на Excel кликнете върху „Вмъкване“ и след това имена.
В изскачащото поле въведете името на нашия динамичен диапазон - което е „ВЪЗРАСТНИ ДАННИ“:
В полето с надпис „Отнася се до“ трябва да въведем обхвата на нашите данни. Това ще бъде постигнато чрез функция OFFSET. Това има 5 аргумента:
= OFFSET (Справка, Редове, Cols, Височина, Ширина)
- Референцията е адресът на горния ляв ъгъл на нашия диапазон - в този случай клетка B5
- Редовете са броят редове от горния ляв ъгъл, който искаме да бъде този диапазон - който ще бъде 0 в този случай
- Cols е броят редове от горния ляв ъгъл, който искаме да бъде този диапазон - който ще бъде 0 в този случай
- Височината на диапазона - вижте по -долу за това
- Ширината на диапазона - това е 2, имаме две колони в нашия диапазон (името на хората и тяхната възраст)
Сега височината на диапазона ще трябва да варира в зависимост от броя на записите в нашата таблица (която в момента е 7).
Разбира се, ние искаме начин за преброяване на редовете в нашата таблица, който се актуализира автоматично - така че един от начините да направите това е да използвате функцията COUNTA. Това просто брои броя на непразни клетки в диапазон. Тъй като имената ни са в колона B, броят на записите в нашите данни е COUNTA (B: B).
Обърнете внимание, че ако поставите това в клетка, ще получите стойността 8 - тъй като тя включва имената на заглавката. Това обаче е без значение.
Така че в полето „Отнася се до“ поставяме:
= OFFSET ($ B $ 5,0,0, counta (B: B), 2)
И щракнете върху бутона OK. Нашият динамичен диапазон вече е създаден.
Сега се върнете към формулите на VLOOKUP и заменете диапазона $ B: 4: $ C11 с името на новия ни динамичен диапазон AGE_DATA, така че имаме:
Досега нищо не се е променило. Ако обаче добавим още няколко имена към нашата таблица:
И в клетката, където имахме Пол, я заменете с ново име, като Педро (което не беше в оригиналния списък):
Виждаме, че Excel автоматично върна възрастта на Педро - въпреки че не сме променили формулите на VLOOKUP. Вместо това обхватът на динамичния диапазон се е увеличил, за да включи допълнителните имена.
Динамичните диапазони са много полезни, когато имаме увеличаващи се обеми от данни - особено когато са необходими таблици VLOOKUP и PIVOT.
