Поле со списком и функция ИНДЕКС в Excel
Опубликовано 13 Июл 2013
Рубрика: Справочник Excel | 4 комментария
В статье «Расчет передачи винт-гайка» я обещал рассказать, как работает функция ИНДЕКС в Excel. Конечно, все желающие могут разобраться в этом самостоятельно, изучив формулы в вышеназванной статье и почитав об этой функции в Справке Excel и в Сети. Эта небольшая статья...
...написана для тех, кто не разобрался или не пожелал разбираться самостоятельно.
При создании расчетных прикладных программ в MS Excel и OOo Calc существует задача оптимизации и автоматизации ввода данных из справочников. Выбор и ввод информации из справочников позволяет минимизировать ошибки пользователя, возникающие при наборе данных на клавиатуре, существенно ускоряет работу, делает ее более комфортной, приятной и понятной. В качестве справочников очень часто используется набор одномерных и двухмерных таблиц, размещенных на одном рабочем листе с программой расчета или на других листах файла Excel.
Рассмотрим использование поля со списком и функции ИНДЕКС на примере.
Итак, есть в наличии таблица, например, перечень материалов для изготовления колеса червячной передачи с механическими характеристиками [σв] и [σт]. Необходимо из этой таблицы для выбранной пользователем марки бронзы скопировать значения предела прочности [σв] и предела текучести [σт] в ячейки Excel, где расположена расчетная программа. То есть, необходимо реализовать следующий алгоритм: пользователь программы выбирает материал для червячного колеса… – и всё!!! После выбора материала копии значений [σв] и [σт], соответствующие выбранной бронзе, копируются из таблицы в ячейки расчетного модуля программы автоматически!
На представленном снимке экрана изображен пример с результатом выполнения вышеописанного алгоритма.
Пройдем по шагам весь процесс:
1. Создаем новый файл Excel — pole-so-spiskom-i-funktsiya-indeks-v-excel.xls.
2. Размещаем на листе базу данных – таблицу с заголовком в область A1:C9.
3. Делаем соответствующие оформительские записи в расчетном блоке в ячейках A12, A13, A14, C13, C14 и вписываем заголовок в объединенные ячейки A11, B11, C11.
4. Активируем, если не активирована, панель инструментов «Формы». Для этого заходим в закладки «Вид» — «Панели инструментов» — «Формы» и ставим «галочку».
5. На панели инструментов «Формы» выбираем элемент «Поле со списком» и размещаем его над ячейками B12 и C12.
6. Делаем щелчок правой кнопкой мыши на элементе «Поле со списком» и в выпавшем контекстном меню выбираем «Формат объекта».
7. В появившемся окне «Формат элемента управления» переходим на вкладку «Элемент управления».
8. Формируем список по диапазону $A$5:$A$9.
9. Устанавливаем связь с ячейкой $A$3.
10. Изменяем количество строк списка с 8 на 5 – по количеству строк в базе.
11. Ставим галочку внизу окна – включаем объемное затенение. Так элемент выглядит симпатичнее.
12. Нажимаем на кнопку «ОК» и закрываем окно «Форматирование объекта».
13. Проверяем, как работает «Поле со списком». Для этого нажимаем на кнопку справа «Поля…» и в «выпавшем» списке выбираем, например, четвертую запись – БрО5Ц5С5 (песч. форма). После щелчка левой кнопкой мыши на выбранном элементе он появляется в окошке, а полный список «сворачивается» (исчезает).
Обращаю ваше внимание, что после сделанного нами выбора в ячейке A3 появилось число 4. Это число показывает порядковый номер выбранной записи в «Поле со списком» и появилось оно потому, что именно с этой ячейкой в шаге №9 мы установили связь.
Материал для червячного колеса мы выбрали и видим его в установленном над ячейками B12 и C12 элементе «Поле со списком». Теперь выведем значения [σв] и [σт] для выбранной бронзы в ячейки B13 и B14. Для этого запишем в эти ячейки формулы:
14. В ячейку B13: =ИНДЕКС(B5:B9;A3)=150
15. В ячейку B14: =ИНДЕКС(C5:C9;A3)=80
Теперь при выборе из выпадающего списка любого материала функция ИНДЕКС тут же выведет в ячейки B13 и B14 соответствующие этому материалу значения предела прочности [σв] и предела текучести [σт]. Эти значения могут участвовать в дальнейших расчетах, подставляться в формулы в качестве исходных данных.
Одним щелчком мыши мы заполняем сразу три строки исходных данных некой расчетной программы, а если исходная таблица будет с большим количеством строк и столбцов, то ускорение и упрощение работы пользователя становятся очень существенными.
На этом – всё о совместном использовании функции ИНДЕКС и элемента «Поле со списком» из панели инструментов «Формы» для обеспечения доступа к базе данных в виде двухмерной таблицы при написании расчетных программ.
Гуру в Excel расскажут вам, возможно, массу других «более правильных» и простых способов решения подобных задач. Но я, однажды решив эту задачу описанным выше приемом, счел для себя лишним продолжать поиски других путей решения, так как результат меня полностью удовлетворяет вот уже более десяти лет.
Все описанные действия выполнялись в Excel-2003. Для более новых версий программы действия будут похожими – думаю, разберетесь. В OOo Calc аналогом функции ИНДЕКС MS Excel является функция index.
Ссылка на скачивание файла: pole-so-spiskom-i-funktsiya-indeks-v-excel (xls 31,0KB).
Статьи с близкой тематикой
Отзывы
4 комментария на «Поле со списком и функция ИНДЕКС в Excel»
Ваш отзыв
Посетители: 2,1 млн
Ба-адьшущее спасибо! Для меня лично это очень актуально!
Понял, что смогу сделать свои расчеты гораздо эстетичнее и ошибаться в них — меньше.
Как всегда, очень познавательно!
Полезная функция, раньше хотел так же сделать, но не нашёл такого изящного решения и сделал выпадающий список с номером строки и потом по индексу также выбирал что надо, но так гораздо удобнее, когда номер строки выбирается автоматически. Буду теперь такую возможность использовать, спасибо!
Пункта 4 в Excel 2007 у себя не нашёл. Порылся в инете, с версии 2007 эту панель надо искать так: Панель инструментов «Разработчик» открываем вкладку «Вставить» и в элементах управления выбираем «Поле со списком». У меня всё получилось как у Автора
Автору Огромное уважение и успехов!
С удовольствием изучаю все Ваши статьи. Много полезного для себя в них нахожу, как по методикам расчёта, так и по использованию Excel. Если что-то не пригодилось сейчас, беру на заметку, может потом понадобится такой расчёт
Спасибо