Parfois, plutôt que de répéter la même valeur plusieurs fois, nous pouvons laisser des cellules vides dans les feuilles de calcul. Dans ce cas, vous pouvez rencontrer des problèmes pour mettre en évidence ces cellules vides en utilisant la valeur de la cellule précédente.
Par exemple, la cellule A1 contient la valeur « Concombre » et la cellule A5 contient la valeur « Laitue ». Dans ce cas, les valeurs réelles dans les cellules A2:A4 sont « Concombre », mais elles sont laissées vides pour éviter la répétition.
Lorsque je veux mettre en évidence toutes les lignes contenant « Concombre » dans la colonne A, en réalité je veux mettre en évidence les lignes A1:A4. Cela n’est normalement pas possible car certaines des cellules sont vides.
Dans cet article, vous allez apprendre à coder la règle de formatage conditionnel (formule) pour mettre en évidence les cellules vides en utilisant la valeur de la cellule précédente.
Il y a un total de 4 étapes impliquées. Avant d’aborder cela, voici les données d’exemple et la mise en évidence que nous attendons.
La règle de format conditionnel pour la plage A1:A10 met en évidence les lignes, y compris les cellules vides, en fonction du critère dans la liste déroulante de la cellule D1. Au lieu de la liste déroulante, vous pouvez directement taper le critère dans la cellule D1.
La formule que nous allons coder est pour une plage infinie (ouverte), par exemple A1:A, pas limitée à A1:A10.
Étape 1 : Remplacer les cellules vides par le texte de la cellule précédente
Normalement, nous ne pouvons pas mettre en évidence les cellules vides en utilisant les valeurs de la cellule précédente. Il n’y a pas de formule magique pour cela.
Nous devons remplacer virtuellement les cellules vides par le texte de la cellule précédente. Ici, nous devons remplacer A2:A4 par « Concombre » et A6:A10 par « Laitue ». Comment faire ?
Insérez la formule suivante dans la cellule B1.
=ArrayFormula(if(row($A$1:$A)<=MATCH(2,1/($A$1:$A<>""),1), lookup( row($A$1:$A), row($A$1:$A)/if($A$1:$A<>"",TRUE,FALSE), $A$1:$A ), ) )Pour une explication de la formule, lisez cet article – Formule de tableau pour remplir les cellules vides avec les valeurs ci-dessus dans Google Sheets.
En réalité, la formule ci-dessus ne remplira que « Concombre » dans A2:A4. Car la formule considère la cellule A5 comme la fin de la plage.
Double-cliquez sur la cellule A11 et insérez un espace en tapant sur la touche espace. Ainsi, la formule peut comprendre que la plage est A1:A10 et remplir « Laitue » dans A6:A10.
Remarque: Si vos données se trouvent dans la plage de cellules A1:A100 et que la dernière valeur se trouve dans la cellule A95, insérez un espace dans la cellule A101.
Par exemple, la valeur dans la cellule A95 est « Betterave ». Pour mettre en évidence la plage A95:A100, vous devez insérer n’importe quel caractère (par exemple un espace) dans la cellule A101.
Étape 2 : Répertorier les numéros de ligne correspondant au critère
Certains d’entre vous peuvent penser que vous pouvez maintenant mettre en évidence A1:A4 si le critère est « Concombre » et mettre en évidence A5:A10 si le critère est « Laitue ».
Si vous utilisez la colonne B comme colonne d’aide, alors vous avez raison. Mais sans utiliser la colonne B, autant que je sache, il n’est pas possible de le faire en formatage conditionnel.
Nous ne pouvons pas mettre en évidence certaines lignes correspondant à une expression (résultat d’une autre formule [formule de l’étape 1]).
Mais nous pouvons faire une chose. Nous pouvons adopter la correspondance des numéros de ligne dans le formatage conditionnel. Veuillez suivre les étapes ci-dessous.
- Entrez le critère dans la cellule D1. J’ai utilisé le menu Données > Validation des données et le « critère » dans celui-ci comme suit « Liste d’articles ».
Cucumber,Lettuce
Remarque: Vous pouvez simplement taper le critère « Concombre » dans la cellule D1. Cela suffira pour notre test. Il n’est pas nécessaire de créer une liste déroulante.
- Dans la cellule E1, entrez le test logique IF suivant pour renvoyer les numéros de ligne des lignes contenant le critère « Concombre » dans A1:A10.
=ArrayFormula(if($B$1:$B$10=$D$1,row($B$1:$B$10),))
Pour mettre en évidence les cellules vides en utilisant la valeur de la cellule précédente, nous pouvons maintenant suivre la correspondance des numéros de ligne. L’étape suivante explique cela.
Étape 3 : Correspondance de numéros de ligne pour mettre en évidence les cellules vides en utilisant la valeur de la cellule précédente
Nous avons plusieurs numéros de ligne (de 1 à 4) à faire correspondre dans la plage de cellules E1:E4 selon le critère actuel dans la cellule D1. Si le critère est « Laitue », alors les numéros de ligne (de 5 à 10) à faire correspondre se trouveront dans E5:E10.
Pour faire correspondre plusieurs numéros de ligne et renvoyer la valeur BOOLEAN TRUE, nous pouvons utiliser la fonction Regexmatch.
Syntaxe : REGEXMATCH(texte, expression_régulière)
L’expression régulière est les numéros de ligne ci-dessus. Mais il doit être formaté comme une expression régulière.
Je veux dire formatez les numéros de ligne comme ^1$|^2$|^3$|^4$ pour « Concombre » et ^5$|^6$|^7$|^8$|^9$|^10$ pour « Laitue ». La formule suivante dans la cellule F1 fait cela.
="^"&TEXTJOIN("$|^",true,$E$1:$E$10)&"$"L’argument « texte » est le numéro de ligne formaté en tant que texte en ajoutant un caractère nul ;
row(A1)& » »
Voici la formule Regexmatch. Insérez-la dans la cellule F1 et pour la tester, faites glisser vers le bas (copiez-collez) jusqu’à F10.
=REGEXMATCH(row(A1)&"","^"&TEXTJOIN("$|^",true,$E$1:$E$10)&"$")
La formule renvoie TRUE pour toutes les lignes contenant le critère « Concombre » dans B2:B.
En réalité, la formule ci-dessus dans la cellule F1 est suffisante pour mettre en évidence les cellules vides en utilisant la valeur de la cellule précédente dans Google Sheets.
Parce que dans la formule Regexmatch, la référence de cellule dans l’argument « texte » est relative et toutes les références de cellule dans « expression_régulière » sont absolues.
Cela signifie que dans la mise en forme conditionnelle, la référence relative a l’effet de glisser vers le bas dans la plage sélectionnée.
Je veux dire que si la plage est A1:A10, la formule F1 sera suffisante pour toutes les lignes. Pas besoin d’utiliser les formules de F2, F3…F10 individuellement.
Relatif : Référence relative dans la mise en forme conditionnelle dans Google Sheets.
La formule mettra en évidence les lignes qui renvoient TRUE. Mais nous n’utiliserons pas la formule F1 telle quelle pour mettre en évidence les cellules vides en utilisant la valeur de la cellule précédente dans Google Sheets ! Pourquoi ?
Combinons les formules des étapes précédentes et utilisons-les pour éviter les colonnes d’aide.
Étape 4 : Combinaison de formules pour la règle du format combiné
Remplacez la référence de cellule dans la formule de l’étape 3 par les formules correspondantes des étapes 1 et 2.
Remplacez $E$1:$E$10 par la formule de l’étape 2. Tout en remplaçant, nous pouvons exclure ArrayFormula de la formule de l’étape 2.
=REGEXMATCH(row(A1)&"","^"&TEXTJOIN("$|^",true,if($B$1:$B$10=$D$1,row($B$1:$B$10),))&"$")Remplacez row($B$1:$B$10) par row($B$1:$B).
=REGEXMATCH(row(A1)&"","^"&TEXTJOIN("$|^",true,if($B$1:$B$10=$D$1,row($B$1:$B),))&"$")Ensuite, dans $B$1:$B$10=$D$1, remplacez $B$1:$B$10 par la formule de l’étape 1. Ici aussi, nous pouvons exclure ArrayFormula.
FORMULE FINALE (enveloppée avec ArrayFormula pour compenser les formules de tableau précédemment supprimées).
=ArrayFormula(REGEXMATCH(row(A1)&"","^"&TEXTJOIN("$|^",true,if(if(row($A$1:$A)<=MATCH(2,1/($A$1:$A<>""),1),lookup(row($A$1:$A),row($A$1:$A)/if($A$1:$A<>"",TRUE,FALSE),$A$1:$A),)=$D$1,row($B$1:$B),))&"$"))Gardez seulement les valeurs dans A1:A10 et les critères dans la cellule D1. Supprimez toutes les formules.
Il vous suffit de copier la formule ci-dessus. Voyons comment l’utiliser pour mettre en évidence les cellules vides en utilisant la valeur ci-dessus dans Google Sheets.
Étape finale – Insérer la formule combinée pour mettre en évidence les lignes vides en utilisant la valeur précédente
- Sélectionnez A1:A ou pour une ligne entière, sélectionnez A1:1000. Je sélectionne A1:A10.
- Ouvrez Format > Mise en forme conditionnelle.
- Vous verrez votre plage sélectionnée dans le champ « Appliquer à la plage ». Dans mon cas, c’est A1:A10.
- Sous « Règles de formatage », ouvrez le menu déroulant pour sélectionner « La formule personnalisée est ». En dessous, vous verrez un champ vide. Insérez notre formule finale et cliquez sur « Terminé ».
Modifiez le critère/la condition dans la cellule D1 et voyez comment la mise en évidence déplace les lignes !
Voilà tout sur la façon de mettre en évidence les cellules vides en utilisant la valeur de la cellule précédente dans Google Sheets.
Merci de rester et profitez-en !
Example_18720
