Подсчет отсутствующих значений
Предположим, у вас есть два списка. Чтобы подсчитать общее количество значений в одном списке, которых нет в другом списке, вы можете использовать формулу СУММПРОИЗВ с помощью функций ПОИСКПОЗ и ЕНД, либо функцию СЧЁТЕСЛИ .
Подсчет отсутствующих значений с помощью СУММПРОИЗВ, ПОИСКПОЗ и ЕНД
Подсчет отсутствующих значений с помощью СУММПРОИЗВ и СЧЁТЕСЛИ
Подсчет отсутствующих значений с помощью СУММПРОИЗВ, ПОИСКПОЗ и ЕНД
Чтобы подсчитать общее количество значений в списке B, которые отсутствуют в списке A, как показано выше, вы можете сначала использовать функцию ПОИСКПОЗ для возврата массива относительных позиций значений из списка B в списке A. Если значение не существует в списке A, будет возвращена ошибка #Н/Д. Затем функция ЕНД определит ошибки #Н/Д, а СУММПРОИЗВ подсчитает общее количество этих ошибок.
Общий синтаксис
=СУММПРОИЗВ(--ЕНД(ПОИСКПОЗ(диапазон_для_подсчета;диапазон_поиска;0)))
- диапазон_для_подсчета: Диапазон, из которого нужно подсчитывать отсутствующие значения. Здесь это список B.
- диапазон_поиска: Диапазон для сравнения с диапазоном_для_подсчета. Здесь это список A.
- 0: Тип_совпадения 0 заставляет функцию ПОИСКПОЗ выполнять точное совпадение.
Чтобы подсчитать общее количество значений в списке B, которые отсутствуют в списке A, скопируйте или введите формулу ниже в ячейку H6 и нажмите Enter, чтобы получить результат:
=СУММПРОИЗВ(--ЕНД(ПОИСКПОЗ(F6:F8;B6:B10;0)))
Объяснение формулы
=СУММПРОИЗВ(--ЕНД(ПОИСКПОЗ(F6:F8;B6:B10;0)))
- ПОИСКПОЗ(F6:F8;B6:B10;0): Тип_совпадения 0 заставляет функцию ПОИСКПОЗ возвращать числовые значения, указывающие относительные позиции значений через ячейки F6 до F8 в диапазоне B6:B10. Если значение не существует в списке A, будет возвращена ошибка #Н/Д. Таким образом, результаты будут в виде массива, например: {2;3;#Н/Д}.
- ЕНД(ПОИСКПОЗ(F6:F8;B6:B10;0)) = ЕНД({2;3;#Н/Д}): Функция ЕНД проверяет, является ли значение ошибкой «#Н/Д» или нет. Если да, функция вернет ИСТИНА; если нет, она вернет ЛОЖЬ. Таким образом, формула ЕНД вернет {ЛОЖЬ;ЛОЖЬ;ИСТИНА}.
- СУММПРОИЗВ(--ЕНД(ПОИСКПОЗ(F6:F8;B6:B10;0))) = СУММПРОИЗВ(--{ЛОЖЬ;ЛОЖЬ;ИСТИНА}): Двойной знак минус преобразует ИСТИНЫ в 1, а ЛОЖИ в 0: {0;1;0}. Затем функция СУММПРОИЗВ возвращает сумму: 1.
Подсчет отсутствующих значений с помощью СУММПРОИЗВ и СЧЁТЕСЛИ
Чтобы подсчитать общее количество значений в списке B, которые отсутствуют в списке A, вы также можете использовать функцию СЧЁТЕСЛИ, чтобы определить, существует ли значение в списке A или нет, с условием «=0», поскольку 0 будет сгенерирован, если значение отсутствует. Затем функция СУММПРОИЗВ подсчитает общее количество отсутствующих значений.
Общий синтаксис
=СУММПРОИЗВ(--(СЧЁТЕСЛИ(диапазон_поиска;диапазон_для_подсчета)=0))
- диапазон_поиска: Диапазон для сравнения с диапазоном_для_подсчета. Здесь это список A.
- диапазон_для_подсчета: Диапазон, из которого нужно подсчитывать отсутствующие значения. Здесь это список B.
- 0: Тип_совпадения 0 заставляет функцию ПОИСКПОЗ выполнять точное совпадение.
Чтобы подсчитать общее количество значений в списке B, которые отсутствуют в списке A, скопируйте или введите формулу ниже в ячейку H6 и нажмите Enter, чтобы получить результат:
=СУММПРОИЗВ(--(СЧЁТЕСЛИ(B6:B10;F6:F8)=0))
Объяснение формулы
=СУММПРОИЗВ(--(СЧЁТЕСЛИ(B6:B10;F6:F8)=0))
- СЧЁТЕСЛИ(B6:B10;F6:F8): Функция СЧЁТЕСЛИ подсчитывает количество появлений ячеек через F6 до F8 в диапазоне B6:B10. Результат будет в виде массива, например: {1;1;0}.
- --(--(СЧЁТЕСЛИ(B6:B10;F6:F8)=0)=0) = --(--({1;1;0}=0)=0): Фрагмент {1;1;0}=0 дает массив ИСТИНА и ЛОЖЬ {ЛОЖЬ;ЛОЖЬ;ИСТИНА}. Двойной знак минус затем превращает ИСТИНЫ в 1, а ЛОЖИ в 0. Окончательный массив выглядит так: {0;0;1}.
- СУММПРОИЗВ(--(СЧЁТЕСЛИ(B6:B10;F6:F8)=0)) = СУММПРОИЗВ({0;0;1}): Затем функция СУММПРОИЗВ возвращает сумму: 1.
Связанные функции
В Excel функция СУММПРОИЗВ может использоваться для умножения двух или более столбцов или массивов вместе, а затем получения суммы произведений. На самом деле, СУММПРОИЗВ — это полезная функция, которая помогает подсчитывать или суммировать значения ячеек по нескольким критериям, как функции СЧЁТЕСЛИМН или СУММЕСЛИМН. Эта статья представит синтаксис функции и некоторые примеры для этой функции СУММПРОИЗВ.
Функция ПОИСКПОЗ в Excel ищет определенное значение в диапазоне ячеек и возвращает относительную позицию этого значения.
Функция СЧЁТЕСЛИ — это статистическая функция в Excel, которая используется для подсчета количества ячеек, соответствующих определенному критерию. Она поддерживает логические операторы (<>, =, >, и <), а также подстановочные знаки (? и *) для частичного совпадения.
Связанные формулы
Бывают случаи, когда вам нужно сравнить два списка, чтобы проверить, существует ли значение из списка A в списке B в Excel. Например, у вас есть список продуктов, и вы хотите проверить, существуют ли продукты из вашего списка в списке продуктов, предоставленном вашим поставщиком. Для выполнения этой задачи мы перечислили три способа ниже, выберите тот, который вам больше нравится.
Подсчет ячеек, равных заданному значению
Эта статья будет сосредоточена на формулах Excel для подсчета ячеек, которые точно равны указанной текстовой строке или частично равны данной текстовой строке, как показано на скриншотах ниже. Во-первых, она объяснит синтаксис формулы и аргументы, а затем предоставит примеры для лучшего понимания.
Подсчет количества ячеек, не находящихся между двумя заданными числами
Подсчет количества ячеек между двумя числами — это обычная задача для нас в Excel, но в некоторых случаях вам может потребоваться подсчитать ячейки, не находящиеся между двумя заданными числами. Например, у меня есть список продуктов с продажами с понедельника по воскресенье, теперь мне нужно получить количество ячеек, которые не находятся между определенными нижним и верхним числами, как показано на скриншоте ниже. В этой статье я представлю несколько формул для решения этой задачи в Excel.
Лучшие инструменты для повышения производительности Office
Kutools для Excel - Помогает вам выделиться из толпы
| 🤖 | KUTOOLS AI Помощник: Революционизируйте анализ данных на основе: Интеллектуальное выполнение | Генерация кода | Создание пользовательских формул | Анализ данных и создание диаграмм | Вызов функций Kutools… |
| Популярные функции: Поиск, Выделение или Отметка дубликатов | Удалить пустые строки | Объединить столбцы или ячейки без потери данных | Округлить без формулы ... | |
| Супер VLookup: Множественные критерии | Множественные значения | На нескольких листах | Распознавание нечетких соответствий... | |
| Расширенный раскрывающийся список: Простой раскрывающийся список | Зависимый раскрывающийся список | Раскрывающийся список с множественным выбором... | |
| Управление столбцами: Добавить определенное количество столбцов | Переместить столбцы | Переключить статус видимости скрытых столбцов | Сравнить столбцы для выбора одинаковых и разных ячеек ... | |
| Основные функции: Сетка фокусировки | Дизайн листа | Улучшенная строка формул | Управление книгами и листами | Библиотека автотекста (Авто текст) | Выбор даты | Объединить данные | Шифрование/Расшифровка ячеек | Отправка писем по списку | Супер фильтр | Специальный фильтр (фильтр жирного/курсивного/зачеркнутого текста...) ... | |
| Топ-15 наборов инструментов: 12 инструментов для работы с текстом (Добавить текст, Удалить определенные символы ...) | 50+ типов диаграмм (Диаграмма Ганта ...) | 40+ практических формул (Расчет возраста на основе даты рождения ...) | 19 инструментов вставки (Вставить QR-код, Вставить изображение по пути ...) | 12 инструментов преобразования (Преобразовать в слова, Конвертация валюты ...) | 7 инструментов объединения и разделения (Расширенное объединение строк, Разделить ячейки Excel ...) | ... и многое другое |
Kutools для Excel имеет более 300 функций, гарантируя, что то, что вам нужно, находится всего в одном клике...
Office Tab - Включите работу с вкладками в Microsoft Office (включая Excel)
- Один щелчок, чтобы переключаться между десятками открытых документов!
- Сократите сотни кликов мышью каждый день, попрощайтесь с болью в руке от мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.