Использование арифметических и логических формул и функций. Основные приемы работы в MS Excel

ОГЛАВЛЕНИЕ

Обработка данных средствами электронных таблиц. 2

Практическая работа 8. 2

Часть 1. Основные приемы работы в MS Excel. 2

Дополнительные задания. 7

Контрольные вопросы.. 8

Часть 2. Абсолютная и относительная адресация. 8

Дополнительные задания. 13

Контрольные вопросы.. 13

Практическая работа 9. 13

Использование арифметических и логических формул и функций. 13

Дополнительные задания. 22

Контрольные вопросы.. 23

Практическая работа 10. 24

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

Дополнительные задания. 28

Контрольные вопросы.. 28

Практическая работа 11. 29

Построение и форматирование диаграмм.. 29

Дополнительные задания. 35

Контрольные вопросы.. 35

Практическая работа 12. 36

Форматирование таблиц. Использование условного форматирования. 36

Дополнительные задания. 40

Контрольные вопросы.. 41

Создание презентаций в MS Power Point 41

Практическая работа 13. 41

Разработка презентации. 41

Дополнительные задания. 49

Контрольные вопросы.. 50

 

 

Обработка данных средствами электронных таблиц

Практическая работа 8

Часть 1. Основные приемы работы в MS Excel.

  Порядок работы Вставьте в рабочую книгу новый лист и назовите его "Форматирование".

Дополнительные задания

1. Введите в строку, начиная с С4, арифметическую прогрессию с первым элементом 4, разностью -1, предельным значением -5.

2. Введите в столбец, начиная с F6, геометрическую прогрессию с первым элементом 4, знаменателем 2, последним элементом 256. А что получится, если в качестве последнего элемента задать 254?

3. В приложении "Блокнот" ("Nоtераd") подготовьте файл, содержащий таблицу следующего вида:

Фамилия И.О. Рост Вес

Антонов К.С. 176,3 84,2

Пименов И.А. 164,7 67,8

Николаев С.П. 185,6 92,4

Сделайте два варианта исходного файла:

1) данные в строке разделены символом табуляции — tab.tхt;

2) данные в строке разделены пробелами — sрасе.tхt.

Для каждого из файлов выполните следующие действия:

1) откройте файл в Ехсе1 и с помощью Мастера текстов распределите данные по трем столбцам;

2) перенесите полученную таблицу в Excel 01.х1s.

 

Контрольные вопросы

1. Как выполнить автоподбор ширины столбца?

2. Как отменить последнюю команду (откат)?

3. Как отменить выполненное форматирование?

4. Чем отличаются способы копирования только значения, значения и формата, только формата и всего содержимого ячейки? Продемонстрировать на примерах.

5. Окружить каждую ячейку блока рамкой.

6. Изменить представление числа в ячейке, изменяя количество цифр после десятичного разделителя.

7. Что дает использование формата с разделителями?

8. Изменить представление числа в ячейке с 300 на 300,00 р.

9. Как измениться представление числа в ячейке, если там находится число 12,367 и будет использован процентный формат?

10. С какого символа начинается ввод формулы?

11. Как выполняется автозаполнение?

12. Что можно настроить в диалоговом окне Параметры страницы? Продемонстрировать.

13. Рассказать о назначении кнопок в верхней части окна Предварительного просмотра?

14. Как выполняется импорт файлов? Продемонстрировать.

 

Часть 2. Абсолютная и относительная адресация.

Цель занятия. Приобрести навыки применения средств автоматизации ввода и итоговых функций. Изучить технологию организации расчетов с абсолютной адресацией данных в таблицах MS Excel.

 

Порядок работы

Применение средств автоматизации ввода

2. Создайте (Файл > Создать) новую рабочую книгу. Сохраните ее (Файл > Сохранить как) под именем Excel 02.xls. 3. Выберите щелчком на ярлычке неиспользуемый рабочий лист или создайте новый… 4. Сделайте текущей ячейку А1 и введите в нее текст: Месяцы.

Применение итоговых функций

2. Откройте рабочую книгу Excel 02.xls. 3. Выберите рабочий лист Дополнительные расходы по месяцам, созданный в… 4. Сделайте текущей первую свободную ячейку в столбце В (В26).

Абсолютная и относительная адресации

2. Выберите щелчком на ярлычке неиспользуемый рабочий лист или создайте новый (Вставка > Лист). Дважды щелкните на ярлычке нового листа и… 3. В ячейку А1 введите текст Прейскурант и нажмите клавишу ENTER. 4. В ячейку А2 введите текст Курс пересчета: и нажмите клавишу ENTER. В ячейку В2 введите текст 1 у.е. = и нажмите…

Предварительный просмотр и печать прейскуранта

2. Выберите рабочий лист Прейскурант, созданный в предыдущем упражнении. 3. Убедитесь, что внешний вид документа вас устраивает, и щелкните на кнопке… 4. Щелкните на кнопке Масштаб, чтобы увидеть изображение страницы в натуральную величину.

Дополнительные задания

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

2. Оформить любой прайс (таблица должна обязательно содержать столбцы Наименование товара, Цена товара в $, Цена товара в руб. и ячейку с указанием курса пересчета).

Контрольные вопросы

1. Как изменить формат ячейки?

2. Как выполняется автоматическое заполнение месяцев?

3. Как осуществляется ввод формулы в ячейку?

4. Выполнить подсчет сумы чисел, расположенных в столбце.

5. Подсчитать среднее, минимальное и максимальное значения чисел в столбце.

6. Что вычисляет функция СЧЕТ?

7. Какой символ является признаком абсолютной адресации?

8. Как выполнить абсолютную ссылку на ячейку?

9. Выполнить объединение ячеек.

10. Выполнить формирование колонтитула.

11. Как управлять разбиением документа на печатные страницы?

 

Практическая работа 9

