Обработка списков или использование электронных таблиц как баз данных
Обычно базы данных (БД) представляют собой набор взаимосвязанных таблиц. Простейшие БД состоят из одной таблицы. В качестве такой базы данных вполне можно использовать электронную таблицу Excel. В Excel имеется набор функций, позволяющих выполнить все основные операции, присущие БД.
Информация в БД состоит из набора записей, каждая из которых содержит один и тот же набор полей. Записи характеризуются порядковыми номерами, а каждое поле имеет заголовок, описывающий его назначение.
В современных электронных таблицах термины база данных и список это фактически синонимы, однако, таблицы в документах Excel называют списками, а базами данных называют файлы таблиц, созданных другими системами обработки данных, такими как Microsoft Access, dBase или Fox Pro.
Для того чтобы таблица считалась списком, должны выполнятся следующие условия:
- Каждому полю записи соответствует один столбец рабочего листа. Каждый столбец должен содержать однородную информацию. Например, в списке успеваемости один столбец - фамилии, три других - оценки, последний - рейтинг.
- Верхняя строка в списке должна содержать метки, описывающие назначение соответствующего столбца (шапка таблицы). Заголовок поля должен занимать не более одной ячейки. Содержимое ячейки заголовка должно быть уникально в пределах рабочего листа.
- Столбцы должны идти подряд, без промежутков между ними.
- Записи должны идти непосредственно ниже строки заголовков. Пустые строки не допускаются, так как пустая строка рассматривается как признак окончания списка.
- Если на рабочем листе кроме списка есть другая информация, то список нужно отделить от остальных данных, по крайней мере, пустой строкой и пустым столбцом.
Проектирование электронных таблиц
Рассмотрим проектирование электронных таблиц на примере задачи расчета окладов сотрудникам некоего абстрактного предприятия. Будем считать, что оклад сотрудника определяется разрядом его должности, которому соответствует коэффициент, который мы будем умножать на минимальный размер оплаты труда.
Разработка любой электронной таблицы начинается с постановки цели. В нашем случае каждый раз должны пересчитываться должностные оклады с изменением минимальной оплаты труда.
Задание. Рассчитать оклады сотрудников, если известен минимальный оклад и тарифная сетка.
Проектирование этой электронной таблицы основано на использовании уже известных вам команд копирования и перемещения с учетом относительной и абсолютной адресации.
Сначала оформим заголовок задачи как надпись. Делается это следующим образом:
- Вкладка Вставка → группа Текст → кнопка Надпись.
- Перемещаем указатель в верхний левый угол предполагаемой надписи.
- Нажимаем левую кнопку мыши и, не отпуская, перемещаем до правого нижнего угла.
- В образовавшейся рамке набираем текст заголовка. Например, Оклады сотрудников.
- Для выхода щелкаем мышью в любом другом месте рабочего листа.
Теперь надпись можно форматировать, перемещать по листу, изменять размеры (как при работе с диаграммой).
Для форматирования текста надписи, нужно выделить текст. Затем можно воспользоваться кнопками из групп Шрифт и Выравнивание на вкладке Главная:
Теперь введем изменяемый параметр Минимальный размер оплаты труда с подсказкой. Для этого создадим маленькую табличку, которая будет располагаться над всеми нашими будущими таблицами и диаграммами. В дальнейшем, располагайте все изменяемые параметры и начальные значения (курсы валют, наценки, скидки и т.п.) в начале рабочего листа для большей наглядности и удобного доступа.
В ячейку А5 запишем текст Минимальный размер оплаты труда (это подсказка), а в ячейку B5 запишем число 4330 (адрес этой ячейки будем использовать для дальнейших расчетов):
Так как в ячейке A5 находится довольно длинный текст, отформатируем ее следующим образом (вкладка Главная → группа Ячейки → кнопка Формат → пункт Формат ячеек… или через контекстное меню – правая кнопка мыши → Формат ячеек…). На закладке Выравнивание устанавливаем выравнивание по горизонтали по центру и ставим “галочку” в пункте переносить по словам:
Для ячейки B5 установим выравнивание по горизонтали и по вертикали - по центру.
После создания рамочки получим окончательный вид нашего начального условия:
Создадим вспомогательную таблицу, заполнив сначала “шапку”:
Теперь автоматически заполним первый столбец для чего в А8 запишем 1. Оставив ячейку активной вызовем диалоговое окно Прогрессия для чего на вкладке Главная в группе Редактирование (Правка) надо нажать кнопку Заполнить:
В открывшемся списке выбираем пункт Прогрессия…:
В диалоговом окне Прогрессия устанавливаем Расположение по столбцам, Шаг: 1, Предельное значение: 20. После нажатия кнопки OK первый столбец заполнится номерами с 1 по 20.
Теперь автоматически заполним второй столбец. В ячейку B8 запишем 1 и вызовем диалоговое окно Прогрессия:
Таблица с тарифными коэффициентами, соответствующими разрядам, после форматирования примет окончательный вид:
Теперь создадим и заполним исходными данными основную таблицу:
Нам осталось заполнить только третий столбец. Для поиска разрядов в вспомогательной таблице будем использовать функцию ПРОСМОТР. Вводим в ячейку F8 формулу =$B$5*ПРОСМОТР(E8;$A$8:$A$27;$B$8:$B$27), затем делаем автозаполнение.
Функция ПРОСМОТР ищет значение (первый параметр) в строке или столбце (второй параметр) и возвращает соответствующее значение из строки или столбца (третий параметр):
ПРОСМОТР(искомое значение; просматриваемый вектор; вектор результатов)
Просматриваемые значения в строке или столбце (второй параметр) должны быть расположены в порядке возрастания.
Размеры строк или столбцов (второй и третий параметры) должны совпадать.
Полученная таблица в режиме значений:
Полученная таблица в режиме формул:
Полученные результаты представим в графическом виде. Для формирования диаграммы выделяем колонки со значением аргументов (левый столбец) и функции (правый столбец). Так как столбцы несмежные, то выделяем их при нажатой клавише <Ctrl>, причем выделяем столбцы вместе с шапкой таблицы D7:D18, F7:F18. Далее на вкладке Вставка в группе Диаграммы щелкаем по кнопке Круговая и выбираем Объемная разрезанная круговая:
Получим:
Щелкаем правой кнопкой мыши на любом секторе полученной фигуры и выбираем из контекстного меню пункт Добавить подписи данных:
Появятся значения окладов:
Опять щелкаем правой кнопкой мыши на любом секторе полученной фигуры и выбираем из контекстного меню пункт Формат подписей данных…:
На закладке Параметры подписи убираем “галочку” в пункте значения и ставим “галочку” в пункте доли:
Осталось отредактировать заголовок диаграммы. Новое название может, например, быть таким - Процентное соотношение величины окладов сотрудников:
