На примере разработки однострочного табеля продемонстрируем, как быстро и без механических ошибок заполнить ячейки, в которых должны находиться Ф.И.О. и табельные номера. Исходным файлом послужит табличная база данных сотрудников.
Связывание книг
Вы уже знаете, что при вводе формул образуются зависимые и влияющие ячейки. На ход расчетов может повлиять содержимое других ячеек - при наличии в формуле ссылок на них. Цель создания ссылок заключается в использовании содержащейся в ячейках информации. Рассматривавшиеся ранее таблицы размещались на одном рабочем листе. Если же ячейку, содержащую ссылку, перенести на другой лист или в другую книгу, ссылка не разорвется и мы получим связанные посредством формул рабочие листы или книги.
Таким образом, вместо копирования данных из одной книги в другую можно просто создавать ссылки на ячейки другой книги. Воспользуемся этим методом для ввода в табель фамилий и табельных номеров работников.
Для удобства при создании связи между файлами (книгами) разместим их в одной папке. Создайте папку под названием Табель и поместите в нее файлы Табель и База (с базой данных сотрудников).
Заполнение ячеек с названиями должностей и табельными номерами
Название должности каждого работника будет извлекаться из книги База. Реализуется это следующим образом:
1. Откройте две книги, между которыми будет установлена связь. Желательно расположить их в одном окне посредством команды Окно/Расположить.
2. Выделите диапазон ячеек D9:D18 и введите знак равенства.
3. Перейдите в книгу База и выделите ячейку G4. При появлении ссылки на другую книгу автоматически создается абсолютная ссылка. Для преобразования ее в относительную следует нажать три раза функциональную клавишу [F4].
4. В завершение нажмите комбинацию клавиш [Enter+Ctrl].
В результате диапазон D9:D18 будет заполнен такими формулами, как в ячейке D9:
=[База.хls]Сотрудники!G4
Ссылка между открытыми книгами имеет такую структуру:
[Имя_книги]Имя_листа!Адрес_ячейки
После закрытия книги, с которой установлена связь, ссылка изменится: в ней будет указан полный путь, по которому находится исходная информация:
='С:\Отдел кадров\Табель\[База.xls]Сотрудники'!В2
ПРИМЕЧАНИЕ
|
При наличии пробелов в именах листов, книг или папок весь путь автоматически заключается в одинарные кавычки. |
Для создания ссылки на ячейку другой книги путь можно набрать с клавиатуры, но это занимет много времени. Преимущество данного метода лишь в том, что нет необходимости открывать книгу, с которой устанавливается связь.
Для получения ссылок на табельные номера и фамилии установим связь. Например, для ячейки С9 (табельный номер) выполните следующие действия:
1. Выделите диапазон ячеек С9:С18 и введите знак равенства.
2. Перейдите в книгу База и выделите ячейку В2.
3. Нажмите комбинацию клавиш [Enter+Ctrl].
В результате формула в ячейке С9 будет иметь такой вид:
= [База.xls]Сотрудники!В2
Аналогичными формулами будет заполнен весь диапазон ячеек С9:С18.
Рис. 7.21. Фрагмент однострочного табеля с формулами для ввода порядкового номера, Ф.И.О., табельного номера и должности
Автоматический ввод Ф.И.О.
Формула в ячейке В9 (рис. 7.21) возвращает фамилию, которая находится в ячейке С2 рабочего листа Сотрудники, и инициалы, которые берутся из ячеек D2 и Е2. Она также обеспечивает расстановку между ними пробелов и точек:
=СЦЕПИТЬ([База.xls]Сотрудники!С2;" "; ЛЕВСИМВ([База.xls]Сотрудники!D2;1);" "; ЛЕВСИМВ([База.xls]Сотрудники!Е2;1))
Действие этой формулы сводится к следующему: из базы данных извлекается полная фамилия, а от имени и отчества отсекаются первые буквы, после которых ставятся точки. Кроме того, перед отчеством (или после имени) добавляется пробел.
Введем сначала простые формулы, которые впоследствии будут соединены. В ячейке В10 должна находиться формула, которая будет извлекать фамилию:
=[База.xls]Сотрудники!С2
Первую букву имени поместим в ячейку ВИ. Для этого занесем в нее такую формулу:
=ЛЕВСИМВ([База.xls]Сотрудники!D2;1)
ПРИМЕЧАНИЕ
|
Из панели функций можно сослаться на другие листы или книги точно так же, как из ячеек. Для этого достаточно поместить курсор в поле ввода панели функций и выделить ячейку на другом листе или в другой книге. |
В ячейку В12 введите формулу, приведенную ниже. Она будет извлекать первую букву отчества.
=ЛЕВСИМВ([База.xls]Сотрудники!Е2;1)
Перейдите в ячейку В9 и, вызвав функцию СЦЕПИТЬ, установите ссылки на эти ячейки. Затем замените ссылки формулами, которые находятся в ячейках.