Использование арифметических и логических формул и функций.

  Порядок работы Запустите Ехсеl. Сохраните рабочую книгу "Книга1" под именем Excel 03.х1s (в меню: "Файл/ Сохранить…

ПРИМЕР 3.

Вложенные функции. Введем в ячейку АЗ формулу для вычисления функции . Эта функция представляет собой композицию двух функций: и . Cсоответствующие функции Ехсе1: КОРЕНЬ(z) и ТАN(х)+1.

Выделяем ячейку АЗ. Щелкаем кнопку со знаком равенства слева от строки ввода. Выбираем КОРЕНЬ. Находясь в поле ввода аргумента функции КОРЕНЬ, в панели функций выбираем ТАN и в поле ввода аргумента этой функции вводим имя х. А теперь внимание! Мы не щелкаем кнопку "ОК", как следовало бы ожидать, — это досрочно завершит ввод формулы (испытайте это). Вместо этого устанавливаем в поле ввода курсор на функции КОРЕНЬ (т.е. на внешней функции). Тотчас второе окно Мастера функций для ТАN заменяется на второе окно Мастера функций для функции КОРЕНЬ. В поле ввода аргумента отображается ТАN(х). Добавляем к этой функции +1 и щелкаем "ОК". Результат: 1.468952.

Обратите внимание, когда Вы находились во втором окне Мастера функций для функции ТАN() и ввели в качестве аргументах, то справа от поля ввода аргумента Вы видите =4, ниже Вы видите =1.157821282 (это значение tg 4), а в самом низу окна Вы видите: Значение: 1.076021042. Это вычисленное значение формулы =КОРЕНЬ(ТАN(х)), которая присутствует сейчас в строке ввода.

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

Когда Вы освоите функции Ехсе1, то увидите, что проще вводить их вручную в строке ввода, а не возиться с Мастером функций (за исключением случаев, когда функция имеет много аргументов). Здесь рекомендация такая: вводите имена функций строчными буквами: =корень(tan(х)+1). Если Вы ввели имена функций правильно, то по завершении ввода они автоматически будут преобразованы в прописные буквы. Например, если Вы ввели формулу =корень(tg(х)+1), то по завершении ввода она примет вид: =КОРЕНЬ(tg(х)), а в качестве результата вычислений в ячейке будет фигурировать сообщение об ошибке #ИМЯ?. Имя функции tg в отличие от функции КОРЕНЬ не преобразовано в прописные буквы, а так как имени tg в рабочей книге нет, то формула возвращает ошибочное значение.

3. ЧИСЛОВЫЕ ФУНКЦИИ

Некоторые из функций, о которых ниже пойдет речь, Вы можете и не найти в Мастере функций. Чтобы эти функции были доступны, установите надстройку "Пакет анализа". С этой целью выберите в меню пункт "Сервис/ Надстройки". В диалоговом окне найдите в списке надстроек "Пакет анализа" и установите слева от него флажок. Теперь "Пакет анализа" будет подгружаться автоматически при загрузке Ехсе1. В Мастере функций появится категория "Мат. и тригонометрия", там и находятся дополнительные функции. В дальнейшем мы не будем специально указывать, нужно ли для использования той или иной функции использовать "Пакет анализа". Эти сведения Вы найдете в Справке.

ПРИМЕР 4.

Вычисление частного и остатка. Сначала познакомимся с функцией ЦЕЛОЕ. Введите в ячейку А8 формулу =целое(5,7), а затем скопируйте эту формулу в ячейку А9 и замените число 5,7 на –5,7. В первом случае формула вернет число 5, а во втором — число - 6. Вывод: эта функция округляет число до ближайшего меньшего целого.

Вычислять частное мы уже умеем, для этого применяется оператор деления /. Но если нужно вычислить частное от деления нацело, то для этого от результата деления нужно вычислить функцию ЦЕЛОЕ. Функция ОСТАТ( число; делитель) вычисляет остаток от деления нацело. Имеет место соотношение

п =d * ЦЕЛОЕ( п/d) + ОСТАТ( п, d), (1)

т.е. Делимое = Делитель * Частное + Остаток

Примеры использования функций: =ЦЕЛОЕ(20/3) возвращает 6, т.е. результат деления нацело 20 на 3. =ОСТАТ(20;3) вернет2, так как 20 - 3*6 = 2. Эти функции дают разумные результаты и для нецелых значений аргументов: =ЦЕЛОЕ(8,6/2,3) возвращает 3, а =ОСТАТ(8,6;2,3) вернет 1.7, так как 8.6-3*2.3= 1.7.

Упражнение 4. Введите в ячейку С14 формулу =ЦЕЛОЕ(А14/В14). а в D14 формулу =ОСТАТ(А14,В14). Последовательно вводите в ячейки А14 и В14 пары чисел: 5 и 3;5 и -3; -5 и 3; -5 и -3. Сохраняйте результаты вычислений в блоке F14:I17 с помощью копирования значений (перетаскивать блок А14:D14 правой кнопкой мыши и в контекстном меню выбирать "Копировать только значения"). В блок F13:I13 введите заголовки: "Делимое", "Делитель", "Частное", "Остаток". Дайте интерпретацию результатов в соответствии с формулой (1).

ПРИМЕР 5.

Функции округления. В бухгалтерских расчетах большую роль играют функции, позволяющие округлять результаты вычислений. В Ехсеl их несколько.

1. ОКРУГЛ(число;число_разрядов)

Если число_разрядов больше 0, то число округляется до указанного количества десятичных разрядов справа от десятичного разделителя.

Если число_разрядов равно 0, то число округляется до ближайшего целого.

Если число_разрядовменьше 0, то число округляется до указанного количества десятичных разрядов слева от десятичного разделителя.

Пусть в ячейке А19 записано число 143,3184. Разместим в B19 формулу =ОКРУГЛ(А19;2). Она вернет число 143.32. Разместим в С19 формулу =ОКРУГЛ(А19;0). Она вернет число 143. Эти примеры отвечают требованиям округления до копеек и до рублей. Если же Вы введете в D19 формулу =ОКРУГЛ(А19;-1), то она вернет число 140.

2. Несколько иные задачи решают функции

ОКРУГЛВНИЗ(число;число_разрядов),

ОКРУГЛВВЕРХ(число;число_разрядов).

В соответствии сих названиями они работают как функция ОКРУГЛ, но округляют всегда в большую или меньшую сторону. Самостоятельно рассмотрите примеры.

3. Три предыдущие функции всегда округляют до степеней десяти, их второй аргумент — это показатель со знаком минус для десяти. Но есть более общие функции:

ОКРУГЛТ(число; множитель),

ОКРВВЕРХ(число; множитель),

ОКРВНИЗ(число; множитель).

Число — это округляемое значение. Множитель — это кратное, до которого требуется округлить. Число и множитель должны иметь одинаковый знак. Примеры:

=ОКРВНИЗ(9,3; 4) вернет 8, =ОКРВВЕРХ(9,3; 4) вернет 12.

4. Функция ОТБР(число;число_разрядов) отбрасывает дробную часть числа, если опустить второй аргумент. Если его указать, то функция работает, как ОКРУГЛВНИЗ. Функция ЦЕЛОЕ(число) работает, как ОТБР(число), но только для неотрицательных аргументов. Поэкспериментируйте и самостоятельно сформулируйте, чем отличаются возвращаемые значения этих функций для отрицательного аргумента.

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

ЛОГИЧЕСКИЕ ВЫРАЖЕНИЯ.

ПРИМЕР 1. Введем в ячейку А1 формулу =7>5. Она вернет значение ИСТИНА. Скопируем… Рассмотрим другой пример. Введем в ячейку А4 число 2, а в ячейку В4 формулу = А4>3. Формула возвращает значение…

ЗАПРЕТ ВВОДА НЕДОПУСТИМОГО ЧИСЛА.

Выделите ячейку А41. Выберите в меню команду "Данные/ Проверка". Появится диалоговое окно с тремя вкладками. На первой вкладке…

Дополнительные задания

1. В ячейку В5 введите формулу

Должен получиться результат: - 5.93548.

2. В ячейку В6 введите формулу . Какое сообщение будет выведено в ячейке? Почему? Исправьте формулу, прибавив к знаменателю 1.

3. В ячейку А2 ввести формулу для вычисления функции . Формула возвращает 1.610922.

4. В Ехсеl имеется функция ПИ(), она не имеет аргументов (хотя скобки обязательны) и возвращает число p = 3.14159.... Каким образом вычислить в Ехсеl число е = 2.71828... — основание натуральных логарифмов, хотя функция, аналогичная ПИ(), для числа е отсутствует?

5. Что больше:e^p или p^e?

6. Введите в ячейку В7 формулу, возвращающую значение ИСТИНА, если zÎ(2;4]È[7,12)È[20,¥), и ЛОЖЬ — в противном случае.

7. Дайте ячейкам А20, В20 и С20 имена u, v, w. В самих ячейках содержатся числа. Введите в ячейки А21, А22 и т.д. логические формулы, которые возвращают значение ИСТИНА тогда и только тогда, когда

а) каждое из чисел u, v, w является положительным;

