19 sfaturi simple, trucuri și comenzi rapide Excel

Uneori, Excel pare prea bun pentru a fi adevărat. Tot ce trebuie să fac este să introduc o formulă și aproape orice aș avea nevoie să fac manual se poate face automat.

Trebuie să îmbinați două foi cu date similare? Excel o poate face.

Trebuie să faci matematică simplă? Excel o poate face.

Trebuie să combinați informații în mai multe celule? Excel o poate face.

În această postare, voi trece peste cele mai bune sfaturi, trucuri și comenzi rapide pe care le puteți folosi chiar acum pentru a vă duce jocul Excel la nivelul următor. Nu sunt necesare cunoștințe avansate de Excel.

Ce este Excel?

Microsoft Excel este un software puternic de vizualizare și analiză a datelor, care utilizează foi de calcul pentru a stoca, organiza și urmări seturi de date cu formule și funcții. Excel este folosit de marketeri, contabili, analiști de date și alți profesioniști. Face parte din suita de produse Microsoft Office. Alternativele includ Google Sheets și Numbers.

Găsiți mai multe alternative Excel aici.

Excel este folosit în principal pentru crearea de documente financiare datorită puterilor sale de calcul puternice. Veți găsi adesea software-ul în birourile și echipele de contabilitate, deoarece le permite contabililor să vadă automat sumele, mediile și totalurile. Cu Excel, aceștia pot înțelege cu ușurință datele afacerii lor.

În timp ce Excel este cunoscut în primul rând ca instrument de contabilitate, profesioniștii din orice domeniu își pot folosi caracteristicile și formulele - în special agenții de marketing - deoarece poate fi folosit pentru urmărirea oricărui tip de date. Îndepărtează nevoia de a petrece ore și ore numărând celulele sau copierea și lipirea numerelor de performanță. Excel are de obicei o comandă rapidă sau o remediere rapidă care accelerează procesul.

De asemenea, puteți descărca șabloane Excel de mai jos pentru toate nevoile dvs. de marketing.

După ce descărcați șabloanele, este timpul să începeți să utilizați software-ul. Să acoperim mai întâi elementele de bază.

Bazele Excel

Dacă abia începi cu Excel, există câteva comenzi de bază cu care vă sugerăm să vă familiarizați. Acestea sunt lucruri precum:

• Crearea unei noi foi de calcul de la zero.
• Executarea calculelor de bază precum adunarea, scăderea, înmulțirea și împărțirea.
• Scrierea și formatarea textului coloanei și a titlurilor.
• Utilizarea funcțiilor de completare automată ale Excel.
• Adăugarea sau ștergerea unor singure coloane, rânduri și foi de calcul. (Mai jos, vom vedea cum să adăugați lucruri precum mai multe coloane și rânduri.)
• Păstrarea titlurilor coloanelor și rândurilor vizibile pe măsură ce derulați pe lângă ele într-o foaie de calcul, astfel încât să știți ce date completați pe măsură ce vă deplasați mai jos în document.
• Sortarea datelor în ordine alfabetică.

Să explorăm câteva dintre acestea mai în profunzime.

De exemplu, de ce contează umplerea automată ?

Dacă aveți cunoștințe de bază despre Excel, este posibil să cunoașteți deja acest truc rapid. Dar pentru a ne acoperi bazele, permiteți-mi să vă arăt gloria umplerii automate. Acest lucru vă permite să completați rapid celulele adiacente cu mai multe tipuri de date, inclusiv valori, serii și formule.

Există mai multe moduri de a implementa această caracteristică, dar mânerul de umplere este printre cele mai simple. Selectați celulele pentru care doriți să fie sursă, localizați mânerul de umplere în colțul din dreapta jos al celulei și fie trageți mânerul de umplere pentru a acoperi celulele pe care doriți să le umpleți, fie faceți dublu clic:

În mod similar, sortarea este o caracteristică importantă pe care veți dori să o cunoașteți atunci când vă organizați datele în Excel.

Uneori este posibil să aveți o listă de date care nu are nicio organizație. Poate ați exportat o listă cu contactele dvs. de marketing sau postările de blog. Oricare ar fi cazul, Funcția de sortare a Excel vă va ajuta să alfabetizați orice listă.

Faceți clic pe datele din coloana pe care doriți să le sortați. Apoi faceți clic pe fila „Date” din bara de instrumente și căutați opțiunea „Sortați” din stânga. Dacă „A” este deasupra lui „Z”, puteți doar să faceți clic pe acel buton o dată. Dacă „Z” este deasupra „A”, faceți clic pe butonul de două ori. Când „A” este deasupra lui „Z”, înseamnă că lista dvs. va fi sortată în ordine alfabetică. Cu toate acestea, când „Z” este deasupra lui „A”, înseamnă că lista dvs. va fi sortată în ordine alfabetică inversă.

În continuare, să explorăm mai multe elemente de bază ale Excel (împreună cu funcțiile avansate).

Cum se utilizează Excel

Pentru a utiliza Excel, trebuie doar să introduceți datele în rânduri și coloane. Și apoi veți folosi formule și funcții pentru a transforma acele date în perspective.

Vom trece peste cele mai bune formule și funcții pe care trebuie să le cunoașteți. Dar mai întâi, să aruncăm o privire la tipurile de documente pe care le puteți crea folosind software-ul. În acest fel, aveți o înțelegere globală a modului în care puteți utiliza Excel în viața de zi cu zi.

Documente pe care le puteți crea în Excel

Nu ești sigur cum poți folosi Excel în echipa ta? Iată o listă de documente pe care le puteți crea:

• Declarații de venit: puteți utiliza o foaie de calcul Excel pentru a urmări activitatea de vânzări și sănătatea financiară a unei companii.
• Bilanțuri: Bilanțurile sunt printre cele mai comune tipuri de documente pe care le puteți crea cu Excel. Vă permite să obțineți o viziune holistică asupra situației financiare a unei companii.
• Calendar: puteți crea cu ușurință un calendar lunar cu foaie de calcul pentru a urmări evenimentele sau alte informații sensibile la dată.

Iată câteva documente pe care le puteți crea special pentru marketeri.

Aceasta este doar o mică mostră a tipurilor de documente de marketing și de afaceri pe care le puteți crea în Excel. Am creat o listă extinsă de șabloane Excel pe care le puteți folosi chiar acum pentru marketing, facturare, management de proiect, bugetare și multe altele.

În spiritul de a lucra mai eficient și de a evita munca manuală obositoare, iată câteva formule și funcții Excel pe care va trebui să le cunoașteți.

Formule Excel

Este ușor să fii copleșit de gama largă de formule Excel pe care le poți folosi pentru a înțelege datele tale. Dacă abia ați început să utilizați Excel, vă puteți baza pe următoarele formule pentru a îndeplini unele funcții complexe - fără a spori complexitatea traseului de învățare.

• Semn egal: înainte de a crea orice formulă, va trebui să scrieți un semn egal (=) în celula în care doriți să apară rezultatul.
• Adăugare : Pentru a adăuga valorile a două sau mai multe celule, utilizați semnul + . Exemplu: =C5+D3 .
• Scădere : Pentru a scădea valorile a două sau mai multe celule, utilizați semnul – . Exemplu: =C5-D3 .
• Înmulțire : pentru a înmulți valorile a două sau mai multe celule, utilizați semnul * . Exemplu: =C5*D3 .
• Diviziune : Pentru a împărți valorile a două sau mai multe celule, utilizați semnul / . Exemplu: =C5/D3 .

Punând toate acestea împreună, puteți crea o formulă care adună, scădea, înmulțește și împarte toate într-o singură celulă. Exemplu: =(C5-D3)/((A5+B6)*3) .

Pentru formule mai complexe, va trebui să utilizați paranteze în jurul expresiilor pentru a evita utilizarea accidentală a ordinii operațiilor PEMDAS. Rețineți că puteți utiliza numere simple în formulele dvs.

Funcții Excel

Funcțiile Excel automatizează unele dintre sarcinile pe care le-ați utiliza într-o formulă tipică. De exemplu, în loc să utilizați semnul + pentru a adăuga o serie de celule, ați folosi funcția SUM. Să ne uităm la câteva funcții suplimentare care vor ajuta la automatizarea calculelor și a sarcinilor.

• SUM: Funcția SUM adună automat o serie de celule sau numere. Pentru a finaliza o sumă, ar trebui să introduceți celula de pornire și celula finală cu două puncte între ele. Iată cum arată: SUM(Cell1:Cell2) . Exemplu: =SUM(C5:C30) .
• MEDIE: Funcția MEDIE face o medie a valorilor unui interval de celule. Sintaxa este aceeași cu funcția SUM: AVERAGE(Cell1:Cell2). Exemplu: =AVERAGE(C5:C30) .
• IF: Funcția IF vă permite să returnați valori pe baza unui test logic. Sintaxa este următoarea: IF(test_logic, valoare_dacă_adevărat, [valoare_dacă_fals]) . Exemplu: =IF(A2>B2,”Depășire buget”,”OK”) .
• CĂUTARE V: funcția CĂUTARE V vă ajută să căutați orice pe rândurile foii dvs. Sintaxa este: VLOOKUP(valoare de căutare, matrice de tabel, număr de coloană, Potrivire aproximativă (ADEVĂRAT) sau Potrivire exactă (FALSE)) . Exemplu: =VLOOKUP([@Avocat],tbl_Avocați,4,FALSE).
• INDEX: Funcția INDEX returnează o valoare dintr-un interval. Sintaxa este următoarea: INDEX(array, row_num, [column_num]) .
• MATCH: Funcția MATCH caută un anumit articol într-un interval de celule și returnează poziția acelui articol. Poate fi folosit în tandem cu funcția INDEX. Sintaxa este: MATCH(lookup_value, lookup_array, [match_type]) .
• COUNTIF: Funcția COUNTIF returnează numărul de celule care îndeplinesc un anumit criteriu sau au o anumită valoare. Sintaxa este: COUNTIF(interval, criterii). Exemplu: =COUNTIF(A2:A5,”Londra”).

