Trčanje ukupno (naziva se i kumulativni zbroj) se često koristi u mnogim situacijama. To je metrika koja vam govori koji je zbroj dosadašnjih vrijednosti.
Na primjer, ako imate mjesečne podatke o prodaji, tada će vam ukupni iznos reći koliko je prodaje obavljeno do određenog dana od prvog dana u mjesecu.
Postoje i neke druge situacije u kojima se često koristi tekući iznos, kao što je izračun stanja gotovine na bankovnim izvacima/glavnoj knjizi, brojanje kalorija u vašem obroku itd.
U programu Microsoft Excel postoji više različitih načina za izračunavanje ukupnih iznosa.
Metoda koju odaberete ovisit će i o strukturi vaših podataka.
Na primjer, ako imate jednostavne tablične podatke, tada možete upotrijebiti jednostavnu formulu SUM, ali ako imate Excel tablicu, onda je najbolje koristiti strukturirane reference. Za to možete upotrijebiti i Power Query.
U ovom ću vodiču pokriti sve ove različite metode izračunajte ukupne iznose u Excelu.
Pa krenimo!
Izračunavanje tekućeg zbroja s tabličnim podacima
Ako imate tablične podatke (tj. Tablicu u Excelu koja nije pretvorena u Excel tablicu), možete koristiti neke jednostavne formule za izračun tekućih zbrojeva.
Korištenje operatora zbrajanja
Pretpostavimo da imate datumske podatke o prodaji i želite izračunati ukupni iznos u stupcu C.
U nastavku su navedeni koraci za to.
Korak 1 - Unesite u ćeliju C2, koja je prva ćelija u kojoj želite tekući zbroj
= B2
Ovo će jednostavno dobiti iste prodajne vrijednosti u ćeliji B2.
Korak 2 - U ćeliju C3 unesite donju formulu:
= C2+B3
Korak 3 - Primijenite formulu na cijeli stupac. Pomoću ručice Fill možete je odabrati i povući ili jednostavno kopirati i zalijepiti ćeliju C3 u sve preostale ćelije (što bi automatski prilagodilo referencu i dalo pravi rezultat).
To će vam dati rezultat kao što je prikazano u nastavku.
To je doista jednostavna metoda i dobro funkcionira u većini slučajeva.
Logika je jednostavna - svaka ćelija preuzima vrijednost iznad sebe (što je kumulativni zbroj do prethodnog datuma) i dodaje vrijednost u ćeliji koja joj je susjedna (što je prodajna vrijednost za taj dan).
Postoji samo jedan nedostatak - u slučaju da izbrišete neki od postojećih redaka u ovom skupu podataka, sve ćelije ispod koje bi vratile referentnu pogrešku (#REF!)
Ako je to moguće s vašim skupom podataka, upotrijebite sljedeću metodu koja koristi formulu SUM
Korištenje SUM -a s djelomično zaključanom referencom ćelije
Pretpostavimo da imate datumske podatke o prodaji i želite izračunati ukupni iznos u stupcu C.
Ispod je formula ZBOR koja će vam dati ukupni zbroj.
= ZBIR ($ B $ 2: B2)
Dopustite mi da objasnim kako ova formula radi.
U gornjoj formuli SUM upotrijebio sam referencu za dodavanje kao $ B $ 2: B2
- $ B $ 2 - ovo je apsolutna referenca, što znači da kada kopiram istu formulu u donje ćelije, ta se referenca neće promijeniti. Dakle, prilikom kopiranja formule u donju ćeliju, formula bi se promijenila u SUM ($ B $ 2: B3)
- B2 - ovo je drugi dio reference koji je relativna referenca, što znači da bi se to prilagodilo dok kopiram formulu prema dolje ili udesno. Dakle, pri kopiranju formule u donju ćeliju ta će vrijednost postati B3
Također pročitajte: Apsolutne, relativne i mješovite ćelijske reference u Excelu
Odlična stvar u vezi s ovom metodom je ta što bi se u slučaju da izbrišete bilo koji redak u skupu podataka ta formula prilagodila i još uvijek vam dala ispravne ukupne zbrojeve.
Izračunavanje tekućeg zbroja u tablici programa Excel
Prilikom rada s tabličnim podacima u Excelu dobra je ideja pretvoriti ih u Excel tablicu. To uvelike olakšava upravljanje podacima, a omogućuje i jednostavnu upotrebu alata poput Power Query i Power Pivot.
Rad u Excel tablicama donosi prednosti kao što su strukturirane reference (što olakšava pozivanje na podatke u tablici i njihovu upotrebu u formulama) te automatsko prilagođavanje referenci u slučaju da dodate ili izbrišete podatke iz tablice.
Iako još uvijek možete koristiti gornju formulu koju sam vam pokazao u Excel tablici, dopustite mi da vam pokažem neke bolje metode za to.
Pretpostavimo da imate tablicu programa Excel kao što je prikazano u nastavku i želite izračunati ukupni iznos u stupcu C.
Ispod je formula koja će to učiniti:
= SUM (Podaci o prodaji [[#zaglavlja], [Rasprodaja]]: [@Rasprodaja])
Gornja formula može izgledati dugo, ali ne morate je sami napisati. ono što vidite u formuli zbroja naziva se strukturirane reference, što je Excelov učinkovit način upućivanja na određene podatkovne točke u Excelovoj tablici.
Na primjer, SalesData [[#Headers], [Sale]] odnosi se na zaglavlje Sale u tablici SalesData (SalesData je naziv tablice Excel koju sam dao prilikom izrade tablice)
A [@Prodaja] odnosi se na vrijednost u ćeliji u istom retku u stupcu Prodaja.
Objasnio sam ovo ovdje radi vašeg razumijevanja, ali čak i ako ne znate ništa o strukturiranim referencama, i dalje možete jednostavno stvoriti ovu formulu.
U nastavku su navedeni koraci za to:
- U ćeliju C2 unesite = SUM (
- Odaberite ćeliju B1, koja je zaglavlje stupca koji ima prodajnu vrijednost. Možete koristiti miš ili tipke sa strelicama. Primijetit ćete da Excel automatski unosi strukturiranu referencu za tu ćeliju
- Dodajte: (simbol dvotočke)
- Odaberite ćeliju B2. Excel bi ponovno automatski umetnuo strukturiranu referencu za ćeliju
- Zatvorite zagradu i pritisnite enter
Primijetit ćete i da ne morate kopirati formulu u cijeli stupac, to umjesto vas automatski čini Excel tablica.
Još jedna sjajna stvar u vezi s ovom metodom je ta što bi, u slučaju da dodate novi zapis u ovaj skup podataka, tablica programa Excel automatski izračunala ukupni iznos za sve nove zapise.
Iako smo u formulu uključili zaglavlje stupca, imajte na umu da će formula zanemariti tekst zaglavlja i uzeti u obzir samo podatke u stupcu
Izračunavanje ukupnog trčanja koristeći Power Query
Power Query nevjerojatan je alat kada se radi o povezivanju s bazama podataka, izdvajanju podataka iz više izvora i njihovoj transformaciji prije stavljanja u Excel.
Ako već radite s Power Queryjem, bilo bi učinkovitije dodati tekuće zbrojeve dok transformirate podatke unutar samog uređivača Power Query (umjesto da prvo dobijete podatke u Excelu, a zatim dodate tekuće zbrojeve pomoću bilo kojeg od gore navedenih gore opisane metode).
Iako u Power Queryju nema ugrađene značajke za dodavanje tekućih zbrojeva (što bih volio da postoji), to možete učiniti i dalje pomoću jednostavne formule.
Pretpostavimo da imate Excel tablicu kao što je prikazano u nastavku i želite ovim podacima dodati tekuće zbrojeve:
U nastavku su navedeni koraci za to:
- Odaberite bilo koju ćeliju u tablici programa Excel
- Kliknite na Podaci
- Na kartici Get & Transform kliknite ikonu from Table/Range. Ovo će otvoriti tablicu u uređivaču programa Power Query
- [Izborno] U slučaju da stupac Datum već nije sortiran, kliknite ikonu filtra u stupcu Datum, a zatim kliknite Poredaj uzlazno
- Kliknite karticu Dodaj stupac u uređivaču programa Power Query
- U grupi Općenito kliknite padajući izbornik Stupac indeksa (nemojte kliknuti na ikonu Stupac indeksa, već na malu crnu nagnutu strelicu pokraj nje za prikaz više opcija)
- Kliknite opciju ‘Od 1’. Time ćete dodati novi stupac indeksa koji će početi od jedan i unijeti brojeve povećavajući za 1 u cijeli stupac
- Kliknite ikonu "Prilagođeni stupac" (koja se također nalazi na kartici Dodaj stupac)
- U dijaloški okvir prilagođenog stupca koji se otvori unesite naziv za novi stupac. u ovom primjeru koristit ću naziv 'Running Total'
- U polje Formula prilagođenog stupca unesite donju formulu: List.Sum (List.Range (#”Dodani indeks” [Prodaja], 0, [Indeks]))
- Provjerite postoji li potvrdni okvir pri dnu dijaloškog okvira koji kaže - "Nisu otkrivene sintaksne pogreške"
- Pritisnite U redu. To bi dodalo novi stupac ukupnog broja prikaza
- Uklonite stupac indeksa
- Kliknite karticu Datoteka, a zatim kliknite "Zatvori i učitaj"
Gore navedeni koraci umetnuli bi novi list u vašu radnu knjigu s tablicom koja sadrži tekuće zbrojeve.
Ako mislite da je ovo previše koraka u usporedbi s prethodnim metodama korištenja jednostavnih formula, u pravu ste.
Ako već imate skup podataka i sve što trebate učiniti je dodati ukupne iznose, bolje je da ne koristite Power Query.
Korištenje programa Power Query ima smisla ako morate izdvojiti podatke iz baze podataka ili kombinirati podatke iz više različitih radnih knjiga, a pritom mu dodati i ukupne iznose.
Također, nakon što napravite ovu automatizaciju pomoću programa Power Query, sljedeći put kada se vaš skup podataka promijeni, ne morate to ponoviti, možete jednostavno osvježiti upit i to će vam dati rezultat na temelju novog skupa podataka.
Kako ovo radi?
Dopustite mi da brzo objasnim što se događa u ovoj metodi.
Prvo što radimo u uređivaču programa Power Query je umetnuti indeksni stupac koji počinje od jedan i povećava se za jedan dok se spušta niz ćelije.
To činimo jer moramo koristiti ovaj stupac dok izračunavamo tekući zbroj u drugom stupcu koji smo umetnuli u sljedećem koraku.
Zatim umetnemo prilagođeni stupac i upotrijebimo donju formulu
List.Sum (List.Range (#"Dodani indeks" [Prodaja], 0, [Indeks]))
Ovo je formula List.Sum koja bi vam dala zbroj raspona koji je u njoj naveden.
I taj raspon je naveden pomoću funkcije List.Range.
Funkcija List.Range daje navedeni raspon u stupcu prodaje kao izlaz, a taj raspon se mijenja na temelju vrijednosti indeksa. Na primjer, za prvi zapis raspon bi jednostavno bio prva prodajna vrijednost. Kako se spuštate po ćelijama, ovaj raspon će se širiti.
Dakle, za prvu ćeliju. List.Sum bi vam dao samo zbroj prve prodajne vrijednosti, a za drugu ćeliju dati zbroj za prve dvije prodajne vrijednosti i tako dalje.
Iako ova metoda dobro funkcionira, postaje jako spora s velikim skupovima podataka - tisućama redaka. Ako imate posla s velikim skupom podataka i želite mu dodati ukupne iznose, pogledajte ovaj vodič koji prikazuje druge metode koje su brže.Izračunavanje tekućeg zbroja na temelju kriterija
Do sada smo vidjeli primjere u kojima smo izračunali ukupni iznos za sve vrijednosti u stupcu.
No, mogli bi postojati slučajevi u kojima želite izračunati ukupni iznos za određene zapise.
Na primjer, ispod imam skup podataka i želim izračunati ukupni rad za pisače i skenere odvojeno u dva različita stupca.
To se može učiniti pomoću formule SUMIF koja izračunava ukupni ukupni rad, pritom pazeći da je navedeni uvjet ispunjen.
Ispod je formula koja će to učiniti za stupce pisača:
= SUMIF ($ C $ 2: C2, $ D $ 1, $ B $ 2: B2)
Slično, za izračun tekućeg zbroja skenera, upotrijebite donju formulu:
= SUMIF ($ C $ 2: C2, $ E $ 1, $ B $ 2: B2)
U gornjim formulama koristio sam SUMIF, koji bi mi dao zbroj u rasponu kada su zadovoljeni navedeni kriteriji.
Formula ima tri argumenta:
- domet: ovo je raspon kriterija koji bi se provjerio prema navedenim kriterijima
- kriterijima: ovo su kriteriji koji bi se provjerili samo ako je ovaj kriterij zadovoljen tada bi se dodale vrijednosti u trećem argumentu, a to je raspon zbroja
- [sum_range]: ovo je raspon zbroja iz kojeg bi se vrijednosti dodale ako su kriteriji zadovoljeni
Također, u domet i zbroj_područje argument, zaključao sam drugi dio reference, tako da bi se, dok se spuštamo niz ćelije, raspon stalno povećavao. To nam omogućuje samo razmatranje i dodavanje vrijednosti do tog raspona (dakle tekući zbrojevi).
U ovoj formuli sam koristio stupac zaglavlja (pisač i skener) kao kriterij. također možete tvrdo kodirati kriterije ako zaglavlja stupaca nisu potpuno ista kao tekst kriterija.
U slučaju da imate više uvjeta koje morate provjeriti, tada možete koristiti formulu SUMIFS.
Pokretanje zbroja u zaokretnim tablicama
Ako želite dodati ukupne iznose u rezultat izvedene tablice, to možete lako učiniti pomoću ugrađene funkcije u zaokretnim tablicama.
Pretpostavimo da imate zaokretnu tablicu kao što je prikazano u nastavku gdje u jednom stupcu imam datum, a u drugom stupcu vrijednost prodaje.
U nastavku su navedeni koraci za dodavanje dodatnog stupca koji će prikazivati ukupnu prodaju po datumu:
- Povucite polje Prodaja i stavite ga u područje Vrijednost.
- To će dodati još jedan stupac s prodajnim vrijednostima
- Kliknite na opciju Zbir prodaje2 u području Vrijednost
- Kliknite opciju ‘Postavke polja vrijednosti’
- U dijaloškom okviru Postavke polja vrijednosti promijenite prilagođeni naziv u "Pokrenuti zbrojevi"
- Kliknite karticu "Prikaži vrijednost kao"
- Na padajućem izborniku Prikaži vrijednost kao odaberite opciju "Trčanje ukupno u"
- U opcijama polja Base provjerite je li odabran Datum
- Pritisnite U redu
Gore navedeni koraci promijenili bi drugi stupac prodaje u stupac Running Total.
Dakle, ovo su neki od načina na koje možete koristiti za izračun tekućeg zbroja u Excelu. Ako imate podatke u tabličnom obliku, možete koristiti jednostavne formule, a ako imate tablicu programa Excel, tada možete koristiti formule koje koriste strukturirane reference.
Također sam opisao kako izračunati ukupni rad pomoću Power Queryja i u zaokretnim tablicama.
Nadam se da vam je ovaj vodič bio koristan.
