Iedomājies situāciju - Tev jāiedod noteikts rezultāts vai vērtējums balstoties uz skaitlisku skalu. Piemēram, eksāmena rezultātam jāpiešķir atzīme, pamatojoties uz iegūto punktu skaitu. Esmu redzējis vairākus risinājuma variantus. Piemēram, kāds uzskaita visus iespējamos rezultātus tabulā, piešķir tiem vērtības un izmanto VLOOKUP formulu. Ir redzētas arī dažādas sarežģītības IF formulu konstrukcijas. Šoreiz piedāvāju divus gana universālus variantus:
Risinājums ar formulu MATCH kombinācijā ar HLOOKUP
Risinājums ar formulu VLOOKUP
Noskaties video un zemāk lasi detalizēti pa soļiem.
Piemēram esmu sagatavojis ASV atzīmju vērtēšanas skalu, pēc kuras nepieciešams noteikt vērtējumu. Protams, skala ir tikai indikatīva un katram gadījumam var piemērot atbilstošu.
0-59 => F
60-66 => D
67-69 => D+
70-72 => C-
73-76 => C
77-79 => C+
80-82 => B-
83-86 => B
87-89 => B+
90-92 => A-
93-100 => A
Piemēram, ja eksāmenā saņemts rezultāts zem 60 punktiem, tad atzīme ir F, ja punktu skaits ir no 60 līdz 66 ieskaitot, tad atzīme ir D. Lai darbību paveiktu ar VLOOKUP, būtu jāsastāda tabula ar vērtībām no 0 līdz 100 un, iespējams, rastos grūtības izvērtēt vērtības ar decimāldaļām. Tāpat šādu izvērtējumu veikt ar IF formulām būtu pārlieku sarežģīti.
MATCH izmantošana
Ķeramies klāt. Iesākumam sakārtosim datus šādā tabulā.
B kolonā redzama zemākā vērtība rezultāta kategorijai. Tātad sākot no 0, atzīme ir F, sākot no 60, atzīme ir D, utt. Šajā gadījumā izmantošu MATCH formulas parametru 'match_type'=1, lai norādītu uz lielāko vērtību, kura ir mazāka vai vienāda ar meklējamo vērtību.
T.i., norādot vērtību 85, lielākā vērtība, kura ir mazāka par 85, būs 83.
=MATCH(lookup_value, lookup_array, [match_type])
lookup value - meklējamā vērtība
table_array - apgabals, kurā meklēt šo vērtību
[match_type] -
'1' - atrast lielāko vērtību, kura mazāka par meklējamo vērtību (vērtībām jābūt sakārtotām augošā secībā)
'0' - atrast precīzu meklējamo vērtību
'-1' - atrast mazāko vērtību, kura lielāka kā meklējamā vērtība (vērtībām jābūt sakārtotām dilstošā secībā)
Manā piemērā izmantošu parametru '1' un vērtības sakārtoju augošā secībā. Robežvērtības tiks pieskaitītas kategorijai, kura atrodas vērtības rindā (piemēram, 60 atbilst "D"). Ja vēlies, lai norādītā robežvērtība tiktu pieskaitīta iepriekšējai kategorijai, sakārto vērtības dilstošā secībā un izvēlies parametru '-1'.
Rezultātā iegūst 8, kas norāda uz to, ka meklējamā vērtība atrodas 8. pozīcijā meklēšanas apgabalā.
HLOOKUP iesaistīšana
Rezultāta iegūšanai izmantošu HLOOKUP formulu (horizontālā meklēšana), kas ir līdzīga VLOOKUP tikai meklēšana notiek horizontāli nevis vertikāli.
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
lookup_value - meklējamā vērtība table_array - apgabals, kurā meklēt row_index_num - rindas numurs, no kuras atgriezt rezultātu [range_lookup] - neobligāts parametrs. FALSE - atgriezt precīzu vērtību, TRUE - atgriezt aptuvenu vērtību, ja netiek atrasta tieši atbilstoša vērtība, tiek atgriezta nākamā lielākā vērtība, kas ir mazāka par meklējamo vērtību.
Formulas konstrukcijā meklējamā vērtība man ir "Grade" (jeb šūna F5), apgabals ir B2:D13 (kas ietver arī nosaukumu rindu), rinda, kurā atrodas atgriežamā vērtība ir MATCH formulā iegūtais skaitlis plus 1, jo apgabals paplašināts ar nosaukuma rindu. Visbeidzot gribu, lai formula atgriež precīzu rezultātu, tādēļ beigās norādu FALSE. Spied Enter un rezultāts ir gatavs.
Formulu var ielīmēt arī G7 šūnā un tā atgriezīs tieši "Grade points" jeb atzīmes punktus. To nodrošina HLOOKUP, jo meklēta tiks vērtība "Grade points" un atgriezta vērtība tieši no šīs kolonas. Tātad svarīgi, lai HLOOKUP izmantotā meklējamā vērtība būtu atrodama meklēšanas apgabalā.
Vienkāršs VLOOKUP izmantošanas paņēmiens
Šis varbūt šķitīs pat vienkāršāks variants par iepriekšējo. Kā zināms VLOOKUP arī izmanto parametru [range_lookup]. Un tieši šo mazo āķi arī izmantošu.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value - meklējamā vērtība table_array - apgabals, kurā meklēt col_index_num - kolonnas numurs, no kuras atgriezt rezultātu [range_lookup] - neobligāts parametrs. FALSE - atgriezt precīzu vērtību, TRUE - atgriezt aptuvenu vērtību, ja netiek atrasta tieši atbilstoša vērtība, tiek atgriezta nākamā lielākā vērtība, kas ir mazāka par meklējamo vērtību. Arī šajā gadījumā vērtībām jābūt sakārtotām augošā secībā.
Kā redzi kolonnas numuru norādīju manuāli (3). Perfektumam, protams, prasās uzbūvēt formulu bez konstantēm un visur lietot mainīgos. Iespējams, atceries, ka iepriekš esmu rakstījis, kā aizvietot kolonnas numuru ar MATCH formulu (par to lasi šeit). Gala rezultāts ir šāds:
Kopsavilkums
Šie ir divi varianti, kā tikt galā peldošas skalas vērtējuma noteikšanu. Ja Tev jānosaka tikai vērtības kārtas skaitlis (reitings), tad MATCH būs vienkāršākais variants. Ja esi ar mieru izmantot statiskās VLOOKUP kolonnas indeksā, tad VLOOKUP, iespējams, būs vienkāršāks variants. Jebkurā gadījumā, abi ir labi un attiecīgā situācijā izmantojami.
Paldies, ka lasīji un skatījies. Ceru, ka mani padomi Tev noderēs. Lejupielādē darba failu un atceries pierakstīties jaunumiem!
Comments