Број ставки доступних за филтрирање је ограничен. Екцел не може филтрирати колоне у којима број ставки прелази 999 (не број редова).
За филтрирање ако има више од 999 ставки, користите напредни филтер.
За креирање напредног филтера користићемо функције „ОФФСЕТ“ и „ЦОУНТА“ у програму Мицрософт Екцел.
ЦОУНТА: Он враћа број ћелија које садрже вредности.
Синтакса функције „ЦОУНТА“: = ЦОУНТА (вредност1, вредност2, вредност3….)
Пример: У опсегу А1: А5, ћелије А2, А3 и А5 садрже вредности, а ћелије А1 и А4 су празне. Одаберите ћелију А6 и напишите формулу-
= ЦОУНТА (А1: А5) функција ће се вратити 3
ОФСЕТ: Враћа референцу на опсег који је помакнут за низ редова и колона из другог опсега или ћелије.
Синтакса функције ОФФСЕТ: = ОФФСЕТ (референца, редови, ступци, висина, ширина)
Референца:- Ово је ћелија или опсег из којег желите да се померите.
Редови и колоне за премештање: - Број редова које желите да померите од почетне тачке, а оба ова могу бити позитивна, негативна или нула.
Висина и ширина: - Ово је величина опсега који желите да вратите. Ово је опционо поље.
Узмимо пример за разумевање функције Оффсет у Екцелу.
Имамо податке у распону А1: Д10. Колона А садржи шифру производа, колона Б садржи количину, колона Ц садржи цену производа, а колона Д садржи укупну цену. Морамо да вратимо вредност ћелије Ц5 у ћелији Е2.
Да бисмо постигли жељени резултат, морамо следити доле наведене кораке.
- Одаберите ћелију Е2 и напишите формулу.
- = ОФФСЕТ (А1,4,2,1,1)и притисните Ентер на тастатури.
- Функција ће вратити вредност ћелије Ц5.
У овом примеру морамо да добијемо вредност из ћелије Ц5 до Е2. Наша референтна ћелија је прва ћелија у опсегу који је А1, а Ц5 је 4 реда испод и 2 колоне десно од А1. Дакле, формула је = ОФФСЕТ (А1,4,2,1,1) или = ОФФСЕТ (А1,4,2) (будући да је 1,1 опционална).
Узмимо сада пример за преузимање последње вредности на динамичкој листи.
Имамо имена држава у низу. Сада, ако овој листи додамо још земаља, она би требала бити аутоматски доступна на падајућој листи.
Да бисте припремили напредни филтер, следите доле наведене кораке:-
- Изаберите ћелију Б2.
- Идите на картицу Подаци, изаберите Валидација података из групе Алати за податке.
- Појавиће се дијалошки оквир „Валидација података“. На картици „Поставке“ изаберите „Прилагођено“ са падајуће листе Дозволи.
- Оквир формуле ће бити активиран.
- Напишите формулу у ово поље.
- = ОФФСЕТ (А: А, 1,0, ЦОУНТА (А: А) -1,1).
- Притисните ОК.
- У овој фази, последња ажурирана ћелија је А11.
- Да бисте проверили да ли потврда података ради исправно, додајте назив града у ћелију А12.
Чим додате унос у А12, он ће бити додат на падајућу листу.
Ово је начин на који можемо додати више уноса од 999 ставки у Мицрософт Екцел.