Информатика / Учебные_материалы / Excel / вводФормул

РХТУ им. Д.B. Менделеева Кафедра ИКМ Методическое пособие по изучению Excel

Ввод данных и формул.

Проверка и отладка создаваемого рабочего листа

"Проведение численного эксперимента

с помощью электронных таблиц Excel"

Что необходимо знать в Excel для решения этой задачи:

 Ввод формул вручную и с помощью Мастера функций.

 Ввод относительных и абсолютных ссылок в формулы.

 Копирование формул.

 Панель инструментов Зависимости.

Постановка задачи

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

Т_i+1 = (Т_в – Т_i)  0,03 + Т (1)

где Т_в – температура воздуха;

Т_i – температура чая в текущую минуту;

Т_i+1 – температура чая в следующую минуту.

Пусть температура чая, налитого в стакан, равна 90С. Необходимо выяснить, через сколько минут температура чая упадет до 60С. Это, конечно, можно установить экспериментальным путем. Однако задача решается и с использованием численного моделирования.

Математическая модель

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

Выполнение задания состоит из трех этапов:

1. Заполнение рабочего листа исходными данными и формулами.

2. Проверка правильности организации данных.

3. Отладка и проверка правильности работы при различных исходных данных.

 Ввод исходных данных и формул

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

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

 Заполнить таблицу вычислений:

Столбец "Время (мин)"  заполнить как арифметическую прогрессию с шагом 1 следующим образом:

ячейка F2  ввести 0;

ячейка F3  ввести 1;

выделить ячейки F2 и F3 и протащить мышкой за маркер выделения

вниз для заполнения всей колонки:

Столбец "Время (мин)" заполнен.

Столбец "Температура чая (мин)":

Ячейка G2  ввести вручную формулу =$D$8, т.е. нажать клавишу с символом "=", щелкнуть мышкой в области ячейки D8, нажать клавишу F4 для изменения типа ссылки относительной на абсолютную.

Ячейка G3  ввести формулу =($D$7-G2)0,03+G2 вручную аналогичным образом.

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

Столбец "Поиск":

Ячейка Н2  ввести формулу, которая будет высчитывать время, необходимое для охлаждения чая. Если абсолютная разность между очередным вычисленным значением и искомым значением температуры стала равна или меньше заданной точности, вывести соответствующее значение времени из столбца "Время (мин)", иначе вывести символ "–":

= ЕСЛИ (АВS(G2-$D$9)=$D$10; F2; "–")

Введите эту формулу с помощью Мастера функций –

1. Активизировать ячейку Н2;

2. Нажать кнопку Мастер функций на стандартной панели инструментов.

3. Затем в первом окне диалога Мастер функций, показанном на рис. 2, выберите Логические в списке Категория.

4. Выберите ЕСЛИ в списке Функция и нажмите кнопку Далее, чтобы перейти ко второму окну диалога Мастер функций

5. В поле логическое_выражение нужно вести выражение, содержащее другую функцию Excel – ABS(). Поэтому для заполнения этого поля нужно снова нажать кнопку Мастер функций Fx. На экране снова будет диалоговое окно.

6. В первом окне диалога Мастер функцийна этот раз выберите Математические в списке Категория. Выберите ABS в списке Функция и нажмите кнопку Далее, чтобы перейти ко второму окну диалога Мастер функций.

7. Для заполнения аргумента функции ABS (рис. 4) щелкнуть по ячейке G2, набрать символ минус (-), щелкнуть по ячейке D9, нажать F4 для изменения типа ссылки – аргумент введен. Щелкнуть по кнопке Ok. Возврат в предыдущее окно.

8. Завершить ввод логического выражения во втором окне Мастера функций. Добавить в строку "=", щелкнуть по ячейке D10 и нажать F4 для изменения типа ссылки.

Заполнить второе после значение _если_ истина – щелкнуть по ячейке F2. Заполнить третье поле значение _если_ ложь – "–". Щелкнуть по кнопке Готово.

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

Ввод данных и формул завершен.

 Для удобства дальнейшей работы уменьшить точность вывода расчета температур до одного знака после запятой:

Для этого выделить весь столбец  вызвать контекстное меню, щелкнув правой кнопкой мыши  выбрать команду Формат ячеек… (рис. 6)  вкладка Число  окно Числовые форматы: выбрать Числовой, установить Число десятичных знаков 1, кнопка Ok.

 Проверка правильности организации данных на рабочем листе

 Вызвать панель Зависимости.

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

 Проверить правильность организации данных.

Например, проверим, что влияет на расчет температуры через

две минуты, после того как налили чай.

Выбрать ячейку G4  Щелкнуть по кнопке Влияющие ячейки на панели Зависимости.

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

 Щелкните по кнопке Влияющие ячейки еще несколько раз – появившиеся стрелки указывают, что на результат косвенно влияет температура чая в предыдущие минуты, а также начальная температура чая.

Результат проверки заполнения столбца "Температура чая (мин)" показывает, что формула введена и работает правильно.

Отмените все стрелки – кнопка Убрать все стрелки.

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

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