Количество элементов, доступных для фильтрации, ограничено. Excel не может фильтровать столбцы, в которых количество элементов превышает 999 (а не количество строк).

Чтобы отфильтровать более 999 элементов, используйте расширенный фильтр.

Для создания расширенного фильтра мы будем использовать функции «СМЕЩЕНИЕ» и «СЧЁТ» в Microsoft Excel.

COUNTA: возвращает количество ячеек, содержащих значения.

Синтаксис функции «СЧЁТ»: = СЧЁТ (значение1, значение2, значение3 …….)

Пример: _ _ В диапазоне A1: A5 ячейки A2, A3 и A5 содержат значения, а ячейки A1 и A4 пусты. Выделите ячейку A6 и напишите формулу- _ = COUNTA (A1: A5) _ _ функция вернет 3_

img1

СМЕЩЕНИЕ: возвращает ссылку на диапазон, который смещен на количество строк и столбцов из другого диапазона или ячейки.

Синтаксис функции OFFSET: = OFFSET (ссылка, строки, столбцы, высота, ширина) Reference: — Это ячейка или диапазон, из которого вы хотите смещаться .

Rows and Columns to move: _ _- Количество строк, которые вы хотите переместить от начальной точки, и оба они могут быть положительными, отрицательными или нулевыми .

_Height и Width _ * : — это размер диапазона, который вы хотите вернуть. Это необязательное поле . Давайте рассмотрим пример, чтобы понять функцию смещения в Excel.

У нас есть данные в диапазоне A1: D10. Столбец A содержит код продукта, столбец B — количество, столбец C — стоимость продукта, а столбец D — общую стоимость. Нам нужно вернуть значение ячейки C5 в ячейке E2.

img2

Чтобы получить желаемый результат, нам нужно выполнить следующие шаги.

  • Выберите ячейку E2 и напишите формулу.

OFFSET (A1,4,2,1,1) * и нажмите Enter на клавиатуре.

  • Функция вернет значение ячейки C5 .

img3

img4

В этом примере нам нужно получить значение от ячейки C5 до E2. Наша справочная ячейка — это первая ячейка в диапазоне A1, а C5 на 4 строки ниже и на 2 столбца справа от A1. Следовательно, формула = СМЕЩЕНИЕ (A1,4,2,1,1) или = СМЕЩЕНИЕ (A1,4,2) (поскольку 1,1 является необязательным).

Теперь давайте рассмотрим пример получения последнего значения в динамическом списке.

У нас есть названия стран в диапазоне. Теперь, если мы добавим в этот список больше стран, он должен автоматически появиться в раскрывающемся списке.

img5

Чтобы подготовить расширенный фильтр, выполните следующие действия: — * Выберите ячейку B2.

  • Перейдите на вкладку «Данные» и выберите «Проверка данных» в группе инструментов «Данные».

img6

  • Откроется диалоговое окно «Проверка данных». На вкладке «Настройки» выберите «Пользовательский» в раскрывающемся списке «Разрешить».

img7

  • Окно формулы будет активировано.

  • Напишите формулу в этом поле.

* _ = СМЕЩЕНИЕ (A: A, 1,0, COUNTA (A: A) -1,1) ._

  • Щелкните ОК.

img8

  • На этом этапе последней обновленной ячейкой является A11.

img9

  • Чтобы проверить, правильно ли работает проверка данных, добавьте название города в ячейку A12.

img10

Как только вы добавите запись в A12, она будет добавлена ​​в раскрывающийся список.

_Таким образом мы можем добавить больше записей, чем 999 элементов в Microsoft Excel. _