Մեթոդ 1. Բանաձևեր
Սկսենք տաքանալու համար ամենապարզ տարբերակից՝ բանաձևերից։ Եթե մենք ունենք փոքր աղյուսակ, որը դասավորված է ըստ ամսաթվի որպես մուտքագրում, ապա առանձին սյունակում ընթացիկ ընդհանուր գումարը հաշվարկելու համար մեզ անհրաժեշտ է տարրական բանաձև.
Այստեղ հիմնական առանձնահատկությունը SUM ֆունկցիայի ներսում միջակայքի բարդ ամրագրումն է. միջակայքի սկզբին հղումը բացարձակ է (դոլարի նշաններով), իսկ վերջում՝ հարաբերական (առանց դոլարի): Համապատասխանաբար, բանաձևը ամբողջ սյունակում պատճենելիս մենք ստանում ենք ընդլայնվող միջակայք, որի գումարը մենք հաշվարկում ենք:
Այս մոտեցման թերությունները ակնհայտ են.
- Աղյուսակը պետք է դասավորված լինի ըստ ամսաթվի:
- Տվյալներով նոր տողեր ավելացնելիս բանաձևը պետք է ձեռքով երկարացվի:
Մեթոդ 2. Առանցքային աղյուսակ
Այս մեթոդը մի փոքր ավելի բարդ է, բայց շատ ավելի հաճելի։ Իսկ ավելի խորացնելու համար եկեք դիտարկենք ավելի լուրջ խնդիր՝ տվյալների 2000 տողերի աղյուսակ, որտեղ չկա դասակարգում ըստ ամսաթվի սյունակի, բայց կան կրկնություններ (այսինքն՝ մենք կարող ենք վաճառել մի քանի անգամ նույն օրը).
Մենք վերափոխում ենք մեր սկզբնական աղյուսակը «խելացի» (դինամիկ) ստեղնաշարի դյուրանցման Ctrl+T կամ թիմ Գլխավոր – Ձևաչափել որպես աղյուսակ (Տուն — Ձևաչափել որպես աղյուսակ), և այնուհետև հրամանով դրա վրա կառուցում ենք առանցքային աղյուսակ Տեղադրել – PivotTable (Տեղադրեք - առանցքային աղյուսակ). Ամփոփման մեջ տողերի տարածքում մենք դնում ենք ամսաթիվը, իսկ արժեքների տարածքում վաճառված ապրանքների քանակը.
Խնդրում ենք նկատի ունենալ, որ եթե ունեք Excel-ի ոչ այնքան հին տարբերակ, ապա ամսաթվերը ավտոմատ կերպով խմբավորվում են ըստ տարիների, եռամսյակների և ամիսների: Եթե Ձեզ անհրաժեշտ է այլ խմբավորում (կամ ընդհանրապես դրա կարիքը չունեք), ապա կարող եք շտկել այն՝ աջ սեղմելով ցանկացած ամսաթվի վրա և ընտրելով հրամաններ։ Խումբ / Ungroup (Խումբ / Չխմբավորում).
Եթե դուք ցանկանում եք տեսնել և՛ ստացված գումարներն ըստ ժամանակաշրջանների, և՛ գործարկվող ընդհանուրը առանձին սյունակում, ապա իմաստ ունի դաշտը գցել արժեքի տարածք: Վաճառված է կրկին դաշտի կրկնօրինակը ստանալու համար – դրանում մենք կմիացնենք վազող ընդհանուրների ցուցադրումը: Դա անելու համար դաշտի վրա սեղմեք աջ և ընտրեք հրամանը Լրացուցիչ հաշվարկներ – Կուտակային ընդհանուր (Ցույց տալ արժեքները որպես - վազող ընդհանուր գումարներ):
Այնտեղ կարող եք նաև ընտրել տոկոսադրույքով գումարների աճի տարբերակը, իսկ հաջորդ պատուհանում պետք է ընտրեք այն դաշտը, որի համար կգնա կուտակումը. մեր դեպքում սա ամսաթվի դաշտն է.
Այս մոտեցման առավելությունները.
- Մեծ քանակությամբ տվյալներ արագ ընթերցվում են:
- Ոչ մի բանաձև պետք չէ ձեռքով մուտքագրել:
- Աղբյուրի տվյալները փոխելիս բավական է թարմացնել ամփոփագիրը մկնիկի աջ կոճակով կամ Data – Refresh All հրամանով։
Թերությունները բխում են նրանից, որ սա ամփոփում է, ինչը նշանակում է, որ դրանում չես կարող անել այն, ինչ ուզում ես (տողեր մտցնել, բանաձևեր գրել, որևէ դիագրամ կառուցել և այլն) այլևս չի աշխատի։
Մեթոդ 3. Power Query
Եկեք բեռնենք մեր «խելացի» աղյուսակը աղբյուրի տվյալների հետ Power Query հարցման խմբագրիչում՝ օգտագործելով հրամանը Տվյալներ – Աղյուսակից / միջակայքից (Տվյալներ՝ աղյուսակից/միջակայքից). Excel-ի վերջին տարբերակներում, ի դեպ, այն վերանվանվել է – այժմ կոչվում է Տերեւներով (Թերթից):
Այնուհետև մենք կկատարենք հետևյալ քայլերը.
1. Աղյուսակը դասավորեք աճման կարգով ըստ ամսաթվի սյունակի հրամանով Տեսակավորել բարձրանալը աղյուսակի վերնագրի ֆիլտրի բացվող ցանկում:
2. Մի փոքր ուշ, ընթացիկ ընդհանուր գումարը հաշվարկելու համար մեզ անհրաժեշտ է օժանդակ սյունակ՝ հերթական տողի համարով։ Հրամանով ավելացնենք Ավելացնել սյունակ – ինդեքս սյունակ – 1-ից (Ավելացնել սյունակ — Ինդեքս սյունակ — 1-ից).
3. Բացի այդ, ընթացիկ ընդհանուր գումարը հաշվարկելու համար մեզ անհրաժեշտ է հղում սյունակին Վաճառված է, որտեղ են մեր ամփոփ տվյալները։ Power Query-ում սյունակները կոչվում են նաև ցուցակներ (ցուցակ), և դրան հղում ստանալու համար աջ սեղմեք սյունակի վերնագրի վրա և ընտրեք հրամանը. Մանրամասներ (Ցույց տալ մանրամասները). Մեզ անհրաժեշտ արտահայտությունը կհայտնվի բանաձևերի տողում, որը բաղկացած է նախորդ քայլի անունից #«Ինդեքսն ավելացված է», որտեղից վերցնում ենք աղյուսակը և սյունակի անվանումը [Վաճառք] այս աղյուսակից՝ քառակուսի փակագծերում.
Պատճենեք այս արտահայտությունը clipboard-ում՝ հետագա օգտագործման համար:
4. Ջնջել ավելորդ ավելի վերջին քայլը Վաճառված է և փոխարենը ավելացրեք հաշվարկված սյունակ՝ հրամանով գործող ընդհանուր գումարը հաշվարկելու համար Սյունակի ավելացում – Պատվերով սյունակ (Ավելացնել սյունակ — Հատուկ սյունակ). Մեզ անհրաժեշտ բանաձևը կունենա հետևյալ տեսքը.
Ահա ֆունկցիան Ցուցակ.Ռեզերք վերցնում է բնօրինակ ցուցակը (սյունակ [Վաճառք]) և դրանից հանում է տարրեր՝ սկսած առաջինից (բանաձևում սա 0 է, քանի որ Power Query-ում համարակալումը սկսվում է զրոյից)։ Առբերվող տարրերի թիվը տողի համարն է, որը մենք վերցնում ենք սյունակից [Ինդեքս]. Այսպիսով, առաջին շարքի այս ֆունկցիան վերադարձնում է սյունակի միայն մեկ առաջին բջիջը Վաճառված է. Երկրորդ տողի համար՝ արդեն առաջին երկու բջիջները, երրորդի համար՝ առաջին երեքը և այլն։
Դե, ապա գործառույթը Ցուցակ.Գումար գումարում է արդյունահանված արժեքները և յուրաքանչյուր տողում ստանում ենք բոլոր նախորդ տարրերի գումարը, այսինքն՝ կուտակային ընդհանուրը.
Մնում է ջնջել Index սյունակը, որը մեզ այլևս պետք չէ, և արդյունքները ետ վերբեռնել Excel-ում՝ «Close & Load» հրամանով:
Խնդիրը լուծված է։
Արագ եւ գազազած
Սկզբունքորեն, դա կարելի էր կասեցնել, բայց քսուքի մեջ մի փոքրիկ ճանճ կա. մեր ստեղծած խնդրանքը գործում է կրիայի արագությամբ: Օրինակ, իմ ոչ ամենաթույլ համակարգչի վրա ընդամենը 2000 տողանոց աղյուսակը մշակվում է 17 վայրկյանում։ Իսկ եթե ավելի շատ տվյալներ կան:
Արագացնելու համար կարող եք օգտագործել բուֆերավորում՝ օգտագործելով հատուկ List.Buffer ֆունկցիան, որը որպես փաստարկ բեռնում է իրեն տրված ցուցակը (ցուցակը) RAM-ում, ինչը մեծապես արագացնում է մուտքը դեպի ապագա: Մեր դեպքում իմաստ ունի բուֆերացնել #«Ավելացված ինդեքսը»[Վաճառված] ցանկը, որին Power Query-ը պետք է մուտք ունենա մեր 2000 տողանոց աղյուսակի յուրաքանչյուր տողում գործող ընդհանուր գումարը հաշվարկելիս:
Դա անելու համար հիմնական ներդիրի Power Query խմբագրիչում սեղմեք Ընդլայնված խմբագիր կոճակը (Տուն – Ընդլայնված խմբագիր)՝ Power Query-ում ներկառուցված M լեզվով մեր հարցման սկզբնական կոդը բացելու համար.
Եվ հետո այնտեղ ավելացրե՛ք տող փոփոխականով Իմ ցուցակը, որի արժեքը վերադարձնում է բուֆերային ֆունկցիան, և հաջորդ քայլում ցուցակի կանչը փոխարինում ենք այս փոփոխականով.
Այս փոփոխությունները կատարելուց հետո մեր հարցումը զգալիորեն ավելի արագ կդառնա և կկատարվի 2000 տողանոց աղյուսակը ընդամենը 0.3 վայրկյանում:
Ուրիշ բան, չէ՞: 🙂
- Պարետոյի աղյուսակը (80/20) և ինչպես կառուցել այն Excel-ում
- Հիմնաբառերի որոնում տեքստում և հարցումների բուֆերավորում Power Query-ում