Trucuri de viață Excel pentru cei implicați în raportare și prelucrare a datelor

2024 Autor: Malcolm Clapton | [email protected]. Modificat ultima dată: 2024-01-13 02:48

În această postare, Renat Shagabutdinov, director general adjunct al editurii Mann, Ivanov și Ferber, împărtășește câteva trucuri interesante de viață Excel. Aceste sfaturi vor fi utile pentru oricine este implicat în diverse raportări, procesarea datelor și crearea de prezentări.

Acest articol conține tehnici simple pentru a vă simplifica munca în Excel. Sunt utile în special pentru cei care sunt angajați în raportarea managementului, pregătesc diverse rapoarte analitice bazate pe descărcări din 1C și alte rapoarte, formează prezentări și diagrame din acestea pentru management. Nu mă prefac a fi o noutate absolută – într-o formă sau alta, aceste tehnici au fost probabil discutate pe forumuri sau menționate în articole.

Alternative simple la LOOKUP și HLOOKUP, dacă valorile dorite nu sunt în prima coloană a tabelului: LOOKUP, INDEX + SEARCH

Funcțiile LOOKUP și HLOOKUP funcționează numai dacă valorile dorite sunt în prima coloană sau rând a tabelului din care intenționați să obțineți date.

În caz contrar, există două opțiuni:

1. Utilizați funcția LOOKUP.

   Are următoarea sintaxă: LOOKUP (lookup_value; lookup_vector; result_vector). Dar pentru ca acesta să funcționeze corect, valorile intervalului view_vector trebuie sortate în ordine crescătoare:

   

2. Utilizați o combinație de funcții MATCH și INDEX.

   Funcția MATCH returnează numărul ordinal al unui element din matrice (cu ajutorul lui puteți găsi în ce rând al tabelului se află elementul căutat), iar funcția INDEX returnează un element de matrice cu un număr dat (pe care îl vom afla folosind funcția MATCH).

   

   Sintaxa funcției:

   • SEARCH (search_value; search_array; match_type) - pentru cazul nostru, avem nevoie de un tip de potrivire „potrivire exactă”, care corespunde cu numărul 0.

   • INDEX (matrice; număr_linie; [număr_coloană]). În acest caz, nu trebuie să specificați numărul coloanei, deoarece matricea constă dintr-un rând.

Cum să umpleți rapid celulele goale dintr-o listă

Sarcina este să completați celulele din coloană cu valorile din partea de sus (astfel încât subiectul să fie în fiecare rând al tabelului și nu numai în primul rând al blocului de cărți pe subiect):

Selectați coloana „Subiect”, faceți clic pe panglica din grupul „Acasă”, butonul „Găsiți și selectați” → „Selectați un grup de celule” → „Celele goale” și începeți să introduceți formula (adică puneți un semn) și referiți-vă la celula din partea de sus, pur și simplu făcând clic pe săgeata sus de pe tastatură. După aceea apăsați Ctrl + Enter. După aceea, puteți salva datele primite ca valori, deoarece formulele nu mai sunt necesare:

Cum să găsiți erori într-o formulă

Calculul unei părți separate a unei formule

Pentru a înțelege o formulă complexă (în care alte funcții sunt folosite ca argumente ale funcției, adică unele funcții sunt imbricate în altele) sau pentru a găsi sursa erorilor din ea, de multe ori trebuie să calculați o parte din ea. Există două moduri simple:

1. Pentru a calcula o parte dintr-o formulă chiar în bara de formule, selectați acea parte și apăsați F9:

   

   În acest exemplu, a existat o problemă cu funcția SEARCH - argumentele au fost schimbate în ea. Este important să rețineți că, dacă nu anulați calculul părții din funcție și nu apăsați Enter, atunci partea calculată va rămâne un număr.

2. Faceți clic pe butonul Calculate Formula din grupul Formule de pe panglică:

   

   În fereastra care apare, puteți calcula formula pas cu pas și puteți determina în ce stadiu și în ce funcție apare o eroare (dacă există):

   