Bine, ești gata să intri în mie? Să ajungem la asta. (Și tuturor fanilor Harry Potter de acolo... sunteți bineveniți în avans.)

Notă : GIF-urile și imaginile sunt dintr-o versiune anterioară de Excel. Când este cazul, copia a fost actualizată pentru a oferi instrucțiuni pentru utilizatorii versiunilor Excel noi și mai vechi.

1. Folosiți tabelele pivot pentru a recunoaște și a înțelege datele.

Tabelele pivot sunt folosite pentru a reorganiza datele într-o foaie de calcul. Nu vor schimba datele pe care le aveți, dar pot însuma valori și pot compara diferite informații din foaia de calcul, în funcție de ceea ce doriți să facă.

Să aruncăm o privire la un exemplu. Să presupunem că vreau să arunc o privire la câți oameni sunt în fiecare casă la Hogwarts. Poate vă gândiți că nu am prea multe date, dar pentru seturi de date mai lungi, acest lucru va fi util.

Pentru a crea Tabelul Pivot, merg la Date > Tabel Pivot . Dacă utilizați cea mai recentă versiune de Excel, veți merge la Insert > Pivot Table . Excel va completa automat tabelul pivot, dar puteți schimba oricând ordinea datelor. Apoi, aveți patru opțiuni din care să alegeți.

• Filtru de raport : Acesta vă permite să vă uitați numai la anumite rânduri din setul de date. De exemplu, dacă aș dori să creez un filtru în funcție de casă, aș putea alege să includ doar studenții din Gryffindor în loc de toți studenții.
• Etichete coloane : acestea ar fi anteturile dvs. din setul de date.
• Etichete de rând : acestea ar putea fi rândurile dvs. din setul de date. Atât etichetele de rând, cât și de coloană pot conține date din coloanele dvs. (de exemplu, Prenumele poate fi tras fie la eticheta de rând, fie de coloană - depinde doar de modul în care doriți să vedeți datele.)
• Valoare : această secțiune vă permite să vă uitați la datele în mod diferit. În loc să trageți doar orice valoare numerică, puteți să însumați, să numărați, media, max, min, numărați numere sau să faceți alte câteva manipulări cu datele dvs. De fapt, în mod implicit, când trageți un câmp în Valoare, acesta face întotdeauna o numărare.

Deoarece vreau să număr numărul de studenți din fiecare casă, voi merge la generatorul de tabel pivot și voi trage coloana Casă atât la Etichete de rând, cât și la Valori. Aceasta va însuma numărul de studenți asociați cu fiecare casă.

2. Adăugați mai multe rânduri sau coloane.

Pe măsură ce vă jucați cu datele, este posibil să descoperiți că aveți nevoie în mod constant să adăugați mai multe rânduri și coloane. Uneori, poate chiar trebuie să adăugați sute de rânduri. Făcând asta unul câte unul ar fi super plictisitor. Din fericire, există întotdeauna o cale mai ușoară.

Pentru a adăuga mai multe rânduri sau coloane într-o foaie de calcul, evidențiați același număr de rânduri sau coloane preexistente pe care doriți să le adăugați. Apoi, faceți clic dreapta și selectați „Inserați”.

În exemplul de mai jos, vreau să adaug încă trei rânduri. Evidențiind trei rânduri și apoi făcând clic pe Inserare, pot adăuga rapid și ușor încă trei rânduri goale în foaia de calcul.

3. Utilizați filtre pentru a vă simplifica datele.

Când te uiți la seturi de date foarte mari, de obicei nu trebuie să te uiți la fiecare rând în același timp. Uneori, vrei să te uiți doar la date care se încadrează în anumite criterii.

Acolo intervin filtrele.

Filtrele vă permit să reduceți datele pentru a vedea numai anumite rânduri simultan. În Excel, un filtru poate fi adăugat la fiecare coloană din datele dvs. - și de acolo, puteți alege apoi ce celule doriți să vizualizați simultan.

Să aruncăm o privire la exemplul de mai jos. Adăugați un filtru făcând clic pe fila Date și selectând „Filtru”. Făcând clic pe săgeata de lângă antetele coloanelor, veți putea alege dacă doriți ca datele dvs. să fie organizate în ordine crescătoare sau descrescătoare, precum și ce rânduri specifice doriți să afișați.

În exemplul meu Harry Potter, să presupunem că vreau să văd doar studenții din Gryffindor. Selectând filtrul Gryffindor, celelalte rânduri dispar.

