Вставка вложенных функций

Вложенность функций появляется в том случае, если аргументом является другая функция. Можно использовать до 7 уровнейвложенности.

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

Математические функции.

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

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

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

Средний рост =СРЗНАЧ(А1:А10)

Максимальный рост =МАКС(А1:А10)

Минимальный рост =МИН(А1:А10)

1. Подсчет количества значений в исходном интервале, удовлетворяющих заданному условию. Функция имеет следующий синтаксис:

=СЧЕТЕСЛИ (интервал; критерий), где интервал - заданный ряд данных; критерий - проверяемый критерий в заданном интервале (число, выражение, текст).

Примеры записи функции:

a) =СЧЕТЕСЛИ(А1:А9; 30) - сколькоразчисло30 встречается в интервале А1: А9 среди числовых данных;

b) =СЧЕТЕСЛИ(А1:А9; ">30") – подсчет количества значений больше 30;

c) =СЧЕТЕСЛИ(В1:В8; "м") - подсчет количества повторений символа "м" среди текстовых данных.

2. Определение суммы значений при заданном условии.

Синтаксис функции: =СУММЕСЛИ( интервал; критерий; сумм_ интервал), где интервал - ячейки, в которых проверяется критерий; критерий - заданное условие; сумм_ интервал - ячейки, в которых производится суммирование согласно условию.

Примеры записи функции:

а) =СУММЕСЛИ(А1:А4; "<120"; В1:В4) - подсчет суммы чисел в
интервале В1:В4, причем выбираются только те строки, которые
соответствуют значениям меньших 120 в интервале А1: А4.

б) =СУММЕСЛИ(Dl:D7; "инженер"; Е1:Е7) – подсчет суммы значений в интервале Е1:Е7 соответствующих строке "инженер" в интервале D1:D7.

3. РАНГ( число; ссылка; порядок). Если требуется расположить числа в порядке возрастания (убывания), то применяют функция ранжирования. Первый аргумент – номер ячейки, к которой следует применить функцию РАНГ. Второй элемент ссылка – диапазон ячеек (чисел) в котором происходит распределение мест. Третий элемент – число, определяющее вид распределения. Если это число 0 (или вообще отсутствует), то распределение произойдет от максимального до минимального по убыванию; если число отлично от нуля, то распределение будет происходить в обратном порядке от минимального до максимального.

Функции даты и времени

Летоисчисление в Excel начинается с 1 января 1900 года и кончается 31 декабря 2078 года. Все дни нумеруются от 1 до 65380.

Вводить дату можно непосредственно с клавиатуры с использованием символов - разделителей: / (слэш), - (минус), . (точка).

Например: 7/03/95 7-03-95 7.03.95

Для представления даты в ячейке применяются разные форматы, которые можно найти в команде Формат, Ячейки, Число, Дата.

1. Формула =ГОД( 23.05.99)дает результат 1999,т.е. из даты выделяется номергода. Аналогично работают функции МЕСЯЦ, ДЕНЬ.

2. Вфункции ДЕНЬНЕДпараметр типопределяет порядковый номер первого дня недели. Если тип равен 1,первым днем недели считается воскресенье;при типе равном 2 - понедельник; равном 3- первым днем считается понедельник с номером 0.

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

В состав логических функций входят логические константы и логические выражения. Логическая константа- это одно из двух значений: истинаилиложь. Логическое выражениепринимает значение логической константы. Частным случаем логического выражения является выражение отношениядва арифметических выражения, связанных одним из знаков сравнения: <, >, =, <= (меньше или равно), >= (больше или равно), <> (не равно).

1. ЕСЛИ(логическое выражение; значение_ истина; значение_ ложь)Эта функция позволяет осуществить выбор варианта вычислений. Если значение логического выражения истинно,то выбирается значение _ истина;иначе - значение _ ложь.

Функция ЕСЛИ():Начнем с самой простой версии функции ЕСЛИ ():

ЕСЛИ (лог_выражение; значение_если_истина)

Например, рассмотрим следующую формулу:

=ЕСЛИ(А1 >= 1000; "Это слишком много!")

Логическое выражение А1 >= 1000, если оно вер­но (то есть значение в ячейке А1 больше или равняется 1000), то функция воз­вращает строку Это слишком много!. (Если же значение в ячейке А1 меньше 1000, то формула возвращает результат ЛОЖЬ.)

Теперь предположим, что необходимо отобразить другую строку для зна­чений больше некоторого предела, например 10000. Другими словами, если ус­ловие А1 > 1000 выполняется, то нужно провести другую проверку, превышает ли значение 10000: А1 > 10000. Этот сценарий можно обработать с помо­щью вложенной функции ЕСЛИ () в качестве аргумента значение_если_ истина внутри первой функции:

=ЕСЛИ(А1 >= 1000;ЕСЛИ(А1 >= 10000; "Очень много!!"; "Много!"); "Не много")

