Почему в сводной таблице вместо суммы количество

Как установить функцию Sum как функцию по умолчанию в сводной таблице?

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

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

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

Чтобы установить функцию Sum как функцию по умолчанию, вам необходимо выполнить следующие действия:

1. Выберите диапазон данных, на основе которого вы хотите создать сводную таблицу, а затем щелкните Главная > Найти и выбрать > Перейти к специальному, В Перейти к специальному диалоговое окно, выберите Пробелы из Выберите раздел, см. снимок экрана:

doc сводная сумма по умолчанию 2

2. Затем нажмите OK кнопку, чтобы закрыть диалоговое окно, и все пустые ячейки были выбраны сразу, теперь вы должны ввести 0 напрямую, а затем нажмите Ctrl + Enter вместе, и все пустые ячейки были заполнены 0, см. снимок экрана:

doc сводная сумма по умолчанию 3

3. Затем выберите диапазон данных и нажмите Вставить > PivotTable, см. снимок экрана:

doc сводная сумма по умолчанию 4

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

doc сводная сумма по умолчанию 5

5, Сейчас Сумма Функция была установлена как функция по умолчанию, как вам нужно, в созданной сводной таблице, как показано на следующем снимке экрана:

Изменение итоговых расчетов в сводных таблицах Excel 2010

При создании отчета сводной таблицы программа Excel по умолчанию суммирует данные, подсчитывая и складывая элементы. Вместо функций Сумма (Sum) или Количество (Count) можно использовать такие функции, как Минимум (Min), Максимум (Мах), Количество чисел (Count Numeric) и т.д. Всего в сводных таблицах итоги подсчитываются с помошью 11 функций. Однако самая распространенная причина изменения итоговых расчетов заключается в подсчете элементов данных в тех ситуациях, когда их нужно суммировать.

Если все ячейки в столбце содержат числовые данные, программа Excel просуммирует их. Если же хотя бы одна ячейка в столбце пуста или содержит текст, то программа подсчитает количество элементов данных. В сводной таблице, показанной на рис. 3.23, в столбце М находится множество числовых записей, а ячейка М2 является пустой. Этой одной ячейки достаточно, чтобы программа автоматически сосчитала количество ячеек, а не сумму их значений.

В Excel 2010 один из самых существенных факторов, влияющих на правильность проводимых расчетов, — это правильность автоматического определения программой типов данных полей. Например, при установке флажка поля Доход в диалоговом окне Список полей сводной таблицы (PivotTable Field List) это поле автоматически перемешается в область Названия строк (Row Labels), а само поле рассматривается программой как текстовое, а не числовое.

Рис. 3.23. Единственная пустая ячейка М2 вызывает большие проблемы при определении типа данных

Будьте предельно внимательны при перетаскивании полей в область Значения (Values). Если полученные суммы кажутся вам подозрительно маленькими, обязательно проверьте, какой тип функции применяется в итоговых вычислениях. Вполне возможно, что вместо суммы по полю вычисляется количество записей поля. При создании сводной таблицы, показанной на рис. 3.24, обратите внимание на то, что компания имеет доход всего 93155 долларов вместо реальных миллионных доходов. Причина тому — вычисление в поле ВЗ количества элементов в поле Доход, а не суммы их значений. Таким образом, число 93155 указывает количество записей в базе данных, а не обший доход компании.

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

Для изменения неправильной формулы выделите одну из ячеек в столбце Доход. Выберите контекстную вкладку ленты Параметры (Options), щелкните на кнопке раскрывающегося списка Итоги по (Summarize Values By) и выберите в списке (рис. 3.25) функцию Сумма (Sum) вместо функции Количество (Count).

Рис 3.25. В списке Итоги по вместо функции Количество выберите функцию Сумма

Как сделать сводную таблицу в Excel: пошаговая инструкция

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

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

Данные для сводной таблицы

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

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

Шапка сводной таблицы

Данную табличку нужно заполнить, т.е. просуммировать выручку по соответствующим товарам и регионам. Это нетрудно сделать с помощью функции СУММЕСЛИМН. Также добавим итоги. Получится сводный отчет о продажах в разрезе область-продукция.

Сведение данных с помощью формулы

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

— Можно ли отчет сделать не по выручке, а по прибыли?

— Можно ли товары показать по строкам, а регионы по столбцам?

— Можно ли такие таблицы делать для каждого менеджера в отдельности?

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

Рассмотрим, как создать сводную таблицу в Excel.

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

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

Читать:

Зачем нужно обновлять биос на материнской плате

Кнопки построения сводной таблицы на ленте

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

Макеты рекомендуемых сводных таблиц

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

Диалоговое окно создания сводной таблицы

Обычно ничего менять здесь не нужно. После нажатия Ок будет создан новый лист Excel с пустым макетом сводной таблицы.

Пустая сводная таблица

Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.

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

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

Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.

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

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

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

Область строк – названия строк, которые расположены в крайнем левом столбце. Это все уникальные значения выбранного поля (столбца). В области строк может быть несколько полей, тогда таблица получается многоуровневой. Здесь обычно размещают качественные переменные типа названий продуктов, месяцев, регионов и т.д.

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

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

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

Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».

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

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

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

На ее построение потребовалось буквально 5-10 секунд.

Работа со сводными таблицами в Excel

Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания директора легко воплощаются в реальность.

Заменим выручку на прибыль.

Товары и области меняются местами также перетягиванием мыши.

Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.

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

Источник данных сводной таблицы Excel

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

1. Лучший формат для данных – это Таблица Excel. Она хороша тем, что у каждого поля есть наименование и при добавлении новых строк они автоматически включаются в сводную таблицу.

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

3. Уберите пропуски и пустые ячейки иначе данная строка может выпасть из анализа.

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

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

Обновление данных в сводной таблице Excel

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

Обновление сводной таблицы

или
через команду во вкладке Данные – Обновить все.

Обновить все

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

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

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