Использование формул массива

В Excel существует особый класс формул, называемых "формула массива". В общем случае, формула массива возвращает не одно значение как обычная формула, а несколько. Таким образом, для нескольких ячеек, которые составляют интервал массива (массив-интервал), может быть задана одна общая формула — формула массива.

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

Ячейки массива обрабатываются программой как единое целое. На попытку изменить одну из ячеек массива Excel отреагирует сообщением о недопустимости выполнения этой операции. Изменить формулу массива можно путем включения режима редактирования для любой ячейки, содержащей формулу, и внесения необходимых изменений. Для подтверждения изменений в формуле следует использовать клавиатурную комбинацию Ctrl+Shift+Enter.

Массив констант. В любую формулу массива, где используется ссылка на интервал ячеек, можно ввести массив значений, содержащихся в этом интервале. Вводимый массив значений называется массивом констант. Чтобы создать массив констант, необходимо ввести значения, образующие массив констант, непосредственно в формулу, заключив их в фигурные скобки "{ }". При этом значения разделяются с помощью точки с запятой, а строки разделяются двоеточием. Например, {1; 2; 3 : 1; 2; 3}, {1,3; "Среда";ИСТИНА}.

Элементами массива констант могут быть:

§ числа — целые, дробные с десятичной точкой и в экспоненциальной форме;

§ текст — должен быть взят в двойные кавычки;

§ логические значения — ИСТИНА или ЛОЖЬ;

§ значения ошибок.

Массив констант может состоять из элементов разного типа, например, {1;2;3:ИСТИНА;ЛОЖЬ;"Январь"}.

В качестве элементов массива констант нельзя использовать: формулы, знаки доллара "$", круглые скобки, символы процента "%", ссылки.

Массив констант не может иметь столбцы или строки разного размера.

Функции

В Excel существует более 200 встроенных функций, которые разбиты на соответствующие категории. Кроме того, имеется возможность создавать пользовательские функции с помощью языка VBA. Рассмотрим использование наиболее употребляемых функций.

Математические

Алгебраические. Функция СУММ — суммирует числа, определяемые аргументами функции.

Синтаксис функции: СУММ(число1;число2; ... ; число30), где число1, число2, ... ;число30 — аргументы (от 1 до 30) функции, для которых требуется определить сумму.

При использовании функции необходимо иметь в виду следующее:

1. В суммировании участвуют числа, логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов. Например, функция СУММ("5"; 4; ИСТИНА) возвращает значение 10, так как текстовое значение "5"преобразуются в число 5, а логическое значение ИСТИНА преобразуется в число 1;

2. В том случае, если аргументом является массив констант или ссылка на ячейку (интервал ячеек), то учитываются только числа, расположенные в массиве или в соответствующей ячейке (интервале ячеек). Пустые ячейки, логические значения, тексты и значения ошибок игнорируются. Например, функция СУММ(A1; А2; А3) возвращает значение 3, если ячейка A1 содержит текстовое значение "3", ячейка А2 —значение ИСТИНА, а ячейка А3 — число 3. То же самое значение возвратит и функция СУММ(A1:А3);

3. Аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, вызывают ошибки.

Функция ПРОИЗВЕД — перемножает числа, заданные в качестве аргументов и возвращает их произведение.

Синтаксис функции: ПРОИЗВЕД(число1;число2; ... ; число30),где число1, число2, ... ;число30 — аргументы (от 1 до 30) функции, для которых требуется определить произведение

Для этой функции справедливы те же замечания, что и для функции СУММ.

Функция СТЕПЕНЬ — возвращает результат возведения числа в степень.

Синтаксис функции:СТЕПЕНЬ(число;степень),где число — это основание степени, степень — показатель степени, в которую возводится основание. Оно может быть любым вещественным числом.

Примеры использования функции: СТЕПЕНЬ(5;2)~ 5²; СТЕПЕНЬ(4;5/4)~ 4^5/4; СТЕПЕНЬ(98,6;3,2)~ 98,6^3,2, где символ ~ означает эквивалентность, в данном случае это означает, что запись в языке формул Excel эквивалентна записи в математическом языке.

Следует заметить, что вместо функции СТЕПЕНЬ для возведения в степень можно использовать операцию "^", например 5^2 ~ 5².

Функция КОРЕНЬ — возвращает арифметическое значение квадратного корня.

Синтаксис функции: КОРЕНЬ(число), где число — это число, для которого вычисляется квадратный корень. Если число отрицательно, то функция КОРЕНЬ возвращает значение ошибки #ЧИСЛО!

Примеры использования функции: КОРЕНЬ(16); КОРЕНЬ(-16) равняется #ЧИСЛО!; КОРЕНЬ(ABS(-16)).

