Microsoft Excel. От основ к задачам обработки и представления данных (стр. 23 )

Примеры.

1.  В следующем примере, если значение ячейки A10 равно 100, то условие выполняется, т. е. имеет значение ИСТИНА и вычисляется сумма для ячеек B5:B15. В противном случае условие не выполняется, т. е. имеет значение ЛОЖЬ и возвращается пустой текст (""), очищающий ячейку, которая содержит функцию ЕСЛИ.

=ЕСЛИ(A10=100;СУММ(B5:B15);"")

2.  Предположим, что рабочий лист по расходам содержит в ячейках B2:B4 фактические расходы за январь, февраль, март: 1500, 500 и 500 соответственно. Ячейки C2:C4 содержат данные по предполагаемым расходам за те же периоды: 900, 900 и 925. Можно написать формулу для проверки соответствия бюджету расходов определенного месяца, генерируя тексты сообщений с помощью следующих формул:

=ЕСЛИ(B2>C2;"Превышение бюджета";"")

условие выполняется, значение в ячейке с условием равняется "Превышение бюджета";

=ЕСЛИ(B3>C3;"Превышение бюджета";"")

условие не выполняется, значение в ячейке с условием равняется "".

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

В нашем случае для диагностирования двух категорий студентов необходимо проверить количество двоек (если оно не равно нулю, то студент двоечник) и количество пятерок (если оно равно количеству предметов, т. е. 12, то студент отличник). В ячейке AS17 записана соответствующая формула:

=ЕСЛИ(AO17<>0;"двоечник";ЕСЛИ(AR17=12;"отличник";""))

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

=ЕСЛИ(AO17<>0;"двоечник";

ЕСЛИ(AR17=12;"отличник";

ЕСЛИ(AP17<>0;"троечник","хорошист"))).

Замечание.

1.  Необходимо строго следить за числом скобок в формулах: число открытых скобок должно равняться числу закрытых.

2.  Рекомендуется рисовать блок-схему условий на бумаге для более эффективной записи условий и самоконтроля.

Для нашего последнего условия блок-схема имеет вид (рис. 83).

Рис. 83. Блок-схема условия.

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

Задание № 30. Замените в таблице формулу в столбце AS, так чтобы она определяла все категории студентов (двоечник, троечник, хорошист и отличник). Формула, приведенная в таблице, определяет только две категории: двоечник и отличник.

8.2.5. Определение стипендии студента

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

Особенность, которая характерная для многих задач заключается в следующем: если Вы в качестве аргумента функции ЕСЛИ укажете число (реальный размер стипендии), то при изменении этого числа Вам придется переписать формулу и снова ее скопировать во все ячейки, где использовалось это число. Для того чтобы этого не происходило, указывайте не числа, а ссылки на ячейки, где эти числа хранятся, тогда при изменении значения этих ячеек изменятся и значения формул. Конечно, ссылки должны быть абсолютными.

Напоминание. Абсолютной ссылкой на ячейку называют ссылку на данную ячейку независимо от положения формулы,

например, $A$10, A$5, $B12.

В столбце AT записана следующая формула напротив каждого студента:

=ЕСЛИ(AS17="двоечник";"";ЕСЛИ(AS17="отличник";$AK$10;$AT$10))

В ячейках AK10 и AT10 хранятся суммы стипендий для отличников и для троечников-хорошистов соответственно. Изменив значения этих ячеек, Вы увидите, как изменятся значения стипендий в ячейках столбца AT.

Задание № 31. Замените в таблице формулу в столбце AT, так чтобы она определяла стипендии для всех категорий студентов (двоечников – без стипендии, троечников – минимальная, хорошистов – повышенная и отличников – именная). Формула, приведенная в таблице, определяет только две стипендии – для двоечников и отличников.

Подсказка. Отведите какие-нибудь не занятые ячейки таблицы для хранения размера одной стипендии для троечников, хорошистов и отличников. Занесите размер стипендий в эти ячейки (назначьте сами). Формула определения стипендий будет строиться также как формула определения категории студентов, но на базе формулы приведенной выше для назначения стипендий отличникам и двоечникам.

8.2.6. Определение стипендиального фонда

Для определения общего стипендиального фонда необходимо сложить все значения стипендий в диапазоне AT17:AT316. Для определения стипендиального фонда отличников необходимо сложить стипендии отличников. Это можно осуществить с помощью функции условного суммирования СУММЕСЛИ. У этой функции три параметра: интервал; критерий; суммируемый интервал.

Интервал – это интервал вычисляемых ячеек (для них проверяется критерий).

Критерий – это критерий в форме числа, выражения или текста, который определяет, какая ячейка добавляется. Например, критерий может быть выражен как 32, "32", ">32", "яблоки".

Суммируемый интервал – диапазон, в котором производится суммирование. Если не задан, то суммируются ячейки определенные параметром "интервал". Т. е. суммироваться одни ячейки, а проверяться на удовлетворение параметра "критерий" другие.

Формула вида: =СУММЕСЛИ($AT$17:$AT$316;$AT$10) суммирует ячейки, находящиеся в столбце AT, при условии, что они равны (по значению) ячейке AT10, т. е. в нашем случае размеру стипендии троечника-хорошиста. Абсолютные ссылки не позволяют измениться формуле при копировании.

Аналогично можно подсчитать сумму стипендиального фонда отличников, только необходимо изменить адрес ячейки, содержащей размер одной стипендии отличника (в таблице это ячейка AK10).

8.2.7. Вычисление процентных отношений

Вычисление процентного отношения Вам знакомо еще со школы и каких-либо трудностей не представляет. Формулы, вычисляющие процент, просты и Вы сможете изучить их реализацию в Excel самостоятельно. Единственной особенностью является то, что умножение на 100% в Excel не нужно. Достаточно указать процентный формат ячеек. Это можно выполнить через меню «Формат» пункт «Ячейки», вкладку «Число». Либо вызвав контекстное меню правой кнопкой мыши и командой «Формат ячеек», либо через панель инструментов «Форматирование» нажав на кнопку «Процентный формат» .

Замечание. Одной из особенностей вычисления процентов является то, что при вычислении процентов для округленных чисел (или при округлении процентов) может получиться ситуация, что сумма процентов не будет равняться 100 %, а будет отличаться в последнем знаке (в меньшую сторону).

8.2.8. Вычисление среднего балла

по предметам и определение максимального из них

Средний балл по предметам находится суммированием всех оценок в соответствующем столбце и делением их на число студентов. Формула, определяющая средний балл по математике (первому предмету), имеет вид: =СУММ(AB$16:AB$317)/ЧСТРОК($AB$17:$AB$316).

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

Для определения максимального балла из средних по предметам можно использовать функцию МАКС: =МАКС(AW37:AW48).

Диапазон ячеек с AW37 по AW48 содержит средние баллы по предметам. Определение же названия предмета немного сложнее.

="П"&ТЕКСТ(ПОИСКПОЗ(МАКС(AW37:AW48);AW37:AW48;0);"0")

Функция ПОИСКПОЗ возвращает относительное положение элемента массива, который соответствует заданному значению указанным образом. С помощью данной функции можно найти положение максимального среднего балла в диапазоне предметов. Функция записывается следующим образом: ПОИСКПОЗ (искомое значение; просматриваемый массив; тип сопоставления).

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

Просматриваемый массив – это непрерывный интервал ячеек, возможно, содержащих искомые значения. Параметр "просматриваемый массив" может быть массивом или ссылкой на массив.

Тип сопоставления – это число –1, 0 или 1. Тип сопоставления указывает, как Microsoft Excel сопоставляет параметр "искомое значение" со значениями в аргументе "просматриваемый массив".