3 formule nebunești Microsoft Excel extrem de utile

3 formule nebunești Microsoft Excel extrem de utile

Formulele Microsoft Excel pot face aproape orice. În acest articol, veți afla cât de puternice pot fi formulele Microsoft Excel și formatarea condiționată, cu trei exemple utile.





Cunoașterea Microsoft Excel

Am acoperit o serie de moduri diferite de a utiliza mai bine Excel, cum ar fi unde să găsiți șabloane Excel excelente care pot fi descărcate , și cum să utilizați Excel ca instrument de gestionare a proiectelor .





O mare parte din puterea Excel se află în spatele formulelor și regulilor Excel care vă ajută să manipulați automat datele și informațiile, indiferent de datele pe care le introduceți în foaia de calcul.





Să cercetăm cum puteți utiliza formule și alte instrumente pentru a utiliza mai bine Microsoft Excel.

Formatare condiționată cu formule Excel

Unul dintre instrumentele pe care oamenii nu le folosesc suficient de des este formatarea condiționată. Cu ajutorul formulelor Excel, regulilor sau doar câteva setări simple, puteți transforma o foaie de calcul într-un tablou de bord automat.



Pentru a ajunge la Formatarea condiționată, trebuie doar să faceți clic pe Acasă fila și faceți clic pe Formatarea condițională pictograma barei de instrumente.

Sub Formatare condiționată, există o mulțime de opțiuni. Cele mai multe dintre acestea depășesc domeniul de aplicare al acestui articol, dar majoritatea dintre ele vizează evidențierea, colorarea sau umbrirea celulelor pe baza datelor din acea celulă.





Aceasta este probabil cea mai obișnuită utilizare a formatării condiționate - lucruri precum transformarea unei celule în roșu folosind formule mai mici decât sau mai mari decât. Aflați mai multe despre cum să utilizați declarațiile IF în Excel.

Unul dintre instrumentele de formatare condiționată mai puțin utilizate este Seturi de pictograme opțiune, care oferă un set excelent de pictograme pe care le puteți utiliza pentru a transforma o celulă de date Excel într-o pictogramă de afișare a tabloului de bord.





Când faceți clic pe Gestionați regulile , te va duce la Managerul de reguli de formatare condiționată .

cum să creezi un joc roblox

În funcție de datele pe care le-ați selectat înainte de a alege setul de pictograme, veți vedea celula indicată în fereastra Manager cu setul de pictograme pe care tocmai l-ați ales.

Când faceți clic pe Editați regula , veți vedea dialogul unde se întâmplă magia.

Aici puteți crea formula logică și ecuațiile care vor afișa pictograma de bord dorită.

Acest exemplu de tablou de bord va arăta timpul petrecut pentru diferite sarcini față de timpul bugetat. Dacă depășiți jumătate din buget, se va afișa o lumină galbenă. Dacă ați depășit complet bugetul, va deveni roșu.

După cum puteți vedea, acest tablou de bord arată că bugetarea timpului nu are succes.

Aproape jumătate din timp este petrecut cu mult peste sumele bugetate.

E timpul să vă concentrați și să vă gestionați mai bine timpul!

1. Utilizarea funcției VLookup

Dacă doriți să utilizați funcții Microsoft Excel mai avansate, atunci iată câteva pentru a le încerca.

Probabil că sunteți familiarizat cu funcția VLookup, care vă permite să căutați într-o listă un anumit element dintr-o coloană și să returnați datele dintr-o coloană diferită din același rând cu acel element.

Din păcate, funcția necesită ca elementul pe care îl căutați în listă să fie în coloana din stânga, iar datele pe care le căutați să fie în dreapta, dar ce se întâmplă dacă sunt schimbate?

În exemplul de mai jos, ce se întâmplă dacă vreau să găsesc Activitatea pe care am efectuat-o pe 25.06.2018 din următoarele date?

În acest caz, căutați valorile din dreapta și doriți să returnați valoarea corespunzătoare din stânga.

