Ce post décrit comment corriger les noms intervertis dans une formule de comptage continu sur Google Sheets.
Une formule de comptage continu classique considérera les noms intervertis comme une nouvelle entrée et renverra un nouveau comptage.
Par exemple, si le nom « Denise Young » apparaît deux fois et « Young Denise » apparaît trois fois, les numéros d’occurrence corrects seront {1, 2, 3, 4, 5}. Le résultat incorrect serait {1, 2, 1, 2, 3}. Veuillez consulter la Figure 1 ci-dessous.
Figure 1
Dans l’exemple ci-dessus, j’ai utilisé une formule de tableau dans la cellule D2 qui corrige les noms intervertis tout en renvoyant le comptage continu correct.
Note : Vous devez comprendre un problème qui peut se poser lors de l’utilisation d’une telle formule. Même si « Essie Phelps » et « Phelps Essie » sont deux noms différents, la formule les traitera comme étant identiques. C’est parce que la formule les considérera comme des noms inversés.
Cela étant dit, passons à la formule et à son explication.
Comment corriger les noms intervertis dans le comptage continu sur Google Sheets
Nous utiliserons une formule LAMBDA pour renvoyer le comptage continu correct après avoir corrigé les noms intervertis.
Voici la formule :
=ARRAYFORMULA(MAP( B2:B,C2:C, LAMBDA(first,last,IF(first&last="",,SUM( COUNTIFS(B2:first&C2:last,{first&last,last&first}) ))) ))
Où :
- B2:B est la plage de cellules qui contient les prénoms.
- C2:C est la plage de cellules qui contient les noms de famille.
- B2 est la première cellule dans la plage de cellules qui contient les prénoms.
- C2 est la première cellule dans la plage de cellules qui contient les noms de famille.
La formule ci-dessus est une formule de tableau, il vous suffit donc de l’insérer dans la cellule D2. Elle se développera automatiquement vers le bas. Cependant, si des valeurs en bas de la colonne empêchent la formule de se développer, elle renverra l’erreur #REF!.
Comment la formule ci-dessus corrige-t-elle les noms intervertis et renvoie-t-elle le comptage continu correct ? Laissez-moi vous expliquer étape par étape.
Explication de la formule
Nous commencerons par écrire la formule non-array qui corrige les noms intervertis dans le comptage continu.
1. Formule non-array pour corriger les noms intervertis dans le comptage continu
L’utilisation de l’opérateur OR dans plusieurs colonnes de la fonction COUNTIFS est la clé de la formule qui corrige les noms de famille et les prénoms inversés dans le comptage continu des occurrences sur Google Sheets.
Voici la formule :
=ARRAYFORMULA(SUM( COUNTIFS($B$2:B2&$C$2:C2,{B2&C2,C2&B2}) ))
Si vous insérez cette formule dans la cellule D2 et faites glisser la poignée de remplissage vers le bas jusqu’à la cellule C2, vous obtiendrez le comptage continu après avoir corrigé les noms de famille et les prénoms inversés.
Figure 2
Alors, apprenons d’abord la formule COUNTIFS en détail. Ensuite, nous pourrons facilement la convertir en une formule de tableau qui se développe.
La syntaxe de la fonction COUNTIFS est :
COUNTIFS(plage_critères1, critère1, [plage_critères2, critère2, ...])
La formule COUNTIFS suivante compte la plage $B$2:B2&$C$2:C2 avec les critères {B2&C2,C2&B2}.
=ARRAYFORMULA(COUNTIFS($B$2:B2&$C$2:C2,{B2&C2,C2&B2}))
Où :
- plage_critères1 : $B$2:B2&$C$2:C2
- critère1 : {B2&C2,C2&B2}
Comme les critères dans critère1 contiennent deux conditions, nous avons utilisé la fonction ARRAYFORMULA.
Vous pouvez facilement le comprendre à l’aide de la formule générique suivante :
=ARRAYFORMULA(COUNTIFS(nom_prenom,{nom_prenom,prenom_nom}))
La fonction SUM totalise les valeurs renvoyées.
=ARRAYFORMULA(SUM(COUNTIFS($B$2:B2&$C$2:C2,{B2&C2,C2&B2})))
La plage devient $B$2:$B$3&$C$2:$C$3 et les critères deviennent {B3&C3,C3&B3} dans la deuxième ligne lorsque nous faisons glisser la formule vers le bas. Veuillez consulter la Figure 3 ci-dessous pour le comprendre.
Figure 3
2. Formule de tableau pour corriger les noms de famille et les prénoms inversés dans le comptage continu
Comment convertir la formule de la cellule D2 en une formule de tableau afin qu’elle puisse se développer sans intervention manuelle, c’est-à-dire en la faisant glisser vers le bas ?
Nous avons utilisé la fonction MAP pour cela.
La syntaxe de la fonction MAP est :
MAP(tableau1, [tableau2, ...], LAMBDA)
Le tableau1 est B2:B et le tableau2 est C2:C.
=MAP( B2:B,C2:C, LAMBDA( )
Nous devons utiliser la formule COUNTIFS à l’intérieur de cette lambda. Nous ne devons pas copier-coller telle quelle. Ce que nous devons faire, c’est d’abord définir des noms représentant les deux tableaux et les remplacer dans la formule COUNTIFS.
=MAP( B2:B,C2:C, LAMBDA(prenom,nom)
Nous avons appelé B2:B prenom et C2:C nom. Maintenant, mettons à jour la formule COUNTIFS et collons-la à l’intérieur de la lambda ci-dessus.
=MAP( B2:B,C2:C, LAMBDA(prenom,nom, ARRAYFORMULA(SUM(COUNTIFS($B$2:prenom&$C$2:nom,{prenom&nom,nom&prenom}))) ))
Cette formule corrige les noms de famille et les prénoms inversés et renvoie le comptage continu.
La formule ci-dessus nécessite une autre modification car elle peut renvoyer le comptage continu des cellules vides dans les colonnes des prénoms et des noms de famille. Le test logique IF dans ma formule principale se charge de cela.
Et voilà ! Vous avez maintenant la formule pour corriger les noms intervertis dans le comptage continu sur Google Sheets. Essayez-la par vous-même et créez des tableaux précis et organisés avec les noms corrects. N’oubliez pas de partager ce secret avec vos amies sur Crawlan.com pour leur faciliter la vie avec Google Sheets.
