3 Formule nebun Excel care fac lucruri uimitoare

Microsoft Excel este unul dintre cele mai puternice instrumente de calcul tabelar, cu o colecție impresionantă de instrumente și caracteristici încorporate. În acest articol, veți afla cât de puternice sunt formulele Excel și formatul condițional, cu trei exemple utile.

Săpat în Microsoft Excel

Am acoperit o serie de moduri diferite de utilizare mai bună a programului Excel, cum ar fi utilizarea acestuia pentru a crea propriul șablon de calendar sau pentru a-l utiliza ca instrument de management de proiect.

O mare parte a puterii se află în spatele formulelor și regulilor Excel pe care le puteți scrie pentru a manipula automat datele și informațiile, indiferent de ce date inserați în foaia de calcul.

Să explorăm modul în care puteți folosi formule și alte instrumente pentru a utiliza mai bine Microsoft Excel.

Formatul condiționat cu formule

Unul dintre instrumentele pe care oamenii nu le utilizează destul de des este formatarea condiționată. Dacă sunteți în căutarea unor informații mai avansate despre formatarea condiționată în Microsoft Excel, asigurați-vă că ați verificat articolul lui Sandy privind formatarea datelor în Microsoft Excel cu formatare condiționată.

Cu ajutorul formulelor Excel, regulilor sau a doar câteva setări foarte simple, puteți transforma o foaie de calcul într-un tablou de bord automatizat.

Pentru a ajunge la Formatarea condiționată, faceți clic pe Acasă ș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 sunt dincolo de sfera de aplicare a acestui articol special, dar majoritatea acestora sunt despre evidențierea, colorarea sau umbrirea celulelor pe baza datelor din acea celulă.

Aceasta este probabil cea mai frecventă utilizare a formatării condiționate - lucruri precum rotirea unei celule roșii folosind mai puțin decât sau mai mult decât formulele. Aflați mai multe despre modul de utilizare a instrucțiunilor IF în Excel.

Unul dintre instrumentele de formatare condiționată mai puțin folosit este Seturi de pictograme , care oferă un set mare 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 Condiționat Manager de reguli de formatare.

Î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 în care 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 în raport cu timpul bugetat. Dacă depășiți jumătate din buget, se va afișa o lumină galbenă. Dacă sunteți în întregime peste buget, va deveni roșu.

După cum puteți vedea, acest tablou de bord arată că bugetarea de timp nu reușește.

Aproape jumătate din timp este cheltuit peste sumele însumate în buget.

Este timpul să vă reorientaț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ă un altul pentru dvs..

Probabil sunteți familiarizați cu funcția VLookup, care vă permite să căutați într-o coloană o listă pentru un anumit element și să returnați datele dintr-o coloană diferită în același rând ca 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 sunt la dreapta, dar dacă se schimbă?

În exemplul de mai jos, dacă aș vrea să găsesc sarcinile pe care le-am efectuat în data de 25.06.2018 din următoarele date?

În acest caz, căutați în valorile din dreapta și doriți să returnați valoarea corespunzătoare din stânga - opusă modului în care funcționează în mod normal VLookup.

Dacă citiți forumuri Microsoft Excel pentru pro-utilizatori veți găsi o mulțime de oameni spunând că acest lucru nu este posibil cu VLookup și că trebuie să utilizați o combinație de funcții Index și Match pentru a face acest lucru. Nu este chiar adevărat.

Puteți obține VLookup să lucreze în acest fel prin cuibărind o funcție CHOOSE în ea. În acest caz, formula Excel ar arăta astfel:

"= VLOOKUP (DATE (2018,6,25), ALEGE (1,2, E2: E8, A2: A8), 2,0)"

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

Î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, dreapta?

E adevărat, dar ce faci cu “ALEGE” funcția manipulează cele două câmpuri.

Atribuiți referință “index” numere la intervale de date - atribuirea datelor indexului numărul 1 și a sarcinilor la indexul 2.

Deci, când tastați “2” în funcția VLookup, vă referiți de fapt la Indexul 2 în funcția CHOOSE. Răcoros, bine?

Deci, acum VLookup folosește Data coloană și returnează datele din Sarcină , chiar dacă Task este în partea stângă.

Acum, că știți acest mic joc, imaginați-vă ce altceva puteți face!

Dacă încercați să efectuați alte activități avansate de căutare a datelor, asigurați-vă că verificați articolul integral al Dann privind găsirea datelor în Excel utilizând funcții de căutare.

