Лаб 21

Задание для лабораторной работы №6 «Графики».

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

Найти на компьютере файл, содержащий в названии «лаб 21 эи III». Открыть его только для чтения (пароль для открытия «III»).

Выполнить следующие настройки:

В параметрах Excel (кнопка «Office») на закладке «формулы» выключить стиль ссылок R1C1.

На закладке «дополнительно» (см.2.1) в разделе параметры для следующей книги «…» активировать все элементы.

На закладке «Вид» строки меню включить отображение сетки, строки формул и заголовков.

Переместить/Скопировать (создать копию) лист «Данные» в новую книгу. Исходный файл закрыть без сохранения. Новый файл сохранить с новым паролем на открытие (пароль ввести самостоятельно, и, запомнить; его необходимо будет использовать в будущем) под именем «ВашаФамилия лаб 21» в директорию своей группы.

В своем файле провести операции форматирования данных:

Столбец «А» (№ пп) пронумеровать (479 записей).

Столбец «В» (Дата операции) преобразовать в формат «дата» ( вид ДД.ММ.ГГГГ).

В столбце «Е» (Сумма) все точки заменить на запятые, затем преобразовать формат ячеек этого столбца в числовой с разделителем групп разрядов и числом десятичных знаков 2.

Установить ширину всех столбцов таким образом, что бы весь текст во всех строках был виден полностью и без переносов. Установить стандартные границы таблицы.

Отсортировать данные листа по столбцу «Дата операции» от старых к новым.

Скопировать строки за 2008 год и вставить их на новый лист в этой книге, который назвать «2008». Выполнить аналогичные действия с данными 2009, 2010 и 2011 годов.

На каждом листе, созданном в задании № 6 отсортировать данные по столбцу «статья затрат». Для столбца «Сумма» создать правило заливки ячейки красным цветом, если оно превышает значение 50 000.

На новом листе, который назвать «итоги» создать таблицу. Значения в соответствующие ячейки (кроме итогов, которые рассчитать стандартным образом) вставить, применяя описанные ниже способы (опробуйте их все хотя бы по 2-3 раза):

Таблица № 1 «Сумма затрат»

Статьи затрат

2008

2009

2010

2011

Итого

Амортизация

 

 

 

 

 

Материальные

 

 

 

 

 

Оплата труда

 

 

 

 

 

Отчисления с ЗП

 

 

 

 

 

Прочие

 

 

 

 

 

Итого

 

 

 

 

 

Способ А. На листе определенного года отфильтровать значения по столбцу «статья затрат» в соответствии с нужной статьёй, ниже получить формулу суммы промежуточных итогов, скопировать полученный результат и вставить в итоговую таблицу, как специальную вставку (контекстная клавиша или правая кнопка мыши), выбрав параметр вставки – «значения».

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

Способ В. На листе «итоги» в соответствующей ячейке вставить формулу аналогичную описанному ниже примеру:

Пример для ячейки амортизация за 2008 год: =СУММЕСЛИ(2008!D:D;»Амортизация»;2008!E:E)

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

Рядом с таблицей построить гистограммы, отражающие величину затрат по годам и круговые диаграммы отражающие состав затрат в рамках одного года, а так же величину затрат за все 4 года и состав затрат за 4 года. (В зависимости от вашего понимания задачи и навыков использования гистограмм и круговых диаграмм, их количество может варьироваться). Постройте так же вторичную круговую диаграмму (например, за 2008 и 2009 годы), и вторичную гистограмму (например за 2010 и 2011 годы) выбрав в качестве вторичного показателя значения «содержание операции» по статье затрат «прочие», разместите их и необходимые для этого таблицы на отдельном листе с названием «вторичные»

Далее необходимо на диаграммах типа «график» отразить изменения статей затрат по времени в течении года, добавив так же, для каждого из них линейную линию тренда, изменив её стандартную толщину и цвет для удобства восприятия. Построить четыре графика (за 2008, 2009, 2010 и 2011 годы соответственно) разместив их каждый на отдельном листе. Для построения графиков используйте свои навыки работы с программой MS Excel (фильтры, сортировки, построение дополнительных таблиц, копирование и вставка значений и прочие приёмы).

Пример графика:

На листе «итоги» построить таблицу № 2 «Изменение статей затрат»:

Таблица № 2 «Изменение статей затрат»

Статьи затрат

2008/2009

2009/2010

2010/2011

2008/2011

Амортизация

 

 

 

 

Материальные

 

 

 

 

Оплата труда

 

 

 

 

Отчисления с ЗП

 

 

 

 

Прочие

 

 

 

 

Итого

 

 

 

 

Данные заполнить вычитанием значений прошлого года из сравниваемого (например для столбца 2008/2009 нужно из значений 2009 года вычесть значения 2008). Значения получить из таблицы № 1). Рядом с таблицей построить диаграммы, отражающие указанные данные по аналогии с заданием № 9 (без усложненных заданий).

Используя способ А из задания №8, но применив для формулы промежуточных итогов другие номера функций, построить на листе «итоги» таблицу №3, по аналоги с таблицей № 1, но содержащей для каждого года 3 значения (максимальное, минимальное и среднее); подсчитывать итоги в этой таблице нет необходимости.

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

На отдельном листе построить линейчатую диаграмму, отражающую сравнение затрат по годам и по составу одновременно.

Пример линейчатой диаграммы:

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