Функция EXP — возвращает число e (основание натурального логарифма), возведенное в указанную степень, является обратной к функции LN.

Синтаксис функции: EXP(число), где число — это число, для которого вычисляется экспоненциальная функция с основанием e.

Для того, чтобы вычислить показательную функцию с другим основанием, необходимо использовать операцию возведения в степень (^) или функцию СТЕПЕНЬ.

Примеры использования функции:EXP(2) ~ e², EXP(1)~e.

Функция LN— возвращает натуральный логарифм числа, является обратной к функции EXP.

Синтаксис функции: LN(число), где число— это положительное вещественное число, для которого вычисляется натуральный логарифм.

Примеры использования функции: LN(86); LN(2,7182818); LN(Х*EXP(3))~ 3^Х.

Функция LOG— возвращает логарифм числа по заданному основанию.

Синтаксис функции: LOG(число; основание), где число— это положительное вещественное число, для которого вычисляется логарифм; основание —положительное не равное единицеоснование логарифма. Если основание не задано оно принимается равным 10.

Примеры использования функции: LOG(86;2)~ log₂(86); LOG(2,7)~ lg(2,7).

Функция ФАКТР— возвращает факториал числа.

Синтаксис функции: ФАКТР(число), где число— это положительное целое число, для которого вычисляется факториал.

Примеры использования функции: ФАКТР(4)~ 1·2·3·4 = 24; ФАКТР(5)~ 1·2·3·4·5 = 120.

Тригонометрические. Функция SIN возвращает синус числа.

Синтаксис функции: SIN(число), где число— это угол, заданный в радианах, для которого вычисляется синус.

Примеры использования функции: SIN(86)~ sin(86); SIN(-6)~ sin(-6).

Функция COS возвращает косинус числа..

Синтаксис функции: COS(число), где число— это угол, заданный в радианах, для которого вычисляется косинус.

Примеры использования функции: COS(6)~ cos(6); COS(-7) ~ cos(-7).

Функция TAN возвращает тангенс числа..

Синтаксис функции: TAN(число), где число— это угол, заданный в радианах, для которого вычисляется тангенс.

Примеры использования функции: TAN (6)~ tg(6); TAN (-7) ~ tg(-7).

Функция ПИвозвращает, округленное до 15 знаков после запятой число π (пи).

Синтаксис функции: ПИ() — функция не имеет аргументов.

Примеры использования функции: SIN(ПИ()/2)~ sin(π/2) = 1; COS(ПИ())~ cos(π) = 0.

Функция РАДИАНЫпреобразует градусы в радианы.

Синтаксис функции: РАДИАНЫ(число) — где число— это угол, заданный в градусах, который преобразуется в радианы.

Примеры использования функции: РАДИАНЫ(90)~ π/2,РАДИАНЫ(180)~ π.

Функция ГРАДУСЫпреобразует радианы в градусы.

Синтаксис функции: ГРАДУСЫ(число) — где число— это угол, заданный в радианах, который преобразуется в градусы.

Примеры использования функции: ГРАДУСЫ(π/2)~ 90⁰,ГРАДУСЫ(π)~ 180⁰.

Следует заметить, что тригонометрической функции ctg(x) нет, и ее следует выражать через другие тригонометрические функции.

Обратные Тригонометрические. Функция ASINвозвращает арксинус числа в радианах.

Синтаксис функции: ASIN(число), где число— это число в пределах от –1 до 1, для которого вычисляется арксинус.

Примеры использования функции: ASIN(0,5)~ arcsin(0,5); ASIN(6)равняется #ЧИСЛО!

Функция ACOSвозвращает арккосинус числа в радианах.

Синтаксис функции: ACOS(число), где число— это число в пределах от –1 до 1, для которого вычисляется арккосинус.

Примеры использования функции: ACOS(0,5)~ arccos(0,5); ACOS(6)равняется #ЧИСЛО!

Функция ATANвозвращает арктангенс числа в радианах.

Синтаксис функции: ATAN (число), где число— это число, для которого вычисляется арктангенс.

Примеры использования функции: ATAN(0,5)~ arctg(0,5); ATAN(6)~ arctg(6).

Следует заметить, что обратной тригонометрической функции arcctg(x) нет, и ее следует выражать через другие обратные тригонометрические функции.

Логические

Функция ЕСЛИ — возвращает одно из двух значений в зависимости от значения логического выражения.

Синтаксис функции:

ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь),
где лог_выражение — это логическое выражение, принимающее значение ИСТИНА или ЛОЖЬ;

