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