Възможност за проверка, за да Excel - е прекрасно нещо, но то не е в състояние да изпълни едно важно действие (без следващия трик) за автоматично добавяне на нов запис в списъка, който се използва като източник за проверка на списъка.
Ако вече сте работили с тест, тогава знаете, че това е един много умен функция. Може би най-впечатляващ е способността му да се добави към списъка всяка клетка, в която потребителят може да избере ценности. Би било чудесно, ако въведете ново име в доказан Excel клетка автоматично да го добавите към списъка. Това е възможно благодарение на следния трик.
Да предположим, че има списък от имена в диапазон А1: (. Фигура 2.24) A10.
Фиг. 2.24. А работна книга, който е конфигуриран да се провери списъка
Тези имена представляват служителите на компанията. Не толкова рядко в такъв списък се добавят към имената на нов персонал, но сега единственият начин да се направи това - да се добавят нови имена до края на списъка и след това да ги изберете от списъка в тестваната клетка. За да се избегне това ограничение, направете следното. В A11 клетка тип следната формула и копиране до линията 20 - имайте предвид относителната препратка към клетка A10: = IF (OR ($ D 1 $ = ""; COUNTIF ($ A $ 1: (фигура 2.25). A10; $ D $ л)); "х"; $ D $ л). в руската версия на Excel: = IF (OR ($ D 1 $ = ""; COUNTIF ($ A $ 1: A10, $ D $ 1)); "х", $ D $ 1).
Фиг. 2.25. Списък на формулата добавя към A11 на линия: A20
Сега изберете Формула> зададете име (Формули> Define Name) и в името (имената в работна книга) въведете MyNames. Във формулата (Отнася се до) въведете следната формула (. Фигура 2.26), щракнете върху бутона Add (Добавяне), след това щракнете върху OK: = OFFSET (Sheet1 $ A $ 1; 0; 0; COUNTIF (Sheet1 $ к $ A! ; "<>х "); 1) в руската версия на Excel: !! = CMESCH (Sheet1 $ A $ 1; 0; 0; COUNTIF (Sheet1 $ A:. $ А;"<>х "), 1).
Фиг. 2.26. Трансформацията на списъка в динамичен списък
Изберете клетка D1 и изберете данни> Check (Data> Validation). В тип данни (Allow), изберете Списък (List), както и източник (Source), тип = MyNames; се уверете, че в квадратчето до списък с валидни стойности (по-Cell). Изберете съобщението за раздела за грешка (Error Alert) и махнете отметката съобщение Покажи грешка (Покажи сигнал грешка, след като е въведен невалиден данни). Щракнете върху OK. Вие ще видите резултата, както е показано на фиг. 2.27.
Фиг. 2.27. Списък проверки, които се добавят към клетката D1
Щракнете с десния бутон върху раздел лист, и от контекстното меню изберете Source Code (Виж Code). Въведи кода в Обявата 2.5.
// Обява 2.5 Private Sub Worksheet_Calculate () при грешка подновени следващата Application.EnableEvents = False Range ( "MyNames") = Range ( "MyNames"). Value Application.EnableEvents = True On Error GoTo 0 End Sub
За да се върнете към Excel и запишете работната книга, затворете прозореца. Сега изберете клетка D1, въведете името на всеки, който не е в списъка, и натиснете Enter. Отново изберете клетка D1 и да погледнем в списъка. Сега, списъкът трябва да съдържа новото име (фиг. 2.28).
Фиг. 2.28. Списък след добавяне на нов запис в клетка D1
Ако искате да добавите повече от 10 нови имена в списъка, копирайте формулата по-долу ред 20.
Свързани статии