Проектирование таблицы

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

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

Пример. Вычисление элементов треугольника.

Решение.

Проект таблицы 

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

Дайте листу  имя "Треугольник". Введите данные, как на рисунке (Вы можете просто скопировать таблицу на рабочий лист). В ячейку В6 введите формулу

=(В2+ВЗ+В4)/2.

	А	B	С
1	Стороны треугольника
2	a	3
3	b	4
4	c	5
5
6	p	6
7
8	S

 

Использование имен.

Чтобы упростить ввод и восприятие формул, дадим имена ячейкам В2, ВЗ, В4, В6. Выделите блок А2:В6, дайте команду Формулы/ Имена/Создать из выделенного фрагмента. Программа предложит вариант "в столбце слева", т.е. взять в качестве имен для ячеек В2, ВЗ, В4, В6 текстовые строки (в нашем случае однобуквенные), хранящиеся в ячейках А2, A3, А4, А6. Нажмите "ОК". Теперь, выделяя ячейку В2, в окошке слева от строки формул Вы увидите не адрес В2, а имя а. Для ячейки В4 имя не с, как можно было ожидать, а c_. Это связано с тем, что имена с и г в Excel зарезервированы (с — column - столбец, г — row - строка). Поэтому Excel ввел в имя символ подчеркивания.

Введите в В8 формулу Герона =корень(р*(р-а)*(р-b)*(р-с_)). 

После нажатия Enter название функции будет отображено прописными буквами. Это означает, что мы правильно набрали имя функции. Если бы не созданные имена ячеек, нам пришлось бы набрать формулу =КОРЕНЬ(В6*(В6-В2)*(В6-ВЗ)*(В6-В4)), что намного труднее для восприятия.

Форматирование ячеек.

Хотелось бы придать таблице более "читабельный" вид.

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

Введите длину стороны а, равную 2. Тогда S = 3,799671. Предположим, нам нужна точность три знака после запятой. Для этого выделите В8 и несколько раз нажмите кнопку "Уменьшить разрядность", пока число не приобретет нужный формат 3.800. Важно понимать, что "внутренние" вычисления выполняются с прежней точностью, но число, отображаемое в ячейке, округлено до трех десятичных знаков.

«Развитие "таблицы".

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

Создайте для ячейки В8 имя, взятое из соседней ячейки А8 (т.е. ячейка В8 должна получить имя S). 

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

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

	А	В	С	D	Е	F	G
1	Стороны треугольника
2	а	2
3	b	4
4	с	5
5
6	p	55
7
8	S	3,800
9
10				r	0,691	R	2,632

Исследование зависимостей.

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

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

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

Сообщение об ошибочных данных.

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

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

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

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

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

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

=ЕСЛИ(B7>0;S/p;""). Аналогично измените формулу в G10.

Скрытие строк.

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

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

Защита листа.

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

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

Ограничение ввода

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