Электронная таблица Excel. Встроенные функции. (Часть 1)

1. Электронная таблица Excel встроенные функции. Часть 1

1
ЭЛЕКТРОННАЯ ТАБЛИЦА
EXCEL
ВСТРОЕННЫЕ ФУНКЦИИ. ЧАСТЬ 1
Встроенные функции в Excel

2. Постановка задачи

2
Подготовить таблицу для определения
среднего балла и стипендии для студентов
по результатам экзаменационной сессии.
Исходными данными для расчета являются:
• фамилия студента,
• курс,
• оценки.

3. Постановка задачи

3
Характеристика данных:
Количество экзаменов на курсе определяется по числу
непустых клеток в строке для экзаменационных оценок.
Если студент не аттестован по предмету, то клетка
содержит текст н/а.

4. Постановка задачи

4
Условие расчета среднего балла:
Средний балл определяется только для тех студентов,
которые получили оценки по всем экзаменационным
дисциплинам.
Условие назначения и размер стипендии:
Стипендия устанавливается студенту в том случае, если по
всем экзаменационным дисциплинам получены оценки
не ниже 4.
Студент, получивший одни «пятерки» является «отличником», получивший хотя бы одну 4 – «хорошистом».
Установлен свой размер стипендии для «отличника» и
«хорошиста».

5. Таблица с данными для расчета

5

6. Расчет среднего балла

6
=СРЗНАЧ(C4:F4)

7. Расчет среднего балла

7
=ЕСЛИ(СЧЕТ(C4:F4)=СЧЕТЗ(C4:F4);СРЗНАЧ(C4:F4);””)

8. Расчет среднего балла

8

9. =ЕСЛИ(G4=5;K4;ЕСЛИ(И(G4<>"";МИН(C4:F4)=4);K5;""))

Расчет стипендии
9
=ЕСЛИ(G4=5;K4;ЕСЛИ(И(G4<>"";МИН(C4:F4)=4);K5;""))

10. Расчет стипендии

10
=ЕСЛИ(G4=5;$K$4;ЕСЛИ(И(G4<>"";МИН(C4:F4)=4);$K$5;""))

11. Расчет стипендиального фонда

11
=СУММ(H4:H10)

12. Встроенные функции

12
Функция
Назначение
МАКС(диапазон)
Возвращает наименьшее значение
среди числовых значений в ячейках
указанного диапазона
МИН(диапазон)
Возвращает наименьшее значение
среди числовых значений в ячейках
указанного диапазона
Возвращает среднее
СРЗНАЧ(диапазон) арифметическое числовых значений
из ячеек диапазона

13. Встроенные функции

13
Функция
Назначение
МАКС(диапазон)
Возвращает наибольшее значение
среди числовых значений в ячейках
указанного диапазона
МИН(диапазон)
Возвращает наименьшее значение
среди числовых значений в ячейках
указанного диапазона
Возвращает среднее
СРЗНАЧ(диапазон) арифметическое числовых значений
из ячеек диапазона

14. Встроенные функции

14
Функция
СЧЕТ(диапазон)
СЧЕТЗ(диапазон)
СЧЕТЕСЛИ(диапазон;
критерий)
Назначение
Подсчитывает количество ячеек в
диапазоне, которые содержат
числовые значения
Подсчитывает количество непустых
ячеек в диапазоне
Подсчитывает количество ячеек в
диапазон, содержимое которых
удовлетворяет заданному критерию

15. Встроенные функции

15
Функция
Назначение
СЧИТАТЬПУСТОТЫ
(диапазон)
Подсчитывает количество пустых
ячеек в заданном диапазоне
СУММ(диапазон)
Возвращает сумму числовых
значений из ячеек диапазона
Возвращает суммарное значение
числовых ячеек диапазона2, если
СУММЕСЛИ(диапазон1;
соответствующая ячейка
критерий;дипазон2)
диапазона1 удовлетворяет
указанному критерию

16. Встроенные функции

16
Функция
ЕПУСТО(Ссылка)
ЕЧИСЛО(Значение)
ЕТЕКСТ(Значение)
ЕОШИБКА(Значение)
Назначение
Возвращает логическое значение
ИСТИНА, если клетка, определяемая
ссылкой, является пустой, иначе ЛОЖЬ
Возвращает значение ИСТИНА, если
аргумент принимает числовое значение,
иначе ЛОЖЬ
Возвращает значение ИСТИНА, если
аргумент принимает текстовое
значение, иначе ЛОЖЬ
Возвращает значение ИСТИНА, если
аргумент принимает ошибочное
значение #Н/Д!, #ЗНАЧ!, #ССЫЛКА!,
#ДЕЛ/0 и др., иначе ЛОЖЬ

17. Пример 1

17
В электронной таблице,
фрагмент которой
приведен на рисунке,
собраны данные о средней
дневной температуре и
количестве выпавших
осадков в Петрозаводске в
июле 1995 года.
Данные занимают строки
таблицы с 4-й по 34-ю.
На рисунке показаны
только 5 строк с данными.

18. Пример 1.1

18
Общее количество осадков, выпавших в июле:
=СУММ(C4:C34)

19. Пример 1.2

19
Среднемесячную температуру :
=СРЗНАЧ(C4:C34)

20. Пример 1.3

20
Минимальная и максимальная дневная
температура:
=МИН(B4:B34) и
=МАКС(B4:B34)

21. Пример 1.4

21
Сколько дней в июле имели среднюю
дневную температуру выше 20оС:
=СЧЕТЕСЛИ(B4:B34;”>20”)

22. Пример 1.5

22
Сколько дней было без дождей:
=СЧЕТЕСЛИ(C4:C34;”=0”)

23. Пример 2

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

24. Пример 2.1

24
Сколько реализовано карт типа
«GNT-807»?
=СУММЕСЛИ(B3:B14;”GNT-807”;
C3:C14)

25. Пример 2.2

25
Сколько сделано записей в
журнале о продаже таксофонных
карт типа «TMC151K3»?
=СЧЕТЕСЛИ(B3:B14;”TMC151K3”)

26. Пример 3

26
С помощью ЭТ, фрагмент которой приведен на рисунке, ведется учет
выполнения лабораторных работ по информатике. В каждой строке клетки
колонок C:G заполняются следующим образом: если работа выполнена
полностью, то в клетку вводится количество баллов; если работу необходимо
исправить, то вводится символ «и»; если работа не сдана, то клетка остается
пустой.

27. Пример 3.1

27
Количество сданных работ (содержимое клетки H3)
определяется по формуле:
=СЧЕТ(C3:G3)

28. Пример 3.2

28
Количество работ, которое необходимо исправить
(содержимое клетки I3):
=СЧЕТЗ(C3:G3)-H3

29. Пример 3.3

29
Количество несданных работ (содержимое клетки J3):
=СЧИТАТЬПУСТОТЫ(C3:G3)

30. Пример 3.4

30
Заработанное количество баллов (содержимое клетки K3):
=СУММ(C3:G3)