Excel Formulas Cheat Sheet: Ghid intermediar

Așa că ați reușit să stăpâniți elementele de bază ale Excel – felicitări! Acum, te-ai hotărât să ridici lucrurile și să te scufunzi în lucruri mai avansate, nu? Ei bine, ai noroc pentru că am creat o fișă excelentă intermediară Excel doar pentru tine!

Această foaie intermediară Microsoft Excel acoperă o gamă largă de subiecte care vă vor îmbunătăți abilitățile în analiza numerică, procesarea textului și logica complexă a datei și orei.

Având Excel Intermediate Cheat Sheet ca referință pentru desktop, vă puteți scufunda adânc în formulele și funcțiile mai puternice ale Microsoft Excel.

Acum, asigurați-vă că citiți mai departe, dar și imprimați și salvați foaia de cheat de mai jos!

Să începem!

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

Cuprins

• Formule matematice intermediare
  • Logica condiționată
• Formule statistice intermediare
  • 1. MINA și MAXA
  • 2. COUNTA și COUNTIF
• Formule Excel pentru analiză financiară
  • 1. Funcția PRODUS
  • 2. Funcțiile QUOTIENT și LOG
  • Funcții pentru scenarii de investiții
• Formule Excel intermediare pentru dată și oră
  • 1. Funcțiile lunii intermediare
  • 2. Funcții pentru săptămâna intermediară
  • 3. Funcții de timp intermediar
• Funcții Excel pentru algebră condiționată și logică
  • 1. Funcția IF
  • 2. Funcția AND
  • 3. Funcția SAU
  • 4. Funcția NOT
• Celulele de referință
  • 1. Funcția INDIRECT
  • 2. Funcția INDEX
  • 3. Funcția OFFSET
• Formule de text intermediare
  • 1. Funcția TEXTJOIN
  • 2. Funcția REPLACE
  • 3. Funcția SUBSTITUT
• Formule de căutare intermediare
• Formule pentru tratarea erorilor Microsoft Excel
  • 1. IFERROR Funcția
  • 2. Funcția IFNA
• Comenzi rapide pentru formule și funcții
• Formatarea celulelor intermediare
• Gânduri finale

Formule matematice intermediare

Cheat sheet pentru începători acoperă cele mai comune funcții matematice pentru începători, cum ar fi funcția SUM. Utilizatorii intermediari ar trebui să se familiarizeze cu aceste funcții mai complexe:

• ABS() returnează valoarea absolută a unui număr.

• SQRT() returnează rădăcina pătrată a unui număr

• RAND() oferă un număr aleator între 0 și 1

Logica condiționată

Utilizatorii intermediari ar trebui să știe, de asemenea, cum să efectueze calcule matematice pe baza mai multor condiții folosind aceste funcții:

• SUMIFS(interval_sumă, interval_criterii1, criteriu1, [….])

• COUNTIFS(interval_criterii1, criteriu1, [interval_criterii2, criterii2], …)

• AVERAGEIFS(interval_criterii1, criteriu1, [interval_criterii2, criterii2], …)

Funcțiile evaluează criteriile furnizate și produc o valoare logică de TRUE sau FALSE.

Să presupunem că aveți următoarele date de vânzări în coloanele A, B și C:

Pentru a calcula suma vânzărilor pentru tricourile roșii, utilizați formula SUMIFS:

=SUMIFS(C2:C6, A2:A6, „T-shirt”, B2:B6, „Roșu”)

Pentru a număra numărul de rânduri cu tricouri roșii, utilizați formula COUNTIFS:

=COUNTIFS(C2:C6, A2:A6, „T-trico”, B2:B6, „Roșu”)

Pentru a calcula vânzările medii pentru tricouri roșii, utilizați formula AVERAGEIFS:

=AVERAGEIFS(C2:C6, A2:A6, „T-trico”, B2:B6, „Roșu”)

Formule statistice intermediare

Pe lângă funcțiile de bază precum MIN și MAX, Excel are o gamă mai largă de formule statistice pentru utilizatorii intermediari. Iată câteva dintre cele mai utile:

• MINA

• MAXA

• COUNTA

• COUNTIF

1. MINA și MAXA

Funcțiile MIN și MAX mai frecvent utilizate ignoră valorile text.

Funcțiile alternative MINA și MAXA iau în considerare atât textul, cât și numerele atunci când caută cele mai mari sau cele mai mici valori. Valorile textului sunt evaluate ca și cum ar fi zero.

Diferențele sunt prezentate în imaginea de mai jos. Primul rând evaluează o valoare minimă de 10, în timp ce al doilea rând evaluează ca 0 datorită prezenței unei valori text.

2. COUNTA și COUNTIF

