Калі вы не зусім пачатковец карыстальнік, то напэўна ўжо заўважылі, што 99% усяго ў Excel прызначана для працы з вертыкальнымі табліцамі, дзе па слупках праходзяць параметры або атрыбуты (поля), а таксама размяшчаецца інфармацыя аб аб'ектах або падзеях. у радках. Зводныя табліцы, прамежкавыя вынікі, капіяванне формул падвойным пстрычкай мышы - усё адаптавана менавіта для гэтага фармату дадзеных.
Аднак правілаў без выключэнняў не бывае, і мяне даволі часта задаюць пытанне, што рабіць, калі ў працы трапілася табліца з гарызантальнай семантычнай арыентацыяй або табліца, у якой радкі і слупкі маюць аднолькавую вагу па значэнні:
І калі Excel яшчэ ведае, як сартаваць па гарызанталі (з дапамогай каманды Дадзеныя – Сартаванне – Параметры – Сартаванне слупкоў), то з фільтраваннем сітуацыя горш - у Excel проста няма ўбудаваных сродкаў для фільтрацыі не радкоў, а слупкоў. Такім чынам, калі перад вамі стаіць такая задача, то прыйдзецца прыдумляць абыходныя шляхі рознай ступені складанасці.
Спосаб 1. Новая функцыя FILTER
Калі вы выкарыстоўваеце новую версію Excel 2021 або маеце падпіску на Excel 365, вы можаце скарыстацца новай функцыяй Фільтр (ФІЛЬТР), які можа фільтраваць зыходныя дадзеныя не толькі па радках, але і па слупках. Для працы гэтай функцыі патрабуецца дапаможны гарызантальны аднамерны масіў-радок, дзе кожнае значэнне (TRUE або FALSE) вызначае, будзем мы паказваць або, наадварот, хаваць наступны слупок у табліцы.
Давайце дадамо наступны радок над нашай табліцай і напішам статус кожнага слупка ў ёй:
- Дапусцім, мы заўсёды хочам адлюстроўваць першы і апошні слупкі (загалоўкі і вынікі), таму для іх у першай і апошняй ячэйках масіва мы ўсталёўваем значэнне = TRUE.
- Для астатніх слупкоў змесціва адпаведных вочак будзе ўяўляць сабой формулу, якая правярае патрэбнае нам ўмова з дапамогай функцый И (І) or OR (OR). Напрыклад, што сума ў дыяпазоне ад 300 да 500.
Пасля гэтага застанецца толькі скарыстацца функцыяй Фільтр каб выбраць слупкі, над якімі наш дапаможны масіў мае значэнне TRUE:
Падобным чынам вы можаце фільтраваць слупкі па зададзеным спісе. У гэтым выпадку дапаможа функцыя COUNTIF (COUNTIF), які правярае колькасць уваходжанняў наступнага імя слупка з загалоўка табліцы ў спісе дазволеных:
Спосаб 2. Зводная табліца замест звычайнай
У цяперашні час у Excel ёсць убудаваная гарызантальная фільтрацыя па слупках толькі ў зводных табліцах, таму, калі нам удасца пераўтварыць нашу зыходную табліцу ў зводную, мы можам выкарыстоўваць гэтую ўбудаваную функцыянальнасць. Для гэтага наша зыходная табліца павінна задавальняць наступным умовам:
- мець «правільны» аднарадковы радок загалоўка без пустых і аб'яднаных вочак - інакш пабудаваць зводную табліцу не атрымаецца;
- не ўтрымліваць дублікатаў у надпісах радкоў і слупкоў – яны «згорнуцца» ў зводцы ў спіс толькі унікальных значэнняў;
- ўтрымліваюць толькі лічбы ў дыяпазоне значэнняў (на скрыжаванні радкоў і слупкоў), таму што зводная табліца абавязкова прыменіць да іх нейкую функцыю агрэгавання (сума, сярэдняе і г.д.), і гэта не будзе працаваць з тэкстам
Калі ўсе гэтыя ўмовы выкананы, то для таго, каб пабудаваць зводную табліцу, якая выглядае як наша зыходная табліца, яе (арыгінальную) трэба будзе разгарнуць з крос-табліцы ў плоскую (нармалізаваную). І самы просты спосаб зрабіць гэта з дапамогай надбудовы Power Query, магутнага інструмента пераўтварэння даных, убудаванага ў Excel з 2016 года.
Да іх адносяцца:
- Давайце пераўтворым табліцу ў «разумную» дынамічную каманду Галоўная - фармат у выглядзе табліцы (Галоўная — фармат у табліцу).
- Загрузка ў Power Query з дапамогай каманды Дадзеныя – з табліцы / дыяпазону (Даныя – з табліцы / дыяпазону).
- Адфільтроўваем радок з вынікамі (у зводцы будуць свае вынікі).
- Пстрыкніце правай кнопкай мышы загаловак першага слупка і абярыце Разгарнуць іншыя слупкі (Адмяніць паварот іншых слупкоў). Усе невылучаныя слупкі канвертуюцца ў два – імя супрацоўніка і значэнне яго паказчыка.
- Фільтраванне слупка з агульнымі вынікамі, якія ўвайшлі ў слупок атрыбут.
- Будуем зводную табліцу па атрыманай плоскай (нармаванай) табліцы з дапамогай каманды Галоўная — Зачыніць і загрузіць — Зачыніць і загрузіць у… (Галоўная старонка — Зачыніць і загрузіць — Зачыніць і загрузіць у…).
Цяпер вы можаце выкарыстоўваць магчымасць фільтрацыі слупкоў, даступных у зводных табліцах - звычайныя галачкі перад назвамі і элементамі Фільтры подпісаў (Фільтры этыкетак) or Фільтры па значэнні (Фільтры значэнняў):
І, вядома, пры змене дадзеных вам трэба будзе абнавіць наш запыт і зводку з дапамогай спалучэння клавіш Ctrl+Alt+F5 або каманда Дадзеныя – абнавіць усе (Дадзеныя — Абнавіць усе).
Спосаб 3. Макрас ў VBA
Усе папярэднія метады, як вы лёгка заўважылі, не зусім фільтруючыя - мы не хаваем слупкі ў зыходным спісе, а фарміруем новую табліцу з зададзеным наборам слупкоў з зыходнай. Калі ж патрабуецца адфільтраваць (схаваць) слупкі ў зыходных дадзеных, то патрэбен прынцыпова іншы падыход, а менавіта макрас.
Выкажам здагадку, што мы хочам адфільтраваць слупкі на хаду, дзе імя кіраўніка ў загалоўку табліцы адпавядае масцы, указанай у жоўтай ячэйцы A4, напрыклад, пачынаецца з літары «A» (гэта значыць, атрымаецца «Ганна» і «Артур»). " у выніку).
Як і ў першым спосабе, мы спачатку рэалізуем дапаможны дыяпазон-радок, дзе ў кожнай вочку наш крытэрый будзе правярацца па формуле і будуць адлюстроўвацца лагічныя значэння TRUE або FALSE для бачных і схаваных слупкоў адпаведна:
Тады давайце дадамо просты макрас. Пстрыкніце правай кнопкай мышы на ўкладцы аркуша і абярыце каманду крыніца (Зыходны код). Скапіруйце і ўстаўце наступны код VBA ў якое адкрылася акно:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Then For Кожная ячэйка In Range("D2:O2") If cell = True Then cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = Праўда End If Наступная ячэйка End If End Sub Яго логіка такая:
- Увогуле, гэта апрацоўшчык падзей Працоўны ліст_Змяніць, г.зн. гэты макрас будзе аўтаматычна запускацца пры любым змене любой ячэйкі на бягучым аркушы.
- Спасылка на змененую ячэйку заўсёды будзе знаходзіцца ў зменнай Мэта.
- Спачатку правяраем, што карыстальнік змяніў менавіта ячэйку з крытэрам (А4) – гэта робіць аператар if.
- Затым пачынаецца цыкл Для кожнага… для перабору шэрых вочак (D2:O2) са значэннямі індыкатара TRUE / FALSE для кожнага слупка.
- Калі значэнне наступнай шэрай ячэйкі TRUE (ісціна), то слупок не схаваны, інакш мы яго схаваем (уласцівасць Утоены).
- Функцыі дынамічнага масіва з Office 365: FILTER, SORT і UNIC
- Зводная табліца з шматрадковым загалоўкам з дапамогай Power Query
- Што такое макрасы, як іх ствараць і выкарыстоўваць