Поле со списком и функция ИНДЕКС в Excel

Опубликовано 13 Июл 2013
Рубрика: Справочник Excel | 2 комментария



Доступ к данным в таблицах ExcelВ статье «Расчет передачи винт-гайка» я обещал рассказать, как работает функция ИНДЕКС в Excel. Конечно, все желающие могут разобраться в этом самостоятельно, изучив формулы в вышеназванной статье и почитав об этой функции в Справке Excel и в Сети. Эта небольшая статья...

...написана для тех, кто не разобрался или не пожелал разбираться самостоятельно.

При создании расчетных прикладных программ в MS Excel и OOo Calc существует задача оптимизации и автоматизации ввода данных из справочников. Выбор и ввод информации из справочников позволяет минимизировать ошибки пользователя, возникающие при наборе данных на клавиатуре, существенно ускоряет работу, делает ее более комфортной, приятной и понятной. В качестве справочников очень часто используется набор одномерных и двухмерных таблиц, размещенных на одном рабочем листе с программой расчета или на других листах файла Excel.

Рассмотрим использование поля со списком и функции ИНДЕКС на примере.

Итак, есть в наличии таблица, например, перечень материалов для изготовления колеса червячной передачи с механическими характеристиками [σв] и [σт]. Необходимо из этой таблицы для выбранной пользователем марки бронзы скопировать значения предела прочности [σв] и предела текучести [σт] в ячейки 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.

Уважаемые читатели, для получения анонсов статей моего блога прошу оформить подписку в окне «Подпишитесь на новости», расположенном вверху страницы. Введите адрес своей электронной почты и нажмите на кнопку «Получать анонсы статей». Один раз в 7…10 дней к вам на почтовый ящик будет приходить небольшое уведомление о появлении на моем блоге новой статьи, ее название и краткое описание. Если вам что-то не понравится или просто надоест автор или тема, вы прямо в почте всегда можете отказаться от подписки.

Жду ваших комментариев!

Ссылка на скачивание файла: pole-so-spiskom-i-funktsiya-indeks-v-excel (xls 31,0KB).

Другие статьи автора блога

На главную


Введите Ваш e-mail:

Статьи с близкой тематикой

Отзывы

2 комментария на «Поле со списком и функция ИНДЕКС в Excel»

  1. Алексей 28 Окт 2013 18:41

    Ба-адьшущее спасибо! Для меня лично это очень актуально!

  2. Вячеслав 18 Ноя 2013 12:02

    Понял, что смогу сделать свои расчеты гораздо эстетичнее и ошибаться в них — меньше.

Ваш отзыв







  • Посетители: 656 202

  • Подписчики: 3 414