Файл: Контрольная работа по теме Базы данных в Excel 72 IV. Макросы в ms excel 78 Макросы для автоматизации работ 78.doc

5.2.3.4.2. Работа с данными

В этом разделе вся работа ведется на листе «Продажи». Очевидно, что при необходимости все описанные здесь методы можно применить к данным любого другого листа.

Для операций по вводу, удалению и корректировке данных в Excel имеется встроенное средство – форма ввода данных.

Она вызывается из главного меню командой:

Данные > Форма.

С помощью появившейся формы можно выполнить все указанные операции.

Для «цивильного» вызова этой формы создадим на листе кнопку с именем «Данные» и для нее создадим макрос следующего содержания:
Sub Работа_с_данными()

Range("C11").Select ‘Переход на ячейку БД

CommandBars.FindControl(ID:=860).Execute ‘Вызовформывводаданных

ActiveWorkbook.Names("Данные_продаж").Delete

ActiveCell.CurrentRegion.Name = "Данные_продаж"

End Sub
При вводе или удалении данных размеры БД могут изменяться. По этой причине в макрос добавлены еще две команды:

первая – удаляет имеющееся имя БД:

вторая – определяет новый размер БД и присваивает ему только что удаленное имя.
5.2.3.4.3. Сортировка

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

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

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

– из списка «Сортировать по…» выбирается поле сортировки (на рис. 5.5 уже выбрано поле «Дата продажи») и щелчок по кнопке «Сортировать».
Создание со списка полей

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

	P	Q	R
3
4
5		Дата продажи
6		Код товара
7		Наименование
8		Количество
9		Цена
10		Сумма
11		3
12

---

– вызываем панель форм (Вид > Панели > Инструментов >Формы), на ней выбираем элемент «Поле со списком» и рисуем его в районе ячейки D3 (как на рис. 5.6);

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


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

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

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

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

Произведите несколько выборок в получившемся списке и посмотрите, что происходит в ячейке Q11.
Создание макроса для сортировки

Выполните команды: Сервис > Макрос > Начать запись > На запрос об имени макроса напечатайте «Сортировка» > «Ok» > Установите курсор в C11 > Данные > Сортировка > В качестве поля сортировки выберите «Наименование» > «Ok» > Сервис > Макрос > Остановить запись.

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

– с панели «Формы» взять элемент «Кнопка» и нарисовать ее районе ячейки E3 (как на рис. 5.6):

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

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

Если сейчас щелкнуть по созданной кнопке, то данные должны будут отсортироваться по полю «Наименование».

Модификация макроса

Точно так же можно сделать кнопки для сортировки по остальным полям. Но все это как–то «не смотрится». Тем более, что работа кнопки никак не зависит от выбранного в списке поля сортировки.

Посмотрим, что записано в созданном макросе.

Выполним команды Сервис > Макрос > Макросы > Выбрать макрос «Сортировка» > Изменить.

Появится текст макроса.
Sub Сортировка()

Range("C11").Select

Range("Данные

---

_продаж").Sort Key1:=Range("E12"),Order1:=xlAscending,Header:= _

xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal

EndSub

Номинальное знание английского языка позволяет понять записанные команды и по возможности изменить их.

Первая команда соответствует переходу на ячейку «С11» (когда мы щелкнули по ней).

Вторая команда очень длинная, занимает три строчки и выполняет метод сортировки для диапазона «Данные_продаж».

Основная часть команды – Range("Данные_продаж").Sort выполняет сортировку выделенной части. Остальные компоненты – это параметры сортировки, которые можно частично или все удалить.

Нас интересует параметр Key1, который определяет поле сортировки. Его значение, равное E12, соответствует столбцу E, в котором находится поле «Наименование». Если сейчас вместо E11 напечатать G11 и в Excel щелкнуть по кнопке «Сортировка», то сортировка произойдет по полю «Цена».

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

В Excel для обращения к ячейкам существует два способа.

Первый – с помощью объекта Range (как в приведенном макросе).

Второй – с помощью объекта Cells следующего формата:
Cells(Номер строки, Номер столбца).
Способы эквиваленты и используются по ситуации. Например, вместо Range(«C11») вполне можно записать Cells(11, 3).

Поэтому макрос можно переписать следующим образом:
Sub Сортировка()

DimkAsInteger ‘Объявляем переменную целого типа

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

k=Range(“Q11”) ‘Определяем номер выбранного пункта

Range("Данные_продаж").Sort Key1:=Cells(12,k+2), Header:=xlGuess

EndSub
Здесь из параметров сортировки оставлен лишь два параметра – ключ сортировки и наличие заголовка.

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

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

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


Рис. 5.8. Интерфейс для организации операции поиска
Поиск производится следующим образом:

---

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

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

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

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

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

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

Обратите внимание на следующие моменты:

- в шаблоне нет поля «Код товара». Это связано с тем, что данное поле связано с полем «Наименование» и эти поля дублируют друг друга. Поэтому при поиске можно использовать любое из них.

- нельзя заставлять пользователя вручную вводить наименование товара.

Очевидно, что в подавляющем большинстве случаев он введет что-то «не то».

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

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

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

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

Рис. 5.9. Окно «Проверка вводимых значений»
Если сейчас перейти в ячейку E7, то там появится флажок раскрытия списка, с помощью которого можно выбрать нужный товар.

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

Выполним команды Сервис > Макрос > Начать запись > На запрос об имени макроса напечатать имя «Найти» > Установить курсор в C11 > Данные > Фильтр > Расширенный фильтр > В окне «Расширенный фильтр» в поле «Исходный диапазон» указать адрес основной базы> В поле «Диапазон условий» указать $D$6:$H$7 > Установить переключатель в опции «Фильтровать список на месте» >

---

Ok > Сервис > Макрос > Остановить запись.
В результате должен получиться следующий макрос:
Sub Найти()

Range("C11").Select

Range("Данные_продаж").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Range("D6:H7"), Unique:=False

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

Выполним команды Сервис > Макрос > Начать запись > На запрос об имени макроса напечатать имя «ОтобразитьВсе» > Установить курсор в C11 > Данные > Фильтр > Отобразить все > Остановить запись.

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

Range("C11").Select

ActiveSheet.ShowAllData

EndSub
4. Создать кнопки «Найти» и «Отобразить все», и связать их соответствующими макросами.

Проверьте действие кнопок, задавая различные критерии поиска.

У созданной системы поиска имеется одна неприятная особенность: если случайно нажать на кнопку «Отобразить все» два раза подряд, то выйдет сообщение об ошибке.

Если это произошло, то в появившемся сообщении необходимо нажать кнопку «End». Один из вариантов устранения этого неудобства изложен в разделе 5.4.6.5.
5.2.3.5. Отчеты

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

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

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

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

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

---