Применение MS Excel для практических расчетов (информационно-аналитическая система магазина по продаже посуды)

End Sub

 

Лист 8. «Функции»

На листе должны быть использованы пять функций рабочего листа из категории  «Работа с базой данных»: ДМАКС, ДМИН, ДСРЗНАЧ, БДСУММ, БДСЧЕТ

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

Для создания таблицы критериев  выделяем ячейки A10:G10 и копируем их в ячейки A1:G1. Строка 2 будет содержать  критерии для фильтрации данных. Создадим на листе следующие 7 кнопок: Титульный лист, Отмена расчетов, ДМАКС, ДМИН, ДСРЗНАЧ, БДСУММ, БСЧЕТ. Заполним ячейки A4:A8 и F4:F8 в соответствии с образцом.

8.1. Функция ДМАКС

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

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

1. Выбрать команду Сервис/Макрос/Начать запись. Задать имя «функции_ДМАКС»
2. Установить курсор в ячейку D2 и ввести с клавиатуры любую фамилию, которая есть в таблице, например, Антонова
3. Установить курсор в ячейку G4 и выбрать команду Вставка/Функция. В появившемся окне из списка «Категория» выбрать «Работа с базой данных», а затем – функцию ДМАКС.
4. В появившемся окне ввести следующие параметры:

5. Нажать ОК и выбрать команду Сервис/Макрос/Остановить запись.
6. Чтобы изменить текст макроса нужно выбрать команду Сервис/Макрос/Макросы/ДМАКС и нажать кнопку «Войти»

 

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

ActiveCell.FormulaR1C1 = "Антонова"

Внести изменения:

ActiveCell.FormulaR1C1 = InputBox("Введите ФИО  продавца")

Связать соответствующую кнопку с макросом ДМАКС. При нажатии на эту кнопку будет выводиться диалоговое окно:

8.2 Функция ДМИН

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

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

1. Выбрать команду Сервис/Макрос/Начать запись. Задать имя «функции_ДМИН»
2. Установить курсор в ячейку E2 и ввести с клавиатуры любое наименование фрукта, которое есть в таблице, например, апельсины
3. Установить курсор в ячейку G5 и выбрать команду Вставка/Функция. В появившемся окне из списка «Категория» выбрать «Работа с базой данных», а затем – функцию ДМИН.
4. В появившемся окне ввести следующие параметры:

 

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

ActiveCell.FormulaR1C1 = "апельсины"

Внести изменения:

ActiveCell.FormulaR1C1 = InputBox("Введите наименование  фрукта")

Связать соответствующую кнопку с  макросом ДМИН. При нажатии на эту  кнопку будет выводиться диалоговое окно:

8.3.  Функция ДСРЗНАЧ

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

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

1. Выбрать команду Сервис/Макрос/Начать запись. Задать имя «функции_ДСРЗНАЧ»
2. Установить курсор в ячейку С2 и ввести с клавиатуры любое наименование фрукта, которое есть в таблице, например, апельсины
3. Установить курсор в ячейку G6 и выбрать команду Вставка/Функция. В появившемся окне из списка «Категория» выбрать «Работа с базой данных», а затем – функцию ДСРЗНАЧ.
4. В появившемся окне ввести следующие параметры:

 

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

ActiveCell.FormulaR1C1 = "01.10.07"

Внести изменения:

ActiveCell.FormulaR1C1 = InputBox("Введите дату")

Связать соответствующую кнопку с  макросом ДСРЗНАЧ. При нажатии на эту кнопку будет выводиться диалоговое окно:

8.4. Функция БДСУММ

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

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

1. Выбрать команду Сервис/Макрос/Начать запись. Задать имя «функции_БДСУММ»
2. Установить курсор в ячейку С2 и ввести с клавиатуры любое наименование фрукта, которое есть в таблице, например, апельсины
3. Установить курсор в ячейку G7 и выбрать команду Вставка/Функция. В появившемся окне из списка «Категория» выбрать «Работа с базой данных», а затем – функцию БДСУММ.
4. В появившемся окне ввести следующие параметры:

 

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

ActiveCell.FormulaR1C1 = "01.10.07"

Внести изменения:

ActiveCell.FormulaR1C1 = InputBox("Введите дату")

Связать соответствующую кнопку с  макросом БДСУММ. При нажатии на эту кнопку будет выводиться диалоговое окно:

8.5. Функция БСЧЕТ

Запишем макрос для нахождения количества строк на определенную сумму, задаваемую с помощью условия, которое будем  вводить через диалоговое окно InputBox.

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

1. Выбрать команду Сервис/Макрос/Начать запись. Задать имя «функции_БСЧЕТ»
2. Установить курсор в ячейку G2 и ввести с клавиатуры любое наименование фрукта, которое есть в таблице, например, апельсины
3. Установить курсор в ячейку G8 и выбрать команду Вставка/Функция. В появившемся окне из списка «Категория» выбрать «Работа с базой данных», а затем – функцию БСЧЕТ.
4. В появившемся окне ввести следующие параметры:

 

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

ActiveCell.FormulaR1C1 = ">1000"

Внести изменения:

ActiveCell.FormulaR1C1 = InputBox("Введите условие для суммы")

Связать соответствующую  кнопку с макросом БСЧЕТ. При нажатии  на эту кнопку будет выводиться диалоговое окно:

8.6.  Отмена функций

Запишем макрос для отмены расчетов. Действия пользователя следующие:

1. Выбрать команду Сервис/Макрос/Начать запись. Задать имя «функции_отмена»
2. Выделить ячейки A2:G2 и нажать клавишу Delete
3. Выделить ячейки G4:G8 и нажать клавишу Delete
4. Установить курсор в ячейку A1
5. Выбрать команду Сервис/Макрос/Остановить запись

 

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

Для всех остальных кнопок назначаем соответствующие макросы.

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

Во все макросы, кроме  макроса «функции_отмена», для очистки  ячеек с результатами расчетов добавляем  после названия строку

Call функции_отмена

 

Полный текст  полученного модуля:

Sub функции_ДМАКС()

Call функции_отмена

Range("D2").Select

ActiveCell.FormulaR1C1 = InputBox("Введите ФИО продавца")

Range("G4").Select

ActiveCell.FormulaR1C1 = "=DMAX(R[6]C[-6]:R[36]C,R[6]C,R[-3]C[-6]:R[-2]C)"

End Sub

 

Sub функции_ДМИН()

Call функции_отмена

Range("E2").Select

ActiveCell.FormulaR1C1 = InputBox("Введите наименование фрукта")

Range("G5").Select

ActiveCell.FormulaR1C1 = "=DMIN(R[5]C[-6]:R[35]C,R[5]C,R[-4]C[-6]:R[-3]C)"

End Sub

 

Sub функции_ДСРЗНАЧ()

Call функции_отмена

Range("C2").Select

ActiveCell.FormulaR1C1 = InputBox("Введите дату")

Range("G6").Select

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

End Sub

 

Sub функции_БДСУММ()

Call функции_отмена

Range("C2").Select

ActiveCell.FormulaR1C1 = InputBox("Введите дату")

Range("G7").Select

ActiveCell.FormulaR1C1 = "=DSUM(R[3]C[-6]:R[33]C,R[3]C,R[-6]C[-6]:R[-5]C)"

End Sub

 

Sub функции_БСЧЕТ()

Call функции_отмена

Range("G2").Select

ActiveCell.FormulaR1C1 = InputBox("Введите  условие для суммы")

Range("G8").Select

ActiveCell.FormulaR1C1 = "=DCOUNT(R[2]C[-6]:R[32]C,R[2]C,R[-7]C[-6]:R[-6]C)"

End Sub

 

Sub функции_отмена()

Range("A2:G2").Select

Selection.ClearContents

Range("G4:G8").Select

Selection.ClearContents

Range("A1").Select

End Sub

Лист 9. «Сводная таблица»

Рассмотрим  порядок создания сводной таблицы  один из вариантов создания.

Перейти на лист «Продажи», установить курсор внутри таблицы  в ячейке A1 и выбрать команду Данные/Сводная таблица. Появится Мастер создания сводных таблиц.

Первый шаг:

Нажать кнопку «Далее»

Второй шаг  – выделить всю таблицу вместе со строкой заголовков:

Третий шаг - выбрать место размещения таблицы

нажать кнопку «макет», появится макет сводной таблицы

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

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

В область «строка» поместим столбец «наименование», в  область «столбец» поместим столбец  «ФИО продавца».

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

Один из примеров макета:

Нажать кнопку «ОК», а затем «Готово».

На новом  листе появится таблица:

Переименуем лист и зададим ему название «Сводная таблица».

Создаем на листе  кнопку «Титульный лист» и назначаем ранее созданный макрос «лист_титульный_лист».

Лист 10. «Сводная диаграмма»

Щелкнем правой кнопкой мыши внутри сводной таблицы  и выберем пункт «сводная диаграмма». На отдельном листе появится диаграмма, которая связана с полями сводной таблицы.

Переименуем лист и зададим ему название «Сводная диаграмма».

Изменим тип  диаграммы

Название диаграммы  – «Результаты работы магазина по продаже фруктов», подписи данных – значения.

Создаем на листе  кнопку «Титульный лист» и назначаем  ранее созданный макрос «лист_титульный_лист».

В результате получим  следующую диаграмму

Лист 11. «Отчеты»

Для получения  отчетов будем использовать возможность  отбора данных из таблицы с помощью  расширенного фильтра и элементов  управления.

Для создания таблицы критериев выделяем ячейки A10:G10 и копируем их в ячейки A1:G1. В столбцах I, J, K создаем списки возможных значений для полей таблицы: Дата продажи, ФИО продавца, Наименование.

На листе  создаем 2 кнопки: Титульный лист, Отменить все. С помощью панели Формы создаем 3 поля со списком и добавляем соответствующие названия полей над ними.

Для поля «Дата  продажи» задаем следующие параметры:

Для поля «ФИО продавца»  задаем следующие параметры:

Для поля «Наименование» задаем следующие параметры:

Лист «Отчеты» должен выглядеть  следующим образом

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

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

1. Выбрать команду Сервис/Макрос/Начать запись. Задать имя «отчеты»
2. Установить курсор в ячейку С2 и нажать клавишу Delete
3. Выбрать команду Вставка/Функция. В появившемся окне в категории «Ссылки и массивы» выбрать функцию ИНДЕКС.
4. В появившемся окне ввести следующие параметры:

5. Установить курсор в ячейку D2 и нажать клавишу Delete
6. Выбрать команду Вставка/Функция. В появившемся окне в категории «Ссылки и массивы» выбрать функцию ИНДЕКС.
7. В появившемся окне ввести следующие параметры: