Электронная тетрадь для практической работы
методическая разработка по информатике и икт (8, 9 класс)

Задание: “Вы - бухгалтер МП “Магазин Продукты”. Ваша фирма торгует в розницу продуктами питания. Вы должны организовать движение товаров внутри Вашей фирмы по следующей схеме:

закупка товара по оптовой цене; продажа за март текущего года по цене розничной;
подсчет остатков на складе; подсчитать прибыль по каждому товару;
подсчитать накладные расходы: расход на аренду, равный 25% от прибыли, и расход на налоги 12% от прибыли; подсчитать чистую прибыль: из полученной прибыли вычесть расходы;
подвести итоги по закупке, продаже и полученной прибыли.

Для решения задачи создать таблицу «Продажи за март».

Задание 1.

1. Зайти в папку Мои документы.
2. Зайти в папку Группа с номером Вашей группы.
3. Создать папку с именем Ваша Фамилия.
4. Создать файл Лист Microsoft Excel.
5. Переименовать файл в Магазин.
6. Открыть файл.

Задание 2.

1. Создать на Листе 1 таблицу «Продажи за март».

На первой строке листа внести заголовок Продажи за март, оформить полужирным шрифтом, размер 14.

2. Занести названия столбцов Наименование товара и Цена оптовая, в руб.

Текст оформить размером 12. Что бы заголовки столбцов занимали меньше места, задать команду [Формат – ячейки…],  вкладка Выравнивание и отметить флажок  Перенос по словам.

3. Заполнить в таблицу данные по столбцам Наименование товара и Цена оптовая, в руб..

Взять 5 наименований товара  и цену для каждого отдела из карточки по выбору. Всего 15 наименований.

В столбце Цена оптовая, в руб. задать числовой формат Денежный с 2 десятичными знаками, Обозначение выбрать р. 

Для выполнения выделить столбец и зайти [Формат – ячейки…],  вкладка Число.

4. В столбце № п/п введите номер товара, используя авто заполнение.

Для этого в ячейку в строке первого товара внести цифру 1, для следующего товара – 2. Выделить обе ячейки, взять мышкой за маркер авто заполнения  и протянуть до последнего товара.

5. Текущий лист назвать «Март».

Для этого левой кнопкой мыши дважды щелкнуть на имени Лист 1 в левом нижнем краю экрана. Имя листа подсветиться. Ввести текст Март. Нажать клавишу Enter для завершения ввода.

Задание 3.

1. Для каждого Наименованию товара заполнить произвольно данные в столбцы Кол-во было и Кол-во прод. по всем товарам. Учтите, что значение в столбце «Кол-во было» должно быть больше, чем столбце «Кол-во прод.».
2. Отсортировать данные по столбцу Наименование товара.

Для этого курсор поставить в заголовок столбца и нажать кнопку Сортировка по возрастанию на Панели инструментов.

Задание 4.

1. Создать диаграмму График, показывающую «Сколько товара по каждому наименованию было закуплено и сколько продано».

Выделить, с использованием клавиши Ctrl, данные в столбцах Наименование товара,  Кол-во было и Кол-во прод..

Включить Мастер диаграмм, используя команду [Вставка – Диаграмма…]. 

На шаге 1 выбрать Тип диаграммы График Вид График с маркерами.

Оценить внешний вид диаграммы с Вашими данными: нажать кнопку Просмотр результата.

На шаге 2 проверить правильность выделения данных.

На шаге 3 задать название диаграммы «Сколько товара по каждому наименованию было закуплено и сколько продано» на вкладке Заголовки.

На шаге 4 - разместить диаграмму на отдельном листе.

2. Сохранить результаты работы.

Задание 5.

1. Выполните расчет для первого товара:

Для выполнения задания создайте столбцы и внесите формулы в строке для первого товара.

2. Для данных в столбцах Цена розн., в руб, Сумма было, Сумма прод., Сумма ост., Прибыль, Налог 12%, Арен. 25%, Чистая прибыль задать формат числовой формат Денежный с 2 десятичными знаками, Обозначение выбрать р.
3. Авто заполните формулами вниз для остальных Наименований товаров.

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

Задание 6.

1. Создать круговую диаграмму, показывающую «Доли  Чистой прибыли по Наименованиям товаров».

Для выполнения выделить данные в столбцах Наименование товара и  Чистая прибыль. Включить Мастер диаграмм.

На шаге 1 выбрать Тип диаграммы Круговая, Вид Разрезная круговая диаграмма. Выполнить Просмотр результата. На шаге 2 проверить правильность выделения данных.

На шаге 3 задать название диаграммы «Доли  Чистой прибыли по Наименованиям товаров»на вкладке Заголовки. На шаге 4 - разместить диаграмму на отдельном листе.

2. Сохранить результаты работы.

Задание 7.

1. Подсчитать итоги по столбцам Кол-во было,  Кол-во прод., Чистая прибыль.

Для выполнения в строку под таблицей внести текст ИТОГИ. Курсор поставить внизу столбца Кол-во было и нажать кнопку Авто суммирование Σ, проверить выделение данных, нажать Enter.

Так же подсчитать итоги по другим столбцам.

2. Оформите таблицу для печати так, чтобы она целиком помещалась на одном печатном листе.

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

Задание 8.

1. Добавить столбец Отдел.

Для выполнения курсор поставить в столбец Наименование товара, выполнить команду [Вставка – Столбец…]. Внести заголовок столбца. 

2. Для  каждого товара ввести название отдела, в котором он продается (Мясо-Рыба, Бакалея, Овощи-Фрукты).
3. Отобрать с использованием фильтра и скопировать на лист «Овощи-Фрукты» данные по отделу Овощи-Фрукты.

Для выполнения задания создать новый лист: выполнить команду  [Вставка – Лист…], назвать лист  «Овощи-Фрукты».

Установить Авто фильтр на листе Март. Для выполнения задания вернуться на лист Март. Курсор поставить в шапку таблицы. Выполнить команду  [Данные – Фильтр – Авто фильтр].

В столбце Отдел отобрать данные по условию «равно Овощи-Фрукты».

Выделить и скопировать шапку таблицы и оставшиеся на экране строки, перейти левой кнопкой мыши на лист Овощи-Фрукты, курсор поставить в столбец А, вставить.

4. Отменить Авто фильтр на листе Март.

Для выполнения задания перейти левой кнопкой мыши на лист Март. Курсор поставить в шапку таблицы. Выполнить команду  [Данные – Фильтр – Авто фильтр].

Задание 9.

1. Подсчитать на листе Март итоги  по каждому отделу и по всему магазину в целом по столбцам  «Сумма было», «Сумма прод.», «Сумма ост.», «Прибыль», «Налог 12%», «Арен. 25%», «Чист. прибыль».

• Для выполнения задания удалить строку ИТОГИ. Курсор поставить в строку ИТОГИ, выполнить команду  [Правка – Удалить - Строку…]
• Отсортировать данные Сортировать по «Отдел»,  Затем по «Товар».

Для выполнения курсор поставить в шапку таблицы (выделять не нужно, так как в заголовках столбцов объединенные ячейки не использовались!!!),  выполнить команду  [Данные – Сортировка – …]. В открывшемся окне в разделе Сортировать по в раскрывающемся списке выбрать Отдел, в разделе Затем по – выбрать Товар.

• Подвести Промежуточные итоги.

Для выполнения курсор поставить в шапку таблицы. Выполнить команду  [Данные - Итоги…].

В открывшемся окне в разделе «При каждом изменении в»  в раскрывающемся списке  выбрать столбец «Отдел»; в разделе «Операция» – выбрать Сумма; в разделе «Добавить итоги по» птичкой отметить столбцы, по которым нужно подсчитать суммы: «Сумма было», «Сумма прод.», «Сумма ост.», «Прибыль», «Налог 12%», «Арен. 25%», «Чист. прибыль».

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

Для выполнения задания в левом верхнем углу экрана нажать мышкой на кнопку 2.

3. Скопировать эти данные на лист «Итоги», выделив только строки с итогами.

Для выполнения задания создать новый лист, назвать лист - «Итоги».

Вернуться на лист Март. Выделить строку с шапкой таблицы: нажать левой кнопкой мыши на номер строки, в которой находится шапка таблицы. Зажать клавишу Ctrl  и, не отпуская, нажимать левой кнопкой мыши на номера строк с итогами. Скопировать полученной выделение. Перейти на лист Итоги. Курсор в столбец А, вставить.

4. Отменить Промежуточные итоги на листе Март.

Для выполнения задания перейти на лист Март. Курсор поставить в шапку таблицы; выполнить команду  [Данные – Итоги…]; в открывшемся окне нажать  на кнопку Убрать все.

Задание 10.

1. На новом листе создать таблицу «Продажи за апрель».

Для выполнения смотри Задание 2. Лист назвать Апрель.

2. Занести данные по пяти товарам из списка за март: Отдел; Товар; Цена было, в руб.; Кол-во было; Кол-во прод. Сделать расчет для этих товаров по формулам Задания 5.

Для выполнения задания попробуйте скопировать шапку таблицы и данные по столбцам Отдел; Товар; Цена было, в руб. с листа Март, данные Кол-во было; Кол-во прод. заполните произвольно. Так как расчет идет по той же схеме, что и на листе Март (Задания 5), попробуйте скопировать не только исходные данные, но и формулы расчета. При вставке не перепутайте порядок следования столбцов!

3. Занести данные по пяти новым товарам: Отдел; Товар; Цена было, в руб.; Кол-во было; Кол-во прод. Сделать расчет для этих товаров по формулам Задания 5.

Для выполнения задания добавить данные из карточки, данные Кол-во было; Кол-во прод. заполните произвольно.

Так как расчет идет по той же схеме, что и на листе Март (Задания 5), попробуйте заполнить формулы для этих строк с использованием маркера автозаполнения.

Задание 11.

1. Отобрать с использованием фильтра и скопировать на лист «Овощи-Фрукты» данные по отделу Овощи-Фрукты.

Для выполнения смотри Задание 8. Результат скопировать на уже существующий лист Овощи-Фрукты.

2. Отменить Авто фильтр на листе Апрель.

Задание 12.

1. Подсчитать на листе Апрель итоги  по каждому отделу и по всему магазину в целом по столбцам  «Сумма было», «Сумма прод.», «Сумма ост.», «Прибыль», «Налог 12%», «Арен. 25%», «Чист. прибыль».

Для выполнения смотри Задание 9.

2. При помощи кнопок уровней с левой стороны показать только итоги по отделам и общий итог. Скопировать эти данные на лист «Итоги», выделив только строки с итогами.

Для выполнения смотри Задание 9. Результат скопировать на уже существующий лист Итоги.

3. Отменить Промежуточные итоги на листе Апрель.

Для выполнения смотри Задание 9.

Задание 13.

1. Подсчитать на листе Овощи-Фрукты итоги  за два месяца по каждому Товару и в целом по отделу  по столбцам  «Сумма было», «Сумма прод.», «Сумма ост.», «Прибыль», «Налог 12%», «Арен. 25%», «Чист. прибыль».

Обратите внимание, что итоги подводятся не отделу и магазину в целом (как в Задание 9), а по товару и отделу. Поэтому перед подведением итогов необходимо отсортировать данные по столбцу Товар, при каждом изменении в котором нужно подводить промежуточные итоги.

Подвести Промежуточные итоги. Для выполнения смотри Задание 9. Только в разделе При каждом изменении в  выбрать «Товар».

Задание 14.

1. Промежуточные итоги на листе Овощи-Фрукты, подсчитанные в Задании 13, не отменять!!!
2. Проверить с использованием Промежуточных итогов на листе Овощи-Фрукты: были ли товары, которые продавались и в марте, и апреле? Результат поместить на одном листе с Итогами по операции сумма.

Для ответа на вопрос вспомним, что на лист копировались данные о продаже за март и апрель по отделу Овощи-Фрукты (Задание 8 и 11) . Поэтому, если товар продавался и в том, и в другом месяцах, то встречаться в таблице он будет два раза.

Для выполнения курсор поставить в шапку таблицы. Выполнить команду  [Данные - Итоги…].

Чтобы не затереть Итоги по операции Сумма, Убрать флажок «Заменить текущие итоги».

В открывшемся окне в разделе «При каждом изменении в»  выбрать столбец «Товар»; в разделе «Операция» выбрать Количество; в разделе «Добавить итоги по» птичкой отметить «Товар».

Итог пункта 2. Проверить в строках итогов по Количество в столбце Товар встречается ли Цифра 2. Если – да, то эти товары продавались и в марте, и в апреле.

На листе Овощи-Фрукты мы получили Анализ данных по двум операциям Промежуточных итогов.

Задание 15.

1. Сделать сводную таблицу для листа «Март» для столбцов Отдел, Товар, Чист. прибыль. Поместить Отдел в поля страницы, Товар в поля строк, Чист. прибыль в поля данных, Цена оптовая, в руб. в поля столбцов.

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

Выполнить команду  [Данные – Сводная таблица…]. Загрузится Мастер сводных таблиц.

На шаге 1. Так как мы используем одну таблицу, в разделе «Создать таблицу на основе данных, находящихся:» выбрать позицию  «в списке или базе данных». В разделе «Вид создаваемого отчета» выбрать «Сводная таблица». Перейти к следующему шагу мастера (нажать кнопку Далее  ).

На шаге 2. проверить выделении таблицы.

На шаге 3. В разделе «Поместить таблицу» выбрать новый лист.

Назвать полученный лист «Сводная».

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

Таким же образом взять поле Наименование товара и перетащить на «Перетащите сюда поля строк».

Таким же образом взять поле Цена оптовая, в руб и перетащить на «Перетащите сюда поля столбцов».

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

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

Для выполнения задания перейти на лист Сводная. Создать диаграмму. Для этого выполнить команду  [Вставка – Диаграмма…]. На отдельном листе автоматически создастся Сводная диаграмма. Просмотреть полученную диаграмму.