Как в сводной таблице добавить вычисляемое поле: Поля промежуточных и общих итогов в сводной таблице

Поля промежуточных и общих итогов в сводной таблице

Вычисление промежуточных итогов для строк и столбцов

1. Выберите элемент строки или поле в столбце сводной таблицы. Убедитесь, что это поле, а не значение.

2. На вкладке «Анализ» в группе «Активное поле» нажмите кнопку «Параметры поля».

   Отобразит диалоговое окно «Параметры поля».

3. В диалоговом окне Параметры поля в разделе Итоги выполните одно из указанных ниже действий.

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

   • Для удаления промежуточных итогов выберите пункт Нет.

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

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

     Функции, доступные для вычисления промежуточных итогов

     Функция	Описание
     Сумма	Сумма значений. Функция по умолчанию для числовых данных.
     Количество	Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для данных, отличных от числовых.
     Среднее	Среднее арифметическое.
     Максимум	Наибольшее значение.
     Минимум	Наименьшее значение.
     Произведение	Произведение значений.
     Количество чисел	Количество числовых значений. Действует аналогично функции СЧЁТ.
     Стандартное отклонение	Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
     Несмещенное отклонение	Стандартное отклонение генеральной совокупности, которая содержит все сводимые данные.
     Дисперсия	Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
     Несмещенная дисперсия	Дисперсия генеральной совокупности, которая содержит все сводимые данные.

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

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

Советы: 

• Чтобы быстро отобразить или скрыть текущие итоги, щелкните элемент поля правой кнопкой мыши, а затем установите или снимите флажок Промежуточный итог «<Имя метки>».

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

  1. На вкладке Конструктор в группе Макет нажмите кнопку Промежуточные итоги.

  2. Выполните одно из указанных ниже действий.

     • Выберите пункт Не показывать промежуточные суммы.

     • Выберите Показывать все промежуточные итоги в нижней части группы.

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

Отображение и скрытие общих итогов по всему отчету

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

Отображение или скрытие общих итогов

1. Щелкните в любом месте сводной таблицы.

2. На вкладке Конструктор в группе Макет нажмите кнопку Общие итоги, а затем выберите нужный пункт.

Изменение состояния общих итогов по умолчанию (отображение или скрытие)

1. Щелкните сводную таблицу.

2. На вкладке «Анализ» в группе «Таблица» нажмите кнопку «Параметры».

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

   • Чтобы отобразить общие итоги, выберите «Показать общие итоги для столбцов» или «Показать общие итоги для строк» или «Показать общие итоги для строк».

   • Чтобы скрыть общие итоги, откроем либо «Показать общие итоги для столбцов», либо «Показать общие итоги для строк» или «Показать общие итоги для строк».

Вычисление промежуточных и общих итогов с отфильтрованными элементами и без них

1. Щелкните в любом месте сводной таблицы.

2. На вкладке «Анализ» в группе «Таблица» нажмите кнопку «Параметры».

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

   • Если вы используете источник данных OLAP, выполните одно из указанных ниже действий.

     • Установите или снимите флажок Промежуточные суммы по отобранным фильтром элементам страницы

       , чтобы включить отфильтрованные элементы в итоги или убрать из них.

       Примечание: Источник данных OLAP должен поддерживать синтаксис подзапросов выборки многомерного выражения.

     • Установите или снимите флажок Помечать итоги *, чтобы отобразить или скрыть звездочку рядом с итогами. Звездочка означает, что при вычислении итогов в Excel использовались не только отображаемые значения.

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

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

Посмотреть видео

Вычисление промежуточных итогов для строк и столбцов

1. Выберите элемент строки или поле в столбце сводной таблицы. Убедитесь, что это поле, а не значение.

2. На вкладке Параметры в группе Активное поле нажмите кнопку Параметры поля.

   Отобразит диалоговое окно «Параметры поля».

3. В диалоговом окне Параметры поля в разделе Итоги выполните одно из указанных ниже действий.

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

   • Для удаления промежуточных итогов выберите пункт Нет.

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

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

     Функции, доступные для вычисления промежуточных итогов

     Функция	Описание
     Сумма	Сумма значений. Функция по умолчанию для числовых данных.
     Количество	Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для данных, отличных от числовых.
     Среднее	Среднее арифметическое.
     Максимум	Наибольшее значение.
     Минимум	Наименьшее значение.
     Произведение	Произведение значений.
     Количество чисел	Количество числовых значений. Действует аналогично функции СЧЁТ.
     Стандартное отклонение	Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
     Несмещенное отклонение	Стандартное отклонение генеральной совокупности, которая содержит все сводимые данные.
     Дисперсия	Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
     Несмещенная дисперсия	Дисперсия генеральной совокупности, которая содержит все сводимые данные.

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

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

Советы: 

