Фильтрация ограничивается до 999 элементов в Microsoft Excel
Количество элементов, доступных для фильтрации, ограничено. Excel не может фильтровать столбцы, в которых количество элементов превышает 999 (а не количество строк).
Чтобы отфильтровать более 999 элементов, используйте расширенный фильтр.
Для создания расширенного фильтра мы будем использовать функции «СМЕЩЕНИЕ» и «СЧЁТ» в Microsoft Excel.
COUNTA: возвращает количество ячеек, содержащих значения.
Синтаксис функции «СЧЁТ»: = СЧЁТ (значение1, значение2, значение3 …….)
Пример: _ _ В диапазоне A1: A5 ячейки A2, A3 и A5 содержат значения, а ячейки A1 и A4 пусты. Выделите ячейку A6 и напишите формулу- _ = COUNTA (A1: A5) _ _ функция вернет 3_
СМЕЩЕНИЕ: возвращает ссылку на диапазон, который смещен на количество строк и столбцов из другого диапазона или ячейки.
Синтаксис функции OFFSET: = OFFSET (ссылка, строки, столбцы, высота, ширина) Reference: — Это ячейка или диапазон, из которого вы хотите смещаться .
Rows and Columns to move: _ _- Количество строк, которые вы хотите переместить от начальной точки, и оба они могут быть положительными, отрицательными или нулевыми .
_Height и Width _ * : — это размер диапазона, который вы хотите вернуть. Это необязательное поле . Давайте рассмотрим пример, чтобы понять функцию смещения в Excel.
У нас есть данные в диапазоне A1: D10. Столбец A содержит код продукта, столбец B — количество, столбец C — стоимость продукта, а столбец D — общую стоимость. Нам нужно вернуть значение ячейки C5 в ячейке E2.
Чтобы получить желаемый результат, нам нужно выполнить следующие шаги.
-
Выберите ячейку E2 и напишите формулу.
OFFSET (A1,4,2,1,1) * и нажмите Enter на клавиатуре.
-
Функция вернет значение ячейки C5 .
В этом примере нам нужно получить значение от ячейки C5 до E2. Наша справочная ячейка — это первая ячейка в диапазоне A1, а C5 на 4 строки ниже и на 2 столбца справа от A1. Следовательно, формула = СМЕЩЕНИЕ (A1,4,2,1,1) или = СМЕЩЕНИЕ (A1,4,2) (поскольку 1,1 является необязательным).
Теперь давайте рассмотрим пример получения последнего значения в динамическом списке.
У нас есть названия стран в диапазоне. Теперь, если мы добавим в этот список больше стран, он должен автоматически появиться в раскрывающемся списке.
Чтобы подготовить расширенный фильтр, выполните следующие действия: — * Выберите ячейку B2.
-
Перейдите на вкладку «Данные» и выберите «Проверка данных» в группе инструментов «Данные».
-
Откроется диалоговое окно «Проверка данных». На вкладке «Настройки» выберите «Пользовательский» в раскрывающемся списке «Разрешить».
-
Окно формулы будет активировано.
-
Напишите формулу в этом поле.
* _ = СМЕЩЕНИЕ (A: A, 1,0, COUNTA (A: A) -1,1) ._
-
Щелкните ОК.
-
На этом этапе последней обновленной ячейкой является A11.
-
Чтобы проверить, правильно ли работает проверка данных, добавьте название города в ячейку A12.
Как только вы добавите запись в A12, она будет добавлена в раскрывающийся список.
_Таким образом мы можем добавить больше записей, чем 999 элементов в Microsoft Excel. _