Справочно-методический материал

МЕТОДИЧЕСКИЕ УКАЗАНИЯ К ВЫПОЛНЕНИЮ ЛАБОРАТОРНОЙ

РАБОТЫ № 2

«РАБОТА В ПРИЛОЖЕНИИ MS EXCEL 2007 – 2013»

 

 

Составил: старший преподаватель каф. ИСЭ С.Ю. Фетисова

 

 

Барнаул, 2014г.

Содержание

Введение. 3

Справочно-методический материал. 3

Порядок выполнения работы.. 8

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

Список терминов. 9

 

Введение

Цель работы – научиться профессионально проводить расчеты и анализировать информацию в приложении MS Excel.

 

Задачи работы:

- ознакомиться с функциями и меню приложения;

- овладеть навыками ввода и корректировки данных в таблицу;

- научиться форматировать данные и ячейки;

- изучить функции для выполнения линейных расчетов;

- изучить функции для выполнения разветвленных расчетов;

- научиться отображать табличную информацию графическим способом;

- научится использовать условное форматирование для анализа данных в таблице;

- научиться защищать данные средствами приложения и отображать их в удобном виде;

- научиться связывать документы, выполненные в разных приложениях MS Office.

 

Оборудование: персональный компьютер, подключенный к локальной компьютерной сети и сети Internet, приложение MS Excel.

 

Справочно-методический материал

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

 

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

Например, «=B1*(C1+D1)/100» или «=A1+B1+C1+D1». Однако последнюю формулу – получение суммы чисел, записанных в ячейках A1 – D1, можно записать более рационально с помощью специального сервиса приложения - ФУНКЦИИ. Например, «=СУММ(А1:D1)», где «СУММ» - это название функции «сумма» в приложении, а запись в скобках (параметр функции) «A1:D1» определяет диапазон ячеек, подлежащих суммированию (A1, В1, C1, D1).

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

 

2 При проведении расчетов в формулах MS Excel используются различные типы адресных ссылок на ячейки таблицы. Существует три типа ссылок. Во-первых, относительные, когда адрес ячейки записывается привычным для нас образом: A2, G34, F11 и т.п. Во-вторых, абсолютные, когда адрес ячейки записывается особым образом: $A$2, $G$34, $F$11. Наконец, смешанные ссылки: $A2, G$34, $F11.

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

Пример 1

A	B	C	D
Расчет заработной платы
Ф.И.О.	Часы работы	Ставка за час	ИТОГО
Иванов П.		5,40р.	=СУММ(B3:C3)
Сидоров И.		5,20р.	=СУММ(B4:C4)
Николаев А.		4,80р.	=СУММ(B5:C5)
Петров В.		5,60р.	47,60р.
Дроздов Е.		5,00р.	46,00р.
Никонов М.		5,95р.	45,95р.
Торчков Л.		5,50р.	50,50р.
Дикуль В.		5,20р.	49,20р.
Васильев Ф.		5,10р.	45,10р.
ИТОГО			284,35р.

Абсолютные ссылки используются при проведении расчетов, когда значения одной или нескольких ячеек, чьи адреса включены в формулу, должны оставаться неизменными при копировании этой формулы в другие строки (столбцы). для того, чтобы при копировании формулы исходные данные брались не из новой строки (столбца), а из ячейки с одним и тем же адресом, этот адрес можно зафиксировать. Для этого достаточно в относительном адресе ячейки, например, А2, перед именем столбца А и перед номером строки 2 напечатать знак $: $A$2.

Пример 2

A	B	C	D
Прейскурант
	Ставка НДС		18%
Код изделия	Цена	НДС	Общая стоимость (цена + НДС)
СИ340	20,45р.	=B6*$D$2	=СУММ(B6:C6)
СИ341	31,45р.	=B7*$D$2	=СУММ(B7:C7)
СИ342	14,65р.	=B8*$D$2	=СУММ(B8:C8)
СИ343	22,44р.	4,04р.	26,48р.
СИ344	25,50р.	4,59р.	30,09р.
СИ345	31,20р.	5,62р.	36,82р.
СИ346	39,50р.	7,11р.	46,61р.
СИ347	28,40р.	5,11р.	33,51р.

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

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

Пример 3

Предприятие	Стоимость основных фондов в 2010 г.	Коэффициент поправки для предприятия ААА	Коэффициент поправки для предприятия БББ	Коэффициент поправки для предприятия ВВВ	Коэффициент поправки для предприятия ГГГ
		2,5	2,2	2,5
ААА	15 000 000р.	"=$B3*C$2"
БББ	25 000 000р.		"=$B4*D$2"
ВВВ	12 000 000р.			"=$B5*E$2"
ГГГ	34 000 000р.				"=$B6*F$2"

 

3 При ведении расчетов активно используется логическая функция ЕСЛИ, которая автоматически проверяет условие и выполняет то или иное действие в зависимости от того, выполняется или не выполняется условие. Функция имеет следующий формат: ЕСЛИ(логическое выражение; значение, если «истина»; значение, если «ложь»). Логическое выражение – это условие, которое нужно проверить. Два других параметра – действия при выполнении или невыполнении условия. Например, =ЕСЛИ(А9>=(D7 – H3);B2*A9;0).

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

Разработанная для решения данной задачи система может иметь следующий вид.

A B C D E

Величайшие вершины мира
Вершина	Специальные отметки	Горная страна	Высота над уровнем моря в м	Статус вершины
Белуха		Алтай		=если(В4="*";"вулкан";" ”)
Дыхтау		Бол. Кавказ
Ичинская Сопка	*	п-ов Камчатка
Казбек		Бол. Кавказ
Ключевская Сопка	*	п-ов Камчатка
Корякская Сопка	*	п-ов Камчатка
Максимальное значение высоты над уровнем моря	=макс(d4:d19)
Минимальное значение высоты над уровнем моря	=мин(d4:d19)
Среднее значение высоты над уровнем моря	=срзнач(d4:d19)

Графа «Специальные отметки» была введена разработчиком для выделения строк, посвященных описанию вулканов, при занесении исходных данных. В графе «Статус вершины» показана разработанная формула для автоматического определения, является ли вершина вулканом. В данном конкретном случае для горы Белухи в графе «Статус вершины» напечатаются пробелы, т. к. вершина не отмечена «*» как вулкан в графе В.

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

Функцию можно напечатать с клавиатуры, а можно воспользоваться мастером функций. В 2003 офисе – это меню Вставка – Функции. В 2007 офисе – это меню Формулы – Вставка функции.

 

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

Для закрепления строк или столбцов следует выделить строку ниже или столбец правее закрепляемого. В 2003 офисе затем выбирают меню Окно - Закрепить области. В 2007 офисе выбирают меню Вид – Закрепить области, команда Закрепить области.

Если нужно одновременно закрепить «шапку» и первый столбец, достаточно установить прямоугольный курсор на ячейку, расположенную в точке пересечения строки ниже и столбца правее выделяемых, как показано на рисунке. Затем выбрать соответствующее меню.

 

5 Чтобы защитить лист книги от несанкционированного доступа, достаточно активировать лист соответствующего файла (книги) Excel, выбрать пункт меню Сервис – Защита – Защитить лист (2003) либо Рецензирование – Защитить лист (2007). В появившихся окнах можно набрать пароль доступа (можно без пароля, но опасно!), параметры работы с защищаемыми ячейками. Снимают защиту в том же меню.

Если требуется защитить несколько ячеек, то сначала выделяют те ячейки таблицы, которые НЕ нуждаются в защите, любым способом активизируют меню Формат ячейки, выбирают вкладку Защита и снимают статус «Защищаемая ячейка». После этого можно повторить процедуру защиты листа.

 

6 Если часть данных и результатов необходимо представить в графической форме, разрабатывают простые или сложные диаграммы.

Существует алгоритм построения диаграмм с помощью мастера диаграмм.

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

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

3) Запустить мастер диаграмм (меню Вставка-Диаграмма или кнопка на стандартной панели инструментов).

4) Далее следовать указаниям мастера.

5) Если на одном и том же графике необходимо показать диаграммы разных типов (сложная диаграмма), это легко организовать в режиме корректировки диаграммы.

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

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

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

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

8) При необходимости подписать числовые значения для всех элементов ряда или для одного элемента, необходимо выделить ряд, вызвать контекстное меню, выбрать меню Формат рядов данных – вкладка Подписи данных (2003) или меню Добавить подписи данных (2007). Чтобы проделать эту процедуру с одним элементом ряда, следует выделить весь ряд, а затем еще раз единожды щелкнуть левой кнопкой мышки на нужном элементе. Выделится только он один. Потом повторить действия для добавления подписи.

7 Для связывания между собой документов, выполненных в разных приложениях MS Office, используют разные способы. Наиболее простой из них – копирование фрагментов (объектов) из одного документа в другой с помощью технологии OLE.

OLE (англ. Object Linking and Embedding, произносится как oh-lay [олэй]) — технология связывания и внедрения объектов в другие документы и объекты, разработанная корпорацией Майкрософт.

В 1996 году Microsoft переименовала технологию в ActiveX.

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

Рассмотрим пример связывания документов Word и Excel, когда источник информации – электронные таблицы, а приемник – текстовый документ.

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

1) Выделить в Excel диаграмму и скопировать ее в буфер обмена информацией (например, Ctrl+C).

2) Создать новый документ Word, напечатав в нем необходимую текстовку или открыть уже готовый документ.

3) Установить курсор в позицию (строку), в которую необходимо скопировать диаграмму.

4) Зайти в меню Главная, щелкнуть по нижней части кнопки Вставка, во всплывающем выбрать строку Специальная вставка.

5) В открывшемся диалоговом окне задать параметр Связать и выбрать объект вставки (обычно он стоит в первой строке в перечне объектов) – диаграмма MS Excel.

6) Чтобы проверить связь, достаточно щелкнуть на появившейся диаграмме правой кнопкой мыши (вызов контекстного меню) и убедиться, что в меню присутствует строка!Обновить связь.

 

Порядок выполнения работы

Используя средства MS Excel 2007-2013 выполнить задания, предложенные преподавателем.

 

Содержание отчета

В качестве средства оценки знаний студентов по приложению MS Excel выполняются две контрольные работы по заданиям, предложенным преподавателем. Контрольные выполняются во время проведения аудиторных занятий в электронном виде, проверяются преподавателем и копируются в базу выполненных студентами работ. Оценка выставляется сразу.

 

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

1 Приведите примеры разных типов адресных ссылок. Объясните суть этих ссылок. В каком случае применяет каждая из них? Приведите примеры конкретных ситуаций.

2 Что такое функция автозаполнения данных? Приведите пример.

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

4 Опишите формат функции ЕСЛИ. Каков результат применения этой функции? Приведите пример ее использования.

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

6 Чем отличается процедура защиты листа от защиты диапазона ячеек?

7 Что такое диаграмма? Для чего они используются специалистами?

8 Как подписать данные диаграммы?

9 Для чего нужна вспомогательная ось?

10 Что такое тренд? Для чего он нужен? Как выбирается тип линии тренда?

11 Что такое технология OLE в MS Office?

 

Список терминов

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