Dans cette vidéo, nous verrons comment mettre en évidence les recherches de correspondance approximative avec une mise en forme conditionnelle.
Ici, nous avons un tableau de recherche simple qui montre les coûts des matériaux pour différentes hauteurs et largeurs. La formule dans K8 utilise les fonctions INDEX et MATCH pour récupérer le coût correct basé sur les valeurs de largeur et de hauteur entrées dans K6 et K7.
Notez que la recherche est basée sur une correspondance approximative. Puisque les valeurs sont dans l'ordre croissant, MATCH vérifie les valeurs jusqu'à ce qu'une valeur plus élevée soit atteinte, puis revient en arrière et renvoie la position précédente.
Construisons une règle de mise en forme conditionnelle pour mettre en évidence la ligne et la colonne correspondantes.
Comme toujours avec une mise en forme conditionnelle plus délicate, je vous recommande de travailler d'abord avec des formules factices, puis de transférer une formule de travail directement dans la règle de mise en forme conditionnelle. De cette façon, vous pouvez utiliser tous les outils d'Excel lorsque vous déboguez la formule, ce qui vous fera gagner beaucoup de temps.
Je vais d'abord configurer la formule pour la largeur. Nous devons renvoyer TRUE pour chaque cellule de la ligne 7, où la largeur correspondante est de 200.
Cela signifie que nous commençons notre formule avec $ B5 =, et nous devons verrouiller la colonne.
= $ B5 =
Maintenant, nous ne pouvons pas rechercher 275 dans la colonne des largeurs, car ce n'est pas là. Au lieu de cela, nous avons besoin d'une correspondance approximative qui trouve 200, tout comme notre formule de recherche.
Le moyen le plus simple consiste à utiliser la fonction LOOKUP. LOOKUP effectue automatiquement une correspondance approximative et, au lieu de renvoyer une position telle que MATCH, LOOKUP renvoie la valeur de correspondance réelle. Ainsi, nous pouvons écrire:
$ B5 = RECHERCHE ($ K $ 6, $ B $ 6: $ B $ 12)
Avec notre largeur d'entrée pour la valeur de recherche et toutes les largeurs du tableau pour le vecteur de résultat.
Si j'utilise F9, vous pouvez voir la valeur renvoyée par LOOKUP.
Maintenant, lorsque j'entre une formule dans le tableau, nous obtenons VRAI pour chaque cellule de la ligne de 200 largeurs.
Nous devons maintenant étendre la formule pour qu'elle corresponde à la colonne de hauteur. Pour ce faire, je vais ajouter la fonction OR, puis une deuxième formule pour correspondre à la hauteur.
Nous allons commencer la formule de la même manière, mais cette fois, nous devons verrouiller la ligne:
= 5 B $
Ensuite, nous utilisons à nouveau la fonction LOOKUP avec la hauteur pour la valeur de recherche et et toutes les hauteurs de la table comme vecteur de résultat.
= OU ($ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = LOOKUP ($ K $ 7, $ C $ 5: $ H $ 5))
Lorsque je copie la formule dans le tableau, nous obtenons TRUE pour chaque cellule de la colonne correspondante et chaque cellule de la ligne correspondante - exactement ce dont nous avons besoin pour la mise en forme conditionnelle.
Je peux simplement copier exactement la formule dans la cellule supérieure gauche et créer une nouvelle règle.
Maintenant, si je change la largeur ou la hauteur, la mise en évidence fonctionne comme prévu.
Enfin, si vous souhaitez uniquement mettre en évidence la valeur de recherche elle-même, il s'agit d'un simple changement. Modifiez simplement la formule et remplacez la fonction OU par la fonction ET.
= AND ($ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = LOOKUP ($ K $ 7, $ C $ 5: $ H $ 5))
Cours
Mise en forme conditionnelleRaccourcis associés
Entrez les mêmes données dans plusieurs cellules Ctrl + Enter ⌃ + Return Afficher la boîte de dialogue Collage spécial Ctrl + Alt + V ⌘ + ⌃ + V Basculer les références absolues et relatives F4 ⌘ + T
