Работа с формулами и функциями

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

Операторы, используемые в формулах

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

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

 

Обозначение оператора	Пример использования оператора	Результат вычисления
+ (сложение)	=А1+А7	сумма значений ячеек А1 и А7
	=25+14	39
– (вычитание)	=В7 - С5	разность значений ячеек В7 и С5
	=100-25	75
* (умножение)	=А9*С2	произведение значений ячеек А9 и С2
	=125*125	15625
/ (деление)	=С5/В5	частное значений ячеек С5 и В5
	=895/23,55	38,004246
% (процент)	=5%	0,05
	=А5%	процент от значения ячейки А5
^ (возведение в степень)	=5^4	625
	=А6^3	значение ячейки А6 в кубе

Операторы сравнения используются для сравнения как числовых, так и текстовых значений, а результатом операций сравнения всегда являются логические значения ИСТИНА или ЛОЖЬ.

 

Обозначение оператора	Пример использования оператора	Результат вычисления
= (равно)	=А5=А6	проверяет равенство значений ячеек А5 и А6
> (больше)	=А5>А6	проверяет, действительно ли значение ячейки А5 больше, чем А6
< (меньше)	=F2<100	проверяет, действительно ли значение ячейки F2 меньше 100
>= (больше или равно)	=F2>=100	проверяет, действительно ли значение ячейки F2 больше либо равно 100
<= (меньше или равно)	=F2<=50	проверяет, действительно ли значение ячейки F2 меньше либо равно 50
<> (не равно)	=А5<>А6	проверяет неравенство значений ячеек А5 и А6
	=5<>7	проверяет неравенство чисел 5 и 7, результатом операции, выводимым в ячейке будет логическое значение ИСТИНА

Текстовый оператор объединяет числовые или текстовые значения в одну строку символов.

 

Обозначение оператора	Пример использования оператора	Результат вычисления
&	=А1&В1	создает текстовую строку, как результат присоединения содержимого ячейки В1 к концу содержимого ячейки А1
	="табличный"&"процессор"	табличный процессор
	=1234&5678	12345678

Адресные операторы или операторы связи предназначены для того, чтобы сделать ссылку на нужный диапазон ячеек. Выделяют операторы диапазона, объединения и пересечения.

 

Обозначение оператора	Пример использования оператора	Результат вычисления
: (диапазон)	=СУММ(А1:А5)	Суммирует значения ячеек А1 - А5
; (объединение)	=СУММ(В1;В2;С1;С2)	Суммирует значения ячеек В1, В2, С1, С2
	=СУММ(А1:А5;С1:С5)	Суммирует значения диапазонов ячеек А1:А5 и С1:С5
пробел (пересечение)	=СУММ(А1:В10 В1:С5)	Суммирует значения ячеек, общих для диапазонов А1:В10, В1:С5, т.е. В1:В5

 

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

- оператор диапазона;

- операторы объединения и пересечения;

- оператор процентов;

- оператор возведения в степень;

- операторы умножения и деления;

- операторы сложения и вычитания;

- текстовый оператор;

- операторы сравнения.

Функции в Excel

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

Всего в MS Excel используется более 200 функций различных категорий:

- математические,

- тригонометрические,

- инженерные,

- логические,

- финансовые,

- информационные,

- статистические,

- текстовые для обработки текстовых строк и значений,

- даты и времени для обработки числовых значений даты и времени,

- функции ссылок и массивов,

- функции для работы с базами данных,

- функции проверки свойств и значений ячеек рабочего листа.

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

Чтобы использовать функцию, ее нужно ввести как часть формулы в ячейку рабочего листа. Если нарушить правила синтаксиса, то MS Excel выдаст сообщение о том, что в формуле есть ошибка.

Итак, любая функция MS Excel всегда состоит из знака равенства, имени и списка аргументов.

Аргументами функций могут быть числовые и текстовые значения, логические значения, массивы, ссылки на ячейки, значения ошибок, или они могут вовсе отсутствовать для некоторых функций. Например, функция СУММ(А1:А10) суммирует значения диапазона ячеек А1:А10, а функция СЕГОДНЯ() вычисляет текущую дату. Аргументы могут быть как константами, так и формулами. В свою очередь эти формулы могут содержать другие функции. Функции, являющиеся аргументом другой функции, называются вложенными. В формулах MS Excel можно использовать до семи уровней вложенности функций.

Обязательным при вводе функции в ячейку рабочего листа является:

- предшествующий ей знак равенства  =;

- круглые скобки, в которые заключаются аргументы функции, причем скобки нужны даже в том случае, если для функции не требуется задавать список аргументов, как для  функций СЕГОДНЯ() и ТДАТА();

- отсутствие пробела между именем функции и списком аргументов;

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

Ввод функций можно осуществить несколькими способами:

- набрав с клавиатуры;

- нажатием кнопки  в строке формул;

- при помощи строки меню: Вставка / Функция, после чего на экране появляется диалоговое окно Мастер функций, в котором нужно выбрать категорию, имя функции и указать аргументы функции (рис. 10). Диалоговое окно Мастер функций облегчает ввод функций и контролирует правильность вводимых данных. При вводе функции диалоговое окно Мастер функций отображает имя функции, все ее аргументы, описание функции и каждого аргумента, текущий результат функции и всей формулы;

- при помощи инструмента Автосумма  на панели инструментов.

 

Рисунок 10. Диалоговое окно Мастер функций

Способы адресации и ссылки

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

1. Чаще всего в формулах используют относительные ссылки. Относительная ссылка на ячейку А1 выглядит так: А1. Относительные ссылки меняются при копировании формул. Например, если в ячейку A3 ввести формулу =А1+А2, то при копировании ее из ячейки A3 в ячейку ВЗ она преобразуется в формулу =В1+В2.

2. Абсолютные ссылки не меняются при копировании формул из одной ячейки в другую. Абсолютные ссылки на ячейки А1, D8, АА10 выглядят следующим образом: $А$1, $D$8, $АА$10. Например, если в ячейку A3 ввести формулу =А1+$А$2, то при копировании ее из ячейки A3 в ячейку ВЗ она преобразуется в формулу =В1+$А$2, т. е., делая абсолютную ссылку на ячейку, мы ее фиксируем.

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

4. Внутренняя ссылка на данные другого рабочего листа имеет вид ' Имя рабочего листа'!Имя ячейки, например, Лист1!А1 - ссылка на ячейку А1 листа1. Если рабочий лист переименован и его имя содержит пробелы, то в ссылках такое имя заключается в одинарные кавычки (апострофы), например, 'Отчет'!А1 - ссылка на ячейку А1 листа Отчет.

5. Внешняя ссылка на ячейку другой рабочей книги имеет вид '[Имя рабочей книги]Имя рабочего листа!'Абсолютная ссылка на ячейку, например, '[Отчеты.xls] Отчет_январь'!$А$1 - ссылка на ячейку А1 рабочего листа Отчет_январь рабочей книги Oтчеты.xls.

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

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

- #### – ширина ячейки не позволяет отобразить число в заданном формате;

- #ИМЯ? – Excel не смог распознать имя, использованное в формуле;

- #ДЕЛ/0! – в формуле делается попытка деления на нуль;

- #ЧИСЛО! – нарушены правила задания операторов, принятые в математике;

- #Н/Д – такое сообщение может появиться, если в качестве аргумента задана ссылка на пустую ячейку;

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

- #ССЫЛКА! – в формуле задана ссылка на несуществующую ячейку;

- #ЗНАЧ! – использован недопустимый тип аргумента.

 

Условное форматирование

Можно контролировать результат формулы или другие значения, применяя условные форматы. Например, текст в ячейке можно окрасить в зеленый цвет, если условие выполняется, и красным – если нет. В качестве условия можно использовать как константы, так и формулы.

Для добавления условного форматирования нужно выделить ячейку и в меню Формат выбрать команду Условное форматирование. Чтобы в качестве условия форматирования использовать значения ячеек, нужно выбрать параметр значение, выбрать операцию сравнения, а затем ввести заданное значение или формулу (рис. 11). Перед формулой нужно поставить знак равенства (=). Для использования формулы в качестве критерия форматирования, нужно выбрать параметр Формула, а затем ввести формулу (рис. 12).

 

Рисунок 11. Диалоговое окно Условное форматирование (одно условие)

 

Рисунок 12. Диалоговое окно Условное форматирование (два условия)

 

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

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

ЛАБОРАТОРНАЯ РАБОТА № 4

 

ЦЕЛЬ: научиться вводить и редактировать формулы, применять функции для анализа статистической информации, оперировать различными ссылками.

СОДЕРЖАНИЕ РАБОТЫ