Связи баз данных листа

Задание № 13. На основании табельного номера создайте запрос к базам данных листа о сотрудниках.

Выполнение в MS Excel

• На листе ЗапросСотрудникивведите информацию, представленную на Рис. 20.

В ячейку А4будет вводиться табельный номер, все остальные графы будут заполняться автоматически.

Рис. 20.Шаблон запроса информации о сотрудниках

Предварительно присвоим имена диапазонам баз данных листа.

• На листе СправочникДолжностейвыделите диапазон ячеек (Рис. 21).

• выполните команды Формулы | Определенные имена | Присвоить имя.

• в диалоговом окне Создание именив полеИмя:введитеСправочникДолжностей(Рис. 21)и щёлкните на кнопкеОК.

Рис. 21. Присвоение имени СправочникДолжностей диапазону данных

Присвойте имя диапазону данных Сотрудники на листе Сотрудники(Рис. ).

Рис. 22.Присвоение именидиапазону Сотрудники

Создайте на отдельных листах СправочникОтделов, Проектыи присвойте имена диапазонов данных: СправочникОтделов(Рис. 23), Проекты (рис. 24).

Для связи баз данных листа будет использоваться функция ВПР()из категорииСсылки и массивы.

• В ячейку A4листаЗапросСотрудникивведите табельный номер интересующего Вас сотрудника, например, 8.

Рис. 23.Присвоение имени диапазонуСправочникуОтделов

Рис. 24.Присвоение именидиапазону Проекты

• Сделайте ячейку В4активной и выполните командыФормулы | Библиотека функций | Вставить функцию.

• В окне Мастер функций шаг 1 выберите категорию Ссылки и массивы, затем функцию ВПРи щёлкните на кнопкеОК.

• В окне Аргументы функциивведите информацию, как показано на Рис. 25, и щёлкните на кнопкеОК. В ячейке отразится только фамилия.

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

• =ВПР($A$4;Сотрудники;2;0)&" "&ВПР($A$4;Сотрудники;3;0)&" "&ВПР($A$4;Сотрудники;4;0).

• Введите в ячейку С4формулу:=ВПР(ВПР($A$4;Сотрудники;9;0);СправочникОтделов;2;0).

• Введите в ячейку D4 формулу:

=ВПР(ВПР($A$4;Сотрудники;10;0);СправочникДолжностей;2;0).

• Введите в ячейку E4формулу:=ВПР(ВПР($A$4;Сотрудники;10;0);Проекты;2;0).

Рис. 25.Задание аргументов функции ВПР()

Результаты запроса представлены на рис. 26.

Рис. 26.Результаты запроса к базе данных по сотрудникам

2.3. Созданиесводныхтаблиц

Задание № 14. По данным таблицыСотрудникисоздайте сводную таблицу по подсчету общего количества сотрудников в организации.

Выполнение в MSExcel

• Сделайте активной ячейку в диапазоне Сотрудники.

• Выполните командыВставить | Таблицы | Сводная таблица | Сводная таблица(рис. 27).

• Настройте сводную таблицу, перетаскивая поля, как показано на рис. 27 (результаты представлены на рис. 28).

Задание 15.Определите, сколько человек работает в каждом отделе.

Выполнение в MS Excel

• Щёлкните по стрелке Номер отдела (Все)и выберите отдел02/5(Рис. 29).

Рис.  27. Настройка сводной таблицы Рис.  28. Общее количество

сотрудников в организации

• Щёлкните по кнопке ОК.Результаты представлены на рис. 30.

Рис. 29.Настройка сводной таблицы Рис. 30.Сводная таблица

на отдел02/5по отделу02/5