Excel вычисляемое поле сводной таблицы если ошибка
Обновлено: 28.06.2024
Добрый день.
Прошу помочь с составлением сводной таблицы.
Есть исходная таблица. В ней сотрудники, с указанием месяца, года, ставки и отработанных часов.
В сводной таблице нужно показать сколько сотрудник заработал за каждый год.
Добавляю в сводную таблицу вычисляемое поле: ставка умножить на часы
Но вот у сотрудников, у которых есть более 1 записи в исходной таблице, итоговая ЗП по году больше в n раз (n - количество записей по сотруднику за год), чем реальная подсчитанная на калькуляторе ЗП. Как в сводной таблице добиться того, что бы значение ЗП было верное? Или может как-то иначе через сводную таблицу можно показать доход каждого за год.
Исходную таблицу по составу столбцов менять нельзя.
Сводную таблицу обязательно нужно использовать.
Помощь в написании контрольных, курсовых и дипломных работ здесь
Сводная таблица: как создать вычисляемое поле?
Добрый вечер! Есть огромная таблица из 8 полей. 8 поле "% выполнения", где цифры от 0,0 до 100,0.
EXCEL 2010 сводная таблица расчетное поле
Добрый день! Возникла следующая проблема -необходимо добавить расчетные строки в сводную.
Сводная таблица
Здравствуйте. Прошу помощи в создании сводной таблицы. Надо создать Сводную таблицу, в которой.
сводная таблица
Создать сводную таблицу, выводящую для каждого курса и группы максимальную, минимальную и среднюю.
Вручную сумму если подсчитать, примерно так Вариант подсчета по годам с использованием сводной таблицы К сожалению нужно только средствами сводной таблицы. Без доп формул. Состав данных поменяется и уже не сработают формулы. Например, добавятся сотрудники в свод. Исходную таблицу по составу столбцов менять нельзя
в чем причина? Если это учебная задача, тогда еще как-то можно понять.
Добавлено через 1 минуту
И еще, в таблице у сотрудника часы есть, а ставки нет. Ничего не заработал?
Данные в поле значений обобщают данные источников (а не значения, которые отображается) в отчете сводной таблицы следующим образом: для числовых значений используется функция СУММ, а для текстовых — функция СЧЁТ. Однако функция сведения данных может быть изменена. Можно также создать настраиваемое вычисление.
В области значений выделите поле, для которого требуется изменить функцию сведения отчета сводной таблицы.
На вкладке Анализ в группе Активное поле нажмите кнопку Активное поле, а затем — кнопку Параметры поля.
Откроется диалоговое окно Параметры поля значений.
В поле Имя источника отображается имя источника данных поля.
В поле Пользовательское имя отображается текущее имя в отчете сводной таблицы или имя источника, если пользовательское имя не указано. Для изменения значения параметра Пользовательское имя щелкните текст в этом поле и измените имя.
Перейдите на вкладку Операция.
Выберите нужную функцию сведения данных в списке Операция.
Доступные функции сведения данных
Сведение данных
Сумма значений. Функция по умолчанию для числовых значений.
Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для значений, отличных от числовых.
Количество числовых значений. Действует аналогично функции СЧЁТ.
Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Стандартное отклонение генеральной совокупности, которая содержит все сводимые значения.
Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Дисперсия генеральной совокупности, которая содержит все сводимые значения.
Число разных элементов
Число уникальных значений. Эта функция сведения работает только при использовании модели данных в Excel.
Примечание: Для некоторых типов исходных данных, таких как данные OLAP, вычисляемых полей и полей с вычисляемыми элементами функцию сведения изменить нельзя.
При необходимости можно использовать настраиваемое вычисление следующим способом.
Откройте вкладку Дополнительные вычисления.
В поле Дополнительные вычисления выберите нужный тип вычисления.
Выключение настраиваемого вычисления.
Отображение значения в процентах от общей суммы всех значений или точек данных в отчете.
% от суммы по столбцу
Отображение всех значений в каждом столбце или ряду в процентах от итогового значения по этому столбцу или ряду.
% от суммы по строке
Отображение значения в каждой строке или категории в процентах от итогового значения по этой строке или категории.
Отображение доли в процентах от значения базового элемента в соответствующем базовом поле.
% от суммы по родительской строке
Вычисление (значение элемента) / (значение родительского элемента по строкам).
% от суммы по родительскому столбцу
Вычисление (значение элемента) / (значение родительского элемента по столбцам).
% от родительской суммы
Вычисление (значение элемента) / (значение родительского элемента в выбранном базовом поле).
Отображение значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле.
Отображение значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле.
С нарастающим итогом в поле
Отображение значения в виде нарастающего итога для последовательных элементов в базовом поле.
% от суммы с нарастающим итогом в поле
Отображение значения в виде нарастающего итога в процентах для последовательных элементов в базовом поле.
Сортировка от минимального к максимальному
Отображение ранга выбранных значений в определенном поле с учетом того, что наименьшему из них присваивается значение 1, а остальным — значения более высокого ранга соответственно.
Сортировка от максимального к минимальному
Отображение ранга выбранных значений в определенном поле с учетом того, что наибольшему значению в поле присваивается значение 1, а каждому меньшему значению — более высокий ранг.
Вычисление значения следующим образом:
((значение в ячейке) x (общий итог)) / ((итог строки) x (итог столбца)).
Выберите базовое поле и базовый элемент, если они доступны для нужных вычислений.
Примечание: Базовое поле не должно совпадать с полем, выбранным на шаге 1.
Для изменения форматирования чисел нажмите кнопку Числовой формат, а затем на вкладке Число диалогового окна Формат ячеек выберите числовой формат и нажмите кнопку OK.
Если в отчете содержится несколько полей значений, повторите предыдущие шаги для каждого такого поля, которое нужно изменить.
Примечание: Чтобы использовать для одного поля несколько функций сведения, снова добавьте это поле из списка полей сводной таблицы и повторите шаги, выбрав другую функцию.
В области значений выделите поле, для которого требуется изменить функцию сведения отчета сводной таблицы.
На вкладке Параметры в группе Активное поле нажмите кнопку Активное поле, а затем — кнопку Параметры поля.
Откроется диалоговое окно Параметры поля значений.
В поле Имя источника отображается имя источника данных поля.
В поле Пользовательское имя отображается текущее имя в отчете сводной таблицы или имя источника, если пользовательское имя не указано. Для изменения значения параметра Пользовательское имя щелкните текст в этом поле и измените имя.
Перейдите на вкладку Операция.
Выберите нужную функцию сведения данных в списке Операция.
Сведение данных
Сумма значений. Функция по умолчанию для числовых значений.
Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для значений, отличных от числовых.
Количество числовых значений. Действует аналогично функции СЧЁТ.
Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Стандартное отклонение генеральной совокупности, которая содержит все сводимые значения.
Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности.
Дисперсия генеральной совокупности, которая содержит все сводимые значения.
Примечание: Для некоторых типов исходных данных, таких как данные OLAP, вычисляемых полей и полей с вычисляемыми элементами функцию сведения изменить нельзя.
При необходимости можно использовать настраиваемое вычисление следующим способом.
Откройте вкладку Дополнительные вычисления.
В поле Дополнительные вычисления выберите нужный тип вычисления.
Выключение настраиваемого вычисления.
Отображение значения в процентах от общей суммы всех значений или точек данных в отчете.
% от суммы по столбцу
Отображение всех значений в каждом столбце или ряду в процентах от итогового значения по этому столбцу или ряду.
% от суммы по строке
Отображение значения в каждой строке или категории в процентах от итогового значения по этой строке или категории.
Отображение доли в процентах от значения базового элемента в соответствующем базовом поле.
% от суммы по родительской строке
Вычисление (значение элемента) / (значение родительского элемента по строкам).
% от суммы по родительскому столбцу
Вычисление (значение элемента) / (значение родительского элемента по столбцам).
% от родительской суммы
Вычисление (значение элемента) / (значение родительского элемента в выбранном базовом поле).
Отображение значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле.
Отображение значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле.
С нарастающим итогом в поле
Отображение значения в виде нарастающего итога для последовательных элементов в базовом поле.
% от суммы с нарастающим итогом в поле
Отображение значения в виде нарастающего итога в процентах для последовательных элементов в базовом поле.
Сортировка от минимального к максимальному
Отображение ранга выбранных значений в определенном поле с учетом того, что наименьшему из них присваивается значение 1, а остальным — значения более высокого ранга соответственно.
Сортировка от максимального к минимальному
Отображение ранга выбранных значений в определенном поле с учетом того, что наибольшему значению в поле присваивается значение 1, а каждому меньшему значению — более высокий ранг.
Вычисление значения следующим образом:
((значение в ячейке) x (общий итог)) / ((итог строки) x (итог столбца)).
Выберите базовое поле и базовый элемент, если они доступны для нужных вычислений.
Примечание: Базовое поле не должно совпадать с полем, выбранным на шаге 1.
Для изменения форматирования чисел нажмите кнопку Числовой формат, а затем на вкладке Число диалогового окна Формат ячеек выберите числовой формат и нажмите кнопку OK.
Если в отчете содержится несколько полей значений, повторите предыдущие шаги для каждого такого поля, которое нужно изменить.
Примечание: Чтобы использовать для одного поля несколько функций сведения, снова добавьте это поле из списка полей сводной таблицы и повторите шаги, выбрав другую функцию.
В области значений выделите поле, для которого требуется изменить функцию сведения отчета сводной таблицы.
На вкладке Параметры в группе Активное поле нажмите кнопку Активное поле, а затем — кнопку Параметры поля.
Откроется диалоговое окно Параметры поля значений.
В поле Имя источника отображается имя источника данных поля.
В поле Пользовательское имя отображается текущее имя в отчете сводной таблицы или имя источника, если пользовательское имя не указано. Для изменения значения параметра Пользовательское имя щелкните текст в этом поле и измените имя.
Перейдите на вкладку Операция.
Выберите нужную функцию сведения данных в списке Операция.
Доступные функции сведения данных
Сведение данных
Сумма значений. Функция по умолчанию для числовых значений.
Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для значений, отличных от числовых.
Вычисления в сводной таблице подчиняются правилам по умолчанию. При формировании сводной таблицы данные в поле значений обрабатывают данные источника следующим образом:
- для числовых значений используется функция СУММ;
- для текстовых – функция СЧЁТ.
Возьмем, например, исходные данные, представленные на рис. 1. Если включить в область значений сводной таблицы поля Заказчик и Доход, то по первому полю посчитается число заказчиков, а по второму – сумма дохода (рис. 2).
Рис. 1. Исходные данные, используемые во всех примерах заметки
Рис. 2. В сводной таблице для числовых полей в области значений по умолчанию находится сумма, для остальных типов данных – количество
В Excel 2013 значительно расширены возможности вычислений в полях, относящихся к области значений. [1] Чтобы увидеть все доступные опции, откройте окно Параметры поля значений (рис. 3). Для этого, например, кликните правой кнопкой мыши на любой ячейки из области значений (на рис. 2 это – область В3:С8), и выберите пункт меню Параметры полей значений… [2] В Excel 2013 на вкладке Операция доступны 11 функций вычислений (на рис. 3а обведены 6 функций, видимых на экране), на вкладке Дополнительные вычисления – еще 15 (рис. 3б). Для сравнения, в Excel 2007 таких функций было только 8.
Рис. 3. Окно Параметры поля значений: (а) вкладка Операция, (б) вкладка Дополнительные вычисления
Основные функции
Проиллюстрируем, как работают 11 основных функций. Для этого создадим сводную таблицу, в которую 11 раз в область значений перетащим поле Доход, и последовательно настроим функции вычисления (рис. 4; я создал две таблицы, поскольку слишком широкое изображение будет плохо читаемым). Для настройки функции кликните на одной из ячеек настраиваемого столбца правой кнопкой мыши, выберите пункт меню Параметры полей значений… и отметьте соответствующую функцию на вкладке Операция. Перейдите в поле Пользовательское имя (см. рис. 3), и введите имя, соответствующее функции, например, Сумма, Среднее и др. На рис 4 видно, что в поле Произведение некоторые значения так велики, что даже превышают возможности Excel отражать такие числа.
Рис. 4. Основные функции вычислений в сводной таблице
Некоторые функции требуют пояснения. Смещенное отклонение – оно же среднеквадратичное отклонение выборки. Несмещенное отклонение – стандартное отклонение в генеральной совокупности. Аналогично и два вида дисперсии. Видно, что смещенные значения немного больше несмещенных. Более подробно о сути различий этих двух оценок см., например, Выборочная дисперсия.
Дополнительные вычисления
Дополнительные вычисления покрывают самые разнообразные требования, которые могут возникнуть при анализе данных. В зависимости от выбранной функции (область 1 на рис. 5) дополнительная настройка может не потребоваться (рис. 5а), или может потребоваться выбрать только базовое поле (рис. 5б) или, и базовое поле, и базовый элемент (рис. 5в).
Рис. 5. В зависимости от выбранной функции (область 1): (а) дополнительная настройка не требуется, (б) требуется выбор базового поля, (в) требуется выбор базового поля и базового элемента
Помните, что дополнительные вычисления накладываются на основные. Например, если в качестве основной функции выбрана Сумма (столбцы Cи Dна рис. 6), то дополнительное вычисление % от общей суммы покажет долю каждого элемента (в столбце D) от итоговой суммы 6 707 812. Если же качестве основной функции выбран Максимум, то дополнительное вычисление % от общей суммы покажет долю каждого элемента (в столбце F) от максимума 25 350.
Рис. 6. Совместное влияние на вычисления основной и дополнительной функций
Работа дополнительных функций «% от общей суммы», «% от суммы по столбцу» и «% от суммы по строке» показана на рис. 7. Эти функции не требуют дополнительных настроек.
Рис. 7. Дополнительные функции: (а) % от общей суммы, (б) % от суммы по столбцу, (в) % от суммы по строке
Несколько более сложной для понимания является дополнительная функция «доля». Чтобы создать сводную таблицу, изображенную на рис. 8:
- Дважды перетащите в область значений поле Доход
- Отсортируйте строки по полю Доход по убыванию
- Установите параметры поля значений для столбца С, как показано на рисунке.
Рис. 8. Дополнительная функция Доля
Видно, что значения в столбце Доля показывают процент от продаж в Нью-Йорке.
Начиная с версии 2010 в Excel появились дополнительные функции Сортировки. На рис. 9а показана сортировка от минимального значения (которому присваивается ранг 1) до максимального. На рис. 9б добавлено поле Регион, а сортировка осуществляется от максимального значения (ранг 1) к минимальному. Видно, что сортировка выполняется в каждом регионе отдельно.
Рис. 9. Дополнительная функция Сортировка: (а) сортировка от минимального к максимальному; (б) сортировка от максимальному к минимальному
Вычисление суммы с нарастающим итогом обычно выполняется для таблиц, в которых месяцы расположены по строкам (рис. 10а), либо, когда нужно показать, что первые Nклиентов дают N% дохода/прибыли (рис. 10б). Видно, что первые 9 клиентов обеспечивают 80% продаж.
Рис. 10. Дополнительная функция: (а) С нарастающим итогом в поле; (б) % от суммы с нарастающим итогом в поле
Дополнительная функция Приведенное отличие похожа на функцию Доля (см. рис. 8). Например, на рис. 9 в ячейке С4 функция Приведенное отличие показывает на сколько процентов доход в Детройте меньше, чем доход в Нью-Йорке.
Рис. 11. Дополнительная функция Приведенное отличие
Дополнительная функция «% от суммы по родительской строке» показывает долю текущего элемента в промежуточных итогах. Например, в ячейке Е13 (рис. 12) значение 2,75% показывает долю дохода в Чикаго ($ 184 425) от общего по стране ($6 707 812). В ячейке D9 значение 78,84% показывает долю Детройта ($1 372 957) от общего по региону Средний Запад ($1 741 424).
Рис. 12. Дополнительная функция % от суммы по родительской строке
Самой загадочной является дополнительная функция Индекс (рис. 13). Обратите внимание, индекс персиков в Джорджии 2,55, а в Калифорнии – 0,5. Если в следующем году урожай персиков пострадает, это нанесет сильный удар по фермерам Джорджии и лишь слегка затронет фермеров Калифорнии.
Рис. 13. Дополнительная функция Индекс
Чтобы понять, что в Excel подразумевается под индексом, проведите следующие вычисления. Сначала разделите продажи персиков в Джорджии (180) на продажи фруктов в Джорджии (210). Получите относительную стоимость персиков в Джорджии = 0,86. Далее разделите общие продажи персиков (285) на общие продажи всех фруктов (847). Получите Относительную долю продаж персиков = 0,34. Индекс равен отношению первого частного (0,86) ко второму (0,34). Индекс = 2,55.
Например, в Огайо индекс яблок = 4,91, поэтому производство яблок имеет первостепенную важность для этого штата.
[2] Заметка написана на основе книги Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013. Глава 3.
Pivot table и логические функции
День добрый. Есть Pivot с полями a, b и c, добавляется вычисляемое поле c логической функцией.
Pivot Table & Сроки (Продолжительность)
Как в сводных таблицах учесть что называется "продолжительность"? Поясню. Планирую ресурсы и.
Вычисляемое поле
Добрый день. Прошу помочь советом. Создаю отчет, в сводной таблице. Необходимо создать.
Сводная таблица: как создать вычисляемое поле?
Добрый вечер! Есть огромная таблица из 8 полей. 8 поле "% выполнения", где цифры от 0,0 до 100,0.
если честно так и не разобрался в таблице. если можно то поподробней что хочешь добиться и формулы по каким считать.
Добавлено через 1 минуту
считает правильно.
Например:
Регион Код Сумма по полю B*1000/a/K Сумма по полю b Сумма по полю a Сумма по полю k
Забайкальский край 3.1.5 20 953 = 5301,0575 *1000/ 253 / 1
Так же и в сводной.
Вот я и спрашиваю: В чём проблема-то?
Я привел схему расчёта в приложенном файле!
Если не очень понятно, то напишу ещё что-нибудь
з.ы. Подчеркну, что итоги считаются не так, как нужно. В итогах должно делится на k, а не сумму всех соответствующих периоду и региону k.
з.ы. Подчеркну, что итоги считаются не так, как нужно. В итогах должно делится на k, а не сумму всех соответствующих периоду и региону k.Теперь понятно.
analyst, к сожалению вынужден Вас огорчить.
По логике создателей сводных таблиц и итоги считаются тоже правильно.
Итоги подводятся согласно формуле вычисляемого поля.
Если формула = СУММ(), то и итоги будут тоже =СУММ(), но по ВСЕМ элементам поля.
В Вашем случае например Забайкальский край Итог = 8 858 исходя из Сумма по полю b=9763,1675 *1000/ Сумма по полю a=1120 / Сумма по полю k=3
Так что ничего изменить Вы не сможете, таково ядро движка сводной.
Да, я уже догнал, что это все правильно посчитано с точки зрения логики вычисляемого поля сводной таблицы! Если эту задачу нельзя решить для такого вида сводной таблица, то получается, что нужно добавить в поле "код" источника данных, название каждого региона и высчитать итоговые показатели для каждого региона? После чего убрать в сводной таблице промежуточные итоги и воспользоваться рассчитанными полями? Может есть какие-нибудь другие светлые идеи?. Если эту задачу нельзя решить для такого вида сводной таблица, то получается, что нужно добавить в поле "код" источника данных, название каждого региона и высчитать итоговые показатели для каждого региона? После чего убрать в сводной таблице промежуточные итоги и воспользоваться рассчитанными полями? Может есть какие-нибудь другие светлые идеи? Есть. Их всего две. Не у меня, а вообще
Одну Вы уже озвучили, вторая в примере. Так пойдёт? Есть. Их всего две. Не у меня, а вообще
Одну Вы уже озвучили, вторая в примере. Так пойдёт? К сожалению не работат! Если выбрать отобразить данные за 3 месяца и т.п., то отображаются не верные данные. К сожалению не работат! Если выбрать отобразить данные за 3 месяца и т.п., то отображаются не верные данные. У меня правильно считает.
Давайте файл с ошибкой (выделите цветом) и как по-Вашему должно быть. У меня правильно считает.
Давайте файл с ошибкой (выделите цветом) и как по-Вашему должно быть. . Подчеркну, что итоги считаются не так, как нужно. В итогах должно делится на k, а не сумму всех соответствующих периоду и региону k. .
Они считаются правильно.
Одновременно получить правильные данные по подуровню и по пром итогам не получится, об этом я писал ранее: . Итоги подводятся согласно формуле вычисляемого поля.
Если формула = СУММ(), то и итоги будут тоже =СУММ(), но по ВСЕМ элементам поля. Если вы хотите в одной таблице иметь и то и то, то необходимо ДВА вычисляемых поля - одно по каждому элементу, одно для пром итогов. Ну или: . добавить в поле "код" источника данных, название каждого региона и высчитать итоговые показатели для каждого региона? После чего убрать в сводной таблице промежуточные итоги и воспользоваться рассчитанными полями. Ясно! Большое спасибо за помощь!
Сводные таблицы мне не помогли!( Ещё возник вопрос: а можно ли в сводной таблице вычислять итоги и подитоги с помощью функций отличных от тех, которые есть по умолчанию? Ещё возник вопрос: а можно ли в сводной таблице вычислять итоги и подитоги с помощью функций отличных от тех, которые есть по умолчанию? Конечно можно. Любое пользовательское вычисляемое поле (или объект) именно это и делает. Но не забывайте что и весь столбец (кроме 2010 Экса) будет делать тоже самое. Конечно можно. Любое пользовательское вычисляемое поле (или объект) именно это и делает. Но не забывайте что и весь столбец (кроме 2010 Экса) будет делать тоже самое. В 2010 Excel сводные на новом движке (с надстройкой powerpivot, анализ до ста милионов строк) и правила там другие.
Сам только вчера установил и ещё пока не разобрался полностью.
Отпишусь позже.
Добрый день, форумчане.
Понимаю, что тема давнишняя, но сама недавно столкнулась с проблемой итогов для вычисляемых полей в сводной таблице.
Задача такая: дана таблица (в файле придумана для примера), по которой создается сводная таблица. В ней должна рассчитываться премия: если выручка превышает 40000, то 5%, иначе 3% от выручки. Вычисляемое поле ПремияЕсли создано, для каждого месяца рассчитывается правильно, но итог неверный.
Прочитав вышесказанное в этой теме, поняла, что итоги Excel рассчитывает не СУММ(ЕСЛИ(. )), а ЕСЛИ(СУММ(. )), т.е. всегда получается 5%.
Вопрос: можно ли как-то все-таки вывести в сводной таблице правильный итог?
Читайте также: