Ако нисте баш почетник, онда сте сигурно већ приметили да је 99% свега у Екцел-у дизајнирано за рад са вертикалним табелама, где параметри или атрибути (поља) пролазе кроз колоне, а информације о објектима или догађајима се налазе у редовима . Заокретне табеле, међузбирови, копирање формула двоструким кликом – све је скројено посебно за овај формат података.
Међутим, нема правила без изузетака и са прилично редовном учесталошћу питам се шта да радим ако у раду наиђе на табелу са хоризонталном семантичком оријентацијом, или табелу у којој редови и колоне имају исту тежину по значењу:
А ако Екцел и даље зна како да сортира хоризонтално (са командом Подаци – Сортирај – Опције – Сортирај колоне), онда је ситуација са филтрирањем гора – једноставно нема уграђених алата за филтрирање колона, а не редова у Екцел-у. Дакле, ако се суочите са таквим задатком, мораћете да смислите решења различитог степена сложености.
Метод 1. Нова функција ФИЛТЕР
Ако користите нову верзију програма Екцел 2021 или претплату на Екцел 365, можете искористити предности новоуведене функције ФИЛТЕР (ФИЛТЕР), који може да филтрира изворне податке не само по редовима, већ и по колонама. Да би радила, ова функција захтева помоћни хоризонтални једнодимензионални низ-ред, где свака вредност (ТРУЕ или ФАЛСЕ) одређује да ли ћемо приказати или, обрнуто, сакрити следећу колону у табели.
Хајде да додамо следећу линију изнад наше табеле и упишемо статус сваке колоне у њој:
- Рецимо да увек желимо да прикажемо прву и последњу колону (заглавља и збројеве), па за њих у првој и последњој ћелији низа постављамо вредност = ТРУЕ.
- За преостале колоне, садржај одговарајућих ћелија биће формула која проверава стање које нам је потребно помоћу функција И (И) or OR (ИЛИ). На пример, да је укупан број у распону од 300 до 500.
Након тога, остаје само да користите функцију ФИЛТЕР да изаберете колоне изнад којих наш помоћни низ има ТРУЕ вредност:
Слично, можете филтрирати колоне према датој листи. У овом случају, функција ће помоћи ЦОУНТИФ (ЦОУНТИФ), који проверава број појављивања имена следеће колоне из заглавља табеле на листи дозвољених:
Метод 2. Пивот табела уместо уобичајене
Тренутно, Екцел има уграђено хоризонтално филтрирање по колонама само у изведеним табелама, тако да ако успемо да конвертујемо нашу оригиналну табелу у изведену табелу, можемо да користимо ову уграђену функционалност. Да бисмо то урадили, наша изворна табела мора да задовољи следеће услове:
- имају „тачну“ линију заглавља у једном реду без празних и спојених ћелија – у супротном неће радити да се направи стожерна табела;
- не садрже дупликате у ознакама редова и колона – они ће се „скупити“ у резимеу у листу само јединствених вредности;
- садрже само бројеве у опсегу вредности (на пресеку редова и колона), јер ће стожерна табела дефинитивно применити неку врсту функције агрегирања на њих (збир, просек, итд.) и то неће радити са текстом
Ако су сви ови услови испуњени, онда да бисмо направили стожерну табелу која изгледа као наша оригинална табела, она (оригинална) ће морати да се прошири из унакрсне табеле у равну (нормализовану). А најлакши начин да то урадите је помоћу додатка Повер Куери, моћног алата за трансформацију података уграђеног у Екцел од 2016.
Су:
- Хајде да претворимо табелу у „паметну“ динамичку команду Почетна – Формат као табела (Почетна — Формат као табела).
- Учитавање у Повер Куери помоћу команде Подаци – из табеле / опсега (подаци – из табеле / опсега).
- Филтрирамо линију са укупним вредностима (резиме ће имати сопствене збројеве).
- Кликните десним тастером миша на наслов прве колоне и изаберите Поништите друге колоне (Опозови друге колоне). Све колоне које нису изабране се претварају у две – име запосленог и вредност његовог индикатора.
- Филтрирање колоне са укупним износима који су ушли у колону атрибут.
- Командом правимо стожерну табелу према резултујућој равној (нормализованој) табели Почетна — Затвори и учитај — Затвори и учитај у… (Почетна — Затвори и учитај — Затвори и учитај до…).
Сада можете да користите могућност филтрирања колона доступних у изведеним табелама – уобичајене квачице испред имена и ставки Сигнатуре Филтерс (Филтери ознака) or Филтрира по вредности (Филтери вредности):
И наравно, када мењате податке, мораћете да ажурирате наш упит и резиме помоћу пречице на тастатури Цтрл+други+F5 или тим Подаци – Освежи све (Подаци — Освежи све).
Метод 3. Макро у ВБА
Све претходне методе, као што можете лако видети, нису баш филтрирање – не сакривамо колоне у оригиналној листи, већ формирамо нову табелу са датим скупом колона из оригиналне. Ако је потребно филтрирати (сакрити) колоне у изворним подацима, онда је потребан фундаментално другачији приступ, наиме, макро.
Претпоставимо да желимо да филтрирамо колоне у ходу где име менаџера у заглављу табеле задовољава маску наведену у жутој ћелији А4, на пример, почиње словом „А“ (то јест, добијете „Анна“ и „Артур“ " као резултат).
Као иу првом методу, прво имплементирамо помоћни ред опсега, где ће у свакој ћелији наш критеријум бити проверен формулом и логичке вредности ТРУЕ или ФАЛСЕ ће бити приказане за видљиве и скривене колоне, респективно:
Затим додајмо једноставан макро. Кликните десним тастером миша на картицу листа и изаберите команду извор (Изворни код). Копирајте и налепите следећи ВБА код у прозор који се отвори:
Приватни под Радни лист_Цханге(БиВал Таргет Ас Ранге) Ако Таргет.Аддресс = "$А$4" Онда За сваку ћелију у опсегу("Д2:О2") Ако је ћелија = Труе Онда целл.ЕнтиреЦолумн.Хидден = Фалсе Елсе целл.ЕнтиреЦолумн.Хидден = Труе Енд Иф Нект целл Енд Иф Енд Суб Његова логика је следећа:
- Генерално, ово је руковалац догађаја Ворксхеет_Цханге, тј. овај макро ће се аутоматски покренути при свакој промени било које ћелије на тренутном листу.
- Референца на промењену ћелију ће увек бити у променљивој Мета.
- Прво проверавамо да ли је корисник променио тачно ћелију са критеријумом (А4) – то ради оператер if.
- Затим почиње циклус За сваки… за понављање преко сивих ћелија (Д2:О2) са вредностима индикатора ТРУЕ / ФАЛСЕ за сваку колону.
- Ако је вредност следеће сиве ћелије ТРУЕ (тачно), колона није скривена, у супротном је сакривамо (особина Сакривен).
- Функције динамичког низа из Оффице 365: ФИЛТЕР, СОРТ и УНИЦ
- Заокретна табела са вишелинијским заглављем користећи Повер Куери
- Шта су макрои, како их креирати и користити