rss
  •  

Быстрое преобразование даты

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

0

Дата в разных региональных настройках выглядит по-разному. И, особенно, если это выгрузка из сторонних источников, что досталась Вам уже в файле Excel, то возникает необходимость преобразовать это значение в полноценную дату для дальнейших расчетов.

Один из способов преобразование текста в дату описан в статье.

Однако, если необходимо осуществить быстрое преобразование, то можно выполнить и быстрее!

1) Выделить столбец с исходными данными.
На вкладке Данные [Data], в группе Работа с данными [Data Tools] выбрать Текст по столбцам [Text to Columns].

2) В диалоговом окне Мастер распределения текста по столбцам – шаг 1 из 3 [Convert Text to Columns Wizard – Step 1 of 3] не менять никаких настроек и нажать Далее [Next], на 2-м шаге поступить аналогично — нажимать Далее [Next].

3) На 3-м шаге необходимо задать Формат столбца данных [Column data format] — раскрыть список дата и выбрать вариант следования составляющих как в источнике, т.е. в данном случае ГДМ:

Нажать Готово!

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

| Категория: Приемы и советы, Работа с табличными массивами |

2

Возможно, Вы тоже сталкивались с ситуацией, когда построили один отчет сводной таблицы, а от Вас требуют сразу несколько подобных отчетов.

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

Сперва создается отчет. В область макета Фильтры располагается поле, по которому необходимо создавать отчеты и фильтровать. Например, поле Город.

При наличии курсора в любой ячейке отчета, на вкладке Анализ [Analyze] (Параметры в 2007-2010 версия Excel) в группе Сводная таблица [PivotTable] раскрыть список кнопки Параметры [Options] и выбрать Отобразить страницы фильтра отчета [Show Report Filter Pages].

В раскрывшемся окне выбрать поле, по которому фильтруем, например, поле Город:

Результат:

В поле Город 4 значения, поэтому и было получено именно 4 новых листа с соответствующими названиями городов:

 

 

 

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

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

4

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

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

Готово!