б) хотя бы одно из чисел u, v, w является положительным;

в) только одно из чисел u, v, w является положительным;

г) ни одно из чисел u, v, w не является положительным;

д) хотя бы одно из чисел u, v, w не является положительным.

8. Торговый агент получает процент от суммы совершенной сделки. Если объем сделки до 3000, то 5%; если объем до 10 000, то 2%; если выше 10 000, то 1.5%. Введите в ячейку А10 текст "Объем сделки", в ячейку А11 — "Размер вознаграждения". В ячейку В10 введите объем сделки, а в В11 — формулу, вычисляющую размер вознаграждения.

9. Дать решение примеров 2 и 3 (о принадлежности точки отрезку или одному из двух лучей) без использования функций И, ИЛИ, НЕ, а с помощью вложенных функций ЕСЛИ.

10. Экзаменатор проверяет письменную работу, состоящую из пяти задач. За каждую задачу он проставляет оценку — целое число в диапазоне от 0 до 4. Введите в А24:Е24 порядковые номера задач (от 1 до 5), в F24 — строку "Сумма". Экзаменатор вводит оценки в диапазон А25:Е25. В F25 автоматически должна вычисляться сумма оценок. При переходе к ячейке подсказка не выводится, при неверном вводе выводится предупреждение.

Указание. Перед вызовом меню "Данные/ Проверка" выделите диапазон А25:Е25.

 

Контрольные вопросы

1. Какие математические операции используются в Excel?

2. Как присвоить ячейке имя?

3. Какие вы знаете основные математические функции в Excel?

4. Для чего служат и как используются функции ЦЕЛОЕ, ОСТАТ, ОКРУГЛ?

5. Для чего служит и как используется функция ЕСЛИ?

6. Как осуществить вывод сообщения об ошибке?

7. Как осуществить запрет ввода недопустимого значения?

 

Практическая работа 10

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

Цель занятия. Освоение новых возможностей Excel на примере проектирования расчетов на рабочем листе.

 

Порядок работы

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

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

Запустите Ехсеl. Сохраните рабочую книгу "Книга1" под именем Excel 04.х1s (в меню: "Файл/ Сохранить как").

ПРИМЕР 1.

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

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

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

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

 

	A	B	C
	Стороны треугольника
	a
	b
	c
	p
	S

Рис. 4.1

 

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

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

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

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

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

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

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

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

	A	B	C	D	E	F	G
	Стороны треугольника
	a
	b
	c
	p	5,5
	S	3,800
				r	0,691	R	2,632

Рис. 4.2

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Подбор параметра. Итак, мы вычислили радиус описанной окружности R по трем сторонам треугольника а, b, с. Если зафиксировать длины сторон b и с (пусть а = 2, b = 4, с = 5), то можно считать, что мы вычисляем R как функцию а. Но Ехсеl даст нам возможность решить обратную задачу: по заданному R вычислить а. При этом не нужно решать вручную громоздкую задачу отыскания а как функции R. Формул на рабочем листе для этой цели вполне достаточно. Например, мы хотим определить величину а при R = 3. Выделим ячейку G10, в которой вычисляется R. В меню выберем "Сервис/ Подбор параметра". Выводится диалоговое окно "Подбор параметра". Поле "Установить в ячейке:" уже содержит адрес выделенной ячейки G10. Нажатием Таb перемещаемся в поле "Значение:" и вводим 3. Еще раз нажимаем Таb и в поле "Изменяя значение ячейки:" выводим адрес ячейки В2, содержащей величину стороны а (если щелкнем мышью по этой ячейке, то в поле ввода окажется $В$2 — пока не обращайте внимания на знаки доллара, в дальнейшем мы узнаем, что они означают). Щелкаем кнопку “OK”. Выводится новое окно "Результаты подбора параметра". Разберитесь с его содержимым самостоятельно. Если увеличить разрядность числа в ячейке G10, то Вы увидите, что R достигло значения 2.9999172. При этом а = 1.515753.

А можно ли определить величину а еще точнее? — Да, можно. Выберите в меню "Сервис/ Параметры/ Вычисления". На вкладке имеется поле ввода "Относительная погрешность". Значение по умолчанию: 0.001. Введите число 0.00001. Повторите подбор параметра а для R = 3. Вы получите в G10 число 2.9999977. При этом а = 1.515698. Верните прежнее значение относительной погрешности подбора параметра.

Дополнительные задания

1. Скопируйте лист «Треугольник» на новый рабочий лист и дайте ему имя «Дополнительные вычисления». Расположите ячейки с величинами радиусов друг под другом и дайте им общий заголовок. Вычислите углы треугольника и т.д. (по теореме косинусов), переведите их в соседнем столбце в градусы (найдите соответствующую функцию в Справке), сосчитайте отдельно сумму углов для углов треугольника, выраженных в радианах и в градусах.

2. Вычислите медианы ( и т.д.), биссектрисы ( и т.д.), высоты ( и т.д.). Разместите их на рабочем листе отдельными блоками с заголовками. (Если пользователь вводит длины сторон, которые не образуют треугольника, то названия вычисляемых элементов также должны заменяться пустыми строками.)

3. Поместите эти блоки для радиусов, углов, медиан и т.д. в рамки (Сtr1+1, вкладка "Граница").

4. Посмотрите зависимости.

5. Исправьте таблицу. Пусть надпись "Это не треугольник!" выводится в ячейке С4 крупным шрифтом и красным цветом, а в ячейке В8 в этом случае ничего не выводится.

6. Найти действительные корни квадратного равнения aх² + bх + c =0 по заданным коэффициентам. Если действительных корней нет, вывести об этом сообщение.

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

Контрольные вопросы

1. Почему ячейке нельзя дать имя с?

2. Как используется и для чего кнопка Формат по образцу?

3. Как посмотреть от каких ячеек зависит вычисляемая функция?

4. Как посмотреть в каких формулах используется данная ячейка?

5. Если задать длину сторон треугольника а=10, b=4, c=5, появляется ошибка #ЧИСЛО! Почему и как ее исправить?

6. Как защитить лист от изменения?

7. Для чего и как осуществляется подбор параметра?

 

Практическая работа 11

Построение и форматирование диаграмм

  Порядок работы Создайте новую рабочую книгу и назовите ее Excel 05.хls.

ПРИМЕР 2. Изменение диаграммы с использованием Главного меню.

Перейдите на лист с диаграммой, построенной в примере 1. Скопируйте этот лист и назовите новый лист "Пример 2". Обратимся к Главному меню. Оно изменилось.

