Материал: Базовые технологии в табличном процессе Exel - Учебное пособие (Ильина О.П.)

Предмет: Информационные технологии

Просмотров: 3405


Анализ движения товаров

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

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

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

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

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

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

Поле группирования Товар,

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

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

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

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

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

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

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

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

6. Убрать итоги команда Данные, Итоги, Убрать все.

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

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

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

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

 

 

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

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

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

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

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

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

а также о продажах товаров на сумму в заданном диапазоне:

Цена

Итого реализация

Итого реализация

>100

 

>0

 

>=12000

<=30000

 

Рис. 47 Расширенный фильтр

 

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

15\%:

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

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

 

Рис. 48 Вычисляемый критерий

 

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

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

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

Обработка Фильтровать список на месте Исходный диапазон имя блока База_данных, Диапазон условий блок критериев ( лист Критерии); Только уникальные записи.

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

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

 

А

В

1

2

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

2

 

 

3

Организация

Итого реализация

4

Универмаг ЦФТ

>0

 

Рис. 49 Условия фильтрации

 

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. Фильтрация базы данных команда меню Данные,  Фильтр, Расширенный  фильтр,

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

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

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

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

 

 

-           Выполнение команды Данные,  Фильтр, Расширенный фильтр

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

1. Выполнить команду меню Сервис,  Макрос,  Начать запись.  В диалоговом окне задать имя макроса Фильтрация и "горячие" клавиши вызова макроса +, указать место хранения макроса Эта книга, нажать кнопку ОК

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

Правка, Удалить.

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

4. Выполнить команду меню Данные,  Фильтр, Расширенный фильтр,

указав:

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

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

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

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

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

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

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

2. Выполнить команду меню Сервис, Макрос, Макросы.

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

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

1. Выполнить команду меню Вид,  Панели  инструментов, выбрать панель Формы;

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

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

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

5. Убрать панель инструментов Формы.

Автоматически макрос транслируется на язык Visual Basic. Для просмотра и редактирования  текста макроса выполнить команду Сервис, Макрос,   Макросы,  выбрать  из  списка  макрос  Фильтрация  и  нажать кнопку Изменить. Текст макроса (аналогичного содержания):

 

Sub Фильтрация()

' название подпрограммы по имени макрса

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

' тело макроса

 

 

' выделение интервала строк 7 – 25

‘ для очистки от предыдущих результатов

Rows("7:25").Select

' удаление выделенных строк

Selection.Delete Shift:=xlUp

' установка курсора в ячейку А7

Range("A7").Select

' команда расширенного фильтра,

' критерии отбора в диапазоне A3:B4,

‘результат в области, начинающейся с А7

Range("Database").AdvancedFilter       Action:=xlFilterCopy,   _ CriteriaRange:=Range ("A3:B4"), _ CopyToRange:=Range("A7"), Unique:=False

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

End Sub

Макрос “Лист”

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

Информационная технология создания макроса Лист:

1. Выполнить команду Сервис,  Макрос,  Начать запись.  Указать имя макроса Лист, горячие клавиши +.

2. Добавить новый лист команда меню Вставка, Лист;

3. Выполнить все действия этапа 1 (см. выше).

4. Выполнить команду меню Вид,  Панели  инструментов, выбрать панель Формы;

5. Нажать элемент Кнопка и обрисовать кнопку на листе Фильтрация, назначить кнопке макрос Фильтрация, изменить текст на кнопке Фильтрация БД.

6. Убрать панель инструментов Формы.

7. Нажать кнопку Остановить запись. Макрос Лист будет записан.

Для проверки макроса Лист  следует выполнить команду меню Сервис, Макрос,  Макросы, выбрать из списка макрос Лист  и нажать кнопку Выполнить.

 

Для просмотра и редактирования текста макроса на языке Visual Basic выполнить команду Сервис,  Макрос,  Макросы, выбрать из списка макрос Лист и нажать кнопку Изменить.

Текст макроса:

 

Sub Лист()

 

 

'Название процедуры, являющейся записью клавишного макроса -  Лист

'Сочетание клавиш: Ctrl+t

'Добавление нового листа и для ячейки А1

‘Создание списка для выбора

Sheets.Add

With Selection.Validation

.Delete

.Add Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="1; 2"

.IgnoreBlank = True

.InCellDropdown = True

.InputTitle = ""

.ErrorTitle = ""

.InputMessage = ""

.ErrorMessage = ""

.ShowInput = True

.ShowError = True

End With

' Установка курсора в ячейку А3 и ввод текста Range("A3").Select ActiveCell.FormulaR1C1 = "Организация"

'Установка курсора в ячейку А4 и

‘обеспечение выбора значений из списка блока Название

Range("A4").Select

With Selection.Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,_ Operator:= xlBetween, Formula1:="=Название"

.IgnoreBlank = True

.InCellDropdown = True

.InputTitle = ""

.ErrorTitle = ""

.InputMessage = ""

.ErrorMessage = ""

.ShowInput = True

.ShowError = True

End With

' Установка курсора в ячейку В1 и ввод формулы

Range("B1").Select

 

 

ActiveCell.FormulaR1C1 = _

"=CHOOSE(RC[-1],""Поступление товаров"",_ ""Реализация товаров"")"

' Установка курсора в ячейку В3 и ввод формулы Range("B3").Select ActiveCell.FormulaR1C1 = _

"=CHOOSE(R[-2]C[-1],""Итого поступление"",_

""Итого реализация"")"

' Установка курсора в ячейку В4 и ввод текста Range("B4").Select ActiveCell.FormulaR1C1 = ">0"

'Установка курсора в ячейку В5 и

‘создание кнопки для вызова макроса Фильтрация

Range("B5").Select

ActiveSheet.Buttons.Add(192.75, 12.75, 94.5, 25.5).Select Selection.OnAction = "Фильтрация" ActiveSheet.Shapes("Button 2").Select Selection.Characters.Text = "Отбор данных БД"

With Selection.Characters(Start:=1, Length:=15).Font

.Name = "Arial  Cyr"

.FontStyle = "обычный"

.Size = 10

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline  = xlUnderlineStyleNone

.ColorIndex  = xlAutomatic

End With

' Установка курсора в ячейку А1

Range("A1").Select

End Sub

 

Внести изменения в текст программы:

1. Объявить в начале программного модуля переменные, которые используются как параметры функции InputBox():

'Оператор объявления переменных

Dim Message, Title, Default, MyValue

'Сообщение пользователю

 

 

Message = "Введите название листа"

'Заголовок диалгового окна

Title = "Отбор записей БД"

'Если не бужет введено другое название нового листа

’используется значение по умолчанию

Default = "Выборка"

2. Вывод дилогового окна для имени листа, и присвоения введенного имени переменной

MyValue = InputBox(Message, Title, Default)

3. Вместо инструкции Sheets.Add следует вставить:

Set NewSheet = Worksheets.Add NewSheet.Name = MyValue Worksheets(MyValue).Activate Range("A1").Select

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

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

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

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

Страница нет

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

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

Данные поле Итого  поступление, операция Кол-во значений,

имя Кол-во документов;

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

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

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

Таблица В использует макет вида: Страница Наименование; Строка Дата;

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

Данные поле Итого  реализация, операция Кол-во значений,

имя Кол-во документов;

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

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

 

 

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

 


Загрузка...