Sfat profesionist : copiați și inserați valorile în foaia de calcul atunci când un filtru este activat pentru a face analize suplimentare într-o altă foaie de calcul.

4. Eliminați punctele sau seturile de date duplicat.

Seturile de date mai mari tind să aibă conținut duplicat. Este posibil să aveți o listă cu mai multe contacte într-o companie și să doriți doar să vedeți numărul de companii pe care le aveți. În astfel de situații, eliminarea duplicatelor este destul de utilă.

Pentru a elimina duplicatele, evidențiați rândul sau coloana pentru care doriți să eliminați duplicatele. Apoi, accesați fila Date și selectați „Eliminați duplicatele” (care se află sub subtitlul Instrumente în versiunea mai veche de Excel). Va apărea o fereastră pop-up pentru a confirma datele cu care doriți să lucrați. Selectați „Eliminați duplicatele” și sunteți gata.

De asemenea, puteți utiliza această funcție pentru a elimina un întreg rând pe baza unei valori de coloană duplicat. Deci, dacă aveți trei rânduri cu informațiile lui Harry Potter și trebuie să vedeți doar unul, atunci puteți selecta întregul set de date și apoi eliminați duplicatele pe baza e-mailului. Lista dvs. rezultată va avea doar nume unice, fără duplicate.

5. Transpuneți rândurile în coloane.

Când aveți rânduri de date în foaia de calcul, puteți decide că doriți să transformați elementele dintr-unul dintre aceste rânduri în coloane (sau invers). Ar fi nevoie de mult timp pentru a copia și lipi fiecare antet individual, dar ceea ce vă permite caracteristica de transpunere este să mutați pur și simplu datele de rând în coloane sau invers.

Începeți prin a evidenția coloana pe care doriți să o transpuneți în rânduri. Faceți clic dreapta pe el, apoi selectați „Copiere”. Apoi, selectați celulele din foaia de calcul unde doriți să înceapă primul rând sau coloană. Faceți clic dreapta pe celulă, apoi selectați „Lipire specială”. Va apărea un modul - în partea de jos, veți vedea o opțiune de transpunere. Bifați acea casetă și selectați OK. Coloana dvs. va fi acum transferată pe un rând sau invers.

Pe versiunile mai noi de Excel, va apărea un meniu drop-down în loc de o fereastră pop-up.

6. Împărțiți informațiile text între coloane.

Ce se întâmplă dacă doriți să împărțiți informațiile care se află într-o celulă în două celule diferite? De exemplu, poate doriți să scoateți numele companiei cuiva prin adresa de e-mail. Sau poate doriți să separați numele complet al cuiva într-un nume și un prenume pentru șabloanele dvs. de marketing prin e-mail.

Datorită Excel, ambele sunt posibile. Mai întâi, evidențiați coloana pe care doriți să o împărțiți. Apoi, accesați fila Date și selectați „Text în coloane”. Va apărea un modul cu informații suplimentare.

Mai întâi, trebuie să selectați fie „Delimitat”, fie „Fixed Width”.

• „Delimitat” înseamnă că doriți să despărțiți coloana pe baza caracterelor precum virgule, spații sau file.
• „Fixed Width” înseamnă că doriți să selectați locația exactă pe toate coloanele în care doriți să aibă loc împărțirea.

În exemplul de mai jos, să selectăm „Delimitat”, astfel încât să putem separa numele complet în prenume și prenume.

Apoi, este timpul să alegeți Delimitatorii. Acesta poate fi o tabulație, punct și virgulă, virgulă, spațiu sau altceva. („Altceva” ar putea fi semnul „@” folosit într-o adresă de e-mail, de exemplu.) În exemplul nostru, să alegem spațiul. Excel vă va afișa apoi o previzualizare a modului în care vor arăta noile dvs. coloane.

Când sunteți mulțumit de previzualizare, apăsați pe „Următorul”. Această pagină vă va permite să selectați Formate avansate dacă alegeți. Când ați terminat, faceți clic pe „Terminare”.

7. Folosiți formule pentru calcule simple.

Pe lângă efectuarea unor calcule destul de complexe, Excel vă poate ajuta să faceți aritmetică simplă, cum ar fi adăugarea, scăderea, înmulțirea sau împărțirea oricăror date.

• Pentru a adăuga, utilizați semnul +.
• Pentru a scădea, utilizați semnul –.
• Pentru a înmulți, utilizați semnul *.
• Pentru a împărți, utilizați semnul /.

De asemenea, puteți utiliza paranteze pentru a vă asigura că anumite calcule sunt făcute mai întâi. În exemplul de mai jos (10+10*10), al doilea și al treilea 10 au fost înmulțiți împreună înainte de a adăuga cei 10 suplimentari. Cu toate acestea, dacă am face (10+10)*10, primul și al doilea 10 s-ar aduna mai întâi. .

8. Obțineți media numerelor din celulele dvs.

