Fișă cheat pentru formulele Excel: Ghid avansat

Așa că ai parcurs elementele de bază, ai abordat lucrurile intermediare și acum ești gata să te lupți cu armele mari - formulele avansate Excel! Excel are multe funcții și formule avansate pentru calcule sofisticate, așa că este util să ai un ghid care le identifică pe cele de care ai nevoie.

Această foaie de cheat cu formule Excel acoperă formule avansate de prognoză, analiză statistică, funcții de manipulare a datelor, gestionarea erorilor și multe altele.

Această referință vă va echipa cu cunoștințele despre cum să utilizați aceste funcții avansate. Fiecare formulă este însoțită de explicații clare, sintaxă și exemple practice pentru a ajuta utilizatorii intermediari Excel să devină utilizatori avansați.

Vă rugăm să descărcați și să tipăriți foaia de cheat și să o păstrați la îndemână.

Ok, să începem.

Excel-formule-cheat-sheet-advanced-1 Descărcare

În primul rând, să intrăm în formulele Array.

Cuprins

• Formule matrice
  • 1. Funcția UNICA
  • 2. Funcția SORT
  • 3. Funcția FILTER
• Randomizarea funcțiilor Excel
  • 1. Funcția RANDBETWEEN
  • 2. Funcția RANDARRAY
• Formule avansate de prognoză în Microsoft Excel
  • 1. Funcția FORECAST.ETS
  • 2. Funcția TREND
• Formule statistice avansate
  • 1. Funcția PERCENTILĂ
  • 2. Funcția CUARTILĂ
• Formule avansate de analiză și manipulare a datelor
  • 1. Funcția TRANSPOSE
  • 2. Funcția FREQUENCY
  • 3. Funcția GETPIVOTDATA
• Gestionarea avansată a erorilor
• Formule avansate de căutare
  • 1. Funcția XLOOKUP
  • 2. Funcția XMATCH
• Gânduri finale

Formule matrice

Fișa noastră de informații pentru începători vă arată cum să sortați și să filtrați datele manual. Utilizatorii avansați fac acest lucru în mod programatic cu formule matrice.

Formulele matrice vă permit să efectuați calcule pe mai multe celule simultan. Acestea sunt trei funcții cheie:

1. UNIC

2. FEL

3. FILTRU

Unele dintre aceste funcții sunt disponibile numai în cele mai recente versiuni de Microsoft Excel.

1. Funcția UNICA

Funcția UNIQUE acceptă un interval și returnează o listă de valori unice.

Să presupunem că aveți date de vânzări pentru articole de îmbrăcăminte. Pentru a găsi elementele unice din coloana A, utilizați această formulă:

=UNIC(A2:A6)

2. Funcția SORT

Funcția SORT sortează conținutul unui interval. Sintaxa este:

SORT(matrice, [sort_index], [sort_order], [by_col])

• matrice : intervalul de valori de sortat.

• sort_index : coloana de sortat (1 implicit)

• sort_order : 1 pentru crescător (implicit) sau 2 pentru descrescător).

• by_col : TRUE pentru a sorta după coloană (implicit) sau FALSE pentru a sorta după rând.

Ultimele trei argumente sunt opționale, iar valorile implicite sunt de obicei ceea ce doriți.

Pentru a sorta datele eșantionului după prima coloană, utilizați această formulă:

=SORTARE(A2:C6)

3. Funcția FILTER

Funcția FILTER vă permite să filtrați un interval în funcție de o anumită condiție. Aceasta este sintaxa:

=FILTER(matrice, include, [dacă_gol])

• matrice : intervalul de filtrat.

• include : condiția care determină ce valori să filtreze.

• if_empty : specifică ce să returneze dacă nicio valoare nu îndeplinește criteriile de filtrare (implicit este „”).

Să presupunem că doriți să filtrați rândurile din eșantionul de date pentru a afișa numai acolo unde valoarea vânzărilor este mai mare de 15 USD. Utilizați această formulă:

=FILTRE(A2:C6; C2:C6>15)

Randomizarea funcțiilor Excel

Cheat-ul nostru intermediar arată cum să folosiți funcția RAND care produce un număr aleator între 0 și 1.

Utilizatorii avansați de Excel știu cum să folosească funcțiile de randomizare pentru a genera rapid date mostre.

1. Funcția RANDBETWEEN

Funcția RANDBETWEEN este mai flexibilă decât RAND, deoarece puteți specifica numerele de jos și de sus ca altele decât 0 și 1.

Pentru a genera date cu numere între 1 și 100, introduceți această formulă în celula A1:

=RANDBTREEN(1, 100)

Apoi, copiați celula în câte rânduri și coloane doriți. Este nevoie de câteva secunde pentru a produce o grilă de numere aleatorii:

2. Funcția RANDARRAY

S-ar putea să vă gândiți că ar fi bine să evitați copia manuală a funcției RANDBETWEEN. Pentru a deveni super-avansat, puteți utiliza noua funcție RANDARRAY în cea mai recentă versiune de Microsoft Excel.

Sintaxa este:

RANDARRAY([rânduri], [coloane], [min], [max], [număr întreg])

• rânduri : numărul de rânduri

• coloane : numărul de coloane

• min : cel mai mic număr

• max : cel mai mare număr

• număr întreg : implicit este TRUE, altfel folosește numere zecimale.

Pentru a genera numere aleatorii între 1 și 100 pe șase rânduri și nouă coloane, utilizați această formulă:

=RANDARRAY(6, 9, 1, 100, TRUE)

Formule avansate de prognoză în Microsoft Excel

Funcțiile de prognoză ale Excel sunt utilizate pentru prognoza valorilor viitoare pe baza tendințelor existente ale datelor. Aceste funcții ajută la identificarea tiparelor și a tendințelor de proiect pe baza datelor dvs.

1. Funcția FORECAST.ETS

Vechea funcție FORECAST a fost înlocuită cu un set de funcții mai noi în Excel 2016.

Alegeți funcția în funcție de modelul de prognoză specific pe care îl doriți. De exemplu, funcția FORECAST.ETS folosește algoritmul Exponential Smoothing .

Sintaxa este:

FORECAST.ETS(data_țintă, valori, cronologie)

• target_date : data pentru care doriți o valoare calculată.

• valori : datele istorice.

• cronologie : o serie de date

Să presupunem că aveți date de la 1 până la 5 ianuarie în coloana A și sumele vânzărilor în coloana B. Această formulă va prezice următoarea sumă de vânzări:

= FORECAST.ETS(„6 ianuarie 2023”, B2:B6, A2:A6)

2. Funcția TREND

Funcția TREND proiectează un set de valori bazat pe metoda celor mai mici pătrate. Returnează o matrice. Sintaxa este:

TREND(cunoscut_y, [cunoscut_x], [new_x], [const])

• cunoscut_y : interval de valori y

• cunoscut_x : interval de valori x

• new_x : interval de valori calculate

Adesea, cunoscutele_y sunt punctele de date, în timp ce cunoscutele_x sunt datele.

Folosind aceleași date ca în exemplul anterior, puteți introduce formula de mai jos în celula C2. Se va genera un set de valori.

=TENDINȚA(B2:B6; A2:A6)

Formule statistice avansate

Funcțiile statistice avansate includ calcularea percentilelor și a quartilelor. Unele funcții matematice sunt disponibile pentru compatibilitate cu versiunea anterioară, dar este recomandat să utilizați cele mai recente versiuni.

1. Funcția PERCENTILĂ

Această funcție calculează procentul de puncte de date care se încadrează sub o anumită valoare. Sintaxa este:

PERCENTILE.INC(matrice, k)

• matrice : intervalul de celule

• k : percentila de la 0 la 1

Să presupunem că doriți să calculați a 70-a percentila de date din coloana B. Folosiți această formulă:

=PERCENTILĂ.INC(B2:B6; 0,7)

2. Funcția CUARTILĂ

Această funcție este o variație a funcției percentilei, dar folosește sferturi pentru a împărți datele. Aceasta este sintaxa:

QUARTILE.INC(matrice, litru)

• matrice : intervalul de date

• quart : 1 pentru a 25-a percentila, 2 pentru a 50-a, 3 pentru a 75-a și 4 pentru a maxim.

Formula de mai jos va calcula primul quartil de date din coloana A.

=CUARTIL.INC(A2:A5, 1)

Formule avansate de analiză și manipulare a datelor

Mai multe funcții avansate vă permit să schimbați formatul datelor, să analizați distribuțiile de frecvență și să extrageți date din tabelele pivot.

1. TRANSPUNE

2. FRECVENȚĂ

3. GETPIVOTDATA

1. Funcția TRANSPOSE

Uneori doriți să mutați datele din rândurile dvs. în coloane și invers. Puteți face acest lucru manual sau puteți utiliza în schimb funcția TRANSPOSE.

Să presupunem că aveți articolele „T-shirt”, „Haoura” și „Blagi” în celulele A2, A3 și A4. Doriți să le transformați în titluri de coloană. Această funcție returnează valorile într-un singur rând:

=TRANSPUNERE(A2:A4)

2. Funcția FREQUENCY

Această funcție calculează distribuția de frecvență a valorilor într-un set de date. Aceasta este sintaxa:

FREQUENCY(matrice_date, matrice_bins)

• data_array : interval de valori.

• bins_array : intervalele de utilizat.

Să presupunem că aveți date despre vânzări în coloana B și doriți să analizați distribuția de frecvență a valorilor în funcție de câte sume sunt:

• sub 20.

• de la 20 la 80.

• peste 80.

Aceasta reprezintă trei containere și poate fi calculată cu această formulă:

=FRECVENȚA(A2:A6, {20,80})

Pentru mai multe despre distribuțiile de frecvență în Excel, vedeți acest videoclip:

3. Funcția GETPIVOTDATA

Această funcție vă permite să extrageți informații rezumate din tabelele pivot. Aceasta este sintaxa:

GETPIVOTDATA(câmp_date, tabel_pivot, [câmp1, element1], [câmp2, articol2], …)

• date_field : câmpul de date sau valoarea pe care doriți să o preluați din tabelul pivot.

• tabel_pivot : o referință la tabelul pivot.

• field1, item1 etc. : perechile câmp/articol pentru care se filtrează.

Să presupunem că aveți un tabel pivot bazat pe culoarea articolelor vândute. Pentru a extrage vânzările pentru articole roșii, utilizați această formulă:

=GETPIVOTDATA(„Vânzări”, A1, „Culoare”, „Roșu”)

Gestionarea avansată a erorilor

Chiar și cele mai simple formule Excel pot produce erori. Utilizatorii intermediari ar trebui să știe cum să folosească ISERROR pentru a gestiona erorile. Utilizatorii avansați ar trebui, de asemenea, să fie familiarizați cu funcția ERROR.TYPE pentru identificarea erorilor.

Funcția ERROR.TYPE ajută la identificarea tipului specific de eroare dintr-o celulă sau formulă.

Returnează o valoare numerică corespunzătoare diferitelor tipuri de erori, cum ar fi #N/A, #VALOR!, #REF! și altele.

Să presupunem că aveți o eroare în celula A1 și doriți să identificați tipul ei de eroare. Următoarea formulă va returna numărul care corespunde erorii specifice:

=EROARE.TIP(A1)

Puteți combina acest lucru cu mai multe funcții pentru a răspunde diferit, în funcție de tipul de eroare. Acestea sunt cele mai frecvente erori și valorile lor:

1. #NUL! (nu a fost găsită nicio celulă comună într-un interval)

2. #DIV/0! ( diviziunea la zero sau o celulă goală)

