Перемещение и копирование формул

После того, как формула введена ячейку, ее можно перенести или скопировать. При перемещении формулы в новое место таблицы ссылки в формуле не изменяются. При копировании формула перемещается в новое место таблицы, ссылки перенастраиваются на новые адреса. Необходимо определить способ автоматического изменения адресов входящих в эту формулу ссылок. Для этого вводятся понятия относительной и абсолютной ссылок. Абсолютная ссылка – адрес ячейки, не изменяющийся при копировании формулы. Относительная ссылка – адрес ячейки, автоматически изменяющийся при копировании формулы. Относительная ссылка записывается в обычной форме (F3, E7). Для указания абсолютной адресации вводится ее признак – символ $. Различают два типа абсолютной адресации: полная и частичная. Полная абсолютная адресация записывается в формуле, если при ее копировании в адресе ячейки, содержащей исходные данные, не меняются обе части: имя столбца и номер строки. Для указания такой ссылки в ее записи знак $ ставится перед именем столбца и перед номером строки ($A$6). Частичная абсолютная ссылка используется, когда при копировании формулы в этой ссылке меняется только какая-то одна часть: либо имя столбца, либо номер строки ($C8, F$9).

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

Правила копирования формул:

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

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

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

Технология копирования формулы из ячейки в блок ячеек:

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

с помощью команды Копировать из раздела меню Правка скопировать эту формулу в буфер обмена;

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

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

Технология копирования формул из одного блока в другой:

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

командой Копировать из раздела меню Правка скопировать формулы из выделенного блока в буфер обмена;

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

задать команду Вставить из раздела меню Правка.

ВЫЧИСЛЕНИЕ ЧАСТИ ФОРМУЛЫ

При поиске ошибок в составленной формуле бывает удобно посмотреть результат вычисления какой-то части формулы. Для этого:

Встать на ячейку, содержащую формулу.

В строке формул выделить часть формулы, которую нужно вычислить.

F9 – вычисление

          Enter – результат вычисления вставить в формулу.

          Esc – возврат формулы в исходное состояние.

МАСТЕР ФУНКЦИЙ

При вводе формулы обращение к встроенной функции можно написать вручную или вызвать Мастер функций: Вставка Функция или нажать пиктограмму f_x на панели инструментов, или нажать f_x на панели формул. В левом окне Категория нужно выбрать категорию функции, в правом Функция – саму функцию. Затем, нажав кнопку Далее, выполнить пошаговую подстановку аргументов. Посреди окна несколько строк для ввода аргументов, названия которых указаны слева от этих строк. Если название аргумента притушено, значит он необязателен. Значение аргумента можно записать вручную, а можно выделить на листе диапазон клеток. Кнопки f_x предназначены для создания вложенных функций. Справа вдавленный прямоугольник, в котором отображается текущее значение аргумента.

Автосуммирование доступно через кнопку ∑ на панели инструментов. С ее помощью можно очень просто реализовать функцию суммирования, практически не прикасаясь к клавиатуре.

Недопустим символ «пробел» в числе и в формуле. Длинные текстовые данные автоматически растекаются на соседние клетки, при условии, что эти клетки чистые. Формулы автоматически пересчитываются, как только хотя бы один из их аргументов изменен.

СТИЛЬ ПРЕДСТАВЛЕНИЯ ДАННЫХ.

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

Имя стиля – список имен стилей. В это же поле можно ввести имя нового стиля.

Стиль включает – перечень составляющих стиля, любую из которых можно отменить, сняв флаг.

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

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

Удалить – удалить стиль. Активна только для собственных стилей пользователя.

Объединить – используется для копирования стилей из одной рабочей книги в другую.

Чтобы спрятать рабочий лист: Формат Лист Скрыть

Восстановить спрятанный рабочий лист: Формат Лист Показать

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

МАСТЕР УСЛОВНОГО ФОРМАТИРОВАНИЯ.

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

КОНТРОЛЬ ВВОДА.

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

