2018-01-08
1757Цель занятия - Изучение логических функций EXCEL для создания и обработки анкетных данных и обработки результатов эксперимента.
В результате выполнения лабораторной работы студент должен приобрести навыки работы по составлению и обработке результатов анкетирования или тестирования испытуемых.
Задание 1. Используя логическую функцию ЕСЛИ, составить простейший тест.
Указание по выполнению задания1.
Одной из наиболее часто используемых логических функций является функция "ЕСЛИ", которая позволяет реализовать ветвящуюся алгоритмическую структуру.
Функция ЕСЛИ возвращает одно значение, если указанное условие дает в результате значение ИСТИНА, и другое значение, если условие дает в результате значение ЛОЖЬ.
Например, формула = ЕСЛИ(A1>10,"Больше 10","10 или меньше") возвращает строку "Больше 10", если значение в ячейке A1 больше 10, и "10 или меньше", если оно меньше или равно 10.
Синтаксис функции ЕСЛИ:
ЕСЛИ(лог_выражение;[значение_если_истина]; [значение_если_ложь])
|
|
|
Аргументы(Аргумент: Значение, предоставляющее информацию для действия, события, метода, свойства, функции или процедуры.) функции ЕСЛИ указаны ниже.
Лог_выражение. Обязательный аргумент. Любое значение или выражение, дающее в результате значение ИСТИНА или ЛОЖЬ. Например, "A10=100" — логическое выражение; если значение в ячейке A10 равно 100, это выражение принимает значение ИСТИНА, в противном случае — значение ЛОЖЬ. В этом аргументе может использоваться любой оператор сравнения.
Так выглядит окно мастера функций при работе с функцией ЕСЛИ
Функции и выражения могут быть вложены друг в друга, в частности, функция "ЕСЛИ" в качестве значений аргументов "Значение_если_истина" и "Значение_если_ложь" допускает вложенность до 7 уровней, что позволяет конструировать проверку достаточно сложных условий.
Построить таблицу вида:
Таблица должна содержать 3 столбца и 10 строк. Ниже приведен пример из двух строк. Столбец С содержит уровень, который определяется по правилу: если значение IQ в соответствующей строке больше или равно 100, то уровень интеллекта высокий, в противном случае низкий.
| A | B | C | |
| ФИО | IQ | уровень | |
| Иванов Иван Иванович | = ЕСЛИ(B2>=100;"выше среднего";"ниже среднего") | ||
| Сидоров Петр Васильевич |
Для того, чтобы система автоматически определяла уровень интеллекта испытуемых по каждой строке, необходимо в С2 записать формулу = ЕСЛИ(B2>=100;"выше среднего";"ниже среднего"). Тогда при копировании формулы в С3 и далее будет автоматически определяться уровень интеллекта, и таблица примет вид:
|
|
|
| A | B | C | |
| ФИО | IQ | уровень | |
| Иванов Иван Иванович | выше среднего | ||
| Сидоров Петр Васильевич | ниже среднего |
Составить тест на коммуникабельность:
| A | B | C | |
| Тест на коммуникабельность | |||
| № | Вопрос | Ответ | |
| Любите ли Вы общаться | да | ||
| Вы являетесь лидером | нет | ||
| Легко ли Вы знакомитесь с новыми людьми | да | ||
| Любите ли Вы одиночество | нет | ||
| В компании Вы находитесь в центре внимания | да | ||
| Нужно ли Вам обдумать ответ на заданный вопрос | да | ||
| Любите ли Вы прогулки в одиночестве | да | ||
| Вам необходимо, чтобы рядом с Вами был кто-то всегда | да | ||
| Вы любите оказывать помощь окружающим | нет | ||
| Сумма баллов | |||
| Результат - Вы коммуникабельны |
Для того, чтобы подсчитать сумму баллов, необходимо определить количество ответов типа «да». Так как это не числовая переменная, то вместо обычной суммы, можно использовать функцию СЧЕТЕСЛИ.
Функция СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые соответствуют одному указанному пользователем критерию. Например, можно подсчитать количество всех ячеек, которые начинаются с определенной буквы или содержат числа, большие или меньшие указанного значения. Предположим, например, что есть лист со списком задач в столбце A, а имя пользователя, ответственного за каждую из задач, указано в столбце B. Для подсчета количества повторений имени ответственного в столбце B можно использовать функцию СЧЁТЕСЛИ. Это позволяет определить количество задач, назначенных этому пользователю.
СЧЁТЕСЛИ(диапазон, критерий)
Функция СЧЁТЕСЛИ имеет аргументы(Аргумент. Значение, предоставляющее информацию для действия, события, метода, свойства, функции или процедуры.), указанные ниже.
Диапазон. Обязательный аргумент. Одна или несколько ячеек, по которым требуется выполнить подсчет. В этих ячейках могут находиться числа или имена, массивы или ссылки, содержащие числа. Пустые ячейки и текстовые значения не учитываются.
Критерий. Обязательный аргумент. Число, выражение, ссылка на ячейку или текстовая строка, которая определяет, какие ячейки нужно подсчитывать. Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки" или B4. В нашем случае
=СЧЁТЕСЛИ(C3:C11;"да") записывается в ячейке С12. Чтобы сделать заключение или вывод, далее используем функцию ЕСЛИ. В ячейке В13 необходимо записать формулу.
=ЕСЛИ(C12>=5;"Вы коммуникабельны";"Вы не коммуникабельны").
Аналогичным образом можно обрабатывать анкеты, полученные после опроса испытуемых.
Задание 2. Используя логические функции ЕСЛИ, И, ИЛИ решить задачу анализа успеваемости студентов.
Указание по выполнению задания1.
Решить задачу назначения стипендии студентам по результатам успеваемости студентов за семестр.
Необходимо учесть следующие правила:
| если все сдано на 5 - повышенная стипендия | |||||||||||||||||
| если сдано на 4-5 - обычная стипендия | |||||||||||||||||
| если сессия сдана с 3 - нет стипендии Составить примерную таблицу. | |||||||||||||||||
| A | B | C | D | E | |||||||||||||
| 1 | ФИО | Информатика | История | Математика | Стипендия | ||||||||||||
| Борисов Алексей | |||||||||||||||||
| Бурштейн Михаил | |||||||||||||||||
| Зиновьев Владимир | |||||||||||||||||
| Ивашинин Сергей | |||||||||||||||||
| Кейлина Мария | |||||||||||||||||
| Маркова Екатерина | |||||||||||||||||
| Петрова Ольга | |||||||||||||||||
| Познарева Елена | |||||||||||||||||
| Чернецов Иван | |||||||||||||||||
| Ямашкина Наталья | |||||||||||||||||
Для того, чтобы автоматизировать процесс заполнения последнего столбца по стипендии, в ячейку Е2 необходимо записать формулу:
|
|
|
ЕСЛИ(И(B2=5;C2=5;D2=5);"повышеннаястипендия";ЕСЛИ(ИЛИ(B2<=3;C2<=3;D2<=3);"нет стипендии";"обычная стипендия")).
В результате заполнится последний столбец и таблица примет вид:
| A | B | C | D | E | |
| 1 | ФИО | Информатика | История | Математика | Стипендия |
| Борисов Алексей | обычная | ||||
| Бурштейн Михаил | обычная | ||||
| Зиновьев Владимир | обычная | ||||
| Ивашинин Сергей | нет стипендии | ||||
| Кейлина Мария | нет стипендии | ||||
| Маркова Екатерина | обычная | ||||
| Петрова Ольга | нет стипендии | ||||
| Познарева Елена | нет стипендии | ||||
| Чернецов Иван | нет стипендии | ||||
| Ямашкина Наталья | повышенная |
Как видно из формулы используются функции логические И и ИЛИ. Функция ЕСЛИ вложенная.
Логическая функция И возвращает значение ИСТИНА, если в результате вычисления всех аргументов получается значение ИСТИНА; возвращает значение ЛОЖЬ, если в результате вычисления хотя бы одного из аргументов получается значение ЛОЖЬ.
Обычно функция И используется для расширения возможностей других функций, выполняющих логическую проверку. Например, функция ЕСЛИ выполняет логическую проверку и возвращает одно значение, если при проверке получается значение ИСТИНА, и другое значение, если при проверке получается значение ЛОЖЬ. Использование функции И в качестве аргумента лог_выражение функции ЕСЛИ позволяет проверять несколько различных условий вместо одного.
Синтаксис
И(логическое_значение1, [логическое_значение2],...)
|
|
|
Логическая функция ИЛИ возвращает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА или ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.
Синтаксис
ИЛИ(логическое_значение1;логическое_значение2;...)
Контрольные вопросы
1. В каких случаях применяются логические функции?
2. Чем отличается функция ЕСЛИ от остальных функций?
3. Составьте примеры случаев, в которых необходимо использовать функцию ЕСЛИ и логические функции И, ИЛИ.
4. Чем отличаются функции И и ИЛИ от функции ЕСЛИ?
5. В каком формате записываются функции И, ИЛИ, ЕСЛИ?
Литература основная
1. Информатика: экспресс-подготовка к интернет-тестированию. Под общей редакцией Рубальской О.Н.- М: Финансы и статистика, 2010- с.19-24.
