| =ЕСЛИ(СЧЁТЕСЛИ($C$5:$C$10;D5)=0;СТРОКА(D5);»есть в списке 1″) |
| =ИНДЕКС($D$5:$D$10;НАИМЕНЬШИЙ($E$5:$E$10;G5)-4) |
Задача: Найти недостающие записи при сравнении двух списков.
Для решения задачи будут использоваться функции:
ЕСЛИ(), СЧЕТЕСЛИ(), СТРОКА(), ИНДЕКС() и НАИМЕНЬШИЙ()
Логика решения:
В первую очередь определяем количество не совпадений в столбцах C и D используя функцию СЧЕТЕСЛИ() все что не найдено будет с значением 0.
=СЧЁТЕСЛИ($C$5:$C$10;D5
все не найденные позиции как раз нам и нужны
Второе действие надо определить строку отсутствующей (то-есть с 0) записи в списке
Для этого используем логическую функцию ЕСЛИ() и функцию СТРОКА()
Логическое выражение
СЧЁТЕСЛИ($C$5:$C$10;D5)=0
Нам надо найти все 0 и проставить номера строк не найденных элементов из списка, при истинности логического выражения мы задаем действие которое определяет строку не найденного элемента. При истине ссылка на ячейку с функцией СТРОКА(D7) .
=СТРОКА(D7)
да и если ЛОЖЬ тогда пишем «есть в списке 1»
Полностью формула выглядит так
=ЕСЛИ(СЧЁТЕСЛИ($C$5:$C$10;D5)=0;СТРОКА(D5);»есть в списке 1″)
Для формирования нужного нам списка отсутствующих элементов мы будем использовать замечательные функции НАИМЕНЬШИЙ() и
ИНДЕКС()
Так как у нас есть номера строк или цифры в столбике E то мы можем использовать диапазон $E$5:$E$10 таблички или списка найти номер строки наименьшего числа с индексу из столбца G (номер строки определяется от первой строки)
G5- это номер индекса который нам надо вывести то есть от первого до последнего нужного элемента 1 до 10.
(таким образом можно создавать ТОП продаж или ещё что то интересное)
да и надо обязательна вычесть количество строк (4) до первой строки списка для правильного подсчета.
НАИМЕНЬШИЙ($E$5:$E$10;G5)-4
После определения строки нужной нам позиции мы используем функцию ИНДЕКС() для вывода не номера строки а наименование не достающего элемента.
Указываем диапазон $D$5:$D$10 для поиска и номер строки НАИМЕНЬШИЙ($E$5:$E$10;G5)-4 элемента для формирования нового списка недостающих элементов.
Полностью формула выглядит так:
=ИНДЕКС($D$5:$D$10;НАИМЕНЬШИЙ($E$5:$E$10;G5)-4)
Вы можете скачать файл с готовым решением.