3 lude Excel formule koje čine zadivljujuće stvari

  • Lesley Fowler
  • 0
  • 2696
  • 515
Oglas

Formule Excel mogu učiniti gotovo sve. U ovom ćete članku naučiti kako moćne Microsoft Excel formule i uvjetno oblikovanje mogu biti s tri korisna primjera.

Kopanje u Microsoft Excel

Opisali smo nekoliko različitih načina da bolje iskoristimo Excel, poput njegovog korištenja za izradu vlastitog predloška kalendara ili upotrebe kao alata za upravljanje projektima.

Veliki dio snage leži iza Excelovih formula i pravila koja možete napisati kako biste automatski upravljali podacima i informacijama, bez obzira na to koje podatke umetnite u proračunsku tablicu..

Razmotrimo kako možete koristiti formule i druge alate za bolje korištenje Microsoft Excel-a.

Uvjetno oblikovanje s Excelovim formulama

Jedan od alata koji ljudi ne koriste dovoljno često je uvjetno oblikovanje. Ako tražite naprednije informacije o uvjetnom oblikovanju u programu Microsoft Excel, provjerite Sandyin članak o oblikovanju podataka u programu Microsoft Excel s uvjetnim oblikovanjem..

Pomoću Excel formula, pravila ili samo nekoliko stvarno jednostavnih postavki, možete pretvoriti proračunsku tablicu u automatiziranu nadzornu ploču.

Da biste došli do uvjetnog oblikovanja, samo kliknite na Dom i kliknite na Uvjetno oblikovanje ikona alatne trake.

Pod uvjetnim oblikovanjem postoji puno opcija. Većina njih je izvan dosega ovog određenog članka, ali većina se odnosi na isticanje, bojanje ili sjenčanje ćelija na temelju podataka unutar te ćelije.

To je vjerojatno najčešća upotreba uvjetnog oblikovanja - stvari poput pretvaranja ćelije u crveno koristeći formule manje ili veće od formula. Saznajte više o tome kako koristiti IF izjave u Excelu.

Jedan od manje korištenih alata za uvjetno oblikovanje je Postavlja ikone opcija koja nudi sjajan niz ikona pomoću kojih možete pretvoriti Excel ćeliju podataka u ikonu zaslona na nadzornoj ploči.

Kada kliknete na Upravljanje pravilima, odvest će vas do vrata Upravitelj pravila uvjetnog oblikovanja.

Ovisno o odabranim podacima prije odabira skupa ikona, prikazat će se ćelija navedena u prozoru Upravitelj, s odabranim setom ikona.

Kada kliknete na Uredi pravilo, vidjet ćete dijaloški okvir gdje se događa magija.

Ovdje možete stvoriti logičku formulu i jednadžbe koje će prikazati željenu ikonu nadzorne ploče.

Ovaj primjer nadzorne ploče pokazat će vrijeme provedeno na različite zadatke u odnosu na predviđeno vrijeme. Ako premašite pola proračuna, prikazat će se žuto svjetlo. Ako ste u potpunosti potrošili proračun, pocrvenit će.

Kao što vidite, ova nadzorna ploča pokazuje da vremenski proračun nije uspješan.

Gotovo polovica vremena troši se preko proračunskih iznosa.

Vrijeme je za ponovno fokusiranje i bolje upravljanje vremenom!

1. Korištenje VLookup funkcije

Ako želite koristiti naprednije funkcije programa Microsoft Excel, evo još jedne za vas.

Vjerojatno ste upoznati s funkcijom VLookup koja vam omogućuje pretraživanje po popisu za određenu stavku u jednom stupcu i vraćanje podataka iz drugog stupca u istom retku kao i ta stavka.

Nažalost, funkcija zahtijeva da se stavka koju tražite na popisu nalazi u lijevom stupcu, a podaci koji tražite nalaze se s desne strane, ali što ako su prebačeni?

U primjeru u nastavku, što ako od sljedećih podataka želim pronaći zadatak koji sam obavio 25.6.2018?

U ovom slučaju pretražujete vrijednosti s desne strane i želite vratiti odgovarajuću vrijednost s lijeve strane - suprotno načinu na koji VLookup obično radi.

Ako čitate korisničke forume Microsoftovog Excela, naći ćete puno ljudi koji kažu da to nije moguće s VLookupom i da za to morate koristiti kombinaciju indeksa i podudaranja funkcija. To nije sasvim istina.

Možete dobiti VLookup da radi na ovaj način umetanjem funkcije CHOOSE u njega. U ovom slučaju, Excel formula će izgledati ovako:

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

Što ova funkcija znači je da želite pronaći datum 6/25/2013 na popisu za pretraživanje, a zatim vratiti indeks odgovarajuće vrijednosti iz indeksa stupaca.

U tom slučaju primijetit ćete da je indeks stupaca “2”, ali kao što vidite stupac u gornjoj tablici je zapravo 1, točno?

To je istina, ali što radiš s tim “ODABERITE” funkcija je manipuliranje dva polja.

Dodjeljujete referencu “indeks” brojevima rasponima podataka - dodjeljivanje datuma indeksu broj 1, a zadaci indeksu broj 2.

Dakle, kad tipkate “2” u funkciji VLookup zapravo se pozivate na indeks broj 2 u funkciji CHOOSE. Super, zar ne?

Dakle, sada VLookup koristi Datum i vraća podatke iz Zadatak stupac iako je zadatak s lijeve strane.

Sada kada znate ovaj mali sitniš, zamislite što još možete učiniti!

Ako pokušavate obaviti druge napredne zadatke pretraživanja podataka, provjerite Dannov cijeli članak o pronalaženju podataka u Excelu pomoću funkcija pretraživanja.

2. Gniježđena formula do struna Parse

Evo još jedne lude Excel formule za vas.

Može se dogoditi da podatke u Microsoft Excel uvezete iz vanjskog izvora koji se sastoji od niza ograničenih podataka.

Jednom kada unesete podatke, želite ih raščlaniti u pojedinačnim komponentama. Evo primjera imena, adrese i podataka o telefonskom broju koji su ograničeni znakom “;” lik.

Evo kako možete analizirati ove podatke pomoću Excelove formule (pogledajte možete li mentalno slijediti ovaj ludit):

Za prvo polje, za izdvajanje krajnje lijeve stavke (ime osobe), jednostavno biste koristili funkciju LEFT u formuli.

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

Evo kako funkcionira ta logika:

  • Pretražuje tekstni niz od A2
  • Pronalazi “;” simbol razgraničenja
  • Oduzme jedno za pravilno mjesto na kraju tog niza odjeljka
  • Dohvaća krajnji lijevi tekst do te točke

U ovom slučaju je krajnji lijevi tekst “Ryan”. Misija izvršena.

3. Unesena formula u Excelu

Ali što je s ostalim odjeljcima?

Možda postoje lakši načini za to, ali budući da želimo isprobati i stvoriti najluđu moguću Nested Excel formulu (koja zapravo funkcionira), koristit ćemo jedinstven pristup.

Da biste izdvojili dijelove s desne strane, potrebno je ugurati više desnih funkcija da biste prvo ugradili dio teksta “;” simbol i na njemu ponovo izvesti LEFT funkciju. Evo kako izgleda vađenje dijela adrese s brojem ulice.

"= Lijevi ((desni (A2, LEN (A2) -FIND (",”A2))), FIND ( ""(desni (A2, LEN (A2) -FIND ( "" A2)) ), 1) -1)”

Izgleda ludo, ali nije teško sastaviti se. Sve što sam učinio preuzeo sam ovu funkciju:

Pravo (A2, LEN (A2) -FIND ( "" A2))

I umetnite ga na svako mjesto u funkciji LIJEVO iznad gdje postoji “A2”.

Ovo ispravno izdvaja drugi odjeljak niza.