3. #VALOARE! (tip de date sau argument neadecvat într-o formulă)

4. #REF! (o celulă la care se face referire a fost ștearsă sau există o referință circulară )

5. #NUME? (Excel nu recunoaște funcția sau intervalul)

6. #NUM! (valoare numerică nevalidă)

7. #N/A (valoarea nu poate fi găsită)

Să presupunem că doriți să gestionați trei tipuri de erori specifice. Utilizați această formulă pentru a afișa un mesaj de eroare specific bazat pe tipul:

=IF(ISERROR(C2), IF(ERROR.TYPE(C2)=2, „Eroare împărțită la zero”, IF(ERROR.TYPE(C2)=3, „Eroare valoare nevalidă”, IF(ERROR.TYPE(C2) )=7, „Eroare de valoare nu a fost găsită”, „Altă eroare”))), C2)

Formule avansate de căutare

Fișele noastre pentru începători și intermediari au acoperit o selecție de funcții de căutare . Iată câteva opțiuni avansate:

1. CĂUTARE XL

2. XMECI

1. Funcția XLOOKUP

Această funcție de căutare vă permite să căutați o valoare într-un interval și să returnați o valoare corespunzătoare dintr-o altă coloană sau interval.

Oferă mai multă versatilitate decât funcțiile de căutare mai simple, cum ar fi VLOOKUP . Aceasta este sintaxa:

XLOOKUP(valoare_căutare, matrice_căutare, matrice_returnată, [mod_potrivire], [mod_căutare], [dacă_nu_găsită])

• lookup_value : valoarea pe care doriți să o căutați.

• lookup_array : intervalul pentru căutare.

• return_array : intervalul care va afișa valoarea corespunzătoare.

• match_mode : potrivire exactă (0), următoarea mai mică (1), următoarea mai mare (-1) sau potrivirea wildcard (2).

• search_mode : -1 pentru de sus în jos, 1 pentru de jos în sus sau 2 pentru căutare binară.

• if_not_found : setează valoarea de returnat dacă nu se găsește nicio potrivire.

Să presupunem că doriți să căutați un interval de date pentru prima apariție a unui articol de îmbrăcăminte și să returnați sumele vânzărilor. Această formulă va căuta textul „Hauda” și va returna valoarea în celula alăturată dacă este găsită:

=XLOOKUP(„Hauda”, A2:A6, B2:B6)

2. Funcția XMATCH

Această funcție vă permite să găsiți poziția unei valori specificate într-un interval sau o matrice. Aceasta este sintaxa:

XMATCH(valoare_căutare, matrice_căutare, [tip_potrivire], [mod_căutare])

• lookup_value : valoarea pe care doriți să o găsiți.

• lookup_array : intervalul pe care doriți să îl căutați.

• tip_potrivire : potrivire exactă (0), următoarea cea mai mică (-1), următoarea cea mai mare (1).

• search_mode : căutare binară (1) sau căutare liniară (2).

Să presupunem că doriți să găsiți prima apariție a unui element galben într-un interval din coloana B. Utilizați această formulă:

=XMATCH(„Galben”, B2:B6, 0)

Gânduri finale

Această foaie de cheat a acoperit o gamă largă de funcții, de la analiză statistică, formule de căutare, tehnici de manipulare a datelor și strategii de tratare a erorilor.

Exemplele și explicațiile oferite ajută la demistificarea acestor formule avansate, făcându-le accesibile chiar și celor cu experiență limitată.

Pe măsură ce începeți să le încorporați în sarcinile dvs. Excel, sunteți pe cale să vă creșteți abilitățile Excel la un nivel avansat.

Dar amintiți-vă, această foaie de cheat este doar vârful aisbergului. Lucrurile cu adevărat uimitoare se întâmplă atunci când devii creativ, amesteci și potriviți aceste formule și le personalizați pentru a vă rezolva provocările unice. Excel este ca o pânză, iar aceste formule sunt paleta ta - așa că haide, pictează-ți capodopera!