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

_продаж").Sort Key1:=Range("E12"),Order1:=xlAscending,Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _DataOption1:=xlSortNormalEndSubНоминальное знание английского языка позволяет понять записанные команды и по возможности изменить их.Первая команда соответствует переходу на ячейку «С11» (когда мы щелкнули по ней).Вторая команда очень длинная, занимает три строчки и выполняет метод сортировки для диапазона «Данные_продаж». Основная часть команды – Range("Данные_продаж").Sort выполняет сортировку выделенной части. Остальные компоненты – это параметры сортировки, которые можно частично или все удалить.Нас интересует параметр Key1, который определяет поле сортировки. Его значение, равное E12, соответствует столбцу E, в котором находится поле «Наименование». Если сейчас вместо E11 напечатать G11 и в Excel щелкнуть по кнопке «Сортировка», то сортировка произойдет по полю «Цена».Для того, чтобы связать выбранный элемент списка с режимом сортировки придется проявить немного квалификации. В Excel для обращения к ячейкам существует два способа.Первый – с помощью объекта Range (как в приведенном макросе). Второй – с помощью объекта Cells следующего формата:Cells(Номер строки, Номер столбца).Способы эквиваленты и используются по ситуации. Например, вместо Range(«C11») вполне можно записать Cells(11, 3).Поэтому макрос можно переписать следующим образом:Sub Сортировка() DimkAsInteger ‘Объявляем переменную целого типаRange("C11").Select ‘Выделяемячейку C11k=Range(“Q11”) ‘Определяем номер выбранного пунктаRange("Данные_продаж").Sort Key1:=Cells(12,k+2), Header:=xlGuessEndSubЗдесь из параметров сортировки оставлен лишь два параметра – ключ сортировки и наличие заголовка.Перепечатайте (перекопируйте) указанный текст макроса и убедитесь, что он нормально работает.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").SelectRange("Данные_продаж").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Range("D6:H7"), Unique:=FalseEndSub3. Записать макрос для кнопки «Отобразить все»Выполним команды Сервис > Макрос > Начать запись > На запрос об имени макроса напечатать имя «ОтобразитьВсе» > Установить курсор в C11 > Данные > Фильтр > Отобразить все > Остановить запись.В результате должен получиться следующий макрос:Sub ОтобразитьВсе() Range("C11").Select ActiveSheet.ShowAllDataEndSub4. Создать кнопки «Найти» и «Отобразить все», и связать их соответствующими макросами.Проверьте действие кнопок, задавая различные критерии поиска.У созданной системы поиска имеется одна неприятная особенность: если случайно нажать на кнопку «Отобразить все» два раза подряд, то выйдет сообщение об ошибке.Если это произошло, то в появившемся сообщении необходимо нажать кнопку «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

Рис. 5.10. Интерфейс расчета выручки за определенный период времениВычисления производятся следующим образом:– в D5 и D6 вводятся даты начала и конца отчетного периода, а ячейке D8 отражается результат вычислений.Для организации вычислений: – на этом же листе за пределами экрана создаем шаблон критерия отбора;

		P	Q	R	S
5
6
7			Дата продажи	Дата продажи
8			>=11.11.09	<=20.11.09
9

Рис. 5.11. Размещение критериев для расчета выручки за определенный период времени– в Q8 вводим формулу =">="&D6; – в R8 вводим формулу ="<="&D7;– в D11 вводим формулу:=БДСУММ(Данные_продаж;Продажи!H11;Q7:R8).

5.2.3.5.2. Использование встроенных функций в макросахВ макросах можно использовать и имеющиеся в Excel функции. Но при этом имеется одно ограничение: функция должна быть в англоязычном варианте.Например.Пусть для отчета, рассмотренного в предыдущем разделе необходимо выбрать вариант расчета.К примеру:- общая сумма выручки (уже реализовано в разделе 5.2.3.5.1.);- средняя выручка;- максимальная выручка;- минимальная выручка.Можно конечно выполнить все эти расчеты сразу. Т.е. в ячейку D12 (рис. 5.10) ввести формулу:=ДСРЗНАЧ(Данные_продаж;Продажи!H11;Q7:R8);в ячейку D13 (рис. 5.10) ввести формулу:=ДМАКС(Данные_продаж;Продажи!H11;Q7:R8);и т.д.Но если сделать вариант с выбором вида расчета, то интерфейс отчета может быть следующим (рис. 5.12):Рис. 5.12. Интерфейс расчета показателей продаж за определенный период времениИз раскрывающегося списка выбирается вид расчета и затем щелчок по кнопке «Рассчитать».Технология создания такого интерфейса уже описана в разделах 5.2.3.4.3. Сортировка, 5.2.3.4.4. Поиск. Поэтому дадим только краткие комментарии:- для выбора операции используется элемент «Поле со списком»;- этот элемент связан со списком операций, который введен в ячейки U11:U14;- с этим списком связана ячейка U15;Макрос для кнопки «Рассчитать» может иметь вид:Sub Рассчитать()k = Range("U15")Select Case kCase 1Range("F11") = "=DSUM(Данные_продаж,Продажи!H11,Q7:R8)"Case 2Range("F11")="=DAVERAGE(Данные_продаж,Продажи!H11,Q7:R8)"Case 3Range("F11") = "=DMAX(Данные_продаж,Продажи!H11,Q7:R8)"Case 4Range("F11") = "=DMIN(Данные_продаж,Продажи!H11,Q7:R8)"End SelectEnd SubДля определения вида англоязычного варианта функции рекомендуется стандартная технология:– записывается временный макрос, в котором вызывается нужная нам функция;- получившаяся команда копируется в нужный нам макрос;- временный макрос удаляется.5.2.3.5.3. Использование сводных таблиц