Я пытаюсь сделать следующее. У меня естьЛист 1иЛист 2.
На третьем листе мне нужно получить строки на листе 1, где ИНСТРУМЕНТ листа 1 не найден в FIM листа 2 (например, поскольку ни один из ИНСТРУМЕНТОВ листа 1 не находится в FIM листа 2, лист 3 будет содержать строки листа 1). Как мне это сделать? Спасибо
решение1
Функции поиска, такие как , VLOOKUP()могут использоваться для поиска искомого значения и возврата некоторого результата или для того, чтобы не найти его и вернуть #ERROR!. Очевидно, что это обычно используется в «положительном» смысле, для поиска некоторого результата, но можно использовать и в «отрицательном» смысле, для поиска НЕ какого-то результата, а скорее #ERROR!.
Затем можно также проверить наличие #ERROR! с помощью функции ISERROR(). Иногда можно использовать, но во многих ситуациях механизм IFERROR()не подходит , и это одна из них. будет использоваться здесь как более сложный . Затем, если INSTRUMENT не найден, тест будет успешным, поскольку он ищет такую ошибку. Результат TRUE в этом случае — это значение ячейки поиска. Если тест не пройден (INSTRUMENT там есть), возвращается "".IFERROR()ISERROR()NOT()IF()
Это делается с помощью следующей формулы:
=IF(ISERROR(VLOOKUP(A2:A9,$F$2:$F$5,1,FALSE)),A2:A9,"")
Если у вас есть SPILLфункциональность, формула нужна только первой ячейке. Если нет, вставьте ее в первую ячейку и используйте {CSE}, чтобы выполнить старый метод создания массива.
Обратите внимание, что формула будет иметь результаты, разбросанные по всему выходному диапазону. Чтобы сгруппировать их, вам нужно отсортировать их так, чтобы пустые оказались в конце списка, чтобы они не мешали ни удобному использованию списка, ни просто его внешнему виду, в зависимости от того, что для вас важнее.
Однако, практически все, что вы используете для тех, которые существуют, как "" в формуле выше, даже гораздо более высокого символа, например, UNICODE(160)который может быть пробелом, все равно будет сортироваться в начало списка. Вы можете подойти к этому многими способами, включая копирование, затем вставку в качестве значений, затем сортировку, но чтобы сделать это с помощью формулы, чтобы вам не нужно было взаимодействовать, вы можете попробовать что-то вроде следующего:
=SUBSTITUTE(SORT(IF(ISERROR(VLOOKUP(A2:A9,$F$2:$F$5,1,FALSE)),A2:A9,"ZZZZZZZZZZ")),"ZZZZZZZZZZ","")
Вы используете строку символов, которая всегда должна сортироваться в конец вашего списка. В этой формуле используется "ZZZZZZZZZZ". Затем вы SORT()принудительно помещаете все эти элементы в конец, а те, которые вы хотите видеть первыми, вверху. Затем вы SUBSTITUTE()помещаете эту строку с пробелом, например "", и все эти "уходят"... Не совсем так, конечно, но так кажется.
(Очевидно, что вам необходимо использовать реальные источники в выбранной или адаптированной вами формуле.)