2.4 ЛАБОРАТОРНАЯ РАБОТА № 4

«СОЗДАНИЕ ТАБЛИЧНОЙ БАЗЫ ДАННЫХ СОТРУДНИКОВ С ПОМОЩЬЮ ТАБЛИЧНОГО ПРОЦЕССОРА MICROSOFT EXCEL (Ч. II)»

ЦЕЛЬ РАБОТЫ: научиться создавать списки данных, формировать заголовки списка, переносить данные из текстового редактора в электронные таблицы, обрабатывать перенесенные текстовые данные, определять длину текста заголовка, создавать сложные формулы для обработки текста, заносить информацию в базу данных с помощью табличного процессора Ms. Excel.

ПРИБОРЫ И ОБОРУДОВАНИЕ: Pentium II и выше, Microsoft Excel 2003, 2007.

ЛИТЕРАТУРА:

1. Гончаров А. Excel 7.0 в примерах / А. Гончаров. - СПб: Питер, 2006.-256с.

2. Горев А., Ахаян Р., Макашарипов С. Эффективная работа с СУБД / А.Горев, Р. Ахаян, С. Макашарипов.- СПб: Питер, 2007.-704с.

3. Кэмпбелл М. Excel / М. Кэмпбелл. - М.: БИНОМ, 2006.-384с.

4. Наумов А.Н., Вендров А.М., Иванов В. К. и др. Системы управления базами данных и знаний / под ред. А.Н. Наумова. - М.: Финансы и статистика, 2001.-80с.

5. Николь Н., Ральф А. Электронные таблицы Excel 7.0 для квалифицированных пользователей / Н. Николь, А. Ральф - М.: ЭКОМ, 2006.-304с.

ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ:

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

Мы научимся, как создать базу данных, которая содержит информацию о работниках. Такая база данных удобна по следующим причинам:

• она обеспечивает удобный метод поиска информации о работнике (фамилия, имя и отчество, место проживания, телефон, должность, дата рождения, количество детей и т. д.);

• с ее помощью можно выполнять различного рода анализ, например, определять структуру кадров или причины их текучести;

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

Для учета данных о сотрудниках на предприятиях используют самые разнообразные методы. В одних организациях существуют журналы учета, куда информация вносится вручную, в других применяются классические базы данных для учета кадров, в третьих используются СУБД Access. Но в большинстве случаев на предприятиях учет данных о сотрудниках ведется в электронных таблицах Excel.

Списки

Аналогом простой базы данных в Excel служит список. Список - это группа строк таблицы, содержащая связанные данные. Отличительной особенностью списка является то, что каждый его столбец содержит однотипные данные, например, перечень фамилий, цену за единицу товара и т. д.

Если провести аналогию между списком и табличной базой данных, то столбцы списка являются полями базы данных, а его строки - записями. Считается, что первая строка списка является его заголовком и содержит названия столбцов списка. Заголовок должен иметь на листе электронных таблиц горизонтальную ориентацию (в нашем примере он располагается в строке 1). Заголовки применяются Excel при составлении отчетов, а также при поиске и организации данных.

Шрифт, размер шрифта, выравнивание и другие параметры форматирования, присвоенные заголовкам столбцов списка, должны отличаться от параметров, назначенных для строк данных. Для отделения заголовка от расположенных ниже данных применять пустые строки не следует.

1 Формирование заголовка списка

Предположим, что перечень столбцов списка, который будет применяться при создании базы данных, набит в текстовом редакторе Ms.Word (рис.1).

ПРИМЕЧАНИЕ.

Рис.1- Окно текстового редактора с перечнем столбцов списка

Количество столбцов списка ограничено только допустимым количеством столбцов рабочего листа Excel (то есть 256).

На данном рисунке льготы по пн (пункт 18) - это льготы, которыми пользуется сотрудник при начислении подоходного налога. Пункт «совместитель – многодетный», расположенный ниже, содержит сведения о том, является ли данный сотрудник совместителем или многодетным родителем. Справочный столбец предназначен для внесения дополнительной информации.

Перенос данных из текстового редактора в электронные таблицы

Поскольку перечень полей табличной базы данных находится в документе Ms.Word, надо переместить его в электронные таблицы. Это можно быстро и без ошибок выполнить так:

• выделите перечень в документе Ms.Word и скопируйте его в буфер обмена.

• перейдите в программу Excel (если она запушена, то это можно осуществить посредством клавиш [Alt+Tab]).

• поместите табличный курсор в любую ячейку рабочего листа, расположенную под строкой 1 (в строке 1 будут находиться имена полей табличной базы данных). Допустим, вы выбрали ячейку А4.

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

Обработка перенесенных текстовых данных

Обратите внимание, что все заголовки оформлены в одном формате:

• порядковый номер;

• точка;

• пробел;

• непосредственно текст заголовка;

