#arrays #google-sheets #vlookup #array-formulas #google-query-language
#массивы #google-sheets #vlookup #массив-формулы #google-query-language
Вопрос:
У меня есть набор данных, из которого я хочу отфильтровать некоторые данные. Мне удалось запросить нужные данные, включая выбор даты. Проблема в том, что мне нужно немного доработать формулу, чтобы ограничить количество возвращаемых результатов (в определенных столбцах — L, M) до 3 и упорядочить по самым последним.
Формула, которую я имею на данный момент, выглядит примерно так: моя формула в I1
={
query(Sheet1!D2:H, "SELECT * WHERE E MATCHES 'East' and D > date'"amp;TEXT(B2,"yyyy-mm-dd")amp;"' and D <= date '"amp;TEXT(C2,"yyyy-mm-dd")amp;"'");
query(Sheet1!D2:H, "SELECT * WHERE E MATCHES 'West' and D > date'"amp;TEXT(B3,"yyyy-mm-dd")amp;"' and D <= date '"amp;TEXT(C3,"yyyy-mm-dd")amp;"'")
}
Ссылка на мой документ
https://docs.google.com/spreadsheets/d/1a_iUWKF4C0rFdaiHKZKbt1wrWySbWLLWUBiGvLjAKbE/edit?usp=sharing
Спасибо
Комментарии:
1. Я думаю, было бы полезно, если бы вы включили образец небольшого ввода и желаемого результата. Потому что вы говорите «уникальный» между L и M, но вы имеете в виду комбинацию или рассматривать каждую строку как две строки, потому что у них два имени? Включите этот образец в вопрос, а не во внешний лист.
2. Не могли бы вы уточнить, какой результат вы ожидаете, учитывая образец данных, который вы показываете? Когда вы говорите «Столбцы A, B, C содержат мои даты фильтрации», означает ли это возврат записей с Востока, где столбец D (дата) >= 2020-05-01 и <= 2020-07-30, и записей с Запада, где столбец D (дата) >= 2020-06-01 и <= 2020-09-30? И только три последние записи? Или по три последние записи для каждого представителя?
Ответ №1:
попробуйте:
=ARRAYFORMULA(QUERY({SORT(QUERY({DATEVALUE(D2:D), E2:H, IFNA(VLOOKUP(E2:E, A2:C, {2, 3}, 0))},
"where Col1>Col6-1 and Col1<Col7 1", 0), 1, ), COUNTIFS(
INDEX(SORT(QUERY({DATEVALUE(D2:D), E2:H, IFNA(VLOOKUP(E2:E, A2:C, {2, 3}, 0))},
"where Col1>Col6-1 and Col1<Col7 1", 0), 1, ),,2),
INDEX(SORT(QUERY({DATEVALUE(D2:D), E2:H, IFNA(VLOOKUP(E2:E, A2:C, {2, 3}, 0))},
"where Col1>Col6-1 and Col1<Col7 1", 0), 1, ),,2), ROW(INDIRECT("E2:E"amp;COUNTA(
INDEX(SORT(QUERY({DATEVALUE(D2:D), E2:H, IFNA(VLOOKUP(E2:E, A2:C, {2, 3}, 0))},
"where Col1>Col6-1 and Col1<Col7 1", 0), 1, ),,2)) 1)), "<="amp;ROW(INDIRECT("E2:E"amp;COUNTA(
INDEX(SORT(QUERY({DATEVALUE(D2:D), E2:H, IFNA(VLOOKUP(E2:E, A2:C, {2, 3}, 0))},
"where Col1>Col6-1 and Col1<Col7 1", 0), 1, ),,2)) 1)))},
"select Col1,Col2,Col3,Col4,Col5 where Col8 < 4 format Col1 'yyyy-mm-dd'", 0))
