¿Cómo utilizar EXCEL para implementar la recuperación de múltiples condiciones?

para mejorar la velocidad de consulta Cómo implementar consultas de múltiples condiciones mediante indexación y coincidencia

1. Columna de casos

1. Como se muestra en la imagen, ingrese la fórmula

=INDEX(B2:D8,MATCH(F3amp;G3,B2:B8amp;C2:C8,0),MATCH(D1,B1 :D1,0 ))

Función de índice: devuelve el valor del área de la tabla de datos o una referencia al valor (el valor se devuelve aquí)

Función de coincidencia: devuelve el área donde se encuentra el valor objetivo Posición (regrese filas y columnas aquí)

Para obtener más información sobre el uso de funciones de índice y funciones de coincidencia, puede ver mi experiencia Cómo usar funciones de índice y cómo usar funciones coincidentes en Excel

2. Debido a que la fórmula es una fórmula matricial, presione Ctrl Mayús Intro

3. Ingrese la fórmula =INDEX($B$2:$D$8,MATCH. (F3 amp; G3,$ B$2: $B$8 amp; $C$2: $C$8, 0), MATCH ($D$1, $B$1: $D$1, 0))

Tenga en cuenta la referencia absoluta y la referencia relativa. De lo contrario, se producirá un error al copiar la fórmula en el menú desplegable

2. Explicación de la fórmula

4. Como se muestra en la figura, la función de índice busca el área B2 donde se encuentra el volante Mercedes-Benz: la intersección del número de fila y el número de columna en D8 devuelve el valor de búsqueda

La función de coincidencia devuelve el número de fila y la columna. número

MATCH(F3amp; G3. , B2: B8; C2: C8, 0)

La fórmula explica cómo conectar el volante de Mercedes-Benz a través del conector amp; y luego regresar a la posición 4.

5.MATCH(D1, B1: D1, 0)

Esta fórmula indica el número de columnas donde se encuentra el valor de retorno. Devuelve 3. Cómo utilizar fórmulas EXCEL para lograr un filtrado preciso con múltiples condiciones

Cree un área de condiciones fuera del área de datos. El título del área de datos es el nombre del campo, luego enumere las condiciones del filtro y enumere cada relación. la misma fila, o enumere cada relación en filas diferentes, luego seleccione "Filtrado avanzado" en la opción "Datos". En la opción "Datos" en "Filtrado avanzado", cambie el "Rango de datos" a "Rango de lista", cambie el rango de condiciones a "Rango de criterios", marque "Copiar resultados del filtro a otras ubicaciones" y especifique * **El La posición es la posición inicial. Una vez determinada, se puede completar el filtrado avanzado.

Métodos/Pasos

1. EXCEL en sí proporciona funciones de filtrado precisas limitadas, que solo pueden basarse en el orden de la primera letra del contenido de la celda. o el orden del valor numérico; este artículo presenta cómo usar fórmulas combinadas IF, AND, OR para lograr un filtrado preciso de datos

2. La función de filtrado preciso proporcionada por EXCEL es limitada y puede. basarse solo en la primera letra del contenido de la celda o el tamaño numérico. Organizar en orden. Este artículo presenta cómo usar fórmulas combinadas IF, AND, OR para lograr un filtrado preciso de datos.

3. El preciso La función de filtrado proporcionada por EXCEL es limitada y solo puede basarse en el orden de la primera letra del contenido de la celda o el valor numérico ordenado por tamaño.

La fórmula de combinación IF AND implementa el filtrado condicional:

Como se muestra en la lista, hay nueve estudiantes en cada uno de los expedientes académicos de los cinco cursos, entre los cuales hay al menos un estudiante reprobado. La fórmula es: =. IF ( AND (D2gt; =60, F2gt; =60, H2gt; =60, J2gt; =60, L2gt; =60), "todo pasó", "falló")

AND la sintaxis es: AND(Fórmula 1, Fórmula 2, Fórmula 3...)

Siempre que se cumplan todas las fórmulas entre paréntesis, el valor total de la fórmula AND es verdadero, de lo contrario, cualquiera; de las fórmulas es Falso, el valor total es falso; la relación entre fórmulas es suma lógica;

Sintaxis IF: IF (fórmula, valor 1, valor 2)

El valor 1 es el valor de salida de la fórmula El valor verdadero de, el valor 2 es el valor falso en el valor de salida de la fórmula;

Entonces la fórmula en este momento es:

IF (fórmula, valor 1, valor 2)

El valor 1 es el valor verdadero en el valor de salida de la fórmula y el valor 2 es el valor falso en el valor de salida de la fórmula: =IF(AND(D2gt;=60,F2gt;= 60,H2gt;=60,J2gt;=60, L2gt;=60), "todo pasó", "todo falló")

Representa los valores de las cinco celdas de D2/F2/H2 /J2/L2 (puntuaciones de las cinco materias), todas Si es mayor o igual a 60 se considera "aprobado todo", en caso contrario se considera "reprobado"

3. Utilice la fórmula combinada SI O para lograr el filtrado condicional:

En la lista que se muestra en la figura, 9 estudiantes tienen calificaciones excelentes en cada uno de los cinco cursos, y entre ellos se selecciona al menos un estudiante con calificaciones excelentes. , la fórmula es: =SI (O (D2gt; =95, F2gt; =95, H2gt; =95, J2gt; =95, L2gt; =95), "Asignaturas especiales", "No hay materias destacadas")

Sintaxis OR: OR (fórmula 1, fórmula 2, fórmula 3...)

Siempre que se cumpla una fórmula entre paréntesis, el valor general de la fórmula OR es verdadero cuando; todos Cuando todos los valores de la fórmula son falsos, el valor general es falso, la relación entre las fórmulas es lógica O;

Sintaxis IF: IF (fórmula, valor 1, valor 2)

El valor 1 es el valor de salida cuando el valor de la fórmula es verdadero, el valor 2 es el valor de salida cuando el valor de la fórmula es falso;

Entonces en este momento, la fórmula: = SI (O (D2gt; =95, F2gt ;=95, H2gt; =95, J2gt; =95, L2gt; =95), "materias especiales", "no asignaturas destacadas")

Representa las cinco categorías de D2/F2/H2/J2/L2 Los valores de las celdas (las puntuaciones de estas cinco materias), siempre que una puntuación sea superior a 95, demuestra que el estudiante tiene una "materia especial", de lo contrario es " ningún tema pendiente"

4. La fórmula combinada SI Y O cumple las condiciones de filtrado:

Como se muestra en la figura, enumere los expedientes académicos de cinco cursos de cada uno de los 9 estudiantes y filtre al menos uno de los cinco cursos.

Descarte a los estudiantes que tienen al menos una calificación reprobatoria y al menos un estudiante de "materia parcial" con calificaciones excelentes: la fórmula es la siguiente: =IF (AND (OR (D2lt;=60,F2lt;=60,H2lt;=60 ,J2lt;= 60, L2lt; = 60), O ( D2gt; = 95, F2gt; = 95, H2gt; = 95, J2gt; = 95, L2gt; = 95)), "Ciencia Parcial", "Equilibrado")

La sintaxis de AND es: AND(Fórmula 1, Fórmula 2, Fórmula 3...)

Siempre que se cumplan todas las fórmulas entre paréntesis, el valor total de la fórmula AND es verdadera; de lo contrario, si alguna fórmula es falsa, el valor total es falso, existe una relación lógica entre las fórmulas;

La sintaxis OR es: OR (Fórmula 1, Fórmula 2, Fórmula 3). ...)

Siempre que se cumpla una fórmula entre paréntesis, el valor general de la fórmula OR es verdadero; cuando los valores de todas las fórmulas son falsos, el valor general es falso; la relación entre las fórmulas es lógica O;

Sintaxis IF: IF (fórmula, valor 1, valor 2)

