15 čarobnih formula u Excelu

Excel je stroga teta. S jedne strane, nezaobilazan je alat za stvaranje izvještaja, popisa i analiza. S druge strane, informacije koje želite iz proračunske tablice možete izvući samo ako savladate tipični Excel jezik. Takve Excel-ove formule pridružuju sve vrste odnosa ćelijama kako bi vratile ciljane informacije. Evo 15 značajki koje vam mogu uštedjeti vrijeme.

Priručnik ili čarobnjak za formule?

Pretpostavljamo da ste u međuvremenu savladali osnovne formule za primjenu glavnih operacija. Ne upadajući u hocus pocus za stručnjake, pokazujemo kako se sastavljaju korisne formule. Možete ih unijeti ručno, ali možete upotrijebiti i gumb fx na traci s formulama: čarobnjak za formule. Vodit će vas da korak po korak izradite formulu.

01 Trenutno vrijeme

Jeste li netko tko redovito zaboravlja pravilno datirati svoj rad? Formula DANAS automatski popunjava dan, mjesec i godinu, dok funkcija SADA čak dodaje vrijeme minutu. Zatim upišete = DANAS () ili = SADA () . Te su funkcije korisne i na radnom listu gdje želite izračunati vrijednost na temelju trenutnog dana i vremena. Desnim klikom i izborom za Svojstva ćelije možete zatim prilagoditi prikaz datuma i vremena. Da biste ažurirali podatke o vremenu na aktivnom radnom listu, pritisnite Shift + F9; koristite F9 za ažuriranje cijele radne knjige.

02 Prebroj napunjene stanice

Ako imate skupinu ćelija s tekstom i brojevima i želite znati koliko brojeva ima u odabiru, upotrijebite funkciju COUNT . Struktura formule tada izgleda ovako: = COUNT (područje pretraživanja) . Područje u kojem bi Excel trebao pretraživati ​​pojavljuje se između zagrada. To mogu biti stanice ispod ili jedna pored druge, ali to može biti i pravokutni odabir stanica. Ako u odabiru postoje riječi, one s funkcijom COUNT ne broje se. Ako samo želite prebrojati sve stanice u kojima je nešto napisano, upotrijebite funkciju = BROJ (bez točke).

03 Koliko često?

Upotrijebite funkciju COUNTIF za brojanje određenih podataka . Pretpostavimo da ste sastavili mrežu u kojoj se pojavljuju četiri osobe, a zatim možete vidjeti koliko se često ime Herman javlja s = NANTAL.IF (područje pretraživanja; "Herman") . Unesite raspon pretraživanja između zagrada i kriterije pretraživanja priložite u navodnike.

04 Selektivni dodatak

Široko se koristi funkcija SUM za zbrajanje stanica. Pametnija varijanta je SOM.IF () . Prvo odredite područje na kojem bi Excel trebao tražiti između zagrada. Raspon pretraživanja mora biti niz susjednih ćelija. Nakon zareza odredite što treba dodati. To mogu biti brojevi ili referenca. Ako je riječ o jednadžbi, tada je morate priložiti u dvostruke navodnike. Na primjer, = SUMIF (B20: B40, ”> 50”) čini zbroj svih ćelija u ovom rasponu koje su veće od 50.

05 Dodatak pod uvjetom

Uvjet dodavanja možete još više proširiti korištenjem podataka u drugom stupcu. Primjer to jasno pokazuje. Pretpostavimo da imate podatke koji se odnose na tri grada: Amsterdam, Rotterdam i Eindhoven. Tada možete dodati brojeve Amsterdama samo s = SUMIF (raspon; "Amsterdam"; raspon dodavanja) . U ovom slučaju, formula postaje = SUMIF (C48: C54; "Amsterdam"; B48: B54) . Jednostavnim jezikom: Kada je riječ Amsterdam u rasponu od C48 do C54, Excel mora zbrojiti odgovarajuću vrijednost ćelije do nje u rasponu od B48 do B54.

06 Spajanje

Pomoću funkcije CONCATENATE spajate podatke iz različitih ćelija. Na primjer, ćelije s imenom i prezimenom s nečim poput = KONKATENIRANI TEKST (E34, ""; F34) . Dvostruki navodnici s razmakom osiguravaju razmak između imena i prezimena. Na isti način moguće je spojiti tekst s valutom. Na primjer, da biste dodali valutu euro, morate je upisati kao funkciju kao što je = KONKATENIRANI TEKST (A1, ""; B1; "" EURO (C1)) . To možete pročitati kao „spojiti ćelije A1, B1 i C1 s razmacima između njih i staviti znak eura prije trećeg elementa spajanja“.

07 Zamotajte

Excel ima nekoliko mogućnosti za zaokruživanje. Zadana zaokruživanja izgledaju = OKRUGLO (broj, broj decimalnih mjesta) . Dakle, formula = ROUND (12.5624, 1) vraća 12.6 . Napokon, tražite da zaokružite na jedan broj nakon decimalne točke. Excel će se također zaokružiti na broj decimalnih mjesta koji navedete pomoću funkcija ROUND.TO.ABOVEN i ROUND.BOTH . = ROUNDUP (12,5624; 2) dakle daje rezultat 12,57 i = ROUNDDOWN (12,5624; 2), što rezultira 12,56 . Funkcija INTEGER je zapravo i funkcija zaokruživanja, ali s tim Excel zaokružuje na najbliži cijeli broj.