Funcția COUNTA este utilizată pentru a număra numărul de celule negoale dintr-un interval.

Funcția COUNTIF este mai puțin specifică prin aceea că este utilizată pentru a număra numărul de celule dintr-un interval care îndeplinesc o anumită condiție sau criterii.

De asemenea, puteți utiliza aceste funcții pentru a număra numărul de valori distincte dintr-o coloană. Acest videoclip arată cum să faceți acest lucru.

Formule Excel pentru analiză financiară

Există mai multe formule pe care veți dori să le utilizați atunci când efectuați analize financiare, cum ar fi prognozarea investițiilor:

• PRODUS(număr1, [număr2…])

• QUOTIENT(numărător, numitor)

• LOG(număr, [bază])

1. Funcția PRODUS

Să presupunem că doriți să calculați valoarea viitoare a unei investiții inițiale de 1.000 USD (celula B1) la o rată anuală a dobânzii de 4% (celula B2) după cinci ani (celula B3).

Utilizați funcția PRODUS pentru a calcula valoarea viitoare cu această formulă:

=B1 * PRODUS(1 + B2)^B3

Această formulă va returna valoarea viitoare a investiției dvs.

2. Funcțiile QUOTIENT și LOG

Următorul pas este de a calcula câți ani vor dura pentru ca o investiție să își dubleze valoarea la rata dobânzii dată.

Utilizați funcția QUOTIENT în combinație cu funcția LOG pentru a calcula acest lucru după cum urmează:

=COTIENT(LOG(2) / LOG(1 + B2), 1)

Această formulă va returna 17 cu datele eșantionului, indicând că va dura 17 ani pentru ca investiția să se dubleze la o rată anuală a dobânzii de 4%.

Această imagine arată formulele dintr-o foaie de lucru Excel :

Funcții pentru scenarii de investiții

Acestea sunt câteva funcții care sunt potrivite pentru scenarii financiare specifice. Una dintre ele poate fi exact ceea ce aveți nevoie:

• NPV – Funcția NPV (Valoarea actuală netă) va calcula valoarea actuală netă a unei investiții pe baza unei serii de fluxuri de numerar viitoare bazate pe o rată de actualizare.

• ACCRINT – The ACCRINT function calculates the accrued interest of a security that pays periodic interest. This is useful for determining the interest earned on a security since the last payment date up to a given settlement date.

• INTRATE – The INTRATE function calculates the interest rate for a fully invested security.

• PMT – The PMT function calculates the total payment on a debt security.

• IRR – The IRR function provides the internal rate of a return.

• YIELD – The YIELD function provides the yield of a security based on interest rate, face value, and maturity.

Intermediate Excel Date and Time Formulas

The basic date and time functions in Excel include the NOW and TODAY functions for the current date. Intermediate users should also know how to extract components from a given date using:

• DAY(date)

• MONTH(date)

• YEAR(date)

The formula =MONTH(“23 April 2023″) will return a result of 4 for the 4th month. Similarly, the DAY and YEAR functions will return 23 and 2023 respectively.

1. Intermediate Month Functions

Intermediate users will sometimes deal with adding or subtracting months to a date and finding the end of a month.

• EDATE(start_date, number_of_months)

• EOMONTH(start_date, number_of_months)

For example, the formula =EDATE(“23 April 2023”, 2) will calculate the date two months later.

The formula =EOMONTH(23 April 2023”, 2) calculates the end of the month two months later.

The result is “30 June 2023”, which takes into account that there are only thirty days in June. If you specify 3 months, the result will be “31 July 2023”.

2. Intermediate Week Functions

You can use a combination of the SUM and WEEKDAY functions to count the number of whole workdays within a range of dates.

The WEEKDAY function returns the day of the week for a given date. The second parameter determines the numbering system with the default being Sunday to Saturday.

If you have a series of dates in a cell range of B1:B40, use this formula to calculate the workdays:

=SUM(–(WEEKDAY(B1:B40,2)>5))

3. Intermediate Time Functions

The YEARFRAC function calculates the fraction of a year between two dates.

The TIMEVALUE function is used to convert a time represented as text into a decimal number representing the proportion of a 24-hour day.

The result is an Excel serial number where 1 represents a full 24-hour day, 0.5 represents 12 hours, 0.25 represents 6 hours, and so on.

Suppose you have a time in text format “17:45” in cell A1 and you want to convert it into a decimal number. This is the formula:

=TIMEVALUE(A1)

The value 0.74 represents the proportion of the 24-hour day that has passed by at the time of fifteen minutes before six o’clock.

Take to to change the time cell from a time format to a text format. You may want to format this as a percentage to be clearer.