• точка с запятой.

Однако из заголовка нам нужен только текст, образующий имя поля списка. Поэтому следующим этапом работы будет очистка текста от лишних символов. Конечно, эту операцию можно произвести вручную, до или после копирования текста в электронные таблицы. но если записей будет несколько десятков или сотен, то вы потратите на это очень много времени и, скорее всего, внесете ошибки.

Справиться с данной работой можно довольно быстро, если использовать текстовые функции Excel. Алгоритм одного из таких методов представлен на рис.2.

Рис.2 - Алгоритм очистки текста от лишних символов

При этом предполагается, что формат перечня заголовков точно соответствует приведенному выше описанию. В противном случае (если, например, после точки вместо одного пробела введены два), алгоритм работать не будет.

1.1Определение длины текста заголовка.

Для определения количества символов в тексте, содержащемся в ячейке, используется функция ДЛСТР. Рассмотрим ячейку А4 со вставленным в нее текстом «7. порядковый номер; ». Поместите табличный курсор в ячейку В4, которая находится справа от ячейки А4, и вызовите окно Мастер функций. В списке Категория выберите элемент текстовые, в списке Функция выделите имя функции ДЛСТР, нажмите кнопку Ок, и на экране появится панель функции ДЛСТР (рис.3). Данная функция возвращает количество символов в текстовой строке. Синтаксис ее таков:

ДЛСТР (текст), где текст - это текст, в котором определяется количество символов. Имеющиеся в тексте пробелы также считаются символами.

Введите в поле Текст значение А4, нажмите кнопку Ок, и в ячейке В4 появится следующая формула:

=ДЛСТР(А4), таким образом мы узнаем, что в ячейке А4 находится текст, содержащий 20 символов.

Рис.3 - Панель функции ДЛСТР

1.2Удаление последнего символа из заголовка.

Следующий этап нашей работы - удаление символа «;» в конце строки. Выберите из текста, находящегося в ячейке А4, все символы, кроме последнего. Для этой цели следует использовать текстовую функцию ЛЕВСИМВ (рис.4). Она возвращает заданное количество символов текстовой строки, отсчитанных от ее начала, то есть слева. Синтаксис функции имеет такой вид:

ЛЕВСИМВ (текст;количество_символов), где текст - это текстовая строка, из которой извлекаются символы. Параметр количество_символов определяет, сколько символов должна извлечь функция ЛЕВСИМВ из текста.

Рис.4 - Панель функции ЛЕВСИМВ

Количество извлекаемых символов во втором аргументе функции следует задавать в виде числа. Это число может находиться в ячейке, адрес которой указан в данном аргументе. Введите функцию ЛЕВСИМВ в ячейку С4. Первым аргументом функции является адрес ячейки А4, где расположен исходный текст. В качестве второго аргумента использовано число, которое возвращает функция ДЛСТР, находящаяся в ячейке В4. Но если во второй аргумент ввести только адрес ячейки В4, то в ячейке С4 будет присутствовать все содержимое ячейки А4. Однако для удаления из исходного текста последнего символа во втором аргументе необходимо указать число, которое на единицу меньше количества символов, присутствующих в исходном тексте. Поэтому формула в ячейке С4 должна выглядеть следующим образом:

=ЛЕВСИМВ(А4;В4-1)

1.3Удаление начальных символов из заголовка

Для удаления из исходного текста начальных символов (то есть порядкового номера и пробела) можно использовать текстовую функцию ПРАВСИМВ, аналогичную описанной выше функции ЛЕВСИМВ. синтаксис функции ПРАВСИМВ такой же, только она отсчитывает символы, начиная с конца указанного текста, то есть справа.

При вводе этой функции в ячейку D4 в качестве исходного текста задаем строку без одного последнего символа, полученную в результате преобразования в ячейке С4.

Количество символов во втором аргументе не является одинаковым для всех формул диапазона D4:D24 и зависит от порядкового номера поля в исходном тексте. Для заголовков с порядковыми номерами от 0 до 9 во втором аргументе должно находиться число, равное количеству символов, рассчитанному функцией ДЛСТР в ячейке В4, уменьшенное на 4:

• порядковый номер - 1 символ;

• точка после порядкового номера - 1 символ;

• пробел после точки - 1 символ;

• символ «;», который уже удален предыдущей функцией ЛЕВСИМВ, но учитывается функцией ДЛСТР в ячейке в - 1 символ.

Таким образом, в ячейке D4 у нас будет такая формула:

=ПРАВСИМВ (С4;В4-4)

В заголовках с порядковыми номерами от 10 до 99 необходимо вычесть уже 5 символов, потому что данные номера состоят из двух цифр, для номеров от 100 до 999 - 6 символов и т. д. В данном случае мы создаем формулу, которая нуждается в последующей корректировке второго аргумента.

На рис.5 приведен рабочий лист для преобразования заголовков с формулами, а на рис.6 - результаты преобразований в текстовом виде.

Рис.5 - Рабочий лист с формулами преобразования текста

1.4Создание сложной формулы для обработки текста.

Для выполнения всех описанных выше действий можно создать одну формулу и разместить ее в одной ячейке. С этой целью воспользуемся методом вложения одной формулы (функции) в другую, при котором одни формулы (функции) применяются в качестве аргументов в других.

Продемонстрируем этот метод на примере. Сначала мы должны модифицировать ячейку В4.

• активизируйте ячейку В4 и в режиме правки в строке формул скопируйте (вырежьте) находящуюся в этой ячейке формулу без знака равенства.

• нажмите клавишу [Enter] и поместите табличный курсор в ячейку С4.

• в строке формул выделите ссылку на адрес ячейки В4 и вместо этой ссылки вставьте содержимое буфера обмена.

Рис.6 - Этапы преобразования текста

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

=ЛЕВСИМВ (А4;ДЛСТР(А4)-1)

Как видите, в качестве второго аргумента функции ЛЕВСИМВ, определяющего, какое количество символов текста необходимо вернуть, задано значение, возвращаемое функцией ДЛСТР (то есть количество символов в исходном тексте), уменьшенное на единицу.

Далее перейдите в ячейку D4 и произведите в ней аналогичную замену адреса ячейки на формулу. Формула в ячейке D4 должна быть похожа на предыдущую:

=ПРАВСИМВ (С4;ДЛСТР(А4)-4)

Затем возвратитесь к ячейке С4 и скопируйте находящуюся в ней формулу из строки формул. После этого нажмите клавишу [Enter], перейдите к ячейке D4 и вставьте в нее вместо ссылки на адрес ячейки С4 скопированную формулу. После всех этих манипуляций формула в ячейке D4 должна выглядеть следующим образом (рис.7):

=ПРАВСИМВ (ЛЕВСИМВ (а4;ДЛСТР (а4)-1);ДЛСТР (а4)-4)

Первым аргументом функции ПРАВСИМВ является уменьшенный на один символ текст, состоящий из начальных символов, возвращенных функцией ЛЕВСИМВ из исходного текста. Второй аргумент функции ПРАВСИМВ - это уменьшенное на 4 количество символов исходного текста, вычисленное функцией ДЛСТР.

Pис.7 - Рабочий лист со сложной формулой

ПРИМЕЧАНИЕ

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

1.5Перенос заголовков из столбца в строку

После удаления из названий столбцов лишних символов можно приступать к формированию строки заголовка списка. Для этого необходимо поместить имена столбцов в строку 1:

• выделите и скопируйте в буфер обмена, полученный после обработки текст из диапазона ячеек D4:D24.

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

• нажмите правую кнопку мыши и выделите в контекстном меню команду Специальная Вставка.

• в диалоговом окне Специальная Вставка отметьте опцию Транспонировать.

• нажмите кнопку Ок в результате все заголовки будут расположены по горизонтали в диапазоне А1:U1.

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

2Занесение информации в базу данных

Ниже приведены советы, которые помогут вам создать удобную для работы базу данных сотрудников.

1. При вводе табельного номера (или другого набора цифр), который начинается с нуля, помните, что Excel может воспринять его как число и отбросить этот нуль. Чтобы этого не произошло, следует указать перед нулем префикс «'» либо задать для ячейки (или диапазона) текстовый формат. Для выбора формата откройте диалоговое окно Формат ячеек на вкладке Число и отметьте в списке Числовые форматы элемент текстовый.

2. Для фамилии, имени и отчества выделяйте отдельные ячейки, а не вносите все эти данные в одно поле, где обрабатывать их довольно сложно. При необходимости содержимое ячеек можно объединить.

3. Наименование отдела (сектора, подразделения) и должность сотрудника также заносите в отдельные поля. Это позволит быстро определить, например, количество сотрудников в данном подразделении, текучесть кадров по указанной должности в течение определенного времени и многое другое. Представленные таким образом данные легко обрабатывать автоматически.

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

5. Если в таблице имеется столбец с таким заголовком, как улица, не заносите в него сокращение ул. (например, вместо ул. Красноармейская в поле достаточно ввести только слово Красноармейская либо для Проспекта - правды пр-т). Номера телефонов в табличной базе данных старайтесь записывать в одном стиле. К примеру, если один телефонный номер записан в виде 234-56-78, то не вносите другие как 234-5678 или 2345-678. Нарушение этих принципов значительно усложняет поиск и сортировку данных.

6. Не забывайте вносить в базу данных даты рождения сотрудников. Используя дополнительную сервисную функцию (она описана ниже), вы сможете определить, у кого из работников в ближайшее время день рождения, и поздравить этого человека.

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