Лабораторная работа № 12

 

Наименование: Обработка экономической информации средствами электронных таблиц

1. Цель: Научиться обрабатывать экономическую информацию в электронных таблицах. Сформировать компетенции ОК 2, ОК 4; овладеть знаниями и умениями для освоения ПК 1.6, ПК 3.1, ПК 3.2, ПК 3.4 (спец. ПКС), ПК 1.5, ПК 1.7 (спец. ИС)

 

2. Подготовка к занятию:

2.1 Повторить, для чего предназначены фильтры в ЭТ, в каких случаях они применяются;

2.2 Повторить основные элементы интерфейса редактора Excel

3. Литература:

3.1 А.П.Алексеев Информатика 2015: учебное пособие – М.:СОЛОН-ПРЕСС, 2015

3.2 Е.А. Шомас Информационные технологии, учебное пособие, КС ПГУТИ, 2016

 

4 Перечень оборудования:

4.1 ПЭВМ, подключенные к сети Интернет;

4.2 Электронные таблицы Excel.

 

5. Задание:

5.1 Создайте таблицу по образцу и выполните необходимые расчеты:

- вычислите Стоимость по каждому наименованию затрат, задав формулу для наименования "Стол", с последующим копированием этой формулы для других наименований.

- вычислите Общее количество затрат в столбце Стоимость, используя кнопку Автосумма.

- для каждого наименования затрат вычислите долю затрат (в %) от общего количества затрат.

Внимание! При вычислении долей (в %) используйте формулу, содержащую в качестве делителя абсолютный адрес ячейки с числом, обозначающим суммарное количество затрат (например, = E2/$9 ).

- переведите формат данных, получившихся в ячейках F2:F8 в процентный, используя кнопку на панели форматирования Процентный формат.

- с помощью команды Формат - Строка - Скрыть скройте (временно удалите из таблицы) последнюю строку.

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

- в исходном экземпляре таблицы выделите две строки, находящиеся выше и ниже удаленных строк, и восстановите скрытые строки с помощью команды Формат–Строка–Отобразить.

- отформатируйте исходный экземпляр таблицы, используя команды Формат ячейки:
шрифт – Courier New Cyr;
начертание – полужирное;
размер – 14 пт;
выравнивание – По центру.

- отформатируем заголовок таблицы, предварительно выделив его. Формат ячейки – Выравнивание – По горизонтали: по центру – По вертикали: по центру – Отображение – Переносить по словам - Ок

- отформатируйте второй экземпляр таблицы с помощью команд меню Главная -   Формат– Автоформат, выбрав одно из стандартных оформлений.

5.2 Постройте таблицу по следующему образцу:

- построение таблицы начните с ввода наименования колонок: Номер, Фамилия, Зарплата, Премия и т. д. в строку 7.

- введите в ячейку A8 значение: 1. Для заполнения ячеек А9:А13 используйте Автозаполнение.

- введите в ячейки В8:В13 фамилии сотрудников.

- запишите в ячейки С8:С13 размер зарплаты, указанный в таблице:

 

 

- введите в ячейку D8 размер премии: 100. Для заполнения ячеек D9:D13 используйте Автозаполнение. В контекстном меню выберите пункт Прогрессия, в окне диалога установите параметр Геометрическая с шагом 1,15.

- введите в ячейку Е8формулу для расчета начислений по районному коэффициенту: =15%*(С8+D8), т. е. 15%*(Зарплата + Премия). Для ячеек Е9:Е13 копируйте формулу.

- введите в ячейку F8 размер подоходного налога по формуле: =20%*(С8+D8)+14%*Е8, т. е. 20%*(Зарплата + Премия) + 14%*(Уральские). Скопируйте формулу для ячеек F9:F13.

- введите в ячейку G8 размер дохода по формуле: =С8+D8+Е8-F8, т. е. Зарплата + Премия + Уральские – Налог. Скопируйте формулу для ячеек G9:G13.

- для заполнения строки Суммарное значение выделите диапазон значений со строкой Суммарное значение: С8:G14 и на панели инструментов нажмите кнопку Автосуммирования

- Рассчитайте в ячейке С15 Среднее значение по колонке Зарплата, в ячейку С16- Наибольшее значение, С18  - Наименьшее значение.

- выделите диапазонс числами (С8:G17) и установите денежный формат нажав кнопку Денежный стиль

Ваша таблица будет иметь следующий вид:

 

- На Листе 2 оформите таблицу, используя команду Формат, Автоформат. Добавьте цвета и рамки, используя соответствующие кнопки.

5.3 Модификация таблиц:

- выделите таблицу (диапазон ячеек А7: G17) и скопируйте ее в буфер обмена, используя команду Правка, Копировать.

- перейдите на третийлист, щелкнув по вкладке Лист 3.

- щелкните по ячейке АЗ (Лист 3) и вставьте таблицу командой Правка, Вставить.