2. Formulă imbricată pentru a parsa șirul

Iată o formulă nebun Excel pentru tine.

Pot exista cazuri în care fie importați date în Microsoft Excel dintr-o sursă exterioară 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ărul de telefon delimitat de “;” caracter.

Iată cum puteți analiza aceste informații utilizând 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), pur și simplu ați folosi o funcție LEFT în formula.

"= LEFT (A2, FIND (" "A2,1) -1)"

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

• Căută șirul text din A2
• Găsește “;” simbolul delimitator
• Subtrage unul pentru poziția corectă a capătului acelei secțiuni de șir
• Grabs textul din stânga la acel moment

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

3. Formulă născută în Excel

Dar 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 Nested (care funcționează de fapt), vom folosi o abordare unică.

Pentru a extrage piesele din partea dreaptă, trebuie să cuipeți mai multe funcții DREAPTA pentru a apuca secțiunea de text până la prima “;” simbol, și efectuați din nou funcția LEFT. Iată ce arată pentru extragerea părții cu numărul de stradă al adresei.

"= LEFT ((DREAPTA (A2, LEN (A2) Găsește-("“, A2))), FIND ( "", (RIGHT (A2, LEN (A2) Găsește-( "", A2)) ), 1) -1)“

Arată nebun, dar nu este greu să te bucuri împreună. Tot ce am făcut este să ia această funcție:

DREAPTA (A2, LEN (A2) Găsește-( ";", A2))

Și l-ați introdus în fiecare loc în funcția STÂNGA deasupra unde este un “A2”.

Aceasta extrage corect cea de-a doua secțiune a șirului.

Fiecare secțiune a șirului are nevoie de un alt cuib creat. Deci, acum luați nebunul “DREAPTA” ecuația pe care ați creat-o pentru ultima secțiune și apoi treceți-o într-o nouă formulă corectă cu formula anterioară RIGHT inserată în sine oriunde vedeți “A2”. Iată cum arată asta.

(DREAPTA ((DREAPTA (A2, LEN (A2) Găsește-( "", A2))), LEN ((DREAPTA (A2, LEN (A2) Găsește-( "", A2)))) - FIND ( "" (RIGHT (A2, LEN (A2) Găsește-( "", A2))))))

Apoi luați acea formulă și plasați-o în formula originală STÂNGA, oriunde există “A2”.

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

"= LEFT ((DREAPTA ((DREAPTA (A2, LEN (A2) Găsește-("“, A2))), LEN ((DREAPTA (A2, LEN (A2) Găsește-( "", A2))) ) Găsește-( "", (RIGHT (A2, LEN (A2) Găsește-( "", A2)))))), FIND ( "", (DREAPTA ((DREAPTA (A2, LEN (A2) Găsește-( "", A2))), LEN ((DREAPTA (A2, LEN (A2) Găsește-( "", A2)))) - FIND ( "", (RIGHT (A2, LEN (A2 ) Găsește-( "", A2)))))), 1) -1)“

Această formulă se 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 se pot îmbraca într-adevăr, dar tot ceea ce faceți este să tăiați și să lipiți formule lungi în sine, să creați cuiburi lungi care funcționează de fapt.

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

Doar selectați coloana cu datele delimitate și apoi sub Date , selectați Text în coloane.

Aceasta va aduce o fereastră în care puteți împărți șirul cu orice delimitator dorit.

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

Noțiuni de bază nebun cu Microsoft Excel

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

Uneori acele formule Excel nu sunt de fapt cea mai ușoară (sau cea mai bună) cale de a realiza lucrurile. Majoritatea programatorilor vă vor spune să o păstrați simplu, și este la fel de adevărat cu formulele Excel ca orice altceva.

Dacă doriți cu adevărat să folosiți programul Excel, veți dori să citiți ghidul nostru de începători pentru a utiliza Microsoft Excel Ghidul începătorului pentru Microsoft Excel Ghidul începătorului pentru Microsoft Excel Utilizați acest ghid pentru începători pentru a vă începe experiența cu Microsoft Excel. Sfaturile de bază pentru foaia de calcul de aici vă vor ajuta să începeți să învățați Excel pe cont propriu. Citeste mai mult . Are tot ce aveți nevoie pentru a începe să creșteți productivitatea cu Excel.

Explorați mai multe despre: Microsoft Excel, Microsoft Office 2016, Foaie de calcul.