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

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

  • автор:

Добавление, удаление и упорядочение полей в сводной таблице в Excel с помощью средства чтения с экрана

Эта статья предназначена для людей с нарушениями зрения или когнитивных функций, использующих программы чтения с экрана, такие как WindowsЭкранный диктор, JAWS или NVDA, с продуктами Microsoft 365. Эта статья входит в набор содержимого поддержки средства чтения с экрана Microsoft 365, где содержатся дополнительные сведения о специальных возможностях в наших приложениях. Чтобы получить справку общего характера, посетите службу поддержки Майкрософт.

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

  • Новые возможности Microsoft 365 становятся доступны подписчикам Microsoft 365 по мере выхода, поэтому в вашем приложении эти возможности пока могут отсутствовать. Чтобы узнать о том, как можно быстрее получать новые возможности, станьте участником программы предварительной оценки Office.
  • Дополнительные сведения о средствах чтения с экрана см. в статье О работе средств чтения с экрана в Microsoft 365.

В этом разделе

  • Открытие области Полей сводной таблицы вручную
  • Добавление полей в сводную таблицу
  • Удаление полей из сводной таблицы
  • Упорядочивание полей в сводной таблице

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

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

  1. Поместите курсор в любую ячейку сводной таблицы.
  2. Нажмите клавиши ALT+J, T, а затем — L. Откроется область Поля сводной таблицы .

Добавление полей в сводную таблицу

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

Область Поля сводной таблицы, открытая в Excel для Windows, где отображаются выбранные поля таблицы.

  1. На листе со сводной таблицей поместите курсор в любую ячейку сводной таблицы и нажимайте клавишу F6, пока не услышите фразу «Поля сводной таблицы, введите имя поля для поиска».
  2. Чтобы просмотреть список доступных полей, используйте клавиши СТРЕЛКА ВНИЗ или СТРЕЛКА ВВЕРХ. Вы услышите имя поля и если оно не выбрано или выбрано. Неподбранные поля объявляются как «Флажок снят».
  3. При нажатии клавиши ПРОБЕЛ выберите незабранное поле, которое вы хотите добавить в сводную таблицу. Поле и его данные добавляются в сводную таблицу на сетке листа. Повторите этот шаг во всех полях, которые нужно добавить в сводную таблицу. Выбранные поля добавляются в области по умолчанию: нечисловые поля добавляются в строки, иерархии даты и времени добавляются в столбцы, а числовые поля добавляются в значения.

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

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

  1. На листе со сводной таблицей поместите курсор в любую ячейку сводной таблицы и нажимайте клавишу F6, пока не услышите фразу «Поля сводной таблицы, введите имя поля для поиска».
  2. Чтобы просмотреть список доступных полей, используйте клавиши СТРЕЛКА ВНИЗ или СТРЕЛКА ВВЕРХ. Вы услышите имя поля и если оно не выбрано или выбрано. Выбранные поля объявляются как «Флажок установлен».
  3. При переходе на выбранное поле, которое нужно удалить из сводной таблицы, нажмите клавишу ПРОБЕЛ. Поле и его данные удаляются из сводной таблицы. Повторите этот шаг для всех полей, которые нужно удалить из сводной таблицы.

Упорядочивание полей в сводной таблице

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

  1. В области Поля сводной таблицы нажимайте клавишу TAB, пока не услышите имя поля, которое нужно переместить, а затем «Кнопка».
  2. Нажмите клавишу ПРОБЕЛ, чтобы открыть контекстное меню.
  3. Нажимайте клавишу СТРЕЛКА ВВЕРХ или СТРЕЛКА ВНИЗ, пока не услышите нужный параметр, например «Переместить к подписям столбцов» или «Вверх», и нажмите клавишу ВВОД. Сводная таблица в сетке обновляется соответствующим образом.

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

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

См. также

Используйте Excel в Интернете с клавиатурой и средством чтения с экрана, чтобы добавлять или удалять поля в сводной таблице с помощью области Поля сводной таблицы . Мы протестировали его с помощью экранного диктора вMicrosoft Edge и JAWS и NVDA в Chrome, но он может работать с другими средствами чтения с экрана и веб-браузерами, если они соответствуют общим стандартам и методам специальных возможностей. Вы также узнаете, как изменить порядок полей, чтобы изменить структуру сводной таблицы.

  • Если вы используете Экранный диктор в Windows 10 Fall Creators Update, для изменения документов, электронных таблиц или презентаций в Microsoft 365 для Интернета вам понадобится отключить режим сканирования. Дополнительные сведения см. в статье Отключение виртуального режима или режима просмотра в средствах чтения с экрана в Windows 10 Fall Creators Update.
  • Новые возможности Microsoft 365 становятся доступны подписчикам Microsoft 365 по мере выхода, поэтому в вашем приложении эти возможности пока могут отсутствовать. Чтобы узнать о том, как можно быстрее получать новые возможности, станьте участником программы предварительной оценки Office.
  • Дополнительные сведения о средствах чтения с экрана см. в статье О работе средств чтения с экрана в Microsoft 365.
  • Для работы с Excel в Интернете советуем использовать в качестве веб-браузера Microsoft Edge. Excel в Интернете работает в веб-браузере, поэтому в сочетаниях клавиш есть некоторые отличия от классической программы. Например, для перехода в область команд и выхода из нее вместо клавиши F6 используются клавиши CTRL+F6. Кроме того, такие распространенные сочетания клавиш, как F1 (справка) и CTRL+O (открыть), относятся к командам веб-браузера, а не Excel в Интернете.

В этом разделе

  • Открытие области Полей сводной таблицы вручную
  • Добавление полей в сводную таблицу
  • Удаление полей из сводной таблицы
  • Упорядочивание полей в сводной таблице

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

Область Поля сводной таблицы должна автоматически отображаться при расположении курсора в любом месте сводной таблицы. Если вы несколько раз нажимаете клавиши SHIFT+CTRL+F6, но не услышите фразу «Закрыть, кнопка», необходимо вручную открыть панель.

  1. ВExcel в Интернете нажмите клавишу F11, чтобы переключиться в полноэкранный режим.
  2. В сводной таблице поместите курсор в любую ячейку.
  3. Чтобы отобразить область Поля сводной таблицы, выполните одно из следующих действий:
    • Нажмите клавиши ALT+Клавиша с логотипом Windows, J, T, а затем — L.
    • Нажимайте клавиши SHIFT+F10 или меню Windows, нажимайте клавишу СТРЕЛКА ВНИЗ или СТРЕЛКА ВПРАВО, пока не услышите фразу «Показать список полей», а затем нажмите клавишу ВВОД.

Добавление полей в сводную таблицу

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

  1. В Excel в Интернете нажмите клавишу F11, чтобы переключиться в полноэкранный режим.
  2. Чтобы переместить фокус в область Поля сводной таблицы , нажимайте клавиши SHIFT+CTRL+F6, пока не услышите фразу «Закрыть, кнопка». Фокус находится на кнопке Закрыть в области Поля сводной таблицы .
  3. В области Поля сводной таблицы , чтобы переместить фокус в список полей, нажимайте клавишу TAB, пока не услышите первое поле в списке. При использовании JAWS и NVDA вы также услышите, установлен или снят флажок поля. Экранный диктор, чтобы узнать, установлен ли флажок, нажимайте клавиши SR+СТРЕЛКА ВПРАВО несколько раз, пока экранный диктор не объявит поле и флажок.
  4. Чтобы просмотреть список полей, используйте клавишу СТРЕЛКА ВНИЗ или СТРЕЛКА ВВЕРХ.
  5. Когда фокус находится на поле, которое вы хотите добавить в сводную таблицу, нажмите клавишу ПРОБЕЛ. Выбранное поле и его данные добавляются в сводную таблицу на сетке листа. Повторите этот шаг в полях, которые нужно добавить в сводную таблицу. Выбранные поля добавляются в области по умолчанию: нечисловые поля добавляются в строки, иерархии даты и времени добавляются в столбцы, а числовые поля добавляются в значения.

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

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

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

  1. В Excel в Интернете нажмите клавишу F11, чтобы переключиться в полноэкранный режим.
  2. Чтобы переместить фокус в область Поля сводной таблицы , нажимайте клавиши SHIFT+CTRL+F6, пока не услышите фразу «Закрыть, кнопка». Фокус находится на кнопке Закрыть в области Поля сводной таблицы .
  3. В области Поля сводной таблицы , чтобы переместить фокус в список полей, нажимайте клавишу TAB, пока не услышите первое поле в списке. При использовании JAWS и NVDA вы также услышите, установлен или снят флажок поля. Экранный диктор, чтобы узнать, установлен ли флажок, нажимайте клавиши SR+СТРЕЛКА ВПРАВО, пока не объявит поле и флажок.
  4. Чтобы просмотреть список полей, используйте клавишу СТРЕЛКА ВНИЗ или СТРЕЛКА ВВЕРХ.
  5. Когда фокус находится на поле, которое нужно удалить из сводной таблицы, нажмите клавишу ПРОБЕЛ. Поле и его данные удаляются из сводной таблицы. Повторите этот шаг в полях, которые нужно удалить из сводной таблицы.

Упорядочивание полей в сводной таблице

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

  1. В Excel в Интернете нажмите клавишу F11, чтобы переключиться в полноэкранный режим.
  2. Чтобы переместить фокус в область Поля сводной таблицы , нажимайте клавиши SHIFT+CTRL+F6, пока не услышите фразу «Закрыть, кнопка». Фокус находится на кнопке Закрыть в области Поля сводной таблицы .
  3. В области Поля сводной таблицы нажимайте клавишу TAB, пока не услышите имя области, содержащей поле, которое нужно переместить, после чего появится всплывающее окно. Вы услышите, например, «Строки», за которым следует имя поля, и «Есть всплывающее окно».
  4. Когда фокус находится в нужной области, нажимайте клавишу СТРЕЛКА ВНИЗ или СТРЕЛКА ВВЕРХ, пока не услышите имя поля, которое нужно переместить.
  5. Когда фокус находится в правом поле, нажмите клавиши ALT+СТРЕЛКА ВНИЗ, чтобы развернуть контекстное меню.
  6. Нажимайте клавишу ВВЕРХ, ВНИЗ, СТРЕЛКА ВПРАВО или СТРЕЛКА ВЛЕВО, пока не услышите нужный параметр, например «Перейти к столбцам», а затем нажмите клавишу ВВОД. Сводная таблица в сетке листа обновляется соответствующим образом.

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

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

См. также

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

Корпорация Майкрософт стремится к тому, чтобы все наши клиенты получали наилучшие продукты и обслуживание. Если у вас ограниченные возможности или вас интересуют вопросы, связанные со специальными возможностями, обратитесь в службу Microsoft Disability Answer Desk для получения технической поддержки. Специалисты Microsoft Disability Answer Desk знакомы со многими популярными специальными возможностями и могут оказывать поддержку на английском, испанском, французском языках, а также на американском жестовом языке. Перейдите на сайт Microsoft Disability Answer Desk, чтобы узнать контактные сведения для вашего региона.

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

Вычисление значений в сводной таблице

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

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

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

Доступные методы вычислений

Для вычисления значений в сводной таблице можно использовать любые из описанных ниже методов.

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

Функция Сведение данных
Сумма Сумма значений. Функция по умолчанию для числовых данных.
Количество Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для данных, отличных от числовых.
Среднее Среднее арифметическое.
Максимум Наибольшее значение.
Минимум Наименьшее значение.
Произведение Произведение значений.
Количество чисел Количество числовых значений. Действует аналогично функции СЧЁТ.
Стандартное отклонение Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
СТАНДОТКЛОНП Стандартное отклонение генеральной совокупности, которая содержит все сводимые данные.
ДИСП Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Несмещенная дисперсия Дисперсия генеральной совокупности, которая содержит все сводимые данные.
Функция Результат
Без вычислений Значение, введенное в данное поле.
% от общей суммы Значения в процентах от общей суммы всех значений или точек данных в отчете.
% от суммы по столбцу Все значения в каждом столбце или ряду в процентах от итогового значения по этому столбцу или ряду.
% от суммы по строке Значение в каждой строке или категории в процентах от итогового значения по этой строке или категории.
Доля Значения в процентах от значения базового элемента в соответствующем базовом поле.
% от суммы по родительской строке Рассчитывает значения следующим образом: (значение элемента) / (значение родительского элемента по строкам).
% от суммы по родительскому столбцу Рассчитывает значения следующим образом: (значение элемента) / (значение родительского элемента по столбцам).
% от родительской суммы Рассчитывает значения следующим образом: (значение элемента) / (значение родительского элемента в выбранном базовом поле).
Отличие Значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле.
Приведенное отличие Значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле.
С нарастающим итогом в поле Значение в виде нарастающего итога для последовательных элементов в базовом поле.
% от суммы с нарастающим итогом в поле Значение в виде нарастающего итога в процентах для последовательных элементов в базовом поле.
Сортировка от минимального к максимальному Ранг выбранных значений в определенном поле с учетом того, что наименьшему из них присваивается значение 1, а остальным — значения более высокого ранга соответственно.
Сортировка от максимального к минимальному Ранг выбранных значений в определенном поле с учетом того, что наибольшему значению в поле присваивается значение 1, а каждому меньшему значению — более высокий ранг.
Индекс Рассчитывает значения следующим образом: ((значение в ячейке) x (общий итог)) / ((итог строки) x (итог столбца)).
  • Формулы. Если функции сведения и настраиваемые вычисления не дают желаемых результатов, вы можете создать собственные формулы в вычисляемых полях и вычисляемых объектах. Например, можно добавить вычисляемый объект с формулой расчета комиссионных за продажу, которые могут быть разными в различных регионах. Эти комиссионные будут автоматически включены в промежуточные и общие итоги в отчете.

Влияние типа источника данных на вычисления

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

  • Вычисления на основе исходных данных OLAP. При создании сводных таблиц на основе кубов OLAP сводные значения вычисляются на сервере OLAP еще до отображения результатов в Excel. В сводной таблице невозможно изменить способ вычисления этих значений. Например, вы не сможете выбрать другую функцию сведения для вычисления полей данных или промежуточных итогов и добавить вычисляемые поля или вычисляемые объекты. Кроме того, если сервер OLAP предоставляет вычисляемые поля, называемые «вычисляемыми элементами», вы увидите их в списке полей сводной таблицы. Вы также увидите все вычисляемые поля и вычисляемые объекты, созданные с помощью макросов, которые написаны на языке Visual Basic для приложений (VBA) и хранятся в книге, но не сможете их изменить. Если вам нужны дополнительные типы вычислений, обратитесь к администратору базы данных OLAP. Если исходные данные получены из базы данных OLAP, то при вычислении промежуточных и общих итогов можно включить или исключить значения для скрытых элементов.
  • Вычисления на основе исходных данных не из базы данных OLAP. В сводных таблицах, основанных на внешних данных других типов или на данных листа Excel, для вычисления полей значений, содержащих числовые данные, используется функция «Сумма», а для вычисления полей данных, содержащих текст, — функция «Количество». Для дальнейшего анализа и обработки своих данных вы можете выбрать другие функции сведения, например «Среднее», «Максимум» или «Минимум». Кроме того, можно создавать собственные формулы, в которых используются элементы отчета или другие данные листа. Для этого нужно создать вычисляемое поле или вычисляемый объект в поле.

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

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

  • Элементы формулы сводной таблицы. В формулах, которые создаются для вычисляемых полей и вычисляемых объектов, можно использовать операторы и выражения, как и в других формулах на листе. Также можно использовать константы и ссылаться на данные из отчета, но не допускается использование ссылок на ячейки и определенных имен. Невозможно использовать функции листа, для которых нужны аргументы в виде ссылок на ячейки или определенных имен, а также формулы массива.
  • Имена полей и элементов. В Excel имена полей и элементов используются для идентификации этих элементов отчета в формулах. В приведенном ниже примере для данных в диапазоне C3:C9 используется имя поля Молоко. Для вычисляемого объекта в поле Тип, оценивающего объем продаж нового продукта на основе данных о продажах молочных продуктов, можно использовать формулу =Молоко * 115%.

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

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

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

Например, на этой сводной диаграмме представлены данные о продажах для каждого продавца по регионам:

Чтобы посмотреть, как будут выглядеть объемы продаж, если увеличатся на 10 %, можно создать вычисляемое поле в связанной сводной таблице и воспользоваться формулой =Продажи * 110%.

Результат сразу отображается на сводной диаграмме, как показано на этом рисунке:

Чтобы отобразить отдельный маркер данных для продаж в регионе «Север» за вычетом транспортных расходов, которые составляют 8 %, можно создать в поле «Регион» вычисляемый объект с такой формулой: =Север – (Север * 8%).

Диаграмма будет выглядеть следующим образом:

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

Создание формул в сводной таблице

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

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

В случае вычисляемых объектов в отдельных ячейках можно вводить разные формулы. Например, если вычисляемый объект с именем ЯблокиОбласть содержит формулу =Яблоки * 0,25 для всех месяцев, то для июля, августа и сентября ее можно заменить формулой =Яблоки * 0,5.

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

Добавление вычисляемого поля

Изображение ленты Excel

  1. Щелкните сводную таблицу. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
  2. На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемое поле.

Добавление вычисляемого объекта в поле

Изображение ленты Excel

  1. Щелкните сводную таблицу. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
  2. Если элементы в поле сгруппированы, на вкладке Анализ в группе Группировать выберите команду Разгруппировать.

Изображение ленты Excel

  • Щелкните поле, в которое нужно добавить вычисляемый объект.
  • На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемый объект.

    Ввод разных формул для вычисляемых объектов в отдельных ячейках

    1. Щелкните ячейку, для которой нужно изменить формулу. Чтобы изменить формулу для нескольких ячеек, нажмите клавишу CTRL и, удерживая ее, выделите остальные ячейки.
    2. В строке формул внесите изменения в формулу.

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

    Изображение ленты Excel

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

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

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

    Изображение ленты Excel

    1. Щелкните сводную таблицу. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
    2. На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вывести формулы.

    Редактирование формулы в сводной таблице

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

    В случае вычисляемых объектов можно изменять отдельные формулы для конкретных ячеек одного объекта. Например, если вычисляемый объект с именем ЯблокиРасчет содержит формулу =Яблоки * 0,25 для всех месяцев, то для июля, августа и сентября ее можно заменить формулой =Яблоки * 0,5.

    Определение принадлежности формулы к вычисляемому полю или вычисляемому объекту

    Изображение ленты Excel

    1. Щелкните сводную таблицу. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
    2. На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вывести формулы.

    Редактирование формулы вычисляемого поля

    Изображение ленты Excel

    1. Щелкните сводную таблицу. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
    2. На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемое поле.

    Редактирование одной формулы для вычисляемого объекта

    Изображение ленты Excel

    1. Щелкните поле, содержащее вычисляемый объект.
    2. На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемый объект.

    Редактирование отдельной формулы для конкретной ячейки вычисляемого объекта

    1. Щелкните ячейку, для которой нужно изменить формулу. Чтобы изменить формулу для нескольких ячеек, нажмите клавишу CTRL и, удерживая ее, выделите остальные ячейки.
    2. В строке формул внесите изменения в формулу.

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

    Удаление формулы из сводной таблицы

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

    Изображение ленты Excel

    1. Определите, к чему относится формула: к вычисляемому полю или вычисляемому объекту. Вычисляемые поля отображаются в списке полей сводной таблицы. Вычисляемые объекты отображаются в виде элементов в других полях.
    2. Выполните одно из указанных ниже действий.
      • Если нужно удалить вычисляемое поле, щелкните в любом месте сводной таблицы.
      • Если нужно удалить вычисляемый объект, в сводной таблице щелкните поле, которое его содержит. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
    3. На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемое поле или Вычисляемый объект.

    Для сведения данных в сводной таблице в Excel в Интернете можно использовать такие функции, как СУММ, СЧЁТ и СРЗНАЧ. По умолчанию для чисел в полях значений используется функция СУММ. Вы можете просматривать и редактировать сводную таблицу на основе источника данных OLAP, но не можете создать ее в Excel для Интернета.

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

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

    Поле значений в списке полей

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

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

    Сводная таблица на iPad постепенно развертывается для пользователей, использующих Excel на iPad версии 2.80.1203.0 и выше.

    Чтобы суммировать значения в сводной таблице в Excel для iPad, можно использовать функции сводки, такие как Sum, Count и Average. По умолчанию для чисел в полях значений используется функция СУММ. Вы можете просматривать и изменять сводную таблицу на основе источника данных OLAP, но не можете создать ее в Excel для iPad.

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

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

    Кнопка списка полей

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

    Многоточие значений полей сводной таблицы на iPad

    3. Выберите Параметры поля.

    Поля значений сводной таблицы на iPad.

    4. Проверьте нужную функцию сводки.

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

    Функция сведения данных

    Вычисляемое значение

    Сумма значений. Используется по умолчанию для полей с числовыми значениями.

    Количество заполненных полей. Функция сведения данных СЧЁТ работает так же, как СЧЁТЗ. СЧЁТ по умолчанию используется для пустых полей и полей с нечисловыми значениями.

    Количество значений, содержащих числа (отличается от функции СЧЁТ, в которой учитываются заполненные поля).

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

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

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

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

    Дополнительные сведения

    Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

    Вычисляемое поле в Сводных таблицах в MS Excel

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

    Нашей задачей будет:

    • вычислить % выполнения плана
    • представить полученные данные по годам для каждого месяца (каждый год — отдельный столбец)

    В итоге у нас должна получиться вот такая сводная таблица.

    Исходная таблица

    Исходную таблицу подготовим в специальном формате таблиц MS EXCEL (см. статью Таблицы в формате EXCEL 2007 ).

    На основе даты продажи в столбце А, в таблице рассчитываются 2 столбца: Номер месяца =МЕСЯЦ() и Год =ГОД() . Для форматирования ячеек столбца А в виде окт11 использован пользовательский формат Даты [$-419]МММГГ;@.

    Столбец План представляет собой линейный тренд (это не важно для целей данной статьи), столбец Продано — фактический объем продаж.

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

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

    Нажав ОК, сводная таблица автоматически создастся на новом листе.

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

    Перед тем как создать Вычисляемое поле перетащите поле Номер месяца в Названия строк.

    Создаем вычисляемое поле

    Для решения задачи нам потребуется вычислить % выполнения плана по формуле =’Продано, руб.’/’План, руб.’

    Это можно сделать непосредственно в Сводной таблице , создав Вычисляемое поле ПроцентВыполнения.

    Для этого выделите ячейку в Сводной таблице, в появившемся меню Работа со сводными таблицами выберите Параметры/ Вычисления/ Поля, элементы и наборы/ Вычисляемое поле :

    Появится диалоговое окно:

    Интерфейс этого окна не относится к интуитивно понятным вещам, поэтому требует дополнительного пояснения:

    • Вместо Поле1 введите название Вычисляемого поля, например, ПроцентВыполнения
    • В списке полей выделите поле Продано, руб. и нажмите кнопку Добавить поле или дважды кликните на него. Название поля будет введено в поле Формула
    • Введите символ деления / в поле Формула
    • В списке полей выделите поле План, руб. и нажмите кнопку Добавить поле
    • Нажмите ОК

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

    После несложного форматирования Сводная таблица приобретет законченный вид (необходимо убрать ошибку #ДЕЛ/0!, изменить названия столбцов и изменить формат ячеек на процентный ).

    Обратите внимание, что Сводная таблица содержит Общий итог как по столбцам, так и по строкам.

    Теперь разберемся, что Вычисляемое поле нам насчитало.

    Вычисляемое поле. Алгоритм расчета

    Для каждого месяца у нас есть только одно значение фактических продаж (столбец Продажи) и плана. Вычисляемое поле ПроцентВыполнения возвращает значение равное их отношению. Например, для января 2012 года — это 50,19% (продано было 36992,22, а план был 73697,76). 36992,22/73697,76=0,5019 (см. строку 10 на листе Исходная таблица).

    Теперь проверим итоги по месяцам. За январь итоговым значением является 93,00%. Как это значение получилось?

    Сначала программа вычислила СУММУ продаж за январь по всем годам, затем, вычислила СУММУ всех плановых значений. Разделив одно на другое, было получено 93,00%. В этом можно убедиться проделав вычисления самостоятельно (см. строку 10 на листе Сводная таблица, столбцы H:J).

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

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

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

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

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

    Изменяем и удаляем Вычисляемое поле

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

    Там же можно удалить это поле.

    Еще одно ограничение

    Еще одно ограничение Вычисляемого поля проявляется при попытке использовать его в качестве названия Строк или Столбцов Сводной таблицы. Этого сделать нельзя. Покажем это на нашем примере.

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

    Создать само Вычисляемое поле для номера месяца — не проблема:

    Однако, перенести его в качестве строк сводной таблицы не получается.

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

    Margin

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

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

    В примере вычисляемым поле является поле с валовым доходом с НДС .

    Предположим, нам необходимо изменить расчет в поле и перевести в валовый доход БЕЗ НДС.

    Для упрощения, определим ставку НДС=18% для всех групп товаров в таблице.

    Для этого:

    1. Кликнем на любом элементе сводной таблицы и в группе меню «Параметры» на ленте.

    1

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

    =’Т/О в розничных ценах с НДС’ -‘Т/О в ценах закупки с НДС’


    4

    =(‘Т/О в розничных ценах с НДС’ -‘Т/О в ценах закупки с НДС’)/1,18

    5. Переименуем поле и нажмем кнопку «Изменить» . Закроем модуль.

    Итогом будет пересчитанный, уже по новой формуле, столбец.

    Для удаления поля, необходимо выбрать имя поля в модуле и нажать «Удалить».

    Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:

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

    Ваш адрес email не будет опубликован. Обязательные поля помечены *