Форма отчетности.

Задания и рекомендации по выполнению практических работ

Задание 1. Построение простых таблиц

Цепь работы: Научиться вводить данные в таблицу, применять функции автозаполнения и автосуммирования.

Используя встроенные возможности Excel, составить таблицу-справку по объемам реализации канцелярских товаров магазином «Все для знаний» в рублях за первый и второй кварталы 2016 года с помесячной детализацией.

1. Найдите на рабочем столе экрана панель Microsoft Office и на ней кнопку Microsoft Excel, либо воспользуйтесь кнопкой «Пуск», расположенной в левом нижнем углу экрана, и командой «Программы».

2. Информацию о номенклатуре товаров расположите по строкам, предусмотрительно оставив место для шапки таблицы по приведенной ниже схеме:

Магазин "Всё для знаний"
	Объём продаж по месяцам
Наименование	Январь	Февраль	Март	Апрель	Май	Июнь	Всего	Максимум
1. Набор ручек
2. Набор карандашей
3. Общая тетрадь
4. Набор линеек
5. Стёрка
6. Точилка
7. Стиплер
8. Дырокол
9. Клей
10. Фломастеры
11. Набор дискет
12. Папка для
дипломного проекта
ИТОГО

Измените пункты с 1 по 12, задав собственные товары и суммы.

3.Подгоните ширину получившегося столбца под самое длинное наименование номенклатуры (с помощью мыши в области обозначения столбцов). Для этого переведите указатель мыши в область обозначений столбцов и установите его на границе между первым и вторым столбцами. Указатель мыши примет вид стрелки, направленной в обе стороны, с вертикальной чертой посередине. Нажмите левую кнопку мыши и, не отпуская ее, перетащите границу столбца так, чтобы самое широкое имя строки полностью поместилось в первом столбце. Теперь кнопку мыши можно отпустить.

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

5.Добавьте к уже имеющейся части таблицы столбцы «Всего» и «Максимум». Для расчета и заполнения соответственно используйте функцию автосуммирования (кнопка Σ) и функцию определения максимального значения диапазона (кнопка ƒH). Последняя функция относится к категории «статистические». Указанные операции выполните для первой значимой строки таблицы.

6.Выделите ячейки с полученными значениями суммы и максимума. Используйте функцию автозаполнения (маркер в правом нижнем углу) для расчета данных значений для оставшейся части номенклатуры товаров.

7.Аналогичным образом заполните строку «Итого» таблицы.

8.Заголовок таблицы разместите по центру. Для этого выделите весь диапазон ячеек первой строки таблицы и нажмите кнопку «Объединить и поместить в центре». Если соответствующая кнопка в панели отсутствует, то можно воспользоваться пунктом меню Формат->Ячейки…. В появившемся окне необходимо выбрать вкладку Выравнивание. На данной вкладке необходимо отметить пункт Объединение ячеек.

9.Оформите таблицу так, чтобы доказать, что вы умеете менять цвет текста и фона; цвет, толщину и тип линий (внутренних и внешних границ таблицы). Для этого используйте команду «Ячейки» в меню «Формат».

10. Сохраните книгу под своим именем.

Задание 2. Построение таблицы «Бюджет»

Цель работы: Научиться вводить данные в таблицу, используя адреса ячеек и операторы, научиться форматировать таблицу.

Используя встроенные возможности Excel, составить таблицу-справку по объемам доходов и расходов личного бюджета в первом полугодии 2015 года с помесячной детализацией.

1.Запустите программу Microsoft Excel.

2.Наберите данные таблицы по приведенной ниже схеме, применяя изменение ширины столбцов и функцию автозаполнения. В ячейках строк «Доход», «Расходы» и «Выплаты» примените функцию автосуммирования с помощью кнопки Σ панели инструментов «Стандартная». Выделение необходимых диапазонов ячеек выполняйте протаскиванием по ним указателя мыши.

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

4. В ячейках столбца «В среднем» необходимо разместить функцию расчета среднего значения (СРЗНАЧ), указав выделением диапазон ячеек, содержащих данные за все месяцы этой строки.

5. Выделите все ячейки, начиная с ячейки «Наименование». Для выделенного диапазона примените команду «Ячейки» меню «Формат» и на вкладке «Границы» выберите тип линии, ее цвет и толщину отдельно для внешних границ и линий внутри.

6. Выделите первую ячейку с заголовком «БЮДЖЕТ» и все ячейки, расположенные правее нее над столбцами заполненной таблицы. Для этого диапазона нажмите кнопку «Объединить и поместить в центре». Не снимая выделения, измените цвет и размер шрифта кнопками на панели инструментов «Форматирование».

7. Сохраните документ под своим именем.

БЮДЖЕТ

НАИМЕНОВАНИЕ

Январь

Февраль

Март

Апрель

Май

Июнь

Всего

В сред­нем

Доход

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

- основная работа

- почасовые

- др. источники

Расходы

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

- коммунальные платежи

- транспортные расходы

-расходы на питание

-

Выплаты

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

- выплаты по ипотечным ссудам

Не могут превышать 30% от дохода

- подоходный налог

Рассчитывается для каждого месяца как произведение 0,13 на разность значений дохода и выплат ипотечных долгов

- профсоюзный сбор

Остаток

Поместите здесь для каждого месяца разность значений дохода и суммы расходов и выплат

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

9. В ячейках столбца «В среднем» необходимо разместить функцию расчета среднего значения (СРЗНАЧ), указав выделением диапазон ячеек, содержащих данные за все месяцы этой строки.

10. Выделите все ячейки, начиная с ячейки «Наименование». Для выделенного диапазона примените команду «Ячейки» меню «Формат» и на вкладке «Границы» выберите тип линия, ее цвет и толщину отдельно для внешних границ и линий внутри.

11. Выделите первую ячейку с заголовком «БЮДЖЕТ» и все ячейки, расположенные правее нее над столбцами заполненной таблицы. Для этого диапазона нажмите кнопку "Объединить и поместить в центре". Не снимая выделения, измените цвет и размер шрифта кнопками на панели инструментов «Форматирование».

12. Сохраните документ под своим именем.

Задание 3. Составление формул

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

Используя встроенные возможности Excel, рассчитать чистую прибыль предприятия от продажи продукции в первом полугодие 2016 г. отдельно по каждому месяцу. Определить итоговую рентабельность производства.

1. В строках таблицы расположите следующие параметры расчета:

o выручка от реализации с НДС (т.е. цена, которую платит покупатель);

o НДС (18%); {формула}

o выручка от реализации без НДС, {формула}

o материальные затраты;

o амортизация;

o фонд оплаты труда (ФОТ);

o отчисления на ФОТ (30% от ФОТ); { формула }

o рента;

o лизинг.

o выплаты процентов;

o себестоимость; { формула }

o валовая прибыль; { формула }

o налог на прибыль (20%); { формула }

o чистая прибыль; { формула}

2. Помесячно введите исходные значения и соответствующие формулы:

НДС =18*{Цена}/120

В/р _{без НДС}={Цена}-{НДС} либо = 100*{Цена}/120

Отчисления=0,3*{ФОТ}

С/ст={МЗ}+{А}+1,3*{ФОТ}+{Л}+{Р}+{выплаты %}

Вал.П={В/р _{без НДС}}-{С/ст}

НП=0,2*{Вал.П}

ЧистаяП ={Вал.П}-{НП}

3. Отформатируйте числовые значения таблицы категорией «Денежный формат». Если ячейки заполняются символами «#####», выделите ячейки этого столбца таблицы и выполните команду «Автоподбор ширины», которая находится в меню следующего уровня, открываемом командой «Столбцы» меню «Формат».

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

5. Отформатируйте полученное значение соответствующей категорией (%).

6. С помощью меню «Файл» установите колонтитулы, в которые занесите свою фамилию, а также текущую дату и время. Для этого выберите команду «Параметры страницы» и вкладку «Колонтитулы». Затем с помощью кнопки «Создать верхний колонтитул» по центру страницы расположите свою фамилию. Используя кнопки «Создать нижний колонтитул», «Часы» и «Календарь», в нижнем левом углу страницы расположите текущую дату, а в правом — текущее время.

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

8. Скопируйте таблицу на другой лист рабочей книги и откорректируйте часть исходных данных так, чтобы снизить рентабельность.

9. Рассчитайте темп прироста рентабельности, который в данном примере должен оказаться отрицательным =(Лист2!{R}-Лист1!{R})/Лист2!{R}

10.Сохраните книгу под своим именем.

Задание 4. Построение и редактирование диаграмм.

Цель работы: Научиться строить диаграммы по выделенным в таблице данным, а также редактировать построенные диаграммы.

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

1. Постройте круговые диаграммы структуры себестоимости для обоих примеров.

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

3. Постройте сглаженные кривые динамики рентабельности производства во втором и третьем кварталах.

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

Задание 5. Построение прогноза, решение оптимизационных и поисковых задач.

Цель работы: Научиться решать оптимизационные задачи с помощью дополнительного средства Excel Solver, а также составлять прогнозы.

1. Составьте прогноз на 1 период вперед для следующего ряда данных:-0,7 0,6 1,9 3,2 4,5 5,8 7,1 8,4 9,7 1,1

Для этого необходимо в диаграмму ряда добавить линию тренда (пункт меню «Диаграмма» команда «Добавить линию тренда»), затем в контекстном меню линии тренда пометить пункт «Показывать уравнение на диаграмме».

Тренд - линия, показывающая общую направленность изменения данных в предыдущие периоды времени и позволяющая предсказать характер изменения в ближайшем будущем.

Составьте прогноз также с помощью встроенных функций Excel (ЛИНЕЙН ТЕНДЕНЦИЯ РОСТ ЛГРФПРИБЛ).

2. Составьте прогноз на ноябрь для следующего ряда данных:.

Январь февраль март апрель май июнь июль август сентябрь октябрь
34 23 12 14 18 20 22 25 29 35

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

- прогнозное значение для каждого типа тренда;

- разность между прогнозным значением и реальным (39 тыс. руб.).

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