08 Velika slova - mala slova

Da biste osigurali da se sve prikazuje velikim slovima u stupcu, upotrijebite funkciju GORNJE . SMALL.LETTERS formula ne suprotno. A ako želite da svaka riječ započinje velikim slovom iza kojeg slijede mala slova, koristite funkciju POČETNA SLOVA . Formula = MALA SLOVA (B4) prikazuje sadržaj ćelije B4, ali malim slovima.

09 U stanju

Kada izračun ovisi o određenim uvjetima, koristite funkciju IF . Princip ove funkcije je: = IF (uvjet, izračun ako je uvjet zadovoljen, ostali slučajevi) . Da biste formulirali uvjet, upotrijebite znakove: = jednako,nije jednako, > više od, < manje od, > = više ili jednako, < = manje ili jednako. Pretpostavimo da u organizaciji svi dobiju bonus koji je prodan za 25.000 eura ili više. Ako primite bonus, riječ "Ura" automatski će se pojaviti pored njihovog imena, ako ne, pojavit će se riječ "Nažalost". Formula koja vam treba je = IF (B2> = 2500; "Ura"; "Nažalost") .

10 najvećih - najmanjih

Za brzo pronalaženje najveće i najniže vrijednosti postoji funkcija MAX i MIN . S = MAX (B2: B37) dobivate najveću vrijednost ovih ćelija, a s = MIN (B2: B37) dobivate najnižu vrijednost u rasponu. Funkcije NAJVEĆA i MALA suptilnije su: možete zatražiti i treću po veličini ili drugu najmanju, na primjer. Najveći se nalazi s = NAJVEĆIM (B2: B37; 1); broj 1 označava najveći. S = LARGE (B2: B37,2) dobivate drugo po veličini i tako dalje. Na taj način lako možete sastaviti top 3 ili top 10.

11 Vertikalno pretraživanje

Pretpostavimo da imate dva radna lista s različitim informacijama o istim ljudima. Pomoću VLOOKUP-a dolazite do podataka s radnog lista 2 na radnom listu 1. Da bismo to olakšali, svakoj smo osobi dali jedinstveni registracijski broj na obje kartice. Također dodajte naziv rasponu na kartici 2 iz kojeg želite dobiti informacije. U ovom primjeru, u radnom listu 2, odabiremo stupce A i B, a u okvir s imenom gore lijevo upisujemo ime Adresa . U ćeliju E2 radnog lista 1 stavljamo funkciju VLOOKUP . Format je sada = VLOOKUP (A2, Popis adresa, 2, FALSE) . A2 se odnosi na ćeliju s upisnim brojem u drugom radnom listu, Popis adresaoznačava opseg pretraživanja, 2 je broj stupca u radnom listu 2 u kojem su traženi podaci. Posljednji je argument logička vrijednost gdje unosite FALSE ako želite da se pronađena vrijednost točno podudara.

12 Izbrišite razmake

Pomoću funkcije TRIM brišete nepotrebne razmake u tekstu. Ova će funkcija ostaviti razmake između riječi, ali uklonit će razmake prije ili nakon riječi. = TRIM (raspon ćelija) koristan je za tekst uvezen iz drugog programa. U nekim se verzijama Excela ova funkcija naziva SPACES.CLEAR .

13 Razmjena

Sadržaj stupaca možete prenijeti u retke ili obratno pomoću funkcije TRANSPOSE . Prvo odaberite ćelije u koje treba staviti informacije. Obavezno odaberite onoliko ćelija koliko je izvorne serije. Ovdje smo upisali godine u red 8, a tromjesečja u stupac A. Zatim upišite funkciju = TRANSPOSE i otvorite zagrade. Zatim povucite preko ćelija koje želite zamijeniti (ovdje iz ćelija B2 u E5). Zatvorite zagrade i pritisnite kombinaciju tipki Ctrl + Shift + Enter. To će stvoriti formulu niza koja se nalazi u kovrčavim zagradama.

14 Mjesečna otplata

Ako se posudite za kupnju, koliko mjesečno morate isplatiti? Pretpostavimo da posudite 25.000 eura ( B1 ) uz kamatu od 6% ( B2 ) na 5 godina ( B3 ). Formulu prikazujemo u čarobnjaku, ali možete i samo tipkati. S kamatama stavljate B2 / 12 , jer se kamata odnosi na godinu i želite znati koliko plaćate svaki mjesec. Za obročne brojeve množite B3 s 12 , jer godine morate pretvoriti u mjesece. PV okvir znači Trenutna vrijednost , koja iznosi 25.000 eura. To daje formulu = BET (B2 / 12; B3 * 12; B1) ili =KLET (6% / 12,5 * 12,25000) .

15 Lažne brojke

Kada eksperimentirate s formulama, korisno je imati lažne podatke. Funkcija SELECT BETWEEN generira slučajne podatke koji se nalaze između navedene najniže i najviše vrijednosti. Funkcija = RANDBET (50, 150) daje brojeve između 49 i 151.