значение_если_истина — это значение, которое возвращается, если первый аргумент лог_выражение имеет значение ИСТИНА.

значение_если_ложь — это значение, которое возвращается, если первый аргумент лог_выражение имеет значение ЛОЖЬ.

Например, функция ЕСЛИ(A1=10;СУММ(B1:B10);"не определена")возвращает сумму чисел, расположенных в интервале B1:B10, если в ячейке A1содержится число 10, и текст "не определена" в противном случае.

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

1. Если лог_выражение имеет значение ИСТИНА и значение_если_истина опущено, то возвращается значение ИСТИНА; если лог_выражение имеет значение ЛОЖЬ и значение_если_ложь опущено, то возвращается значение ЛОЖЬ

2. Аргументызначение_если_истина и значение_если_ложь могут представлять собой формулу (знак равно при этом не набирается);

3. Если какой-либо аргумент функции ЕСЛИ является массивом, то при выполнении вычисляется каждый элемент массива.

Для того, чтобы конструировать более сложные проверки, можно использовать до 7 функций ЕСЛИ, вложенных друг в друга в качестве значений аргументов значение_если_истина и значение_если_ложь.

Статистические

Функция МАКС — возвращает максимальное значение из списка, задаваемого аргументами.

Синтаксис функции: МАКС (число1;число2; ... ; число30),где число1, число2, ... ;число30 — аргументы (от 1 до 30) функции, определяющие список, из которого требуется выделить максимальное значение.

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

1.В качестве аргументов можно использовать числа, логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов. Например, функция МАКС("6"; 4; ИСТИНА) возвращает значение 6, так как текстовое значение "5"преобразуются в число 5, а логическое значение ИСТИНА преобразуется в число 1;

2.В том случае, если аргументом является массив констант или ссылка на ячейку (интервал ячеек), то учитываются только числа, расположенные в массиве или в соответствующей ячейке (интервале ячеек). Пустые ячейки, логические значения, тексты и значения ошибок игнорируются. Например, функция МАКС(A1; А2; А3) возвращает значение 3, если ячейка A1 содержит текстовое значение "5", ячейка А2 —значение ИСТИНА, а ячейка А3 — число 3. То же самое значение возвратит и функция МАКС(A1:А3).Если логические значения или текст не должны игнорироваться, следует использовать функцию МАКСА;

3.Аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, вызывают ошибки.

4.Если аргументы не содержат чисел, функция МАКС возвращает значение 0.

Функция МИН — возвращает минимальное значение из списка, задаваемого аргументами.

Синтаксис функции: МИН (число1;число2; ... ; число30),где число1, число2, ... ;число30 — аргументы (от 1 до 30) функции, определяющие список, из которого требуется выделить минимальное значение.

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

Вложенные функции

Функции могут использоваться как аргументы в других функциях. Если функция используется в качестве аргумента, т.е. является вложенной функцией, то она должна возвращать аргументу значение того же типа. Если функция возвращает значение другого типа, отобразится ошибка #ЗНАЧ! Например, следующая формула =ЕСЛИ(СРЗНАЧ(А1:А5)>60;СУММ(В1:В5);СУММ(В1:В5)) использует вложенную функцию СРЗНАЧ для сравнения ее значения со значением 60 — результат сравнения является логической величиной (ИСТИНА или ЛОЖЬ). Это требуемый тип для первого аргумента функции ЕСЛИ. Кроме того, в формуле имеется вложенная функция СУММ, возвращающая значение суммы числовых данных, расположенных в соответствующих интервалах. Это также требуемый тип для второго и третьего аргументов функции ЕСЛИ.

Другой пример, рассмотрим формулу =ЕСЛИ(СРЗНАЧ(А1:А5)>60;СУММ(В1:В5)<4;СУММ(В1:В5)). На первый взгляд тип второго аргумента не соответствует требуемому типу. Однако это не так, результат, возвращаемый сравнением СУММ(В1:В5)<4является тоже значением, но только значением логического типа (ИСТИНА, ЛОЖЬ), который допустим для второго аргумента функции ЕСЛИ.

Однако если формула имеет вид =ЕСЛИ(СРЗНАЧ(А1:А5)>60;В1:В5;В5)), то при условии, что среднее значение данных, расположенных в ячейках А1:А5, меньше 60, в ячейке, где расположена формула, отобразится ошибка #ЗНАЧ! Это объясняется тем, что адресный оператор ":" (двоеточие) не формирует результат какого либо типа данных, допустимого в Excel.

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

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

Мастер функций

Для удобства работы с функциями в Excel предусмотрен Мастер функций, запускаемый кнопкой

— Вставка функций, расположенной на панели инструментов

Стандартнаяили кнопкой