Информационная технология создания шапки документа

1. В ячейке С2 содержится код вида накладной для выбора: 1 (поступление), 2 (реализация). Для формирования списка кодов выполняется команда меню Данные, Работа с данными, Проверка данных, Проверка данных, Тип данных - Список, Источник - 1; 2.

2. В ячейку А1 вводится формула для вывода названия документа:

=СЦЕПИТЬ(ВЫБОР(C2;"Приходная";"Расходная");" накладная №").

3. В ячейку Е1 вводится постоянный текст – «от».

4. В ячейку F1 вводится формула для вывода текущей даты оформления документа: =СЕГОДНЯ()

5. В ячейку А2 вводится постоянный текст – «Вид движения».

6. В ячейку Е2 вводится формула для расшифровки кода вида накладной:

=ВЫБОР(C2;"(поступление товаров)";"(реализация товаров)")

7. В ячейку А3 для изменения названия типа организации вводится формула: - =ЕСЛИ(C2=1;"Поставщик";"Покупатель")

8. В ячейке С3 содержатся названия организаций для выбора. Для формирования списка организаций выполняется команда Данные, Работа с данными, Проверка данных, Проверка данных, Тип данных - Список, Источник - Название.

9. В ячейку А4 вводится постоянный текст – «Банковские реквизиты».

10. В ячейку С4 для вывода банковских реквизитов выбранной организации вводится формула:

=ПРОСМОТР(C3;Название;Банковские_реквизиты)

11. В ячейку А5 вводится постоянный текст – «Ставка НДС по счету». В ячейке С5 содержится список значений ставки налога на добавленную стоимость для выбора. Для формирования списка выполняется команда меню Данные, Работа с данными, Проверка данных, Проверка данных, Тип данных - Список, Источник - 0%; 10%; 20%.

Информационная технология формирования табличной части документа:

1. Оформить шапку таблицы.

2. Для фиксированного количества строк табличной части документа (5 – 7 строк) выделить блок ячеек, не включающий заголовки столбцов, создать именованный блок Накладная – команда Формулы, Присвоить имя.

3. Выделить блок ячеек в столбце Товар для формирования списка кодов товаров - команда Данные, Работа с данными, Проверка данных, Проверка данных, Тип данных - Список, Источник - блок Код_товара.

4. В столбце Наименование выводится наименование выбранных товаров.

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

=ПРОСМОТР($A9;Код_товара; Наименование)

где А9 – адрес ячейки первой строки табличной части документа, содержащей код товара.

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

5. В столбце Ед.изм. выводится единица измерения выбранных товаров.

Для ячейки столбца в первой строке таблицы вводится формула вида:

=ПРОСМОТР($A9;Код_товара;Ед.изм.)

где А9 – адрес ячейки первой строки табличной части документа, содержащей код товара.

Размножить формулу по всем ячейкам столбца Ед. изм.

6. В столбце Цена выводится цена выбранных товаров. Для ячейки столбца в первой строке таблицы вводится формула вида:

=ПРОСМОТР($A9;Код_товара;Цена)

где А9 – адрес ячейки первой строки табличной части документа, содержащей код товара.

7. В ячейках столбца Сумма вычисляется стоимость товара каждого наименования по формуле: = Цена*Количество.

8. В ячейках столбца Сумма НДС вычисляется сумма налога для каждого товара по формуле: = Ставка НДС по счету*Сумма

9. Столбец Итого -значение выводится согласно формуле:

= Сумма + Сумма НДС

10. Вычислить сумму по столбцу Итого.

11. Создать именованный блок Вид_движения для ячейки С2.

12. Для ячеек С1, С2, С3, C5 снять защиту; для ячеек С4, F1 скрыть формулы.

13. Для ячеек столбцов таблицы А, Е снять защиту, для ячеек остальных столбцов табличной части установить защиту и скрыть формулы.

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

=ЕСЛИ (Вид_движения = 1; ПРОСМОТР (A9; Код_товара; Остаток_на_конец); ЕСЛИ(ПРОСМОТР(A9; Код_товара; Остаток_на_конец)-E9>=0; ПРОСМОТР (A9; Код_товара; Остаток_на_конец)-E9;"НЕТ В НАЛИЧИИ)"))

где А9 – адрес ячейки первой строки табличной части документа, содержащей код товара.

Если вид движения 1 (поступление товаров), выводится текущий остаток выбранного товара на складе. Если вид движения 2 (реализация), вычисляется текущий остаток, контролируется его неотрицательное значение после отпуска товара со склада.

Формулу скопировать во все ячейки столбца. Скрыть формулы проверки от просмотра.

15. Защитить информацию рабочего листа.

ВВОД ДАННЫХ О ДВИЖЕНИИ ТОВАРОВ

Данные накладных по приходу и расходу товаров переносятся в базу данных Движение с помощью командной кнопки, размещенной на листе Документ. Основные этапы работ:

1. Подготовка программного модуля на языке Visual Basic

2. Отладка программного модуля

3. Запуск программного модуля.

Создание программного модуля

1. Выполнить команду Разработчик, Visual Basic

2. Выполнить команду Insert, Module.

3. Текст программного модуля:

' Обязательное объявление всех переменных, используемых в модуле

Option Explicit

' Пользовательский тип данных для добавления данных документа в базу данных

‘ Объявление пользовательского типа данных – shet

' Объявление переменных различных типов: String – текст,

‘Integer - целое, Variant - произвольное, Date – дата, Long - число

Type shet

Товар As String

Наименование As String

Едизм As String

Цена As Long

Количество As Long

End Type

Dim sh() As shet

Dim nr As Integer

Dim nc As Integer

Dim i As Integer

Dim str1 As Variant

Dim str As Date

Dim str2 As Variant

4. Выполнить команду меню Insert, Procedure, указать имя процедуры Накладная_в_БД

5. Текст программного модуля:

' начало процедуры переноса данных документа в базу данных

Sub Накладная_в_БД()

' выбор блока Накладная, находящегося на листе Документ

Application.Goto Reference:="Накладная"

' определение количества строк в блоке Накладная

nr = Selection.Rows.Count

' настройка размерности динамического массива

ReDim sh(nr)

' установка на начало области блока Накладная

ActiveCell.Offset(1, 0).Range("A1").Select

' цикл формирования элементов динамического массива

‘ на основании блока Накладная

For i = 1 To nr

sh(i).Товар = Range("Накладная").Cells(i, 1).Value

sh(i).Наименование = Range("Накладная").Cells(i, 2).Value

sh(i).Едизм = Range("Накладная").Cells(i, 3).Value

sh(i).Цена = Range("Накладная").Cells(i, 4).Value

sh(i).Количество = Range("Накладная").Cells(i, 5).Value

Next i

i = 1

' присвоение переменной str2 номера накладной

Range("C1").Select

str2 = ActiveCell.Value

' присвоение переменной str даты выписки накладной

Range("F1").Select

str = ActiveCell.Value

' присвоение переменной str1 названия организации

Range("C3").Select

str1 = ActiveCell.Value

' выбор блока База_данных

Application.Goto Reference:="База_данных"

' установка на ячейку - А2

ActiveCell.Offset(1, 0).Range("A1").Select

' вставка новой записи в базу данных

Selection.EntireRow.Insert

' организация цикла добавления новой строки в блок База_данных

‘ заполнение ячеек строки данными динамического массива

‘счетчик цикла равен числу строк

‘табличной части документа Накладная

For i = 1 To nr

Cells(i + 1, 1).Value = str

Cells(i + 1, 2).Value = sh(i).Товар

Cells(i + 1, 3).Value = sh(i).Наименование

Cells(i + 1, 4).Value = sh(i).Едизм

Cells(i + 1, 5).Value = sh(i).Цена

Cells(i + 1, 7).Value = str2

' проверка вида документа для заполнения ячеек и вычисление

‘стоимости поступивших и стоимости отгруженных товаров

If Range("Вид_движения").Value = 1 Then _

Cells(i + 1, 8).Value = sh(i).Количество

If Range("Вид_движения").Value = 2 Then _

Cells(i + 1, 9).Value = sh(i).Количество

'формулы вычисления стоимости поставки/отгрузки товаров

Cells(i + 1, 10).FormulaR1C1 = "=RC[-2]*RC[-5]"

Cells(i + 1, 11).FormulaR1C1 = "=RC[-2]*RC[-6]"

‘ перемещение курсора на начало строки

Selection.End(xlToLeft).Select

ActiveCell.Offset(1, 0).Range("A1").Select

‘ вставка пустой строки

