Например, моя таблица, которую я назову Table1 (я определил это как имя), пополняется по мере поступления новых данных. Как я могу использовать vlookup для извлечения значения 2-го столбца конечной (последней) точки данных в нижней части Table1?
решение1
Первый столбец числовой или текстовый? Вы можете использовать опцию поиска по диапазону и найти значение, которое будет больше любого значения в вашей таблице. Если ваш первый столбец содержит слова, попробуйте:
=VLOOKUP("ZZZZZ", Table1, 2, TRUE)
или, если ваш первый столбец представляет собой пятизначное число, попробуйте:
=VLOOKUP("99999", Table1, 2, TRUE)
И мне только что пришло в голову, что это тоже будет работать, но медленнее. Определите первый столбец Table1 как Table1Col1.
=VLOOKUP(MAX(Table1Col1), Table1, 2, TRUE)
решение2
Если вы хотите динамически обновить выбранный столбец, вы можете использовать формулу MATCH() для заполнения значения col_index_num. Например:
=VLOOKUP("d", Table1, 2, FALSE)
вернет значение во втором столбце. тогда как:
=VLOOKUP("d",Table1[#All],MATCH("value2",Table1[#Headers],0),FALSE)
вернет значение в столбце, заголовок которого — value2. Еще более динамично, вы можете записать это так:
=VLOOKUP("d", Table1, MATCH(Table1[[#Headers],[value2]],Table1[#Headers],0), FALSE)
который обновится автоматически, если вы переименуете заголовки столбцов таблицы
решение3
=VLOOKUP(A2,Table1,2,MAX(Table1Col2))
Объяснение: =VLOOKUP(**Data being looked up**,**Name of Table or data range**,**column of data being looked up**,MAX(**Name or data range of data being looked up**))
Он вернет последнее значение строк, которые соответствуют искомому значению.
решение4
Отвечаю в основном для того, чтобы дать современный ответ для текущих пользователей («современный» = 2021).
Вы бы использовали XLOOKUP(). Это не идеальная замена и не подойдет для некоторых ситуаций. Но это простая замена, и ее бета-версия была переработана для этого.
Интересная особенность заключается в том, что у него есть параметр для поиска от конца к началу, так что... ура!
Однако, читая ответы, я заметил, что нет однозначного ответа на вопрос в терминах, которые бы работали, когда он был опубликован. Средний ответ вообще не затрагивает вопрос, а третий лжив в энной степени. Он использует что-то причудливое для генерации булевого значения, TRUEкогда простое печатание TRUE(или 0для них что предпочитает) сделало бы то же самое. Почти создает впечатление, что отвечающий надеялся, что что-то причудливое, что-то претенциозное примет вид значения. Ужас. И это НЕ НЕ НЕ правильно в том, что, хотя оно МОЖЕТ дать не неправильный ответ, это просто случайно. Неплохой шанс, поскольку основным режимом отказа будет ситуация, когда желаемое значение будет абсолютно последним элементом в диапазоне, но, учитывая, что это может быть основано на дате, это имеет довольно приличный шанс произойти.
Первый ответ дает несколько практических вещей, которые можно попробовать, и в зависимости от ваших данных, один из них или что-то, вдохновленное ими, может хорошо подойти вам. Не как третий, который довольно аферистский и дал бы многим сильное чувство уверенности в результатах, оставив их ошеломленными, когда босс придет и начнет кричать о неудаче.
Однако это всего лишь практические вещи, а не обязательно решения. Существует стандартный подход к этому, доступный "много где" в интернете, который используется LOOKUP()умным способом. Но как насчет чего-то гораздо более прямого и легкого для понимания работы? (Для большинства этот LOOKUP()метод, хотя и умный, действительно умный и на самом деле очень простой, просто сбивает с толку, теряя их на первом этапе взгляда, так что, хотя они могли бы ЛЕГКО понять его, они никогда не доходят до того момента, чтобы осознать это.)
Функцию INDEX()можно использовать для переворачивания таблицы вверх дном, так сказать, так, чтобы первая строка, представленная остальной части формулы, была последней строкой в исходном («физическом») диапазоне. Это делается путем указания для параметра строки чего-то, что генерирует последовательность чисел, начиная с самого высокого номера строки (самого высокого «индекса») и уменьшаясь до 1.
Это было сделано в 2012/2013 годах с помощью хитрого трюка для генерации номеров строк в последовательности: ROW(1:100)дает последовательность 1, 2, 3, ... 98, 99, 100. Чтобы обратить это, вы могли бы подумать, что вы могли бы просто использовать , ROW(100:1)но Excel услужливо «исправляет» вашу глупую ошибку, давая вам ROW(1:100)— нравится вам это или нет. Никакого способа преодолеть это. Но если вы знаете наибольший использованный номер строки, и вы бы так не думали, вы можете прибавить к нему 1, так что в этом примере 100 + 1, а затем вычесть последовательность, которую дает вам Excel, из этого. Поскольку у вас есть 101 - 1, 100 - 2, 100 - 3, ..., вы получаете 100, 99, 98, ... и так до 101 - 100 или 1.
Используйте это для параметра строки, чтобы INDEX()поменять строки местами, от последней к первой.
Для столбцов для INDEX()вам придется думать дальше обычного опыта с VLOOKUP(). Обычно вы даете ему таблицу/диапазон, который может иметь много столбцов. Так, например, A2:W900. Вы хотите выполнить поиск в столбце A и найти что-то в столбце T (или столбце 20). Так что у вас может быть что-то вроде VLOOKUP("cow",A2:W900,20,false). Ну, INDEX()позволяет вам использовать небольшой трюк, чтобы указать только определенные столбцы, а не использовать все столбцы в заданном диапазоне: константа массива.
Array Constantsэто способ дать, ну, массив... значений Excel. Они выглядят так: {"a","cow",13,999,"fisherman","circu"}. So in this example, you want to have columns A and T, or 1 and 20, so you'd giveINDEX() {1,20}` для третьего параметра, параметра столбца.
Суть всего этого в том, что это INDEX()создает вам виртуальную таблицу для использования в формуле вместо реальной таблицы. Виртуальная таблица не имеет строк 2-900 и столбцов AW. Она имеет строки с 900 по 2 и только столбцы A и T. Эта функция будет выглядеть так для примера:
INDEX( A2:W900, 900-ROW(1:899), {1,20} )
(Помните, вы используете строки со 2 по 900, поэтому у вас всего 899 строк, а не 900. Таким образом, строка Excel 2 — это «индекс» 1 в диапазоне, а строка Excel 900 — это индекс 899. 899 + 1 = 900, поэтому вы используете 900, а не 901. Тогда числа в формуле имеют «смысл». Например, назвать первый жесткий диск «Диском 0», а не «Диском 1».)
Теперь у вас есть виртуальная таблица ТОЛЬКО из столбцов A и T, строка 900 первая, спускающаяся к строке 2 последняя, и это ваша таблица для использования в VLOOKUP(). Таким образом, полная формула для поиска слова "корова" и получения любого точного совпадения... начиная с КОНЦА и возвращаясь к началу, будет выглядеть так:
=VLOOUP( "cow", INDEX( A2:W900, 900-ROW(1:899), {1,20} ), 2, false )
Поскольку данные "реальной" таблицы находятся в строках 2-900, это поиск ПОСЛЕДНЕГО совпадения с использованием таблицы, которая перевернула данные, чтобы получить строки 900-2. И она использует только два столбца, тот, в котором вы делаете поиск, и тот, из которого вы получаете данные, а не все 23 столбца данных. Поскольку есть только два столбца, столбец для возврата ответа всегда ЛЕГКИЙ: это столбец 2. "false" гарантирует точное совпадение, если таковое имеется.
И это ВСЕГДА будет работать.
Кстати, та константа массива, которую мы использовали, {1,20}... что, по-вашему, произойдет, если поменять местами эти значения? Например, {20,1}. Они меняют порядок столбцов. Так что если вы хотите использовать VLOOKUP(), но ваш столбец поиска в этом примере - столбец T, а вам нужны результаты из столбца A, вам не повезет. (Старая "несчастный случай из-за неудачи" со времен моей работы в ядерной энергетической программе ВМС США. Господи, несчастный случай "OOL"... мы были ТАК близки к созданию "LOL" на несколько лет раньше, но... просто... не... Ну, я полагаю, аварии на реакторных установках - это не повод для смеха, так что, может, это и к лучшему, а?) Ваш столбец поиска ДОЛЖЕН быть слева от столбца результатов, чтобы использовать VLOOKUP()... no bueno!
Однако, поскольку вы создаете виртуальную таблицу, которая меняет столбцы местами, ваш столбец поиска внезапно оказывается СЛЕВА! BUENO!
Кроме того, константа массива для столбцов (или строк, но обычно столбцов) может использовать только некоторые числа, использовать их в любом порядке и использовать их более одного раза, если захотите.
Вспоминаем «современный метод»: в настоящее время мы можем использовать его SEQUENCE()для непосредственной генерации обратной последовательности от 899 до 1.