Применение табличного процессора для решения уравнений

Применение табличного процессора calc для решения уравнений

Алтайский государственный педагогический университет

NovaInfo51, с. 263-266
Опубликовано 30 сентября 2016
Раздел: Педагогические науки
Просмотров за месяц: 38
CC BY-NC

Аннотация

В работе представлена технология решения уравнений методом подбора параметра в табличном процессоре LibreOffice Calc. Выделены методические рекомендации по изучению данного метода. Приведены задачи для самостоятельного изучения.

Ключевые слова

Текст научной работы

Широко известно, что программные средства, называемые табличными процессорами, обладают огромными вычислительными возможностями. Они позволяют эффективно осуществлять однотипные расчеты над большими наборами данных, автоматизацию итоговых вычислений, обработку результатов экспериментов, поиск оптимальных значений параметров и др.

В настоящее время существует огромное количество программных продуктов указанного вида: SuperCalc, Microsoft MultiPlan, Quattro Pro, Lotus 1-2-3, Microsoft Excel, LibreOffice Calc и др. Среди вышеперечисленных программных средств наибольшую популярность имеет табличный процессор Microsoft Excel. Вместе с тем, следует отметить, что табличный процессор LibreOffice Calc, входящий в состав свободно-доступного, полнофункционального офисного пакета LibreOffice, не уступает по возможностям MS Excel [1]. Кроме того, в Calc есть возможность работать с рабочими книгами Microsoft Excel и сохранять их в формате Excel.

В данной статье в связи с переходом на использование свободно распространяемого программного обеспечения, остановимся на применении в учебном процессе табличного процессора LibreOffice Calc, а именно на решении уравнений с помощью данного программного продукта методом подбора параметра.

Метод подбора параметра в табличном процессоре Calc позволяет определить значение одной входной ячейки рабочего листа, которое требуется для получения желаемого результата в зависимой ячейке (ячейке результата).

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

Методом подбора параметра определите корень уравнения ^<2>-sinx+0,1=0

с точностью до четырех знаков после запятой.

1. Занесите в ячейку A1 переменную x, в ячейку A2 переменную y, в ячейку B1 значение 0.

2. Занесите в ячейку B2 левую часть уравнения, используя в качестве независимой переменной ссылку на ячейку B1. Соответствующая формула может, например, иметь вид: =B1^2-SIN(B1)+0,1 (рис. 1).

3. Выполните команду Сервис > Подбор параметра.

4. В поле Яч. с формулой укажите $B$2, в поле Целевое значение задайте 0, в поле Изменяемая яч. укажите $B$1 (рис. 1).

Рисунок 1. Диалоговое окно «Подбор параметра»

5. Щелкните на кнопке ОК и посмотрите на результат подбора, отображаемый в диалоговом окне Подбор параметра (рис. 2). Щелкните на кнопке Да, чтобы сохранить полученные значения ячеек, участвовавших в операции.

Рисунок 2. Результат подбора параметра

6. Используя команду Формат > Ячейки, установите отображение найденного корня с точностью до четырех знаков после запятой (рис. 4). Обратите внимание, что в строке формул отображается иррациональное значение текущей ячейки B1.

Рисунок 4. Отображение найденного корня уравнения

Ниже приведем ряд аналогичных заданий для самостоятельного выполнения учащимися.

В контексте вышеизложенного сделаем два замечания.

1. При решении уравнений необходимо обратить внимание учащихся на то, что в ячейке B1 мы изначально вносим произвольное значение переменной x, входящее в область допустимых значений уравнения!;
2. Приведенные задания можно усложнить:

• предложить решить аналогичное уравнение, но содержащее в правой части не 0, а какое-либо целое число;
• предложить решить аналогичное уравнение, содержащее в правой части какое-либо выражение, зависящее от x; в этом случае, учащимся потребуется сначала перенести все слагаемые из правой части уравнения в левую и только потом воспользоваться возможностями программы.

В качестве дополнения к представленному материалу укажем следующие направления:

• целесообразно рассмотреть и графический метод решения уравнений в электронных таблицах [2];
• для закрепления метода подбора параметра целесообразно рассмотреть задачи с практическим содержанием, например, задачи с экономическим содержанием [3].

В заключении отметим, что приведенный материал успешно используется в институте физико-математического образования Алтайского государственного педагогического университета при изучении табличного процессора LibreOffice Calc. Кроме того, данный материал можно эффективно использовать и на уроках информатики и ИКТ в старших классах общеобразовательной школы.

Читайте также

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

1. Дронова Е.Н.

NovaInfo59, с.310-314, 9 февраля 2017 , Педагогические науки, CC BY-NC

Список литературы

1. Дронова Е.Н. Использование табличного процессора для составления математических таблиц / NovaInfo.Ru. – 2015. – Т.2. – № 31. – С. 324-332.
2. Дронова Е.Н., Михалёв А.С. Интегрированный урок информатики и математики по теме «Исследование алгебраических моделей» / Педагогическое образование на Алтае. – 2015. – № 1. – С. 206-212.
3. Дронова Е.Н. Решение задач оптимизации методом подбора параметра в электронных таблицах как средство развития мыслительных операций у учащихся / Современная педагогика. – 2015. – № 1 (26). – С. 19-24.

Цитировать

Дронова, Е.Н. Применение табличного процессора calc для решения уравнений / Е.Н. Дронова. — Текст : электронный // NovaInfo, 2016. — № 51. — С. 263-266. — URL: https://novainfo.ru/article/7861 (дата обращения: 18.02.2022).

Поделиться

Электронное периодическое издание зарегистрировано в Федеральной службе по надзору в сфере связи, информационных технологий и массовых коммуникаций (Роскомнадзор), свидетельство о регистрации СМИ — ЭЛ № ФС77-41429 от 23.07.2010 г.

Соучредители СМИ: Долганов А.А., Майоров Е.В.

Применение табличного процессора Excel для графического решения уравнений n-й степени

Цели урока:

1. Формирование умений и навыков, носящих в современных условиях общенаучный и обще интеллектуальный характер.
2. Развитие у школьников теоретического, творческого мышления, а также формирование операционного мышления, направленного на выбор оптимальных решений.
3. Научить учащихся применять современное программное обеспечение в решении нестандартных задач.
4. Повторение пройденного материала.

Задачи урока:

1. Воспитательная – развитие познавательного интереса, воспитание информационной культуры.
2. Учебная – изучить и закрепить основные навыки работы с электронными таблицами.
3. Развивающая – развитие логического мышления, расширение кругозора.

Оборудование: персональные компьютеры (ПК), раздаточный материал, доска, маркеры, проектор.

План урока

1. Организационный момент.
2. Фронтальный опрос для проверки уровня подготовки учащихся к усвоению нового материала.

1) Какие дополнительные возможности есть у программы Excel?
2) Как вы понимаете термин деловая графика?
3) Какими возможностями для создания деловой графики обладает Excel?
4) При помощи какой команды меню можно построить диаграммы и графики в Excel?
5) Как задать автоматическое вычисление в таблице значений ячеек по определенной формуле?
6) Каким образом можно занести формулу в несколько ячеек, т.е. скопировать ее?

Ход урока

1. Организационный момент.

2. Фронтальный опрос.

1) Для чего нужна программа Excel?

Ответ: для создания таблиц, вычисляемых таблиц, диаграмм и графиков (деловой графики).

2) Какими возможностями для создания деловой графики обладает Excel?

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

3) При помощи какой команды меню можно построить диаграммы и графики в Excel?

Ответ: с помощью вызова Мастера диаграмм (по команде Вставка-Диаграмма или с помощью кнопки Мастер диаграмм).

4) Как задать автоматическое вычисление в таблице значений ячеек по определенной формуле?

Ответ: активизировать нужную ячейку, затем ввести знак «=» и формулу, которая может содержать адреса ячеек, знаки арифметических операций и функции. Контролировать и редактировать ввод формулы можно с помощью строки ввода формулы, которая расположена в верхней части окна программы.

5) Каким образом можно занести формулу в несколько ячеек, т.е. скопировать ее.

Ответ: ввести формулу в ячейку, установить курсор на нижнем правом маркере ячейки (при этом курсор должен принять вид маленького черного крестика) и протянуть его до последней ячейки в нужном диапазоне.

3. Объяснение нового материала (проводится одновременно с работой учеников на компьютерах синхронно с учителем).

Тема урока «Применение табличного процессора Excel для графического решения уравнений n-ой степени».

Из курса математики нам известно, что корнями уравнения являются значения точек пересечения графика функции (то есть нашего уравнения) с осью абсцисс. Если же мы решаем систему уравнений, то ее решениями будут координаты точек пересечения графиков функций. Этот метод нахождения корней называется графическим. На прошлом занятии мы узнали, что с помощью программы Excel можно строить практически любые графики. Воспользуемся этими знаниями для нахождения корней системы уравнений графическим методом.

Для примера рассмотрим решение следующей системы уравнений:

Y — X 2 = 0
Y – 2X = 9

Преобразуем данную систему в приведенную:

Y = X 2
Y = 2X + 9

Для оценки решений воспользуемся диаграммой, на которой отобразим графики обеих функций. Сначала построим таблицу 1 (рисунок 1).

• Первая строка – строка заголовков. Далее для построения таблицы используем формулы.
• При заполнении столбца А: в ячейку А2 заносится начальное значение аргумента Х=-10, для автоматического заполнения всего столбца нужно в ячейку А3 занести формулу А2+1 и скопировать ее до ячейки А23.
• При заполнении столбца В в ячейку В2 заносится формула А2*А2, которая затем копируется до ячейки В23.
• При заполнении столбца С в ячейку С2 заносится формула 2*А2+9, и также копируется до С23.
• Выделяем таблицу вместе со строкой заголовка и помощью мастера диаграмм выберем тип диаграмм Точечная и построим черновую диаграмму первоначальной оценки решений.
• Вводим заголовок «Диаграмма оценки решения» и обозначения осей x, y (поле ввода текста).
• Добавляем основные линии сетки по оси X и по оси Y (выставляем флажки).
• Размещаем легенду справа от графиков (выставляем флажок «добавить легенду» и включаем переключатель «размещение справа»).
• Размещаем графики на имеющемся листе.
• Подписываем лист 1 «Диаграмма оценки решения» (рисунок 2).

Диаграмма оценки решения

На диаграмме видно, что оба графика имеют точки пересечения – эти координаты точек и есть решения системы. Так как шаг изменения аргумента был достаточно велик, то мы получили приближенные значения решений. Уточним их, построив два графика в интервалах от –3 до 0, где находится первое решение, и от 3 до 5 – где находится второе. Составим новые таблицы.

Для первого решения (таблица 2, рисунок 3).

• При заполнении столбца А: в ячейку А2 заносится начальное значение аргумента Х=-3, для автоматического заполнения всего столбца нужно в ячейку А3 занести формулу А2+0,1(в этом случае мы уменьшаем шаг изменения аргумента для более точного построения) и скопировать ее до ячейки А23.
• При заполнении столбца В в ячейку В2 заносится формула А2*А2, которая затем копируется до ячейки В23.
• При заполнении столбца С в ячейку С2 заносится формула 2*А2+9, и также копируется до С23.
• Выделяем таблицу вместе со строкой заголовка и помощью мастера диаграмм
• выберем тип диаграмм Точечная и построим диаграмму для первого решения.
• Вводим заголовок «Первое решение» и обозначения осей x, y (поле ввода текста).
• Добавляем основные линии сетки по оси X и по оси Y (выставляем флажки).
• Размещаем легенду справа от графиков (выставляем флажок «добавить легенду» и включаем переключатель «размещение справа»).
• Размещаем графики на имеющемся листе.
• Подписываем лист 2 «Первое решение» (рисунок 4).

