Создание связи между рабочими книгами

Создание связи между рабочими книгами аналогично связыванию рабочих листов. При создании связи между листами после названия листа необходимо ставить знак «!». При связи рабочих книг в целевой ячейке в формуле название книги должно быть указано в квадратных скобках. Весь «путь» к ячейке в апострофах заканчивается знаком «!». Например:

'[Книга2.xls]М опт'!$C$2,

где Книга2.xls – название книги, М опт - название листа в данной книге, $C$2 - исходная ячейка.

 

Разрыв связи

· Выберите ячейку, содержащую связь, которую хотите разорвать.

· Нажмите кнопку Копировать на панели Стандартная.

· Выберите из меню Правка команду Специальная вставка.

· Установите переключатель Значения, нажмите Ок(рис.24).

 

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

Анализ данных с помощью сводной таблицы

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

 

Задание 1

1. Создайте исходную таблицу на листе Исходные данные книги Сводная таблица (рис. 25).

 

Создание сводной таблицы

Создание Сводной таблицы с помощью Мастера Сводной таблицы состоит из четырех основных этапов (шагов).

 

Этап 1

· Выделите любую ячейку в диапазоне списка.

· Выберите из меню Данные команду Сводная таблица.

· Если используется диапазон списка, установите переключатель в списке или базе данных Microsoft Excel (Рис.25).

· Нажмите кнопку Далее.

Этап 2

· Укажите диапазон, содержащий исходные данные.

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

· Нажмите кнопку Далее.

Этап 3

· Переместите необходимые заголовки в поля Строка, Столбец и Данные.

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

2.
Создайте все представленные варианты сводных таблиц (рис. 26, 27)

Этап 4

· Задайте положение рабочего листа, на котором будет размещена таблица - на новый лист (или на существующий лист). Нажмите кнопку Готово.

 

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

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

При изменении исходных данных для отображения их в сводной таблице:

· Нажмите любую ячейку в сводной таблице.

· На панели Сводная таблица нажмите кнопку Обновить данные.

Чтобы добавить (удалить) поле в сводной таблице:

· Нажмите кнопку Мастер Сводных таблиц на панели Сводная таблица.

· Добавьте новые (или переместите старые) заголовки в поля Строка, Столбец и Данные.

· Нажмите кнопку Готово.

 

ЛАБОРАТОРНАЯ РАБОТА 11

(Книга Подбор параметров. Лист 1)

Нахождение корней уравнения с помощью подбора параметра

Задание 1

Выполните пример, рассмотренный в данном разделе.

Рассмотрим пример нахождения всех корней уравнения

Отметим, что у полинома третьей степени имеется не более трех вещественных корней.

Для нахождения корней их первоначально надо локализовать, то есть найти интервалы, на которых эти корни существуют. Такими интервалами локализации корней служат промежутки, на концах которых функция имеет противоположные знаки. С целью нахождения интервалов, на концах которых функция изменяет знак, необходимо построить ее график или ее протабулировать. Протабулируем, например, наш полином на интервале [-1; 1] с шагом 0,2. Результат табуляции приведен на риc. 1, где в ячейку В2 введена формула:

=А2^3-0,01*А2^2-0,7044*A2+0,139104

Из рис. 28 видно, что полином меняет знак на интервалах [-1; -0,8], [0,2; 0,4] и [0,6; 0,8], и поэтому на каждом из этих интервалов имеется свой корень. Так как полином третьей степени имеет не более трех корней, то они все и локализованы.

Прежде чем приступить к нахождению корней при помощи подбора параметра, необходимо выполнить некоторую подготовительную работу:

· Корень при помощи подбора параметра находится методом последовательных приближений. Установите точность, с которой находится корень. Для этого выберите команду Сервис из меню Параметры и на вкладке Вычисления диалогового окна Параметры задайте относительную погрешность и предельное число итераций равными 0,00001 и 1000, соответственно.

· Отведите на рабочем листе ячейку, например, С2, под искомый корень. Эта ячейка будет играть двойную роль. До применения подбора параметра в ней находится начальное приближение к корню уравнения, а после применения – найденное приближенное значение корня.

· Корень при помощи подбора параметра находится методом последовательных приближений. Поэтому в ячейку С2 надо ввести значение, являющееся приближением к искомому корню. В нашем случае, первым отрезком локализации корня является [-1; -0,8]. Следовательно, за начальное приближение к корню можно взять среднюю точку этого отрезка –0,9.

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

=С2^3-0,01*С2^2-0,7044*С2+0,139104.

Аналогично надо поступить с двумя другими искомыми корнями.

Теперь можно переходить к нахождению первого корня уравнения.

· Выберите команды Сервис, Подбор параметра. На экране отобразится диалоговое окно Подбор параметра.

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

· В поле Значение введите 0. Здесь указывается значение из правой части уравнения.

· В поле Изменяя значение ячейки введите С2. В данном поле приводится ссылка на ячейку, отведенную под переменную.

· Нажмите кнопку OK.

На экране отобразится окно Результат подбора параметра с результатами работы команды Подбор параметра. Кроме того, найденное приближенное значение корня будет помещено в ячейку С2. В данном случае оно равно -0,919999 (рис. 29).

Аналогично в ячейках С3 и С4 найдите два оставшихся корня.

Задание 2

Выполнить контрольную работу 10 на листе К.р. № 10 в книге Подбор параметров.

 

КОНТРОЛЬНАЯ РАБОТА 10

Найдите все корни уравнения

Вариант 1
Вариант 2
Вариант 3
Вариант 4
Вариант 5
Вариант 6
Вариант 7
Вариант 8
Вариант 9
Вариант 10
Вариант 11
Вариант 12
Вариант 13
Вариант 14
Вариант 15

 

Библиографический список

1. Гарнаев, А. Ю. Excel, VBA, Internet в экономике и финансах / А. Ю. Гарнаев. – СПб.: БХВ-Петербург, 2002. – 816 с.

2. Информатика: Базовый курс / под ред. С.В. Симоновича. – СПб.: Питер, 2001. – 640 с.

3. Информатика: Практикум по технологии работы на компьютере / под ред. Н.В. Макаровой. – 2-е изд. – М.: Финансы и статистика, 2003. – 256 с.

4. Карпов, Б. Microsoft Excel 2002: справочник / Б. Карпов. – СПб.: Питер, 2002. – 544 с.

5. Лавренов, С.М. Сборник примеров и задач / С. М. Лавренов. – М.: Финансы и статистика, 2000.

6. Могилев, А.В. Практикум по информатике / А.В. Могилев, Н.И. Пак, Е.К. Хеннер. – М.: Издательский центр «Академия», 2001. – 608 с.

7. Microsoft Excel - 97. Методические указания к лабораторным работам /сост. И. Галайко. – Красноярск: КрасГАСА.

Оглавление

ЛАБОРАТОРНАЯ РАБОТА 1. 3

Ввод данных. 3

ЛАБОРАТОРНАЯ РАБОТА 2. 8

Создание таблицы.. 8

ЛАБОРАТОРНАЯ РАБОТА 3. 11

Некоторые приемы форматирования и редактирование таблиц. Автоматическое форматирование данных. 11

КОНТРОЛЬНАЯ РАБОТА 1. 16

ЛАБОРАТОРНАЯ РАБОТА 4. 19

Абсолютная и относительная адресация ячеек. 19

КОНТРОЛЬНАЯ РАБОТА 2. 23

ЛАБОРАТОРНАЯ РАБОТА 5. 25

Функции в формуле. 25

КОНТРОЛЬНАЯ РАБОТА 3. 28

ЛАБОРАТОРНАЯ РАБОТА 6. 33

Примеры использования некоторых функций. 33

КОНТРОЛЬНАЯ РАБОТА 4. 36

ЛАБОРАТОРНАЯ РАБОТА 7. 39

Составление сложных формул. 39

КОНТРОЛЬНАЯ РАБОТА 5. 41

ЛАБОРАТОРНАЯ РАБОТА №8. 50

Создание графиков и диаграмм.. 50

КОНТРОЛЬНАЯ РАБОТА 6. 56

ЛАБОРАТОРНАЯ РАБОТА № 9. 57

Сортировка и фильтрация данных списка. 57

КОНТРОЛЬНАЯ РАБОТА 9. 61

ЛАБОРАТОРНАЯ РАБОТА 10. 62

Создание связей и сводных таблиц. 62

ЛАБОРАТОРНАЯ РАБОТА 11. 67

Нахождение корней уравнения с помощью подбора параметра. 67

КОНТРОЛЬНАЯ РАБОТА 10. 70

 

Оглавление

Ввод данных. 3

Создание таблицы.. 8