Урок 20
Прикладная среда табличного процессора Excel
Использование функций и логических формул
Выполнив задания этой темы, вы научитесь:
- использовать в формулах функции;
- создавать формулы с логическими функциями;
- изменять условия с логическими функциями.
Основные понятия и правила записи функции
В ячейки электронной таблицы можно вводить встроенные функции. Напомним, что с понятием функции тесно связано понятие аргумента, который надо задать, чтобы получить значение функции. Такими аргументами могут быть как конкретные данные, так и ссылки на них. Функции в электронной таблице реализованы с помощью стандартных программ, которые можно вызывать по заранее известным именам. Количество различных функций, которые можно использовать в формулах, очень велико и может варьироваться в различных версиях табличных процессоров.
Все функции для удобства выбора и обращения к ним объединены по назначению в группы, называемые категориями: математические, статистические, текстовые, логические, финансовые, функции даты и времени и др. Использование любых функций в формулах происходит по одинаковым правилам:
♦ Каждая функция имеет свое уникальное имя.
♦ При обращении к функции после ее имени в круглых скобках указывается список аргументов, разделенных точкой с запятой.
♦ Ввод функции в ячейку начинается со знака равенства, а затем указывается ее имя.
Пример формулы, использующей функцию:
Для вычисления произведения сумм значений блоков ячеек А1:А9 и В7:С10 и сохранения результата в ячейке А10 можно использовать функцию СУММА (табл. 4.2). Для этого необходимо ввести в ячейку А10 формулу: = СУММА(А 1:А9) * СУММА(В7:С10)
Таблица 4.2. Некоторые функции табличного процессора Excel
Логические функции с простым условием
Остановимся более подробно на логических функциях, использование которых позволяет решать с помощью табличного процессора логические задачи. Для понимания и грамотного применения логических функций вспомним некоторые основные правила формальной логики.
В логической функции обязательно используется условие. Условие представляет собой величины или выражения одного типа, связанные одним из знаков отношений: = (равно), <> (не равно), > (больше), < (меньше), >= (больше или равно), <= (меньше или равно).
Например: А > 5; 3 < 5; ах² + + bх + с > 0, и т. п.
Такие условия называются простыми. Они могут быть составлены из данных, ссылок и выражений.
Любое условие может принимать одно из двух возможных значений: ИСТИНА (логическая единица) или ЛОЖЬ (логический ноль).
Если условие составлено из констант (постоянных величин), то его значение не изменяется. Например, условие 3 < 5 всегда имеет значение ИСТИНА. Если же в условие входят переменные величины, то оно может быть как истинным, так и ложным, в зависимости от значений переменных. Например, условие А > 5 будет иметь значение ИСТИНА, если А = 7, и значение ЛОЖЬ, если А = 2.
Для проверки условия в табличном процессоре используется функция ЕСЛИ. Функция ЕСЛИ имеет следующий формат записи: ЕСЛИ(условие;выражение_если_истина; Пример использования функции проверки простого условия:
Значение ячейки С10 зависит от соотношения значений ячеек А1 и А2: Логические функции со сложным условием
Сложным, или составным, условием называют несколько простых условий, связанных с помощью логических операций: И (логическое умножение), ИЛИ (логическое сложение) и др.
Рассмотрим некоторые жизненные ситуации, когда выбор вашего решения зависит от нескольких условий: Для вычисления результата сложного условия необходимо знать, как определяется результат соответствующей логической операции (И, ИЛИ и др.). Ответ на этот вопрос дает таблица истинности (табл. 4.3). В этой таблице приняты обозначения: цифра 1 соответствует значению ИСТИНА, цифра 0 — значению ЛОЖЬ. В табл. 4.3 приведена таблица истинности для логических операций И и ИЛИ над двумя условиями, обозначенными как Условие! и Усповие2.
Таблица 4.3. Таблица истинности для логических операций И и ИЛИ
Для выполнения указанных логических операций в электронной таблице как раз и предусмотрены логические функции И и ИЛИ, аргументами которых являются условия.
Для пояснения сказанного рассмотрим пример использования логической функции ЕСЛИ, в которую входит сложное логическое условие с функцией И.
Пример использования логических функций при проверке сложного условия:
Предположим, что электронная таблица используется для хранения и обработки среднего балла по школьному аттестату зрелости и оценок по результатам вступительных экзаменов в вуз (рис. 4.12). Пусть в ячейке А1 указан средний балл по аттестату, а в диапазоне А2:А5 хранятся экзаменационные оценки по четырем предметам, полученные на вступительных экзаменах. В ячейке А6 вычисляется сумма вступительных баллов ученика.
Рис. 4.12. К примеру использования логических функций
Требуется сравнить полученный суммарный балл с проходным баллом, который хранится в ячейке В1. В ячейке А7 должен быть выведен результат сравнения:
Для решения поставленной задачи в ячейку А7 необходимо ввести логическую функцию ЕСЛИ следующего вида: Если оба простых условия (А6>=В1 и А1>4) истинны, то в соответствии с таблицей истинности будет истинно и проверяемое условие в функции ЕСЛИ, и в ячейке А7 будет выведен текст из ячейки С1. Иначе (то есть, если ложно хотя бы одно из условий, А6>=В1 или А1>4) в ячейке А7 появится текст из ячейки С2.
В таблице Расписание.xls рассчитайте стоимость железнодорожных билетов для поездов разных категорий: пассажирские, скорые, фирменные. Цена билета зависит от времени движения поезда к пункту назначения и категории поезда. Каждой категории поезда соответствует определенный коэффициент, на который следует умножать время в пути (в минутах) для расчета цены билета: пассажирский — 0,5 р., скорый — 1,5 р., фирменный — 1,7 р. Организуйте таблицу Расписание.х1э так, чтобы пересчет цены билета можно было бы осуществлять, изменяя значение ячейки Категория.
В процессе редактирования таблицы вы познакомитесь с основными технологическими операциями создания формул.
Технология работы
1. Запустите табличный процессор.
2. Откройте документ Расписание.xls.
3. Для задания категории поезда выполните следующие действия: 4. Дополните таблицу столбцом Цена (см. рис. 4.11): 5. Задайте денежный формат чисел для диапазона ячеек F4:F11, используя контекстное меню: 6. Создайте в ячейке F5 логическую формулу для расчета стоимости билета.
Договоримся, что значение ячейки Категория для пассажирского поезда — 1, для скорого — 2, для фирменного — 3. Структура формулы должна быть следующей:
=ЕСЛИ($Е$1=1;МИНУТЫ(Е5)*0,5; Рассмотрим последовательность действий по созданию этой формулы: 7. Скопируйте формулу из ячейки F5 в каждую ячейку диапазона F6:F11, используя раскрывающееся меню кнопки Вставить на
панели инструментов: 8. Изменяя значение категории поезда в ячейке Е1, проанализируйте результаты.
9. Измените ширину столбца F так, чтобы его ширина точно соответствовала длине заголовка.
10. Измените ширину столбца F так, чтобы числовые данные были отображены полностью.
11. Сохраните документ и закройте табличный процессор командой Файл ► Выход.
1. Какие логические функции вам известны?
2. Что такое условие в логической функции и по каким правилам оно записывается? Приведите примеры.
3. В чем отличие сложного условия от простого? Приведите примеры.
4. Расскажите о сути логической операции И. Приведите примеры записи логической функции И.
5. Расскажите о сути логической операции ИЛИ. Приведите примеры записи логической функции ИЛИ.
6. Объясните, что такое таблица истинности. Приведите пример.
7. В чем отличие функции от формулы? Приведите примеры записи функции и формулы.
Задание 4.9.
Дополните созданные вами самостоятельно таблицы одним или несколькими столбцами, использовав в них логические и прочие функции.
Выполнив задания этой темы, вы научитесь: Для создания диаграммы необходимо: Редактирование диаграммы производится с помощью команд контекстного меню, позволяющих изменять ее параметры: заголовки, легенду, подписи рядов и данных. Можно добавлять новые данные для построения диаграммы или удалить построенные ранее диаграммы.
Форматирование диаграммы производится с помощью команд меню Формат, которые позволяют: Используя данные таблицы Расписание.xls, создайте столбчатую диаграмму распределения времени стоянок по станциям и круговую объемную диаграмму распределения времени в пути. Отредактируйте диаграммы.
В результате выполнения задания вы освоите основные технологические операции создания и редактирования диаграмм.
Технология работы
1. Запустите табличный процессор.
2. Откройте документ Расписание.xls.
3. Создайте столбчатую диаграмму распределения времени стоянок по станциям: 4. На следующем шаге Мастера диаграмм задайте подписи по оси X: 5. На следующем шаге Мастера диаграмм задайте подписи осей X и У: 6. На следующем шаге Мастера диаграмм задайте расположение диаграммы, выбрав переключатель Поместить диаграмму на отдельном листе, и щелкните на кнопке Готово.
7. Измените шрифт заголовка диаграммы: 8. Создайте диаграмму распределения времени в пути: 9. Задайте подписи по оси X: 10. На следующем шаге мастера задайте подписи данных на диаграмме: 11. Задайте расположение диаграммы, выбрав переключатель Поместить диаграмму на отдельном листе, и щелкните на кнопке Готово.
12. Сохраните документ и закройте табличный процессор командой Файл ► Выход.
Задание 4.11.
Представьте данные созданной в предыдущей теме таблицы в виде диаграмм. Используйте несколько типов диаграмм. Задайте им различное оформление.
Задание 4.12.
Создайте таблицу успеваемости нескольких учеников по разным предметам. Отобразите эти данные на нескольких диаграммах. Используйте различные возможности оформления диаграмм.
♦ если значение ячейки А1 больше значения ячейки А2, то в ячейке С10 надо записать число 1;
♦ если значение ячейки А1 меньше или равно значению ячейки А2, то в ячейке СЮ надо записать число 0.
Для выполнения этого задания надо ввести в ячейку С10 функцию
=ЕСЛИ(А>А2; 1;0)
♦ Если вы стоите на перекрестке И горит зеленый свет светофора, то вам разрешено переходить улицу.
♦ Если у вас есть собака И вы ее выдрессировали, то любая ваша команда будет ей понятна.
♦ Если в субботу вечером вы пойдете с друзьями в театр ИЛИ в клуб, ИЛИ в гости, ИЛИ погулять, то вы отлично проведете время.
Функции И и ИЛИ имеют следующий формат записи:
И(усповие1;условие2;...) ИЛ И(условие1 ;условие2;...)
Правила определения результата логических операций по таблице истинности:
✔ Результатом логической операции И будет ИСТИНА тогда и только тогда, когда истинны все перечисленные в аргументах условия (их количество не ограничено).
✔ Результатом логической операции ИЛИ будет ИСТИНА, если истинно хотя бы одно из перечисленных в аргументах условий.
♦ если условие (суммарный балл >= проходному баллу И средний балл по аттестату >4), то выводится текст из ячейки С1: «Поздравляем, вы успешно сдали экзамены и приняты»;
♦ если условие не выполнено, то выводится текст из ячейки С2: «Сожалеем, но вы не прошли по конкурсу».
♦ =ЕСЛИ(И(А6>=В1;А1>4);С1;С2)
Задание 4.8
♦ выберите ячейку D1 и введите текст Категория;
♦ выберите ячейку Е1 и введите первоначальное значение категории, равное 1.
♦ выберите ячейку F3;
♦ введите текст Цена и нажмите клавишу Enter.
♦ выделите диапазон ячеек F4:F11;
♦ вызовите контекстное меню и выберите в нем команду Формат ячеек;
♦ в появившемся диалоговом окне выберите вкладку Число;
♦ выберите в списке числовых форматов тип Денежный;
♦ если обозначение денежных единиц р. не будет выбрано автоматически, выберите его в списке Обозначение;
♦ в поле Число десятичных знаков укажите 2 знака:
♦ нажмите кнопку ОК.
♦ выделите ячейку F5;
♦ выберите команду меню Вставка ► Функция;
♦ в открывшемся диалоговом окне выберите категорию — Логические, функцию — ЕСЛИ и нажмите кнопку ОК;
♦ в окне ввода аргументов функции в поле Лог_выражение наберите $Е$1=1: щелкните мышью в ячейке Е1, нажмите клавишу F4, затем введите =1;
♦ в поле Значение_если_истина введите МИНУТЫ(Е5)*0,5;
♦ нажмите кнопку ОК;
♦ скопируйте полученную формулу ЕСЛИ($Е$1=1;МИНУТЫ(Е5)*0,5), добавьте в нее перед последней скобкой точку с запятой и вставьте после нее скопированную формулу, заменив значение категории на 2, а коэффициент — на 1,5;
♦ добавьте после числа 1,5 еще одну точку с запятой и снова вставьте скопированную формулу, заменив значение категории на 3, а коэффициент — на 1,7;
♦ добавьте после числа 1,7 точку с запятой и вставьте текст "Неверное значение категории";
♦ нажмите клавишу Enter;
♦ сверьте структуру формулы с образцом.
♦ выделите ячейку F5, откуда надо скопировать формулу;
♦ щелкните на кнопке Копировать на панели инструментов;
♦ выделите диапазон ячеек F6: F11;
♦ щелкните на стрелке справа от кнопки Вставить и выберите в открывшемся меню команду Формулы.
При копировании формулы абсолютная ссылка не изменяется.
Если данные не помещаются в ячейке, они заменяются знаками #####.
Контрольные вопросы
Задания для самостоятельной работы
Представление данных в виде диаграмм
- создавать различные типы диаграмм;
- форматировать объекты диаграммы;
- изменять размеры диаграммы.
Создание и редактирование диаграмм
♦ выделить диапазон ячеек, содержащий данные для ее построения;
♦ нажать кнопку Мастер диаграмм на панели инструментов;
♦ выбрать тип диаграммы из предлагаемого набора;
♦ задать основные и дополнительные параметры диаграммы.
Форматирование диаграмм
♦ изменить тип диаграммы;
♦ изменить используемые шрифты;
♦ изменить цвет и узор объектов диаграммы;
♦ задать масштаб и формат вертикальной и горизонтальной осей;
♦ выбрать вид рамки, ограничивающей диаграмму.
Задание 4.10
♦ выделите диапазон ячеек С5:С10;
♦ выберите команду меню Вставка ► Диаграмма;
♦ в появившемся окне Мастера диаграмм выберите тип диаграммы — Гистограмма, вид — объемный;
♦ щелкните на кнопке Далее.
♦ на вкладке Диапазон данных установите переключатель Ряды в строках;
♦ перейдите на вкладку Ряд;
♦ щелкните в поле Подписи по оси X и выделите в таблице диапазон ячеек А5:А10;
♦ задайте подпись ряда, щелкнув в поле Имя и выделив в таблице ячейку СЗ;
♦ щелкните на кнопке Далее.
♦ перейдите на вкладку Заголовки;
♦ в поле Название диаграммы введите Распределение стоянок;
♦ в поле Ось X (категорий) введите Станция',
♦ в поле Ось Y (значений) введите Время',
♦ перейдите на вкладку Легенда;
♦ сбросьте флажок Добавить легенду;
♦ щелкните на кнопке Далее.
♦ щелкните мышью на заголовке диаграммы — вокруг текста появится рамка с маркерами выделения;
♦ щелкните на заголовке правой кнопкой мыши и выберите в контекстном меню команду Формат заголовка диаграммы;
♦ перейдите на вкладку Шрифт;
♦ в появившемся диалоговом окне выберите полужирное начертание шрифта, размер — 16, цвет — синий;
♦ щелкните на кнопке ОК.
♦ выделите диапазон ячеек Е5:Е11;
♦ выберите команду меню Вставка ► Диаграмма;
♦ в появившемся окне Мастера диаграмм выберите тип диаграммы — Круговая, вид — объемный;
♦ щелкните на кнопке Далее.
♦ на вкладке Диапазон данных установите переключатель Ряды в столбцах;
♦ перейдите на вкладку Ряд;
♦ щелкните в поле Подписи по оси X и выделите в таблице диапазон ячеек А5:А11;
♦ задайте подпись ряда, щелкнув в поле Имя и выделив в таблице ячейку ЕЗ;
♦ щелкните на кнопке Далее.
♦ выберите вкладку Подписи данных;
♦ установите флажок Включить в подписи — значения;
♦ перейдите на вкладку Легенда;
♦ установите флажок Добавить легенду, выберите переключатель Размещение — справа;
♦ щелкните на кнопке Далее.
Задания для самостоятельной работы
