Разработка информационной системы

В результате должен получиться следующий макрос:

Sub Найти()

    Sheets("Продажи").Range("C11:H303").AdvancedFilter Action:=xlFilterCopy, _

    CriteriaRange:=Range("C10:H11"), CopyToRange:=Range("C17:H17"), _ 

    Unique:=  False

End Sub

      Макрос  для кнопки «Очистить». Выполним команды Сервис > Макрос > Начать запись > На запрос об имени макроса напечатать имя «Очистить» > Выделить мышью ячейки C18:H32 > Нажать клавишу «Delete» > Установить курсор в C17 >  Сервис > Макрос > Остановить запись. В результате должен получиться следующий макрос:

Sub Очистить()

    Range("C18: H356").Select

    Selection.ClearContents

    Range("C17").Select

End Sub

     Нарисуем кнопки «Найти» и «Очистить» назначив им соответствующие макросы.

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

     В этом смысле отчетами являются ведомости заработной платы,  квитанции об оплате, приходные и расходные накладные, налоговые декларации и т.д.

     В данном разделе покажем, как можно  формировать итоговую отчетную информацию.  

     

     5.1. Использование функций 

     Предположим, что периодически нам необходимы данные о выручке от продаж за определенный период времени.

     Интерфейс расчетов может выглядеть следующим  образом:

     Вычисления  производятся следующим образом:

     - в D5 и D6 вводятся даты начала и конца отчетного периода, а ячейке D8 отражается результат вычислений.

     Для организации вычислений:

     - на этом же листе за пределами экрана создаем шаблон критерия отбора; 

 

     - в Q8 вводим формулу =">="&D6;

     - в R8 вводим формулу ="<="&D7;

     - в D11 вводим формулу:

     =БДСУММ (Данные_продаж;Продажи!H11;Q7:R8).

    Использование сводных таблиц

     Предположим, что нам периодически требуются  данные о структуре продаж следующего вида: 

 

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

     Интерфейс вычислений может выглядеть следующим  образом:

 

    При щелчке по кнопке «Сформировать» появляется соответствующая сводная таблица.

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

     Сервис > Макрос > Начать запись > Ввести имя  макроса (например, «Структура_продаж») >  Данные > Сводная таблица > Далее > В качестве диапазона указать Данные_продаж > Далее > Переключатель установить в положение «Существующий лист» и ввести адрес  формируемой сводной таблицы (в данном случае C22) > Макет > В область макета «Строка» перетаскиваем поле «Наименование», а в  область «Данные» перетаскиваем поле «Количество» > Ok > Готово > Сервис > Макрос > Остановить запись

     Затем создаем кнопку «Сформировать» и  связываем ее с созданным макросом.

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

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

     Для автоматизации очистки запишем макрос:

     Сервис > Макрос > Начать запись > Ввести имя  макроса (например, «Очистка») > Выделяем область B21:E31 > Нажимаем клавишу «Delete» > Сервис > Макрос > Остановить запись

     Создадим  кнопку «Очистить» и свяжем ее с  макросом «Очистка» 

     Окончательный интерфейс расчетов будет выглядеть  следующим образом:

 
 
 

Расчет  заработной платы 

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

     6.1. Постановка задачи

     Результатом расчетов заработной платы (з/п) обычно является сводная ведомость з/п следующего вида: 

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

     В четвертой колонке должен рассчитываться так называемый НДФЛ (налог на доходы с физических лиц).

     Пятая колонка – есть разница между  «Начислено» и «Удержано».

     Самое трудное здесь это расчет НДФЛ.

     В настоящее время он рассчитывается по следующим правилам:

     а) Если к моменту расчета общая  сумма начислений менее 40 тыс. руб., то

     НДФЛ = (Начислено –  ЛьготаНаДетей –  Стандартная льгота) * 0.13;

     б) Если к моменту расчета общая  сумма начислений (суммарная заработная плата с начала года) составила от 40 до 280 тыс. руб., то

     НДФЛ = (Начислено –  ЛьготаНаДетей) * 0.13;

     в) Если к моменту расчета общая  сумма начислений (суммарная заработная плата с начала года) более 280 тыс. руб., то

     НДФЛ = Начислено * 0.13.

     Во  всех формулах:

     - в качестве «Начислено» выступает оклад работника;

     - в качестве  общей суммы начислений  выступает  суммарная заработная плата с начала года. Например, если у работника оклад =7000 руб. и заработная плата начисляется в мае, то общая сумма начислений будет равна 35000 руб.;

     - К моменту написания пособия  льгота на одного ребенка равняется 1000 руб. Поэтому параметр  «ЛьготаНа детей» рассчитывается как:

     «ЛьготаНа детей» = 1000 * Количество детей;

      -  К моменту написания пособия  стандартная льгота на одного  работника равняется 400 руб.

     Эти правила и необходимо реализовать  в виде вычислений в колонке «Удержано».

 

    Интерфейс расчета заработной платы

Примерный вариант интерфейса приведен на рис.

     С помощью предлагаемого интерфейса расчет з/п производится следующим образом:

     - из раскрывающегося списка выбирается месяц начисления з/п;

     - после выбора месяца система  автоматически рассчитывает все  параметры ведомости.

   

Реализация  расчетов

 

1. Создание  списка «Период начисления»

     - на текущем листе (где в стороне,  так, чтобы этого потом не было видно на экране) создается список месяцев:

 
 

     - вызываем панель форм (Вид > Панели > Инструментов >Формы), на ней выбираем элемент «Поле со списком» и рисуем его в районе ячеек C7-D7 (как на предыдущем рисунке);

     - ставим мышь на нарисованный  элемент, щелчком ПКМ вызываем контекстное меню и выбираем пункт «Формат объекта»:

     

     - в поле «Формировать список по диапазону» указать местоположение списка месяцев;

     - в поле «Связь с ячейкой»  указать ячейку, в которую будет  записываться номер выбранного  месяца.

     - щелкнуть «Ok».

     Произведите несколько выборок в получившемся списке и посмотрите, что происходит в ячейке U14.

     2. Создание ведомости

     1. Создаем шапку ведомости требуемого  вида:

 
 
 

 

     Обратите  внимание на то, что кроме нужных  для ведомости колонок в шапку  включены необходимые для расчетов дополнительные столбцы.

2. В B13 вводим формулу:

=Сотрудники!B7,

т.е. с листа  основной базы копируется табельный  номер.

3. В C13 вводим формулу:

=Сотрудники!C7&" "&ЛЕВСИМВ(Сотрудники!D7)&"."

     Смысл формулы в общем-то очевиден: из ячейки С7 листа «Сотрудники» берется фамилия, к ней значком & присоединяется пробел, затем присоединяется первый левый символ имени (из ячейки D7 того же листа) и в конце добавляется точка.

4. В D13 вводится формула:

=Сотрудники!F7,

т. е. копируется величина оклада.

4. В G13 вводится формула:

=Сотрудники!H7,

т.е. копируется количество детей.

5. В H13 вводится формула расчета общей суммы начислений:

=$U$14*D13.

     Обратите  внимание на то, что адрес номера месяца является абсолютным.

     6. В I13 вводится формула расчет налоговой льготы на детей: