Google листы: формула между листами, похоже, игнорирует критерии СООТВЕТСТВИЯ… (Скопируйте данные с одного листа на другой, используя 1 критерий соответствия)

#google-sheets #indexing #filter #match #google-sheets-formula

Вопрос:

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

Лист1 содержит сотни строк данных по многочисленным столбцам. В одной из колонок перечислены даты. Этот столбец в основном имеет формат даты. Эта дата col также использует различные правила условного форматирования. (Я не могу изменить их или форматирование этого столбца!).

В листе 5 у меня есть формула, которая должна просмотреть лист1 и найти все строки с определенной датой в таблице дат, а затем скопировать данные в этой строке в строку в листе 5.

Это выглядит так: =INDEX(sheet1!$A1:$O2002,MATCH($B$1,sheet1!$Q:$Q,0),0)

Это вроде как работает… если я просто вставлю формулу в одну ячейку листа 5, она найдет строку, соответствующую критериям даты, и скопирует данные. Но если я хочу запросить больше листов 1, перетащив ячейку вниз и найдя больше строк данных для копирования… он просто копирует все строки из листа 1 после первоначального поиска… полностью игнорируя то, с чем он сравнивается $B$1 $Q:$Q … Я подозреваю, что то, что, по моему мнению, сравнивается, MATCH($B$1,sheet1!$Q:$Q,0) может быть не тем, что происходит на самом деле, следовательно, результат не соответствует моим ожиданиям…

Вот снимок экрана листа, с которого я хочу скопировать данные: этот лист настроен и управляется другой стороной. Я НЕ МОГУ изменить данные; Я НЕ МОГУ изменить формат данных, который уже введен (например, я не могу изменить col, установленный на дату, на обычный текст!) Смотрите красные заметки. Но я могу преобразовать даты в col N в обычный текст в col Q. Столбец Q-это то, что я запрашиваю/сравниваю в формуле на целевом листе (см. Второй снимок экрана ниже).

Захват листа 1: данные для копирования, таблица критериев даты В листе 5 У меня есть эта формула, которая запрашивает лист1: =INDEX(sheet1!$A1:$O2002,MATCH($B$1,sheet1!$Q:$Q,0),0)

Вот скриншот того, что эта формула производит в листе5

Захват листа 5: результат использования уведомления о формуле, это вроде как работает…(когда я просто вставляю формулу в свою начальную ячейку… он нашел одну строку с заданными мной критериями), но затем, если я перетащу B3 вниз, чтобы запросить лист1 больше… он просто захватывает все, даже если $Q:$Q не соответствует критериям, установленным в B1…

Почему? Любая помощь или уточняющие вопросы приветствуются. Спасибо

Комментарии:

1. поделитесь копией своего листа

2. Для игрока 0 Хорошо, вот копия. Вы можете просматривать и комментировать. Никаких правок. Надеюсь, это поможет. docs.google.com/spreadsheets/d/…

Ответ №1:

при перетаскивании вы не заблокировали строки с помощью $

ваша формула:

 =INDEX(Sheet1!$A1:$Q100, MATCH($B$1, Sheet1!$Q:$Q, 0), 0)
 

должно быть:

 =INDEX(Sheet1!A$1:Q$100, MATCH($B$1, Sheet1!Q:Q, 0))
 

потому что с помощью вашей первоначальной формулы вы только что создали своего рода «диапазон скольжения» при каждом перетаскивании. чтобы понять формулу… вы индексируете диапазон A1:A100 (от строки 1 до строки 100), где вы сужаете его до 13-й строки (СОВПАДЕНИЕ выводит строку 13, потому что найдено совпадение. затем вы перетаскиваете вниз, и диапазон индексирования изменяется на A2:A101, но формула СООТВЕТСТВИЯ всегда выводит 13, поэтому 13-я строка из диапазона A2:A101-это строка 14 и т. Д.

в любом случае, используйте это в Sheet5!B3 после удаления всего в диапазоне B3:B:

 =FILTER(Sheet1!A:P, Sheet1!N:N*1=B1*1)
 

Комментарии:

1. Спасибо, игрок 0, я попробую это сделать. Если это сработает, я поддержу ваш ответ.

2. К сожалению, это не делает того, чего я хочу… Он находит одно совпадение, но когда я перетаскиваю его вниз, он просто повторяет одно и то же совпадение. Смотрите ссылку google sheet и результат используемой формулы/перенесенной из B3) Тестовая форма общего листа Google…только комментарий

3. @AZook как и предлагалось, используйте ФИЛЬТР (последняя формула из ответа)

4. Поэтому я заменил формулу (ваше исправление) =ИНДЕКС(Лист1!A$1:Q$100, СОВПАДЕНИЕ($B$1, Лист1!Q:Q, 0)) на =ФИЛЬТР… один, и это, похоже, работает. Я, должно быть, тупой, но тогда я не уверен, зачем мне это вообще нужно… =ИНДЕКС(Лист1!A$1:Q$100, СООТВЕТСТВУЕТ($B$1, Лист1!Q:Q, 0)) В любом случае, спасибо за ваши ответы. Я кое-чему здесь учусь.

5. Формула @AZook iIndex была просто объяснением того, что пошло не так. вам это совсем не нужно, потому что формула ФИЛЬТРА удовлетворит все ваши потребности.