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

Sub Товары()

Sheets("Товары").Select

End Sub

9. 
   Для создания остальных макросов имеющийся текст макроса как в Word выделяется, копируется в буфер и путем вставки создается шесть его копий.
   
10. 
    Каждую копию следует исправить, создавая новые макросы. Например, первую копию исправить, так, чтобы она приняла следующий вид:
    

Sub Продажи()

Sheets("Продажи").Select

End Sub

Вторую копию:

Sub Поставщики()

Sheets("Поставщики").Select

EndSub

Третью копию:

Sub Поставки()

Sheets("Поставки").Select

EndSub

Четвертую копию:

Sub Отчеты()

Sheets("Отчеты").Select

End Sub

Пятую копию:

Sub Меню()

Sheets("Меню").Select

End Sub

Шестой макрос, обеспечивающий выход из системы должен содержать следующую команду:

Sub Выход()

Workbooks.Close

End Sub

Запишем еще один макрос, который обеспечивает открытие книги с листа «Заставка». Для этого в редакторе Visual Basic в окне Project – VBAProject двойным щелчком выделить объект «Эта Книга», в открывшемся окне модуле объекта записать следующий макрос (рис. 5.5):

Sub Workbook_Open()

Sheets("Заставка").Select

End Sub


Рис. 5.5. Вид окна Project – VBAProject

11. 
    Все необходимые макросы созданы.
    

Создание кнопок

5. 
   Вызывается панель инструментов с заготовками интерфейса – Вид > Панели инструментов > Формы.
   
6. 
   На появившейся панели выбрать элемент «Кнопка» и нарисовать ее в нужном месте экрана. На запрос о назначении макроса из списка выбрать «Товары». Надпись на кнопке «Кнопка 1» исправить на «Товары»
   
7. 
   Аналогично создаются все остальные кнопки главного меню.
   
8. 
   На остальных листах (пока еще пустых) создать кнопки возврата к главному меню (используется макрос «Меню»).
   

Оформление главного меню

Оформлять или не оформлять главное меню дело вкуса. Но если в этом есть необходимость, то:

6. 
   Вызвать панель рисования (Вид > Панели инструментов > Рисование), выбрать на ней объект «Прямоугольник» и накрыть им кнопки главного меню.
   
7. 
   Выделить нарисованный прямоугольник и на панели рисования выбрать Рисование > Порядок > На задний план. При этом скрытые прямоугольником кнопки выйдут на передний план.
   
8. 
   Не снимая выделения с прямоугольника залить его выбранным цветом и стилем.
   
9. 
   Убрать сетку таблицы – Сервис > Параметры > Снять отметку с параметра «Сетка».
   
10. 
    Выбрать подложку для фона – Формат > Лист > Подложка > Выбрать рисунок > Вставить. Рисунок можно выбрать из коллекции ClipArt.
    

---

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

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

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

1. 
   Справочник по товарам
   
2. 
   Справочник по поставщикам
   
3. 
   Данные о продажах
   
4. 
   Данные о поставках
   

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

Например, данные о товарах:

	A	B	C	D	E	F	G
1
2		Код товара	Наименование	Сорт	Оптовая цена	Наценка	Код поставщика
3		1	Лапти липовые	2	100	10%	1
4		2	Лапти дубовые	1	200	20%	1
5		3	Галоши	2	150	10%	2
6		4	Валенки	2	300	10%	3
7		5	Кепка a ’la Lenin	2	400	10%	4
8		6	Кепка a 'la Luzkov	1	500	20%	4

---

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

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

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

Пусть шапка этой таблицы имеет следующий вид:

		С	D	E	F	G	H
10
11		Дата продажи	Код товара	Наименование	Количество	Цена	Сумма
12
13

И пусть в ней должны храниться сведения о продажах за прошедший месяц. Предположим также, что в день у нас производится по десять продаж. Тогда общее количество записей будет равно 300.

Очевидно, что

---

– колонки С, D и F должны заполняться случайно;

– колонки E, и G будут заполняться исходя из данных справочника по товарам;

– колонка H должна рассчитываться по данные колонок F и G.

Для заполнения колонок случайными данными можно использовать имеющуюся в Excel функцию генерации случайных чисел – СЛЧИС(). Она генерирует случайные числа из диапазона 0..1. Для генерации целых чисел из произвольного диапазона используется формула:
=А + ЦЕЛОЕ((В – А+1)*СЛЧИС())(5.7)
где А – нижняя граница необходимого диапазона;

В – верхняя граница диапазона;

ЦЕЛОЕ – имеющаяся в Excel функция округления дробных чисел.
Начнем с колонки «Дата продажи».

Для этой колонки нам необходимо определить параметры A и B в формуле (5.7).

Для определения параметра A:

– в отдельную ячейку (например, в A1) вводим начальную дату продаж – пусть это будет 01.10.09. Задаем для этой ячейки формат «общий». В ней получится число 40087. Это будет число дней, прошедших с сначала прошлого века (с 1900 года).

Поэтому в ячейку С12 вводим формулу:
= 40087+ ЦЕЛОЕ(30 * СЛЧИС())
и копируем ее на 300 строк данного столбца.

Вы должны были обратить внимание на то, что после каждой манипуляции с данными их значения меняются. Это свойство функции СЛЧИС.

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

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

– не снимая выделения произведем перекопирование данных командой Правка > Специальная ставка > Значения;

– не снимая выделения, преобразуем данные столбца C в формат «Дата» (Формат > Ячейки > Дата).

Не забудьте удалить из А1 ненужную теперь дату.
По аналогичной схеме заполняется колонка D – «Код товара»:

– в ячейку D12 вводится формула

= 1+ ЦЕЛОЕ(6 *СЛЧИС())
(здесь 6 – количество товаров);

– формула копируется на 300 строк;

– путем перекопированния столбца D избавляемся от формулы.
Аналогично заполняется колонка F – «Количество»:

– в ячейку F12 вводится формула:

= 1+ ЦЕЛОЕ(10 *СЛЧИС())
(здесь 10 – количество товаров, т.е. больше 10–и кепок в одни руки не даем!);

– формула копируется на 300 строк;

– путем перекопированния столбца F избавляемся от формулы.

Для заполнения столбца E в ячейку E12 вводим формулу:
=ВПР(D12;Справочник_товары;2)
и копируем ее на 300 строк.

Формула содержит функцию ВПР, которая ищет значение поля D12 в первой колонке справочной таблицы товаров и в качестве результата берет значения из второй колонки таблицы товаров.

---

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

РозничнаяЦена = ОптоваяЦена*(1+Наценка)(5.8)
При реализации в Excel эта формула должна «сама» по коду товара из столбца D брать с листа Товары» значения оптовой цены и наценки. Для этого также используется функция ВПР. Т.е. в G12 вводится формула:
= ВПР(D12; Справочник_товары;4) * (1 + ВПР(D12; Справочник_товары;5))
Обратите внимание, в первой ВПР оптовая цена берется из четвертой колонки справочной таблицы, а во второй – наценка берется из пятой колонки справочной таблицы. Данная формула копируется на весь столбец G.

И, наконец, в столбец H вводится формула расчета суммы покупки (с последующим копированием): = F12 * G12.
Таблица заполнена.

Осталось только отсортировать ее по полю «Дата продажи» и присвоить имя – «Данные_продаж».

По аналогичной схеме можно было бы создать и таблицу «Поставки».

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

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

= ВПР(D12; Справочник_товары;4)

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

Для этого добавляется колонка «Поставщик» (столбец I), в который вводится формула:

= ВПР(D12; Справочник_товары;6)

---