Bidimensional cautartea tabel (CDF 2d)
Dacă sunteți familiarizat cu funcția de CDF (VLOOKUP) sau analogul orizontal PGR (HLOOKUP). trebuie să ne amintim că aceste funcții remarcabile caută informații despre un singur parametru, și anume într-o matrice unidimensională - pe linie sau coloana. Și dacă avem nevoie pentru a selecta date dintr-un tabel bidimensional Întâmplător doar doi parametri - și de rânduri și coloane, în același timp? Să ne uităm la un exemplu în viața reală a unor astfel de probleme și soluțiile lor.
Exemplul 1. Găsiți valoarea produsului și orașul
Să presupunem că avem aici este o matrice bidimensională de date cu privire la orașe și bunuri:
Utilizatorul introduce (sau alege din listele derulante) în celulele galbene ale produsului drept și oraș. Celula verde, trebuie să găsim formula și a afișa numărul tabelului corespunzător parametrii selectați. De fapt, dorim să găsim valoarea unei celule la intersecția dintre un rând și coloană specifică în tabel. Pentru claritate, vom împărți sarcina în trei etape.
Total, combinând toate cele de mai sus într-o singură formulă, soluția pentru celulele verzi:
INDEX = (B2: F10; MECI (J2; A2: A10; 0), MECI (J3; B1: F1; 0))
sau în limba engleză versiunea = INDEX (B2: F10; MECI (J2; A2: A10; 0); POTRIVIRE (J3; B1: F1; 0))
Exemplul 2. căutare dimensională aproximativă
modifica ușor exemplul anterior. Să presupunem că avem o situație aici:
Ideea este că utilizatorul trebuie să intre în înălțimea celulei galben și lățimea ușii, de exemplu, cabinet, pe care vrea să comande produsele companiei, iar în caseta gri ar trebui să apară valoarea din tabel. O rezervă importantă este că, dacă un utilizator introduce un valori de dimensiuni non-standard, acestea ar trebui să fie rotunjite în mod automat la cel mai apropiat disponibil în tabel și în caseta gri ar trebui să apară în costul de fabricație a ușii pentru aceste dimensiuni Standart rotunjite.
Soluția pentru celula gri este aproape complet analog cu exemplul anterior:
INDEX = (C7: K16; MECI (D3; B7: B16; 1), MECI (G3; C6: K6; 1))
= INDEX (C7: K16; MECI (D3; B7: B16; 1); POTRIVIRE (G3; C6: K6; 1))
- 1 - găsi cel mai apropiat număr mic, și anume dimensiunea ușii introduse de către utilizator va fi rotunjit la cel mai apropiat cea mai mică dimensiune adecvată a tabelului. În acest caz, înălțimea va fi rotunjită la 500 450, iar lățimea de 480 la 300, iar costul ușii ar fi fost de 135.
- -1 - găsi cel mai apropiat de cel mai mare număr, și anume înălțimea non-standard de 500 ar fi rotunjite până la 700, iar lățimea 480 - 600, iar costul ar fi ridicat la 462. Pentru afaceri mult mai interesant. )
- 0 - caută exact, fără nici o rotunjire. Folosit pentru valoarea-țintă de 100% cu una dintre valorile din tabel. Firește, se aplică atunci când se caută parametrii de text (ca în exemplul anterior), deoarece Rotunjirea nu este posibil pentru ei.
Este important de notat că atunci când se utilizează o căutare aproximativă cu interval de căutare este rotunjit - și, prin urmare, întregul tabel - ar trebui să fie sortate în ordine crescătoare (pentru comparație Type = 1) sau descendent (pentru comparație Type = -1) peste liniile și coloanele. În caz contrar, căutarea aproximativă nu va funcționa corect!
Pentru a rafina căutarea (tipul de potrivire = 0) sortarea nu este necesară și nu contează.
P.S. Problema inversă
Principiul de funcționare este după cum urmează:
- prin fiecare celulă din gama B2: F10 și căutarea unui meci cu valoarea dorită (13) a celulelor J4 folosind IF funcția (IF)
- se potrivesc atunci când a constatat, apoi determină numărul rândului (coloana) din tabelul primului element din rândul (coloana) cu ajutorul funcțiilor COLOANA (COLOANA) și LINE (ROW)
- scoate valoarea orașului sau a mărfurilor dintr-un tabel cu ajutorul indexului (INDEX)