Imaginez que vous ayez une feuille de calcul dans Google Sheets avec des données qui contiennent des lignes en double. Par exemple, vous avez plusieurs lignes avec le même nom ou le même produit. Vous souhaitez fusionner ces lignes en conservant toutes les informations, que ce soit les valeurs de cellule, les numéros agrégés ou le texte concaténé.
Bien que Google Sheets ne propose pas de fonctionnalité native pour fusionner directement les lignes en double de cette manière, je vais vous montrer une solution basée sur des formules matricielles qui vous permettra de le faire.
Pourquoi fusionner les lignes en double dans Google Sheets ?
La fusion des lignes en double dans Google Sheets peut impliquer la fusion des valeurs des cellules, l’agrégation des nombres et la concaténation du texte, des dates ou des horodatages.
La fusion des lignes consiste à combiner deux ou plusieurs lignes de données qui contiennent les mêmes informations. Par exemple, deux ou plusieurs lignes ayant le même prénom et nom, ou le même produit dans plusieurs lignes.
Dans le cas de la fusion de deux ou plusieurs lignes de données contenant le même produit, vous devrez peut-être ajouter des quantités ou des montants pour éviter de perdre des données. Nous devons prendre en compte tous ces éléments lors de la programmation des formules.
Si vous recherchez une approche basée sur une formule matricielle, sans utiliser d’extension, ma solution suivante pourrait vous convenir.
Il n’y a pas de fonction intégrée ou de commande de menu pour fusionner les lignes en double dans Google Sheets de la manière que nous souhaitons.
Pour résoudre ce problème, nous utiliserons trois formules matricielles, chacune assignée à une tâche spécifique.
- La première formule matricielle supprimera les lignes en double (en fusionnant les cellules) dans la plage de données des colonnes spécifiées.
- La deuxième formule matricielle fonctionnera comme une alternative à la fonction CONCATENER ou à la fonction JOINDRE. Elle permettra de joindre les valeurs des lignes supprimées avec les lignes existantes.
- La troisième formule matricielle additionnera conditionnellement les colonnes. Elle ajoutera les valeurs des lignes supprimées aux lignes existantes.
Bien sûr, vous pouvez fusionner les cellules (en double) en utilisant la fonction UNIQUE. Mais qu’en est-il de l’agrégation et de la concaténation des valeurs ?
En réalité, nous pouvons gérer la plupart de ces tâches en utilisant la fonction QUERY elle-même. Cependant, elle est insuffisante lorsqu’il s’agit de fusionner ou de concaténer des valeurs en double.
Comment fusionner les lignes en double dans Google Sheets avec un exemple
Dans l’exemple suivant, les données sources pour fusionner les lignes en double se trouvent dans la plage de cellules A2:E, et les résultats dans la plage de cellules G2:K.
Figure 1
Dans le concept de fusion des lignes en double, nous commencerons par supprimer les doublons dans la colonne « Item » (A). Cela nous donnera les noms uniques des fruits (colonne G).
Nous additionnerons ensuite les quantités correspondantes dans la colonne (B) et le montant dans la colonne (D) séparément. Les résultats se trouvent dans les colonnes H et J.
La colonne « Rate » (D) contient plusieurs taux pour les mêmes fruits. Nous allons concaténer ces taux pour chaque fruit (colonne I). La colonne « Grade » (E) sera concaténée de la même manière. Les résultats se trouvent dans la colonne K.
Nous utiliserons trois formules matricielles pour chaque objectif, et les répéterons, si nécessaire, pour des colonnes supplémentaires. Cela permettra à chacun d’ajuster les plages dans la formule pour personnaliser la formule en fonction de son jeu de données.
Comme les formules sont des formules matricielles, nous pouvons les utiliser avec des données de feuille de calcul classiques, des données de formulaire Google Sheets, ainsi que des données IMPORTRANGE.
Dans ce tutoriel, vous obtiendrez les formules matricielles pour fusionner les lignes en double dans Google Sheets. Je vais également expliquer comment les utiliser.
Étape 1 : L’unicité pour fusionner les cellules en double
Syntaxe : UNIQUE(plage, [par_colonne], [exactement_une_fois])
Dans l’exemple de fusion des lignes en double ci-dessus, la colonne clé pour la fusion est la colonne A, qui contient des valeurs en double (noms de fruits). Parfois, vous pouvez avoir plusieurs colonnes que vous souhaitez fusionner. Nous verrons ce scénario plus tard.
Nous pouvons utiliser la fonction UNIQUE pour rendre uniques les noms de fruits dans la colonne A. Cependant, cela ne suffit pas. Nous devons également inclure la fonction FILTRE dans la fonction UNIQUE. Cela permet de filtrer les lignes vides, car la fonction UNIQUE est connue pour renvoyer une cellule vide à la fin.
La formule suivante dans la cellule G2 rendra uniques les noms de fruits dans la colonne A et supprimera les cellules vides :
=LET(clé,A2:A,UNIQUE(FILTER(clé,CHOOSECOLS(clé,1)<>""")))Lorsque vous utilisez cette formule, remplacez simplement A2:A par l’étendue de colonne correspondante.
Étape 2 : Agrégation des valeurs tout en conservant les valeurs des lignes supprimées
La fusion des lignes en double consiste non seulement à supprimer les lignes en double, mais aussi à conserver les valeurs qui s’y trouvent avec la ligne conservée. Si les valeurs sont du texte, une date, une heure ou une horodatage, nous les combinerons. Mais si les valeurs sont des nombres, nous pouvons les additionner ou les combiner, selon notre objectif.
Comment pouvons-nous additionner les valeurs correspondant aux valeurs fusionnées à l’étape précédente ?
Nous pouvons utiliser la fonction SOMME.SI pour cela. La syntaxe est SOMME.SI(plage, critère, [plage_somme]).
Dans nos données d’exemple, la plage_somme est B2:B, la plage est A2:A et le critère sont les éléments uniques dans G2:G.
Figure 2
Cependant, vous n’avez pas besoin de vous soucier des valeurs uniques (G2:G) dans votre formule. Il suffit de spécifier la plage et la plage_somme, et la formule s’occupera du reste.
Formule H2 :
=ARRAYFORMULA(LET(plage,A2:A,plage_somme,B2:B,SOMME.SI(TRANSPOSE(QUERY(TRANSPOSE(plage),,9^9)),TRANSPOSE(QUERY(TRANSPOSE(UNIQUE(FILTER(plage,CHOOSECOLS(plage,1)<>"""))),,9^9)),plage_somme)))Nous pouvons utiliser la même formule résultante dans la cellule J2 pour additionner la colonne « Montant » en remplaçant plage_somme B2:B par plage_somme D2:D.
=ARRAYFORMULA(LET(plage,A2:A,plage_somme,D2:D,SOMME.SI(TRANSPOSE(QUERY(TRANSPOSE(plage),,9^9)),TRANSPOSE(QUERY(TRANSPOSE(UNIQUE(FILTER(plage,CHOOSECOLS(plage,1)<>"""))),,9^9)),plage_somme)))Voilà donc la deuxième étape de la fusion des lignes en double et de la concaténation des valeurs.
La formule ci-dessus peut sembler complexe, mais ne vous inquiétez pas. Il suffit de spécifier les plages et vous êtes prêt à partir.
J’ai inclus une certaine flexibilité dans la formule pour l’ajuster automatiquement lorsque vous avez plusieurs colonnes de fusion à l’étape 1 (formule G2). Nous verrons un exemple de cela plus tard.
Étape 3 : Concaténation des valeurs tout en conservant les valeurs des lignes supprimées
Il s’agit de la troisième et dernière formule pour fusionner les lignes en double et concaténer les valeurs.
Dans cette étape, nous concaténerons les taux des lignes supprimées avec les taux existants. Cela implique deux colonnes : la colonne utilisée pour identifier les lignes uniques à l’étape 1, et la colonne des taux.
Voici la formule I2 :
=LET(fusion,A2:A,combiner,C2:C,CARTEDUPLACEMENT(TRANSPOSE(QUERY(TRANSPOSE(UNIQUE(FILTER(fusion,CHOOSECOLS(fusion,1)<>"""))),,9^9)),LAMBDA(ligne,JOINDRE.TEXTE(", ",VRAI,FILTER(combiner,TRANSPOSE(QUERY(TRANSPOSE(fusion),,9^9))=ligne)))))Pour concaténer la colonne des notes, utilisez la même formule mais remplacez la plage C2:C par E2:E.
=LET(fusion,A2:A,combiner,E2:E,CARTEDUPLACEMENT(TRANSPOSE(QUERY(TRANSPOSE(UNIQUE(FILTER(fusion,CHOOSECOLS(fusion,1)<>"""))),,9^9)),LAMBDA(ligne,JOINDRE.TEXTE(", ",VRAI,FILTER(combiner,TRANSPOSE(QUERY(TRANSPOSE(fusion),,9^9))=ligne)))))
Figure 4
Vous disposez maintenant de trois formules clés pour fusionner les lignes en double dans Google Sheets.
Maintenant, voyons un ensemble de données différent où nous voulons faire une fusion unique de deux colonnes.
Fusion des lignes en double et de plusieurs colonnes uniques dans Google Sheets
Voici un nouvel exemple de données :
Figure 5
Dans cet exemple, nous voulons fusionner le tableau en fonction des prénoms et noms de famille dans les colonnes A et B. La note doit être agrégée et la matière doit être concaténée.
Voici les instructions étape par étape pour fusionner les lignes en double des prénoms et noms de famille et concaténer ou agréger les colonnes correspondantes :
- Remontez et copiez la formule sous « Unique pour fusionner les cellules en double ». Remplacez la plage A2:A par A2:B. Insérez cette formule dans la cellule F2.
- De même, copiez la première formule de « Aggregating Values while Retaining Removed Rows’ Values ». Remplacez A2:A par A2:B et B2:B par C2:C.
- Ensuite, copiez la première formule de « Concatenating Values while Retaining Removed Rows’ Values ». Remplacez A2:A par A2:B et C2:C par D2:D.
Et voilà ! Vous pouvez utiliser ces trois formules pour fusionner les lignes en double dans Google Sheets.
La fusion des lignes en double dans Google Sheets peut être un défi, mais avec les bonnes formules matricielles, vous pouvez obtenir des résultats précis et efficaces. Utilisez les formules présentées dans cet article pour fusionner les lignes en double dans vos feuilles de calcul Google Sheets et simplifier votre travail.
Si vous voulez en savoir plus sur Google Sheets, consultez Crawlan.com, un site Web spécialisé dans l’optimisation et les astuces pour les outils Google, y compris Google Sheets. Sur Crawlan.com, vous trouverez d’autres articles, tutoriels et conseils pour vous aider à tirer le meilleur parti de Google Sheets et d’autres produits Google.
Mettez en pratique ces formules matricielles et découvrez comment elles peuvent vous aider à simplifier votre travail dans Google Sheets. Vous serez étonné de la puissance de ces formules et de leur capacité à résoudre des problèmes complexes de fusion de données.
