Cuprins
În practică, foarte des există cazuri când tu și cu mine trebuie să găsim cea mai apropiată valoare dintr-un set (tabel) în raport cu un anumit număr. Ar putea fi, de exemplu:
- Calculul reducerii in functie de volum.
- Calculul sumei bonusurilor în funcție de implementarea planului.
- Calculul tarifelor de transport in functie de distanta.
- Alegerea containerelor potrivite pentru mărfuri etc.
În plus, rotunjirea poate fi necesară atât în sus, cât și în jos, în funcție de situație.
Există mai multe moduri – evidente și nu atât de evidente – de a rezolva o astfel de problemă. Să le privim secvenţial.
Pentru început, să ne imaginăm un furnizor care oferă reduceri la comerț cu ridicata, iar procentul reducerii depinde de cantitatea de mărfuri achiziționate. De exemplu, la cumpărarea a mai mult de 5 bucăți, se acordă o reducere de 2%, iar la cumpărarea de la 20 de bucăți – deja 6% etc.
Cum se calculează rapid și frumos procentul de reducere la introducerea cantității de bunuri achiziționate?
Metoda 1: IF imbricate
O metodă din seria „ce este acolo să gândești – trebuie să sari!”. Folosind funcții imbricate IF (DACĂ) pentru a verifica secvenţial dacă valoarea celulei se încadrează în fiecare dintre intervale şi pentru a afişa o reducere pentru intervalul corespunzător. Dar formula în acest caz se poate dovedi a fi foarte greoaie:
Cred că este evident că depanarea unei astfel de „păpuși monstru” sau încercarea de a adăuga câteva condiții noi după ceva timp este distractiv.
În plus, Microsoft Excel are o limită de imbricare pentru funcția IF – de 7 ori în versiunile mai vechi și de 64 de ori în versiunile mai noi. Dacă ai nevoie de mai mult?
Metoda 2. CĂUTARE V cu vizualizare interval
Această metodă este mult mai compactă. Pentru a calcula procentul de reducere, utilizați funcția legendară VPR (CĂUTARE V) în modul de căutare aproximativă:
Unde
- B4 – valoarea cantității de mărfuri din prima tranzacție pentru care căutăm o reducere
- $G$4:$H$8 – un link către tabelul de reduceri – fără „antet” și cu adresele fixate cu semnul $.
- 2 — numărul ordinal al coloanei din tabelul de reduceri din care dorim să obținem valoarea reducerii
- TRUE – aici este îngropat „câinele”. Dacă ca ultimul argument al funcției VPR specifica MINCIUT (FALS) sau 0, atunci funcția va căuta potrivire strictă în coloana cantității (și în cazul nostru va da o eroare #N/A, deoarece nu există valoarea 49 în tabelul de reduceri). Dar dacă în schimb MINCIUT scrie TRUE (ADEVĂRAT) sau 1, atunci funcția va căuta nu exact, dar cel mai apropiat cel mai mic valoare și ne va oferi procentul de reducere de care avem nevoie.
Dezavantajul acestei metode este necesitatea de a sorta tabelul de reduceri în ordine crescătoare după prima coloană. Dacă nu există o astfel de sortare (sau se face în ordine inversă), atunci formula noastră nu va funcționa:
Prin urmare, această abordare poate fi utilizată numai pentru a găsi cea mai apropiată valoare. Dacă trebuie să găsiți cel mai apropiat cel mai mare, atunci trebuie să utilizați o abordare diferită.
Metoda 3. Găsirea celui mai apropiat cel mai mare utilizând funcțiile INDEX și MATCH
Acum să ne uităm la problema noastră din cealaltă parte. Să presupunem că vindem mai multe modele de pompe industriale de diferite capacități. Tabelul de vânzări din stânga arată puterea cerută de client. Trebuie să selectăm o pompă cu cea mai apropiată putere maximă sau egală, dar nu mai mică decât ceea ce este cerut de proiect.
Funcția VLOOKUP nu va ajuta aici, așa că va trebui să utilizați analogul său - o grămadă de funcții INDEX (INDEX) și MAI EXPUSĂ (MECI):
Aici, funcția MATCH cu ultimul argument -1 funcționează în modul de a găsi cea mai apropiată valoare, iar funcția INDEX extrage apoi numele modelului de care avem nevoie din coloana alăturată.
Metoda 4. Noua funcție VIEW (XLOOKUP)
Dacă aveți o versiune de Office 365 cu toate actualizările instalate, atunci în loc de VLOOKUP (CĂUTARE V) puteți folosi analogul său – funcția VIEW (CĂUTARE XL), pe care l-am analizat deja în detaliu:
Aici:
- B4 – valoarea inițială a cantității de produs pentru care căutăm o reducere
- $G$4:$G$8 – gama în care căutăm chibrituri
- $H$4:$H$8 – gama de rezultate din care doriți să returnați reducerea
- al patrulea argument (-1) include căutarea celui mai apropiat număr pe care îl dorim în loc de o potrivire exactă.
Avantajele acestei metode sunt că nu este necesară sortarea tabelului de reduceri și posibilitatea de a căuta, dacă este necesar, nu numai cea mai apropiată valoare mai mică, ci și cea mai apropiată valoare. Ultimul argument în acest caz va fi 1.
Dar, din păcate, nu toată lumea are încă această funcție - doar proprietarii fericiți ai Office 365.
Metoda 5. Power Query
Dacă nu sunteți încă familiarizat cu programul de completare puternic și complet gratuit Power Query pentru Excel, atunci sunteți aici. Dacă sunteți deja familiarizat, atunci să încercăm să-l folosim pentru a ne rezolva problema.
Să facem mai întâi niște lucrări pregătitoare:
- Să convertim tabelele noastre sursă în dinamice (inteligente) folosind o comandă rapidă de la tastatură Ctrl+T sau echipa Acasă – Formatați ca tabel (Acasă — Format ca tabel).
- Pentru claritate, să le dăm nume. Vânzări и Reduceri fila Constructor (Proiecta).
- Încărcați fiecare dintre tabele pe rând în Power Query folosind butonul Din masă/gamă fila Date (Date - Din tabel/gamă). În versiunile recente de Excel, acest buton a fost redenumit în Cu frunze (Din foaie).
- Dacă tabelele au nume de coloane diferite cu cantități, ca în exemplul nostru („Cantitatea de mărfuri” și „Cantitatea de la …”), atunci acestea trebuie redenumite în Power Query și denumite la fel.
- După aceea, puteți reveni înapoi la Excel selectând comanda din fereastra editorului Power Query Acasă — Închideți și încărcați — Închideți și încărcați în... (Acasă — Închide&Încărcare — Închide&Încărcare la...) și apoi opțiunea Doar creați o conexiune (Creați doar conexiune).
- Apoi începe cel mai interesant. Dacă aveți experiență în Power Query, atunci presupun că linia ulterioară de gândire ar trebui să fie în direcția îmbinării acestor două tabele cu o interogare de îmbinare (merge) la VLOOKUP, așa cum a fost cazul în metoda anterioară. De fapt, va trebui să îmbinăm în modul de adăugare, ceea ce nu este deloc evident la prima vedere. Selectați în fila Excel Date – Obțineți date – Combinați solicitări – Adăugați (Date — Obțineți date — Combinați interogări — Adăugați) și apoi mesele noastre Vânzări и Reduceri in fereastra care apare:
- După ce faceți clic pe OK mesele noastre vor fi lipite într-un singur întreg – una sub alta. Vă rugăm să rețineți că coloanele cu cantitatea de mărfuri din aceste tabele au căzut una sub alta, deoarece. au acelasi nume:
- Dacă succesiunea inițială de rânduri din tabelul de vânzări este importantă pentru dvs., astfel încât după toate transformările ulterioare să o puteți restaura, adăugați o coloană numerotată în tabelul nostru folosind comanda Adăugarea unei coloane – coloană index (Adăugați coloană — coloană Index). Dacă succesiunea de linii nu contează pentru tine, atunci poți sări peste acest pas.
- Acum, folosind lista derulantă din antetul tabelului, sortați-o după coloană Cantitate Ascendent:
- Și trucul principal: faceți clic dreapta pe antetul coloanei Reducere alege o echipa Completați (Completați). Celulele goale cu zero completat automat cu valorile de reducere anterioare:
- Rămâne să restabilim secvența originală de rânduri prin sortarea după coloană index ( îl puteți șterge în siguranță mai târziu) și scăpați de liniile inutile cu un filtru zero după coloană Cod de tranzacție:
- Folosind funcția VLOOKUP pentru a căuta și a căuta date
- Utilizarea VLOOKUP (VLOOKUP) face distincție între majuscule și minuscule
- XNUMXD VLOOKUP (VLOOKUP)