Excel Functions for Conditional and Logical Algebra

Conditional and logical functions are essential for decision-making in Excel. The following functions can be combined with the more basic Excel formulas for powerful logic:

1. IF(condition, value_if_true, value_if_false):

2. AND(condition1, condition2, …)

3. OR(condition1, condition2, …)

4. NOT(condition1, condition2, …)

The IF function evaluates a condition and returns different values depending on whether the condition is true or false.

The AND, OR, and NOT functions check which conditions are true and let you make decisions accordingly.

Here are some examples based on the sales data we used before.

1. IF Function

Suppose you want to display the text “Low Sales” if the sales for the items are less than 15. If the sales are higher, you want to display “High Sales” in a new column.

Use this formula and copy it down to the other rows:

=IF(C2<15, “Low Sales”, “High Sales”)

2. AND Function

Suppose you want to display TRUE or FALSE for t-shirts with at least 15 sales. Use this formula:

=AND(A3=”T-Shirt”, C3>=15)

3. OR Function

Suppose you want to test multiple conditions and return TRUE if any of the conditions are met and FALSE otherwise, then you’d use the OR function.

For instance, if we have scores of a test in cells A1 and B1, and we want to know if either score is above 80, we could use: =OR(A1>80, B1>80). This will return TRUE if either score (or both) is over 80, and FALSE if both are 80 or less.

4. NOT Function

Suppose you want to check if the item is not a hoodie. Use this formula:

=NOT(A2=”Hoodie”)

Referencing Cells

Un începător se va familiariza rapid cu utilizarea referințelor absolute și relative, cum ar fi $A$1 sau B2. Utilizatorii intermediari ar trebui să se familiarizeze cu utilizarea referințelor indirecte, a indicilor și a decalajelor:

1. INDIRECT(text_ref)

2. INDEX(interval, row_num, column_num)

3. OFFSET(celula_referință, rânduri, coloane):

1. Funcția INDIRECT

Funcția INDIRECT returnează valoarea referinței de celulă specificată introdusă ca text. Pentru a face referire la celula B3, utilizați această formulă:

=INDIRECT(„B3”)

Avantajul față de utilizarea referințelor simple de celule este că această funcție vă oferă o referință dinamică. Aceasta înseamnă că Excel actualizează automat referința atunci când structura foii de calcul se modifică, de exemplu, un rând este șters.

2. Funcția INDEX

Funcția INDEX este utilizată pentru a face referire la celule dintr-un interval specificat, pe baza numerelor rândurilor și coloanelor.

Pentru a face referire la celula B3 într-un tabel de șase rânduri, utilizați această formulă:

=INDEX(B1:B6, 3)

3. Funcția OFFSET

Funcția OFFSET returnează o celulă sau un interval care este un număr specificat de rânduri și coloane la distanță de o celulă de referință.

Dacă doriți să afișați valoarea celulei care are două celule peste și o celulă mai jos de la A1, utilizați această formulă:

=OFFSET(A1, 2, 1)

Iată formulele în acțiune:

Formule de text intermediare

Începătorii ar trebui să cunoască funcții text precum LEFT, care extrage unul sau mai multe caractere din partea stângă a unui șir. Un utilizator cu putere ar trebui să fie familiarizat cu funcții precum acestea:

1. TEXTJOIN(delimitator, ignore_empty, text1, [text2, …])

2. REPLACE(text_vechi, num_început, num_care, text_nou)

3. SUBSTITUTE(text, text_vechi, text_nou, [num_instanță])

1. Funcția TEXTJOIN

Funcția TEXTJOIN concatenează celulele cu delimitatorul pe care îl furnizați. De asemenea, puteți specifica unde să ignorați celulele goale.

Pentru a produce o listă delimitată prin virgulă a unor elemente din coloana A, utilizați această formulă:

=TEXTJOIN(“, „, TRUE, A2:A4)

2. Funcția REPLACE

Funcția de înlocuire vă permite să specificați poziția de pornire și lungimea șirului pe care doriți să îl schimbați în țintă.

Pentru a înlocui primele două caractere ale unui șir cu „XX”, utilizați această formulă:

=TEXTJOIN(A2, 1, 2, „XX”)

3. Funcția SUBSTITUT

Funcția de înlocuire înlocuiește aparițiile specificate ale unui șir de text într-un alt șir de text cu un șir de text nou.

Pentru a înlocui aparițiile cuvântului „Jeans” cu „Leggings”, utilizați această formulă:

=ÎNLOCUIT(A3, „Blagi”, „Leggings”)

Iată formulele în acțiune:

Formule de căutare intermediare

Funcțiile obișnuite de căutare, cum ar fi VLOOKUP și MATCH, sunt acoperite în fișa de cheat pentru începători.

