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 |_+_| Sommaire

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 RENCONTRE . Dans l'exemple illustré, la dernière valeur de la liste B se trouve dans la cellule D11. La formule en D12, copiée, est :





= INDEX (complete, MATCH (TRUE, ISNA ( MATCH (complete, partial_expanding,0)),0))

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

Remarque : il s'agit d'un formule matricielle et doit être saisi avec control + shift + enter.





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 Correspondre à l'expression :

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

Ici, la fonction MATCH est utilisée pour comparer toutes les valeurs « complètes » avec la liste partielle. La plage nommée « complet » 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 en expansion 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 :

 
 ISNA ( MATCH (complete,$D:D11,0)

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

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

La fonction ISNA renvoie ce tableau au extérieur MATCH comme tableau de recherche. Les Fonction MATCH renvoie toujours la première correspondance trouvée, donc match renvoie 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 'complète' 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
 
{FALSETRUEFALSEFALSETRUEFALSEFALSEFALSETRUEFALSETRUE}

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

 
= INDEX (complete,2) // returns 'kiwi'

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

Auteur Dave Bruns


^