Окретне табеле су добре за све – брзо израчунавају и флексибилно су конфигурисане, а дизајн се може елегантно умотати у њих, ако је потребно. Али постоји и неколико проблема, посебно немогућност креирања резимеа, где област вредности не би требало да садржи бројеве, већ текст.
Хајде да покушамо да заобиђемо ово ограничење и смислимо „пар штака“ у сличној ситуацији.
Претпоставимо да наша компанија транспортује своје производе у контејнерима у неколико градова у нашој земљи и Казахстану. Контејнери се шаљу не више од једном месечно. Сваки контејнер има алфанумерички број. Као почетни подаци, постоји стандардна табела са списком испорука, из које треба да направите неку врсту резимеа да бисте јасно видели бројеве контејнера послатих у сваки град и сваки месец:
Ради практичности, направимо табелу са почетним подацима унапред „паметном“ користећи команду Почетна – Формат као табела (Почетна — Формат као табела) и дај јој име Испоруке табулатор Конструктор (Дизајн). У будућности ће ово поједноставити живот, јер. биће могуће користити име табеле и њених колона директно у формулама.
Метод 1. Најлакши – користите Повер Куери
Повер Куери је супер моћан алат за учитавање и трансформацију података у Екцел-у. Овај додатак је подразумевано уграђен у Екцел од 2016. Ако имате Екцел 2010 или 2013, можете га засебно преузети и инсталирати (потпуно бесплатно).
Цео процес, ради јасноће, анализирао сам корак по корак у следећем видеу:
Ако није могуће користити Повер Куери, онда можете ићи на друге начине – кроз стожерну табелу или формуле.
Метод 2. Помоћни резиме
Хајде да додамо још једну колону нашој оригиналној табели, где помоћу једноставне формуле израчунавамо број сваког реда у табели:
Очигледно је потребно -1, јер имамо заглавље у једном реду у нашој табели. Ако ваша табела није на почетку листа, онда можете користити мало сложенију, али универзалнију формулу која израчунава разлику у бројевима тренутног реда и заглавља табеле:
Сада, на стандардан начин, направићемо стожерну табелу жељеног типа на основу наших података, али у пољу вредности ћемо испустити поље Број линије уместо онога што желимо контејнер:
Пошто немамо више контејнера у истом граду у истом месецу, наш резиме ће, у ствари, дати не количину, већ бројеве редова контејнера који су нам потребни.
Поред тога, можете да искључите главне и међузбирове на картици Конструктор – Општи износи и Међузбирови (Дизајн — укупни збројеви, међузбирови) и на истом месту пребаците резиме на погоднији изглед табеле помоћу дугмета Модел извештаја (Изглед извештаја).
Дакле, већ смо на пола пута до резултата: имамо табелу у којој се, на раскрсници града и месеца, налази број реда у изворној табели, где лежи код контејнера који нам је потребан.
Сада копирајмо резиме (на исти или други лист) и налепимо га као вредности, а затим унесемо нашу формулу у област вредности, која ће издвојити код контејнера према броју реда који се налази у резимеу:
функција IF (АКО), у овом случају, проверава да следећа ћелија у резимеу није празна. Ако је празно, онда испишите празан текстуални стринг “”, тј. оставите ћелију празном. Ако није празно, онда извуците из колоне Контејнер изворна табела Испоруке садржај ћелије према броју реда помоћу функције ИНДЕКС (ИНДЕКС).
Можда једина не баш очигледна тачка овде је двострука реч Контејнер у формули. Тако чудан облик писања:
Залихе[[Контејнер]:[Контејнер]]
… је потребно само за референцу на колону Контејнер је била апсолутна (као референца са знаковима $ за обичне „непаметне“ табеле) и није склизнула на суседне колоне приликом копирања наше формуле на десно.
Убудуће при промени података у изворној табели Испоруке, морамо запамтити да ажурирамо наш помоћни резиме са бројевима редова тако што ћемо кликнути десним тастером миша на њега и изабрати команду Ажурирајте и сачувајте (Освјежи).
Метод 3. Формуле
Овај метод не захтева креирање средње заокретне табеле и ручно ажурирање, већ користи Екцелово „тешко оружје“ – функцију СУММЕСЛИМН (СУМИФС). Уместо да тражите бројеве редова у резимеу, можете их израчунати помоћу ове формуле:
Са извесном спољашњом гломазношћу, у ствари, ово је стандардни случај употребе функције селективног сумирања СУММЕСЛИМНА који сабира бројеве редова за дати град и месец. Опет, пошто немамо више контејнера у истом граду у истом месецу, наша функција ће, у ствари, издати не износ, већ сам број линије. А онда функција већ позната из претходне методе ИНДЕКС Такође можете издвојити кодове контејнера:
Наравно, у овом случају више не морате размишљати о ажурирању резимеа, али на великим табелама функција СУММЕСЛИ може бити приметно спор. Тада ћете морати да искључите аутоматско ажурирање формула или да користите први метод – стожерну табелу.
Ако изглед резимеа није баш прикладан за ваш извештај, онда можете извући бројеве редова из њега у коначну табелу не директно, као што смо ми урадили, већ помоћу функције ГЕТ.ПИВОТ.ТАБЛЕ.ДАТА (ГЕТ.ПИВОТ.ДАТА). Како то учинити можете пронаћи овде.
- Како направити извештај користећи заокретну табелу
- Како подесити прорачуне у изведеним табелама
- Селективно бројање са СУМИФС, ЦОУНТИФС итд.