Гипермаркет знаний>>Информатика>>Информатика 11 класс>> Информатика: Основные понятия баз данных

Конспект урока к предмету «Информатика 11 класс» по теме «Работа с фомулой»

Урок 2 Работа с формулами

2.1. Общие сведения
2.2. Массивы формул
2.3. Функции
2.4. Условная функция (функция ЕСЛИ())
2.5. Сообщения об ошибках

2.1. Общие сведения

Таблица в Excel может содержать как основные (непосредственно введеные), так и производные (вычисляемые) данные. Достоинство электронных таблиц заключается в том, что они позволяют организовать автоматическое вычисление производных данных. Для этой цели в ячейках таблицы использую. Структура или порядок элементов в определяет конечный результат вычислений.

Формула может состоять из математических операторов, констант, ссылок на ячейку (переменные) и функций. Результатом выполнения формулы - новое значение, содержащееся в ячейке, где находится формула. Формула всегда начинается со знака равенства " = ". В формуле используются следующие операторы:

1. " + " (сложить),
2. " - " (вычесть),
3. " * " (умножить),
4. " / " (разделить) и
5. " ^ " (возвести в степень).

Порядок вычислений определяется обычными математическими законами и может быть изменен использованием скобок.

Примеры формул: =2*(А1+В2); =F7*С4+B1; =(А1+В1)^3

Константы – текстовые или числовые значения, которые вводятся в ячейку и не могут быть изменены во время вычислений.

Ссылка на ячейку или группу ячеек – способ, которым указывается конкретная ячейка или несколько ячеек. У каждой ячейки есть свой адрес. Он однозначно определяется номерами столбца и строки, то есть, именем ячейки. Адресация по методу "левее", "правее", "ниже" и т.д. называется относительной адресацией. По умолчанию Excel рассматривает адреса ячеек как относительные. При выполнении копирования формулы в режиме Автозаполнения Excel изменяет адреса последующих ячеек.

Однако иногда возникает необходимость сохранить абсолютный адрес ячейки, т.е. чтобы при использовании режима Автозаполнение адрес не менялся. Для того, чтобы задать ссылку на ячейку как абсолютную, надо поставить перед обозначением номера столбца или номера строки символ $. Таким образом, ссылка на ячейку, например А1, может быть записана в формуле четырьмя способами: А1, $А1, А$1, $А$1. При заполнении ячеек формулой врежиме автозаполнения, абсолютной рассматривается та часть адреса, перед которой стоит знак $.

Для обращения к группе ячеек используются специальные символы:

1. : (двоеточие) – формирует обращение к блоку ячеек. Через двоеточие указывается левая верхняя и правая нижняя ячейки блока. Например : С2: D4 – обращение к ячейкам С2, С3, С4, D2, D3, D4.
2. ; (точка с запятой) – обозначает объединение ячеек. Например, А1;:В2;D4:D7 – обращение к ячейкам А2, В2, D4, D5, D6, D7.

Пример 2.1. Составить таблицу расчета стоимости компьютера.


 
1. В ячейку В2 заносится текущее значение курса.
2. В ячейки В5:В11 вносится цена товара в $.
3. В ячейку С5 записывается формула =В5*$B$2 (ссылка на ячейку В5 - относительная адресация; ссылка на ячейку В2 - абсолютная алресация).
4. Ячейки В6:В11 заполняются в режиме Автозаполнение.
5. К ячейкам В5:С12 применен формат Финансовый., причем для ячеек В5:В12 выбрано обозначение $ Английский, а для ячеек С5:С12 выбрано обозначение р.
6. В ячейки В12 и С12 записана функция СУММ. Для этого достаточно нажать кнопку Автосумма.

2.2. Массивы формул

Массивы формул удобно использовать для введения однотипных формул и обработки данных в виде таблиц. Например, для вычисления квадратов, размещенных в ячейках B1:E1 , вместо ввода формул в каждую ячейку можно ввести одну формулу – массив для всех ячеек. Microsoft Excel добавляет вокруг массива формул фигурные скобки { }, по которым его можно отличить.

Для создания массива формул необходимо:

1. выделить ячейки, в которых должен находиться массив формул;
2. ввести формулу обычным способом, указав в качестве аргументов группу ячеек-аргументов;
3. для завершения ввода вместо клавиши Enter нажать комбинацию клавиш Ctrl+Shift+Enter. 

Для редактирования массива формул необходимо:

1. выделить ячейки, в которых находится массив;
2. щелкнуть мышью внутри строки формул и отредактировать формулу;
3. для завершения нажать комбинацию клавиш Ctrl+Shift+Enter.

2.3 Функции

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

Примеры:

=СУММ(В5:В11) - сумма ячеек В5, В6, В7, В8, В9, В10, В11;
=КОРЕНЬ(D6) - извлечение корня квадратного из содержимого ячейки D6;
=EXP(A1*LN(B1))

В программе Excel можно использовать свыше 400 функций, которые разделены на категории: математические, финансовые, статистические, текстовые, логические, даты и времени.

Функцию в ячейку можно ввести непосредственно, если вы знаете как, или воспользоваться Мастером функций. В последнем случае необходимо::

1. выделить ячейку для формулы;
2. вызывать Мастер функций с помощью команды Функция меню Вставка или кнопки Вставка функции;
3. в диалоговом окне Мастер функций, в поле Категория необходимо выбрать тип функции, затем в списке Функция выбрать нужную функцию;
4. щелкнуть кнопку ОК ;


 
5. В полях Число1, Число2 и т.д. следующего окна ввести аргументы функции (числовые значения или ссылки на ячейки);


 
6. чтобы указать аргументы, можно щелкнуть кнопку  , находящуюся справа от поля, и выделить мышью ячейки, содержащие аргументы функции; для выхода из этого режима следует щелкнуть кнопку  , которая находится под строкой формул;
7. щелкнуть ОК .

2.4. Условная функция (функция ЕСЛИ())

Часто при решении задач требуется вычислить значение исходя из некоторых условий. Например, при оценке выполнения бюджета требуется сравнить значение расходов по статье, заложенное в бюджет с реальными расходами и по результатм сравнения сообщить "расходы не превышают бюджетные" или "расходы превышают бюджетные". Для этого используется функция ЕСЛИ, которая проверяет условия для значений и формул. Она возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

Синтаксис

ЕСЛИ ( <логическое выражение> ; <значение если истина> ; <значение если ложь>)

Логическое выражение (условие) - это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100: В1>B2 и т.д.

Значение если истина - это значение, которое возвращается, если логическое выражение равно ИСТИНА, т.е. условие выполнено.

Значение если ложь - это значение, которое возвращается, если логическое выражение равно ЛОЖЬ или условие не выполнено.

До 7 функций ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов для конструирования более сложных проверок.

Пример 2.2. С помощью электронной таблицы найти корни квадратного уравнения.

1. В ячейки А3, В3, С3 внесем значение коэффициентов a, b, c соответственно.
2. В ячейку D3 запишим формулу вычисления дискриминанта: =B3^2-4*A3*C3
3. В ячейку Е3 запишим формулу вычисления x1: =(-B3+КОРЕНЬ(D3))/(2*А3)
4. В ячейку F3 запишим формулу вычисления x2: =(-B3-КОРЕНЬ(D3))/(2*А3)
5. Если есть необходимость, скопируем формулы в режиме Автозаполнение на несколько строк.


 
2.5. Сообщения об ошибках

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

1. #### – значение ошибки выводится в том случае, когда ячейка содержит число, дату или время, число знаков которого больше ширины ячейки, или в том случае, если ячейка содержит дату и/или формулу, которая выводит отрицательный результат. Чтобы исправить ошибку необходимо увеличить ширину столбца или выбрать другой числовой формат .

2. #ИМЯ? – не верно введено имя ячейки (чаще всего возникает из-за того что имя столбца указывается русскими буквами) или имя функции, в формулу введен текст, не заключенный в двойные кавычки:

3. #ЗНАЧ! – использован недопустимый тип аргумента. Вместо числового или логического (ИСТИНА или ЛОЖЬ) значения введен текст, и Microsoft Excel не может преобразовать его к нужному типу данных.

4. #ДЕЛ/0! – в формуле делается попытка деления на нуль. Для устранения ошибки проверьте написание формулы (возможно, неправильно поставлены скобки) или используйте функцию ЕСЛИ();

5. #ЧИСЛО! – в функции с числовым аргументом используется неприемлемый аргумент, или нарушены правила задания операторов, принятые в математике;

6. #Н/Д – является сокращением термина “Неопределенные Данные”. Это значение помогает предотвратить использование ссылки на пустую ячейку. Введите в ячейки листа значение #Н/Д , если они должны содержать данные, но в настоящий момент эти данные отсутствуют. Формулы, ссылающиеся на эти ячейки, тоже будут возвращать значение #Н/Д вместо того, чтобы пытаться производить вычисления. ;

7. #ПУСТО! – появляется, когда задано пересечение двух областей, которые в действительности не имеют общих ячеек. ;

8 #ССЫЛКА! – в формуле задана ссылка на несуществующую ячейку. Ячейки, на которые ссылаются формулы, были удалены или в эти ячейки было помещено содержимое других скопированных ячеек;

9. #ССЫЛКА! – в формуле задана ссылка на несуществующую ячейку. Ячейки, на которые ссылаются формулы, были удалены или в эти ячейки было помещено содержимое других скопированных ячеек.

Вопросы для самоконтроля

1. Как записывается формула в Excel?
2. Какие операторы используются при записи формул?
3. Как можно изменить порядок вычисления в формуле?
4. Что такое относительная и абсолютная адресация?
5. Как задаются группы адресов ячеек?
6. Как задается массив формул?
7. Как вводится функция?
8. Как проверяются условия при вычислении?
9. Каковы основные ошибки при вычислении формул?

Отослано Чебаном Л.И., учителем информатики Международного лицея "Гранд"