Чтобы получить последнее значение в динамическом списке, мы будем использовать опцию Data Validation вместе с функциями OFFSET и COUNTA в Microsoft Excel 2010/2013.

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

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

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

img1

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

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

Строки и столбцы для перемещения: _ _- Сколько строк вы хотите переместить в начальную точку, и оба значения могут быть положительными, отрицательными или нулевыми .

_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, она будет добавлена ​​в раскрывающийся список.

Это способ создания динамического списка и автоматического заполнения его новыми записями в Microsoft Excel 2010 и 2013.

__