2.5. Пример проектирования расчетов на рабочем листе

Теперь мы достаточно подготовлены к решению конкретной задачи: нужно спроектировать рабочую книгу, которая будет вычислять различные элементы треугольника по трем его сто­ронам. Задачу будем решать поэтапно, исправляя некоторые не­удачные решения, как это обычно и происходит на практике. На этом примере мы освоим много возможностей Excel.

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

ПРИМЕР 2.13.ВЫПОЛНИТЬ!

Вычисление элементов треугольника. Даны три стороны треугольника а, b, с. Требуется вычислить его площадь по фор­муле Герона , где р — полупериметр: , а также радиус вписанной окружности и радиус описанной окружности .

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

Переименуйте рабочий лист, дайте ему имя "Треугольник". Введите данные, как показано на рис. 2.7. В ячейку В6 введите формулу =(В2+ВЗ+В4)/2.

Рис. 2.7

Использование имен. В ячейку В8 нужно ввести формулу Герона. Чтобы упростить ввод, дадим имена ячейкам В2, ВЗ, В4, В6. Выделите блок А2:В6, выберите в меню команду "Формулы/ Определенные имена/ Создать из выделенного фрагмента".

Excel предложит вариант "в столбце слева", т.е. взять в качестве имен для ячеек В2, ВЗ, В4, В6 тек­стовые строки (в нашем случае однобуквенные), хранящиеся в ячейках А2, A3, А4, Аб. Нажмите "ОК". Теперь, выделяя ячейку В2, в окошке слева от строки ввода Вы увидите не адрес В2, а имя а. Для ячейки В4 имя не с, как можно было ожидать, а с_. Это связано с тем, что имена с и r в Excel зарезервированы (с - column - столбец, т - row - строка). Поэтому Excel ввел в имя символ подчеркивания.

Введите в В8 формулу =корень(р*(р-а)*(р-Ь)*(р-с_)). После нажатия Enter (или щелчка по зеленой галочке слева от строки ввода) название функции будет отображено прописными буква­ми. Это означает, что мы правильно набрали имя функции. Если бы не введенные имена, нам пришлось бы набрать формулу

=КОРЕНЬ(В6*(В6-В2)* (В6-ВЗ)* (В6-В4)),

что намного труднее для восприятия.

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

Выровняем названия величин по правому краю. Выделите блок А2:А8 и на панели "Форматирование" нажмите кнопку "По правому краю".

Введите длину стороны а, равную 2. Тогда S = 3.799671. Предположим, нам нужна точность три знака после точки. Для этого выделите В8 и несколько раз нажмите кнопку "Умень­шить разрядность" , пока число не приобретет нужный формат 3.800. Важно понимать, что "внутренние" вычисления выпол­няются с прежней точностью, но число, отображаемое в ячейке, округлено до трех десятичных знаков. Отмените форматирова­ние (Ctrl+Z) и испытайте другой способ: выберите в меню "Главная/ Ячейки/ Формат/ Формат ячеек… " (Ctrl+1), в диалоговом окне – вкладку "Чис­ло", в списке "Числовые форматы:" - "Числовой". Далее само­стоятельно разберитесь, как задать нужное количество разрядов.

"Развитие" таблицы. Дополним таблицу вычислением ра­диусов вписанной и описанной окружностей.

Создайте для ячейки В8 имя, взятое из соседней ячейки А8 (т.е. ячейка В8 должна получить имя S). Можно воспользовать­ся ранее освоенным приемом (выделить А8:В8 и "Формулы/ Определенные имена/ Создать из выделенного фрагмента"), но так как здесь всего одно имя, проще поступить так: выделите В8 и в окне имен над столбцом А (там сейчас ото­бражается адрес В8) введите имя S, нажмите Enter.

В ячейки D10 и F10 введите r и R, а в Е10 и G10 – соот­ветствующие формулы. Наложите на эти ячейки такие же фор­маты, как и ранее. Для этого воспользуйтесь кнопкой "Формат по образцу" (на ней изображена кисть). Например, выделите А8, нажмите кнопку и "покрасьте" кистью Е10.

У Вас должен получиться следующий результат (рис. 2.8).

Рис. 2.8

Исследование зависимостей. Выделите G10 и выберите в меню пункт "Формулы/ Зависимости формул/ Влияющие ячейки". На эк­ране протянутся синие стрелки от ячеек, содержащих длины сторон и площадь треугольника, к ячейке G10. Исследуйте зависимости и для других ячеек. Уберите стрелки соответствую­щей командой меню.

