Тригонометрические функции
ü ПИ() – возвращает значение константы p=3,14159265358979.
ü ГРАДУСЫ(угол в радианах) – преобразует радианную меру угла в градусную.
ü РАДИАНЫ(угол в градусах) – преобразует градуснуюмеруугла в радианную.
ü SIN(угол в радианах) – синус угла.
ü COS(угол в радианах) – косинус угла.
ü TAN(угол в радианах) – тангенс угла.
ü ASIN(число) – арксинус числа. Угол возвращается в радианах.
ü ACOS(число) – арккосинус числа. Результат в радианах.
ü ATAN(число) – арктангенс числа. Результат в радианах.
ü ATAN2(X;Y) – арктангенс для заданных координат точки X и Y.
Положительный результат соответствует отсчету угла против часовой стрелки относительно оси Х, отрицательный – по часовой стрелке. Функция ATAN(y/x) аналогична ATAN2(x;y), но в последней аргумент Х может быть равен 0. Примеры: SIN(РАДИАНЫ(90))=1, SIN(ПИ()/2)=1, ГРАДУСЫ(ATAN(1))=45.
Заключая обзор математических функций, заметим, что их список гораздо шире. Кроме перечисленных выше, в Excel имеется группа тригонометрических гиперболических функций, множество статистических функций, инженерные функции, функции для комплексных переменных и др., которые мы здесь не рассматриваем.
Текстовые функции
ü ЗНАЧЕН(текст) – превращает текстовое отображение числа в числовое.
Пример: ЗНАЧЕН("1232,56")=1232,56.
ü ТЕКСТ(число; формат) – преобразует число в текст, учитывая заданный формат представления.
Примеры: ТЕКСТ(2,715; "00,00")="02,72", ТЕКСТ("25.3.98";"ДДДД")="Среда".
ü ФИКСИРОВАННЫЙ(число[; количество десятичных знаков])
– функция округляет число до заданного количествазнаков и преобразует результат в текстовую форму. Если второй аргумент опущен, он полагается равным двум.
Пример: ФИКСИРОВАННЫЙ(12345,678; 2)="12345,68", ФИКСИРОВАННЫЙ(12345,678; -3)="12000".
ü ДЛСТР(текст) – возвращает длину текстовой строки. Пример: ДЛСТР("Клавиша Enter")=13.
ü СЖПРОБЕЛЫ(текст) – функция удаляет из текстовой строки концевые пробелы и уменьшает число пробелов внутри строк (если есть) до одного.
Примеры: СЖПРОБЕЛЫ(" 1 Мая ")= "1 Мая", СЖПРОБЕЛЫ(" Пере ")&"кресток"= "Перекресток".
ü СОВПАД(текст1; текст2) – проверяет совпадают ли два аргумента. Если да, результатом является ИСТИНА, иначе – ЛОЖЬ. Здесь подразумевается полное совпадение, включая содержание и длину. Прописные и строчные буквы считаются разными буквами.
Примеры: СОВПАД("Саша";"Саша")= ИСТИНА, СОВПАД("Саша";"cаша")= ЛОЖЬ,
СОВПАД("Саша";"Саша ")= ЛОЖЬ.
ü ЗАМЕНИТЬ(исходный текст; начальная позиция замены; число заменяемых символов в исходном тексте; текст замены)– функция заменяет часть исходной текстовой строки на новую текстовую строку (текст замены) с заданной начальной позиции.
Примеры: ЗАМЕНИТЬ("1234567"; 3; 4; "*")=12*7, ЗАМЕНИТЬ("1234567"; 1; 9; "*")=*,
ЗАМЕНИТЬ("1234567"; 4; 0; "*")=123*4567,
ЗАМЕНИТЬ("1234567"; 4; 0; " меньше ")=123 меньше 4567.
ü НАЙТИ(искомый текст; текст, где ведется поиск [; начальная позиция поиска])– возвращает номер позиции, где искомый фрагмент найден в просматриваемом тексте. Необязательный параметр начальная позиция задает номер в искомом фрагменте, с которого следует вести поиск. Если параметр начальная позиция опущен, он считается равным 1 (поиск ведется с первого символа).
Пример: НАЙТИ("Петропавловский";"павлов")=6.
ü ПОИСК(искомый текст; текст, где ведется поиск [;начальная позиция])– функция аналогична предыдущей, однако допускает включение в искомый текст символов шаблона (? обозначает один произвольный символ и * обозначает любое число произвольных символов). Применение шаблона удобно при поиске по неточно заданному ключу. Например, вы хотите выяснить факт нахождения в клетке С12 фамилии, начинающейся на букву И с инициалами А.В. Функция ПОИСК("И*А.В";C12) вернет значение 1, если в анализируемой ячейке находится, например, текст Иванов А.В. и значение ошибки #ЗНАЧ!, если поиск неуспешный.
ü ПРАВСИМВ(текст; длина фрагмента)– функция извлекает с правой стороны текста фрагмент заданной длины. Пример: ПРАВСИМВ("в нашем случае";6)= "случае".
ü ЛЕВСИМВ(текст; длина фрагмента)– функция извлекает с левой стороны текста фрагмент заданной длины. Пример: ЛЕВСИМВ("в нашем случае";7)= "в нашем".
ü ПСТР(текст; номер первого символа; длина фрагмента)– функция извлекает из текстовой строки фрагмент, начиная с заданного номера указанной длины. Отсчет номера позиции ведется слева направо. Если длина оказалась больше, чем остаток текста, результатом явится вся оставшаяся часть текста. Пример: ПСТР("в нашем случае";3;5)= "нашем".
ü СЦЕПИТЬ(текст1; текст2; ...) – возвращает строку, образованную соединением всех перечисленных аргументов. Действие функции аналогично применению оператора &. Функция работает и с числовыми значениями, воспринимая их как символы.
Пример: СЦЕПИТЬ("Саша ";"Петров")="Саша Петров", СЦЕПИТЬ(3;5,81)="35,81"
ü СТРОЧН(текст) – преобразует буквы текста в строчные. Пример: СТРОЧН("А. Петров")="а. петров".
ü ПРОПИСН(текст) – преобразует буквы текста в прописные.
Пример: ПРОПИСН("А. Петров")= "А. ПЕТРОВ".
Логические функции
Логические функции позволяют строить выражения, вырабатывающие разные результаты в зависимости от некоторых условий. Эта группа функций является чрезвычайно полезным инструментом Excel, придавая ему необходимую гибкость.
ü ЕСЛИ(условие; результат, если условие истинно; [результат, если условие ложно])– функция оценивает логическое условие, и если оно истинно, возвращается первый результат, если нет – второй.
Пример: Далее функция в зависимости от результата сравнения содержимого числовых клеток А1 и В5 выдает первое "A1 больше B5" или второе "A1 меньше или равно B5" сообщение в клетке, куда была внесена
 |
