Я надеюсь, что смогу получить некоторую помощь в улучшении подхода, который я использую в настоящее время для суммирования и подсчета строк данных на основе фильтров, которые могут выбирать мои пользователи.
В настоящее время у меня есть 7 фильтров, которые я проверяю, чтобы увидеть, должен ли я включить строку в расчет. Мы хотим добавить дополнительные фильтры, но я не сталкиваюсь с максимальным вложенным ifs. Я также обеспокоен тем, что в моей формуле могут быть ошибки из-за ее сложности.
Таким образом, каждая строка данных имеет характеристики (данные о продажах), которые я проверяю, чтобы увидеть, должен ли я включить эту строку в расчет. Таким образом, ряд данных будет иметь следующие типы характеристик, по которым я фильтрую:
Тип сделки (все, новый, продление или добавление), Регион (все, восток, запад, центр), Конкурент (все, затем имена конкретных конкурентов), Фильтр даты (даты начала и окончания) и Размер сделки (начальный размер и конечный размер).
Затем мы определили, почему мы выиграли или проиграли сделку по ряду характеристик. Мы указываем, была ли характеристика причиной исхода сделки с использованием "1" в ячейке для потерь и "0" для выигрышей. Таким образом, у меня есть колонки под названием Цена, предложение, презентация, ссылки на компании и т.д.
Если мы потеряли сделку и считаем, что потеряли сделку из-за цены и ссылок, у меня будет 1 в каждом из этих столбцов для этой строки данных.
Затем у меня есть сводный раздел, в котором я пытаюсь выяснить, сколько сделок мы потеряли из-за "цены" (я считаю число 1, которые также соответствуют критериям фильтра) и сколько дохода мы потеряли из-за цены (та же формула , но я суммирую размер сделки вместо подсчета сделок).
Надеюсь, это даст вам хорошее представление о том, с чем я работаю. Я хотел бы иметь возможность улучшить формулы, чтобы я мог добавлять дополнительные фильтры по мере необходимости, а также упростить его, если это возможно, чтобы упростить его обслуживание.
Формула, которую я сейчас использую для подсчета потерь по определенной причине, выглядит следующим образом (в основном это та же формула для выигрышей, но я считаю нули вместо единиц):
=IF(AND($CU$1="ALL", $CR$2="ALL", $CU$2="ALL"),COUNTIFS($Y$7:$Y$1000,"=1",$A$7:$A$1000,">="&$CN$1,$A$7:$A$1000,"<="&$CP$1,$J$7:$J$1000,">="&$CN$2,$J$7:$J$1000,"<="&$CP$2),
IF(AND($CU$1="ALL", $CR$2="ALL", NOT($CU$2="ALL")),COUNTIFS($Y$7:$Y$1000,"=1",$A$7:$A$1000,">="&$CN$1,$A$7:$A$1000,"<="&$CP$1,$J$7:$J$1000,">="&$CN$2,$J$7:$J$1000,"<="&$CP$2,$D$7:$D$1000,"="&$CU$2),
IF(AND($CU$1="ALL", NOT($CR$2="ALL"), $CU$2="ALL"),COUNTIFS($Y$7:$Y$1000,"=1",$A$7:$A$1000,">="&$CN$1,$A$7:$A$1000,"<="&$CP$1,$J$7:$J$1000,">="&$CN$2,$J$7:$J$1000,"<="&$CP$2,$K$7:$K$1000,"="&$CR$2),
IF(AND(NOT($CU$1="ALL"), $CR$2="ALL", $CU$2="ALL"),COUNTIFS($Y$7:$Y$1000,"=1",$A$7:$A$1000,">="&$CN$1,$A$7:$A$1000,"<="&$CP$1,$J$7:$J$1000,">="&$CN$2,$J$7:$J$1000,"<="&$CP$2,$M$7:$M$1000,"="&$CU$1),
IF(AND(NOT($CU$1="ALL"), NOT($CR$2="ALL"), $CU$2="ALL"),COUNTIFS($Y$7:$Y$1000,"=1",$A$7:$A$1000,">="&$CN$1,$A$7:$A$1000,"<="&$CP$1,$J$7:$J$1000,">="&$CN$2,$J$7:$J$1000,"<="&$CP$2,$M$7:$M$1000,"="&$CU$1,$K$7:$K$1000,"="&$CR$2),
IF(AND(NOT($CU$1="ALL"), NOT($CR$2="ALL"), NOT($CU$2="ALL")),COUNTIFS($Y$7:$Y$1000,"=1",$A$7:$A$1000,">="&$CN$1,$A$7:$A$1000,"<="&$CP$1,$J$7:$J$1000,">="&$CN$2,$J$7:$J$1000,"<="&$CP$2,$M$7:$M$1000,"="&$CU$1,$K$7:$K$1000,"="&$CR$2,$D$7:$D$1000,"="&$CU$2),
IF(AND($CU$1="ALL", NOT($CR$2="ALL"), NOT($CU$2="ALL")),COUNTIFS($Y$7:$Y$1000,"=1",$A$7:$A$1000,">="&$CN$1,$A$7:$A$1000,"<="&$CP$1,$J$7:$J$1000,">="&$CN$2,$J$7:$J$1000,"<="&$CP$2,$D$7:$D$1000,"="&$CU$2,$K$7:$K$1000,"="&$CR$2),
COUNTIFS($Y$7:$Y$1000,"=1",$A$7:$A$1000,">="&$CN$1,$A$7:$A$1000,"<="&$CP$1,$J$7:$J$1000,">="&$CN$2,$J$7:$J$1000,"<="&$CP$2,$M$7:$M$1000,"="&$CU$1,$D$7:$D$1000,"="&$CU$2))))))))
Таким образом, для каждой причины результата (цена, предложение, рекомендации и т.д.) Я должен продублировать эту формулу четыре раза. Один раз для подсчета потерь, один раз для суммирования потерь, один раз для подсчета выигрышей и один раз для суммирования побед.
В результате я получаю таблицу, которая выглядит следующим образом:
Decision Driver | Win Frequency | Won Revenue | Loss Frequency | Lost Revenue ----------------------------------------------------------------------------- Price | 23 | $230,000 | 12 | $165,000 Proposal | 12 | $140,000 | 16 | $195,000 etc...
Любая помощь приветствуется.