Вкладка Параметры (на переднем плане) определяет условия, проверяемые системой при вводе. Во вкладке Сообщение для ввода формируется подсказка при входе в контролируемую клетку. Во вкладке Сообщение об ошибке формируется сообщение, предъявляемое пользователю, если он ввел неправильное значение. Важнейшей является вкладка Параметры. Здесь нужно задать тип (Тип данных) контролируемой величины. Для большинства типов данных можно задать граничные значения, выбрав одно из отношений (параметр Значение). Флаг Распространить изменения на другие ячейки с тем же условием устанавливает режим, когда изменение условий для одной клетки вызывает идентичные изменения в других с теми же условиями. Установка флага Игнорировать пустые ячейки подавляет вывод сообщения об ошибке при вводе пустого значения в контролируемую ячейку рабочего листа (оно не будет проверяться). Вкладка Сообщение об ошибке предопределяет реакцию системы на ошибочный ввод. Сообщение об ошибке может быть трех видов (перечислены в порядке их «строгости»):

Останов. Самая жесткая реакция. Здесь возможны только два действия пользователя – повтор, уже правильный, ввода (Повторить) или полный отказ от ввода (Отмена).

Предупреждение. Пользователь может игнорировать предупреждение системы (Да) и остановить введенное значение, вернуться для исправления ввода (Нет) или совсем отказаться от него (Отмена).

Сообщение – пользователь может остановить введенные данные (Ок) или отказаться от них (Отмена).

ФУНКЦИИ РАБОЧЕГО ЛИСТА.

Главным инструментом пользователя при работе с электронными таблицами являются клеточные функции, основные из которых рассмотрим далее:

Функция суммирования имеет следующий формат:

СУММ(<число1>;<число2>; …) – возвращает сумму чисел, входящих в список аргументов. Список может содержать до 30 элементов. Если в суммируемом блоке встречаются нечисловые элементы, они участвуют в суммировании как нули. Выражение, содержащее функцию суммирования, можно ввести с клавиатуры, с помощью пиктограммы Автосуммирование или комбинации клавиш Alt+=.

Арифметические функции:

ABS(<число>) – абсолютное значение <числа>;

ЗНАК(<число>) – функция определения знака <числа>. Если аргумент больше 0, возвращается 1, если ноль – то 0, если отрицательное – то –1.

ОСТАТ(<делимое>;<делитель>) – возвращает целочисленный остаток от деления двух чисел. Если <делимое> меньше <делителя>, результат равен делимому. Если деление выполняется без остатка, результат – ноль. Функция определяет кратность одной величины другой величине.

ПРОИЗВЕД(<число1>;<число2>; …) – возвращает произведение <чисел>, входящих в список аргументов. Если в множестве аргументов встречаются нечисловые элементы, они в произведении не участвуют (пропускаются).

ЧАСТНОЕ(<делимое>;<делитель>) – возвращает целую часть частного, полученного от деления (дробь отбрасывается).

НОК(<число1>;<число2>; …) – наименьшее общее кратное всех перечисленных аргументов (до 29), которые должны быть больше 1. Дробная часть (если есть) при аргументах отбрасывается.

НОД(<число1>;<число2>; …) – наибольший общий делитель всех аргументов, которые все должны быть больше 1. Дробная часть аргументов отбрасывается. Единица является делителем любого числа.

Функции округления:

ОКРУГЛ(<число>;<десятичные знаки>) – функция округляет <число> до указанного количества <десятичных знаков>. Второй аргумент может быть положительным (округление происходит справа от десятичной запятой) и отрицательным (округление слева от запятой – младшие целые разряды округляются до нулей).

ОКРВНИЗ(<число>;<множитель>) – округляет заданное <число> до ближайшего меньшего числа, кратного второму аргументу.

ОКРВВЕРХ(<число>;<множитель>) – округляет заданное <число> до ближайшего большего числа, кратного второму аргументу.

ОКРУГЛВНИЗ(<число>;<количество цифр>) – округляет <число> по модулю до ближайшего меньшего целого с заданным <количеством цифр>. Если параметр <количество цифр> равен 0 или опущен, число округляется до ближайшего меньшего целого. Если <количество цифр> больше 0, число округляется до заданного количества десятичных разрядов после запятой. Если меньше 0, целая часть числа округляется слева от десятичной запятой на заданное <количество цифр>.

ОКРУГЛВВЕРХ(<число>;<количество цифр>) – округляет <число> по модулю до ближайшего большего целого с заданным <количеством цифр>.

ЦЕЛОЕ(<число>) – функция возвращает ближайшее снизу целое число от аргумента.

ОТБР(<число>) – отбрасывает все дробные разряды <числа>.

ЧЕТН(<число>) – округляет <число> до ближайшего четного.

НЕЧЕТ(<число>) – округляет <число> до ближайшего нечетного.

Обе функции ЧЕТН() и НЕЧЕТ() положительные значения округляют вверх, а отрицательные – вниз (следует помнить, что ноль число четное).

Степенные функции:

КОРЕНЬ(<число>) – извлекает квадратный корень из положительного <числа>.

СТЕПЕНЬ(<число>;<степень>) – возводит <число> в заданную <степень>.

EXP(<степень>) – возвращает результат возведения в <степень> основания натурального логарифма (е=2,71878…).

LOG10(<число>) – вычисляет десятичный логарифм <числа>.

LOG(<число>;<основание>) – вычисляет логарифм <числа> по заданному <основанию>.

LN(<число>) – вычисляет натуральный логарифм <числа>. Функция является обратной по отношению к функции EXP().

Тригонометрические функции:

ПИ() – возвращает значение константы  .

КОРЕНЬПИ(<число>) – квадратный корень числа .

ГРАДУСЫ(<угол в радианах>) – преобразует <радианную меру угла> в градусную.

РАДИАНЫ(<угол в градусах>) – преобразует <градусную меру угла> в радианную.

SIN(<угол в радианах>) – синус <угла>.

COS(<угол в радианах>) – косинус <угла>.

TAN(<угол в радианах>) – тангенс <угла>.

ASIN(<число>) – арксинус <числа>. Угол возвращается в радианах.

ACOS(<число>) – арккосинус <числа>. Результат в радианах.

ATAN(<число>) – арктангенс <числа>. Результат в радианах.

ATAN2(X; Y) – арктангенс для заданных координат точки Х и Y. Положительный результат соответствует отсчету угла против часовой стрелки относительно оси Х, отрицательный – по часовой стрелке.

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

Текстовые функции:

ЗНАЧЕН(<текст>) – превращает текстовое отображение числа в числовое.

ТЕКСТ(<число>;<формат>) – преобразует <число> в текст, учитывая заданный <формат> представления.

ФИКСИРОВАННЫЙ(<число>[;<количество десятичных знаков>]) – функция округляет <число> до заданного <количества знаков> и преобразует результат в текстовую форму. Если второй аргумент опущен, он полагается равным двум.

ДЛСТР(<текст>) – возвращает длину текстовой строки.

СЖПРОБЕЛЫ(<текст>) – функция удаляет из текстовой строки концевые пробелы и уменьшает число пробелов внутри строк (если есть) до одного.

СОВПАД(<текст1>;<текст2>) – проверяет совпадают ли два аргумента. Если да, результатом является ИСТИНА, иначе – ЛОЖЬ. Здесь подразумевается полное совпадение, включая содержание и длину. Прописные и строчные буквы считаются разными.

НАЙТИ(<искомый текст>;<текст, где введется поиск>[;<начальная позиция>]) – возвращает номер позиции, где <искомый фрагмент> найден в просматриваемом тексте. Необязательный параметр <начальная позиция> задает номер в искомом фрагменте, с которого следует вести поиск. Если параметр <начальная позиция> опущен, он считается равным 1 (поиск ведется с первого символа).

ПОИСК(<искомый текст>;<текст, где ведется поиск>[;<начальная позиция>]) – функция аналогична предыдущей, однако допускает включение в <искомый текст> символов шаблона (? Обозначает один произвольный символ и * обозначает любое число произвольных символов). Применение шаблона удобно при поиске по неточно заданному ключу.

ПРАВСИМВ(<текст>;<длина фрагмента>) – функция извлекает с правой стороны <текста> фрагмент заданной <длины>.

ЛЕВСИМВ(<текст>;<длина фрагмента>) – функция извлекает с левой стороны <текста> фрагмент заданной <длины>.

ПСТР(<текст>;<номер первого символа>;<длина фрагмента>) – функция извлекает из текстовой строки фрагмент, начиная с заданного <номера> указанной <длины>. Отсчет номера позиции ведется слева направо. Если <длина> оказалась больше, чем остаток текста, результатом явится вся оставшаяся часть текста.