El valor 1 es el valor de salida de la fórmula, el valor 1 es el verdadero valor, el valor 2 es el valor de salida de la fórmula y el valor 2 es el valor falso;

Entonces la fórmula en este momento es: =IF(AND(OR(D2lt; =60, F2lt; =60, H2lt; =60, J2lt; =60, L2lt; =60), O( D2gt;=95,F2gt;=95,H2gt;=95,J2gt;=95,L2gt;=95)), "sesgo ", "balance ")

Primero, OR(D2lt;=60 , F2lt;=60, H2lt;=60, J2lt;=60, L2lt;=60), los valores de los cinco celdas de D2/F2/H2/J2/L2 (las puntuaciones de las cinco materias), siempre que haya Si una puntuación es inferior a 60, el valor OR es verdadero;

2. OR (D2gt ; =95, F2gt; =95, H2gt; =95, J2gt; =95, L2gt; =95), D2/ Para los valores de las cinco celdas F2/H2/J2/L2 , siempre que una puntuación supere 95, el valor OR es verdadero;

AND (OR fórmula 1, OR fórmula 2) Significa que las puntuaciones de los estudiantes en cada materia deben satisfacer dos fórmulas OR al mismo tiempo tiempo, es decir, una puntuación es inferior a 60 puntos y una puntuación es superior a 95 puntos. En este momento, utilizamos la declaración IF para definir al estudiante como una "asignatura parcial". Ayuda, Cómo utilizar VBA para implementar múltiples. consulta de condición

El algoritmo está relacionado con la cantidad de datos, el tipo de datos y los requisitos especiales. Generalmente, puede considerar usar objetos de diccionario. El algoritmo de consulta de diccionario es el más simple. Diccionario para consultar datos. Lo importante es utilizar un diccionario para consultar los datos. Los algoritmos de consulta de objetos y diccionarios son los más simples y rápidos. Hay dos conjuntos de implementaciones de diccionario para consultas de múltiples condiciones. Uno es establecer un índice conjunto, KEY1|KEY2|KEY3-gt; el otro es usar un diccionario multicapa KEY1-gt; ; KEY3-gt; VALOR estructura de datos; VALOR Cómo implementar la función BUSCARV de Excel de búsqueda multicondicion

Función BUSCARV

Encuentra el valor especificado en la primera columna de la tabla numérica o matriz. y luego devuelve la fila de la tabla numérica o matriz con el valor especificado.

La "matriz" aquí puede entenderse como un área en la tabla.

Número de columna de la matriz: la "primera columna" de la matriz, es decir, la primera columna del área. El lado derecho de esta columna es la segunda columna y la columna 3... Suponga que el área de la matriz es B2:E10. , entonces, B2:B10 es la primera columna, C2:C10 es la segunda columna...

Sintaxis:

BUSCARV (valor de búsqueda, rango, número de columna, valor lógico)

"Buscar valor": El valor que se encontrará en la primera columna del array, que puede ser un valor numérico, una referencia o una cadena de texto.

"Región": La región donde se encuentra la matriz, como "B2:E10", también puede usar una referencia a la región o el nombre de la región, como una base de datos o una lista de datos.

"Número de columna": es decir, desea que el rango (matriz) devuelva el número de columna del valor coincidente. Para 1, devuelva el valor de la primera columna, para 2, devuelva el valor de. la segunda columna, y así sucesivamente si el número de columna es menor que 1 y la función BUSCARV devuelve el valor de error #¡VALOR! Número de columnas, la función BUSCARV devuelve el valor de error #REF.

"Valor lógico": VERDADERO o FALSO. Especifica si la función BUSCARV devuelve una coincidencia exacta o aproximada. Si es VERDADERO o se omite, se devuelve un valor de coincidencia aproximado, es decir, si no se encuentra una coincidencia exacta, se devuelve el valor máximo menor que el "valor de búsqueda" si el "valor lógico" es FALSO, la función BUSCARV devuelve un valor de coincidencia exacta; . Si no se puede encontrar la búsqueda, se devuelve el valor de error #N/A. Si el "valor de búsqueda" es texto, el "valor lógico" debe ser FALSO. Además:

- Si el "Valor de búsqueda" es menor que el valor mínimo en la primera columna de "Área", la función BUSCARV devuelve el valor de error #N/A.

- Si la función BUSCARV no puede encontrar el "valor de búsqueda", entonces el "valor lógico" debe ser FALSO.

Supongamos que los precios unitarios de venta de trigo, arroz, maíz, maní y otros productos agrícolas se almacenan en la Hoja 1:

A B

1 unidad de nombre de producto agrícola precio

2 Trigo 0.56

3 Arroz 0.48

4 Maíz 0.39

5 Maní 0.51

.. .... ........................................

100 soja 0,45

La Hoja2 es una lista de ventas y el contenido que se completa es diferente cada vez: después de ingresar el nombre y la cantidad de productos agrícolas en la Hoja2, el precio unitario y el volumen de ventas se generan automáticamente en función de los datos en la Hoja1. Establezca la siguiente tabla para la Hoja 2:

A B C D

1 Nombre de los productos agrícolas Cantidad Precio unitario Cantidad

2 Arroz 1000 0,48 480

3 Maíz 2000 0,39 780

............................. ................................. .............

Ingresa la fórmula en la celda D2:

=C2*B2;

In Ingresa la fórmula en la celda C2:

=BUSCARV(A2, Hoja1!A2 : B100, 2, FALSO).

Expresarlo en texto, es decir, en el área A2: B100 de la Hoja1 Encuentra el valor de la celda A2 en la primera columna de la tabla Hoja2 Después de la. Se completa la verificación, regrese a la fila del valor de la segunda columna.

De esta forma, cuando el nombre de la celda A2 en la Hoja2 cambie, el precio unitario en C2 seguirá automáticamente el cambio.

Por supuesto, si el valor unitario en la Hoja1 cambia, el valor correspondiente en la Hoja2 también cambiará.

Las fórmulas en otras celdas se pueden escribir usando el método de relleno.

Precauciones al usar la función BUSCARV

Hablando de la función BUSCARV, creo que todos pueden usarla y usarla con mucha habilidad. Sin embargo, hay algunos detalles a los que todo el mundo debería prestar atención.

Primero, la sintaxis de BUSCARV

La sintaxis completa de la función BUSCARV es la siguiente:

BUSCARV (valor_buscado, matriz_tabla, num_índice_col, búsqueda_rango)

1. Los cuatro parámetros entre paréntesis son obligatorios. El último parámetro range_lookup es un valor lógico, generalmente ingresamos 0 o Falso, de hecho, también puedes ingresar 1 o verdadero; La primera es una búsqueda completa, y si no la encuentra, devolverá el valor de error #N/A; la segunda es la misma búsqueda, y si no la encuentra, buscará un valor muy cercano. no puede encontrarlo, tendrá que devolver el valor de error #N/A. En realidad, esto no tiene ningún significado práctico para nosotros, simplemente satisface la curiosidad de amigos que están interesados ​​en experimentarlo.

2.Lookup_value es un parámetro muy importante, puede ser un valor numérico, una cadena de texto o una dirección de referencia. Normalmente utilizamos direcciones de referencia. Con este parámetro, hay dos recordatorios especiales:

A) La categoría de formato de celda de la dirección de referencia debe ser consistente con la categoría de formato de celda a buscar. De lo contrario, a veces, cuando vea información claramente, simplemente. agarrarlo. No puedo vivir. Especialmente la dirección de referencia cuyo valor es un número es la más obvia. Si la categoría de formato de celda de la búsqueda es texto, aunque parece 123, no puede detectar nada.

Los datos que no son de entrada en la categoría de formato deben determinarse primero. Si se ingresan datos y se descubre que el formato es inconsistente, será demasiado tarde si desea detectarlos. para volver a ingresarlo.

B) El segundo recordatorio son consejos fáciles y prácticos que creo que mucha gente ya los conoce. Cuando usamos direcciones de referencia, a veces necesitamos corregir el valor de valor de búsqueda en una celda y usar el método desplegable (o copiar) para agregar la función a una nueva celda. Aquí se usa el símbolo "$". un símbolo de acción fija. Por ejemplo, si siempre quiero capturar datos en formato D5, puedo configurar D5 en esto: $D$5. Luego, no importa cómo baje o copie, la función siempre capturará los datos del valor D5.

3.Table_array es el rango de búsqueda, col_index_num es el número de columnas en el rango. col_index_num no puede ser menor que 1 y, de hecho, igual a 1 no tiene ningún uso práctico. Si recibe un error como #REF! value, es posible que el valor de col_index_num exceda el número total de campos en el rango.

II.BUSCARV manejo de valores de error.

Todos sabemos que si no se pueden encontrar los datos, la función siempre devolverá un valor de error como #N/A, lo cual es realmente muy útil. Por ejemplo, si queremos manejarlo así: si se encuentra, devuelve el valor correspondiente, si no se encuentra, lo estableceré automáticamente en 0, entonces la función se puede escribir así:

=if(iserror( vlookup(1,2,3,0))=true,0,vlookup(1,2,3,0))

El significado de esta oración es el siguiente: Si el valor devuelto por la función BUSCARV es un valor de error (no se encontraron datos), entonces es igual a 0; de lo contrario, es igual al valor devuelto por la función BUSCARV (es decir: 1, 2, 3, 0). es decir, el valor correspondiente encontrado).

Aquí también se utilizan dos funciones.

La primera es la función iserror. Su sintaxis es iserror (valor), que determina si el valor entre paréntesis es un valor de error. Si es así, es igual a verdadero; de lo contrario, es igual a falso.

La segunda es la función if, que también es una función de uso común. La explicaré en detalle cuando tenga la oportunidad. Su sintaxis es if (juicio condicional, resultado 1, resultado 2). Por ejemplo: = if (D2="", "vacío", "hay algo"), lo que significa que debido a que la celda D2 es un valor nulo, se mostrará el texto "vacío", en caso contrario, se mostrará el texto "hay algo". "algo". (Parece sencillo, ¿verdad? De hecho, así lo juzga el programa).

III. Procesamiento de archivos de hoja de trabajo, incluida la función BUSCARV.

En términos generales, si la hoja de trabajo que contiene la función BUSCARV captura datos de otro archivo, el archivo suele ser más grande. Especialmente si está trabajando con un archivo que es inherentemente muy grande, abrir y guardar el disco cada vez puede ser una experiencia estresante.

No hay forma de comprimir archivos para acelerar su apertura y guardado. Aquí os dejo un poco de mi experiencia.

En la hoja de trabajo, haga clic en "Herramientas" - "Opciones" - "Cálculo", desmarque "Actualizar referencias remotas" y "Almacenar enlaces externos" y luego guarde el archivo.

Aquí tienes las instrucciones detalladas.

1. Una hoja de trabajo con función BUSCARV guardará una copia del archivo vinculado externamente cada vez que se guarde. Por lo tanto, la función BUSCARV captura los mismos valores incluso si la hoja de trabajo se abre por separado.

2. Cuando abra la hoja de trabajo, Microsoft le preguntará si desea actualizar la referencia remota. Esto significa que si desea conectarse al archivo externo más reciente para que su función BUSCARV pueda capturar los valores más recientes. Si tienes suficiente paciencia, inténtalo.

3. Para entender esto, debemos saber que cada vez que la hoja de trabajo que contiene la función BUSCARV se abre por separado, tomará los valores del archivo externo, pero solo los valores que guardamos por última vez. en el valor del disco. Si desea vincular al último valor, el archivo externo debe abrirse al mismo tiempo.

Entiendo las funciones de BUSCARV, pero se limitan a estas. Si tiene alguna buena experiencia o pregunta, no dude en preguntarla y discutirla juntos.