- выделите таблицу(диапазон ячеек А3:G13, Лист 3) и скопируйте ее в буфер обмена, используя команду Правка, Копировать.

- Выделите ячейку А17 и выполните команду Правка, Специальная вставка, установите параметр Транспонировать.

Ваша таблица примет следующий вид:

 

 

- выделите таблицу без строк Суммарное, Среднее, Наиб. значение, Наим. значение (диапазон ячеек АЗ:G9, Лист 3) и скопируйте в буфер обмена, используя команду Правка, Копировать. Затем выделите ячейку А25, выполните команду Правка, Специальная вставка и установите параметр Значения. В новой таблице содержатся только числовые данные, т. е. пересчет по формулам невозможен.

- полученную таблицу сортируйте по алфавиту, выделив какую-либо ячейку в колонке Фамилия. Используйте команду Данные, Сортировка. Для быстрой сортировки данных в таблице по одной колонке можно использовать кнопку Сортировка по возрастанию, выделив предварительно ячейку в колонке с данными. Excel самостоятельно распознает область таблицы при выделении одной из ячеек. Выделение части таблицы приводит к сортировке данных только в выделенной области по первой колонке в выделенном диапазоне.

- создайте еще одну копию таблицы с началом в ячейке J25, Лист 3 и сортируйте данные по убыванию дохода, используя кнопку Сортировка по убыванию

- установите Автофильтр, используя команду Данные, Фильтр, Автофильтр. Раскройте список в колонке Доход и выберите в нем пункт Условие. Заполните данные фильтра: 1300<ДОХОД <1400.

 

Таблица примет вид:

 

 

- на втором листе выделите таблицу (диапазон А7:G15, Лист 2) и скопируйте в буфер, используя команду Правка, Копировать. Затем установите указатель в ячейку А20, Лист 2 и выполните команду Правка, Специальная вставка с параметром Значения.

- измените полученную таблицу, сохранив колонки Фамилия, Доход. Для этого выделите диапазон ячеек С20:F28 и выполните команду Правка, Удалить с параметром Ячейки со сдвигом влево.

 

 

- выделите диапазон ячеек с суммарными значениями (А27:С27) и нажмите клавишу Del для удаления данных.

- добавьте колонку Среднее значение, используя приемы перетаскивания данных в ячейках. Для этого выделите ячейку А28, установите указатель (в виде стрелки) на нижнюю границу ячейки и, удерживая нажатой левую кнопку мыши, перетащите данные в ячейку D20. Аналогично перетащите данные из ячейки С28 в ячейку D21.

- заполните данные в колонке Среднее значение копированием.

Вы получите таблицу вида:

 

- для каждой ячейки можно набрать текст примечания. Выделите ячейку и выполните команду Вставка, Примечание. Напишите Имя и Отчество в ячейке с фамилиями. Ячейки с примечанием отмечаются в правом верхнем углу треугольным значком. Установите указатель на ячейку — в рамке появится текст примечания.

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

- д ля ячеек с фамилиями сотрудников добавьте Примечания.

- постройте диаграмму по данным таблицы. Для этого выделите область таблицы В20:D26 и нажмите кнопку Мастер диаграмм    на панели инструментов. На первом шаге выберите тип диаграммы на вкладке Нестандартные: График / Гистограмма.

- на втором шаге проверьте правильность задания диапазонов имен колонок к данным.

- на третьем шаге задайте заголовки и параметры оформления диаграммы, пролистав все вкладки.

 

 

- на четвёртом шагеопределите место расположения диаграммы в книге.

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

Ваша диаграмма примет вид:

6. Порядок выполнения:

6.1 Изучить предложенный материал, при подготовке к лабораторной работе;

6.2 Выполнить задания лабораторной работы, пользуясь пунктом Приложение.

 

7. Содержание отчета:

7.1 Наименование и цель работы

7.2 Выполненное задание

7.3 Ответы на контрольные вопросы

7.4 Перенесите полученные расчеты в отчет

8. Контрольные вопросы:

8.1 Для чего применяется Автофильтр?

8.2 Какие операции можно производить через команду Формат ячеек?

8.3 Перечислите основные числовые форматы.

8.4 Перечислите и охарактеризуйте элементы искусственного интеллекта, присущие электронным таблицам.

 

ПРИЛОЖЕНИЕ:

Диаграммы являются средством наглядного представления данных и облегчают выполнение сравнений, выявление закономерностей и тенденций данных. Диаграммы создают на основе данных, расположенных на рабочих листах. Как правило, используются данные одного листа. Это могут быть данные диапазонов как смежных, так и не смежных ячеек. Несмежные ячейки должны образовывать прямоугольник. При необходимости, в процессе или после создания диаграммы, в нее можно добавить данные, расположенные на других листах.

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

Диаграмма постоянно связана с данными, на основе которых она создана, и обновляется автоматически при изменении исходных данных. Более того, изменение положения или размера элементов данных на диаграмме может привести к изменению данных на листе.