Вопрос или проблема
Я пытаюсь вернуть заголовок строки и заголовок столбца на основе значения, которое я ищу в матрице. Как я могу найти ближайшее совпадение в матрице и вернуть его заголовок строки и заголовок столбца в одной ячейке? Я пробовал формулы обратного поиска и формулу “ИНДЕКС-СОВПАДЕНИЕ”, но не получил никаких результатов.
Кстати, я использую версию Excel 2021.
=LET(
reference,L1,
array,B2:I12,
tocol,TOCOL(array),
cols,COLUMNS(array),
closest,XLOOKUP(MIN(ABS(tocol- reference)), ABS(tocol - reference), tocol),
position,MATCH(closest,tocol),
xpos,MOD((position-1),cols)+1,
ypos,INT((position-1)/cols)+1,
VSTACK("Этап " & xpos, "Уровень " & ypos))
Вы должны указать, какое близкое значение вы хотите, большее или меньшее. Предположим, вы хотите точное совпадение или большее значение, тогда попробуйте следующую формулу для заголовка столбца.
=LET(x,XLOOKUP(K6,TOCOL(B2:I31),TOCOL(B2:I31),,1),
INDEX(A1:I1,1,MAX(COLUMN(B2:I31)*(B2:I31=x))))
А для заголовка строки –
=LET(x,XLOOKUP(K6,TOCOL(B2:I31),TOCOL(B2:I31),,1),
INDEX(A1:A31,MAX(ROW(B2:I31)*(B2:I31=x))))
Ответ или решение
Для решения задачи поиска ближайшего значения в матрице и получения заголовков строки и столбца в Excel, необходимо использовать функции, такие как LET, XLOOKUP, INDEX и MATCH. Приведу поэтапное описание, как это сделать.
Шаг 1: Определение ближайшего значения
Используйте следующую формулу для поиска ближайшего значения в матрице и получения его заголовка столбца:
=LET(
reference, K6, // значение, к которому вы ищете ближайшее
array, B2:I31, // диапазон вашей матрицы
tocol, TOCOL(array),
cols, COLUMNS(array),
closest, XLOOKUP(MIN(ABS(tocol - reference)), ABS(tocol - reference), tocol, , 1), // поиск ближайшего значения
position, MATCH(closest, tocol), // позиция ближайшего значения
xpos, MOD((position - 1), cols) + 1, // координата по столбцу
INDEX(A1:I1, 1, xpos) // заголовок столбца
)Шаг 2: Получение заголовка строки
Для получения заголовка строки, используйте следующую формулу:
=LET(
reference, K6,
array, B2:I31,
tocol, TOCOL(array),
position, MATCH(XLOOKUP(MIN(ABS(tocol - reference)), ABS(tocol - reference), tocol, , 1), tocol),
ypos, INT((position - 1) / COLUMNS(array)) + 1, // координата по строке
INDEX(A2:A31, ypos) // заголовок строки
)Шаг 3: Объединение результатов
Вы можете объединить оба заголовка в одну ячейку, используя функцию TEXTJOIN, следующим образом:
=TEXTJOIN(", ", TRUE,
LET(
reference, K6,
array, B2:I31,
tocol, TOCOL(array),
cols, COLUMNS(array),
closest, XLOOKUP(MIN(ABS(tocol - reference)), ABS(tocol - reference), tocol, , 1),
position, MATCH(closest, tocol),
xpos, MOD((position - 1), cols) + 1,
row_header, INDEX(A2:A31, INT((position - 1) / cols) + 1),
column_header, INDEX(A1:I1, 1, xpos),
row_header & " - " & column_header // объединение заголовков
)
)Примечание:
- Не забудьте заменить диапазоны и ссылки на ячейки на ваши собственные в формуле.
K6– это ячейка, где вы вводите значение, к которому хотите найти ближайшее.- Для правильной работы формул может потребоваться наличие Office 365 или версии Excel 2021, так как они используют функции, введенные в обновленных версиях.
С помощью данных формул вы сможете быстро находить ближайшие значения в матрице и возвращать соответствующие заголовки строки и столбца. Обязательно протестируйте формулы на своих данных для подтверждения корректности решений.