Подсчет Уникальных и Неповторяющихся ТЕКСТовых значений в заданном интервале в EXCEL

Произведем подсчет уникальных и неповторяющихся текстовых значений не во всем списке, а только в определенном пользователем диапазоне.

Пусть исходная таблица состоит из двух столбцов: Даты продаж и Проданный товар . Столбец с датами должен быть отсортирован по возрастанию (см. файл примера ).

Задача

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

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

В статьях Подсчет уникальных текстовых значений и Подсчет Неповторяющихся значений было показано, что такое уникальные и  неповторяющиеся значения и как их подсчитать во всем исходном списке. Отличие этой статьи в том, что пользователь может настроить диапазон подсчета: теперь можно подсчитать значения не во всем исходном списке, а в нужном диапазоне (границы которого легко настраиваются).

Решение

Границы интервала будут задаваться пользователем в ячейках Е6 и Е7 . Строки таблицы, которые попадают в заданный интервал выделены с помощью Условного форматирования серым цветом.

Сначала для удобства определим Именованный диапазон Весь_диапазон_Дат как ссылку на диапазон =$A$7:$A$41 (т.е. полностью на весь диапазон дат исходной таблицы).

Затем определим диапазон, в котором будем подсчитывать уникальные и неповторяющиеся значения. Для этого:

• Найдем верхнюю границу диапазона (номер позиции в исходном списке), воспользовавшись решением из статьи Ближайшее событие =ПОИСКПОЗ(МИН(ЕСЛИ(Весь_диапазон_Дат>=E6;Весь_диапазон_Дат;""));Весь_диапазон_Дат;0) Эту формулу массива помещаем в ячейку K6 .
• Найдем наибольшую дату, которая меньше или равна Конечной дате (ячейка E7 ). =ВПР(E7;Весь_диапазон_Дат;1;ИСТИНА) Результат помещаем в ячейку J7 .
• Найдем нижнюю границу диапазона (номер позиции в исходном списке) с помощью формулы =СУММПРОИЗВ(МАКС((J7=Весь_диапазон_Дат)*(СТРОКА(Весь_диапазон_Дат))))-СТРОКА($A$6) Результат помещаем в ячейку K7 .
• По определенным позициям границ диапазона (ячейки K6 и K7 ), формируем Динамический диапазон для товаров ( Текущий_диапазон_Товаров ): =СМЕЩ(лист1!$B$7;лист1!$K$6-1;;лист1!$K$7-лист1!$K$6+1))

Теперь найдем количество уникальных (различающихся) значений в определенном нами диапазоне. Результат поместим в ячейку H6 : =СУММПРОИЗВ((Текущий_Диапазон_Товаров<>"")/ СЧЁТЕСЛИ(Текущий_Диапазон_Товаров;Текущий_Диапазон_Товаров&""))

Напомним, что Уникальные значения соответствуют, в нашем случае, количеству проданных наименований Фруктов в указанный временной интервал.

Число неповторяющихся значений в определенном нами диапазоне можно найти с помощью нижеследующей формулы. Результат поместим в ячейку H7 : =СУММПРОИЗВ(--(СЧЁТЕСЛИ(Текущий_Диапазон_Товаров;Текущий_Диапазон_Товаров)=1))

Напомним, что неповторяющиеся значения соответствуют, в нашем случае, количеству Фруктов, проданных в указанный интервал, по 1 партии.

Тестируем

1. В ячейку Е6 введите начальную дату (верхняя граница временного интервала) - 02.01.2008

2. В ячейку Е7 введите конечную дату (нижняя граница временного интервала) - 10.01.2008

3. Новый временной интервал будет выделен серым цветом. Подсчет наименований товара будет производиться только в этом интервале.

4. После ввода границ диапазона, в ячейках H6 и Н7 будет подсчитано число неповторяющихся и уникальных наименований товара.

5. В зависимости от значения Переключателя , уникальные и неповторяющиеся значения в диапазоне подсчета будут выделены соответственно зеленым (см. рисунок выше) и синим цветом (см. рисунок ниже).

СОВЕТ: Подобный анализ данных можно также выполнить с помощью стандартного инструмента EXCEL - Сводных таблиц .