Excel life hacks za one koji se bave izvještavanjem i obradom podataka

2024 Autor: Malcolm Clapton | [email protected]. Zadnja izmjena: 2024-01-13 00:53

U ovom postu, Renat Shagabutdinov, pomoćnik generalnog direktora izdavačke kuće Mann, Ivanov i Ferber, dijeli neke sjajne Excel lajf hakove. Ovi savjeti će biti korisni svima koji se bave različitim izvještavanjem, obradom podataka i kreiranjem prezentacija.

Ovaj članak sadrži jednostavne tehnike za pojednostavljenje vašeg rada u programu Excel. Posebno su korisni za one koji se bave izvještavanjem menadžmenta, pripremaju različite analitičke izvještaje na osnovu preuzimanja iz 1C i drugih izvještaja, formiraju prezentacije i dijagrame od njih za upravljanje. Ne pretvaram se da sam apsolutna novost - u ovom ili onom obliku, o ovim tehnikama se vjerovatno raspravljalo na forumima ili spominjalo u člancima.

Jednostavne alternative za VLOOKUP i HLOOKUP, ako željene vrijednosti nisu u prvoj koloni tabele: LOOKUP, INDEX + SEARCH

Funkcije VLOOKUP i HLOOKUP rade samo ako se željene vrijednosti nalaze u prvom stupcu ili redu tabele iz koje planirate da dobijete podatke.

Inače, postoje dvije opcije:

1. Koristite funkciju LOOKUP.

   Ima sljedeću sintaksu: LOOKUP (vrijednost_potraži; vektor_potraži; vektor_rezultata). Ali da bi ispravno radio, vrijednosti raspona view_vector moraju biti sortirane uzlaznim redoslijedom:

   

2. Koristite kombinaciju funkcija MATCH i INDEX.

   Funkcija MATCH vraća redni broj elementa u nizu (uz pomoć nje možete pronaći u kojem se redu tabele nalazi traženi element), a funkcija INDEX vraća element niza sa zadatim brojem (što ćemo saznati koristeći funkciju MATCH).

   

   Sintaksa funkcije:

   • SEARCH (search_value; search_array; match_type) - za naš slučaj, potreban nam je odgovarajući tip "exact match", on odgovara broju 0.

   • INDEX (niz; broj_reda; [broj_kolone]). U ovom slučaju, ne morate navesti broj kolone, jer se niz sastoji od jednog reda.

Kako brzo popuniti prazne ćelije na listi

Zadatak je popuniti ćelije u stupcu vrijednostima na vrhu (tako da se tema nalazi u svakom redu tabele, a ne samo u prvom redu bloka knjiga o toj temi):

Odaberite kolonu "Subject", kliknite na traku u grupi "Home", dugme "Pronađi i odaberi" → "Izaberi grupu ćelija" → "Prazne ćelije" i počnite unositi formulu (odnosno, stavite jednak znak) i uputite se na ćeliju na vrhu, jednostavnim klikom na strelicu prema gore na tastaturi. Nakon toga pritisnite Ctrl + Enter. Nakon toga, primljene podatke možete spremiti kao vrijednosti, jer formule više nisu potrebne:

Kako pronaći greške u formuli

Izračunavanje posebnog dijela formule

Da biste razumjeli složenu formulu (u kojoj se druge funkcije koriste kao argumenti funkcije, odnosno da su neke funkcije ugniježđene u druge) ili da biste pronašli izvor grešaka u njoj, često morate izračunati njen dio. Postoje dva laka načina:

1. Da biste izračunali dio formule direktno u traci formule, odaberite taj dio i pritisnite F9:

   

   U ovom primjeru, došlo je do problema s funkcijom SEARCH - u njoj su zamijenjeni argumenti. Važno je zapamtiti da ako ne otkažete izračunavanje dijela funkcije i pritisnete Enter, onda će izračunati dio ostati broj.

2. Kliknite na dugme Izračunaj formulu u grupi Formule na traci:

   

   U prozoru koji se pojavi možete izračunati formulu korak po korak i odrediti u kojoj fazi i u kojoj funkciji dolazi do greške (ako postoji):

   

Kako odrediti o čemu formula ovisi ili na što se odnosi

Da biste odredili od kojih ćelija zavisi formula, u grupi Formule na traci kliknite na dugme koje utiču na ćelije:

Pojavljuju se strelice koje pokazuju o čemu ovisi rezultat izračuna.

Ako je simbol označen na slici crvenom bojom prikazan, onda formula ovisi o ćelijama na drugim listovima ili u drugim knjigama:

Klikom na njega možemo vidjeti gdje se tačno nalaze ćelije ili rasponi koji utiču:

Pored dugmeta „Uticaj na ćelije“nalazi se dugme „Zavisne ćelije“koje radi na isti način: prikazuje strelice od aktivne ćelije sa formulom do ćelija koje zavise od nje.

Dugme "Ukloni strelice", koje se nalazi u istom bloku, omogućava vam da uklonite strelice na ćelije koje utiču, strelice na zavisne ćelije ili obe vrste strelica odjednom:

Kako pronaći zbir (broj, prosjek) vrijednosti ćelija iz više listova

Recimo da imate nekoliko listova istog tipa s podacima koje želite dodati, prebrojati ili obraditi na neki drugi način:

Da biste to učinili, u ćeliju u kojoj želite vidjeti rezultat, unesite standardnu formulu, na primjer SUM (SUM), i navedite naziv prvog i posljednjeg lista sa liste onih listova koje trebate obraditi u argument, odvojen dvotočkom:

Dobićete zbir ćelija sa adresom B3 iz listova "Podaci1", "Podaci2", "Podaci3":

Ovo adresiranje radi za locirane listove dosljedno … Sintaksa je sljedeća: = FUNCTION (prva_lista: zadnja_lista! Referenca raspona).

Kako automatski napraviti šablonske fraze

Koristeći osnovne principe rada s tekstom u Excelu i nekoliko jednostavnih funkcija, možete pripremiti predloške fraze za izvještaje. Nekoliko principa rada sa tekstom:

• Povezujemo tekst pomoću znaka & (možete ga zamijeniti funkcijom CONCATENATE, ali to nema puno smisla).
• Tekst se uvijek piše pod navodnicima, reference na ćelije sa tekstom su uvijek bez.
• Da biste dobili "navodnike" servisnog karaktera, koristite funkciju CHAR s argumentom 32.

Primjer kreiranja šablonske fraze pomoću formula:

rezultat:

U ovom slučaju, pored funkcije CHAR (za prikaz navodnika), koristi se i funkcija IF, koja vam omogućava da promijenite tekst u zavisnosti od toga da li postoji pozitivan trend prodaje, te funkcija TEXT, koja vam omogućava da prikažete broj u bilo kom formatu. Njegova sintaksa je opisana u nastavku:

TEKST (vrijednost; format)

Format je naveden u navodnicima, baš kao da unosite prilagođeni format u prozor Format Cells.

Složeniji tekstovi se također mogu automatizirati. U mojoj praksi je došlo do automatizacije dugih, ali rutinskih komentara na menadžment izvještavanje u formatu „INDIKATOR pao/porastao za XX u odnosu na plan, uglavnom zbog rasta/pada FACTOR-a1 za XX, rasta/pada FACTOR-a2 za YY…” sa promjenjivom listom faktora. Ako često pišete takve komentare i proces njihovog pisanja se može algoritmizirati, vrijedi jednom zagonetnuti kreirati formulu ili makro koji će vam uštedjeti barem dio posla.

Kako pohraniti podatke u svaku ćeliju nakon spajanja

Kada spajate ćelije, zadržava se samo jedna vrijednost. Excel upozorava na ovo kada pokušavate spojiti ćelije:

U skladu s tim, ako ste imali formulu ovisno o svakoj ćeliji, ona će prestati raditi nakon što ih kombinirate (# N / A greška u redovima 3-4 primjera):

Da spojite ćelije i još uvijek sačuvate podatke u svakoj od njih (možda imate formulu kao u ovom apstraktnom primjeru; možda želite spojiti ćelije, ali zadržati sve podatke za budućnost ili ih namjerno sakriti), spojite sve ćelije na listu, odaberite ih, a zatim koristite naredbu Format Painter da prenesete formatiranje u ćelije koje trebate kombinirati:

Kako napraviti pivot iz više izvora podataka

Ako trebate napraviti pivot iz nekoliko izvora podataka odjednom, morat ćete dodati "Čarobnjak za zaokretnu tabelu i grafikon" na traku ili panel za brzi pristup, koji ima takvu opciju.

To možete učiniti na sljedeći način: "Datoteka" → "Opcije" → "Traka s alatima za brzi pristup" → "Sve naredbe" → "Čarobnjak za zaokretnu tabelu i grafikon" → "Dodaj":

Nakon toga, na traci će se pojaviti odgovarajuća ikona, klikom na koju se poziva isti čarobnjak:

Kada kliknete na njega, pojavljuje se dijaloški okvir:

U njemu morate odabrati stavku "U nekoliko raspona konsolidacije" i kliknuti "Dalje". U sljedećem koraku možete odabrati "Kreiraj jedno polje stranice" ili "Kreiraj polja stranice". Ako želite samostalno smisliti naziv za svaki od izvora podataka, odaberite drugu stavku:

U sljedećem prozoru dodajte sve opsege na osnovu kojih će biti izgrađen pivot i dajte im imena:

Nakon toga, u posljednjem dijaloškom okviru, navedite gdje će se staviti izvještaj zaokretne tabele - na postojeći ili novi list:

Izvještaj zaokretne tabele je spreman. U filteru "Stranica 1" možete odabrati samo jedan od izvora podataka, ako je potrebno:

Kako izračunati broj pojavljivanja teksta A u tekstu B ("MTS SuperMTS tarifa" - dva pojavljivanja skraćenice MTS)

U ovom primjeru, stupac A sadrži nekoliko redova teksta, a naš zadatak je da saznamo koliko puta svaki od njih sadrži tekst za pretragu koji se nalazi u ćeliji E1:

Da biste riješili ovaj problem, možete koristiti složenu formulu koja se sastoji od sljedećih funkcija:

1. DLSTR (LEN) - izračunava dužinu teksta, jedini argument je tekst. Primjer: DLSTR ("mašina") = 6.
2. SUBSTITUTE - zamjenjuje određeni tekst u tekstualnom nizu drugim. Sintaksa: SUBSTITUTE (tekst; stari_tekst; novi_tekst). Primjer: ZAMJENA (“auto”; “auto”; “”) = “mobilni”.
3. UPPER - zamjenjuje sve znakove u nizu velikim slovima. Jedini argument je tekst. Primjer: UPPER (“mašina”) = “CAR”. Ova funkcija nam je potrebna za pretraživanje bez obzira na velika i mala slova. Uostalom, UPPER ("auto") = UPPER ("Mašina")

Da biste pronašli pojavljivanje određenog tekstualnog niza u drugom, potrebno je da obrišete sva njegova pojavljivanja u originalnom nizu i uporedite dužinu rezultirajućeg niza sa originalnim:

DLSTR (“Tarifa MTS Super MTS”) - DLSTR (“Tarifa Super”) = 6

I onda podijelite ovu razliku s dužinom niza koji smo tražili:

6 / DLSTR (“MTS”) = 2

Tačno dva puta je linija "MTS" uključena u originalnu.

Ostaje da ovaj algoritam napišemo jezikom formula (označimo sa "tekstom" tekst u kojem tražimo pojavljivanja, a sa "tražimo" - onaj čiji nas broj pojavljivanja zanima):

= (DLSTR (tekst) -LSTR (ZAMJENA (GORNJA (tekst); GORNJA (pretraga), ""))) / DLSTR (pretraga)

U našem primjeru formula izgleda ovako:

= (DLSTR (A2) -LSTR (ZAMJENA (GORNJI (A2), GORNJI ($ E $ 1), “”))) / DLSTR ($ E $ 1)