Создание собственных функций (прямое программирование)

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

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

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

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

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

Пример 1.

Пусть у нас на листе «Прайс-лист» имеются сведения о поставщиках и поставляемых ими товарах (рис. 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

Рис. 18. Размещение данных на листе «Прайс-лист».

А на листе «План закупок» сформирован план следующего вида (рис. 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

Рис. 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.

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

При автоматизации этой задачи обычно фиксируется информация следующего вида - рис. 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

Рис. 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

Пример 3.

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

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

Но это как минимум непрофессионально, хотя бы потому, что номенклатура товаров может состоять из нескольких сотен наименований.

Для автоматического определения максимального значения можно использовать следующую функцию:

Function ПоискМакс (x As Range, y As Range) As Variant

Max = -1E+38

For i = 1 To x.Count

   If x(i) > Max Then

       Max = x(i): Imax = i

   End If

Next

ПоискМакс = y(Imax)

End Function

Для определения товара с максимальным спросом, например, по данным рис. 13, достаточно вызвать эту функции (из категории Определенные пользователем) и в качестве первого параметра ввести D24:D29,  а в качестве второго – C24:C29.

Различные вариации этой функции могут стать хорошим дополнением к стандартным функциям ВПР, ГПР, ПРОСМОТР, СУММАЕСЛИ и т.д.

Такие функции могут понадобиться при формировании таких отчетов, как:

определить наиболее прибыльный товар;

определить товар, пользующийся наименьшим спросом;

определить товар, дающий наименьшую прибыль и т.д.

2.8. Другие возможные варианты отчетов

1. Отчет по структуре доходов (руб.).

2. Отчет по структуре доходов (%).

3. Отчеты по структуре продаж на заданный период времени (количество).

4. Отчеты по структуре продаж на заданный период времени (%).

5. Отчеты по структуре доходов на заданный период времени (руб.).

6. Отчеты по структуре доходов на заданный период времени (%).

7. Отчет об остаточных суммах на складе (руб).

8. Отчет о сумме поставок определенного товара (по выбору) в руб. и/или %.

9. Отчет о сумме продаж определенного товара (по выбору) в руб.

10. Отчет о сумме поставок от определенного поставщика (по выбору) в руб.

11. Отчет о поставках (% по каждому поставщику).

12. Отчет о поставках (в руб. по каждому поставщику).

Администрирование системы

        

Рекомендация