Применение операторов в формулах и использование мастера функций

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

+ сложение;

– вычитание или унарный минус;

* умножение;

/ деление;

% процент;

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

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

= равно;

> больше;

< меньше;

>= больше или равно;

<= меньше или равно;

<> не равно.

Текстовый оператор & (а мперсанд) используется для обозначения операции объединения символов в единую последовательность. Например, результатом выполнения выражения «ГИПОТЕЗА» & «ДИСПОЗИЦИЯ» & «САНКЦИЯ» будет:

«ГИПОТЕЗА ДИСПОЗИЦИЯ САНКЦИЯ».

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

(двоеточие), (запятая), (пробел) операторы ссылок;

унарный минус (например, – 1);

1. % процент;

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

3. * и / умножение и деление;

4. + и – сложение и вычитание;

5. & текстовый оператор;

6. = < > <= >= <> операторы сравнения.

Чтобы изменить порядок обработки операторов, применяют круглые скобки.

Тип данных, размещаемых в ячейке, определяется автоматически при вводе. Если данные можно интерпретировать как число, MS Excel задает числовой тип, в противном случае – текстовый.

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

Формат записи функции: «имя функции» («аргумент»).

Например: СУММ(А1:А10), где:

СУММ – имя функции;

(А1:А10) – аргумент, записанный в виде диапазона ячеек.

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

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

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

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

Функции могут быть:

— без аргументов:

Пример: =СЛЧИС() – функция вычисления случайного числа от 0 до 1;

— с одним аргументом:

Пример: =КОРЕНЬ(А1^2+A2^2) – функция извлечения корня из числа;

— с фиксируемым количеством аргументов:

Пример: =ЕСЛИ(условие;истина;ложь) – функция вычисления по условию;

— с неопределённым количеством аргументов:

Пример: =СУММ(А1:A10;C1:C10;G11;100) – суммирование ячеек, диапазонов и чисел;

— с необязательными аргументами, которые могут отсутствовать при вычислении значения функции.

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

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

В таких случаях MS Excel ссылается на диапазоны ячеек с помощью трехмерных ссылок. Трехмерная ссылка устанавливается путем включения диапазона листов (с указанием начального и конечного листа) и соответствующего диапазона ячеек. Например, формула, использующая трехмерную ссылку, которая включает листы от Лист1 до Лист5 и ячейки А4:А8, может иметь следующий вид: =SUM(ЛИСТ1:ЛИСТ5!А4:А8).

Трехмерные ссылки можно включить в формулу и другим способом. Для этого достаточно щелкнуть на рабочем листе, который нужно включить в формулу, правой кнопкой мышки. Сначала начинают запись формулы в результирующую ячейку. Когда дойдет очередь до использования трехмерной ссылки, щелкают на ярлычке первого листа, подлежащего включению в ссылку. Затем нажав и удерживая клавишу SHIFT, щелкают на ярлычке последнего листа, подлежащего включению в ссылку. После этого выделяют нужные ячейки. Завершается построение формулы нажатием клавиши ENTER.

Связывание рабочих книг. При связывании книга, содержащая формулу связывания, называется зависимой рабочей книгой. Рабочая книга, содержащая связываемые данные – исходной рабочей книгой.

Связь между двумя файлами (книгами) достигается за счет введения в один файл формулы связи со ссылкой на ячейку в другом файле. Файл, который получает данные из другого, называется файлом назначения, а файл, который предоставляет данные, – файлом – источником.

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

При ссылке на ячейку, содержащуюся в другой рабочей книге, используется следующий синтаксис: [Книга]Лист!Ячейка. Вводя формулу связывания для ссылки из другой рабочей книги, используется имя этой книги, заключенное в квадратные скобки, за которыми должно следовать имя рабочего листа, затем восклицательный знак (!), а после него – адрес ячейки.

Например: 'C:\Petrov\[Журнал1.хls]Литература'!L3.

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

Использование мастера функций. При выборе пункта Другие функции (рис. 5.13) запускается Мастер функций, облегчающий выбор нужной функции. В списке Категория выбирается категория, к которой относится функция. При затруднении в выборе используют пункт Полный алфавитный перечень, а в списке Функция – конкретную функцию данной категории (рис. 5.14). После щелчка на кнопке ОК имя функции заносится в строку формул вместе со скобками, ограничивающими список параметров. Текстовый курсор устанавливается между этими скобками. Мастер функций может вызываться командой Вставка – Функции.

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

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

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

— функции работы с БД;

— функции даты и времени;

— финансовые функции;

— информационные функции;

— логические функции;

— математические функции;

— статистические функции;

— текстовые функции;

— функции работы со ссылками и массивами.

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

Если требуется отобразить итоговое значение для диапазона ячеек, используют Автовычисление. При выделении ячеек MS Excel отображает сумму для диапазона в строке состояния, которая представляет собой горизонтальную область под окном листа (рис. 5.16). Если строка состояния не отображается, необходимо выбрать команду Строка состояния в меню Вид.

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

Автосумма. Для итоговых вычислений применяют ограниченный набор функций, наиболее типичной из которых является функция суммирования (СУММ). Это единственная функция, для применения которой есть отдельная кнопка на стандартной панели инструментов (кнопка Автосумма).

Диапазон суммирования, выбираемый автоматически, включает в себя ячейки с данными, расположенные над текущей ячейкой (предпочтительнее) или слева от нее и образующие непрерывный блок (рис. 5.17). При неоднозначности выбора используется диапазон, непосредственно примыкающий к ячейке. Автоматический подбор диапазона не исключает возможности редактирования формулы. Можно переопределить диапазон, который был выбран автоматически, а также задать дополнительные параметры функции.

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

Дата добавления: 2014-12-27