Cum să determinați de ce depinde sau la ce se referă o formulă

Pentru a determina de ce celule depinde o formulă, în grupul Formule de pe panglică, faceți clic pe butonul Celulele care afectează:

Săgețile par să indice de ce depinde rezultatul calculului.

Dacă simbolul evidențiat în imagine în roșu este afișat, atunci formula depinde de celulele de pe alte foi sau din alte cărți:

Făcând clic pe el, putem vedea exact unde sunt situate celulele sau intervalele de influență:

Lângă butonul „Celele de influență” se află butonul „Celele dependente”, care funcționează în același mod: afișează săgeți din celula activă cu o formulă către celulele care depind de aceasta.

Butonul „Eliminați săgețile”, situat în același bloc, vă permite să eliminați săgețile către celulele de influență, săgețile către celulele dependente sau ambele tipuri de săgeți simultan:

Cum să găsiți suma (număr, medie) a valorilor celulelor din mai multe foi

Să presupunem că aveți mai multe foi de același tip cu date pe care doriți să le adăugați, să le numărați sau să le procesați într-un alt mod:

Pentru a face acest lucru, în celula în care doriți să vedeți rezultatul, introduceți o formulă standard, de exemplu SUM (SUMA) și specificați numele primei și ultimei foi din lista acelor foi pe care trebuie să le procesați în argumentul, separat prin două puncte:

Veți primi suma celulelor cu adresa B3 din foile „Data1”, „Data2”, „Data3”:

Această adresare funcționează pentru foile localizate consecvent … Sintaxa este următoarea: = FUNCȚIE (first_list: last_list! Referință de interval).

Cum să construiți automat expresii șablon

Folosind principiile de bază ale lucrului cu text în Excel și câteva funcții simple, puteți pregăti fraze șablon pentru rapoarte. Câteva principii de lucru cu text:

• Concatenăm textul folosind semnul & (îl puteți înlocui cu funcția CONCATENATE, dar asta nu are prea mult sens).
• Textul este întotdeauna scris între ghilimele, referințele la celulele cu text sunt întotdeauna fără.
• Pentru a obține caracterul de serviciu „ghilimele”, utilizați funcția CHAR cu argumentul 32.

Un exemplu de creare a unei fraze șablon folosind formule:

Rezultat:

În acest caz, pe lângă funcția CHAR (pentru a afișa ghilimele), se folosește funcția IF, care vă permite să schimbați textul în funcție de faptul dacă există o tendință pozitivă a vânzărilor, și funcția TEXT, care vă permite să afișați număr în orice format. Sintaxa sa este descrisă mai jos:

TEXT (valoare; format)

Formatul este specificat între ghilimele, la fel ca și cum ați introduce un format personalizat în fereastra Format Cells.

De asemenea, textele mai complexe pot fi automatizate. În practica mea, a existat automatizarea comentariilor lungi, dar de rutină la raportarea managementului în formatul „INDICATOR a scăzut/a crescut cu XX în raport cu planul, în principal datorită creșterii/scăderii FACTOR1 cu XX, creșterii/scăderii FACTORului2 cu XX. YY …” cu o listă schimbătoare de factori. Dacă scrieți des astfel de comentarii și procesul de scriere a acestora poate fi algoritmizat, merită odată derutant să creați o formulă sau o macrocomandă care să vă economisească măcar o parte din muncă.

Cum se stochează datele în fiecare celulă după concatenare

Când îmbinați celule, este reținută o singură valoare. Excel avertizează despre acest lucru atunci când încearcă să îmbine celulele:

