Использование электронных таблиц для решения задач

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

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

• реальная задача;

• компьютерная модель;

• компьютерный эксперимент.

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

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

З адача 1: найдите точки пересечения с осями координат функции Y=(x-6)⁵. Для того, чтобы найти точку пересечения с осью ординат, нужно вычислить значение функции при х=0. Создайте таблицу, в которой в ячейку А2 введите аргумент Х, в ячейку А3 – функцию Y=(x-6)⁵, в ячейку В2 – 0, в ячейку В3 с помощью формулы или Мастера функций найдите значение этой функции (оно будет –7776). Точка пересечения с осью ординат имеет координаты (0; -7776).

Для того, чтобы найти точки пересечения с осью абсцисс функции Y=(x-6)⁵, нужно решить уравнение (х-6)⁵ =0. То есть подобрать такое значение х, при котором формула "=(x-6)⁵" принимает нулевое значение.

Для этого:

• выделить ячейку В3;

• в меню Сервис выберите команду Подбор параметра, откроется окно, в которое введите требуемые значения (если нужно добиться того, чтобы в ячейке В3 было установлено значение 0, посредством изменения значений ячейки В2, то в поле "установить в ячейке" – вводим ссылку абсолютную на ячейку В3, в поле "значение" – 0, в поле "изменяя значение ячейки" – ссылку на ячейку В2;

• выберите ОК;

• в диалоговом окне "Результаты подбора параметра" выберите ОК.

Результат должен разочаровать - найдена точка (5, 76370244254674; - 0,000736709) на первый взгляд очень близкая к искомой, но не совсем та. Однако если округлить полученные значения с точностью до целых, результат вполне может удовлетворить (6; 0). Именно такое значение можно найти аналитически.

При решении этой задачи была построена компьютерная модель процесса, описываемая математической формулой Y=(x-6)⁵, а затем проведен компьютерный эксперимент. Команда Подбор параметра скрупулезно подставляла в заданную формулу различные числа, пока не нашла нужного значения.

Задача 2: в городском транспорте стало появляться все больше кондукторов. Выясним целесообразность подобного нововведения в нашем регионе.

Рассмотрим некоторые аспекты данного явления, чтобы выяснить возможные преимущества и недостатки:

• кондуктору придется платить заработную плату;

• можно будет сократить штат контролеров;

• сразу большое количество людей сможет найти работу;

• с введением кондукторов можно решить проблему безбилетников;

• отпадает необходимость продавать абонементные билеты на городской транспорт;

• отпадает необходимость устанавливать компостеры;

• продолжить список вы можете самостоятельно.

Рассмотрим этот вопрос только с одной стороны. Кондуктору придется платить заработную плату, причем на каждый автобус придется нанять несколько кондукторов. С другой стороны – безбилетникам придется оплачивать проезд. Поставим задачу: определить, сколько безбилетников в сутки должны приобрести билеты, чтобы окупалась зарплата кондуктора (например, 800 рублей). Автобус находится на маршруте с 5 часов утра до 1 часа ночи – в этом случае на каждый автобус придется нанять сразу трех кондукторов, которые будут работать посменно. Следовательно, суммарная месячная заработная плата троих кондукторов будет 8003=2400 (рублей).

Создайте таблицу: в ячейку В2 введите "Средняя зарплата кондукторов", в ячейку В3 – "Размер штрафа", в ячейку В4 – "Среднее число безбилетников в сутки", в ячейку В5 – "Разность между заработной платой кондукторов и суммой штрафов", а в ячейки С2 и С3 – фиксированные величины (2400 и 10), в ячейку С5 введите формулу (=С2/30-С3*С4), где С2/30 –средняя зарплата кондукторов в сутки. Ячейку С4 оставьте пустой, так как именно эту величину мы попытаемся определить.

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

• выделите ячейку С5;

• в меню Сервис выберите команду Подбор параметра;

• в диалоговом окне "Подбор параметра" введите требуемые значения, если нужно добиться того, чтобы в ячейке $C$5 было установлено значение 0, посредством изменения значений ячейки $С$4;

• выберите ОК;

• в диалоговом окне "Результаты подбора параметра" выберите ОК.

В результате, в ячейке С4 появится число 8. Следовательно, если в сутки восемь потенциальных безбилетников оплатят свой проезд, работа кондуктора окупается. Если потенциальных безбилетников больше, работа кондуктора будет приносить доход.

ЗАДАЧА 3 предположим, вы решили приобрести машину. Нужно определить, выгодно ли ездить на ней на работу.

Будем считать, что для поездки на работу вы пользуетесь двумя видами транспорта: трамваем и метро. Следовательно, ежедневные расходы на дорогу в оба конца составляют 10 рублей. Примем стоимость бензина равной 3 рубля 20 копеек, а расход 11 литров на 100 км. Следовательно, для вычисления затрат на один километр пути нужно цену бензина умножить на 0,11.

Создайте таблицу по образцу, где присутствуют две фиксированные величины ("Средняя стоимость проезда на городском транспорте в сутки " и "Стоимость бензина", соответственно в ячейках С2 и С4) и две формулы: в ячейке С5 (=С3*С4*0,11) ("Затраты на дорогу при пользовании автомобилем") и в ячейке С6 (=С2-С5) ("Разность между стоимостью проезда на городском транспорте и затратами на автомобильные поездки").

Необходимо определить, при каком километраже сравняются затраты на пользование городским транспортом и автомобильным. Для этого:

• выделить ячейку С6;

• в меню Сервис выберите команду Подбор параметра;

• в диалоговом окне Подбор параметра введите требуемые значения, если нужно, чтобы в ячейке $C$6 было установлено значение 0, посредством изменения значений ячейки $C$3 и выберите ОК;

• в диалоговом окне Результаты подбора нажмите клавишу ОК.

В результате, в ячейке С3будет вычислено ближайшее подходящее значение 28,4090909090909 (километраж в оба конца). Значит, половина этого значения уравнивает затраты. При увеличении километража возрастает выгода применения личного автомобиля.

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

Создание базы данных с помощью электронных таблиц.

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

Запись – сведения об одном объекте, представленные в виде набора полей, которые расположены в некотором порядке (одинаковом для всех записей данной базы данных).

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

Содержимое поля – информация, которая хранится в данном поле записи. Поле может содержать текст, число или формулу.

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

Система управления базами данных (СУБД) – комплекс программ и языковых средств, предназначенных для создания, ведения и использования баз данных, позволяющий выполнять следующие операции:

• создание базы данных;

• поиск информации в базе данных;

• упорядочивание информации в базе данных;

• редактирование данных;

• вывод информации из базы данных.

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

ЗАДАЧА 4

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

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

• С целью автоматизации заполнения базы данных воспользуйтесь специальным диалоговым окном – Формой данных, в котором легко выполнять все операции над записями: ввод, редактирование, поиск. Для этого выбрать в меню Данные команду Форма указатель ячейки при этом должен находиться внутри списка.

• Нажатием кнопки ОК подтвердите ввод строки названий.

• На экране появится диалоговое окно формы данных, в котором рядом с названиями полей создаваемого списка находятся поля ввода, в которые вводите данные.

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

• Заполнив все поля, т.е. заполнив всю базу данных нажмите клавишу Закрыть (ограничитесь 12-15 записями).

• Нажимая кнопки Назад и Далее просмотрите записи. С помощью кнопки Удалить ненужная запись может быть удалена. После редактирования записи становится доступной кнопка Вернуть. Ее нажатие приводит к восстановлению прежнего вида записи.

1. Сохраните таблицу в своей личной папке под именем Абитуриент.

2. Заполните таблицу данными.

3. Отформатируйте таблицу так, чтобы на экране была видна вся информация. Для этого необходимо выделить название нужного столбца при нажатой клавише <Ctrl>, в меню Формат выбрать команду Ячейки. На вкладке Выравнивание установить флажок Переносить по словам. Переместить указатель мыши в область заголовков на правую границу каждого столбца и дважды щелкнуть по ней.

4. Отформатируйте заголовок таблицы следующим образом:

• разместить данные по центру столбцов;

• установить “полужирный” шрифт;

• установить голубой фон заливки.

5. Выделить ячейки с названиями столбцов и выбрать на панели инструментов кнопку Объединить и поместить в центре. Установить обрамление ячеек, используя все границы.

6. Подсчитайте сумму баллов с помощью функции автосуммирования.

7. Введите новый столбец Анализ и отформатируйте его, как весь заголовок.

8. Заполните данный столбец, вводя формулы, обеспечивающие анализ информации:

• если абитуриент получил 2 по какому-либо предмету, то вывести слово "Завалил";

• если абитуриент не набрал проходного бала9, то вывести слово "Не прошел";

• если абитуриент набрал нужное количество баллов, то вывести слово "Поступил".

Примечание:

Для этой цели используется функция =ЕСЛИ(…<3;"Завалил"; ЕСЛИ(…<3;"Завалил"; ЕСЛИ(…>8;"Поступил"; "Не прошел")))

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

10. Отсортируйте таблицу по фамилиям в алфавитном порядке.

11. Отсортируйте данные по результатам анализа так, чтобы сначала шли поступившие абитуриенты, затем не прошедшие по конкурсу, а затем не сдавшие один из предметов.

12. Ниже основной таблицы в столбец B введите ячейки, имеющие следующие значения:

• всего поступавших,

• поступило,

• не поступило,

• завалило.

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

Примечание:

• Для подсчета всех поступавших используется формула =СЧЕТ( …).

• Для подсчета поступивших используется формула =СЧЕТЕСЛИ( …, "Поступил")

• Для подсчета не поступивших, заваливших аналогичная формула =СЧЕТЕСЛИ( )

13. По полученным данным постройте диаграмму Абитуриенты на отдельном листе.

УПРАЖНЕНИЕ 1.

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

1. Найдите точки пересечения с осями координат функции Y=(x+2)³.

2. Решите квадратные уравнения: x²-12x+36=0; x²-3x-4=0.

3. Исследуйте как зависит сопротивление проводника от длинны проводника (возьмите проводник длиной от 2 метров до 18, площадью поперечного сечения 0,4 мм², удельное сопротивление 0,42*10^-2 мм²ом/м).

4. Используя закон Ома для участка цепи, исследуйте зависимость силы тока от напряжения, которое изменяется от 22 В до 220 В, при сопротивлении проводника 10 ом.

КОНТРОЛЬНЫЕ ЗАДАНИЯ ДЛЯ ОТЧЕТА.

1. Решение задач.

2. Абитуриент. Диаграмма "Абитуриент".

10