Dacă doriți media unui set de numere, puteți utiliza formula =AVERAGE(Cell1:Cell2) . Dacă doriți să însumați o coloană de numere, puteți utiliza formula =SUM(Cell1:Cell2) .

9. Utilizați formatarea condiționată pentru a face celulele să își schimbe automat culoarea pe baza datelor.

Formatarea condiționată vă permite să schimbați culoarea unei celule pe baza informațiilor din celulă. De exemplu, dacă doriți să semnalați anumite numere care sunt peste medie sau în primele 10% din datele din foaia de calcul, puteți face asta. Dacă doriți să colorați punctele comune între diferitele rânduri din Excel, puteți face asta. Acest lucru vă va ajuta să vedeți rapid informațiile care sunt importante pentru dvs.

Pentru a începe, evidențiați grupul de celule pe care doriți să utilizați formatarea condiționată. Apoi, alegeți „Formatare condiționată” din meniul Acasă și selectați logica din meniul drop-down. (De asemenea, vă puteți crea propria regulă dacă doriți ceva diferit.) Va apărea o fereastră care vă solicită să furnizați mai multe informații despre regula de formatare. Selectați „OK” când ați terminat și ar trebui să vedeți automat rezultatele.

10. Utilizați formula IF Excel pentru a automatiza anumite funcții Excel.

Uneori, nu vrem să numărăm de câte ori apare o valoare. În schimb, dorim să introducem informații diferite într-o celulă dacă există o celulă corespunzătoare cu acea informație.

De exemplu, în situația de mai jos, vreau să acord zece puncte tuturor celor care aparțin casei Gryffindor. În loc să introduc manual 10 în dreptul numelui fiecărui student Gryffindor, pot folosi formula IF Excel pentru a spune că, dacă studentul este în Gryffindor, atunci ar trebui să obțină zece puncte.

Formula este: IF(test_logic, valoare_dacă_adevărată, [valoare_dacă_fals])

Exemplu prezentat mai jos: =IF(D2=”Gryffindor”,”10″,”0″)

În termeni generali, formula ar fi IF (Test logic, valoarea adevăratului, valoarea falsului). Să cercetăm fiecare dintre aceste variabile.

• Logical_Test : testul logic este partea „IF” a instrucțiunii. În acest caz, logica este D2=”Griffindor” deoarece dorim să ne asigurăm că celula corespunzătoare elevului spune „Griffindor”. Asigurați-vă că puneți Gryffindor între ghilimele aici.
• Value_if_True : Aceasta este ceea ce vrem să arate celula dacă valoarea este adevărată. În acest caz, dorim ca celula să arate „10” pentru a indica faptul că studentului i s-au acordat cele 10 puncte. Folosiți ghilimele numai dacă doriți ca rezultatul să fie text în loc de număr.
• Value_if_False : Aceasta este ceea ce vrem să arate celula dacă valoarea este falsă. În acest caz, pentru orice student care nu este în Gryffindor, dorim ca celula să arate „0”. Folosiți ghilimele numai dacă doriți ca rezultatul să fie text în loc de număr.

Notă : În exemplul de mai sus, am acordat 10 puncte tuturor celor din Gryffindor. Dacă mai târziu aș fi vrut să însumez numărul total de puncte, nu aș putea, deoarece 10 sunt între ghilimele, făcând astfel text și nu un număr pe care Excel îl poate însuma.

Puterea reală a funcției IF vine atunci când înșiri mai multe instrucțiuni IF împreună, sau cuib-le. Acest lucru vă permite să setați mai multe condiții, să obțineți rezultate mai specifice și, în cele din urmă, să vă organizați datele în bucăți mai ușor de gestionat.

Intervalele sunt o modalitate de a vă segmenta datele pentru o analiză mai bună. De exemplu, puteți clasifica datele în valori care sunt mai mici de 10, 11 până la 50 sau 51 până la 100. Iată cum arată în practică:

=IF(B3<11,„10 sau mai puțin”,IF(B3<51,„11 la 50”,IF(B3<100,„51 la 100”)))

Poate fi nevoie de încercări și erori, dar odată ce ați înțeles, formulele IF vor deveni noul tău cel mai bun prieten Excel.

11. Folosiți semnele dolarului pentru a păstra formula unei celule la fel, indiferent de locul în care se mișcă.

Ați văzut vreodată un semn dolar într-o formulă Excel? Când este folosit într-o formulă, nu reprezintă un dolar american; în schimb, se asigură că coloana și rândul exact sunt păstrate la fel, chiar dacă copiați aceeași formulă în rândurile adiacente.

Vedeți, o referință de celulă - când vă referiți la celula A5 din celula C5, de exemplu - este relativă în mod implicit. În acest caz, vă referiți de fapt la o celulă care are cinci coloane la stânga (C minus A) și pe același rând (5). Aceasta se numește formulă relativă. Când copiați o formulă relativă dintr-o celulă în alta, aceasta va ajusta valorile din formulă în funcție de locul în care este mutată. Dar uneori, dorim ca acele valori să rămână aceleași, indiferent dacă sunt mutate sau nu - și putem face asta transformând formula într-o formulă absolută.

Pentru a schimba formula relativă (=A5+C5) într-o formulă absolută, am precede valorile rândurilor și coloanei cu semnele dolarului, astfel: (=$A$5+$C$5) . (Aflați mai multe pe pagina de asistență Microsoft Office aici.)

12. Utilizați funcția CĂUTARE V pentru a extrage date dintr-o zonă a unei foi în alta.

Ați avut vreodată două seturi de date pe două foi de calcul diferite pe care doriți să le combinați într-o singură foaie de calcul?

De exemplu, este posibil să aveți o listă cu numele persoanelor lângă adresele lor de e-mail într-o foaie de calcul și o listă cu adresele de e-mail ale acelorași persoane lângă numele companiilor lor în cealaltă - dar doriți numele, adresele de e-mail și numele companiilor. a acelor oameni să apară într-un singur loc.

Trebuie să combin foarte multe seturi de date ca acesta - și atunci când o fac, VLOOKUP este formula mea de bază.

Înainte de a utiliza formula, totuși, fiți absolut sigur că aveți cel puțin o coloană care apare identic în ambele locuri. Scoateți-vă seturile de date pentru a vă asigura că coloana de date pe care o utilizați pentru a vă combina informațiile este exact aceeași, fără spații suplimentare.

Formula: =CĂUTAREV(valoare de căutare, matrice de tabel, număr de coloană, potrivire aproximativă (ADEVĂRAT) sau potrivire exactă (FALSE))

Formula cu variabile din exemplul nostru de mai jos: =VLOOKUP(C2,Sheet2!A:B,2,FALSE)

În această formulă, există mai multe variabile. Următoarele sunt valabile atunci când doriți să combinați informațiile din Foaia 1 și Foaia 2 în Foaia 1.

• Valoare de căutare : aceasta este valoarea identică pe care o aveți în ambele foi de calcul. Alegeți prima valoare din prima foaie de calcul. În exemplul care urmează, aceasta înseamnă prima adresă de e-mail din listă sau celula 2 (C2).
• Table Array : Matricea tabelului este intervalul de coloane din foaia 2 din care veți extrage datele, inclusiv coloana de date identică cu valoarea de căutare (în exemplul nostru, adresele de e-mail) din foaia 1, precum și coloana de date pe care încercați să le copiați în foaia 1. În exemplul nostru, acesta este „Sheet2!A:B”. „A” înseamnă coloana A din foaia 2, care este coloana din foaia 2 în care sunt enumerate datele identice cu valoarea noastră de căutare (e-mail) din foaia 1. „B” înseamnă coloana B, care conține informațiile care sunt disponibile numai în foaia 2 pe care doriți să le traduceți în foaia 1.
• Număr de coloană : acesta îi spune lui Excel în ce coloană se află noile date pe care doriți să le copiați în foaia 1. În exemplul nostru, aceasta ar fi coloana în care se află „Casa”. „Casa” este a doua coloană din gama noastră de coloane (matrice de tabel), deci numărul coloanei noastre este 2. [ Notă : intervalul dvs. poate fi mai mult de două coloane. De exemplu, dacă există trei coloane pe Foaia 2 — E-mail, Vârstă și Casă — și încă doriți să aduceți Casa pe Foaia 1, puteți utiliza în continuare o CĂUTARE V. Trebuie doar să schimbați „2” cu un „3”, astfel încât să trage înapoi valoarea din a treia coloană: =VLOOKUP(C2:Sheet2!A:C,3,false).]
• Potrivire aproximativă (ADEVĂRAT) sau Potrivire exactă (FALSE) : utilizați FALSE pentru a vă asigura că introduceți doar potriviri exacte ale valorii. Dacă utilizați TRUE, funcția va genera potriviri aproximative.

În exemplul de mai jos, Foaia 1 și Foaia 2 conțin liste care descriu informații diferite despre aceleași persoane, iar firul comun dintre cele două sunt adresele lor de e-mail. Să presupunem că vrem să combinăm ambele seturi de date, astfel încât toate informațiile despre casă din foaia 2 să se traducă în foaia 1.

Deci, când introducem formula =VLOOKUP(C2,Sheet2!A:B,2,FALSE) , aducem toate datele casei în Foaia 1.

Rețineți că VLOOKUP va retrage numai valorile din a doua foaie care se află în dreapta coloanei care conține datele dvs. identice. Acest lucru poate duce la unele limitări, motiv pentru care unii oameni preferă să folosească funcțiile INDEX și MATCH.

13. Folosiți formulele INDEX și MATCH pentru a extrage date din coloanele orizontale.

La fel ca VLOOKUP, funcțiile INDEX și MATCH aduc date dintr-un alt set de date într-o locație centrală. Iată principalele diferențe:

