Как использовать Excel для составления таблицы, показывающей, какие виды растений могут присутствовать в зависимости от места и среды обитания?

Question

Я ботаник, и я пытаюсь создать таблицу Excel, которая фильтрует очень длинный список видов в зависимости от местоположения и среды обитания.

Я создал основную таблицу с 200 строками для каждого вида, и вдоль столбцов сначала располагаются местоположения (например, столбец b для Англии, столбец b для Шотландии и т.д.), А затем после местоположения столбцы представляют собой основные места обитания (то есть столбец f для лесных массивов). , столбец g для пастбищ, столбец h для водных). Все клетки содержат ДА или НЕТ, относящиеся к каждому виду, показывающие, в каком географическом положении и в какой среде обитания этот вид встречается.

Я хочу составить таблицу, которая спрашивает меня, где находится мой участок и какие места обитания он поддерживает, и использует это, чтобы сузить круг видов, которые могут присутствовать. Например, если бы у меня был сайт в Лондоне, который поддерживал лесные массивы, луга и живую изгородь, я мог бы использовать фильтры, чтобы сократить список видов с 200+, надеюсь, до нескольких видов!

Любые идеи о том, как произвести это было бы здорово. Я приложил фотографию моего текущего рабочего листа.

Answer 1

Довольно просто

Вы разместили свою информацию по существу в двух параллельных таблицах с выровненными рядами. Таблица 1 - ваше местоположение, а Таблица 2 - ваша среда обитания. По сути, вы хотите создать список или номера строк, в которых есть «да» в соответствующем столбце местоположения и «да» в соответствующем столбце среды обитания. Основываясь на этом списке номеров строк, вытащите соответствующий вид.

Для этого сделаем несколько предположений:

• Лист, где находятся все ваши данные, называется SData
• Данные выложены из A1:R200
• Строка 1 - строка заголовка
• Столбцы C: я столбцы местоположения
• Столбцы 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, чтобы быть приемлемой строкой. Если ячейка = да, будет True. В математических операциях Excel рассматривает True как 1 и False как 0. Так что, если обе ячейки подряд - да, у вас будет 1 * 1 = 1. Если оба значения ЛОЖЬ, то у вас будет 0 * 0 = 0. И если для одной ячейки установлено значение «Да», а для другой - «Нет», то результатом будет 1 * 0 = 1.

Таким образом, для этого будет использоваться агрегатная функция. Он будет выполнять операции с массивами для определенных функций, таких как 14 и 15, которые будут использоваться. Агрегат также может быть проинструктирован игнорировать ошибки. Таким образом, мы установим AGGREGATE, чтобы разделить номер строки на проверку состояния для yes. В результате вы либо получите номер строки, либо погрешность деления на 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, чтобы при появлении ошибки отображалось «».

=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: