Формулы и функции

Технология работы с табличным процессором

Цель выполнения части 1 лабораторной работы

MS Excel

И диаграмм в среде табличного процессора

Финансово-экономические расчеты,

Часть

создание таблиц листа, сводных таблиц

Целью выполнения части 1 лабораторной работы является приоб-

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

возможностей MS Excel 2010 для решения финансово-экономи-

ческих задач.

Каждая ячейка книги MS Excel может содержать не только текст

или число, но и формулу.

Формула — это комбинация постоянных значений (констант), адресов,

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

в ячейку и определяет правила вычисления значения в ней. Формула

должна начинаться со знака =.

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

С помощью формул можно осуществлять самые разнообразные

вычисления.

Элементы, следующие за знаком равенства, являются операнда­

ми, разделяемыми операторами вычислений. Формула, вводимая

в ячейку таблицы, может состоять из таких элементов, как:

ƒ операторы;

ƒ ссылки на ячейки и диапазоны ячеек;

ƒ числовые или символьные константы;

ƒ функции.

В MS Excel включено четыре типа операторов: арифметические,

текстовые, операторы сравнения и операторы ссылок.

Операторы — это математические символы, указывающие операции,

выполняемые над элементами формулы.

Во многих формулах используются ссылки на одну или несколько

ячеек. В ссылке указывается адрес ячейки или диапазона ячеек.

Существует четыре типа ссылок. Различить их помогает знак $.

Относительные ссылки — при копировании формулы ссылка

на ячейку обновляется. Например, в формуле есть ссылка на ячейку

С21. При копировании формулы по столбцам будет изменяться

имя столбца С, а при копировании по строкам — номер строки 21.

Абсолютные ссылки — при копировании формулы ссылка на ячейку

не обновляется (например, $C$21).

Абсолютная строка — при копировании формулы ссылка на ячейку

частично обновляется. Например, в формуле есть ссылка на ячейку

С$21. При копировании формулы по столбцам будет изменяться

имя столбца С, а при копировании по строкам номер строки 21

изменяться не будет.

Абсолютный столбец — при копировании формулы ссылка на ячей-

ку частично обновляется. Например, в формуле есть ссылка на ячейку

$С21. При копировании формулы по столбцам имя столбца С из-

меняться не будет, а при копировании по строкам номер строки 21

будет изменяться.

Константы — это значения, которые в процессе вычисления формулы

не меняются.

Символьные константы при вводе в формулу должны заключаться

в кавычки. Например, =«Российская»&«Федерация».

Функция — это заранее созданная формула, которая выполняет операции

над заданными значениями (аргументами функции).

На вкладке Формулы в группе Библиотека функций собраны

все функции, имеющиеся в MS Excel 2010 (рис. 1.1, 1.2).

Рис. 1.1. Библиотека функций

Рис. 1.2. Другие функции

Структура функции начинается с указания имени функции, затем

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

ющиеся точкой с запятой, а затем — закрывающая скобка. Список

аргументов может состоять из чисел, текста, логических величин

(ИСТИНА или ЛОЖЬ), массивов, значений ошибок (например,

#Н/Д), ссылок и формул. Эти формулы, в свою очередь, могут со-

держать другие функции.

При задании аргументов нужно помнить следующие правила:

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

ƒ аргументы отделяются друг от друга точкой с запятой;

ƒ число аргументов должно быть не более 255;

 

 

ƒ в качестве аргументов могут быть использованы ссылки, числа,

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

нов ячеек, функции.

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

ция называется вложенной. В этом случае сначала выполняются

функции «внутри» конструкции, а затем внешние функции.

Формула, приведенная на рис. 1.3, будет вычисляться в следу-

ющем порядке: к данным, хранящимся в ячейке В4, прибавляется

константа 25, затем полученный результат делится на сумму данных,

хранящихся в ячейках D5, E5 и F5, которая вычисляется с помощью

функции СУММ.

Рис. 1.3. Структура формулы

При определении приоритета арифметических операторов MS

Excel придерживается следующих правил:

ƒ круглые скобки — операторы, заключенные в круглые скобки,

выполняются в первую очередь;

ƒ знак числа — преобразование числа из положительного в отри-

цательное предшествует любой другой операции;

ƒ проценты — следующей выполняется операция вычисления

процентов;

ƒ возведение в степень — затем выполняется эта операция;

ƒ умножение и деление — затем выполняются эти операции;

ƒ сложение и вычитание — выполняются последними.

„ Ввод и редактирование формул

Удобно использовать ввод функции с помощью Мастера функ-

ций, выполнив команды Формулы | Библиотека функций,

 

 

 

 

 

 

 

кнопка

, щелкнув по кнопке

в строке формул или нажав

комбинацию клавиш Shift+F3 на клавиатуре.

Задание 1.1. Вычислите на листе Упражнение 1 столбец НДС

и столбец Сумма.

Выполнение в MS Excel

1. Введите исходные данные так, как это показано на рис. 4.

2. Введите в ячейку D4 формулу =B4*C4*$B$1 и выполните двой-

ной щелчок по Маркеру заполнения (расположен в правом нижнем

углу активной ячейки), формула скопируется в ячейки D5:D6.

3. Введите в ячейку Е4 формулу =B4*C4+D4 и скопируйте ее

в ячейки Е5:Е6. Результаты вычислений представлены на рис. 1.4.

Рис. 1.4. Результаты вычислений

Чаще всего формулы не имеют пробелов. Но если формулы длин-

ные, то использование пробелов и разрывов строк упростит чтение

формулы и не повлияет на результаты вычислений. Для задания раз-

рыва строки нажмите комбинацию клавиш Alt+Enter на клавиатуре.

Задание 1.2. На Листе 3 введите в ячейку В3 формулу.

Выполнение в MS Excel

1. Введите исходные данные так, как это показано на рис. 1.5.

2. Для получения результата в ячейку В3 введите функцию:

=ЕСЛИ(B2>100000;B2*0,05;B2*0,04), используя разрывы строк

и пробелы.

 

 

 

Рис. 1.5. Использование пробелов

Дата добавления: 2014-11-08