Ввод и редактирование данных в MS Excel

РАБОТА С ЭЛЕКТРОННЫМИ ТАБЛИЦАМИ

В MS EXCEL 2007

 

Методические указания

к выполнению лабораторных работ

по дисциплине «Информатика»

для студентов очной формы обучения

направления 38.03.01 «Экономика»

профиль подготовки

«Бухгалтерский учет, анализ и аудит»

 

Севастополь

УДК 004.912

МЕТОДИЧЕСКИЕ УКАЗАНИЯ к выполнению лабораторных работ « РАБОТА С ЭЛЕКТРОННЫМИ ТАБЛИЦАМИ В MS EXCEL 2007» по дисциплине «Информатика» для студентов очной формы обучения по направлению 38.03.01 «Экономика» профиль подготовки «Бухгалтерский учет, анализ и аудит» / Сост. Ст. преподаватель кафедры ИТиКС М.А.Лебедева. – Севастополь: Изд-во СГУ, 2014. – 43 с.

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

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

 

 

Методические указания рассмотрены и утверждены на заседании кафедры Информационных технологий и компьютерных систем, протокол №2 от 14 октября 2015г.

 

Рецензент: С.Н.Фисун, доцент кафедры Информационных технологий и компьютерных систем

СОДЕРЖАНИЕ

 

ВВЕДЕНИЕ. 4

1 ЛАБОРАТОРНАЯ РАБОТА №1. ВВОД И РЕДАКТИРОВАНИЕ ДАННЫХ В MS EXCEL. 4

2 ЛАБОРАТОРНАЯ РАБОТА №2. ФОРМАТИРОВАНИЕ ТАБЛИЦ. ИСПОЛЬЗОВАНИЕ ТАБЛИЧНЫХ ФУНКЦИЙ.. 10

3 ЛАБОРАТОРНАЯ РАБОТА №3. ПОСТРОЕНИЕ ДИАГРАММ.. 21

4 ЛАБОРАТОРНАЯ РАБОТА №4. ОБРАБОТКА СПИСКОВ.. 27

5 ЛАБОРАТОРНАЯ РАБОТА №5. АНАЛИЗ ДАННЫХ.. 33

6 ЛАБОРАТОРНАЯ РАБОТА №6. СОЗДАНИЕ МАКРОСОВ.. 39

СПИСОК ЛИТЕРАТУРЫ.. 43

 

ВВЕДЕНИЕ

 

Программа Microsoft Office Excel 2007 обладает эффективными сред- ствами обработки числовой информации, представленной в виде электронных таблиц. Она позволяет выполнять математические, финансовые и статистические вычисления, оформлять отчеты, построенные на базе таблиц, выводить числовую информацию в виде графиков и диаграмм.

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

 

ЛАБОРАТОРНАЯ РАБОТА №1

ВВОД И РЕДАКТИРОВАНИЕ ДАННЫХ В MS EXCEL

 

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

 

Теоретические сведения

Ввод данных на рабочий лист

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

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

 

Рис.5.1 – поля Excel

При вводе числовых данных следует соблюдать следующие правила:

- при вводе отрицательных чисел нужно вводить знак – или заключать число в круглые скобки, например –4 или (4);

- для отделения групп разрядов (классов) можно вводить пропуски, например 1 000 000;

- при вводе дробных чисел нужно обязательно вводить целую часть, отделяя ее от дробной части пробелом. Например, число ½ вводится как 0 1/2 Excel преобразует число в десятичную дробь 0,5, которая отображается в поле Строки формул, а в ячейке отображаются введенные дробные числа.

- для обозначения процентов после числа вводят символ %;

- для ввода чисел в экспоненциальном виде используют латинскую букву Е (е), например 3003,4 = 3,0034·10³ = 3,0034E+3.

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

 

Ввод формул

Формула в Excel – это выражение, которое начинается со знака = и может содержать числа, текст, ссылки на ячейки, знаки действий (операторы), скобки и функции.

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

- для обозначения действия возведение в степеньиспользуется оператор ^;

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

- для изменения порядка выполнения действий используют круглые скобки;

- для обозначения действия нахождение процентовиспользуется оператор %; например, формула нахождения 25 %от числа 134 будет выглядеть так: =134∙25 %;

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

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

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

Для того чтобы при копировании ссылки в формуле не модифицировались, перед номером столбца строки добавляют символ $. Так, ссылка B10 будет модифицироваться, а ссылка $B$10 – нет. Если в ссылке символ $ добавлен только перед номером столбца или номером строки, например $B10 или B$10, то при копировании такие ссылки модифицируются частично: изменяется лишь номер строки или столбца, около которого не стоит символ $.

Ссылка, которая модифицируется при копировании формулы, называется относительной.

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

Ссылка, в которой модифицируется или номер столбца, или номер строки, называется смешанной.

Редактирование данных

Редактирование данных можно выполнять непосредственно в ячейке или в поле Строки формул. Для редактирования данных в ячейке можно дважды щелкнуть по этой ячейке или сделать ячейку текущей и нажать клавишу F2. Для редактирования данных в поле Строки формул нужно сделать ячейку текущей, выбрать необходимое место в поле Строки формул, выполнить редактирование, после чего нажать клавишу Enter.

Для удаления данных из ячейки можно сделать ее текущей и нажать клавишу Delete. Можно также выполнить Главная - Редактирование - Очистить - Очистить содержимое.

Выполнение операций копирования и перемещения данных из ячейки или диапазона ячеек электронной таблицы осуществляется: с использованием команд Копировать, Вырезать, Вставить группы Буфер обмена вкладки Главная; с использованием команд контекстного меню объектов; с использованием комбинаций клавиш; перетягиванием.

Если содержимым ячеек, которое копируется или перемещается, являются формулы, то при вставке из Буфера обмена можно вставить в выделенные ячейки не сами формулы, а вычисленные по ним значения. Для этого необходимо выполнить Главная ® Буфер обмена ® Вставить ® Вставить значения.

Можно вставить данные из Буфера обмена в выделенные ячейки таким образом, чтобы они не заменяли существующие данные, а добавлялись к ним (или вычитались от существующих значений, или умножались на существующие значения, или существующие данные делились на те, которые вставляются). Для этого нужно выполнить Главная ® Буфер обмена ® Вставить ® Специальная вставка и в окне Специальная вставка, которое откроется, в группе Операция выбрать соответствующий переключатель.

Если выполнить Главная ® Буфер обмена ® Вставить ® Транспонировать, то при вставке произойдет транспонирование данных, которые вставляются из Буфера обмена: данные из столбцов будут расположены в строках, а данные из строк – в столбцах. Ссылки в формулах при такой вставке также изменяются соответствующим образом.

Транспонирование можно также задать установкой метки флажка транспонировать в окне Специальная вставка.

Для вставки в таблицу новых столбцов (строк) необходимо выделить столбцы (строки), перед которыми нужно вставить новые, и выполнить Главная - Ячейки - Вставить - Вставить строки (столбцы) на лист. Если выделить один столбец (строку), то перед ним вставится один новый столбец (строка), а если выделить несколько столбцов (строк) подряд, то перед ними вставляется столько столбцов (строк), сколько выделено.

Для вставки в таблицу нескольких ячеек нужно:

- Выделить необходимый диапазон ячеек.

- Выполнить Главная - Ячейки -Вставить - Вставить ячейки.

- Выбрать в списке переключателей окна Добавление ячеек нужный

- Выбрать кнопку ОК.

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

Аналогично происходит и вставка новых ячеек при выборе переключателя ячейки со сдвигом вправо.

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

- Выделить необходимый диапазон ячеек с данными.

- Выполнить Главная - Буфер обмена – Копировать.

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

- Выполнить Главная - Ячейки - Вставить - Вставить скопированные ячейки.

- Выбрать необходимый переключатель из списка переключателей окна Вставка скопированных ячеек.

- Выбрать кнопку ОК.

Удаление столбцов, строк, отдельных ячеек и их диапазонов происходит аналогично вставке. Для выполнения этих операций нужно выполнить Главная - Ячейки - Удалить - Удалить ячейки (Удалить строки (столбцы) с листа). При вставке или при удалении фрагментов электронной таблицы во фрагментах таблицы, которые при этом сдвигаются, происходит модификация формул. Благодаря такой модификации все результаты вычислений, которые были найдены перед вставкой или удалением, не изменяются.

 

Задание на выполнение работы

1.3.1 Создать новую электронную книгу в среде Excel 2007. Переименовать Лист1 в «Задание 1». На этом листе произвести вычисление следующих выражений:

a) ;

b) 32% от числа ;

Записать в виде целого числа или десятичной дроби числа, представленные в экспоненциальном виде: а) 2,15Е+03; б) 1,35Е–02.

Задание выполняется на Листе2. Значение переменной x находится в ячейке А1, значение переменной у – в ячейке А2, значение переменной z – в ячейке А3. Запишите формулы для вычисления в Excel 2007 значений выражений:

a) ; b) .

1.3.2 На листе 3 заполнить: а) ячейки диапазона А2:А8 членами арифметической прогрессии с первым членом 3 и шагом 2; б) ячейки диапазона С3:С8 членами геометрической прогрессии с первым членом 2 и знаменателем 0,5.

Создать пользовательский список из названий восьми изучаемых предметов. Заполните этими названиями 8 ячеек столбца D.

1.3.4 В новой книге создать таблицу, содержащую доходы 5 фирм по продаже компьютеров (видеотехники, косметики и т.д.) за каждый месяц первого полугодия текущего года (пример на рис.1.2). Первая строка содержит названия месяцев и заполняется с помощью маркера заполнения. В ячейку А1 ввести «Фирмы».

 

Рис.1.2. Общий вид таблицы "Доходы фирм по продаже компьютеров в 1- ом полугодии текущего года"

 

Вычислить значения столбца "Всего за полгода" с помощью формулы, суммирующей значения ячеек. (=А1+.....). Размножить формулу в столбце, используя маркер заполнения.

Вычислить итоги для каждой фирмы за каждый месяц.

Выполнить редактирование данных:

- Очистите содержимое столбца "всего за полгода". Вычислить значения столбца с использованием функции =СУММ().

- Добавить три пустые строки в верхней части таблицы.

- Удалить две пустых строки.

- Заполнить пустую строку текстом "Доходы фирм по продаже компьютеров на 1 полугодие 201_года". Разместить заголовок по центру области (кнопка на панели Центрировать по столбцам).

- Скопировать таблицу на лист 2 через буфер обмена.

- На листе 2 с помощью команд из меню Правка (Копировать, вставить, Удалить) поменять местами столбцы "Мин. доход" и "Макс. доход". Проверьте правильность ссылок в формулах. Выполнить обратную операцию обмена столбцов местами с помощью перетаскивания мышью

 

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

Цель работы; постановка задачи; описание выполнения заданий раздела 1.3; Выводы по работе.

 

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

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

2. Перечислите способы выполнения команд копирования, удаления и вставки ячеек в таблице. Как добавить (удалить) строки и столбцы таблицы.

3. Каким образом в Excel формула отличается от данных. Каким образом в ячейку таблицы можно поместит текст ==A==.

4. Что происходит с формулой при ее копировании?

ЛАБОРАТОРНАЯ РАБОТА №2

Теоретические сведения

Числовые форматы

Числовые форматы задаются в окне Формат ячеек, вкладка Число, спискок Числовые форматы ( рис.2.1)

 

Рис.2.1 – Числовые форматы

 

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

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

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

Дата используется для представления числа в виде даты определенного типа

В формате Процентный данные представляются числом, которое является результатом умножения содержимого ячейки на 100, со знаком %

Текстовый используют для представления чисел в ячейках как текст

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

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

Выравнивание

По умолчанию числа в формате Текстовый выравниваются в ячейке по левому краю, во всех других форматах – по правому краю. Тексты по умолчанию выравниваются по левому краю.

Для изменения значений свойств выравнивание по горизонтали, выравнивание по вертикали, отображение, направление текста, ориентация чисел или текста в ячейках можно использовать элементы управления группы Выравнивание вкладки Главная на Ленте или вкладки Выравнивание окна Формат ячеек.

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

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

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

Указанные выше действия можно выполнить с помощью элементов управления группы Выравнивание вкладки Главная на Ленте.

 

Границы, заливка, защита

На вкладке Граница окна Формат ячеек (рис.2.1) можно установить такие значения свойств границ ячеек: наличие всех границ или только отдельных, тип и цвет линий границ.

Значения свойств границ можно также установить, используя кнопку со списком Границы (ее название и вид изменяется в зависимости от последнего установленного значения этого свойства) группы Шрифт вкладки Главная на Ленте.

Используя элементы управления вкладки Заливка окна Формат ячеек или кнопку со списком Цвет заливки группы Шрифт вкладки Главная на Ленте,можно установить цвет фона ячейки, способ заливки, узор и его цвет.

На вкладке Защита окна Формат ячеек можно установить или отменить режимы защита ячеек и скрытие формул. Защита ячеек устанавливается для того, чтобы защитить данные от несанкционированного изменения, а скрытие формул – для того, чтобы данные не отображались в Строке формул. Для установки режимов защиты и скрытия нужно поставить метки соответствующих флажков: Защищаемая ячейка и Скрыть формулы, выбрать кнопку ОК, после чего выполнить Рецензирование - Изменения – Защитить лист или Главная - Ячейки - Формат - Защитить лист. В окне Защита листа, которое откроется, можно установить пароль для снятия режимов защиты и скрытия, а также установить разрешения на определенные операции при установленных режимах.

 

Функции Excel

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

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

Вставить функцию в формулу можно несколькими способами:

- использовать список функций кнопки категории функций в группе Библиотека функций вкладки Формулы на Ленте (рис.2.2);

- выполнить Формулы - Библиотека функций - Вставить функциюили выбрать кнопку Вставить функциюСтроки формул;

- ввести функцию непосредственно в ячейку или в поле Строки формул;

Рис.2.2 – вставка функции

 

Если выполнить Формулы - Библиотека функций – Вставить функцию или выбрать кнопку Вставить функциюСтроки формул, то откроется окно Мастер функций (рис.2.3).

 

Рис.2.3 – Мастер функций

 

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

 

Математические функции

Суммирование =СУММ (числа), числа – список из не более, чем 30-ти аргументов, каждый из которых число, формула или ссылка на ячейку, содержащую числовое значение.

=СУММЕСЛИ (интервал; критерий; суммируемый интервал) - суммирует значения в ячейках, содержимое которых удовлетворяет заданному критерию. 3-й аргумент – суммируемый интервал – необязательный, используется для поиска значений в таблице. В качестве критерия могут использоваться константы, а так же операции отношения: >, <, >=, <=

Округление =ОКРУГЛ(число; десятичные знаки), число – округляемое значение; десятичные знаки – целое число определяет разряд округления. Может быть: >0 – округление происходит в дробной части числа; =0 - число округляется до целого; <0 - округление происходит в целой части числа.

Произведение =ПРОИЗВЕД(число1;число2...) перемножает все числа, задаваемые ее аргументами. Может иметь до 30 аргументов. Excel игнорирует любые пустые ячейки, текстовые и логические значения.

Остаток от деления =ОСТАТ(число; делитель) Если число точно делится на делитель, функция возвращает 0. Если делитель равен 0, функция ОСТАТ возвращает ошибочное значение.

Корень =КОРЕНЬ(число). Аргумент число должен быть положительным числом. Если число отрицательное, КОРЕНЬ возвращает ошибочное значение.

 

Статистические функции

Используются для обработки данных.

=СРЗНАЧ (числа) - вычисляет среднее арифметическое для последовательности чисел. Игнорирует пустые, логические, текстовые ячейки. Может содержать до 30-ти аргументов.

=МАКС (числа) и =МИН (числа) соответственно возвращает максимальное и минимальное значения для заданной последовательности чисел.

= МОДА (числа) – возвращает наиболее часто встречающееся значение во множестве чисел.

=СЧЕТ (интервал) и =СЧЕТЗ (интервал) вычисляет в заданном интервале количество ячеек, содержащих числа, даты, формулы. СЧЕТЗ вычисляет количество заполненных ячеек.

=СЧЕТЕСЛИ (интервал; критерий) подсчитывает количество ячеек, содержимое которых удовлетворяет критерию поиска.

 

Логические функции

=И, ИЛИ, НЕ – аргументами функций являются логические выражения; функции возвращают значения логических констант: ИСТИНА и ЛОЖЬ. Функция НЕ имеет один аргумент: =НЕ(истина) возвращает ЛОЖЬ. =НЕ(В2>5) возвращает ИСТИНА, если в ячейке В2 содержится значение, меньшее 5, иначе – ЛОЖЬ. И, ИЛИ могут содержать до 30 аргументов. Аргументами функций И, ИЛИ, НЕ могут быть логические функции. Используются для построения сложных логических выражений.

