Cuprins
Necesitatea de a găsi cele mai mari și mai mici valori în orice afacere este evidentă: cele mai profitabile produse sau clienți valoroși, cele mai mari expedieri sau expedieri și așa mai departe.
Dar, împreună cu aceasta, uneori trebuie să căutați în date nu pentru partea de sus, ci pentru valorile care apar cel mai frecvent, care, deși sună similar, nu este, de fapt, deloc la fel. În legătură cu un magazin, de exemplu, aceasta poate fi o căutare a bunurilor cel mai frecvent achiziționate, mai degrabă decât a celor mai profitabile, sau a numărului de articole care apar cel mai frecvent într-o comandă, minute într-o conversație etc.
Într-o astfel de situație, problema va trebui rezolvată puțin diferit, în funcție de ceea ce avem de-a face – numere sau text.
Găsirea celor mai comune numere
Să presupunem că ne confruntăm cu sarcina de a analiza datele disponibile privind vânzările dintr-un magazin pentru a determina numărul cel mai frecvent de articole achiziționate. Pentru a determina cel mai frecvent număr dintr-un interval, puteți utiliza funcția FASHION (MODĂ):
Adică, conform statisticilor noastre, cel mai adesea cumpărătorii cumpără 3 buc. bunuri.
Dacă nu există una, ci mai multe valori simultan care apar de același număr maxim de ori (mai multe moduri), atunci pentru a le identifica, puteți utiliza funcția MODĂ.NSK (MOD.MULT). Trebuie introdusă ca formulă matrice, adică selectați mai multe celule goale simultan, astfel încât să fie suficient pentru toate modurile cu o marjă și introduceți =MODA.NSK(B2:B16) în bara de formule și apăsați comanda rapidă de la tastatură Ctrl + Shift + Enter.
La ieșire, vom obține o listă cu toate modurile din datele noastre:
Adică, judecând după datele noastre, adesea iau nu numai 3, ci și 16 bucăți. bunuri. Vă rugăm să rețineți că în datele noastre există doar două moduri (3 și 16), astfel încât restul celulelor alocate „în rezervă” vor avea eroarea #N/A.
Analiza frecventei pe benzi cu functia FREQUENCY
Dacă este necesar să se analizeze nu numere întregi, ci numere fracționale, atunci ar fi mai corect să se evalueze nu numărul de valori identice, ci se încadrează în intervalele specificate. De exemplu, trebuie să înțelegem ce greutate este cel mai adesea achiziționată de mărfuri pentru a alege cărucioarele potrivite și pungile de ambalare de dimensiunea potrivită pentru magazin. Cu alte cuvinte, trebuie să stabilim câte numere se încadrează în intervalul 1..5 kg, câte în intervalul 5..10 kg etc.
Pentru a rezolva o problemă similară, puteți utiliza funcția FRECVENȚĂ (FRECVENȚĂ). Pentru aceasta, trebuie să pregătiți celulele în avans cu intervale (buzunare) care ne interesează și apoi să selectați un interval gol de celule (G2: G5) cu o celulă mai mare decât intervalul de buzunare (F2: F4) și să îl introduceți ca un formula matrice prin apăsarea combinației de la sfârșit Ctrl + Shift + Enter:
Analiză de frecvență cu un tabel pivot cu grupare
O soluție alternativă la problemă: creați un tabel pivot în care puneți greutatea achizițiilor în zona de rânduri și numărul de clienți în zona de valori, apoi aplicați gruparea - faceți clic dreapta pe valorile de greutate și selectați comanda grup (Grup). În fereastra care apare, puteți seta limitele și etapa de grupare:
… și după ce faceți clic pe butonul OK obțineți un tabel cu numărul de accesări ale cumpărătorilor din fiecare interval de grupare:
Contra pe aici:
- pasul de grupare poate fi doar constant, spre deosebire de funcție FRECVENȚĂ, unde buzunarele pot fi specificate absolut orice
- tabelul pivot trebuie să fie actualizat atunci când datele sursă se modifică (făcând clic pe butonul din dreapta al mouse-ului – Actualizează), iar funcția este recalculată automat din mers
Căutați textul care apare cel mai frecvent
Dacă nu avem de-a face cu numere, ci cu text, atunci abordarea soluției va fi fundamental diferită. Să presupunem că avem un tabel cu 100 de rânduri de articole vândute într-un magazin și dorim să stabilim ce articole au fost cumpărate cel mai des?
Cea mai simplă și mai evidentă soluție ar fi adăugarea unei coloane lângă funcție COUNTIF (COUNTIF)pentru a număra numărul de apariții ale fiecărui element din coloana A:
Apoi, bineînțeles, sortați coloana rezultată în ordine descrescătoare și priviți primele rânduri.
Sau adăugați o coloană cu unele în lista originală și construiți un tabel rezumat pe baza tabelului rezultat, numărând numărul total de unități pentru fiecare produs:
Dacă nu există multe date sursă și, practic, nu doriți să utilizați tabele pivot, atunci puteți utiliza formula matrice:
Să o descompunem bucată cu bucată:
- COUNTIF(A2:A20;A2:A20) este o formulă matrice care caută pe rând numărul de apariții ale fiecărui produs din intervalul A2:A100 și produce o matrice cu numărul de repetări la ieșire, adică, de fapt, înlocuiește coloana suplimentară
- MAX – găsește cel mai mare număr din matricea de apariții, adică cel mai cumpărat produs
- MATCH – calculează numărul ordinal al rândului din tabel în care MAX a găsit cel mai mare număr
- INDEX – returnează din tabel conținutul celulei cu numărul găsit de MATCH
- Numărarea numărului de valori unice dintr-o listă
- Extragerea articolelor unice dintr-o listă duplicată
- Gruparea în tabele pivot