Создание динамического определенного диапазона в листах Excel
Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.
Аннотация
В Microsoft Excel у вас может быть диапазон имен, который необходимо расширить, чтобы включить новые сведения. В этой статье описывается метод создания динамического определенного имени.
Метод в этой статье предполагает, что данных не более 200 строк. Можно пересмотреть определенные имена, чтобы они использовали соответствующее число и отражали максимальное количество строк.
Использование формулы OFFSET с определенным именем
Для этого выполните указанные действия, если это уместно для версии Excel, которую вы запускали.
Microsoft Office Excel 2007, Microsoft Excel 2010, русская версия Microsoft Excel 2013
В новом таблице введите следующие данные.
| Номер | A | B |
|---|---|---|
| 1 | Month | Sales |
| 2 | Jan | 10 |
| 3 | Feb | 20 |
| 4 | Mar | 30 |
Щелкните вкладку Формулы.
В группе "Определенные имена" нажмите кнопку Name Manager.
Нажмите кнопку Создать.
В поле Имя введите дату.
В поле "Ссылается на поле" введите следующий текст и нажмите кнопку ОК:
=OFFSET ($A$2,0,0,COUNTA ($A$2:$A$200),1)
Нажмите кнопку Создать.
В поле Имя введите Sales.
В поле "Ссылается на поле" введите следующий текст и нажмите кнопку ОК:
=OFFSET ($B$2,0,0,COUNT ($B$2:$B$200),1)
Щелкните Закрыть.
Очистить ячейку B2, а затем введите следующую формулу:
В этой формуле COUNT используется для столбца чисел. COUNTA используется для столбца текстовых значений.
В этой формуле используется волатильная функция RAND. Эта формула автоматически обновляет формулу OFFSET, которая используется в определенном имени "Sales" при вводе новых данных в столбце B. Значение 10 используется в этой формуле, так как 10 — это исходное значение ячейки B2.
Microsoft Office Excel 2003
В новом таблице введите следующие данные:
| Номер | A | B |
|---|---|---|
| 1 | Month | Sales |
| 2 | Jan | 10 |
| 3 | Feb | 20 |
| 4 | Mar | 30 |
В меню Insert указать имя и нажмите кнопку Определить.
В поле Имена в книге введите Дату.
В поле "Ссылается на поле" введите следующий текст и нажмите кнопку ОК:
=OFFSET ($A$2,0,0,COUNTA ($A$2:$A$200),1).
Нажмите Добавить.
В поле Имена в книге введите Sales.
В поле "Ссылается на поле" введите следующий текст и нажмите кнопку Добавить:
=OFFSET ($B$2,0,0,COUNT ($B$2:$B$200),1)
Нажмите кнопку ОК.
Очистить ячейку B2, а затем введите следующую формулу:
В этой формуле COUNT используется для столбца чисел. COUNTA используется для столбца текстовых значений.
В этой формуле используется волатильная функция RAND. Эта формула автоматически обновляет формулу OFFSET, которая используется в определенном имени "Sales" при вводе новых данных в столбце B. Значение 10 используется в этой формуле, так как 10 — это исходное значение ячейки B2.
Динамический диапазон в EXCEL
history 21 января 2013 г.
- Группы статей
- Имена
- Проверка данных
- Условное форматирование
Если вам необходимо постоянно добавлять значения в столбец, то для правильной работы Ваших формул, Вам наверняка понадобятся динамические диапазоны, которые автоматически увеличиваются или уменьшаются в зависимости от количества ваших данных.
Динамический диапазон — это Именованный диапазон с изменяющимися границами. Границы диапазона изменяются в зависимости от количества значений в определенном диапазоне.
Динамические диапазоны используются для создания таких структур, как: Выпадающий (раскрывающийся) список , Вложенный связанный список и Связанный список .
Задача
Имеется таблица продаж по месяцам некоторых товаров (см. Файл примера ):
Необходимо найти сумму продаж товаров в определенном месяце. Пользователь должен иметь возможность выбрать нужный ему месяц и получить итоговую сумму продаж. Выбор месяца пользователь должен осуществлять с помощью Выпадающего списка .
Для решения задачи нам потребуется сформировать два динамических диапазона : один для Выпадающего списка , содержащего месяцы; другой для диапазона суммирования.
Для формирования динамических диапазонов будем использовать функцию СМЕЩ() , которая возвращает ссылку на диапазон в зависимости от значения заданных аргументов. Можно задавать высоту и ширину диапазона, а также смещение по строкам и столбцам.
Создадим динамический диапазон для Выпадающего списка , содержащего месяцы. С одной стороны нужно учитывать тот факт, что пользователь может добавлять продажи за следующие после апреля месяцы (май, июнь…), с другой стороны Выпадающий список не должен содержать пустые строки. Динамический диапазон как раз и служит для решения такой задачи.
Для создания динамического диапазона:
- на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
- в поле Имя введите: Месяц ;
- в поле Область выберите лист Книга ;
- в поле Диапазон введите формулу =СМЕЩ(лист1!$B$5;;;1;СЧЁТЗ(лист1!$B$5:$I$5))
- нажмите ОК.
Теперь подробнее. Любой диапазон в EXCEL задается координатами верхней левой и нижней правой ячейки диапазона. Исходной ячейкой, от которой отсчитывается положение нашего динамического диапазона, является ячейка B5 . Если не заданы аргументы функции СМЕЩ() смещ_по_строкам, смещ_по_столбцам (как в нашем случае), то эта ячейка является левой верхней ячейкой диапазона. Нижняя правая ячейка диапазона определяется аргументами высота и ширина . В нашем случае значение высоты =1, а значение ширины диапазона равно результату вычисления формулы СЧЁТЗ(лист1!$B$5:$I$5) , т.е. 4 (в строке 5 присутствуют 4 месяца с января по апрель ). Итак, адрес нижней правой ячейки нашего динамического диапазона определен – это E 5 .
При заполнении таблицы данными о продажах за май , июнь и т.д., формула СЧЁТЗ(лист1!$B$5:$I$5) будет возвращать число заполненных ячеек (количество названий месяцев) и соответственно определять новую ширину динамического диапазона, который в свою очередь будет формировать Выпадающий список .
ВНИМАНИЕ! При использовании функции СЧЕТЗ() необходимо убедиться в отсутствии пустых ячеек! Т.е. нужно заполнять перечень месяцев без пропусков.
Теперь создадим еще один динамический диапазон для суммирования продаж.
Для создания динамического диапазона :
- на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
- в поле Имя введите: Продажи_за_месяц ;
- в поле Диапазон введите формулу = СМЕЩ(лист1!$A$6;;ПОИСКПОЗ(лист1!$C$1;лист1!$B$5:$I$5;0);12)
- нажмите ОК.
Функция ПОИСКПОЗ() ищет в строке 5 (перечень месяцев) выбранный пользователем месяц (ячейка С1 с выпадающим списком) и возвращает соответствующий номер позиции в диапазоне поиска (названия месяцев должны быть уникальны, т.е. этот пример не годится для нескольких лет). На это число столбцов смещается левый верхний угол нашего динамического диапазона (от ячейки А6 ), высота диапазона не меняется и всегда равна 12 (при желании ее также можно сделать также динамической – зависящей от количества товаров в диапазоне).
И наконец, записав в ячейке С2 формулу = СУММ(Продажи_за_месяц) получим сумму продаж в выбранном месяце.
Или, например, в апреле.
Примечание: Вместо формулы с функцией СМЕЩ() для подсчета заполненных месяцев можно использовать формулу с функцией ИНДЕКС() : = $B$5:ИНДЕКС(B5:I5;СЧЁТЗ($B$5:$I$5))
Формула подсчитывает количество элементов в строке 5 (функция СЧЁТЗ() ) и определяет ссылку на последний элемент в строке (функция ИНДЕКС() ), тем самым возвращает ссылку на диапазон B5:E5 .
Визуальное отображение динамического диапазона
Выделить текущий динамический диапазон можно с помощью Условного форматирования . В файле примера для ячеек диапазона B6:I14 применено правило Условного форматирования с формулой: = СТОЛБЕЦ(B6)=СТОЛБЕЦ(Продажи_за_месяц)
Условное форматирование автоматически выделяет серым цветом продажи текущего месяца , выбранного с помощью Выпадающего списка .