Առանցքային աղյուսակ՝ արժեքներով տեքստով

Առանցքային աղյուսակները լավ են բոլորի համար. դրանք արագ են հաշվարկվում և կազմաձևվում են ճկուն կերպով, և անհրաժեշտության դեպքում դիզայնը կարելի է նրբագեղ կերպով ներդնել դրանց մեջ: Բայց քսուքի մեջ կան նաև մի քանի ճանճեր, մասնավորապես, ամփոփագիր ստեղծելու անկարողությունը, որտեղ արժեքային տարածքը չպետք է պարունակի թվեր, այլ տեքստ։

Փորձենք շրջանցել այս սահմանափակումը և նման իրավիճակում հանդես գալ «մի քանի հենակներով»:

Ենթադրենք, մեր ընկերությունը իր արտադրանքը տարաներով տեղափոխում է Մեր երկրի և Ղազախստանի մի քանի քաղաքներ: Բեռնարկղերը ուղարկվում են ոչ ավելի, քան ամիսը մեկ անգամ: Յուրաքանչյուր տարա ունի այբբենական թվային համար: Որպես նախնական տվյալ, կա առաքումների ցուցակագրման ստանդարտ աղյուսակ, որից դուք պետք է որոշակի ամփոփում կատարեք, որպեսզի հստակ տեսնեք յուրաքանչյուր քաղաք և յուրաքանչյուր ամիս ուղարկված բեռնարկղերի քանակը.

Հարմարության համար եկեք նախնական տվյալներով աղյուսակը նախապես «խելացի» դարձնենք՝ օգտագործելով հրամանը Գլխավոր – Ձևաչափել որպես աղյուսակ (Տուն — Ձևաչափել որպես աղյուսակ) և անուն տվեք նրան Առաքում ականջակալ շինարար (Դիզայն). Ապագայում դա կհեշտացնի կյանքը, քանի որ. աղյուսակի և դրա սյունակների անվանումը հնարավոր կլինի օգտագործել անմիջապես բանաձևերում։

Մեթոդ 1. Ամենահեշտը՝ օգտագործել Power Query

Power Query-ը գերհզոր գործիք է Excel-ում տվյալները բեռնելու և փոխակերպելու համար: Այս հավելումը լռելյայն ներկառուցվել է Excel-ում 2016 թվականից: Եթե ունեք Excel 2010 կամ 2013, կարող եք ներբեռնել և տեղադրել այն առանձին (ամբողջովին անվճար):

Ամբողջ գործընթացը, պարզության համար, ես քայլ առ քայլ վերլուծեցի հետևյալ տեսանյութում.

Եթե ​​հնարավոր չէ օգտագործել Power Query-ը, ապա կարող եք գնալ այլ ճանապարհներով՝ առանցքային աղյուսակի կամ բանաձևերի միջոցով: 

Մեթոդ 2. Օժանդակ ամփոփում

Եկեք ևս մեկ սյունակ ավելացնենք մեր սկզբնական աղյուսակին, որտեղ պարզ բանաձևով մենք հաշվարկում ենք աղյուսակի յուրաքանչյուր տողի թիվը.

Ակնհայտ է, որ անհրաժեշտ է -1, քանի որ մեր աղյուսակում մեկ տողով վերնագիր կա: Եթե ​​ձեր աղյուսակը թերթի սկզբում չէ, ապա կարող եք օգտագործել մի փոքր ավելի բարդ, բայց ունիվերսալ բանաձև, որը հաշվարկում է ընթացիկ տողի և աղյուսակի վերնագրի թվերի տարբերությունը.

Այժմ, ստանդարտ ձևով, մենք մեր տվյալների հիման վրա կկառուցենք ցանկալի տիպի առանցքային աղյուսակ, բայց արժեքի դաշտում մենք կթողնենք դաշտը: Տողի համարը մեր ուզածի փոխարեն աման:

Քանի որ մենք նույն քաղաքում մի քանի բեռնարկղ չունենք նույն ամսում, մեր ամփոփագրում, ըստ էության, կհրապարակվի մեզ անհրաժեշտ բեռնարկղերի ոչ թե գումարը, այլ տողերի համարները:

Բացի այդ, դուք կարող եք անջատել հիմնական և ենթագումարները ներդիրում Կոնստրուկտոր – Ընդհանուր գումարներ и Ենթածրագրեր (Դիզայն - ընդհանուր գումարներ, ենթագումարներ) և նույն տեղում կոճակով անցեք ամփոփումը սեղանի ավելի հարմար դասավորության Հաղորդել մոդելի մասին (Հաշվետվության դասավորություն).

Այսպիսով, մենք արդեն հասել ենք արդյունքի կես ճանապարհին. մենք ունենք աղյուսակ, որտեղ քաղաքի և ամսվա հատման կետում սկզբնաղբյուր աղյուսակում կա տողի համար, որտեղ գտնվում է մեզ անհրաժեշտ կոնտեյների կոդը:

Այժմ եկեք պատճենենք ամփոփագիրը (նույն թերթիկում կամ մեկ այլ) և տեղադրենք այն որպես արժեքներ, այնուհետև մուտքագրենք մեր բանաձևը արժեքի տարածքում, որը կհանի բեռնարկղի կոդը ամփոփագրում հայտնաբերված տողի համարով.

ֆունկցիա IF (ԵԹԵ), այս դեպքում ստուգում է, որ ամփոփագրի հաջորդ բջիջը դատարկ չէ: Եթե ​​դատարկ է, ապա թողարկեք դատարկ տեքստային տող «», այսինքն թողեք բջիջը դատարկ: Եթե ​​դատարկ չէ, ապա քաղեք սյունակից Աման աղբյուրի աղյուսակ Առաքում բջջի բովանդակությունը ըստ տողի թվի, օգտագործելով ֆունկցիան INDEX (INDEX).

Այստեղ, թերևս, միակ ոչ շատ ակնհայտ կետը երկակի բառն է Աման բանաձեւում. Գրելու այսպիսի տարօրինակ ձև.

Պաշարներ[[Բեռնարկղ]:[Բեռնարկղ]]

… անհրաժեշտ է միայն սյունակին հղում կատարելու համար Աման բացարձակ էր (ինչպես սովորական «ոչ խելացի» աղյուսակների համար $ նշաններով հղում) և մեր բանաձևը դեպի աջ պատճենելիս չէր սահում հարևան սյունակներում:

Հետագայում՝ սկզբնաղբյուր աղյուսակի տվյալները փոխելու ժամանակ Առաքում, մենք պետք է հիշենք, որ թարմացնենք մեր օժանդակ ամփոփագիրը տողերի համարներով՝ աջ սեղմելով դրա վրա և ընտրելով հրամանը Թարմացնել և պահպանել (Թարմացնել).

Մեթոդ 3. Բանաձևեր

Այս մեթոդը չի պահանջում միջանկյալ առանցքային աղյուսակի ստեղծում և ձեռքով թարմացում, այլ օգտագործում է Excel-ի «ծանր զենքը»՝ ֆունկցիան։ SUMMESLIMN (SUMIFS). Համառոտ տողերի համարները փնտրելու փոխարեն կարող եք դրանք հաշվարկել հետևյալ բանաձևով.

Որոշակի արտաքին մեծությամբ, փաստորեն, սա ընտրովի գումարման ֆունկցիայի ստանդարտ օգտագործման դեպք է SUMMESLIMNA, որը գումարում է տվյալ քաղաքի և ամսվա համարները: Կրկին, քանի որ մենք նույն քաղաքում մի քանի կոնտեյներ չունենք նույն ամսում, մեր ֆունկցիան, ըստ էության, կտա ոչ թե գումարը, այլ հենց գծի համարը։ Եվ այնուհետև գործառույթն արդեն ծանոթ է նախորդ մեթոդից INDEX Կարող եք նաև հանել բեռնարկղերի կոդերը.

Իհարկե, այս դեպքում այլևս պետք չէ մտածել ամփոփագիրը թարմացնելու մասին, բայց մեծ սեղանների վրա՝ ֆունկցիան. ՍՈՒՄՄԵՍԼԻ կարող է նկատելիորեն դանդաղ լինել: Այնուհետև ստիպված կլինեք անջատել բանաձևերի ավտոմատ թարմացումը կամ օգտագործել առաջին մեթոդը՝ առանցքային աղյուսակը:

Եթե ​​ամփոփագրի տեսքը այնքան էլ հարմար չէ ձեր հաշվետվության համար, ապա դուք կարող եք դրանից դուրս հանել տողերի համարները վերջնական աղյուսակում ոչ ուղղակիորեն, ինչպես մենք արեցինք, այլ օգտագործելով ֆունկցիան: GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Ինչպես դա անել, կարելի է գտնել այստեղ:

• Ինչպես ստեղծել հաշվետվություն՝ օգտագործելով առանցքային աղյուսակը
• Ինչպես կարգավորել հաշվարկները առանցքային աղյուսակներում
• Ընտրովի հաշվարկ SUMIFS-ով, COUNTIFS-ով և այլն: