Броят на наличните елементи за филтриране е ограничен. Excel не може да филтрира колони, в които броят на елементите надвишава 999 (не броят на редовете).
За да филтрирате, когато има повече от 999 елемента, използвайте разширен филтър.
За да създадем разширен филтър, ще използваме функциите „OFFSET“ и „COUNTA“ в Microsoft Excel.
COUNTA: Той връща броя на клетките, които съдържат стойности.
Синтаксис на функцията „COUNTA“: = COUNTA (стойност1, стойност2, стойност3….)
Пример: В диапазон A1: A5 клетки A2, A3 и A5 съдържат стойностите, а клетките A1 и A4 са празни. Изберете клетката A6 и напишете формулата-
= COUNTA (A1: A5) функцията ще се върне 3
ИЗМЕСТВАНЕ: Той връща препратка към диапазон, който е изместен на редица редове и колони от друг диапазон или клетка.
Синтаксис на функцията OFFSET: = OFFSET (справка, редове, cols, височина, ширина)
Справка:- Това е клетката или диапазонът, от който искате да изместите.
Редове и колони за преместване: - Броят редове, които искате да преместите от началната точка, и двата могат да бъдат положителни, отрицателни или нула.
Височина и ширина: - Това е размерът на диапазона, който искате да върнете. Това е поле по избор.
Нека вземем пример, за да разберем функцията Offset в Excel.
Имаме данни в диапазон A1: D10. Колона А съдържа код на продукта, колона Б съдържа количество, колона С съдържа цена на продукт, а колона D съдържа обща цена. Трябва да върнем стойността на клетка C5 в клетка E2.
За да постигнем желания резултат, трябва да следваме посочените по -долу стъпки.
- Изберете клетката E2 и напишете формулата.
- = OFFSET (A1,4,2,1,1)и натиснете Enter на клавиатурата.
- Функцията ще върне стойността на клетка C5.
В този пример трябва да получим стойността от клетката C5 до E2. Нашата референтна клетка е първата клетка в диапазона, който е A1 и C5 е 4 реда по -долу и 2 колони вдясно от A1. Следователно формулата е = OFFSET (A1,4,2,1,1) или = OFFSET (A1,4,2) (тъй като 1,1 е по избор).
Сега нека вземем пример за извличане на последната стойност в динамичен списък.
Имаме имена на държави в диапазон. Сега, ако добавим още държави към този списък, той трябва да е наличен автоматично в падащия списък.
За да подготвите разширен филтър, следвайте следните стъпки:-
- Изберете клетката B2.
- Отидете в раздела Данни, изберете Валидиране на данни от групата Инструменти за данни.
- Ще се появи диалоговият прозорец „Проверка на данни“. В раздела „Настройки“ изберете „Персонализиран“ от падащия списък Разрешаване.
- Полето за формула ще бъде активирано.
- Напишете формулата в това поле.
- = OFFSET (A: A, 1,0, COUNTA (A: A) -1,1).
- Щракнете върху OK.
- На този етап последната актуализирана клетка е A11.
- За да проверите дали валидирането на данни работи правилно, добавете име на град в клетка A12.
Веднага след като добавите запис в A12, той ще бъде добавен към падащия списък.
Това е начинът, по който можем да добавим повече записи от 999 елемента в Microsoft Excel.