Блог

У меня есть список значений в столбце A на ВКЛАДКЕ Excel, а в столбце B рядом с ним у меня есть список слов. На другой вкладке я пытаюсь извлечь слова из B, где A имеет наименьшее значение, затем 2-е меньшее значение, 3-е и т.д.

Пример: столбец A будет содержать (2,3,3,6,8) и B (автомобиль, собака, кошка, дом, лодка)

Сначала я создал столбец, в котором я извлек наименьшие значения:

• В столбце X1 я добавил: SMALL('Table'!$A:$A,1)
• В столбце X2 я добавил: SMALL('Table'!$A:$A,2)
• и т.д…

и тогда VLOOKUP сработал для меня:

• В столбце Y1 я добавил: VLOOKUP(X1,'Table'!$A:$B,2,FALSE)
• В столбце Y2 я добавил: VLOOKUP(X2,'Table'!$A:$B,2,FALSE)

Пока все хорошо. Моя проблема в том, что мои значения повторяются. * т. е.: В приведенном выше примере всякий раз, когда функция находит значение 3 в столбце A, я получаю слово dog дважды вместо dog и cat, потому что оно просто отображает первое значение, которое находит vlookup.

Я попытался добавить смещение: =OFFSET(SMALL('Table'!$A:$A,1),1,0) но не уверен, работает ли оно для извлечения из других вкладок.

Любая помощь или обходной путь для этого, пожалуйста? Заранее большое спасибо.

Я предлагаю использовать индекс / совпадение вместо VLOOKUP и добавлять единицу в позицию совпадения, если одно и то же значение встречалось один раз раньше, два, если оно происходило дважды и т. Д.:-

 =INDEX(Table!B:B,MATCH(A2,Table!A:A,0) COUNTIF(A$1:A1,A2))
 

Вы также можете использовать что-то подобное, чтобы получить наименьшее, второе наименьшее и т.д., а не жестко кодировать его:-

 =SMALL(Table!$A:$A,ROW(1:1))
 

Итак, трюк, который я собираюсь использовать для разделения строк с равными значениями, заключается в добавлении небольшого количества к каждой строке, чтобы сделать значение уникальным. Вот мои формулы:-

 =LARGE(IF(A$2:A$9="Restaurants",C$2:C$9),ROW(1:1))
 

чтобы получить наибольшее значение, это стандартная формула

 =INDEX(D$2:D$9,MATCH(LARGE(IF(A$2:A$9="Restaurants",C$2:C$9 ROW(C$2:C$9)/10^7),ROW(1:1)),C$2:C$9 ROW(C$2:C$9)/10^7,0))
 

чтобы получить соответствующий город. Это будет работать, даже если равные значения не находятся в соседних строках.

Я не знаю, сколько строк у вас в ваших фактических данных, если бы их было больше 100, вам нужно было бы использовать меньшее количество, чем .01.

Пожалуйста, обратите внимание, что это формулы массива, и их необходимо вводить с помощью CtrlShiftEnter

Для самых маленьких просто измените значение LARGE на SMALL.

Если вы хотите исключить любые нечисловые значения, такие как «не ранжированные», вам нужно

 =INDEX(D$2:D$9,MATCH(LARGE(IF((A$2:A$9="Restaurants")*ISNUMBER(C$2:C$9),C$2:C$9 ROW(C$2:C$9)/10^7),ROW(1:1)),C$2:C$9 ROW(C$2:C$9)/10^7,0))
 

Я бы не рекомендовал менять его на использование D: D, C: C и т. Д., Потому что это было бы медленно, но это сработало бы.