Задачи по сбору информации с разных листов в одну консолидированную таблицу встречаются постоянно:
Исходные данные выглядят однотипно, Например, на каждом листе в столбце А находятся Фамилии, а в столбце В – Количество заказов. Причем на каждом листе фамилии могут располагаться в разном порядке:
Знание функций ВПР [VLOOKUP] и ДВССЫЛ [INDIRECT] существенно облегчает решение данной задачи:
=ВПР(C$1;ДВССЫЛ($B2&”!A:B”);2;0) или в англоязычной версии:
=VLOOKUP(C$1;INDIRECT($B2&”!A:B”);2;0)
Функция ДВССЫЛ позволяет управлять адресом диапазона не путем обычного выделения, а путем формирования из 2-х частей: названия листа и диапазона!
Больше знаний – быстрее решение!
Потребность в написании формул в несколько строк может быть продиктована желаем более удобно её читать и быстрее понимать алгоритм вычисления, а при необходимости и редактировать формулу.
При обычном вводе формулы всё прописывается в одну строку, а при условии, что формула “длинная”, происходит перемещение символов на следующую строку по мере не хватки места в предыдущей. И такая формула не всегда наглядна и легка для понимания:
Чтобы добавить наглядности, можно принудительно ставить разрывы строк, используя комбинацию клавиш Alt+Enter, при необходимости увеличить отступ слева:
И пусть Ваши формулы будут наглядными 🙂
Автор: Ольга Кулешова | Категория: Надстройки Power, Приемы и советы | Опубликовано 29-01-2018
10
Существуют разные способы транспонирования. Более практичный – с помощью PowerQuery.
Сперва таблицу надо добавить в редактор запросов PowerQuery. Т.к. источник в текущем файле, в него же мы будем размещать результат, то выделить любую ячейку таблицы и на вкладке Данные [Date] в группе Скачать & Преобразовать [Get & Transform] выбрать Из таблицы [From Table]. Нажать ОК.
Открывается окно редактора запросов:
В правой части область Параметры запроса [Query Settings]. Сразу удалить шаг Измененный тип [Changed Type] и выполнить следующие действия, используя команды с вкладки Преобразование [Transform]:
- Использовать первую строку в качестве заголовков [Use First Row as Headers] – Использовать заголовки как первую строку [Use Headers as First Row].
Удалить добавленный шаг Измененный тип [Changed Type].
- Транспонировать [Transpose].
- Использовать первую строку в качестве заголовков [Use First Row as Headers] – Использовать первую строку в качестве заголовков [Use First Row as Headers].
Таблица трансформировалась:
Теперь можно выгрузить на лист – на вкладке Главная [Home] нажать кнопку Закрыть и загрузить [Close & Load]. Таблица-результат будет размещена на новом листе:
Если в дальнейшем в исходную таблицу будут добавлены новые записи или поля, то выполнив обновление запроса, результат будет снова актуален!