Данный курс - второй в рамках специализации «Практики анализа экономических данных: от простого к сложному». Курс посвящен «продвинутым» инструментам работы в среде MS Excel: применению сценариев, использованию инструмента «Подбор параметра», формированию дэшбордов и интерактивных диаграмм, использованию функций массивов, и др. Анализ данных – актуальная проблема для всех без исключения компаний. Главная цель анализа – дать информацию, полезную для принятия управленческих решений, лучшего понимания бизнеса. Научить анализировать данные сложно. Представленные курсы охватывают практики оперативной и управленческой аналитики в среде электронных таблиц MS Office. Используется подход к обучению анализу данных в среде Microsoft Excel через решение типовых задач, проецируемых на любую предметную область. Данный подход апробирован авторами на большом количестве групп повышения квалификации экономистов и менеджеров. В данном курсе слушатель сможет применить знания инструментов MS Excel для анализа деятельности компании. По завершении курса слушатели будут: Знать: - инструменты разработки аналитических отчетов: функции, фильтры, формулы массивов, сводные таблицы; - инструменты графического анализа экономических данных: интерактивных и других диаграмм; - правила применения инструментов ЧТО-ЕСЛИ; - инструменты разработки информационно-аналитических панелей. Уметь: - формировать аналитические отчеты, используя весь спектр инструментов MS Excel; - моделировать поведение показателей компании с помощью инструментов ЧТО-ЕСЛИ; - пользоваться инструментами фильтрации при сложных условиях; - формировать дэшборды с показателями деятельности компании. Владеть: - навыками выбора адекватных аналитических инструментов; - навыками совместной работы с экономическими данными; - инструментами работы с текстовыми данными.
Видео 2.6 "Консолидация данных из нескольких таблиц"
Loading...
From the course by Saint Petersburg State University
Практики управленческой аналитики в MS Excel
14 ratings
Saint Petersburg State University
14 ratings
Course 2 of 4 in the Specialization Практики анализа экономических данных от простого к сложному
From the lesson
Инструменты представления аналитических отчетов
Meet the Instructors
Лезина Татьяна Андреевнадоцент
Кафедра информационных систем в экономике
Иванова Виктория Валерьевнадоцент
Кафедра информационных систем в экономике
Гадасина Людмила Викторовнадоцент
Кафедра информационных систем в экономике
[МУЗЫКА]
[МУЗЫКА] Посмотрим
нечасто используемый инструмент «Консолидация»,
который, тем не менее, достаточно полезен для решения некоторых проблем.
Вот сейчас у меня на экране есть информация по продажам в трех
странах: в России, Германии и Венгрии товаров,
и, предположим, компании нужно получить агрегированные результаты,
то есть итоговые суммы продаж по всем трем странам.
Обычное суммирование с трех листов здесь не спасет.
Почему?
Потому что набор товаров, которые продаются в разных странах, – разный.
Мы видим что, другими словами, список товаров здесь не симметричный.
И обычная сумма с трех листов здесь просто не сработает именно
из-за несимметричности списков с названиями товаров.
Как можно получить итоговый результат?
В принципе это можно сделать с помощью функции ВПР.
Но вот инструмент Консолидация позволяет это сделать проще.
Давайте посмотрим, как он работает.
Запускать инструмент надо всегда с чистого листа.
Находится он на закладке Данные → Консолидация, и мы видим,
что здесь нам предлагают различные агрегирующие функции.
Сейчас нам нужна сумма и на следующем этапе нам нужно указать диапазоны данных,
которые, собственно, мы собираемся анализировать.
Указываем диапазон с итогами продаж на листе
«Россия» и этот диапазон добавляем в список тех диапазонов,
которые будут суммироваться.
Следующий диапазон, как не трудно догадаться,
выделяется на листе «Германия», и тоже добавляется в список
агрегируемых диапазонов, и то же самое делаем с диапазоном,
который находится на листе «Венгрия», и тоже его добавляем.
Для этой задачи и для большинства тех, которые встречаются на практике,
имеет смысл поставить две галки,
выделить две опции: «Использовать в качестве имен – подписи верхней строки»,
соответственно, суммы с трех листов
будут между собой сложены; и «значение левого столбца»
– здесь очень важен выбор этой опции, потому что именно она
обеспечит нам сопоставление товара 1 на всех трех листах.
Нажимаем ОК.
Я специально привела такие данные,
чтобы их легко было анализировать в итоговой таблице.
Здесь можно посмотреть, что, например,
если на листе «Россия» у меня все товары; на листе «Германия» у меня отсутствует
третий товар; на листе «Венгрия» третий товар тоже отсутствует.
На итоговом листе третий товар просуммирован правильно.
Дальше суммы тоже правильно посчитаны.
Чрезвычайно полезный инструмент, который, тем не менее,
имеет один недостаток вот именно в таком использовании.
Если исходные данные изменились, например я добавляю 00, то отчет не обновился.
Соответственно, вот эту самую Консолидацию нужно запустить еще раз.
Второй вариант использования инструмента Консолидация
следующий: запускаем с чистого листа опять ту же самую Консолидацию.
Опять выбираем сумму и снова указываем все три диапазона: первый добавляем,
второй добавляем, третий добавляем.
Опять выбираем опцию «Использовать в качестве имен подписи верхней строки»,
значение левого столбца, потому что у нас проблемы с данными ровно те же самые,
и сейчас попросим посмотреть «Создавать связи с исходными данными» и посмотрим,
собственно, какой будет результат.
Нажимаем ОК.
Результат получился следующий.
Во-первых, мы можем расшифровать каждое из
итоговых значений,
но самое главное, если сейчас у нас каких-то данных...
на одном из листов или на всех листах что-то меняется, предположим товар 3...
давайте тоже добавим 00,
то на листе с результатами мы видим,
что автоматически обновилось суммарное значение.
То есть, выставление опции связи с исходными данными
позволит вам видеть обновленные данные,
ну и при этом появляется – может быть для многих ситуаций и полезная –
возможность расшифровки конкретного полученного значения.
Рассмотрим еще один пример, который не очевидно решается без использования
Консолидации и с помощью Консолидации как раз решается очень красиво.
Компания имеет товары, которые точно также, по-прежнему,
продаются в трех странах.
На этот раз у нас не итоги продаж, а цены на товары,
которые зафиксированы в разных странах.
Они у меня сейчас одинаковые.
Этот пример был подсказан одной из слушательниц на практических занятиях.
Там проблема была в том, что в разных странах цены разные и
слушательнице дали полтора месяца для того,
чтобы сделать единый отчет, в котором был бы полный список товаров.
И в разных колонках стояли цены на товары в разных странах.
То есть, человеку нужно было получить вот такой отчет.
Сейчас я сделаю его покрупнее.
Опять же, проблема в том, что списки несимметричны.
Как получить такой отчет вручную?
Слушательница пошла по пути просто обычного копирования и вставки.
Полтора месяца ей на это уже не хватало, потому что номенклатура товаров была
больше тысячи, а, соответственно, листов с информацией было достаточно много.
Как эта проблема решается с помощью Консолидации?
Давайте попробуем обсудить.
Во-вторых, этот пример показывается не только потому,
что решается эта конкретная частная задача, но мне она нравится, потому что
этот пример позволяет проиллюстрировать, как можно угадать или догадаться,
какая последовательность инструментов решит конкретную вашу практическую задачу.
Итак, у вас есть несимметричные списки товаров.
Нам нужно получить вот такой отчет,
в котором цены на товары разнесены в разные столбцы.
Единственный инструмент, который у нас умеет работать с несимметричными списками,
это Консолидация.
Но Консолидация у нас или суммирует, или ищет среднее значение, или минимум,
или максимум.
Как нам преодолеть вот этот избыточный в данной ситуации сервис Консолидации?
Вспоминаем школьные знания, что сумма от одного слагаемого – это само слагаемое.
Тогда, если у нас столбцы будут называться по разному,
и мы попросим посчитать сумму от этих столбцов с разными названиями,
то мы, скорее всего, получим нужный результат.
Конкретно: что я предлагаю сделать?
Я предлагаю на каждом листе ввести название: «Цена Россия»,
«Цена Германия», «Цена Венгрия».
Можно было просто оставить названия стран...
Мы снова встретили ситуацию,
когда нам нужно немножко поменять исходные данные для того, чтобы можно было
применить инструменты, собственно, то, что мы обсуждали уже в диаграммах.
И теперь запускаем инструмент Консолидация,
выделяем диапазоны с чуть-чуть измененными данными,
добавить...
добавить.
Обязательно выставляем «Использовать в качестве имен – подписи верхней строки».
Это и будет нам обеспечивать применение функции суммы для
каждого столбца с уникальным именем.
И для того чтобы преодолеть проблему несимметричных списков товаров,
выставляем опцию «значение левого столбца».
Никакие связи с исходными данными в этом сюжете мне не нужны.
Нажимаю ОК.
И вот практически тот вид отчета,
который и планировалось получить, и мы решили немножко более
сложную задачу с помощью инструмента Консолидация.
Сейчас я хочу обсудить,
как преодолевать проблему выгруженных данных из информационных
систем или присланных клиентами, в которых есть объединенные ячейки.
Собственно, такого вида данные, как у меня сейчас на экране, не
позволяют применять ни инструмент сводных таблиц, ни инструмент Консолидация.
Как можно с этим справиться?
Во-первых, давайте обсудим: а что нужно нам изменить здесь для того,
чтобы можно было применить Сводные или Консолидацию?
У нас должно быть отменено объединение ячеек, и название товара 1,
так же как и товара 2, должно повторяться столько раз,
сколько соответствующих значений ему сопоставляется.
Проблема абсолютно несложно решается,
если знать следующую последовательность шагов.
Шаг 1: отменяется Объединение ячеек.
Не снимая выделения, выполняем следующую последовательность шагов.
Найти, выделить пустые ячейки.
Нажимаем ОК.
Не снимая выделения, вводим с клавиатуры «=» и указываем первое значение.
Здесь я щелкаю на ячейки товара 1.
А дальше тоже, не снимая выделения,
нажимаем комбинацию клавиш Ctrl + Enter.
10 тысяч, 15 тысяч ячеек автоматически заполнятся вот таким правильным образом,
и вот этот нехитрый прием позволит вам применять
к априори неправильным данным такие абсолютно важные инструменты,
как Сводные таблицы и Консолидация.
Explore our Catalog
Join for free and get personalized recommendations, updates and offers.
Coursera provides universal access to the world’s best education,
partnering with top universities and organizations to offer courses online.
© 2019 Coursera Inc. All rights reserved.
