Съдържание
Ако не сте съвсем начинаещ потребител, тогава сигурно вече сте забелязали, че 99% от всичко в Excel е проектирано да работи с вертикални таблици, където параметри или атрибути (полета) преминават през колоните и се намира информация за обекти или събития в редовете. Пивотни таблици, междинни суми, копиране на формули с двойно щракване – всичко е пригодено специално за този формат на данни.
Въпреки това, няма правила без изключения и с доста редовна честота ме питат какво да направя, ако таблица с хоризонтална семантична ориентация или таблица, в която редове и колони имат еднакво значение по значение, се натъкна в работата:
И ако Excel все още знае как да сортира хоризонтално (с командата Данни – Сортиране – Опции – Сортиране на колони), тогава ситуацията с филтрирането е по-лоша – просто няма вградени инструменти за филтриране на колони, а не на редове в Excel. Така че, ако сте изправени пред такава задача, ще трябва да измислите заобикалящи решения с различна степен на сложност.
Метод 1. Нова функция FILTER
Ако използвате новата версия на Excel 2021 или имате абонамент за Excel 365, можете да се възползвате от нововъведената функция FILTER (ФИЛТРИРАНЕ), който може да филтрира изходните данни не само по редове, но и по колони. За да работи, тази функция изисква допълнителен хоризонтален едномерен масив-ред, където всяка стойност (TRUE или FALSE) определя дали ще покажем или, обратно, ще скрием следващата колона в таблицата.
Нека добавим следния ред над нашата таблица и да напишем състоянието на всяка колона в нея:
- Да кажем, че винаги искаме да показваме първата и последната колона (заглавия и общи суми), така че за тях в първата и последната клетка на масива задаваме стойност = TRUE.
- За останалите колони съдържанието на съответните клетки ще бъде формула, която проверява условието, от което се нуждаем, с помощта на функции И (И) or OR (OR). Например, че общата сума е в диапазона от 300 до 500.
След това остава само да използвате функцията FILTER за да изберете колони, над които нашият спомагателен масив има TRUE стойност:
По същия начин можете да филтрирате колони по даден списък. В този случай функцията ще помогне COUNTIF (COUNTIF), който проверява броя на срещанията на следващото име на колона от заглавката на таблицата в разрешения списък:
Метод 2. Пивот таблица вместо обичайната
В момента Excel има вградено хоризонтално филтриране по колони само в осеви таблици, така че ако успеем да конвертираме нашата оригинална таблица в обобщена таблица, можем да използваме тази вградена функционалност. За да направим това, нашата таблица източник трябва да отговаря на следните условия:
- да имате „правилен“ едноредов заглавен ред без празни и обединени клетки – в противен случай няма да работи за изграждане на обобщена таблица;
- не съдържат дубликати в етикетите на редове и колони – те ще се „свият“ в резюмето в списък само с уникални стойности;
- съдържат само числа в диапазона от стойности (в пресечната точка на редове и колони), защото обобщената таблица определено ще приложи към тях някаква агрегираща функция (сума, средна стойност и т.н.) и това няма да работи с текста
Ако всички тези условия са изпълнени, тогава, за да се изгради обобщена таблица, която изглежда като нашата оригинална таблица, тя (оригиналната) ще трябва да бъде разширена от кръстосаната таблица в плоска (нормализирана). И най-лесният начин да направите това е с добавката Power Query, мощен инструмент за преобразуване на данни, вграден в Excel от 2016 г.
Това са:
- Нека преобразуваме таблицата в „интелигентна“ динамична команда Начало – Форматиране като таблица (Начало — Форматиране като таблица).
- Зареждане в Power Query с командата Данни – от таблица / диапазон (Данни – от таблица / диапазон).
- Филтрираме реда с общите суми (обобщението ще има свои собствени общи суми).
- Щракнете с десния бутон върху заглавието на първата колона и изберете Отменете свиването на други колони (Отмяна на завъртането на други колони). Всички неизбрани колони се конвертират в две – името на служителя и стойността на неговия показател.
- Филтриране на колоната с общите суми, които са влезли в колоната Атрибут.
- Изграждаме обобщена таблица според получената плоска (нормализирана) таблица с командата Начало — Затвори и зареди — Затвори и зареди в… (Начало — Затвори и зареди — Затвори и зареди в...).
Вече можете да използвате възможността за филтриране на колони, налични в осеви таблици – обичайните отметки пред имената и елементите Филтри за подпис (Филтри за етикети) or Филтрира по стойност (Филтри за стойност):
И разбира се, когато променяте данните, ще трябва да актуализирате нашата заявка и резюмето с клавишна комбинация Ctrl+Друг+F5 или екип Данни – обновяване на всички (Данни — Обновяване на всички).
Метод 3. Макрос във VBA
Всички предишни методи, както лесно можете да видите, не са точно филтриращи – ние не скриваме колоните в оригиналния списък, а формираме нова таблица с даден набор от колони от оригиналния. Ако се изисква филтриране (скриване) на колоните в изходните данни, тогава е необходим коренно различен подход, а именно макрос.
Да предположим, че искаме да филтрираме колони в движение, където името на мениджъра в заглавката на таблицата отговаря на маската, посочена в жълтата клетка A4, например, започва с буквата „A“ (тоест, вземете „Анна“ и „Артър“ " като резултат).
Както в първия метод, ние първо прилагаме спомагателен диапазон-ред, където във всяка клетка нашият критерий ще бъде проверен чрез формула и логическите стойности TRUE или FALSE ще бъдат показани съответно за видими и скрити колони:
Тогава нека добавим прост макрос. Щракнете с десния бутон върху раздела на листа и изберете команда източник (Програмен код). Копирайте и поставете следния VBA код в прозореца, който се отваря:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Then За всяка клетка в Range("D2:O2") If cell = True Then cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Next клетка Край If End Sub Логиката му е следната:
- Като цяло това е манипулатор на събития Работен лист_Промяна, т.е. този макрос ще се стартира автоматично при всяка промяна в която и да е клетка на текущия лист.
- Препратката към променената клетка винаги ще бъде в променливата Цел.
- Първо проверяваме дали потребителят е променил точно клетката с критерия (A4) – това се прави от оператора if.
- След това цикълът започва За всеки… за итерация на сивите клетки (D2:O2) със стойности на индикатора TRUE / FALSE за всяка колона.
- Ако стойността на следващата сива клетка е TRUE (истина), тогава колоната не е скрита, в противен случай я скриваме (свойство Скрит).
- Функции за динамичен масив от Office 365: ФИЛТРИРАНЕ, СОРТИРАНЕ и UNIC
- Обобщена таблица с многоредов хедър с помощта на Power Query
- Какво представляват макросите, как се създават и използват