Dacă citiți forumurile pentru utilizatori Microsoft Excel, veți găsi mulți oameni care spun că acest lucru nu este posibil cu VLookup. Pentru a face acest lucru, trebuie să utilizați o combinație de funcții Index și Match. Nu este cu adevărat adevărat.

Puteți face ca VLookup să funcționeze în acest fel cuibărind o funcție CHOOSE în ea. În acest caz, formula Excel ar arăta astfel:

'=VLOOKUP(DATE(2018,6,25),CHOOSE({1,2},E2:E8,A2:A8),2,0)'

Această funcție înseamnă că doriți să găsiți data 25.06.2013 în lista de căutare și apoi să returnați valoarea corespunzătoare din indexul coloanei.

În acest caz, veți observa că indexul coloanei este „2”, dar după cum puteți vedea, coloana din tabelul de mai sus este de fapt 1, nu?

Este adevărat, dar ce faci cu ALEGE Funcția manipulează cele două câmpuri.

Atribuiți numere de „index” de referință intervalelor de date - atribuiți datele la indexul numărul 1 și sarcinile la indexul numărul 2.

Deci, când tastați „2” în funcția VLookup, vă referiți de fapt la numărul 2 din funcția ALEGE. Mișto, nu?

VLookup folosește acum Data și returnează date din Sarcină coloană, chiar dacă Task este în stânga.

cum se restituie dlc pe abur

Acum, că știi acest mic pic, doar imaginează-ți ce altceva poți face!

Dacă încercați să efectuați alte sarcini avansate de căutare a datelor, consultați acest articol găsirea datelor în Excel folosind funcțiile de căutare .

2. Formula imbricată pentru a analiza șirurile

Iată încă o formulă Excel nebună pentru tine! Pot exista cazuri în care fie importați date în Microsoft Excel dintr-o sursă externă constând dintr-un șir de date delimitate.

Odată ce introduceți datele, doriți să analizați aceste date în componentele individuale. Iată un exemplu de informații despre nume, adresă și număr de telefon delimitate de „;” caracter.

Iată cum puteți analiza aceste informații folosind o formulă Excel (vedeți dacă puteți urmări mental împreună cu această nebunie):

Pentru primul câmp, pentru a extrage elementul din stânga (numele persoanei), ar trebui să utilizați pur și simplu o funcție STÂNGA în formulă.

'=LEFT(A2,FIND(';',A2,1)-1)'

Iată cum funcționează această logică:

  • Căutează șirul de text din A2
  • Găsește „;” simbol delimitator
  • Scade unul pentru locația corectă a sfârșitului secțiunii șirului respectiv
  • Prinde textul din stânga până la acel punct

În acest caz, textul din stânga este „Ryan”. Misiune indeplinita.

3. Formula imbricată în Excel

Dar ce se întâmplă cu celelalte secțiuni?

S-ar putea să existe modalități mai ușoare de a face acest lucru, dar din moment ce dorim să încercăm să creăm cea mai nebună formulă Excel imbricată posibilă (care funcționează de fapt), vom folosi o abordare unică.

Pentru a extrage părțile din dreapta, trebuie să cuibăriți mai multe funcții DREAPTA pentru a prelua secțiunea de text până la acea primă ';' și efectuați din nou funcția STÂNGA. Iată cum arată acest lucru pentru extragerea părții cu numărul de stradă a adresei.

'=LEFT((RIGHT(A2,LEN(A2)-FIND(';',A2))),FIND(';',(RIGHT(A2,LEN(A2)-FIND(';',A2))),1)-1)'

Pare o nebunie, dar nu este greu să le împărțiți. Tot ce am făcut este să ia această funcție:

RIGHT(A2,LEN(A2)-FIND(';',A2))

Și l-a introdus în fiecare loc din STÂNGA funcția de mai sus unde există un „A2”.

Aceasta extrage corect a doua secțiune a șirului.

conectat la Windows 10 fără încărcare

Fiecare secțiune ulterioară a șirului are nevoie de un alt cuib creat. Acum tot ce trebuie să faceți este să luați DREAPTA „ecuația pe care ați creat-o în ultima secțiune și lipiți-o într-o nouă formulă DREAPTA cu formula DREAPTA anterioară lipită în ea unde vedeți„ A2 ”. Iată cum arată.

(RIGHT((RIGHT(A2,LEN(A2)-FIND(';',A2))),LEN((RIGHT(A2,LEN(A2)-FIND(';',A2))))-FIND(';',(RIGHT(A2,LEN(A2)-FIND(';',A2))))))

Apoi, trebuie să luați acea formulă și să o plasați în formula originală STÂNGA oriunde există un „A2”.

Formula finală de înțelegere a minții arată astfel:

'=LEFT((RIGHT((RIGHT(A2,LEN(A2)-FIND(';',A2))),LEN((RIGHT(A2,LEN(A2)-FIND(';',A2))))-FIND(';',(RIGHT(A2,LEN(A2)-FIND(';',A2)))))),FIND(';',(RIGHT((RIGHT(A2,LEN(A2)-FIND(';',A2))),LEN((RIGHT(A2,LEN(A2)-FIND(';',A2))))-FIND(';',(RIGHT(A2,LEN(A2)-FIND(';',A2)))))),1)-1)'

Această formulă extrage corect „Portland, ME 04076” din șirul original.

Pentru a extrage următoarea secțiune, repetați procesul de mai sus din nou.

Formulele dvs. Excel pot deveni foarte neobișnuite, dar tot ce faceți este să tăiați și să lipiți formule lungi în ele însele, făcând cuiburi lungi care încă funcționează.

Da, acest lucru îndeplinește cerința pentru „nebun”. Dar să fim sinceri, există o modalitate mult mai simplă de a realiza același lucru cu o singură funcție.

Doar selectați coloana cu datele delimitate, apoi sub Date element de meniu, selectați Text către coloane .

Aceasta va afișa o fereastră în care puteți împărți șirul după orice delimitator doriți. Pur și simplu introduceți ' ; 'și veți vedea că previzualizarea datelor selectate se modifică corespunzător.

În câteva clicuri, poți face același lucru cu formula nebună de mai sus ... dar unde e distracția?

Deveniți nebuni cu formulele Microsoft Excel

Deci, iată-l. Formulele de mai sus demonstrează cât de mult poate obține o persoană atunci când creează formule Microsoft Excel pentru a îndeplini anumite sarcini.

Uneori, acele formule Excel nu sunt de fapt cel mai simplu (sau cel mai bun) mod de a realiza lucrurile. Majoritatea programatorilor vă vor spune să îl păstrați simplu și acest lucru este la fel de adevărat cu formulele Excel ca și cu orice altceva.

Dacă doriți cu adevărat să fiți serios cu utilizarea Excel, veți dori să citiți ghidul nostru pentru începători pentru utilizarea Microsoft Excel. Are tot ce aveți nevoie pentru a începe să vă creșteți productivitatea cu Excel. După aceasta, asigurați-vă că consultați foaia de trucuri esențiale pentru funcțiile Excel pentru mai multe îndrumări.

Credit de imagine: kues / Depositphotos

Acțiune Acțiune Tweet E-mail Cele mai importante 7 funcții financiare din Excel

Fie că sunteți contabil sau profesionist în finanțe, ar trebui să cunoașteți aceste formule Excel.

Citiți în continuare
Subiecte asemănătoare
  • Productivitate
  • Sfaturi pentru foi de calcul
  • Microsoft Excel
  • Microsoft Office 2016
  • Sfaturi Microsoft Office
  • Microsoft Office 2019
Despre autor Ryan Dube(942 articole publicate)

Ryan are o diplomă de licență în inginerie electrică. A lucrat 13 ani în ingineria automatizării, 5 ani în IT, iar acum este inginer de aplicații. Fost editor manager al MakeUseOf, a vorbit la conferințe naționale privind vizualizarea datelor și a fost prezentat la TV și radio naționale.

Mai multe de la Ryan Dube

Aboneaza-te la newsletter-ul nostru

Alăturați-vă newsletter-ului pentru sfaturi tehnice, recenzii, cărți electronice gratuite și oferte exclusive!

Faceți clic aici pentru a vă abona