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

Question

Есть "умная таблица" в Экселе с заголовками и макросами.
Столбец А - "№ п/п". Заполняется автоматически формулой =СТРОКА()-1 при добавлении новой строки.

Столбец B - "Дата". Дата оформления заказа. Заполняется автоматически с помощью макроса, когда в соседней ячейке в столбце C появляется хоть какой-то текст. Формат ДД.ММ.ГГГГ

Столбец С - "Номер заказа". Пишется вручную.

Столбец D - "Сумма (общая)". Пишется вручную. Формат денежный, значение может быть разным (но всегда больше нуля), но может повторяться

Столбец E - "Сумма (доп.продажи)". Пишется вручную. Формат денежный, значение может быть разными (или нулевым, или больше нуля), но может повторяться. В зависимости от того, была доп.продажа или нет, в ячейку ставится или сумма доп.продажи, или 0 соответственно.

Столбец F - "Клиент". Пишется вручную. Есть несколько основных клиентов, заказов от них может быть несколько в один день.

В таблице очень много строк, пример того, как это выглядит -

https://1drv.ms/x/s!Aqw2y54zadyx-WC0WfEzW4MvTrks?e=jhXqd7 (лист "Осн. таблица").

Я пытаюсь создать две сводные таблицы по этой основной - "Статистика" и "Дополнительные продажи" (схемы есть в примере на листе "Сводная таблица"). Сами таблицы (точнее, то, что у меня получилось) - на листах с соответствующими названиями.
"Статистика" - не получается сделать так, чтобы в количестве заказов с доп.продажами НЕ учитывались заказы с нулевым значением доп.продаж. Но сводная таблица считает и их тоже. С помощью функции СЧЕТЕСЛИ в обычных ячейках можно задать условие ">0", но как это сделать в ячейках сводной таблицы?..

"Дополнительные продажи" - тут вообще ничего не получается(( В ячейках должен отображаться процент заказов с дополнительными продажами для клиентов в выбранные даты. Например, если от Клиента1 в Дату1 было 10 заказов, и 7 из них с доп.продажами, то на пересечении строки "Дата1" и "Клиент1" должно отобразиться "70,00%". Но сводная таблица считает или только общее количество заказов, или суммы от продаж.
Вопрос: как сделать эти сводные таблицы работающими? И что я делаю не так?

Answer 1

Привет Мария А!

К сожалению, у меня нет никаких дальнейших действий по устранению неполадок.

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

С наилучшими пожеланиями Шакиру

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

Answer 2

Привет Мария А!

К сожалению, у меня нет никаких дальнейших действий по устранению неполадок.

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

С наилучшими пожеланиями Шакиру

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

Answer 3

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

Здесь для каждого клиента должен быть ОДИН столбец - Процент дополнительных продаж. Считаться он должен по каждому дню и в общем итоге. Например, у Клиента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 из них с доп. продажами). Как это реализовать?

Answer 4

* В поле "Формула" введите следующую формулу: =COUNTIF(Дополнительные продажи, ">0")

(Примечание. "Дополнительные продажи" должно быть именем поля, содержащего дополнительные значения продаж в источнике данных.)

* Нажмите кнопку "Добавить", чтобы добавить вычисляемое поле в сводную таблицу.

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

Answer 5

Привет Мария А!

Благодарим вас за письмо на форум сообщества Microsoft Answer. Я Шакиру, независимый советник и такой же пользователь, как и вы, и я рад помочь вам сегодня.

Чтобы исключить заказы с нулевым значением дополнительных продаж из счетчика в сводной таблице "Статистика", можно создать вычисляемое поле в сводной таблице, использующее функцию СЧЁТЕСЛИм с условием ">0".

Пожалуйста, попробуйте это: * Выберите любую ячейку в сводной таблице "Статистика". * На панели «Поля сводной таблицы» щелкните «Значения», чтобы развернуть раздел «Значения». * Щелкните правой кнопкой мыши поле значения, которое вы хотите изменить (например, «Количество заказов»), и выберите «Добавить вычисляемое поле». * В диалоговом окне «Вставить вычисляемое поле» введите имя нового поля (например, «Количество заказов с дополнительными продажами»). * В поле "Формула" введите следующую формулу: =COUNTIF(Дополнительные продажи, ">0") (Примечание. "Дополнительные продажи" должно быть именем поля, содержащего дополнительные значения продаж в источнике данных.) * Нажмите кнопку "Добавить", чтобы добавить вычисляемое поле в сводную таблицу.

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

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

Вот как это сделать: * Выберите любую ячейку в сводной таблице "Дополнительные продажи". * На панели «Поля сводной таблицы» щелкните «Значения», чтобы развернуть раздел «Значения». * Щелкните правой кнопкой мыши на любом поле значения и выберите «Добавить вычисляемое поле». В диалоговом окне "Вставка вычисляемого поля" введите имя нового поля (например, "Процент заказов с дополнительными продажами"). * В поле "Формула" введите следующую формулу: =COUNTIFS(Дополнительные продажи, ">0", Клиент, [Клиент], Дата, [Дата])/СУММЕСЛИМН(Количество заказов, Клиент, [Клиент], Дата, [Дата]) (Примечание: «Дополнительные продажи», «Клиент», «Дата» и «Количество заказов» должны быть названиями полей в источнике данных. Замените [Клиент] и [Дата] соответствующими именами полей в сводной таблице, например "Метки строк" или "Подписи столбцов".) * Нажмите кнопку "Добавить", чтобы добавить вычисляемое поле в сводную таблицу.

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

С наилучшими пожеланиями Шакиру

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