Naravno, da u ekselu je moguće posao završiti na više načina i ovo je verovatno najjednostavniji.
Napredni filter – izdvajanje unikata
Upotrebićemo listu sa imenima koja se ponavljaju. Na prvoj slici možemo videti razliku između jedinstvenih različitih vrednosti i unikatnih vrednosti. Jedinstvene različite vrednosti se mogu više puta pojavljivati u tabeli, i često su nam potrebne. Tu excel ima ograničenja kod standardne opcije filtriranja da može prikazati samo 5000 jedinstvenih vrednosti.
Da bi izdvojili sve jedinstvene različite vrednosti upotrebićemo napredni filter – Advanced filter koji se nalazi na traci sa alatima Data.
- Prvo selektujte kolonu u kojoj se nalaze podaci koje želite obraditi.
- Pokrenite napredni filter (Data/Advanced u delu alata Sort&filter)
- Izaberite opciju za kopiranje (Copy to another location)
- Izaberite lokaciju gde želite da kopirate vrednosti (Copy to:)
- Štiklirajte opciju Unique records only i poslednje je OK.
Kada ste to završili dobićete rezultat kao na prethodnoj slici u koloni Različite vrednosti.
Sada je ostalo da upotrebom formule COUNTIF prebrojite jedinstvene različite vrednosti u početnoj tabeli i da ih filtrirate opcijom filter.
U konkretnom slučaju formula u koloni broj pojavljivanja izgleda ovako:
=COUNTIF(F5:F9,G5)
Prvi podatak u funciji su sva ona polja koja ćemo prebrojavati, a drugi parametar je uslov ko će ući u konačan zbir. Kada je uslov jednakost sa sadržajem u ćeliji tada je dovoljno navesti samo o kojoj se ćeliji radi. U ovom slučaju je sadržaj u ćeliji G5.[:en]Advanced filter allows you to extract (display) only those data that meet some criteria. Filter and Advanced filter are basic functions that allow filtering. Here we will deal with advanced filtering, and only by a specific situation, when we want to single out unique and unique values.
Of course, in a Excel, it’s possible to end the job in many ways and this is probably the simplest.
Advanced Filter – Uniqueness Separation
We’ll use a list of repetitive names. In the first picture we can see the difference between unique different values and unique values. Unique different values can appear several times in the table, and we often need them. Excel has limitations in standard filtering options that can only display 5000 unique values.
To extract all unique different values, we will use an advanced filter – the Advanced filter located on the Data toolbar.
- First, select the column that contains the information you want to process.
- Start an advanced filter (Data / Advanced in the Sort & Filter toolpart)
- Select the copy option (Copy to another location)
- Select the location where you want to copy the values (Copy to :)
- Select the Unique records only option and the last one is OK.
When finished, you will get the result as in the previous figure in the Various values column.
Now it is left to use the formula COUNTIF to count the unique different values in the initial table and filter them with the filter option.
In the concrete case, the formula in the column shows the number of impressions:
=COUNTIF(F5:F9,G5)
The first data in the function are all those fields that we will count, and the second parameter is the condition that will enter the final sum. When the condition is equal to the content in the cell then it is sufficient to state only which cell is involved. In this case, the content is in the G5 cell.[:]