=ЕСЛИ(A1>B5; "A1 больше B5";"A1 меньше или равно B5").
Здесь результатом является выражение текстового типа.
Пример. Пусть требуется выяснить не какое из чисел А1, В5 является большим, а показать само это число
=ЕСЛИ(A1>B5; A1; B5).
Результатом будет значение числового типа. Очевидно, что его можно использовать в качестве слагаемого, сомножимого и т.п. Так, корректным будет выражение вида =5*ЕСЛИ(A1>B5;A1;B5)-24.
В функцию ЕСЛИ() могут включаться другие такие функции (до семи) для формирования более сложных условий. Положим, имеются три клетки А1, А2, А3, о которых нужно выяснить, правда ли, что значение А1 больше остальных. Эту функцию реализует следующая строка:
 |  |
=ЕСЛИ(A1>B1;ЕСЛИ(A1>C1; "A1 самое большое";
 |
"А1 не самое большое"); "А1 не самое большое").
Параметр результат, если условие ложно в функции ЕСЛИ() может быть опущен. Тогда, если результат, полученный при анализе условия, ложен, функция вырабатывает логическое значение ЛОЖЬ. Обычно такого не следует допускать, но в некоторых случаях это позволяет немного упростить функцию ЕСЛИ(). Так, например, если она участвует в операции сложения, значение ЛОЖЬ интерпретируется как 0.
Большую гибкость при формировании условий придает использование логических функций И(), ИЛИ(), НЕ(). С их помощью можно формулировать достаточно сложные условия.
ü И(условие; условие; ...) – возвращает значение ИСТИНА, если истинны все аргументы. Функция еще называется функцией логического умножения.
ü ИЛИ(условие; условие; ...) – возвращает значение ИСТИНА, если истинен хотя бы один из аргументов. Функция еще называется функцией логического сложения.
ü НЕ(условие) – возвращает значение ИСТИНА, если ложен аргумент и наоборот. Функция еще называется функцией отрицания или инверсии, поскольку ее значение всегда противоположно значению аргумента.
Пример. Выяснить, правда ли, что А1 больше остальных (решение предыдущей задачи):
ЕСЛИ(И(A1>A2;A1>A3);"A1 самое большое";"A1 не самое большое").
Пример: Выяснить, правда ли, что среди клеток есть отрицательные.
ЕСЛИ(ИЛИ(A2<0; A1<0; A3<0); "Правда"; "Неправда").
Видим, что применение логических функций существенно упрощает запись сложных условий.
Функции И(), ИЛИ(), НЕ() по смыслу, в общем, отвечают соответствующим союзам в русском языке. Некоторые замечания следует сделать в отношении функции ИЛИ(). В обычном предложении слово ИЛИ может быть как объединяющим, так и разделяющим союзом в зависимости от контекста, в котором оно используется. Объединяющее ИЛИ определяет событие, которое произойдет, если имеет место хотя бы одно из перечисленных условий (в том числе, одновременно несколько из них, например, все условия сразу). Именно такой смысл имеет функция ИЛИ(), описанная выше. Разделяющее ИЛИ определяет событие, которое произойдет, если наблюдается только строго одно из условий-участников, но не несколько сразу. Если нам все-таки нужно описать условие, соответствующее разделяющему ИЛИ, нужно сформировать логическую функцию вида
ИЛИ(И(условие1; НЕ(условие2));И(НЕ(условие1);условие2)).
Пусть требуется сформировать конкретную функцию, выявляющую факт того, что только строго одна из клеток А1 или А2 больше 0
ЕСЛИ(ИЛИ(И(A1>0;НЕ(A2>0));И(НЕ(A1>0);A2>0));"ДА";"НЕТ")
или несколько проще
ЕСЛИ(ИЛИ(И(A1>0;A2<=0);И(A1<=0;A2>0));"ДА";"НЕТ"). Функция формирует ответ вида ДА/НЕТ.
Замечание. Часто вместо функций И, ИЛИ удобно использовать операции умножения и сложения. Так для предыдущей задачи выявления того, что строго одна из клеток А1 или А2 больше 0 можно записать
=ЕСЛИ((A1>0)*(A2<=0)+(A1<=0)*(A2>0);"ДА";"НЕТ").
Здесь, если первые два условия удовлетворяются, формула приведет нас к результату следующего вида
=ЕСЛИ(1*1+0*0;"ДА";"НЕТ")=ЕСЛИ(1;"ДА";"НЕТ")="ДА",
Так как 1 (и вообще любое число не равное нулю) интерпретируется Excel как ИСТИНА ответом будет слово ДА.
Пример. Рассмотрим аналогичный, но более сложный пример. Пусть нам требуется выяснить факт того, что только одна из четырех ячеек (А1, А2, А3, А4) больше нуля. Очевидное решение заключается в том, что перебираются все возможные комбинации аргументов, в которых только одно из значений больше нуля
=ЕСЛИ(ИЛИ(И(A1>0;A2<=0;A3<=0;A4<=0); И(A1<=0;A2>0;A3<=0;A4<=0);
И(A1<=0;A2<=0;A3>0;A4<=0); И(A1<=0;A2<=0;A3<=0;A4>0)); "Правда"; "Неправда").
Получилось громоздко. Часто, однако, можно упростить решение, если подходить к нему “не в лоб”. Здесь лучше не анализировать каждую из четырех возможных комбинаций значений этих ячеек, а воспользоваться некоторым косвенным признаком, – для каждой из ячеек выяснить, больше ли она нуля, и, если больше, в качестве результата выработать 1 (например, ЕСЛИ(А1>0;1)). Теперь, если результат сложения таких элементарных выражений в точности равен 1, значит только одна из ячеек больше нуля. Тогда запишем:
=ЕСЛИ(ЕСЛИ(A1>0;1)+ЕСЛИ(A2>0;1)+ЕСЛИ(A3>0;1)+ЕСЛИ(A4>0;1)=1; "Правда";"Неправда").
Третий аргумент внутренних функций ЕСЛИ() опущен поскольку, как уже говорилось, если анализируемое условие ложно, будет выработано значение ЛОЖЬ, которое в цепочке сложений будет восприниматься как ноль. Можно еще сократить функцию, оставив от внутренних ЕСЛИ() только одни условия. Окончательно
=ЕСЛИ((A1>0)+(A2>0)+(A3>0)+(A4>0)=1;"Правда";"Неправда").
Рассмотрим пример “из жизни”.
Пример. Положим, требуется построить таблицу выявления факта пригодности призывников к военной службе (Годен/Не годен), например, к зачислению в летное училище. Пусть это невозможно для лиц, имеющих недостаточные/избыточные значения антропометрических параметров (рост и вес). Положим, отклоняются кандидатуры ростом менее 150 см или более 200 см, а также с весом менее 55 кг или свыше 100 кг. Иными словами
=ЕСЛИ(рост<150 ИЛИ рост>200 ИЛИ вес <55 ИЛИ вес>100, то Не годен, иначе Годен).
Или в виде формулы для первой строки таблицы на рис. 4.7-1.
D2=ЕСЛИ(ИЛИ(B2<150; B2>200; C2<55; C2>100);"Не годен";"Годен"),
Аналогичные результаты можно получить с помощью логической функции, в некотором смысле обратной к исходной. Для этого следует все функции И() заменить на ИЛИ() и, наоборот, перевернуть условия на противоположные и поменять местами вырабатываемые результаты (Годен/Нет):
D2=ЕСЛИ(И(B2>=150;B2<=200;C2>=55;C2<=100);"Годен";"Нет")
| A | B | C | D | |
| Призывник | Рост | Вес | Отбор | |
| Иван | Не годен | |||
| Петр | Годен | |||
| Рис. 4.7-1 |
Этот подход справедлив всегда и иногда может привести к более простым выражениям. Такое преобразование в алгебре логики называется правилом инверсии. Функции, содержащие несколько вложенных условий (несколько ЕСЛИ) могут строиться различным образом. Чтобы избежать ошибок удобно придерживаться следующей схемы их формирования (на примере анализа трех условий):
=ЕСЛИ(условие1; ответ_да_1;
ЕСЛИ(условие2; ответ_да_2;
ЕСЛИ(условие3; ответ_да_3;
ответ_нет_3))).
Здесь после проверки условия (например, условия1) сначала записывается выражение, соответствующее реакции функции при истинности анализируемого условия (ответ_да_1), а только затем проверяется следующее условие2 и т.д.
Или =ЕСЛИ(условие1;ответ_да_1;ЕСЛИ(условие2; ответ_да_2; ЕСЛИ(условие3; ответ_да_3; ответ_нет_3))).
Пример. Пусть требуется вычислить значения кусочно-ломанной функции Y(X) следующего вида
| A | B | C | D | E | F | G | H | I | J | K | L | M | |
| X | -6 | -4 | -2 | ||||||||||
| Y | -1 | ||||||||||||
| Рис. 4.7-2 |
5+Х при Х<0,
Y= 5 при 0£Х<10,
10-0,5X при 10£Х.
в диапазоне изменения аргумента Х от -6 до +16 с шагом 2.
|
Здесь Y=ЕСЛИ(X<0;5+X;ЕСЛИ(И(0<=X;X<10);5;10-0,5*X))
или в адресах для первого значения Y
B2=ЕСЛИ(B1<0;5+B1;ЕСЛИ(И(0<=B1;B1<10);5;10-0,5*B1))
или проще
B2=ЕСЛИ(B1<0;5+B1;ЕСЛИ(B1<=10;5;10-0,5*B1)).
Упрощение стало возможным поскольку, если в первом ЕСЛИ() условие В1<0 неверно, значит обязательно верно противоположное условие В1>=0. Таким образом, выяснять это больше не нужно.
Вообще, вложенных функций ЕСЛИ() должно быть на единицу меньше, чем возможных диапазонов данных. В нашем случае, поскольку ось абсцисс Х разбита на три интересующих нас подмножества, должно быть две функции ЕСЛИ(). Диаграмма функции, построенная с помощью средств деловой графики Excel из таблицы рис. 4.7-2, приведена на рис. 4.7-3.
@ Рассмотрим некоторые технические вопросы построения функций анализа для очень распространенной задачи вычисления значения одной величины в зависимости от диапазона, в котором находится другая величина.
Пример. Положим, банковский процент следующим образом зависит от величины вклада, находящегося в ячейке А1 (в верхней строке рисунка показаны значения процентов, в нижней – диапазоны вкладов).
| Процент: | 10% | 12% | 14% | 17% | 20% |
 Вклад:
