Skip to content

Astuce 9 : Comparer deux listes de valeurs

30/10/2009

<maj>Voir aussi cet article, qui présente un outil complémentaire et extrêmement utile</maj>

Après la fonction SOMME sous Excel (et encore !), c’est la fonction NB.SI qui me sert le plus souvent sous Excel.

Je dois régulièrement comparer des listes de valeurs :

  • Listes d’ISSN issues de deux sources différentes
  • Listes d’ISBN
  • Listes de noms
  • etc.

pour y trouver quelles valeurs sont présentes dans une liste et pas dans l’autre.

Par exemple j’ai ceci :

Et je veux savoir quelles valeurs présentes dans la liste 1 ne sont pas dans la liste 2.

Pour l’exprimer à la manière d’Excel : pour chaque cellule de la colonne A, je vais examiner toute la colonne B.

C’est à ça que sert la fonction NB.SI, à qui je vais indiquer la colonne B comme « plage » et la cellule A2 comme « critère »

Remarque importante : de manière logique, on pense d’abord à la valeur recherchée. Dans Excel, il faut d’abord indiquer où cette valeur va être recherchée (la plage).

Donc en cellule C2, j’écrirai : =NB.SI(B:B;A2).

Ecrire « B:B » permet de définir toute la colonne B. Si seule une partie de cette colonne m’intéresse (les cellules B2 à B65), j’écrirai : « =NB.SI(B2:B65;A2)

Si Excel trouve la valeur de la cellule A2 dans la colonne B (280041459), il m’affiche 1. S’il ne la trouve pas, il m’affiche 0.

Voir la vidéo Voir la vidéo (ce sera certainement plus clair !)

Une fois que ma colonne C est pleine de 1 et de 0, j’utilise un filtre pour n’afficher que les 1 : je vois ainsi dans la colonne A la liste des nombres présents dans la colonne B.

Pour comparer des listes de noms

Si vous devez comparer des noms (c’est-à-dire des chaînes de lettres) au lieu de chiffres, pensez aux problèmes d’accents et de majuscules (la fonction NB.SI considère que « Lully » et « LULLY » sont deux choses différentes). Comparez plutôt deux listes qui sont toutes les deux en majuscules et sans accent, par exemple (rappel : pour passer un ensemble de mots en lettres capitales, c’est par là. Et si vos capitales restent accentuées en dépit de vos efforts, c’est par ici)

Sous le tableur d’OpenOffice

La fonction NB.SI est exactement la même : on indique la plage, puis le critère.

Une nuance : la colonne complète ne peut pas se désigner « B:B ». Il faut obligatoirement mettre : « B1:B600 » (ou B1:B30000). Ou alors il existe une syntaxe que je n’ai pas trouvée.

Autre utilisation possible de cette fonction : décompter des occurrences

Si vous avez une liste d’informations, avec pour un champ un nombre limité de valeur (par exemple : une liste de titres avec des années de publication), vous pouvez décompter le nombre d’occurrences de ces valeurs. La valeur (qui n’est donc pas une référence à une cellule) est alors mise entre guillemets.

Publicités
6 commentaires
  1. B. Majour permalink
    31/10/2009 16:08

    Pour ceux qui souhaitent travailler indifféremment avec les ISBN 10 et ISBN 13, voici la formule pour éliminer le 978 et la valeur de contrôle en fin.

    l’ISBN étant en A2
    on copie-colle la formule suivante en B2
    =SI(NBCAR(A2)=10;STXT(A2;1;9);STXT(A2;4;9))

    Et on obtient l’ISBN en short. (ou en bermuda 🙂 )
    soit les 9 chiffres capitaux.

    On peut alors utiliser le NB.SI pour comparer les ISBN en shorts.
    Sans se préoccuper plus avant de savoir si leurs mensurations sont à 10 ou 13 chiffres.

    Bien cordialement
    B. Majour (qui vient de comparer, sur Excel, sa Pile d’Acquisition « à trouver » avec son fonds, grâce à NB.SI… Merci Lully.)

  2. 31/10/2009 16:29

    @B. Majour : Etiez-vous là quand je rédigeais mon billet ? Aurais-je laissé ma webcam branchée 😉 ? Car quand j’ai voulu utiliser des listes de nouveautés pour mes comparaisons en vidéo, je n’ai eu sous la main qu’une liste d’ISBN 13 d’un côté, et une liste d’ISBN 10 de l’autres.
    Plutôt que de rentrer dans des détails sur la fonction STXT et la structure d’un ISBN, j’ai lâchement renoncé… (et j’ai pris n’importe quoi).

    Mais puisque vous vous ête lancé, je précise tout de même (sauf si tout le monde sait comment sont structuré des ISBN 10 et 13 ?)
    Un ISBN 10, ce sont 9 chiffres, et un chiffre qui est le résultat d’une addition-multiplication sur les 9 premiers, et sert de clé de contrôle.
    Un ISBN 13, c’est : « 978 » suivi de 9 chiffres, suivi d’une clé de contrôle portant sur les 12 premiers chiffres.

    Pour un même ouvrage, les ISBN 10 et 13 auront donc de différent les 3 premiers chiffres de l’ISBN 13, et la clé de contrôle.
    Exemple :
    286253416-1
    978-286253416-9

    La fonction STXT (sous-texte, pour une sous-chaîne de caractères) utilisée dans la formule de B. Majour sert à ne prendre dans les ISBN 10 que les 9 premiers chiffres, et dans les ISBN 13 que les chiffres 4 à 12.

  3. B. Majour permalink
    01/11/2009 12:28

    Bonjour

    Eh bien, comme tu le précises Lully, dans ton billet suivant « Pédagogie,
    geeks et technophobes », ma douchette me retourne des ISBN 10, lorsque je suis obligé d’en taper à 13 chiffres quand elle ne veut rien lire.

    La seule solution de comparaison facile, c’est de ne prendre que le « short » de l’ISBN 🙂
    (Au passage, ça veut dire 1 seul petit million de livres référençables)

    STXT (texte dans la cellule; premier caractère désiré ; longueur)
    En tâtonnant un peu pour trouver le premier caractère du texte que l’on veut extraire (au lieu de les compter à la main ;-), on arrive à :

    =STXT(« je veux le short de l’ISBN »;12;5) donne => short
    =STXT(« je veux le short de l’ISBN »;23;4) donne => ISBN

    =STXT(« je veux le short de l’ISBN »;12;5) &  »  » & STXT(« je veux le short de l’ISBN »;23;4)
    donne => short ISBN

    Une dernière précision sur le NB.SI

    Bien sûr, on peut l’étendre à plusieurs colonnes, ou à des plages de cellules à définir à la souris.

    Bien cordialement
    B. Majour

Trackbacks

  1. Astuce : pointer une livraison de libraire « Bertrand Calenge : carnet de notes
  2. AtoZ – Quelques choix simples « Bibliothèques [reloaded]
  3. Comparer deux listes de valeurs « Bibliothèques [reloaded]

Commentaires fermés

%d blogueurs aiment cette page :