Общие понятия о формулах

 

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

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

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

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

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

+ сложение,

- вычитание,

* умножение,

/ деление,

^Ù возведение в степень.

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

Арифметические формулы могут также содержать операторы сравнения: равно (=), не равно (< >), больше (>), меньше (<), не более (<=), не менее (>=). Результатом вычисления арифметической формулы является число.

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

СУММ(Список) – функция для определения суммы всех числовых значений в Списке. Список может состоять из адресов ячеек и диапазонов, а также числовых значений. Например, СУММ(D5:F8) вычисляет сумму чисел в диапазоне D5:F8, а СУММ(A1;B3:D5;33) – к сумме чисел в диапазоне B3:D5 прибавляет содержимое ячейки A1 и число 33.

СРЗНАЧ(Список) –функция вычисления среднего арифметического значения всех перечисленных в Списке величин.

ЕСЛИ(Условие;Х;Y) – результат вычисления этой функции естьодно из двух возможных значений (X или Y) в зависимости от заданного условия. Если условие истинно, то результат есть Х; если условие ложно, то результат Y.

В качестве условия чаще всего используется логическое выражение, X и Y – числовые выражения либо строка текста в двойных кавычках. Например, результат функции ЕСЛИ(А5>10;0;77) есть 0, если содержимое ячейки А5 больше 10 и 77 в противном случае.

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

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

	А	В	С

 

Рис 5.1 - Фрагмент электронной таблицы.

 

Таблица 5.1 - Примеры использования формул.

Формула	Результат	Пояснение
=А1+В1*3		Содержимое ячейки В1 умножается на 3, и резуль­тат складывается с содержимым ячейки А1. (Умно­жение выполняется первым).
=А2-ВЗ+С2	-3	Содержимое ячейки ВЗ вычитается из содержимого ячейки А2, а затем к результату добавляется со­держимое ячейки С2. (Сложение и вычитание как действия одного уровня выполняются слева на­право).
=B2/(C1*A2)		Содержимое ячейки С1 умножается на содержимое А2, и затем содержимое ячейки В2 делится на полученный результат. (Любые действия в скобках выполняются первыми).
=B1^C1-B2/AЗ		Содержимое ячейки В1 возводится в степень, определяемую содержимым ячейки С1, затем определяется частное от деления содержимого ячейки В2 на содержимое ячейки A3. Полученное частное вычитается из первого результата. (Воз­ведение в степень выполняется первым, затем выполняется деление и только потом — вычитание).
=СУММ(A1:C1)		Сумма чисел в первой строке
=СРЗНАЧ(C1:C3)		Вычисление среднего значения чисел, находящихся в колонке С
=ЕСЛИ(С1=2,10,20)		Ячейка С1 содержит значение 2, условие выполняется, поэтому результат равен 10.
=ЕСЛИ(B2>20, 0,C1+7)		Так как условие B2>20 не выполняется, то результат есть содержимое ячейки С1 плюс число 7
=ЕСЛИ(А1>0, В2/3,В2/4)		Условие А1>0 выполняется, поэтому результат определяется выражением В2/3.

Ввод формул

 

Чтобы ввести в ячейку формулу нужно:

· выделить нужную ячейку;

· начать ввод формулы со знака =;

· набрать текст формулы;

· завершив набор, щелкнуть мышкой на кнопке в строке формул или нажать клавишу <Enter>.

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

Мышь и клавиши управления курсором при наборе формул часто используются для выделения диапазонов. По этой причине, попытка завершить ввод иным способом может привести к не совсем адекватным результатам.

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

К наиболее «популярным» ошибкам можно отнести использование в адресах ячеек русских букв (сообщение #ИМЯ?), наличие в тексте формулы пробелов, а также пропуск оператора или скобок.

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

· наберите текст формулы до того места, где следует ввести ссылку на ячейку или диапазон;

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

· при необходимости завершите набор формулы;

· выполните двойной щелчок мышью на кнопке в строке формул.

 

 

Ввод данных и формул

Рассмотрим решение в EXCEL следующей задачи начинающего бухгалтера – «Разработка ведомости начисления зарплаты».

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

Отведем для каждого работника одну строку таблицы. В первом столбце будем вводить фамилии работников, во втором столбце начисленную заработную плату, в третьем столбце рассчитаем размер подоходного налога (13%) от второго столбца, в последнем столбце рассчитаем сумму к получению (разность между данными во втором столбце и в третьем столбце).

Запускаем EXCEL и начинаем заполнять таблицу. Указатель ячейки с самого начала установлен в ячейке А1. Набираем на клавиатуре название колонки (например, Фамилия). Каждый символ, введенный на клавиатуре, одновременно отражается в самой ячейке и в области формул. Ввод и редактирование (с помощью клавиши <BackSpase>) можно продолжать до тех пор, пока не нажата клавиша <Enter> на клавиатуре. После этого процесс ввода данных в ячейку заканчивается, а активной становится ячейка, расположенная на следующей строке (А2). Пользователь, конечно, имеет возможность вернуться и изменить содержимое первой ячейки.

В ячейке А2 вводим таким же образом фамилию Иванов, в ячейке А3 – Петров, в ячейке А4 – Сидоров. На этом формирование данных в колонке А можно считать законченным.

Переходим к колонке В, для чего делаем активной ячейку В1 и вводим туда заголовок столбца (например, Начислено). В ячейку В2 вводим сумму начисленной заработной платы для Иванова, например, 2000, в ячейку В3 – для Петрова (3000), и ячейку В4 – для Сидорова (5000).

Затем в ячейку С1 вводим заголовок колонки С (Налог) и переходим к ее заполнению. В ячейку С2 нужно ввести формулу для расчета подоходного налога. Вводим формулу =0.13*В2, что означает: в этой ячейке должно быть записано число, равное результату умножения 0.13 на число из ячейки В2. После полного ввода формулы нажмем клавишу <Enter>, в ячейке С2 появится результат вычислений, то есть 260, а в области формул останется текст введенной формулы. Аналогично заполняем ячейки С3, где должна быть формула =0.13*В3, и С4 (=0.13*В4).

Колонка D должна содержать разность начисленной заработной платы и величины подоходного налога. В ячейку D1 вводим заголовок колонки (например, Выдать), а в ячейки D2, D3, D4 – соответствующие формулы. По аналогии с вышеизложенным, они должны иметь вид =В2-С2, =B3-С3, =В4-С4, соответственно.

Если все операции выполнены правильно, то на экране появляется двухмерная таблица, показанная на рисунке 5.2.

 

Рис 5.2 - Двумерная таблица.

 

Для сохранения созданной таблицы выполняем команду

Ø Файл Ø Сохранить как…,

при этом появляется стандартное окно сохранения файла, рассмотренное в предыдущих лабораторных работах.

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

Пользователю, начинающему работать в EXCEL, нужно научиться:

· открывать и закрывать файлы;

· вводить и редактировать данные;

· выполнять форматирование таблиц EXCEL;

· выполнять математические расчеты с помощью формул.

 

 

Копирование формул

 

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

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

ОТНОСИТЕЛЬНЫЕ ССЫЛКИ

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

Пусть, например, в ячейке В2 имеется ссылка на ячейку А3. В относительном представлении можно сказать, что ссылка указывает на ячейку, которая располагается на один столбец левее и на одну строку ниже данной. Если формула будет скопирована в другую ячейку, то такое относительное указание ссылки сохранится. Например, при копировании формулы в ячейку D4, ссылка будет продолжать указывать на ячейку, находящуюся левее и ниже, в данном случае на ячейку С5.

Поскольку применяемые в EXCEL ссылки в основном являются относительными, то этот тип ссылок специально не обозначается.

АБСОЛЮТНЫЕ ССЫЛКИ

Абсолютные ссылки – это ссылки, которые остаются постоянными при копировании и перемещении формул.

Для их обозначения используется специальный знак доллара ( $ ). Этот знак должен ставиться перед каждой составляющей адреса: перед именем столбца и перед номером строки, например, $D$5.

Например, пусть в ячейке В2 помещена формула =$A$1+$B$1. При копировании этой формулы в ячейку С2 эта формула сохранит свой прежний вид =$A$1+$B$1.

СМЕШАННЫЕ ССЫЛКИ

Смешанные ссылки – это ссылки, в которых одна часть рассматривается как абсолютная, а другая – как относительная: ссылка D$5 является абсолютной по отношению к номеру строки, а по отношению к столбцу является относительной.

На рис. 5.3 показан пример копирования формул, содержащих относительные, абсолютные и смешанные ссылки. Стрелками показано направление копирования.

 

 

=A1+B1	=B1+C1	=$A$1+$B$1	=$A$1+$B$1	=$A1+B$1	=$A1+C$1
=A2+B2	=B2+C2	=$A$1+$B$1	=$A$1+$B$1	=$A2+B$1	=$A2+C$1

 

Рис. 5.3 – Копирование формул с относительными, абсолютными

и смешанными ссылками.