В меню "Вид" испытайте пункты "Во весь экран" и "По размеру окна". Сначала выберите пункт "По размеру окна". Диаграмма вытянется по горизонтали, чтобы полностью занять окно. Если Вы еще раз войдете в меню "Вид", Вы увидите, что возле пункта "По размеру окна" появилась галочка. Теперь выберите пункт "Вид/ Во весь экран" и верните экран к прежнему размеру.

В Главном меню имеется пункт "Диаграммы". Раскройте это меню. Первые четыре пункта меню: "Тип диаграммы", "Исходные данные", "Параметры диаграммы", "Размещение". По очереди войдите в эти пункты меню и убедитесь, что появляющиеся диалоговые окна аналогичны диалоговым окнам Мастера диаграмм (но не вполне!). Вы можете с помощью этих пунктов вносить изменения в вид диаграммы. Например, добавьте горизонтальные линии сетки, таблицу значений (обратите внимание, что, таблица транспонирована по сравнению с исходной, расположенной на рабочем листе).

ПРИМЕР 3.

Скопируйте лист с диаграммой, построенной в примере 1.

Изменение диаграммы с использованием контекстного меню. Для вызова контекстного меню нужно сначала выделить форматируемый элемент. Это можно сделать левой или правой кнопкой мыши. Можно выделить элемент с помощью клавиатуры. Для этого последовательно нажимайте клавишу "Стрелка вверх" или "Стрелка вниз". Вокруг выделяемого элемента появляются маркеры. При этом в окне имен появляется название элемента (Область диаграммы. Область построения, Легенда, Ось значений и т.д.). Внутри элементов диаграммы имеются свои элементы, например элементы легенды, элементы ряда. Они выделяются последовательным нажатием клавиш "Стрелка влево" и "Стрелка вправо". (Поэкспериментируйте.) Если элементы достаточно крупные, то проще выделятьих с помощью мыши. Например, мы хотим выделить самый левый столбик гистограммы. Один щелчок мышью по столбику выделит все столбики, отвечающие первому ряду, второй щелчок выделяет элемент ряда (следите только, чтобы эти два последовательных щелчка не слились в двойной щелчок — это вызовет диалоговое окно "Формат ряда данных").

Выделив элемент, подлежащий форматированию, нажмите правую кнопку мыши и выберите в контекстном меню пункт "Формат..." либо нажмите комбинацию клавиш Сtr1+1.

1. Сделаем фон диаграммы прозрачным. Щелкнем правой кнопкой мыши на свободной области диаграммы и выберем в контекстном меню пункт "Форматирование области построения". В диалоговом окне выберем "Заливка: прозрачная". Выйдя из окна диалога, нажмем Еsс, чтобы снять выделение.

2. Изменим шкалу значений. Выделим ось значений. Выберем пункт контекстного меню "Формат оси". Укажем для шкалы максимальное значение 500, цену основных делений 100. Вернемся к диаграмме и снимем выделение.

3. Установите перекрытие столбиков на диаграмме -20 (отрицательное число) и ширину зазора 70 ("Формат рядов данных/ Параметры").

4. Покрасьте другим цветом второй столбик первого ряда данных.

5. Выведите легенду внизу, окружив ее рамкой с тенью.

6. Значения (подписи) рядов данных выведите шрифтом величиной 8 пунктов.

7. Выберите подходящий узор для второго ряда данных ("Формат рядов данных/ Вид/ Способы заливки/ Узор").

Вам нужно самостоятельно экспериментировать с различными элементами диаграммы. Вы всегда можете отменить результаты своих действий нажатием клавиш Сtr1+Z.

Упражнение 3. Иногда мелкие детали диаграммы на экране плохо различимы (на печати они будут выглядеть намного лучше из-за более высокого разрешения принтера). Воспользуйтесь пунктом меню "Вид/ Масштаб", чтобы подробнее рассмотреть детали диаграммы. (Предварительно снимите флажок "Вид/ По размеру окна".)

Когда Вы выделяли ряд данных "Приход", то в поле ввода появлялась функция:

= РЯД(Фирма!$В$1.Фирма!$А$2:$А$5.Фирма!$В$2:$В$5,1)

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

ПРИМЕР 4.

Скопируйте лист с диаграммой предыдущего примера на новый лист.

