Министерство образования и науки Украины
Севастопольский национальный технический университет
ОБРАБОТКА И АНАЛИЗ ДАННЫХ В СРЕДЕ
EXCEL
Методические указания
к выполнению контрольной работы
по дисциплине “ИНФОРМАТИКА”
для студентов направления
“Экономика и предпринимательство”
Севастополь
2007
УДК 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. ЗАДАНИЕ № 1.
ГРАФИЧЕСКИЕ СРЕДСТВА ЭЛЕКТРОННЫХ ТАБЛИЦ
Цель - приобрести навыки работы с таблицами, освоить технологию создания и форматирования диаграмм.
1.1. Краткие теоретические сведения
1.1.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 – Вычисление значений функции у(х
).
1.1.2. Создание диаграмм
Диаграмма
- графическое представление числовых данных. Набор значений, отображаемых на диаграмме, называется рядом данных. Это группа ячеек с данными в пределах одной строки или столбца таблицы. На одной диаграмме можно отображать несколько рядов данных.. Диаграмма – внедренный объект. Он может располагаться на одном листе с данными либо на отдельном листе. Диаграмма сохраняет связь с данными, на основе которых она построена, и при обновлении этих данных, изменяет свой вид.
Для построения диаграмм используют Мастер диаграмм, запускаемый командой Вставка-Диаграмма
или нажатием кнопкиМастер диаграмм
на стандартной панели инструментов. Перед построением диаграммы выделяют мышью диапазон, содержащий исходные данные. При задании диапазона имеет смысл включить в него все заголовки, которые идентифицируют ряды данных и категории диаграммы (названия точек одного ряда диаграммы). Эти заголовки мастер включит в диаграмму в виде легенды или меток на осях координат. Диапазоны заголовков и рядов данных могут быть несмежными, тогда они выделяются при нажатой клавише CTRL..Нажимают кнопку Мастер диаграмм
или выполняют команду Вставка-Диаграмма.
Шаг 1.
В первом окне диалога мастера диаграмм в
ыбирают тип и формат диаграммы.
Шаг 2.
Задают или подтверждают диапазон данных, отображаемых на диаграмме. Если диаграмма включает в себя несколько рядов, можно осуществить группировку данных в строках или столбцах таблицы. Для форматирования и настройки рядов используют вкладку Ряд.При построении графиков задают метки оси Х (поле Подписи оси Х).
Шаг 3.
Устанавливаются параметры диаграммы: заголовки, подписи, формат легенды, координатной сетки, таблицы данных.
Шаг 4.
Устанавливаются параметры размещения диаграммы: на отдельном или имеющемся листе. После выполнения всех этапов построения необходимо задать кнопку Готово
.
Диаграмма состоит из набора отдельных элементов (ряды, оси, заголовки). Для форматирования элемента диаграммы необходимо подвести к нему указатель мыши, выполнить двойной щелчок левой кнопкой мыши и в появившемся диалоговом окне установить требуемые параметры. Для изменения элемента диаграммы (редактирования) необходимо вызвать контекстное меню правой кнопкой мыши.
1.2. Порядок выполнения задания
1. Выбрать вариант I
= (
N
mod
25 ) +1,
где N
- последние две цифры зачетной книжки, а I
- остаток от деления N
на 25
2. Вычислить значения функции на заданном интервале с заданным шагом изменения аргумента. Результаты оформить в виде таблицы. Первый столбец таблицы должен содержать значения аргумента x, второй столбец – значения функции y. Для ввода значений x использовать метод автозаполнения, для вычисления значений y – встроенную логическую функцию ЕСЛИ.
3. Построить график функции. Сформировать на графике: заголовок, названия осей, метки легенды.
3.1. На графике разными маркерами выделить максимальное и минимальное значения функции.
3.2. Метки на оси X представить в формате с фиксированной точкой (числовой формат), а на оси Y – с плавающей точкой (экспоненциальный формат).
1.3. Варианты индивидуальных заданий
Таблица 1.1 – Варианты заданий
| № | Функция | Интервал изменения аргумента | Шаг изменения аргумента |
| 0 | y = |
|
0.2 |
| 1 | y = |
|
0.2 |
| 2 | y = |
|
0.4 |
| 3 | y = |
|
0.2 |
| 4 | y = |
|
0.2 |
| 5 | y = |
|
0.2 |
| 6 | y = |
|
0.2 |
| 7 | y = |
|
0.2 |
| 8 | y = |
|
0.5 |
| 9 | y = |
|
1 |
| 10 | y = |
|
0.1 |
| 11 | y = |
|
0.2 |
| 12 | y = |
|
0.2 |
| 13 | y = |
|
0.2 |
14 |
y = |
|
0.2 |
| 15 | y = |
|
0.2 |
| 16 | y = |
|
0.2 |
| 17 | y = |
|
0.2 |
| 18 | y = |
|
0.2 |
| 19 | y = |
|
0.2 |
| 20 | y = |
|
0.2 |
| 21 | y = |
|
0.2 |
| 22 | y = |
|
0.2 |
| 23 | y = |
|
0.2 |
| 24 | y = |
|
0.2 |
1.4. Содержание отчета
Цель работы, постановка задачи, основные шаги формирования таблицы, компьютерные распечатки результатов моделирования, выводы.
1.5. Контрольные вопросы
1. Какие средства имеются в электронных таблицах Excel для построения графиков и диаграмм?
2. Какие этапы необходимо выполнить для построения графика с помощью мастера диаграмм?
3. Как изменить формат отображения ряда данных на диаграмме?
4. Какие изменения можно внести при редактировании формата оси на диаграмме?
5. Как изменить область данных при работе с диаграммой?
6. Какие типы диаграмм вы знаете. Как изменить вид диаграммы?
2. ЗАДАНИЕ №2
ОБРАБОТКА СПИСКОВ
Цель – изучить принципы выполнения операций сортировки данных (строк / столбцов) и поиска записей с использованием фильтров.
2.1. Краткие теоретические сведения
2.1.1. Создание списков
Список в Excel – снабженная метками последовательность строк рабочего листа, содержащих в одинаковых столбцах данные одного типа. Списки можно рассматривать как внутренние базы данных, записями в которых являются строки, а полями – столбцы.
Список можно создавать вручную, вводя в ячейки таблицы необходимые данные. Ввод списка можно осуществлять с помощью команды Данные – Форма
. В окне команды размещаются поля таблицы.Форма дает возможность просматривать, редактировать, добавлять записи, а также осуществлять поиск по заданным признакам. Операции редактирования записей выполняются с помощью кнопок «Добавить», «Удалить». Движение по списку осуществляется с помощью кнопок «Назад», «Далее». Кнопка «Критерий» позволяет получить доступ к окну поиска. Окно содержит перечень столбцов таблицы, причем все соответствующие поля пусты. Достаточно ввести в одно или несколько полей нужное значение или условие и записи, удовлетворяющие этому условию будут представлены в окне команды.
2.1.2. Сортировка данных
Список можно отсортировать по возрастанию, по убыванию или в заданном пользователем порядке. Чтобы отсортировать весь список необходимо выделить список либо одну ячейку в списке и выбрать в меню команду Данные – Сортировка.
Сортировка строк. В полях Сортировать по
и Затем по
необходимо указать (выбрать из раскрывающегося меню) имена сортируемых столбцов (ключи сортировки), порядок сортировки (возрастание или убывание) и нажать кнопку OK
. Поле Затем по (
второй ключ сортировки)
используют, если в поле первого ключа есть одинаковые значения.
Сортировка столбцов. В окне команды Сортировка
нажать кнопку Параметры
. Установить переключатель Сортировать
в положение Столбцы диапазона
и нажать кнопку OK
. Выбор строк, по которым требуется отсортировать столбцы производитсяв полях Сортировать по
и Затем по
.
Сортировка в задаваемом пользователем порядке. Требуемый порядок сортировки задается в окне Сортировк
а - Параметры.
Для этогонеобходимо вызвать раскрывающееся меню в поле Сортировка по первому ключу.
Пользовательский список порядка сортировки необходимо создать заранее с помощью команды Сервис-Параметры-Списки
либо из существующих на листе элементов (их необходимо выделить и нажать кнопку Импорт)
, либо с помощью непосредственного ввода элементов списка.
2.1.3. Применение фильтров
С помощью фильтров можно выводить и просматривать только те данные, которые удовлетворяют определенным условиям.
Для этих целей в меню Данные – Фильтр
есть две команды Автофильтр
и Расширенный фильтр
. Для применения фильтров необходимо выделить список или установить курсор на любую ячейку списка.
Автофильтр активизируется путем выбора пункта Данные - Фильтр -
Автофильтр
. В первой строке таблицы появляются кнопки с изображением стрелки. Щелкнув по такой кнопке, можно выбрать поле,
для которого осуществляется поиск записей.
Если в открывшемся окне задать любую константу, соответствующую значению в поле, в результате на выходе фильтра оказывается только одна запись. Параметр Все
восстанавливает в таблице все записи.
С помощью автофильтра можно устанавливать сложные пользовательские критерии. Для этого в раскрывающемся списке необходимо выбрать строку «Условие», в результате чего появится диалоговое окно.
Левые поля диалогового окна задают операцию отношения, которая выбирается из меню (больше, меньше, равно, … для чисел, «содержит», «не содержит»… для текстов). Правые поля должны содержать сравниваемые значения. Можно использовать символы «?» и «*» вместо любого символа или группы символов соответственно. Критерии типа “И”, “ИЛИ”
могут быть заданы для отдельного поля. Если задать условия для нескольких полей, то будут выбраны записи, для которых одновременно выполняются заданные условия (критерий “И”).
Пример использования автофильтра приведен на рисунке 2.1.
Рисунок 2.1 – Выбор строк, для которых значения в поле «Доход» находятся в диапазоне 2000…3000.
При необходимости использовать более сложные критерии отб
. В отличие от автофильтра результаты фильтрации можно вынести в другое место рабочего листа.
Чтобы воспользоваться командой Расширенный фильтр
, предварительно необходимо создать таблицу критериев, расположив ее на том же листе. Обычно для этого копируют имена полей списка на свободное пространство. В ячейку, расположенную под именем поля, для которого осуществляется поиск записей, записывается условие отбора. Пример использования расширенного фильтра приведен на рисунке 2.2.
Рисунок 2.2 – Выбор строк, для которых значения в поле «Доход» превышают 2000.
При использовании расширенного фильтра необходимо выделить область, содержащую данные (A2:B4
). Активизировать фильтр путем выбора пункта Данные - Фильтр -
Расширенный
Фильтр
. Подтвердить область исходной таблицы и указать диапазон критерия D2:
E
3
.На выходе фильтра появятся записи, соответствующие заданному критерию.
Результирующую таблицу можно помещать, как на место исходного списка, так и вне его, задав в диалоговом окне левую верхнюю ячейку будущей таблицы результата.
Аналогично в ячейку под соответствующей меткой столбца можно вставить другой критерий. При этом критерию “И”
соответствует несколько условий, задаваемых в одной строке (для нескольких полей). Несколько условий в различных строках интерпретируются как критерий “ИЛИ”.
2.2. Порядок выполнения задания
1. Выбрать вариант i=N mod 14,
где N
– две последние цифры зачетки.
2. Создать таблицу для заданной предметной области в соответствии с вариантом. Таблица должна содержать 5-6 полей ( 2-3 числовых поля, остальные - символьные, даты и др.), 10-15 записей. Использовать построенную таблицу при выполнении последующих заданий.
3. Выполнить сортировку строк по значениям одного и нескольких полей. Выполнить сортировку столбцов Порядок сортировки задать самостоятельно. Для выполнения задания созданную таблицу скопировать четыре раза. В первом случае значения символьного поля расположить а алфавитном порядке, во втором – отсортировать по значениям числового поля, в третьем – выполнить сортировку по значениям двух полей, в четвертом – сортировку столбцов (по значениям заголовков).
4. Выполнить выборку данных из таблицы с использованием фильтров.
4.1. Автофильтр. Использовать в качестве условий : 1) точную форму (константу) выбора; 2) текст выбора (шаблон); 3) формулу выбора (условие).
Задать критерий типа "И" для поиска данных по значениям двух полей. Задать критерий типа "ИЛИ" для поиска данных в отдельном столбце.
4.2. Расширенный фильтр. Создать на рабочем листе таблицу критериев. Для числового поля задать простое условие, сложное условие для попадания значений числового поля в диапазон (критерий И). Для значений символьного поля реализовать условие ИЛИ.
2.3. Варианты индивидуальных заданий
Таблица 2.1 – Варианты заданий
| Номер варианта
|
Предметная область
|
Номер варианта
|
Предметная область
|
| 0
|
городская справка
|
7
|
склад
|
| 1
|
кафедра университета
|
8
|
авиакасса
|
| 2
|
магазин
|
9
|
счет банка
|
| 3
|
гостиница
|
10
|
библиотека
|
| 4
|
отдел кадров
|
11
|
ж/д вокзал
|
| 5
|
бухгалтерия
|
12
|
телефонный справочник
|
| 6
|
экзаменационная ведомость
|
13
|
автопредприятие
|
2.4. Содержание отчета
Цель работы, постановка задачи, основные шаги выполнения сортировки, компьютерные распечатки исходной таблицы, результаты сортировки с выбранными полями (строками) и порядком сортировки по каждому полю (строке); описание основных шагов выполнения поиска, критерии “И”, “ИЛИ” для поиска числовых и символьных полей, выводы.
2.5. Контрольные вопросы
1. Что такое "База данных" с точки зрения Excel?
2. Какие средства существуют в Excel для ввода данных в базу?
3. Как осуществлять поиск записи по критерию в режиме "Форма"?
4. Какие возможности предоставляет команда "Автофильтр"?
5. Как выполняется поиск с использованием расширенного фильтра?
6. Пояснить критерий типа «ИЛИ».
7. Пояснить критерий типа «И».
3. ЗАДАНИЕ №3.
ИСПОЛЬЗОВАНИЕ MICROSOFT EX
C
EL В ЭКОНОМИЧЕСКИХ РАСЧЕТАХ
Цель - приобрести навыки применения функций просмотра и ссылок в решении экономических задач, обработки списков в режиме подбора параметров, создания сводных таблиц
3.1
. Краткие теоретические сведения
Для извлечения информации из блоков (диапазонов) используют функции категории «ссылки и массивы». Массив - это объект Excel, используемый для получения нескольких значений в результате вычисления одной формулы (диапазон массива) или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам (диапазон констант).
Для ссылки на ячейку, группу ячеек или формулу удобно использовать имена. Для присвоения имени используется команда Вставка – Имя - Присвоить
. В поле Имя
вводится имя диапазона, в поле Формула
указывается ячейка или диапазон (вводится непосредственно либо выделяется с помощью мыши). После нажатия кнопки Добавить
имя отображается в соответствующем поле. По умолчанию имена являются абсолютными ссылками. Использование имен может упростить понимание формулы. Например, формула «=Активы-Пассивы» понятнее формулы «=F6-D6». Имена можно использовать в любом листе книге. Например, если имя «Контракты» ссылается на группу ячеек "A20:A30" в первом листе рабочей книге, то это имя можно применить в любом другом листе той же рабочей книги.
3.1.1.
Основные встроенные функции для работы с массивами
Функция ПРОСМОТР. Векторная форма функции ПРОСМОТР просматривает диапазон, в который входят значения только одной строки или одного столбца (вектор) в поисках определенного значения и возвращает значение из другого столбца или строки.
ПРОСМОТР(искомое_значение;просматриваемый_вектор;вектор результата)
Искомое_значение – значение, которое ищется в просматриваемом векторе; может быть числом, текстом, логической константой, ссылкой на ячейку. Просматриваемый вектор – диапазон, содержащий одну строку или столбец с текстом, числами или логическими значениями, расположенными в порядке возрастания. Вектор результата – диапазон, содержащий строку или столбец того же размера, что и просматриваемый вектор.
Функция ВПР. Ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы:
ВПР(искомое_значение;инфо_таблица;номер_столбца;интервальн_просмотр)
Искомое_значение - это значение, которое должно быть найдено в первом столбце массива. Искомое_значение может быть значением, ссылкой или текстовой строкой. Инфо_таблица - это таблица с информацией, в которой ищутся данные. Можно использовать ссылку на интервал или имя интервала.Если интервальный_просмотр имеет значение ИСТИНА, то значения в первом столбце аргумента инфо_таблица должны быть расположены в возрастающем порядке. Если интервальн_просмотр имеет значение ЛОЖЬ, то инфо_таблица не обязана быть сортированной.
Функция ИНДЕКС. Возвращает значение элемента таблицы или массива, заданного номером строки и номером столбца:
ИНДЕКС(массив;номер_строки;номер_столбца)
Номер_строки — это номер строки в массиве, из которой нужно возвращать значение. Номер_столбца — это номер столбца в массиве, из которого нужно возвращать значение. Если используются оба аргумента номер_строки и номер_столбца, то функция ИНДЕКС возвращает значение, находящееся в ячейке на пересечении указанных строки и столбца.
Функция ПОИСКПОЗ. Возвращает относительное положение элемента массива, который соответствует заданному значению. Например: ПОИСКПОЗ("б";{"а";"б";"в"};0) возвращает 2 -- относительную позицию буквы "б" в массиве {"а";"б";"в"}. ПОИСКПОЗ не различает регистры при сопоставлении текстов:
ПОИСКПОЗ
(Искомое_значение
,просматриваемый_массив,тип_сопоставлен
)
Искомое_значение — это значение, используемое при поиске значения в таблице, которое сопоставляется со значениями в аргументе просматриваемый_массив Просматриваемый_массив - это непрерывный интервал ячеек, содержащих искомые значения.Тип_сопоставлен - это число -1, 0 или 1. Если тип_сопоставлен равен 1, то функция ПОИСКПОЗ находит наибольшее значение, которое равно или меньше, чем искомое_значение. Просматриваемый_массив должен быть упорядочен по возрастанию.Если тип_сопоставлен равен 0, то функция ПОИСКПОЗ находит первое значение, которое в точности равно аргументу искомое_значение. Просматриваемый_массив может быть в любом порядке.Если тип_сопоставлен равен -1, то функция ПОИСКПОЗ находит наименьшее значение, которое равно и больше чем искомое_значение. Просматриваемый_массив должен быть упорядочен по убыванию. Если тип_сопоставления опущен, то предполагается, что он равен 1.
Пример использования встроенных функций для работы с массивами. Для заполнения поля «КоэфА» таблицы 1 (рисунок 3.1) на основании таблицы 2 в ячейку С3 можно ввести одну из следующих формул:
1) =ПРОСМОТР(B3;$F2:$F5;$G2:$G5);
2) =ВПР(B3;$F2:$H5;2);
3) =ИНДЕКС(;$F2:$H5;ПОИСКПОЗ(B3; ;$F2:$F5;0);2).
Рисунок 3.1 – Исходные данные для работы с функциями категории «Ссылки и массивы»
Результатом вычислений по каждой из формул будет значение 1,8. Для заполнения ячеек С4:С7 в них необходимо скопировать введенную в ячейку С3 формулу.
3.1.2. Подбор
параметра
Подбор параметра является частью блока задач, который иногда называют инструментами анализа "что-если". Когда желаемый результат одиночной формулы известен, но неизвестны значения, которые требуется ввести для получения этого результата, можно воспользоваться средством «Подбор параметра» выбрав команду Подбор параметра
из меню Сервис
. Подбор параметра - способ поиска определенного значения ячейки путем изменения значения в другой ячейке. В окне команды Подбор параметра
необходимо заполнитьполя:
- Установить в ячейке
: вводят адрес ячейки, содержащей формулу;
- Значение
: вводят искомое значение;
- Изменяя значение ячейки:
вводят ссылку на изменяемую ячейку.
В ячейку, задаваемую в поле Изменяя значение ячейки
по определенному алгоритму подставляются значения до тех пор, пока не будет найдено решение, задаваемое в поле Значение.
По умолчанию по команде Подбор параметра
вычисления прекращаются либо после 100 операций, либо когда найденное решение отличается от заданного не более чем на 0.01. Если требуется большая точность, то следует выбрать команду Параметры
из меню Сервис,
щелкнуть кнопку Вычисления
и изменить поле Максимальное
число
или поле Максимальное
изменение.
При подборе параметра решение имеет тот же знак, что и начальное значение.
3.1.3. Сводные таблицы
Сводные таблицы позволяют осуществлять групповые операции над данными, находящимися либо в списках, либо во внешних базах данных. Для построения сводных таблиц используют Мастер сводных таблиц, запускаемый с помощью команды Данные -
Сводная таблица.
Перед вызовом Мастера выделяют список, на основании котрого строится сводная таблица.
Шаг 1.
Выбирают источник с данными (список или база данных Excel) и вид создаваемого отчета (сводная таблица).
Шаг 2.
Задают или подтверждают диапазон, содержащий исходные данные.
Шаг 3.
Выбираютразмещение таблицы (новый или существующий лист). В случае размещения сводной таблицы на существующем листе указывают адрес левой верхней ячейки сводной таблицы. После нажатия кнопки «Готово» появляется конструктор обработки данных списка (рисунок 3.2)
Рисунок 3.2 – Конструктор обработки данных
Конструктор позволяет задать структуру сводной таблицы, т.е. определить заголовки исходного списка, которые будут использоваться в качестве полей страницы, столбца, строки. Структура задается путем перетаскивания нужных полей на соответствующие области шаблона таблицы. Выбираются данные, по которым будут подводиться итоги. В качестве данных по умолчанию предлагается сумма по выбранному полю числовых данных или количество значений, если исходные данные текстовые. Например, для определения суммарной заработной платы по магазинам в область «Поля строк» перетаскиваем элемент «№ магазина» из списка полей, в область «Элементы данных» перетаскиваем элемент «Зарплата» из списка полей. Полученная сводная таблица приведена на рисунке 3.3. Чтобы выбрать другие итоги (например, среднее значение), следует правой кнопкой вызвать контекстное меню и выбрать строку «Параметры поля».Для удаления любого поля из области конструктора нужно просто потянуть за него мышкой и вытащить за пределы данной области
Рисунок 3.3 – пример сводной таблицы
3.2. Порядок выполнения задания
1. Выбрать вариант i=N mod 15,
где N
– две последние цифры зачетной книжки.
2. Перенести на рабочий лист таблицу3.1. Добавить в таблицу пустые столбцы с заголовками«Коэф_A», «Коэф_В», «Зарплата». Присвоить таблице имя «Сотрудники»
3.Создать на этом же рабочем листе таблицу, содержащую поля: «Должность», «Коэф_А», «Коэф_В». Заполнить таблицу в соответствии с вариантом задания(таблица 3.2). В поле «Должность» ввести значения: Кассир, Продавец, Ст.продавец. Присвоить таблице имя «Коэффициенты».
4. В свободную ячейку рабочего листа занести значение минимальной заработной платы в соответствии с вариантом (таблица 3.2). Присвоить ячейке имя «Мин_зарплата».
5.Заполнить поля «Коэф_A», «Коэф_В», «Зарплата» таблицы«Сотрудники»: поля «Коэф_A» и «Коэф_В» заполнить на основании таблицы«Коэффициенты» с помощью функции ВПР, используя в качестве искомого значения должность сотрудника. Зарплата сотрудника считается по формуле Зарплата=Коэф_А*Мин_зарплата+Коэф_В.
6. Вычислить Фонд заработной платы, используя для поля «Зарплата» таблицы сотрудники итоговую функцию СУММ. Используя режим «Подбор параметра» определить минимальную заработную плату для заданного вариантом фонда заработной платы. В качестве изменяемой ячейки выбрать ячейку с именем «Мин_зарплата».
7. Построить сводную таблицу. На основании сводной таблицы определить среднюю заработную плату сотрудников по магазинам, общую заработную плату сотрудников по должностям.
3.3. Варианты индивидуальных заданий
Таблица 3.1
| № магазина | ФИО | Должность |
| № 24 | Стрелкова | Ст.продавец |
| № 24 | Петрова | Продавец |
| № 24 | Семенова | Кассир |
| № 36 | Бойко | Ст.продавец |
| № 36 | Алехина | Продавец |
| № 36 | Орлова | Продавец |
| № 36 | Яковлева | Кассир |
| № 47 | Галкина | Ст.продавец |
| № 47 | Точилина | Продавец |
| № 47 | Долгова | Продавец |
| № 47 | Веселова | Продавец |
| № 47 | Яковенко | Кассир |
| № 54 | Егорова | Ст.продавец |
| № 54 | Зайко | Продавец |
| № 54 | Павлова | Кассир |
Таблица 3.2
| № вар | Коэффициенты | Минимальная заработная плата |
Фонд заработной платы | |||||
| Ст.продавец | Продавец | Кассир | ||||||
| А | B | А | B | А | B | |||
| 0 | 2,2 | 100 | 1,8 | 60 | 1,5 | 50 | 300 | 10000 |
| 1 | 2,3 | 150 | 1,7 | 75 | 1,6 | 55 | 350 | 11000 |
| 2 | 2,9 | 140 | 2,2 | 90 | 1,7 | 60 | 400 | 12000 |
| 3 | 2,8 | 90 | 2,1 | 85 | 1,75 | 70 | 420 | 13000 |
| 4 | 2,5 | 80 | 1,8 | 70 | 1,55 | 45 | 370 | 11000 |
| 5 | 2,6 | 125 | 1,9 | 100 | 1,65 | 60 | 360 | 13000 |
| 6 | 2,4 | 143 | 2,0 | 75 | 1,8 | 50 | 410 | 12500 |
| 7 | 2,7 | 95 | 2,3 | 65 | 1,9 | 65 | 380 | 13000 |
| 8 | 2,9 | 115 | 2,4 | 80 | 1,85 | 75 | 405 | 11000 |
| 9 | 2,8 | 130 | 2,2 | 90 | 1,95 | 60 | 340 | 14000 |
| 10 | 2,3 | 135 | 1,6 | 110 | 1,45 | 80 | 330 | 12000 |
| 11 | 2,6 | 95 | 2,1 | 105 | 1,7 | 85 | 390 | 13000 |
| 12 | 2,5 | 110 | 1,9 | 120 | 1,55 | 90 | 310 | 11000 |
| 13 | 2,4 | 85 | 1,8 | 115 | 1,6 | 85 | 340 | 13000 |
| 14 | 2,1 | 135 | 1,6 | 120 | 1,4 | 90 | 290 | 10000 |
3.5. Контрольные вопросы
1. Что такое массив?
2. Как присвоить имя массиву или ячейке? Приведите примеры использования имен.
3. Какие примеры использования массивов Вы можете привести?
4. Какие встроенные функции для работы с массивами Вы знаете?
5. Для чего применяется инструмент «Подбор параметра»
6. Этапы построения сводной таблицы.
БИБЛИОГРАФИ
ЧЕСКИЙ СПИСОК
1. Хэлворсон М., Янг М. Эффективная работа с MicrosoftOffice 97. Пер. с англ. — СПб.: Питер, 1999.—1056 с.: ил.
2. MicrosoftOffice 97. Шаг за шагом: Практ. пособ. / Пер. с англ. — М.: ЭКОМ, 1997. — 304 с.: ил.
3. Брюховецкий А.А., Сорокин С.С. Методические указания к практическим занятиям по дисциплине “Информатика” по разделу “MSOffice 97. Excel”.-Севастополь, изд-во СевНТУ, 1999.-20с.
4. К.Карлберг. Бизнес – анализ с помощью Excel.- Киев, “Диалектика”,1997. –440с.
5. Ларсен Р.У. Инженерные расчеты в Excel / – Р.У.Ларсен. – М.: Вильямс,2002. – 359с.
6. Гайдышев И.П. Решение научных и инженерных задач средствами Excel, Vba, C/C++ / И.П. Гайдышев. – СПб.:BHV – Петербург,2004.– 512 с.
7. Петрунин Ю.Ю. Решение экономических задач в Excel / Ю.Ю. Петрунин. – СПб.:Питер,2001. – 188с.
8. Microsoft Excel 2000. – М.: ЭКОМ, 2001. – 471 с.
9. Додж М. Эффективная работа с MSExcel 2000 / М.Додж. – М.: BHV, 2001.– 1052 с.
Составители: Брюховецкий А.А., канд. техн. наук, доцент
Лебедева М.А., ассистент
Ответственный за выпуск зав. кафедрой кибернетики и вычислительной техники Скатков А.В. д.т.н., профессор