Задание 18
Часть 1. Создание, редактирование, форматирование таблицы. Построение диаграмм
Создание таблицы
1. Создать таблицу анализа торгово-закупочной деятельности предприятий в соответствии с образцом (рис. 11). Ввести в таблицу следующие названия столбцов: Фирма, Товары, Стоимость покупки (тыс. руб.), Себестоимость покупки, Стоимость продажи (тыс. руб.), Процент НДС, НДС продажи, Выручка, Налог на выручку, Прибыль (убыток). Ввести 10 записей.
2. Ввести исходные данные в столбцы Фирма, Товары, Стоимость покупки, Стоимость продажи. Все расчеты выполняются в тысячах рублей.
3. Значения остальных столбцов рассчитать по формулам:
- Себестоимость покупки = Стоимость покупки +Прочие расходы при покупке. Для ввода в формулу значений прочих расходов использовать дополнительную таб.1 и функцию ПРОСМОТР;
- значения столбца Процент НДС определить с помощью функции ПРОСМОТР, используя дополнительную таб.1;
- НДС продажи = Стоимость продажи * Процент НДС / (100% + Процент НДС);
- Выручка = Стоимость продажи - Себестоимость покупки - НДС продажи;
- значения столбца Налог на выручку рассчитать с использованием функции ЕСЛИ. Если выручка есть, то налог составляет 24% от выручки. Значение % выручки ввести за пределами таблицы;
- Прибыль (убыток) = Выручка - Налог на выручку.
Рис. 11
4. В соответствии с образцом в выделенных ячейках рассчитать итоговые значения столбцов. При вводе функции суммирования использовать различные средства: Мастер функций или Автосуммирование.
Форматирование таблицы
5. Отформатировать таблицу:
- для ячеек первой строки («шапки») таблицы длинные заголовки столбцов перенести в ячейке по словам;
- изменить шрифт первой строки на жирный курсив, размер 11pt, выполнить заливку бледно-голубым цветом, расположить текст по центру;
- столбец Прибыль (убыток) выделить серым фоном;
- добавить две - три строки для заголовка таблицы и ввести заголовок как объект WordArt c текстом «Анализ торгово-закупочной деятельности предприятий»;?
- отменить вывод на экран сетки таблицы. Расчертить таблицу линиями: внешнюю рамку таблицы – жирной линией, «шапку» таблицы подчеркнуть синей линией, столбцы – тонкими черными линиями, строки линиями не разделять;
- используя условное форматирование, выделить красным цветом в столбце Прибыль (убыток) значения, соответствующие убытку;?
- в ячейку с названием одного из убыточных предприятий вставить выноску с текстом «Убыточное предприятие»;
- присвоить листу имя Расчет прибыли. Изменить цвет ярлычка листа.
Построение диаграмм
6. На отдельном листе построить график с маркерами. В качестве ряда использовать данные из столбца Стоимость покупки, в качестве категорий – данные из столбца Фирма. Добавить в диаграмму ряды Стоимость продажи и Прибыль (убыток) путем копирования. Ряд Прибыль (убыток) расположить по вспомогательной оси. Для точек с максимальным и минимальным значениями продаж вывести имена категорий и значения продаж. Присвоить заголовок диаграмме и названия осям. Добавить на лист с диаграммой узор подложки.?
7. Создать внедренную пирамидальную гистограмму, отображающую значения стоимости продаж ООО “Стиль”. В качестве категорий выбрать соответствующие названия товаров. Для самой высокой пирамиды в качестве заливки выбрать текстуру «Циновка». Изменить заливку стенок диаграммы. Подписи по оси категорий выполнить курсивом. Изменить шрифт и обрамление заголовка.?
8. Сохранить файл под именем Фамилия_№ задания.xls.
Подбор параметра
9. Используя инструмент Подбор параметра, рассчитать, какова должна быть стоимость продаж одежды ЗАО «Норма», чтобы прибыль этой фирмы составила 10 тыс. руб.?
Таблица подстановки
10. При помощи таблицы подстановки данных проанализировать зависимость прибыли ООО “Стиль” от изменения процента НДС при продаже продовольственных товаров. Исходные значения выделить цветом.
Работа с базами данных. Фильтрация списка
11. Преобразовать таблицу на листе Расчет прибыли в список. Для этого вставить в книгу новый лист Список. Скопировать на лист Список значения ячеек таблицы с листа Расчет прибыли (в диапазон ячеек для копирования не включать итоговые строки и строку заголовка таблицы).
12. Используя форму данных, просмотреть всю информацию по предприятию ООО “Стиль”.
13. Вычислить количество фирм, продающих продовольственные товары, используя функцию баз данных БДСЧЕТА.
Подведение итогов
14. Скопировать значения ячеек таблицы Расчет прибыли на новый лист Итоги (в диапазон ячеек для копирования не включать итоговые строки и строку заголовка таблицы). Отсортировать таблицу на листе Итоги по фирмам, а внутри - по товарам.
15. Разбить список на листе Итоги на группы по фирмам и подвести промежуточные и общие итоги по полям Фирма (операции Количество), Стоимость покупки, Стоимость продажи, Выручка, Прибыль (операция Сумма).