Я ботаник и пытаюсь создать таблицу Excel, которая отфильтрует очень длинный список видов по местоположению и основной среде обитания.
Я создал основную таблицу с 200 строками для каждого вида, и вдоль столбцов сначала указаны местоположения (например, столбец b для Англии, столбец b для Шотландии и т. д.), а затем после местоположения столбцы — основные местообитания (например, столбец f для лесов, столбец g для лугов, столбец h для водных). Все ячейки содержат ДА или НЕТ, относящиеся к каждому виду, показывающие, в каком географическом местоположении и в какой среде обитания встречается вид.
Я хочу создать таблицу, которая спрашивает меня, где находится мой участок и какие среды обитания он поддерживает, и использует это, чтобы сузить круг видов, которые, скорее всего, там будут присутствовать. Например, если бы у меня был участок в Лондоне, на котором есть лес, луга и живая изгородь, я мог бы использовать фильтры, чтобы сократить список видов с 200+ до нескольких видов, надеюсь!
Любые идеи о том, как это сделать, были бы великолепны. Я прикрепил фотографию моего текущего рабочего листа.
решение1
Довольно просто
Вы разместили свою информацию в двух таблицах, расположенных рядом друг с другом, строки которых выровнены. Таблица 1 — это ваше местоположение, а Таблица 2 — это среда обитания. По сути, вы хотите создать список или номера строк, где в столбце соответствующего местоположения и в столбце соответствующего местообитания будет «да». На основе этого списка номеров строк вытащите соответствующие Виды.
Для этого сделаем несколько предположений:
- Лист, на котором находятся все ваши данные, называется SData.
- Данные выложены из A1:R200
- Строка 1 — строка заголовка.
- Столбцы C:I — это столбцы местоположения.
- Столбцы J:R — это места обитания.
- Местоположение, которое вы ищете, — B1.
- Место обитания, которое вы ищете, — D1.
- Ваш список будет отображаться в A2:B200
1) Определить местоположение столбца
=INDEX(SData!C2:I200,0,MATCH($B$1,SData!$C$1:$I$1,0)
2) Определить колонку «Ареал обитания»
=INDEX(SData!J2:R200,0,MATCH($D$1,SData!$J$1:$R$1,0)
3) Определите, какие строки содержат Да
И Habitat, и Location должны содержать yes, чтобы быть приемлемой строкой. Если cell = yes, то будет True. В математических операциях Excel обрабатывает True как 1, а False как 0. Поэтому, если обе ячейки в строке имеют значение yes, у вас будет 1*1=1. Если обе имеют значение FALSE, то у вас будет 0*0=0. А если одна ячейка имеет значение Yes, а другая — No, то результатом будет 1*0=1.
Итак, чтобы сделать это, будет использоваться агрегатная функция. Она будет выполнять операции, подобные массиву, для определенных функций, таких как 14 и 15, которые будут использоваться. Агрегат также может быть проинструктирован игнорировать ошибки. Поэтому мы настроим AGGREGATE так, чтобы он делил номер строки на проверку условия на «да». В результате вы либо получите номер строки, либо ошибку деления на 0, которую агрегат перевернет и проигнорирует эти результаты.
=INDEX(SData!A:A,AGGREGATE(14,6,ROW(SData!$A$2:$A$200)/((INDEX(SData!$C$2:$I$200,0,MATCH($B$1,SData!$C$1:$I$1,0))="yes")*(INDEX(SData!$J$2:$R$200,0,MATCH($B$1,SData!$J$1:$R$1,0))="yes")),ROW(A1)))
Поместите эту формулу выше в A2 и скопируйте вниз и поперек в B200. Проблема, которую я предвижу на данный момент, возникает, когда у вас заканчиваются строки, соответствующие вашим данным. В этой ситуации или если у вас нет ни одного результата, будет создана ошибка. Чтобы избежать ошибок в ваших ячейках, вы можете обернуть всю формулу выше в формулу ЕСЛИОШИБКА, чтобы отображалось "", когда результатом является ошибка.
=IFERROR(INDEX(SData!A:A,AGGREGATE(14,6,ROW(SData!$A$2:$A$200)/((INDEX(SData!$C$2:$I$200,0,MATCH($B$1,SData!$C$1:$I$1,0))="yes")*(INDEX(SData!$J$2:$R$200,0,MATCH($D$1,SData!$J$1:$R$1,0))="yes")),ROW(A1))),"")
Пример:
Таблица данных:
Лист результатов 1:
Лист результатов 2:
