За да извлечем последната стойност в динамичен списък, ще използваме опцията за валидиране на данни заедно с функциите OFFSET и COUNTA в Microsoft Excel 2010/2013.
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, той ще бъде добавен към падащия списък.
Това е начинът, по който можете да създадете динамичен списък и автоматично да попълните нови записи в него в Microsoft Excel 2010 и 2013.