Utilizatorii intermediari ar trebui să se familiarizeze cu funcția de căutare ALEGE:

• ALEGE (num_index, valoare1, [valoare2, …])

Funcția ALEGE returnează o valoare dintr-o listă de valori bazată pe un număr de index specificat.

Să presupunem că aveți o coloană de mărimi de tricouri etichetată ca 1, 2 sau 3. Doriți să afișați o categorie de mic, mediu și, respectiv, mare în același rând. Utilizați această formulă:

=ALEGE(A1, „Mic”, „Mediu”, „Mare”)

Formule pentru tratarea erorilor Microsoft Excel

Cheatsheetul pentru începători a enumerat mesajele de eroare tipice pe care le veți vedea când lucrați cu Excel. Utilizatorii intermediari ar trebui să poată folosi formule pentru a gestiona erorile cu grație.

1. IFERROR(valoare, if_error)

2. IFNA(valoare, valoare_dacă_na)

1. IFERROR Funcția

Erorile de împărțire la zero sunt comune, dar afișarea implicită a Excel este #DIV/0! nu va fi clar pentru fiecare utilizator.

Utilizați funcția IFERROR pentru a înlocui eroarea standard cu „Nu se poate împărți la zero” cu această formulă:

=IFEROARE(A1/B1, „Nu se poate împărți la zero”)

Această imagine arată eroarea brută din al treilea rând și funcția IFERROR care gestionează al patrulea rând.

2. Funcția IFNA

Funcția IFNA din Excel este utilizată pentru a detecta și gestiona erorile #N/A.

O altă secțiune a acestei foi de cheat arăta funcția ALEGE. Această funcție va returna o eroare N/A dacă numărul de index nu este un întreg.

Să presupunem că dimensiunea unui tricou a fost introdusă ca 1,5, utilizatorul crezând în mod eronat că aceasta va arăta o categorie undeva între mic și mediu. În schimb, va afișa o eroare #N/A .

Pentru a furniza o eroare mai utilă, utilizați această formulă:

=IFNA(ALEGE(A2, „Scăzut”, „Mediu”, „Ridicat”), „Index nevalid”)

Comenzi rapide pentru formule și funcții

Există mai multe comenzi rapide de la tastatură care vă pot face să lucrați mai eficient cu foaia de lucru.

• F2 : Editați celula activă și poziționați punctul de inserare la sfârșitul conținutului celulei.

• F9 : Calculați și afișați rezultatul porțiunii selectate a unei formule.

• Ctrl + Shift + Enter : introduceți o formulă matrice.

• Shift + F3 : deschideți caseta de dialog Inserare funcție.

• Esc : Anulați introducerea unei formule și reveniți la conținutul celulei inițiale.

Este posibil ca unele dintre aceste comenzi rapide Excel să nu fie disponibile în toate limbile sau în toate formele de tastatură.

Formatarea celulelor intermediare

În analiza financiară, formatarea adecvată a celulelor care conțin numere, date și monede este crucială. Pentru a formata celulele, urmați acești pași:

1. Selectați celulele pe care doriți să le formatați.

2. Faceți clic dreapta pe celulele selectate și alegeți „Format Cells”.

3. Selectați categoria corespunzătoare (de exemplu, Număr, Monedă, Data) și aplicați formatul dorit.

Unele tipuri comune de formatare de luat în considerare în domeniul financiar includ:

• Moneda : 1.234,56 USD

• Procent : 12,34%

• Contabilitate : (1.234,00 USD)

• Data : 10-mai-2023

Gânduri finale

Pe măsură ce vă simțiți mai confortabil cu diversele formule și funcții evidențiate în această fișă de cheat , vă veți găsi mai bine echipat pentru a gestiona sarcini și proiecte din ce în ce mai complexe.

Practica este cheia pentru stăpânirea oricărei abilități, așa că nu vă fie teamă să experimentați cu aceste formule în munca de zi cu zi. Îți vor aduce experiența de la nivelul începător la nivelul intermediar.

Aceasta este doar o mostră a ceea ce pot face funcțiile intermediare ale Excel pentru a vă simplifica munca, a vă eficientiza analizele și a vă ajuta să descoperiți informații din datele dvs.

Adevărata magie se întâmplă atunci când începeți să combinați funcțiile din foaia noastră de trucuri și să le adaptați nevoilor dvs. unice. Deci, nu te opri aici. Continuați să explorați, continuați să experimentați și veți descoperi că Excel nu este doar un instrument, este un schimbător de jocuri!

Simți că vrei să duci lucrurile la următorul nivel? Verificați fișa noastră Excel Advanced Formulas Cheat Sheet.