În acest videoclip, vom analiza cum să evidențiați căutările aproximative de potrivire cu formatare condiționată.
Aici avem un tabel simplu de căutare care arată costurile materiale pentru diferite înălțimi și lățimi. Formula din K8 folosește funcțiile INDEX și MATCH pentru a recupera costul corect pe baza valorilor de lățime și înălțime introduse în K6 și K7.
Rețineți că căutarea se bazează pe o potrivire aproximativă. Deoarece valorile sunt în ordine crescătoare, MATCH verifică valorile până la atingerea unei valori mai mari, apoi face un pas înapoi și returnează poziția anterioară.
Să construim o regulă de formatare condiționată pentru a evidenția rândul și coloana potrivite.
Ca întotdeauna cu o formatare condițională mai dificilă, vă recomand să lucrați mai întâi cu formule fictive și apoi să transferați o formulă de lucru direct în regula de formatare condițională. În acest fel, puteți utiliza toate instrumentele Excel atunci când depanați formula, ceea ce vă va economisi mult timp.
Mai întâi voi seta formula pentru lățime. Trebuie să returnăm TRUE pentru fiecare celulă din rândul 7, unde lățimea potrivită este 200.
Aceasta înseamnă că începem formula noastră cu $ B5 = și trebuie să blocăm coloana.
= $ B5 =
Acum, nu putem căuta 275 în coloana de lățimi, deoarece nu este acolo. În schimb, avem nevoie de o potrivire aproximativă care să găsească 200, la fel ca formula noastră de căutare.
Cel mai simplu mod este de a face acest lucru prin utilizarea funcției LOOKUP. LOOKUP face automat o potrivire aproximativă și, în loc să returneze o poziție precum MATCH, LOOKUP returnează valoarea reală de potrivire. Deci, putem scrie:
$ B5 = CAUTARE ($ K $ 6, $ B $ 6: $ B $ 12)
Cu lățimea noastră de intrare pentru valoarea de căutare și toate lățimile din tabel pentru vectorul de rezultat.
Dacă folosesc F9, puteți vedea valoarea LOOKUP returnată.
Acum, când introduc formula peste masă, obținem ADEVĂRAT pentru fiecare celulă din rândul de 200 de lățimi.
Acum trebuie să extindem formula pentru a se potrivi cu coloana de înălțime. Pentru a face acest lucru, voi adăuga funcția SAU și apoi o a doua formulă pentru a se potrivi cu înălțimea.
Vom începe formula în același mod, dar de data aceasta trebuie să blocăm rândul:
= B $ 5
Apoi folosim din nou funcția LOOKUP cu înălțime pentru valoarea de căutare și și toate înălțimile din tabel ca vector de rezultat.
= SAU ($ B5 = CĂUTARE ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = CĂUTARE ($ K $ 7, $ C $ 5: $ H $ 5))
Când copiez formula peste masă, obținem ADEVĂRAT pentru fiecare celulă din coloana potrivită și pentru fiecare celulă din rândul potrivit - exact ceea ce avem nevoie pentru formatarea condiționată.
Pot să copiez exact formula din celula din stânga sus exact și să creez o regulă nouă.
Acum, dacă schimb lățimea sau înălțimea, evidențierea funcționează așa cum era de așteptat.
În cele din urmă, dacă doriți doar să evidențiați valoarea de căutare în sine, este o simplă schimbare. Editați doar formula și înlocuiți funcția SAU cu funcția ȘI.
= ȘI ($ B5 = CĂUTARE ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = CĂUTARE ($ K $ 7, $ C $ 5: $ H $ 5))
Curs
Formatarea condiționalăComenzi rapide asociate
Introduceți aceleași date în mai multe celule Ctrl + Enter ⌃ + Return Afișați caseta de dialog Lipire specială Ctrl + Alt + V ⌘ + ⌃ + V Comutați referințele absolute și relative F4 ⌘ + T
