Решение профессиональных задач средствами табличного процессора MS Excel

Задание №1. Решение профессиональных задач средствами табличного процессора MS Excel.

Задания выполняются с данными файла “Продажи.xls”.  Под одной продажей понимаются данные одной строки. Количество – это количество единиц проданного товара.

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

 

Вариант 15

 

 

1. Выполнить сортировку данных

• по области в алфавитном порядке;

• по товару, городу, магазину, дате.

2. Выбрать данные о продажах с помощью Автофильтра

колбасы в магазинах 1, 2 города Гомеля

3. Выбрать данные о продажах  с помощью Расширенного фильтра

колбасы в магазинах 1, 2 города Гомеля

4. Определить, используя подведение итогов

средние цены каждого товара за каждую дату

5. Определить, используя сводные таблицы

общее количество проданного для каждого товара в каждой области

 

 

1. Выполнить сортировку данных

• скопировать список на лист 2;

по области в алфавитном порядке;

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

Результат:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

• по четырем ключам:
• выделить любую ячейку списка;
• выполнить команду Данные – Сортировка, появится диалоговое окно (ДО) Сортировка диапазона;

 

• переименовать лист 2  в Сортировка;
• выполнить сортировку:

Выполнение:

инструментов;

• выделить любую ячейку списка;
• выполнить команду Данные – Сортировка, появится диалоговое окно (ДО) Сортировка диапазона;
• в ДО указать ключи и порядок сортировки (выбрать из списка полей):

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2.  Выбрать данные о продажах с помощью Автофильтра

• скопировать список на лист 3;

• переименовать лист 3 в Автофильтр;
• выбрать данные о колбасы в магазинах 1, 2 города Гомеля
• Выполнение:
• выделить любую ячейку списка;
• выполнить команду Данные - Фильтр – Автофильтр. Ячейки с названиями полей превращаются в раскрывающиеся списки;
• щелкнуть по кнопке раскрытия списка в поле Товар, щелкнуть по названию колбаса;
• раскрыть список в поле № магазина, выбрать Условие, появится ДО Пользовательский автофильтр. Выбрать из списков: 1 и 2;
• раскрыть список в поле Город, выбрать Условие, в ДО Пользовательский автофильтр указать: равно   Гомель
• выделить любую ячейку списка;

выполнить команду Данные - Фильтр – Автофильтр

 

 

 

 

 

3. Выбрать данные о продажах  с помощью Расширенного фильтра

колбасы в магазинах 1, 2 города Гомеля

• скопировать список на лист 4;
• переименовать лист 4 в Расширенный фильтр;
• выбрать данные о продажах по условию Автофильтра, отфильтрованные данные разместить:

а) на месте исходного списка;

б) в свободной части листа;

Выполнение:

• создать в свободной области Рабочего листа дополнительную таблицу (диапазон условий), заголовки которой скопировать из заголовков базы данных;
• выделить любую ячейку списка;
• выполнить команду Данные - Фильтр – Расширенный фильтр, появится ДО;
• заполнить поля, выделяя на Рабочем листе:

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

           Диапазон условий         выделить дополнительную таблицу

• а) Обработка            фильтровать список на месте, нажать ОК;

      б) Обработка            скопировать результат в другое место,

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

Результат:

 

 

 

 

 

 

• вывести все записи списка на экран

       выполнить команду Данные - Фильтр –Отобразить все.    

 

 

 

4. Определить, используя подведение итогов

средние цены каждого товара за каждую дату

• скопировать список на лист 4;
• переименовать лист 4 в Итоги;

Выполнение:

• выделить любую ячейку списка;
• выполнить сортировку списка по полям Дата, Товар ;
• выполнить команду Данные – Итоги, появится ДО Промежуточные итоги;.

     в ДО указать:

                  щелчок по кнопке  ОК

• выполнить команду Данные – Итоги, появится ДО Промежуточные итоги;.

     в ДО указать:

 

 

  

 

 

 

 

щелчок по кнопке  ОК

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Итоговые данные:

 

 

 

•  вывести только итоговые строки и озаглавить:
• слева от строки заголовков списка на служебном поле щелкнуть по кнопке с номером 3;

 

