☰

Excel — Фильтрация таблиц с одного листа на другой с помощью формулы

tag-icon • tag-icon microsoft-excel microsoft-excel-2013
Excel — Фильтрация таблиц с одного листа на другой с помощью формулы

Как мне отфильтровать таблицу с одного листа на другой лист, используя формулу? Без формулы я могу просто скопировать ссылку на ячейки, например так:

=INDEX(MainSheet!A:A,ROW())

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

Например, предположим, у меня есть 3 листа:

  • Основной лист
  • Лист 2
  • Лист 3

На моем листе «Основной» у меня есть:

| ID    | Name      | Filter To
|-------------------------------
| 1     | Record 1  | Sheet 2
| 2     | Record 2  | Sheet 3
| 3     | Record 3  | Sheet 2
| 4     | Record 4  | Sheet 2
| 5     | Record 5  | Sheet 3

Далее мне нужно взять эти ячейки, отфильтровать их по формуле, а затем поместить отфильтрованные строки на нужные листы («Лист 2» и «Лист 3»).

решение1

СОсновной листнравиться:

введите описание изображения здесь

ВЛист 2, клеткаА1входить:

=MATCH("Sheet 2",'Main Sheet'!C:C,0)

И вА2входить:

=IFERROR(MATCH("Sheet 2",INDEX('Main Sheet'!C:C,A1+1):INDEX('Main Sheet'!C:C,9999),0)+A1,"")

КопироватьА2вниз. ВЛист 2клеткаВ1входить:

=INDEX('Main Sheet'!A:A,$A1)

и скопируйте это вниз и поперек.

введите описание изображения здесь

Примечание столбцаАопределяет строку, из которой фильтруются данные, столбцыБиСявляютсяИдентификаториИмяиз этого ряда.

решение2

введите описание изображения здесь

Запишите следующую формулу на Листе 2:

В ячейке E242 напишите следующую формулу массива, завершите ввод нажатием Ctrl+Shift+Enter, а затем заполните ее:

{=IFERROR(INDEX(MainSheet!$A$242:$A$246,MATCH(0,IF($F$240=MainSheet!$C$242:$C$246,COUNTIF($E$241:$E241,MainSheet!$A$242:$A$246),""),0)),"")}

В ячейке F242 напишите следующую формулу массива, завершите ввод нажатием Ctrl+Shift+Enter, а затем заполните ее:

{=IFERROR(INDEX(MainSheet$B$242:$B$246, MATCH(0, IF($F$240=MainSheet!$C$242:$C$246, COUNTIF($F$241:$F241, MainSheet!$B$242:$B$246), ""), 0)),"")}

Напишите следующую формулу на листе 3:

В ячейке G242 напишите следующую формулу массива, завершите ввод нажатием Ctrl+Shift+Enter, а затем заполните ее:

{=IFERROR(INDEX(MainSheet!$A$242:$A$246,MATCH(0,IF($H$240=MainSheet!$C$242:$C$246,COUNTIF($G$241:$G241,MainSheet!$A$242:$A$246),""),0)),"")}

В ячейке H242 напишите следующую формулу массива, завершите ввод нажатием Ctrl+Shift+Enter, а затем заполните ее:

{=IFERROR(INDEX(MainSheet!$B$242:$B$246, MATCH(0, IF($H$240=MainSheet!$C$242:$C$246, COUNTIF($H$241:$H241, MainSheet!$B$242:$B$246), ""), 0)),"")}

Примечание. Отрегулируйте адрес ячейки в формуле в соответствии с вашими потребностями.

Связанный контент