Использование формул массива
В 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)~ 52; СТЕПЕНЬ(4;5/4)~ 45/4; СТЕПЕНЬ(98,6;3,2)~ 98,63,2, где символ ~ означает эквивалентность, в данном случае это означает, что запись в языке формул Excel эквивалентна записи в математическом языке.
Следует заметить, что вместо функции СТЕПЕНЬ для возведения в степень можно использовать операцию "^", например 5^2 ~ 52.
Функция КОРЕНЬ — возвращает арифметическое значение квадратного корня.
Синтаксис функции: КОРЕНЬ(число), где число — это число, для которого вычисляется квадратный корень. Если число отрицательно, то функция КОРЕНЬ возвращает значение ошибки #ЧИСЛО!
Примеры использования функции: КОРЕНЬ(16); КОРЕНЬ(-16) равняется #ЧИСЛО!; КОРЕНЬ(ABS(-16)).
Функция EXP — возвращает число e (основание натурального логарифма), возведенное в указанную степень, является обратной к функции LN.
Синтаксис функции: EXP(число), где число — это число, для которого вычисляется экспоненциальная функция с основанием e.
Для того, чтобы вычислить показательную функцию с другим основанием, необходимо использовать операцию возведения в степень (^) или функцию СТЕПЕНЬ.
Примеры использования функции:EXP(2) ~ e2, EXP(1)~e.
Функция LN— возвращает натуральный логарифм числа, является обратной к функции EXP.
Синтаксис функции: LN(число), где число— это положительное вещественное число, для которого вычисляется натуральный логарифм.
Примеры использования функции: LN(86); LN(2,7182818); LN(Х*EXP(3))~ 3Х.
Функция LOG— возвращает логарифм числа по заданному основанию.
Синтаксис функции: LOG(число; основание), где число— это положительное вещественное число, для которого вычисляется логарифм; основание —положительное не равное единицеоснование логарифма. Если основание не задано оно принимается равным 10.
Примеры использования функции: LOG(86;2)~ log2(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)~ 900,ГРАДУСЫ(π)~ 1800.
Следует заметить, что тригонометрической функции 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.
В формулах можно использовать до семи уровней вложения функций. Когда функцияF2 является аргументом функции F1, то F2 считается вторым уровнем вложения. Если в функции F2 содержится в качестве аргумента функция F3, то она будет считаться третьим уровнем вложения функций и т.д.
Чтобы использовать функцию в качестве аргумента, необходимо воспользоваться панелью формул. Например, нажимая стрелку списка формул панели формул, можно вставить функцию ПРОИЗВЕД в качестве аргумента функции СУММ. Для продолжения ввода аргументов в функцию СУММ требуется один раз щелкнуть указателем мыши на ее имени в строке формул.
Мастер функций
Для удобства работы с функциями в Excel предусмотрен Мастер функций, запускаемый кнопкой
— Вставка функций, расположенной на панели инструментов
Стандартнаяили кнопкой