• VLOOKUP este o formulă mult mai simplă. Dacă lucrați cu seturi mari de date care ar necesita mii de căutări, utilizarea funcției INDEX și MATCH va reduce semnificativ timpul de încărcare în Excel.
• Formulele INDEX și MATCH funcționează de la dreapta la stânga, în timp ce formulele VLOOKUP funcționează doar ca o căutare de la stânga la dreapta. Cu alte cuvinte, dacă trebuie să faceți o căutare care are o coloană de căutare în partea dreaptă a coloanei cu rezultate, atunci va trebui să rearanjați acele coloane pentru a face o CĂUTARE V. Acest lucru poate fi plictisitor cu seturi mari de date și/sau poate duce la erori.

Deci, dacă vreau să combin informațiile din Foaia 1 și Foaia 2 în Foaia 1, dar valorile coloanei din Foile 1 și 2 nu sunt aceleași, atunci pentru a face o CĂUTARE V, ar trebui să schimb coloanele mele. În acest caz, aș alege să fac un INDEX și MATCH.

Să ne uităm la un exemplu. Să presupunem că Foaia 1 conține o listă cu numele oamenilor și adresele lor de e-mail Hogwarts, iar Foaia 2 conține o listă cu adresele de e-mail ale oamenilor și Patronus-ul pe care îl are fiecare elev. (Pentru fanii care nu sunt Harry Potter, fiecare vrăjitoare sau vrăjitor are asociat cu el sau ea un tutore de animale numit „Patronus”.) Informațiile care se află în ambele foi sunt coloana care conține adrese de e-mail, dar această coloană cu adrese de e-mail este în numere diferite de coloane pe fiecare foaie. Aș folosi formulele INDEX și MATCH în loc de VLOOKUP, astfel încât să nu trebuiască să schimb nicio coloană.

Deci, care este formula? Formula este de fapt formula MATCH imbricată în formula INDEX. Veți vedea că aici am diferențiat formula MATCH folosind o culoare diferită.

Formula: =INDEX(matrice tabel, formula MATCH)

Acesta devine: =INDEX(matrice de tabel, MATCH ( valoare_căutare, matrice_căutare))

Formula cu variabile din exemplul nostru de mai jos: =INDEX(Sheet2!A:A,(MATCH(Sheet1!C:C,Sheet2!C:C,0)))

Iată variabilele:

• Table Array : Gama de coloane din Foaia 2 care conțin noile date pe care doriți să le aduceți în Foaia 1. În exemplul nostru, „A” înseamnă coloana A, care conține informațiile „Patronus” pentru fiecare persoană.
• Valoare de căutare : aceasta este coloana din foaia 1 care conține valori identice în ambele foi de calcul. În exemplul care urmează, aceasta înseamnă coloana „e-mail” de pe foaia 1, care este coloana C. Deci: Sheet1!C:C.
• Matrice de căutare : aceasta este coloana din foaia 2 care conține valori identice în ambele foi de calcul. În exemplul care urmează, aceasta se referă la coloana „e-mail” de pe foaia 2, care se întâmplă să fie și coloana C. Deci: Sheet2!C:C.

După ce ați clarificat variabilele, introduceți formulele INDEX și MATCH în celula cea mai de sus a coloanei necompletate Patronus de pe foaia 1, unde doriți să trăiască informațiile combinate.

14. Utilizați funcția COUNTIF pentru a face Excel să numere cuvinte sau numere în orice interval de celule.

În loc să numărați manual cât de des apare o anumită valoare sau număr, lăsați Excel să facă treaba pentru dvs. Cu funcția COUNTIF, Excel poate număra de câte ori un cuvânt sau un număr apare în orice interval de celule.

De exemplu, să presupunem că vreau să număr de câte ori apare cuvântul „Gryffindor” în setul meu de date.

Formula: =COUNTIF(interval, criterii)

Formula cu variabile din exemplul nostru de mai jos: =COUNTIF(D:D,”Gryffindor”)

În această formulă, există mai multe variabile:

• Interval : intervalul pe care dorim să îl acopere formula. În acest caz, deoarece ne concentrăm doar pe o singură coloană, folosim „D:D” pentru a indica faptul că prima și ultima coloană sunt ambele D. Dacă m-aș uita la coloanele C și D, aș folosi „C:D .”
• Criterii : Indiferent de numărul sau fragmentul de text pe care doriți să îl numărați Excel. Folosiți ghilimele numai dacă doriți ca rezultatul să fie text în loc de număr. În exemplul nostru, criteriul este „Gryffindor”.

Pur și simplu tastând formula COUNTIF în orice celulă și apăsând „Enter” îmi va arăta de câte ori apare cuvântul „Gryffindor” în setul de date.

15. Combinați celule folosind &.

Bazele de date tind să împartă datele pentru a le face cât mai exacte posibil. De exemplu, în loc să aibă o coloană care arată numele complet al unei persoane, o bază de date poate avea datele ca prenume și apoi un nume de familie în coloane separate. Sau, poate avea locația unei persoane separate prin oraș, stat și cod poștal. În Excel, puteți combina celule cu date diferite într-o singură celulă, folosind semnul „&” din funcția dumneavoastră.

Formula cu variabile din exemplul nostru de mai jos: =A2&” „&B2

Să parcurgem împreună formula folosind un exemplu. Prefaceți-vă că vrem să combinăm prenumele și numele de familie în nume complete într-o singură coloană. Pentru a face acest lucru, mai întâi ne-am plasa cursorul în celula goală unde vrem să apară numele complet. Apoi, evidențiem o celulă care conține un prenume, introducem semnul „&”, apoi evidențiem o celulă cu numele de familie corespunzător.

Dar nu ai terminat - dacă tot ce introduci este =A2&B2, atunci nu va exista niciun spațiu între prenumele și numele persoanei. Pentru a adăuga acel spațiu necesar, utilizați funcția =A2&” „&B2 . Ghilimelele din jurul spațiului spun Excel să pună un spațiu între nume și prenume.

Pentru a face acest lucru adevărat pentru mai multe rânduri, pur și simplu trageți colțul primei celule în jos, așa cum se arată în exemplu.

16. Adăugați casete de selectare.

If you're using an Excel sheet to track customer data and want to oversee something that isn't quantifiable, you could insert checkboxes into a column.

For example, if you're using an Excel sheet to manage your sales prospects and want to track whether you called them in the last quarter, you could have a “Called this quarter?” column and check off the cells in it when you've called the respective client.

Iată cum să o faci.

Highlight a cell you'd like to add checkboxes to in your spreadsheet. Then, click DEVELOPER. Then, under FORM CONTROLS, click the checkbox or the selection circle highlighted in the image below.

Once the box appears in the cell, copy it, highlight the cells you also want it to appear in, and then paste it.

17. Hyperlink a cell to a website.

If you're using your sheet to track social media or website metrics, it can be helpful to have a reference column with the links each row is tracking. If you add a URL directly into Excel, it should automatically be clickable. But, if you have to hyperlink words, such as a page title or the headline of a post you're tracking, here's how.

Highlight the words you want to hyperlink, then press Shift K. From there a box will pop up allowing you to place the hyperlink URL. Copy and paste the URL into this box and hit or click Enter.

If the key shortcut isn't working for any reason, you can also do this manually by highlighting the cell and clicking Insert > Hyperlink .

18. Add drop-down menus.

Sometimes, you'll be using your spreadsheet to track processes or other qualitative things. Rather than writing words into your sheet repetitively, such as “Yes”, “No”, “Customer Stage”, “Sales Lead”, or “Prospect”, you can use dropdown menus to quickly mark descriptive things about your contacts or whatever you're tracking.

Here's how to add drop-downs to your cells.

Highlight the cells you want the drop-downs to be in, then click the Data menu in the top navigation and press Validation.

From there, you'll see a Data Validation Settings box open. Look at the Allow options, then click Lists and select Drop-down List. Check the In-Cell dropdown button, then press OK.

19. Use the format painter.

As you've probably noticed, Excel has a lot of features to make crunching numbers and analyzing your data quick and easy. But if you ever spent some time formatting a sheet to your liking, you know it can get a bit tedious.

Don't waste time repeating the same formatting commands over and over again. Use the format painter to easily copy the formatting from one area of the worksheet to another. To do so, choose the cell you'd like to replicate, then select the format painter option (paintbrush icon) from the top toolbar.

Excel Keyboard Shortcuts

Creating reports in Excel is time-consuming enough. How can we spend less time navigating, formatting, and selecting items in our spreadsheet? Glad you asked. There are a ton of Excel shortcuts out there, including some of our favorites listed below.

Create a New Workbook

PC: Ctrl-N | Mac: Command-N

Select Entire Row

PC: Shift-Space | Mac: Shift-Space

Select Entire Column

PC: Ctrl-Space | Mac: Control-Space

Select Rest of Column

PC: Ctrl-Shift-Down/Up | Mac: Command-Shift-Down/Up

Select Rest of Row

PC: Ctrl-Shift-Right/Left | Mac: Command-Shift-Right/Left

Add Hyperlink

PC: Ctrl-K | Mac: Command-K

Open Format Cells Window

PC: Ctrl-1 | Mac: Command-1

Autosum Selected Cells

PC: Alt-= | Mac: Command-Shift-T

Other Excel Help Resources

Use Excel to Automate Processes in Your Team

Even if you're not an accountant, you can still use Excel to automate tasks and processes in your team. With the tips and tricks we shared in this post, you'll be sure to use Excel to its fullest extent and get the most out of the software to grow your business.

Editor's Note: This post was originally published in August 2017 but has been updated for comprehensiveness.