☰

Excel: подсчет количества уникальных/различимых строк в диапазоне с условием

tag-icon • tag-icon microsoft-excel
Excel: подсчет количества уникальных/различимых строк в диапазоне с условием

У меня есть таблица Excel с:

  • в столбце A: номера недель
  • в столбце B: даты (записи табеля учета рабочего времени)

Мне нужно знать количество отработанных дней на каждой неделе. Поэтому мне нужно количество уникальных записей дат на номер недели.

Я нашел формулы (как массивы, так и не массивы), которые обрабатывают это для фиксированного диапазона, но я хочу, чтобы результаты были в другом столбце (по номеру недели).

Результатом примера набора данных ниже будет (двоеточие указано только для ясности):

14: 2 
15: 3 
17: 6 
20: 2 
21: 3

Если это исходные данные:

14: 4/04/2012
14: 4/04/2012
15: 10/04/2012
15: 10/04/2012
15: 11/04/2012
17: 26/04/2012
17: 26/04/2012
17: 26/04/2012
17: 26/04/2012
17: 27/04/2012
17: 27/04/2012
20: 14/05/2012
20: 14/05/2012
21: 23/05/2012
21: 23/05/2012
21: 25/05/2012

решение1

Чтобы подсчитать количество записей, используйте =countif(A:B,D1), предположив, что номер недели находится в ячейке D1, а список записей — в столбцах A и B.

Другой вариант — создать сводную таблицу с номерами недель в качестве меток строк и количеством записей в качестве данных. Это даст хорошую сводку, которую можно быстро обновлять.

решение2

Можно обойтись полностью формулами. Для этого нужна небольшая косвенная адресация и один (но для ясности я сделаю два) отдельных рабочих столбца вдоль исходных данных, а также три дополнительных столбца в таблице результатов:

Я предположу, что фактические данные начинаются в строке 3, чтобы учесть некоторые заголовки. Я буду использовать ;для разделения аргументов, что не является значением по умолчанию для локали США.Я не буду предполагать, что даты отсортированы.. При таком предположении решение будет проще.

  1. Ячейка H2 (сколько строк во входных данных):=COUNT(A3:A1048576)
  2. Ячейка C3 (динамический диапазон поиска): ничего
  3. Ячейки C4:C1000:=ADDRESS(ROW(A$3);COLUMN(A$3)) & ":" & ADDRESS(ROW(A3);COLUMN(A3))
  4. Ячейка D3 (уникальна):TRUE
  5. Ячейка D4:D1000:=COUNTIF(INDIRECT(C4);A4)=0
  6. Ячейка E3 (номер уникальной записи):1
  7. Ячейка E4:E1000:=IF(D4;E3+1;E3)
  8. Ячейка I2 (Сколько найдено уникальных):=OFFSET(E3;H2-1;0)
  9. Ячейка J2 (диапазон дней недели):=ADDRESS(ROW(A3);COLUMN(A3);4) & ":" & ADDRESS(ROW(A3)-1+$H$2;COLUMN(A3);4)
  10. Ячейка K2 (номер уникального диапазона дней недели):=ADDRESS(ROW(E3);COLUMN(E3);4) & ":" & ADDRESS(ROW(E3)-1+$H$2;COLUMN(E3);4)
  11. Ячейка H5 (счетчик):1
  12. Ячейка H6:H100=H5+1
  13. Ячейка I5:I100 (позиция):=MATCH(H5;INDIRECT($K$2);0)
  14. Ячейка J5:J100 (будний день):=OFFSET($A$3;I5-1;0)
  15. Ячейка K5:K100 (Количество):=COUNTIF(INDIRECT($J$2);J5)

Конечный результат находится в диапазоне К5:К100.

Обратите внимание, что хотя я и работаю с косвенными формулами, решение будет работать, если вы вставите столбец в любом месте или удалите столбец F:F из G:G. Вы также можете перемещать ячейки, при условии, что вы сохраняете столбцы с данными вместе.

Важно хранить все на одном листе. Если вы настаиваете на перемещении таблицы H4:K100 на другой лист, вам следует изменить адреса в ячейках J2 и K2, включив в них имя листа.

введите описание изображения здесь

решение3

Сводная таблица может быть самым простым вариантом

Пример

Связанный контент