Лабораторная работа 2.
Темы работы.
Мастер функций.
Фильтры
Мастер функций.
На строке формул есть небольшая кнопка fx, с помощью которой можно запустить мастер функций – список всевозможных функций, разделённых по категориям, которые можно использовать для сложных вычислений.
Упражнение 6. «Результаты вступительных экзаменов».
Задание. Составить таблицу в соответствии с рис. 27. Определить вторичные данные и вычислить их с помощью мастера функций.
Рис. 27
Порядок выполнения.
Первичными данными являются оценки по предметам. Вторичными являются данные в столбцах «Средний балл», «Принят, не принят».
Значения в столбце «Средний балл» и в строке «Средний балл по предмету» можно подсчитывать с помощью мастера функций , используя функцию СРЗНАЧ категории «статистические», или выбрать из списка итоговых функций на панели «Стандартная» (рис. 28).
Рис.28
Анализ «Принят, не принят» сначала выполнить без учёта двоек, только по среднему баллу. Для этого нужно вызвать мастер функций (fx), выбрать категорию «логические», функцию ЕСЛИ, и заполнить бланк функции выбора (Visual Basic). Условие выбора - средний балл больше или равен 3. Кавычки не вносить – они добавляются автоматически (рис. 29).
Рис.29
Создать анализ «Принят ,не принят с учётом двоек», чтобы двоечники не были приняты. Для этого нужно усложнить конструкцию выражения, добавив ещё одно условие – минимум оценок должен быть больше 2.
Последовательность заполнения бланка следующая:
внести слова «Принят» и «Не принят». Кавычки не писать – они добавляются в бланк автоматически.
установить курсор в строке «Лог_выражение». В этой строке нужно сформировать условие выбора. В эту строку нужно вложить функцию «И». Для этого нужно развернуть панель дополнительных вложений, появляющуюся в левой области строки формул (на месте поля имени), найти в списке функцию «И», если её нет, то выбрать «другие функции», и найти её в категории «логические». В первой строке сформировать первое условие (Средний балл>3) (рис. 30):
Рис.30
во вторую строку нужно вложить функцию «МИН» области оценок. Для этого установить курсор во второй строке и, с помощью панели дополнительных вложений на слева в строке формул найти функцию МИН категории СТАТИСТИЧЕСКИЕ. Установить курсор в строке «Число1» и маркёром выделения описать область С4-:F4 (Рис.31).
Рис.31
при необходимости скорректировать расстановку скобок в строке формул, чтобы выражение приобрело вид как на рис. 32.
Рис.32.
Для выявления наиболее частой оценки нужно воспользоваться функцией «МОДА» из категории «статистические».
Для подсчёта количества принятых подойдёт функция «СЧЁТЕСЛИ» категории «статистические». Эта функция подсчитывает количество определённых значений в указанной области. Бланк функции изображен на рис. 33. Кавычки в строке «Критерий» следует вносить с клавиатуры вручную, в отличие о заполнения бланка функции «ЕСЛИ».
Рис.33
Самостоятельная работа 8.
Задание. Составить таблицу корней квадратного уравнения ( Табл. 5).
Таблица 5.
|
A
|
B
|
С
|
D
|
E
|
F
|
1
|
|
|
Корни уравнения вида aX2+bx+c=0
|
|
2
|
a
|
b
|
c
|
Дискриминант
|
x1
|
x2
|
3
|
1
|
3
|
0
|
9
|
0
|
-3
|
4
|
2
|
6
|
3
|
12
|
-0,63
|
-2,37
|
5
|
3
|
9
|
6
|
9
|
-1
|
-2
|
6
|
4
|
12
|
9
|
0
|
-1,5
|
-1,5
|
7
|
5
|
15
|
12
|
-15
|
решения нет
|
решения нет
|
8
|
6
|
18
|
15
|
-36
|
решения нет
|
решения нет
|
9
|
7
|
21
|
18
|
-63
|
решения нет
|
решения нет
|
10
|
8
|
24
|
21
|
-96
|
решения нет
|
решения нет
|
11
|
9
|
27
|
24
|
-135
|
решения нет
|
решения нет
|
Пояснения к выполнению.
Образец для вычисления дискриминанта = B3^2-4*A3*C3.
Образец для формулы корня:
=ЕСЛИ(D3<0;"решения нет";(КОРЕНЬ(D3)-B3)/(2*A3))
Функция КОРЕНЬ находится в категории «Математические».
Упражнение 7. «Возраст».
Задание. Создать электронную таблицу, которая автоматически высчитывает возраст по дате рождения и определяет социальную группу по возрасту ( рис. 34).
Примечание. Так как электронная таблица связана с системной датой, в таблице приведены сведения по состоянию на 15 апреля 2006. Поэтому в выполняемой таблице значения должны соответствовать состоянию на конкретную дату.
Рис.34
Порядок выполнения.
Заполнить первичными данными столбцы «№», «Фамилия», Дата рождения», «Пол». Следует помнить, что даты нужно вносить числами, разделёнными точкой, например 12.12.1950. Чтобы месяц выводился словом и к году приписывалась буква «г.», нужно отформатировать ячейку через пункт меню ФОРМАТ- ЯЧЕЙКИ, выбрать закладку ЧИСЛО, далее ДАТА и в списке найти нужный вариант представления даты.
В столбце «Возраст без учёта даты» возраст подсчитывается первым способом: как разницу между годом рождения и текущим годом – столбец Е. Выражение, сформированное мастером функций, в этом случае будет выглядеть так-
= ГОД(СЕГОДНЯ())- ГОД(С3)
Вычисленное таким образом значение является числом. Но программа может ошибочно отнести его к типу «Дата-время». В этом случае нужно вручную задать тип данных «Числовой». Для этого следует выделить область Е3:Е7, затем пойти в пункт меню ФОРМАТ-ЯЧЕЙКИ, выбрать закладку ЧИСЛО и указать на строку ЧИСЛОВОЙ.
Недостаток такого вычисления очевиден, так как не в этом случае возраст вычисляется без учёта месяца и дня рождения. Но полученное значение является числом и его можно использовать в дальнейшем в математических вычислениях.
В столбце «Возраст с учётом Даты» (столбец F) значение возраста подсчитывается другим способом : как разница между сегодняшней датой и датой рождения. В этом случае выражение будет выглядеть так:
= СЕГОДНЯ()-С3
Вычисленное таким образом значение будет являться датой. Чтобы дата выводилась в полных годах, нужно ввести дополнительный формат. Для этого следует выделить область F3:F7, выбрать пункт меню ФОРМАТ – ЯЧЕЙКИ, затем закладку ЧИСЛО, указать в списке форматов на строку (все форматы), а затем, в окно «Тип» ввести «ГГ» (рис. 35).
 
Рис. 35
Возраст при таком подсчёте вычисляется точно, с учётом месяца и дня рождения, но, так как значение является датой, то его сложно использовать в дальнейших расчётах, например, для определения социальной группы. Кроме того, такой приём годится только для возраста меньше 100 лет. При разности дат больше 100 перевод в года получается ошибочным.
Месяц рождения подсчитывается с помощью функции МЕСЯЦ мастера функций как МЕСЯЦ (С3).
День рождения подсчитывается с помощью мастера функций ДЕНЬ как ДЕНЬ(С3).
Чтобы подсчитать возраст с точностью до дня как число, нужно сначала создать промежуточную дату, у которой год совпадает с текущим годом, а месяц и день недели совпадает с днём и месяцем дня рождения. Промежуточную дату можно создать с помощью функции ДАТА, аналога функции Visual Basic DATESERIAL, которая создаёт дату из чисел, поставленных в неё в качестве аргументов. Бланк функции для первой строки списка представлен на рис.36.
Рис.36
Чтобы определить возраст точно как число, нужно сравнить текущую дату с датой, полученной в п.6. Если сегодняшняя дата больше, т.е. дня рождения ещё не было, то от возраста по годам нужно отнять единицу (рис. 37).
Рис. 37
Анализ «Пенсионер- работающий- иждивенец» выполнить с помощью тройного вложения функции «ЕСЛИ», используя подсказки на рис. 38. Логика отбора следующая:
для мужчин - если пол = «м»(рис. 38,бланк 1), то – если возраст больше 60 (рис. 38,бланк 2), то отнести к категории пенсионеров, а иначе- если возраст меньше 18 лет, то иждивенец, а иначе работающий (рис. 38, бланк 3).
Бланк 1.
Бланк 2.
Бланк 3.
Рис. 38
Чтобы вернуться к бланку 1 и продолжать формировать выражение для женщин, нужно в строке формул установить курсор на первом «ЕСЛИ» в выражении и щёлкнуть по fx.(рис. 39).
Рис.39
для женщин - если пол не равен «м» (установить курсор в ветви «…ложь» бланка 1 ), то (вызвать ещё одно «ЕСЛИ») - если возраст больше 55, то отнести к категории пенсионеров (рис. 40,бланк 4), а если возраст меньше 18 лет, то к иждивенкам (рис. 40,бланк 5). В результате бланк 1 примет вид как на рис. 41.
Бланк 4.
Бланк 5
Рис. 40
Итог заполнения бланка 1.
Рис. 41
Обратите внимание, что если для построения выражения социальной группы использовать возраст с учётом даты (ДАТА), т.е. столбец F, то ничего не получится, так как тип данных в этом столбце не является числом.
Самостоятельная работа 9.
Задание. В таблице есть три отдельных столбца с фамилиями, именами и отчествами сотрудников. Создать ещё один столбец, в котором средствами мастера функций автоматически будут формироваться фамилии с инициалами (табл. 6).
Таблица 6
№пп
|
Фамилия
|
Имя
|
Отчество
|
ФИО
|
1
|
Иванов
|
Иван
|
Иванович
|
Иванов И.И.
|
2
|
Петров
|
Пётр
|
Петрович
|
Петров П.П.
|
3
|
Никитин
|
Николай
|
Иванович
|
Никитин Н.И.
|
4
|
Николаева
|
Людмила
|
Петровна
|
Николаева Л.П.
|
5
|
Федосова
|
Нина
|
Петровна
|
Федосова Н.П.
|
Пояснения к выполнению.
В этом упражнении следует использовать функцию обработки строковых величин ЛЕВСИМВ, выделяющую из записи заданное количество символов слева.
Логика формирования записи в столбце ФИО следующая:
=Фамилия &” “&ЛЕВСИМВ(Имя, 1)&”.“&ЛЕВСИМВ(Отчество, 1)&”.”
Знак & означает сложение строк. В кавычках записаны пробел и точки соответственно.
|