СЦЕПИТЬ(<текст1>;<текст2>; …) – возвращает строку, образованную соединением всех перечисленных аргументов. Действие функции аналогично применению оператора &.

СТРОЧН(<текст>) – преобразует буквы <текста> в строчные.

ПРОПИСН(<текст>) – преобразует буквы <текста> в прописные.

Логические функции:

ЕСЛИ(<условие>;<результат, если условие истинно>;[<результат, если условие ложно>]) – функция оценивает логическое <условие>, и если оно истинно, возвращает первый результат, если нет – второй. Большую гибкость при формировании условий придает использование логических функций И(), ИЛИ(), НЕ(). С их помощью можно формулировать достаточно сложные условия.

И(<условие>;<условие>; …) –возвращает значение ИСТИНА, если истинны все аргументы. Функция еще называется функцией логического умножения.

ИЛИ(<условие>;<условие>; …) – возвращает значение ИСТИНА, если истинен хотя бы один из аргументов. Функция еще называется функцией логического сложения.

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

Функции выбора и поиска:

ВЫБОР(<номер>;<значение1>;<значение2>; …) – возвращает значение из списка значений с заданным <номером> от 1 до 29.

ПОИСКПОЗ(<искомое значение>;<область поиска>[;<тип поиска>]) – осуществляет поиск позиции (клетки) в <области поиска>, значение данных в которой близко искомому значению. Результатом является не само найденное значение (оно известно), а его номер в <области поиска>. Искомое значение и область могут быть любого возможного типа. Понятие близости определяется значением параметра <тип поиска>. Он может иметь одно из трех значений:

0 – соответствие должно быть точным (полное совпадение).

1 – ищется наибольшее значение, которое меньше или равно (если есть) искомому. Область поиска должна быть упорядочена по возрастанию. Если параметр не указан, он считается равным 1.

-1 – ищется наименьшее значение, которое больше или равно искомому. Область поиска должна быть упорядочена по убыванию.

ГПР(<искомое значение>;<область поиска>;<номер строки извлечения>[;<тип поиска>]) – осуществляет поиск <искомого значения> в самой верхней строке <области поиска>. Эту строку будем называть ключевой строкой. Результат извлекается из строки с заданным <номером> относительно ключевой строки <области поиска>. Номер ключевой строки блока поиска – всегда 1 (этот номер не имеет никакого отношения к фактическим номерам строк в рабочих листах книги). Т.о., в несколько схематичной форме функция может быть описана следующим образом:

ГПР(<что искать>;<где искать>;<откуда взять>;<как искать>).

Параметр <тип поиска> имеет значение ИСТИНА или ЛОЖЬ. Если ЛОЖЬ, поиск в первой строке будет точным, если ИСТИНА (или параметр не задан) – приблизительным (интервальным). В этом случае найденным считается наибольшее значение, меньшее или равное искомому. Ключевая строка тогда должна быть отсортирована по возрастанию. Следует помнить, что логические значения ИСТИНА и ЛОЖЬ внутри формул могут быть заменены на 1 и 0 соответственно.

ВПР(<искомое значение>;<область поиска>;<номер столбца извлечения>[;<тип поиска>]) – осуществляет поиск <искомого значения> в самом левом ключевом столбце <области поиска>. Результат извлекается из столбца с заданным <номером> относительно ключевого столбца <области поиска>. Функция является вертикальным аналогом функции ГПР().

СМЕЩ(<ссылка>;<строк смещения по вертикали>;<столбцов смещения по горизонтали>;<высота>;<ширина>) – функция возвращает диапазон заданной <высоты> и <ширины>, смещенной относительно <ссылки> на соответствующее число <строк> и <столбцов>. Результатом может быть как адрес одной клетки, так и целый блок. Функция может быть использована другими функциями, работающими с адресами (например, функцией СУММ()).

ЧИСЛСТОЛБ(<область>) – возвращает число столбцов в <области>.

ЧСТРОК(<области>) – возвращает число строк в <области>.

АДРЕС(<номер строки>;<номер столбца>;[<тип ссылки>];[<стиль ссылки>];[<стиль ссылки>];[<имя листа>]) – возвращает в текстовой форме адрес ячейки, находящейся на пересечении строки и столбца с указанными номерами в заданном листе. <Тип ссылки> - указывает тип возвращаемой ссылки и может иметь одно из значений:

