Excel

Encontrar y recuperar valores perdidos

Find Retrieve Missing Values

Fórmula de Excel: buscar y recuperar valores perdidosFórmula genérica
= INDEX (complete, MATCH (TRUE, ISNA ( MATCH (complete, partial_expanding,0)),0))
Resumen

Para comparar dos listas y extraer valores faltantes de una lista a otra, puede utilizar una fórmula de matriz basada en ÍNDICE y PARTIDO . En el ejemplo que se muestra, el último valor de la lista B está en la celda D11. La fórmula en D12, copiada, es:

|_+_|

donde 'completo' es el rango con nombre B5: B15.



Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter.



vlookup de una hoja de cálculo a otra
Explicación

Trabajando desde adentro hacia afuera, el núcleo de esta fórmula es el interno Expresión COINCIDIR:

Excel encuentra la última celda no en blanco en la columna
|_+_|

Aquí, la función COINCIDIR se usa para comparar todos los valores 'completos' con la lista parcial. El rango con nombre 'completo' se usa para valores de búsqueda, y la lista parcial se usa como matriz de búsqueda. Observe, sin embargo, que la lista parcial se ingresa como un rango en expansión que termina 'una celda por encima' de la celda de fórmula. Esto permite que la lista parcial se expanda para incluir nuevos valores a medida que aparecen debajo de la lista original.



El resultado de MATCH es una matriz de números y errores # N / A, donde los números representan valores en la lista completa que existen en la lista parcial y los errores representan valores faltantes:

|_+_|

los Función ISNA se utiliza para convertir estos resultados en una matriz de valores VERDADERO y FALSO. En esta matriz, VERDADERO corresponde a los valores perdidos y FALSO corresponde a los valores existentes:

|_+_|

La función ISNA devuelve esta matriz a la exterior MATCH como la matriz de búsqueda. los Función COINCIDIR siempre devuelve la primera coincidencia encontrada, por lo que la coincidencia devolverá la posición (fila) del primer valor faltante encontrado. Este resultado se devuelve a ÍNDICE como el número de fila, con el rango con nombre 'completo' proporcionado como la matriz.



En la celda D12, el primer valor faltante encontrado es 'kiwi' en la fila 2, por lo que tenemos:

cómo calcular el porcentaje en la hoja de Excel
|_+_|

En D13, 'kiwi' ahora se incluye en la referencia de expansión, por lo que el primer valor que falta es 'pera':

|_+_|

Etcétera. Una vez que se hayan agregado todos los valores faltantes, la fórmula devolverá el error # N / A.

Autor Dave Bruns


^