=ЕСЛИ - используется, когда, в зависимости от значения логического выражения, выполняются те или иные вычисления. =ЕСЛИ (логическое выражение; значение «истина»; значение «ложь»). Вложенные функции ЕСЛИ используются для построения сложных условий.

=ЕСЛИ(А1=100;"Всегда";ЕСЛИ(И(А1>=80;А1<100);"Обычно";ЕСЛИ(И(А1>=60;А1<80);"Иногда";"Никогда")))

Если значение в ячейке А1 является целым числом, формула читается следующим образом: "Если значение в ячейке А1 равно 100, возвратить строку "Всегда". В противном случае, если значение в ячейке А1 находится между 80 и 100, возвратить "Обычно". В противном случае, если значение в ячейке А1 находится между 60 и 80, возвратить строку "Иногда". И, если ни одно из этих условий не выполняется, возвратить строку "Никогда". Всего допускается до 7 уровней вложения функций ЕСЛИ.

Использование функции ЕСЛИ наделяет формулу способностью “принимать решения”. Любое логическое выражение должно содержать по крайней мере один оператор сравнения, который определяет отношение между элементами логического выражения: = Равно, > Больше, < Меньше, >= Больше или равно, <= Меньше или равно, <> Не равно. Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).

 

Текстовые функции

=ДЛСТР( текст ) - возвращает количество символов в текстовой строке. Аргумент текст должен быть строкой символов, заключенной в двойные кавычки, или ссылкой на ячейку.

= ЗАМЕНИТЬ (старый_текст; нач_ном; число_знаков; новый_текст) – заменяет часть текста строки на другой текст. Старый_текст - текст, в котором заменяют некоторые символы; нач_ном - позиция символа в тексте «старый_текст», начиная с которой будет произведена замена; число_знаков - число символов в тексте «старый_текст», которые заменяются новым_текстом; новый_текст - текст, который заменяет символы в тексте «старый_текст»

=ЛЕВСИМВ (текст; кол-во знаков) – возвращает указанное кол-во знаков с начала текста

=ПРАВСИМВ (текст; кол-во знаков) – возвращает указанное кол-во знаков с конца текстовой строки.

=СЦЕПИТЬ (текст1; текст2) – объединяет несколько текстовых строк в одну.

 

Функции даты и времени

= ДАТА( год; месяц; день) - возвращает заданную дату в числовом формате Microsoft Excel.

=ДАТАЗНАЧ( текст ) - Преобразует дату из текстового формата в числовой. Пример текстового аргумента “01.01.2015”.

=МЕСЯЦ( дата ) - Преобразует дату в числовом формате в месяцы.

=СЕГОДНЯ() -Выдает текущую дату.

=ДЕНЬНЕД( дата; [тип] ) - - Преобразует дату в числовом формате в день недели. тип – необязательный аргумент. Целое число от 1 до 3, которое указывает формат подсчета:1 – неделя начинается с воскресенья. Является значением по умолчанию; 2 – неделя начинается с понедельника; 3 – неделя начинается с понедельника, а отсчет начинается с нуля.

=ГОД( дата ) - Находит год для заданной даты.

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

Цель работы; постановка задачи; описание выполнения заданий раздела 6.4; выводы по работе.

 

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

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

2. Выравнивание содержимого ячеек

3.Как изменить высоту и ширину ячеек?

4. Основные категории функций

5. Типы аргументов, правила записи аргументов

 

ЛАБОРАТОРНАЯ РАБОТА №3

ПОСТРОЕНИЕ ДИАГРАММ

3.1 Цель работы - получение практических навыков создания, редактирования, форматирования диаграмм.

Теоретические сведения

Создание диаграммы

Диаграмма – это графическое изображение, в котором числовые данные представляются в виде геометрических фигур. Диаграммы в Excel 2007 строятся по данным, представленным в электронной таблице. В Excel 2007 можно построить диаграммы 14 типов. Наиболее распространенными типами диаграмм являются гистограммы, круговые диаграммы и графики.

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

Для построения диаграммы нужно выделить диапазон ячеек с данными, по которым будет строиться диаграмма. Выделенный диапазон ячеек может быть как связным, так и несвязным. Желательно, чтобы в него вошли подписи строк и столбцов.

После этого можно:

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

- использовать элементы управления на Ленте:

1. Открыть на вкладке Вставка в группе Диаграммы список кнопки нужного типа диаграмм (рис.3.1).

Рис.3.1 - Группа Диаграммы вкладки Вставка

 

2. Выбрать необходимый вид диаграммы.

- использовать элементы управления окна Вставка диаграммы:

1. Выбрать на вкладке Вставка в группе Диаграммы кнопку открытия диалогового окна .

2. В окне Вставка диаграммы выбрать нужный тип и вид диаграммы.

3. Выбрать кнопку ОК.

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

На ленте появится временный раздел Работа с диаграммами с тремя временными вкладками с элементами управления для работы с диаграммой: Конструктор, Макет и Формат. Вкладка Конструктор автоматически становится текущей. На ней в группе Макеты диаграмм можно выбрать нужный макет диаграммы, который определяет набор объектов диаграммы и их расположение, а в группе Стили диаграмм – стиль оформления диаграммы.

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

Чтобы изменить диапазон ячеек, по данным в которых построена диаграмма, нужно:

1. Выбрать диаграмму.

2. Выполнить Конструктор - Данные - Выбрать данные, либо использовать контекстное меню.

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

 

Рис.3.2 – Окно «Выбор источника данных»

 

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

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

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

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

Выбранные параметры макета будут применены к выделенному элементу. Например, если выделена вся диаграмма, метки данных будут применены ко всей последовательности данных. Если выделена единственная точка данных, метки данных будут применены только к выделенной последовательности данных или к выделенной точке данных.

Для форматирования оси необходимо выполнить двойной щелчок и в появившемся диалоговом окне «Формат оси» (рис.3.3) установить необходимые параметры.

 

Рисунок 3.3 – Формат оси

 

Построение графиков функции

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

В качестве примера рассмотрим процедуру построения графика функции y = x ² при х Î[-0,5;0,5]. Выберем щаг изменения аргумента (0,1). Введем в ячейки А1 и В1 названия полей - X и Y, соответственно. В диапазон ячеек А2:А12 введем значения переменной х: -0,5; -0,4; …; 0,5. Выбранные значения переменной образуют арифметическую прогрессию, поэтому воспользуемся приемом автозаполнения. В ячейку В2 введем формулу =А1^2. Скопируем формулу в ячейки В3:В12.

Для построения графика функции выделим диапазон ячеек В1:В12, содержащий таблицу значений функции y, на ленте выберем команду Вставка – График (область диаграммы).

На оси Х должны быть отложены значения аргумента из ячеек А2:A12. Для этого на вкладке Конструктор нажимаем кнопку Выбрать данные. В появившемся окне используем кнопку Изменить, в поле Диапазон подписей оси Х вводим диапазон А2:А12.

 

Рис.3.4. График функции

 

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

Если на вкладке Конструктор в группе Расположение щелкнуть Переместить диаграмму, можно выбрать размещение диаграммы на отдельном листе, при этом таблица со значениями данных и рядов не отображается.

Для построения графика функции с одним условием в ячейку В2 необходимо вставить функцию Если.

Пример:

В ячейку В2 вводится формула: =ЕСЛИ(А2>=0;A2^2;A2^(1/3))

Построение поверхности

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

Рассмотрим пример построения поверхности при х, z Î[-1, 1]. Пусть шаг изменения аргументов Х и Y одинаков и равен 0,2

В диапазон ячеек B1:L1 введем последовательность значений: -1, -0.8,..., 1 переменной Х, а в диапазон ячеек А2:А12 — последовательность значений: -1, -0.8,..., 1 переменной Y. В ячейку В2 введем формулу для вычисления Z: =$A2^2-B$1^2. Скопируем формулу, заполнив диапазон B2:L12. При копировании формулы ссылки на первый столбец (А) и первую строку (1) изменяться не должны. Поэтому перед ними ставится знак $, который дает абсолютную ссылку на столбец А и строку 1 (смешанная адресация). Чтобы набрать абсолютную ссылку (например, $A2), в строке формул установите курсор перед адресом А2 и дважды щелкните по функциональной клавише F4.

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

 

Задание на лабораторную работу

3.4.1 На основании таблицы "Доходы фирм по продаже компьютеров в 1- ом полугодии текущего года" постройте:

1) гистограмму распределения доходов фирмы1 по месяцам 1 квартала,

2) график распределения доходов фирмы2 за полгода

3) гистограмму доходов всех фирм за