III.3. Использование логической функции Если 1 страница

3. В третьем столбце (C) таблицы ETS рассчитайте оклады для каждого разряда сразу для всего столбца (работа с массивом). Выделите блок ячеек C4:C14 введите знак = с помощью мышки выделите A4:A14 затем знак * и блок ячеек B4:B14. В строке формул в результате появится формула: =A4:A14*B4:B14. Подтвердите ввод формулы, нажав комбинацию клавиш Ctrl+Shift+Enter.

Рис. 26. Таблицы: Единая тарифная сетка (ETC) и Список преподавателей кафедры

4. Полученному блоку А4:С14 дайте имя ets, для этого выделите блок ячеек А4:С14 войдите в меню Вставка, Имя, Присвоить напишите имя ets и нажмите OK. Теперь при выделении блока ячеек А4:С14 в строке имени появится имя ets.

5. На листе Упр_13 книгиРаботасоздайте таблицу Cписок преподавателей кафедры в диапазоне ячеек A1:C14. Осуществите подгонку ширины столбцов.

6. Внимание! Книги Работа и Работа1 должны быть открыты в одном приложении Excel.

7. В столбце D таблицы Список преподавателей кафедры рассчитайте оклады для каждого преподавателя. Для этого используйте Мастер функций, выберите функцию ВПР.

8. При вводе второго аргумента функции переключитесь в рабочую книгу Работа1 (для этого нажмите клавиши Ctrl+F6- переход к другому открытому документу; можно также воспользоваться пунктом меню Окно, в котором перечислены открытые рабочие книги). В формуле уместно указать четвертый аргумент ВПР равный нулю, так как следует искать точное соответствие разрядов.

9. Окончательно формула имеет вид: =ВПР(С3;Работа1.xls!ets;3;0).

В процессе набора второй аргумент имеет вид [Работа1.xls]ets!ets, т.е. в рабочей книге Работа1.xls. выбирается рабочий лист ets, а на нём блок с именем ets; но так как имя est является глобальным для рабочей книги, то окончательная ссылка упрощается. Скопируйте формулу с помощью маркера заполнения для остальных преподавателей.

10. Рассчитайте премию. Вставьте в таблицу Список преподавателей кафедры константу Процент (Вставка, Имя, Присвоить напишите имя Процент в формуле =19 и нажмите OK).

11. Вычислите (массивом) в столбце Е сумму премии. Формула будет иметь следующий вид: =D3:D14*Процент. В ячейку E2 впишите Премия. Наложите на столбец D и E формат с двумя цифрами после точки (т.е. рубли-копейки) (см. Раздел II.1).

12. Сохраните рабочие книги Работа.xls и Работа1.xls. Закройте обе книги. Вновь откройте книгу Работа.xls. На вопрос об обновлении связи ответьте Да. Убедитесь в работоспособности таблицы (например, замените какому-нибудь преподавателю разряд- оклад этого преподавателя изменится).

13. Если Вы хотите в дальнейшем открывать обе рабочие книги Работа.xls и Работа1.xls одновременно, то воспользуйтесь командой меню Файл, Сохранить рабочую область. Подробности Вы найдете в Справке Excel.

III.6. Разрешение вопросов, связанных с исследованием зависимости формул

Упр. 14. Вычисление основных параметров для треугольника

Даны три стороны треугольника a, b, с. Требуется вычислить его площадь по формуле Герона ,

где p – полупериметр , а также радиус вписанной окружности и радиус описанной окружности .

Технология работы:

1. Введите данные, как показано на Рис. 27 В ячейку B6 введите формулу =(В2+В3+В4)/2.

Рис. 27. Проектирование расчетов

2. Присвоим имена ячейкам B2, B3, B4, B6. Выделите блок A2:B6, выберите в меню команду Вставка, Имя, Создать. Выберите вариант в столбце слева, нажмите ОК. Теперь, выделяя ячейку B2, в строке формул в поле Имя вы увидите не адрес B2, а имя a. Для ячейки B4 имя не c, как можно, было ожидать, а c_. Это связано с тем, что имена c и r в Excel зарезервированы (c - column - столбец, r - row - строка). Поэтому Excel ввел в имя символ подчеркивания.

3. Введите в B8 формулу Герона = КОРЕНЬ(p*(p–a)*(p–b)*(p–c_)), через ниспадающий список поля имён. Если бы не введенные имена, нам пришлось бы набирать формулу

=КОРЕНЬ(B6*(B6–B2)*(B6–B3)*(B6–B2)), что намного труднее для восприятия.

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

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

