3 хитрости для анализа данных с помощью сводных таблиц

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

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

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

И тогда мы просто даем ОК

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

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

Или в этой очень красивой статье: https://expert-bg.org/article-pivot-table-ms-excel/

Но я обещал тебе три трюка

1. Расчетное поле

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

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

Читать также:  Power Query - краткое руководство от Microsoft

Появится меню «Вставить вычисляемое поле»..

Здесь в поле «Имя» мы пишем имя создаваемого поля. Я назвал это «Среднее посещение музея».

Поле под ним называется Формула, что предполагает, что мы должны написать здесь формулу. Обратите внимание на поле «Поля». В сводной таблице есть столбцы. Выбираем их двойным щелчком по ним. А для написания формул мы используем стандартные математические операторы + (сложение), — (вычитание), / (деление) и * (умножение), а также круглые скобки..

Вот как выглядит наша формула:

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

Это была первая уловка — создание поля расчета в сводной таблице. С его помощью мы можем конвертировать суммы из BGN в EUR, рассчитывать% скидку от некоторой суммы, делить числа на 100 или 1000 и т. Д. БЕЗ создания дополнительных столбцов в нашей основной таблице. Таким образом мы не увеличиваем объем нашего файла. Поле расчета хранится только в кеше сводной таблицы. И это занимает гораздо меньше места, чем формула, занимающая несколько (десятков) тысяч строк..

2. Сортировка для облегчения анализа

Добавим в нашу сводную таблицу данные о количестве музеев..

Как видите, по умолчанию Excel сортирует данные по алфавиту. Но это не очень удобно для анализа, потому что нас интересуют тенденции в цифрах. Разберем их более удобным способом. Для этого щелкните правой кнопкой мыши где-нибудь в столбце с региональными городами и выберите «Сортировать дополнительные параметры сортировки».

Читать также:  4 места, где можно найти новую музыку для Spotify

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

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

Результат немного удивителен — на первом месте Видин. Посмотрите, как легко и элегантно мы установили этот факт. Буквально несколькими щелчками мыши.

Однако есть одна проблема. Эти данные являются общими за два года, за которые у нас есть данные. Итак, давайте добавим столбцы нашей сводной таблицы и поле «Год». Теперь у нас есть основа для сравнения с течением времени.

Что подводит нас к третьему трюку

3. Изменения в процентах

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

Теперь щелкните правой кнопкой мыши это второе поле и выберите «Параметры поля значений». Затем выберите Показать значения как

 

Щелкните стрелку справа от поля Показывать значения как (обычно там написано Нормальное). В раскрывающемся меню выберите% от

Читать также:  7 причин иметь слабый интернет на вашем смартфоне

Затем в поле «База» выбираем «Год» и «2011»..

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

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

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

Сразу возникают вопросы. Почему Видин так «выделяется» среди других? Как это возможно, что наша почти двухмиллионная столица находится только на шестом месте по среднему количеству посещений музеев? Почему количество музеев в Хасково на 40% больше, а средняя посещаемость снизилась на 18% по сравнению с 2011 годом? Что происходит в Кырджали — посещаемость там всего треть от того, что 5 лет назад.

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

Понравилась статья? Поделиться с друзьями:
Что нужно знать пользователю?