Стожерните табели се добри за секого - брзо се пресметуваат и се конфигурираат флексибилно, а дизајнот може елегантно да се навива во нив, доколку е потребно. Но, има и неколку муви во маста, особено неможноста да се создаде резиме, каде што областа на вредноста не треба да содржи бројки, туку текст.
Ајде да се обидеме да го заобиколиме ова ограничување и да излеземе со „двојка патерици“ во слична ситуација.
Да претпоставиме дека нашата компанија ги транспортира своите производи во контејнери до неколку градови во нашата земја и Казахстан. Контејнерите се испраќаат не повеќе од еднаш месечно. Секој контејнер има алфанумерички број. Како првични податоци, постои стандардна табела со наведени испораки, од која треба да направите некој вид резиме за јасно да ги видите броевите на контејнери испратени до секој град и секој месец:
За погодност, ајде да ја направиме табелата со почетните податоци однапред „паметна“ користејќи ја командата Дома – Форматирајте како табела (Дома - Формат како табела) и дај ѝ име Испораки табот Изведувач (Дизајн). Во иднина ова ќе го поедностави животот, бидејќи. ќе може да се користи името на табелата и нејзините колони директно во формулите.
Метод 1. Најлесно – користете Power Query
Power Query е супер моќна алатка за вчитување и трансформирање на податоци во Excel. Овој додаток стандардно е вграден во Excel од 2016 година. Ако имате Excel 2010 или 2013, можете да го преземете и инсталирате одделно (целосно бесплатно).
Целиот процес, за јасност, го анализирав чекор по чекор во следното видео:
Ако не е можно да се користи Power Query, тогаш можете да одите на други начини - преку стожерна табела или формули.
Метод 2. Помошно резиме
Ајде да додадеме уште една колона на нашата оригинална табела, каде што со едноставна формула го пресметуваме бројот на секој ред во табелата:
Очигледно, потребно е -1, бидејќи имаме заглавие од една линија во нашата табела. Ако вашата табела не е на почетокот на листот, тогаш можете да користите малку посложена, но универзална формула која ја пресметува разликата во броевите на тековниот ред и заглавието на табелата:
Сега, на стандарден начин, ќе изградиме стожерна табела од саканиот тип врз основа на нашите податоци, но во полето за вредност ќе го испуштиме полето Број на линија наместо тоа што го сакаме контејнер:
Бидејќи немаме неколку контејнери во ист град во ист месец, нашето резиме, всушност, ќе ја даде не количината, туку броевите на линиите на контејнерите што ни се потребни.
Дополнително, можете да ги исклучите големите и подвкупните збирки на картичката Конструктор – Општи збирки и Подзбирки (Дизајн - Големи вкупни збирки, потвкупи) и на истото место префрлете го резимето на поудобен распоред на табелата со копчето Пријавете макета (Извештај за распоред).
Така, веќе сме на половина пат до резултатот: имаме табела каде што, на пресекот на градот и месецот, има број на ред во изворната табела, каде што лежи кодот на контејнерот што ни треба.
Сега ајде да го копираме резимето (на истиот лист или на друг) и да го залепиме како вредности, а потоа да ја внесеме нашата формула во областа за вредности, која ќе го извлече кодот на контејнерот според бројот на линијата што се наоѓа во резимето:
функција IF (АКО), во овој случај, проверува дали следната ќелија во резимето не е празна. Ако е празно, тогаш внесете празна текстуална низа „“, т.е. оставете ја ќелијата празна. Ако не е празно, тогаш извадете од колоната Контејнер изворна табела Испораки содржина на ќелија по број на ред користејќи функција Индекс (ИНДЕКС).
Можеби единствената не многу очигледна точка овде е двојниот збор Контејнер во формулата. Таков чуден облик на пишување:
Залихи[[Контејнер]:[Контејнер]]
… е потребно само за повикување на колоната Контејнер беше апсолутна (како референца со знаци $ за обични „непаметни“ табели) и не се лизгаше во соседните колони кога ја копиравме нашата формула надесно.
Во иднина, при промена на податоците во изворната табела Испораки, мора да запомниме да го ажурираме нашето помошно резиме со броеви на линии со десен клик на него и избирање на командата Ажурирајте и зачувајте (Освежи).
Метод 3. Формули
Овој метод не бара создавање на средна стожерна табела и рачно ажурирање, туку го користи „тешкото оружје“ на Excel - функцијата САМЕСЛИМН (SUMIFS). Наместо да ги барате броевите на редовите во резиме, можете да ги пресметате користејќи ја оваа формула:
Со одредена надворешна гломазност, всушност, ова е стандардна употреба за функцијата за селективно собирање САМЕСЛИМНA што ги сумира броевите на редовите за дадениот град и месец. Повторно, бидејќи немаме неколку контејнери во ист град во ист месец, нашата функција, всушност, ќе ја даде не сумата, туку самиот број на линијата. И тогаш функцијата веќе позната од претходниот метод Индекс Можете исто така да извлечете кодови за контејнери:
Се разбира, во овој случај, повеќе не треба да размислувате за ажурирање на резимето, туку на големи табели, функцијата СУММЕСЛИ може да биде значително бавно. Потоа ќе треба да го исклучите автоматското ажурирање на формулите или да го користите првиот метод - стожерна табела.
Ако изгледот на резимето не е многу погоден за вашиот извештај, тогаш можете да извлечете броеви на редови од него во конечната табела не директно, како што направивме ние, туку користејќи ја функцијата GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Како да го направите ова, можете да најдете овде.
- Како да креирате извештај користејќи стожерна табела
- Како да поставите пресметки во стожерните табели
- Селективно броење со SUMIFS, COUNTIFS итн.