2.2 Организация работы с базой данных

Заполнение таблиц модельными данными

В соответствии с проектом у нас должны быть следующие таблицы:

1. Тарифы

2. Расчеты по тарифам

3. Управляющие компании

4. Клиенты

5. Расчеты с клиентами

6. Расчеты по квартплате

Переходим на создание первого листа «Тарифы». Шапка таблицы имеет следующий вид:

	С	D	E	F	G	H
4	Код	Наименование	Способ расчета	Ед. измерения	Стоимость услуг	Статус заказа
55 5	1	Водоснабжение	По показаниям счетчика	куб.метр	19,85	Выполнено
6	2	Горячее водоснабжение	По показаниям счетчика	куб.метр	56,75	Выполнено
7	3	Холодное водоснабжение	По показаниям счетчика	куб.метр	8,00	Выполнено
8	4	Канализация	По показаниям счетчика	куб.метр	8,35	Выполнено
9	5	Газовое снабжение	По количеству проживающих	куб.метр	57,51	Выполнено
10	6	Электрическая энергия	По показаниям счетчика	руб./кВт*ч	2,06	Выполнено
11	7	Содержание жилого дома с удобствам, с уборкой в подъездах	По общей площади	кв.метров	8,77	Выполнено
12	8	Идеальный ремонт жилого дома с удобствами	По общей площади	кв.метров	2,60	Выполнено
13	9	Содержание жилого дома без удобств	По общей площади	кв.метров	4,50	Выполнено
14	10	Косметический ремонт жилого дома без удобств	По общей площади	кв.метров	2,00	Выполнено
15	11	Содержание жилого дома с удобствами,без уборки в подъездах	По общей площади	кв.метров	4,67	Выполнено

Присваваем этой таблице имя «тарифы». Данное имя будет доступно с любого листа.

Рассмотрим пример заполнения таблицы «расчеты по тарифам».

B	C	D	E	F	G	H	I	J	K	L
№УК	Дата заявки	Дата оказания услуги	Код	Наименование услуги	Адрес	Способ расчета	Ед. измер.	Цена	Количество	Стоимость

Начнем с колонки «Дата заявки».

– в отдельную ячейку в A1 вводим начальную дату продаж – пусть это

будет 01.01.12. Задаем для этой ячейки формат «общий». В ней получится число 40909.

Поэтому в ячейку С18 вводим формулу:

= 40909+ ЦЕЛОЕ(30 * СЛЧИС())

и копируем ее на 300 строк данного столбца.

Чтобы избавиться от этого эффекта СЛЧИС:

– выделяем столбец C и копируем его в буфер;

– не снимая выделения произведем перекопирование данных;

– не снимая выделения, преобразуем данные столбца C в формат «Дата».

Удаляем из А1 ненужную теперь дату.

В D18 вводим формулу:

= 40969+ ЦЕЛОЕ(30 * СЛЧИС())

Избавляемся от эффекта случайных чисел.

В Е18 вводим формулу:

= 1+ ЦЕЛОЕ(11 *СЛЧИС())

Копируем на 300 строк. Избавляемся от эффекта СЛЧИС.

В F18 вводим формулу:

=ВПР(E18;тарифы!$C$5:$H$15;2)

Н18 будет формула:

=ВПР(E18;тарифы!$C$5:$H$15;3

I18 вводим формулу:

=ВПР(E18;тарифы!$C$5:$H$15;4)

В J18 находим цену по формуле:

=ВПР(E18;тарифы!$C$5:$H$15;5)

Количество (ячейка К18) находим по эффекту СЛЧИС:

=1+ЦЕЛОЕ(300*СЛЧИС())

Стоимость вычисляется по формуле:

=K18*J18,т.е. количество умножаем на цену услуги.

В М18 находим управляющую компанию. Для этого используем

формулу:

=ВПР(B18;управляющиекомпании!$C$11:$H$91;2)

И в конце в ячейке N18 находим статус заказа. Для этого введем

формулу:

=ВПР(E18;тарифы!$C$5:$H$15;6)

Все ячейки копируем на 300 строк. Таблица готова.

Сортировка

Сортировка является типовой операцией с базами данных и

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

Рис. 1.4. Интерфейс реализации операции Сортировка

С помощью предлагаемого интерфейса сортировка выполняется

следующим образом:

– из списка «Сортировать по…» выбирается поле сортировки и щелчок

по кнопке «Сортировать».

Создание списка полей

– на текущем листе (где–то в стороне, так, чтобы этого потом не было

видно на экране) печатается список полей:

	Q
4	Дата заявки
5	Дата оказания услуги
6	Код
7	Наименование услуги
8	Адрес
9	Способ расчета
10	Ед. измер.
	Цена
11	Количество
12	Стоимость
13	2

– вызываем панель форм, на ней выбираем элемент «Поле со списком» и рисуем его в районе ячейки D4;

– ставим мышь на нарисованный элемент, щелчком ПКМ вызываем контекстное меню и выбираем пункт «Формат объекта», при этом откроется окно формата создаваемого списка, рис. 1.5.

Рис. 1.5. Окно Формат элемента управления

– в поле «Формировать список по диапазону» указать местоположение списка полей;

– в поле «Связь с ячейкой» указать ячейку, в которую будет

записываться номер выбранного поля.

– щелкнуть «Ok».

Произведите несколько выборок в получившемся списке и посмотрите,

что происходит в ячейке Q13.

Создание кнопки для запуска макроса

– с панели «Формы» взять элемент «Кнопка» и нарисовать ее районе

ячейки G4;

– на запрос о назначении макроса указать макрос «Сортировка»;

– исправить надпись на кнопке.

Макрос для кнопки сортировка будет выглядеть следующим образом:

Sub сортировать()

Dim k As Integer 'Объявляем переменную целого типа

Range("C18").Select 'Выделяем ячейку C18

k = Range("Q15") 'Определяем номер выбранного пункта

Range("C18:L316").Sort Key1:=Cells(18, k + 2), Header:=xlGuess

End Sub

Поиск данных

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

На рис. 1.6 приведен возможный вариант интерфейса для организации поиска.

Рис. 1.6. Интерфейс для организации операции поиска

Поиск производится следующим образом:

– в группе полей «Наименование услуги» вводятся нужные значения;

– щелкается кнопка «Найти».

Кнопка «Отобразить все» предназначена для восстановления исходной

таблицы.

Технология создания элементов интерфейса аналогична предыдущему

разделу – т.е. сначала пишутся макросы, выполняющие нужные операции, а затем создаются кнопки, связанные с этими макросами.

Итак, поэтапно.

1. В ячейках С8:L9 сформировать шаблон для ввода критериев

поиска.

Для автоматизации ввода наименований можно поступить следующим

образом:

- с листа «Тарифы» скопируем на данный лист (в ячейки S5:S15)

наименование услуг;

- устанавливаем курсор в F9 и выполняем команды:

Данные > Проверка > В появившемся окне > В поле «Тип данных» выбираем «Список» > В поле «Источник» указываем адрес списка ( т.е. S5:S15) > Ok.

Рис. 1.7.

Рис. 1.7. Окно «Проверка вводимых значений»

Таким же образом делаем список в ячейку Е9, т.е. «Код ».

Если сейчас перейти в нужные нам ячейки, то там появится флажок

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

2. Записываем макрос для кнопки «Найти».

Макрос будет иметь следующий вид:

Sub Найти()

Range("C17").Select

Range("C17:M316").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

Range("C8:L9"), Unique:=False

End Sub

3. Запишем макрос для кнопки «Отобразить все».

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

Sub Отобразить_все()

ActiveWindow.SmallScroll ToRight:=-2

Range("C17").Select

ActiveSheet.ShowAllData

End Sub

Переходим на создание листа «Управляющие компании».

Шапка будет выглядеть следующим образом:

	С	D	E	F	G	H
11	Код управляющей компании	Управляющие компании	Район	Общая площадь жилых помещений, м2	Общее количество жильцов	Площадь помещения на 1 человека,м2

Все столбцы заполняются самостоятельно. Для вычисления площади

помещения на одного человека в Н12 вводится формула:

=F12/G12,т.е. общая площадь жилых помещений делится на общее

количество жильцов.

Для удобного использования создаем поиск данных.

Поиск производится следующим образом:

– в группе полей «Район» вводятся нужные значения;

– щелкается кнопка «Найти».

Кнопка «Отобразить все» предназначена для восстановления исходной

таблицы.

Технология создания элементов интерфейса аналогична предыдущему

разделу – т.е. сначала пишутся макросы, выполняющие нужные операции, а затем создаются кнопки, связанные с этими макросами.

Макрос для кнопки «Найти» будет иметь следующий вид:

Sub Найти2()

Range("C11").Select

Range("C11:H91").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

Range("C4:H5"), Unique:=False

End Sub

Кнопка отобразить все будет иметь такой вид:

Sub Отобразить_все2()

Range("C11").Select

ActiveSheet.ShowAllData

End Sub

Рис. 1.8.

Рис.1.8. Интерфейс страницы управляющие компании

Создание листа «Клиенты». Таблица заполняется самостоятельно и имеет следующий вид:

	С	D	E	F
7	Код	Название	Адрес	Телефон
8	1	ОАО "Чувашсетьгаз"	ул.И.Франко,26	8(835)129845
9	2	филиал "Чебоксарыгоргаз"	ул.Николаева,12	8(835)569846
10	3	ОАО "Промтрактор"	пр.Тракторостроителей,101	8(835)156390
11	4	Химпром	ул.Машиностроителей,20	8(835)178938
12	5	Энегозапчасть	ул.50 лет Октября,56	8(835)179329
13	6	Агрегатный завод	пр.Мира,29	8(835)126780
14	7	Эллара	ул.Афанасьева,23	8(835)123028
15	8	Электро-механический завод	Вурнарское шоссе,45	8(835)129852

Этот лист потребуется для помощи создания следующего листа.

Создание листа «Расчеты с клиентами». Шапка этой таблицы будет

иметь следующий вид:

	C	D	E	F	G	H	I	J	K
18	Код УК	Управляющая компания	Код	Услуги	Количество	Код клиента	Клиент	Цена	Стоимость

В D19 (управляющая компания) вводим формулу:

=ВПР(C19;управляющиекомпании!$C$11:$H$91;2)

В F19 также будет формула для начисления услуг. Она будет иметь

следующий вид:

=ВПР(E19;тарифы!$C$5:$H$15;2)

Ячейки код и количество исчисляются с помощью случайных чисел.

В I19 вводим формулу:

=ВПР(H19;клиенты!$C$7:$F$15;2)

В J19 вводится формула:

=ВПР(E19;тарифы!$C$5:$H$15;5

И в К19 будет следующая формула:

=G19*J19

Все столбцы копируются на 300 строк. Присваиваем таблице имя

«данные2». Так же как и в других разделе создаем сортировку следующего вида (рис. 1.9.).

Рис.1.9. Интерфейс страницы расчет к клиентами

Макрос для сортировки будет иметь следующий вид:

Sub сотрировка2()

Dim k As Integer 'Объявляем переменную целого типа

Range("C18").Select 'Выделяем ячейку C18

k = Range("O11") 'Определяем номер выбранного пункта

Range("C18:K317").Sort Key1:=Cells(18, k + 2), Header:=xlGuess

End Sub

В конце проверяем исправность сортировки.

Начинаем заполнять лист «расчеты по квартплате».

Все столбцы заполняются самостоятельно. С помощью всех данных в

Н12 вводится формула:

=F12-G12,т.е. из начисленной суммы вычитается оплаченная. Так мы

нашли долг на 01.05.12г. Формула копируется на 80 строк. Данной таблице присваивается имя «данные3».

Для удобного использования создаем поиск данных.

Поиск производится следующим образом:

– в группе полей «Услуга» вводятся нужные значения;

– щелкается кнопка «Найти».

Кнопка «Отобразить все» предназначена для восстановления исходной

таблицы.

Технология создания элементов интерфейса аналогична предыдущему

разделу – т.е. сначала пишутся макросы, выполняющие нужные операции, а затем создаются кнопки, связанные с этими макросами.

Макрос для кнопки «Найти» будет иметь следующий вид:

Sub Найти3()

Range("B11").Select

Range("B11:H164").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

Range("B5:H6"), Unique:=False

End Sub

Макрос для кнопки «отобразить все» имеет следующий вид:

Sub Отобразить_все3()

Range("B11").Select

ActiveSheet.ShowAllData

End Sub

Таблица готова.

Отчеты

Отчеты представляют собой некоторую выходную информацию,

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

Использование встроенных функций

Предположим, что периодически нам необходимы данные о выручке

услуг за определенный период времени.

Интерфейс расчетов может выглядеть следующим образом:

	A	B	C	D	E	F
3
4			Отчетный период
5
6			Начало периода	01.01.2012
7			Конец периода	01.03.2012
8
9
10
11			Выручка	3817
12

Рис.1.10. Интерфейс расчета

Вычисления производятся следующим образом:

– в Е6 и Е7 вводятся даты начала и конца отчетного периода, а ячейке

Е11 отражается результат вычислений.

Для организации вычислений:

– на этом же листе за пределами экрана создаем шаблон критерия отбора;

	Q	R
6	Дата заявки	Дата оказания услуг
7	>=40909	<=40969

Рис. 1.11. Размещение критериев для расчета выручки за определенный период времени

– в Q7 вводим формулу =">="&E6;

– в R7 вводим формулу ="<="&E7;

– в E11 вводим формулу:

=ДМАКС(БазаДанных;расчетпотарифам!L17;Q7:R8).

Использование встроенных функций в макросах.

Cделаем вариант с выбором вида расчета.

Рис. 1.11. Интерфейс выбор вида расчета

Из раскрывающегося списка выбирается вид расчета и затем щелчок по

кнопке «Рассчитать».

Технология создания такого интерфейса уже описана в разделах

Сортировка, Поиск.

Поэтому дадим только краткие комментарии:

- для выбора операции используется элемент «Поле со списком»;

- этот элемент связан со списком операций, который введен в ячейки V2:V5;

- с этим списком связана ячейка V6.

Макрос для кнопки «Рассчитать» будет иметь вид:

Sub Рассчитать()

k = Range("V6")

Select Case k

Case 1

Range("E11") "=DSUM(БазаДанных,расчетпотарифам!L17,Q7:R8)"

Case 2

Range("E11")= "=DAVERAGE(БазаДанных,расчетпотарифам!L17,Q7:R8)"

Case 3

Range("E11") "=DMAX(БазаДанных,расчетпотарифам!L17,Q7:R8)"

Case 4

Range("E11")= "=DMIN(БазаДанных,расчетпотарифам!L17,Q7:R8)"

End Select

End Sub

Использование сводных таблиц

Используя значение сводной таблицы сделаем таблицу «отчет по

расчету с клиентами» следующего вида:

Рис.1.12. Отчет по расчету с клиентами

Используя значение сводной таблицы сделаем таблицу «отчет по расчету квартплаты» следующего вида:

Рис.1.13. Отчет долгов по каждому виду услуг

В этой таблице можно посмотреть сколько долгов по каждому виду услуг за 01.05.2012 год.