- •Оглавление
- •1. Первое знакомство.
- •1.1. Назначение процессора электронных таблиц Microsoft Excel
- •1.2. Общие правила работы с книгами и листами
- •1.3. Основные типы данных
- •1.4. Форматирование таблиц и отдельных ячеек
- •1.5. Основные команды системы
- •1.5.1. Панель инструментов «Стандартная»
- •1.5.2. Перемещение, копирование и заполнение ячеек
- •1.6. Формулы
- •1.6.1. Синтаксис формулы
- •1.6.2. Операторы
- •1.6.3. Ссылки на ячейку
- •1.6.4. Особенности копирования формул.
- •1.6.5. Заголовки и имена в формулах.
- •1.6.6. Функции
- •1.6.7. Использование строки формул для ввода и изменения формул
- •1.6.8. Основные причины возникновения ошибок в формулах
- •2. Решение задач средствами Microsoft Excel.
- •. Создание диаграмм
- •2.1.1 Разработка новой диаграммы
- •2.1.2. Редактирование существующей диаграммы
- •2.2. Использование логических функций;
- •2.3. Работа с массивами.
- •Описание функций первой группы.
- •Описание функций второй группы.
- •2.3.1. Решение системы линейных уравнений.
- •2.3.2. Задача аппроксимации.
- •2.4. Использование специальных средств для решения оптимизационных задач.
- •2.4.1 Решение нелинейного уравнения.
- •2.4.2 Транспортная задача
- •3. Вопросы для самоконтроля по разделу Microsoft Excel
- •4.Тестовые задачи по теме «Электронные таблицы Microsoft Excel»
- •4.1. Рекомендации к решению задач .
- •4.2. Требования к оформлению отчета
- •Варианты заданий
- •Рекомендуемый библиографический список
- •Пример оформления контрольной работы. Приложение 1
Рекомендуемый библиографический список
-
Хселир Р., Фаненштих К. Текстовый процессор Word 6.0 для Windows.- M.: Эком., 1996
-
Лукин С.Н. Word и Windows. Самоучитель для начинающих. Практические советы. М.: «Диалог-МИФИ», 2003.-273 с.
-
MS Word 97 (2000). Шаг за шагом., М.: Ecom 1999 (2000).
-
MS Excel 97 (2000). Шаг за шагом, М.: Ecom 1999 (2000).
-
Николь Н., Альбрект Р. Электронные таблицы EXCEL 5.0. М.: Эком., 1995
Пример оформления контрольной работы. Приложение 1
Задание 2
Задача №1. Построение графиков функций
Задание:
-
Рассчитать таблицу значений функций
при значениях X, изменяющихся от -2 до 2 с шагом 0,1.
-
Отформатировать таблицу, используя различные варианты выравнивания текста в заголовке, форматы чисел и виды рамок
-
Создать диаграмму, содержащую графики всех трех функций.
-
Отредактировать диаграмму так, чтобы первый график был изображен линей, второй маркерами, третий- линей с маркерами.
Решение.
Фрагмент таблицы в режиме отображения формул
Задача №2. Построение графиков функций.
Задание:
-
Рассчитать таблицу значений функций: для значения аргумента в градусах от 180 до 360 с шагом 5.
-
Отформатировать таблицу.
-
Создать диаграмму точечного типа на отдельном листе, содержащую первый график из приведенных в задании функций. Остальные графики добавить в процессе редактирования диаграммы. На диаграмме должно быть: её название, название осей, легенды. Ось X должна называться «Угол в градусах» и содержать значение угла в градусах.
-
Отредактировать диаграмму так, чтобы первый график был изображен линей, второй – маркерами, а третий – линей с маркерами.
Решение.
Значения X из градусов в радианы преобразовываем с использованием функции «РАДИАНЫ» и помещаем в дополнительный столбец и используем полученные значения в в качестве аргумента для вычисления заданных функций.
Таблица значений:
Фрагмент таблицы в режиме отображения формул:
Графики функций:
Задача №3. Логические функции.
Задание:
Создать таблицу «Стоимость лечения» и построить круговую диаграмму распределения стоимости лечения по пациентам. Стоимость лечения = 600+ длительность_пребывания*оплата_за_день. Оплата_за_день равна 500руб. при длительности лечения менее 10 дней и 400 руб. при длительном пребывании в больнице.
Решение.
Фрагмент таблицы в режиме отображения формул:
Задача №4. Логические функции (блок 2
Задание:
Рассчитать таблицу значений составной функции, вычислить сумму её значений на заданном интервале с заданным шагом и построить график (точечную диаграмму) функции.
начальное |
-1 |
конечное |
2 |
шаг |
0,1 |
Решение.
Формула для вычисления значений составной функции: =ЕСЛИ(A2>1;COS(A2);ЕСЛИ(A2<=0;5*A2-7;A2^0,5))
Сумма вычислялась автосуммированием, значение суммы отформатировано влево.
Задание 3
Задача №1. Система линейных уравнений.
Задание:
Найти решение системы линейных уравнений методом обратной матрицы.
-
Записать систему линейных уравнений в «классическом виде»
-
Ввести матрицу коэффициентов при неизвестных и правые части.
-
Решить систему линейных уравнений методом «,обратной матрицы».
Решение.
Дана система линейных уравнений:
-
Записать матрицу коэффициентов системы уравнений (размерность 4×4), и матрицу правых частей (размерность 4×1). Отсутствующие коэффициенты при неизвестных равны нулю.
-
При помощи функции МОБР(A2:D5) вычислить матрицу, обратную матрице коэффициентов уравнений1.
-
При помощи функции МУМНОЖ выполняется умножение обратной матрицы на матрицу правых частей системы уравнений. Результат операции – матрица размерностью 4×1 является решением системы линейных уравнений.
Решение системы уравнений:
Задача №2. Аппроксимация. Вариант 3:
Найти аналитическую формулу зависимости Yi и X, в виде параболы методом наименьших квадратов.
X |
0 |
0,2 |
0,4 |
0,6 |
0,8 |
Y |
10 |
11 |
13 |
8 |
6 |
Решение.
Порядок действий:
-
Формируем таблицу для вычисления коэффициентов и правых частей системы уравнений.
-
Решаем систему уравнений методом обратной матрицы.
-
Рассчитываем таблицу значений аппроксимированной функции для построения графика. На график помещаем исходный набор точек (Y) и сплошную линию, рассчитанную по формуле полученного полинома (Yр).
Набор заданных точек приближенно описывается функцией:
Задача №3. Решение нелинейного уравнения.
Задание:
Найти один из корней нелинейного уравнения:
Порядок действий:
-
Отделить корни графическим методом, для этого построить график функции и найти хотя бы одно пересечение графика с осью X. Отрезок, на котором график функции пересекает ось X, является базовым для дальнейшего уточнения корня.
-
Для уточнения значения корня использованы средства «Подбор параметра» и «Поиск решения». В обоих случаях в качестве начального значения X выбираем левую границу отрезка, на котором есть корень.
Решение.
Как видно из таблицы и графика функции имеет корень, на отрезке [-3;-2]. Для уточнения корня используем средства «Подбор параметра» и «Поиск решения». В обоих случаях значения корня X=-2,383.
Настройка окон «Подбор параметра» и «Поиск решения».
Задача №4. Транспортная задача
Задание:
Имеется 5 заводов – поставщиков, производящих некоторые изделия и 5 потребителей (строек), использующих эти изделия. Мощности поставщиков, потребности строек и стоимость доставки одного изделия от любого поставщика к любому потребителю даны.
-
Определить план перевозок, имеющий минимальную стоимость.
-
Создать диаграмму, иллюстрирующую оптимальный план перевозок.
Решение.
Оптимальный план перевозок содержит 9 элементов, отличных от нуля и содержится в ячейках C3:G7 . для удобства отображения плана перевозок в этой области применено условное форматирование: значения, равные нулю отображаются белым цветом на белом фоне.
Средства, используемые для решения задачи.
Настройка окна «Поиск решения».
Расчетные таблицы в режиме отображения формул.
Диаграмма, иллюстрирующая оптимальный план перевозок.
1 Для выполнения операции с массивами после ввода формулы нажать F2 , а затем одновременно нажать на три клавиши: «Ctrl»+ «Shift»+ «Enter».