5. Дополним таблицу вычислением радиусов вписанной и описанной окружностей. Присвойте ячейке B8 имя - S. Для этого выделите B8 и в поле имя введите имя S, нажмите Enter. В ячейки D10 и F10 введите r и R, а в E10 и G10 – соответствующие формулы (см. выше). Наложите на эти ячейки такие же форматы, как и ранее. Для этого воспользуйтесь кнопкой - Формат по образцу. Например, выделите A8, нажмите кнопку и "покрасьте" кистью E10. У Вас должен получиться следующий результат (Рис. 28).

Рис. 28. Результат выполнения упр.14

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

Удобнее работать с помощью панели кнопок Зависимости. Выведите на экран панель Зависимости (меню Вид, Панели инструментов, Зависимости; или Сервис, Зависимости, Панель зависимостей) и изучите работу кнопок этой панели. Например, выделите ячейку G10, а затем несколько раз нажмите кнопку ‑ Влияющие ячейки. Если у Вас возникнут затруднения при выполнении этого упражнения, обратитесь к cправке Excel.

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

7. Сообщение об ошибочных данных. Будем вычислять отдельно подкоренное выражение p*(p–a)*(p–b)*(p–c_) и определять его знак. Если оно положительно, вычисляем S, R и r. Если же нет, то в ячейке B8 выведем текстовую строку Это не треугольник!, а в ячейках E10 и G10 выведем пустые строки.

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

В ячейку B8 разместите формулу

=ЕСЛИ(B7>0;КОРЕНЬ(B7);"Это не треугольник!"). В E10 разместим формулу =ЕСЛИ(B7>0;S/p;""). Аналогично измените формулу в G10 для R.

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

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

Выделите целиком лист (Таблица 2, п.3), нажмите Ctr+1, выберите вкладку Защита и убедитесь, что флажок Защищаемая ячейка установлен.

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

10. Разрешите пользователю вводить только положительные длины сторон треугольника. Для этого выделите блок ячеек B2:B4 и войдите в Данные, Проверка. В поле Тип данных: выберите Действительное, в поле Значение: - больше, в поле Минимум: - 0. Попробуйте ввести отрицательные (или текст) данные в диапазон B2:B4 и посмотрите реакцию Excel.

11. Выберите в меню пункт Файл, Предварительный просмотр. Изучите назначение кнопок в окне предварительного просмотра. Нажмите кнопку Закрыть. Рабочий лист разбит пунктирными линиями на прямоугольники, соответствующие листам формата А4. Если печатаемая таблица не помещается целиком на один лист, необходимо войти в Параметры страницы и на вкладке Страница установить переключатель разместить не более чем на одной странице в ширину и высоту.

12. Подбор параметра. Например, мы хотим определить величину a при R=3 для этого выделите ячейку G10 и в меню Сервис, Подбор параметра в поле Значение: введите 3 и в поле Изменяя значение ячейки: введите адрес ячейки B2, содержащей величину стороны a (если мы щелкнем мышью по этой ячейке, то в поле ввода окажется адрес $B$2). Нажмите на кнопку ОК. Посмотрите получившиеся результаты.

13. Для выявления ошибок в процессе вычислений используют три функции ЕОШ, ЕОШИБКА и ЕН/Д, с которыми можно познакомиться через встроенную справку MS Excel.

В ячейку С8 введите формулу

=ЕСЛИ(ЕОШ(корень(p*(p–a)*(p–b)*(p–c_)));“”;корень(p*(p–a)*(p–b)*(p–c_))), которая возвращает пустую строку, если вычисление площади привело к ошибке.

В ячейку Е9 введите формулу для вычисления радиуса вписанной окружности = ЕСЛИ(ЕЧИСЛО(С8); C8/р; “”). В ячейку G9 введите формулу для вычисления радиуса описанной окружности. Сравните результаты.

IV. РАБОТА С ДИАГРАММАМИ

В МS Ехсеl данные можно представлять в графическом виде, для этого используют специальный инструмент — Мастер диаграмм, дающий возможность строить различные типы диаграмм.

Для вставки диаграммы необходимо выделить конкретные данные и выбрать пункт меню Вставка, Диаграмма или кнопка на панели инструментов .

Упр. 15. Построение линейного графика

Протабулируйте функцию Y=f(x) на отрезке [-3:3] с шагом ∆=0,25, т.е. составьте таблицу значений линейной функции у=2х+1. Постройте линейный график зависимости Y=f(x). График должен содержать легенду, заголовок, подписи осей и данных.

Технология работы:

1. Прежде чем строить прямую, необходимо составить таблицу данных Y=f(x). Для этого значения х и у следует представить в виде таблицы, где столбцами являются соответствующие показатели. Пусть в рассматриваемом примере первый столбец будет значениями х, а второй соответствующими показателями у. Для этого в ячейку А1 вводим слово Аргумент X, а в ячейку В1 — слово Функция Y.

