Численное дифференцирование
Известно, что численными приближенными методами производная функции в заданной точке может быть вычислена с использованием конечных разностей. Выражение, записанное в конечных разностях, для вычисления производной функции одного переменного имеет вид:
Для вычисления производной в Excel будем использовать приведенную зависимость.
Рассмотрим методику вычисления производной на примере упражнения.
Упражнение 1
Допустим требуется найти производную функции Y= 2x3 + x2 в точке x=3. Производная, вычисленная аналитическим методом, равна 60.
Для вычисления производной выполните следующие действия:
 табулируйте заданную функцию в окрестности точки х=3 с достаточно малым шагом, например 0,001 (см рис.)
в ячейку С2 введите формулу вычисления производной. Здесь ячейка В2 содержит значение хк+1, ячейка А2 - хк.
буксировкой скопируйте формулу до строки 7, получим значения производных в точках табуляции аргумента.
Для значения х =3 производная функции равна значению 60,019, что близко к значению, вычисленному аналитически.
Численное вычисление определенных интегралов
 Для численного вычисления определенного интеграла методом трапеций используется формула:
Методику вычисления определенного интеграла в Excel с использованием приведенной формулы рассмотрим на примере.
Упражнение 2
Пусть требуется вычислить определенный интеграл
Величина интеграла, вычисленная аналитически равна 9. Для численного вычисления величины интеграла с использованием приведенной формулы выполните следующие действия:
табулируйте подинтегральную функцию в диапазоне изменения значений аргумента 0 – 3 (см. рис.).
в ячейку С3 введите формулу =(A3-A2)*B2+(A3-A2)*(B3-B2)/2+C2, которая реализует подинтегральную функцию.
 Скопируйте буксировкой формулу, записанную в ячейке С3 до значения аргумента х = 3. Вычисленное значение в ячейке С17 и будет величиной заданного интеграла - 9.
Практичская работа № 8.
Тема: MS Excel: относительная и абсолютная адресация.
Цель: научиться использовать абсолютную адресацию при заполнении таблиц.
Задачи:
закрепить навыки по заполнению и форматированию таблиц
Изучить понятие абсолютной ссылки
Отработать навык вставки абсолютной функции
Ход работы:
Составьте таблицу распределения дивидендов (выплаты доходов акционерам по итогам работы) в акционерном обществе в соответствии с количеством акций каждого акционера.
Сначала нужно определить исходные данные задачи: величину распределяемой суммы дохода (число), фамилии работников (текст), количество акций у каждого акционера (целое число). В качестве рассчитываемых данных в таблице будет суммарное значение числа всех акций (число) и сумма выплаты каждому акционеру (число).
Запустите Excel, переименуйте Лист1 в Дивиденды, опишите на нем структуру таблицы и введите исходные данные следующим образом
В ячейку В8 введите формулу суммы диапазона В4:В7; для этого, выделив диапазон В4:В7, щелкните кнопку Автосумма на панели инструментов Стандартная.
|
А
|
В
|
С
|
1
|
Распределение дивидендов в акционерном обществе
|
2
|
Сумма доходов
|
|
|
3
|
Фамилия
|
Количество акций
|
Выплата
|
4
|
Иванов
|
5
|
|
5
|
Петров
|
6
|
|
6
|
Сидоров
|
8
|
|
7
|
Костин
|
4
|
|
8
|
Итого
|
Введите в ячейку В2 величину распределяемого дохода, например, 10000.
Введите в ячейку С4 формулу расчета выплаты =$В$2/$В$8*В4. (Выплата каждому акционеру равна частному от всей суммы доходов акционерного общества и количества всех акций, умноженному на количество акций данного акционера.) Обратите внимание, что адреса ячеек В2 и В8 записаны в абсолютной форме, т.е. они не изменятся при копировании формулы из ячейки С4 в С5:С7.
Примечание. Для создания абсолютной ссылки на ячейки В2, В8 нужно при вводе формулы щелкнуть на данной ссылке клавишей F4.
Задайте финансовый формат для значений данных в ячейках С4:С7, для чего, выделив этот диапазон, выберите в меню Формат опцию ячейки, а затем на вкладке Число окна Формат ячеек выберите вариант числового формата: Финансовый, после чего укажите число десятичных знаков 2, а в поле Обозначение выберите р. и щелкните кнопку «ОК».
Измените ширину столбца Е, чтобы в нем правильно отображались выплаты в финансовом формате.
Проверьте действие таблицы, изменяя данные в ячейках В2,В4:В7.
Постройте диаграмму, отображающую долю выплат каждому акционеру от общей суммы доходов. Для этого выделите диапазон ячеек А4:С7 и щелкните кнопку «Мастер диаграмм» в панели инструментов Стандартная. В начале построения диаграммы выберите тип Разрезная круговая диаграмма и щелкните кнопку «Далее». На этапе определения источника данных диаграммы выберите для отображения в диаграмме данные из рядов в столбцах, уточните диапазон. На вкладке Ряд в поле Имя задайте диапазон данных, отображающих название Лист1!$А$1. Щелкнув кнопку «Далее», на вкладке Подписи данных в поле Подписи значений выберите опцию доля. Щелкнув кнопку «Далее», выберите место размещения диаграммы на отдельном листе с названием Диаграмма1 и щелкните кнопку «Готово».
Измените формат области диаграммы; для этого, щелкнув правой кнопкой мыши на диаграмме, в контекстном меню выберите команду Формат области диаграммы. Выбрав вкладку Вид, щелкните кнопку «Способы заливки», затем в окне Заливка выберите вкладку Текстура, затем щелкните кнопку с текстурой (например, белый мрамор). Щелкнув кнопку «ОК», закройте окно Заливка, а затем щелкните «ОК» для закрытия окна Формат области диаграммы. Просмотрите вид полученной диаграммы на отдельном листе Диаграмма1.
Практическая работа №9
Тема: MS Excel как база данных
Цель занятия: Научиться создавать базы данных в MS Excel. Изучить основные способы обработки данных в базе данных MS Excel.
Ход работы:
1. Запустите Excel и изучите справочную информацию об использовании списков, их сортировке и фильтрации. Для этого, вызвав справку Excel, на вкладке Содержание нужно выбрать раздел Работа с данными на листах, тему Отбор и сортировка.
2. Создайте на первом листе таблицу Список участников соревнований. Вначале нужно определить исходные данные задачи: фамилии и имена спортсменов (текст), вес (число), название региона (текст), набранные в ходе состязаний очки и место, занятое на соревновании (числа).
3. Опишите структуру таблицы и заполните ее данными, например, как показано в таблице.
|
А
|
В
|
С
|
D
|
Е
|
F
|
1
|
Список участников соревнований
|
2
|
Фамилия
|
Имя
|
Вес
|
Регион
|
Очки
|
Место
|
3
|
Власов
|
Василий
|
97
|
Москва
|
6
|
1
|
4
|
Знаков
|
Евгений
|
54
|
Самара
|
8
|
1
|
5
|
Зуйков
|
Владимир
|
76
|
Ульяновск
|
5
|
1
|
6
|
Кадышев
|
Евгений
|
130
|
Самара
|
3
|
3
|
7
|
Карелин
|
Александр
|
130
|
Новосибирск
|
5
|
1
|
8
|
Сайфутдинов
|
Джалиль
|
54
|
Курган
|
7
|
1
|
9
|
Сердюков
|
Александр
|
130
|
Воронеж
|
4
|
2
|
10
|
Чеглаков
|
Алексей
|
97
|
Пермь
|
5
|
2
|
11
|
Шевченко
|
Алексей
|
76
|
Ростов
|
6
|
1
|
4. Отсортируйте список участников, упорядочив его в порядке возрастания весовых категорий. Для этого установите курсор в столбец С и щелкните кнопку «Сортировка» по возрастанию в панели инструментов Стандартная. После этого порядок размещения записей о спортсменах в таблице изменится.
5. Найдите участников соревнований из Самары. Для этого в меню Правка выберите команду Найти, затем в окне Найти и заменить в поле Найти введите образ поиска «Самара», щелкнув кнопку «Параметры», задайте опции поиска: не учитывать регистр, просматривать по строкам. Для начала поиска щелкните кнопку «Найти далее». Курсор выделяет ячейку с найденным текстом. Щелчок кнопки «Найти далее» переводит курсор на следующую ячейку таблицы, удовлетворяющую условиям поиска. Если данных нет, то на экране будет выведено сообщение об этом. Закройте окно поиска.
6. Отфильтруйте список спортсменов и просмотрите участников состязаний в весовой категории до 130 кг. Для этого выберите команду Фильтр в меню Данные, а затем выберите опцию Автофильтр. После этого в правой части заголовков всех граф таблицы появятся стрелки. Выбрав графу Вес, нажмите кнопку со стрелкой вниз, выберите значение в списке, например 130.
После этого в окне Excel будет выведен список участников, удовлетворяющий заданному фильтру.
|
А
|
В
|
С
|
D
|
Е
|
F
|
1
|
Список участников соревнований
|
|
|
|
2
|
Фамилия
|
Имя
|
Вес
|
Регион
|
Очки
|
Место
|
9
|
Кадышев
|
Евгений
|
130
|
Самара
|
3
|
3
|
10
|
Карелин
|
Александр
|
130
|
Новосибирск
|
5
|
1
|
11
|
Сердюков
|
Александр
|
130
|
Воронеж
|
4
|
2
|
Можно применить фильтр к уже отфильтрованному списку. Щелкнув стрелку в графе Имя, установите фильтр Александр. После этого в таблице будут отображаться только записи о спортсменах, у которых Вес=130 и Имя=«Александр».
7. Удалите фильтр Имя=«Александр»; для этого нажмите кнопку со стрелкой в графе Имя, а затем выберите из развернувшегося списка Все.
Чтобы удалить фильтры для всех столбцов списка, нужно выбрать пункт Фильтр в меню Данные, а затем - команду Отобразить все. Для удаления автофильтра выберите пункт Фильтр в меню Данные, а затем - команду Автофильтр.
8. Для просмотра записей таблицы с использованием формы выберите команду Форма в меню Данные. После этого на экране раскроется форма с отображением записей таблицы на листе 1,как показано на рис. 4.
Рис. 4. Форма с отображением записей таблицы
Для получения подсказки о назначении какого-либо параметра формы нажмите кнопку «?» в правом верхнем углу окна и щелкните интересующий вас параметр.
Сначала в форме отображается информация из первой строки таблицы. В окне формы над кнопкой «Добавить» отображается информация 1 из 9, что означает: в форме отображается текущая запись с номером 1, всего записей в таблице 9. Для просмотра следующей записи щелкните кнопку «Далее», предыдущей записи -«Назад».
9. Для того чтобы задать фильтр отбора данных, щелкните кнопку «Критерии», затем в поле Регион введите значение «Самара» и нажмите клавишу Enter.
После этого в форме будут отображаться только данные об участниках из Самары. Для просмотра остальных записей, удовлетворяющих условиям отбора Регион=«Самара» используйте кнопки «Далее» и «Назад».
10. Для отмены фильтрации данных следует щелкнуть кнопку«Критерии» и очистить поле, в котором задано значение фильтра отбора данных (в данном примере в поле Регион удалить значение «Самара»).
11. Для вставки записи в таблицу укажите ячейку списка, начиная с которой следует добавлять записи (например, 5), затем щелкните кнопку «Добавить» и введите значения полей новой записи, используя клавишу Tab для перемещения к следующему полю. Задайте следующие данные новой записи: Петров, Василий, 97, Тула, 5, 2. Завершите ввод данных, нажав клавишу Enter. Обратите внимание, что количество записей в таблице, отображаемое над кнопкой «Добавить», увеличилось на 1.
12. Измените запись об участнике из Ульяновска, для чего, щелкнув кнопку Критерии, в поле Регион введите значение «Ульяновск» и нажмите Enter, затем измените данные в записи (например, измените фамилию «Зуйков» на «Зайцев»). После изменения данных нажмите клавишу Enter для обновления текущей записи.
Примечание. Если поле списка содержит формулу, то в форме выводится ее результат. Изменять это поле в форме нельзя.
13. Для удаления записи об участнике из Ульяновска установите ее текущей, а затем щелкните кнопку «Удалить». Подтвердите удаление записи, щелкнув кнопку «ОК» на панели сообщения Microsoft Excel.
Примечание. Чтобы отменить добавление записи, нажмите кнопку «Вернуть» перед нажатием клавиши Enter или кнопки «Закрыть».
14. Нажмите кнопку «Закрыть» для выхода из формы и просмотрите изменения, внесенные в таблицу в режиме формы.
Создание базы данных в MS Excel. Добавление, изменение и поиск данных в базе данных MS Excel с помощью форм. Функции работы с базой данных. Создание базы данных в MS Excel.
База данных — интервал ячеек, формирующих список или базу данных. База данных представляет собой список связанных данных, в котором строки данных являются записями, а столбцы — полями. Верхняя строка списка содержит названия всех столбцов.
• Создайте новую рабочую книгу.
• Лист 1 переименуйте в База данных.
• На листе База данных Создайте таблицу следующего вида:
A
B
C
D
E
1
Дерево
Высота
Возраст
Урожай
Доход
2
3
4
Дерево
Высота
Возраст
Урожай
Доход
5
Яблоко
18
20
14
105,00 грн.
6
Груша
12
12
10
96,00 грн.
7
Вишня
13
14
9
105,00 грн.
8
Яблоко
14
15
10
75,00 грн.
9
Груша
9
8
8
76,80 грн.
10
Яблоко
8
9
6
45,00 грн.
11
Яблоко
9
6,4
5
45,04 грн.
12
Груша
10
15
14
96,00 грн.
13
Вишня
11
8
10
105,00 грн.
14
Яблоко
13
9
9
75,00 грн.
15
Груша
14
20
10
76,80 грн.
16
Яблоко
9
12
14
45,00 грн.
17
Яблоко
8
14
10
96,00 грн.
18
Груша
16
15
9
105,00 грн.
19
Вишня
13
8
10
75,00 грн.
20
Яблоко
14
9
8
76,80 грн.
21
Груша
9
20
14
45,00 грн.
22
Яблоко
8
12
10
45,04 грн.
2. Добавление, изменение и поиск данных в базе данных MS Excel с помощью форм.
Открытие формы
• Выделите диапазон ячеек А4:Е22
• Выполните Данные→Форма.
• В окне формы с помощью колеса прокрутки можно просматривать данные внесенные в базу данных.
Добавления данных
• В окне формы нажмите кнопку Добавить
• Заполните поле Дерево – Абрикос; Высота – 12;Возраст – 16; Урожай – 100; Доход – 120.
• Нажмите Enter.
Задание: Добавьте произвольно 10 новых записей в базу данных.
Удаление данных
Для удаления какой либо записи найдите ее в списке данных и нажмите кнопку Удалить.
Поиск данных в базе данных
Найдем все деревья названия которых начинаются на букву Я приносящих доход больше 10 гривен.
В окне формы нажмите кнопку
Критерии
В поле
Дерево
Введите Я* В поле
Доход
Введите >10 Нажмите
Enter.
Для просмотра других результатов отбора данных по заданному критерию нажмите кнопку
Далее.
Задание: Найдите все данные о деревьях, урожай которых превышает 20, а название заканчивается на «А».
3.Функции работы с базой данных.
Пример1: Определим суммарную высоту всех яблок.
В ячейку А2 введите Яблоко Выделите ячейку G2 Выполните
Вставка→Функция.
Выберите категорию функций
Работа с базой данных
и функцию
БДСУММ.
Нажмите
ОК.
В поле
База данных
Укажите диапазон ячеек содержащих данные и поля вашей базы данных без диапазона критериев, например А4:Е23. В графе
Поле
Введите адрес ячейки с подзаголовком поля
Высота,
Например В4. В поле
Критерий
Введите диапазон ячеек А1:А2. Нажмите
ОК.
Задание: Определите суммарный доход со всех Вишен.
Пример2: Определим сколько всего имеется деревьев яблонь и вишен.
В ячейку А3 введите
Вишня,
А2 –
Яблоко
Выделите ячейку I2 Выполните
Вставка→Функция.
Выберите категорию функций
Работа с базой данных
и функцию
БСЧЁТ.
Нажмите
ОК.
В поле
База данных
Укажите диапазон ячеек содержащих данные и поля вашей базы данных без диапазона критериев, например А4:Е23. В графе
Поле
Введите адрес ячейки с подзаголовком поля
Высота,
Например В4. В поле
Критерий
Введите диапазон ячеек А1:А3. Нажмите
ОК.
Задание: Подсчитайте сколько всего в нашей базе данных деревьев превышающих высоту 11 метров.
Пример3: Определим средний доход со всех деревьев высотой больше 12
В ячейку В2 введите >12 Выделите ячейку K2 Выполните
Вставка→Функция.
Выберите категорию функций
Работа с базой данных
и функцию
ДСРЗНАЧ.
Нажмите
ОК.
В поле
База данных
Укажите диапазон ячеек содержащих данные и поля вашей базы данных без диапазона критериев, например А4:Е23. В графе
Поле
Введите адрес ячейки с подзаголовком поля
Доход,
Например Е4 В поле
Критерий
Введите диапазон ячеек B1:B2. Нажмите
ОК.
Задание: Определите среднюю высоту деревьев старше 10 лет
Контрольные вопросы:
Что такое база данных Что такое поле? Для чего необходим диапазон критериев? Как добавить новую запись в базу данных? Как найти нужную запись в базе данных по заданному критерию? Какие функции работы с базами данных вы знаете?
Довольно часто возникает необходимость хранить и обрабатывать данные представленные в виде таблиц.
Информация, хранящаяся в таблицах, организована в виде строк и столбцов. Каждая строка таблицы, называемая записью, содержит данные об одном объекте. В столбце, называемом полем, содержатся сведения о каком-либо свойстве всех объектов хранящихся в таблице.
Т.о., можно сказать, что База данных (БД)- это совокупность различных записей, обладающих определенными свойствами.
В первой строке любой базы данных обязательно должны быть указаны имена полей. Максимальный размер базы данных в MS Excel определяется возможностями версии Excel (число строк и число столбцов в листе).
БД может быть сформирована на одном листе. Один лист может содержать несколько БД, но активной и доступной для выполнения различных операций в данный момент времени может быть только одна из них.
Рассмотрим процесс построения и обработки базы данных на следующем примере.
ПРИМЕР 9.1.
Наименование товара
|
Дата
|
Поставщик
|
Город
|
Закупочная цена (грн.)
|
Отпускная цена
|
Транспортные расходы
|
Количество
|
Прибыль
|
Колбаса
|
02.10.03
|
Колбико
|
Макеевка
|
15.80
|
18.00
|
20 грн.
|
100
|
|
Творог
|
05.10.03
|
Ромол
|
Харьков
|
2.20
|
2.50
|
45 грн..
|
500
|
|
Сформируем БД на первом листе MS Excel, после чего лист переименуем (рис. 9.1).
Сформируем поле Прибыль. Для этого в ячейку I2 введем формулу:
I2=(F2-E2)*H2-G2
С помощью маркера заполнения скопируем эту формулу в ячейки I2:I18.
В результате лист примет вид изображенный на рис. 9.2.
|
Рис. 9.1. База данных "Поставщики товара"
|
|
Рис. 9.2. Формирование поля "Прибыль"
| |
