- Какая формула ищет заданное значение в диапазоне Excel по столбцам?
- Как найти заданное значение в двумерном диапазоне если искать по столбцам?
- Можно ли формулой искать первое число в таблице Эксель по столбцам?
тэги:
excel,
поиск,
формула
категория:
компьютеры и интернет
ответить
комментировать
в избранное
бонус
1 ответ:
старые выше
новые выше
по рейтингу
1
vdtest
[20.5K]
1 неделю назад
Алгоритм создания формулы:
составим массив номеров столбцов, содержащих заданное значение (если значения нет то пишем количество элементов диапазона)
составим формулу для столбца с минимальным номером (это искомый столбец)
составим выражение для массива элементов этого столбца
составим формулу для индекс первого появления заданного значения (для искомой строки прибавляем смещение диапазона в таблице)
подставим найденные значения в функцию определения адреса элемента
Из алгоритма следует, что придётся составлять формулу массива (ввод 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)
комментировать
в избранное
ссылка
отблагодарить