Общие понятия о вычислениях в 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 – Арифметические операторы

Арифметический оператор	Значение (пример)
+ (знак плюс)	Сложение (3+3)
– (знак минус)	Вычитание (3–1) Отрицание (–1)
* (звездочка)	Умножение (3*3)
/ (косая черта)	Деление (3/3)
% (знак процента)	Процент (20%)
^ (крышка)	Возведение в степень (3^2)

 

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

 

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

Оператор сравнения	Значение (пример)
= (знак равенства)	Равно (A1=B1)
> (знак больше)	Больше (A1>B1)
< (знак меньше)	Меньше (A1<B1)
>= (знак больше или равно)	Больше или равно (A1>=B1)
<= (знак меньше или равно)	Меньше или равно (A1<=B1)
<> (знак не равно)	Не равно (A1<>B1)

 

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

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

 

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

Оператор ссылки	Значение (пример)
: (двоеточие)	Ставится между ссылками на первую и последнюю ячейки диапазона. Такое сочетание является ссылкой на диапазон (B5:B15)
; (точка с запятой)	Оператор объединения. Объединяет несколько ссылок в одну ссылку (СУММ(B5:B15;D5:D15))
(пробел)	Оператор пересечения множеств, служит для ссылки на общие ячейки двух диапазонов (B7:D7 C6:C8)

 

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

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

 

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

Ячейка или диапазон	Использование
Ячейку в столбце A и строке 10	A10
Диапазон ячеек: столбец А, строки 10-20.	A10:A20
Диапазон ячеек: строка 15, столбцы B-E.	B15:E15
Все ячейки в строке 5.	5:5
Все ячейки в строках с 5 по 10.	5:10
Все ячейки в столбце H.	H:H
Все ячейки в столбцах с H по J.	H:J
Диапазон ячеек: столбцы А-E, строки 10-20.	A10:E20

 

Ссылка на другой лист. В примере =СРЗНАЧ(Маркетинг!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

Ссылка	Значение
R[-2]C	Относительная ссылка на ячейку, расположенную на две строки выше и в том же столбце
R[2]C[2]	Относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее
R2C2	Абсолютная ссылка на ячейку, расположенную во второй строке и во втором столбце
R[-1]	Относительная ссылка на строку, расположенную выше текущей ячейки
R	Абсолютная ссылка на текущую строку

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

 

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

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

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

 

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

Функция	Назначение
ДАТА	Возвращает заданную дату в числовом формате Microsoft Excel.
ДАТАЗНАЧ	Преобразует дату из текстового формата в числовой.
ДЕНЬ	Преобразует дату в числовом формате в день месяца.
ДНЕЙ360	Вычисляет количество дней между двумя датами на основе 360-дневного года.
ДАТАМЕС	Возвращает дату в числовом формате, отстоящую на заданное число месяцев вперед или назад от начальной даты.
КОНМЕСЯЦА	Возвращает дату в числовом формате для последнего дня месяца, отстоящего вперед или назад на заданное число месяцев.
ЧАС	Преобразует дату в числовом формате в часы.
МИНУТЫ	Преобразует дату в числовом формате в минуты.
МЕСЯЦ	Преобразует дату в числовом формате в месяцы.
ЧИСТРАБДНИ	Возвращает количество рабочих дней между двумя датами.
ТДАТА	Возвращает текущую дату и время в числовом формате.
СЕКУНДЫ	Преобразует дату в числовом формате в секунды.
ВРЕМЯ	Возвращает заданное время в числовом формате.
ВРЕМЗНАЧ	Преобразует время из текстового формата в числовой формат.
СЕГОДНЯ	Возвращает числовой формат текущей даты.
ДЕНЬНЕД	Преобразует дату в числовом формате в день недели.
НОМНЕДЕЛИ	Преобразует числовое представление в число, которое указывает, на какую неделю года приходится указанная дата.
РАБДЕНЬ	Возвращает дату в числовом формате, отстоящую вперед или назад на заданное количество рабочих дней.
ГОД	Преобразует дату в числовом формате в год.
ДОЛЯГОДА	Возвращает долю года, которую составляет количество дней между начальной и конечной датами.

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

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

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

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

 

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

Функция	Назначение
ЯЧЕЙКА	Возвращает информацию о формате, местоположении или содержимом ячейки.
ТИП.ОШИБКИ	Возвращает номер, соответствующий одному из типов ошибок Excel.
ИНФОРМ	Возвращает информацию о текущей операционной среде.
ЕПУСТО	Возвращает логическое значение ИСТИНА, если аргумент является ссылкой на пустую ячейку.
ЕОШ	Возвращает логическое значение ИСТИНА, если аргумент ссылается на любое значение ошибки, кроме #Н/Д.
ЕОШИБКА	Возвращает логическое значение ИСТИНА, если аргумент ссылается на любое значение ошибки.
ЕЧЁТН	Возвращает логическое значение ИСТИНА, если аргумент — четное число.
ЕЛОГИЧ	Возвращает логическое значение ИСТИНА, если аргумент ссылается на логическое значение.
ЕНД	Возвращает логическое значение ИСТИНА, если аргумент ссылается на значение ошибки #Н/Д (значение недоступно).
ЕНЕТЕКСТ	Возвращает логическое значение ИСТИНА, если аргумент ссылается на значение, которое не является текстом.
ЕЧИСЛО	Возвращает логическое значение ИСТИНА, если аргумент ссылается на число.
ЕНЕЧЁТ	Возвращает логическое значение ИСТИНА, если аргумент — нечетное число.
ЕССЫЛКА	Возвращает логическое значение ИСТИНА, если аргумент ссылается на ссылку.
ЕТЕКСТ	Возвращает логическое значение ИСТИНА, если аргумент ссылается на текст.
Ч	Возвращает значение, преобразованное в число.
НД	Возвращает значение ошибки #Н/Д.
ТИП	Возвращает тип значения.

 

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

 

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

Функция	Назначение
И	Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА.
ЛОЖЬ	Возвращает логическое значение ЛОЖЬ.
ЕСЛИ	Выполняет проверку условия.
НЕ	Меняет на противоположное логическое значение своего аргумента.
ИЛИ	Возвращает ИСТИНА, если хотя бы один аргумент имеет значение ИСТИНА.
ИСТИНА	Возвращает логическое значение ИСТИНА.

 

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

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

 

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

Функция	Назначение
ABS	Возвращает модуль (абсолютную величину) числа.
ACOS	Возвращает арккосинус числа.
ACOSH	Возвращает гиперболический арккосинус числа.
ASIN	Возвращает арксинус числа.
ASINH	Возвращает гиперболический арксинус числа.
ATAN	Возвращает арктангенс числа.
ATAN2	Возвращает арктангенс для заданных координат x и y.
ATANH	Возвращает гиперболический арктангенс числа.
COS	Возвращает косинус числа.
COSH	Возвращает гиперболический косинус числа.
ГРАДУСЫ	Преобразует радианы в градусы.
ЧЁТН	Округляет число до ближайшего четного целого.
EXP	Возвращает число e, возведенное в указанную степень.
ФАКТР	Возвращает факториал числа.
ОКРВНИЗ	Округляет число до ближайшего меньшего по модулю целого.
НОД	Возвращает наибольший общий делитель.
ЦЕЛОЕ	Округляет число до ближайшего меньшего целого.
НОК	Возвращает наименьшее общее кратное.
LN	Возвращает натуральный логарифм числа.
LOG	Возвращает логарифм числа по заданному основанию.
LOG10	Возвращает десятичный логарифм числа.
МОПРЕД	Возвращает определитель матрицы, хранящейся в массиве.
МОБР	Возвращает обратную матрицу (матрица хранится в массиве).
МУМНОЖ	Возвращает произведение матриц, хранящихся в массивах.
ОСТАТ	Возвращает остаток от деления.
ОКРУГЛТ	Возвращает число, округленное с требуемой точностью.
НЕЧЁТ	Округляет число до ближайшего нечетного целого.
ПИ	Возвращает число «пи».
СТЕПЕНЬ	Возвращает результат возведения числа в степень.
ПРОИЗВЕД	Возвращает произведение аргументов.
ЧАСТНОЕ	Возвращает целую часть частного при делении.
РАДИАНЫ	Преобразует градусы в радианы.
СЛЧИС	Возвращает случайное число в интервале от 0 до 1.
СЛУЧМЕЖДУ	Возвращает случайное число в заданном интервале.
ОКРУГЛ	Округляет число до указанного количества десятичных разрядов.
ОКРУГЛВНИЗ	Округляет число до ближайшего меньшего по модулю целого.
ОКРУГЛВВЕРХ	Округляет число до ближайшего по модулю большего целого.
ЗНАК	Возвращает знак числа.
SIN	Возвращает синус заданного угла.
SINH	Возвращает гиперболический синус числа.
КОРЕНЬ	Возвращает положительное значение квадратного корня.
СУММ	Суммирует аргументы.
TAN	Возвращает тангенс числа.
TANH	Возвращает гиперболический тангенс числа.
ОТБР	Отбрасывает дробную часть числа.

 

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

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

 

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

Функция	Назначение
ASC	Преобразует полноширинные (двухбайтные) английские буквы или знаки катакана в текстовой строке в полуширинные (один байт).
БАТТЕКСТ	Преобразует число в текст, используя денежный формат Я (БАТ).
СИМВОЛ	Возвращает знак с заданным кодом.
ПЕЧСИМВ	Удаляет все непечатаемые знаки из текста.
КОДСИМВ	Возвращает числовой код первого знака в текстовой строке.
СЦЕПИТЬ	Объединяет несколько текстовых элементов в один.
РУБЛЬ	Преобразует число в текст, используя денежный формат доллара.
СОВПАД	Проверяет идентичность двух текстов.
НАЙТИ	Ищет вхождение одного текста в другой (с учетом регистра).
ФИКСИРОВАННЫЙ	Форматирует число и преобразует его в текст с заданным числом десятичных знаков.
JIS	Преобразует полуширинные (однобайтные) английские буквы или знаки катакана в текстовой строке в полноширинные (два байта).
ЛЕВСИМВ	Возвращает самые левые знаки текстового значения.
ДЛСТР	Возвращает количество знаков в текстовой строке.
СТРОЧН	Делает все буквы в тексте строчными.
ПСТР	Возвращает определенное число знаков из строки текста, начиная с указанной позиции.
PHONETIC	Извлекает фонетические (фуригана) знаки из текстовой строки.
ПРОПНАЧ	Делает прописной первую букву в каждом слове текста.
ЗАМЕНИТЬ	Заменяет знаки в тексте.
ПОВТОР	Повторяет текст заданное число раз.
ПРАВСИМВ	Возвращает самые правые знаки текстовой строки.
ПОИСК	Ищет вхождение одного текста в другой (без учета регистра).
ПОДСТАВИТЬ	Заменяет в текстовой строке старый текст новым.
Т	Преобразует аргумент в текст.
ТЕКСТ	Форматирует число и преобразует его в текст.
СЖПРОБЕЛЫ	Удаляет из текста пробелы.
ПРОПИСН	Делает все буквы в тексте прописными.
ЗНАЧЕН	Преобразует текстовый аргумент в число.