Начнем с введения значений аргумента. В ячейку А2 введите первое значение аргумента — левая граница диапазона -3. В ячейку АЗ введите второе значение аргумента — -2,75. Затем, выделите блок ячеек А2:АЗ и за маркер заполнения протяните до получения значения равного 3 (Таблица 2, пт. 7).

2. Далее введите значения функции. В ячейку В2 введите ее уравнение: = 2*А2 + 1. Затем автозаполнением скопируйте эту формулу в диапазон В2:В26.

Выделите диапазон ячеек A1:B26 и вызовите Мастер диаграмм. В появившемся диалоговом окне Мастер диаграмм (шаг 1 из 4): тип диаграммы выберите тип диаграммы – Точечная (Рис. 29). Далее действуем в соответствии с указаниями Мастера диаграмм.

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

Рис. 29. Выбор типа диаграммы в
диалоговом окне Мастер диаграмм

Упр. 16. Графическое представление данных

Представьте данные Упр. 9 в наглядном графическом виде.

Для этого скопируйте лист Упр_9, вставьте его в конец и переименуйте его в Упр_16.

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

· Гистограмм (плоских и объёмных), позволяющих сравнивать значения планового и фактического выпуска по месяцам;

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

· Графиков, отображающих процесс изменения значений фактического выпуска по месяцам;

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

Технология работы:

1. Постройте гистограмму. Для лучшего представления данных на диаграмме в исходных данных не должно быть объединенных ячеек. Выделите диапазон ячеек С4:Е16, содержащий два ряда числовых данных: план выпуска и фактический выпуск по месяцам, а также ряды с названиями строк и заголовками столбцов. Названия в строках будут использоваться в качестве обозначений на оси X (категорий), а заголовки столбцов - в качестве легенды.

· Вызовите Мастер диаграмм. В появившемся диалоговом окне Мастер диаграмм (шаг 1 из 4): тип диаграммы выберите тип диаграммы – Гистограмма (Рис. 29). В окне Вид —обычную плоскую гистограмму. Нажав и удерживая кнопку Просмотр результата, можно увидеть образец диаграммы. Перейдите к следующему шагу, нажав кнопку Далее.

· На втором шаге Мастер диаграмм (шаг 2 из 4):источник данных диаграммы на вкладке Диапазон данных выводит образец диаграммы и сообщает, что гистограмма для выделенных данных сформирована стандартным образом: ряды данных - в столбцах (Рис. 30). Это соответствует представлению данных задачи. Перейдите к следующему шагу, нажав кнопку Далее.

Рис. 30. Мастер диаграмм шаг второй
Вкладка Ряд

· На третьем шаге во вкладке Заголовки определите название диаграммы и названия для осей: в поле ввода Название диаграммы введите текст Показатели производства, в поле Ось X (Категорий): — Месяцы, в поле Ось У (Значений): — Количество; во вкладке Легенда включите переключатель Добавить легенду и укажите место размещения легенды. Внесенные изменения будут сразу же отражены в образце диаграммы. Во вкладке Подписи данных можно выбрать режим, в котором на диаграмме будут показываться значения (по умолчанию значения не выводятся). Установив необходимые параметры, перейдите к следующему шагу.

· На последнем шаге выберите место размещения диаграммы (лучше на имеющемся листе, а не на отдельном). Нажмите кнопку Готово.

· Выполните редактирование диаграммы и, если необходимо, форматирование для сбалансированности объектов диаграммы и лучшего восприятия данных.

2. Постройте круговую диаграмму.

· Выделите блок ячеек, состоящий из двух несмежных столбцов С4:С16 и Е4:Е16, содержащих соответственно названия месяцев (для оформления диаграммы) и фактический выпуск по месяцам (собственно данные). Несмежные столбцы выделяются при нажатой клавише Ctrl.

· Вызовите Мастер диаграмм:

ü На первом шаге Мастера диаграмм для типа Круговая выберите вид. Следуйте далее по шагам мастера

ü На третьем шаге во вкладке Заголовок можете ввести или изменить название диаграммы. Во вкладке Легенда включите переключатель Добавить легенду (если он не включен) и выберите место размещения легенды. Во вкладке Подписи данных выберите переключатель доли (в этом случае около каждого сектора диаграммы будет выводиться доля в процентах) или категория и доля (тогда будут выводиться названия месяцев и доля в про­центах). Переключатель Линии выноски отключите. Посмотрите на образец. Перейдите к следующему шагу

ü Выберите место размещения диаграммы на имеющемся листе, нажмите кнопку Готово