Часть 2. Анализ и управление данными. Работа с массивами. Часть 1. Создание, редактирование, форматирование таблицы

Часть 1. Создание, редактирование, форматирование таблицы. Построение диаграмм

Создание таблицы

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

2. Ввести исходные данные в столбцы Фирма, Товары, Стоимость покупки, Стоимость продажи.Все расчеты выполняются в тысячах рублей.

3. Значения остальных столбцов рассчитать по формулам:

- Себестоимость покупки = Стоимость покупки +Прочие расходы при покупке.Для ввода в формулу значений прочих расходов использовать дополнительную таб.1 и функцию ПРОСМОТР;

- значения столбца Процент НДС определить с помощью функции ПРОСМОТР, используя дополнительную таб.1;

 

- НДС продажи = Стоимость продажи * Процент НДС / (100% + Процент НДС);

- Выручка = Стоимость продажи - Себестоимость покупки - НДС продажи;

- значения столбцаНалог на выручкурассчитать с использованием функции ЕСЛИ. Если выручка есть, то налог составляет 24% от выручки. Значение % выручки ввести за пределами таблицы;

- Прибыль (убыток) = Выручка - Налог на выручку.

 

Рис. 11

4. В соответствии с образцом в выделенных ячейках рассчитать итоговые значения столбцов. При вводе функции суммирования использовать различные средства: Мастер функцийилиАвтосуммирование.

 

Форматирование таблицы

5. Отформатировать таблицу:

 

- для ячеек первой строки («шапки») таблицы длинные заголовки столбцов перенести в ячейке по словам;

- изменить шрифт первой строки на жирный курсив, размер 11pt, выполнить заливку бледно-голубым цветом, расположить текст по центру;

- столбец Прибыль (убыток) выделить серым фоном;

- добавить две - три строки для заголовка таблицы и ввести заголовок как объект WordArt c текстом «Анализ торгово-закупочной деятельности предприятий»;

- отменить вывод на экран сетки таблицы. Расчертить таблицу линиями: внешнюю рамку таблицы – жирной линией, «шапку» таблицы подчеркнуть синей линией, столбцы – тонкими черными линиями, строки линиями не разделять;

- используя условное форматирование, выделить красным цветом в столбце Прибыль (убыток) значения, соответствующие убытку;

- в ячейку с названием одного из убыточных предприятий вставить выноску с текстом «Убыточное предприятие»;

- присвоить листу имя Расчет прибыли.Изменить цвет ярлычка листа.

Построение диаграмм

6. На отдельном листе построить график с маркерами. В качестве ряда использовать данные из столбца Стоимость покупки, в качестве категорий – данные из столбца Фирма. Добавить в диаграмму ряды Стоимость продажииПрибыль (убыток)путем копирования. Ряд Прибыль (убыток)расположить по вспомогательной оси.Для точек с максимальным и минимальным значениями продаж вывести имена категорий и значения продаж. Присвоить заголовок диаграмме и названия осям. Добавить на лист с диаграммой узор подложки.

7. Создать внедренную пирамидальную гистограмму, отображающую значения стоимости продаж ООО “Стиль”. В качестве категорий выбрать соответствующие названия товаров. Для самой высокой пирамиды в качестве заливки выбрать текстуру «Циновка». Изменить заливку стенок диаграммы. Подписи по оси категорий выполнить курсивом. Изменить шрифт и обрамление заголовка.

8. Сохранить файл под именем Фамилия_№ задания.xlsx.

 

Часть 2. Анализ и управление данными. Работа с массивами

Подбор параметра

9. Используя инструмент Подбор параметра, рассчитать, какова должна быть стоимость продаж одежды ЗАО «Норма», чтобы прибыль этой фирмы составила 10 тыс. руб.

Таблица подстановки

10. При помощи таблицы подстановки данных проанализировать зависимость прибыли ООО “Стиль” от изменения процента НДС при продаже продовольственных товаров. Исходные значения выделить цветом.

 

11. Сохранить файл на жестком диске и на дискете под именем Фамилия_№ задания_2.xlsx.