Первое решение

4. Самостоятельная работа.

Для второго решения ребята самостоятельно строят таблицу (таблица 3, рисунок 5), выбрав правильно промежуток. Затем по таблице строят диаграмму для второго решения (рисунок 6). Учитель проходит и проверяет правильность выполнения работы. И если нужна помощь, то в индивидуальном порядке оказывает ее.

Второе решение

Решением нашей системы будут координаты точек пересечения графиков: X1=-2,1; Y1=4,8; X2=4,2; Y2=17,4.

Как вы уже поняли, графическое решение системы дает приблизительные результаты.

5. Сравнение результатов, полученных графическим способом (Excel) и аналитическим (Qbasic).

Учитель предлагает решить данную систему уравнений аналитическим способом, используя ранее полученную на уроках информатики программу решения квадратного уравнения. К доске приглашается ученик, который преобразует систему в квадратное уравнение:

Выделяем коэффициенты a, b, c, (a=1, b=-2,c=-9) и подставляем в программу (ребята открывают программу, которая была составлена ранее на уроках программирования).

REM Решение квадратного уравнения
INPUT «Введите коэффициенты a, b, с»; a, b,c
d= b^2-4*a*c
IF d 2
Y=4X+12

2. C помощью табличного процессора Excel решить графически систему:

3. C помощью табличного процессора Excel решить графически систему:

4. C помощью табличного процессора Excel решить графически систему:

5. C помощью табличного процессора Excel решить графически систему:

6. C помощью табличного процессора Excel решить графически систему:

7. C помощью табличного процессора Excel решить графически систему:

8. C помощью табличного процессора Excel решить графически систему:

9. C помощью табличного процессора Excel решить графически систему:

10. C помощью табличного процессора Excel решить графически систему:

11. C помощью табличного процессора Excel решить графически систему:

12. C помощью табличного процессора Excel решить графически систему:

7. Подведение итогов.

8. Выставление оценок.

9. Домашнее задание.

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

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

Учебно-методическое пособие Использование табличного процессора MS Excel для численного решения систем линейных уравнений и вычисления определенного интеграла разработано для организации самостоятельной работы студентов и соответствует действующей программе по курсу дисциплины «Информатика и ИКТ» для темы «Возможности динамических (электронных) таблиц. Математическая обработка числовых данных» раздела «Технология создания и преобразования информационных объектов».

Кротко изложены основы технологии работы с табличным процессором MS Excel и основы численных методов решения математических задач для систем линейных уравнений и вычисления определенного интеграла. Приведена пошаговая реализация нахождения неизвестных для системы линейных уравнений и вычисления значения определенного интеграла на конкретных примерах с использованием MS Excel. Представлены индивидуальные задания для самостоятельной работы студентов.

Учебное пособие предназначено для студентов образовательных учреждений среднего профессионального образования, изучающих дисциплину «Информатика и ИКТ», а также для реализации математических моделей с помощью компьютера при выполнении расчетно-графических, курсовых и дипломных работ.

Скачать:

Предварительный просмотр:

МИНИСТЕРСТВО ОБРАЗОВАНИЯ НИЖЕГОРОДСКОЙ ОБЛАСТИ

Государственное бюджетное профессиональное образовательное учреждение «Кстовский нефтяной техникум имени Бориса Ивановича Корнилова»

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

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

Учебно-методическое пособие
для самостоятельной работы студентов
по дисциплине « Информатика и ИКТ »

Учебно-методическое пособие Использование табличного процессора MS Excel для численного решения систем линейных уравнений и вычисления определенного интеграла разработано для организации самостоятельной работы студентов и соответствует действующей программе по курсу дисциплины «Информатика и ИКТ» для темы «Возможности динамических (электронных) таблиц. Математическая обработка числовых данных» раздела « Технология создания и преобразования информационных объектов».

Кротко изложены основы технологии работы с табличным процессором MS Excel и основы численных методов решения математических задач для систем линейных уравнений и вычисления определенного интеграла. Приведена пошаговая реализация нахождения неизвестных для системы линейных уравнений и вычисления значения определенного интеграла на конкретных примерах с использованием MS Excel. Представлены индивидуальные задания для самостоятельной работы студентов.

Учебное пособие предназначено для студентов ГБОУ СПО «Кстовский нефтяной техникум им. Б.И. Корнилова», изучающих дисциплину «Информатика и ИКТ», а также для реализации математических моделей с помощью компьютера при выполнении расчетно-графических, курсовых и дипломных работ.

1.5. Основные правила создания формул и функций

Учебно-методическое пособие для самостоятельной работы студентов разработано в соответствии с действующей программой по курсу дисциплины «Информатика и ИКТ» для темы «Возможности динамических (электронных) таблиц. Математическая обработка числовых данных» раздела « Технология создания и преобразования информационных объектов».

Учебно-методическое пособие содержит теоретический материал по технологии использования табличного процессора MS Excel и математическим методам решения систем линейных уравнений и приближенному вычислению определенного интеграла, который является основой для практического использования решения математических задач с помощью компьютерных технологий. В разделе «Реализация решения математических задач в MS Excel» приведены подробные пошаговые решения примеров решения системы линейных уравнений и приближенного вычисления интеграла, которые могут быть воспроизведены студентом самостоятельно. Для закрепления полученных навыков в соответствии предлагаемыми методическими указаниями студент должен выполнить индивидуальные задания и представить их на проверку преподавателю.