• убрать все итоги:
• выделить любую ячейку списка;
• выполнить команду Данные – Итоги,
• в ДО щелкнуть по кнопке Убрать все.

 

5. Определить, используя сводные таблицы

общее количество проданного для каждого товара в каждой области

скопировать список на лист 4;

• переименовать лист 4 в Сводные;

Выполнение:

• выделить любую ячейку списка;
• выполнить команду Данные – Сводная таблица, появится ДО Мастер сводных таблиц и диаграмм шаг 1 из 3;
• на шаге 1 указать:

        Создать сводную таблицу на основе:    данных в списке Microsoft Excel;

       Вид создаваемого отчета:                 сводная таблица;

        щелчок по  кнопке Далее;

• на шаге 2 указать диапазон данных (если ячейка списка была выделена,  то диапазон выделяется автоматически); щелчок по кнопке Далее;
• на шаге 3 указать:

       поместить таблицу в     Новый лист;

      щелкнуть по кнопке Макет;

       в ДО Макет перетащить кнопки полей:

 

 

 

 

 

 

Выполнить двойной щелчок в области Данные по кнопке

 Сумма по полю Цена, выбрать в ДО Вычисление поля сводной таблицы операцию Минимум, нажать ОК;

                  нажать ОК в ДО Макет:

• щелчок по кнопке Готово.

 

На новом Рабочем листе будет создана Сводная таблица. 

• озаглавить сводную таблицу.

 

 

 

 

 

 

 

 

 

 

 

 

Задание 2. СОЗДАНИЕ И ИСПОЛЬЗОВАНИЕ МАКРОСОВ

Создать 2 макроса для выполнения следующих действий:

1. расчета значений переменных в соответствии со своим вариантом в таблице 1.1;
2. очистки ячеек с результатами.

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

Предусмотреть возможность запуска макросов с помощью:

1. панели инструментов пользователя с двумя кнопками;
2. кнопок на рабочем листе;
3. меню пользователя с двумя командами;

графические объекты.

 

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

15

Гипотенузу и площадь прямоугольного треугольника с катетами a, b

 

Порядок выполнения работы:

 

На  рабочем листе создаем таблицу для расчета гипотенузы и площади прямоугольного треугольник:

Записываем макрос для вычисления S и P.

Выполняем  команду:

Сервис – Макрос – Начать запись… Появится Диалоговое окно  Запись макроса.

• указать имя макроса Макрос1;
• ввести описание макроса (расчет гипотенузы и площади прямоугольного треугольника);
• назначить сочетание клавиш для быстрого вызова макроса (использовать только латинские буквы) – Ctrl+Shift+Q;
• выбрать режим сохранения макроса  Эта книга;
• нажать кнопку ОК.

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

• ввести формулу для вычисления гипотенузы в ячейку С3: =КОРЕНЬ(С1*С1+С2*С2) ;
• ввести формулу для вычисления площади в ячейку C4: = С1*С2/2;
• нажать на кнопку Остановить запись или выполнить команду Сервис – Макрос – Остановить запись.

1. Создать макрос для очистки ячеек с формулами.

• выполнить команду Сервис – Макрос – Начать запись…Задать необходимые параметры в диалоговом окне Запись макроса и нажать ОК;

 

• выделить ячейки С3 и С4;
• выполнить команду Правка – Очистить;

• нажать на кнопку Остановить запись или выполнить команду Сервис – Макрос – Остановить запись.

2. Выполнить макросы и проверить правильность их работы.
• ввести значения радиуса в ячейку С1;
• выполнить автоматический расчет гипотенузы и площади прямоугольного треугольника, а затем очистку ячеек с формулами с помощью созданных макросов разными способами:

расчет - с помощью команды Сервис – Макрос – Макросы...,

  выбрать нужный макрос в списке, щелкнуть по кнопке Выполнить;

очистка – с использованием заданного для вызова макроса сочетания клавиш.

3. Выполнить запуск макросов, используя:

• панель инструментов пользователя с двумя кнопками;

Для создания панели инструмента используем команду: Вид – Панели инструментов – Настройка создать и вводим имя панели – гипотенузы и площади прямоугольного треугольника и нажимаем Ок