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

 

Операторы	Значение
Арифметические
+ - / *	Сложение, вычитание, деление, умножение
%	Процент (например, 5% в формулах рассматривается как 0.05)
^	Возведение в степень (например, 2.3 ^ 3 = 12.167)
Операторы сравнения
= > <	Равно, больше, меньше
>= <= <>	Больше или равно, меньше или равно, не равно
Текстовый оператор
&	Объединение последовательностей символов в одну последовательность (конкатанация)
Адресные операторы
Диапазон (двоеточие)	Ссылка на все ячейки в границах диапазона включительно
Объединение (запятая)	Ссылка на объединение ячеек диапазонов
Пересечение (пробел)	Ссылка на общие ячейки диапазонов

 

 

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

1) Выделить нужную ячейку.

2) Указать в качестве первого символа знак равенства; для этого ввести знак = или щелкнуть мышью по знаку = в строке формул.

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

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

5) Ввести оставшуюся часть формулы. Завершить ввод нажатием клавиши <Enter>. В ячейке при правильном вводе формулы появляется результат вычисления. Саму формулу можно увидеть теперь в строке формул.

 

Если формула введена с ошибками или вычисление результата по формуле невозможно, то в ячейке появляется сообщение об ошибке (Таблица 8.2).

 

Таблица 8.2

Сообщения об ошибках в формуле

Текст сообщения	Возможная причина	Способ устранения
# ДЕЛ/0!	Деление на ноль	Проверить и изменить содержимое влияющей ячейки
#ССЫЛКА!	Указана некорректная ссылка	Проверить и исправить ссылки, особенно внешние: правильно ли указан путь, не был ли переименован файл, лист и т.д.
#ЧИСЛО!	Невозможность вычисления значений (например, извлечение квадратного корня из отрицательного числа)	Проверить правильность задания аргументов функции
#ПУСТО!	Неверно указано пересечение диапазонов ячеек (нет общих ячеек)	Задать правильно диапазон ячеек
#ЗНАЧ!	Задан аргумент недопустимого типа	Уточнить типы аргументов для применяемой функции
#ИМЯ!	Указано недопустимое имя операнда (например, функции или диапазона)	Проверить правильность написания имен
#Н/Д!	Нет данных в ячейке, на которую сделана ссылка	Проверить содержимое влияющей ячейки

 

Для поиска ошибок в формулах Excel предоставляет специальный режим, в котором на экране графически отображаются связи между влияющими и зависимыми ячейками. Для включения этого режима надо воспользоваться пунктом меню Вид/Панели инструментов/Настройка/Зависимости, и тогда можно визуально наблюдать, какие ячейки влияют на содержимое той или иной ячейки (выявить влияющие ячейки) и на содержимое каких ячеек она сама влияет (выявить зависимые ячейки).

 

	A	B	C	D
				=A1+B1+C1
	=A1+A2

 

Рис. 8.4. Пример использования ссылок

 

В примере на рис. 8.4 в ячейки A1:C2 введены числа, а в ячейке A3 записана формула =A1+A2, т.е. сумма элементов первого столбца, и в ячейке D1 – формула =A1+B1+C1, т.е. сумма элементов первой строки. Обе формулы имеют относительные ссылки. При вычислении по этим формулам в ячейках A3 и D1 будут отображены значения 79 и 56, соответственно. Если использовать режим заполнения, указав ячейку A3 и заполнив ячейки B3 и C3, то в них появятся формулы =B1+B2 и =C1+C2, т.е. суммы второго и третьего столбца соответственно. При копировании ячейки D1 в ячейку D2 в последней будет записана формула =A2+B2+C2, т.е. сумма второй строки, равная 75.

Изменим ссылки в обеих исходных формулах. В ячейку A3 запишем формулу =$A$1+A2, а в ячейку D1 – формулу = A1+$B$1+C$1. При копировании первой формулы в ячейки B3 и C3 в них появятся записи =$A$1+B2 и =$A$1+C2, соответственно. При копировании второй формулы в ячейку D2 в последней появится запись = A2+$B$1+C$1. Так как в формулах указаны абсолютные ссылки (перед номером строки или именем столбца стоит знак $), то при копировании формулы из одной ячейки в другую такая ссылка в формуле не изменяется. Если надо найти сумму соседних ячеек, то можно воспользоваться кнопкой ∑ (автосуммирование) панели инструментов Стандартная. Для этого требуется выделить ячейки, которые надо сложить, вместе с ячейкой, в которой будет находиться результат суммирования, и нажать кнопку ∑.

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

1) Выбрать меню Вставка/Функция или нажать кнопку f_x панели инструментов Стандартная.

2) Выделить в левом окне нужную категорию, а в правом требуемую функцию и щелкнуть дважды по имени функции или нажать кнопку ОК (Рис. 8.5).

3) Указать аргументы.

 

Рис. 8.5. Окно, содержащее список функций

 

 

На первом шаге выбрана математическая функция SIN(), на втором шаге указывается аргумент функции.

Данные, содержащиеся в таблице, можно отобразить графически в виде диаграммы. Построение диаграмм выполняется с помощью Мастера диаграмм, который можно вызвать кнопкой панели инструментов Стандартная или с помощью меню Вставка/Диаграмма. Диаграмма отображает зависимость значений, образующих ряды данных, от признаков, называемых категориями. Например, на любой диаграмме, которая показывает изменение величин во времени, категорией будет время. Ряды данных могут быть в строках или столбцах таблицы. Категории часто размещаются в первом столбце или в первой строке. Для примера приведена таблица 8.3. В ней ряды данных – Продажи, Расходы, категории данных – Годы. На рис. 8.6 показаны соответствующие диаграммы.

Таблица 8.3

Ряды и категории данных

	Год 1	Год 2	Год 3	Год 4	Год 5
Продажи
Расходы

Рис. 8.6. Диаграммы в виде графиков и гистограмм

 

Рис. 8.7. Окно выбора типа и вида диаграммы

 

Построение диаграмм состоит из следующих шагов:

1) выделить диапазон, содержащий ряды данных с подписями – с именами строк или столбцов,

2) вызвать мастер диаграмм,

3) выбрать тип и вид диаграммы (Рис. 8.7), нажать кнопку Далее,

4) во вкладке Диапазон данных (шаг 2 мастера диаграмм) уточнить (при необходимости) положение рядов данных, а во вкладке Ряд указать местоположение подписей оси Х – категорий,

5) ввести необходимые заголовки (название диаграммы, названия осей) и условные обозначения, если они необходимы,

6) разместить диаграмму на том же листе, где находится таблица или на отдельном листе.

Форматирование элементов диаграммы выполняется следующим образом. Щелкнув правой кнопкой мыши на элементе диаграммы, надо выбрать команду Формат, а затем требуемую вкладку для форматирования диаграммы.

8.5. Сортировка, консолидация данных, сводные таблицы

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

Консолидация данных позволяет создать таблицу-сводку по одной или нескольким категориям данных, используя один или несколько блоков данных. При выполнении консолидации надо задать тип функции (например, СУММ или СРЗНАЧ), по которой будут вычисляться итоговые значения. Рассмотрим смысл консолидации на примере компьютерной фирмы, имеющей три филиала (Таблица 8.4). Надо построить таблицу, содержащую консолидированные данные по всем филиалам, т.е. суммарный объем продаж компьютеров, мониторов, процессоров и материнских плат. Для получения этой таблицы надо выполнить следующие действия:

1) Выделить ячейку, которая будет соответствовать левому верхнему углу таблицы консолидированных данных. В нашем примере пусть это будет ячейка A22.

2) Выбрать пункт меню Данные/Консолидация (Рис. 8.8).

3) В окне Консолидация указать функцию.

4) В строке Ссылка щелкнуть мышью по стрелке в правом углу и выделить первую исходную область, в примере – A6:E9 – она оконтурится мерцающим пунктиром.

5) Щелкнуть кнопку Добавить.

6) Повторить действия 4 – 5 для следующих диапазонов (A11:E14 и A16:E19).

7) Установить флажок Значения левого столбца и нажать кнопку OK (Рис. 8.8).

Таблица 8.4