Filtrarea devine mult mai ușoară cu funcții noi în Excel 365

Din toamna anului 2018 am urmărit rapoarte despre noile funcții din matrice dinamică Excel. Cu toate acestea, până acum acestea ar putea fi utilizate doar de către utilizatorii cu canalul de actualizare „Insider” și „Direcționare lunară”. Funcțiile precum UNIQUE, FILTER, SORT și SORT AFTER sunt acum disponibile și în canalul de actualizare „Lunar”.
Deoarece sortarea și filtrarea listelor este una dintre sarcinile obișnuite în Excel, am încercat mai întâi funcțiile FILTER și SORT. Pot spune un lucru în avans: sunt uimit de cât de ușor este de operat și sunt încântat de numeroasele posibilități. Iată un prim exemplu.

Previzualizarea rezultatului finalizat cu FILTER

În viitor, va fi posibil prin simpla apăsare a unui buton: afișați și imprimați datele filtrate pe o foaie separată

Cei care trebuie să își filtreze datele cunosc frecvent problema:

Mai întâi filtrul este configurat.
Apoi criteriul de filtrare este setat.
Baza de date filtrată este apoi copiată pe o foaie separată.
În cele din urmă, se adaugă un antet și rezultatul este tipărit.

Cu noua funcție FILTRU acest lucru este mult mai ușor și mai rapid:

Doar selectați criteriul de filtrare dorit într-o singură celulă, de exemplu un produs, un client sau o regiune.
Funcția FILTER generează imediat o listă a înregistrărilor de date aplicabile. TERMINAT.

Iată cum funcționează: Utilizați FILTER pentru a lista doar anumite înregistrări de date

Următoarea figură arată setul de date: Vânzări înregistrate după dată, grup de articole, regiune, client, reprezentant de vânzări (ADM) și vânzări nete.

De asemenea, schimb lista cu datele de vânzări Ctrl + T într-o masă „inteligentă”. Prin filă Unelte de masă Ii dau numele tbl_ vânzări la. Această abordare are două avantaje:

Scrierea formulelor devine mai ușoară, deoarece nu trebuie să lucrez cu referințe de celule criptice.
În plus, tabelul meu este ajustat automat la introducerea și ștergerea înregistrărilor de date, iar evaluarea mea accesează în mod fiabil baza de date curentă.

Extrageți din baza de date care urmează să fie filtrată

Vreau doar să afișez înregistrările pentru clientul TerraMeda. Așa o fac:

Trec la foaia de lucru evaluare și introduceți numele clientului în celula E3, deci aici TerraMeda.
În rândul 4 al bazei de date, copiez titlurile coloanelor din coloanele B în G.
Înregistrările de date pentru clientul selectat ar trebui apoi să fie listate pe foaia de evaluare de la rândul 6. Așa că marchez celula B6 și încep formula cu = FILTRU (
Această funcție are trei argumente matrice, încuie și dacă_blank.
Pentru primul argument matrice mutați mouse-ul în colțul din stânga sus al tabelului, așa cum se arată mai jos. Șoarecele se transformă într-o săgeată neagră înclinată. Un singur clic este suficient și toate înregistrările de date sunt marcate.

Pur și simplu marcați baza de date cu săgeata unghiulară

După introducerea unui punct și virgulă, urmează al doilea argument: Excel ar trebui să verifice dacă numele clientului apare în celula E3 din coloana cu clienții. Marcez coloana client care urmează să fie căutată făcând simplu clic deasupra cuvântului din baza de date client faceți clic (mouse-ul este acum o săgeată verticală neagră). Apoi introduc un semn egal și dau clic pe celula de comparație E3.
Formula este până acum = FILTER (tbl_Umsatz; tbl_Umsatz [client] = evaluare! E3
Nu am nevoie de al treilea argument aici. Deci închid formula cu o paranteză și Enter.
Rezultatul arată acum aproape ca în Figura 1. Numai formatele numerice pentru coloană Data și net mai trebuie ajustate. Fac asta în fila Acasă din grupul Număr folosind lista formatelor predefinite. Acolo aleg Data, scurtă precum valută.

Formula FILTER finită

Acesta este modul în care devine mai clar: Sortează rezultatele filtrate

Este adesea foarte util să afișați rezultatele filtrate într-o anumită comandă, de exemplu sortate după articol, regiune sau vânzări.
Datorită noilor funcții matrice, acest lucru se realizează cu doar câteva clicuri. Pentru a face acest lucru, încorporez funcția FILTER pe care tocmai am creat-o în funcția SORT după cum urmează.

Am pus cursorul în fața cuvântului FILTRU din bara de formule.
cred Sor A. Excel sugerează funcția SORT. Accept această sugestie apăsând tasta filă-buton.
Voi pune cursorul la sfârșitul formulei și voi introduce un punct și virgulă acolo.
Acum trebuie să argumentez Sortați indexul introduceți numărul coloanei conform căruia lista filtrată urmează să fie sortată. Vreau să sortez după vânzări, așa că am pus 6, deoarece coloana 6 a matricei este vânzările.
După încă un punct și virgulă, se decide dacă se sortează în ordine crescătoare sau descendentă. Aș dori să am cele mai mari vânzări în partea de sus, așa că alegeți opțiunea -1.

Pentru sortare în ordine descrescătoare -1 alege

Prindeți un posibil mesaj de eroare cu IFERROR

Analiza mea de date filtrate și sortate are încă un mic defect. Dacă celula E3 din foaia de evaluare este goală, adică dacă nu este specificat niciun criteriu de filtrare, noul mesaj de eroare apare în celula B6 #LĂMÂIE VERDE!.

Noul mesaj de eroare #KALK! în celula B6

O evit prin ...

plasați cursorul în fața cuvântului SORT în bara de formule,
WENNF și tastați funcția din lista de sugestii în Excel IFERROR prin apăsarea butonului filă-Butonul Aplicare,
plasați cursorul la sfârșitul formulei și introduceți un punct și virgulă și două ghilimele acolo,
apoi închideți formula cu o paranteză și introduceți.

Formula terminată arată astfel:

Interceptați mesajele de eroare în mod specific cu funcția IFERROR

perspectivă

Ar fi mai elegant dacă aș putea selecta în mod convenabil criteriul de filtrare în celula E3 printr-o listă derulantă. În următoarea postare voi descrie cum pot face acest lucru verificând datele și folosind noua funcție UNIQUE.

Și dacă noile funcții nu sunt (încă) disponibile?

Noile funcții din matrice dinamică sunt disponibile numai în Excel 365.
În prezent, acestea sunt disponibile numai dacă canalul de actualizare este setat la „Insider” sau „Direcționat lunar” sau „Lunar”.
Toți cei care au canalul de actualizare "de două ori pe an" trebuie să aibă răbdare.
Ce canal de actualizare este setat poate fi determinat prin secvența de comandă fişier >cont afla.

Următoarea figură arată informațiile afișate pentru canalul de actualizare »Lunar«. Numărul versiunii este decisiv aici 1911 (adică noiembrie 2019).

Informațiile despre canalul de actualizare și versiunea marcate cu galben sunt decisive