Лабораторная работа 1. Создание и форматирование таблиц.

Учебное пособие

ОСНОВЫ ОБРАБОТКИ ЭКОНОМИЧЕСКОЙ ИНФОРМАЦИИ В EXCEL 2010

(задания для выполнения лабораторных работ по дисциплине «Информационные системы в экономике»)

для УГС направления подготовки 38.00.00 – Экономика и управление

Иркутск – 2016

ББК 65.050.2+32.973.76

УДК 338.2:004

Основы обработки экономической информации в Excel 2010. Учебное пособие по дисциплине «Информационные системы в экономике» для выполнения лабораторных работ для студентов направления 38.03.01 - Экономика / сост. Н.И. Федурина; ИрГСХА. – 2014. – 135 с.

Методические указания предназначены для студентов, изучающих работу табличного процессора Excel в рамках курсов «Информационные системы в экономике», «Основы обработки экономической информации», также могут использоваться студентами различных направлений и специальностей, изучающими работу в Office 2010. Пособие содержит методические материалы по работе с табличным процессором Excel пакета MS Office 2010, задания для выполнения лабораторных работ. Работа подготовлена на кафедре информатики и математического моделирования.

©Федурина Н.И.

Введение

В связи с увеличением объемов обрабатываемой информации и развитием компьютерного рынка особенно актуальным становится применение пакетов прикладных программ для обработки и анализа экономической информации. Табличный процессор Microsoft Excel 2010 является одним из программно-инструментальных средств, которое может быть применено при решении широкого класса задач финансово-экономического характера. Его используют в своей деятельности экономисты, финансисты, маркетологи, менеджеры и др., поскольку множество задач, стоящих перед ними, носят учетно-аналитический характер и требуют табличной компоновки данных с последующим их анализом, сортировкой, группировкой, подведением итогов, построением диаграмм и графиков. Целью лабораторного практикума является формирование практических навыков использования инструментальных средств табличного процессора Excel для решения экономических задач. Практикум явился результатом практических и лабораторных занятий, проводимых кафедрой информатики и математического моделирования в течение последних лет. Он состоит их 9 лабораторных работ, которые содержат как подробно разобранные примеры, так и задачи для самостоятельной работы студентов. Лабораторный практикум предназначен для студентов, обучающихся по направлениям 080100 «Экономика».

Лабораторная работа 1. Создание и форматирование таблиц.

Цель работы:

1. Знакомство с различными способами организации данных

2. Освоение навыков практической работы по созданию, редактированию и форматированию электронных таблиц

3. Выполнение простейших вычислений в таблицах Excel. Знакомство с элементарными функциями.

4. Использование абсолютной и относительной адресации ячеек в формулах.

5. Контроль правильности введенных ранее значений с помощью условного форматирования

Существует два способа организации данных на листе: таблица и список (см. лабораторную работу 8). При организации данных в виде таблицы формируются строки и столбцы с записями, для которых в ячейку на пересечении строки и столбца помещаются данные (рис.1.1).

Таблицы могут иметь весьма сложную структуру с несколькими уровнями записей

Рисунок 1.1. - Табличный способ организации данных.

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

Создание и форматирование таблицы:

Таблица обычно создается на основе имеющихся на листе данных.

1. Выделите любую ячейку в диапазоне данных.

2. Нажмите кнопку Форматировать как таблицув группе Стиливкладки Главнаяи выберите стиль оформления.

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

В результате будет создана таблица. В каждый столбец автоматически добавляется значок автофильтра. Автоматически будет отображена контекстная вкладка Работа с таблицами/Конструктор(рис.1.2.).

Рисунок 1.2. - Контекстная вкладка Работа с таблицами/Конструктор.

Функции вставки ячейки, строки, столбца или листа доступны в меню кнопки «Вставить», которая расположена в группе «Ячейки» на вкладке «Главная»

Рисунок 1.3. Меню кнопки Вставить.

Для форматирования ячеек, после их выделения, используют вкладки диалогового окна Формат ячеек, а также элементы группы Шрифтвкладки Главная, минипанель инструментов

Рисунок 1.4. - Мини-панель инструментов для форматирования.

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

Для форматирования заголовка таблицы нужно выделить все ячейки строки с заголовком по ширине таблицы и нажать кнопку Объединитьпомещенную на вкладке Главнаяв группе Выравнивание

Рисунок 1.5. - Элементы группы Выравниваниена вкладке Главная.

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

1. Выделите ячейку, в которую требуется ввести формулу.

2. Введите = (знак равенства).

3. Выделите мышью ячейку, являющуюся аргументом формулы.

4. Введите знак оператора.

5. Выделите мышью ячейку, являющуюся вторым аргументом формулы.

6. При необходимости продолжайте ввод знаков операторов и выделение ячеек.

7. Подтвердите ввод формулы в ячейку: нажмите клавишу Enterили Tabили кнопку Ввод(галочка) в строке формул.

Задание 1. Освоение приемов работы с электронными таблицами.

1. Создайте новую рабочую книгу и сохраните ее в своей папке под именем Tabl1.xlsх.

2. Введите в следующие ячейки указанный текст:

А1 – Показатели производства	В2 – Март
А2 – Квартал	С2 – Июнь
А3 – План	D2 – Сентябрь
А4 – Факт	Е2 – Декабрь
А5 – Процент выполнения	F2 – Всего
В3 – 1000 С3 – 1000	D3 – 1100 Е3 – 1100
В4 – 900 С4 – 1050	D4 – 1200 Е4 – 1000

3. Проведите редактирование данных в ячейках План и Факт – добавьте единицы измерения тыс.руб. Для этого выделите щелчком мыши ячейку А3. Содержимое ячейки появится в строке формул. Поместите мышью текстовый курсор в строку формул после слова «План» и допишите «(тыс. руб.)». Для завершения редактирования нажмите клавишу Enter. Аналогично внесите изменения в ячейку А4.

4. Подсчитайте значения в столбце Всего. Для этого выделите ячей-ку F3, на вкладке Главнаяв группе Редактированиенажмите кнопку – Автосумма (при этом диапазон ячеек В3 : Е3 выделяется мигающей пунктирной линией), нажмите клавишу Enter.

5. В строке 5 «Процент выполнения» подсчитайте отношения План/Факт. Для этого в ячейке В5 задайте частное, набрав: = В4/В3, нажмите клавишу Enter. Установите процентный формат. В ячейке появится

результат деления.

6. Скопируйте формулу из ячейки В5 в ячейки С5: F5. Для этого скопируйте формулу из ячейки В5

7. Выделите название таблицы. Для этого покажите ячейку А1, задайте размер шрифта 20, выделите диапазон ячеек А1 : F1 и нажмите

8. Выделите созданную таблицу и скопируйте ее ниже на этом же листе.

9. Отформатируйте исходый экземпляр таблицы с помощью автоформатирования. Для этого нажмите кнопку Форматировать как таблицув группе Стиливкладки Главная.Выберите стиль оформления таблицы Светлый.

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

1 способ «Использование кнопки Формат по образцу группы Буфер - обменавкладки Главная»: Выделите ячейку, оформление которой требуется копировать. Нажмите кнопку Формат по образцу.После этого кнопка Формат по образцуостанется нажатой, а справа от указателя мыши появится значок копирования формата. Выделите ячейку или диапазон ячеек, для которых устанавливается копируемый формат. Если требуется многократно применять оформление выбранной ячейки, то необходимо два раза щелкнуть по кнопке Формат по образцу(щелкать надо быстро, чтобы кнопка осталась нажатой), а затем выделять разные фрагменты. Для окончания копирования формата нажмите клавишу Escили кнопку Формат по образцу, чтобы она пришла в обычное состояние.

2 способ «Использование автозаполнения»: Копирование автозаполнением используют при копировании оформления одной ячейки на рядом расположенные ячейки.

· Выделите ячейку с копируемым оформлением.

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

· Щелкните по кнопке Параметры автозаполненияв правом нижнем углу области заполнения и выберите команду Заполнить только форматы.

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

· Ячейку с копируемым форматом скопируйте в буфер обмена.

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

· Извлеките скопированную ячейку из буфера обмена. Первоначально все ячейки заполнятся не только форматом, но и содержимым копируемой ячейки.