Добавление данных на диаграмму. Предположим, в таблицу (в блок А6:С6) добавлены новые данные: 1996 320 270. Нужно добавить их и на диаграмму, не перестраивая ее заново. Скопируйте новые данные в буфер (клавиши Сtr1+С), перейдите на лист с диаграммой, извлеките данные из буфера (клавиши Сtr1+V). В нашем случае получилось не совсем то, что нужно: добавились два столбика, отвечающие числам 320 и 270, но на оси категорий под ними не появилось отметки 1996. Поэтому выполните откат (Сtrl+Z) и воспользуйтесь более строгим средством. Находясь на листе с диаграммой, выберите в меню "Диаграмма/ Добавить данные". Появляется диалоговое окно "Новые данные". Вводим в него диапазон =Фирма!$А$6:$С$6 (можно свернув данное диалоговое окно, перейти на лист Фирма и выделить диапазон данных мышью). Появляется диалоговое окно "Специальная вставка". Устанавливаем переключатель "Добавить новые значения рядов" и устанавливаем флажок "Категории (подписи оси X) в первом столбце". В результате этой операции данные добавлены на диаграмму корректно.

Типы диаграмм

Обзор типов диаграмм имеется в Справке. Посмотрите раздел "Работа с диаграммами/ Изменение типа диаграммы/ Примеры типов диаграмм".

ПРИМЕР 5.

Построение линейного графика. Для исходных данных примера 1 постройте диаграмму, на первом шаге выберите тип "График". Остальные шаги делаются аналогично построению гистограммы.

В полученном графике имеется особенность: порядковые номера годов расположены между метками делений на оси категорий. Для графика уместнее было бы видеть подписи под метками оси. Чтобы добиться этого, вызовите контекстное меню для оси категорий, выберите пункт "Формат оси/ Шкала" и снимите флажок "Пересечение с осью Y (значений) между категориями". График примет привычный вид.

Дополнительные задания

1. Удалите лист с диаграммой, построенной в примере 1 и, не торопясь, заново повторите все шаги построения. При этом экспериментируете: перемещайте легенду, вводите линии сетки и т.д. Потом еще раз постройте эту же диаграмму, но постарайтесь ускорить темп.

2. Построить диаграмму на основе таблицы прихода и расхода. Ряды данных должны быть расположены в строках, категории — в В1:С1, метки рядов — в А2:А5. (Перетащите содержимое ячейки А1 за пределы списка, например, в А8; с помощью Мастера диаграмм постройте диаграмму, после чего верните содержимое А1 на место.)

3. Редактируя функцию для первого ряда данных в примере 3, уберите столбик, отвечающий 1995 г. Отмените это действие.

4. Для графика из примера 5 поменяйте форму и цвет маркеров.

6. Постройте круговую диаграмму "Год— Расход".

Указание. Перед вызовом Мастера диаграмм выполните выделение несмежных блоков А1:А5и С1:С5.

7. Дана таблица "Закладкана хранение овощей и фруктов, в тоннах" (рис. 5.4).

	План	Факт
Картофель
Яблоки
Морковь

Рис. 5.4

Построить столбиковую диаграмму. В таблицу добавить строку Лук 230 187.

Добавить эти данные на диаграмму, не перестраивая ее.

8. Треугольник на плоскости задан координатами своих вершин. Изобразите его на диаграмме.

Указание. Использовать точечную диаграмму (точки соединены отрезками).

Контрольные вопросы

1. Опишите этапы построения диаграмм.

2. Как внести изменение в диаграмму с помощью Главного меню?

3. Как внести изменение в диаграмму, используя контекстное меню?

4. Как выполнить добавление новых данных в существующую диаграмму?

 

Практическая работа 12

Форматирование таблиц. Использование условного форматирования

  Порядок работы Изменение внешнего вида таблицы без изменения ее содержимого называется форматированием. В Ехсеl имеются богатые…

ПРИМЕР 5.

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

1. Выберите щелчком на ярлычке неиспользуемый рабочий лист или создайте новый (Вставка > Лист). Дважды щелкните на ярлычке листа и переименуйте его как Ведомость.

2. В первую строку рабочего листа, начиная с ячейки В1, введите названия экзаменов.

3. В первый столбец рабочего листа, начиная с ячейки А2, введите фамилии студентов.

4. Заполните таблицу экзаменационными оценками по своему усмотрению. Оценки должны изменяться от 2 до 5 баллов.

5. Выделите ячейку А2 и дайте команду Формат > Условное форматирование.

6. В раскрывающемся списке на панели Условие 1 выберите вариант формула.

7. В поле для формулы введите следующую формулу: = МИН($В2:$Е2)>3. Обратите внимание на способ использования абсолютных и относительных ссылок в формуле, так как ее планируется распространить на всю таблицу. Эта формула рассчитана на четыре экзамена, при другом их числе выбранный диапазон несколько изменится (измените его для пяти экзаменов).

8. Щелкните на кнопке Формат. В открывшемся диалоговом окне Формат ячеек выберите вкладку Вид и щелкните на светло-зеленом цвете для его использования в качестве фона ячеек. Щелкните на кнопке ОК.

9.Щелкните на кнопке А также, чтобы задать второе условие форматирования. Далее действуйте аналогично тому, как указано в п.8. Нужная формула должна иметь вид: = МИН($В2:$Е2)<3 (измените его для пяти экзаменов).

10. Подобно тому, как указано в п.9, задайте светло-розовый фон для ячеек.

11. Щелкните на кнопке ОК. Фон ячейки А2 должен измениться, если соответствующий студент не имеет троек или, напротив, имеет задолженность.

12. Выделите весь диапазон ячеек ведомости и дайте команду Формат > Условное форматирование. Диалоговое окно Условное форматирование должно содержать настройки, подготовленные для ячейки А2.

13. Щелкните на кнопке ОК. Условное форматирование распространится на всю выделенную область с автоматической коррекцией относительных ссылок. Убедитесь, что формат ведомости соответствует тому, что требовалось.

Дополнительные задания

1. Заполнить и отформатировать таблицу, представленную на рис. 6.3.

План погашения кредита
М е с я ц	Непогашенная сумма основного долга, тыс. руб	Процентный платеж, тыс. руб	Месячная выплата основного долга, тыс. руб	Сумма месячного погашенного взноса, тыс. руб.
		36.0
		30.0
		24.0
		18.0
		12.0
		6.0
Итого		126.0	1800.0	1926.0

Рис. 6.3

2. Для составления налоговой карточки нужно внести в ячейки месячный доход, а строкой ниже вычислить доход по нарастающей с начала года (рис. 6.4).

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

	А	В	С	D	…	М	N
		янв	фев	мар	...	дек	Итого
	доход	500.00		500.00	...	500.00	4500.00
	с нач. года	500.00	500.00	1000.00	...	4500.00	4500.00

Рис. 6.4

 

Контрольные вопросы

1. Как выполнить автоформатирование таблицы?

2. Как избавиться от наложенного формата?

3. Описать возможности, предоставляемые на вкладках Выравнивание, Шрифт, Граница и Вид окно Формат ячеек.

4. Для чего и как используется условное форматирование?

 

Создание презентаций в MS Power Point

Практическая работа 13

Разработка презентации

  Порядок работы Краткая справка. Создание презентации можно производить двумя способами – вручную (без использования заготовок) и с…

Дополнительные задания

Задание 1. Изменить стиль заголовков.

Для этого выполните команду Вид/Образец/Образец слайдов. Щелкните по заголовку, измените тип шрифта (вместо Times New Roman используйте Arial Cyr или наоборот).

Вернитесь в слайд командой Вид/Обычный.

Задание 2. Создать комплект слайдов о вашем учебном заведении.

Задание 3. Подготовьте раздаточный материал для слушателей.

Распечатайте раздаточный материал, располагая на странице по два слайда.

Выполните команду Файл/Печать. В раскрывающемся списке Печатать выберите Выдачи с количеством слайдов на странице – 2.

 

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

Краткая справка. Однотипные слайды удобно копировать. Если установить до копирования параметры перехода слайда, то все новые слайды уже будут иметь соответствующие установки.

Контрольные вопросы

1. Расскажите о назначении программы PowerPoint.

2. Что такое "Мастер презентации"?

3. Как задать стиль презентации?

4. Что такое цветовая гамма презентации?

5. Как вставить в слайд презентации картинку?

6. Как вставить в слайд презентации диаграмму?

7. Как вставить в слайд презентации таблицу?

8. Что такое структура презентации?

9. Что такое автофигуры?

10. Как и для чего можно скрыть слайд в презентации?

11. Как создать текст слайда на фоне желаемого рисунка (диаграммы)?

12. Что такое анимация?

13. Что такое размётка слайда?