Куперс

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

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

Визуализация данных с помощью диаграмм

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

Обратитесь к учебным таблицам Excel для получения дополнительной информации о типах диаграмм.

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

Создание комбинационных диаграмм

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

Мы создадим кластеризованную диаграмму столбцов для этих результатов.

Как вы заметили, трудно быстро визуализировать сравнение между целями и фактическими значениями на этом графике. Это не оказывает реального влияния на ваши результаты.

Лучший способ различения двух типов данных для сравнения значений — использование комбинированных диаграмм. В Excel 2013 и вышеприведенных версиях вы можете использовать комбинированные диаграммы для той же цели.

Используйте вертикальные столбцы для целевых значений и линию с маркерами для фактических значений.

• Перейдите на вкладку «ДИЗАЙН» под вкладкой «ИНСТРУМЕНТЫ ДИАГРАММ» на ленте.
• Нажмите Изменить тип диаграммы в группе Тип. Откроется диалоговое окно «Изменить тип диаграммы».

• Нажмите Combo.

• Измените Тип диаграммы для серии Фактическая на Линия с маркерами. Предварительный просмотр отображается в разделе «Пользовательская комбинация».

• Нажмите ОК.

Нажмите Combo.

Измените Тип диаграммы для серии Фактическая на Линия с маркерами. Предварительный просмотр отображается в разделе «Пользовательская комбинация».

Нажмите ОК.

Ваша индивидуальная комбинационная таблица будет отображена.

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

Однако этот тип представления не работает, когда диапазоны данных двух ваших значений данных значительно различаются.

Создание комбинированной диаграммы со вторичной осью

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

Если вы используете ту же комбинацию, что и раньше, вы получите следующее —

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

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

• Нажмите вкладку INSERT.
• Нажмите Combo в группе диаграмм.
• Нажмите Create Custom Combo Chart из выпадающего списка.

Появится диалоговое окно «Вставка диаграммы» с выделенным списком.

Для Типа диаграммы выберите —

• Линия с маркерами для серии № единиц
• Кластерный столбец для фактической прибыли серии
• Установите флажок «Вторичная ось» справа от номера серии и нажмите «ОК».

Предварительный просмотр вашей диаграммы появится под Custom Combination.

Ваша комбо-диаграмма отображается с Secondary Axis.

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

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

Различающие серии и оси категорий

Предположим, вы хотите спроектировать фактическую прибыль, полученную в 2013-2016 годах.

Создайте кластеризованный столбец для этих данных.

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

Удалить заголовок года в диапазоне данных.

Теперь год считается категорией, а не серией. Ваша диаграмма выглядит следующим образом —

Элементы диаграммы и стили диаграммы

Элементы диаграммы дают больше описания вашим диаграммам, помогая тем самым визуализировать ваши данные более осмысленно.

• Нажмите на диаграмму

Три кнопки появляются рядом с правым верхним углом графика —

• Элементы диаграммы
• Стили диаграммы
• Фильтры диаграммы

Для подробного объяснения этого обратитесь к учебнику Excel Charts.

• Нажмите Элементы диаграммы.
• Нажмите Метки данных.

• Нажмите Стили диаграммы
• Выберите стиль и цвет, которые соответствуют вашим данным.

Вы можете использовать Trendline для графического отображения трендов в данных. Вы можете расширить линию тренда на графике за пределы фактических данных, чтобы предсказать будущие значения.

Метки данных

Excel 2013 и более поздние версии предоставляют различные варианты отображения меток данных. Вы можете выбрать одну метку данных, отформатировать ее так, как вам нравится, а затем использовать текущую метку клонирования, чтобы скопировать форматирование в остальные метки данных на диаграмме.

Метки данных в диаграмме могут иметь эффекты, различающиеся по форме и размеру.

Также возможно отобразить содержимое ячейки как часть метки данных с полем «Вставить метку данных».

Быстрый макет

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

• Нажмите на график.
• Перейдите на вкладку «ДИЗАЙН» под «ИНСТРУМЕНТЫ»
• Нажмите Быстрый макет.

Различные возможные макеты будут отображаться. При перемещении по параметрам макета макет диаграммы меняется на этот конкретный параметр.

Выберите макет, который вам нравится. Диаграмма будет отображаться с выбранным макетом.

Использование изображений в столбчатых диаграммах

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

• Нажмите на столбец на диаграмме столбца.

• В Серии данных формата нажмите Заполнить.

• Выберите изображение.

• В разделе «Вставить картинку из» укажите имя файла или, если необходимо, буфер обмена, если вы ранее скопировали изображение.

Нажмите на столбец на диаграмме столбца.

В Серии данных формата нажмите Заполнить.

Выберите изображение.

В разделе «Вставить картинку из» укажите имя файла или, если необходимо, буфер обмена, если вы ранее скопировали изображение.

Выбранная вами картинка появится вместо столбцов на диаграмме.

Диаграмма группы

Возможно, вам придется представить результаты опроса клиентов продукта из разных регионов. Band Chart подходит для этой цели. Полосная диаграмма — это линейная диаграмма с добавленной заштрихованной областью для отображения верхней и нижней границ групп данных.

Предположим, ваши результаты опроса клиентов из восточных и западных регионов, по месяцам, —

Здесь в данных <50% — Низкий, 50% — 80% — Средний,> 80% — Высокий.

С помощью Band Chart вы можете отобразить результаты своего опроса следующим образом —

Создайте линейную диаграмму из ваших данных.

Измените тип диаграммы на —

• Ряды Востока и Запада с линией маркеров.
• Низкий, средний и высокий ряд в столбец с накоплением.

Ваша диаграмма выглядит следующим образом.

• Нажмите на один из столбцов.
• Измените ширину промежутка до 0% в формате Data Series.

Вы получите группы вместо столбцов.

Чтобы сделать диаграмму более презентабельной —

• Добавить заголовок диаграммы.
• Отрегулируйте диапазон вертикальной оси.
• Измените цвета полос на Зеленый-Желтый-Красный.
• Добавьте ярлыки в группы.

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

Диаграмма термометра

Когда вам нужно представить целевое значение и фактическое значение, вы можете легко создать диаграмму термометра в Excel, которая четко показывает эти значения.

С помощью диаграммы термометра вы можете отобразить ваши данные следующим образом —

Расположите ваши данные, как показано ниже —

• Выберите данные.
• Создайте диаграмму кластерных столбцов.

Как вы заметили, правая колонка является Target.

• Нажмите на столбец в диаграмме.
• Нажмите на переключатель строки / столбца на ленте.

• Щелкните правой кнопкой мыши на целевой колонке.
• Нажмите на Формат данных серии.
• Нажмите на Вторичную Ось.

Как вы наблюдаете, у Первичной Оси и Вторичной Оси есть разные диапазоны.

• Щелкните правой кнопкой мыши по основной оси.
• В параметрах Оси формата в разделе Границы введите 0 для минимума и 1 для максимума.
• Повторите то же самое для Вторичной Оси.

Первичная и вторичная оси будут установлены в 0% — 100%. Целевая колонка скрывает фактическую колонку.

• Щелкните правой кнопкой мыши видимый столбец (Target)
• В Серии форматирования данных выберите
  • Не заполнять для FILL
  • Сплошная линия для ГРАНИЦЫ
  • Синий для цвета

• В элементах диаграммы отмените выбор
  • Ось → Первичная Горизонтальная
  • Ось → Вторичная Вертикаль
  • Сетки
  • Заголовок диаграммы
• На графике щелкните правой кнопкой мыши на Первичной вертикальной оси
• В опциях формата оси нажмите на метки
• Для основного типа выберите Внутри

• Щелкните правой кнопкой мыши на Chart Area.
• В опциях Формат области диаграммы выберите
  • Не заполнять для FILL
  • Нет линии для границы

Измените размер области диаграммы, чтобы получить форму термометра.

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

• Вставьте прямоугольник, накладывая синюю прямоугольную часть на диаграмме.
• В параметрах формата формы выберите —
  • Градиентная заливка для FILL
  • Линейный для Типа
  • 180 0 для угла
• Установите градиентные остановки на 0%, 50% и 100%.
• Для градиента останавливается на 0% и 100%, выберите черный цвет.
• Для градиента на 50% выберите белый цвет.

• Вставьте овальную форму внизу.
• Форматировать фигуру с теми же параметрами.

Результатом является таблица термометров, с которой мы начали.

Диаграмма Ганта

Диаграмма Ганта — это диаграмма, на которой ряд горизонтальных линий показывает объем работы, выполненной в определенные периоды времени, относительно объема работы, запланированного на эти периоды.

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

Рассмотрим следующие данные, где столбец —

• Задача представляет Задачи в проекте
• Начало представляет количество дней с даты начала проекта
• Длительность представляет продолжительность задачи

Обратите внимание, что начало любой задачи — это начало предыдущей задачи + продолжительность. Это тот случай, когда задачи находятся в иерархии.

• Выберите данные.
• Создать столбчатую диаграмму с накоплением.

• Щелкните правой кнопкой мыши на Start Series.
• В параметрах «Форматировать ряд данных» выберите «Не заполнять».

• Щелкните правой кнопкой мыши по оси категорий.
• В параметрах формата оси выберите Категории в обратном порядке.

• В элементах диаграммы отмените выбор
  • легенда
  • Сетки
• Отформатируйте горизонтальную ось, чтобы
  • Отрегулируйте диапазон
  • Основные отметки тика с интервалом в 5 дней
  • Незначительные отметки тика с интервалом в 1 день
• Формат Data Series, чтобы он выглядел впечатляюще
• Дайте название диаграммы

Водопад Chart

Waterfall Chart — один из самых популярных инструментов визуализации, используемых в малых и крупных компаниях. Графики водопадов идеально подходят для того, чтобы показать, как вы достигли чистой стоимости, такой как чистый доход, путем разбивки совокупного эффекта положительных и отрицательных взносов.

Excel 2016 предоставляет тип диаграммы водопада. Если вы используете более ранние версии Excel, вы все равно можете создать диаграмму водопадов с помощью столбчатой ​​диаграммы с накоплением.

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

Рассмотрим следующие данные.

• Подготовьте данные для диаграммы водопада

• Убедитесь, что столбец «Чистый денежный поток» находится слева от столбца «Месяцы» (это потому, что этот столбец не будет включен при создании диаграммы)

• Добавьте 2 столбца — Увеличение и Уменьшение для положительных и отрицательных денежных потоков соответственно

• Добавить столбец Start — первый столбец в диаграмме с начальным значением в Net Cash Flow

• Добавить столбец Конец — последний столбец на графике с конечным значением в Чистом денежном потоке

• Добавить столбец с плавающей точкой, поддерживающий промежуточные столбцы.

• Вычислите значения для этих столбцов следующим образом

Подготовьте данные для диаграммы водопада

Убедитесь, что столбец «Чистый денежный поток» находится слева от столбца «Месяцы» (это потому, что этот столбец не будет включен при создании диаграммы)

Добавьте 2 столбца — Увеличение и Уменьшение для положительных и отрицательных денежных потоков соответственно

Добавить столбец Start — первый столбец в диаграмме с начальным значением в Net Cash Flow

Добавить столбец Конец — последний столбец на графике с конечным значением в Чистом денежном потоке

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

Вычислите значения для этих столбцов следующим образом

• В столбце Float вставьте строку в начале и в конце. Разместите n произвольное значение 50000. Это просто, чтобы иметь некоторое пространство слева и справа от графика.

В столбце Float вставьте строку в начале и в конце. Разместите n произвольное значение 50000. Это просто, чтобы иметь некоторое пространство слева и справа от графика.

Данные будут следующими.

• Выберите ячейки C2: H18 (столбец Исключить чистый денежный поток)
• Создать столбчатую диаграмму с накоплением

• Щелкните правой кнопкой мыши на Float Series.
• Нажмите Формат данных серии.
• В параметрах «Форматировать ряд данных» выберите «Не заполнять».

• Щелкните правой кнопкой мыши на Negative Series.
• Выберите Fill Color как Red.

• Щелкните правой кнопкой мыши на Positive Series.
• Выберите Fill Color как Зеленый.

• Щелкните правой кнопкой мыши на Start Series.
• Выберите Цвет заливки как Серый.
• Щелкните правой кнопкой мыши по Конец серии.
• Выберите Цвет заливки как Серый.
• Удалить легенду.

• Щелкните правой кнопкой мыши на любой серии
• В параметрах «Форматировать ряд данных» выберите «Ширина зазора» как 10% в разделе «Параметры ряда».

Дайте название диаграммы. Диаграмма водопада будет отображаться.

Sparklines

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

Вы можете добавить Sparklines с помощью инструмента Quick Analysis.

• Выберите данные, для которых вы хотите добавить Sparklines.
• Держите пустой столбец справа от данных для Спарклайнов.

Кнопка быстрого анализа появится в правом нижнем углу выбранных вами данных.

• Нажмите на Быстрый анализ кнопка. Появится панель быстрого анализа с различными параметрами.

Нажмите на Быстрый анализ кнопка. Появится панель быстрого анализа с различными параметрами.

Нажмите ИСКРЫ . Отображаемые параметры диаграммы основаны на данных и могут отличаться.

Нажмите Линия . Линейная диаграмма для каждой строки отображается в столбце справа от данных.

сводные диаграммы

Сводные диаграммы используются для графического суммирования данных и изучения сложных данных.

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

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

Вы можете создать сводную диаграмму из

• Сводная таблица.
• Таблица данных как отдельная без сводной таблицы.

Сводная диаграмма из сводной таблицы

Чтобы создать сводную диаграмму, выполните следующие действия:

• Нажмите на сводную таблицу.
• Нажмите АНАЛИЗ в разделе СРЕДСТВА СЧЕТА на ленте.
• Нажмите на сводную диаграмму. Откроется диалоговое окно «Вставка диаграммы».

Выберите Clustered Column из опции Column.

Нажмите ОК. Сводная диаграмма отображается.

Сводная диаграмма имеет три фильтра — Регион, Продавец и Месяц.

• Нажмите «Регион управления фильтром». Появится окно поиска со списком всех регионов. Флажки отображаются рядом с регионами.

• Выберите Восток и Юг.

Нажмите «Регион управления фильтром». Появится окно поиска со списком всех регионов. Флажки отображаются рядом с регионами.

Выберите Восток и Юг.

Отфильтрованные данные отображаются как в сводной диаграмме, так и в сводной таблице.

Сводная диаграмма без сводной таблицы

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

• Нажмите Таблица данных.
• Нажмите вкладку Вставка.
• Нажмите Сводная диаграмма в группе Графики. Откроется окно «Создание сводной диаграммы».
• Выберите таблицу / диапазон.
• Выберите место, где вы хотите разместить сводную диаграмму.

Вы можете выбрать ячейку в самом существующем рабочем листе или в новом рабочем листе. Нажмите ОК.

Пустая сводная диаграмма и пустая сводная таблица появляются вместе со списком полей сводной диаграммы для построения сводной диаграммы.

• Выберите поля, которые будут добавлены в сводную диаграмму

• Расположите поля, перетащив их в ФИЛЬТРЫ, ЛЕГЕНДЫ (СЕРИЯ), ОСЬ (КАТЕГОРИИ) и ЦЕННОСТИ

• Используйте элементы управления фильтра на сводной диаграмме, чтобы выбрать данные для размещения на сводной диаграмме.

Выберите поля, которые будут добавлены в сводную диаграмму

Расположите поля, перетащив их в ФИЛЬТРЫ, ЛЕГЕНДЫ (СЕРИЯ), ОСЬ (КАТЕГОРИИ) и ЦЕННОСТИ

Используйте элементы управления фильтра на сводной диаграмме, чтобы выбрать данные для размещения на сводной диаграмме.

Excel автоматически создаст связанную сводную таблицу.

Код курса: Т-ЭКСЕЛЬБ-Г Путеводитель

Пользователь ПК

Microsoft Excel

Бизнес-аналитика

Эту Программу повышения квалификации
в нашем Центре успешно закончили
141 человек!

09.01.03 Мастер по обработке цифровой информации

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

Получить необходимые знания и навыки вы сможете на комплексной программе «Бизнес-аналитика и продвинутая работа с данными в Microsoft Excel».

Программа состоит из 5 курсов:

• Microsoft Excel 2019/2016. Уровень 6. Бизнес-аналитика с использованием Power Pivot, Power Query и 3D Map
• Microsoft Excel 2019/2016. Уровень 7. Power Query в Microsoft Excel
• Практикум: Создание запросов Power Query в MS Excel
• Microsoft Excel 2019/2016. Уровень 8. Углублённое изучение DAX и Excel PowerPivot
• Microsoft Power BI Desktop для пользователей

На первом курсе программы вы научитесь с помощью надстройки PowerPivot загружать в Эксель данные из разных источников и формировать интуитивно понятные динамические отчеты.

Второй курс программы посвящен обработке данных перед анализом. Эта часть работы часто занимает большую часть времени и сил. Освоив на курсе расширенные возможности Power Query (Get & Transform), вы сможете быстро и легко загружать данные из любого источника в любом формате и преобразовывать их в тот вид и формат, который вам требуется.

На третьем курсе программы вы научитесь создавать сложные модели данных и писать нетривиальные конструкции на языке DAX.

Преимущества комплексной программы:

• Вы освоите весь набор продвинутых функций Эксель для обработки и анализа данных, что поможет значительно упростить бизнес-аналитику.
• Наши курсы читают сертифицированные тренеры Microsoft, знающие все скрытые «фишки» программы.
• Оплачивая всю программу, а не курсы по отдельности, вы получаете существенную скидку на 3-й курс.
• После окончания программы вы получите свидетельство центра или удостоверение о повышении квалификации.
• «Специалист» – лучший авторизованный учебный центр Microsoft в России, мы обучаем работе с решениями компании почти 20 лет!

Программа предназначена для аналитиков и разработчиков отчетов, продвинутых пользователей Excel.

Используйте продвинутые возможности Эксель для эффективной бизнес-аналитики! Записывайтесь на программу!

		с 10:00 до 17:00	Вечер или Выходные Стандартная цена	Онлайн	Записаться
	Частные лица	66 690	66 690	66 690
	Организации	71 890	71 890	71 890
	Первый взнос (для частных лиц)	13 300	13 300	13 300

«Оплата обучения в кредит: учеба сразу, а деньги потом! Потребительский кредит от Альфа-банка (не менее 5 рабочих дней до старта группы, сумма кредита 5-200 тыс. рублей, без первоначального взноса). От 3 559 руб./месяц. Начните обучение сейчас! Только для наших слушателей! Индивидуальная консультация по карьере совершенно бесплатно + ежемесячная рассылка вакансий от наших партнеров.

Дата

Другие даты

Продолжительность

108 ак. ч.

Комплексная программа

Специалист по бизнес — аналитике в Microsoft Excel и Power BI

Комплексная программа — 5 курсов.

Преподаватель

СидороваЕлена
Владимировна
Читать отзывы

Стоимость

Дата

Выбрать дату

Продолжительность

24 ак. ч.

Преподаватель

СидороваЕлена
Владимировна
Читать отзывы

Стоимость

Дата

Выбрать дату

Продолжительность

24 ак. ч.

Преподаватель

МаксимоваЛидия
Михайловна
Читать отзывы

Стоимость

Дата

Выбрать дату

Продолжительность

16 ак. ч.

Преподаватель

КулешоваОльга
Владимировна
Читать отзывы

Стоимость

Дата

Выбрать дату

Продолжительность

24 ак. ч.

Преподаватель

КозловАлексей
Олегович
Читать отзывы

Стоимость

Дата

Выбрать дату

Продолжительность

20 ак. ч.

Преподаватель

КулешоваОльга
Владимировна
Читать отзывы

Стоимость

Заказ добавлен в Корзину.
Для завершения оформления, пожалуйста, перейдите в Корзину!

Документы об окончании

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

Удостоверение*

Свидетельство

Cертификат международного образца

*Для получения удостоверения вам необходимо предоставить копию диплома о высшем или среднем профессиональном образовании.

По окончании каждого отдельного курса, входящего в Программу повышения квалификации, в вашем личном кабинете формируются электронные сертификаты об обучении по каждому отдельному курсу. По окончании обучения по Программе повышения квалификации вы получаете Удостоверение о повышении квалификации по этой программе. Если вы проходили курсы, входящие в Программу повышения квалификации, по отдельности, то вы получите Удостоверения за каждый пройденный курс.

Обязательно уточняйте перед заключением договора, какой документ Вам будет выдан после окончания обучения!

Все документы Центра

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

Для начала работы нужно активировать дополнительный пакет анализа

Первое, с чего нужно начать — установить надстройку. Весь процесс рассмотрим на примере версии Microsoft Excel 2010. Делается это следующим образом. Перейдите на вкладку «Файл» и нажмите «Параметры», затем выберите раздел «Надстройки». Далее, отыщите «Надстройки Excel» и кликните по кнопке «Перейти». В открывшемся окне доступных надстроек отметьте пункт «Пакет анализа» и подтвердите выбор, нажав «ОК». В случае, если необходимого пункта нет в списке, вам придётся найти его вручную, воспользовавшись кнопкой «Обзор».

Так как вам ещё могут пригодиться функции Visual Basic, желательно также установить «Пакет анализа VBA». Делается это аналогичным образом, разница только в том, что вам придётся выбрать другую надстройку из списка. Если вы точно знаете, что Visual Basic вам не нужен, то можно ничего больше не загружать.

Процесс установки для версии Excel 2013 точно такой же. Для версии программы 2007, разница только в том, что вместо меню «Файл» необходимо нажать кнопку Microsoft Office, далее следуйте по пунктам, как описано для Эксель 2010. Также перед тем как начать загрузку, убедитесь, что на вашем компьютере установлена последняя версия NET Framework.

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

• Дисперсионный. Вы можете выбрать из предложенных вариантов в списке (однофакторный, двухфакторный с повторениями, двухфакторный без повторений). Всё зависит от количества факторов и выборок.
• Корреляционный. Позволяет построить корреляционную матрицу. Такой подход даёт возможность определить, связаны ли большие значения одной группы данных с большими значениями другой группы. Или проделать то же самое для маленьких значений. Это называется отрицательной корреляцией.
• Ковариационный. Используется в случаях, когда необходимо посчитать функцию «КОВАРИАЦИЯ.Г». Также такой тип анализа позволяет определить, ассоциированы ли группы данных по величине.
• Фурье. Применяется, когда необходимо решить задачу в линейных системах либо проанализировать периодические данные.
• Гистограмма. Очень удобно использовать для решения задач типа: распределить значение успеваемости студентов в группе.
• Скользящее среднее. Применяется, когда нужно рассчитать значения, находящиеся в прогнозируемом периоде, основываясь на среднем значении переменной.
• Генерация случайных чисел. Заполняет указанный диапазон случайными числами.
• Ранг и перцентиль. Нужен, чтобы вывести таблицу с порядковым и центральным рангами.
• Регрессия. Позволяет подобрать график набора наблюдений, применяя метод наименьших квадратов.
• Выборка. Применяется в случаях, когда нужно создать выборку из генеральной совокупности, в качестве которой выступает входной диапазон.
• Т-тест. Даёт возможность проверить на равенство значения по каждой выборке. Существует несколько разновидностей этого инструмента. Выбирайте тот вариант, который больше подходит для решения текущей задачи.
• Z-тест. Этот инструмент нужен, чтобы проверять гипотезу о неразличии между средними одной и другой генеральных совокупностей относительно одно- и двусторонней гипотез.

Excel содержит множество мощных инструментов для выполнения сложных математических вычислений, например, Анализ «что если”. Этот инструмент способен экспериментальным путем найти решение по Вашим исходным данным, даже если данные являются неполными. В этом уроке Вы узнаете, как использовать один из инструментов анализа «что если” под названием Подбор параметра.

Подбор параметра

Каждый раз при использовании формулы или функции в Excel Вы собираете исходные значения вместе, чтобы получить результат. Подбор параметра работает наоборот. Он позволяет, опираясь на конечный результат, вычислить исходное значение, которое даст такой результат. Далее мы приведем несколько примеров, чтобы показать, как работает Подбор параметра.

Как использовать Подбор параметра (пример 1):

Представьте, что Вы поступаете в определенное учебное заведение. На данный момент Вами набрано 65 баллов, а необходимо минимум 70 баллов, чтобы пройти отбор. К счастью, есть последнее задание, которое способно повысить количество Ваших баллов. В данной ситуации можно воспользоваться Подбором параметра, чтобы выяснить, какой балл необходимо получить за последнее задание, чтобы поступить в учебное заведение.

На изображении ниже видно, что Ваши баллы за первые два задания (тест и письменная работа) составляют 58, 70, 72 и 60. Несмотря на то, что мы не знаем, каким будет балл за последнее задание (тестирование 3), мы можем написать формулу, которая вычислит средний балл сразу за все задания. Все, что нам необходимо, это вычислить среднее арифметическое для всех пяти оценок. Для этого введите выражение =СРЗНАЧ(B2:B6) в ячейку B7. После того как Вы примените Подбор параметра к решению этой задачи, в ячейке B6 отобразится минимальный балл, который необходимо получить, чтобы поступить в учебное заведение.

1. Выберите ячейку, значение которой необходимо получить. Каждый раз при использовании инструмента Подбор параметра, Вам необходимо выбирать ячейку, которая уже содержит формулу или функцию. В нашем случае мы выберем ячейку B7, поскольку она содержит формулу =СРЗНАЧ(B2:B6).
2. На вкладке Данные выберите команду Анализ «что если”, а затем в выпадающем меню нажмите Подбор параметра.
3. Появится диалоговое окно с тремя полями:
   • Установить в ячейке – ячейка, которая содержит требуемый результат. В нашем случае это ячейка B7 и мы уже выделили ее.
   • Значение – требуемый результат, т.е. результат, который должен получиться в ячейке B7. В нашем примере мы введем 70, поскольку нужно набрать минимум 70 баллов, чтобы поступить.
   • Изменяя значение ячейки – ячейка, куда Excel выведет результат. В нашем случае мы выберем ячейку B6, поскольку хотим узнать оценку, которую требуется получить на последнем задании.
4. Выполнив все шаги, нажмите ОК.
5. Excel вычислит результат и в диалоговом окне Результат подбора параметра сообщит решение, если оно есть. Нажмите ОК.
6. Результат появится в указанной ячейке. В нашем примере Подбор параметра установил, что требуется получить минимум 90 баллов за последнее задание, чтобы пройти дальше.

Как использовать Подбор параметра (пример 2):

Давайте представим, что Вы планируете событие и хотите пригласить такое количество гостей, чтобы не превысить бюджет в $500. Можно воспользоваться Подбором параметра, чтобы вычислить число гостей, которое можно пригласить. В следующем примере ячейка B4 содержит формулу =B1+B2*B3, которая суммирует общую стоимость аренды помещения и стоимость приема всех гостей (цена за 1 гостя умножается на их количество).

1. Выделите ячейку, значение которой необходимо изменить. В нашем случае мы выделим ячейку B4.
2. На вкладке Данные выберите команду Анализ «что если”, а затем в выпадающем меню нажмите Подбор параметра.
3. Появится диалоговое окно с тремя полями:
   • Установить в ячейке – ячейка, которая содержит требуемый результат. В нашем примере ячейка B4 уже выделена.
   • Значение – требуемый результат. Мы введем 500, поскольку допустимо потратить $500.
   • Изменяя значение ячейки – ячейка, куда Excel выведет результат. Мы выделим ячейку B3, поскольку требуется вычислить количество гостей, которое можно пригласить, не превысив бюджет в $500.
4. Выполнив все пункты, нажмите ОК.
5. Диалоговое окно Результат подбора параметра сообщит, удалось ли найти решение. Нажмите OK.
6. Результат появится в указанной ячейке. В нашем случае Подбор параметра вычислил результат 18,62. Поскольку мы считаем количество гостей, то наш окончательный ответ должен быть целым числом. Мы можем округлить результат в большую или меньшую сторону. Округлив количество гостей в большую сторону, мы превысим заданный бюджет, значит, остановимся на 18-ти гостях.

Как видно из предыдущего примера, бывают ситуации, которые требуют целое число в качестве результата. Если Подбор параметра выдает десятичное значение, необходимо округлить его в большую или меньшую сторону в зависимости от ситуации.

Другие типы анализа «что если”

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

• Диспетчер сценариев позволяет подставлять значения сразу в несколько ячеек (до 32). Вы можете создать несколько сценариев, а затем сравнить их, не изменяя значений вручную. В следующем примере мы используем сценарии, чтобы сравнить несколько различных мест для проведения мероприятия.
• Таблицы данных позволяют взять одну из двух переменных в формуле и заменить ее любым количеством значений, а полученные результаты свести в таблицу. Этот инструмент обладает широчайшими возможностями, поскольку выводит сразу множество результатов, в отличие от Диспетчера сценариев или Подбора параметра. В следующем примере видно 24 возможных результата по ежемесячным платежам за кредит:

Оцените качество статьи. Нам важно ваше мнение: