Šis straipsnis – adaptuotas „MrExcel.com“ straipsnis. Svetainėje „MrExcel.com“ rasite daugiau patarimų ir naudingos informacijos.

Tarkime, jūs turite mėnesio finansinių operacijų sąrašą, kuriame kiekvienos sąskaitos numeris gali kartotis daugybę kartų. Mėnesio pabaigoje jums reikia surūšiuoti duomenis pagal sąskaitų kodus ir suskaičiuoti kiekvienos sąskaitos tarpines sumas.

Šią užduotį galima atlikti bent penkiais būdais. Toliau jie visi išsamiai aprašomi.

Pastaba  Šiame pavyzdyje stulpelyje A yra sąskaitų numeriai, o stulpelyje B – sumos. Duomenų diapazonas aprėpia langelius A2:B100. Duomenys dar nesurūšiuoti.

1 būdas. Sukurkite IF sakinius ir pritaikykite komandą „Įklijuoti specialų“ (Paste Special)

Atlikite tokius veiksmus:

1. Surūšiuokite duomenis pagal lauką „Sąskaita“ (t. y. stulpelį A).
2. Stulpelyje C sugalvokite formulę, kuri skaičiuotų einamąsias kiekvienos sąskaitos sumas. Pvz., į langelį C2 galite įvesti tokią formulę:

   =IF(A2=A1,C1+B2,B2)

3. Stulpelyje D sugalvokite formulę, kuri nustatytų paskutinį kiekvienos sąskaitos įrašą. Pvz., į langelį D2 galite įvesti tokią formulę:

   =IF(A2=A3,FALSE,TRUE)

4. Langelių C2:D2 formules kopijuokite į visas tolesnes eilutes.
5. Kopijuokite langelius C2:D100. Diapazonas tebėra pažymėtas, taigi formules galite pakeisti jų reikšmėmis – spustelėkite meniu Redagavimas (Edit) komandą Įklijuoti specialų (Paste Special), paskui – Reikšmės (Values) ir Gerai (OK).
6. Duomenis surūšiuokite pagal stulpelį D mažėjimo tvarka.
7. Stulpelio A eilutėse, kurių stulpelyje D yra reikšmės TRUE, jūs turite unikalių sąskaitų numerių sąrašą, o stulpelyje C – šių sąskaitų einamąsias galutines sumas.

Privalumai  Tai greitas būdas. Tik reikia būti gerai įgudusiam rašyti IF sakinius.

Trūkumai  Esama ir geresnių būdų.

2 būdas. Unikalių sąskaitų numerių sąrašą gaukite komanda „Išplėstinis filtras“ (Advanced Filter)

Unikalių sąskaitų numerių sąrašas gaunamas taip:

1. Pažymėkite langelius A1:A100.
2. Perkelkite pelės žymeklį ant meniu Duomenys (Data) komandos Filtras (Filter) ir spustelėkite Išplėstinis filtras (Advanced Filter).
3. Spustelėkite Kopijuoti į kitą vietą (Copy to another location).
4. Pažymėkite laukelį Tik unikalius įrašus (Unique records only).
5. Pasirinkite tuščią darbalapio sritį, kurioje turės būti unikalių sąskaitų numerių sąrašas. Šią vietą nurodykite lauke Kopijuoti į (Copy to).

   Pastaba  Kol nepasirenkate komandos Kopijuoti į kitą vietą (Copy to another location), laukas Kopijuoti į (Copy to) būna blankus (pilkas).

6. Spustelėkite Gerai (OK). Nurodytoje vietoje pasirodo unikalūs sąskaitų numeriai.
7. Atlikite kitus rezultatams gauti reikalingus veiksmus, įveskite masyvų formules ir pan.

Privalumai  Šis būdas greitesnis už pirmąjį. Nereikia rūšiuoti.

Trūkumai  Rašydami masyvų formules turėsite gerokai pasukti galvą.

3 būdas. Pasinaudokite komanda „Sutraukti„ (Consolidate)

Komandą „Sutraukti“ (Consolidate) galima naudoti tik tada, kai tenkinami tam tikri reikalavimai. Sąskaitų numeriai turi būti sumuojamų skaitinių laukų kairėje. Kiekvienas stulpelis privalo turėti antraštę. Jums reikės įvardyti stačiakampį darbalapio langelių bloką, kurį sudaro kairiojo stulpelio sąskaitų numeriai ir viršutinė antraščių eilutė. Šiuo atveju bloką sudaro langeliai A1:B100.

1. Pažymėkite diapazoną A1:B100.
2. Suteikite jam vardą – spustelėkite vardo lauke (formulės lauko kairėje) ir įveskite pavadinimą, pvz., „Sumos“. (Arba spustelėkite meniu Įterpimas (Insert) komandą Vardas (Name)).
3. Langelio žymeklį perkelkite į tuščią darbalapio sritį.
4. Spustelėkite meniu Duomenys (Data) komandą Sutraukti (Consolidate).
5. Lauke Nuoroda (Reference) surinkite diapazono vardą (Sumos).
6. Srityje Naudoti žymes (Use labels in) pažymėkite ir Viršutinėje eilutėje (Top row), ir Kairiajame stulpelyje (Left column).
7. Spustelėkite Gerai (OK).

Privalumai  Nereikia rūšiuoti. Reikiamus veiksmus galima atlikti keliomis klavišų kombinacijomis: ALT+D+N (diapazono įvardijimas), ALT+T, ALT+L, ENTER. Nesunku išplėsti. Jei diapazonas aprėpia 12 mėnesių stulpelius, atsakyme bus kiekvieno mėnesio sumos.

Trūkumai  Jei tame pačiame darbalapyje norėsite dar kartą įvykdyti komandą „Sutraukti“ (Consolidate), jums reikės iš lauko Visos nuorodos (All references) išvalyti esamą diapazoną klavišu DELETE. Sąskaitų numeriai būtinai turi būti skaitinių duomenų kairėje. Šis būdas truputį lėtesnis už suvestinės lentelės (PivotTable) būdą. Tai ypač pastebima, kai duomenų įrašų skaičius viršija 10 000.

4 būdas. Pasinaudokite komanda „Tarpinė suma„ (Subtotal)

Tai puiki priemonė. Bet dirbti su rezultatais šiek tiek keblu, todėl tikriausiai ja naudositės rečiau nei komanda „Sutraukti“ (Consolidate).

1. Duomenis surūšiuokite pagal stulpelį A didėjančia tvarka.
2. Pažymėkite bet kurį duomenų diapazono langelį.
3. Spustelėkite meniu Duomenys (Data) komandą Tarpinės sumos (Subtotals).
4. Kaip numatyta, programa „Excel“ siūlo sumuoti paskutinį duomenų stulpelį. Šiame pavyzdyje tai tiks, bet dažnai tenka slinkti sąrašą Pridėti tarpinę sumą prie (Add subtotal to) ir ieškoti reikiamų laukų.
5. Spustelėkite Gerai (OK). Kaskart pasikeitus sąskaitos numeriui programa „Excel“ įterpia naują eilutę ir suskaičiuoja tarpinę sumą.

Kai tarpinės sumos jau suskaičiuotos, žemiau vardo lauko rodomi mygtukai 1, 2 ir 3. Spustelėję 2, darbalapyje matysite tik po vieną kiekvienos sąskaitos eilutę su sumomis. Spustelėję 3, matysite visas eilutes.

Privalumai  Puiki priemonė. Ypač tinka spausdinti ataskaitoms su tarpinėmis sumomis, kai kiekviena grupė turi būti pradedama naujame puslapyje.

Trūkumai  Pirmiausia būtina surūšiuoti duomenis. Jei duomenų daug, gali dirbti lėtai. Pasirinkus meniu Redagavimas (Edit) komandą Perėjimas (Go To) ir spustelėjus Specialusis (Special), galima pažymėti tik matomus langelius. Taigi sumas galima perkelti į kitą vietą. Pasirinkus meniu Duomenys (Data) komandą Tarpinės sumos (Subtotals) ir spustelėjus Šalinti viską (Remove All), galima grąžinti pradinius duomenis.

5 būdas. Pasinaudokite suvestine lentele (PivotTable)

Suvestinės lentelės (PivotTable) – pats lanksčiausias sprendimas. Duomenis rūšiuoti nebūtina. Skaičių stulpelis gali būti ir sąskaitų numerių kairėje, ir dešinėje. Puslapyje sąskaitų numerius galima išdėstyti ir vertikaliai, ir horizontaliai.

1. Pažymėkite bet kurį duomenų diapazono langelį.
2. Spustelėkite meniu Duomenys (Data) komandą PivotTable ir PivotChart ataskaita (PivotTable and PivotChart Report).
3. Spustelėkite Pirmyn (Next), t. y. priimkite visas numatytąsias pirmojo žingsnio pasirinktis.
4. Antrajame žingsnyje patikrinkite, ar teisingas duomenų diapazonas (paprastai jis būna teisingas), ir spustelėkite Pirmyn (Next).
5. Trečiajame žingsnyje spustelėkite mygtuką Maketas (Layout) (programa „Excel 97“ trečiajame žingsnyje automatiškai pereina į maketą).
6. Dialogo lange Maketas (Layout) vilkite mygtuką Sąskaita iš dešiniosios pusės į eilučių sritį ir ten numeskite.
7. Mygtuką Suma iš dešiniosios pusės atvilkite į sritį Duomenys (Data).
8. Spustelėkite OK (Gerai) (programoje „Excel 97“ spustelėkite Pirmyn (Next)).
9. Nurodykite, ar rezultatai turės būti pateikiami naujame darbalapyje, ar tam tikroje esamo darbalapio srityje, ir spustelėkite Baigti (Finish).

Privalumai  Greita, lanksti ir universali priemonė. Veikia sparčiai, net jei duomenų labai daug.

Trūkumai  Nedrąsu išbandyti.