Работа с базами данных в MS Excel (стр. 5 из 6)
2.1.1. Структура базы данных
В соответствии с заданием база данных должна содержать следующие поля:
| № поля | Имя поля | Тип поля | Тип данных | Длина поля |
| 1 | ФИО | Символьный | Исходные | 20 символов |
| 2 | Бригада | Символьный | Исходные | 12 символов |
| 3 | Специальность | Символьный | Исходные | 15 символов |
| 4 | Оклад | Числовой | Исходные | 4 символа |
| 5 | Премия | Числовой | Вычисляемые | 4 символа |
| 6 | Начислено | Числовой | Вычисляемые | 4 символа |
| 7 | Налог | Числовой | Вычисляемые | 8 символов |
| 8 | К выплате | Числовой | Вычисляемые | 8 символов |
Рис.2.1 Структура проектируемой базы данных.
2.1.2. Определение формул для вычисляемой части базы данных.
В создаваемой базе несколько вычисляемых полей Определим зависимости, по которым вычисляются значения в этих полях. Введем условные обозначения, которые будем использовать при составлении формул:
Премия – П;
Оклад – О;
Стаж- С;
Начисленная сумма - НС;
Подоходный налог - ПН;
1.Премия.
В соответствии с условиями премия начисляется сотрудникам, проработавшим определенное время на фирме. Для сотрудников со стажем от 2-х до 5-ти лет премия составит 15% оклада, со стажем более 5 лет 25% оклада.
При использовании Мастера функции логическое выражение для вычисления премии приобретет вид:
П= Если (С <= 2; 0; если(С>5;0.25*O;0.15*O))
2.Начисленная сумма. Значение начисленной суммы определяется как результат сложения значения оклада и премии.
НС=П+О.
3.Подоходный налог.
Подоходный налог определяется в зависимости от величины начисленной суммы: не облагаются налогом суммы до 70 грв. включительно; при начисленной сумме более 250 грв. подоходный налог составляет 20% от суммы; в остальных случаях подоходный налог равен 10% от суммы.
ПН=
При использовании Мастера функций логическое выражение примет вид:
ПН=Если(НС<=70;0;если(НС>250;0.2*НС;0,1*НС))
4. К выплате. Значение определяется как разность Начисленной суммы и Подоходного налога.
К_вып=НС-ПН
В результате проектирования базы данных получен эскиз таблицы следующего вида.
| A | B | C | D | E | F | G | H | I | |
| 1 | ФИО | Бригада | Специальность | Оклад | Стаж | Премия | Начисленная сумма | Подоходный налог | К выплате |
| 2 |
Рис. 2.2. Эскиз таблицы для заполнения базы данных.
В таблице исходные данные отмечены синим цветом, а вычисляемые значения – красным.
2.2. Создание базы данных.
2.2.1 Создание заголовка таблицы и первой строки.
Для создания таблицы раскрываем приложение Excel Microsoft Office. После ввода названия таблицы заполняем заголовки столбцов и форматируем их. Для этого выбираем команды меню Формат Столбец Ширина и устанавливаем необходимые значения ( в соответствии с количеством символов в каждом поле, оговоренном в структуре базы данных). После ввода заголовков столбцов выбираем соответствующий вид форматирования. Для этого используем пункты меню Формат Ячейки и активизируем соответствующие вкладки: Число, Выравнивание, Шрифт. В числовых полях задаем 2 знака после точки, выравнивание в тестовых полях устанавливаем по правому краю ячейки, выравнивание числовых полей выполняем по центру.
После форматирования заголовков стролбцов приступаем к заполнению 1-й строки. Вводим исходные данные и в вычисляемых полях записываем расчетные формулы.
2.2. Заполнение таблицы с помощью Мастера форм.
Дальнейшее заполнение данных выполняем с помощью пунктов меню Данные Форма (в этот момент курсор должен быть установлен на ячейке в области создаваемой таблицы). При этом открывается окно диалога Мастера форм с заполненной первой записью исходной базы данных.
Щелкая на клавише «Добавить» в окне диалога и последовательно заполняя пустые поля исходными данными, создаем исходную базу данных.
2.3. Ведение базы данных.
Ведение базы данных заключается в корректировке существующих данных, добавлении новых, удалении полей, содержащих ненужную или ошибочную информацию. В соответствии с заданием выполним операции по редактированию, добавлению, удалению информации. При работе в среде электронных таблиц для этого могут быть использованы различные средства.
2.3.1. Редактирование полей.
Добавим к существующей базе поле, которое отражает порядковый номер записей в базе. Для этого установим курсор в ячейку первого столбца и активизируем пункт меню Вставка Столбцы. В появившемся пустом столбце запишем название поля и заполним его. Результат поместим в новый файл. Удаление полей осуществим с помощью команд меню Правка Удалить Удалить столбец. При этом курсор необходимо установить в поле , которое собираемся удалить. На Рис. 2.6 показан вид базы данных с добавленным полем №, а на Рис.2.7 показана база данных, из которой удалили поля № и «Бригада».
2.2. Редактирование записей
Для удаления записей из базы данных необходимо эти записи выделить и активизировать команды меню Правка Удалить Строку . В результате выполнения этих действий строка, в которой был установлен курсор будет удалена. Для добавления строк в базу данных необходимо выполнить следующие действия: активизировать команды меню Вставка Строки. В результате в базу данных будет добавлена пустая строка над строкой, в которой находился курсор. Далее заносим нужные сведения в добавленную строку.
в ячейке сохранится откорректированная информация. Добавим к исходной базе строку, содержащую информацию о рабочем Васечкине и исправим фамилию Иванов на Иванченко в 1-й записи. При редактировании можно также пользоваться окном диалога Мастера форм.
2.4.Начальная обработка данных.
2. 4.1. Добавление суммы по столбцам.
Добавим в числовых полях суммирование по столбцам.
Формулы для вычисления сумм в ячейках F27, G27,H27,I27,J27 будут выглядеть соответствующим образом.
F27=СУММ(F7:F26); G27=СУММ(G7:G26); H27=СУММ(H7:H26);
I27=СУММ(I7:I26) J27=СУММ(J7:J26)
2.4.2. Добавление суммирования по критерию.
Используем функцию СУММЕСЛИ для добавления суммы по заданному условию. В соответствии со справкой Мастера формул функция может быть представлена в общем виде как СУММЕСЛИ(диапазон; условие; диапазон_суммирования) В нашем случае просуммируем премиальный фонд ветеранам труда (стаж должен превышать 10 лет). Расчетная формула примет вид:
G27=СУММЕСЛИ(E7:E26;>10;G7:G26)
Результат использования формулы приведен на Рис. 2.10.
2.5. Сортировка базы данных.
2.5.1. Простая сортировка по полю.
Для обычной сортировки базы данных по одному полю ( по возрастанию или по убыванию) необходимо воспользоваться пунктом меню Данные Сортировка или пиктограммами на графическом меню.
Сортировка по возрастанию Сортировка по убыванию
Рис.2.11. Пиктограммы сортировки.
При этом курсор должен быть установлен в поле, которое будем сортировать. Для сортировки по возрастанию по полю «Стаж» установим курсор на ячейку в этом поле и выберем направление сортировки «по возрастанию». Результат сортировки представим на Рис.2.12.
Рис.2.12. Сортировка по полю «Стаж» по возрастанию.
2.5.2 Сортировка по нескольким полям.
Для проведения более сложной сортировки (по нескольким полям) откроем окно диалога «Сортировка диапазона» (см. Рис.2.13). Для этого выполним команды меню Данные Сортировка. Чтобы сортировка выполнялась по двум или трем полям в окне диалога для каждого диапазона задаем направление сортировки. Наименование диапазонов выбираем в окне диалога , раскрывая список наименований (щелкаем последовательно по областям окна диалога «Сортировать по», «Затем по», «В последнюю очередь по»), и указываем направление сортировки по каждому полю (убывание/возрастание). В нашем примере выполним сортировку по трем критериям: по полю «Стаж», затем по полю «Специальность» и в последнюю очередь по полю «Оклад».Для всех трех критериев задаем направление сортировки «По возрастанию». Результат выполнения сортировки по нескольким критериям поместим на Рис.2.14.