#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 была просто объяснением того, что пошло не так. вам это совсем не нужно, потому что формула ФИЛЬТРА удовлетворит все ваши потребности.