Таблицы сводных данных в Excel - эффективный анализ данных

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

Основные результаты

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

Пошаговое руководство

Шаг 1: Создание сводной таблицы

Начните с создания сводной таблицы. Для этого откройте Excel и выберите данные. Это можно сделать быстро, используя комбинацию клавиш Ctrl + A, а затем Ctrl + T, чтобы выделить и форматировать все. Затем создайте сводную таблицу с помощью опции «Сводная таблица» и поместите ее в новый рабочий лист, который можно назвать "Анализ сводной таблицы".

Шаг 2: Обзор продаж по городам и отделам

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

Шаг 3: Расчет выручки

Теперь можно приступить к расчету выручки. Это происходит путем умножения числа продаж на цены за единицу. Для этого добавьте новый столбец с помощью правой кнопки мыши и "Вставить ячейки". Назовите новый столбец "Выручка" и рассчитайте выручку с формулой "Продажа * Цена за единицу". Перенесите эту формулу для всех строк вниз. Этот формат обеспечивает возможность дальнейшего обновления таблицы.

Шаг 4: Определение прибыли

Для расчета прибыли можно создать еще один столбец. В качестве альтернативы можно также использовать опции сводных таблиц, создав новую сводную таблицу и добавив соответствующие поля для выручки и затрат. Прибыль рассчитывается как "Выручка - Общие затраты".

Шаг 5: Расчет чистой прибыли

Для расчета чистой прибыли вернитесь к своему анализу сводной таблицы. Нажмите на "Анализ", затем на "Поле, элемент и вычисленное поле". Здесь добавьте чистую прибыль, взяв прибыль умноженную на 0,7, что представит 70% вашей общей прибыли в виде чистой прибыли.

Шаг 6: Детальный анализ с помощью экспорта

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

Шаг 7: Экспорт данных по выручке

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

Шаг 8: Сортировка прибыли

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

Шаг 9: Группировка городов

Чтобы создать более удобный обзор, вы можете группировать города. Например, вы можете включить Мюнхен и Штутгарт в группу "Юг", а Берлин, Кёльн и Гамбург в группу "Север". Это делается путем копирования и настройки сводной таблицы, а также использования функций группировки.

Шаг 10: Процентное отображение чистой прибыли

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

Шаг 11: Процентное отображение выручки

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

Обзор

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