[МУЗЫКА]
[МУЗЫКА] Посмотрим
нечасто используемый инструмент «Консолидация»,
который, тем не менее, достаточно полезен для решения некоторых проблем.
Вот сейчас у меня на экране есть информация по продажам в трех
странах: в России, Германии и Венгрии товаров,
и, предположим, компании нужно получить агрегированные результаты,
то есть итоговые суммы продаж по всем трем странам.
Обычное суммирование с трех листов здесь не спасет.
Почему?
Потому что набор товаров, которые продаются в разных странах, – разный.
Мы видим что, другими словами, список товаров здесь не симметричный.
И обычная сумма с трех листов здесь просто не сработает именно
из-за несимметричности списков с названиями товаров.
Как можно получить итоговый результат?
В принципе это можно сделать с помощью функции ВПР.
Но вот инструмент Консолидация позволяет это сделать проще.
Давайте посмотрим, как он работает.
Запускать инструмент надо всегда с чистого листа.
Находится он на закладке Данные → Консолидация, и мы видим,
что здесь нам предлагают различные агрегирующие функции.
Сейчас нам нужна сумма и на следующем этапе нам нужно указать диапазоны данных,
которые, собственно, мы собираемся анализировать.
Указываем диапазон с итогами продаж на листе
«Россия» и этот диапазон добавляем в список тех диапазонов,
которые будут суммироваться.
Следующий диапазон, как не трудно догадаться,
выделяется на листе «Германия», и тоже добавляется в список
агрегируемых диапазонов, и то же самое делаем с диапазоном,
который находится на листе «Венгрия», и тоже его добавляем.
Для этой задачи и для большинства тех, которые встречаются на практике,
имеет смысл поставить две галки,
выделить две опции: «Использовать в качестве имен – подписи верхней строки»,
соответственно, суммы с трех листов
будут между собой сложены; и «значение левого столбца»
– здесь очень важен выбор этой опции, потому что именно она
обеспечит нам сопоставление товара 1 на всех трех листах.
Нажимаем ОК.
Я специально привела такие данные,
чтобы их легко было анализировать в итоговой таблице.
Здесь можно посмотреть, что, например,
если на листе «Россия» у меня все товары; на листе «Германия» у меня отсутствует
третий товар; на листе «Венгрия» третий товар тоже отсутствует.
На итоговом листе третий товар просуммирован правильно.
Дальше суммы тоже правильно посчитаны.
Чрезвычайно полезный инструмент, который, тем не менее,
имеет один недостаток вот именно в таком использовании.
Если исходные данные изменились, например я добавляю 00, то отчет не обновился.
Соответственно, вот эту самую Консолидацию нужно запустить еще раз.
Второй вариант использования инструмента Консолидация
следующий: запускаем с чистого листа опять ту же самую Консолидацию.
Опять выбираем сумму и снова указываем все три диапазона: первый добавляем,
второй добавляем, третий добавляем.
Опять выбираем опцию «Использовать в качестве имен подписи верхней строки»,
значение левого столбца, потому что у нас проблемы с данными ровно те же самые,
и сейчас попросим посмотреть «Создавать связи с исходными данными» и посмотрим,
собственно, какой будет результат.
Нажимаем ОК.
Результат получился следующий.
Во-первых, мы можем расшифровать каждое из
итоговых значений,
но самое главное, если сейчас у нас каких-то данных...
на одном из листов или на всех листах что-то меняется, предположим товар 3...
давайте тоже добавим 00,
то на листе с результатами мы видим,
что автоматически обновилось суммарное значение.
То есть, выставление опции связи с исходными данными
позволит вам видеть обновленные данные,
ну и при этом появляется – может быть для многих ситуаций и полезная –
возможность расшифровки конкретного полученного значения.
Рассмотрим еще один пример, который не очевидно решается без использования
Консолидации и с помощью Консолидации как раз решается очень красиво.
Компания имеет товары, которые точно также, по-прежнему,
продаются в трех странах.
На этот раз у нас не итоги продаж, а цены на товары,
которые зафиксированы в разных странах.
Они у меня сейчас одинаковые.
Этот пример был подсказан одной из слушательниц на практических занятиях.
Там проблема была в том, что в разных странах цены разные и
слушательнице дали полтора месяца для того,
чтобы сделать единый отчет, в котором был бы полный список товаров.
И в разных колонках стояли цены на товары в разных странах.
То есть, человеку нужно было получить вот такой отчет.
Сейчас я сделаю его покрупнее.
Опять же, проблема в том, что списки несимметричны.
Как получить такой отчет вручную?
Слушательница пошла по пути просто обычного копирования и вставки.
Полтора месяца ей на это уже не хватало, потому что номенклатура товаров была
больше тысячи, а, соответственно, листов с информацией было достаточно много.
Как эта проблема решается с помощью Консолидации?
Давайте попробуем обсудить.
Во-вторых, этот пример показывается не только потому,
что решается эта конкретная частная задача, но мне она нравится, потому что
этот пример позволяет проиллюстрировать, как можно угадать или догадаться,
какая последовательность инструментов решит конкретную вашу практическую задачу.
Итак, у вас есть несимметричные списки товаров.
Нам нужно получить вот такой отчет,
в котором цены на товары разнесены в разные столбцы.
Единственный инструмент, который у нас умеет работать с несимметричными списками,
это Консолидация.
Но Консолидация у нас или суммирует, или ищет среднее значение, или минимум,
или максимум.
Как нам преодолеть вот этот избыточный в данной ситуации сервис Консолидации?
Вспоминаем школьные знания, что сумма от одного слагаемого – это само слагаемое.
Тогда, если у нас столбцы будут называться по разному,
и мы попросим посчитать сумму от этих столбцов с разными названиями,
то мы, скорее всего, получим нужный результат.
Конкретно: что я предлагаю сделать?
Я предлагаю на каждом листе ввести название: «Цена Россия»,
«Цена Германия», «Цена Венгрия».
Можно было просто оставить названия стран...
Мы снова встретили ситуацию,
когда нам нужно немножко поменять исходные данные для того, чтобы можно было
применить инструменты, собственно, то, что мы обсуждали уже в диаграммах.
И теперь запускаем инструмент Консолидация,
выделяем диапазоны с чуть-чуть измененными данными,
добавить...
добавить.
Обязательно выставляем «Использовать в качестве имен – подписи верхней строки».
Это и будет нам обеспечивать применение функции суммы для
каждого столбца с уникальным именем.
И для того чтобы преодолеть проблему несимметричных списков товаров,
выставляем опцию «значение левого столбца».
Никакие связи с исходными данными в этом сюжете мне не нужны.
Нажимаю ОК.
И вот практически тот вид отчета,
который и планировалось получить, и мы решили немножко более
сложную задачу с помощью инструмента Консолидация.
Сейчас я хочу обсудить,
как преодолевать проблему выгруженных данных из информационных
систем или присланных клиентами, в которых есть объединенные ячейки.
Собственно, такого вида данные, как у меня сейчас на экране, не
позволяют применять ни инструмент сводных таблиц, ни инструмент Консолидация.
Как можно с этим справиться?
Во-первых, давайте обсудим: а что нужно нам изменить здесь для того,
чтобы можно было применить Сводные или Консолидацию?
У нас должно быть отменено объединение ячеек, и название товара 1,
так же как и товара 2, должно повторяться столько раз,
сколько соответствующих значений ему сопоставляется.
Проблема абсолютно несложно решается,
если знать следующую последовательность шагов.
Шаг 1: отменяется Объединение ячеек.
Не снимая выделения, выполняем следующую последовательность шагов.
Найти, выделить пустые ячейки.
Нажимаем ОК.
Не снимая выделения, вводим с клавиатуры «=» и указываем первое значение.
Здесь я щелкаю на ячейки товара 1.
А дальше тоже, не снимая выделения,
нажимаем комбинацию клавиш Ctrl + Enter.
10 тысяч, 15 тысяч ячеек автоматически заполнятся вот таким правильным образом,
и вот этот нехитрый прием позволит вам применять
к априори неправильным данным такие абсолютно важные инструменты,
как Сводные таблицы и Консолидация.