Cuprins
Dacă nu sunteți un utilizator începător, atunci trebuie să fi observat deja că 99% din tot ce este în Excel este proiectat să funcționeze cu tabele verticale, unde parametrii sau atributele (câmpurile) trec prin coloane și se află informații despre obiecte sau evenimente. în rânduri. Tabele pivot, subtotaluri, copierea formulelor cu un dublu clic – totul este adaptat special pentru acest format de date.
Cu toate acestea, nu există reguli fără excepții și cu o frecvență destul de regulată sunt întrebat ce să fac dacă în lucrare a apărut un tabel cu o orientare semantică orizontală sau un tabel în care rândurile și coloanele au aceeași pondere ca semnificație:
Și dacă Excel încă știe să sorteze orizontal (cu comanda Date – Sortare – Opțiuni – Sortare coloane), atunci situația cu filtrarea este mai proastă – pur și simplu nu există instrumente încorporate pentru filtrarea coloanelor, nu a rândurilor în Excel. Deci, dacă vă confruntați cu o astfel de sarcină, va trebui să veniți cu soluții alternative de diferite grade de complexitate.
Metoda 1. Noua functie FILTER
Dacă utilizați noua versiune de Excel 2021 sau un abonament Excel 365, puteți profita de noua caracteristică introdusă FILTRU (FILTRU), care poate filtra datele sursă nu numai după rânduri, ci și după coloane. Pentru a funcționa, această funcție necesită o matrice-rând unidimensională orizontală auxiliară, în care fiecare valoare (adevărat sau fals) determină dacă afișăm sau, dimpotrivă, ascundem următoarea coloană din tabel.
Să adăugăm următoarea linie deasupra tabelului nostru și să scriem starea fiecărei coloane din ea:
- Să presupunem că vrem întotdeauna să afișăm prima și ultima coloană (anteturi și totaluri), așa că pentru ele în prima și ultima celulă ale matricei setăm valoarea = TRUE.
- Pentru coloanele rămase, conținutul celulelor corespunzătoare va fi o formulă care verifică condiția de care avem nevoie folosind funcții И (ȘI) or OR (SAU). De exemplu, că totalul este în intervalul de la 300 la 500.
După aceea, rămâne doar să utilizați funcția FILTRU pentru a selecta coloanele deasupra cărora matricea noastră auxiliară are o valoare TRUE:
În mod similar, puteți filtra coloanele după o listă dată. În acest caz, funcția va ajuta COUNTIF (COUNTIF), care verifică numărul de apariții ale următoarei nume de coloană din antetul tabelului din lista permisă:
Metoda 2. Tabel pivot în locul celui obișnuit
În prezent, Excel are încorporată filtrare orizontală după coloane numai în tabelele pivot, așa că dacă reușim să convertim tabelul nostru original într-un tabel pivot, putem folosi această funcționalitate încorporată. Pentru a face acest lucru, tabelul nostru sursă trebuie să îndeplinească următoarele condiții:
- au o linie de antet „corectă” pe o linie, fără celule goale și îmbinate – altfel nu va funcționa pentru a construi un tabel pivot;
- nu conțin duplicate în etichetele rândurilor și coloanelor – acestea se vor „restrânge” în rezumat într-o listă de valori unice;
- conțin doar numere din intervalul de valori (la intersecția rândurilor și coloanelor), deoarece tabelul pivot le va aplica cu siguranță un fel de funcție de agregare (sumă, medie etc.) și acest lucru nu va funcționa cu textul
Dacă toate aceste condiții sunt îndeplinite, atunci pentru a construi un tabel pivot care să semene cu tabelul nostru original, acesta (cel original) va trebui extins din tabelul încrucișat într-unul plat (normalizat). Iar cel mai simplu mod de a face acest lucru este cu programul de completare Power Query, un instrument puternic de transformare a datelor, integrat în Excel din 2016.
Acestea sunt:
- Să transformăm tabelul într-o comandă dinamică „inteligentă”. Acasă – Formatați ca tabel (Acasă — Format ca tabel).
- Se încarcă în Power Query cu comanda Date – Din tabel/Interval (Date – Din tabel/Interval).
- Filtrăm linia cu totalurile (rezumatul va avea propriile sale totale).
- Faceți clic dreapta pe primul titlu de coloană și selectați Destrămați alte coloane (Anulați pivotarea altor coloane). Toate coloanele neselectate sunt convertite în două – numele angajatului și valoarea indicatorului acestuia.
- Filtrarea coloanei cu totalurile care au intrat în coloană Atribut.
- Construim un tabel pivot conform tabelului plat (normalizat) rezultat cu comanda Acasă — Închideți și încărcați — Închideți și încărcați în... (Acasă — Închideți și încărcați — Închideți și încărcați în...).
Acum puteți utiliza abilitatea de a filtra coloanele disponibile în tabelele pivot - bifele obișnuite în fața numelor și elementelor Filtre de semnătură (Filtre de etichete) or Filtrează după valoare (Filtre de valori):
Și, desigur, atunci când modificați datele, va trebui să actualizați interogarea noastră și rezumatul cu o comandă rapidă de la tastatură Ctrl+Alt+F5 sau echipa Date – Actualizează toate (Date — Actualizează tot).
Metoda 3. Macro în VBA
Toate metodele anterioare, după cum puteți vedea cu ușurință, nu sunt exact filtre - nu ascundem coloanele din lista originală, ci formăm un nou tabel cu un anumit set de coloane din cel original. Dacă este necesară filtrarea (ascunderea) coloanelor din datele sursă, atunci este necesară o abordare fundamental diferită, și anume o macrocomandă.
Să presupunem că vrem să filtram coloanele din mers în care numele managerului din antetul tabelului satisface masca specificată în celula galbenă A4, de exemplu, începe cu litera „A” (adică obțineți „Anna” și „Arthur " ca urmare).
Ca și în prima metodă, implementăm mai întâi un interval-rând auxiliar, în care în fiecare celulă criteriul nostru va fi verificat printr-o formulă și vor fi afișate valorile logice TRUE sau FALSE pentru coloanele vizibile și respectiv ascunse:
Apoi să adăugăm o macrocomandă simplă. Faceți clic dreapta pe fila foii și selectați comanda Sursă (Cod sursa). Copiați și inserați următorul cod VBA în fereastra care se deschide:
Private Sub Worksheet_Change(ByVal Target As Range) Dacă Target.Address = "$A$4" Atunci pentru fiecare celulă din interval ("D2:O2") Dacă celulă = True Atunci cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Next cell End If End Sub Logica sa este următoarea:
- În general, acesta este un handler de evenimente Foaia de lucru_Modificare, adică această macrocomandă va rula automat la orice modificare a oricărei celule din foaia curentă.
- Referința la celula modificată va fi întotdeauna în variabilă Ţintă.
- În primul rând, verificăm dacă utilizatorul a schimbat exact celula cu criteriul (A4) – acest lucru este făcut de operator if.
- Apoi începe ciclul Pentru fiecare… pentru a itera peste celulele gri (D2:O2) cu valori ale indicatorului TRUE / FALSE pentru fiecare coloană.
- Dacă valoarea următoarei celule gri este TRUE (adevărat), atunci coloana nu este ascunsă, altfel o ascundem (proprietate Ascuns).
- Funcții de matrice dinamică din Office 365: FILTER, SORT și UNIC
- Tabel pivot cu antet cu mai multe linii folosind Power Query
- Ce sunt macrocomenzile, cum să le creați și să le utilizați