Юлия Перминова
Тренер Учебного центра Softline с 2008 года.
Базовый инструмент для работы с огромным количеством неструктурированных данных, из которых можно быстро сделать выводы и не возиться с фильтрацией и сортировкой вручную. Сводные таблицы можно создать с помощью нескольких действий и быстро настроить в зависимости от того, как именно вы хотите отобразить результаты.
Полезное дополнение. Вы также можете создавать сводные диаграммы на основе сводных таблиц, которые будут автоматически обновляться при их изменении. Это полезно, если вам, например, нужно регулярно создавать отчёты по одним и тем же параметрам.
Исходные данные могут быть любыми: данные по продажам, отгрузкам, доставкам и так далее.
Перед нами таблица с неструктурированными данными. Мы можем их систематизировать и настроить отображение тех данных, которые есть у нас в таблице. «Сумму заказов» отправляем в «Значения», а «Продавцов», «Дату продажи» - в «Строки». По данным разных продавцов за разные годы тут же посчитались суммы. При необходимости можно развернуть каждый год, квартал или месяц - получим более детальную информацию за конкретный период.
Набор опций будет зависеть от количества столбцов. Например, у нас пять столбцов. Их нужно просто правильно расположить и выбрать, что мы хотим показать. Скажем, сумму.
Можно её детализировать, например, по странам. Переносим «Страны».
Можно посмотреть результаты по продавцам. Меняем «Страну» на «Продавцов». По продавцам результаты будут такие.
Этот способ визуализации данных с географической привязкой позволяет анализировать данные, находить закономерности, имеющие региональное происхождение.
Полезное дополнение. Координаты нигде прописывать не нужно - достаточно лишь корректно указать географическое название в таблице.
Точки на карте - это наши города. Но просто города нам не очень интересны - интересно увидеть информацию, привязанную к этим городам. Например, суммы, которые можно отобразить через высоту столбика. При наведении курсора на столбик показывается сумма.
Также достаточно информативной является круговая диаграмма по годам. Размер круга задаётся суммой.
Зачастую в бизнес-процессах наблюдаются сезонные закономерности, которые необходимо учитывать при планировании. Лист прогноза - наиболее точный инструмент для прогнозирования в Excel, чем все функции, которые были до этого и есть сейчас. Его можно использовать для планирования деятельности коммерческих, финансовых, маркетинговых и других служб.
Полезное дополнение. Для расчёта прогноза потребуются данные за более ранние периоды. Точность прогнозирования зависит от количества данных по периодам - лучше не меньше, чем за год. Вам требуются одинаковые интервалы между точками данных (например, месяц или равное количество дней).
В примере ниже у нас есть данные за 2011, 2012 и 2013 годы. Важно указывать не числа, а именно временные периоды (то есть не 5 марта 2013 года, а март 2013-го).
Для прогноза на 2014 год вам потребуются два ряда данных: даты и соответствующие им значения показателей. Выделяем оба ряда данных.
На вкладке «Данные» в группе «Прогноз» нажимаем на «Лист прогноза». В появившемся окне «Создание листа прогноза» выбираем формат представления прогноза - график или гистограмму. В поле «Завершение прогноза» выбираем дату окончания, а затем нажимаем кнопку «Создать». Оранжевая линия - это и есть прогноз.
Эта функциональность, пожалуй, первый шаг к тому, что можно назвать бизнес-анализом. Приятно, что эта функциональность реализована наиболее дружественным по отношению к пользователю способом: желаемый результат достигается буквально в несколько кликов. Ничего не нужно считать, не надо записывать никаких формул. Достаточно выделить нужный диапазон и выбрать, какой результат вы хотите получить.
Полезное дополнение. Мгновенно можно создавать различные типы диаграмм или спарклайны (микрографики прямо в ячейке).
В быстром анализе также есть несколько вариантов форматирования. Посмотреть, какие значения больше, а какие меньше, можно в самих ячейках гистограммы.
Также можно проставить в ячейках разноцветные значки: зелёные - наибольшие значения, красные - наименьшие.
Надеемся, что эти приёмы помогут ускорить работу с анализом данных в Microsoft Excel и быстрее покорить вершины этого сложного, но такого полезного с точки зрения работы с цифрами приложения.
Если вам нужно разработать сложные статистические или инженерные анализы, вы можете сэкономить этапы и время с помощью пакета анализа. Вы предоставляете данные и параметры для каждого анализа, и в этом средстве используются соответствующие статистические или инженерные функции для вычисления и отображения результатов в выходной таблице. Некоторые инструменты создают диаграммы в дополнение к выходным таблицам.
Функции анализа данных можно применять только на одном листе. Если анализ данных проводится в группе, состоящей из нескольких листов, то результаты будут выведены на первом листе, на остальных листах будут выведены пустые диапазоны, содержащие только форматы. Чтобы провести анализ данных на всех листах, повторите процедуру для каждого листа в отдельности.
Примечание: Чтобы включить функцию Visual Basic для приложений (VBA) для пакета анализа, вы можете загрузить надстройку " Пакет анализа - VBA " таким же образом, как и при загрузке пакета анализа. В диалоговом окне Доступные надстройки установите флажок Пакет анализа - VBA .
Чтобы загрузить пакет анализа в Excel для Mac, выполните указанные ниже действия.
В меню Сервис выберите пункт надстройки Excel .
В окне Доступные надстройки установите флажок Пакет анализа , а затем нажмите кнопку ОК .
Если надстройка Пакет анализа отсутствует в списке поля Доступные надстройки , нажмите кнопку Обзор , чтобы найти ее.
Если появляется сообщение о том, что пакет анализа не установлен на компьютере, нажмите кнопку Да , чтобы установить его.
Выйдите из приложения Excel и перезапустите его.
Теперь на вкладке Данные доступна команда Анализ данных .
Существуют несколько сторонних надстроек, которые предоставляют функции пакета анализа для Excel 2011.
Вариант 1. Скачайте статистическое программное обеспечение надстройки КСЛСТАТ для Mac и используйте его в Excel 2011. КСЛСТАТ содержит более 200 основных и расширенных статистических средств, включающих все функции пакета анализа.
Выберите версию КСЛСТАТ, соответствующую операционной системе Mac OS, и загрузите ее.
Откройте файл Excel, содержащий данные, и щелкните значок КСЛСТАТ, чтобы открыть панель инструментов КСЛСТАТ.
В течение 30 дней вы получите доступ ко всем функциям КСЛСТАТ. По истечении 30 дней вы сможете использовать бесплатную версию, включающую функции пакета анализа, или заказать одно из более полных решений КСЛСТАТ.
Вариант 2. Скачайте Статплус: Mac LE бесплатно из Аналистсофт, а затем используйте Статплус: Mac LE с Excel 2011.
Вы можете использовать Статплус: Mac LE для выполнения многих функций, которые ранее были доступны в пакетах анализа, таких как регрессия, гистограммы, анализ вариации (Двухфакторный дисперсионный обработки) и t-тесты.
Перейдите на веб-сайт аналистсофт и следуйте инструкциям на странице загрузки.
После загрузки и установки Статплус: Mac LE откройте книгу, содержащую данные, которые нужно проанализировать.
В этой статье речь пойдёт о том, как провести анализ данных с помощью сводной таблицы. Для тренировки Вы можете использовать таблицу, доступную по этой ссылке (простая таблица.xlsx).
Первое, что может понадобиться проанализировать с помощью сводной таблицы - это подвести промежуточные итоги. В нашем примере это может быть необходимость подсчитать объемы продаж по всем магазинам на каждую дату.
Для этого необходимо кликнуть на любом заголовке строки сводной таблицы (в нашем примере - это поля Дата, Точка продажи и Марка телефона ), и в открывшихся вкладках Работа со сводными таблицами перейти на вкладку Параметры . На ней необходимо нажать кнопку Параметры поля в группе Активное поле .
В открывшемся окне первой закладкой будет закладка .
Отсутствие такой закладки означает, что Вы не выбрали заголовок строки, то есть курсор установлен на ячейке с числовым значением.
На закладке Промежуточные итоги и фильтры Вы можете выбрать условие для выведения промежуточных итогов. Предлагаются следующие условия:
Установив автоматическое подведение промежуточных итогов, мы получим следующую таблицу, которая содержит промежуточные итоги для каждого условия:
Если настройка промежуточных итогов с помощью команды Параметры поля , не даёт видимых результатов, проверьте настройки отображения промежуточных итогов с помощью команды Промежуточные итоги группы Макет вкладки Конструктор .
Допустим, нам необходимо вывести промежуточные итоги только для дат, скрыв промежуточные итоги для точек продаж. Для этого щелкните на любое поле таблицы с названием магазина и вызовите контекстное меню. В нём нужно убрать галочку с условия Промежуточный итог: точка продажи . Как мы видим, промежуточные итоги остались только для дат:
Часто бывает необходимо отсортировать данные сводной таблицы, для лучшего их восприятия. Для этого достаточно выбрать поле, по которому нужно провести сортировку, перейти на вкладку Общие , в группе Редактирование нажать на кнопку Сортировка и фильтр и установить нужные Вам условия сортировки.
Очень полезной функцией для анализа информации в сводной таблице является возможность группировки данных. Например, нам нужно сгруппировать наши продажи по неделям месяца. Для этого нужно выделить даты, которые входят в первую неделю (15.05-21.05):
Обратите внимание, что для удобства выделения мы свернули данные по отдельным магазинам, воспользовавшись кнопкой + в левой части ячейки с названием магазина.
Далее нужно выполнить команду Группировка по выделенному группы Группировать вкладки Параметры . В таблице появится новый столбец, в котором поле Группа1 будет объединять выбранные нами поля.
Останется только переименовать название группы путём простого редактирования ячейки:
Для отмены группировки достаточно воспользоваться командой Разгруппировать из этой же группы, предварительно выбрав поле, которое подлежит разгруппировке. Обратите внимание, что нельзя разгруппировать поле, которое мы включили в условие построения сводной таблицы - например поле Точка продажи или Дата .
Рассмотрим ещё один способ вывода данных, который поможет нам проанализировать информацию из нашей таблицы. Например, нам нужно узнать объемы выручки не в денежном выражении, а в виде процента от общего объема выручки за весь период продаж.
Для этого нужно выделить любую ячейку в столбце Выручка нашей сводной таблицы. После этого нужно выполнить команду Параметры поля в группе Активное поле вкладки Параметры .
В открывшемся диалоговом окне необходимо перейти на вкладку Дополнительные вычисления и из выпадающего меню выбрать пункт Доля от суммы по столбцу . После нажатия кнопки Ок, наша таблица будет иметь следующий вид:
Если данные в таблице будут выводиться не в виде процентов, проверьте настройки числового формата ячеек (это можно сделать сразу же в диалоговом окне Параметры поля , нажав на кнопку Числовой формат , или вызвав соответствующее окно из контекстного меню).
При выполнении сложных аналитических задач по статистике (к примеру, корреляционного и дисперсионного анализа, расчетов по алгоритму Фурье, создания прогностической модели) пользователи часто интересуются, как добавить анализ данных в Excel. Обозначенный пакет функций предоставляет разносторонний аналитический инструментарий, полезный в ряде профессиональных сфер. Но он не относится к инструментам, включенным в Эксель по умолчанию и отображающимся на ленте. Выясним, как включить анализ данных в Excel 2007, 2010, 2013.
Рассмотрим анализ данных в Excel: как включить и чем будет отличаться процедура активации для других версий. В большинстве вариантов программы процедура выполняется одинаково. Поэтому последовательность действий, изложенная в разделе, подходит для большинства версий, в том числе для выпусков 2013 и 2016 годов.
Рассматриваемый пакет относится к категории надстроек, то есть сложных аналитических дополнений. Соответственно, для включения пакета переходим в меню надстроек. Эта процедура выполняется следующим образом:
Рассмотрим, как активировать аналитические функции, предоставляемые надстройкой пакета:
Аналитический пакет оперирует большим набором инструментов, оптимизирующих решение статистических задач. Некоторые из числа:
Чтобы применить ту или иную опцию, действуют по нижеприведенному алгоритму:
Функции, входящие в пакет, рассчитаны на использование чисел только с одного листа Эксель. Если нужные статистические значения помещены на нескольких листах, потребуется предварительно создать сводную таблицу, скопировав туда требуемые параметры.
Алгоритм, как включить анализ данных в Excel 2007, отличается от остальных тем, что в самом начале (для выхода на параметры Excel) вместо кнопки «Файл» пользователь нажимает четырехцветный символ Microsoft Office. В остальном же последовательность операций идентична приведенной для других версий.
Анализ данных в Excel предполагает сама конструкция табличного процессора. Очень многие средства программы подходят для реализации этой задачи.
Excel позиционирует себя как лучший универсальный программный продукт в мире по обработке аналитической информации. От маленького предприятия до крупных корпораций, руководители тратят значительную часть своего рабочего времени для анализа жизнедеятельности их бизнеса. Рассмотрим основные аналитические инструменты в Excel и примеры применения их в практике.
Одним из самых привлекательных анализов данных является «Что-если». Он находится: «Данные»-«Работа с данными»-«Что-если».
Средства анализа «Что-если»:
Практический пример использования «Что-если» для поиска оптимальных скидок по таблице данных .
Другие инструменты для анализа данных:
Анализировать данные в Excel можно с помощью встроенных функций (математических, финансовых, логических, статистических и т.д.).
Чтобы упростить просмотр, обработку и обобщение данных, в Excel применяются сводные таблицы.
Программа будет воспринимать введенную/вводимую информацию как таблицу, а не простой набор данных, если списки со значениями отформатировать соответствующим образом:
К указанному диапазону применится заданный по умолчанию стиль форматирования. Станет активным инструмент «Работа с таблицами» (вкладка «Конструктор»).
Составить отчет можно с помощью «Сводной таблицы».
Создание сводной таблицы – это уже способ анализа данных. Более того, пользователь выбирает нужную ему в конкретный момент информацию для отображения. Он может в дальнейшем применять другие инструменты.
Мощное средство анализа данных. Рассмотрим организацию информации с помощью инструмента «Что-если» - «Таблица данных».
Важные условия:
Процедура создания «Таблицы данных»:
Для анализа деятельности предприятия берутся данные из бухгалтерского баланса, отчета о прибылях и убытках. Каждый пользователь создает свою форму, в которой отражаются особенности фирмы, важная для принятия решений информация.
