Разширяем свързан списък в MS EXCEL

Статията разглежда подобрена версия на свързания списък.

Нека наложим ограничение на реда на попълване на списъците с държави за Региони в листа Списъци. Сега редът на имената на регионите в колонатаA трябва точно да съвпада с реда на заглавията на списъците с държави в ред1 (в предишния случай вижте статията Свързан списък, това не беше задължително, основното е, че всички имена на регионите присъстват в заглавията и редът не е важен).

За да изпълните това изискване, в клеткаB1 на листСписъци формулата =INDIRECT(ADDRESS(ROW($A$1)-COLUMN($A$1)+COLUMN();1))

Сега имената на заглавията на колоните автоматично ще бъдат взети от колонатаA и уникално ще съответстват на имената на регионите.

разширяем

Промяна на примерния файл от статията за свързания списък. Първо, нека изтрием чрез Мениджъра на имена ( Формули / Дефинирани имена / Мениджър на имена ) всички създадени преди това имена.

Нека създадем Динамичен диапазон, за да формираме падащ (падащ) списък, съдържащ имената на Регионите. За целта са ви необходими:

  • натиснете бутона на менюто "Задаване на име" ( Формули / Дефинирани имена / Задаване на име );
  • в полето Име въведете Региони;
  • в полето Обхват въведете формулата

Формулата =lists!$A$2:INDEX(lists!$A:$A;COUNT(lists!$A:$A)) преброява броя на въведените региони в колонаA на листLists (функция COUNT()) и дефинира връзка към последния елемент в колоната (функция INDEX(), като по този начин формира диапазон, съдържащ всички стойности на регионите. Не се допускат пропуски в колонаA. Можете също да използвате функцията OFFSET(), за да формирате динамичния диапазон.

Нека създадем константа с име MaxCountry, равна на 20. Константата съответства намаксималният брой държави в региона (задаваме константата произволно).

Създайте именуван диапазон Selected_Region, за да дефинирате диапазон в листСписъци, съдържащ държавите от избрания регион: =OFFSET(lists!$A$2;;Position;MaxCountries) . Сега, например, ако изберете региона Америка, функцията OFFSET() ще върне препратка към списъците с диапазони!$B$2:$B$20, съдържащи всички държави от този регион.

По принцип можете да посочите =Selected_Region като източник на свързания списък за клетки от колонаB на листТаблица, но тогава в падащия списък ще се появят празни редове. За да изключим тези редове, накрая, нека създадем последната формула за именувана държава, за да дефинираме по-точно обхвата в листаСписъци, съдържащ държавите от избрания регион: =OFFSET(lists!$A$2;;Position;COUNT(Selected_Region))

Сега чрез Мениджър на имена ( Формули / Дефинирани имена / Мениджър на имена ) можете да видите всички имена, създадени по-горе.

имена

И накрая, нека създадем падащ списък (свързан списък) за клетки от колоната Държава вТаблица.

  • изберете диапазонB5:B22 ;
  • извикайте инструмента за проверка на данните,
  • задайте типа данни на Списък,
  • в полето Източник въведете: =Държави.

Тестване. Използвайки падащия списък в клеткаA5, изберете Регион - Америка, извикайте свързания списък в клеткаB5 и копеле - появи се списък с държави за Регион Америка: САЩ, Мексико ... За разлика от свързания списък - без празни редове.

имена

Сега попълнете следващия ред. Изберете в клеткаA6 Регион - Африка, извикайте свързания списък в клеткаB6 и пак полудете: Чад, Танзания... Отново без празни редове.

списък

И сега - основната разлика от свързания списък: за да добавите нови региони и техните държави, сега е достатъчно да въведете нов регион в колонатаA (листСписъци ), в ред1 автоматично ще се покаже съответното заглавие. Под заглавието, което се появява, в ред1 въведете държавите от новия регион. И това е!

Специални случаи на свързания списък са: