Я собираюсь отработать несколько предположений:
- На вашем отчете есть ячейка A1, где кто-то может ввести интересующее вас оборудование.
- На странице «Сбор данных» есть два столбца, A и B: название оборудования и год / год для полноты
- На данный момент вы хотите видеть только «завершенные» строки на странице отчета (это может быть расширено путем незначительных изменений в формулах).
- Завершенные строки на странице отчета начнутся со строки 2
Во-первых, вам нужен способ определить, что оборудование, введенное в ячейку A1 вкладки отчета, соответствует каждой строке в столбце A набора данных и что работа в столбце B завершена. Это можно сделать с помощью формулы "и" в столбце C вашего вкладка сбора данных.
=AND(A1=reference!$A$1, B1='Y')
Это вернет TRUE, если оба условия выполнены, и FALSE, если одно или оба не выполнены.
Далее мы должны получить немного более высокий уровень по этому вопросу. Нам нужно получить ИСТИННЫЕ строки от листа ввода данных до листа отчета. Мы не хотим, чтобы на листе отчета была пустая строка между перенесенными ИСТИННЫМИ строками. В противном случае мы могли бы просто сделать небольшой vlookup или index/match на TRUE и назвать это днем. Вместо этого мы можем использовать RANK, чтобы установить порядок, в котором строки переносятся со страницы сбора данных на справочную страницу.
Чтобы сделать это, в столбце D на листе сбора данных нам нужно присвоить ИСТИНА ИСТИНА от 1 до любого количества ИСТИННЫХ строк. Поскольку нельзя сказать, что одна ИСТИННАЯ строка ранжируется выше другой, мы будем использовать ROW() для номера строки, чтобы установить ранг. В столбце D введите:
=IF(C1=TRUE, ROW(), 0)
Все, что нужно будет сделать, это записать строку в столбце D, когда будут выполнены критерии, установленные в столбце C. Теперь, чтобы добавить RANK, в столбце E введите:
=RANK(D1,D1:D14)*(D1>0)
Это делает две вещи. Сначала он определяет ранг строки, начиная с 1 и заканчивая любым количеством "ИСТИННЫХ" строк в первых 14 строках (увеличьте это, чтобы охватить все строки ввода данных). Во-вторых, *(D1>0) помещает 0 в столбец E, если значение в столбце D равно 0, потому что мы не хотим даже учитывать ранг, если это строка FALSE.
Теперь вы должны были ранжировать значения в столбце E в порядке убывания, основываясь на том, был ли критерий ИСТИНА и номер строки. Теперь нам нужно перетащить эти значения на лист отчета. Для этого в отчете, начиная с ячейки А2, начинайте с нумерации 1,2,3,4, ... вниз по столбцу. Они будут "соответствовать" до рангов в вашем листе сбора данных в столбце E.
Теперь, чтобы получить первый столбец совпадающих строк, введите следующее в столбец B вашего отчета (начиная с B1):
=INDEX(Sheet2!A:A, MATCH(Sheet1!A2, Sheet2!E:E,0), 1)
Это использует функцию индекса, которая позволяет вам извлечь значение из определенной строки и столбца в диапазоне. Наш ассортимент - Sheet2!A:A (лист сбора данных, столбец A). Наш ряд в зависимости от ранга (sheet2!E:E) значение совпадает с нашими числами, которые мы ввели в столбец А нашей таблицы отчетности. Наш столбец - только 1. Посмотрите "индекс соответствия в Excel" в Google, чтобы понять это еще раз, если это ново для вас. Вы можете извлечь свой столбец сбора данных B таким же образом. В столбце B вашего отчета (начиная с B2) введите следующее:
=INDEX(Sheet2!B:B, MATCH(Sheet1!A2, Sheet2!E:E,0), 1)
Вот и все для моих простых предположений. По сути, вам нужно три столбца, чтобы определить, были ли выполнены ваши критерии на вкладке сбора данных, а затем ранжировать эти данные от 1 до n. И вам нужно использовать индексное совпадение (или vlookup, если вы немного переставите вещи), чтобы вытянуть эти ранжированные строки в свой отчетный лист. Вы можете скопировать эту же логику для ранжирования FALSE в случае, если вы хотите перенести строки, помеченные как неполные, на вкладке сбора данных.
Наконец, после того, как вы обдумаете это, рассмотрите возможность перемещения столбцов C, D, E на вкладке сбора данных на какую-то другую вкладку, чтобы люди, выполняющие сбор данных, не смешивали ваши формулы. Функция «Индекс / соответствие» будет по-прежнему работать нормально, и ваши собеседники не увидят мрачных деталей внутренней работы вашей книги.
