Excel život hakiranje za one koji su uključeni u izvještavanje i obradu podataka

<

Na tom mjestu, Renat Shagabutdinov, pomoćnik generalnog direktora izdavačke kuće Mann, Ivanov i Ferber, dijeli kul Excel životni vek. Ovi savjeti će biti korisni svima koji se bave različitim izvještavanjem, obradom podataka i izradom prezentacija.

Renat nije prvi put da je autor na Pc-članku. Prethodno smo od njega objavili odličan materijal o tome kako napraviti plan obuke: glavne knjige i online izvore, kao i korak po korak algoritam za izradu plana obuke.

Ovaj članak sadrži jednostavne trikove koji olakšavaju rad u Excelu. Posebno su korisni onima koji su uključeni u izvještavanje o upravljanju, pripremaju različite analitičke izvještaje na temelju 1C preuzimanja i drugih izvješća, njihove oblike prezentacija i grafikona za upravljanje. Ne pretvaram se da sam apsolutna novost - u jednom ili drugom obliku te se metode vjerojatno raspravljaju na forumima ili se spominju u člancima.

Jednostavne alternative CDF-u i PDW-u, ako tražene vrijednosti nisu u prvom stupcu tablice: VIEW, INDEX + MATCH

Funkcije VLOOKUP i HLOOKUP rade samo ako su vrijednosti koje tražite u prvom stupcu ili retku tablice iz koje planirate primati podatke.

U drugim slučajevima postoje dvije mogućnosti:

  1. Koristite funkciju LOOKUP.
    Ima sljedeću sintaksu: VIEW ( seek_value; view_vector; result_vector ). Ali za ispravan rad potrebno je da su vrijednosti raspona vektorskog_izgleda sortirane uzlaznim redoslijedom:
  2. Koristite kombinaciju MATCH i INDEX.
    MATCH funkcija vraća redni broj elementa u nizu (pomoću njegove pomoći možete pronaći koji redak tablice elemenat tražite), a funkcija INDEX vraća element niza s navedenim brojem (koji ćemo saznati pomoću MATCH funkcije). Sintaksa funkcija:
    • MATCH (tražena vrijednost; polje pretraživanja; vrsta podudaranja ) - za naš slučaj potreban je tip mapiranja "točno podudaranje", koji odgovara broju 0.
    • INDEX ( polje; broj_druge; [stupanj_broj] ). U tom slučaju broj stupca nije potreban, 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 se subjekt nalazi u svakom redu tablice, a ne samo u prvom redu bloka knjiga o predmetu):

Odaberite stupac "Predmeti", kliknite gumb "Pronađi i osvijetli" na početnoj skupini i odaberite grupu stanica → Isprazni stanice i počnite unositi formulu (tj. Stavite znak jednakosti) i označite ćeliju od vrha jednostavnim pritiskom na strelica gore na tipkovnici. Nakon toga pritisnite Ctrl + Enter. Nakon toga možete spremiti podatke kao vrijednosti, budući da formule više nisu potrebne:

Kako pronaći pogreške u formuli

Izračunavanje zasebnog dijela formule

Da bismo razumjeli složenu formulu (u kojoj se druge funkcije koriste kao argumenti funkcije, to jest, neke su funkcije ugniježđene u drugima) ili kako bismo pronašli pogreške u njoj, često je potrebno izračunati njegov dio. Postoje dva jednostavna načina:

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


    U ovom primjeru je postojao problem s funkcijom SEARCH - argumenti su bili pomiješani u njemu. Važno je zapamtiti da ako ne poništite izračun dijela funkcije i pritisnete Enter, izračunati dio će ostati broj.

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

    U prozoru koji se pojavi možete izračunati formulu u koracima i odrediti u kojoj fazi i u kojoj se funkciji javlja pogreška (ako postoji):

Kako odrediti na što formula ovisi ili se odnosi

Da biste odredili na koje ćelije ovisi formula, u grupi Formule na vrpci kliknite gumb "Utjecajne stanice":

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

Ako je na slici prikazan crveni simbol, formula ovisi o ćelijama koje se nalaze na drugim listovima ili u drugim knjigama:

Klikom na nju vidjet ćemo točno gdje su stanice ili opsezi koji utječu:

Pored gumba "Utjecajne stanice" nalazi se gumb "Zavisne stanice", koji radi na sličan način: prikazuje strelice iz aktivne ćelije s formulom za stanice koje ovise o njoj.

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

Kako pronaći sumu (količinu, prosjek) vrijednosti stanica iz nekoliko listova

Pretpostavimo da imate nekoliko listova iste vrste s podacima koje želite dodati, izračunati ili obraditi na neki drugi način:

Da biste to učinili, u ćeliji u kojoj želite vidjeti rezultat, unesite standardnu ​​formulu, na primjer, SUM (SUM), i navedite u argumentu, nakon dvotočke, ime prvog i posljednjeg lista s popisa tih listova koje trebate obraditi:

Dobit ćete zbroj ćelija s adresom B3 iz listova "Data1", "Data2", "Data3":

Ovo adresiranje radi za listove raspoređene u nizu . Sintaksa je sljedeća: = FUNCTION ( first_list: last_list! Link to range ).

Kako automatski izgraditi predložak fraze

Koristeći osnovne principe rada s tekstom u Excelu i nekoliko jednostavnih funkcija, možete pripremiti predloške izraza za izvješća. Neka načela rada s tekstom:

  • Spajamo tekst s znakom & (možete ga zamijeniti funkcijom CONCATENATE, ali to nema puno smisla).
  • Tekst se uvijek piše u navodnicima, reference stanica s tekstom su uvijek bez.
  • Da bismo dobili znakove usluga "citati", koristimo funkciju CHAR s argumentom 32.

Primjer izrade fraze predloška pomoću formula:

rezultat:

U ovom slučaju, osim funkcije CHAR (za prikaz navodnika), koristi se funkcija IF (IF), koja omogućuje promjenu teksta ovisno o tome postoji li pozitivan trend prodaje i funkcija TEXT, koja omogućuje prikaz broja u bilo kojem formatu. Njegova sintaksa opisana je u nastavku:

TEXT ( vrijednost; format )

Format je naveden u navodnicima, kao što biste to učinili ako biste u prozor "Format ćelija" unijeli prilagođeni format.

Možete automatizirati složenije tekstove. U mojoj praksi postojala je automatizacija dugih, ali rutinskih komentara na izvješća uprave u formatu “INDIKATOR je pao / porastao za XX u odnosu na plan uglavnom zbog povećanja / smanjenja FACTOR1 po XX, rasta / smanjenja FACTOR2 po YY ...” s promjenjivim popisom čimbenika. Ako takve komentare često pišete i proces njihovog pisanja može biti algoritamiziran - vrijedi jednom stvoriti formulu ili makronaredbu koja će vam uštedjeti barem dio posla.

Kako spremiti podatke u svaku ćeliju nakon spajanja

Pri spajanju ćelija sprema se samo jedna vrijednost. Excel upozorava na to prilikom pokušaja spajanja ćelija:

Prema tome, ako ste imali formulu koja ovisi o svakoj ćeliji, prestat će raditi nakon spajanja (pogreška # N / D u redovima 3-4 primjera):

Spajanje stanica i istovremeno spremanje podataka u svakoj od njih (možda imate formulu, kao u ovom apstraktnom primjeru; možda želite spojiti ćelije, ali spremiti sve podatke za budućnost ili ih namjerno sakriti), spojiti sve ćelije na listu, odaberite ih, a zatim pomoću naredbe "Oblik uzorka" prenesite oblikovanje na one ćelije koje trebate spojiti:

Kako napraviti sažetak nekoliko podataka sv

Ako trebate izraditi sažetak od nekoliko podataka odjednom, morat ćete dodati čarobnjak za tablice i tablice za okretanje na vrpcu ili ploči za brzi pristup, koja ima tu opciju.

To možete učiniti na sljedeći način: “Datoteka” → “Parametri” → “Ploča za brzi pristup” → “Sve naredbe” → “Čarobnjak za okretne tablice i grafikone” → “Dodaj”:

Nakon toga na vrpci će se pojaviti odgovarajuća ikona, klikom na koju ćete nazvati:

Kada ga kliknete, pojavit će se dijaloški okvir:

U njemu morate odabrati "U nekoliko opsega konsolidacije" i kliknite "Dalje". U sljedećem odlomku možete odabrati "Stvori polje jedne stranice" ili "Stvori polja stranice". Ako želite samostalno smisliti ime za svaki od podataka s - odaberite drugu stavku:

U sljedećem prozoru dodajte sve raspone na kojima će se sažeti sažetak i navedite im imena:

Nakon toga, u zadnjem dijaloškom okviru odredite gdje će biti postavljena tablica sažetka - na postojećem ili novom listu:

Izvješće stožerne tablice je spremno. U filtru Page 1 možete odabrati samo jedan podatak s, ako je potrebno:

Kako izračunati broj pojavljivanja teksta A u tekstu B ("MTS SuperMTS tarifa" - dvije pojave kratice MTS)

U ovom primjeru postoji nekoliko redaka teksta u stupcu A, a naš zadatak je da saznamo koliko puta u svakoj od njih je traženi tekst 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. SUBSTITUTE - zamjenjuje određeni tekst u retku s drugim. Sintaksa: ZAMJENA ( tekst; star_text; novi_tekst ). Primjer: SUB ("automobil"; "auto"; "”) = "mobilni".
  3. GORNJI (GORNJI) - zamjenjuje sve znakove u nizu s velikim slovima. Jedini argument je tekst. Primjer: GORNJI ("stroj") = "STROJ". Trebat ćemo ovu funkciju za pretragu bez obzira na velika i mala slova. Uostalom, GORNJI ("stroj") = GORNJI ("Stroj")

Da biste pronašli pojavu određenog retka teksta u drugom, morate ukloniti sve njegove pojave u izvorniku i usporediti duljinu rezultirajućeg niza s originalom:

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

Zatim podijelite tu razliku prema duljini niza koji smo tražili:

6 / DLSTR ("MTS") = 2

To je linija "MTS" koja dolazi u dva navrata.

Ostaje napisati taj algoritam na jeziku formula (tekstom označavamo tekst u kojem tražimo događaje, a “željeni” je onaj čiji je broj pojava koje nas zanimaju):

= (DLSTR ( text ) -DLSTR (FIT (PRESP ( tekst ); PROFIT ( obavezno ); ""))) / DLSTR ( obavezno )

U našem primjeru formula je sljedeća:

= (DLSTR (A2) -DLSTR (FIT (PROPISN (A2); PROPISN ($ E $ 1); “))) / DLSTR ($ E $ 1)

<

Popularni Postovi