Exceller

Rechercher et récupérer les valeurs manquantes

Find Retrieve Missing Values

Formule Excel: rechercher et récupérer les valeurs manquantesFormule générique
= INDEX (complete, MATCH (TRUE, ISNA ( MATCH (complete, partial_expanding,0)),0))
Résumé

Pour comparer deux listes et extraire les valeurs manquantes d'une liste à l'autre, vous pouvez utiliser une formule matricielle basée sur INDICE et CORRESPONDRE . Dans l'exemple illustré, la dernière valeur de la liste B se trouve dans la cellule D11. La formule en D12, recopiée, est:

 
= INDEX (complete, MATCH (TRUE, ISNA ( MATCH (complete,$D:D11,0)),0))

où 'complet' est le plage nommée B5: B15.



Remarque: il s'agit d'un formule matricielle et doit être entré avec Ctrl + Maj + Entrée.



comment calculer le total dans excel
Explication

Travaillant de l'intérieur vers l'extérieur, le cœur de cette formule est le intérieur Expression MATCH:

comment faire un tableau croisé dynamique dans Excel
 
 ISNA ( MATCH (complete,$D:D11,0)

Ici, la fonction MATCH est utilisée pour comparer toutes les valeurs «complètes» avec la liste partielle. La plage nommée «complete» est utilisée pour les valeurs de recherche et la liste partielle est utilisée comme tableau de recherche. Notez, cependant, que la liste partielle est entrée comme un gamme élargie qui se termine «une cellule au-dessus» de la cellule de formule. Cela permet à la liste partielle de s'étendre pour inclure de nouvelles valeurs telles qu'elles apparaissent sous la liste d'origine.



Le résultat de MATCH est un tableau de nombres et d'erreurs # N / A, où les nombres représentent les valeurs de la liste complète qui existent dans la liste partielle et les erreurs représentent les valeurs manquantes:

 
{1#N/A23#N/A456#N/A7#N/A}

Le Fonction ISNA est utilisé pour convertir ces résultats en un tableau de valeurs TRUE et FALSE. Dans ce tableau, TRUE correspond aux valeurs manquantes et FALSE correspond aux valeurs existantes:

 
{FALSETRUEFALSEFALSETRUEFALSEFALSEFALSETRUEFALSETRUE}

La fonction ISNA renvoie ce tableau au extérieur MATCH comme tableau de recherche. Le Fonction MATCH renvoie toujours la première correspondance trouvée, donc match renverra la position (ligne) de la première valeur manquante trouvée. Ce résultat est renvoyé à INDICE comme numéro de ligne, avec la plage nommée «complete» fournie comme tableau.



Dans la cellule D12, la première valeur manquante trouvée est 'kiwi' à la ligne 2, nous avons donc:

quelle est la référence de cellule dans Excel
 
= INDEX (complete,2) // returns 'kiwi'

Dans D13, 'kiwi' est maintenant inclus dans la référence en expansion, donc la première valeur manquante est 'pear':

 
= INDEX (complete,5) // returns 'pear'

Etc. Une fois que toutes les valeurs manquantes ont été ajoutées, la formule renverra l'erreur # N / A.

Auteur Dave Bruns


^