I denne video ser vi på, hvordan man fremhæver omtrentlige matchopslag med betinget formatering.
Her har vi en simpel opslagstabel, der viser materialeomkostninger i forskellige højder og bredder. Formlen i K8 bruger INDEX- og MATCH-funktionerne til at hente de korrekte omkostninger baseret på bredde- og højdeværdier indtastet i K6 og K7.
Bemærk, at opslag er baseret på et omtrentligt match. Da værdierne er i stigende rækkefølge, kontrollerer MATCH værdierne, indtil en større værdi nås, og træder derefter tilbage og returnerer den forrige position.
Lad os oprette en betinget formateringsregel for at fremhæve den matchede række og kolonne.
Som altid med mere vanskelig betinget formatering anbefaler jeg, at du arbejder med dummyformler først og derefter overfører en arbejdsformel direkte til den betingede formateringsregel. På denne måde kan du bruge alle Excels værktøjer, når du debuggerer formlen, hvilket sparer dig meget tid.
Jeg opretter først formlen for bredde. Vi skal returnere SAND for hver celle i række 7, hvor den matchede bredde er 200.
Dette betyder, at vi starter vores formel med $ B5 =, og vi skal låse kolonnen.
= $ B5 =
Nu kan vi ikke kigge efter 275 i kolonnen bredder, fordi den ikke er der. I stedet har vi brug for et omtrentligt match, der finder 200, ligesom vores opslagsformel.
Den nemmeste måde er at gøre dette er at bruge LOOKUP-funktionen. LOOKUP udfører automatisk et omtrentligt match, og i stedet for at returnere en position som MATCH returnerer LOOKUP den aktuelle matchværdi. Så vi kan skrive:
$ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12)
Med vores inputbredde for opslagsværdi og alle bredderne i tabellen for resultatvektor.
Hvis jeg bruger F9, kan du se værdien LOOKUP returnerer.
Nu når jeg indtaster formel på tværs af tabellen, får vi SAND for hver celle i rækken med 200 bredder.
Nu skal vi udvide formlen til at matche højdekolonnen. For at gøre dette tilføjer jeg OR-funktionen og derefter en anden formel for at matche højden.
Vi starter formlen på samme måde, men denne gang skal vi låse rækken:
= B $ 5
Derefter bruger vi LOOKUP-funktionen igen med højde for opslagsværdi og og alle højder i tabellen som resultatvektor.
= ELLER ($ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = LOOKUP ($ K $ 7, $ C $ 5: $ H $ 5))
Når jeg kopierer formlen over hele tabellen, får vi SAND for hver celle i den matchede kolonne og hver celle i den matchede række - lige hvad vi har brug for til betinget formatering.
Jeg kan bare kopiere formlen i den øverste venstre celle nøjagtigt og oprette en ny regel.
Hvis jeg nu ændrer bredden eller højden, fungerer fremhævningen som forventet.
Endelig, hvis du kun vil fremhæve selve opslagsværdien, er det en simpel ændring. Du skal bare redigere formlen og erstatte OR-funktionen med AND-funktionen.
= AND ($ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = LOOKUP ($ K $ 7, $ C $ 5: $ H $ 5))
Rute
Betinget formateringRelaterede genveje
Indtast de samme data i flere celler Ctrl + Enter ⌃ + Return Vis dialogboksen Indsæt speciel Ctrl + Alt + V ⌘ + ⌃ + V Skift absolutte og relative referencer F4 ⌘ + T
