Вторник, 19.03.2024
Уголок эникейщика
Меню сайта
Категории раздела
Около ПК [67]
Полезные программы [33]
Интересности [26]
[8]
Android [8]
Личное [27]
Настройки сайта [8]
Избранное

Рахни православні

Погода в Виннице
Сайт Экслера

Форум Ru.Board
CWER.ru

Hot Line - Цены
Яндекс.Маркет

Форум rutracker.org

Статистика

Онлайн всего: 1
Гостей: 1
Пользователей: 0
Форма входа
Главная » 2012 » Ноябрь » 9 » Расширенный выбор значений в Excel
16:21
Расширенный выбор значений в Excel

Постановка задачи

Дать пользователю возможность выбора нужного элемента из справочника и автозаполнения ячеек информацией из полей выбранного элемента.
Справочником будет служить таблица, размещённая на отдельном листе. Строками таблицы будут сами элементы. В столбцах таблицы будут храниться значения их полей.

Ref

По ссылке можно скачать сам пример, рассматриваемый в данной заметке.

Логика решения

  1. С помощью встроенных средств Excel даем пользователю возможность выбрать нужный элемент из раскрывающегося списка. Можно использовать два варианта: через элемент управления "Поле со списком" и через проверку вводимых значений с помощью списка.
  2. Сохраняем номер выбранного элемента (его позицию в списке элементов).
  3. Используем полученный номер строки и номер столбца со значением нужного поля для получения его значения.
    Для этого подходит встроенная функция ИНДЕКС:

Из справки:
Функция ИНДЕКС: Возвращает значение элемента таблицы или массива
Синтаксис: ИНДЕКС(массив, номер_строки, [номер_столбца])

В нашем случае в формулу вместо массива будем подставлять название именованного диапазона (для примера "Клиенты"), содержащего данные таблицы-справочника (без шапки).

Создание именованного диапазона:

Excel 2003 Excel 2010

 

Создание диапазона 2003

 

Создание диапазона 2010

 

 

Вариант 1. Пользователь выбирает с помощью элемента управления "Поле со списком"

Данный вариант лучше всего подходит для случаев, когда нужно выбирать один-два раза, точно зная, где будет находиться полученная информация. Он проще, работа ведётся только с одним диапазоном "Клиенты".
Элемент управления служит для выбора и на печать не выводится

Для получения возможности вставлять элементы управления необходимо: в Excel 2003 отобразить панель "Формы", а в Excel 2010 - вкладку "Разработчик".

Вставка элемента управления:

Excel 2003 Excel 2010

В главном меню: Вид - Панели инструментов.
Включаем панель "Формы"

Панель Формы 2003

На панели "Формы" находим кнопку "Поле со списком":

Вставка элемента 2003

Вкладка Файл - Параметры. Включаем отображение панели "Разработчик".

Вкладка Разработчик 2010

На вкладке "Разработчик" нажимаем кнопку "Вставить" и выбираем "Поле со списком":

Вставка элемента 2010

Созданный элемент управления должен иметь свойства:

Свойства элемента управления

Вариант 2. Пользователь выбирает в ячейках из списка через проверку вводимых значений

Данный вариант лучше всего подходит для случаев, когда нужно выбирать много раз. Использование элементов управления становится трудоёмким.
Выбор производится с помощью проверки вводимых в ячейки значений (тип - "Список").

Ситуация усложняется тем, что при такой проверке можно использовать диапазон, состоящий только из одного столбца.
Кроме того, результатом выбора будет наименование элемента справочника, а не его номер в списке элементов.

Решением может служить введение дополнительного столбца в таблицу-справочник. Ячейки этого столбца будут содержать наименования элементов справочника вместе с номером строки. В примере я использую формулу вида =СЦЕПИТЬ(СТРОКА(A3)-2;": ";A3).

Этот дополнительный столбец станет новым диапазоном "КлиентыДляВыбора", пользователь получит возможность выбрать по нему, из результата выбора можно получить номер строки, потом по этому номеру - всю остальную информацию из основного диапазона "Клиенты". Это возможно, так как нумерация строк обоих диапазонов совпадает.

Диапазон "Клиенты для выбора" Диапазоны

Далее, в ячейках, в которых будет производиться выбор из списка, нужно установить проверку данных. Устанавливаем курсор в выбранную ячейку и производим следующие действия:

Excel 2003 Excel 2010

Проверка данных 2003

 

Проверка данных 2010

 

Свойства элемента управления

В выбранном значении находим позицию строки ": ", которая идет после нужного номера (с помощью формулы вида: =НАЙТИ(": ";A4)). После чего получаем сам номер элемента справочника в списке элементов (с помощью формулы вида: =ПСТР(A4;1;B4-1) ).

Понятно, что способ соединения названия элемента справочника и номера его строки можно изменить. Соответственно, изменятся формулы для такого соединения и для обратного процесса извлечения номера из выбранного значения.

Категория: Около ПК | Просмотров: 3377 | Добавил: Игорь | Рейтинг: 0.0/0
Облако тегов
сайт программы Статьи личное разочарования Windows 1c антивирус политика почитать рекомендую Prices math ссылки english скрипты total сеть VBS
Календарь
«  Ноябрь 2012  »
ПнВтСрЧтПтСбВс
   1234
567891011
12131415161718
19202122232425
2627282930
Архив записей
Поиск
Google

WWW на сайте
Copyright IgorDanyK © 2024
Создать бесплатный сайт с uCoz