Jste zde: Úvodní stránka » excel » articles-in-english » advanced-filter-excel

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak na Excel - naučte se efektivně využívat Excel - nejoblíbenější (Hodnocení 97%, přes 13.000 studentů, + 750 pozitivních zpětných vazeb) - Získat kurz
MaxiKurz Excel - získejte lepší práci, více peněz, staňte se nepostradatelní (přes 6 hodin videí, 160 lekcí) - Získat kurz
G tabulky... - pokud nemáte rádi Excel využijte kurz na Google-tabulky

Advanced filter - Excel

How to practically at Advanced filter Excel

Advanced filter in Czech language: Rozšířený filtr - Excel - česká verze

Introduction to advanced filter

Advanced Filter for effectively filter (select) data. For various analyzes, search, selection of data. If you can do using basic filter, is a little more complicated.

Theme is divided into the chapters:

Theory
- Advanced filter in Ribbon
- Dialog: Advanced Filter - Description
- Operators and Special Characters
Source data
Example
- Filter all data
- Show...
Downloads
Related topics

Example

Advanced filter in Ribbon

Excel 2013/2010

On the Ribbon Data click at Advanced. You will receive a dialog box: Advanced Filter.

Dialog: Advanced Filter

The dialog Advanced Filter

Actions - where copy
- Filter the list, in-place - copy to the some Table
- Copy to another location - I recommend using the result of the copy in a separate table
List range: - Table of data which are Filtered
Criteria range: - enter the criteria (the value that we find)
Copy to: - where you want copy to another location
Unique records only - if you do not see duplicate records

Note: Inserting the area, including column names.

Operators and Special Characters

For filters can be used operators (marks) and special characters.

Operators

= equals
< less
> more
<= less equals
>= more equals
Not equal

Special Characters

* - few characters (even zero)
? - one character
~ - search for special characters in the text as *,?,~

Note: If you need to filter the exact name must be used:


="=Text"

Filters only the word Text


Text

Filters word Text, Texty, Text1, ...

Source data

In the practical examples will use the following table.

ID	Name	Date	Season
1	Pavel	1.2.2015	spring
2	John	2.2.2015	summer
3	Jane	3.2.2015	autumn
4	Jone	4.2.2015	winter
5	Ivone	5.2.2015	winter
6	Pivone	6.2.2015	winter
6	Pivone	6.2.2015	winter
8	Jane	8.2.2015	winter

Praktical Example

List of practical examples:

01 Filter all data
02 Filter one parameter
03 Filter more parameters (one column)
04 Filter range in parameters (one column)
05 Filter range more parameters (one column)
06 Filter text (one column)
07 Filter only said text (one column)
08 Filter text which contains value (one column)
09 Filter find special Characters in text (one column)
10 Filter value (more column)

01 Filter all data

Filter all data from ID

Example 01

ID

02 Filter one parameter

Filter one parameter from column ID

Example 02


ID 
1

03 Filter more parameters (one column)

Filter more parameters from column ID

Example 03


ID 
1
3
5

04 Filter range in parameters (one column)

Filter range in parameters from column ID. In Example all values larger than 5.

Example 04


ID 
>5

05 Filter range more parameters (one column)

Filter more parameters from column ID. In Example all values larger than 2 or values less equal 6.

Example 05


ID	ID 
>2	<=6

06 Filter text (one column)

Filter text in column Name. In Example all values whith "Pivone". Caution filter find also Pivone, Pivonek, ...

Example 06


Name 
Pivone

07 Filter only said text (one column)

Filter text in column Name. In Example all values whith "Pivone". Find only "Pivone".

Example 07


Name 
="=Pivone"

08 Filter text which contains value (one column)

Filter text in column Name. In Example all values whith "ivone". Find Pivone, Ivone, Pivonek .

Example 08


Name 
*Ivone*

09 Filter find special Characters in text (one column)

Filter text in column Season. Find *

Note: The source table is modified.

Example 09a 09b


Season 
*~*

or

Season 
winter~*

10 Filter value (more column)

Filter text in column Name. In Example all values whith "ivone". Find Pivone, Ivone, Pivonek .

Example 10


ID	Season
6	winter

Downloads

File Advanced filter practically example Excel free download.

Donate

For further development of this web site.

Thank you for donations.

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, PowerPoint) se věnuji od roku 2000 (od 2004 ne této doméně) - Roku 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici také na on-line videích pro SEDUO. Školím a konzultuji, učím na MUNI. Hlavně tvořím tento web. Je zde k dispozici přes 1.000 návodu, tipů a triků včetně stovek různých šablon, sešitů.