Значение поиска на основе последних критериев соответствия

Ниже приведен пример таблицы, которая у меня есть, я пытаюсь получить значение в столбце значений для определенных критериев на основе последнего появления (не включая сегодняшнюю дату).

Итак, в приведенном ниже примере я хочу найти значение для последнего вхождения 'A', которое равно 12.

Я думаю, что это можно сделать с помощью Index-Match, хотя я просто не могу понять это.

Например

Todays Date: 15/12/2013
---------------------------------|
|Date        | Criteria | Value
|--------------------------------|
|12/11/2013  | A        | 3      |
|16/11/2013  | B        | 6      |
|27/11/2013  | C        | 7      |
|3/12/2013   | A        | 12     |
|5/12/2013   | B        | 8      |
|15/12/2013  | A        |        |
----------------------------------

РЕДАКТИРОВАТЬ: Я также хотел бы добавить, что эта формула будет на другом листе в таблице выше. Ссылка на лист в формуле также должна быть динамической, она будет отображать имя листа из другой ячейки.


google-sheets
person KGambit    schedule 15.12.2013    source источник

Ответы (3)


arrow_upward
6
arrow_downward

Я бы использовал эту формулу:

= index (C: C, max (arrayformula (match (filter (A: A, B: B = "A", C: C ‹>" "), A: A, 0))), 1)

Эта формула предполагает, что ваши данные находятся в столбцах A, B, C и для каждого значения «A» в столбце Criteria дата отличается. (Если это не так, то эта формула не будет работать, см. Ниже.

Посмотрим на формулу снаружи снаружи:

  1. filter (A: A, B: B = "A", C: C ‹>" ") - в результате будут получены даты, где в столбце" Критерии "стоит" A ", а где Столбец «Значение» не пуст.
  2. arrayformula (match (filter (A: A, B: B = "A", C: C ‹>" "), A: A, 0)) - на этом этапе мы в основном находим строку номер, в котором присутствуют эти даты. Функция совпадения будет искать даты (подсчитанные на шаге 1). Формула массива нужна, потому что результатов будет больше.
  3. max (arrayformula (match (filter (A: A, B: B = "A", C: C ‹>" "), A: A, 0))) - найдет максимальное номер строки (максимальный номер строки, который содержит букву "A" в столбце "Критерии")
  4. index (C: C, max (arrayformula (match (filter (A: A, B: B = "A", C: C ‹>" "), A: A, 0))), 1) < / strong> - Наконец, мы используем функцию ИНДЕКС для перехода к значению с максимальным номером строки.

Теперь, если вы хотите, чтобы эта формула работала на другом листе, вы должны написать вместо, например:

= index (C: C, ... => = index (Data! C: C, ...

Предполагая, что ваши данные находятся в вашем листе данных.

Если вы хотите, чтобы этот лист был динамичным, это немного сложно. Предположим, вы получаете значение имени листа из ячейки G1. Затем следует написать:

= индекс (косвенный (объединить (G1, "! C: C")), ...

Это не так красиво, как вам следовало бы делать в каждом случае, когда это встречается в этой длинной формуле (описанной ранее). Вместо этого вы можете сделать некоторые предварительные работы.

Давайте напишем это в вашу ячейку H1: = concatenate (G1, "! C: C") - Если в ячейке G1 имя листа - "Data", тогда ячейка H1 должна содержать: Data! C: C, аналогично вы можете добавить к

Ячейка H2: = объединить (G1, "! A: A"), ячейка H3: = объединить (G1, "! B: B")

Теперь вы можете написать (и это, я думаю, окончательный ответ на ваш вопрос):

= индекс (косвенный (H1), max (формула массива (соответствие (фильтр (косвенный (H2), косвенный (H3) = "A", косвенный (H1) ‹>" "), косвенный (H2), 0) )), 1) - где H1, H2, H3 будут ссылаться на столбцы вашей таблицы данных.

Я надеюсь, что это помогает.

person zolley    schedule 29.04.2014

arrow_upward
2
arrow_downward

Для этого используйте следующую формулу.

Формула

=QUERY(
   B1:D6,                // data
   "SELECT D             // select
    WHERE                // where clause
      C = 'A' AND        // first criterium
      D IS NOT NULL      // second criterium
    ORDER BY B DESC      // order by
    LIMIT 1,             // limit
    0"                   // headers
 )

for copy/paste
=QUERY(B1:D6, "SELECT D WHERE C = 'A' AND D IS NOT NULL ORDER BY B DESC LIMIT 1", 0)

Объяснил

Ключом к разгадке формулы является использование параметров ORDER BY и LIMIT в формуле QUERY. Предложения WHERE подготовят результат в первую очередь. Затем столбец B (даты) упорядочивается по убыванию (сначала самый высокий). Параметр LIMIT устанавливает количество строк, отображаемых в 1.

Пример

Я создал для вас пример файла: Значение поиска на основе последних критериев соответствия

person Jacob Jan Tuinstra    schedule 15.12.2013
comment
Привет, приятель, это отлично работает при прямом обращении к ячейкам, но я должен был быть более конкретным. Я использую эту формулу на листе, отличном от запрашиваемого диапазона ячеек. Я также получаю имя листа динамически из другой ячейки. Для других запросов, которые я делал с помощью ВПР, я использовал функцию ДВССЫЛ, чтобы ссылаться на диапазон ячеек. Однако это не работает с функцией QUERY (я думаю, что QUERY принимает только прямые ссылки). Я обновлю свой вопрос, чтобы он был более конкретным. Спасибо еще раз. - person KGambit; 16.12.2013
comment
Вы можете использовать ссылки на ячейки внутри запроса, используя формат '& $ A4 &' - person Bjorn Behrendt; 27.02.2015

arrow_upward
0
arrow_downward

Я понимаю, что это немного старый вопрос, но есть способ, которым я достиг цели фильтрации массива, который я нашел как более концептуально простым, так и более универсальным, чем другие ответы, которые я видел, используя определяющую способность vlookup выбирать первое совпадающее значение в массиве.

ПРОБЛЕМА, УСТАНОВЛЕННАЯ:

Предполагая образец данных: A ... B ... C ... D ... E, созданный с помощью формы Google

A - дата ввода формы. B, C и D - записи из списка (предположим, это, например, название продукта, география и дата продажи) E - значение

Если новое значение вводится для определенного продукта, в географическом регионе, на дату, я хочу, чтобы это использовалось вместо более старой версии тех же данных.

РЕШЕНИЕ: Если в вашей форме вы создаете три новых столбца: F Уникальный тест G Объединение тестовых ячеек H Уникальные ячейки

Затем в столбце G вы создаете комбинацию всех ячеек, на которых хотите протестировать (в данном случае B, C и E) cell G2: "=arrayformula(B2:B & char(9) & C2:C & char(9) & D2:D)"

Следующий столбец представляет собой повторное определение ячеек, которые вы хотите отфильтровать на основе (в данном случае даты в A) cell H2: "=arrayformula(A2:A)"

И, наконец, в столбце F мы фактически проводим тест: cell F2: "=arrayformula(A2:A=vlookup(G2:G,sort({G2:H},2,false),2,false))"

Разбивая это, vlookup (vlookup (G2: G, [RANGE], 2, false) сравнивает данные в G2, G3 ... Gn с [RANGE], который представляет собой виртуальный массив, состоящий из двух столбцов, G и H, предварительно отсортированные по ячейке H в порядке убывания.

т.е. для любого уникального значения G (комбинация тестовых данных) vlookup вернет наибольшее значение H

Последняя часть представляет собой простое сравнение с исходными данными (A2, A3 ... An) для возврата TRUE или FALSE в зависимости от того, является ли это последней версией уникального значения.

Последним шагом, если потребуется, будет создание нового листа с "=filter('Form Responses 1'!A:E,'Form Responses 1'F:F=TRUE) для воссоздания данных без старых версий.

Надеюсь это поможет.

person David Battley    schedule 20.08.2015