1 – абсолютный (например, $A$1);

2 – абсолютная строка, относительный столбец (A$1);

3 – относительная строка, абсолютный столбец ($A1);

4 – относительный (А1).

Если тип не указан, он считается абсолютным (тип 1).

<Стиль ссылки> - логическое значение. ИСТИНА (или опущено) – ссылка в стиле А1; ЛОЖЬ – ссылка в стиле С1К1.

<Имя листа> - имя рабочего листа или листа макросов.

ДВССЫЛ(<ссылка на ячейку>;<стиль ссылки>) – возвращает ссылку, заданную аргументом <ссылка на ячейку>.

<Ссылка на ячейку> - адрес ячейки, которая содержит либо ссылку в стиле А1, либо ссылку в стиле R1C1, либо имя, определенное как ссылка. Если <ссылка> не допустима, возвращается значение ошибки #ССЫЛ!.

<Стиль ссылки> - логическое значение, указывающее стиль ссылки, содержащейся в ячейке (ИСТИНА – ссылка в стиле А1, ЛОЖЬ – в стиле R1C1).

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

ИНДЕКС(<область>;<номер строки>;<номер столбца>) – возвращает ссылку на ячейку, находящуюся на пересечении <строки> и <столбца> с указанными номерами внутри <области>. Результат функции интерпретируется другими функциями как ссылка (адрес), однако функция ИНДЕКС() «чистом виде» возвращает значение (число, текст), находящееся в клетке. Если <область> содержит только одну строку/столбец, то аргумент <строка> или <столбец>, соответственно, является необязательным. Так, для одной строки можно использовать форму ИНДЕКС(<область>;;<столбец>). Если аргумент <строка/столбец> равен 0, функция вернет ссылку соответственно на целую строку/столбец.

СТОЛБЕЦ([<ссылка>]) – возвращает номер столбца по заданной <ссылке> на клетку.

СТРОКА([<ссылка>]) – возвращает номер строки по заданной <ссылке> на клетку.

Сводные функции:

СУММЕСЛИ(<область просмотра>;<критерий поиска>[;<область суммирования>]) – функция ищет в заданной <области просмотра> данные, отвечающие <критерию поиска> и суммирует значения соответствующих ячеек из <области суммирования>. Если <область суммирования> не задана, суммирование производится из <области просмотра>. <Критерий поиска> может включать только одно условие.

СЧЕТ(<область просмотра>) – подсчет в <области просмотра> количества числовых ячеек. Пустые ячейки, логические значения и тексты пропускаются.

СЧЕТЗ(<область просмотра>) – подсчет в <области просмотра> количества непустых ячеек, которыми считаются значения любого типа, включая и строки нулевой длины (“ “).

СЧЕТЕСЛИ(<область просмотра>;<критерий поиска>) – производится подсчет в <области просмотра> числа ячеек, отвечающих <критерию поиска>.

В функциях СЧЕТЕСЛИ() и СУММЕСЛИ() в качестве критерия можно использовать не только константы, но и ссылки на ячейки. Однако последние нельзя брать в кавычки, т.е. возможно сравнение только на строгое равенство.

СРЗНАЧ(<число1>;<число2>; …) – ищет среднее арифметическое всех непустых значений.

МИН(<число1>;<число2>; …) – возвращает минимальное число из списка. Если аргументы не содержат чисел, функция возвращает 0.

СЧИТАТЬПУСТОТЫ(<область просмотра>) – возвращает количество пустых ячеек в заданной <области просмотра> (нули не считаются пустыми значениями).

МАКС(<число1>;<число2>; …) возвращает максимальное число. Если аргументы не содержат чисел, возвращается 0.

НАИБОЛЬШИЙ(<область просмотра>;<номер>) – возвращает наибольший по счету элемент с указанным <номером> от максимального значения в заданной <области просмотра> или массиве данных.

НАИМЕНЬШИЙ(<область просмотра>;<номер>) – возвращает наименьший по счету элемент с указанным <номером> от минимального значения в заданной <области> или массиве.

ПРОМЕЖУТОЧНЫЕ.ИТОГИ(<номер функции>; <обрабатываемый блок>) – возвращает итог в соответствии с <номером функции>, который указывает на вид используемой функции:

1 - Срзнач	2 - Счет	3 - Счетз	4 – Макс
5 - Мин	6 - Произвед	7 – Стандотклон	8 – Стандотклонп
9 - Сумм	10 - Дисп	11 – Диспр

РАНГ(<число>;<диапазон>;<порядок>) – возвращает порядковый номер значения клетки среди элементов заданного диапазона. Параметр <порядок> определяет способ упорядочения. Если он имеет значение 0 или опущен, то Excel определяет ранг числа так, как если бы ссылка была списком, отсортированном в порядке убывания. Если <порядок> - любое ненулевое число, то – в порядке возрастания.

СУММПРОИЗВ(<блок1>;<блок2>; …) – перемножает пары элементов, включенных в <блоки> и возвращает их сумму.

Функции обработки дат:

ДАТА(<год>;<месяц>;<день>) – возвращает дату из отдельных ее компонент, полученных, возможно, в результате вычислений.

СЕГОДНЯ() – возвращает текущую системную дату компьютера.

ДЕНЬНЕД(<дата>;2) – возвращает номер дня недели из <даты>. Первый день недели – Понедельник.

ГОД(<дата>) – возвращает год <даты> в форме числа.

МЕСЯЦ(<дата>) – возвращает номер месяца <даты> в форме числа.

НОМНЕДЕЛИ(<дата>;1) – возвращает номер недели с начала года, на приходится заданная <дата>.

ДЕНЬ(<дата>) – возвращает день <даты> в форме числа.

ДАТАЗНАЧ(<текст>) – преобразует текстовую форму даты в числовую.

РАБДЕНЬ(<начальная дата>;<число дней>[;<праздники>]) – возвращает дату, которая отстоит на заданное число <рабочих дней> от <начальной даты>. Если в определяемый период могут попасть праздники, их можно перечислить как аргументы функции.

ЧИСТРАБДНИ(<начальная дата>;<конечная дата>[;<праздники>]) – используется вычисления количества рабочих дней в диапазоне от <начальной> до <конечной даты>. Функция возвращает число рабочих дней, включая начальную и конечную даты. Если это одна и та же дата, результат – единица.

КОНМЕСЯЦА(<начальная дата>;<число месяцев>) – возвращает дату последнего дня месяца, отстоящего на указанное <число месяцев> от <начальной даты>. Функция используется для вычисления даты вступления в силу договора или даты платежа, которая приходится на конец месяца. Параметр <число месяцев> может быть отрицательным. В этом случае отсчет ведется назад.

ДАТАМЕС(<начальная дата>;<число месяцев>) – возвращает дату, отстоящую на указанное <число месяцев> от <начальной даты>. Функция используется для вычисления даты вступления в силу договора или даты платежа, которая приходится на конец месяца. Параметр <число месяцев> может быть отрицательным. В этом случае отсчет ведется назад.

ТДАТА() – возвращает текущую системную дату и время.

ДОЛЯГОДА(<начальная дата>;<конечная дата>;1) – вычисляет число лет, прошедших между <конечной> и <начальной датами. Дробная часть <дат> отбрасывается, т.е. время при дате (если есть) в вычислениях не участвует. Функцию можно использовать, например, для определения числа полных лет рабочего стажа (если отбросить дробную часть результата). Если требуется вычислить точное число месяцев стажа, нужно прибегнуть к более сложной технологии.

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

финансовые функции используются в коммерческих расчетах и широко представлены в Excel. Данная группа функций позволяет учесть ценность денег в зависимости от времени. Хотя сфера применения финансовых функций, в основном, работа с деньгами, они с успехом могут применяться для решения и других задач, где участвуют время и проценты. В коммерческих расчетах используются два важнейших понятия – наращивание и дисконтирование. Учет наращивания исходного капитала позволяет определить его значение в будущем. Дисконтирование решает обратную задачу – вычисление настоящей величины активов, исходя из желаемого значения в будущем. Приняты следующие обозначения:

ставка – годовая процентная ставка по вкладу.

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

количество периодов – число периодов для которого производится расчеты.

выплата – вносимая/получаемая сумма. Вносимая сумма вводится со знаком минус.

БЗ(<ставка>;<количество периодов>;<выплата>;[<начальное значение>];[<тип>]) – определяет будущее значение (будущую стоимость) вклада, как функцию <начального значения> вклада и срока хранения. Здесь: <тип> - определяет время начисления процентов: в конце/начале периода. Если тип опущен, он считается равным 0.

БЗРАСПИС(<начальный вклад>;<ставки>) – определяет будущее значение инвестиции (<начального вклада>) с переменной процентной <ставкой> в разные периоды.

ПЗ(<ставка>;<количество периодов> [;<периодические выплаты>]; [<разовая выплата>];[<тип>]) – определяет настоящее (текущее) значение вклада в зависимости от ожидаемого дохода в будущем. Эта функция обратная функции БЗ().

ППЛАТ(<ставка>;<количество периодов>;<сумма кредита>; [<остаток>;[<тип>]) – определяет величину периодических выплат для погашения кредита (полного или до заданного остатка) при фиксированной годовой процентной ставке. Если параметр <остаток> опущен, он считается равным 0.

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

Информационные функции:

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

ЕПУСТО(<ссылка>) – возвращает значение ИСТИНА, если ячейка, на которую указывает <ссылка>, пуста и ЛОЖЬ в противном случае.

ЕЛОГИЧ(<ссылка>) – выявляет логическое значение.

ЕНЕТЕКСТ(<ссылка>) – в ячейке находится не текстовое значение или ячейка пуста.

ЕЧИСЛО(<ссылка>) – в ячейке число.

ЕТЕКСТ(<ссылка>) – в ячейке текст.

ЕССЫЛКА(<ссылка>) – возвращает ИСТИНА, если обнаружена ссылка.

Кроме перечисленных информационных функций, имеется функция, позволяющая прямо установить тип значения/клетки.

ТИП(<ссылка>) – возвращает число, указывающее на тип содержащегося в ячейке значения: 1 – числовое, 2 – текстовое, 3 – логическое, 8 – формула, 16 – ошибка, 64 – массив. Функция используется когда результаты вычисления другой функции зависят от типа значения в некоторой ячейке.

ЕЧЕТН(<число>) – возвращает значение ИСТИНА, если число четное и ЛОЖЬ, если число нечетное.

ЕНЕЧЕТ(<число>) – возвращает значение ИСТИНА, если число нечетное и ЛОЖЬ, если число четное.

Если анализируемое число <число> не целое, дробная часть аргумента в функциях ЕЧЕТН() и ЕНЕЧЕТ() отбрасывается.

Ошибочные значения:

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

#ДНЛ/0! – попытка деления на ноль.

#ЗНАЧ! – недопустимый тип аргумента.

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

#Н/Д – неопределенные или отсутствующие данные («нет данных»).

#ПУСТО! – в формуле задано пересечение двух интервалов, которые на самом деле не имеют общих ячеек.

#ССЫЛКА! –недопустимая (обычно отсутствующая) ссылка.

#ЧИСЛО! – используется недопустимый аргумент в числовых формулах, например, отрицательное подкоренное выражение.

Функции анализа ошибок:

эти функции позволяют установить тип возникшей ошибки. Соединяя их с функцией ЕСЛИ(), можно локализовать ошибку в формулах и затем корректно обработать ее.

ЕОШ(<ссылка>) – возвращает ИСТИНА, если обнаруживается значение любой ошибки, кроме #Н/Д.

ЕОШИБКА(<ссылка>) – возвращает ИСТИНА при любом значении ошибки (#Н/Д, #ЗНАЧ!, #ССЫЛ!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ?, или #ПУСТО!).

ЕНД(<ссылка>) возвращает ИСТИНА, если обнаружено значение ошибки #Н/Д (нет данных).

ТИП.ОШИБКИ(<ссылка>) – универсальная функция, возвращающая число, соответствующее типу ошибки: #ПУСТО! – 1, #ДЕЛ/0! – 2, #ЗНАЧ! – 3, #ССЫЛ! – 4, #ЧИСЛО! – 6, #Н/Д – 7. Любое другое значение в анализируемой клетке порождает сообщение – #Н/Д.

Матричные функции:

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

МУМНОЖ(<матрица1>;<матрица2>) – возвращает произведение матриц. Число столбцов <матрицы1> должно совпадать с числом строк <матрицы2>. Результирующая матрица буд

⇐ Предыдущая123Следующая ⇒

Поделиться:

Воспользуйтесь поиском по сайту: