✅ Atrodiet vistuvāko atbilstību programmā Excel, izmantojot formulas

Satura rādītājs

Atrodiet tuvāko atbilstību programmā Excel

Excel funkcijas var būt ārkārtīgi spēcīgas, ja vēlaties apvienot dažādas formulas. Lietas, kas varētu šķist neiespējamas, pēkšņi sāktu izskatīties kā bērnu rotaļas.

Viens no šādiem piemēriem ir atrast uzmeklēšanas vērtības vistuvāko atbilstību Excel datu kopā.

Programmā Excel ir pāris noderīgas uzmeklēšanas funkcijas (piemēram, VLOOKUP & INDEX MATCH), kas dažos vienkāršos gadījumos var atrast tuvāko atbilstību (kā es parādīšu ar piemēriem zemāk).

Bet labākais ir tas, ka varat apvienot šīs uzmeklēšanas funkcijas ar citām Excel funkcijām, lai paveiktu daudz vairāk (tostarp atrast tuvāko meklēšanas vērtības atbilstību nešķirotā sarakstā).

Šajā apmācībā es parādīšu, kā jūs atrodat Excel tuvāko meklēšanas vērtības atbilstību ar uzmeklēšanas formulām.

Atrodiet tuvāko atbilstību programmā Excel

Var būt daudz dažādu scenāriju, kad jums ir jāmeklē tuvākā atbilstība (vai tuvākā atbilstošā vērtība).

Tālāk ir sniegti piemēri, kurus es apskatīšu šajā rakstā:

Atrodiet komisijas maksu, pamatojoties uz pārdošanu
Atrodiet labāko kandidātu (pamatojoties uz tuvāko pieredzi)
Nākamā notikuma datuma atrašana

Sāksim!

Noklikšķiniet šeit, lai lejupielādētu parauga failu

Atrast komisijas maksu (meklējot tuvāko pārdošanas vērtību)

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, kur vēlaties atrast visu pārdošanas personāla komisijas likmes.

Komisija tiek piešķirta, pamatojoties uz pārdošanas vērtību. Un to aprēķina, izmantojot tabulu labajā pusē.

Piemēram, ja pārdevējs veic kopējos pārdošanas apjomus 5000, tad komisijas maksa ir 0% un, ja viņš/viņa veic kopējos pārdošanas apjomus 15000, komisijas maksa ir 5%.

Lai iegūtu komisijas maksu, jums jāatrod tuvākais pārdošanas diapazons, kas ir tikai zemāks par pārdošanas vērtību. Piemēram, par 15 000 pārdošanas vērtībām komisijas maksa būtu 10 000 (kas ir 5%), bet pārdošanas vērtība - 25 000, komisijas maksa būtu 20 000 (kas ir 7%).

Lai atrastu tuvāko pārdošanas vērtību un iegūtu komisijas maksu, varat izmantot aptuveno atbilstību vietnē VLOOKUP.

Tālāk sniegtā formula to darītu:

= MEKLĒŠANA (B2, $ E $ 2: $ F $ 6,2,1)

Ņemiet vērā, ka šajā formulā pēdējais arguments ir 1, kas liek formulai izmantot aptuvenu uzmeklēšanu. Tas nozīmē, ka formula iet caur pārdošanas vērtībām E slejā un atrod vērtību, kas ir tikai zemāka par uzmeklēšanas vērtību.

Tad VLOOKUP formula sniegs komisijas maksu par šo vērtību.

Piezīme: Lai tas darbotos, jums ir jāsakārto dati augošā secībā.

Noklikšķiniet šeit, lai lejupielādētu parauga failu

Atrodiet labāko kandidātu (pamatojoties uz tuvāko pieredzi)

Iepriekš minētajā piemērā dati bija jāsakārto augošā secībā. Bet var būt gadījumi, kad dati netiek sakārtoti.

Tātad apskatīsim piemēru un redzēsim, kā mēs varam atrast Excel tuvāko atbilstību, izmantojot formulu kombināciju.

Zemāk ir datu kopas paraugs, kur man jāatrod tā darbinieka vārds, kura darba pieredze ir vistuvāk vēlamajai vērtībai. Vēlamā vērtība šajā gadījumā 2,5 gadu laikā.

Ņemiet vērā, ka dati nav sakārtoti. Arī tuvākā pieredze var būt mazāka vai lielāka par doto pieredzi. Piemēram, 2 gadi un 3 gadi ir vienādi tuvi (atšķirība 0,5 gadi).

Zemāk ir formula, kas mums sniegs rezultātu:

= INDEKSS ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15))), ABS (D2- $ B $ 2: $ B $ 15), 0))

Šīs formulas triks ir mainīt uzmeklēšanas masīvu un uzmeklēšanas vērtību, lai atrastu minimālo pieredzes atšķirību nepieciešamajās un faktiskajās vērtībās.

Vispirms sapratīsim, kā jūs to darītu manuāli (un tad es paskaidrošu, kā šī formula darbojas).

Veicot to manuāli, jūs iziesit katru B slejas šūnu un atradīsit atšķirību pieredzē starp prasīto un personas rīcībā esošo. Kad jums ir visas atšķirības, jūs atradīsit minimālo un iegūsit šīs personas vārdu.

Tieši to mēs darām ar šo formulu.

Ļauj man paskaidrot.

Uzmeklēšanas vērtība MATCH formulā ir MIN (ABS (D2-B2: B15)).

Šī daļa sniedz minimālo atšķirību starp doto pieredzi (kas ir 2,5 gadi) un visu pārējo pieredzi. Šajā piemērā tas atgriež 0.3

Ņemiet vērā, ka esmu izmantojis ABS, lai pārliecinātos, ka meklēju tuvāko (kas var būt vairāk vai mazāk par doto pieredzi).

Tagad šī minimālā vērtība kļūst par mūsu meklēšanas vērtību.

Funkcijas MATCH uzmeklēšanas masīvs ir ABS (D2- $ B $ 2: $ B $ 15).

Tādējādi mēs iegūstam skaitļu masīvu, no kura ir atņemta 2,5 (nepieciešamā pieredze).

Tagad mums ir uzmeklēšanas vērtība (0,3) un uzmeklēšanas masīvs ({6,8; 0,8; 19,5; 21,8; 14,5; 11,2; 0,3; 9,2; 2; 9,8; 14,8; 0,4; 23,8; 2,9})

Funkcija MATCH šajā masīvā atrod 0,3 pozīciju, kas ir arī tās personas vārda pozīcija, kurai ir tuvākā pieredze.

Šo pozīcijas numuru funkcija INDEX izmanto, lai atgrieztu personas vārdu.

Piezīme. Gadījumā, ja ir vairāki kandidāti, kuriem ir vienāda minimālā pieredze, iepriekš minētajā formulā tiks norādīts pirmā atbilstošā darbinieka vārds.

Atrodiet nākamā notikuma datumu

Šis ir vēl viens piemērs, kurā varat izmantot uzmeklēšanas formulas, lai atrastu nākamo notikuma datumu, pamatojoties uz pašreizējo datumu.

Zemāk ir datu kopa, kurā ir notikumu nosaukumi un notikumu datumi.

Es gribu nākamā notikuma nosaukumu un šī gaidāmā notikuma datumu.

Zemāk ir formula, kas sniegs gaidāmā notikuma nosaukumu:

= INDEKSS ($ A $ 2: $ A $ 11, MATCH (E1, $ B $ 2: $ B $ 11,1) +1)

Un zemāk esošā formula sniegs gaidāmā notikuma datumu:

= INDEKSS ($ B $ 2: $ B $ 11, MATCH (E1, $ B $ 2: $ B $ 11,1) +1)

Ļaujiet man paskaidrot, kā šī formula darbojas.

Lai iegūtu notikuma datumu, funkcija MATCH B slejā meklē pašreizējo datumu. Šādā gadījumā mēs meklējam nevis precīzu atbilstību, bet aptuvenu. Līdz ar to pēdējais MATCH funkcijas arguments ir 1 (kas atrod lielāko vērtību, kas ir mazāka vai vienāda ar uzmeklēšanas vērtību).

Tātad MATCH funkcija atgriezīs šūnas stāvokli, kuras datums ir tikai mazāks vai vienāds ar pašreizējo datumu. Tātad nākamais notikums šajā gadījumā būtu nākamajā šūnā (jo saraksts ir sakārtots augošā secībā).

Tātad, lai uzzinātu gaidāmā notikuma datumu, vienkārši pievienojiet vienu šūnu pozīcijai, ko atgriež funkcija MATCH, un tā jums parādīs nākamā notikuma datuma šūnas atrašanās vietu.

Pēc tam šo vērtību norāda funkcija INDEX.

Lai iegūtu notikuma nosaukumu, tiek izmantota tā pati formula, un funkcijas INDEX diapazons tiek mainīts no kolonnas B uz kolonnu A.

Noklikšķiniet šeit, lai lejupielādētu parauga failu

Šī piemēra ideja man radās, kad draugs vērsās pie lūguma. Viņam slejā bija visu draugu/radinieku dzimšanas dienu saraksts un viņš vēlējās uzzināt nākamo dzimšanas dienu (un personas vārdu).

Šie ir trīs piemēri, kas parāda, kā programmā Excel atrast tuvāko atbilstošo vērtību, izmantojot uzmeklēšanas formulas.

Jums varētu patikt arī šādi Excel padomi/apmācības