5.1. Отчеты

В столбцах L, M, N создаем списки возможных значений для полей таблицы: Дата продажи, ФИО продавца, Наименование.

В ячейки C5, D5, E5 копируем названия полей: Дата продажи, ФИО продавца, Наименование. В ячейках C7, D7, E7 с помощью команды Разработчик/Вставить/Элементы управления формой размещаем элементы управления в виде полей со списком .

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

Задаем свойства каждого элемента управления с помощью команды Разработчик/группа Элементы управления/кнопка Свойства или щелкаем элемент управления правой кнопкой мыши и выбираем пункт Формат элемента управления.

В поле Формировать список по диапазону вводим ссылку на диапазон ячеек, в котором хранятся элементы списка (записанные в столбцах L, M, N), в поле Связь с ячейкой - ссылка на ячейку, в которой будет храниться выбранный элемент списка (C6, D6, E6).

Для созданных элементов управления (полей со списком) задаем следующие параметры с помощью команды контекстного меню Формат объекта – диалоговое окно Формат элемента управления – вкладка Элемент управления:

Для поля «Дата продажи»
Для поля «ФИО продавца»
Для поля «Наименование»

Связанная ячейка возвращает номер выбранного элемента списка. Первый элемент в диапазоне ячеек возвращает значение 1, второй — 2 и так далее.

Этот номер будем использовать в функции ИНДЕКС для формирования критерия в ячейках A1:G2.

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

Действия пользователя следующие:

1. Выделить ячейку A1.

2. Выбрать команду Сервис/Макрос/Начать запись (Разработчик/Записать макрос). Задать имя «отчеты».

3. Установить курсор в ячейку С2 и нажать клавишу Delete

4. Выбрать команду Вставка/Функция. В появившемся окне в категории «Ссылки и массивы» выбрать функцию ИНДЕКС (форма массива).

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

6. Установить курсор в ячейку D2 и нажать клавишу Delete

7. Выбрать команду Вставка/Функция. В появившемся окне в категории «Ссылки и массивы» выбрать функцию ИНДЕКС.

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

9. Установить курсор в ячейку E2 и нажать клавишу Delete

10. Выбрать команду Вставка/Функция. В появившемся окне в категории «Ссылки и массивы» выбрать функцию ИНДЕКС.

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

12. Установить курсор в ячейку A10, выбрать команду Данные/Фильтр/Расширенный фильтр (Данные/Сортировка и фильтр/Дополнительно)и в появившемся окне ввести следующие параметры:

13. Выбрать команду Сервис/Макрос/Остановить запись (Разработчик/Остановить запись).

Для ячейки С2 в строке

ActiveCell.FormulaR1C1 = "=INDEX(R[-1]C[6]:R[5]C[6],R[4]C)"

Необходимо добавить текст «If Range("C6") <> 1 Then», в результате получим

If Range("C6") <> 1 Then ActiveCell.FormulaR1C1 = "=INDEX(R[-1]C[6]:R[5]C[6],R[4]C)"

Для ячейки D2 в строке

ActiveCell.FormulaR1C1 = "=INDEX(R[-1]C[6]:R[2]C[6],R[4]C)"

Необходимо добавить текст «If Range("D6") <> 1 Then », в результате получим

If Range("D6") <> 1 Then ActiveCell.FormulaR1C1 = "=INDEX(R[-1]C[6]:R[2]C[6],R[4]C)"

Для ячейки E2 в строке

ActiveCell.FormulaR1C1 = "=INDEX(R[-1]C[6]:R[4]C[6],R[4]C)"

Необходимо добавить текст «If Range("E6") <> 1 Then », в результате получим

If Range("E6") <> 1 Then ActiveCell.FormulaR1C1 = "=INDEX(R[-1]C[6]:R[4]C[6],R[4]C)"

Полный текст полученной процедуры после внесения изменений:

Sub отчеты()

Range("C2").Select

Selection.ClearContents

If Range("C6") <> 1 Then ActiveCell.FormulaR1C1 = "=INDEX(R[-1]C[6]:R[5]C[6],R[4]C)"

Range("D2").Select

Selection.ClearContents

If Range("D6") <> 1 Then ActiveCell.FormulaR1C1 = "=INDEX(R[-1]C[6]:R[2]C[6],R[4]C)"

Range("E2").Select

Selection.ClearContents

If Range("E6") <> 1 Then ActiveCell.FormulaR1C1 = "=INDEX(R[-1]C[6]:R[4]C[6],R[4]C)"

Range("A10").Select

Range("A10:G40").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

Range("A1:G2"), Unique:=False

End Sub

После выполнения фильтрации получим итоговые значения – общее количество товара и сумму. Для этого выделяем первую пустую ячейку в нужном столбце (например, для суммирования количества - F51, а для сумм - G51) и нажимаем на значок автосуммы ∑ (на ленте Главная/Редактирование).

В указанных ячейках появляются искомые значения в результате выполнения функций =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;F11:F50) и =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;G11:G50).

Для отмены фильтрации данных в редакторе Visual Basic создается процедура «отменить_все», которая должна содержать следующий программный код:

Sub отменить_все()

Range("C6") = "1"

Range("D6") = "1"

Range("E6") = "1"

Application.Run "отчеты"

Range("A1").Select

End Sub

В MS Office 2007/10 для отмены результатов расширенного фильтра можно записать макрос:

1. Выбрать команду Разработчик/Записать макрос. Задать имя «отменить_все»;

2. Выделить ячейку A10;

3. Выполнить команду Данные/группа Сортировка и фильтр/кнопка Очистить;

4. Выбрать команду Разработчик/Остановить запись.

Sub отменить_фильтр()

' отменить_фильтр Макрос

'

Range("A10").Select

ActiveSheet.ShowAllData

End Sub

Созданные макросы назначаем кнопкам «Получить отчет» и «Отменить все» соответственно.

Для кнопки «Титульный лист» создаем гиперссылку для перехода на титульный лист».

Модулю, в котором записаны все программы для работы с листом «отчеты» задаем имя «№6_отчеты».

Для скрытия вспомогательных данных на листе выделяем диапазоны A1:G2 и L1:N7 и задаем белый цвет шрифта символов.

По условию задачи нужно получить отчет о выручке каждого продавца с указанием количества и стоимости проданного товара. Зададим, например, ФИО продавца Попова, тогда БД примет следующий вид:

В ячейках F51 и G51- итоговые значения, полученные с помощью функций =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;F11:F50) и =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;G11:G50).