Selection.EntireRow.Insert

‘ повторение цикла

Next i

' удаление лишней пустой строки в блоке Движение

Selection.EntireRow.Delete

' конец прцедуры

End Sub

Отладка программного модуля

1. Заполнить лист Документ первичными данными.

2. Перейти на лист программного модуля и выполнить запуск макроса клавишей F5.

3. Перейти на лист Движение и проверить правильность формирования записей списка.

4. Перейти на лист Итоги и проверить изменение остатков товаров.

5. Перейти на лист Расчеты и проверить изменение итоговых показателей.

6. Если обработка выполняется корректно, перейти на лист Движение, удалить введенные строки, оставив первую вспомогательную строку блока База_данных, проверить его границы (блок База_данных должен содержать строку за головков столбцов и хотя бы одну строку).

Запуск программы

Для запуска программного модуля использовать использование командной кнопки, которая размещается в форме документа Накладная:

1. Снять защиту с листа Документ, разместить кнопку запуска модуля Накладная_в_БД:

§ Выполнить команду Разработчик, Элементы управления, Вставить.

§ Выбрать кнопку и назначить ей макрос – Накладная_в_БД.

§ Изменить название кнопки – Запись документа.

2. Защитить лист Документ.

3. Заполнить документ и нажать кнопку для переноса данных в базу данных Движение.

4. Перейти в базу данных, проверить наличие новых данных и сохранность определения блока База_данных.

АНАЛИЗ ДВИЖЕНИЯ ТОВАРОВ

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

Промежуточные итоги

Промежуточные итоги могут создаваться по различным ключам сортировки базы данных Движение.

1. Выбрать блок База_данных.

2. Отсортировать список по полям: Товар (старший ключ), Организация - младший ключ сортировки;

3. Сформировать итог 1 уровня - команда Данные, Промежуточные итоги, указав:

Поле группирования – Код товара,

Операция - Сумма,

Добавить итоги по полям: Поступление, Реализация, Итого поступление, Итого реализация

Итоги размещать под данными, предыдущие итоги не удалять.

4. Сформировать итог 2 уровня - команда Данные, Промежуточные итоги, указав:

Поле группирования - Организация,

Операция - Сумма,

Добавить итоги по полям: Поступление, Реализация, Итого поступление, Итого реализация

Итоги размещать под данными, предыдущие итоги не удалять.

5. Убрать итоги - команда Данные, Промежуточные итоги, Убрать все.

Фильтрация базы данных

Выполнить автофильтрацию базы данных Движение:

§ Получить сведения о реализации товаров определенных видов;

§ Получить сведения о реализации товаров за указанный интервал времени;

§ Получить сведения о поставках товаров, цена которых находится в заданном интервале;

§ Получить сведения о движении крупных партий товаров за определенный интервал дат;

§ Получить сведения о 10 продажах с наименьшей общей стоимостью;

§ Получить сведения о 5 поступлениях товаров с наибольшей общей стоимостью и др.

Подготовить на листе Критерии условия для реализации запросов:

1. Сведения о продажах товаров, цена которых выше заданного значения, а также о продажах товаров на сумму в заданном диапазоне:

Таблица 11 - Расширенный фильтр

Цена	Итого реализация	Итого реализация
>100		>0
	>=12000	<=30000

2. Сведения о товарах, продажа которых превосходит средний уровень на 15%:

Таблица 12 - Вычисляемый критерий

Стоимость товаров

=Движение!K2>=СРЗНАЧ(Движение!$K$2:$K$94)*1,15

Информационная технология фильтрации базы данных Движение:

1. Установить курсор на лист Движение.

2. Выполнить команду меню Данные, Фильтр, Дополнительно и указать:

Обработка - Фильтровать список на месте

Исходный диапазон - имя блока База_данных,

Диапазон условий - блок критериев (лист Критерии);

Только уникальные записи.

Макрос “ Фильтрация”

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

Таблица 13 - Условия фильтрации

	А	В
		Реализация товаров
	Организация	Итого реализация
	Универмаг ЦФТ	>0

1. В ячейке А1 обеспечен выбор вида движения товаров (1 - поступление товаров, 2 - реализация товаров).

2. В ячейке А3 содержится постоянный текст Организация, который совпадает с именем столбца базы данных Движение.

3. В ячейке А4 выбирается название организации из списка возможных значений.

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

5. В ячейке В4 сдержится постоянный текст >0.

Если выбран вид движения - 1, в ячейке В1 появляется слово Поступление товаров, а в ячейке В3 появляется Итого поступление, совпадающее с названием столбца в базе данных. При выборе вида движения 2 в ячейке В1 будет записано Реализация товаров, а в ячейке В3 – Итого реализация, совпадающее с именем столбца базы данных Движение..

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

Информационная технология подготовки листа Фильтрация.

1 этап. Подготовка условий фильтрации записей базы данных:

1. Установить курсор в ячейку А1 и выполнить команду Данные, Проверка данных, указать тип - Список, источник - 1; 2.

2. Установить курсор в ячейку В1 и ввести формулу вида:

=ВЫБОР(A1;"Поступление товаров";"Реализация товаров")

3. Установить курсор в ячейку А3 и ввести текст Организация. Соответствует названию поля базы данных движения товаров.

4. Установить курсор в ячейку А4 и выполнить команду Данные, Проверка данных, указать тип - Список, источник - блок Название

5. Установить курсор в ячейку В3 и ввести формулу:

=ВЫБОР(A1;"Итого поступление";"Итого реализация")

Результат функции соответствует выводу названия столбца базы данных движения товаров.

6. Установить курсор в ячейку В4 и ввести символы >0

2 этап. Фильтрация базы данных.

1. Установить курсор в ячейку $A$7

2. Фильтрация базы данных - команда меню Данные, Дополнительно

Указать: Исходный диапазон - База_данных, Диапазон условий - $A$3:$B$4, Скопировать результат в другое место, Поместить результат в диапазон - $A$7.

3 этап. Создание макроса.

Макрос состоит из следующих действий:

· Удаление области результата предыдущей фильтрации;

· Выполнение команды Данные, Дополнительно с параметрами базы данных и диапазона условий.

1. Выполнить команду Разработчик, Записать макрос. В диалоговом окне задать имя макроса - Фильтрация и "горячие" клавиши вызова макроса - <Ctrl>+<q>, указать место хранения макроса - Эта книга, нажать кнопку ОК

2. Выделить диапазон строк, например, 7 - 25, выполнить команду Удалить.

3. Снять выделение, установив курсор в ячейке А7.

4. Выполнить команду меню Данные, Дополнительно, указав:

§ Исходный диапазон - База_данных,

§ Диапазон условий - $A$3:$B$4,

§ Скопировать результат в другое место, Поместить результат в диапазон - $A$7.

5. Выполнить команду меню Разработчик, Остановить запись.

Макрос Фильтрация будет записан.

Для проверки макроса Фильтрация следует:

1. Изменить условия отбора (изменить вид движения, название организации).

2. Выполнить команду меню Разработчик, Макросы.

3. Выбрать из списка макрос Фильтрация, нажать кнопку Выполнить или нажать "горячие клавиши".

Для создания управляющего элемента - кнопки, нажатие которой вызывает запуск предписанного макроса:

1. Выполнить команду меню Разработчик, Элементы управления, Вставить;

2. Нажать элемент Кнопка и обрисовать кнопку на листе,

3. Назначить кнопке макрос Фильтрация.

4. Изменить текст на кнопке - Фильтрация БД.

Итоги движения товаров

Построить сводные таблицы для базы данных Движение:

Таблица А использует макет вида:

Фильтр - нет

Строка - Наименование;

Столбец - Организация;

Значения - поле Итого поступление, операция - Количество чисел, имя - Количество документов;

поле Итого поступление, операция - Сумма, имя - Всего поступило;

поле Итого поступление, операция - Сумма, дополнительное вычисление - Доля от суммы по строке; имя - Уд. вес.

Сводную таблицу разместить на новом листе с именем Своды.

Таблица В использует макет вида:

Фильтр - Наименование;

Строка - Дата;

Столбец - Организация;

Значения - поле Итого реализация, операция - Количество чисел, имя - Количество документов;

поле Итого реализация, операция - Сумма, имя - Сумма реализации.

Сводную таблицу разместить на листе Своды. Выполнить группирование данных по полю Дата - интервал группирования месяц. Для группирования курсор поместить в сводной таблице в область значений поля Дата, выполнить команду меню Данные, Структура, Группировать, Группировать указать шаг – Месяцы.