Пример3. Определить стипендиальный фонд студентов со средним баллом больше 6.
=СУММЕСЛИ (D2:D20;">6";E2:E20)
Можно поместить значение среднего балла в ячейку C22 и сделать на нее ссылку:
=СУММЕСЛИ (D2:D20;">"& С22;E2:E20)
В тех случаях, когда условие должно быть сложным, необходимо использовать диапазоны для дополнительных вычислений и логические функции.
Если проверять условие и суммировать значения нужно в одном и том же диапазоне, то диапазон суммирования не указывается.
Пример4. Определить стипендиальный фонд студентов, у которых стипендия больше 100000.
=СУММЕСЛИ (Е2:Е20;">100000")
3.2. Статистические функции
Функция СРЗНАЧ вычисляет среднее арифметическое числовых значений среди аргументов. Имеет следующий синтаксис:
=СРЗНАЧ (число1;число2;...).
Пустые, логические, текстовые значения в ячейках игнорируются.
Функция может иметь до 30 аргументов.
Функции МАКС и МИН возвращают наибольшее и наименьшее значение в списке аргументов. Синтаксис функций:
= МАКС (число1; число2;...)
= МИН (число1; число2;...)
Пример5. Определить средний, максимальный и минимальный балл всех студентов.
= СРЗНАЧ (D2:D20)
= МАКС (D2:D20)
= МИН (D2:D20)
Функции СЧЕТ и СЧЕТЗ определяют количество ячеек, содержащих числа (включая даты) и, соответственно, количество непустых ячеек в диапазоне, заданном аргументами. Функции имеют следующий синтаксис:
=СЧЕТ(значение1; значепие2;...);
=СЧЕТЗ(значенис1; значение2;...)Пример6. Функции и возвращаемые значения:
=СЧЕТ (А2:А20) 0 =СЧЕТЗ (А2:А100) 19
=СЧЕТ(D2:D20) 19 =СЧЕТЗ (D2:D100) 19
Функция СЧЕТЕСЛИ определяет количество ячеек в диапазоне, значения в которых удовлетворяют заданному условию.
Имеет следующий синтаксис:
= СЧЕТЕСЛИ (диапазон; условие).
Пример7. Определить количество отличников.
=СЧЕТЕСЛИ (D2:D20;”>=9”).
При записи условия используются те же правила и ограничения, что и у математической функции СУММЕСЛИ.
Пример8. Определить средний балл в группе УП-11.
= СУММЕСЛИ (В2:В20;"УП-11";E2:E20)/ СЧЕТЕСЛИ (В2:В20;"УП-11")
3.3. Функции даты и времени
Eхсеl хранит даты и время как числовые значения. Дата - это число дней прошедших с I января 1900 года, время - часть суток между 12:00 ночи и заданным временем. Представление в ячейке даты и времени зависит от формата ячейки. Максимальная дата в Ехсеl 31 декабря 9999 года представляется числом 2958465. В режиме отображения формул дата в ячейке отображается как числовое значение.
Функция СЕГОДНЯ() не имеет аргументов и возвращает значение текущей системной даты.
Функции ГОД, МЕСЯЦ, ДЕНЬ возвращают, соответственно, год, месяц и день для заданной аргументом даты.
Функции имеют следующий синтаксис:
=ГОД (дата);
=МЕСЯЦ (дата);
=ДЕНЬ (дата).
Аргумент дата может быть десятичным значением даты; ссылкой на ячейку с датой или текстом даты, заключенным в кавычки.
Например, если в ячейке В1 записана дата 24 декабря 1996 года, то все функции
=ДЕНЬ(В1), =ДЕНЬ ("24-дек-96"), =ДЕНЬ(35423) возвращают значение 24.
3.4. Логические функции
В большинстве логических функций для записи условий используются логические выражения. Логические выражения могут содержать числа, ссылки ни ячейки, функции, формулы, текстовые и логические значения и обязательно включают по крайней мере один оператор сравнения(= ; <; >=; <= ; <>).
Результатом вычисления логического выражения является логическое значение ИСТИНА или логическое значение ЛОЖЬ.
Можно использовать следующие логические выражения:
А1>А2; СРЗНАЧ(В1:В5)=СУММ(С1,100); С2<>"Премировать"; СЧЕТ(А1:А10)<=8.
Для создания более сложных логических выражений используются логические функции И, ИЛИ, НЕ, аргументами которых являются логические выражения.
Функция И возвращает значение ИСТИНА, если все ее аргументы имеют значение ИСТИНА, и ЛОЖЬ, если хотя бы один из аргументов имеет значение ЛОЖЬ. Имеет следующий синтаксис:
=И (логическое выражение1; логическое выражение2,..). Может иметь до 30 аргументов.
Пример 9. Определить количество отличников в группе УА-11.
В ячейку F2 запишем формулу =И (В2=”УА-11” ; D2>=9) и скопируем в ячейки F3:F20. Для всех отличников УА-11 в столбце F появится значение ИСТИНА, для всех остальных студентов значение ЛОЖЬ.
Для подсчета значений ИСТИНА (количество отличников в группе УА-11):
=СЧЁТЕСЛИ (F2:F20; ИСТИНА)
Функция ИЛИ возвращает значение ИСТИНА, если хотя бы один из ее аргументов имеет значение ИСТИНА, и ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ. Имеет следующий синтаксис:
=ИЛИ (логическое выражение1; логическое выражение2,..). Может иметь до 30 аргументов.
Пример 10. Определить общее количество студентов в группах УА-11 и
Уважаемый посетитель!
Чтобы распечатать файл, скачайте его (в формате Word).
Ссылка на скачивание - внизу страницы.