- •1. Основные правила работы в excel
- •Список условных обозначений
- •1.2. Основные понятия Excel
- •1.3. Выделение блока ячеек
- •1.4. Ввод текстов
- •1.5. Ввод чисел
- •1.6. Ввод стандартных списков.
- •1.7. Ввод формул
- •1.8. Работа с Мастером функций
- •1.9. Присваивание имён ячейкам и блокам
- •1.10. Использование подписей данных
- •1.11. Правка информации
- •1.12. Копирование и перемещение информации
- •1.13. Команды форматирования
- •Рисование графиков и диаграмм
- •Первый шаг Мастера диаграмм: тип диаграммы
- •2.2. Второй шаг Мастера диаграмм: источник данных диаграммы
- •Третий шаг Мастера диаграмм: параметры диаграммы
- •Четвёртый шаг Мастера диаграмм: размещение диаграммы
- •Исправление диаграммы
- •Построение линии тренда
- •Расчётные алгоритмы в excel
- •3.1. Расчёт таблицы значений функции от одного аргумента
- •3.2. Расчёт таблицы значений функции от двух аргументов
- •3.3. Использование функции если для анализа информации
- •3.4. Оценка определённого интеграла
- •3.5. Нахождение корня уравнения
- •3.6. Решение систем уравнений
- •3.7. Решение задач оптимизации
- •4. Использование visual basic в excel
- •4.1. Работа с макросами
- •5. Задания для самостоятельной работы
- •Литература
- •Содержание
Расчётные алгоритмы в excel
Большинство основных вычислительных алгоритмов в Excel оформлены в виде стандартных функций и вызываются с помощью программы Мастер функций (см. п. 1.8). Самые популярные из них:
ЕСЛИ – позволяет предусмотреть разные варианты заполнения ячейки;
СУММ, ПРОИЗВЕД – соответственно суммирование и произведение значений в одном или нескольких блоках;
СУММПРОИЗВ – суммирование произведений соответствующих элементов двух или нескольких массивов;
СРЗНАЧ, СРГЕОМ – расчёт соответственно среднего арифметического и геометрического по числам в заданных блоках;
СЧЁТ – определение количества чисел в заданном блоке.
Стандартную библиотеку можно дополнять функциями, созданными пользователем самостоятельно (см. [3, 4]).
Более сложные алгоритмы оформлены в виде команд и заказываются через меню Сервис. Наиболее важные из них:
Подбор параметра… – нахождение корня уравнения;
Поиск решения… – решение систем уравнений и задач оптимизации;
Пакет анализа – содержит программы для решения сложных статистических и инженерных задач.
Если нужная для вычислений команда отсутствует в меню, её можно установить с помощью команды СервисНадстройки…
3.1. Расчёт таблицы значений функции от одного аргумента
При явном задании функции таблица состоит из двух главных столбцов (строк). Первый – аргументы, второй – значения функции. Если алгоритм расчёта функции сложный, может потребоваться несколько дополнительных столбцов (строк), для записи промежуточных результатов.
Если аргументы меняются с постоянным шагом, то их можно ввести с помощью протяжки (1.5) или формулы. Последний способ более удобен, если может потребоваться пересчёт таблицы в разных диапазонах аргументов. Рассмотрим его на примере.
Пример.
Найти графически координаты корней и максимумов функции Y=2cos(x+2)e-0,5x
Составим план размещения информации:
Ячейки |
Информация |
Значение |
А1 |
Заголовок расчёта |
Поиск корней и экстремумов |
А2:В2 |
Названия констант, необходимых для расчёта аргуменов |
А2: Начало, В2: Шаг |
А3:В3 |
Ориентировочные значения констант |
А3: 0, В3: 1 |
А4:В4 |
Заголовки таблицы |
А4: Х, В4: Y |
А5 |
Формула для первого аргумента |
=$A$3 |
А6 |
Формула для второго аргумента |
=А5+$B$3 |
А7:А25 |
Формулы для остальных аргументов |
Заполняются протяжкой |
B5 |
Формула для первого значения функции |
=2*COS(A5+2)*EXP(-0,5*A5) |
В6:В25 |
Формулы для остальных значений функции |
Заполняются протяжкой |
Формулы, занесённые в ячейки А5:А25, будут нагляднее, если перед их набором присвоить константам в А3:В3 имена (см. п. 1.9) и вводить их с помощью (Л’), а в формуле функции использовать название столбца аргументов: =2*COS(Х+2)*EXP(-0,5*Х) (см. п. 1.10).
Приведённый выше алгоритм позволяет составить таблицу функции при аргументах, меняющихся с нужным нам шагом. Количество шагов, на которое рассчитана таблица, заранее известно (в нашем примере это 20). Поэтому конечный аргумент, рассмотренный в таблице, определяется по формуле =А3+В3*20. Такой способ перебора аргументов не всегда удобен. Иногда мы знаем начальный и конечный аргументы, при которых функция представляет для нас интерес. И нам нужно подобрать шаг, с которым следует двигаться по аргументам, чтобы в расчётах не выйти за нужные границы. В этом случае в ячейки А
Задание.
Отформатируйте полученную таблицу и постройте диаграмму типа Точечная по ней.
Таблица и график показывают, что при х>9 функция практически равна нулю, первый корень лежит в диапазоне 2<x<3, первый экстремум – около х=4. Введём в А3:В3 новые константы: 2 и 0,1 соответственно. Excel сразу же пересчитал таблицу и график на новый диапазон аргументов и теперь можно локализовать корень и экстремум уже с точностью 0,1. Диапазон для корня 2,7<x<2,8, координата экстремума – x=3,5. При необходимости можно снова изменить константы, с помощью которых создаётся диапазон аргументов, и продолжить уточнение ответов.
Задание.
Найдите какой-нибудь корень и экстремум этой функции в отрицательной области аргументов.