Можно ли ссылаться на две ячейки в Excel, чтобы найти третью ячейку?

Question

Я попытался сделать так, чтобы это работало, предполагая, что:

«Прикрепленные компоненты» всегда находятся в одном столбце.
На самом деле вы пытаетесь найти описание для каждого «Материала №».

И я буду использовать этот лист для работы над:

Пример

Возможно, это не совсем то, что вам нужно, но я могу попытаться улучшить свой ответ, учитывая ваши замечания.

Повторно используя вашу формулу для определения того, где в столбце находятся «Прикрепленные компоненты», а затем добавляя 2, получаем относительную строку, где начинается описание материала:

=MATCH("Attached Components",B1:B32,0)+2

Результат в примере «7».

После этого вам нужно определить последнюю строку, где находятся описания. Для поиска в правильном диапазоне формула должна меняться в зависимости от того, в какой строке находится "Прикрепленные компоненты". Комбинация MATCH, ADDRESS, CONCATENATE воссоздаст диапазон.

MATCH возвращает относительную строку, ADDRESS преобразует номер строки и номер столбца в строку с именем ячейки (ADDRESS(1,1)="$A$1"), CONCATENATE объединяет строки для создания диапазона.

CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))

Это возвращает строку типа "$C$7:$C$25". Таким образом, он охватывает столбец Описание и начинает со строки, где у вас есть значения, на 18 строк ниже. Чтобы охватить больше или меньше строк, просто измените "+20" в формуле на соответствующее значение.

Чтобы найти последнюю строку, нужно просто найти первую пустую ячейку с помощью IF и MIN.

{=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))))
     )
)-1}

Эта формула является формулой массива. Вот почему она заключена в скобки (скобки не вводите, они появляются, когда вы вводите формулу и нажимаете Ctrl+Shift+Enter)

INDIRECT преобразует строку, которую мы построили, в ссылку на ячейку. ROW возвращает номер строки в качестве результата. MIN примет наименьшее значение в возвращаемом диапазоне. «-1» в конце означает, что будет указан номер строки последнего описания, а не первая пустая строка.

В примере эта формула возвращает «9».

Теперь у нас есть номер строки первого описания и последнего описания, от 7 до 9. Мы можем объединить эти числа так, как нам нужно, используя ADDRESS, CONCATENATE и INDIRECT, чтобы выполнить любую операцию, которая вам нужна. Но на этот раз у вас есть конкретная ссылка на ячейку, с которой можно работать.

Например, поиск по номеру материала:

Пример ВПР

В этом последнем примере ячейки содержат

Э2:

=MATCH("Attached Components",B1:B32,0)+2

F2 (для входа с помощью Ctrl+Shift+Enter):

=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))))
    )
)-1

Ф7:

=VLOOKUP(E7,INDIRECT(CONCATENATE(ADDRESS(E2,1),":",ADDRESS(F2,3))),3,FALSE)

Таким образом, когда вы вводите номер материала в ячейку E7, его описание отображается в ячейке F7.

РЕДАКТИРОВАТЬ:

Следуя комментариям, решение можно найти следующим образом:

Используем более сложный пример:

Сложный пример

Сопоставление строк — это просто каскад из 2 функций MATCH. Используем первую функцию MATCH для поиска номера детали, а затем вторую — для поиска интересующего раздела:

2 совпадения

F3: строка части, которую вы ищете

F4: формула для поиска «Номер детали» в первом столбце.

=MATCH($F$3,A1:A32,0)

F6: название раздела, который вы ищете

F7: формула для поиска раздела в части, определенной ранее. Сопоставление выполняется в диапазоне, который начинается со строки "Part #" (хранится в ячейке F4). Диапазон строится с использованием той же формулы, которая использует INDIRECT, CONCATENATE, ADDRESS. Затем относительная строка, возвращаемая MATCH, смещается с помощью F4-1, чтобы получить абсолютный номер строки.

=MATCH($F$6,INDIRECT(CONCATENATE(ADDRESS(F4,2,1),":",ADDRESS(F4+20,2,1))),0)+F4-1

Теперь, чтобы определить первую и последнюю строки описания, мы можем повторно использовать те же формулы, что и раньше:

первая и последняя строка для описаний

F9: добавление 2 к номеру строки «Прикрепленные компоненты» для получения первой строки описания.

=F7+2

F10: поиск первой пустой строки в диапазоне описания (начиная со строки, сохраненной в F9). Это формула массива, которую нужно ввести с помощьюCTRL+SHIFT+ENTER

=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS($F$9,3),":",ADDRESS($F$9+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS($F$9,3),":",ADDRESS($F$9+20,3))))
     )
)-1

Затем для отображения описания мы можем использовать INDIRECT и столбец индекса:

Матрица отображения

Ф15:

=IF($F$9+$E15-1<=$F$10,INDIRECT(ADDRESS($F$9+$E15-1,1)),"")

Г15:

=IF($F$9+$E15-1<=$F$10,INDIRECT(ADDRESS($F$9+$E15-1,3)),"")

Эти формулы отобразят номер материала и описание для строки, идентифицированной индексом в столбце E. Оператор IF нужен для того, чтобы убедиться, что мы не отображаем строки, которые находятся ниже последних строк. В примере отображается только 5 строк, но вы можете просто скопировать эту формулу, перетащив вниз первую строку и добавив новые индексы, чтобы их было больше.

Answer 1