УДК 512.85
ББК 32.973
М70
Рецензенты:
кафедра математики, логики и интеллектуальных
систем Российского государственного
гуманитарного университета;
д-р техн. наук, проф., А.М. Цирлин,
Институт программных систем им. А.К. Айламазяна РАН
Допущено редакционно-издательским советом МГУИЭ
Мокрова Н.В.
М70 Табличный процессор Microsoft
Office
Excel
2007: Методические указания / Н.В. Мокрова. – М.: МГУИЭ, 2012. – 48с.; ил.
В методических указаниях к лабораторным работам изложены основные способы работы в приложении Microsoft
Office
2007 – табличном процессоре Excel
и практические задания для их освоения. Темы лабораторных работ соответствуют учебным и календарным планам занятий. В каждой лабораторной работе приведены задания для получения навыков работы в табличном процессоре и варианты заданий для контроля и проверки знаний. Лабораторные работы по Excel
рассчитаны на знание интерфейса Microsoft
Office
2007. При подготовке лабораторных работ использованы материалы И.В. Кошелевой.
Предназначены студентам I курса инженерных и экономических специальностей МГУИЭ, изучающим дисциплины «Информатика», «Информационные технологии».
УДК 512.85
ББК 32.973
ã Н.В. Мокрова, 2012
ã МГУИЭ, 2012
Лабораторная работа 1
ОСНОВНЫЕ ПРИЁМЫ РАБОТЫ в
MS
Excel
2007
Цели работы
1. Освоить операции ввода чисел, текста и формул в ячейки рабочего листа Excel
.
2. Освоить работу с относительными адресами ячеек рабочего листа Excel
и способы присваивания имен ячейкам.
3. Изучить правила применения арифметических операций и некоторых математических функций Excel
.
4. Освоить основные операции: автозаполнение, копирование, сохранение данных.
5. Создать и отформатировать простейшую таблицу в MS Excel
.
Задание 1
Вычислить арифметические выражения, используя относительную адресацию, арифметические операции и скобки для указания приоритетов действий.
Порядок выполнения работы
1. Дать рабочему листу название «Вычисления
».
2. В ячейку A
1
ввести комментарий х
=
формат ячейки текстовый, в ячейку A
2
– комментарий у
=
(рис. 1).
3. Затем в ячейку В
1
поместить число 4, а в ячейку В
2
– число 3. Выполнить вычисления в ячейках В3, В4 и В5 по предлагаемым формулам, подставляя вместо имен переменных адреса ячеек, в которых хранятся значения х
и у
:
| Ячейка |
B
|
B
|
B
|
| Формула |
|
|
|
Рис.
1. Лист «Вычисления»
Задание 2
Заданы стороны треугольника a, b и c. Вычислить его площадь по формуле Герона , где р – полупериметр, р = (а+b+c)/2, а также радиус вписанной окружности: и радиус описанной окружности .
Порядок выполнения работы
1. Дать следующему рабочему листу название «Треугольник
».
2. Заполнить ячейки А
2:В
6
как показано на рис. 2 (в ячейке В
6
записать формулу в относительных ссылках, обратите внимание на форматирование текста и чисел в ячейках).
3. Присвоить ячейкам В
2
, В
3
и В
4
имена а
,
b
, с
соответственно. Для этого выделить сначала ячейку В
2
, в строке имен (над столбцом А
) выделить В
2
и набрать а
, нажать Enter. Таким образом ячейке В
2
будет присвоено имя а
. С помощью тех же действий ячейкам В
3
и В
6
присвоить имена b
и р
соответственно. При этом невозможно ячейке В
4
присвоить имя с
, так как Е
xcel
использует символы с
и r
как служебные. Значит ячейке А
4
нужно присвоить имя, отличное от с, например, с
_
4. Дать имена ячейкам В
2
, В
3
, В
4
и В
6
можно и другим способом. На вкладке Формула
выбрать Присвоить Имя
(см. рис. 2).
5. Ввести в ячейку В
7
формулу =корень(p
*(p
‑a
)*(p
‑b
)*(p
‑c
_)
, используя именованные ячейки.
6. Дополнить таблицу вычислениями радиусов вписанной и описанной окружностей.
Рис.
2. Лист «Треугольник»
Задание 3
Заполнить ячейки с помощью прогрессии. Заполнить столбец А
с помощью арифметической прогрессии с первым элементом 5, разностью 1 и предельным значением 12.
Порядок выполнения работы
1. Новому рабочему листу дать название «Прогрессия
».
2. Заполнить текстовые заголовки.
3. Заполнить столбец A
арифметической прогрессией:
- в ячейку А
2
ввести первый элемент, затем второй;
- выделить две ячейки, поставить курсор мыши на маркер заполнения и перетащить вниз при нажатой правой кнопке мыши.
4. Заполнить столбец B,
используя геометрическую прогрессию (рис. 3).
- на вкладке Главная
перейти на панель Редактирование
и щёлкнуть Заполнить
;
- в открывшемся списке выбрать Прогрессия
;
- выбрать Расположение
, Тип
, ввести значения: Шаг
; Предельное значение
. Нажать ОК
.
5. Заполнить прогрессию «дата» (столбец С
на рис. 3), установив параметр рабочий день.
6. Самостоятельно изучить приёмы копирования, перемещения, удаления ячеек и диапазонов.
Рис.
3. Лист «Прогрессия»
Задание 4
Создать и отформатировать таблицу «Финансовый план», сохранить таблицы для дальнейшего создания диаграмм.
Порядок выполнения работы
1. Дать рабочему листу имя «Выполнение плана»
.
2. Создать таблицу выполнения финансового плана для двух филиалов фирмы согласно образцу, пользуясь следующими инструментами Excel
:
- строки, содержащие одинаковые текстовые фрагменты, копировать с помощью Копировать
и Вставить
, либо с помощью маркера заполнения;
- столбец А
(названия месяцев) заполнить методом протягивания с использованием инструмента Прогрессия
;
- размеры и начертание шрифтов форматировать с помощью панели инструментов, аналогичной такой же панели инструментов в W
ord
;
- формат записи данных (количество знаков после запятой, указание денежных единиц, способ записи даты и пр.), направление написания текста, способ его выравнивания, вид рамок, фоновый цвет и т.д. задавать, пользуясь инструментами панели форматирования и вкладками диалогового окна Формат ячеек
.
3. В ячейку А
26
введите текст «Курс пересчета». В ячейку В
26
введите текст 1 у.е.=
, выровняйте его по правому краю. В ячейку С
26
введите текущее значение курса доллара.
4. В ячейку С
4
введите формулу =B
4*$C
$26
, с помощью маркера заполнения скопируйте эту формулу на весь диапазон С
4:С
15
.
5. В ячейку Е
4
введите формулу =D
4*$C
$26
, с помощью маркера заполнения скопируйте эту формулу на весь диапазон Е
4:Е
15
.
6. В ячейку G
4
введите формулу =F
4*$C
$26
, с помощью маркера заполнения скопируйте эту формулу на весь диапазон G
4:G
15
.
Где здесь относительная и абсолютная адресация (ссылка)?
Какие адреса (ссылки) изменятся при копировании?
7. Изменить значение курса доллара, проследить, как при этом меняются значения прибыли в столбцах С
, Е
и G
.
8. Щелкнуть на кнопке Предварительный просмотр
, чтобы увидеть, как будет выглядеть созданная таблица при печати.
9. Сделать текущей ячейку В
16
. Щелкнуть на кнопке Автосумма
. Убедитесь, что программа автоматически подставила в формулу функцию СУММ
и правильно выбрала диапазон ячеек для суммирования. Нажмите клавишу Enter
.
10. Повторить подобные действия для ячеек С
16, D
16,
E
16, F
16, G
16.
11. Сделать текущей ячейку B
17.
Щелкнуть на кнопке Вставка функции
(fx
), в списке Категория
выбрать пункт Статистические
, из развернувшегося списка выбрать функцию СРЗНАЧ
и щелкнуть на кнопке ОК
. Обратить внимание на то, что автоматически выделенный диапазон содержит все ячейки с числовым содержимым, включая ячейку значения суммы. Выделить правильный диапазон методом протягивания и нажмите Enter
.
12. Повторить подобные действия для ячеек С
17, D
17,
E
17, F
17, G
17.
13. Используя порядок действий, описанный ранее, определить месяц с наибольшей (функция МАКС
) и наименьшей (функция МИН
) прибылью.
14. Сохранить рабочую книгу в вашем каталоге.
Рис. 4. Лист «Выполнение плана»
Отчет о лабораторной работе должен содержать
электронный документ с указанием фамилии и группы студента, выполненные на переименованных листах задания описательной части лабораторной работы и задания по предложенному преподавателем варианту на отдельных листах.
Задания для самостоятельного выполнения
Заполнить таблицу «Ввод данных в Excel
» (рис. 5) согласно следующим требованиям:
1. Ввод текста осуществлять, соблюдая параметры форматирования ячеек.
2. Выполнить тиражирование значения (столбец B
)
.
3. Оформить число 15,3
в форматах Excel
(столбец C
), выбирая формат для каждой выделенной ячейки.
4. Заполнить произвольными
значениями, не превышающими 15
, с проверкой вводимых данных (столбец D
). Выполнить Данные / Проверка вводимых значений / Тип данных / Действительные
.
5. Заполнить столбец E
случайными числами
с заданным интервалом.
Например: = СЛЧИС()*12 +3.
(математическая функция СЛЧИС()
– возвращает равномерно распределенное случайное число в интервале [0
; 1]
).
6. Заполнить столбец F
упорядоченным
рядом чисел из интервала [3,15]
с шагом 1,5. (
Прогрессия
).
7. Заполнить столбец G
рабочими днями (автозаполнение)
Рис.
5. Таблица «Ввод данных»
Задания по вариантам
| Столб. |
B
|
D
|
E
|
F
|
G
|
| Вар. |
|||||
| 1 |
23,9 |
<= 20 |
[‑2, 10] |
1; шаг 0,5 |
с 01.10.10 |
| 2 |
34,5 |
>= 5 |
[0, 25] |
7; шаг 1,5 |
с 01.11.10 |
| 3 |
0,92 |
[2, 15] |
[–1, 15] |
0; шаг 0,3 |
с 01.12.10 |
| 4 |
98,1 |
< 34 |
[20, 35] |
-1; шаг 0,1 |
с 01.01.11 |
| 5 |
8,54 |
> ‑12 |
[–3, 18] |
2; шаг 1,2 |
с 01.02.11 |
Лабораторная работа 2
ПОСТРОЕНИЕ ГРАФИКОВ
И ДИАГРАММ СРЕДСТВАМИ Excel
Цели работы
1. Ознакомиться с приёмами работы по созданию и редактированию графиков и диаграмм в MS Excel
.
2. Освоить мастер диаграмм табличного процессора при построении графиков и диаграмм различных типов.
Построение графиков и диаграмм
Для построения диаграммы следует:
1. Выделить диапазон данных, которые будут отображены на диаграмме.
2. На вкладке Вставка
и в группе Диаграммы
выбрать тип создаваемой диаграммы (Гистограмма, Круговая, Линейчатая
и др.). При вставке диаграмма переходит в режим редактирования и становится активна контекстная вкладка Работа с диаграммами.
Работа с диаграммами
Вкладка имеет три набора инструментов для изменения диаграмм:
Конструктор
– содержит параметры, определяющие тип диаграммы, источник данных и их упорядочивание, макеты диаграмм, а также команду Переместить диаграмму
.
Макет
– позволяет указывать свойства диаграмм, добавлять или редактировать элементы диаграммы (подписи данных и осей, добавление легенды и элементов рисования), а также выбирать параметры, связанные с трёхмерными графиками.
Формат
– содержит опции выбора различных элементов диаграммы, присвоения стилей её графическим элементам, включая трёхмерные края, тени, фаски и пр.
Построение сравнительной диаграммы финансовой
деятельности двух филиалов фирмы
Задание 1
Построить разные типы диаграмм для таблицы «Финансовый план» (лабораторная работа 1).
Порядок выполнения работы
1. Открыть лист «Выполнение плана
».
2. Методом протягивания выделить диапазон ячеек A
4:
B
15
, затем нажать клавишу CTRL
и, удерживая ее, выделить диапазоны D
4:D
15
и F
4:F
15
.
3. Щелкнуть Вставка / Диаграммы / Гистограмма
(для отображения графиков разных типов – столбчатой диаграммы финансового плана и двух графиков его фактического выполнения следует изменить тип выделенного элемента диаграммы (рис. 6).
Рис.
6. Смешанная диаграмма
4. Так как диапазоны ячеек были выделены заранее, Мастер диаграмм
автоматически определяет расположение рядов данных (в данном случае – по столбцам). Убедитесь, что данные на диаграмме выбраны правильно.
5. Используя вкладки Конструктор, Макет и Формат
, измените тип диаграммы, введите название «Выполнение финансового плана»
, названия осей тыс.$
и Месяцы года,
сделайте соответствующие надписи в легенде.
6. По желанию можно создать эту диаграмму на текущем или отдельном рабочем листе Excel
, установив соответствующий переключатель.
7. Убедитесь, что диаграмма внедрена в рабочий лист. Отформатируйте ее с помощью диалогового окна Форматирование элемента данных
, которое открывается при нажатии правой клавиши мыши и позволяет изменять тип, толщину и цвет линии, а также тип, цвет и фон маркера для выделенного элемента диаграммы.
8. Создать на другом листе по образцу объемную диаграмму (рис. 7).
9. Озаглавить листы.
.
Рис.
7. Объемная диаграмма
Построение полярных графиков и поверхностей
Задание 2
Построить полярный график функции r = 2 sin(j), j Î [0; 2p].
Порядок выполнения работы
1. Табулировать заданную функцию, пользуясь арифметической прогрессией для формирования значений аргумента и автозаполнением формулой с относительной адресацией – для вычисления значений заданной функции.
2. Выделить диапазон заполненных ячеек и построить плоский график (тип диаграммы – График
) в декартовой системе координат.
3. Для этих же данных построить полярный график (тип диаграммы – лепестковая
). В полярных координатах положение точки на плоскости определяется расстоянием r этой точки от центра координат, который в этом случае называется полюсом, и углом поворота j [рад], между лучом r и полярной осью (в Excel
– это вертикаль, проведенная из полюса вверх). Таким образом получается, что значения функции откладываются из центра координат в виде луча определенной длины, а аргумент, являющийся числом на оси Ох, преобразуется в угол поворота этого луча относительно полярной оси.
4. Сравнить полученные диаграммы.
Чем отличается принцип построения этих типов графиков?
Задание 3
Построить поверхность z = x2
– y2
при х
, у
Î [‑1; 1].
Порядок выполнения работы
1. На следующем листе построить поверхность или трехмерную диаграмму (предварительно преобразовать заданное выражение относительно z
).
2. Табулировать заданную функцию следующим образом: в столбец А
, начиная с ячейки А
2
, с помощью арифметической прогрессии ввести значения аргумента х
; в строку 1, начиная с ячейки В
1
с помощью арифметической прогрессии ввести значения аргумента у
.
3. В ячейку В
2
ввести формулу, соответствующую заданной функции, используя при этом смешанную адресацию:
- для аргумента х – указать абсолютную ссылку на имя столбца, в котором хранятся его значения, т.е. на столбец А
;
- для аргумента у – указать абсолютную ссылку на номер строки, содержащей эти значения, т.е. на строку 1.
Например,
для поверхности z = x2
– y2
формула будет иметь вид:
=$A
2^2‑B
$1^2
Создать сетку значений для каждой пары аргументов х – у: с помощью маркера заполнения размножить формулу из ячейки В
2
на весь диапазон.
4. Выделить блок рабочего листа Excel
, содержащий значения функции z и ее аргументов, и построить трехмерную диаграмму типа Поверхность.
Ряды данных при этом должны находиться в столбцах.
5. Отформатировать диаграмму.
Отчет о лабораторной работе должен содержать
электронный документ с указанием фамилии и группы студента, выполненные на переименованных листах задания описательной части лабораторной работы и задания по предложенному преподавателем варианту на отдельных листах.
Задания для самостоятельного выполнения
Построить указанные графики (поверхность и полярный) согласно вариантам (1 – 5). Для построения указанных в таблице типов диаграмм (столбцы «Тип диаграммы») использовать полученные случайным образом значения в заданных диапазонах (лабораторная работа 1).
| Вар. |
Поверхность |
Полярный график |
Тип диаграммы |
||
| Круговые |
Линейчатые |
||||
| 1 |
|
|
Разрезанная круговая |
Пирамидальная |
Биржевая |
| 2 |
|
|
Вторичная круговая |
Коническая |
Кольцевая |
| 3 |
|
|
Вторичная гистограмма |
Объемная линейчатая |
Пузырьковая |
| 4 |
|
|
Объемная круговая |
Цилиндрическая |
Разрезанная кольцевая |
| 5 |
|
|
Объемная разрезанная круговая |
Линейчатая |
Объёмная пузырьковая |
Лабораторная работа 3
ЛОГИЧЕСКИЕ ФУНКЦИИ В Excel
Цели работы
1. Изучить логические функции MS Excel
.
2. Научиться использовать логические функции для построения таблиц истинности.
3. Изучить описание функций с условиями и построение их графиков.
4. Создать таблицу вычисления арифметических выражений в позиционных системах счисления.
5. Изучить применение функций ЕСЛИ
, СЧЁТЕСЛИ
для обработки табличной информации.
Задание 1
Построить таблицу истинности логического выражения (ИСТИНА или ЛОЖЬ).
Порядок выполнения работы
1. Дать рабочему листу название «Лог. выражение
».
2. Заполнить ячейки рабочего листа значениями логических переменных X
, Y
, Z
.
3. Вычислить значения элементарных логических операций И
(), ИЛИ
(), НЕ
() (столбцы D
– G
).
4. В последнем столбце записать логическое выражение X
ˆ Y
ˇ (¬X
ˇ Z
) (значения двух последних столбцов должны совпадать, рис. 10).
5. Отформатировать ячейки таблицы (использовать Переносить по словам
).
Рис.
10. Лист «Лог. выражение»
Задание 2
Табулировать функцию , построить график.
Порядок выполнения работы
1. Дать рабочему листу название «Лог. функция
».
2. Используя арифметическую прогрессию, сформировать ряд значений аргумента (столбец А
).
3. В строке формул для столбца B
задать значение логической функции (использовать автозаполнение с относительной адресацией).
4. Выделить диапазон заполненных ячеек и построить плоский график в декартовой системе координат.
5. Отформатировать диаграмму, отслеживая правильность значений по оси Ох
(рис. 11).
Задание 3
Создать таблицу значений арифметических действий в восьмеричной системе счисления (использовать функции перевода чисел категории Инженерные
).
Порядок выполнения работы
1. Дать рабочему листу название «Восьмеричная
».
2. Заполнить таблицу сложения восьмеричных чисел согласно образцу (рис. 12).
3. Дополнить две строки и два столбца таблицы, выделить результаты «неправильные» в десятичной системе счисления.
4. Составить таблицу вычитания, отличную от таблицы сложения (размерность 5x5).
5. Составить таблицу умножения (размерность 4x4).
6. Записать формулы для перевода действительного числа из восьмеричной системы счисления в десятичную и обратно.
7. Отформатировать таблицы.
Задание 4
Для заданной таблицы, построенной на основе наблюдений метеостанции г. Екатеринбурга, определить:
– минимальное месячное количество осадков за три года;
– суммарное количество осадков, выпавшее за три года;
– среднемесячное количество осадков по каждому году;
– среднемесячное количество осадков по итогам трехлетних наблюдений;
– максимальное месячное количество осадков по итогам трехлетних наблюдений;
– количество засушливых месяцев за все 3 года, в которые выпало меньше 10 мм осадков;
– количество месяцев в каждом году с осадками в пределах нормы (> 20 мм; < 80 мм);
– количество месяцев в каждом году с осадками вне нормы (< 10 мм; > 100 мм);
– построить столбчатую диаграмму по данным наблюдения за осадками в течение трех лет, позволяющую провести сравнение количества осадков.
Порядок выполнения работы
1. Дать рабочему листу название «Погода
».
2. Для выполнения заданий использовать функции МИН
; МАКС
; СРЗНАЧ
; СУММ
категории Статистические.
3. Для выполнения заданий 6, 7, 8 – функцию СЧЁТЕСЛИ
, которая подсчитывает количество непустых ячеек внутри интервала, удовлетворяющих заданному критерию. При ее использовании необходимо задать: интервал поиска (диапазон ячеек) и критерий подсчета (условие). Функция СЧЁТЕСЛИ
имеет только два аргумента! Например
, = СЧЁТЕСЛИ
(А
1:С
15
;”< 25”).
4. Отформатировать таблицу по своему усмотрению.
Отчет о лабораторной работе должен содержать
электронный документ с указанием фамилии и группы студента, времени выполнения, названия, цели лабораторной работы, выполненные задания описательной части работы и задания по предложенному преподавателем варианту.
Таблица
Результаты наблюдений метеостанции г. Екатеринбурга
| Месяцы |
2002 г. |
2003 г. |
2004 г. |
Месяцы |
2002 г. |
2003 г. |
2004г. |
| Январь |
37,2 |
34,5 |
8,0 |
Июль |
57,1 |
152,9 |
50,6 |
| Февраль |
11,4 |
51,3 |
1,2 |
Август |
43,8 |
96,6 |
145,2 |
| Март |
16,5 |
20,5 |
3,8 |
Сентябрь |
85,7 |
74,8 |
79,9 |
| Апрель |
19,5 |
26,9 |
11,9 |
Октябрь |
86,0 |
14,5 |
74,9 |
| Май |
11,7 |
45,5 |
66,3 |
Ноябрь |
12,5 |
21,0 |
56,6 |
| Июнь |
129,1 |
71,5 |
60,0 |
Декабрь |
21,2 |
22,3 |
9,4 |
Задания для самостоятельного выполнения
Выполнить задания 1–3 лабораторной работы 3.
| Вар. |
Составить таблицу истинности |
Построить график функции |
Составить таблицы в СС |
| 1 |
|
|
Семеричная |
| 2 |
|
|
Девятеричная |
| 3 |
|
|
Пятеричная |
| 4 |
|
|
Шестеричная |
| 5 |
|
|
Троичная |
Лабораторная работа 4
РЕШЕНИЕ НЕЛИНЕЙНЫХ УРАВНЕНИЙ и систем уравнений ПРОГРАММНЫМИ СРЕДСТВАМИ Excel
Цели работы
1. Научиться использовать инструмент Подбор параметра
для решения нелинейных уравнений вида f
(x
) = 0.
2. Освоить программный инструмент Поиск решения
для решения систем нелинейных уравнений в среде Excel
.
3. Освоить программные средства для поиска координат и значений экстремумов функции одной переменной с помощью инструмента Поиск решения
.
Задание 1
Найти все корни уравнения .
Порядок выполнения работы
1. Дать рабочему листу название «Уравнение
».
2. Настроить текущий документ Еxcel
на вычисления с заданной точностью, открыв Настройку панели быстрого доступа
и выбрав Другие команды
.
На вкладке Формулы
задать предельное число итераций, равное 10000, и относительную погрешность, равную 0,000001.
3. Найти решение нелинейного уравнения в два этапа.
Этап – ЛОКАЛИЗАЦИЯ КОРНЕЙ
4. Создать таблицу значений функции f
(x
) для х
Î [‑2,5; 2,5], шаг изменения 0,1.
5. Построить график функции f
(x
) (тип – График
).
6. Основываясь на данных таблицы и графика, выделить интервалы, на которых функция меняет знак (существует корень).
Этап – УТОЧНЕНИЕ КОРНЕЙ
7. Задать начальные приближения к корням – точки из отрезков локализации корней, например: ‑2,2; 0 и 1,5. Ввести эти значения в ячейки F
16
, F
17
и F
18
соответственно.
8. В ячейку G
16
ввести формулу =(F
16^3-4*F
16+1)/(ABS
(F
16)+1)
и с помощью маркера заполнения размножить эту формулу в ячейки G
17
и G
18.
9. На вкладке Данные
выбрать Анализ «что-если»
, открыть диалоговое окно Подбор параметра
и заполнить его поля: в поле Установить в ячейке
ввести G
16
(в этом поле дана ссылка на ячейку, в которую введена формула, вычисляющая значение левой части уравнения); в поле Значение
ввести значение 0 (здесь указывается правая часть уравнения); в поле Изменяя значение ячейки
ввести F
16
(в этом поле дана ссылка на ячейку, отведенную под переменную). После нажатия кнопки OK
средство подбора параметра находит приближенное значение корня с заданной точностью.
10. Повторить указанные в п. 9 действия для ячеек G
17
, F
17
и G
18
, F
18
соответственно. Ячейки F
16:F
18
содержат значения корней уравнения, а ячейки G
16:G
18 –
значения функции в этих точках, близкие к нулю.
11. Оформить созданный лист заголовками и комментариями, как в образце (рис. 13).
K
Рис.
13. Лист «Уравнение»
Задание 2
Найти решение системы нелинейных уравнений на интервале [– 1,7; 1,7] с помощью Excel
, используя инструмент Поиск решения
.
Порядок выполнения работы
1. Дать рабочему листу название «Система
».
2. Преобразовать уравнения системы относительно переменной у
, получив две полуокружности в положительной и отрицательной полуплоскостях, пересекающих прямую.
3. Столбец А
(аргумент x
) заполнить с помощью арифметической прогрессии на интервале [– 1,7; 1,7] с шагом 0,1.
4. Столбцы B
,
C
,
D
озаглавить как y
1, у
2, у
3
и заполнить формулами, соответствующими полуокружностям и прямой, используя относительную адресацию и маркер заполнения.
5. Построить графики функций системы уравнений для диапазона А
2:D
36
в одной системе координат и определить интервалы локализации корней.
6. Отредактировать диаграмму, согласно образцу (рис. 15).
7. На втором этапе – УТОЧНЕНИЕ КОРНЕЙ
– найти корни системы уравнений с помощью инструмента Поиск решения.
8. Для нахождения первого корня в ячейку F
25
ввести начальное приближение для первого корня по х
. В ячейку G
25
– начальное приближение для первого корня по у
. В ячейку H
25
ввести формулу = (
F
25^2+
G
25^2–3)^2+(2*
F
25+3*
G
25–1)^2
9. Чтобы получить уточненное значение первого корня, открыть диалоговое окно Поиск решения
вкладки Данные
. В поле Изменяя ячейки
ввести диапазон ячеек F
25:
G
25
. В группе Равной
установить переключатель в положение Значению
, в поле ввода которого ввести 0
. Убедитесь, что в диалоговом окне Параметры поиска решения
снят флажок Линейная модель
. Затем нажать кнопку Выполнить
(рис. 14).
10. Вы получили приближенное решение в ячейках F
25
и G
25
, а в ячейке H
25
достаточно близкое к нулю значение и с заданной точностью приблизились к точке пересечения окружности с прямой линией. Если в ячейке H
25
будет большое число, то решение найдено неверно.
Рис.
14. Инструмент «Поиск решения»
11. Аналогично находят второе решение. Но в качестве начального приближения принимают точку, близкую по координатам ко второму корню.
12. Проверить пару значений (1,5; 0). Область притяжения какого корня вы получаете?
13. Оформить созданный документ заголовками и комментариями (рис. 15).
Задание 3
Найти экстремумы функции на интервале [‑ 2; 2] с шагом 0,1. Точность поиска e = 10-6
Порядок выполнения работы:
1. Дать рабочему листу название «Экстремум
».
2. Задав значения аргумента (столбец А
) и рассчитав значения функции (столбец В
)
, построить график функции и определить интервалы локализации экстремумов.
3. На этапе – УТОЧНЕНИЕ ЭКСТРЕМУМОВ –
найти уточненные значения координат экстремумов и значения функции в этих точках с помощью инструмента Поиск решения
.
Рис.
15. Лист «Система»
4. Для нахождения первого экстремума (максимума) в ячейку F
17
ввести начальное приближение, в ячейку G
17
ввести формулу =
F
17^3 –
F
17^2 + 4.
5. Чтобы получить уточненное значение максимума, открыть диалоговое окно Поиск решения
вкладки Данные.
В поле Установить целевую ячейку
ввести адрес ячейки, содержащей формулу – G
17
. В группе Равной
установить переключатель в положение Максимальному значению
. В поле Изменяя ячейки
ввести адрес ячейки F
17
. Затем нажать кнопку Выполнить.
6. Получено приближенное значение координаты экстремума и максимальное значение функции в ячейках F
17
и G
17.
7. Аналогично находят второй экстремум. Но при настройке диалогового окна Поиск решения
в группе Равной
установить переключатель в положение Минимальному значению.
8. Оформить созданный документ заголовками и комментариями (рис. 16).
Отчет о лабораторной работе должен содержать
электронный документ с указанием фамилии и группы студента, времени выполнения, названия, цели лабораторной работы, выполненные задания описательной части работы и задания по предложенному преподавателем варианту.
При
Если на вкладке Данные
не оказалось Поиска решения
, то его нужно подгрузить, используя Настройку панели быстрого доступа
.
Рис.
16. Лист «Экстремум»
Задания для самостоятельного выполнения
Выполнить задания 1‑3 лабораторной работы
для заданных вариантов функций
| Вар |
Решить уравнение и найти экстремум функции |
Решить систему нелинейных уравнений |
| 1 |
|
|
| 2 |
|
|
| 3 |
|
|
| 4 |
|
|
| 5 |
|
|
Лабораторная работа 5
Формулы Excel
для обработки массивов данных
Цели работы
1. Освоить функции табличного процессора для обработки матриц и решения систем линейных алгебраических уравнений.
2. Изучить процедуру применения табличных формул для обработки больших массивов данных в Excel
.
Важно !
При обработке матриц необходимо помнить два основных правила:
Перед вводом формулы
на рабочем листе выделяется область, размер которой соответствует размерности получаемой при расчетах матрицы.
Ввод матричной формулы завершается
нажатием комбинации клавиш Ctrl
+
Shift
+
Enter
, а не просто Enter
,
как при обычных вычислениях.
Задание 1
Рассчитать требуемые характеристики квадратной матрицы и определить решение системы линейных уравнений.
Порядок выполнения работы
1. Дать рабочему листу название «Матрицы
».
2. Задать матрицы A
и B
(рис. 17).
3. Вычислить определитель квадратной матрицы (числовая характеристика) с помощью функции МОПРЕД
категории Математические (например, =МОПРЕД(B
2:D
4)).
4. Вычислить обратную матрицу для заданной с помощью функции МОБР категории Математические (заметим, что матричное произведение исходной матрицы и ее обратной матрицы дает единичную матрицу).
5. Транспонировать матрицу (поменять местами строки и столбцы) с помощью функции ТРАНСП категории Ссылки и массивы.
(после транспонирования вектор-столбец преобразуется в строку, а вектор-строка – в столбец).
6. Выполнить операции сложения, вычитания, умножения и деления матрицы и числа посредством арифметических операторов: +, ‑, *, / (например, =B
2:D
4 * 0,5).
7. Операции поэлементного сложения, вычитания, умножения и деления применяют только к матрицам одинаковой размерности и их выполняют посредством арифметических операторов +, – , *, /. (например, =F
2:F
4+H
2:H
4).
8. Вычислить матричное произведение двух матриц с помощью функции МУМНОЖ категории Математические (число столбцов первой матрицы равно числу строк второй матрицы). В результате этой операции получается матрица, число строк которой равно числу строк первой умножаемой матрицы, а число столбцов – числу столбцов второй матрицы.
9. Решить систему линейных алгебраических уравнений (СЛАУ).
Рис.
17. Лист «Матрицы»
В алгебраической форме СЛАУ порядка n записывают в виде
.
Или в матричной форме: АХ = В, где А – матрица коэффициентов; В – вектор-столбец свободных членов; Х – вектор-столбец неизвестных:
Решение СЛАУ в матричном виде находят по формуле Х = А–
1
В, где А–1
– матрица, обратная А.
На рабочем листе Excel записаны матрица коэффициентов А и вектор-столбец свободных членов В. Для получения решения выделить ячейки, соответствующие вектору-столбцу из n элементов для неизвестных и записать матричную формулу решения системы (например: =МУМНОЖ(МОБР(B
2:D
4);F
2:F
4))
Задание 2
Создать таблицу расчета заработной платы, используя табличные формулы Excel
для расчета процентных отчислений и сумм к выдаче.
Порядок выполнения работы
1. Дать рабочему листу название «Ведомость
».
2. Создать таблицу ведомости по зарплате на лист Excel
(см. образец), отсортировать по алфавиту.
| № п/п |
Фамилия |
Размер оклада, руб. |
Подоходный налог (12 % от оклада) |
Отчисления в пенсионный фонд (1 % от оклада) |
Профсоюзный взнос(1 % от оклада) |
К выдаче |
| 1 |
Ушков А.С. |
9 500,00 |
||||
| 2 |
Карпов В.А. |
7 000,00 |
||||
| 3 |
Вилков И.И. |
15 000,00 |
||||
| 4 |
Абрамов С.Т. |
18 000,00 |
||||
| 5 |
Иванова С.И. |
13 500,00 |
||||
| 6 |
Кукушкина С.А. |
7 000,00 |
||||
| 7 |
Ларин В.Н. |
10 000,00 |
||||
| 8 |
Машин С.И. |
8 000,00 |
||||
| 9 |
Зуев А.И. |
15 000,00 |
||||
| 10 |
Кошкин А.Н. |
9 500,00 |
||||
| Итого |
0,00р. |
0,00р. |
0,00р. |
0,00р. |
3. Рассчитать итоговую сумму к выдаче (матричная формула).
4. В дополнительном столбце восстановить первоначальные величины окладов без вычетов налогов и взносов.
5. Удалить первую строку ведомости (сотрудник Абрамов С.Т.).
6. Добавить в таблицу ведомости нового работника – Юшкова А.Ф., размер оклада которого составляет 13570 руб. и рассчитать для него значения по всем столбцам ведомости.
Примечание. Табличные формулы редактируются не как обычные формулы рабочего листа Excel
. Ниже приведены алгоритмы редактирования табличных формул.
Редактирование формулы
1. Выделить блок с формулой.
2. Нажать клавишу F
2
.
3. Внести изменения в формулу.
4. Нажать клавиши Ctrl
+ Shift
+ Enter
.
Изменение размеров блока (удаление/вставка строк)
1. Выделить блок с табличной формулой.
2. Нажать клавишу F
2
.
3. В начало формулы добавить апостроф, формула превращается в текст.
4. Вводим текст во все ячейки с помощью клавиш Ctrl + Enter
. Табличная формула прекратила существование.
5. Очистить строку таблицы.
6. Выделить блок с табличной формулой.
7. Нажать клавишу F
2
.
8. Удалить апостроф.
9. Нажать клавиши Ctrl
+ Shift
+ Enter
.
Отчет о лабораторной работе должен содержать
электронный документ с указанием фамилии и группы студента, времени выполнения, названия, цели лабораторной работы, выполненных заданий описательной части работы и задания по предложенному преподавателем варианту.
Задания для самостоятельного выполнения
| Вар |
Решить систему |
Вычислить |
| 1 |
|
Сумму матрицы А
Произведение 1-й строки и 2-го столбца матрицы А
Транспонировать матрицы А
Произведение матрицы Х
Сумму элементов В
|
| 2 |
|
Разность 3-й строки и 1-го столбца матрицы А
Обратную матрицу для матрицы А
Транспонировать матрицы А
Сумму элементов А
Произведение матрицы А
Сумму элементов В
|
| 3 |
|
Сумму 1-й строки и 2-го столбца матрицы А
Обратную матрицу для матрицы А
Транспонировать матрицы 3Х
Сумму элементов В
Произведение матрицы А
Определитель матрицы А.
|
| 4 |
|
Сумму матрицы А
Разность 3-й строки и 2-го столбца матрицы А
Транспонировать матрицы Х
Разность В
Произведение матрицы Х
Найти матричное произведение А
|
| 5 |
|
Сумму матрицы А
Обратную матрицу для матрицы А
Транспонировать матрицы А
Произведение матрицы Х
Матричное произведение А
|
Лабораторная работа 6
ПРОГНОЗ ЗНАЧЕНИЙ ЧИСЛОВЫХ ВЕЛИЧИН С ПОМОЩЬЮ СТАТИСТИЧЕСКИХ ФУНКЦИЙ Excel
Цели работы
1. Освоить статистические функции Excel
для прогнозирования значений числовых последовательностей.
2. Научиться использовать функции Excel
для обработки экспериментальных данных при помощи трендов.
Задание 1
На основании статистических данных о численности населения России на период с 1993 по 1999 годы сделать прогноз на 2001 и 2003 г.
Порядок выполнения работы
1. Дать рабочему листу название «Прогноз
».
2. Заполнить ячейки рабочего листа Excel
следующими статистическими данными, выделив их цветом:
| A
|
B
|
C
|
D
|
E
|
F
|
| 1 |
Годы |
1993 |
1995 |
1997 |
1999 |
| 2 |
Численность населения, млн чел. |
148,3 |
147,9 |
147,5 |
146,3 |
3. Сделать предположение о численности населения России в 2001 году с помощью функции ПРЕДСКАЗ
, которая позволяет вычислить теоретическое значение зависимой переменной (в данном случае это численность населения) в фиксированной точке аргумента (т.е. для определенного года). Для этого в ячейку F
1
ввести дату – 2001, а в ячейку F
2
формулу
= ПРЕДСКАЗ(F
1;B
2:E
2;B
1:E
1)
4. Аналогичным способом предсказать численность российских граждан в 2003 году, но уже учитывая рассчитанное ранее значение в 2001 г.
5. Используя условное форматирование, выделить столбцы с минимальными и максимальными показателями, построить график.
Задание 2
С помощью функций ТЕНДЕНЦИЯ
и РОСТ
предсказать изменение численности населения на периоды с 2001 по 2015 г.г. и смоделировать на период с 1985 по 1993 г.
Порядок выполнения работы
1. Дать рабочему листу название «Тенденция
».
2. Копировать часть таблицы «Прогноз
».
3. Спрогнозировать дальнейшую динамику изменения численности на период с 2003 по 2013 г.г. при помощи функции ТЕНДЕНЦИЯ. Эта функция позволяет предсказать значения зависимой переменной для целого диапазона значений независимой переменной по линейному закону.
4. Ввести в ячейки Н
1:L
1 период времени с 2005 по 2013 г. с шагом 2 года. Выделить диапазон H
2:L
2
и ввести формулу = ТЕНДЕНЦИЯ(B
2:G
2;B
1:G
1;H
1:L
1).
Завершить нажатием комбинации клавиш Ctrl + Shi
ft + Enter
.
5. Вычислить с помощью функции ТЕНДЕНЦИЯ предположительную численность населения России на период с 1987 по 1993 г.г.
6. Аналогичным образом спрогнозировать изменение численности населения с помощью функции РОСТ по экспоненциальному закону.
7. По заданным экспериментальным точкам и полученным модельным данным построить диаграмму типа График
в виде плавной линии. Сделать вывод о поведении линейной и экспоненциальной модели изменения численности населения.
Задание
3
Построить диаграмму, содержащую заданные точки и линейный, экспоненциальный и полиномиальный тренды с соответствующими уравнениями. Оценив достоверность аппроксимации, выбрать лучший вид уравнения.
Порядок выполнения работы
1. Дать рабочему листу название «Регрессия
».
2. Заполнить ячейки исходными данными.
| x
|
1,5 |
2 |
3 |
4,5 |
5 |
6 |
7,5 |
| y
|
12 |
7 |
3 |
11 |
17 |
18 |
23 |
3. Построить диаграмму для экспериментальных точек. Тип диаграммы – График,
но точки не соединены линиями.
4. Выделив график и щелкнув на нем правой клавишей мыши, вызвать контекстное меню и выбрать в нем строку Добавить линию тренда
.
5. Добавить Линейный
тренд, Полиномиальный
2-го и 3-го порядка, Степенной
. В процессе построения тренда указать уравнение, которому подчиняется зависимая величина. Для этого при построении линии тренда на вкладке Параметры установить флажок в строке Показывать уравнение на диаграмме.
6. Выполнить для линий тренда: Поместить на диаграмму величину достоверности аппроксимации.
7. Оформить линии трендов по своему усмотрению, меняя цветовую гамму и форму маркеров.
8. Сформировать сводную таблицу для анализа качества приближения к экспериментальным точкам, указав в столбцах тип тренда, уравнение зависимости и значение достоверности аппроксимации.
9. Сделать вывод: какой из трендов дает наилучшее приближение к экспериментальным точкам?
10. Выполнить условное форматирование таблицы с учётом сделанного вывода.
Отчет о лабораторной работе должен содержать
электронный документ с указанием фамилии и группы студента, времени выполнения, названия, цели лабораторной работы, выполненных заданий описательной части работы и задания по предложенному преподавателем варианту.
Задания для самостоятельного выполнения
По следующим данным с помощью функции ПРЕДСКАЗ спрогнозировать стоимость киловатта электроэнергии до 2015 года.
| Годы |
1996 |
1997 |
1998 |
1999 |
2000 |
2001 |
2002 |
2003 |
| Стоим. кВт,коп. |
4 |
13 |
24 |
38 |
50 |
70 |
80 |
98 |
Выполнить графическую оценку данных, используя функции РОСТ
и ТЕНДЕНЦИЯ.
| Вар. |
1 |
2 |
3 |
4 |
5 |
| Годы |
1996,1998, 2000,2002 |
1999,2001, 2002,2003 |
1997,1999, 2001,2003 |
1998,1999, 2000,2001 |
1996,1997, 1998,1999 |
Построить Линейный
, Полиномиальный
2-го и 3-го порядка и Степенной
тренды для следующих экспериментальных данных. Выбрать наилучший вид зависимости.
| Вар. |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
| 1 |
2 |
5 |
6 |
8 |
3 |
2 |
1 |
3 |
5 |
7 |
8 |
13 |
| 2 |
0,2 |
0,1 |
0,3 |
0,5 |
0,6 |
0,8 |
0,9 |
1,2 |
1,3 |
1,5 |
1,6 |
1,7 |
| 3 |
22 |
25 |
31 |
35 |
37 |
45 |
38 |
36 |
35 |
28 |
27 |
25 |
| 4 |
0,2 |
0,6 |
0,8 |
0,9 |
1,2 |
1,3 |
1,5 |
1,6 |
1,7 |
1,9 |
2,0 |
2,5 |
| 5 |
37 |
37 |
25 |
21 |
9 |
10 |
20 |
24 |
37 |
41 |
45 |
46 |
Лабораторная работа 7
Технология организации и обработки списков данных в EXCEL
Цели работы
1. Изучить возможности для организации данных в Excel
в виде списка или базы данных.
2. Освоить технологию обработки cписков в Excel
.
3. Научиться извлекать определенные записи и поля из баз данных.
Планирование списка
Excel
может работать со списками данных, занимающими большой объем дискового пространства или базами данных. Введем некоторые понятия, характерные для баз данных в Excel.
База данных
– список, состоящий из одного или более столбцов.
Список
– последовательность строк рабочего листа, содержащего в столбцах подобные по типу данные.
Список включает три основных компонента:
запись
– содержать полную информацию по конкретному пункту;
поле
– составная часть записи, содержимое которого можно использовать для сортировки и поиска записей;
строка заголовка – с
трока в начале списка. Каждое поле записи помечается заголовком и используется при сортировке, извлечении данных и составлении отчетов.
В списке, реализованном в Excel
, каждый столбец
является полем
, а каждая строка
– записью
.
При построении списка следует:
– использовать различные шрифты для таблицы и строки заголовков (курсив или полужирный шрифт);
– заносить данные строго в соответствующие поля;
– использовать прописные буквы (сортировка и поиск с учетом или без учета регистра);
– при необходимости вычислений использовать формулы;
– не отделять строку заголовков от прочих данных пустой строкой;
– не разделять заголовки пустыми столбцами;
– не использовать пробел первым символом в полях (создает проблемы при поиске и сортировке);
– избегать размещения списка и прочих данных на одном листе или отделить список пустыми строками и столбцами;
– при работе с несколькими списками выделять для каждого из них отдельный лист;
– не дублировать информацию записей списка;
– предусмотреть пространство для расширения списка.
Создать и заполнить список можно:
1. С помощью команды Форма
можно автоматически создать форму, в которой выводится нужная запись и средства для редактирования данных и для добавления новой информации. Для этого варианта создания списка:
– задать строку заголовков, в каждом столбце указать название поля;
– щелкнуть мышью на любой ячейке в строке заголовков, выбрать Форма
(рис. 18);
Рис.
18. Окно формы для просмотра, редактирования и фильтрации записей по критерию
– заполнить форму, используя клавишу <Т
ab
>
для перехода между полями, щелкнуть по кнопке Добавить
.
По этой команде данные из формы будут перенесены в соответствующие поля записи (запись размещается в конце списка).
2. Создать строку заголовка, затем заполнять список, вводя вручную информацию в каждое поле. Если данные в ячейках таблицы повторяются, использовать Автозаполнение.
Выделив диапазон базы данных нажать Главная
/ Форматировать как таблицу,
в полях заголовка автоматически появятся маркеры всплывающего списка.
Задание
Создать базу данных Excel
оплаты населением города коммунальных услуг, состоящую из следующих столбцов (полей):
| № п/п
|
Характеристики данных списка
|
Имена полей
|
| 1 |
Наименование округа |
Район |
| 2 |
Дата поступления счета |
Дата |
| 3 |
Плательщик |
Квартира |
| 4 |
Категория коммунальных услуг (газ, свет, кв. м) |
Услуги |
| 5 |
Оплаченная сумма (тыс. руб.) |
Сумма |
| 6 |
Задолженность (%) |
Пени(%) |
| 7 |
Задолженность (руб.) |
Пени(руб) |
| 8 |
Всего оплачено |
Всего |
Сформировать таблицу поступлений счетов за коммунальные услуги от населения за месяц с учётом следующих условий:
- поступление счетов происходит раз в неделю;
- даты поступления счетов от районов фиксируются в произвольном порядке;
- наименования районов формируются в произвольной последовательности;
- категории услуг формируются последовательно (газ, свет, м2
);
- стоимость – случайно распределенная величина в диапазоне: за газ – [1; 10]; за свет – [10; 25]; за кв. м – [50; 500];
- пени – случайно распределенная величина от 1 до 12.
Задания разместить на 5-ти рабочих листах Excel
.
1. Базу данных в виде списка, все сведения о поступивших счетах оплаты коммунальных услуг по районам города.
2. Таблица, отфильтрованная по категории за газ.
3. Таблица, отфильтрованная по оплате за свет в первую десятидневку месяца с построением диаграммы.
4. Таблица в соответствии с условием фильтрации списка по технологии Расширенного фильтра
.
5. Таблица и диаграмма в соответствии с условием фильтрации списка по варианту самостоятельной работы.
Примечание.
Учитывать рабочие дни в соответствии с календарем.
Порядок выполнения работы
1. Создать книгу Excel
, дополните ее необходимым количеством рабочих листов.
Заполнить рабочий лист по образцу (рис. 19).
Рис.
19. Лист «База данных»
- заполнить данными столбцы A
, B
, C
(использовать автозаполнение, задав в С
3,
C
4,
C
5
соответственно газ, свет, кв. м).
Задать 50 записей;
- выполнить вычисления в столбце поля Сумма
с использованием функций:
ОКРУГЛ()
– округления чисел до указанного количества десятичных разрядов,
Например: =ОКРУГЛ(СЛУЧМЕЖДУ(1;10);2)
СЛУЧМЕЖДУ()
– генерирования случайных чисел в указанном интервале значений.
Примечание.
Результаты вычислений по формулам могут не совпадать с результатами вычислений на образце, так как использован датчик случайных чисел;
- выполнить вычисления в столбце поля Пени (%)
диапазон [1, 12].
- выполнить вычисления в столбце поля Пени (
руб):
(формула =
D
3*
E
3/100)
.
- выполнить вычисления в столбце поля Всего (=
D
3+
F
3)
.
3. Сохранить числовые значения сформированной базы данных на отдельном листе.
4. Выделив диапазон базы данных, нажать Главная /
Форматировать как таблицу
, в полях заголовка автоматически появятся маркеры всплывающего списка.
5. Просмотреть строки базы данных по одной, используя режим Формы
(рис. 18).
6. Скопировать сформированную базу данных на следующие три листа книги, озаглавить листы.
7. Произвести фильтрацию базы данных по полю Услуги
(рис. 20).
Рис.
20. Фильтрация базы данных по полю Услуги
8. Произвести фильтрацию базы данных по полям Дата
(меньше указанной даты) и Услуги
(Свет).
9. Построить гистограмму для списка значений отфильтрованных по критериям двух полей – Дата
и Услуги
.
10. Выполнить фильтрацию данных списка на отдельном листе с помощью Расширенного фильтра
.
Расширенный фильтр распознает три специальных диапазона: «База данных», «Критерии», «Извлечь»
, которые можно разместить на одном листе, разделив диапазоны пустыми строками и столбцами:
- скопировать диапазон «База данных»
;
- выделить заголовки полей базы данных, скопировать в область критериев и в область отфильтрованных результатов, ввести названия областей соответственно «Критерии»
и «Извлечь»
;
Рис.
21. Фильтрация при помощи Расширенного фильтра
- оформить область критериев, задав номер квартиры и выбранную услугу (рис. 21);
Примечание.
Несколько критериев одной строки связаны логической функцией И
. Критерии разных строк связаны логической функцией ИЛИ
(см. рис. 21)
.
- активизировать расширенный фильтр Данные
/ Фильтр
/ Дополнительно
/ Расширенный фильтр.
Появится окно Расширенный фильтр
(рис. 22). Заполнить поля окна Расширенного фильтра
для трех диапазонов «База данных», «Критерии», «Извлечь»
выделением, предварительно щелкнув на красной стрелке поля справа. Установить Фильтровать список на месте
.
11. Сохранить книгу.
Отчет о лабораторной работе должен содержать
электронный документ с указанием фамилии и группы студента, времени выполнения, названия, цели лабораторной работы, выполненные задания описательной части работы и задания по предложенному преподавателем варианту.
Рис.
22. Диалоговое окно расширенного фильтра
Задания для самостоятельного выполнения
Сформировать таблицу – результат фильтрации из исходной таблицы базы данных по условию варианта (1–5).
| Вар. |
Условие фильтрации |
|||
| 1 |
Счета для газа во 2-ю декаду |
Сумма счета в диапазоне 5¸10 за газ |
Счета ЮВАО за свет |
Пени за газ не превышают 5 % |
| 2 |
Счета для света в 3-ю декаду |
Сумма счета в диапазоне 15¸25 за свет |
Счета ВАО за газ |
Сумма счета в диапазоне 25¸100 |
| 3 |
Счета для света в 1-ю декаду |
Сумма счета в диапазоне 50¸200 за кв.м. |
Счета САО за свет |
Пени за свет не превышают 5 % |
| 4 |
Счета для кв.м. в 1-ю неделю |
Сумма счета за кв.м. в диапазоне 200¸500 |
Счета ЮАО за кв.м. |
Сумма счета в диапазоне 50¸120 |
| 5 |
Счета для кв.м в 3-ю неделю |
Сумма счета в диапазоне 15¸20 за свет |
Счета ЗАО за газ |
Пени за газ менее 5 % |
Приложение 1
Примерный вариант
Контрольной работы Excel
1. Приведите пример использования с описанием пяти арифметических функций.
_____________________________________________________
2. Запишите логическую формулу в Excel
_____________________________________________________
3. Постройте график функции
| Запишите логическую формулу _________________________________________________________________ |
|
4. Найти максимумы функции , решение показать на графике функции.
5. Решить уравнение .
Построить график, записать формулу в относительных ссылках, вычислить корни уравнения.
6. Построить графики функций и решить систему уравнений .
7. Построить полярный график, учитывая период функции .
8. Построить график поверхности .
1. Расчет зарплаты
. Для заданной ведомости произвести вставку удаления (разместить по алфавиту), рассчитать значения по всем столбцам ведомости.
2. Построение трендов
. Построить тренды для заданных данных, сравнить ошибку аппроксимации.
3.
| |
Обработка списков данных
. Создать таблицу базы данных по заданию преподавателя, скопировать на свободный лист. Сформировать таблицу как результат фильтрации данных из исходной таблицы базы данных по заданным фильтрам. Построить диаграмму заданного типа результатов для указанных столбцов.
Приложение 2
В информационных технологиях презентация – это документ, созданный в программе Microsoft PowerPoint
.
Учитывая освоение интерфейса Microsoft
Office
при изучении курсов Word
и Excel
, а также наличие у студентов навыков работы с презентациями, изучение приложения Power
Point
относится к самостоятельной работе.
В самостоятельной работе следует обратить внимание на такие моменты создания презентаций как организация навигационных схем перемещения по слайдам, вставка и управление анимационными эффектами, вставка звуковых и видео фрагментов.
Домашнее задание power
Point
Создать презентацию по предложенной преподавателем теме, рассмотрев следующие этапы.
Планирование.
Определение темы презентации, её типа, а также аудитории, на которую ориентирована мультимедийная презентация.
Проектирование.
Выбор навигационной схемы. Разработка дизайна слайдов.
В презентациях, содержащих более двух слайдов, необходима навигация (перемещение) по слайдам. Можно выделить два типа навигационных схем: линейную
(последовательное перемещение); разветвленную
,
которая позволяет выбирать по своему усмотрению последовательность перемещения по слайдам. При этом смена слайдов организуется при помощи управляющих кнопок.
Информационное наполнение.
Подготовка текстового и иллюстративного материала для наполнения слайдов (аудио, видео, графические файлы, ссылки интернет, документы пакета MS
Office
и др.) Подготовка речевого и видео сопровождения.
Программная реализация
. Заполнение слайдов информационным материалом. Цветовое оформление. Настройка мультимедийных эффектов. Установка гиперссылок на элементы меню в соответствии с навигационной структурой. Установка гиперссылок на элементы меню для выхода в интернет и подключения внешних программ.
Особенности вставки звука и видео
.
Звуковые файлы большого объёма и любые видеофрагменты не внедряются в презентацию, таким образом не переносятся на другой компьютер.
Аудио- и видео файлы следует скопировать в папку с презентацией, а потом делать на неё ссылки.
Анимированные изображения (в формате GIF) вставляются в презентацию как рисунки, внедряются в неё и демонстрируются, как обычные рисунки.
Тестирование
. Устранение ошибок в текстовом и иллюстративном материалах. Проверка гиперссылок.
Эксплуатация.
Демонстрация на конференциях, выставках, при защите реферата, доклада и др.
Сопровождение.
Совершенствование презентации.
Учебное издание
Мокрова Наталия Владиславовна
ТАБЛИЧНЫЙ
ПРОЦЕССОР
MICROSOFT OFFICE EXCEL 2007
Подписано в печать 15.11.2011. Формат бум. 60 x 84 1
/16
.
Объем 2,79 усл. п. л. Уч-изд. л. 3,0. Тираж 100 экз. Зак. 2/2012
| |