Как сделать аналитическую таблицу в excel?

Анализ данных в Excel включает в себя разработку самой электронной таблицы. Для этой задачи подходят многие программные средства.

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

Инструменты анализа Excel

Один из самых убедительных анализов данных — «что, если». Находится: «Данные» — «Работа с данными» — «А если».

Инструменты анализа «что, если»:

1. «Подбор параметров». Он используется, когда пользователю известен результат формулы, но неизвестен ввод этого результата.
2. «Таблица данных». Он используется в ситуациях, когда вы хотите отобразить влияние значений переменных в формулах в таблице.
3. Менеджер сценариев. Используется для формирования, изменения и сохранения различных наборов входных данных и результатов расчетов по группе формул.
4. «Найти решение.» Это надстройка для Excel. Помогает найти лучшее решение конкретной проблемы.

Практический пример использования «Что, если» для поиска лучших скидок в таблице данных.

Другие инструменты для анализа данных:

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

Сводные таблицы в анализе данных

Чтобы упростить просмотр, обработку и обобщение данных, в Excel используются сводные таблицы.

Программа будет обрабатывать введенную/введенную информацию как таблицу, а не как простой набор данных, если списки со значениями имеют соответствующий формат:

1. Перейдите на вкладку «Вставка» и нажмите кнопку «Таблица».
2. Откроется диалоговое окно «Создать таблицу».
3. Укажите диапазон данных (если он уже введен) или предполагаемый диапазон (в каких ячейках будет размещена таблица). Установите флажок рядом с «Таблица с заголовками». Нажмите Ввод.

Стиль форматирования по умолчанию применяется к указанному диапазону. Инструмент «Работа с таблицами» (вкладка «Дизайн») становится активным»).

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

1. Активируйте любую из ячеек в диапазоне данных. Нажмите кнопку «Сводная таблица» («Вставка» — «Таблицы» — «Сводная таблица»).
2. В диалоговом окне прописываем диапазон и место, где разместить сводный отчет (новый лист).
3. Откроется мастер сводных таблиц. Левая часть листа — изображение отчета, правая часть — инструменты для создания сводного отчета.
4. Выберите необходимые поля из списка. Определите значения для имен строк и столбцов. Отчет будет «построен» в левой части листа.

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

Анализ «Что-если» в Excel: «Таблица данных»

Мощный инструмент анализа данных. Рассмотрим организацию информации с помощью инструмента «Что, если» — «Таблица данных».

Важные условия:

• данные должны быть в столбце или строке;
• формула относится к одной входной ячейке.

Порядок создания «Таблицы данных»:

1. Вводим входные значения в один столбец и формулу в соседний столбец на строку выше.
2. Выбираем диапазон значений, включая столбец с входными данными и формулой. Перейдите на вкладку «Данные». Откройте инструмент «Что, если». Нажмите на кнопку «Данные таблицы».
3. В открывшемся диалоговом окне есть два поля. Так как мы создаем таблицу с одной записью, мы вводим адрес только в поле «Заменить значения для строк в». Если входные значения расположены в строках (а не в столбцах), введем адрес в поле «Подставить значения для столбцов в» и нажмем ОК.

Анализ предприятия в Excel: примеры

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

• скачать систему бизнес-аналитики;
• скачать аналитическую таблицу финансов;
• таблица рентабельности бизнеса;
• о движении денежных средств;
• пример точечного метода в финансово-экономическом анализе.

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

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

Начало работы

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

Например, рассмотрим одни и те же финансовые расходы в разные месяцы.

 

 

Создание сводных таблиц

Для построения такой таблицы необходимо выполнить следующие шаги.

1. Для начала его нужно полностью выделить.

 

 

1. Затем перейдите на вкладку «Вставка». Нажмите на значок «Таблица». В появившемся меню выберите «Сводная таблица».

 

 

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

 

 

1. Затем вам будет предложено указать, где именно будет происходить сборка. Лучше выбрать пункт «На существующий лист», так как будет неудобно анализировать информацию, когда все разбросано по нескольким листам. Поэтому вам нужно указать диапазон. Для этого щелкните значок рядом с полем ввода.

 

 

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

 

 

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

 

 

1. Для завершения настроек нажмите кнопку «ОК».

 

 

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

 

 

1. На этом этапе нужно указать, какое поле будет:
   1. столбец;
   2. нить;
   3. значение для анализа.

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

 

 

1. Чтобы добавить какое-либо поле, нужно щелкнуть по нему левой кнопкой мыши и, не отпуская палец, перетащить в нужную область. Курсор изменит свой вид.

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

1. Чтобы увидеть полный результат, вы можете закрыть боковую панель настроек. Для этого просто нажмите на крестик.

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

1. Если вам не нравится таблица, вы можете попробовать построить ее немного по-другому. Для этого нужно изменить поля в областях построения.

1. Снова закройте мастер сборки.

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

Использование рекомендуемых сводных таблиц

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

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

1. Перейдите на вкладку «Вставка». Затем нажмите на иконку «Таблица». В появившемся меню выберите второй пункт.

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