3. Составьте прогноз рентабельности и выручки от реализации с НДС для примера, представленного в задании №3.

4. Найдите максимум функции y=10cosx(0,5x² +3x-4) на интервале 0≤ х ≤ 50. Для этого необходимо использовать модуль «Поиск решения» (Solver) в меню «Сервис». В качестве целевой ячейки выбираем ту ячейку, которая содержит формулу искомой функции, а в качестве изменяющейся ячейки — ячейку с переменной (в начале расчета х = 0). Интервальное ограничение придется задавать, разбив на две части: 0≤x и x≤50. В результате целевая ячейка будет содержать искомое максимальное значение функции, а изменяющаяся— искомый аргумент.

5. Решить систему уравнений:

5x-2y+2z=2

3x+2y-1z+0

6. Приближенно решить систему уравнений:

2x³-4y+2z²=3

x²-2y²-0,5z=0

x+y²-0,1z³=1

7. Дан бак в виде конуса высотой h и радиусом основания г. Найти, при каких г и h бак будет иметь максимальную (например, для нагревания в нем воды с помощью солнца) и минимальную (для снижения расходов на материал и покраску) площадь поверхности при объеме, равном 1000 м³. Объем конуса: V = πr²h/3. Площадь полной поверхности конуса (боковой и основания): . Задача имеет решение при условии задания стартовых позиций высоты и радиуса, равных единице, а не нулю. То есть, конус не должен быть сверхвытянутым или почти сплющенным.

Задание 6. Работа со списками.

Цель работы: Научиться строить таблицы-списки, выполнять их сортировку и фильтрацию, а также подводить промежуточные итоги.

Подведите промежуточные итоги работы маршрутных такси №1 и №23 за первую декаду июня 2016 г.

1. Постройте таблицу, аналогичную приведенной ниже:

Компания	Водитель	Маршрут	Количество рейсов	Выручка
Приоритет	Петров
Приоритет	Сидоров
Таксо	Кузнецов
Таксо	Степанов
Таксо	Чернышев
Приоритет	Горшков
Приоритет	Онищенко
Таксо	Ярцев
Таксо	Петров
Таксо	Петряев
Таксо	Семенов
Приоритет	Иванов
Приоритет	Елкин
Таксо	Никонов

2. Выполните сортировку по компаниям, фамилиям водителей и номерам маршрутов.

3. Подведите промежуточные итоги работы компаний, оформив их следующим образом:

«Приоритет»:

- выручка на хххххруб.

маршруте №1

- выручка на ххххх руб.

маршруте №23

ИТОГО: хххххруб.

«Таксо»:

- выручка на хххххруб.

маршруте №1

- выручка на хххххруб.

маршруте №23

ИТОГО: ххххх руб.

4. Сохраните книгу под своим именем.

Задание 7. Анализ данных и подведения итогов.

Цель работы: Научиться осуществлять фильтрацию записей и подводить промежуточные итоги.

1.Постройте таблицу, аналогичную приведенной ниже:

Реализация товаров за II квартал 2016 г.
Месяц	Дата	Наименование товара	Цена, руб.	Продано, шт.	Выручка
май		Тетрадь 48л.
май		Тетрадь 18л.
май		Скрепки мет.
май		Ручка шарик.
май		Ручка капил.
апрель		Тетрадь 48л.
апрель		Тетрадь 18л.
апрель		Скрепки мет.
апрель		Скрепки пл.
апрель		Карандаш
апрель		Ручка шар.
апрель		Ручка капил.
июнь		Тетрадь 48л.
июнь		Скрепки мет.

2. В окне команды установите сортировку по трем столбцам, выбрав для первого «Месяц», для второго — «Дата», для третьего — «Наименование товара». Установите разный порядок сортировки (по возрастанию или по убыванию).

3. Выделите команду «Фильтр» меню «Данные» и в меню следующего уровня выберите команду «Автофильтр». Эта команда включает режим фильтрации. После этого в заголовках столбцов появятся кнопки, открывающие списки условий выборки.

4.Нажмите такую кнопку в заголовке первого столбца и установите условие (например: апрель). Обратите внимание, что в списке остались только строки, которые удовлетворяют Вашему условию, но порядок строк не изменился.

5. Повторите предыдущие действия, устанавливая разные условия выборки.

6. Снимите режим фильтрации, повторив выполнение п.З.

7. Оставив активную ячейку внутри списка, выберите команду «Итоги» меню «Данные». В окне этой команды выберите функцию итогов (например, сумма). Обратите внимание, что для Вашего примера уже установлено «подводить итоги по месяцам».

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

9. Обратите внимание на появление строк с итоговыми данными и изображение структуры слева от таблицы.

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

11.Отмените последние действия с помощью кнопки «Убрать все» команды «Итоги».

12.Сохраните построенный список под своим именем.

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

В отчет включается:

- номер и название задания;

- цель задания;

- листы Excel содержащие результаты выполнения задания.

Министерство образования и науки

ГБОУВО «Тверской Государственный Технический Университет»

Кафедра «Информационные системы»

Дата добавления: 2017-02-01