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

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как выполнить вычисление в сводной таблице? (Сводные таблицы/Pivot Table)

marusa122

Дата: Понедельник, 27.02.2023, 15:50 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 17

Репутация: 0 ±

Замечаний: 20% ±

Здравствуйте!
Есть "умная таблица" в Экселе с заголовками и макросами.
Столбец А - "№ п/п". Заполняется автоматически формулой =СТРОКА()-1 при добавлении новой строки.
Столбец B - "Дата". Дата оформления заказа. Заполняется автоматически с помощью макроса, когда в соседней ячейке в столбце C появляется хоть какой-то текст. Формат ДД.ММ.ГГГГ
Столбец С - "Номер заказа". Пишется вручную.
Столбец D - "Сумма". Пишется вручную. Формат денежный, значение может быть разным (но всегда больше нуля), но может повторяться
Столбец E - "Доп. продажи". Пишется вручную. Формат денежный, значение может быть разными (или нулевым, или больше нуля), но может повторяться. В зависимости от того, была доп.продажа или нет, в ячейку ставится или сумма доп.продажи, или она остается пустой.
Столбец F - "Клиент". Пишется вручную. Есть несколько основных клиентов, заказов от них может быть несколько в один день.
Прикрепляю файл с примером, как это выглядит.
Я пытаюсь создать две сводные таблицы по этой основной - "Статистика" и "Дополнительные продажи" (схемы есть в примере на листе "Сводная таблица"). Сами таблицы (точнее, то, что у меня получилось) - на листах с соответствующими названиями.
"Статистика" - вроде бы получилось. Для того, чтобы она работала, нельзя будет в столбец E ставить 0 при отсутствии доп.продажи. Тогда таблица правильно считает количество заказов и количество заказов с доп.продажами.
"Дополнительные продажи" - тут вообще ничего не получается(( В ячейках должен отображаться процент заказов с дополнительными продажами для клиентов в выбранные даты. Например, если от Клиента1 в Дату1 было 10 заказов, и 7 из них с доп.продажами, то на пересечении строки "Дата1" и "Клиент1" должно отобразиться "70,00%".
Здесь для каждого клиента должен быть ОДИН столбец - Процент дополнительных продаж. Считаться он должен по каждому дню и в общем итоге. Например, в прикрепленном примере у Клиента1 20.02 процент дополнительных продаж будет 66,67% (3 заказа, 2 из них с доп. продажами), 21.02 - 100,00% (1 заказ, 1 из них с доп. продажами), 22.02 - 0,00% (нет заказов), 26.02 - 0,00% (1 заказ, 0 из них с доп. продажами). И Общий итог должен считаться так же, а не усреднять и не суммировать эти проценты. Общий итог должен быть 60,00% (5 заказов, 3 из них с доп. продажами). Как это реализовать?
Пробовала сделать вычисляемый столбец, в котором будет выражение "Доп.продажи/Сумма" и формат Процентный. Выдает странные цифры (скорее всего, считает и делит суммы, указанные в основной таблице, а не количество заказов).
Пробовала вставить функции СЧЁТ и СЧЁТЕСЛИ. В обоих случаях на этапе создания вычисляемого поля выдает ошибку "Ссылки, имена и массивы нельзя использовать в формулах сводных таблиц".

К сообщению приложен файл: kniga1.xlsm (29.8 Kb)

Сообщение отредактировал marusa122 - Понедельник, 27.02.2023, 15:57

Ответить

СообщениеЗдравствуйте!
Есть "умная таблица" в Экселе с заголовками и макросами.
Столбец А - "№ п/п". Заполняется автоматически формулой =СТРОКА()-1 при добавлении новой строки.
Столбец B - "Дата". Дата оформления заказа. Заполняется автоматически с помощью макроса, когда в соседней ячейке в столбце C появляется хоть какой-то текст. Формат ДД.ММ.ГГГГ
Столбец С - "Номер заказа". Пишется вручную.
Столбец D - "Сумма". Пишется вручную. Формат денежный, значение может быть разным (но всегда больше нуля), но может повторяться
Столбец E - "Доп. продажи". Пишется вручную. Формат денежный, значение может быть разными (или нулевым, или больше нуля), но может повторяться. В зависимости от того, была доп.продажа или нет, в ячейку ставится или сумма доп.продажи, или она остается пустой.
Столбец F - "Клиент". Пишется вручную. Есть несколько основных клиентов, заказов от них может быть несколько в один день.
Прикрепляю файл с примером, как это выглядит.
Я пытаюсь создать две сводные таблицы по этой основной - "Статистика" и "Дополнительные продажи" (схемы есть в примере на листе "Сводная таблица"). Сами таблицы (точнее, то, что у меня получилось) - на листах с соответствующими названиями.
"Статистика" - вроде бы получилось. Для того, чтобы она работала, нельзя будет в столбец E ставить 0 при отсутствии доп.продажи. Тогда таблица правильно считает количество заказов и количество заказов с доп.продажами.
"Дополнительные продажи" - тут вообще ничего не получается(( В ячейках должен отображаться процент заказов с дополнительными продажами для клиентов в выбранные даты. Например, если от Клиента1 в Дату1 было 10 заказов, и 7 из них с доп.продажами, то на пересечении строки "Дата1" и "Клиент1" должно отобразиться "70,00%".
Здесь для каждого клиента должен быть ОДИН столбец - Процент дополнительных продаж. Считаться он должен по каждому дню и в общем итоге. Например, в прикрепленном примере у Клиента1 20.02 процент дополнительных продаж будет 66,67% (3 заказа, 2 из них с доп. продажами), 21.02 - 100,00% (1 заказ, 1 из них с доп. продажами), 22.02 - 0,00% (нет заказов), 26.02 - 0,00% (1 заказ, 0 из них с доп. продажами). И Общий итог должен считаться так же, а не усреднять и не суммировать эти проценты. Общий итог должен быть 60,00% (5 заказов, 3 из них с доп. продажами). Как это реализовать?
Пробовала сделать вычисляемый столбец, в котором будет выражение "Доп.продажи/Сумма" и формат Процентный. Выдает странные цифры (скорее всего, считает и делит суммы, указанные в основной таблице, а не количество заказов).
Пробовала вставить функции СЧЁТ и СЧЁТЕСЛИ. В обоих случаях на этапе создания вычисляемого поля выдает ошибку "Ссылки, имена и массивы нельзя использовать в формулах сводных таблиц".

Автор - marusa122
Дата добавления - 27.02.2023 в 15:50

прохожий2019

Дата: Понедельник, 27.02.2023, 16:27 | Сообщение № 2

Группа: Проверенные

Ранг: Старожил

Сообщений: 1244

Репутация: 317 ±

Замечаний: 0% ±

365 Beta Channel

Цитата marusa122, 27.02.2023 в 15:50, в сообщении № 1 (

)

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

используйте меры: [vba]

Код

=DIVIDE(max(COUNTX('Таблица1';'Таблица1'[Доп.продажи]);0);max(COUNTX('Таблица1';'Таблица1'[Сумма]);0);0)

[/vba]
или вот так: [vba]

Код

=var m = COUNTX('Таблица1';'Таблица1'[Доп.продажи])
var n = COUNTX('Таблица1';'Таблица1'[Сумма])
var d = DIVIDE(m;n)
return if(ISBLANK(d);0;d)

[/vba]

К сообщению приложен файл: 2408201.xlsm (136.0 Kb)

Сообщение отредактировал прохожий2019 - Понедельник, 27.02.2023, 17:04

Ответить

elovkov

Дата: Понедельник, 27.02.2023, 16:28 | Сообщение № 3

Группа: Друзья

Ранг: Обитатель

Сообщений: 368

Репутация: 50 ±

Замечаний: 0% ±

Excel 2013

~~во второй двойной щелчок по ячейке "количество по полю" и в списке выбрать "сумма"~~
извините не дочитал до конца)))

Умное лицо это еще не признак ума. Все глупости на земле делаются именно с этим выражением лица

Сообщение отредактировал elovkov - Понедельник, 27.02.2023, 16:29

Ответить

marusa122

Дата: Понедельник, 27.02.2023, 17:10 | Сообщение № 4

Группа: Пользователи

Ранг: Новичок

Сообщений: 17

Репутация: 0 ±

Замечаний: 20% ±

прохожий2019, А можно пошагово для особо одаренных, как это делается?))

Ответить

прохожий2019

Дата: Понедельник, 27.02.2023, 17:41 | Сообщение № 5

Группа: Проверенные

Ранг: Старожил

Сообщений: 1244

Репутация: 317 ±

Замечаний: 0% ±

365 Beta Channel

При создании сводной поставить галку "добавить в модель данных", далее ПКМ по имени таблицы в списке полей - добавить меру, ну и написать эту меру...
Гуглите по ключевым словам: модель данных, меры, DAX

Ответить

marusa122

Дата: Понедельник, 27.02.2023, 17:45 | Сообщение № 6

Группа: Пользователи

Ранг: Новичок

Сообщений: 17

Репутация: 0 ±

Замечаний: 20% ±

прохожий2019, спасибо большое)) Я про эту возможность узнала впервые))

Ответить

Страница 1 из 1
1