Данный курс - второй в рамках специализации «Практики анализа экономических данных: от простого к сложному». Курс посвящен «продвинутым» инструментам работы в среде MS Excel: применению сценариев, использованию инструмента «Подбор параметра», формированию дэшбордов и интерактивных диаграмм, использованию функций массивов, и др. Анализ данных – актуальная проблема для всех без исключения компаний. Главная цель анализа – дать информацию, полезную для принятия управленческих решений, лучшего понимания бизнеса. Научить анализировать данные сложно. Представленные курсы охватывают практики оперативной и управленческой аналитики в среде электронных таблиц MS Office. Используется подход к обучению анализу данных в среде Microsoft Excel через решение типовых задач, проецируемых на любую предметную область. Данный подход апробирован авторами на большом количестве групп повышения квалификации экономистов и менеджеров. В данном курсе слушатель сможет применить знания инструментов MS Excel для анализа деятельности компании. По завершении курса слушатели будут: Знать: - инструменты разработки аналитических отчетов: функции, фильтры, формулы массивов, сводные таблицы; - инструменты графического анализа экономических данных: интерактивных и других диаграмм; - правила применения инструментов ЧТО-ЕСЛИ; - инструменты разработки информационно-аналитических панелей. Уметь: - формировать аналитические отчеты, используя весь спектр инструментов MS Excel; - моделировать поведение показателей компании с помощью инструментов ЧТО-ЕСЛИ; - пользоваться инструментами фильтрации при сложных условиях; - формировать дэшборды с показателями деятельности компании. Владеть: - навыками выбора адекватных аналитических инструментов; - навыками совместной работы с экономическими данными; - инструментами работы с текстовыми данными.
Видео 3.4 "Продвинутые инструменты формирования отчетов: функция СМЕЩ"
Loading...
Do curso por Saint Petersburg State University
Практики управленческой аналитики в MS Excel
6 ratings
Na lição
Формирование аналитических отчетов в Microsoft Excel
Conheça os instrutores
Лезина Татьяна Андреевнадоцент
Кафедра информационных систем в экономике
Иванова Виктория Валерьевнадоцент
Кафедра информационных систем в экономике
Гадасина Людмила Викторовнадоцент
Кафедра информационных систем в экономике
[МУЗЫКА]
[МУЗЫКА] Практические
задачи всегда сложнее, чем учебные.
Мой опыт обсуждения проблем анализа данных говорит о том,
что ряд нетривиальных задач решается с помощью функции СМЕЩ.
Она не очень очевидна в использовании.
И я приведу два примера, в которых употребление,
использование этой функции играет существенную роль.
Очень часто, например, контрагенты присылают информацию в том виде,
в котором им удобно, а не в том виде,
в котором нам удобно анализировать представленную информацию.
Достаточно часто спрашивают нас, что делать с такой проблемой,
с проблемой, которая представлена сейчас на экране.
И название товара, и его цена размещены в каждой ячейке.
Естественно, для того чтобы проводить какие-то расчеты,
нам нужно из этих ячеек с названиями, ценами, вытащить только цену.
Как это сделать?
Существуют ли какие-то механизмы или технологии, или функции, приемы,
которые бы позволили как-то вытащить только число в данной ситуации,
соответствующее цене товара?
Это не самая сложная, но все равно достаточно неприятная ситуация,
та, которая представлена на экране.
И как можно справиться, например, с этой проблемой?
Для начала применим для решения
поставленной задачи — выделение цены в отдельный столбец,
применим такую полезную очень возможность в Microsoft Excel —
разбиение содержания ячеек
на различные столбцы, то есть содержание, которое состоит из нескольких слов.
Конкретно, выделяю те ячейки, которые у меня сейчас содержат такую неправильную,
с точки зрения анализа организации данных информацию, и выполняю вот эту самую
обещанную полезную команду: Данные, Текст по столбцам.
Не спеша, читаем, что нам предлагается здесь делать.
И нам предлагается указать формат данных.
У нас здесь ситуация, когда слова в ячейках разделены разделителями-пробелами.
Поэтому я оставляю выбор разделителями и нажимаю кнопку «Далее».
В следующем окне требуется совершенно естественная вещь — указать,
что является символом- разделителем.
У нас здесь разделителем, как уже сказано, является пробел, я его оставляю.
И уже сейчас мы видим образец, как здесь называется, разбора данных.
Мы видим эскиз того, что получится в будущем.
Нажимаем Далее, нажимаем Готово.
Совершенно замечательный результат, но пугающий тем,
что значение цен находится в разных столбцах.
И как бы нам вот эти значения цен: 150, 200, 200, 100,
300 автоматически поместить в единый столбец с ценами?
Как раз вот это действие можно реализовать с помощью функции СМЕЩ.
Итак, давайте запустим функцию СМЕЩ.
Она находится в категории Ссылки и массивы.
И посмотрим, что от нас требуется.
Диалог не очень комфортный, но, тем не менее.
Что такое ссылка?
Ссылка — это адрес ячейки, от которой будет проводиться отчет.
И вообще говоря, функция смещения позволяет указывать границы не очевидных
или плавающих диапазонов или, точнее говоря, диапазонов с плавающими границами.
Итак, ссылка — это, условно говоря, левый верхний угол, от которого начнется отсчет.
Смещение по строкам — куда нам нужно сдвинуться, начиная от той ячейки,
которую мы указали.
И смещение по столбцам — куда нам нужно сдвинуться,
чтобы получить соответствующее значение, например, вот в этом примере по столбцам,
для того чтобы получить нужное значение.
Итак, давайте спланируем наши действия.
Если мы укажем, что нам нужно отсчитывать от ячейки,
в которой находится первое слово — название товара.
Вниз мы никуда смещаться не будем.
А куда нам нужно сместиться направо?
А направо нам каждый раз, чтобы вытащить цену,
нужно смещаться на разное количество столбцов,
но количество или номер столбца,
в котором находится цена, в каждой строке разное.
Но мы его можем посчитать.
Поэтому чтобы можно было применить вот эту очень полезную функцию смещения,
нам нужно сначала подготовиться.
Я решу эту задачу в два шага.
Итак, сначала я подсчитаю количество не пустых ячеек,
и, соответственно, это даст мне возможность найти номер столбца,
в котором находится собственно цена.
Для этого мы используем функцию статистическую,
которая называется Счет значений, Счет З,
которая подсчитывает количество не пустых ячеек в диапазоне.
Я выделю только вот эти ячейки,
вообще говоря, даже с запасом выделю,
я хочу учесть все возможные варианты,
все возможные варианты с точки зрения количества слов
в названии товара и цене, и нажимаю OK.
Если я сейчас скопирую эту формулу, то мы увидим,
что относительно первого столбца всего значения у нас: раз, два, три.
Здесь всего значения: раз, два, три, четыре, а цена находится, соответственно,
в столбце, номер которого на единицу меньше,
чем у нас полученные значения в столбике F.
То есть цена для первого товара находится во втором
столбце относительно столбца А,
здесь она находится тоже во втором столбце, а здесь в третьем, то есть 4 − 1.
Попробуем теперь вытащить цены автоматически.
Запускаем функцию СМЕЩ.
Было обещано, что именно она поможет нам решить эту проблему.
И, собственно, теперь указываем
координаты того диапазона или координаты ячейки,
в которой находится цена.
Заметим, для каждого товара координаты ячейки будут разные.
Итак, мы говорим, начиная от этого значения по строкам,
никуда сдвигаться не надо, а вот направо сдвиньтесь, пожалуйста,
на то количество столбцов, которое мы посчитали,
минус один, потому что столбец с рублями мы должны проигнорировать.
Давайте нажмем OK и увидим, что у нас для первого товара совершенно
благополучно цена в ячейке G2 появилась.
Копируем полученную формулу и видим,
что обещанный результат у нас сейчас на экране.
Итак, давайте еще под другими словами попробуем сформулировать этапы решения
задачи.
Функция Счет значений посчитала количество не пустых ячеек в диапазоне
или для каждого товара в строке,
количество не пустых ячеек, в которых содержится информация о товаре после того,
как название и цена были разбиты по разным столбцам.
Мы заметили, что цена товара каждый раз находится в предпоследнем столбце.
Соответственно, если у меня три — это количество не пустых
ячеек кроме первого названия,
в котором находится цена, то собственно, цена будет находиться в 3 − 1,
предпоследней, во втором с начала столбце.
В этой ситуации у меня цена находится (давайте посмотрим) раз, два, три,
четыре ячейки.
Цена находится: 4 − 1 — в третьем столбце.
То есть мы можем теперь указать место, где находится, собственно, цена.
Другое дело нам нужно, и функция СМЕЩ нам в этом помогла,
указать, как найти ячейку с ценой.
Итак, функция СМЕЩ устроена была таким образом: начиная от первой ячейки,
в которой обязательно находится первое слово — название товара,
не смещаясь вниз, а смещаясь вправо на значение,
которое было сосчитано с помощью функции СЧЕТ З − 1.
Почему, я только что объясняла.
Мы как раз получаем значение цены в отдельном столбце,
и копирование формулы дает нам очень важный результат — все цены
выстраиваются в отдельном столбце.
Рассмотрим еще один пример применения функции СМЕЩ.
При работе с именнованными диапазонами на практике очень важно,
чтобы именнованные диапазоны впитывали в себя новые строки.
Для этого часто используют «умные» таблицы, но «умные» таблицы при
удалении последних строк не уменьшаются в границах.
Это часто оказывается проблемой для, например, формирования полей со списками.
Давайте посмотрим, как можно сделать так называемое динамическое имя
для диапазона с помощью функции СМЕЩ.
Выделяем диапазон и на закладке формулы Диспетчер имен создаем новое имя.
Давайте мы его назовем Динамические товары.
И руками укажем формулу, которая будет формировать или описывать
границы того диапазона, который будет расширяться при добавлении
записи или схлопываться, если какие-то товары из конца списка уходят.
Итак, это снова будет функция СМЕЩ.
Здесь нужно писать руками.
Указываем левый верхний угол диапазона.
Никакого смещения вниз нам здесь не требуется, вправо тоже.
Нам нужно только указать глубину и ширину диапазона.
Глубина диапазона будет автоматически рассчитываться с помощью той же самой
функции Счет З, как количество не пустых ячеек в диапазоне.
И дальше или руками, или если вам нужно чтобы границы
вправо расширялись, тоже можно функцию Счет З упомянуть.
Но здесь мне достаточно, чтобы ширина диапазона была 1.
Нажимаем OK.
Как проверять, что диапазон работает?
Изменить.
Видим, что у нас диапазон аккуратно выделен.
Теперь давайте добавим еще один товар.
И проверяем,
впитается ли он в этот самый диапазон.
Смотрите, сейчас у нас диапазон впитал новый добавленный товар.
Давайте попробуем удалить два последних товара.
Диспетчер имен.
Смотрите, граница диапазона поджалась.
Итак, это еще один пример применения функции СМЕЩ для решения
целого ряда сложных задач.
Естественно, этот пример может быть трансформирован для
конкретно вашей ситуации.
Explore nosso catálogo
Registre-se gratuitamente e obtenha recomendações, atualizações e ofertas personalizadas.
O Coursera proporciona acesso universal à melhor educação do mundo fazendo parcerias com as melhores universidades e organizações para oferecer cursos on-line.
© 2018 Coursera Inc. Todos os direitos reservados.
