Заполнение области ввода

На примере разработки однострочного табеля продемонстрируем, как быстро и без механических ошибок заполнить ячейки, в которых должны находиться Ф.И.О. и табельные номера. Исходным файлом послужит табличная база данных сотрудников.

Связывание книг

Вы уже знаете, что при вводе формул образуются зависимые и влияющие ячейки. На ход расчетов может повлиять содержимое других ячеек - при наличии в формуле ссылок на них. Цель создания ссылок заключается в использовании содержащейся в ячейках информации. Рассматривавшиеся ранее таблицы размещались на одном рабочем листе. Если же ячейку, содержащую ссылку, перенести на другой лист или в другую книгу, ссылка не разорвется и мы получим связанные посредством формул рабочие листы или книги.

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

Для удобства при создании связи между файлами (книгами) разместим их в одной папке. Создайте папку под названием Табель и поместите в нее файлы Табель и База (с базой данных сотрудников).

Заполнение ячеек с названиями должностей и табельными номерами

Название должности каждого работника будет извлекаться из книги База. Реализуется это следующим образом:
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 и, вызвав функцию СЦЕПИТЬ, установите ссылки на эти ячейки. Затем замените ссылки формулами, которые находятся в ячейках.