• Чтобы быстро отобразить или скрыть текущие итоги, щелкните элемент поля правой кнопкой мыши, а затем установите или снимите флажок Промежуточный итог «<Имя метки>».

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

  1. На вкладке Конструктор в группе Макет нажмите кнопку Промежуточные итоги.

  2. Выполните одно из указанных ниже действий.

     • Выберите пункт Не показывать промежуточные суммы.

     • Выберите Показывать все промежуточные итоги в нижней части группы.

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

Отображение и скрытие общих итогов по всему отчету

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

Отображение или скрытие общих итогов

1. Щелкните в любом месте сводной таблицы.

2. На вкладке Конструктор в группе Макет нажмите кнопку Общие итоги, а затем выберите нужный пункт.

Изменение состояния общих итогов по умолчанию (отображение или скрытие)

1. Щелкните сводную таблицу.

2. На вкладке Параметры в группе Сводная таблица нажмите кнопку Параметры.

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

   • Чтобы отобразить общие итоги, выберите «Показать общие итоги для столбцов» или «Показать общие итоги для строк» или «Показать общие итоги для строк».

   • Чтобы скрыть общие итоги, откроем либо «Показать общие итоги для столбцов», либо «Показать общие итоги для строк» или «Показать общие итоги для строк».

Вычисление промежуточных и общих итогов с отфильтрованными элементами и без них

1. Щелкните в любом месте сводной таблицы.

2. На вкладке Параметры в группе Сводная таблица нажмите кнопку Параметры.

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

   • Если вы используете источник данных OLAP, выполните одно из указанных ниже действий.

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

       Примечание: Источник данных OLAP должен поддерживать синтаксис подзапросов выборки многомерного выражения.

     • Установите или снимите флажок Помечать итоги *, чтобы отобразить или скрыть звездочку рядом с итогами. Звездочка означает, что при вычислении итогов в Excel использовались не только отображаемые значения.

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

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

В Excel в Интернете можно просмотреть итоги и итоги, если в книге есть сvotttables, которые их показывают. Однако вы не сможете использовать поля для подстановки итогов или итогов. Для работы с суммарными и итогами необходимо использовать настольную версию. См. поля «Итоги» и «Итоги» в pivotTable.

Сводная таблица с текстом в значениях

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

Давайте попробуем обойти это ограничение и придумать «пару костылей» в подобной ситуации.

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

Для удобства, давайте заранее сделаем таблицу с исходными данными «умной» с помощью команды Главная — Форматировать как таблицу (Home — Format as Table) и дадим ей имя Поставки на вкладке Конструктор (Design). В дальнейшем, это упростит жизнь, т.к. можно будет использовать имя таблицы и ее столбцов прямо в формулах.

Способ 1. Самый простой — используем Power Query

Power Query — это супермощный инструмент для загрузки и преобразований данных в Excel. Эта надстройка по умолчанию встроена в Excel начиная с 2016-й версии. Если у вас Excel 2010 или 2013, то ее можно отдельно скачать и установить (совершенно бесплатно).

Весь процесс, для наглядности, я пошагово разобрал в следующем видео:

Если нет возможности использовать Power Query, то можно пойти другими путями — через сводную таблицу или формулы. 

Способ 2. Вспомогательная сводная

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

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

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

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

Дополнительно можно отключить общие и промежуточные итоги на вкладке Конструктор — Общие итоги и Промежуточные итоги (Design — Grand Totals, Subtotals) и там же переключить сводную в более удобный табличный макет кнопкой Макет отчета (Report Layout).

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

Теперь скопируем сводную (на этот же лист или на другой) и вставим как значения, а затем введем в область значений свою формулу, которая будет извлекать код контейнера по номеру строки, найденному в сводной:

Функция ЕСЛИ (IF), в данном случае, проверяет, чтобы очередная ячейка в сводной была не пустой. Если пустая, то выводим пустую текстовую строку «», т.е. оставляем ячейку незаполненной. Если не пустая, то извлекаем из столбца Контейнер исходной таблицы Поставки содержимое ячейки по номеру строки с помощью функции ИНДЕКС (INDEX).

Пожалуй, единственным не очень очевидным моментом тут является задвоенное слово Контейнер в формуле. Такая странная форма записи:

Поставки[[Контейнер]:[Контейнер]]

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

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

Способ 3. Формулы

Этот способ не требует создания промежуточной сводной таблицы и ручного обновления, а использует «тяжелое оружие Excel» — функцию СУММЕСЛИМН (SUMIFS). Вместо поиска номеров строк в сводной их можно вычислить с помощью вот такой формулы:

При некоторой внешней громоздкости, на самом деле, это стандартный вариант использования функции выборочного суммирования СУММЕСЛИМН, которая суммирует номера строк для заданного города и месяца. Опять же, поскольку у нас не бывает нескольких контейнеров в одном и том же городе в один и тот же месяц, то наша функция выдаст, фактически, не сумму, а сам номер строки. А затем уже знакомой по предыдущему способу функцией ИНДЕКС можно извлечь и коды контейнеров:

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

Если внешний вид сводной вам не очень подходит для отчета, то можно вытаскивать из нее номера строк в финальную таблицу не напрямую, как мы делали, а с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GET.PIVOT.DATA). Как это сделать можно посмотреть здесь.

Ссылки по теме

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

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

Файлы для скачивания:

Файл	Описание	Размер файла:	Скачивания
Пример		109 Кб	3078

Допустим, мы имеем такую сводную таблицу:

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

Как включить видео?

Чтобы увидеть видео включите в браузере GIF-Анимацию.

Затем наводим курсор на это поле в окне «Значения» и нажимаем на него левой клавишей мышки, в выпавшем меню выбираем пункт «Параметры полей значений…»:

Как включить видео?

Чтобы увидеть видео включите в браузере GIF-Анимацию.

В открывшемся диалоговом окне «Параметры поля значений», меняем название поля, например, на «Доля»:

Переходим во вкладку «Дополнительные вычисления», в поле со списком (там, где написано «Без вычислений») выбираем пункт «% от суммы по столбцу» и нажимаем кнопку «ОК»:

После чего ваша Сводная таблица должна будет приобрести следующий вид:

Поэкспериментируйте с другими вариантами вычислений.

Еще один пример вычислений, имеем такую Сводную таблицу:

В одном столбце у нас «Сумма продаж», в другой «Прибыль», нам необходимо получить рентабельность каждой категории товаров. Для этого устанавливаем курсор на сводную таблицу, в главном меню в разделе «Работа со сводными таблицами», во вкладке «Анализ» (в 2007 и 2010 «Параметры»), в разделе «Вычисления», нажимаем кнопку «Поля, элементы и наборы», в выпавшем списке выбираем пункт «Вычисляемое поле…» (в 2007 «Сервис» -> «Формулы»):

В открывшемся диалоговом окне меняем название поля, например, на «Рентабельность»:

В окне «Поля:» встаем на поле «Прибыль» и нажимаем кнопку «Добавить поле» так чтобы оно появилось в поле «Формула:», затем нажимаем на клавиатуре «/» — Знак деления, встаем на поле «Сумма» и снова нажимаем кнопку «Добавить поле». В итоге всех манипуляций в поле «Формула:» у вас должно получиться «=Прибыль/Сумма»:

Как включить видео?

Чтобы увидеть видео включите в браузере GIF-Анимацию.

Если все получилось, смело, нажимаем кнопку «ОК»:

В итоге ваша Сводная таблица должна будет принять следующий вид:

Присвоим столбцу «Рентабельность» формат «%», уберем из заголовка лишние слова и получим такую красоту:

В чем, собственно говоря, прелесть таких вычислений? В том, что если вы теперь добавите поле «SKU» в «СТРОКИ», то вы получите «Рентабельность» по каждому SKU, не вбивая никаких дополнительных формул:

Как включить видео?

Чтобы увидеть видео включите в браузере GIF-Анимацию.

Добавить комментарий

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

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

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

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

Что такое сводная таблица?

Это инструмент для изучения и обобщения больших объемов данных, анализа связанных итогов и представления отчетов. Они помогут вам:

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

Например, у вас множество записей в электронной таблице с цифрами продаж шоколада:

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

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

Вот посмотрите сами.

Этот скриншот демонстрирует лишь несколько из множества возможных вариантов анализа продаж. И далее мы рассмотрим примеры построения сводных таблиц в Excel 2016, 2013, 2010 и 2007.

Как создать сводную таблицу.

Многие думают, что создание отчетов при помощи сводных таблиц для «чайников» является сложным и трудоемким процессом. Но это не так! Microsoft много лет совершенствовала эту технологию, и в современных версиях Эксель они очень удобны и невероятно быстры. 

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

1. Организуйте свои исходные данные

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

Использование «умной» таблицы в качестве исходных данных дает вам очень хорошее преимущество — ваш диапазон данных становится «динамическим». Это означает, что он будет автоматически расширяться или уменьшаться при добавлении или удалении записей. Поэтому вам не придется беспокоиться о том, что в свод не попала самая свежая информация.

Полезные советы:

• Добавьте уникальные, значимые заголовки в столбцы, они позже превратятся в имена полей.
• Убедитесь, что исходная таблица не содержит пустых строк или столбцов и промежуточных итогов.
• Чтобы упростить работу, вы можете присвоить исходной таблице уникальное имя, введя его в поле «Имя» в верхнем правом углу.

2. Создаем и размещаем макет

Выберите любую ячейку в исходных данных, а затем перейдите на вкладку Вставка > Сводная таблица .

Откроется окно «Создание ….. ». Убедитесь, что в поле  Диапазон указан правильный источник данных. Затем выберите местоположение для свода:

• Выбор нового рабочего листа поместит его на новый лист, начиная с ячейки A1.
• Выбор существующего листа разместит в указанном вами месте на существующем листе. В поле «Диапазон» выберите первую ячейку (то есть, верхнюю левую), в которую вы хотите поместить свою таблицу.

Нажатие ОК создает пустой макет без цифр в целевом местоположении, который будет выглядеть примерно так:

Полезные советы:

• В большинстве случаев имеет смысл размещать на отдельном рабочем листе. Это особенно рекомендуется для начинающих.
• Ежели вы берете информацию из другой таблицы или рабочей книги, включите их имена, используя следующий синтаксис: [workbook_name]sheet_name!Range. Например, [Книга1.xlsx] Лист1!$A$1:$E$50. Конечно, вы можете не писать это все руками, а просто выбрать диапазон ячеек в другой книге с помощью мыши.
• Возможно, было бы полезно построить таблицу и диаграмму одновременно. Для этого в Excel 2016 и 2013 перейдите на вкладку «Вставка», щелкните стрелку под кнопкой «Сводная диаграмма», а затем нажмите «Диаграмма и таблица». В версиях 2010 и 2007 щелкните стрелку под сводной таблицей, а затем — Сводная диаграмма.

3. Организация макета.

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

• Раздел «Поле» содержит названия показателей, которые вы можете добавить. Они соответствуют именам столбцов исходных данных.
• Раздел «Макет» содержит область «Фильтры», «Столбцы», «Строки» и «Значения». Здесь вы можете расположить в нужном порядке поля.

Изменения, которые вы вносите в этих разделах, немедленно применяются в вашей таблице.

3. Как добавить поле

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

По умолчанию Microsoft Excel добавляет поля в раздел «Макет» следующим образом:

• Нечисловые добавляются в область Строки;
• Числовые добавляются в область значений;
• Дата и время добавляются в область Столбцы.

4. Как удалить поле из сводной таблицы?

Чтобы удалить любое поле, вы можете выполнить следующее:

• Снимите флажок напротив него, который вы ранее установили.
• Щелкните правой кнопкой мыши поле и выберите «Удалить……».

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

5. Как упорядочить поля?

Вы можете изменить расположение показателей тремя способами:

1. Перетащите поле между 4 областями раздела с помощью мыши. В качестве альтернативы щелкните и удерживайте его имя в разделе «Поле», а затем перетащите в нужную область в разделе «Макет». Это приведет к удалению из текущей области и его размещению в новом месте.

1. Щелкните правой кнопкой мыши имя в разделе «Поле» и выберите область, в которую вы хотите добавить его:

1. Нажмите на поле в разделе «Макет», чтобы выбрать его. Это сразу отобразит доступные параметры:

Все внесенные вами изменения применяются немедленно.

Ну а ежели спохватились, что сделали что-то не так, не забывайте, что есть «волшебная» комбинация клавиш CTRL+Z, которая отменяет сделанные вами изменения (если вы не сохранили их, нажав соответствующую клавишу).

6. Выберите функцию для значений (необязательно)

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

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

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

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

В Excel 2010 и ниже опция «Суммировать значения по» также доступна на ленте — на вкладке «Параметры» в группе «Расчеты».

7. Используем различные вычисления в полях значения (необязательно)

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

Это называется «Дополнительные вычисления». Доступ к ним можно получить, открыв вкладку «Параметры …», как это описано чуть выше.

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

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

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

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

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

Вы также можете изменить размер панели по горизонтали, перетаскивая разделитель, который отделяет панель от листа.

Закрытие и открытие панели редактирования.

Закрыть список полей в сводной таблице так же просто, как нажать кнопку «Закрыть» (X) в верхнем правом углу панели. А вот как заставить его появиться снова – уже не так очевидно 🙂

Чтобы снова отобразить его, щелкните правой кнопкой мыши в любом месте таблицы и выберите «Показать …» в контекстном меню.

Также можно нажать кнопку «Список полей» на ленте, которая находится на вкладке меню «Анализ».

Воспользуйтесь рекомендациями программы.

Как вы только что видели, создание сводных таблиц — довольно простое дело, даже для «чайников». Однако Microsoft делает еще один шаг вперед и предлагает автоматически сгенерировать отчет, наиболее подходящий для ваших исходных данных. Все, что вам нужно, это 4 щелчка мыши:

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

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

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

Давайте улучшим результат.

Теперь, когда вы знакомы с основами, вы можете перейти к вкладкам «Анализ» и «Конструктор» инструментов в Excel 2016 и 2013 ( вкладки « Параметры» и « Конструктор» в 2010 и 2007). Они появляются, как только вы щелкаете в любом месте таблицы.

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

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

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

Чтобы настроить макет определенного поля, щелкните на нем, затем нажмите кнопку «Параметры» на вкладке «Анализ» в Excel 2016 и 2013 (вкладка « Параметры» в 2010 и 2007). Также вы можете щелкнуть правой кнопкой мыши поле и выбрать «Параметры … » в контекстном меню.

На снимке экрана ниже показан новый дизайн и макет.

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

Думаю, стало даже лучше. 😊

Как избавиться от заголовков «Метки строк» ​​и «Метки столбцов».

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

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

И вот что мы получим в результате.

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

Другое решение — перейти на вкладку «Анализ», нажать кнопку «Заголовки полей», выключить их. Однако это удалит не только все заголовки, а также выпадающие фильтры и возможность сортировки. А для анализа данных отсутствие фильтров – это чаще всего нехорошо.

Как обновить сводную таблицу.

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

Как обновить вручную.

1. Нажмите в любом месте на свод.
2. На вкладке «Анализ» нажмите кнопку «Обновить» или же нажмите клавиши ALT + F5.

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

Чтобы обновить все сводные таблицы в файле, нажмите стрелку кнопки «Обновить», а затем  —  «Обновить все».

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

После запуска обновления вы можете просмотреть статус или отменить его, если вы передумали. Просто нажмите на стрелку кнопки «Обновить», а затем — «Состояние обновления» или «Отменить обновление».

Автоматическое обновление сводной таблицы при открытии файла.

1. Откройте вкладку параметров, как это мы только что делали.
2. В диалоговом окне «Параметры … » перейдите на вкладку «Данные» и установите флажок «Обновить при открытии файла».

Как переместить на новое место?

Может быть вы захотите переместить своё творение в новую рабочую книгу? Перейдите на вкладку «Анализ», нажмите кнопку «Действия» и затем — «Переместить ….. ». Выберите новый пункт назначения и нажмите ОК.

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

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

• Если таблица находится на отдельном листе, просто удалите этот лист.
• Ежели она расположена вместе с некоторыми другими данными на листе, выделите всю её с помощью мыши и нажмите клавишу Delete.
• Щелкните в любом месте в сводной таблице, которую хотите удалить, перейдите на вкладку «Анализ» (см. скриншот выше) => группа «Действия», нажмите небольшую стрелку под кнопкой «Выделить», выберите «Вся сводная таблица», а затем нажмите Удалить.

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

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

Возможно, вам также будет полезно:

8 способов сравнить две таблицы в Excel и найти разницу — В этом руководстве вы познакомитесь с различными методами сравнения таблиц Excel и определения различий между ними. Узнайте, как просматривать две таблицы рядом, как использовать формулы для создания отчета о различиях, выделить… Как выделить цветом повторяющиеся значения в Excel? — В этом руководстве вы узнаете, как отображать дубликаты в Excel. Мы рассмотрим различные методы затенения дублирующих ячеек, целых строк или последовательных повторений с использованием условного форматирования.  Ранее мы исследовали различные… Группировка в сводной таблице Excel – полезные рекомендации. — Возможность группировки данных является одной из самых мощных и полезных функций в сводной таблице. Это позволит обнаружить ранее скрытые взаимосвязи, сделать интересные выводы. При работе со сводными таблицами в Microsoft Excel вы можете…

0 0 голос

Рейтинг статьи

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

Excel, рассчитанное с использованием счетчика

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

Основы вычисляемого поля

Добавьте свои собственные формулы в сводную таблицу, создав вычисляемые поля.Эти поля могут содержать простые формулы, например «= Итого * 3%». или более сложные формулы, например, показанная ниже: «= ЕСЛИ (Единицы> 100, Итого * 3%, 0).

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

Видео: использование счетчика в вычисляемом поле

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

Использование счетчика в вычисляемом поле

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

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

Этот пример показан на образце файла CalcFieldCount лист.

Подсчитайте поле даты

Во-первых, чтобы увидеть проблему с использованием поля, отображаемого как COUNT, мы добавим поле «Дата» и будем использовать его для отображения количества заказов.

1. Создайте сводную таблицу из данных заказов, с Rep и Product в области «Строка» и «Единицы и итоги» в области «Значения»
2. Добавьте поле даты в область значений, где оно должно появиться. как количество даты.

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

Далее мы создадим вычисляемое поле и проверим, есть ли поле даты больше 2.

1. Выберите ячейку в сводной таблице и на ленте Excel в разделе на вкладке «Инструменты сводной таблицы» перейдите на вкладку «Анализ»
.
2. В группе «Вычисления» щелкните «Поля, элементы и наборы» и затем щелкните Расчетное поле.
3. Введите CountA в качестве имени
4. В поле Формула введите = Дата> 2
   ПРИМЕЧАНИЕ: пробелы можно опустить, если вы предпочитаете
5. Нажмите «Добавить», чтобы сохранить вычисленное поле, и нажмите «Закрыть».

6. Поле CountA отображается в области значений сводной таблицы, и в списке полей в списке полей сводной таблицы.
7. Поле отформатировано как дата, поэтому измените его на общий формат (щелкните правой кнопкой мыши одно из значений, щелкните Параметры поля значений, щелкните Числовой формат)

Вы заметите, что во всех строках отображается 1, что означает результат формулы ИСТИНА, даже если число не больше 2.

Это связано с тем, что Excel вместо этого использует СУММУ поля даты. из COUNT. Серийный номер для даты намного больше, чем 2 — например 27 декабря 2014 года равно 42000. Итак, СУММА четного одна дата будет больше 2, если только это не 1 января 1900 года.

Создание поля заказов

Чтобы получить правильное количество заказов и использовать его в вычисляемом поле, мы добавим новое поле к исходным данным на листе «Заказы».(Этот уже добавлен в образец файла)

1. На листе «Заказы» добавьте новый заголовок в первый пустой столбец — Заказы
2. В ячейке под заголовком введите формулу: = 1

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

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

Расчетное поле с полем счетчика заказов

Чтобы получить правильное количество заказов и использовать его в вычисляемом поле, мы добавим новое поле к исходным данным на листе «Заказы». (Этот уже добавлен в образец файла)

Далее мы создадим вычисляемое поле и проверим, больше 2.

ПРИМЕЧАНИЕ. Поле «Заказы» необязательно добавлять в сводную таблицу. перед созданием относящегося к нему вычисляемого поля.

1. Выберите ячейку в сводной таблице и на ленте Excel в разделе на вкладке «Инструменты сводной таблицы» перейдите на вкладку «Анализ»
.
2. В группе «Вычисления» щелкните «Поля, элементы и наборы» и затем щелкните Расчетное поле.
3. Введите CountB в качестве имени
4. В поле Формула введите = Заказы> 2
   ПРИМЕЧАНИЕ: пробелы можно опустить, если вы предпочитаете
5. Нажмите «Добавить», чтобы сохранить вычисленное поле, и нажмите «Закрыть».

6. Поле CountB отображается в области значений сводной таблицы, и в списке полей в списке полей сводной таблицы.

Вы заметите, что только некоторые строки показывают 1, что означает формулу результат ИСТИНА. Ноль появляется, если количество не больше 2, означает, что результат формулы ЛОЖЬ.

Удалить неправильные поля

Чтобы заполнить сводную таблицу, вы можете выполнить следующие действия:

1. Удалите поле Count of Date и вычисляемое поле CountA.
2. Добавьте поле «Заказы» как «Сумма заказов».
3. Сумма подсчета Заголовок столбца можно изменить на что-то большее. информативные, например «> 2 ».
4. Заголовок столбца «Сумма заказов» можно изменить на «Заказы». «(с пробелом в конце названия)

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

Количество уникальных предметов

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

Однако вы можете использовать один из следующих обходных путей:

1. Добавьте исходные данные в модель данных в Excel 2013 и более поздних версиях.
2. Используйте PowerPivot для создания сводной таблицы и используйте ее функции создать уникальный счетчик. Видеть подробности ниже.
3. Добавьте столбец в базу данных, затем добавьте это поле в сводную таблицу. Следуйте инструкциям ниже

Подсчет уникальных элементов с помощью PowerPivot

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

Загрузите образец файла для этого видео: StoreSales2012_2013.zip

Добавить столбец для вычисления уникального числа

Например, чтобы подсчитать количество уникальных случаев заказа клиента / позиции:

1. добавить столбец в базу данных с заголовком «CustItem»
2. В первой строке данных введите формулу, относящуюся к клиенту. и столбцы товаров.Например:

= ЕСЛИ (СУММПРОИЗВ (($ A $ 2: $ A2 = A2) * ($ C $ 2: $ C2 = C2))> 1,0,1)

3. Скопируйте формулу во все строки в базе данных.
4. Затем добавьте поле в область данных сводной таблицы Excel.

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

Загрузить образец файла

1. Загрузите файл-образец с примерами из этих руководств.Файл заархивирован и имеет формат xlsx. Файл не содержит макросы: Расчетные Пример файла с примерами полей

Инструменты для поворотного стола

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

• скопируйте форматирование из одной сводной таблицы и примените его к другой сводной таблице.
• изменить все значения с Count на Sum
• убрать слово «Сумма» из всех заголовков.

и многое другое!

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

Как вставить вычисляемое поле в сводную таблицу

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

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

• Прежде всего, вам понадобится простая сводная таблица для добавления вычисляемого поля.

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

• После нажатия на вычисляемое поле вы увидите всплывающее меню, как показано ниже. Это всплывающее меню имеет два варианта ввода (имя и формула) и вариант выбора.

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

В этом примере мы рассчитаем среднюю цену продажи. И формула будет = количество / количество.

• В поле «Поля» выберите «Сумма» и нажмите «Вставить», затем введите оператор деления «/» и введите количество после этого.
• Нажмите ОК.
• Теперь в вашей сводной таблице появится новое поле.

• Новое вычисляемое поле создано без какого-либо числового формата.

Как вставить вычисляемые элементы в сводную таблицу

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

Они просто создаются с помощью формулы.

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

• Просто щелкните любой из элементов в своей сводной таблице. На ленте вы увидите опцию сводной таблицы с двумя дополнительными опциями (Анализ и Дизайн).
• Щелкните «Анализ», затем «Поля, элементы и наборы».
• Далее вы получите список опций, просто нажмите «Рассчитанный элемент».

• После нажатия на рассчитанный элемент вы увидите всплывающее меню, как и выше. Это всплывающее меню имеет два варианта ввода (имя и формула) и два варианта выбора (поле и элементы).
• Мы уже обсуждали « Имя, формула и поля » в вычисляемых полях.
• Позиции : для выбора позиций для расчета.

В этом примере мы собираемся вычислить среднее значение для первой половины года и для второй половины года. Нам просто нужно добавить формулу, и она будет = среднее (янв, фев, мар, апр , май, июн).

Нам просто нужно добавить формулу и она будет = среднее (янв, фев, мар, апр, май, июн).

Проделайте то же самое для 2-го тайма.

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

Но подождите минутку.Что это? Общая сумма изменена с 1506 и 311820 долларов на 1746 и 361600 долларов.

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

Что следует помнить

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

Последние слова

. лучше анализировать данные.

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

Как вы используете вычисления в сводной таблице на своей стороне?

Другие советы по сводным таблицам

---

---

Создание вычисляемых столбцов в Power Pivot в Excel

Создание вычисляемых столбцов в Power Pivot в Excel: обзор

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

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

Вы создаете вычисляемые столбцы в Power Pivot в Excel в таблице в окне модели данных, чтобы создать значение столбца. Затем вы можете суммировать это значение в сводной диаграмме или сводной таблице в разделе «Значения».Например, если у вас есть поля «Количество» и «Цена за единицу» в таблице «Сведения о заказе», вы можете создать новый вычисляемый столбец, который отображает результат произведения этих двух столбцов в виде рассчитанной «Итого по заказу». столбец. Затем вы можете добавить этот столбец в раздел «Значения» сводной диаграммы или сводной таблицы, чтобы найти общую сумму заказов для данной группы.

Создание вычисляемых столбцов в Power Pivot в Excel — Инструкции: изображение пользователя, создающего вычисляемый столбец в окне модели данных Power Pivot в Excel, путем ввода простой формулы.

В качестве альтернативы вы также можете создать вычисляемые столбцы в Power Pivot в Excel, чтобы получить новый столбец для использования в разделах «Строка» или «Столбцы» в сводной таблице или сводной диаграмме. Например, если у вас есть поле «Дата заказа» в таблице «Заказы», ​​но вы хотите сгруппировать результаты по кварталу года, в котором был размещен заказ, вы можете создать вычисляемый столбец в таблице, который отображает квартал года для каждого связанного значения «Дата заказа». Затем вы можете добавить этот новый вычисляемый столбец в нужный раздел в сводной диаграмме или сводной таблице для группировки по его значениям.

Создание вычисляемых столбцов в Power Pivot в Excel — Инструкции: изображение пользователя, создающего вычисляемый столбец в окне модели данных Power Pivot в Excel, которое содержит формулу, использующую несколько функций.

Чтобы создать вычисляемый столбец в таблице в модели данных Power Pivot, сначала выберите вкладку таблицы в окне модели данных. Затем щелкните самую верхнюю ячейку в столбце «Добавить столбец» в дальнем правом конце таблицы.Затем введите в ячейку формулу, по которой столбец будет рассчитывать.

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

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

Создание вычисляемых столбцов в Power Pivot в Excel: инструкции

1. Чтобы создать вычисляемые столбцы в Power Pivot в Excel , выберите вкладку таблицы в окне модели данных Power Pivot, в которой нужно создать вычисляемый столбец.
2. Щелкните в самой верхней ячейке в столбце «Добавить столбец» в дальнем правом конце таблицы.
3. Введите формулу, по которой столбец будет рассчитывать в выбранном поле.
4. Для формул, вводимых вручную , формула отображается на панели формул. Начните с ввода знака равенства, за которым следуют имена полей, заключенные в квадратные скобки и соединенные стандартными математическими операторами. Вы также можете просто щелкнуть имена полей в таблице, чтобы добавить ссылку на поле во введенную формулу.
5. Чтобы принять формулу , нажмите кнопку с галочкой на панели формул или нажмите клавишу «Enter» на клавиатуре.
6. Или же, чтобы создать формулу, использующую функцию , щелкните вкладку «Конструктор» на ленте окна модели данных.
7. Затем нажмите кнопку «Вставить функцию» в группе кнопок «Вычисления», чтобы открыть диалоговое окно «Вставить функцию».
8. Затем выберите функцию в списке в этом диалоговом окне.
9. Это действие показывает функцию и ее дополнительные аргументы в нижней части диалогового окна.
10. После выбора нужной функции нажмите кнопку «ОК», чтобы вставить ее в панель формул.
11. Затем завершите ввод дополнительных аргументов функции в панель формул.
12. Чтобы принять формулу , нажмите кнопку с галочкой на панели формул или нажмите клавишу «Enter» на клавиатуре.

Создание вычисляемых столбцов в Power Pivot в Excel: видеоурок

В следующем видеоуроке под названием «Вычисляемые столбцы» показано, как создавать вычисляемые столбцы в Power Pivot в Excel. Этот видео-урок о том, как создавать вычисляемые столбцы в Power Pivot в Excel, взят из нашего полного руководства по Excel под названием «Освоение Excel Made Easy v.2019 и 365 ».

Как создать сводную таблицу с расширяющимися диапазонами данных —

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

Обычно при построении сводной таблицы вы выбираете любую ячейку в диапазоне данных и выбираете ВСТАВИТЬ> Сводная таблица.

При этом создается следующее диалоговое окно с фиксированной таблицей / диапазоном, определяемой абсолютной формулой.

На иллюстрации в качестве примера это: Sheet1! $ A $ 1: $ D $ 11

Это нормально, пока вы не добавите больше данных.

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

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

Использование именованных диапазонов

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

Это довольно просто сделать: просто выберите диапазон и введите имя в поле «Имя». Нажмите Enter, и имя будет определено. Вы также можете использовать поле имени, чтобы выбрать именованный диапазон.

Создание динамического именованного диапазона с помощью формулы смещения

Если мы хотим сделать наш именованный диапазон динамическим, мы больше не можем работать с полем имени, поэтому мы должны перейти к диспетчеру имен.Формулы> Диспетчер имен

Поскольку я уже настроил именованный диапазон и образно назвал его ДАННЫМИ, я могу просто изменить формулу для этого диапазона. Вы можете так же легко создать новое имя для своего диапазона данных, а затем использовать формулу ниже:

= СМЕЩЕНИЕ (Sheet1! $ A $ 1,0,0, COUNTA (Sheet1! $ A: $ A), 4)

Это основано на мощной функции OFFSET.

Как работает функция смещения?

Функция СМЕЩЕНИЕ возвращает диапазон на основе заданной начальной точки с указанной высотой и шириной (количеством ячеек).

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

Функция СМЕЩЕНИЕ имеет следующий синтаксис:

= СМЕЩЕНИЕ (начальная или ссылочная ячейка, строки вниз, столбцы справа, высота, ширина)

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

Вот наглядный пример, иллюстрирующий, как работает функция СМЕЩ.

Итак, новая формула динамического диапазона:

= СМЕЩЕНИЕ (Sheet1! $ A $ 1,0,0, COUNTA (Sheet1! $ A: $ A), 4)

Которая начинается с верхней левой ячейки (A1), а затем ссылается на диапазон, который составляет COUNTA (Sheet1! $ A: $ A) строк в высоту и 4 столбца в ширину. Именно этого мы и хотели.

На что следует обратить внимание:

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

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

Выберите ячейку в сводной таблице

• Параметры> Изменить исходные данные
• Перезаписать диапазон: именем вашего диапазона, в моем случае DATA
• Нажмите «ОК» и будьте уверены, что ваша сводная таблица всегда будет просматривать весь диапазон данных.

Добавление поля формулы в таблицу

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

Посмотрите или прочтите, как это сделать:

1. Создайте новую таблицу или откройте существующую таблицу в режиме конструктора, чтобы добавить поле. Для получения дополнительной информации см. Изменение дизайна таблицы.
2. В первой пустой строке введите имя поля и выберите тип данных Формула .
3. На правой панели в разделе Параметры щелкните Изменить .
4. В диалоговом окне «Формула» введите формулу. Вы также можете вставлять встроенные функции или включать поля записи из раскрывающихся списков.Например, [@field: First_Name] + Space (1) + [@ field: Last_Name].
5. Чтобы убедиться, что формула действительна, нажмите Проверить формулу .
   

   Примечание: Формула недействительна, если она содержит подзапросы SQL, агрегатные функции (SUM, AVERAGE), недетерминированные функции (GetUTCDate, Rand) или другие поля формулы.

6. Необязательно: Чтобы предотвратить дублирование значений в поле, установите флажок Уникальный .
   

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

7. Нажмите Применить , чтобы закрыть диалоговое окно, затем нажмите Сохранить .

Теперь вы можете увидеть новое поле формулы в представлении Datasheet . Выражение, которое вы ввели на вкладке Table Design , вычисляет значения для поля Formula .

Полезная информация

• Чтобы передать и получить значение поля формулы в качестве параметра, используйте следующий синтаксис [@InsertRecord FieldName ].Для получения дополнительной информации см. Параметры.
• Для построения сложных условных формул в поле формулы используйте выражение CASE с его конструкцией CASE WHEN THEN ELSE в T-SQL. Для получения дополнительных сведений просмотрите видеоролик с начала этой статьи и см. Официальную документацию Microsoft по Transact-SQL.
• Чтобы включить в формулу символы Unicode, такие как расширенные латинские символы, символы, смайлы или нелатинские символы, добавьте к строке префикс N, например: N’Ausländer ’.Некоторые символы Unicode игнорируются при сравнении.