Filtru avansat și ceva magie

Pentru marea majoritate a utilizatorilor Excel, atunci când le vine în cap cuvântul „filtrare de date”, doar filtrul clasic obișnuit din filă Date – Filtru (Date — Filtru):

Un astfel de filtru este un lucru familiar, fără îndoială, și pentru majoritatea cazurilor va fi bine. Cu toate acestea, există situații în care trebuie să filtrați după un număr mare de condiții complexe în mai multe coloane simultan. Filtrul obișnuit de aici nu este foarte convenabil și vreau ceva mai puternic. Un astfel de instrument ar putea fi filtru avansat, mai ales cu puțin „terminare cu pilă” (după tradiție).

Bază

Pentru a începe, inserați câteva linii goale deasupra tabelului de date și copiați acolo antetul tabelului - acesta va fi un interval cu condiții (evidențiate cu galben pentru claritate):

Trebuie să existe cel puțin o linie goală între celulele galbene și tabelul original.

În celulele galbene trebuie să introduceți criteriile (condițiile), după care se va efectua apoi filtrarea. De exemplu, dacă trebuie să selectați banane în „Auchan” din Moscova în trimestrul III, atunci condițiile vor arăta astfel:

Pentru a filtra, selectați orice celulă din intervalul cu datele sursă, deschideți fila Date Și faceți clic pe În plus, (Date - Avansat). În fereastra care se deschide ar trebui să fie deja introdus automat un interval cu date și nu va trebui decât să specificăm intervalul de condiții, adică A1:I2:

Vă rugăm să rețineți că intervalul de condiții nu poate fi alocat „cu o marjă”, adică nu puteți selecta linii galbene goale suplimentare, deoarece o celulă goală din intervalul de condiții este percepută de Excel ca absența unui criteriu, iar un întreg gol linie ca o solicitare de a afișa toate datele fără discernământ.

Intrerupator Copiați rezultatul într-o altă locație vă va permite să filtrați lista nu chiar acolo pe această foaie (ca în cazul unui filtru obișnuit), ci să descărcați rândurile selectate într-un alt interval, care apoi va trebui specificat în câmp Pune rezultatul în interval. În acest caz, nu folosim această funcție, plecăm Filtrați lista la locul lor și faceți clic OK. Rândurile selectate vor fi afișate pe foaie:

Adăugarea unei macrocomenzi

„Ei bine, unde este confortul aici?” intrebi si vei avea dreptate. Nu numai că trebuie să introduceți condiții în celulele galbene cu mâinile, ci și să deschideți o casetă de dialog, să introduceți intervale acolo, să apăsați OK. Trist, sunt de acord! Dar „totul se schimbă când vin ©” – macro-uri!

Lucrul cu un filtru avansat poate fi foarte accelerat și simplificat folosind o macrocomandă simplă care va rula automat filtrul avansat atunci când sunt introduse condiții, adică schimbarea oricărei celule galbene. Faceți clic dreapta pe fila foii curente și selectați comanda Text sursă (Cod sursa). În fereastra care se deschide, copiați și lipiți următorul cod:

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) Nu este nimic, atunci în eroare Reluați următorul ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Range("A1").CurrentRegion End If End Sub  

Această procedură va rula automat atunci când orice celulă din foaia de lucru curentă este schimbată. Dacă adresa celulei modificate se încadrează în intervalul galben (A2:I5), atunci această macrocomandă elimină toate filtrele (dacă există) și reaplică filtrul extins la tabelul de date sursă începând cu A7, adică totul va fi filtrat instantaneu, imediat după introducerea următoarei condiții:

Deci totul este mult mai bine, nu? 🙂

Implementarea de interogări complexe

Acum că totul este filtrat din mers, putem aprofunda puțin în nuanțe și putem dezasambla mecanismele interogărilor mai complexe în filtrul avansat. Pe lângă introducerea potrivirilor exacte, puteți utiliza diferite caractere joker (* și ?) și semne de inegalitate matematică într-o serie de condiții pentru a implementa o căutare aproximativă. Lipsa caracterului nu contează. Pentru claritate, am rezumat toate opțiunile posibile într-un tabel:

Puncte subtile:

• Semnul * înseamnă orice număr de caractere și ? – orice personaj.
• Logica în procesarea interogărilor text și numerice este ușor diferită. Deci, de exemplu, o celulă de condiție cu numărul 5 nu înseamnă să căutați toate numerele care încep cu cinci, dar o celulă de condiție cu litera B este egală cu B*, adică va căuta orice text care începe cu litera B.
• Dacă interogarea text nu începe cu semnul =, atunci puteți pune mental * la sfârșit.
• Datele trebuie introduse în formatul SUA lună-zi-an și printr-o fracțiune (chiar dacă aveți setări Excel și regionale).

Conective logice ȘI-SAU

Condițiile scrise în celule diferite, dar pe aceeași linie, sunt considerate a fi interconectate de un operator logic И (ȘI):

Acestea. filtru banane pentru mine in trimestrul trei, tocmai la Moscova si in acelasi timp de la Auchan.

Dacă trebuie să legați condițiile cu un operator logic OR (SAU), atunci trebuie doar introduse în rânduri diferite. De exemplu, dacă trebuie să găsim toate comenzile managerului Volina pentru piersici de Moscova și toate comenzile pentru ceapă în trimestrul al treilea la Samara, atunci acest lucru poate fi specificat într-o serie de condiții, după cum urmează:

Dacă trebuie să impuneți două sau mai multe condiții pe o coloană, atunci puteți pur și simplu să duplicați antetul coloanei în intervalul de criterii și să introduceți sub el al doilea, al treilea etc. termeni. Deci, de exemplu, puteți selecta toate tranzacțiile din martie până în mai:

În general, după „terminarea cu un fișier”, un filtru avansat iese a fi un instrument destul de decent, în unele locuri nu mai rău decât un autofiltru clasic.

• Superfiltru pe macrocomenzi
• Ce sunt macrocomenzile, unde și cum se inserează codul macro în Visual Basic
• Tabelele inteligente în Microsoft Excel