Общие понятия о вычислениях в Microsoft Excel

Лабораторная работа №3

ВЫЧИСЛЕНИЯ В MS EXCEL

Цель работы: создание и копирование формул; порядок операций в формулах; функции; изучение способов ссылок на ячейки в Microsoft Excel.

ТЕОРЕТИЧЕСКИЕ ПОЛОЖЕНИЯ

Общие понятия о вычислениях в Microsoft Excel

Все вычисления в Microsoft Excel выполняются с помощью формул, которые можно вводить в любые ячейки листа. Если содержимое ячейки начинается со знака равенства (=), Excel полагает, что вслед за этим знаком идет формула, и пытается выполнить указанную операцию. Когда это удается, в ячейку с такой формулой выводится результат расчета. Если что-то не так, появляется сообщение об ошибке.

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

Формула также может включать следующие элементы:

- константы;

- операторы;

- ссылки;

- функции.

1.1.1 Константы. Константа представляет собой готовое (не вычисляемое) значение. Например, дата 09.10.2008, число 210 и текст «Прибыль за квартал» являются константами. Выражение и его значение константами не являются. Если в формуле в ячейке не содержится ссылок на другие ячейки (например, формула имеет вид =30+70+110), значение в такой ячейке изменяется только после изменения формулы вручную.

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

- арифметические;

- текстовые;

- операторы сравнения;

- операторы ссылок.

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

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

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

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

Текстовый оператор конкатенации. Амперсанд (&) используется для объединения нескольких текстовых строк в одну строку. Например, "Северный"&"ветер".

Оператор ссылки. Для описания ссылок на диапазоны ячеек используются операторы, представленные в табл. 1.3.

Таблица 1.3 – Оператор ссылки

1.1.3 Ссылки. Ссылка указывает на ячейку или диапазон ячеек листа и передает в Microsoft Excel сведения о расположении значений или данных, которые требуется использовать в формуле. При помощи ссылок можно использовать в одной формуле данные, находящиеся в разных частях листа, а также использовать в нескольких формулах значение одной ячейки. Кроме того, можно задавать ссылки на ячейки других листов той же книги и на другие книги. Ссылки на ячейки других книг называются связями.

Стиль ссылок A1. По умолчанию Microsoft Excel использует стиль ссылок A1, определяющий столбцы буквами (от A до IV, всего не более 256 столбцов), а строки номерами (от 1 до 65536). Эти буквы и номера называются заголовками строк и столбцов. Для ссылки на ячейку введите букву столбца, а следом номер строки. Например, ссылка B2 указывает на ячейку, расположенную на пересечении столбца B и строки 2. Примеры ссылок представлены в табл. 1.4.

Таблица 1.4 – Примеры ссылок стиля А1

Ссылка на другой лист. В примере =СРЗНАЧ(Маркетинг!B1:B10) функция СРЗНАЧ используется для расчета среднего значения в диапазоне B1:B10 на листе «Маркетинг» в той же самой книге.

Обратите внимание на то, что имя листа и восклицательный знак (!) предшествуют ссылке на диапазон ячеек.

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

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

Смешанные ссылки. Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т.д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т.д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется

Стиль трехмерных ссылок. Трехмерные ссылки используются при необходимости анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. Microsoft Excel использует все листы, хранящиеся между начальным и конечным именами, указанными в ссылке. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от Лист2 до Лист13 включительно.

Трехмерные ссылки могут быть использованы для создания ссылок на ячейки на других листах, определения имен и создания формул с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН, СТАНДОТКЛОНА, СТАНДОТКЛОНП, СТАНДОТКЛОНПА, ДИСП, ДИСПА, ДИСПР и ДИСПРА.

Трехмерные ссылки нельзя использовать в формулах массива. Формула массива – это формула, выполняющая несколько вычислений над одним или несколькими наборами значений, а затем возвращающая один или несколько результатов. Формулы массива заключены в фигурные скобки { } и вводятся нажатием клавиш Ctrl+Shift+Enter.

Трехмерные ссылки нельзя использовать вместе с оператором пересечения (пробел), а также в формулах, использующих неявное пересечение. Неявное пересечение – это ссылка на диапазон ячеек вместо одной ячейки, из которого в расчете выбирается одна соответствующая ячейка. Например, если в ячейке C10 содержится формула =B5:B15*5, на 5 будет умножено значение из ячейки B10, потому что ячейки B10 и C10 находятся в одной строке.

Для присвоения имени ячейкам на нескольких листах (создания трехмерной ссылкой) необходимо:

1) выберите команду меню Вставка Þ Имя Þ Присвоить;

2) введите имя в диалоговом окне Присвоить имя;

3) если в поле Формула содержится ссылка, выделите ссылку вместе со знаком равенства (=) и нажмите клавишу Backspace;

4) введите знак равенства (=) в поле Формула;

5) выберите ярлычок первого листа, на который нужно сослаться, и, удерживая нажатой клавишу Shift, выберите ярлычок последнего листа;

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

Стиль ссылок R1C1. Также можно использовать стиль ссылок, в котором нумеруются как строки, так и столбцы. Стиль ссылок R1C1 полезен при вычислении положения столбцов и строк в макросах. В стиле ссылок R1C1 Microsoft Excel указывает положение ячейки буквой «R», за которой идет номер строки, и буквой «C», за которой идет номер столбца (табл. 1.5).

Таблица 1.5 – Примеры ссылок стиля R1C1

При записи макроса Excel записывает некоторые команды с использованием стиля ссылок R1C1. Например, если записывается такая команда как нажатие кнопки Автосумма для вставки формулы, суммирующей диапазон ячеек, Excel использует при записи формулы стиль ссылок R1C1, а не A1. Чтобы включить или выключить стиль ссылок R1C1 выберите команду меню Сервис Þ Параметры, перейдите на вкладку Общие и установите или снимите флажок Стиль ссылок R1C1.

1.1.4 Функции. Функции – это заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Эти функции позволяют выполнять как простые, так и сложные вычисления. Например, функция ОКРУГЛ округляет число в ячейке A10. Различают следующие категории функций:

- Функции баз данных. В Excel включены функции рабочего листа, используемые для анализа данных из списков или БД. Каждая из этих функций, которые из соображений совместимости имеют обобщенное название БДФункция, использует три аргумента: база_данных, поле и критерий. Эти три аргумента ссылаются на интервалы ячеек на рабочем листе, которые используются данной функцией.

- Функции даты и времени (табл. 1.6).

Таблица 1.6 – Функции даты и времени

- Внешние функции. Эти функции загружаются в виде надстроек. Надстройка – вспомогательная программа, служащая для добавления в Microsoft Office специальных команд или возможностей.

- Инженерные функции.

- Финансовые функции.

- Информационные функции (табл. 1.7).

Таблица 1.7 – Информационные функции

- Логические функции (табл. 1.8).

Таблица 1.8 – Логические функции

- Функции ссылки и автоподстановки.

- Арифметические и тригонометрические функции (табл. 1.9).

Таблица 1.9 – Некоторые арифметические и тригонометрические функции

- Статистические функции.

- Текстовые функции (табл. 1.10).

Таблица 1.10 – Некоторые текстовые функции

12
• 3
• Далее ⇒