Excel life hacks za one koji su uključeni u izvješćivanje i obradu podataka

2024 Autor: Malcolm Clapton | [email protected]. Zadnja promjena: 2024-01-13 01:47

U ovom postu Renat Shagabutdinov, pomoćnik generalnog direktora izdavačke kuće Mann, Ivanov i Ferber, dijeli neke cool Excelove životne hakove. Ovi savjeti bit će korisni svima koji se bave raznim izvješćima, obradom podataka i kreiranjem prezentacija.

Ovaj članak sadrži jednostavne tehnike za pojednostavljenje vašeg rada u Excelu. Posebno su korisni za one koji se bave izvještavanjem menadžmenta, pripremaju razna analitička izvješća na temelju preuzimanja iz 1C i drugih izvješća, oblikuju prezentacije i dijagrame od njih za upravljanje. Ne pretvaram se da sam apsolutna novost - u ovom ili onom obliku, o tim se tehnikama vjerojatno raspravljalo na forumima ili spominjalo u člancima.

Jednostavne alternative za VLOOKUP i HLOOKUP, ako željene vrijednosti nisu u prvom stupcu tablice: LOOKUP, INDEX + SEARCH

Funkcije VLOOKUP i HLOOKUP rade samo ako se željene vrijednosti nalaze u prvom stupcu ili retku tablice iz koje planirate dobiti 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 razvrstane uzlaznim redoslijedom:

   

2. Koristite kombinaciju funkcija MATCH i INDEX.

   Funkcija MATCH vraća redni broj elementa u nizu (uz njegovu pomoć možete pronaći u kojem se retku tablice nalazi traženi element), a funkcija INDEX vraća element niza zadanog broja (što ćemo saznati pomoću funkcije MATCH).

   

   Sintaksa funkcije:

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

   • INDEX (niz; broj_retka; [broj_stupca]). U tom slučaju ne morate navesti broj stupca, jer se niz sastoji od jednog retka.

Kako brzo popuniti prazne ćelije na popisu

Zadatak je popuniti ćelije u stupcu vrijednostima na vrhu (tako da je tema u svakom retku tablice, a ne samo u prvom redu bloka knjiga na temu):

Odaberite stupac "Predmet", kliknite na vrpcu u grupi "Početna", gumb "Pronađi i odaberi" → "Odaberi 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 tipkovnici. Nakon toga pritisnite Ctrl + Enter. Nakon toga možete spremiti primljene podatke kao vrijednosti, jer formule više nisu potrebne:

Kako pronaći greške u formuli

Izračunavanje zasebnog 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 u njoj pronašli izvor pogrešaka, često morate izračunati njezin dio. Postoje dva laka načina:

1. Da biste izračunali dio formule izravno 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 poništite izračun dijela funkcije i pritisnete Enter, tada će izračunati dio ostati broj.

2. Kliknite gumb Izračunaj formulu u grupi Formule na vrpci:

   

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

   

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

Da biste odredili o kojim ćelijama ovisi formula, u grupi Formule na vrpci kliknite gumb koji utječe na ćelije:

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

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

Klikom na njega možemo vidjeti gdje se točno nalaze utjecajne ćelije ili rasponi:

Uz gumb "Utjecaj na ćelije" nalazi se gumb "Ovisne ćelije" koji radi na isti način: prikazuje strelice od aktivne ćelije s formulom do ćelija koje ovise o njoj.

Gumb "Ukloni strelice", koji se nalazi u istom bloku, omogućuje vam uklanjanje strelica na ćelije koje utječu, strelice na ovisne ćelije ili obje vrste strelica odjednom:

Kako pronaći zbroj (broj, prosjek) vrijednosti ćelija s više listova

Recimo da imate nekoliko listova iste vrste 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 s popisa onih listova koje trebate obraditi argument, odvojen dvotočkom:

Dobit ćete zbroj ćelija s adresom B3 iz listova "Podaci1", "Podaci2", "Podaci3":

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

Kako automatski izraditi predloške fraze

Koristeći osnovna načela rada s tekstom u Excelu i nekoliko jednostavnih funkcija, možete pripremiti predloške fraze za izvješća. Nekoliko principa rada s tekstom:

• Povezujemo tekst pomoću znaka & (možete ga zamijeniti funkcijom CONCATENATE, ali to nema puno smisla).
• Tekst je uvijek napisan u navodnicima, reference na ćelije s tekstom su uvijek bez.
• Da biste dobili uslužni znak "navodnike", koristite funkciju CHAR s argumentom 32.

Primjer stvaranja fraze predloška pomoću formula:

Proizlaziti:

U ovom slučaju, uz funkciju CHAR (za prikaz navodnika), koristi se i funkcija IF, koja omogućuje promjenu teksta ovisno o tome postoji li pozitivan trend prodaje, te funkcija TEXT koja vam omogućuje prikaz broj u bilo kojem 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 također se mogu automatizirati. U mojoj praksi došlo je do automatizacije dugih, ali rutinskih komentara na izvještavanje menadžmenta u formatu „INDIKATOR je pao/porastao za XX u odnosu na plan, uglavnom zbog rasta/pada FACTOR-a1 za XX, rasta/pada FACTOR-a2 za YY …” s promjenjivim popisom čimbenika. Ako često pišete takve komentare i proces njihovog pisanja može se algoritmizirati, vrijedi jednom zagonetno stvoriti formulu ili makronaredbu koja ć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šava spojiti ćelije:

Sukladno tome, ako ste imali formulu ovisno o svakoj ćeliji, ona će prestati raditi nakon što ih kombinirate (# N/A pogreška u redcima 3-4 primjera):

Da biste spojili ćelije i još uvijek sačuvali 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 upotrijebite naredbu Format Painter za prijenos oblikovanja u ćelije koje trebate kombinirati:

Kako izgraditi pivot iz više izvora podataka

Ako trebate izraditi pivot iz nekoliko izvora podataka odjednom, morat ćete dodati "Čarobnjak za zaokretne tablice i grafikone" na vrpcu ili ploču za brzi pristup koja ima takvu opciju.

To možete učiniti na sljedeći način: "Datoteka" → "Opcije" → "Alatna traka za brzi pristup" → "Sve naredbe" → "Čarobnjak za zaokretnu tablicu i grafikon" → "Dodaj":

Nakon toga, na vrpci ć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 "Stvori 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 raspone na temelju kojih će se pivot graditi i dajte im imena:

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

Izvješće zaokretne tablice je spremno. U filtru "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 kratice MTS)

U ovom primjeru, stupac A sadrži nekoliko redaka teksta, a naš je zadatak otkriti koliko puta svaki od njih sadrži tekst za pretraživanje 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 duljinu teksta, jedini argument je tekst. Primjer: DLSTR ("stroj") = 6.
2. ZAMJENA - 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 (“stroj”) = “CAR”. Ova nam je funkcija potrebna za pretraživanje bez obzira na velika i mala slova. Uostalom, UPPER ("auto") = UPPER ("stroj")

Da biste pronašli pojavljivanje određenog tekstualnog niza u drugom, morate izbrisati sva njegova pojavljivanja u izvornom nizu i usporediti duljinu rezultirajućeg niza s izvornim:

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

Zatim podijelite ovu razliku s duljinom niza koji smo tražili:

6 / DLSTR (“MTS”) = 2

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

Ostaje napisati ovaj algoritam jezikom formula (označimo s "tekstom" tekst u kojem tražimo pojavljivanja, a s "tražimo" - onaj čiji nas broj pojavljivanja zanima):

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

U našem primjeru formula izgleda ovako:

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