Содержание учебно-методического пособия отражает практическую направленность использования компьютера для решения профессиональных задач, так как большинство инженерных задач использующих математические методы требуют сложных длительных вычислений, которые существенно упрощаются и сокращаются при использовании компьютерных технологий. Полученные студентом знания и умения, в первую очередь, могут быть использованы при выполнении расчетно-графических, курсовых работ и. конечно, при выполнении дипломной работы.

1. Табличный процессор Excel и технология работы в нем

1.1. Структура документа MS Excel

MS Excel – это прикладная программа управления электронными таблицами, которая используется для выполнения вычислений, организации данных, анализа результатов и поиска оптимальных решений.

Базовыми понятиями в Excel являются: книга, лист, таблица, ячейка .

Структуру книги можно представить следующим образом (см. Рис.1).

Книга в Excel представляет собой файл с расширением xls . Имя книги (файла) задается при ее сохранении и должно напоминать о ее содержании. Каждая книга может состоять из нескольких поименованных листов (до 255, а по умолчанию – 3). Имя листа указано на ярлычке (внизу листа) и может быть изменено, чтобы напоминать о его содержании. Для перехода на другой лист надо щелкнуть ЛКМ (ЩЛКМ) по его ярлычку. Листы можно добавлять, удалять, копировать или переименовывать (ЩПКМ – щелчок правой кнопкой мыши по ярлычку и появится контекстное меню). Лист – это место для ввода, хранения и обработки данных. Лист Excel состоит из ячеек , каждая из которых имеет свой адрес . Ячейки образуют строки (до 65536) и столбцы (до 256). Строки имеют номера, а столбцы по умолчанию – буквенные имена (если стиль ссылок А1 ), или номера, если установлен стиль ссылок R1C1 (R – строка, а С – столбец). Стили ссылок задаются параметрами при работе с опциями меню «Сервис». Как правило, используется стиль А1.

1.2. Основной принцип работы электронных таблиц

Каждая ячейка листа может содержать текст, числовое значение или формулу и быть отформатирована.

Основной принцип работы электронных таблиц заключается в том, что одни ячейки рабочего листа используются как независимые переменные (влияющие ячейки), которым задаются значения из вне (например, пользователем), а другие ячейки используются как зависимые переменные (зависимые ячейки), которые содержат формулы, ссылающиеся на независимые переменные. Пользователь вводит исходные данные (текст или числа) во влияющие ячейки и формулы в зависимые ячейки, далее автоматически (или, в зависимости от настройки, по команде) производятся вычисления по формулам, и пользователь видит готовый результат в зависимых ячейках. Если же установить режим показа формул («Сервис \ Параметры»), то в зависимых ячейках будут видны только формулы.

Если же режим показа формул не установлен , то в ячейке Е2 сразу появится результат (=4,5).

На листе может быть создана одна или несколько таблиц.

Таблица – это совокупность ячеек, связанных между собой по смыслу или с помощью формул в соответствии с задачей пользователя.

Ячейки таблиц, размещенных на разных листах книги или в разных книгах, также могут быть взаимосвязаны посредством формул. На основе таблицы (для наглядности при анализе исходных данных и результатов вычислений) может создаваться диаграмма , которую можно разместить на листе вместе с таблицей или на отдельном листе.

1.3. Ввод информации в ячейки

Как правило, построение таблицы начинается с заполнения заголовков столбцов (а, если необходимо, и строк, как на Рис.1), поясняющих содержание основных ячеек таблицы, в которых находятся исходные данные и формулы. Перед вводом информации в ячейку ее надо выделить с помощью ЩЛКМ по ячейке или с помощью клавиш перемещения курсора.

Существуют следующие основные способы ввода информации в ячейки:

1) Неавтоматизированный способ, при котором каждый символ вводится с клавиатуры. Он занимает много времени и имеет большую вероятность ошибок, но необходим тогда, когда в таблице нет повторяющейся, либо закономерно изменяющейся информации, что бывает очень редко.

Ввод информации должен быть зафиксирован. Это достигается либо нажатием клавиши ENTER или нажатием клавиш управления курсором на клавиатуре (стрелки ↑ , ← , ↓ , → ). Для изменения (редактирования) информации в ячейке надо сначала 2 раза ЩЛКМ по ней. Например, на Рис. 1 в ячейках А2 и D4 записана информация, изменяющейся не закономерно, поэтому ее невозможно вводить иначе, чем в ручную с клавиатуры.

Полезно запомнить, что:

– при вводе в ячейку длинного предложения для перехода на новую строку (в той же ячейке) надо нажать ALT+ENTER .

– если при вводе обнаружена ошибка, то следует нажать на панели инструментов (ПИ) кнопку ОТМЕНИТЬ .

2) Автоматизированный способ , который может быть реализован двумя методами:

• копирования в буфер ранее заполненной ячейки (или группы ячеек), которые предварительно должны быть выделены, и последующей вставки из буфера в нужную область таблицы, предварительно выделив левую верхнюю ячейку этой области. Для выделения группы смежных ячеек надо выделить первую ячейку диапазона, затем нажать и удерживая SHIFT выделить последнюю ячейку диапазона.
• автозаполнение рядов . Ряд – это группа смежных ячеек, принадлежащая одному столбцу или строке и содержащая одинаковую или закономерно изменяющуюся информацию .