Если условие А1 > 1000 дает значение ИСТИНА, формула продолжает вычисление во вложенной функции ЕСЛИ (), которая возвращает сообщение Очень много!, если условие А1 > 10000 дает результат истина, и сообщение Много!, если получен результат ложь. Если же условие А1 > 1000 дает значение ЛОЖЬ, то формула возвращает сообщение Не много.

Кроме того, вложенную функцию ЕСЛИ () можно использовать и в аргументе значение_если_ложь. Например, если необходимо, чтобы для значения меньше 100 отображалось сообщение Мало, следует использовать такую версию формулы:

=ЕСЛИ(А1>=1000;"Много!";ЕСЛИ(А1<100;"Мало";"Не много"))

Например, предположим, что нужно вычислить сумму премиальных вы­плат для команды менеджеров по продажам, согласно следующим правилам.

■ Если менеджер не выполнил план по продажам, то премия не выплачивается.

■ Если менеджер перевыполнил план по продажам на величину менее 10%, то выплачивается премия в размере 1000.

■ Если менеджер перевыполнил план по продажам на 10% или больше, выплачивается премия в размере 10000.

Вот формула, которая реализует эти правила:

=ЕСЛИ(D2<0;" ";ЕСЛИ(D2<0,1;1000;10000))

Если значение в ячейке D2 отрицательное, то функция не возвращает зна­чение; если значение в ячейке D2 положительное и меньше 10%, то функция возвращает результат 1000; если значение в ячейке D2 больше или равно 10%, то функция возвращает результат 10000. На рис. 8.3 показано, как работает эта формула.

2. И(логическое выражение 1; логическое выражение 2;...)

Результат функции - истина,если значения всех составных логических выражений истинны;в противном случае - ложь.

Во многих ситуациях необходимо выполнить определенное действие тогда и только тогда, когда выполняются одновременно два условия. Например, ме­неджер по продажам должен получать премию только в том случае, если фак­тическая выручка от реализации превысила плановое число и объем продаж в единицах также превысил плановый показатель. Если же выручка от реализа­ции или объем продаж были ниже запланированных (или и выручка, и объем продаж выли ниже запланированных), то премия не выплачивается. В терми­нах логических операций это называют условием И, поскольку для получения положительного результата должно выполняться первое и второе условие.

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

И(логическое_значение1;[логическое_значение2];...)

логичесхое_значение1 Первое логическое условие для проверки

логическое_значсяие2; ... Второе логическое условие для проверки. Вы може­те ввести любое необходимое количество условий

Результат функции И() вычисляется следующим образом.

■ Если все аргументы возвращают результат ИСТИНА (или ненулевое чис­ло), то функция И() возвращает значение ИСТИНА.

■ Если один или несколько аргументов возвращают результат ЛОЖЬ (или 0), то функция И() возвращает значение ЛОЖЬ.

Функция И() используется в любой ситуации, когда можно применить логическую формулу, но чаще всего она применяется в качестве логического условия в функции ЕСЛИ(). Другими словами, если все логические условия и функции И() дают результат ИСТИНА, то функция ЕСЛИ() возвращает свой результат значение_если_истина; если же одно или несколько логических условий и функция и () дают результат ЛОЖЬ, то функция ЕСЛИ() возвращает результат значение_если_ложь.

Например: =ЕСЛИ(И(В2 > 0; С2 > 0); "1000"; "Премия не выплачивается")

Если значения в ячейках В2 и С2 больше нуля, то формула возвращает ре­зультат 1000; в противном случае формула возвращает сообщение Премия не выплачивается.

3. ИЛИ( логическое выражение 1; логическое выражение 2;...) Результат функции - ложьтолько в том случае, если значения составляющих выражений ложны;во всех остальных случаях – истина.

· Если хотя бы один из аргументов дает результат истина (или ненуле­вое число), функция ИЛИ()возвращает результат ИСТИНА.

· Если все аргументы дают результат ЛОЖЬ (или 0), то функция или () возвращает результат ЛОЖЬ.

Аналогично условию И в некоторых ситуациях действие должно быть вы­полнено, если реализовано хотя бы одно из условий. Например, менеджер по продажам получает премию, если он перевыполнил план по выручке от реали­зации или перевыполнил план по объему продаж.

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

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

Рассмотрим некоторые функцииэтой категории.

Функция СТРОЧН() преобразует заданную строку в символы нижнего ре­гистра (строчные буквы): СТРОЧН(текст)

текст - Строка, которую необходимо преобразовать в нижний регистр

Функция СТРОЧН() часто используется для преобразования импортиро­ванные Excel данных.

Функция ПРОПИСН() преобразует указанную строку в символы верхнего регистра (прописные буквы):

Функция ПРОПНАЧ() преобразует заданную строку таким образом, чтобы первый ее символ строки имел верхний регистр, а все остальные символы были заданы в нижнем регистре: