Как добавить столбец в сводную таблицу excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Управление сводными таблицами в Excel
Сводная таблица – мощный инструмент Microsoft Excel. С ее помощью пользователь анализирует большие по объему диапазоны, подводит итоги всего в несколько кликов, выводит на экран только нужную в данный момент информацию.
Фильтр в сводной таблице Excel
В сводную таблицу можно преобразовать практически любой диапазон данных: итоги финансовых операций, сведения о поставщиках и покупателях, каталог домашней библиотеки и т.д.
Для примера возьмем следующую таблицу:
Создадим сводную таблицу: «Вставка» — «Сводная таблица». Поместим ее на новый лист.
Мы добавили в сводный отчет данные по поставщикам, количеству и стоимости.
Напомним, как выглядит диалоговое окно сводного отчета:
Перетаскивая заголовки, мы задаем программе инструкции для формирования сводного отчета. Если случайно допустим ошибку, из нижней области можно удалить заголовок, заменить его другим.
По данным, которые помещены в поле «Значения», подводятся итоги. В автоматическом режиме – сумма. Но можно задать «среднее», «максимум» и т.д. Если сделать это нужно для значений всего поля, то щелкаем по названию столбца и меняем способ представления итогов:
Например, среднее количество заказов по каждому поставщику:
Итоги можно менять не во всем столбце, а только в отдельной ячейке. Тогда щелкаем правой кнопкой мыши именно по этой ячейке.
Установим фильтр в сводном отчете:
- В перечне полей для добавления в таблицу ставим галочку напротив заголовка «Склад».
- Перетащим это поле в область «Фильтр отчета».
- Таблица стала трехмерной – признак «Склад» оказался вверху.
Теперь мы можем отфильтровать значения в отчете по номеру склада. Нажимаем на стрелочку в правом углу ячейки и выбираем интересующие нас позиции:
В отчете отображается информация только по первому складу. Вверху видим значение и значок фильтра.
Отфильтровать отчет можно также по значениям в первом столбце.
Сортировка в сводной таблице Excel
Немного преобразуем наш сводный отчет: уберем значение по «Поставщикам», добавим «Дату».
Сделаем таблицу более полезной – сгруппируем даты по кварталам. Для этого щелкнем правой кнопкой мыши по любой ячейке с датой. В выпадающем меню выберем «Группировать». Заполним параметры группировки:
После нажатия ОК сводная таблица приобретает следующий вид:
Отсортируем данные в отчете по значению столбца «Стоимость». Кликнем правой кнопкой мыши по любой ячейке или названию столбца. Выбираем «Сортировка» и способ сортировки.
Значения в сводном отчете поменяются в соответствии с отсортированными данными:
Теперь выполним сортировку данных по дате. Правая кнопка мыши – «Сортировка». Можно выбрать способ сортировки и на этом остановиться. Но мы пойдем по другому пути. Нажмем «Дополнительные параметры сортировки». Откроется окно вида:
Установим параметры сортировки: «Дата по убыванию». Кликнем по кнопке «Дополнительно». Поставим галочку напротив «Автоматической сортировки при каждом обновлении отчета».
Теперь при появлении в сводной таблице новых дат программа Excel будет сортировать их по убыванию (от новых к старым):
Формулы в сводных таблицах Excel
Сначала составим сводный отчет, где итоги будут представлены не только суммой. Начнем работу с нуля, с пустой таблицы. За одно узнаем как в сводной таблице добавить столбец.
- Добавим в отчет заголовок «Поставщик». Заголовок «Стоимость» три раза перетащим в поле «Значения» — в сводную таблицу добавятся три одинаковых столбца.
- Для первого столбца оставим значение «Сумма» для итогов. Для второго – «Среднее». Для третьего – «Количество».
- Поменяем местами значения столбцов и значения строк. «Поставщик» — в названия столбцов. «Σ значения» — в названия строк.
Сводный отчет стал более удобным для восприятия:
Научимся прописывать формулы в сводной таблице. Щелкаем по любой ячейке отчета, чтобы активизировать инструмент «Работа со сводными таблицами». На вкладке «Параметры» выбираем «Формулы» — «Вычисляемое поле».
Жмем – открывается диалоговое окно. Вводим имя вычисляемого поля и формулу для нахождения значений.
Получаем добавленный дополнительный столбец с результатом вычислений по формуле.
Экспериментируйте: инструменты сводной таблицы – благодатная почва. Если что-то не получится, всегда можно удалить неудачный вариант и переделать.
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
Создание именованного набора в сводной таблице OLAP
При работе с интерактивной аналитической OLAP сводной таблицей в Excel можно создавать именованные наборы, гибкие функции, которые можно использовать для:
- Группирование общих наборов элементов, которые можно использовать повторно, даже если эти наборы не присутствуют в данных.
- Объедините элементы из разных иерархий способами, которые были недоступны в более ранних версиях Excel, которые часто называют асимметричными отчетами.
- Создайте именованный набор с помощью пользовательских многомерных выражений (MDX), языка запросов для баз данных OLAP, который предоставляет синтаксис вычислений, аналогичный формулам листа.
Если вы не знакомы с языком запросов многомерных выражений (MDX), можно создать именованный набор на основе элементов в строках или столбцах сводной таблицы. Дополнительные сведения о языке запросов многомерных выражений см. в статье «Запрос многомерных данных».
Если вы знакомы с языком запросов многомерных выражений, можно использовать многомерные выражения для создания или изменения именованного набора.
В этой статье
- Создание именованного набора на основе элементов строк или столбцов
- Создание именованного набора с помощью многомерных выражений
- Изменение или удаление именованного набора
Создание именованного набора на основе элементов строк или столбцов
- Щелкните сводную таблицу OLAP, для которой нужно создать именованный набор. Откроется окно «Инструменты сводной таблицы» с добавлением вкладок «Параметры » и «Конструктор «.
- На вкладке «Параметры» в группе «Вычисления» щелкните «Поля», «Элементы»,«Наборы &«, а затем выберите команду «Создать набор на основе элементов строк» или «Создать набор на основе элементов столбцов». Отобразится диалоговое окно «Новый набор». При необходимости можно изменить размер этого диалогового окна, перетащив маркер изменения размера в правом нижнем углу диалогового окна.
- В поле «Задать имя » введите имя, которое вы хотите использовать для набора.
- Чтобы указать строки, которые необходимо включить в именованный набор, выполните одно или несколько из следующих действий:
- Чтобы удалить строку из списка элементов, щелкните область слева от нужной строки и нажмите кнопку «Удалить строку».
- Чтобы добавить новую строку в список элементов, щелкните область слева от строки, под которой нужно добавить новую строку, а затем нажмите кнопку «Добавить строку».
- Чтобы создать копию элемента, щелкните область слева от строки, которую нужно скопировать, а затем нажмите кнопку «Копировать строку».
- Чтобы переместить элемент в другое место, щелкните область слева от строки, которую вы хотите переместить, а затем с помощью стрелок вверх и вниз переместите его в соответствующее расположение.
- По умолчанию элементы из разных уровней будут отображаться в отдельных полях иерархии, а именованный набор заменяет текущие поля в области строк или столбцов.
- Чтобы отобразить эти элементы в том же поле, что и другие элементы, снимите флажок «Отображаемые элементы с разных уровней » в отдельных полях .
- Чтобы текущие поля отображались в области строк или столбцов, снимите флажок «Заменить поля в области строк» новым набором или замените поля в области столбцов новым флажком. Набор не будет отображаться в сводной таблице при нажатии кнопки «ОК», но будет доступен в списке полей сводной таблицы.
- Нажмите кнопку « ОК», чтобы создать именованный набор.
- Чтобы отменить все действия после закрытия диалогового окна, нажмите кнопку «Отменить» на панели быстрого доступа.
- К именованным наборам нельзя применить фильтрацию любого типа.
Создание именованного набора с помощью многомерных выражений
Важно: При использовании многомерных выражений для создания именованного набора или изменения определения многомерных выражений существующего именованного набора любые дополнительные изменения можно выполнить только с помощью многомерных выражений.
- Щелкните сводную таблицу OLAP, для которой нужно создать пользовательский именованный набор.
- На вкладке «Параметры» в группе «Вычисления» щелкните «Поля», «Элементы»,«& Наборы«, а затем выберите пункт «Управление наборами». Отобразится диалоговое окно «Диспетчер наборов». При необходимости можно изменить размер этого диалогового окна, перетащив маркер изменения размера в правом нижнем углу диалогового окна.
- Нажмите кнопку «Создать», а затем нажмите кнопку «Создать набор» с помощью многомерных выражений.
- В поле «Задать имя » введите имя, которое вы хотите использовать для набора.
- Чтобы задать определение многомерных выражений для именованного набора, выполните одно из следующих действий:
- В поле «Задать определение » введите или вставьте скопированное определение многомерных выражений.
- На вкладке «Поля и элементы » выберите запись списка полей, которую нужно включить, и нажмите кнопку «Вставить». Вы также можете перетащить запись списка полей в поле определения «Задать» или дважды щелкнуть запись списка полей. Доступные записи списка полей
| Запись списка полей | Примеры многомерных выражений, созданных с помощью куба Adventure Works |
| измерение | [Продукт] |
| Иерархия атрибутов (включает все элементы) | [Продукт]. [Категория] |
| Уровень иерархии атрибутов (не включает все члены) | [Продукт]. [Категория]. [Категория] |
| Элемент из иерархии атрибутов | [Продукт]. [Категория].&[4] |
| Иерархия пользователей | [Продукт]. [Категории продуктов] |
| Уровень иерархии пользователей | [Продукт]. [Категории продуктов]. [Категория] |
| Элемент из пользовательской иерархии | [Продукт]. [Категории продуктов]. [Категория].&[4] |
| Измерения | [Меры]. [Интернет-объем продаж] |
| Вычисляемая мера | [Меры]. [Отношение интернета к родительскому продукту] |
| Именованный набор | [Основная группа продуктов] |
| Значение ключевого показателя эффективности | KPIValue («Коэффициент валовой прибыли продукта) |
| Цель ключевого показателя эффективности | KPIGoal(«Product Gross Profit Margin»), |
| Состояние ключевого показателя эффективности | KPIStatus («Валовая прибыль продукта») |
| Тренд ключевого показателя эффективности | KPITrend(«Валовая прибыль продукта») |
| Свойство member из пользовательской иерархии | [Продукт]. [Категории продуктов]. Properties(«Class») |
| Свойство member из иерархии атрибутов | [Продукт]. [Продукт]. Properties(«Class») |
- Чтобы отобразить эти элементы в том же поле, что и другие элементы, снимите флажок «Отображаемые элементы с разных уровней » в отдельных полях .
- Чтобы изменить иерархию по умолчанию и сохранить повторяющиеся записи, снимите автоматический порядок и удалите дубликаты из установленного флажка.
- Чтобы текущие поля отображались в области строк или столбцов, снимите флажок «Заменить поля в области строк» новым набором или замените поля в области столбцов новым флажком. Набор не будет отображаться в сводной таблице при нажатии кнопки «ОК», но будет доступен в списке полей сводной таблицы.
- Чтобы предотвратить пересчет именованного набора при каждом обновлении, снимите флажок «Пересчет» с каждым флажком обновления.
- Чтобы отменить все действия после закрытия диалогового окна, нажмите кнопку «Отменить» на панели быстрого доступа.
- К именованным наборам нельзя применить фильтрацию любого типа.
Изменение или удаление именованного набора
- Щелкните сводную таблицу OLAP, содержащую именованный набор, который требуется изменить или удалить.
- На вкладке «Параметры» в группе «Вычисления» щелкните «Поля», «Элементы»,«& Наборы«, а затем выберите пункт «Управление наборами». Отобразится диалоговое окно «Диспетчер наборов». При необходимости можно изменить размер этого диалогового окна, перетащив маркер изменения размера в правом нижнем углу диалогового окна.
- Выберите набор, который требуется изменить или удалить.
- Выполните одно из указанных ниже действий.
- Чтобы изменить выбранный именованный набор, нажмите кнопку «Изменить» и внесите необходимые изменения.
- Чтобы удалить выбранный именованный набор, нажмите кнопку «Удалить», а затем нажмите кнопку «Да «, чтобы подтвердить.
Вычисление значений в сводной таблице
В сводных таблицах можно использовать функции сведения в полях значений для объединения значений из исходных данных. Если функции сведения и настраиваемые вычисления не дают желаемых результатов, вы можете создать собственные формулы в вычисляемых полях и вычисляемых объектах. Например, можно добавить вычисляемый объект с формулой расчета комиссионных за продажу, которые могут быть разными в различных регионах. Эти комиссионные будут автоматически включены в промежуточные и общие итоги в сводной таблице.
Другой способ вычислений — использовать меры в 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.
Если есть несколько вычисляемых объектов или формул, можно настроить порядок вычислений.
Добавление вычисляемого поля
- Щелкните сводную таблицу. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
- На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемое поле.
- В поле Имя введите имя для поля.
- В поле Формула введите формулу для поля. Чтобы использовать в формуле данные из другого поля, щелкните его в списке Поля и нажмите кнопку Добавить поле. Например, чтобы вычислить величину комиссионных, составляющую 15 %, для каждого значения в поле «Продажи», введите формулу =Продажи * 15%.
- Нажмите кнопку Добавить.
Добавление вычисляемого объекта в поле
- Щелкните сводную таблицу. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
- Если элементы в поле сгруппированы, на вкладке Анализ в группе Группировать выберите команду Разгруппировать.
- Щелкните поле, в которое нужно добавить вычисляемый объект.
- На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемый объект.
- В поле Имя введите имя вычисляемого объекта.
- В поле Формула введите формулу для этого объекта. Чтобы использовать в формуле данные из имеющегося элемента, щелкните его в списке Элементы и нажмите кнопку Добавить элемент (он должен находиться в том же поле, что и вычисляемый объект).
- Нажмите кнопку Добавить.
Ввод разных формул для вычисляемых объектов в отдельных ячейках
- Щелкните ячейку, для которой нужно изменить формулу. Чтобы изменить формулу для нескольких ячеек, нажмите клавишу CTRL и, удерживая ее, выделите остальные ячейки.
- В строке формул внесите изменения в формулу.
Изменение порядка вычислений для нескольких вычисляемых объектов или формул
- Щелкните сводную таблицу. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
- На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Порядок вычислений.
- Щелкните одну из формул и нажмите кнопку Вверх или Вниз.
- Повторите эти действия для других формул, чтобы расположить их в нужном порядке.
Просмотр всех формул, используемых в сводной таблице
Вы можете отобразить список всех формул, которые используются в текущей сводной таблице.
- Щелкните сводную таблицу. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
- На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вывести формулы.
Редактирование формулы в сводной таблице
Перед тем как редактировать формулу, определите, к чему она относится: к вычисляемому полю или вычисляемому объекту. Если формула находится в вычисляемом объекте, также определите, является ли она единственной для него.
В случае вычисляемых объектов можно изменять отдельные формулы для конкретных ячеек одного объекта. Например, если вычисляемый объект с именем ЯблокиРасчет содержит формулу =Яблоки * 0,25 для всех месяцев, то для июля, августа и сентября ее можно заменить формулой =Яблоки * 0,5.
Определение принадлежности формулы к вычисляемому полю или вычисляемому объекту
- Щелкните сводную таблицу. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
- На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вывести формулы.
- Найдите в списке формулу, которую нужно изменить. Она может находиться в разделе «Вычисляемое поле» или «Вычисляемый объект».
Редактирование формулы вычисляемого поля
- Щелкните сводную таблицу. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
- На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемое поле.
- В списке Имя выберите вычисляемое поле, для которого нужно изменить формулу.
- В поле Формула измените формулу.
- Нажмите кнопку Изменить.
Редактирование одной формулы для вычисляемого объекта
- Щелкните поле, содержащее вычисляемый объект.
- На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемый объект.
- В поле Имя выберите вычисляемый объект.
- В поле Формула измените формулу.
- Нажмите кнопку Изменить.
Редактирование отдельной формулы для конкретной ячейки вычисляемого объекта
- Щелкните ячейку, для которой нужно изменить формулу. Чтобы изменить формулу для нескольких ячеек, нажмите клавишу CTRL и, удерживая ее, выделите остальные ячейки.
- В строке формул внесите изменения в формулу.
Совет: Если есть несколько вычисляемых объектов или формул, можно настроить порядок вычислений. Дополнительные сведения см. в разделе Изменение порядка вычислений для нескольких вычисляемых объектов или формул.
Удаление формулы из сводной таблицы
Примечание: Если удалить формулу из сводной таблицы, восстановить ее будет невозможно. Если вы не хотите удалять формулу безвозвратно, то можете просто скрыть поле или элемент, перетащив его за пределы сводной таблицы.
- Определите, к чему относится формула: к вычисляемому полю или вычисляемому объекту. Вычисляемые поля отображаются в списке полей сводной таблицы. Вычисляемые объекты отображаются в виде элементов в других полях.
- Выполните одно из указанных ниже действий.
- Если нужно удалить вычисляемое поле, щелкните в любом месте сводной таблицы.
- Если нужно удалить вычисляемый объект, в сводной таблице щелкните поле, которое его содержит. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
- На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемое поле или Вычисляемый объект.
- В поле Имя выберите поле или элемент, который нужно удалить.
- Нажмите кнопку Удалить.
Для сведения данных в сводной таблице в Excel в Интернете можно использовать такие функции, как СУММ, СЧЁТ и СРЗНАЧ. По умолчанию для чисел в полях значений используется функция СУММ. Вы можете просматривать и редактировать сводную таблицу на основе источника данных OLAP, но не можете создать ее в Excel для Интернета.
Ниже описано, как выбрать другие функции сведения данных.
- Щелкните в любом месте сводной таблицы, а затем выберите Сводная таблица >Список полей. Вы также можете щелкнуть сводную таблицу правой кнопкой мыши и выбрать Показать список полей.
- В списке Поля сводной таблицы в группе Значения щелкните стрелку рядом с полем значений.
Примечание: Функции сведения недоступны в сводных таблицах на базе источников данных OLAP.
| Функция сведения данных | Вычисляемое значение |
|---|---|
| Сумма | Сумма значений. Используется по умолчанию для полей с числовыми значениями. |
| СЧЁТ | Количество заполненных полей. Функция сведения данных СЧЁТ работает так же, как СЧЁТЗ. СЧЁТ по умолчанию используется для пустых полей и полей с нечисловыми значениями. |
| Среднее | Среднее арифметическое. |
| Максимум | Наибольшее значение. |
| Минимум | Наименьшее значение. |
| Произведение | Произведение значений. |
| Количество чисел | Количество значений, содержащих числа (отличается от функции СЧЁТ, в которой учитываются заполненные поля). |
| Смещенное отклонение | Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности. |
| СТАНДОТКЛОНП | Стандартное отклонение генеральной совокупности, которая содержит все сводимые данные. |
| ДИСП | Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности. |
| Несмещенная дисперсия | Дисперсия генеральной совокупности, которая содержит все сводимые данные. |
Сводная таблица на iPad постепенно развертывается для пользователей, использующих Excel на iPad версии 2.80.1203.0 и выше.
Чтобы суммировать значения в сводной таблице в Excel для iPad, можно использовать функции сводки, такие как Sum, Count и Average. По умолчанию для чисел в полях значений используется функция СУММ. Вы можете просматривать и изменять сводную таблицу на основе источника данных OLAP, но не можете создать ее в Excel для iPad.
Ниже описано, как выбрать другие функции сведения данных.
1. Коснитесь в любом месте сводной таблицы, чтобы отобразить ее на вкладке Сводная таблица , проведите пальцем влево и выберите Список полей , чтобы отобразить список полей.
2. В списке Поля сводной таблицы в разделе Значения коснитесь многоточия рядом с полем значения.
3. Выберите Параметры поля.
4. Проверьте нужную функцию сводки.
Примечание: Функции сведения недоступны в сводных таблицах на базе источников данных OLAP.
Функция сведения данных
Вычисляемое значение
Сумма значений. Используется по умолчанию для полей с числовыми значениями.
Количество заполненных полей. Функция сведения данных СЧЁТ работает так же, как СЧЁТЗ. СЧЁТ по умолчанию используется для пустых полей и полей с нечисловыми значениями.
Количество значений, содержащих числа (отличается от функции СЧЁТ, в которой учитываются заполненные поля).
Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Стандартное отклонение генеральной совокупности, которая содержит все сводимые данные.
Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Дисперсия генеральной совокупности, которая содержит все сводимые данные.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.