Перед автозаполнением надо записать в начальную ячейку текст, число, дату, формулу и т.п., затем выделить эту ячейку и установить указатель мыши над маркером заполнения (маленького черного квадрата в правом нижнем углу выделенной ячейки). При этом вид указателя мыши меняется на знак + , означающий, что автозаполнение разрешено. Затем, нажав ЛКМ, перетащить указатель по ячейкам, которые нужно заполнить информацией и отпустить ЛКМ. При перемещении указателя вниз или вправо, автозаполнение создает в отмечаемых ячейках возрастающие значения, при перемещении указателя вверх или влево отмечаемые ячейки заполняются убывающими значениями.

Если в первоначально выделенной ячейке не распознается закономерность для возрастания или убывания, то ячейки просто дублируются.

Так, например, если в первой ячейке была запись «Блок1», то при перемещении указателя вниз, в последующих ячейках автоматически появятся записи «Блок2», «Блок3» и т.д. В то же время, если в первой ячейке было записано слово «Блок», то в последующих ячейках это слово просто дублируется.

В нашем примере, ячейку Е3 (Рис. 1) целесообразно заполнять с помощью метода автозаполнение рядов, т.е. не вводить в нее формулу, а распространив автозаполнением в нее содержимое ячейки Е2 . При этом содержимое формулы Е3 (аргументы функции СРЗНАЧ()) будут отличаться от аргументов этой же функции для ячейки Е2 по условию автозаполнения:

для Е2: =СРЗНАЧ(С2:D2);

для Е3: =СРЗНАЧ(С3:D3).

Существует и другая возможность автозаполнения рядов – через главное меню: для копирования одной ячейки в несколько смежных или для настройки закономерности автозаполнения можно воспользоваться командой «ПРАВКА \ ЗАПОЛНИТЬ …».

1.4. Форматирование таблиц

Форматирование в MS Excel осуществляется почти так же, как и в других приложениях MS Office.

Для облегчения визуального поиска в таблице требуемой информации целесообразно заголовки столбцов и строк таблицы выделять цветом, отличительным (от другого текста) шрифтом, заливкой, граничной линией и т.п. В некоторых случаях, для более полного восприятия заголовка, целесообразно объединять ячейки.

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

Объектом форматирования могут быть отдельные ячейки или группа ячеек (диапазон), которые предварительно должны быть выделены. ЩПКМ по выделенному объекту приводит к вызову контекстного меню, в котором следует выбрать пункт « формат ячеек» .

Для изменения ширины столбцов или высоты строк таблицы надо поместить указатель мыши (УМ) между соответствующими именами столбцов или номерами строк листа и, удерживая ЛКМ, передвинуть УМ на нужное расстояние.

Если новая таблица содержит компоненты уже существующей таблицы, то целесообразно скопировать созданную таблицу в другую область листа или на другой лист (книгу) и отредактировать ее под условия создаваемой новой таблицы: удалить (или добавить) столбцы, строки или некоторые ячейки. Для работы с этими объектами их надо выделить.

Чтобы выделить целиком строку надо ЩЛКМ по ее номеру, а для столбца – по его имени. После этого ЩПКМ по выделенному объекту вызвать контекстное меню, из которого затеем выбрать, в соответствие с операцией, пункты « Удалить» или « Добавить ячейки». (Для вставки строки надо выделить строку ниже, а для столбца – выделить столбец правее вставляемого).

1.5. Основные правила создания формул и функций

Каждая формула начинается со знака равенства « =» . Знак равенства указывает процессору MS Excel на то, что следующий за ним текст является формулой. В формулах могут присутствовать арифметические операторы для выполнения действий над константами или числами, которые могут вводиться непосредственно с клавиатуры, либо содержаться в других ячейках. В последнем случае в формуле указываются адреса этих ячеек, т.е. ссылки на них. Если в формуле присутствуют несколько арифметических операторов, то в нее можно включить скобки для определения порядка вычислений. В формулах применяют стандартные арифметические операции и соответствующие операторы: сложение (+), вычитание (-), умножение (*), деление (/), возведение в степень (^), (последовательность выполнения операций такая же, как и в арифметике). Также в формулах могут применяться функции из набора встроенных в Excel функций. Большое количество функций (около 500) в значительной степени определяет область использования Excel. Из набора этих функций , которые сгруппированы по категориям , мы будем использовать следующие: Математические, Статистические, Ссылки и массивы, Логические . В логических функциях для обозначения операций сравнения двух чисел используются операции и соответствующие операторы сравнения: равно (=), больше (>), меньше ( больше или равно (>=), меньше или равно ( не равно (<>), логическое сложение «ИЛИ» (+), логическое умножение «И» (*). Результатом выполнения операции сравнения является логическое значение ИСТИНА или ЛОЖЬ.

Формула является основным средством для анализа данных . С помощью формул можно складывать, умножать и сравнивать данные, осуществлять их поиск и т.п. Формулы могут ссылаться на ячейки текущего листа, листов той же книги или других книг.

В следующем примере « =(В4+25)/СУММ(D5:F5)» складывается значение ячейки B4 с константой 25. Полученный результат делится на сумму значений в диапазоне ячеек D5:F5 , т.е. на результат операции D5+E5+F5.

В данном примере выражение « СУММ(…)» является функцией , т.е. стандартной формулой.

При решении конкретной задачи для создания функции целесообразно использовать так называемый Мастер функций и действовать в следующем порядке:

1. Выделить ячейку, в которую будет вставляться функция.
2. Запустить МАСТЕР ФУНКЦИЙ (на панели инструментов значок , либо «ГЛАВНОЕ МЕНЮ/ВСТАВКА/ФУНКЦИЯ»).
3. Задать в нужную категорию функции (это определяется интуицией или методом перебора).
4. выделить требуемую функцию, прочитать пояснения внизу окна.
5. Если все понятно, то нажать — «ОК».
6. На появившейся панели формул, ввести требуемую информацию в соответствующие окна аргументов.

Ввод информации в окна, как правило, осуществляется так:

• установить курсор в окно (в первое окно он устанавливается автоматически);
• выделить нужные ячейки мышью, установить курсор в другое окно и т.д.

Если нужные ячейки не видны, то надо свернуть панель формул, нажав на красную кнопку в правой части окна ввода, затем выделить ячейки и развернуть панель формул, снова нажав на красную кнопку в правой части окна. Если же пояснений в этом окне недостаточно, то следует закрыть окно МАСТЕРА ФУНКЦИЙ и использовать справочную систему, для чего надо:

1. ЩЛКМ по знаку вопроса в Горизонтальном меню и выполнить команду: «ВЫЗОВ СПРАВКИ / ПРЕДМЕТНЫЙ УКАЗАТЕЛЬ / (ввести фразу « функции») / ОБЗОР / (нажать кнопку « Вывести») / ( выбрать нужную категорию (раздел), например, « Статистические функции ») / (нажать кнопку « Вывести») / ( выбрать нужную функцию, например, « СРЗНАЧ ») / (прочитать подробное ее описание и рассмотреть примеры ). Выписать наиболее общий пример в тетрадь, закрыть справочную систему.
2. По аналогии с примером, используя МАСТЕР ФУНКЦИЙ, создать нужную функцию и, изменяя исходные данные, проверить ее корректность.

В Excel можно создавать так называемые вложенные функции , т.е. функции, аргументами которых являются другие функции. Возможно до 7 уровней вложения. В этом случае для вставки аргумента-функции, после того как будет открыта панель исходной функции, надо открыть левое окно строки формул (щелкнув по «стрелке вниз» ), выбрать нужную функцию и далее действовать, как и с обычной функцией.

Как правило, формулы используются для определения их значений в зависимости от параметров (исходных данных), т.е. констант и переменных. Однако в Excel есть возможность решать и обратные задачи (« какой должна быть переменная, чтобы формула приняла заданное значение?» ), т.е. определять значение переменной, причем только одной, если задать значение результата вычисления формулы. Для этого надо выделить ячейку для искомой переменной и задать ей ориентировочное значение, а затем выполнить команду «СЕРВИС / ПОДБОР ПАРАМЕТРА» и, в появившемся окне, указать запрашиваемые данные.

В формулах для обращения к ячейкам, в зависимости от решаемой задачи, применяются различные способы и методы ссылок на ячейки.

1.6. Способы и методы формирования в формулах ссылок на ячейки

Ссылкой однозначно определяется ячейка или группа (диапазон) ячеек листа, используемых в формуле . С помощью ссылок можно использовать в формуле данные, находящиеся в различных местах листа, а также использовать значение одной и той же ячейки в нескольких формулах. Кроме того, можно ссылаться на ячейки, находящиеся на других листах книги или в другой книге, или на данные другого приложения.

Ссылки на ячейки других книг называются внешними ссылками. Ссылки на данные других приложений называются удаленными ссылками.

В Excel можно выделить три способа формирования ссылок: координатный относительно листа книги, координатный относительно таблицы и именной .

При координатном способе адрес ячейки определяется местом пересечения столбца и строки листа книги или таблицы.

При именном способе адрес ячейки определяется текстовым именем ячейки (или диапазона ячеек), которое напоминает о содержании ячеек.

1. Координатный способ формирования ссылок относительно листа книги

До настоящего момента мы уже использовали именно такой способ, но к вышесказанному необходимо добавить следующее:

• Чтобы создать ссылку на диапазон ячеек, надо активизировать ячейку левого верхнего угла диапазона, поставить двоеточие ( : ), а затем — активизировать ячейку правого нижнего угла диапазона. Например, в формуле =СУММ(A1:В2) определяется сумма чисел, записанных в четырех ячейках: А1, А2, В1, В2 .
• В формулах Excel используется два метода ссылок на ячейки:

1. относительные ссылки , которые ссылаются на ячейки относительно позиции формулы (т.е. если формулу с относительными ссылками скопировать, например, через буфер, в другую ячейку, то ссылки в ней изменятся на количество строк и столбцов относительно ее исходного расположения);
2. абсолютные ссылки , которые всегда ссылаются на одни и те же ячейки независимо от изменения места расположения формулы после копирования. Для задания абсолютной ссылки в формуле надо перед именем столбца и номером строки ячейки добавить знак $ . Пример: $А$2 (если в формуле указана ссылка А2 – то это относительная ссылка).

1. Координатный способ формирования ссылок относительно таблицы

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

Например, если в столбце с заголовком « Физика » содержатся средние баллы экзаменационных оценок по физике полученных соответствующим курсом, а заголовками строк являются « Курс11 », « Курс21 », « Курс31 », то, создав формулу « =Физика Курс31 » в любом месте листа, можно определить средний балл по физике полученный курсом №31. Пробел между заголовками является оператором пересечения диапазонов, который предписывает формуле вернуть значения из ячейки, находящейся в пересечении строки « Курс31 » и столбца « Физика ».

1. Именной способ формирования ссылок

Использование имен может упростить понимание формулы. Например, формула «= X*SIN(X) » более привычна, чем «= А1*SIN(A1) » или формула «= МАКС(Возраст) » проще для понимания, чем формула « =МАКС(C20:C30) ». В этом примере имя «Возраст» представляет группу ячеек «C20:C30». Имена можно использовать на любом листе книги.

Чтобы присвоить имя ячейке или группе ячеек нужно:

1. Выделить ячейку или диапазон ячеек, которому необходимо присвоить имя.

2. Щелкнуть ЛКМ по окну имени, которое находится слева в строке формул (это строка, расположенная над заголовками столбцов).

3. Ввести имя ячейки.

4. Нажать клавишу ENTER.

• выделить диапазон ячеек;
• выполнить команду «ВТАВКА / ИМЯ / ПРИСВОИТЬ». Появится окно, в котором можно присвоить, изменить или удалить имя.

По умолчанию имена являются абсолютными ссылками.

2. Математическая постановка задач

2.1. Метод Крамера решения систем линейных уравнений

Рассмотрим метод Крамера решения систем линейных уравнений для системы из трех уравнений с тремя неизвестными x 1 , x 2 и x 3 , которая имеет вид:

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

Тогда исходная система будет равносильна системе вида:

Если все четыре определителя отличны от нуля, то система имеет единственное решение. Значение неизвестных определяются в виде:

Метод Крамера может быть использован для решения систем линейных уравнений, состоящих из четырех и более уравнений, содержащих, соответственно, четыре и более неизвестных. Однако, для вычисления определителей четвертого и выше порядков следует использовать специальные приемы, которые требуют значительных временных затрат и особого внимания при выполнении вычислений. Выполнение вычислений с помощью MS Excel существенного упрощает решение подобных задач.

2.2. Приближенные методы вычисления определенного интеграла

Значение определенного интеграла численно равно площади так называемой криволинейной трапеции, ограниченной кривой, определяемой подынтегральной функцией y = f ( x ), прямыми x = a , x = b и осью Ox .

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

Площадь криволинейной трапеции разбивают на n прямоугольников, высоты которых равны y 0 , y 1 , y 2 , . y n -1 и основания .

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

Если суммировать площади прямоугольников, которые покрывают площадь криволинейной трапеции с избытком (рис. 10.6, б ), то значение определенного интеграла может быть вычислено по формуле: .

Значения y 0 , . y n находят из равенства y k = f ( a + k Δx), k =0, 1, …, n . Эти формулы называются формулами прямоугольников и дают приближенный результат. С увеличением n результат становится более точным.

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

Пусть необходимо вычислить площадь A 1 AmBB 1 криволинейной трапеции, выражаемую формулой .

Заменим дугу AmB хордой АВ и вместо площади криволинейной трапеции A 1 AmBB 1 вычислим площадь трапеции A 1 ABB 1 : , где АА 1 и ВВ 1 – основание трапеции, а А 1 В 1 – ее высота.

Обозначим f ( a )= A 1 A , f ( b )= B 1 B . Высота трапеции A 1 B 1 = b — a , площадь . Следовательно, или . Это так называемая малая формула трапеций .

Для получения более точного результата необходимо разбить площадь криволинейной трапеции на n площадей ординатами, отстоящими друг от друга на расстоянии Δ x . Суммируем площади получившихся трапеций: S = S 1 + S 2 + S 3 + . + S n , где по малой формуле трапеций ; ; ; . .

Сложив, получим или .

Так как и , то можно записать так называемую большую формулу трапеций: , где y 0 , y 1 , y 2 , . y n – значения подынтегральной функции при значениях аргумента, соответственно, a ; a +Δ x ; a +2Δ x ; a+( n -1)Δ x ; b .

3. Реализация решения математических задач в MS Excel

3.1. Решение систем линейных уравнений с использованием табличного процессора MS Excel

В качестве примера рассмотрим систему уравнений:

1. Оформить заголовок в строке 1 на листе 1 «Решение системы линейных уравнений».
2. В области B3:F6 ввести исходные данные, как показано на рисунке 2.
3. Ввести в ячейку A8 текст заголовка «Метод Крамера».
4. В ячейку A11 ввести текст «Δ=» (выравнивание по правому краю).
5. Скопировать исходные данные C4:E6 в область В10:D12. Это главный определитель системы.
6. Скопировать содержимое ячейки A11 в ячейку F11 .
7. В ячейку G11 ввести формулу « =МОПРЕД(B10:D12) ». Получаем значение главного определителя системы.
8. Скопировать исходные данные C4:E6 в область В14:D16. Скопировать исходные данные F4:F6 в область В14:B16. Это определитель для первого неизвестного x 1.
9. В ячейку A15 ввести текст «Δ x 1=» (выравнивание по правому краю).
10. Скопировать содержимое ячейки A15 в ячейку F15 .
11. В ячейку G15 ввести формулу « =МОПРЕД(B14:D16) ». Получаем значение определителя для первого неизвестного x 1.
12. Скопировать исходные данные C4:E6 в область В18:D20. Скопировать исходные данные F4:F6 в область C18:C20. Это определитель для второго неизвестного x 2.
13. В ячейку A19 ввести текст «Δ x 2=» (выравнивание по правому краю).
14. Скопировать содержимое ячейки A19 в ячейку F19 .
15. В ячейку G19 ввести формулу « =МОПРЕД(B18:D20) ». Получаем значение определителя для второго неизвестного x 2.
16. Скопировать исходные данные C4:E6 в область В22:D24. Скопировать исходные данные F4:F6 в область D22:D24. Это определитель для третьего неизвестного x 3.
17. В ячейку A23 ввести текст «Δ x 3=» (выравнивание по правому краю).
18. Скопировать содержимое ячейки A23 в ячейку F23 .
19. В ячейку G23 ввести формулу « =МОПРЕД(B22:D24) ». Получаем значение определителя для третьего неизвестного x 3.
20. В ячейки H15 , H19 и H23 ввести соответственно текст « x 1=», « x 2=» и « x 2=» (выравнивание по правому краю).
21. В ячейку I15 ввести формулу « =G15/$G$11 » (выравнивание по левому краю). Получаем значение первого неизвестного x 1.
22. Скопировать содержимое ячейки I15 в ячейку I19 . Получаем значение второго неизвестного x 2.
23. Скопировать содержимое ячейки I15 в ячейку I23 . Получаем значение третьего неизвестного x 3.

3.2. Нахождение значений определенного интеграла с использованием табличного процессора MS Excel

В качестве примера рассмотрим нахождение значения определенного интеграла при числе разбиения интервала интегрирования n =10. Следует отметить, что точное значение данного интеграла может быть получено аналитическим методом (метод замены переменной) и оно равно 2.

1. Оформить заголовок в строке 1 на листе 2 «Нахождение значения определенного интеграла».
2. В ячейку A3 с помощью редактора формул вставим вид вычисляемого интеграла как показано на рисунке 3.
3. В ячейки E3 , E4 , E5 и E6 соответственно введем текст: «нижний предел интегрирования», «верхний предел интегрирования», «число разбиений отрезка интегрирования» и «шаг интегрирования».
4. В ячейки D3 , D4 , D5 введем исходные данные, определяющие пределы интегрирования и число разбиений отрезка интегрирования (в нашем примере это числа: 0, 5, 10) (выравнивание по центру).
5. В ячейку D6 введем формулу для определения шага интегрирования « =(D4-D3)/D5 » (выравнивание по центру).
6. Оформим заголовок таблицы для численного вычисления подынтегральной функции в узлах интегрирования: в ячейку A8 вводим текст «x», в ячейку B8 вводим текст «f(x)» (выравнивание по центру).
7. В ячейку A9 вводим формулу « =D3 ».
8. В ячейку A10 вводим формулу « =A9+$D$6 ».
9. Копируем формулу из ячейки A10 в диапазон A11:A19 (используем прием растягивания). Получаем значения узлов интегрирования.
10. В ячейку B9 вводим формулу, определяющую значение подынтегральной функции в первом узле интегрирования. В нашем примере она будет: «=1/КОРЕНЬ(A9+4)».
11. Копируем формулу из ячейки B9 в диапазон B10:B19 (используем прием растягивания). Получаем значения подынтегральной функции в узлах интегрирования.
12. В ячейку D8 введем текст « Приближенное значение интеграла: ».
13. В ячейку D10 введем текст «по формуле прямоугольников:».
14. В ячейку G10 введем формулу « =СУММ(B9:B18)*D6 », которая позволяет вычислить значение определенного интеграла по формуле прямоугольников. Получаем приближенное значение вычисляемого определенного интеграла.
15. В ячейку D12 введем текст «по формуле трапеций:».
16. В ячейку G12 введем формулу « =(2*СУММ(B10:B18)+B9+B19)*D6/2 », которая позволяет вычислить значение определенного интеграла по формуле трапеций. Получаем второе приближенное значение вычисляемого определенного интеграла.

Замечание: оба приближенных значения определенного интеграла близки к его точному значению.

1. В диапазоне A21:H36 с помощью Мастера диаграмм построить график подынтегральной функции на основе диаграммы точечного вида:

1. Выделить диапазон A8:B19;
2. Меню Вставка ⇨ Команда Диаграмма …;
3. На 1-ом шаге выбрать тип диаграммы: Точечная диаграмма со значениями, соединенными сглаживающими линиями и нажать кнопку « Далее »;
4. На 2-ом шаге ничего не изменяем и нажимаем кнопку « Далее »;
5. На 3-ем шаге на вкладке «Заголовки» в окно «Ось Х (категорий) заносим текст «x», а на вкладке «Легенда» снимаем флажок в позиции «Добавить легенду» и нажимаем кнопку « Далее »;
6. На 4-ом шаге нажимаем кнопку « Готово ».

1. Переносим построенную диаграмму в диапазон A21:H36 , сжимая (растягивая) до заданных размеров. Форматируем Ось категорий и Ось значений, переносим подписи осей, форматируем Область построения диаграммы (см. Рис. 3).

4. Методические указания по выполнению самостоятельной работы

1. Повторить материал по теме «Возможности динамических (электронных) таблиц. Математическая обработка числовых данных», прочитав раздел учебно-методического пособия «Табличный процессор Excel и технология работы в нем».
2. Повторить материал по дисциплине «Математика» по решению систем линейных уравнений методом Крамера и приближенному вычислению определенных интегралов, прочитав раздел учебно-методического пособия «Математическая постановка задач».
3. Решить на компьютере с использованием табличного процессора MS Excel систему линейных уравнений из раздела «Реализация решения математических задач в MS Excel».
4. Вычислить на компьютере с использованием табличного процессора MS Excel значение определенного интеграла из раздела «Реализация решения математических задач в MS Excel».
5. Решить на компьютере с использованием табличного процессора MS Excel систему линейных уравнений , применив метод Крамера. Коэффициенты при неизвестных и свободные члены задаются по вариантам, указанным в разделе «Варианты заданий для самостоятельной работы». Результаты решения предоставить преподавателю в виде аналогичном Рис 2.
6. Вычислить на компьютере с использованием табличного процессора MS Excel значение определенного интеграла , применив формулу прямоугольников и формулу трапеций. Построить график подынтегральной функции. Вид подынтегральной функции f ( x ), нижний предел интегрирования a , верхний предел интегрирования b задаются по вариантам, указанным в разделе «Варианты заданий для самостоятельной работы». Число разбиений отрезка интегрирования n равно 10 для всех вариантов. Результаты работы предоставить преподавателю в виде аналогичном Рис 3.

5. Варианты заданий для самостоятельной работы

5.1. Варианты заданий систем линейных уравнений

5.2. Варианты заданий определенного интеграла