1. При наведении курсора на каждый элемент будет доступен предварительный просмотр результата. Это делает работу с ним намного комфортнее.

1. Вы можете выбрать то, что вам больше нравится.

1. Чтобы вставить выбранный вариант, просто нажмите кнопку «ОК».

1. В результате вы получите следующий результат.

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

Анализ

Как только вы добавите (неважно как) сводную таблицу, вы увидите новую вкладку «Анализ» на панели инструментов. Он содержит множество различных инструментов и функций.

Рассмотрим каждый из них подробнее.

Сводная таблица

Нажав кнопку, отмеченную на скриншоте, вы можете сделать следующее:

• переименовать;

• откройте окно настроек.

В окне настроек вы увидите много интересного.

Активное поле

С помощью этого инструмента вы можете сделать следующее:

1. Сначала нужно выделить ячейку. Затем нажмите кнопку «Активное поле». В появившемся меню нажмите на пункт «Параметры поля».

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

1. Кроме того, вы можете настроить числовой формат. Для этого нажмите на соответствующую кнопку.

1. Откроется окно «Формат ячеек».

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

Группировать

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

Вставить срез

Редактор Microsoft Excel позволяет создавать интерактивные сводные таблицы. В этом случае ничего сложного делать не нужно.

1. Выберите столбец. Затем нажмите кнопку «Вставить фрагмент».
2. В появившемся окне в качестве примера выберите одно из предложенных полей (в дальнейшем их можно будет выбирать в неограниченном количестве). После выбора чего-либо сразу активируется кнопка «ОК». Нажмите здесь.

1. В результате появится маленькое окошко, которое можно переместить куда угодно. Он вернет все возможные уникальные значения, найденные в этом поле. Благодаря этому инструменту вы можете снять сумму только за определенные месяцы (в данном случае). По умолчанию информация отображается за все время.

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

1. Таким образом, можно будет выбрать любой период времени.

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

В данном случае мы смогли отсортировать отчет по месяцам, потому что у нас было соответствующее поле. Но есть более мощный инструмент для работы с датами.

Вставить временную шкалу

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

В качестве примера создадим небольшую таблицу с несколькими датами.

Затем вам нужно будет построить сводную таблицу.

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

1. Затем нас просят выбрать диапазон значений.

1. Для этого просто выделите всю таблицу.

1. Сразу после этого адрес будет автоматически заменен. Здесь все очень просто, потому что рассчитано на дураков. Нажмите кнопку OK, чтобы завершить сборку».

1. Редактор Excel предложит нам только один вариант, так как таблица очень простая (для примера больше и не нужно).

1. Попробуйте еще раз щелкнуть значок «Вставить временную шкалу» (на вкладке «Анализ»).

1. На этот раз ошибок не будет. Вам будет предложено выбрать поле для сортировки. Установите флажок и нажмите кнопку ОК».

1. Благодаря этому появится окно, в котором можно выбрать нужную дату с помощью ползунка.

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

Обновить

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

Источник данных

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

Для этого нажмите на значок «Источник данных». Затем выберите одноименный пункт меню.

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

Действия

С помощью этого инструмента вы можете:

• почистить стол;
• выделять;
• подвинь это.

Вычисления

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

это включает:

• вычисляемое поле;

• вычисляемый объект;

• порядок расчета (добавленные формулы отображаются в списке);

• показать формулы (без информации, так как нет добавленных формул).

Сервис

Здесь вы можете создать сводную диаграмму или изменить рекомендуемый тип таблицы.

Показать

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

Благодаря этому вы сможете:

• настроить отображение боковой панели со списком полей;

• включить или отключить кнопки плюс/минус;

• настроить отображение заголовков полей.

Конструктор

При работе со сводными таблицами помимо вкладки «Анализ» появится еще одна — «Конструктор». Здесь вы можете изменить внешний вид вашего объекта до неузнаваемости по сравнению со значением по умолчанию.

Можно настроить:

• промежуточные итоги:
  • не показывать;
  • показать все итоги внизу;
  • показать все итоги в заголовке.

• всего результатов:
  • отключить для строк и столбцов;
  • включить для строк и столбцов;
  • включить только для строк;
  • включить только для столбцов.

• макет отчета:
  • отображать в сжатом виде;
  • отображение в виде структуры;
  • отображать в табличной форме;
  • повторить все метки элементов;
  • не повторяйте метки элементов.

• пустые строки:
  • вставьте пустую строку после каждого элемента;
  • удалить пустую строку после каждого элемента.

• параметры стиля сводной таблицы (здесь вы можете включить/отключить каждый элемент):
  • заголовки строк;
  • заголовки столбцов;
  • альтернативные линии;
  • чередующиеся столбцы.

• настроить стиль элементов.

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

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

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

Сортировка значений

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

Для этого сделайте следующее.

1. Нажмите на треугольник рядом с нужным полем.
2. В результате вы увидите следующее меню. Здесь вы можете выбрать нужный вариант сортировки («от А до Я» или «от Я до А»).

Если стандартного варианта недостаточно, вы можете нажать на пункт «Дополнительные параметры сортировки» в том же меню».

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

