Вопрос 1: Понятие о формуле и функции в табличном процессоре Microsoft Excel

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

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

Формула – это результативная комбинация операторов, идентификаторов и значений.

Порядок элементов формулы называется синтаксисом формулы. Формула должна начинаться со знака равенства, за которым следуют операторы и вычисляемые элементы (операнды).

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

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

В формулах могут использоваться 4 вида операторов:

- арифметические операторы +, -, *, /, %, ^ применяются для выполнения математических вычислений, результатами которых являются числа;

Таблица 2: Арифметические операторы

- операторы =, >, <, >=, <=, <> используются для сравнения значений;

Таблица 3: Операторы сравнения

- текстовый оператор «&» используется для объединения последовательно­стей символов;

Таблица 4: Текстовые операторы

- адресные операторы объединяют диапазоны ячеек для осуществления вычислений:

а) двоеточие – оператор диапазона; задает ссылку на все ячейки диапазона;

б) точка с запятой – оператор объединения; задает ссылку на перечис­ленные ячейки или диапазоны, например, МИН(А1:А10;С15:С20);

в) пробел – оператор пересечения, который ссылается на общие ячейки диапазонов. Например, МАКС(С1:Е10 А5:Н8) позволяет определить максимальное из чисел, расположенных в ячейках С5:Е8.

Таблица 5: Операторы ссылки

Идентификатор – это элемент выражения, представляющий собой ссылку на значение ячейки или диапазона ячеек, адрес или имя ячейки или диапазона ячеек.

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

Формулы могут ссылаться:

- на ячейки или диапазоны ячеек,

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

Буквы, расположенные над столбцами рабочего листа называют заголовка­ми столбцов. Цифры, распложенные слева от строк, называют заголовками строк. Местоположение ячейки или диапазона ячеек на рабочем листе называ­ется адресом. Адрес ячейки включает заголовок строки и заголовок столбца, на пересечении которых находится ячейка, например А10, B7.

Адрес диапазона состоит из адресов верхней левой и нижней правой ячеек, разделенных двоеточием, например А1:С7. Адрес диапазона, включающего все ячейки одного столбца, например В, записывается В:В. Аналогично запи­сывается адрес диапазона, представленного всеми ячейками одной строки.

Ячейка, содержащая формулу, называется зависимой ячейкой, так как ее значение зависит от значения ячейки, на которую задана ссылка. По умолчанию в Microsoft Excel используются ссылки стиля А1, то есть ссылки на адре­са ячеек. Примерами этого вида могут быть ссылки:

- С56 – на ячейку С56,

- А12:В25 – на диапазон А12:В25,

- 5:5 – на все ячейки 5-й строки,

- 5:10 – на все ячейки с 5-й по 10-ю строку включительно,

- Н:Н – на все ячейки столбца Н.

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

Абсолютная ссылка жестко привязывает формулу к конкретной ячейке и не изменяется при копировании. Чтобы сделать ссылку абсолютной, нужно добавить символ доллара перед буквой и числом, которые составляют адрес ячейки. Это можно сделать с помощью клавиатуры или нажав клавишу F4 по­сле ввода адреса ячейки.

Смешанная ссылка – это ссылка, в которой часть ссылки является относительной, а другая часть – абсолютной, например $А1 или А$1. При копировании в другую ячейку изменяется только та часть адреса, которая не содержит сим­вол доллара.

Кроме ссылок стиля А1 в Excel могут использоваться ссылки стиля R1C1. В стиле ссылок R1C1 указывается положение ячейки буквой «R», за которой идет номер строки, и буквой «C», за которой идет номер столбца. Ссылки стиля R1C1 также могут быть относительными, абсолютными и смешанными.

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

- указать ячейку, в которую следует ввести функцию,

- ввести знак равенства, имя функции и открывающую круглую скобку, а щелкнуть ярлык первого листа,

- удерживая нажатой клавишу Shift, щелкнуть ярлык последнего листа,

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

- ввести закрывающую круглую скобку и нажать клавишу Enter.

Трехмерные ссылки нельзя использовать:

- в формулах, использующих неявное пересечение,

- в формулах массива,

- вместе с оператором пересечения (пробел).

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

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

Имя – это универсальное средство Microsoft Excel, используемое для обозначения ссылки на значение ячейки или диапазона ячеек.

Присвоить имя можно только активному элементу листа. Чтобы активизировать ячейку нужно щелкнуть ее или перейти в нее с помощью клавиш управления курсором. Для активизации строки или столбца необходимо щелкнуть их заголовки. Для активизации диапазона можно использовать выделение путем протягивания мышью или щелчок в левом верхнем углу диапазона, а затем при нажатой клавише Shift – в правом нижнем. Несмежные диапазоны выделя­ются мышью при нажатой клавише Ctrl.

Рисунок 81: Диалоговое окно Создать имена.

Активизировав объект необходимо щелкнуть в поле имен, ввести имя и нажать кла­вишу Enter. Имя может содержать как буквы, так и цифры.

Excel накладывает ряд ограничений на присваиваемые имена:

- имя не может состоять из заголовков строк и столбцов;

- имя не может содержать пробелы

- минимальная длина имени – 1 символ;

- чем короче имя, тем проще использовать его в формулах.

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

- выделить диапазон;

- дать команду Вставка4Имя4Создать в диалоговом окне Создать имена указать какая строка или какой столбец содержат названия.

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

Литералом называют значение в явном представлении, например, текст, дата, время.

Константа – числовое или строковое значение, которое не изменяется при выполнении программы.

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

При использовании в качестве аргумента формулы массива констант необходимо:

- заключить массив в фигурные скобки;

- после каждого значения столбца вставить точку с запятой;

- после значений каждой строки вставить двоеточие.

Массив констант может включать в любых сочетаниях:

- числа,

- текст, заключенный в двойные кавычки,

- логические значения,

- значения ошибок, например #Н/Д.

Массив констант не может:

- включать формулы,

- содержать ссылок,

- иметь столбцы или строки разного размера,

- содержать знаки доллара, процента, круглые скобки.

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

Функции используются для упрощения сложных и длительных вычислений. Функция позволяет получить (или, как говорят специалисты, возвраща­ет) результат расчета, стандартное или системное значение. Например, функ­ция ПИ() выводит стандартное значение числа пи, функция СЕГОДНЯ() – сис­темную дату.

Excel предоставляет в ваше распоряжение функции следующих категорий:

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

- дата и время,

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

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

- ссылки и массивы,

- работа с базой данных,

- текстовые,

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

- проверка свойств и значений.

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

Для применения функции ввести ее название и задать аргументы с помощью клавиатуры и мыши. Основное условие самостоятельного написания функции – строгое соблюдение синтаксиса. Синтаксис функции определяет ее структуру и порядок элементов. Он включает следующие элементы:

- знак равенства;

- имя функции;

- открывающую скобку;

- аргументы, отделяемые друг от друга точками с запятыми;

- закрывающую скобку.

Аргументы – это значения, служащие источниками данных для расчета значения функции. Аргументами функции могут быть:

- числовые, текстовые или логические значения,

- ссылки на ячейки и диапазоны,

- имена ячеек и диапазонов,

- значения ошибок,

- массивы констант,

- вложенные функции.

Многие функции требуют ввода логического значения ИСТИНА или ЛОЖЬ.

Другой способ, не требующий запоминания (но требующий понимания) синтаксиса функции – это использование Мастера функций. Для вызова Мастера функций необходимо выполнить следующие действия:

- активизировать ячейку;

- дать команду Вставка4Функция или воспользоваться кнопкой в строке формул.

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

Рисунок 82: Диалоговое окно Мастер функций - шаг 1 из 2

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

Рисунок 83: Диалоговое окно Аргументы функции для функции ЕСЛИ

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

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

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

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

Microsoft Excel выводит следующие сообщения об ошибках:

- #ДЕЛ/О – деление на нуль;

- #Н/Д – неопределенные данные;

- #ИМЯ? – не распознается имя;

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

- #ЧИСЛО! – проблема с числовым значением;

- #ССЫЛКА! – неверная ссылка;

- #ЗНАЧЕНИЕ! – неверный тип аргумента или операнда;

- ####### – переполнение ячейки.

Ошибка #ДЕЛ/О! чаще всего появляется, когда в формуле:

- содержится явное деление на ноль,

- в качестве делителя используется ссылка на ячейку, содержащую нуле­вое или пустое значение.

Ошибка #Н/Д может появляться когда:

- заданы не все аргументы функции,

- в формуле массива используется аргумент не соответствующий диапазону.

Ошибка #ИМЯ? появляется, когда программа:

- не может распознать используемое в формуле имя, а имя удалено или не было определено,

- при написании имени или названия функции допущена ошибка,

- в формулу введен текст, не заключенный в двойные кавычки, а в ссылке на диапазон ячеек пропущен знак двоеточия.

Ошибка #ПУСТО! выводится:

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

- использован ошибочный оператор пересечения диапазонов или ошибоч­ная ссылка на ячейку.

Ошибка # ЧИСЛО! появляется, если:

- в функции используется неприемлемый аргумент,

- не сходятся итерации, и не может быть получен результат,

- формула возвращает значение, которое слишком велико или слишком мало, чтобы его можно было представить в Excel.

Ошибка #ССЫЛКА! может быть выведена при:

- ссылке на удаленную ячейку,

- ссылке: на ячейку, в которую помещено содержимое других скопированных ячеек,

- удаленной ссылке на неактивное приложение

Ошибка ##### появляется:

- когда вводимое числовое значение или результат вычислений не умещается в ячейке,

- если при вычислении даты получено отрицательное значение.

Не нашли, что искали? Воспользуйтесь поиском: