Excel uzlabotais filtrs - pilnīga rokasgrāmata ar piemēriem

Skatieties video - Excel uzlabotais filtrs

Excel uzlabotais filtrs ir viena no visvairāk nenovērtētajām un nepietiekami izmantotajām funkcijām, ar kurām esmu saskārusies.

Ja strādājat ar Excel, esmu pārliecināts, ka esat izmantojis (vai vismaz dzirdējis par parasto Excel filtru). Tas ātri filtrē datu kopu, pamatojoties uz atlasi, norādīto tekstu, numuru vai citiem šādiem kritērijiem.

Šajā rokasgrāmatā es parādīšu dažas interesantas lietas, ko varat darīt, izmantojot Excel uzlaboto filtru.

Bet vispirms … Kas ir Excel uzlabotais filtrs?

Excel uzlabotais filtrs - kā norāda nosaukums - ir parastā filtra uzlabotā versija. Varat to izmantot, ja datu kopas filtrēšanai jāizmanto sarežģītāki kritēriji.

Šeit ir dažas atšķirības starp parasto filtru un papildu filtru:

• Lai gan parastais datu filtrs filtrēs esošo datu kopu, varat izmantot Excel uzlaboto filtru, lai iegūtu datu kopu arī citā vietā.
• Excel uzlabotais filtrs ļauj izmantot sarežģītus kritērijus. Piemēram, ja jums ir pārdošanas dati, varat filtrēt datus pēc kritērija, kur pārdošanas pārstāvis ir Bobs un reģions ir ziemeļi vai dienvidi (kā to izdarīt, mēs redzēsim piemēros). Biroja atbalstam ir labs skaidrojums par to.
• Varat izmantot Excel papildu filtru, lai no jūsu datiem iegūtu unikālus ierakstus (vairāk par to sekundē).

EXCEL ADVANCED FILTER (piemēri)

Tagad apskatīsim dažus piemērus par papildu filtra izmantošanu programmā Excel.

1. piemērs - unikāla saraksta izvilkšana

Varat izmantot Excel uzlaboto filtru, lai ātri iegūtu unikālus ierakstus no datu kopas (vai, citiem vārdiem sakot, noņemtu dublikātus).

Programmā Excel 2007 un jaunākās versijās ir iespēja noņemt datu kopas dublikātus. Bet tas maina jūsu esošo datu kopu. Lai sākotnējie dati būtu neskarti, jums jāizveido datu kopija un pēc tam jāizmanto opcija Noņemt dublikātus. Excel papildu filtrs ļaus jums izvēlēties atrašanās vietu, lai iegūtu unikālu sarakstu.

Apskatīsim, kā izmantot uzlabotus filtrus, lai iegūtu unikālu sarakstu.

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk:

Kā redzat, šajā datu kopā ir ierakstu dublikāti (iezīmēti oranžā krāsā). To cēlonis var būt kļūda datu ievadīšanā vai datu apkopošanas rezultāts.

Šādā gadījumā varat izmantot Excel uzlaboto filtru rīku, lai ātri iegūtu visu unikālo ierakstu sarakstu citā vietā (lai sākotnējie dati paliktu neskarti).

Lai iegūtu visus unikālos ierakstus, veiciet tālāk norādītās darbības.

• Atlasiet visu datu kopu (ieskaitot galvenes).
• Atveriet cilni Dati -> Kārtot un filtrēt -> Papildu. (Varat arī izmantot īsinājumtaustiņu - Alt + A + Q). Tiks atvērts dialoglodziņš Papildu filtrs.
• Dialoglodziņā Papildu filtrs izmantojiet šādu informāciju:
  • Darbība: Atlasiet opciju Kopēt uz citu atrašanās vietu. Tas ļaus jums norādīt vietu, kur var iegūt unikālo ierakstu sarakstu.
  • Saraksta diapazons: Pārliecinieties, vai tas attiecas uz datu kopu, no kuras vēlaties atrast unikālus ierakstus. Tāpat pārliecinieties, vai datu kopā ir iekļautas galvenes.
  • Kritēriju diapazons: Atstājiet šo tukšu.
  • Kopēt uz: Norādiet šūnas adresi, kurā vēlaties iegūt unikālo ierakstu sarakstu.
  • Kopēt tikai unikālus ierakstus: Pārbaudiet šo opciju.
• Noklikšķiniet uz Labi.

Tas uzreiz parādīs visu unikālo ierakstu sarakstu.

Uzmanību: Ja izmantojat papildu filtru, lai iegūtu unikālo sarakstu, pārliecinieties, vai esat atlasījis arī galveni. Ja to nedarīsit, tā par galveno šūnu uzskatīs pirmo šūnu.

2. piemērs - Kritēriju izmantošana Excel uzlabotajā filtrā

Unikālu ierakstu iegūšana ir viena no daudzajām lietām, ko varat darīt, izmantojot Excel uzlaboto filtru.

Tās galvenā lietderība ir spēja ļaut datu filtrēšanai izmantot sarežģītus kritērijus.

Lūk, ko es domāju ar sarežģītiem kritērijiem. Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties ātri iegūt visus ierakstus, kuru pārdošanas apjoms pārsniedz 5000 un reģions ir ASV.

Lūk, kā varat izmantot Excel papildu filtru, lai filtrētu ierakstus, pamatojoties uz norādītajiem kritērijiem:

• Pirmais solis, izmantojot Excel papildu filtru ar sarežģītiem kritērijiem, ir norādīt kritērijus. Lai to izdarītu, nokopējiet galvenes un ielīmējiet to kaut kur darblapā.
• Norādiet kritērijus, pēc kuriem vēlaties filtrēt datus. Tā kā šajā piemērā mēs vēlamies iegūt visus ASV ierakstus, kuru pārdošanas apjoms pārsniedz 5000, ievadiet “US” šūnā zem reģiona un> 5000 šūnā zem pārdošanas. Tagad tas tiks izmantots kā ievade uzlabotajā filtrā, lai iegūtu filtrētos datus (kā parādīts nākamajās darbībās).
• Atlasiet visu datu kopu (ieskaitot galvenes).
• Atveriet cilni Dati -> Kārtot un filtrēt -> Papildu. Tiks atvērts dialoglodziņš Papildu filtrs.
• Dialoglodziņā Papildu filtrs izmantojiet šādu informāciju:
  • Darbība: Atlasiet opciju Kopēt uz citu atrašanās vietu. Tas ļaus jums norādīt vietu, kur var iegūt unikālo ierakstu sarakstu.
  • Saraksta diapazons: Pārliecinieties, vai tas attiecas uz datu kopu, no kuras vēlaties atrast unikālus ierakstus. Tāpat pārliecinieties, vai datu kopā ir iekļautas galvenes.
  • Kritēriju diapazons: Norādiet kritērijus, kurus mēs izveidojām, veicot iepriekš minētās darbības. Šajā piemērā tas būtu F1: I3.
  • Kopēt uz: Norādiet šūnas adresi, kurā vēlaties iegūt unikālo ierakstu sarakstu.
  • Kopēt tikai unikālus ierakstus: Pārbaudiet šo opciju.
• Noklikšķiniet uz Labi.

Tas uzreiz sniegtu visus ierakstus, kur reģions ir ASV un pārdošanas apjoms pārsniedz 5000.

Iepriekš minētais piemērs ir gadījums, kad filtrēšana tiek veikta, pamatojoties uz diviem kritērijiem (ASV un pārdošanas apjoms pārsniedz 5000).

Excel uzlabotais filtrs ļauj izveidot daudz dažādu kritēriju kombināciju.

Šeit ir daži piemēri, kā izveidot šos filtrus.

AND kritēriju izmantošana

Ja vēlaties izmantot AND kritērijus, tas jānorāda zem galvenes.

Piemēram:

• Lai filtrētu ierakstus, ja reģions ir ASV, UN pārdošanas pārstāvis ir Džo.
• Lai filtrētu ierakstus, ja reģions ir ASV UN pārdošanas vērtība ir lielāka par 5000.
• Kad reģions ir ASV UN pārdošanas apjomi tiek reģistrēti pēc 31.03.2017.

Izmantojot VAI kritērijus

Ja vēlaties izmantot VAI kritērijus, jums ir jānorāda kritēriji tajā pašā slejā.

Piemēram:

• Lai filtrētu ierakstus, ja reģions ir ASV VAI reģions ir Āzija.
• Lai filtrētu ierakstus, ja pārdošanas pārstāvis ir Bobs VAI Marta.

Līdz šim jūs noteikti sapratāt, ka tad, kad kritēriji ir vienā rindā, tas ir UN kritērijiem, un, ja mums tas ir dažādās rindās, tas ir an VAI kritērijiem.

3. piemērs - WILDCARD rakstzīmju izmantošana uzlabotajā filtrā programmā Excel

Excel uzlabotais filtrs arī ļauj izmantot aizstājējzīmes, veidojot kritērijus.

Programmā Excel ir trīs aizstājējzīmes:

1. * (zvaigznīte) - Tas apzīmē jebkuru rakstzīmju skaitu. Piemēram, ex* var nozīmēt izcilību, izcilību, piemēru, ekspertu utt.
2. ? (jautājuma zīme) - Tas attēlo vienu rakstzīmi. Piemēram, Tr? Mp varētu nozīmēt Trampu vai Trampu.
3. ~ (tilde) - To izmanto, lai tekstā identificētu aizstājējzīmi (~, *,?).

Tagad redzēsim, kā mēs varam izmantot šīs aizstājējzīmes, lai veiktu papildu filtrēšanu programmā Excel.

• Lai filtrētu ierakstus, kur pārdošanas pārstāvja vārds sākas ar J.

Ņemiet vērā, ka * apzīmē jebkuru rakstzīmju skaitu. Tātad jebkurš pārstāvis ar nosaukumu, kas sākas ar J, tiktu filtrēts pēc šiem kritērijiem.

Līdzīgi varat izmantot arī pārējās divas aizstājējzīmes.

Piezīme. Ja izmantojat Office 365, jums jāpārbauda funkcija FILTER. Tas var paveikt daudzas lietas, ko uzlabotais filtrs var izdarīt ar vienkāršu formulu.

PIEZĪME:

1. Atcerieties, ka kritēriju galvenēm jābūt tieši tādām pašām kā datu kopai.
2. Uzlaboto filtrēšanu nevar atsaukt, kopējot uz citām vietām.