Wann Dir net ganz en Ufänger Benotzer sidd, da musst Dir scho gemierkt hunn datt 99% vun allem an Excel entwéckelt ass fir mat vertikalen Dëscher ze schaffen, wou Parameteren oder Attributer (Felder) duerch d'Kolonn goen, an Informatioun iwwer Objeten oder Eventer läit an de Linnen. Pivottabellen, Subtotalen, Formelen kopéieren mat engem Duebelklick - alles gëtt speziell fir dëst Dateformat ugepasst.
Wéi och ëmmer, et gi keng Regelen ouni Ausnahmen a mat enger zimlech reegelméisseger Frequenz ginn ech gefrot wat ze maachen wann en Dësch mat enger horizontaler semantescher Orientéierung, oder eng Tabell wou Reihen a Kolonnen datselwecht Gewiicht a Bedeitung hunn, an der Aarbecht begéint ass:
A wann Excel nach ëmmer weess wéi een horizontal sortéiert (mat dem Kommando Daten - Sortéieren - Optiounen - Kolonnen sortéieren), dann ass d'Situatioun mat der Filterung méi schlëmm - et gi einfach keng agebaute Tools fir Spalten ze filteren, net Reihen an Excel. Also, wann Dir mat esou enger Aufgab konfrontéiert sidd, musst Dir mat Léisunge vu verschiddene Grad vu Komplexitéit kommen.
Method 1. New FILTER Funktioun
Wann Dir op der neier Versioun vun Excel 2021 oder engem Excel 365 Abonnement sidd, kënnt Dir vun der nei agefouerter Feature profitéieren FILTER (FILTER), déi d'Quelldaten net nëmme vu Reihen filteren, awer och duerch Kolonnen. Fir ze schaffen, erfuerdert dës Funktioun eng zousätzlech horizontal eendimensional Array-Zeil, wou all Wäert (TRUE oder FALSE) bestëmmt ob mir déi nächst Kolonn an der Tabell weisen oder ëmgekéiert verstoppen.
Loosst eis déi folgend Zeil iwwer eisen Dësch addéieren a schreiwen de Status vun all Kolonn dran:
- Loosst eis soen, mir wëllen ëmmer déi éischt an déi lescht Kolonnen (Header an Total) weisen, also fir si an der éischter a leschter Zelle vun der Array setze mir de Wäert = TRUE.
- Fir déi verbleiwen Sailen wäert den Inhalt vun den entspriechende Zellen eng Formel sinn déi d'Konditioun iwwerpréift déi mir brauchen mat Funktiounen И (AN) or OR (ODER). Zum Beispill, datt den Total am Beräich vun 300 bis 500 ass.
Duerno bleift et nëmmen d'Funktioun ze benotzen FILTER fir Spalten ze wielen iwwer deenen eis Hëllefsarray e TRUE Wäert huet:
Ähnlech kënnt Dir Kolonnen no enger bestëmmter Lëscht filteren. An dësem Fall wäert d'Funktioun hëllefen COUNTIF (COUNTIF), déi d'Zuel vun den Optriede vum nächste Kolonnennumm aus dem Tabellkopf an der erlaabt Lëscht kontrolléiert:
Method 2. Pivot Dësch amplaz déi üblech
Momentan huet Excel eng agebaute horizontale Filterung duerch Kolonnen nëmmen a Pivottabellen, also wa mir et fäerdeg bréngen eis ursprénglech Tabell an eng Pivot-Tabel ze konvertéieren, kënne mir dës agebaute Funktionalitéit benotzen. Fir dëst ze maachen, muss eis Quelltabell déi folgend Konditiounen erfëllen:
- hunn eng "korrekt" One-Line Header Linn ouni eidel a fusionéiert Zellen - soss funktionnéiert et net fir eng Pivottabell ze bauen;
- enthalen keng Duplikaten an den Etiketten vu Reihen a Spalten - si wäerten am Resumé an eng Lëscht vun nëmmen eenzegaartege Wäerter "zesummebrach";
- enthalen nëmmen Zuelen an der Gamme vu Wäerter (op der Kräizung vun de Reihen a Spalten), well d'Pivottabelle definitiv eng Aart vun Aggregatiounsfunktioun op si uwenden (Zomm, Moyenne, asw.) an dëst funktionnéiert net mam Text
Wann all dës Konditioune erfëllt sinn, da fir e Pivot-Tabelle ze bauen, dee wéi eisen ursprénglechen Dësch ausgesäit, muss se (den ursprénglechen) vun der Crosstab an eng flaach erweidert ginn (normaliséiert). An deen einfachste Wee fir dëst ze maachen ass mam Power Query Add-in, e mächtege Datetransformatiounsinstrument dat zënter 2016 an Excel gebaut gouf.
Dat sinn:
- Loosst eis den Dësch an en "intelligente" dynamesche Kommando konvertéieren Home - Format als Dësch (Home - Format als Table).
- Lueden an Power Query mam Kommando Daten - Vun Table / Range (Daten - Vun Table / Range).
- Mir filteren d'Linn mat den Totalen (de Resumé wäert seng eege Gesamtzuelen hunn).
- Riets-klickt op déi éischt Kolonn Rubrik a wielt Aner Kolonnen unzepaken (Aner Kolonnen unpivot). All net ausgewielte Sailen ginn an zwee ëmgewandelt - den Numm vum Employé an de Wäert vu sengem Indikator.
- Filtert d'Kolonn mat den Totalen, déi an d'Kolonn gaange sinn Spezialfäegkeeten.
- Mir bauen eng Pivot-Tabelle no der resultéierender flaach (normaliséierter) Dësch mam Kommando Home - Zoumaachen a Lueden - Zoumaachen a Lueden an ... (Home - Zoumaachen & Lueden - Zoumaachen & Lueden op ...).
Elo kënnt Dir d'Fähigkeit benotze fir Spalten ze filteren verfügbar an Pivottabellen - déi üblech Scheckzeechen virun den Nimm an Elementer Ënnerschrëft Filteren (Label Filters) or Filtert no Wäert (Wäertfilter):
An natierlech, wann Dir d'Donnéeën ännert, musst Dir eis Ufro an de Resumé mat enger Tastatur Ofkiirzung aktualiséieren Ctrl+Alt+F5 oder Team Donnéeën - Alles erfrëschen (Daten - Alles erfrëschen).
Method 3. Macro an VBA
All déi viregt Methoden, wéi Dir liicht gesitt, sinn net genee Filteren - mir verstoppen d'Säulen net an der ursprénglecher Lëscht, awer bilden eng nei Tabell mat engem bestëmmte Satz vu Spalten aus dem Original. Wann et erfuerderlech ass d'Säulen an de Quelldaten ze filteren (verstoppen), da brauch eng fundamental aner Approche, nämlech e Makro.
Stellt Iech vir, mir wëllen Spalten op der Flucht filteren, wou den Numm vum Manager am Tabellkopf d'Mask entsprécht, déi an der gieler Zell A4 spezifizéiert ass, zum Beispill, fänkt mam Buschtaf "A" un (dat ass, kritt "Anna" an "Arthur" " als Resultat vun).
Wéi an der éischter Method, implementéiere mir fir d'éischt eng Hilfsberäich-Zeil, wou an all Zell eise Critère mat enger Formel iwwerpréift gëtt an déi logesch Wäerter TRUE oder FALSE fir sichtbar a verstoppte Kolonnen ugewise ginn, respektiv:
Da loosse mer en einfachen Makro derbäisetzen. Riets-klickt op de Tab Tab a wielt Kommando Source (Source Code). Kopéiert a paste de folgende VBA Code an d'Fënster déi opmaacht:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Dann Fir All Zell Am Range("D2:O2") Wann Zell = True Dann cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = Richteg Enn Wann nächst Zell Enn Wann Enn Sub Seng Logik ass wéi follegt:
- Am Allgemengen ass dëst en Event Handler Worksheet_Change, dh dëse Macro wäert automatesch op all Ännerung vun enger Zell op der aktueller Blat lafen.
- D'Referenz op déi geännert Zell wäert ëmmer an der Variabel sinn viséieren.
- Als éischt kontrolléiere mir datt de Benotzer genee d'Zelle mam Critère (A4) geännert huet - dëst gëtt vum Bedreiwer gemaach if.
- Da fänkt den Zyklus un Fir all… fir iwwer gro Zellen (D2: O2) ze iteréieren mat TRUE / FALSE Indikatorwäerter fir all Kolonn.
- Wann de Wäert vun der nächster groer Zell TRUE ass (richteg), dann ass d'Kolonn net verstoppt, soss verstoppe mir et (Eegeschafte) verstoppt).
- Dynamesch Arrayfunktiounen aus Office 365: FILTER, SORT, an UNIC
- Pivot Dësch mat Multiline Header benotzt Power Query
- Wat sinn Makroen, wéi se erstellen a benotzen