I denne videoen vil vi se på hvordan du kan markere omtrentlige kampoppslag med betinget formatering.
Her har vi en enkel oppslagstabell som viser materialkostnader for forskjellige høyder og bredder. Formelen i K8 bruker INDEX- og MATCH-funksjonene for å hente de riktige kostnadene basert på bredde- og høydeverdiene som er angitt i K6 og K7.
Legg merke til at oppslaget er basert på en omtrentlig kamp. Siden verdiene er i stigende rekkefølge, sjekker MATCH verdiene til en større verdi er nådd, og går deretter tilbake og returnerer forrige posisjon.
La oss bygge en betinget formateringsregel for å markere den matchede raden og kolonnen.
Som alltid med mer vanskelig betinget formatering, anbefaler jeg at du jobber med dummyformler først, og deretter overfører en fungerende formel direkte til den betingede formateringsregelen. På denne måten kan du bruke alle Excel-verktøyene når du feilsøker formelen, noe som vil spare deg for mye tid.
Jeg setter opp formelen for bredde først. Vi må returnere SANT for hver celle i rad 7, der den matchede bredden er 200.
Dette betyr at vi starter formelen med $ B5 =, og vi må låse kolonnen.
= $ B5 =
Nå kan vi ikke se etter 275 i bredde-kolonnen, fordi den ikke er der. I stedet trenger vi en omtrentlig kamp som finner 200, akkurat som oppslagsformelen vår.
Den enkleste måten er å gjøre dette er å bruke LOOKUP-funksjonen. LOOKUP gjør automatisk en omtrentlig kamp, og i stedet for å returnere en posisjon som MATCH, returnerer LOOKUP den faktiske kampverdien. Så vi kan skrive:
$ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12)
Med vår inngangsbredde for oppslagsverdi og alle bredder i tabellen for resultatvektor.
Hvis jeg bruker F9, kan du se verdien LOOKUP returnerer.
Nå når jeg skriver inn formel over bordet, får vi SANN for hver celle i raden med 200 bredder.
Nå må vi utvide formelen for å matche høydekolonnen. For å gjøre dette, legger jeg til OR-funksjonen, og deretter en annen formel for å matche høyden.
Vi starter formelen på samme måte, men denne gangen må vi låse raden:
= B $ 5
Deretter bruker vi LOOKUP-funksjonen igjen med høyde for oppslagsverdi og og alle høyder 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 formelen over bordet, får vi SANN for hver celle i den matchede kolonnen og hver celle i den matchede raden - akkurat det vi trenger for betinget formatering.
Jeg kan bare kopiere formelen i cellen øverst til venstre nøyaktig, og lage en ny regel.
Nå hvis jeg endrer bredden eller høyden, fungerer uthevingen som forventet.
Til slutt, hvis du bare vil markere selve oppslagsverdien, er det en enkel endring. Bare rediger formelen og erstatt OR-funksjonen med AND-funksjonen.
= AND ($ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = LOOKUP ($ K $ 7, $ C $ 5: $ H $ 5))
Kurs
Betinget formateringRelaterte snarveier
Skriv inn samme data i flere celler Ctrl + Enter ⌃ + Return Vis dialogboksen Lim inn spesial Ctrl + Alt + V ⌘ + ⌃ + V Bytt absolutt og relative referanser F4 ⌘ + T
