In deze video bekijken we hoe je geschatte zoekacties voor overeenkomsten kunt markeren met voorwaardelijke opmaak.
Hier hebben we een eenvoudige opzoektabel met materiaalkosten voor verschillende hoogtes en breedtes. De formule in K8 gebruikt de functies INDEX en MATCH om de juiste kosten op te halen op basis van de waarden voor breedte en hoogte die zijn ingevoerd in K6 en K7.
Merk op dat de zoekopdracht is gebaseerd op een geschatte overeenkomst. Omdat waarden in oplopende volgorde staan, controleert MATCH de waarden totdat een grotere waarde is bereikt, gaat dan terug en retourneert de vorige positie.
Laten we een regel voor voorwaardelijke opmaak maken om de overeenkomende rij en kolom te markeren.
Zoals altijd met meer lastige voorwaardelijke opmaak, raad ik u aan eerst met dummy-formules te werken en vervolgens een werkende formule rechtstreeks over te brengen naar de voorwaardelijke opmaakregel. Op deze manier kunt u alle Excel-tools gebruiken bij het debuggen van de formule, wat u veel tijd bespaart.
Ik zal eerst de formule voor breedte instellen. We moeten TRUE retourneren voor elke cel in rij 7, waar de overeenkomende breedte 200 is.
Dit betekent dat we onze formule beginnen met $ B5 =, en dat we de kolom moeten vergrendelen.
= $ B5 =
Nu kunnen we niet zoeken naar 275 in de breedtekolom, omdat het er niet is. In plaats daarvan hebben we een geschatte overeenkomst nodig die 200 vindt, net als onze opzoekformule.
De eenvoudigste manier is om dit te doen, is door de functie ZOEKEN te gebruiken. ZOEKEN doet automatisch een geschatte overeenkomst, en in plaats van een positie zoals VERGELIJKEN te retourneren, retourneert ZOEKEN de werkelijke overeenkomstwaarde. Dus we kunnen schrijven:
$ B5 = ZOEKEN ($ K $ 6, $ B $ 6: $ B $ 12)
Met onze invoerbreedte voor opzoekwaarde en alle breedtes in de tabel voor resultaatvector.
Als ik F9 gebruik, kun je zien dat de waarde ZOEKEN terugkeert.
Als ik nu een formule in de tabel voer, krijgen we WAAR voor elke cel in de rij met een breedte van 200.
Nu moeten we de formule uitbreiden zodat deze overeenkomt met de hoogtekolom. Om dit te doen, voeg ik de OR-functie toe en vervolgens een tweede formule die overeenkomt met de hoogte.
We starten de formule op dezelfde manier, maar deze keer moeten we de rij vergrendelen:
= B $ 5
Vervolgens gebruiken we de functie ZOEKEN weer met hoogte als opzoekwaarde en en alle hoogtes in de tabel als resultaatvector.
= OF ($ B5 = ZOEKEN ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = ZOEKEN ($ K $ 7, $ C $ 5: $ H $ 5))
Wanneer ik de formule over de tabel kopieer, krijgen we WAAR voor elke cel in de overeenkomende kolom en elke cel in de overeenkomende rij - precies wat we nodig hebben voor voorwaardelijke opmaak.
Ik kan de formule precies in de cel linksboven kopiëren en een nieuwe regel maken.
Als ik nu de breedte of hoogte verander, werkt de markering zoals verwacht.
Ten slotte, als u alleen de opzoekwaarde zelf wilt markeren, is dit een eenvoudige wijziging. Bewerk gewoon de formule en vervang de OF-functie door de EN-functie.
= EN ($ B5 = ZOEKEN ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = ZOEKEN ($ K $ 7, $ C $ 5: $ H $ 5))
Cursus
Conditionele opmaakGerelateerde snelkoppelingen
Voer dezelfde gegevens in meerdere cellen in Ctrl + Enter ⌃ + Return Geef het dialoogvenster Plakken speciaal weer Ctrl + Alt + V ⌘ + ⌃ + V Schakel tussen absolute en relatieve verwijzingen F4 ⌘ + T