Задайте длину стороны а, равную 10. В ячейках с результа­тами появится сообщение об ошибке #ЧИСЛО!. Дело в том, что стороны 10, 4, 5 не образуют треугольника. При вычислении площади под корнем получается отрицательное число. Выдели­те ячейку G10 и выберите "Формулы/ Зависимости формул/ Источник ошибки"

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

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

Будем вычислять отдельно подкоренное выражение р*(р-а)*(р-b)*(р-с_) и определять его знак. Если оно положительно, вычисляем S, R и r. Если же нет, то в ячейке В8 выведем тексто­вую строку "Это не треугольник!", а в ячейках Е10 и G10 выве­дем пустые строки.

Перетащите мышью содержимое В8 в В7. Отредактируйте В7, убрав КОРЕНЬ. В ячейке останется формула =р*(р-а)*(р-b)*(p-c_).

В В8 разместим формулу

=ЕСЛИ(В7>0,КОРЕНЬ(В7),"Это не треугольник!").

В Е10 разместим формулу

=ЕСЛИ(В7>0,S/р,"").

Аналогично измените формулу в G10.

Скрытие строк. В 6-й и 7-й строках расположены результа­ты промежуточных вычислений, видеть которые пользователю таблицы ни к чему. Выделите на левой адресной полосе строки 6 и 7 и в контекстном меню выберите "Скрыть". Если Вы захо­тите вернуть эти строки на экран, выделите 5-ю и 8-ю строки и в контекстном меню выберите "Отобразить".

Аналогично можно скрывать и показывать столбцы. Поэкс­периментируйте.

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

Выделите ячейки, содержащие длины сторон (В2:В4), на­жмите Ctrl+1, выберите вкладку "Защита" и снимите флажок "Защищаемая ячейка". Выберите в меню команду "Рецензирование/ Изменения/ Защитить лист". Попробуйте теперь ввести данные вне диапазона В2:В4 и посмотрите реакцию Excel. Снимите защиту: " Рецензирование/ Изменения / Снять защиту листа".

Ограничение ввода. Разрешите пользова­телю вводить только положительные длины сторон треугольни­ка (пункт меню "Данные/ Работа с данными/Проверка данных").

Имитация печати. Выберите в меню пункт "Кнопка Office/Печать/Предва­рительный просмотр". Изучите назначение кнопок в окне пред­варительного просмотра. Нажмите кнопку "Закрыть". Рабочий лист разбит пунктирными линиями на прямоугольники, соот­ветствующие листам формата А4.

Подбор параметра. Итак, мы вычислили радиус описанной окружности R по трем сторонам треугольника а, b, с. Если за­фиксировать длины сторон b и с (пусть а = 2, b = 4, с = 5), то можно считать, что мы вычисляем R как функцию а. Но Excel дает нам возможность решить обратную задачу: по заданному R вычислить а. При этом не нужно решать вручную громоздкую задачу отыскания а как функции R. Формул на рабочем листе для этой цели вполне достаточно. Например, мы хотим опреде­лить величину а при R = 3. Выделим ячейку G10, в которой вы­числяется R. В меню выберем "Данные/Анализ «что-если»/Подбор параметра". Вы­водится диалоговое окно "Подбор параметра". Поле "Устано­вить в ячейке:" уже содержит адрес выделенной ячейки G10. Нажатием Tab перемещаемся в поле "Значение:" и вводим 3. Еще раз нажимаем Tab и в поле "Изменяя значение ячейки:" вводим адрес ячейки В2, содержащей величину стороны а (если мы щелкнем мышью по этой ячейке, то в поле ввода окажется адрес $В$2 — пока не обращайте внимания на знаки доллара, в позже мы узнаем, что они означают).

Щелкаем кнопку "ОК". Выводится новое окно "Результаты подбора параметра". Разберитесь с его содержимым самостоятельно. Если увеличить разрядность числа в ячейке G10, то Вы увидите, что R достигло значения 2.9999172. При этом а = 1.515753.

Единственное ли значение а соответствует R = 3? На этот вопрос изложенный метод подбора параметра не дает ответа.

Упражнение 2.10. Какое значение а соответствует R = 2? Дайте геометрическую интерпретацию полученному результату.

Упражнение 2.11. Какое значение а соответствует радиусу вписанной окружности r = 0.2?