Алгоритм создания формулы:
составим массив номеров столбцов, содержащих заданное значение (если значения нет то пишем количество элементов диапазона)
составим формулу для столбца с минимальным номером (это искомый столбец)
составим выражение для массива элементов этого столбца
составим формулу для индекс первого появления заданного значения (для искомой строки прибавляем смещение диапазона в таблице)
подставим найденные значения в функцию определения адреса элемента
Из алгоритма следует, что придётся составлять формулу массива (ввод CTRL+SHIFT+ENTER для старых версий Excel)
Разместим исходные данные в диапазоне B3:F7, а искомое значение в ячейке D10
формула для первого столбца, содержащего искомое значение (шаг 1,шаг 2)
=МИН(ЕСЛИ(( B3:F7=D10 )*СТОЛБЕЦ( B3:F7 )>0; СТОЛБЕЦ(B3:F7); ЧИСЛСТОЛБ( B3:F7 )*ЧСТРОК( B3:F7 )))
выражение для массива элементов первого столбца
=СМЕЩ(B3:F7; 0; МИН(ЕСЛИ(( B3:F7=D10 )*СТОЛБЕЦ( B3:F7 )>0; СТОЛБЕЦ(B3:F7) - СТОЛБЕЦ(B3); ЧИСЛСТОЛБ( B3:F7 )*ЧСТРОК( B3:F7 ))); ЧСТРОК(B3:F7); 1)
выражение для искомой строки
=ПОИСКПОЗ(D10; СМЕЩ(B3:F7; 0; МИН(ЕСЛИ(( B3:F7=D10 )*СТОЛБЕЦ( B3:F7 )>0; СТОЛБЕЦ(B3:F7) - СТОЛБЕЦ(B3); ЧИСЛСТОЛБ( B3:F7 )*ЧСТРОК( B3:F7 ))); ЧСТРОК(B3:F7); 1); 0)+СТОЛБЕЦ(B3)
Подставим выражения для строки и столбца искомого элемента в функцию определения адреса элемента:
=АДРЕС(ПОИСКПОЗ(D10; СМЕЩ(B3:F7; 0; МИН(ЕСЛИ(( B3:F7=D10 )*СТОЛБЕЦ(B3:F7)>0; СТОЛБЕЦ(B3:F7) - СТОЛБЕЦ(B3); ЧИСЛСТОЛБ( B3:F7 )*ЧСТРОК( B3:F7 ))); ЧСТРОК(B3:F7); 1); 0)+СТОЛБЕЦ(B3); МИН(ЕСЛИ(( B3:F7=D10 )*СТОЛБЕЦ(B3:F7)>0; СТОЛБЕЦ(B3:F7); ЧИСЛСТОЛБ( B3:F7 )*ЧСТРОК(B3:F7))); 4)
Для наглядности при поиске ошибок в EXCEL есть режим, позволяющий отобразить формулы, прямо в ячейках таблицы.
Если этот режим нужно отменить, то в меню Формулы\зависимост<wbr />и формул надо отжать кнопку Показать формулы и формулы будут удалены, вернее вместо формул в ячейках снова отобразятся результаты вычисления.