În consecință, dacă ați avut o formulă în funcție de fiecare celulă, aceasta va înceta să funcționeze după combinarea lor (eroare # N / A în rândurile 3-4 din exemplu):

Pentru a îmbina celule și a păstra în continuare datele din fiecare dintre ele (poate că aveți o formulă ca în acest exemplu abstract; poate doriți să îmbinați celulele, dar să păstrați toate datele pentru viitor sau să le ascundeți în mod intenționat), îmbinați orice celule de pe foaie, selectați-le, apoi utilizați comanda Format Painter pentru a transfera formatarea în celulele pe care trebuie să le combinați:

Cum să construiți un pivot din mai multe surse de date

Dacă trebuie să construiți un pivot din mai multe surse de date simultan, va trebui să adăugați „PivotTable and Chart Wizard” la panglică sau la panoul de acces rapid, care are o astfel de opțiune.

Puteți face acest lucru după cum urmează: „Fișier” → „Opțiuni” → „Bară de instrumente cu acces rapid” → „Toate comenzile” → „Asistent tabel pivot și diagramă” → „Adăugare”:

După aceea, pe panglică va apărea o pictogramă corespunzătoare, făcând clic pe care apelează același expert:

Când faceți clic pe el, apare o casetă de dialog:

În el, trebuie să selectați elementul „În mai multe intervale de consolidare” și să faceți clic pe „Următorul”. În pasul următor, puteți selecta „Creați un câmp de pagină” sau „Creați câmpuri de pagină”. Dacă doriți să veniți independent cu un nume pentru fiecare dintre sursele de date, selectați al doilea element:

În fereastra următoare, adăugați toate intervalele pe baza cărora va fi construit pivotul și dați-le nume:

După aceea, în ultima casetă de dialog, specificați unde va fi plasat raportul tabel pivot - pe o foaie existentă sau nouă:

Raportul tabelului pivot este gata. În filtrul „Pagina 1”, puteți selecta doar una dintre sursele de date, dacă este necesar:

Cum se calculează numărul de apariții ale textului A în textul B („tarif MTS SuperMTS” - două apariții ale abrevierii MTS)

În acest exemplu, coloana A conține mai multe linii de text, iar sarcina noastră este să aflăm de câte ori fiecare dintre ele conține textul de căutare situat în celula E1:

Pentru a rezolva această problemă, puteți utiliza o formulă complexă constând din următoarele funcții:

1. DLSTR (LEN) - calculează lungimea textului, singurul argument este textul. Exemplu: DLSTR ("mașină") = 6.
2. SUBSTITUTE - înlocuiește un anumit text dintr-un șir de text cu altul. Sintaxă: SUBSTITUTE (text; text_vechi; text_nou). Exemplu: SUBSTITUTE („mașină”; „auto”; „”) = „mobil”.
3. UPPER - înlocuiește toate caracterele dintr-un șir cu majuscule. Singurul argument este textul. Exemplu: SUS („mașină”) = „MAȘINĂ”. Avem nevoie de această funcție pentru a face căutări care nu țin cont de majuscule și minuscule. La urma urmei, SUS ("mașină") = SUS ("Mașină")

Pentru a găsi apariția unui anumit șir de text într-un altul, trebuie să ștergeți toate aparițiile acestuia în cel original și să comparați lungimea șirului rezultat cu cel original:

DLSTR („Tariff MTS Super MTS”) - DLSTR („Tariff Super”) = 6

Și apoi împărțiți această diferență la lungimea șirului pe care o căutam:

6 / DLSTR („MTS”) = 2

De exact de două ori linia „MTS” este inclusă în cea originală.

Rămâne să scriem acest algoritm în limbajul formulelor (să notăm prin „text” textul în care căutăm aparițiile și prin „căutat” - cel al cărui număr de apariții ne interesează):

= (DLSTR (text) -LSTR (înlocuitor (SUS (text); SUS (căutare), ""))) / DLSTR (căutare)

În exemplul nostru, formula arată astfel:

= (DLSTR (A2) -LSTR (înlocuitor (SUS (A2), SUS ($ E $ 1), „”))) / DLSTR ($ E $ 1)