✅ Găsiți cea mai apropiată potrivire în Excel folosind formule

Cuprins

Găsiți cea mai apropiată potrivire în Excel

Funcțiile Excel pot fi extrem de puternice dacă aveți o combinație de formule diferite. Lucrurile care ar fi putut părea imposibile ar începe brusc să pară jocul unui copil.

Un astfel de exemplu este de a găsi cea mai apropiată potrivire a unei valori de căutare într-un set de date în Excel.

Există câteva funcții utile de căutare în Excel (cum ar fi VLOOKUP & INDEX MATCH), care pot găsi cea mai apropiată potrivire în câteva cazuri simple (așa cum voi arăta cu exemple mai jos).

Dar cea mai bună parte este că puteți combina aceste funcții de căutare cu alte funcții Excel pentru a face mult mai mult (inclusiv găsirea celei mai apropiate potriviri a unei valori de căutare într-o listă nesortată).

În acest tutorial, vă voi arăta cum găsiți cea mai apropiată potrivire a unei valori de căutare în Excel cu formule de căutare.

Găsiți cea mai apropiată potrivire în Excel

Pot exista multe scenarii diferite în care trebuie să căutați cea mai apropiată potrivire (sau cea mai apropiată valoare de potrivire).

Mai jos sunt exemplele pe care le voi acoperi în acest articol:

Găsiți rata comisionului pe baza vânzărilor
Găsiți cel mai bun candidat (pe baza celei mai apropiate experiențe)
Găsirea următoarei date a evenimentului

Să începem!

Faceți clic aici pentru a descărca fișierul de exemplu

Găsiți rata comisionului (căutați cea mai apropiată valoare de vânzare)

Să presupunem că aveți un set de date așa cum se arată mai jos, în care doriți să găsiți ratele de comision pentru tot personalul de vânzări.

Comisionul este alocat în funcție de valoarea vânzării. Și acest lucru este calculat folosind tabelul din dreapta.

De exemplu, dacă un agent de vânzări realizează vânzările totale de 5000, atunci comisionul este de 0%, iar dacă el / ea face vânzările totale de 15000, comisionul este de 5%.

Pentru a obține rata comisionului, trebuie să găsiți cea mai apropiată gamă de vânzare doar mai mică decât valoarea de vânzare. De exemplu, pentru valoarea de vânzare de 15000, comisionul ar fi de 10.000 (care este 5%), iar pentru valoarea de vânzare de 25000, rata comisionului ar fi de 20.000 (care este 7%).

Pentru a găsi cea mai apropiată valoare de vânzare și a obține rata comisionului, puteți utiliza potrivirea aproximativă în VLOOKUP.

Formula de mai jos ar face acest lucru:

= VLOOKUP (B2, $ E $ 2: $ F $ 6,2,1)

Rețineți că în această formulă, ultimul argument este 1, care spune formulei să utilizeze o căutare aproximativă. Aceasta înseamnă că formula va trece prin valorile de vânzare din coloana E și ar găsi valoarea care este doar mai mică decât valoarea de căutare.

Apoi formula VLOOKUP va da rata comisionului pentru această valoare.

Notă: Pentru ca acest lucru să funcționeze, trebuie să aveți datele sortate în ordine crescătoare.

Faceți clic aici pentru a descărca fișierul de exemplu

Găsiți cel mai bun candidat (pe baza celei mai apropiate experiențe)

În exemplul de mai sus, datele trebuiau sortate în ordine crescătoare. Dar ar putea exista cazuri în care datele nu sunt sortate.

Deci, să prezentăm un exemplu și să vedem cum putem găsi cea mai apropiată potrivire în Excel folosind o combinație de formule.

Mai jos este un exemplu de set de date în care trebuie să găsesc numele angajatului care are experiența de lucru cea mai apropiată de valoarea dorită. Valoarea dorită în acest caz în 2,5 ani.

Rețineți că datele nu sunt sortate. De asemenea, cea mai apropiată experiență poate fi fie mai mică, fie mai mult decât experiența de a da. De exemplu, 2 ani și 3 ani sunt amândoi la fel de apropiați (diferență de 0,5 ani).

Mai jos este formula care ne va da rezultatul:

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

Trucul din această formulă este de a schimba matricea de căutare și valoarea de căutare pentru a găsi diferența minimă de experiență în valorile necesare și reale.

Mai întâi să înțelegem cum ați face-o manual (și apoi vă voi explica cum funcționează această formulă).

Când faceți acest lucru manual, veți parcurge fiecare celulă din coloana B și veți găsi diferența în experiența dintre ceea ce este necesar și cel pe care îl are o persoană. Odată ce ai toate diferențele, îl vei găsi pe cel care este minim și vei prelua numele acelei persoane.

Exact asta facem cu această formulă.

Lasă-mă să explic.

Valoarea de căutare din formula MATCH este MIN (ABS (D2-B2: B15)).

Această parte vă oferă diferența minimă între experiența dată (care este de 2,5 ani) și toate celelalte experiențe. În acest exemplu, returnează 0,3

Rețineți că am folosit ABS pentru a mă asigura că caut cel mai apropiat (care poate fi mai mult sau mai puțin decât experiența dată).

Acum, această valoare minimă devine valoarea noastră de căutare.

Matricea de căutare din funcția MATCH este ABS (D2- $ B $ 2: $ B $ 15).

Aceasta ne oferă o serie de numere din care s-a scăzut 2,5 (experiența necesară).

Deci, acum avem o valoare de căutare (0,3) și o matrice de căutare ({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})

Funcția MATCH găsește poziția 0.3 în această matrice, care este și poziția numelui persoanei care are cea mai apropiată experiență.

Acest număr de poziție este apoi utilizat de funcția INDEX pentru a returna numele persoanei.

Notă: În cazul în care există mai mulți candidați care au aceeași experiență minimă, formula de mai sus va da numele primului angajat care se potrivește.

Găsiți data evenimentului următor

Acesta este un alt exemplu în care puteți utiliza formule de căutare pentru a găsi următoarea dată a unui eveniment pe baza datei curente.

Mai jos este setul de date în care am nume de evenimente și datele evenimentului.

Ceea ce vreau este numele evenimentului următor și data evenimentului pentru acest eveniment viitor.

Mai jos este formula care va da numele viitorului eveniment:

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

Și formula de mai jos va da data viitoare a evenimentului:

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

Permiteți-mi să vă explic cum funcționează această formulă.

Pentru a obține data evenimentului, funcția MATCH caută data curentă în coloana B. În acest caz, nu căutăm o potrivire exactă, ci una aproximativă. Prin urmare, ultimul argument al funcției MATCH este 1 (care găsește cea mai mare valoare mai mică sau egală cu valoarea de căutare).

Deci funcția MATCH ar returna poziția celulei care are data care este doar mai mică sau egală cu data curentă. Deci, următorul eveniment, în acest caz, ar fi în celula următoare (deoarece lista este sortată în ordine crescătoare).

Deci, pentru a obține data viitoare a evenimentului, trebuie doar să adăugați una la poziția celulei returnată de funcția MATCH și vă va oferi poziția celulei la data evenimentului următor.

Această valoare este apoi dată de funcția INDEX.

Pentru a obține numele evenimentului, se folosește aceeași formulă, iar intervalul din funcția INDEX este schimbat din coloana B în coloana A.

Faceți clic aici pentru a descărca fișierul de exemplu

Ideea acestui exemplu mi-a venit atunci când un prieten s-a apropiat cu o cerere. Avea o listă a tuturor zilelor de naștere ale prietenilor / rudelor într-o rubrică și dorea să afle următoarea zi de naștere (și numele persoanei).

Acestea sunt trei exemple care arată cum să găsiți cea mai apropiată valoare de potrivire în Excel folosind formule de căutare.

Este posibil să vă placă și următoarele sfaturi / tutoriale Excel