Упростите несколько COUNTIFS, используемых для поиска в Excel 2010


Введение:


У меня есть книга Excel, которую я использую, чтобы отслеживать статистику для игры Hearthstone. Один лист содержит данные каждой отдельной игры (выигрывает убытки и т. Д.). Другой лист позволяет пользователю искать статистику выигрыша / убытка на основе критериев поиска пользователя.


Мой вопрос:


В поисковом листе я использую формулы COUNTIFS. Эти формулы становятся довольно длинными. Есть ли способ упростить формулы COUNTIFS?


Настройка: как выглядит Excel Search Sheet:


| Column K | Column L | |____________________|______________________________| Row 5 |Date Start | User input goes in Column L | Row 6 |Date End | | Row 7 |Player's Class | | ... |Turn Number | | |Deck Name | | |Opponent's Class | | |Opponent's Username | | |Match Type 1 | | |Match Type 2 | | |Match Type 3 | | |Match Type 4 | | ... |Match Type 5 | | Row 17 |Match Type 6 | | |____________________|______________________________| | Column K | Column L | |______________________|_______________________________________________| Row 21 | Total Matches Played | Data is displayed based on the user's input. | Row 22 | Total Wins | The code that needs simplifying is goes here. | Row 23 | Total Losses | | Row 24 | Win to Loss Ratio | | Row 25 | Win Percentage | | Row 26 | Loss Percentage | | |______________________|_______________________________________________| 

Код, который нуждается в упрощении. Этот код приведен в строке 22 Столбец L :


 =(COUNTIFS('Indiv. Match Stats'!I:I,"Win",'Indiv. Match Stats'!H:H,L12, 'Indiv. Match Stats'!L:L,L7,'Indiv. Match Stats'!T:T,L9,'Indiv. Match Stats'!Q:Q,L10,'Indiv. Match Stats'!P:P,L11,'Indiv. Match Stats'!C:C, ">="&L5,'Indiv. Match Stats'!C:C,"<="&L6,'Indiv. Match Stats'!N:N,L8)) + (COUNTIFS('Indiv. Match Stats'!I:I,"Win",'Indiv. Match Stats'!H:H,L13, 'Indiv. Match Stats'!L:L,L7,'Indiv. Match Stats'!T:T,L9,'Indiv. Match Stats'!Q:Q,L10,'Indiv. Match Stats'!P:P,L11,'Indiv. Match Stats'!C:C, ">="&L5,'Indiv. Match Stats'!C:C,"<="&L6,'Indiv. Match Stats'!N:N,L8)) + (The code repeats the above four more times. Basically each block of code stands for one Match Type in Column K) 

Объяснение рабочего листа и кода:


Пользователь вводит критерии в строки с 5 по 17, столбец L. Любое оставленное пустое поле рассматривается как шаблон. Критерии ввода пользователя сужают результаты поиска и определяют данные, отображаемые в строках с 21 по 26, столбец L.

Код, показанный выше, ссылается на отдельный лист под названием Indiv. Match Stats Indiv. Match Stats много раз. COUNTIFS сужают поиск по дате, классу игрока, числу оборотов, имени колоды, … и типу соответствия. К сожалению, все эти критерии должны повторяться один раз для каждого типа соответствия, а затем код добавляет результаты, давая конечный результат (правильное количество выигрышей, потерь и т. Д. Для данных критериев). Это большой блок кода, который добавляется в другой блок кода.

Есть ли лучший способ сделать это или просто каким-то образом визуально просто использовать код? Есть ли способ сделать аналогичные блоки кода равными некоторой переменной, так что эти подобные части не обязательно должны быть напечатаны?

Вы можете эффективно использовать «ИЛИ» в COUNTIFS – если вы хотите подсчитать, если столбец H = любой из L12:L17 то используйте эту версию

=SUMPRODUCT(COUNTIFS('Indiv. Match Stats'!I:I,"Win",'Indiv. Match Stats'!H:H,L12:L17, 'Indiv. Match Stats'!L:L,L7,'Indiv. Match Stats'!T:T,L9,'Indiv. Match Stats'!Q:Q,L10,'Indiv. Match Stats'!P:P,L11,'Indiv. Match Stats'!C:C, ">="&L5,'Indiv. Match Stats'!C:C,"<="&L6,'Indiv. Match Stats'!N:N,L8))

COUNTIFS теперь возвращает массив из 6 значений (по одному для L12: L17), а затем SUMPRODUCT используется для суммирования этого массива, потому что он не требует «записи массива», поскольку SUM .

Примечание1: SUMPRODUCT просто SUMPRODUCT 6 значений, поэтому в этом контексте производительность «ударить» не может быть использована – весь «тяжелый подъем» выполняется COUNTIFS

Примечание2: Если какое-либо значение повторяется в L12:L17 тогда вы получите «двойной счет» так же, как и ваша исходная формула

Чтобы избежать двойного счета, используйте эту формулу – обратите внимание на дополнительную функцию COUNTIF в конце:

= SUMPRODUCT (COUNTIFS ('Indiv. Match Stats'! I: I, «Win», «Indiv. Match Stats»! H: H, L12: L17, «Indiv. Stats»! L: L, L7, 'Indiv Матч Статистика:! T: T, L9, 'Indiv. Статистика матчей'! Q: Q, L10, 'Indiv. Match Stats'! P: P, L11, 'Indiv. Match Stats'! C: C, "> = "& L5, 'Indiv. Статистика матча'! C: C," <= "& L6, 'Indiv. Статистика матча'! N: N, L8), 1 / COUNTIF (L12: L17, L12: L17 &" ") )

Мертвый простой подход к сокращению кода – это сокращение названия вкладки «Indiv. Match Stats», чтобы сказать IMS. Это значительно усугубляет ситуацию:

 =(COUNTIFS('IMS'!I:I,"Win",'IMS'!H:H,L12,'IMS'!L:L,L7,'IMS'!T:T,L9,'IVM'!Q:Q,L10,'IMS'!P:P,L11,'IMS'!C:C,">="&L5,'IMS'!C:C,"<="&L6,'IMS'!N:N,L8)) + (COUNTIFS('IMS'!I:I,"Win",'IMS'!H:H,L13,'IMS'!L:L,L7,'IMS'!T:T,L9,'IMS'!Q:Q,L10,'IMS'!P:P,L11,'IMS'!C:C,">="&L5,'IMS'!C:C,"<="&L6,'IMS'!N:N,L8)) 

Еще один более красивый способ сделать это – использовать диапазоны с именем Excel. Выделите каждый диапазон, например «Indiv. Статистика матча '! I: Я и нажмите в поле «Имя», слева от строки формул, введите имя для списка, такого как IMSI. Повторите с «Indiv. Статистика матча '! N: N -> IMSN и так далее.
Это даст вам такой код:

 =(COUNTIFS(IMSI,"Win",IMSH,L12,IMSL,L7,IMST,L9,IVMQ,L10,IMSP,L11,IMSC,">="&L5,IMSC,"<="&L6,IMSN,L8)) + (COUNTIFS(IMSI,"Win",IMSH,L13,IMSL,L7,IMST,L9,IMSQ,L10,IMSP,L11,IMSC,">="&L5,IMSC,"<="&L6,IMSN,L8)) 

Я отправляю этот ответ не как мое предложение, а чтобы показать, в чем проблема. Проблема в том, что в COUNTIFS нет функциональных возможностей OR для сокращения. Таким образом, вы не можете сказать COUNTIFS('Indiv. Match Stats'!H:H;L12 OR L13 OR L14...) .

Существует возможность получить формулу короче с формулой массива с SUMPRODUCT. Это будет работать, потому что есть возможность выполнять OR shortcuts путем суммирования логических результатов, так что сумма равна 1, если истинно только одно логическое значение. Формула будет:

 =SUMPRODUCT( ('Indiv. Match Stats'!I:I="win") *( ('Indiv. Match Stats'!H:H=L12)+('Indiv. Match Stats'!H:H=L13) +('Indiv. Match Stats'!H:H=L14)+('Indiv. Match Stats'!H:H=L15) +('Indiv. Match Stats'!H:H=L16)+('Indiv. Match Stats'!H:H=L17) ) *('Indiv. Match Stats'!L:L=L7) *('Indiv. Match Stats'!T:T=L9) *('Indiv. Match Stats'!Q:Q=L10) *('Indiv. Match Stats'!P:P=L11) *('Indiv. Match Stats'!C:C>=L5) *('Indiv. Match Stats'!C:C<=L6) *('Indiv. Match Stats'!N:N=L8) ) 

Но это будет очень плохой. Такие формулы массива очень медленные, особенно для целых столбцов.

Поэтому предложения BKays являются лучшими и, на мой взгляд.

Приветствую

Axel

  • Форматирование выделенных ячеек - удалить все, кроме цифр
  • Вычисление месячного числа, зная разницу между двумя днями как число
  • Как исправить флаги activeX, перемещающиеся при открытии файла
  • как разбить один столбец на две колонки на условиях в EXCEL
  • Excel, Target Column, Target Offset, можно извлечь из другого листа?
  • Создание ссылок в порядке возрастания для Excel
  • Логика для оператора IF
  • как я могу сравнить два значения и ранжировать их в excel
  • Автоматизация Excel 2010 с использованием F #
  • Проверьте дневной диапазон и нет
  • Петля ошибочно вставляет столбцы с данными
  • Давайте будем гением компьютера.