Svaki sljedeći odjeljak niza mora stvoriti još jedno gnijezdo. Tako da sada samo uzimaš lude “PRAVO” jednadžba koju ste stvorili za posljednji odjeljak, a zatim je dodajte u novu formu DESNO s prethodnom formulom DESNO zalijepljenom u sebe gdje god da vidite “A2”. Evo kako to izgleda.

(Desni ((desni (A2, LEN (A2) -FIND ( "" A2))) LEN ((desni (A2, LEN (A2) -FIND ( "" A2)))) - PRONAĐITE ( "" (desni (A2, LEN (A2) -FIND ( "" A2))))))

Zatim uzmete TAJU formulu i stavite je u originalnu LEFT formulu gdje god postoji “A2”.

Konačna formula savijanja uma izgleda ovako:

"= Lijevi ((desni ((desni (A2, LEN (A2) -FIND (",”A2))) LEN ((desni (A2, LEN (A2) -FIND ( "" A2))) ) -FIND ( "" (desni (A2, LEN (A2) -FIND ( "" A2)))))), FIND ( "" (desni ((desni (A2, LEN (A2) -FIND ( "" A2))) LEN ((desni (A2, LEN (A2) -FIND ( "" A2)))) - PRONAĐITE ( "" (desni (A2, LEN (A2 ) -FIND ( "" A2)))))), 1) -1)”

Ta formula ispravno izdvaja “Portland, ME 04076” izvan izvornog niza.

Da biste izdvojili sljedeći odjeljak, ponovite gornji postupak iznova.

Vaše Excel formule mogu postati vrlo petljive, ali sve što radite je rezanje i lijepljenje dugih formula u sebe, stvaranje dugih gnijezda koja zapravo djeluju.

Da, to ispunjava zahtjev za “lud”. No, budimo iskreni, postoji mnogo jednostavniji način da se jedna stvar izvrši s jednom funkcijom.

Samo odaberite stupac s ograničenim podacima, a zatim ispod Podaci stavka u izborniku, odaberite Tekst stupcima.

Tako će se pojaviti prozor u kojem možete podijeliti niz na bilo koji razgraničitelj koji želite.

U nekoliko klikova možete učiniti istu stvar kao gore navedena luda formula ... ali gdje je zabava u tome?

Postajanje ludo pomoću Microsoft Excel formula

Dakle, tu ga imate. Gore navedene formule dokazuju koliko osoba koja može dobiti više od vrhunca prilikom stvaranja Microsoft Excel formula za postizanje određenih zadataka.

Ponekad te Excelove formule zapravo nisu najlakši (ili najbolji) način za postizanje stvari. Većina programera reći će vam da to bude jednostavno, a to je istina i u Excel formulama kao i bilo što drugo.

Ako se stvarno želite ozbiljno baviti Excel-om, morat ćete pročitati vodič za početnike za upotrebu Microsoft Excel-a Vodič za početnike za Microsoft Excel Vodič za početnike za Microsoft Excel Upotrijebite ovaj početnički vodič za započinjanje svog iskustva s Microsoft Excel-om. Osnovni savjeti za proračunske tablice ovdje će vam pomoći samostalno započeti s učenjem Excela. , Ima sve što je potrebno za početak povećanja produktivnosti s Excelom. Nakon toga, svakako se konzultirajte s našim važnijim listovima programa Excel o funkcijama. Essential Microsoft Excel formule i funkcije Cheat Sheet Bitni Microsoft Excel formule i funkcije Cheat Sheet Preuzmite ovaj Checel obrazac za izradu formula da biste dobili prečac kroz najdraži svjetski program proračunskih tablica.. .




Još ne komentari

O modernoj tehnologiji, jednostavnoj i pristupačnoj.
Vaš vodič u svijetu moderne tehnologije. Naučite kako koristiti tehnologije i uređaje koji nas okružuju svaki dan i naučite kako otkriti zanimljivosti na Internetu.