| до 10т | до 20т | до 30т | до 40т | свыше 40т |
Таким образом, здесь для вклада размером до 10 тыс. банковский процент составляет 10%, при вкладе от 10 до 20 тыс. – 12% и т.д. Здесь можно записать, например, следующее выражение для вычисления прибавки к вкладу по истечении года хранения
=А1*ЕСЛИ(А1<10;10%; ЕСЛИ(И(А1>=10;А1<20);12%;
ЕСЛИ(И(А1>=20;А1<30);14%; ЕСЛИ(И(А1>=30;А1<40);17%;20%)))).
Так как диапазонов пять, нам понадобилось выражение с четырьмя функциями ЕСЛИ(). Невыполнение четырех перечисленных условий, обязательно будет указывать на факт того, что вклад>40, чему соответствует 20% прибыли. Поскольку ограничения для каждого диапазона здесь указаны с обоих сторон, порядок перечисления и анализа условий безразличен. В данном случае логическое выражение, хотя и правильно, но избыточно. Функцию можно упростить, если при выполнении сравнений двигаться строго в одном направлении, в сторону увеличения анализируемого параметра, используя знак отношения меньше (“<”)
=А1*ЕСЛИ(А1<10;10%; ЕСЛИ(А1<20;12%; ЕСЛИ(А1<30;14%; ЕСЛИ(А1<40;17%;20%))))
или в сторону уменьшения, используя знак отношения больше (“>”.)
=А1*ЕСЛИ(А1>=40;20%; ЕСЛИ(А1>=30;17%; ЕСЛИ(А1>=20;14%; ЕСЛИ(А1>=10;12%;10%)))).
Поясним последнее выражение, в предположении что А1=25, т.е.
=25*ЕСЛИ(25>=40;20%; ЕСЛИ(25>=30;17%; ЕСЛИ(25>=20;14%; ЕСЛИ(25>=10;12%;10%)))).
Анализ условий выполняется слева направо и поскольку А1 меньше 40 и меньше 30 – первые два условия ложны. Отношение А1>=20 является первым истинным условием в логической цепочке. Его результатом будет 14% и на этом анализ условий и вычисления прекращаются.
=25*ЕСЛИ(ЛОЖЬ;20%; ЕСЛИ(ЛОЖЬ;17%; ЕСЛИ(ИСТИНА;14%; ЕСЛИ(25>=10;12%;10%)))) = 25*14%.
это условие уже не анализируется
Выражение может быть весьма запутанным, в особенности, если результатом сравнения является не просто какое-нибудь число (как в нашем примере), а некоторая функция. В подобных случаях бывает удобно разбить такое выражение на ряд более простых, например, на несколько сомножимых (но при этом придется вернуться к заданию всех ограничивающих условий).
=А1*ЕСЛИ(А1<10;10%;1)*ЕСЛИ(И(А1>=10;А1<20);12%;1)*ЕСЛИ(И(А1>=20;А1<30);14%;1)*
ЕСЛИ(И(А1>=30;А1<40);17%;1)*ЕСЛИ(А1>=40;20%;1).
Хотя длина выражения и увеличилась, его структура стала проще. Нет больше путающих пользователя и трудно отслеживаемых вложений функций ЕСЛИ() со множеством закрывающихся скобок. Здесь каждое условие представлено одним совершенно автономным сомножимым, которое может иметь значение 1 (если условие ложно) или соответствующий процент, если истинно. Так, при вкладе в 25 тыс., будет получен следующий результат (единицы, которые вырабатывают ложные ЕСЛИ() не влияют на произведение).
= А1*1*1*14%*1*1 =А1*14%.
Можно решить задачу, представив функцию и рядом слагаемых:
=А1*(ЕСЛИ(А1<10;10%;0)+ ЕСЛИ(И(А1>=10;А1<20);12%;0)+ ЕСЛИ(И(А1>=20;А1<30);14%;0)+
ЕСЛИ(И(А1>=30;А1<40);17%;0)+ ЕСЛИ(А1>=40;20%;0)).
Аргумент 0 в функциях ЕСЛИ() можно опустить
=А1*(ЕСЛИ(А1<10;10%)+ЕСЛИ(И(А1>=10;А1<20);12%)+ ... )).
Для того же вклада будет получено (нули на сумму не влияют): =А1*(0+0+14%+0+0)=А1*(14%).
Таким образом, преимущество описанной технологии заключается в том, что одно сложное выражение разбивается на ряд более понятных простых, а также в том, что выражение можно вводить в клетку частями, после того как вы убедитесь в правильности функционирования ранее введенного.
| A | B | C | D | E | |
| Месяц: | |||||
| Работник | Пол | Зарплата | Детей | Премия | |
| Иван | м | ? | |||
| Ольга | ж | ? | |||
| Петр | м | ? | |||
| Рис. 4.7-4 |
Еще пример “из жизни”. Пусть нужно начислить праздничные премии или материальную помощь работникам организации. На рис. 4.7-4 показана структура данных, где на местах формул, которые нам нужно создать, пока стоят знаки “?”.
Решим пример, последовательно усложняя его условия.
1. Положим, текущий месяц март и женщинам устанавливается премия в размере 300 руб. ко дню 8 марта. Иными словами
премия=ЕСЛИ(пол=женский, то 300 руб., иначе – 0 руб).
Или в виде функции: E3=ЕСЛИ(B3="ж";300;0). Можно в обратной постановке E3=ЕСЛИ(B3="м";0;300).
2. Очевидно, такое решение будет правильным в единственном месяце – марте. Если оставить все как есть, в апреле женщинам будет снова назначена премия, чего, конечно, не должно быть. Чтобы учесть этот фактор нужно учесть значение текущего месяца (клетка В1).
премия=ЕСЛИ(пол=женский И месяц=март, то 300 руб, иначе 0 руб),
E3=ЕСЛИ(И(B3="ж";B$1=3);300;0).
Или в обратной постановке
премия=ЕСЛИ(пол=мужской ИЛИ месяц¹март, то 0 руб, иначе 300 руб),
E3=ЕСЛИ(ИЛИ(B3="м";B$1<>3);0;300).
3. Положим, что в феврале и мужчинам назначается премия.
премия=ЕСЛИ((пол=женский И месяц=март) ИЛИ (пол=мужской И месяц=февраль),то 300р, иначе 0р),
E3=ЕСЛИ(ИЛИ(И(B3="ж";B$1=3);И(B3="м";B$1=2));300;0).
В обратной постановке
E3=ЕСЛИ(И(ИЛИ(B3="м";B$1<>3);ИЛИ(B3="ж";B$1<>2));0;300).
4. Положим, что премия мужчинам и женщинам назначается по-разному: мужчинам 10% от зарплаты, женщинам – 300 руб. независимо от заработка. Поскольку теперь премия имеет три значения (0, 10% и 300 руб.), то понадобится две функции ЕСЛИ( ).
премия=ЕСЛИ(пол=женский И месяц=март, то 300 руб,
ЕСЛИ(пол=мужской И месяц=февраль,то 10% от зарплаты, иначе 0 руб)),
E3=ЕСЛИ(И(B3="ж";B$1=3);300;ЕСЛИ(И(B3="м";B$1=2);10%*С3;0))
или несколько проще, если разбить выражение на два слагаемых
премия=ЕСЛИ(пол=женский И месяц=март, то 300 руб.)
+ЕСЛИ(пол=мужской И месяц=февраль, то 10% зарплаты),
E3=ЕСЛИ(И(B3="ж";B$1=3);300)+ЕСЛИ(И(B3="м";B$1=2);10%*C3).
Такое решение делает формулу более прозрачной. Посмотрим, каков будет результат, в разных (январь, февраль, март) месяцах
I – ЕСЛИ(И(B3="ж";B$1=3);300)+ЕСЛИ(И(B3="м";B$1=2);10%*C3) =0+0=0
II – ЕСЛИ(И(B3="ж";B$1=3);300) + 0 [300р. только женщинам]
III – 0 + ЕСЛИ(И(B3="м";B$1=2);10%*C3) [10% только мужчинам].
5. Положим, что, кроме того, всем работникам назначается премия к Новому году в размере 500 руб. (представим эти величины как отдельные слагаемые).
премия=ЕСЛИ(пол=женский И месяц=март, то 300 руб)
+ЕСЛИ(пол=мужской И месяц=февраль, 10% зарплаты)+ЕСЛИ(месяц=январь,500 руб)),
E3=ЕСЛИ(И(B3="ж";B$1=3);300)+ЕСЛИ(И(B3="м";B$1=2);10%*C3)+ЕСЛИ(B$1=1;500).
6. Всем женщинам установить помощь на каждого ребенка в размере 200 руб., но так, чтобы она не превышала собственной зарплаты работника.
премия=ЕСЛИ(пол=женский; ЕСЛИ(детей*200>зарплата; зарплата; детей*200;0)).
Или проще, если выделить признак пол=женский в отдельный множитель, который может принимать одно из двух значений 1 или 0
премия=ЕСЛИ(детей*200>зарплата; зарплата; детей*200;0)*ЕСЛИ(пол=женский;1;0)
E3=ЕСЛИ(D3*200>C3;C3;D3*200)*ЕСЛИ(B3="ж";1;0).
Или даже E3=ЕСЛИ(D3*200>C3;C3;D3*200)*(B3="ж").
Можно заменить функцию ЕСЛИ( ) на функцию МИН( ). Тогда E3=МИН(D3*200;C3)*(B3="ж").
7. Назначить помощь на детей: на первого ребенка 100 руб., на второго – 150 руб., на третьего – 250 руб., на следующих детей помощь не увеличивается
премия=ЕСЛИ(детей=1;100; ЕСЛИ(детей=2;100+150; ЕСЛИ(детей>=3;100+150+250;0)))
E3=ЕСЛИ(D3=1;100; ЕСЛИ(D3=2;250; ЕСЛИ(D3>=3;500;0))).
Как видно из предыдущего, одно и тоже условие часто может быть сформулировано разными способами.
Решите следующие задачи самостоятельно.
8. Выдать в марте премию в 500 руб. женщинам, получающим зарплату до 1000 руб.
9. Выдать к 1 мая премию в 500 руб. всем работникам, получающим зарплату до 1000 руб., а остальным – в размере 20% от зарплаты.
10. То же, но премия не должна быть не более 800 руб.
| № | Этаж | Двор | Евро |
| + | |||
| + | |||
| + | + | ||
| + | |||
| + | |||
| + | + | ||
| другие этажи | + | ||
| + | |||
| + | + | ||
| Рис. 4.7-5 |
Пример. Положим, вы представляете строительную фирму, которой требуется продать квартиры в построенном доме. Средняя цена 1 кв. метра площади известна, но его цена в каждой конкретной квартире может быть различной в зависимости от ее потребительских качеств (этажа, направления окон, уровня отделки). Фирмой установлено, что покупателям квартир на 1 этаже делается скидка в 20%, а жильцам последнего 8-го – 10%. Если окна квартиры выходят во двор – вводится наценка в 15%. Если она отделана в соответствии с евростандартами – наценка в 40%. Очевидно, что в доме возможна любая комбинация этих параметров и всего их 12 (рис. 4.7-5). Если подойти к решению задачи непосредственно (обозначим перечисленные параметры словами Этаж, Двор, Евро, а наличие последних двух – знаком ”+”), нам для вычисления продажной цены одного метра площади придется построить грандиозное выражение, закрывающееся 11 скобками
продажная_цена_1_кв.м=средняя_цена_1кв.м*
ЕСЛИ(И(Этаж=1;Двор¹”+”;Евро¹”+”) то 1-20%; иначе
ЕСЛИ(И(Этаж=1;Двор¹”+”;Евро=”+”) то 1-20%+40%; иначе
ЕСЛИ(И(Этаж=1;Двор=”+”;Евро¹”+”) то 1-20%+15%; иначе
ЕСЛИ(И(Этаж=1;Двор=”+”;Евро=”+”) то 1-20%+15%+40%;иначе
ЕСЛИ(И(Этаж=8;Двор¹”+”;Евро¹”+”) то 1-10%; иначе
ЕСЛИ(И(Этаж=8;Двор¹”+”;Евро=”+”) то 1-10%+40%; иначе
ЕСЛИ(И(Этаж=8;Двор=”+”;Евро¹”+”) то 1-10%+15%; иначе
ЕСЛИ(И(Этаж=8;Двор=”+”;Евро=”+”) то 1-10%+15%+40%;иначе
ЕСЛИ(И(Двор¹”+”;Евро=”+”) то 1+40%; иначе
ЕСЛИ(И(Двор=”+”;Евро¹”+”) то 1+15%; иначе
ЕСЛИ(И(Двор=”+”;Евро=”+”) то 1+15%+40%; иначе 1))))))))))).
Замечание. Последняя группа из четырех условий не содержит указаний на этаж, поскольку предыдущие условия исключают возможность “пропуска” в эту часть выражения 1-го и 8-го этажей.
Видим, что функция получилась необозримых размеров. Но главное, – число уровней вложения функций ЕСЛИ() не может превышать семи, т.е. такое выражение просто не удастся ввести в клетку таблицы. Упрощение возможно, если учитывать влияющие факторы отдельно, как слагаемые к 100% средней цены одного метра
продажная_цена_1м = средняя_цена_1м* (1+ЕСЛИ(Этаж=1, то -20%, иначе 0)
+ЕСЛИ(Двор=”+”, то 15%, иначе 0) +ЕСЛИ(Евро=”+”, то 40%, иначе 0) +ЕСЛИ(Этаж=8, то -10%, иначе 0)).
Положим, покупается квартира на 8-м этаже с евроотделкой и окнами во двор. Тогда мы получим
=средняя_цена_1м*(1+0+15%+40%-10%)
или =средняя_цена_1м*(1+45%)=средняя_цена_1м*145%.
Если предположить, что сведения о влияющих параметрах данной квартиры расположены в клетках С4, D4, E4 соответственно, а средняя цена – в А2, то можем записать выражение как формулу Excel совсем просто
=А2*(1+ЕСЛИ(С4=1;-20%)+ЕСЛИ(D4=”+”;15%)+ЕСЛИ(E4=”+”;40%)+ЕСЛИ(С4=8;-10%)).
Пример. Положим, некоторым категориям жильцов положена скидка при оплате квартплаты: инвалидам – в 70%, участникам войны – 50%, многодетным – 25%. Или в содержательной форме
квартплата=номинальная_квартплата*(1-ЕСЛИ(Инвалид, то 70%, иначе 0)-
ЕСЛИ(Участник, то 50%, иначе 0)- ЕСЛИ(Многодетный, то 25%, иначе 0)).
Пусть, сведения о номинальной квартплате находятся в клетке А1, а информация об инвалидности, участии и многодетности – в С3, D3, E3. Тогда можем построить формулу Excel
=А1*(1-ЕСЛИ(С3=”и”;70%)-ЕСЛИ(D3=”у”;50%)-ЕСЛИ(E3=”м”;25%).
Буквами “и”, “у”, “м” обозначены соответствующие признаки.
Здесь обнаруживаем, что общая скидка может достигать величины более 100% (до 145%), если наблюдаются все льготы, что, конечно, недопустимо. Наша функция должна быть усложнена так, чтобы ограничить льготы до 100%
=А1*(1-МИН(ЕСЛИ(С3=”и”;70%)+ЕСЛИ(E3=”у”;50%)+ЕСЛИ(D3=”м”;25%); 100%)).
Здесь от 1 (от 100%) отнимается минимальное значение из суммы всех льгот и 100%. Таким образом, если сумма льгот больше 100%, размер квартплаты будет =А1*(1-100%)=0, в противном случае =А1*(1-сумма льгот в процентах).
Возможен и другой подход. При наличии нескольких льгот учитывается только одна, но самая большая
=А1*(1-МАКС(ЕСЛИ(С3=”и”;70%);ЕСЛИ(E3=”у”;50%);ЕСЛИ(D3=”м”;25%)).
Иными словами: =А1*(1-максимальная из льгот).
Замечание. Функции МАКС( ) и МИН( ) будут рассмотрены позже.
Функции выбора и поиска
ü ВЫБОР(номер; значение1; значение2; ...) – возвращает значение из списка значений с заданным номером от 1 до 29. Примеры:
ВЫБОР(2;"Апрель";"Май";"Июнь")="Май",
ВЫБОР(5;1;4;7;2)=#ЗНАЧ! – ошибка, поскольку искомый номер 5 больше числа элементов в списке (4).
В качестве номера и значений можно использовать ссылки на ячейки, например ВЫБОР(B8;С3;C4;C5).
ü ПОИСКПОЗ(искомое значение; область поиска[; тип поиска])
– осуществляет поиск позиции (клетки) в области поиска, значение данных в которой близко искомому значению. Результатом является не само найденное значение (оно нам известно), а его номер в области поиска. Искомое значение и область могут быть любого возможного типа. Понятие близости определяется значением параметра тип поиска. Он может иметь одно из трех значений:
0 – соответствие должно быть точным (полное совпадение).
1 – ищется наибольшее значение, которое меньше или равно (если есть) искомому. Область поиска должна быть упорядочена по возрастанию. Если параметр не указан, он считается равным 1.
| A | B | C | D | E | F | |
| Рис. 4.8-1 |
-1 – ищется наименьшее значение, которое больше или равно искомому. Область поиска должна быть упорядочена по убыванию.
Примеры. Пусть, анализируемая таблица изображенное на рис. 4.8-1.
Тогда следующие функции выдадут результаты:
ПОИСКПОЗ(7;B2:F2;0)=4 клетка Е2 (четвертая в области),
ПОИСКПОЗ(10;B2:F2;0)=#Н/Д не найдено,
ПОИСКПОЗ(20;B3:F3;1)=2 клетка С3 (вторая в области),
ПОИСКПОЗ(20;B4:F4;-1)=3 клетка D4 (третья в области).
Найденные клетки выделены жирной рамкой.
ü ГПР(искомое значение; область поиска; номер строки извлечения [;тип поиска])
– осуществляет поиск искомого значения в самой верхней строке области поиска. Эту строку будем называть ключевой строкой. Результат извлекается из строки с заданным номером относительно ключевой строки из области поиска. Номер ключевой строки блока поиска – всегда 1 (этот номер не имеет никакого отношения к фактическим номерам строк в рабочих листах книги).
Таким образом, в несколько схематичной форме функция может быть описана следующим образом