В общей случае Excel является уникальной системой. Благодаря наличию очень большого количества встроенных функций и специальных средств с его помощью можно решать очень широкий класс прикладных задач, связанных с обработкой управленческой информации.
Однако практика создания информационных систем показывает, что обойтись только этими возможностями получается не всегда.
Тогда остается одна возможность – создавать новые возможности самостоятельно путем написания собственных процедур и функций.
Очевидно, что нельзя требовать от среднестатистического студента экономических специальностей навыков программирования. Тем не менее, приводимые далее примеры воспроизводят ситуации, возникшие в процессе выполнения курсовых работ, связанных с созданием реальных информационных систем.
Есть надежда, что решения этих примеров будут достаточно понятны большинству студентов, закончивших школу по старым советским программам.
Пример 1.
Пусть у нас на листе «Прайс-лист» имеются сведения о поставщиках и поставляемых ими товарах (рис. 5.18):
B
C
D
E
F
Код товара
Код поставщика
Оптовая цена
Рис. 5.18. Размещение данных на листе «Прайс-лист».
А на листе «План закупок» сформирован план следующего вида (рис. 5.19):
B
C
D
E
F
G
Код товара
Код поставщика
Количество
Сумма
Рис. 5.19. Размещение данных о закупках на листе «План закупок».
Необходимо определить суммы, выплачиваемые каждому поставщику.
Для данной задачи практически невозможно придумать комбинацию встроенных функций, которая давала бы нужный результат.
В то же время можно создать собственную функцию следующего вида:
Function ЦенаПоставки(КодТовара, КодПоставщика) As Variant
For i = 5 To 16
x1 = Sheets("Прайс-лист").Cells(i, 3)
x2 = Sheets("Прайс-лист").Cells(i, 4)
If x1 = КодТовара And x2 = КодПоставщика Then
ЦенаПоставки = Sheets("Прайс-лист").Cells(i, 5)
Exit Function
End If
Next
ЦенаПоставки = "Неверен код товара или поставщика"
End Function
Для окончательного решения задачи в ячейку F5 вводится формула:
=E5*ЦенаПоставки(C5;D5)
Примечание
Функцию ЦенаПоставки можно написать и без привязки к конкретным адресам. В этом случае данным прайс-листа следует присвоить имя (например, «ПрайсЛист») и тогда параметры размещения данных можно определить автоматически. Единственное что потребуется при возможных модификациях данных это соблюдать правила:
- в первой колонке данных должны быть коды товаров;
- во второй – коды поставщиков;
- в третьей – оптовая цена.
Function ЦенаПоставки(КодТовара, КодПоставщика) As Variant
Dim s As Range
Set s = Range("ПрайсЛист ")
r = s.Row 'Начальная строка диапазона
c = s.Column ' Начальный столбец диапазона
n = s.Rows.Count ' Количество строк в диапазоне
For i = r + 1 To n + r
x1 = Sheets("Прайс-лист").Cells(i, c)
x2 = Sheets("Прайс-лист").Cells(i, c + 1)
If x1 = КодТовара And x2 = КодПоставщика Then
ЦенаПоставки = Sheets("Прайс-лист").Cells(i, с+2)
Exit Function
End If
Next
ЦенаПоставки= "Неверен код товара или поставщика"
End Function
Пример 2.
Одной из основных задач управления является контроль за выполнением решений.
При автоматизации этой задачи обычно фиксируется информация следующего вида (рис. 5.20).
B
C
D
E
F
G
H
12.12.2011
Номер п/п
Дата приема
№ документа
Контрольный срок
Дата решения
Статус
05.11.2011
123/4-12
05.12.2011
05.11.2011
123/3-11
05.12.2011
27.11.2011
06.11.2011
123/4-11
06.12.2011
10.12.2011
14.11.2011
123/4-12
14.12.2011
15.11.2011
123/4-13
15.12.2011
Рис. 5.20. Размещение данных о результатах выполнения решений.
В основной таблице естественно должна и другая дополнительная информация. Например, код лица подготовившего документ (это может быть и обычный гражданин и сотрудник какого-то отдела данной организации), код исполнителя (обычно это сотрудник данной организации), содержание документа, его тип (жалоба, заявка, распоряжение) и т.д.
В ячейке G3 выводится текущая дата (с помощью встроенной функции Сегодня).
Результатом контроля может быть одна из следующих ситуаций:
1. Дата решения по документу меньше или равна контрольному сроку. Такому документу может быть присвоен статус «Выполнено». Этот статус должен быть присвоен документу, располагающемуся в седьмой строчке.
2. Дата решения по документу больше контрольного срока. Такому документу может быть присвоен статус «Выполнено с опозданием». Этот статус должен быть присвоен документу, располагающемуся в восьмой строчке.
3. Дата решения отсутствует, но при этом контрольный срок больше текущей даты. Такому документу может быть присвоен статус «В рассмотрении». Этот статус должен быть присвоен документу, располагающемуся в девятой строчке.
4. Дата решения отсутствует и при этом контрольный срок меньше текущей даты. Такому документу может быть присвоен статус «Не выполнено». Этот статус должен быть присвоен документу, располагающемуся в шестой строчке.
Для автоматического заполнения колонки «Статус» в нее должна быть вставлена соответствующая формула.
Эту формулу можно составить, сконструировав «многоэтажное ЕСЛИ». Например:
=ЕСЛИ(И(G6<=F6;G6<>"");"Выполнено";ЕСЛИ(G6>F6;"Выполнено с опозданием";ЕСЛИ(И(G6="";F6>$G$3);"Выполняется";"Не выполнено"))).
Но можно создать и собственную функцию:
Function Статус(КонтрСрок, ДатаРешения, ТекДата) As String
If КонтрСрок >= ДатаРешения Then Статус = "Выполнено"
If КонтрСрок < ДатаРешения Then Статус = "Выполнено c опозданием"
If КонтрСрок >= ТекДата And ДатаРешения = "" Then Статус = "Выполняется"
If КонтрСрок < ТекДата And ДатаРешения = "" Then Статус = "Не выполнено"
End Function
Варианты заданий
1. Отчет по структуре доходов (руб.).
2. Отчет по структуре доходов (%).
3. Отчеты по структуре продаж на заданный период времени (количество).
4. Отчеты по структуре продаж на заданный период времени (%).
5. Отчеты по структуре доходов на заданный период времени (руб.).
6. Отчеты по структуре доходов на заданный период времени (%).
7. Отчет об остаточных суммах на складе (руб).
8. Отчет о сумме поставок определенного товара (по выбору) в руб. и/или %.
9. Отчет о сумме продаж определенного товара (по выбору) в руб.
10. Отчет о сумме поставок от определенного поставщика (по выбору) в руб.
11. Отчет о поставках (% по каждому поставщику).
12. Отчет о поставках (в руб. по каждому поставщику).