Soy botánico y estoy intentando producir una tabla de Excel que filtre una lista muy larga de especies según su ubicación y hábitat básico.
He creado una tabla maestra con 200 filas para cada especie y a lo largo de las columnas están primero las ubicaciones (por ejemplo, la columna b para Inglaterra, la columna b para Escocia, etc.) y luego, después de la ubicación, las columnas son los hábitats básicos (es decir, la columna f para los bosques). , columna g para pastizales, columna h para acuáticos). Todas las celdas contienen un SÍ o un NO relevante para cada especie que muestra en qué ubicación geográfica y en qué hábitat se encuentra la especie.
Quiero producir una tabla que me pregunte dónde está mi sitio y qué hábitats alberga y use esto para limitar qué especies es probable que estén presentes. Por ejemplo, si tuviera un sitio en Londres que albergara bosques, pastizales y un seto, podría usar los filtros para reducir la lista de especies de más de 200 a, con suerte, un puñado de especies.
Cualquier idea sobre cómo producir esto sería genial. Adjunto una imagen de mi hoja de trabajo actual.
Respuesta1
Bastante sencillo
Ha presentado su información esencialmente en dos tablas una al lado de la otra con sus filas alineadas. La Tabla 1 es su ubicación y la Tabla 2 es su hábitat. Básicamente, desea generar una lista o números de fila donde haya un sí en la columna de ubicación coincidente y un sí en la columna de hábitat coincidente. Según esa lista de números de fila, extraiga la Especie correspondiente.
Para hacer eso, hagamos algunas suposiciones:
- La hoja donde se encuentran todos sus datos se llama SData
- Los datos se presentan desde A1:R200.
- La fila 1 es una fila de encabezado
- Columnas C:Soy columnas de ubicación
- Las columnas J:R son ubicaciones de hábitat.
- La ubicación que buscas es B1
- El hábitat que buscas es D1
- Su lista se mostrará en A2:B200
1) Determinar la columna de ubicación
=INDEX(SData!C2:I200,0,MATCH($B$1,SData!$C$1:$I$1,0)
2) Determinar la columna de hábitat
=INDEX(SData!J2:R200,0,MATCH($D$1,SData!$J$1:$R$1,0)
3) Determinar qué filas contienen Sí
Tanto Hábitat como Ubicación deben contener sí para ser una fila aceptable. Si celda = sí será Verdadero. En operaciones matemáticas, Excel trata Verdadero como 1 y Falso como 0. Entonces, si ambas celdas en una fila son sí, tendrá 1*1=1. Si ambos son FALDOS entonces tendrás 0*0=0. Y si una celda es Sí y la otra es No, el resultado será 1*0=1.
Entonces, para hacer esto, se utilizará la función agregada. Realizará operaciones similares a matrices para ciertas funciones como 14 y 15 que se utilizarán. También se puede indicar al agregado que ignore los errores. Por lo tanto, configuraremos AGREGAR para dividir el número de fila por la verificación de condición para sí. Como resultado, terminará con el número de fila o con un error de división por 0, cuyo agregado se dará la vuelta e ignorará esos resultados.
=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)))
Coloque la fórmula anterior en A2 y cópiela hacia abajo y hacia arriba hasta B200. El problema que preveo hasta ahora es cuando te quedas sin filas que coincidan con tus datos. En esta situación o si no obtuviera ningún resultado, se crearía un error. Para evitar resultados de error en sus celdas, puede envolver toda la fórmula anterior en una fórmula IFERROR para que se muestre "" cuando el resultado sea un error.
=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))),"")
Ejemplo:
Tabla de datos:
Hoja de resultados 1:
Hoja de resultados 2:
