Сопоставление Индекса Excel+ Возвращает повторяющееся значение
Компания, в которой я работаю, использует таблицы Excel, чтобы найти самое дешевое доступное топливо в желаемом месте заправки. Мне удалось заставить формулы уже имеющегося листа работать правильно, за исключением одного очень конкретного случая, когда более чем в одном месте предлагается топливо по одной и той же цене.
Пример изображения
В основном в примере:
Для столбцов H, J и L формула рассчитывает первое, второе и третье место с самым дешевым топливом.
Формулы:
=Small(C3:G3;Countif (C3:G3;0)+1)
=Small(C3:G3;Countif (C3:G3;0)+2)
=Small(C3:G3;Countif (C3:G3;0)+3)
Для столбцов I, K и M он находит $$, соответствующий ячейке рядом с ним с названием станции в первой строке.
В этом случае, поскольку Курсель, Лак-Мегантик и Сен-Гедеон продают топливо по одной и той же цене (1,4505), в качестве ответа возвращается 3X Курсель.
Формулы:
=index($C$1:$G$1;Match(H3;C3:G3;0))
=index($C$1:$G$1;Match(J3;C3:G3;0))
=index($C$1:$G$1;Match(L3;C3:G3;0))`
Можно ли, не переделывая весь лист, либо:
Верните самую дешевую станцию, но если название уже есть в ячейке 1 или 2, перейдите к следующей.
Вернуть все названия станций, соответствующие самой низкой цене, затем найти следующую самую высокую цену и следовать, как если бы
Вот что я хотел бы вернуть/ожидаемый результат
Ответы 1
Вы можете попробовать использовать следующую формулу, предполагая, что в отношении тегов сообщений нет ограничений Excel:
=LET(
_x, A3:E3,
_y, TAKE(SORT(UNIQUE(A3:E3,1),,,1),,3),
TOROW(VSTACK(XLOOKUP(_y,_x,$A$1:$E$1),_y),,1))
Возможно, вам придется изменить ссылку на ячейку или диапазоны в соответствии с вашими предпочтениями.
Или, как вы и ожидали, результат показан на втором снимке экрана:
=LET(
_x, A3:E3,
_y, $A$1:$E$1,
_z, SORT(UNIQUE(_x,1),,,1),
_List, BYCOL(_z,LAMBDA(a,TEXTJOIN(CHAR(10),1,FILTER(_y,a=_x)))),
TOROW(VSTACK(_z,_List),,1))
Используйте следующее, если будут какие-либо пробелы:
=LET(
_x, A3:E3,
_y, $A$1:$E$1,
_z, SORT(UNIQUE(_x,1),,,1),
_List, BYCOL(_z,LAMBDA(a,TEXTJOIN(CHAR(10),1,FILTER(_y,(a=_x)*(_x<>0),NA())))),
TOROW(IF(_List=0,NA(),VSTACK(_z,_List)),2,1))
@Марк-Андрепулиот рад это знать =) Спасибо.
Вторая формула работает безупречно. Однако это поднимает еще одну проблему. В настоящее время все данные собираются на первом листе и извлекаются только последняя непустая строка каждого листа. Можно ли отредактировать эту формулу, чтобы игнорировать значения, равные 0?