Salt la conținutul principal

Căutați cea mai apropiată valoare de potrivire cu mai multe criterii

În unele cazuri, poate fi necesar să căutați valoarea de potrivire cea mai apropiată sau aproximativă pe baza mai multor criterii. Cu combinația de INDEX, MECI și IF funcții, puteți realiza rapid în Excel.


Cum să căutați cea mai apropiată valoare de potrivire cu mai multe criterii?

După cum se arată în imaginea de mai jos, trebuie să găsiți persoana potrivită pentru locul de muncă pe baza a două criterii „majoritatea este Calculator”Și„ experiența de lucru este 15 ani".

notițe: Pentru ca acest lucru să funcționeze corect, dacă conține duplicate majore, experiențele de lucru ale acestor duplicate majors ar trebui să fie sortate în ordine crescătoare.

1. Selectați o celulă goală pentru a afișa rezultatul, apoi copiați formula de mai jos în ea și apăsați pe Ctrl + Schimba + Intrați chei pentru a obține rezultatul.

=INDEX(D3:D8,MATCH(G5,IF(B3:B8=G4,C3:C8),1))

note: în această formulă:

  • D3: D8 este gama de coloane conține rezultatul pe care îl căutați;
  • G5 conține al doilea criteriu (experiența numărul 15) pe care veți căuta valoarea pe baza;
  • G4 conține primele criterii (computer) pe care veți căuta valoarea pe baza;
  • B3: B8 este gama de celule care corespunde primelor criterii;
  • C3: C8 este gama de celule care corespunde celui de-al doilea criteriu;
  • Număr 1 este căutare aproximativă, ceea ce înseamnă că, dacă valoarea exactă nu poate fi găsită, va găsi cea mai mare valoare care este mai mică decât valoarea căutării;
  • Această formulă trebuie introdusă ca o formulă matrice cu Ctrl + Schimba + Intrați chei.

Cum funcționează această formulă

Această formulă poate fi împărțită în mai multe componente:

  • IF(B3:B8=G4,C3:C8): funcția IF aici returnează rezultatul ca {9;13;FALSE;FALSE;FALSE;FALSE}, care provine din testarea valorilor din B3: B8 pentru a vedea dacă se potrivesc cu valoarea din G4. Dacă există o potrivire, atunci returnează valoarea corespunzătoare, în caz contrar, returnează FALS. Aici găsește două potriviri și patru nepotriviri.
  • O formulă matrice =MATCH(G5,{9;13;FALSE;FALSE;FALSE;FALSE},1): funcția MATCH găsește poziția numărului 15 (valoarea din G5) în intervalul C3: C8. Deoarece numărul 15 nu poate fi găsit, acesta se potrivește cu următoarea cea mai mică valoare 13. Deci, rezultatul aici este 2.
  • Și =INDEX(D3:D8,2): Funcția INDEX returnează valoarea celei de-a doua celule din intervalul D3: D8. Deci rezultatul final este Amy.

Funcții conexe

Funcția Excel IF
Funcția IF este una dintre cele mai simple și utile funcții din registrul de lucru Excel. Realizează un test logic simplu care, în funcție de rezultatul comparației, și returnează o valoare dacă un rezultat este ADEVĂRAT sau o altă valoare dacă rezultatul este FALS.

Funcția Excel MATCH
Funcția MATCH Microsoft Excel caută o anumită valoare într-un interval de celule și returnează poziția relativă a acestei valori.

Funcția Excel INDEX
Funcția INDEX returnează valoarea afișată pe baza unei poziții date dintr-un interval sau dintr-o matrice.


Articole pe aceeași temă

Celule medii pe baza mai multor criterii
În Excel, cei mai mulți dintre noi suntem familiarizați cu funcțiile COUNTIF și SUMIF, ne pot ajuta să numărăm sau să sumăm valori pe baza criteriilor. Dar ați încercat vreodată să calculați media valorilor pe baza unuia sau mai multor criterii din Excel? Acest tutorial oferă exemple și formule în detalii pentru a ajuta la realizarea cu ușurință.
Faceți clic pentru a afla mai multe ...

Numărați celulele dacă este îndeplinit unul dintre criteriile multiple
Acest tutorial împărtășește modalitățile de numărare a celulelor dacă conține X sau Y sau Z ... etc. în Excel.
Faceți clic pentru a afla mai multe ...

Numărați valori unice pe baza mai multor criterii
Acest articol ia câteva exemple pentru a număra valori unice bazate pe unul sau mai multe criterii într-o foaie de lucru cu metode detaliate pas cu pas.
Faceți clic pentru a afla mai multe ...


Cele mai bune instrumente de productivitate Office

Kutools pentru Excel - Vă ajută să vă distingeți de mulțime

Caracteristici populare: Găsiți, evidențiați sau identificați duplicatele  |  Ștergeți rândurile goale  |  Combinați coloane sau celule fără a pierde date  |  Rundă fără Formula ...
Super VLookup: Criterii multiple  |  Valoare multiplă  |  Pe mai multe foi  |  Căutare fuzzy...
Adv. Lista verticală: Lista drop-down ușoară  |  Listă drop-down dependentă  |  Listă derulantă cu selectare multiplă...
Manager de coloane: Adăugați un număr specific de coloane  |  Mutați coloanele  |  Comutați starea vizibilității coloanelor ascunse  Comparați coloanele cu Selectați aceleași și diferite celule ...
Caracteristici prezentate: Focus pe grilă  |  Vedere de proiectare  |  Big Formula Bar  |  Manager registru de lucru și foi | Biblioteca de resurse (Text automat)  |  Data Picker  |  Combinați foi de lucru  |  Criptare/Decriptare celule  |  Trimiteți e-mailuri după listă  |  Super Filtru  |  Filtru special (filtrează bold/italic/barat...) ...
Top 15 seturi de instrumente12 Text Instrumente (Adăuga text, Eliminați caractere ...)  |  50+ Diagramă Tipuri de (Gantt Chart ...)  |  40+ Practic Formule (Calculați vârsta pe baza zilei de naștere ...)  |  19 inserare Instrumente (Introduceți codul QR, Inserați imaginea din cale ...)  |  12 Convertire Instrumente (Numere la cuvinte, conversie valutara ...)  |  7 Merge & Split Instrumente (Rânduri combinate avansate, Împărțiți celule Excel ...)  |  ... și altele

Kutools pentru Excel se mândrește cu peste 300 de caracteristici, Asigurați-vă că ceea ce aveți nevoie este la doar un clic distanță...


Fila Office - Activați lectura și editarea cu file în Microsoft Office (includeți Excel)

  • O secundă pentru a comuta între zeci de documente deschise!
  • Reduceți sute de clicuri de mouse pentru dvs. în fiecare zi, spuneți adio de la mâna mouse-ului.
  • Vă crește productivitatea cu 50% atunci când vizualizați și editați mai multe documente.
  • Aduce file eficiente la Office (inclusiv Excel), la fel ca Chrome, Edge și Firefox.
Comments (1)
Rated 0.5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Keep yrs at "15" and switch major to "science"...formula busts. This is not a robust formula...
Rated 0.5 out of 5
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations