Формат записи:
=ЕСЛИ(усл.#1;выражениеВ;ЕСЛИ(усл.#2;выражениеС;ЕСЛИ(…)))
Число вложенных функций в принципе может быть любое, но общая длина строки не должна быть слишком большой, это затрудняет ее чтение; кроме того, необходимо следить за тем, чтобы число открытых скобок в точности равнялось числу закрытых скобок.
Найти работников, у которых имеются одновременно задолженности по обоим видам кредита, и удержать от начисленной им суммы 20% в счет погашения кредитов (рис. 62). С остальных работников, имеющих задолженность по какому-либо одному виду кредита, удержать 10% от начисленной им суммы. Работникам, не имеющим задолженности по кредиту, проставить в графе 2 «Удержано» – «б/к».
В нашем примере логическая функция будет иметь такой вид:
=ЕСЛИ(И(C3>0;D3>0);B3*0,2;ЕСЛИ(И(C3=0;D3=0);"б/к";B3*0,1))
Данная логическая функция означает следующее: если одновременно задолженности по потребительскому кредиту и кредиту на жилищное строительство больше нуля, то необходимо удержать 20% с начисленной суммы, если обе задолженности одновременно равны нулю, то необходимо вывести «б/к», в противном случае необходимо удержать 10% от начисленной суммы.
Запишем ее в ячейку Е3, а затем скопируем в ячейки Е4:Е7 (см. рис. 62).
A | B | C | D | E | |
Ф.И.О | Начислено, тыс. руб. | Задолженность по видам кредита | Удержано, тыс. руб. | ||
Потребитель. кредит | Жилищ. строит. | ||||
Иванов | =ЕСЛИ(И(C3>0;D3>0);B3*0,2;ЕСЛИ(И(C3=0;D3=0);"б/к";B3*0,1)) | ||||
Петров | =ЕСЛИ(И(C4>0;D4>0);B4*0,2;ЕСЛИ(И(C4=0;D4=0);"б/к";B4*0,1)) | ||||
Кузьмин | =ЕСЛИ(И(C5>0;D5>0);B5*0,2;ЕСЛИ(И(C5=0;D5=0);"б/к";B5*0,1)) | ||||
Сухов | =ЕСЛИ(И(C6>0;D6>0);B6*0,2;ЕСЛИ(И(C6=0;D6=0);"б/к";B6*0,1)) | ||||
Рысьев | =ЕСЛИ(И(C7>0;D7>0);B7*0,2;ЕСЛИ(И(C7=0;D7=0);"б/к";B7*0,1)) |
Рис. 62. Вложенная логическая функция ЕСЛИ
В столбцах C и D (рис. 63) будут найдены работники, у которых есть задолженности по двум видам кредита. С них будет удержано 20% от начисленных им сумм. Напротив фамилии работника, у которого нет задолженности по кредиту, в столбце Е будет выведено «б/к». Наконец, с остальных работников будет удержано 10% от начисленных им сумм.
A | B | C | D | E | |
Ф.И.О | Начислено, тыс. руб. | Задолженность по видам кредита | Удержано, тыс. руб. | ||
Потребительский кредит | Жилищное строительство | ||||
Иванов | |||||
Петров | б/к | ||||
Кузьмин | |||||
Сухов | |||||
Рысьев |
Рис. 63. Результат выполнения вложенной логической функции ЕСЛИ
Задание 28. Функции СЧЕТ, СЧЕТЗ, СЧЕТЕСЛИ
Статистическая функция =СЧЕТ(диапазон) подсчитывает, сколько чисел в диапазоне – количество чисел (числовых ячеек) в интервалах и массивах. Считает только ячейки с числами без пустых и текстовых данных.
Статистическая функция =СЧЕТЗ(диапазон) ведет счет заполненных ячеек в диапазоне с числами или текстом, но не пустых (в названии функции буква З, не цифра 3).
Статистическая функция =СЧЕТЕСЛИ(диапазон;критерий) считает, сколько раз в диапазоне ячеек встречается значение, удовлетворяющее критерию.
По таблице 16 функция =СЧЁТ(B2:I2;B4:I4) в диапазоне ячеек с числовыми данными о наличии детей вычислит, сколько женщин-заключенных имеют детей и поэтому подлежат амнистии (9 человек).
Функция =СЧЁТЗ(B1:I1;B3:I3) подсчитает общее число женщин (16 женщин).
Функция =СЧЁТЕСЛИ($B$1:$I$4;">1") подсчитывает количество женщин имеющих более одного ребенка (критерий – неравенство в кавычках) (7 женщин).
=СЧЁТ(B2:I2;B4:I4) = 9;
=СЧЁТЗ(B1:I1;B3:I3) = 16;
=СЧЁТЕСЛИ($B$1:$I$4;">1") = 7.
Таблица 16
Данные о женщинах-заключенных
A | B | C | D | E | F | G | H | I | |
Фамилия | Андреева | Беляева | Воронова | Гараева | Долгова | Еговкина | Зверева | Исакова | |
Кол-во детей | |||||||||
Фамилия | Килина | Кашина | Ласко | Нелина | Олина | Пушина | Соколова | Серова | |
Кол-во детей | Беременность, 7 месяцев |
Задание 29. Результаты экзамена
Подсчитайте результаты экзамена.
A | B | C | D | E | |
Экзаменационная ведомость по группе 1 | |||||
Группа 1 | |||||
№ п.п | ФИО студента | Номер зачетной книжки | Оценка | ||
цифра | прописью | ||||
Белых М. | хорошо | ||||
Возмищева Л. | хорошо | ||||
Гилязов Л. | удовл | ||||
Горшкова Т. | удовл | ||||
Дьяченко М. | отлично | ||||
Закиров А. | удовл | ||||
Иванова Н. | отлично | ||||
Климин Д. | неуд | ||||
Королева Е. | хорошо | ||||
Косаренкова А. | отлично | ||||
Кузнецов А. | отлично | ||||
Лелькина А. | отлично | ||||
Малиновский Ю. | хорошо | ||||
Мартынов А. | неявка | ||||
Результаты экзамена | |||||
Должны сдавать | |||||
Отлично | =СЧЁТЕСЛИ(D7:D19;"5") | ||||
Хорошо | =СЧЁТЕСЛИ(D6:D19;"4") | ||||
Удовлетворительно | =СЧЁТЕСЛИ(D6:D19;"3") | ||||
Неудовлетворительно | =СЧЁТЕСЛИ(D6:D19;"2") | ||||
Средний балл | =СРЗНАЧ(D6:D19) | ||||
Неявка | =СЧИТАТЬПУСТОТЫ(D6:D19) |