I den här videon tittar vi på hur man markerar ungefärliga matchningsuppslag med villkorlig formatering.
Här har vi en enkel uppslagstabell som visar materialkostnader för olika höjder och bredder. Formeln i K8 använder funktionerna INDEX och MATCH för att hämta rätt kostnad baserat på bredd- och höjdvärden som anges i K6 och K7.
Observera att sökningen baseras på en ungefärlig matchning. Eftersom värdena är i stigande ordning kontrollerar MATCH värdena tills ett större värde uppnås, och sedan går tillbaka och returnerar föregående position.
Låt oss bygga en villkorlig formateringsregel för att markera den matchade raden och kolumnen.
Som alltid med mer knepig villkorlig formatering rekommenderar jag att du arbetar med dummyformler först och sedan överför en fungerande formel direkt till villkorlig formateringsregel. På detta sätt kan du använda alla Excels verktyg när du felsöker formeln, vilket sparar mycket tid.
Jag ställer in formeln för bredden först. Vi måste returnera SANT för varje cell i rad 7, där den matchade bredden är 200.
Det betyder att vi börjar vår formel med $ B5 =, och vi måste låsa kolumnen.
= $ B5 =
Nu kan vi inte leta efter 275 i bredden kolumn, eftersom det inte finns. Istället behöver vi en ungefärlig matchning som hittar 200, precis som vår uppslagsformel.
Det enklaste sättet är att använda LOOKUP-funktionen. LOOKUP gör automatiskt en ungefärlig matchning och istället för att returnera en position som MATCH returnerar LOOKUP det aktuella matchningsvärdet. Så vi kan skriva:
$ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12)
Med vår inmatningsbredd för uppslagsvärde och alla bredder i tabellen för resultatvektor.
Om jag använder F9 kan du se värdet LOOKUP returnerar.
Nu när jag anger formel över bordet får vi SANT för varje cell i raden 200-bredd.
Nu måste vi utöka formeln för att matcha höjdkolumnen. För att göra detta lägger jag till OR-funktionen och sedan en andra formel för att matcha höjden.
Vi startar formeln på samma sätt, men den här gången måste vi låsa raden:
= B $ 5
Sedan använder vi LOOKUP-funktionen igen med höjd för uppslagsvärde och och alla 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 jag kopierar formeln över bordet får vi SANT för varje cell i den matchade kolumnen och varje cell i den matchade raden - precis vad vi behöver för villkorlig formatering.
Jag kan bara kopiera formeln i den övre vänstra cellen exakt och skapa en ny regel.
Om jag nu ändrar bredd eller höjd fungerar markeringen som förväntat.
Slutligen, om du bara vill markera själva uppslagsvärdet, är det en enkel förändring. Redigera bara formeln och ersätt OR-funktionen med AND-funktionen.
= OCH ($ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = LOOKUP ($ K $ 7, $ C $ 5: $ H $ 5))
Kurs
Villkorlig formateringRelaterade genvägar
Ange samma data i flera celler Ctrl + Enter ⌃ + Return Visa dialogrutan Klistra in special Ctrl + Alt + V ⌘ + ⌃ + V Växla absoluta och relativa referenser F4 ⌘ + T
