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

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

Тогда остается одна возможность – создавать новые возможности самостоятельно путем написания собственных процедур и функций.

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

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

Пример 1.

Пусть у нас на листе «Прайс-лист» имеются сведения о поставщиках и поставляемых ими товарах (рис. 5.18):

	B	C	D	E	F
4		Код товара	Код поставщика	Оптовая цена
5		1	1	100
6		2	1	120
7		3	1	120
8		2	2	110
9		3	2	150
10		4	3	250
11		1	4	110
12		2	4	130
13		3	4	140
14		4	4	300
15		5	4	400
16		6	4	500
17

Рис. 5.18. Размещение данных на листе «Прайс-лист».А на листе «План закупок» сформирован план следующего вида (рис. 5.19):

	B	C	D	E	F	G
3
4		Код товара	Код поставщика	Количество	Сумма
5		1	1	50
6		2	1	40
7		3	1	10
8		2	2	20
9		3	2	70
10		4	3	55
11		1	4	30
12		2	4	40
13		3	4	80
14		4	4	30
15		5	4	20
16		6	4	20
17

Рис. 5.19. Размещение данных о закупках на листе «План закупок».Необходимо определить суммы, выплачиваемые каждому поставщику.Для данной задачи практически невозможно придумать комбинацию встроенных функций, которая давала бы нужный результат.В то же время можно создать собственную функцию следующего вида:Function ЦенаПоставки(КодТовара, КодПоставщика) AsVariantFor i = 5 To 16x1 = Sheets("Прайс-лист").Cells(i, 3)x2 = Sheets("Прайс-лист").Cells(i, 4)If x1 = КодТовара And x2 = КодПоставщика ThenЦенаПоставки = Sheets("Прайс-лист").Cells(i, 5)Exit FunctionEnd IfNextЦенаПоставки = "Неверен код товара или поставщика"End FunctionДля окончательного решения задачи в ячейку F5 вводится формула:=E5*ЦенаПоставки(C5;D5)ПримечаниеФункцию ЦенаПоставки можно написать и без привязки к конкретным адресам. В этом случае данным прайс-листа следует присвоить имя (например, «ПрайсЛист») и тогда параметры размещения данных можно определить автоматически. Единственное что потребуется при возможных модификациях данных это соблюдать правила: - в первой колонке данных должны быть коды товаров;- во второй – коды поставщиков;- в третьей – оптовая цена.Function ЦенаПоставки(КодТовара, КодПоставщика) AsVariantDim s As RangeSet s = Range("ПрайсЛист ")r = s.Row 'Начальная строка диапазонаc = s.Column ' Начальный столбец диапазонаn = s.Rows.Count ' Количество строк в диапазонеFor i = r + 1 To n + rx1 = Sheets("Прайс-лист").Cells(i, c)x2 = Sheets("Прайс-лист").Cells(i, c + 1)If x1 = КодТовара And x2 = КодПоставщика ThenЦенаПоставки = Sheets("Прайс-лист").Cells(i, с+2)ExitFunctionEndIfNextЦенаПоставки= "Неверен код товара или поставщика"End FunctionПример 2.Одной из основных задач управления является контроль за выполнением решений.При автоматизации этой задачи обычно фиксируется информация следующего вида (рис. 5.20).

	B	C	D	E	F	G	H
3						12.12.2011
4
5		Номер п/п	Дата приема	№ документа	Контрольный срок	Дата решения	Статус
6		1	05.11.2011	123/4-12	05.12.2011
7		2	05.11.2011	123/3-11	05.12.2011	27.11.2011
8		3	06.11.2011	123/4-11	06.12.2011	10.12.2011
9		4	14.11.2011	123/4-12	14.12.2011
10		5	15.11.2011	123/4-13	15.12.2011
11

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

1. 
   Дата решения по документу меньше или равна контрольному сроку. Такому документу может быть присвоен статус «Выполнено». Этот статус должен быть присвоен документу, располагающемуся в седьмой строчке.
   
2. 
   Дата решения по документу больше контрольного срока. Такому документу может быть присвоен статус «Выполнено с опозданием». Этот статус должен быть присвоен документу, располагающемуся в восьмой строчке.
   
3. 
   Дата решения отсутствует, но при этом контрольный срок больше текущей даты. Такому документу может быть присвоен статус «В рассмотрении». Этот статус должен быть присвоен документу, располагающемуся в девятой строчке.
   
4. 
   Дата решения отсутствует и при этом контрольный срок меньше текущей даты. Такому документу может быть присвоен статус «Не выполнено». Этот статус должен быть присвоен документу, располагающемуся в шестой строчке.
   

Для автоматического заполнения колонки «Статус» в нее должна быть вставлена соответствующая формула.Эту формулу можно составить, сконструировав «многоэтажное ЕСЛИ». Например:=ЕСЛИ(И(G6<=F6;G6<>"");"Выполнено";ЕСЛИ(G6>F6;"Выполнено с опозданием";ЕСЛИ(И(G6="";F6>$G$3);"Выполняется";"Не выполнено"))).Но можно создать и собственную функцию:Function Статус(КонтрСрок, ДатаРешения, ТекДата) As StringIf КонтрСрок >= ДатаРешения Then Статус = "Выполнено"If КонтрСрок < ДатаРешения Then Статус = "Выполнено c опозданием"If КонтрСрок >= ТекДата And ДатаРешения = "" Then Статус = "Выполняется"If КонтрСрок < ТекДата And ДатаРешения = "" Then Статус = "Не выполнено"End Function5.2.3.5.7. Варианты заданий

1. 
   Отчет по структуре доходов (руб.).
   
2. 
   Отчет по структуре доходов (%).
   
3. 
   Отчеты по структуре продаж на заданный период времени (количество).
   
4. 
   Отчеты по структуре продаж на заданный период времени (%).
   
5. 
   Отчеты по структуре доходов на заданный период времени (руб.).
   
6. 
   Отчеты по структуре доходов на заданный период времени (%).
   
7. 
   Отчет об остаточных суммах на складе (руб).
   
8. 
   Отчет о сумме поставок определенного товара (по выбору) в руб. и/или %.
   
9. 
   Отчет о сумме продаж определенного товара (по выбору) в руб.
   
10. 
    Отчет о сумме поставок от определенного поставщика (по выбору) в руб.
    
11. 
    Отчет о поставках (% по каждому поставщику).
    
12. 
    Отчет о поставках (в руб. по каждому поставщику).
    

VI. Экономические расчеты6.1. Задачи на проценты6.1.1. Общие сведения

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

Например, при увеличении цены С на N % новая цена рассчитывается по формуле: , (6.1)а при снижении цены на N% расчет ведется по формуле: . (6.2)

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

Например, если цена повышена на 25 %, то в формулу (6.1) необходимо записать число 0,25.

4. 
   В Excel для ввода процентов можно использовать оба варианта. При этом, если формулу записать как =F2*(1+25%), то Excel автоматически преобразует 25% в число 0,25.
   
5. 
   Для удобства ввода данных в процентах можно установить процентный формат ячейки.
   

Указания

1. 
   При выполнении заданий все параметры, которые указаны в общем виде, должны вводиться с клавиатуры.
   
2. 
   Если формула или расчеты слишком сложны, то желательно выводить результаты промежуточных расчетов.
   

6.1.2. ПримерФирма закупила товара A на сумму S₁ руб. и при его реализации получила прибыль, равную N₁%. Товара В было закуплено на сумму S₂ руб. и прибыль от его реализации составила N₂%.Определить общий процент прибыли, полученной от реализации обоих товаров.Исходя из условия задачи, данные можно разместить следующим образом:

	A	B	C	D	E	F
2
3		Товар	Сумма закупки	Прибыль, %	Прибыль, руб.
4		А	100000	20%	20000
5		В	60000	10%	6000
6		Всего	160000	16,25%	26000
7
8