rss
  •  
IV ЕЖЕГОДНАЯ КОНФЕРЕНЦИЯ ПО EXCEL И АНАЛИТИКЕ

Выбор данных на пересечении с использованием функции ИНДЕКС и списками

| Категория: Приемы и советы, Формулы и функции |

5

Один из вариантов решения задач по получению данных из табличного массива уже был описан в статье ранее. Рассмотрим и ещё одну вариацию решения подобной задачи с использованием только функции ИНДЕКС и списков.

indlist1

Сперва подготовим два списка: для выбора названия месяца и названия города.

Для создания списка необходимо отобразить вкладку Разработчик [Developer]: выбрать Файл [File], Параметры [Options], в разделе Настройка ленты [Customize Ribbon] в группе Настроить ленту [Customize Ribbon], выбрать Разработчик [Developer]. ОК.

1) На вкладке Разработчик [Developer] в гindlist2руппе Элементы управления [Controls], раскрыть список команды Вставить [Insert] и выбрать в группе Элементы управления формы [Form Controls] элемент Список (Элемент управления формы) [List Box (Form Control)].
2) Щелкнуть левой кнопкой мыши на листе в предполагаемом месте расположения элемента.
3) Настроить параметры элемента – щелкнуть правой кнопкой мыши по элементу, выбрать Формат Объекта [Format Control]. На вкладке Элемент управления [Control] задать:
Формировать список по диапазону [Input Range] – например, выделяем список с названиями месяцев (ячейки B3:B14);
Связь с ячейкой [Cell link] – указать ячейку, в которую размещается порядковый номер выбранного элемента списка (ячейка H2).

indlist3

По аналогии список для городов и привязка к ячейке I2.

Примечание: исходный диапазон для формирования списка должен быть вертикальным.

А теперь пора и формулу написать!

ИНДЕКС(Массив;Номер_строки;Номер_столбца)
[INDEX(Array;Row_num;Column_num)] – возвращает значение ячейки из диапазона, заданной номером строки и номером столбца.
Массив [Array] – таблица (массив), состоит из строк и столбцов. Выделяем в рамках высоты и ширины диапазонов, используемых в списках месяцев и городов
Номер_строки [Row_num] – номер строки в массиве, из которой нужно определить значение. Ссылка на ячейку с которой установлена связь списка месяцев (например, H2)
Номер_столбца [Column_num] – номер столбца в массиве, из которого определяется значение. Ссылка на ячейку с которой установлена связь списка городов (например, I2).
indlist4
Чтобы позиции выбранного месяца и выбранного города не отвлекали, их можно скрыть – выделить ячейки, в диалоговом окне Формат ячеек [Format Cells] на вкладке Число [Number], выбрать числовой формат (все форматы) [Custom], в поле Тип [Type] ввести ;;; и нажать ОК.
indlist5

Готово!

Транспонирование данных

| Категория: Приемы и советы, Формулы и функции |

2

Чаще всего известна возможность транспонирования данных (преобразование строк в столбцы или наоборот столбцов в строки) с использованием специальной вставки, но в этом случае результат – это значения. Во многих случаях, этого может быть более чем достаточно: решили задачу и забыли. Однако, если возникает необходимость иметь привязку с исходными данными, то можно задействовать функцию ТРАНСП и формулу массива.

ТРАНСП(Массив) [TRANSPOSE(Array)]– преобразует вертикальный диапазон в горизонтальный, или наоборот.
Массив [Array] – диапазон ячеек на листе или массив значений, который нужно транспонировать.

transp1

1. Выделить диапазон ячеек для размещения транспонированной таблицы (ячейки G2:K5).
2. Ввести с клавиатуры знак =.
3. Выбрать функцию ТРАНСП, выделить исходную таблицу (ячейки B2:E9).
4. Нажать Ctrl+Shift+Enter.

{=ТРАНСП(B2:E6)} [{=TRANSPOSE(B2:E6)}]- транспонирует диапазон ячеек В2:Е6 в выделенные ячейки.

Быстрое получение данных из табличного массива

| Категория: Формулы и функции |

6

Каждая третья задача пользователя – это получение данных из табличного массива при заданных условиях:

cros1

Наиболее часто для решения подобных задач используют функции для работы с табличными данными, например: ПОИСКПОЗ, ИНДЕКС, ДВССЫЛ, СМЕЩ и т.д.

Однако, если список критериев уникален и при этом критерии располагаются в левом/правом столбце или верхней/нижней строке, то решение может быть совсем лаконичным.

1) Необходимо присвоить диапазонам имена – выделить таблицу так, чтобы в левом столбце и верхней строке были критерии, нажать клавиши CTRL+SHIFT+F3, чтобы создать имена по выделенным данным:

cros2

Выбрать создать имена из значений в зависимости от их расположения. в данном примере: в строке выше и в столбце слева, ОК.

2) создать простую формулу пересечения диапазонов: ИмяДиапазона1(ПРОБЕЛ)ИмяДиапазона2

cros3

Однако, такая формула не имеет гибкости, т.к. вводить имена в этом случае нужно с клавиатуры снова и снова.

Усовершенствуем!

1) Создадим в 2-х ячейках Н4 и H5 списки для выбора критериев.

2) Напишем формулу, ссылаясь на эти ячейки: =H4 H5

cros4

Но результата в этом случае не будет. т.к. пересечение 2-х текстовых значений не может давать никакого результата. Значит, необходимо сделать так, чтобы значение, которое присутствует в ячейке Н4 (H5) становилось именем. С подобной задачей отлично справляется функция ДВССЫЛ [INDIRECT].

Таким образом, конечная формула будет: =ДВССЫЛ(H4) ДВССЫЛ(H5) или =INDIRECT(H4) INDIRECT(H5)

cros5

Теперь можно легко выбирать месяца и города в списках и результат не заставит себя ждать!