Здесь все настроено на автомат. Если вы снимите этот флажок, вы можете указать нужный вам ключ.

Сводные таблицы в Excel 2003

Описанные выше шаги подходят для современных редакторов (2007, 2010, 2013 и 2016). В старой версии все выглядит иначе. Возможностей, конечно, гораздо меньше.

Чтобы создать сводную таблицу в Excel 2003, вам нужно сделать следующее.

1. Перейдите в раздел меню «Данные» и выберите соответствующий пункт.

1. В результате появится мастер для создания таких объектов.

1. После нажатия кнопки «Далее» откроется окно, в котором нужно указать диапазон ячеек. Затем снова нажмите «Далее».

1. Для завершения настройки нажмите «Готово».

1. В результате этого вы увидите следующее. Здесь нужно перетащить поля в соответствующие области.

1. Например, вы можете получить следующий вывод.

Становится очевидным, что такие отчеты гораздо лучше создавать в современных редакторах.

Заключение

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

Если этого учебника вам недостаточно, вы можете найти дополнительную информацию в интерактивной справке Microsoft.

Видеоинструкция

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

 

Если по работе или учебе вам приходится погружаться в океан цифр и искать в них подтверждения своим гипотезам, наверняка вам пригодятся эти методики работы в Microsoft Excel. Как их применять: показываем с помощью GIF.

Юлия Перминова

Тренер Учебного центра Softline с 2008 года.

1. Сводные таблицы

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

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

Как работать

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

1. Откройте файл с таблицей, данные которой вы хотите проанализировать.
2. Выберите диапазон данных для анализа.
3. Перейдите на вкладку «Вставить сводную таблицу таблицы» (в macOS на вкладке «Данные» группы «Анализ»).
4. Должно появиться диалоговое окно «Создать сводную таблицу».
5. Настройте отображение данных, которые у вас есть в таблице.

Перед нами таблица с неструктурированными данными. Мы можем организовать их и настроить отображение данных, которые у нас есть в таблице. Отправляем «Количество заказов» в «Значения», а «Продажи», «Дату продажи» — в «Строки». По разным продавцам за разные годы суммы рассчитывались сразу. При необходимости можно продлевать каждый год, квартал или месяц; мы получим более подробную информацию за конкретный период.

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

Можно детализировать, например, по странам. Передаем «Страны».

Вы можете увидеть результаты по поставщикам. Измените «Страна» на «Продавцы». Для продавцов результаты будут следующими.

2. 3D-карты

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

Полезное дополнение. Координаты никуда писать не надо; достаточно правильно указать географическое название в таблице.

Как работать

1. Откройте файл, содержащий таблицу, данные которой вы хотите просмотреть. Например, с информацией из разных городов и стран.
2. Подготовить данные для отображения на карте: «Старт» «Форматировать как таблицу».
3. Выберите диапазон данных для анализа.
4. На вкладке «Вставка» есть кнопка «3D-карта.

Точки на карте — наши города. Но нас просто не очень интересуют города; Интересно увидеть информацию, связанную с этими городами. Например, количества, которые можно отобразить через высоту столбца. При наведении курсора на столбец отображается сумма.

Также достаточно информативна круговая диаграмма по годам. Размер круга определяется суммой.

3. Лист прогнозов

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

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

Как работать

1. Откройте таблицу с данными за период и соответствующие показатели, например, один год.
2. Выделите две строки данных.
3. На вкладке Данные в группе нажмите кнопку Лист прогноза».
4. В окне «Создать лист прогноза» выберите график или столбчатую диаграмму для визуального представления прогноза.
5. Выберите дату окончания прогноза.

В следующем примере у нас есть данные за 2011, 2012 и 2013 годы. Важно указывать не цифры, а периоды времени (т.е не 5 марта 2013 года, а март 2013 года).

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

На вкладке Данные в группе Прогноз щелкните Лист прогноза. В появившемся окне «Создать лист прогноза» выберите формат представления прогноза: график или гистограмма. В поле «Конец прогноза» выберите дату окончания и нажмите кнопку «Создать». Оранжевая линия – это прогноз.

4. Быстрый анализ

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

Полезное дополнение. Вы можете мгновенно создавать различные типы диаграмм или спарклайнов (микрофотографии прямо в ячейке).

Как работать

1. Откройте таблицу с данными для анализа.
2. Выберите диапазон, который вы хотите проанализировать.
3. При выборе диапазона внизу всегда появляется кнопка «Быстрый анализ». Он сразу предлагает выполнить различные возможные действия с данными. Например, поиск результатов. Суммы можем узнать, они указаны ниже.

Quick Analysis также имеет несколько вариантов форматирования. В ячейках гистограммы видно, какие значения больше, а какие меньше.

Также можно разместить в ячейках разноцветные значки: зеленые — самые большие значения, красные — самые маленькие.

Надеемся, что эти хитрости помогут вам ускорить работу с анализом данных в Microsoft Excel и быстро покорить вершины этого сложного, но очень полезного приложения, когда речь идет о работе с числами.