Общая формула
=INDEX(sata,MATCH(ROWS(exp_rng),sort,0))
Резюме
Чтобы получить и отобразить значения, отсортированные с помощью вспомогательного столбца, вы можете использовать формулы ИНДЕКС и ПОИСКПОЗ с небольшой помощью функции СТРОКА. В показанном примере формула в F5:
=INDEX(sales,MATCH(ROWS($D$5:$D5),sort,0))
который отображает первый элемент на основе индекса, указанного во вспомогательном столбце. Тот же подход используется для отображения связанных продаж в столбце G. Для удобства рабочий лист содержит следующие именованные диапазоны: item = B5: B11, sales = C5: C11, sort = D5: D11.
Объяснение
Эта формула отвечает на вспомогательный столбец, который уже содержит последовательный список чисел, представляющих установленный порядок сортировки. Числа в вспомогательном столбце не зависят от действия этой формулы. Пока последовательность является непрерывной, она может представлять сортировку по возрастанию или убыванию или даже произвольную сортировку. В большинстве случаев значения берутся из формулы.
По сути, это простая формула INDEX и MATCH, где INDEX извлекает значение на основе указанного номера строки:
=INDEX(item,row)
Уловка заключается в том, что строка вычисляется с помощью функции ПОИСКПОЗ на основе значений в столбце сортировки:
MATCH(ROWS($D$5:$D5),sort,0)
Подстановочное значение в совпадении генерируется с помощью функции ROWS и расширяющейся ссылки. В строке 5 рабочего листа диапазон включает одну ячейку, и ROWS возвращает 1. В строке 6 диапазон включает две ячейки, а ROWS возвращает 2, и так далее.
Массив - это именованный диапазон «sort» (D5: D11). В каждой строке MATCH находит искомое значение и возвращает позицию этого номера строки в исходных данных.
Поскольку нам нужно точное совпадение, третий аргумент, тип соответствия, предоставляется как ноль.
Значение, возвращаемое функцией MATCH, передается в функцию INDEX как номер строки, а INDEX возвращает элемент в этой позиции в исходных данных.
