Для вычислений в Microsoft Excel применяются формулы, которые в ряде случаев приходится создавать вручную. Формула – это математическое выражение, состоящее из чисел или ссылок на ячейки, в которых хранятся числа, (см. ниже), взаимосвязанных в определенной последовательности операторами. В Microsoft Excel каждая формула должна начинаться со знака равенства (=). Если знак равенства отсутствует, формула будет воспринята как простой текст. Отметим, что функция, вставляемая в ячейку автосуммой, также является формулой.
Операторы – это математические символы, указывающие операции над данными (числами, текстом, датами, логическими значениями). Все операторы можно отнести к одной из трех категорий: арифметические, текстовые и сравнения. Арифметические операторы выполняются над числами. Операторы сравнения используются для сравнения двух значений, результатом которого является логическое значение (может принимать два значения, ИСТИНА или ЛОЖЬ). Категория текстовых операторов содержит только один оператор, который используется для объединения нескольких текстовых строк в одну строку. Операторы Microsoft Excel приведены в табл. 11.1.
Сложные формулы могут включать в себя много различных операторов. В этом случае важен порядок выполнения операторов, который определяется приоритетом. Приоритеты операторов, т.е. последовательность, в которой Microsoft Excel выполняет операции, показаны в табл. 11.2. Более высокий приоритет обозначает, что оператор будет выполнен раньше.
Табл. 11.1
| Операторы | Название и назначение | Пример | Операторы | Название и назначение | Пример |
| Арифметические | Сравнения | ||||
| + | Сложение | =2+3 | = | Равно | =А1 = В1 |
| - | Вычитание | =5-1 | > | Больше чем | =А1 > В1 |
| - | Знак отрицательного числа | =-7 | < | Меньше чем | =А1 < В1 |
| * | Умножение | =7*3 | >= | Больше или равно | =А1>=В1 |
| / | Деление | =7/2 | <= | Меньше или равно | =А1<=В1 |
| % | Процент | =50+90% | <> | Не равно | =А1 <>B1 |
| ^ | Возведение в степень | = 7^2 |
| ||
| Текстовый | |||||
| & | Объединяет несколько последовательностей знаков в одну | ="Северный "&"ветер" (результат – "Северный ветер") | |||
Табл. 11.2
| Приоритет | Оператор | Описание |
| 1 | Двоеточие, пробел, запятая | Ссылки (см. ниже) |
| 2 | () | Математические скобки |
| 3 | - | Знак "минус" отрицательного числа |
| 4 | % | Процент |
| 5 | ^ | Возведение в степень |
| 6 | * и / | Умножение и деление |
| 7 | +, - и & | Сложение,вычитание и объединение двух текстовых строк в одну |
| 8 | =, <, >, <=, >=, <> | Сравнение |
Помимо операторов, в формулах также могут использоваться различные функции Microsoft Excel, которым будет посвящена следующая лабораторная работа.
Ссылки на ячейки и диапазоны ячеек Microsoft Excel
Ссылка указывает на ячейку или диапазон ячеек листа и позволяет взять содержимое ячейки для его отображения в других ячейках. Ссылки также используются в формулах, позволяя использовать данные из других ячеек. Также возможно использовать в нескольких формулах значение из одной ячейки. Кроме того, можно задавать ссылки на ячейки других листов этой же книги и даже на ячейки в других книгах (такие ссылки называются связями).
Как было сказано в Лабораторной работе №10, в Microsoft Excel использует один из двух стилей ссылок: стиль A1 или стиль R1C1 (название стиля представляет собой адрес самой первой ячейки листа). Мы всегда будем работать со стилем A1, определяющим столбцы буквами, а строки – числами. Для записи ссылки на ячейку следует вводить букву столбца (латинскими буквами!), а следом, без пробела, номер строки. Отметим, что ссылки должны либо входить в состав формулы, либо сами быть формулами. Например, ссылка =B2, записанная в некоторую ячейку, указывает, что в этой ячейке должно отобразиться содержимое ячейки на пересечении столбца B и строки 2. Запись «B2» без знака равно будет воспринята Microsoft Excel как простой текст.
Ссылки на диапазоны ячеек
Для указания диапазона из нескольких ячеек (используется в функциях, см. Лабораторную работу №13) ячейки начала и конца диапазона (по диагонали) разделяются знаком «:» (двоеточие). Если в одной ссылке необходимо объединить несколько различных диапазонов, либо добавить к диапазону отдельные ячейки, разделителем выступает символ «;» (точка с запятой).
Также имеется возможность разделить диапазоны ячеек пробелом, в этом случае будет использовано пересечение диапазонов, т.е. только те ячейки, которые войдут в оба диапазона.
Если в диапазон должны войти несколько строк целиком, их адрес записывается в виде чисел без букв, а если столбцов – в виде букв без чисел. Если диапазон составляет лишь одна строка или один столбец, имя этой строки или столбца необходимо записать два раза через двоеточие.
Примеры использования ссылок на диапазоны в стиле A1 приведены в табл. 11.3.
Табл. 11.3
| Ячейка или диапазон | Запись |
| Ячейка в столбце A и строке 10 | A10 |
| Диапазон ячеек: столбец А, строки 10-20 | A10:A20 |
| Диапазон ячеек: строка 15, столбцы B-E. | B15:E15 |
| Диапазон ячеек: столбцы А-E, строки 10-20 | A10:E20 |
| Все ячейки в строке 5 | 5:5 |
| Все ячейки в строках с 5 по 10 | 5:10 |
| Все ячейки в столбце H | H:H |
| Все ячейки в столбцах с H по J | H:J |
| Два диапазона ячеек одновременно: и B5:C15, и F5:G15 | B5:C15;F5:G15 |
| Ячейки, лежащие на пересечении двух диапазонов, B3:F7 и A6:G8 (полученный диапазон можно записать как B6:F7): | B3:F7 A6:G8 |
При необходимости сослаться на другой лист в той же книге имя листа (или диапазон из имен листов – также записывается через двоеточие) предшествует ссылке на диапазон ячеек. При этом имя листа отделяется от имени ячеек символом «!» (восклицательный знак). Пример ссылки в составе функции суммы (функция просуммирует содержимое, хранящееся в ячейках диапазонов A5:B8 на каждом из листов книги с первого по третий):
=СУММ(Лист1:Лист3!A5:B8)
В ссылке на другую книгу имя книги заключается в прямоугольные скобки, затем указывается имя листа и, после восклицательного знака, диапазон ячеек с данными. Пример ссылки:
=[Книга2.xls]Лист1!A8
Следует быть осторожными со ссылками такого типа: при перемещении файла «Книга2.xls» (в примере) в другую папку ссылка перестанет работать, и на ее месте отобразится ошибка.
