Lorsque vous souhaitez filtrer les lignes de changement de statut les plus récentes, il n’y a pas de fonction autonome sur laquelle vous pouvez vous appuyer dans Google Sheets. Alors, comment faire ?
Nous devons utiliser une formule combinée, et ma préférence va vers une formule basée sur Filter et Sortn.
J’ai une formule flexible que nous pouvons facilement convertir pour trouver les changements dans n’importe quelle colonne d’une ligne et des lignes suivantes.
Veuillez consulter les deux captures d’écran ci-dessous pour comprendre ce que je veux dire par « lignes de changement de statut ».
Changement de statut dans une colonne (exemple #1) :
Dans cet exemple, le statut des employés « Rosa » and « Silvia » a changé en mars.
Quant à « Ben » et « Gary », il n’y a aucun changement dans leur statut.
Changements de statut dans plusieurs colonnes (exemple #2) :
Ici, la formule évalue le changement de statut dans deux colonnes, c’est-à-dire les colonnes C et D.
Le résultat est le même que précédemment, à l’exception de l’employé Ben.
Veuillez consulter la dernière colonne pour le pays.
Important:
Avant tout, assurez-vous d’avoir trié les données par nom (colonne B) puis par date (colonne A) dans l’ordre croissant.
Cela s’applique aux exemples de changement de statut d’une seule colonne ainsi que de plusieurs colonnes.
Filter les lignes de changement de statut les plus récentes – Exemple d’une colonne
Permettez-moi de vous présenter d’abord les étapes, afin que vous puissiez comprendre la formule plus tard.
Nous commencerons par l’exemple #1, qui concerne une colonne. Ensuite, il sera facile pour nous d’inclure plusieurs colonnes.
Entrez soit les données d’exemple dans A1:C17 selon la capture d’écran #1, soit faites défiler vers le bas de ce tutoriel et copiez-les depuis ma feuille d’exemple.
Une fois cela fait, vous pouvez vous concentrer sur les étapes ci-dessous pour comprendre comment filtrer les lignes de changement de statut les plus récentes dans Google Sheets.
Combinaison de plages (Un point clé)
Comme je l’ai mentionné, Sortn sera la principale formule en dehors de Filter.
Dans Sortn, nous pourrions avoir besoin de combiner des plages de colonnes.
Normalement, pour combiner les plages de colonnes B2:B et C2:C, nous utiliserions B2:B&C2:C (dans ArrayFormula).
Le signe & entre les deux fera l’affaire.
Une méthode alternative pour combiner ces plages est une méthode de combinaison de TRANSPOSE et QUERY, qui est transpose(query(transpose(B2:C),,9^9)).
Nous suivrons cette méthode dans notre formule, et non celle avec le & principalement en raison de sa flexibilité et de l’amélioration des performances.
Related:- La formule de tableau flexible pour joindre des colonnes dans Google Sheets.
Voici les étapes pour filtrer les lignes de changement de statut les plus récentes dans Google Sheets.
Étapes pour filtrer les lignes de changement de statut les plus récentes
Étape 1 – Éliminer les doublons en fonction du nom et du statut
Dans la première étape, nous éliminerons les lignes en double.
Nous tiendrons compte des colonnes B et C pour identifier les doublons.
Veuillez consulter la partie surlignée, c’est-à-dire la combinaison des plages de colonnes, dans la formule et les explications ultérieures de la formule.
=sort(sortn({A2:C,row(A2:A)},9^9,2,transpose(query(transpose(B2:C),,9^9)),1),4,0,2,1)Commençons par SORTN.
Syntaxe: SORTN(plage, [n], [mode_d’affichage_des_égalités], [colonne_de_tri], [est_ordonné_à_l’ascendante])
plage – {A2:C,row(A2:A)} – ajouter les numéros de ligne à la plage (comme dernière colonne).
n – 9^9 (un nombre arbitrairement grand)
mode_d’affichage_des_égalités – 2 (pour éliminer les doublons)
colonne_de_tri – transpose(query(transpose(B2:C),,9^9)) – combiner les plages de colonnes B2:B et C2:C.
Il y a quatre colonnes dans la sortie. Veuillez consulter l’image ci-dessus.
Le SORT extérieur trie la sortie dans l’ordre 4,0,2,1. Cela signifie que la colonne 4 descendante et la colonne 2 ascendante.
Le but de ce tri est de mettre les enregistrements les plus récents en haut par numéros de ligne, puis par noms.
Nous avons terminé la première étape pour filtrer les lignes de changement de statut les plus récentes dans Google Sheets.
Étape 2 – Éliminer les doublons en fonction du nom
Veuillez consulter la capture d’écran n°3 ci-dessus pour le résultat de Sortn.
Nous utiliserons cela comme plage à l’intérieur d’un autre Sortn.
=array_constrain(sortn(sort(sortn({A2:C,row(A2:A)},9^9,2,transpose(query(transpose(B2:C),,9^9)),1),4,0,2,1),9^9,2,2,1),9^9,3)Cette fois, la formule élimine les doublons basés sur la colonne 2, c’est-à-dire les noms.
Nous aurons donc un enregistrement unique de chaque groupe.
Le but de Array_Constrain est de supprimer la dernière colonne, c’est-à-dire les numéros de ligne.
Étape 3 – Identifier les lignes de changement de statut les plus récentes
Avec les étapes 1 et 2, nous avons maintenant une table qui contient un enregistrement unique de chaque groupe.
Note : Il y a quatre noms uniques dans la plage source B2:B, ce qui signifie qu’il y a quatre groupes.
S’il y a un changement de statut dans un groupe, nous aurons cette ligne. Sinon, nous aurons un enregistrement unique de ce groupe.
Nous voulons juste les lignes de changement de statut. Comment les obtenir ?
Tout d’abord, allouez la sortie ci-dessus aux lignes correspondantes en utilisant VLOOKUP.
Syntaxe: VLOOKUP(clé_de_recherche, plage, index, [est_trie])
Formule générique: Recherchev(plage_combinée_colonnes_A2:C, plage_combinée_de_la_sortie_de_l’étape_2, 1, 0)
Formule :
=ArrayFormula(IFNA(vlookup(transpose(query(transpose(A2:C),,9^9)),transpose(query(transpose(array_constrain(sortn(sort(sortn({A2:C,row(A2:A)},9^9,2,transpose(query(transpose(B2:C),,9^9)),1),4,0,2,1),9^9,2,2,1),9^9,3)),,9^9)),1,0)))Le IFNA prend en charge le Vlookup pour supprimer les erreurs N/A (non disponibles).
Veuillez vous référer à la colonne Q.
Avec l’aide de ISTEXT, convertissons maintenant les valeurs allouées en 1 et toutes les cases vides en 0.
Veuillez vous référer à la colonne R dans l’image ci-dessus. J’ai la formule suivante dans R2.
=ArrayFormula(-istext(IFNA(vlookup(transpose(query(transpose(A2:C),,9^9)),transpose(query(transpose(array_constrain(sortn(sort(sortn({A2:C,row(A2:A)},9^9,2,transpose(query(transpose(B2:C),,9^9)),1),4,0,2,1),9^9,2,2,1),9^9,3)),,9^9)),1,0))))Dans S2, j’ai inséré la formule de tableau de comptage suivante pour renvoyer la séquence en cours des noms dans B2:B.
=ArrayFormula(countifs(row(A2:A),"<= "&row(A2:A),B2:B,B2:B))Cela nous aidera à trouver les lignes de changement de statut les plus récentes. Vous trouverez les détails ci-dessous.
Étape finale – Filtrer les lignes de changement de statut les plus récentes dans Google Sheets
Tout d’abord, utilisez la formule FILTER ci-dessous.
=filter(A2:C,R2:R*S2:S>1)Remplacez R2:R et S2:S par les formules correspondantes ci-dessus.
Note : En le faisant, j’ai omis les fonctions Array_Formula car elles ne sont pas nécessaires dans Filter.
=filter(A2:C,-istext(IFNA(vlookup(transpose(query(transpose(A2:C),,9^9)),transpose(query(transpose(array_constrain(sortn(sort(sortn({A2:C,row(A2:A)},9^9,2,transpose(query(transpose(B2:C),,9^9)),1),4,0,2,1),9^9,2,2,1),9^9,3)),,9^9)),1,0)))*countifs(row(A2:A),"<= "&row(A2:A),B2:B,B2:B)>1)De cette façon, nous pouvons filtrer les lignes de changement de statut les plus récentes. J’ai utilisé cette formule dans la cellule E2 (veuillez faire défiler vers le haut et vous référer à la capture d’écran #1).
Filter les lignes de changement de statut les plus récentes – Pluralité des colonnes
Veuillez vous référer aux données d’exemple dans la capture d’écran #2.
Dans le premier exemple, les données se trouvent dans A1:C17. Ici, elles se trouvent en A1:D17.
Nous voulons trouver le changement de statut dans les colonnes C et D.
Vous avez besoin de modifications minimales dans la formule E2 ci-dessus pour y parvenir. Lesquelles ?
- Changer A2:C en A2:D.
- B2:C devient B2:D.
- Le tri de la colonne 4 devient 5 (en gras et surlignée en rose pâle dans la formule ci-dessous).
- Contraindre 4 colonnes au lieu de 3 (en gras et surlignée en vert clair dans la formule ci-dessous).
=filter(A2:D,-istext(IFNA(vlookup(transpose(query(transpose(A2:D),,9^9)),transpose(query(transpose(array_constrain(sortn(sort(sortn({A2:D,row(A2:A)},9^9,2,transpose(query(transpose(B2:D),,9^9)),1),5,0,2,1),9^9,2,2,1),9^9,4)),,9^9)),1,0)))*countifs(row(A2:A),"<= "&row(A2:A),B2:B,B2:B)>1)C’est la formule G2 selon la capture d’écran #2 ci-dessus.
Maintenant, je suppose que vous savez quelles modifications vous devez apporter pour inclure des colonnes de statut supplémentaires. Sinon, n’hésitez pas à demander dans les commentaires.
Enfin, nous pouvons résoudre les deux problèmes ci-dessus en utilisant une logique différente !
Vous pouvez trouver ces solutions dans les cellules ‘test 1’!I2 et ‘test 2’!L2 de ma feuille d’exemple ci-dessous.
J’expliquerai ces formules dans un prochain tutoriel. Profitez-en bien !
Feuille d’exemple 2622
