Šajā videoklipā mēs aplūkosim, kā izcelt aptuvenos atbilstības meklējumus ar nosacītu formatējumu.
Šeit mums ir vienkārša uzmeklēšanas tabula, kas parāda materiālu izmaksas dažādiem augstumiem un platumiem. K8 formulā tiek izmantotas funkcijas INDEX un MATCH, lai izgūtu pareizās izmaksas, pamatojoties uz platuma un augstuma vērtībām, kas ievadītas K6 un K7.
Ņemiet vērā, ka uzmeklēšanas pamatā ir aptuvena atbilstība. Tā kā vērtības ir augošā secībā, MATCH pārbauda vērtības, līdz tiek sasniegta lielāka vērtība, un pēc tam atkāpjas un atgriež iepriekšējo pozīciju.
Izveidosim nosacītu formatēšanas kārtulu, lai iezīmētu atbilstošo rindu un kolonnu.
Kā vienmēr, izmantojot sarežģītāku nosacīto formatēšanu, iesaku vispirms strādāt ar manekena formulām un pēc tam darba formulu pārsūtīt tieši uz nosacījuma formatēšanas kārtulu. Tādējādi, atkļūdojot formulu, varat izmantot visus Excel rīkus, kas ļaus ietaupīt daudz laika.
Vispirms es iestatīšu platuma formulu. Mums jāatgriež TRUE katrai 7. rindas šūnai, kur saskaņotais platums ir 200.
Tas nozīmē, ka mēs sākam formulu ar $ B5 =, un mums ir jābloķē kolonna.
= $ B5 =
Tagad mēs nevaram meklēt 275 slejā Platumi, jo tā tur nav. Tā vietā mums ir nepieciešama aptuvena atbilstība, kas atrod 200, tāpat kā mūsu uzmeklēšanas formula.
Vieglākais veids ir to izdarīt, izmantojot funkciju LOOKUP. LOOKUP automātiski veic aptuvenu atbilstību, un tā vietā, lai atgrieztu tādu pozīciju kā MATCH, LOOKUP atgriež faktisko atbilstības vērtību. Tātad, mēs varam rakstīt:
$ B5 = MEKLĒŠANA ($ K $ 6, $ B $ 6: $ B $ 12)
Ar mūsu meklēšanas vērtības ievades platumu un visiem rezultātu vektora tabulas platumiem.
Ja es izmantoju F9, jūs varat redzēt vērtību LOOKUP atgriež.
Tagad, kad es tabulā ievadu formulu, mēs iegūstam PATIESĪBU par katru šūnu 200 platuma rindā.
Tagad mums ir jāpaplašina formula, lai tā atbilstu augstuma kolonnai. Lai to izdarītu, es pievienošu funkciju OR un pēc tam otru formulu, kas atbilst augstumam.
Mēs sāksim formulu tādā pašā veidā, bet šoreiz mums ir jābloķē rinda:
= B $ 5
Tad mēs atkal izmantojam funkciju MEKLĒŠANA ar meklēšanas rezultāta vektoru ar augstumu uzmeklēšanas vērtībai un visiem tabulas augstumiem.
= VAI ($ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = LOOKUP ($ K $ 7, $ C $ 5: $ H $ 5))
Kad es kopēju formulu visā tabulā, mēs iegūstam PATIESĪBU katrai atbilstošās kolonnas šūnai un katrai atbilstošās rindas šūnai - tieši tas, kas mums nepieciešams nosacītai formatēšanai.
Es varu vienkārši precīzi nokopēt formulu kreisajā augšējā šūnā un izveidot jaunu likumu.
Tagad, ja es mainu platumu vai augstumu, izcelšana darbojas kā paredzēts.
Visbeidzot, ja vēlaties izcelt tikai pašu uzmeklēšanas vērtību, tās ir vienkāršas izmaiņas. Vienkārši rediģējiet formulu un aizstājiet funkciju OR ar funkciju AND.
= UN ($ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = LOOKUP ($ K $ 7, $ C $ 5: $ H $ 5))
Protams
Nosacījuma formatēšanaSaistītie saīsnes
Ievadiet tos pašus datus vairākās šūnās Ctrl + Enter ⌃ + Return Parādiet dialoglodziņu Īpašā ielīmēšana Ctrl + Alt + V ⌘ + ⌃ + V Pārslēdziet absolūtās un relatīvās atsauces F4 ⌘ + T
