Появилась у меня такая задачка, которую уже какое-то время хотелось автоматизировать, чтобы глаза не портить и не тратить на анализ много времени, да и исключить человеческий фактор не помешает.
ЗАДАЧА
Есть 2 массива с данными. Надо проверить, что данные в первом массиве есть во втором массиве и так же провести встречную проверку (то есть данные со 2го массива проверить на наличие в 1ом массиве). В принципе, делается просто, но мне хотелось сделать это все в 1 ячейке и потом еще и получить информацию каких именно данных не хватает.
РЕШЕНИЕ
Итак, у нас есть 2 массива данных - столбец А и столбец В, которые содержат один тип информации (в моем случае это был некий номер, состоящий, скажем, из 10 чисел).
По идее, я могла бы пойти простым путем и сделать несколько столбцов с проверкой данных, но мне ОЧЕНЬ сильно хотелось уменьшить площадь для решения своей задачи и для проверки добавить только один столбец!
Добавляем столбец С, в котором и будет происходить магия. Дальше показываю порядок, в котором я разрабатывала формулу (это мой путь, вы наверняка сможете быстрее и проще создать).
1. Создаем формулу для проверки данных из 1го массива (столбец А) во 2ом массиве (столбец В):
=ЕЧИСЛО(ПОИСКПОЗ(A2;$B$2:$B$10;0))
ПОИСКПОЗ - позволяет найти данные из ячейки А2 (переменный адрес) в массиве В2:В10 (постоянный адрес).
ЕЧИСЛО - позволяет определить содержит ли ячейка числовое значение и возвращает либо ИСТИНА либо ЛОЖЬ.
2. Аналогично создаем формулу для зеркальной проверки:
=ЕЧИСЛО(ПОИСКПОЗ(B2;$A$2:$A$10;0))
3. Ищем способ соединить эти 2 формулы. В этой ситуации нам поможет функция И. Вот так будет выглядеть формула:
=И(ЕЧИСЛО(ПОИСКПОЗ(A2;$B$2:$B$10;0));ЕЧИСЛО(ПОИСКПОЗ(B2;$A$2:$A$10;0)))
И - позволяет проверить, все ли условия принимают значение ИСТИНА и возвращает либо ИСТИНА либо ЛОЖЬ.
4. А теперь самое интересное! Если мы получили ИСТИНУ, то у нас все хорошо. А если ЛОЖЬ? Как понять какая из 2 проверок дала сбой?
Здесь мне очень помог такой прием программирования как создание схемы действия алгоритма в виде блок-схемы.
И в результате я получила вот такую формулу:
=ЕСЛИ(И(ЕЧИСЛО(ПОИСКПОЗ(A2;$B$2:$B$10;0));ЕЧИСЛО(ПОИСКПОЗ(B2;$A$2:$A$10;0)))=ИСТИНА;"ИСТИНА";ЕСЛИ(ЕЧИСЛО(ПОИСКПОЗ(A2;$B$2:$B$10;0))=ИСТИНА;B2;A2))
1 путь. Если результат проверки п.3 будет ИСТИНА, то на этом расчет и закончится (мы увидим в ячейке "ИСТИНА".
2 путь. Если результат проверки п.3 будет отличаться от ИСТИНЫ, то будет проверка из п.1 на истинность и здесь может быть только 2 решения, который мы увидим - это либо номер из В2 (если в случае проверки по п.1 мы получим ИСТИНУ) либо номер из А2 (если в случае проверки по п.1 мы получим ЛОЖЬ).
Схема мне помогла исключить лишнее нагромождение и повторных проверок в формуле.
💡Не забывайте, что количество открытых скобок должно соответствовать количеству закрытых скобок и они должны быть в нужном месте, и у вас все получится😉.