БД	СУБД
Пользователь0	Файл0	Программа0
Пользователь1	Файл1	Программа1
…	…	…
Пользовательm	Файлn	Программаk
		АБД
	СБД
Рис. 2. Схема системы баз данных

2. БАЗЫ ДАННЫХ MS EXCEL

Технология автоматизированных баз данных (АБД) – неотъемлемая составляющая любой ИТ (рис. 1), а собственно АБД входит в состав любой ИС. Классическим определением автоматизированной базы данных является следующее. АБД по своей физической сущности есть не что иное, как совокупность взаимосвязанных файлов, содержащих структурированную информацию о той или иной предметной области деятельности человека. В зависимости от модели данных, используемой для хранения информации в базе, принято различать иерархические, сетевые и реляционные БД. Получившие наибольшее распространение реляционные базы данных представляют собой совокупность таблиц, которые могут храниться в виде отдельных файлов. Таблицы естественны для MS Excel – программы, предназначенной для решения задач, информация (входная и выходная) для которых представима в табличном виде. MS Excel располагает встроен-

6

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

2.1. Списки MS Excel как база данных

Списком MS Excel называется таблица, оформление которой отвечает следующим требованиям:

1)список состоит из строк, называемых записями;

2)столбцы списка, называемые полями, должны содержать однородную (однотипную) информацию;

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

4)внутри списка не должно быть пустых строк и столбцов, которыми список отделяется от остальной части рабочего листа;

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

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

2.2. Создание списков

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

7

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

ООО «Фирма», для автоматизации начисления им их заработной платы. Структура такого списка может быть достаточно простой (табл. 1).

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

Cтаж, Премия, Начислено, Выплачено, страховые взносы в

ПФР, ОМС, ФСС. В качестве вычисляемых полей могут выступать, в зависимости от предметной области, например, стоимость партии товара, время до истечения срока годности продукта, стоимость услуги в у. е. и т. п. При формировании вычисляемых полей необходимо придерживаться двух основных правил:

1)ссылки на ячейки внутри списка, а это поля одной и той же записи, должны быть относительными;

2)ссылки на ячейки вне списка (элементы, например, справочника) должны быть абсолютными.

Рис. 3. Список сотрудников

8

							Таблица 1
			Структура списка сотрудников
	№	Имя поля	Тип поля	Назначение			Комментарий
	1	2	3		4		5
	1	Фамилия	Текстовое	Эти	поля		Строки состоят из букв
	2	Имя	Текстовое	предназначе-			кириллицы без ведущих
				ны для хране-			и хвостовых пробелов,
	3	Отчество	Текстовое	ния	ФИО	со-	первый символ – про-
				трудника			писная буква
		Дата		Дата	приема
	4	приема на	Дата	на работу		со-	Например, «23.11.2008»
		работу		трудника
							Вычисляется по форму-
							ле: (текущая дата–
	5	Стаж	Вычис-	Стаж в годах			Дата приема на
			ляемое				работу)/365, т. е.
							=(СЕГОДНЯ()–
							D2)/365
				Название		от-
	6	Отдел	Текстовое	дела,	в кото-		См. поле 1
				ром	работает
				сотрудник
				Наименование
				должности,
	7	Должность	Текстовое	которую		за-	См. поле 1
				нимает		со-
				трудник
	8	Оклад	Числовое	Размер оклада			Положительное число
				сотрудника
							Вычисляется по формуле:
							=ЕСЛИ(E2>=Ставки!
				Размер начис-			$F$4;H2*Ставки!$G
			Вычис-				$4;ЕСЛИ(E2>=Ставк
	9	Премия	ляемое	ляемой		пре-	и!$F$3;H2*Ставки!
				мии			$G$3;ЕСЛИ(E2>=Ста
							вки!$F$2;H2*
							Ставки!$G$2;0)))

9

Продолжение таблицы 1

	1	2	3		4			5
							Вычисляется		по	фор-
				Величина		на-	муле:	Оклад		+
			Вычис-	численной за-			Премия	+	район-
	10	Начислено					ный	коэффици-
			ляемое	работной		пла-	ент, т. е.
				ты			=(H2+I2)*Ставки!
							$B$1+(H2+I2)
							Вычисляется по форму-
			Вычис-	Величина		за-	ле: Начислено			-
	11	Выплачено		работной		пла-	НДФЛ, т. е.
			ляемое
				ты к выплате			= J2-
							(J2*Ставки!$B$2)
				Сумма		стра-	Вычисляется		по	фор-
				ховых взносов			муле:	Начисле-
	12	ПФР	Вычис-	для	перечис-		но*22%, т. е.
			ляемое	ления	в	пен-	=J2*Ставки!$B$3
				сионный фонд			(№212-ФЗ			от
				РФ			17.07.2009)
				Сумма		стра-	Вычисляется		по	фор-
	13	ОМС	Вычис-	ховых взносов			муле:	Начисле-
			ляемое	для	перечис-		но*5,1%, т. е.
				ления	в	фонд
				ОМС			= J2*Ставки!$B$4
				Сумма		стра-	Вычисляется		по	фор-
	14	ФСС	Вычис-	ховых взносов			муле:	Начисле-
			ляемое	для	перечис-		но*2,9%, т. е.
				ления в ФСС			= J2*Ставки!$B$5
		В нашем примере для вычисления полей ПФР, ОМС, ФСС ис-
	пользуются ставки, установленные					Федеральным		законом от

24 июля 2009 г. № 212-ФЗ «О страховых взносах в Пенсионный фонд Российской Федерации, Фонд социального страхования Российской Федерации, Федеральный фонд обязательного медицинского страхования» (с изменениями и дополнениями). Значения ставок вынесены на отдельный лист. Такой лист обычно называют справочником. Это удобно в том случае, если значения ставок изменятся законодательно, то достаточно будет лишь изменить их значения на листе (в справочнике) Ставки (рис. 4).

10

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

Рис. 4. Лист-справочник Ставки

Рис. 5. Формула для вычисляемого логического поля

Заполняется данными список, как правило, вручную. К основным действиям по обработке списков относятся следующие: добавление, удаление, редактирование, просмотр и поиск записей. При работе со списком перед обращением к вкладке Данные в обязательном порядке необходимо активизировать любую ячейку внутри списка. В этом случае MS Excel автоматически распознает интервал списка (базу данных). Таблицы баз данных MS Access на рабочие листы MS Excel можно перенести и через буфер обмена. Вместе с тем MS Excel имеет специальные, достаточно развитые возможности экспорта и импорта файлов баз данных, созданных другими приложениями. Доступ к этим воз-

11

можностям реализуется с помощью команд вкладки

Данные/Получить внешние данные. При этом драйверы Microsoft Office позволяют получать сведения из следующих источников данных:

1)Microsoft SQL Server OLAP Services;

2)Microsoft Access;

3)dBASE;

4)Microsoft FoxPro;

5)Oracle;

6)SQL Server;

7)текстовые файлы баз данных.

Рис. 6. Фрагмент списка с логическим вычисляемым полем

Внешние данные могут быть получены из баз данных с по-

мощью Microsoft Visual Basic, веб-запросов и Microsoft Query.

Чаще всего настройку источников данных для MS Excel осуществляют посредством Microsoft Query – инструмента, предназначенного для установления подключения к внешним базам данных и создания запросов для извлечения данных. Microsoft Query входит в состав дополнительных компонентов Microsoft Office.

12