Порядок выполнения задания
Севастопольский национальный технический университет
ОБРАБОТКА И АНАЛИЗ ДАННЫХ В СРЕДЕ EXCEL
Методические указания
К выполнению контрольной работы
по дисциплине “ИНФОРМАТИКА”
Для студентов направления
“Экономика и предпринимательство”
Севастополь
УДК 681.3
Методические указания к выполнению контрольной работы по дисциплине «ИНФОРМАТИКА» на тему «Обработка и анализ данных в среде Excel» для студентов направления «Экономика и предпринимательство» / Сост. А.А. Брюховецкий, М.А.Лебедева. — Севастополь: Изд-во СевНТУ, 2007. — 20 с.
Цель –дать студенту практические знания и навыки работы с одним из инструментов современной офисной компьютерной технологии – табличным процессором Excel. В методических указаниях представлено подробное описание выполнения заданий на конкретных примерах, приведены варианты индивидуальных заданий, контрольные вопросы для проверки знаний.
Методические указания рассмотрены и утверждены на заседании кафедры кибернетики и вычислительной техники (протокол № 7 от 27.04.2007 г.)
Допущено учебно-методическим центром СевНТУ в качестве методических указаний.
Рецензент канд. техн. наук, доцент.
Нормоконтроль Персидсков Г.М.
СОДЕРЖАНИЕ
1. Задание 1. Графические средства электронных таблиц......................... 4
1.1. Краткие теоретические сведения...................................................... 4
1.1.1. Работа с таблицами................................................................ 4
1.1.2. Создание диаграмм................................................................ 6
1.2. Порядок выполнения задания.......................................................... 6
1.3. Варианты индивидуальных заданий............................................... 7
1.4. Содержание отчета........................................................................... 9
1.5. Контрольные вопросы..................................................................... 9
2. Задание 2. Обработка списков................................................................ 9
2.1. Краткие теоретические сведения...................................................... 9
2.1.1. Создание списков.................................................................... 9
2.1.2. Сортировка данных.............................................................. 10
2.1.3. Применение фильтров для поиска записей......................... 10
2.2. Порядок выполнения задания........................................................ 12
2.3. Варианты индивидуальных заданий............................................. 12
2.4. Содержание отчета......................................................................... 13
2.5. Контрольные вопросы................................................................... 13
3. Задание 3. Использование MS Excel в экономических расчетах........ 13
3.1. Краткие теоретические сведения................................................... 14
3.1.1. Основные встроенные функции для работы с массивами.. 14
3.1.2. Подбор параметра................................................................ 16
3.1.3. Сводные таблицы................................................................. 16
3.2. Порядок выполнения задания........................................................ 18
3.3. Варианты индивидуальных заданий............................................. 18
3.4. Содержание отчета......................................................................... 19
3.5. Контрольные вопросы................................................................... 19
Библиографический список....................................................................... 20
ЗАДАНИЕ № 1.
ГРАФИЧЕСКИЕ СРЕДСТВА ЭЛЕКТРОННЫХ ТАБЛИЦ
Цель - приобрести навыки работы с таблицами, освоить технологию создания и форматирования диаграмм.
Краткие теоретические сведения
Работа с таблицами
Основными объектами Excel являются таблицы. В ячейки таблицы можно вводить константы и формулы. Константы подразделяют на числовые (2.31; 5.4Е02; 45%), текстовые (Итого, 1 квартал), логические (ИСТИНА, ЛОЖЬ), даты и время (12.03.07; 11:30). Тип данных определяется автоматически при вводе.
Для ввода последовательных рядов данных, т.е. данных, отличающихся друг от друга на фиксированный шаг, используют метод автозаполнения. В две последовательные ячейки таблицы вводят первый и второй члены ряда. Выделяют ячейки и, установив указатель мыши на маркер заполнения выделенной области, растягивают диапазон на требуемое число ячеек (рисунок 1.1). Для построения произвольных рядов используют команду Правка – Заполнить - Прогрессия. Перед выполнением команды необходимо выделить ячейку, содержащую первый член ряда. В окне команды задают тип прогрессии, шаг прогрессии и предельное значение.
Рисунок 1.1 – Построение числового ряда:
интервал изменения аргумента 
; шаг 0,5
Основным средством вычислений в таблицах являются формулы. Формула – последовательность символов, начинающаяся со знака «=». Формула может содержать константы, ссылки на ячейки, функции, имена, знаки операций. Для обозначения арифметических операций используются символы «+, -, *, /» и «^» (возведение в степень). Пример записи формул: =12+5; =SIN(A1)-A1^2.
Важнейшим свойством таблицы является адрес ячейки. Ссылки на ячейки могут быть абсолютными и относительными. Абсолютная ссылка всегда указывает на конкретную ячейку или интервал ячеек. Признаком абсолютной ячейки является знак доллара «$» перед адресом столбца и строки.($A$2). Относительная ссылка вычисляет адрес относительно той ячейки, где эта ссылка используется (A3). Относительные ссылки автоматически корректируются при перемещении формул из одной ячейки в другую. Это свойство позволяет при помощи маркера заполнения автоматически заполнить ячейки одинаковыми формулами. Смешанные ссылки – это ссылки, которые сочетают в себе относительную и абсолютную адресацию ($H4, H$4). Для изменения способа адресации нужно выделить ссылку на ячейку и нажать клавишу F4.
Для выполнения стандартных вычислений используют функции. Функция начинается со знака равенства (=), за ним следует имя функции, открывающая скобка, список аргументов, закрывающая скобка. Аргументы в списке разделяются точкой с запятой. В качестве аргументов можно использовать константы, ссылки, формулы, другие функции. Функции, содержащие в качестве аргументов другие функции, называются вложенными. Допускается использовать до семи уровней вложенности.
Excel содержит более 400 встроенных функций. Для облегчения работы с функциями используют специальное средство – Мастер функций. Мастер функций выбирается командой Вставка –Функцияили нажатием на кнопку 
панели инструментов. Перед вызовом Мастера функций необходимо выделить ячейку, куда будет вставлена готовая формула. Мастер функций вычисляет значение функции за два шага.
Шаг 1. В списке Категория выбирается категория, к которой относится функция (математические, статистические, текстовые, логические, финансовые и т.д), в списке Функция – конкретная функция данной категории.
Шаг 2.Ввод аргументов функции в соответствующие поля диалогового окна. Если в качестве аргумента используются ссылки на ячейки, достаточно выделить эти ячейки с помощью мыши. Справа от поля отображается содержимое соответствующей ячейки, а рядом со знаком равенства в средней части окна выводится текущее значение создаваемой функции с учетом введенных на данный момент ссылок. Завершение очередного шага – нажатие кнопки ОК.
Математические функции. Позволяют выполнять действия из различных областей математики. К математическим функциям относятся: ABS (модуль), LN (натуральный логарифм), EXP (ex), КОРЕНЬ, СТЕПЕНЬ и др.
Логические функции.Используются, когда необходимо реализовать те или иные действия в зависимости от выполнения каких-либо условий. Функция ЕСЛИ(аргумент1, аргумент2, аргумент3) возвращает значение аргумент2, если логический аргумент1 при вычислении приобретает значение ИСТИНА, и аргумент3, если логический аргумент1 при вычислении приобретает значение ЛОЖЬ. Использование функции ЕСЛИ для вычисления значений кусочной функции 
показано на рисунке 1.2.
Рисунок 1.2 – Вычисление значений функции у(х).
Создание диаграмм
Диаграмма - графическое представление числовых данных. Набор значений, отображаемых на диаграмме, называется рядом данных. Это группа ячеек с данными в пределах одной строки или столбца таблицы. На одной диаграмме можно отображать несколько рядов данных.. Диаграмма – внедренный объект. Он может располагаться на одном листе с данными либо на отдельном листе. Диаграмма сохраняет связь с данными, на основе которых она построена, и при обновлении этих данных, изменяет свой вид.
Для построения диаграмм используют Мастер диаграмм, запускаемый командой Вставка-Диаграмма или нажатием кнопки Мастер диаграмм на стандартной панели инструментов. Перед построением диаграммы выделяют мышью диапазон, содержащий исходные данные. При задании диапазона имеет смысл включить в него все заголовки, которые идентифицируют ряды данных и категории диаграммы (названия точек одного ряда диаграммы). Эти заголовки мастер включит в диаграмму в виде легенды или меток на осях координат. Диапазоны заголовков и рядов данных могут быть несмежными, тогда они выделяются при нажатой клавише CTRL..Нажимают кнопку Мастер диаграмм или выполняют команду Вставка-Диаграмма.
Шаг 1.В первом окне диалога мастера диаграмм выбирают тип и формат диаграммы.
Шаг 2.Задают или подтверждают диапазон данных, отображаемых на диаграмме. Если диаграмма включает в себя несколько рядов, можно осуществить группировку данных в строках или столбцах таблицы. Для форматирования и настройки рядов используют вкладку Ряд. При построении графиков задают метки оси Х (поле Подписи оси Х).
Шаг 3. Устанавливаются параметры диаграммы: заголовки, подписи, формат легенды, координатной сетки, таблицы данных.
Шаг 4. Устанавливаются параметры размещения диаграммы: на отдельном или имеющемся листе. После выполнения всех этапов построения необходимо задать кнопку Готово.
Диаграмма состоит из набора отдельных элементов (ряды, оси, заголовки). Для форматирования элемента диаграммы необходимо подвести к нему указатель мыши, выполнить двойной щелчок левой кнопкой мыши и в появившемся диалоговом окне установить требуемые параметры. Для изменения элемента диаграммы (редактирования) необходимо вызвать контекстное меню правой кнопкой мыши.
Порядок выполнения задания
1. Выбрать вариант I= (N mod 25 ) +1, где N - последние две цифры зачетной книжки, а I- остаток от деления N на 25
2. Вычислить значения функции на заданном интервале с заданным шагом изменения аргумента. Результаты оформить в виде таблицы. Первый столбец таблицы должен содержать значения аргумента x, второй столбец – значения функции y. Для ввода значений x использовать метод автозаполнения, для вычисления значений y – встроенную логическую функцию ЕСЛИ.
3. Построить график функции. Сформировать на графике: заголовок, названия осей, метки легенды.
3.1. На графике разными маркерами выделить максимальное и минимальное значения функции.
3.2. Метки на оси X представить в формате с фиксированной точкой (числовой формат), а на оси Y – с плавающей точкой (экспоненциальный формат).
Варианты индивидуальных заданий
Таблица 1.1 – Варианты заданий
| № | Функция | Интервал изменения аргумента | Шаг изменения аргумента |
y =   |  | 0.2 | |
y =  |  | 0.2 | |
y =  |  | 0.4 | |
y =  | | 0.2 | |
y =  | | 0.2 | |
y =  |  | 0.2 | |
y =  |  | 0.2 | |
y =  | | 0.2 | |
y =  | | 0.5 | |
y =  |  | ||
y =  |  | 0.1 | |
y =  | | 0.2 | |
y =  | | 0.2 | |
y =  | | 0.2 | |
y =  | | 0.2 | |
y =  | | 0.2 | |
y =  | | 0.2 | |
y =  | | 0.2 | |
y =  | | 0.2 | |
y =  | | 0.2 | |
y =  | | 0.2 | |
y =  | | 0.2 | |
y =  | | 0.2 | |
y =  | | 0.2 | |
y =  | | 0.2 |
Содержание отчета
Цель работы, постановка задачи, основные шаги формирования таблицы, компьютерные распечатки результатов моделирования, выводы.
Контрольные вопросы
1. Какие средства имеются в электронных таблицах Excel для построения графиков и диаграмм?
2. Какие этапы необходимо выполнить для построения графика с помощью мастера диаграмм?
3. Как изменить формат отображения ряда данных на диаграмме?
4. Какие изменения можно внести при редактировании формата оси на диаграмме?
5. Как изменить область данных при работе с диаграммой?
6. Какие типы диаграмм вы знаете. Как изменить вид диаграммы?
ЗАДАНИЕ №2
ОБРАБОТКА СПИСКОВ
Цель – изучить принципы выполнения операций сортировки данных (строк / столбцов) и поиска записей с использованием фильтров.