Filtrovanie je oveľa jednoduchšie vďaka novým funkciám v Exceli 365

Filtrovanie je oveľa jednoduchšie vďaka novým funkciám v Exceli 365

Od jesene 2018 sledujem správy o nových funkciách dynamického poľa Excelu. Doteraz ich však mohli používať iba používatelia s aktualizačným kanálom „Insider“ a „Mesačne zacielené“. V kanáli mesačných aktualizácií sú teraz k dispozícii aj funkcie ako UNIQUE, FILTER, SORT a SORT AFTER.
Keďže triedenie a filtrovanie zoznamov je jednou z bežných úloh v Exceli, najskôr som vyskúšal funkcie FILTER a Triedenie. Vopred môžem povedať jednu vec: Som prekvapený, aké ľahké je ovládanie, a som nadšený z mnohých možností. Tu je prvý príklad.

jednoduchšie

Ukážka hotového výsledku pomocou filtra FILTER

V budúcnosti to bude možné stlačením tlačidla: zobraziť a vytlačiť filtrované údaje na samostatnom hárku

Tí, ktorí musia často filtrovať svoje údaje, poznajú problém:

  • Najprv je nastavený filter.
  • Potom je nastavené kritérium filtra.
  • Filtrovaná databáza sa potom skopíruje na samostatný hárok.
  • Nakoniec sa pridá nadpis a výsledok sa vytlačí.

S novou funkciou FILTER je to oveľa jednoduchšie a rýchlejšie:

  • Stačí zvoliť požadované kritérium filtra v jednej bunke, napríklad produkt, zákazník alebo región.
  • Funkcia FILTER okamžite vygeneruje zoznam príslušných dátových záznamov. DOKONČENÉ.

Funguje to takto: Pomocou filtra FILTER môžete zobraziť iba určité záznamy údajov

Nasledujúci obrázok zobrazuje množinu údajov: Tržby, ktoré sa zaznamenávajú podľa dátumu, skupiny položiek, regiónu, zákazníka, obchodného zástupcu (ADM) a čistého predaja.

Tiež mením zoznam s údajmi o predaji Ctrl + T do „inteligentného“ stola. Prostredníctvom karty Stolové náradie Dám jej meno tbl_ tržby do. Tento prístup má dve výhody:

  • Písanie vzorcov je jednoduchšie, pretože nemusím pracovať s odkazmi na kryptické bunky.
  • Moja tabuľka sa navyše automaticky upravuje pri zadávaní a mazaní záznamov údajov a moje hodnotenie spoľahlivo pristupuje k aktuálnej databáze.

Výpis z databázy, ktorá sa má filtrovať

Chcem iba zobraziť záznamy pre zákazníka TerraMeda. Takto to robím:

  • Prepnem na list hodnotenie a do bunky E3 zadajte meno zákazníka, takže tu TerraMeda.
  • V riadku 4 databázy skopírujem hlavičky stĺpcov v stĺpcoch B do G.
  • Dátové záznamy za vybraného zákazníka by potom mali byť uvedené na hodnotiacom hárku z riadku 6. Označím teda bunku B6 a začnem svoj vzorec = FILTER (
    Táto funkcia má tri argumenty matrica, zamknúť a if_blank.
  • Na prvý argument matrica posuňte myš do ľavého horného rohu tabuľky, ako je to znázornené nižšie. Myš sa zmení na šikmú čiernu šípku. Stačí jedno kliknutie a všetky dátové záznamy sú označené.

Jednoducho označte databázu pomocou šikmej šípky

  • Po zadaní bodkočiarky teraz nasleduje druhý argument: Excel by mal skontrolovať, či sa meno zákazníka zobrazuje v bunke E3 v stĺpci so zákazníkmi. Jednoduchým kliknutím nad slovo v databáze označím stĺpec zákazníka, ktorý má byť prehľadaný zákazník kliknite (myš je teraz zvislá čierna šípka). Potom zadám znak rovnosti a kliknem na porovnávaciu bunku E3.
  • Vzorec je zatiaľ = FILTER (tbl_Umsatz; tbl_Umsatz [zákazník] = hodnotenie! E3
  • Tretí argument tu nepotrebujem. Takže zavriem vzorec zátvorkou a stlačte Enter.
  • Výsledok teraz vyzerá takmer ako na obrázku 1. Iba formáty čísel pre stĺpec dátum a sieť ešte treba upraviť. Robím to na karte Domov v skupine Číslo pomocou zoznamu preddefinovaných formátov. Tam si vyberiem Dátum, krátky ako napr mena.

Hotový vzorec FILTER

Takto je to jasnejšie: Zoradiť filtrované výsledky

Filtrované výsledky je často veľmi užitočné zobraziť v určitom poradí, napríklad zoradené podľa položky, regiónu alebo predaja.
Vďaka novým funkciám poľa sa tak deje iba niekoľkými kliknutiami. Za týmto účelom začleňujem funkciu FILTER, ktorú som práve vytvoril, do funkcie SORT takto.

  • Kurzor som umiestnil pred slovo FILTER do riadku vzorcov.
  • hádam Prepáč a. Excel navrhuje funkciu SORT. Tento návrh prijímam stlačením tab-tlačidlo.
  • Kurzor umiestnim na koniec vzorca a zadám tam bodkočiarku.
  • Teraz musím pre argument Zoradiť index zadajte číslo stĺpca, podľa ktorého sa má zoradiť filtrovaný zoznam. Chcem zoradiť podľa predaja, takže zadám 6, pretože v stĺpci 6 matice sú tržby.
  • Za ďalším bodkočiarkou sa rozhodne, či sa má radiť vzostupne alebo zostupne. Chcel by som mať najvyššie tržby navrchu, preto zvoľte možnosť -1.

Na triedenie v zostupnom poradí -1 vybrať

Chytiť možné chybové hlásenie pomocou IFERROR

Moja filtrovaná a triedená analýza dát má stále malú chybu. Ak je bunka E3 v hodnotiacom hárku prázdna, tj. Ak nie je zadané žiadne kritérium filtra, v bunke B6 sa zobrazí nové chybové hlásenie. # LIME!.

Nové chybové hlásenie #KALK! v bunke B6

Vyhýbam sa tomu tým, že ...

  • umiestnite kurzor pred slovo SORT do riadku vzorcov,
  • WENNF a zadajte funkciu zo zoznamu návrhov v programe Excel IFERROR stlačením tab-Tlačidlo Použiť,
  • umiestnite kurzor na koniec vzorca a zadajte tam bodkočiarku a dve úvodzovky,
  • potom vzorec zatvorte v zátvorke a zadajte.

Hotový vzorec vyzerá takto:

Zachytávajte chybové správy konkrétne pomocou funkcie IFERROR

výhľad

Bolo by to elegantnejšie, keby som mohol pohodlne zvoliť kritérium filtra v bunke E3 pomocou rozbaľovacieho zoznamu. V ďalšom príspevku popíšem, ako to môžem urobiť kontrolou údajov a použitím novej funkcie UNIQUE.

A ak nové funkcie nie sú (zatiaľ) k dispozícii?

  • Nové funkcie dynamického poľa sú k dispozícii iba v Exceli 365.
  • Momentálne sú k dispozícii, iba ak je aktualizačný kanál nastavený na možnosť „Insider“ alebo „Mesačne zacielené“ alebo „Mesačne“.
  • Každý, kto má aktualizačný kanál »dvakrát ročne«, musí byť trpezlivý.
  • Ktorý aktualizačný kanál je nastavený, je možné určiť pomocou postupnosti príkazov spis >účet zistiť.

Nasledujúci obrázok zobrazuje informácie, ktoré sa zobrazujú pre aktualizačný kanál »Mesačne«. Tu je rozhodujúce číslo verzie 1911 (t. j. november 2019).

Rozhodujúce sú informácie o aktualizačnom kanáli a verzii vyznačené žltou farbou