· Щелкните по кнопке Параметры вставкив правом нижнем углу области вставки и выберите команду Только форматы.

4 способ «Использование возможностей специальной вставки».Копирование форматов с использованием специальной вставки чаще всего используют при копировании оформления на ячейки других листов или книг. Этот способ несколько более трудоемок, чем с использованием буфера обмена и обычной вставки. С другой стороны, он более надежен, так как снижает риск вставки не только формата, но и данных из копируемой ячейки.

· Ячейку с копируемым форматом скопируйте в буфер обмена.

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

· Щелкните по стрелке кнопки Вставитьв группе Буфер обменавкладки Главнаяи выберите команду Специальная вставка или щелкните по выделенной области правой кнопкой мыши и в контекстном меню выберите команду Специальная вставка.

· В диалоговом окне Специальная вставкаустановите переключатель форматы. Нажмите кнопку ОК.

Удаление форматирования.

Можно удалить сразу все параметры оформления (числовые форматы, параметры выравнивания, параметры шрифта, заливки, границы и т. д.).

· Выделите ячейку или диапазон ячеек, для которых удаляется оформление.

· В группе Редактированиевкладки Главнаящелкните по кнопке Очиститьи выберите команду Очистить форматы.

Рисунок 1.6. - Элементы группы Редактированиевкладки Главная.

11. Сохраните результаты работы в своей папке.

Задание 2. Вставка и редактирование формул.

1. Создайте новую таблицу «Оборотная ведомость за ноябрь 2013 г.»

2.

2. Предполагая, что в столбцах D, E, F и G могут быть заполнены все ячейки, заполните ячейки столбцов H и I формулами вида: для столбца H: =В+Е– G, для столбца I : =С+ D*Е– F*G. Введенную в первую ячейку столбца формулу, скопируйте в остальные ячейки столбца.

3. Получите в ячейках С8 и I8 суммы по столбцам. 4. Получите в столбце J процентные соотношения остатков к общей сумме. Выделите ячейку J3 и введите в нее формулу = I3/ I8, нажмите клавишу Enter, щелкните снова по ячейке J3, а затем по кнопке %. Для того чтобы правильно скопировать введенную формулу в остальные ячейки столбца J, ее нужно отредактировать: заменить относительный адрес ячейки I8, абсолютным адресом = I3/ $I$8.Замечание. С помощью символа абсолютной адресации $ можно варьировать способ адресации ячеек. Например, $А8 означает, что при переносе формулы будет меняться только адресация строки, а при обозначении А$8 – только адресация столбца.

Отредактировать уже введенную формулуможно одним из следующих способов:

· дважды щелкните мышью по ячейке, чтобы непосредственно в ней начать редактирование;

· выделите ячейку, нажмите клавишу F2 и редактируйте непосредственно в ячейке;

· выделите ячейку и редактируйте ее содержимое в строке ввода.

· 5. Отформатируйте данные на листе как таблицу. Выберите стиль оформления таблицы Средний 9. 6. Сохраните таблицу в своей папке под именем Tabl2.xlsx.

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

1. Выделите ячейку или диапазон ячеек.

2. Щелкните по кнопке Условное форматированиегруппы Стиливкладки Главная, в галерее выберите команду Правила выделения ячеек, а затем в подчиненном меню выберите условие (рис. 1.7.)

Рисунок 1.7. - Выбор правила выделения значений.

3. Настройте параметры условия и выберите способ выделения.

Название и содержание окна настройки параметров условия зависит от выбранного условия. Например, при выборе условия Между можно указать минимальное и максимальное значения, а при выборе условия Дата можно выбрать отношение выделяемых ячеек к сегодняшней дате (Вчера, Сегодня, Завтра, За последние 7 дней и т.д.), а также выбрать способ выделения. С помощью условного форматирования можно выделить крайние (максимальные или минимальные) значения:

1. Выделите ячейку или диапазон ячеек.

2. Щелкните по кнопке Условное форматированиегруппы Стиливкладки Главная, в галерее выберите команду Правила отбора первых и последних значений, а затем в подчиненном меню выберите принцип отбора.

3. Настройте параметры отбора и выберите способ выделения.