sadržaj
Izrada konačnih zbroja u Excel tabeli je lako. Stoga mnoge korisnike zanima na koje načine je moguće sumirati one srednje u okviru iste tabele. Naravno, sve možete učiniti ručno, ali budući da je Excel program za automatizaciju obrade podataka, pogledajmo bliže metode pomoću kojih ovaj zadatak možete povjeriti računaru.
Zahtjevi za tabele za sumiranje međuzbirova
Kao i svaka automatizacija, međuzbroj ima svoja ograničenja. Stoga morate biti sigurni da tabela ispunjava određene zahtjeve prije nego što pokušate da joj dodate međuzbroje. Evo kratke, ali iscrpne liste:
- Svaka ćelija mora sadržavati informacije. Nijedan ne bi trebao biti prazan.
- Zabranjeno je kreirati zaglavlje koje se sastoji od nekoliko redova. Dozvoljen je samo jedan. Također je važno osigurati da se zaglavlje nalazi u prvom redu tabele.
- Tabela ne mora biti oblikovana kao takva. Jednostavnim riječima, trebao bi postojati normalan raspon.
Pripremna faza je vrlo važna prije bilo kakvog posla. Stoga se prvo morate uvjeriti da raspon ispunjava sve ove uslove.
Proces izračunavanja međuzbirova u Excel-u
Uzmimo brzi primjer da shvatimo kako funkcionišu podzbroji. Recimo da ste prodavač i pišete izvještaj koji opisuje broj prodaje određene vrste proizvoda.
Sada je naš zadatak da odredimo prihod za određene kategorije prodanog namještaja. Naravno, to možete učiniti i sa filterom. Tada nam je dovoljno da postavimo kriterijum po kojem će se informacije birati, ali konačne vrednosti će ipak morati da se odredi ručno. To uzrokuje mnogo neugodnosti ako u ćelijama ima puno podataka.
Postoji još jedan način za postizanje ovog zadatka - posebna komanda, koja se zove "Srednji zbrojevi".
Dakle, vršimo provjeru raspona da vidimo da li ispunjavaju gore opisane kriterije. Uvjerili smo se da je tabela jednostavan raspon a ne pametna tablica, nazivi stupaca su opisani u prvoj koloni, ćelije sadrže vrijednosti istog formata i da nema praznih ćelija.
Nakon toga odmah počinjemo sa radom:
- Fokusirajući se na sadržaj ćelija koje pripadaju prvoj koloni, morate biti sigurni da su podaci istog tipa zajedno.
2 - Nakon toga vršimo slučajni odabir bilo koje ćelije. Zatim pogledajte traku. Kartica koja nas zanima zove se „Podaci“. Tu je i dugme koje nam je potrebno, a nalazi se u grupi „Struktura“.
3 - Nakon izvođenja ovih jednostavnih radnji, ispred nas će se pojaviti prozor u kojem se postavljaju parametri rezultata. Ima sljedeća polja:
- Sa svakom promjenom na snimku ekrana, ova stavka je “Ime”.
- Operacija. Ovdje morate direktno odabrati funkciju koja je najprikladnija za trenutni zadatak. Kod nas je to – “Sum”.
- Dodajte ukupne iznose po. Ovdje morate navesti kolone za koje želite kreirati međuzbrojeve.
- Kliknite na dugme OK da potvrdite promene i zatvorite dijaloški okvir.
Nakon završetka svih operacija, tabela će izgledati ovako.
Moguće je sažimanje redova u podgrupama. Da biste to učinili, trebate kliknuti na minuse, koji se, kao što vidimo na snimku zaslona, nalaze na lijevoj strani ekrana. Dalje u našoj tabeli biće samo srednji zbrojevi.
Svaki put kada se ime promijeni u koloni, ona će napustiti određenu grupu, a vrijednost međuprodukta će se ponovo izračunati.
Postoji mnogo dodatnih postavki za zbrojeve. Kako bi bili odvojeni od glavne tabele prelomom stranice, morate kliknuti na potvrdni okvir „Kraj stranice između grupa“.
Također je moguće promijeniti lokaciju međuzbirova. Moguće ih je postaviti iznad grupe, a ne ispod nje. Da biste to učinili, poništite izbor u polju za potvrdu pored "Ukupni podaci pod podacima".
Možete primijeniti više od jedne agregatne funkcije u međuzbrojima. Već smo dodijelili „Iznos“, ali možete dodati prosječnu prodaju određenih proizvoda.
Da biste to učinili, potrebno je da se vratite na meni „Subtotals“. Zatim morate pronaći stavku "Zamijeni struju", a zatim u polju "Operacija" kliknite na funkciju "Prosjek".
SUBTOTAL funkcija
Kao rezultat koji vraća funkcija SUBTOTALS služi, kao što ime implicira, međurezultat. Uz njegovu pomoć moguće je fleksibilnije kreiranje međuzbirova.
Sintaksa funkcije
Sintaksa je vrlo jednostavna. Prvo morate napisati broj funkcije koja se koristi za sumiranje. To su brojevi u rasponu od jedan do jedanaest. Evo male liste brojeva koji odgovaraju određenoj funkciji.
Slijede referentni brojevi: 1, 2 i tako dalje. Bilježenje prve veze je obavezno jer ukazuje na prvi raspon ukupnih vrijednosti. Kada koristite ovu funkciju, morate uzeti u obzir sljedeće točke:
- Također stvara rezultat za skrivene redove. Stoga, ako ih ima, ovaj faktor se mora uzeti u obzir.
- Ako određeni redovi nisu dodani filteru, program ih preskače.
- Brojanje se vrši isključivo u kolonama. Stoga, ako postoji želja da se sumira horizontalna tablica, to neće uspjeti.
Uzmimo pravi primjer kako pravilno koristiti ovu funkciju.
- Dodajte poseban red. Dajemo mu ime. Neka to bude, kao opcija, “zbir odabranih vrijednosti”.
- Aktiviramo filtriranje informacija tako da u opsegu ostaju samo oni brojevi koji se nalaze u istom redu sa tekstom „Amadis Dining Group“.
- Zatim unesite formulu u ćeliju B2 (ili bilo koju ćeliju u kojoj možete prikazati međurezultat) = PODZBORI (9; B3: B15).
Kao što vidite, značajna prednost ove formule je što se može koristiti gotovo svugdje. Odnosno, tabela možda neće biti tako strogo standardizovana kao u prethodnoj metodi.
Ako bi bilo potrebno izračunati prosječnu vrijednost, onda bi formula izgledala ovako.
=SUBTOTAL(1,B3:B15)
U slučaju pronalaženja maksimalne vrijednosti, funkcija bi bila ovakva.
=SUBTOTAL(4,B3:B15)
Jednostavnim riječima, korisnik može koristiti ovu funkciju vrlo fleksibilno.
SUBTOTAL() funkcija i AutoFilter
Zamislimo da imamo tabelu sa brojevima artikala i njihovim cijenama.
Koristimo funkciju AutoFilter da prikažemo samo redove koji opisuju proizvod broj 1. A onda koristimo ovu funkciju da saznamo zbir proizvoda označenih brojem 1. Prema tome, potrebna nam je funkcija 9 ili 109.
Ovo je još jedna karakteristična prednost ove karakteristike. Ona odmah razumije da li je red sakriven autofilterom ili ne. Ako da, tada se uzimaju u obzir samo vrijednosti prikazane u tabeli. Zgodno, zar ne?
Funkcija SUBTOTAL() i skriveni redovi
Recimo da imamo istu tabelu na koju nije primijenjen automatski filter. Pokušat ćemo jednostavno sakriti redove pod nazivom “Proizvod 2” na standardni način. Odnosno, koristite ili kontekstni meni ili idite putem Početna – Ćelije – Format – Sakrij.
Ovdje korisnik treba osigurati da se koristi kod koji odgovara skrivenim linijama. Pojednostavljeno rečeno, da biste tražili međuzbroj, morate unijeti kod 109, a ne 9. Ova potonja opcija nije osjetljiva na skrivene linije. Stoga, kada ručno filtrirate podatke, trebate koristiti kodove koji počinju od 101 i završavaju sa 111. Oznake odgovaraju gore opisanim, samo im trebate dodati 100.
Funkcija SUBTOTAL() i alat Excel Subtotal
Kreirajmo sada drugu tabelu i napravimo međuzbrojeve koristeći posebnu Excel opciju (ne statističku funkciju, već poseban alat opisan na početku ovog članka).
Sada filtrirajmo tabelu, ostavljajući samo Product1 u njoj.
Ako koristite funkciju koja se koristi u formulama, tada se uklanjaju svi redovi koji su uklonjeni, bez obzira koji se kod funkcije koristi. Jednostavnim riječima, rezultat će biti isti kao da smo koristili autofilter.
Ostale funkcije
Općenito, dovoljan je broj funkcija koje se mogu koristiti za izračunavanje međuvrijednosti. Ali može se ispostaviti da morate uključiti i druge. Pretpostavimo da smo suočeni sa zadatkom da izračunamo ne aritmetičku sredinu, već geometrijsku sredinu. Ova funkcija izgleda kao SRGEOM(), i nije na gore opisanoj listi, ali će se izvući iz ove situacije. Da bismo to uradili, otvorimo istu tabelu.
Sada, koristeći autofilter, ostavljamo samo jednu kategoriju redova. Dakle, naš zadatak je da forsiramo funkciju SUBTOTALS() odrediti geometrijsku sredinu cijena robe sa kodom funkcije 3. Sama formula mora biti formula niza (tj. uzeta u vitičastim zagradama).
Sama formula će biti:
=СРГЕОМ(ЕСЛИ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ($B$10;СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(B10:B19)))-1;));B10:B19;»»))
Kao što vidite, koristimo kombinaciju funkcija LINE и INDIREKTNO na mjestu gdje se nalazi drugi argument funkcije. Na ovaj način možete osigurati da je više raspona proslijeđeno drugom argumentu. Glavna stvar je da se uvjerite da nema skrivene linije, jer će tada, osim cijene, biti prikazana i vrijednost "Prazan tekst", a to je funkcija SRGEOM() nije uzeto u obzir.
Formula međuzbroja u Excelu (primjeri)
funkcija SUBTOTALS može se koristiti u situaciji kada postoji ogromna količina podataka u tabeli. U tom slučaju, nakon ručne konfiguracije kroz funkcionalnost opisanu na početku članka, bit će moguće prikazati samo jedan dio tabele. U ovom slučaju, sve funkcije će raditi kao da tabela uopće nije filtrirana.
Prvi primjer je ovaj. Pretpostavimo da trebamo razumjeti koji su privremeni prodajni rezultati gitare marke lbanez.
Naš asortiman izgleda ovako.
Nakon toga biramo podatke. Nakon što korisnik koristi filter, neki redovi neće biti prikazani. Ako koristite normalnu funkciju SUMA, tada će se proračun izvršiti za cijelu tabelu.
Ako koristimo funkciju PODZBORI, tada će rezultat biti prikazan samo za vrijednosti preostale nakon filtriranja. Ova razlika je vidljiva na ovom snimku ekrana.
Sada ćemo dati primjer selektivnog zbrajanja ćelija. Da biste to učinili, u prozoru postavki formule SUBTOTALS (može se naći u prozoru za unos formule koji se poziva pritiskom na dugme fx) potrebno je da označite odgovarajuća polja za potvrdu u meniju „Dodaj ukupne vrednosti po“ da biste izabrali kolone za koje će se vršiti sumiranje. A da bi se formula mogla ponovo izračunavati svaki put kada se promijeni, postoji postavka „Kad god se promijenite“.
Ručno pisanje formule međuzbroja
Ručno pisanje formule za međuzbir nije ništa teže od pisanja bilo koje druge formule. Jedina stvar koju trebate znati je sintaksa.
Bilo koja formula počinje znakom =, zatim se direktno upisuje naziv funkcije, otvara se zagrada, svi argumenti se navode odvojeni tačkom i zarezom), a zatim se zagrada zatvara.
Međuzbroji u Excel zaokretnoj tabeli
Dodavanje međuzbirova je takođe moguće u zaokretnoj tabeli. Da biste to učinili, koristite karticu "Ukupni podaci i filteri" u svojim parametrima. Da biste prikazali ukupne vrijednosti na ekranu za pojedinačne vrijednosti, morate koristiti dugme filtera (na desnoj strani naziva kolone).
Dakle, postoje tri metode o tome kako možete dodati međuzbrojeve u Excelu, zaokretnu tabelu, formulu i naredbu grupe „Struktura“. Svaki od njih ima svoje prednosti i nedostatke i može se koristiti ako je potrebno automatizirati proces.