Тема 9. Задачи анализа данных с использованием средств Excel при выполнении логических операций

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

Специалисты различного профиля, как правило, из всевозможных логических функций чаще всего употребляют функции: И, ИЛИ и ЕСЛИ. Набора указанных функций вполне достаточно для организации выбора нескольких вариантов решения (операция ветвления). Функции И и ИЛИ позволяют создавать сложные высказывания, с помощью которых можно проверить выполнение (или невыполнение) сразу нескольких условий. Функция ЕСЛИ позволяет проверить истинность высказывания, либо его ложь. Когда существует таблица с текстовыми и числовыми данными (несколько списков объединены общим названием), тогда появляется задача выбора записей (строк) по определенным условиям. Алгоритм поиска необходимых записей задается в виде формулы, в которую могут входить как арифметические операции, так и логические операции с данными. Для формирования логической операции используют условия типа: равно, больше, меньше или равно и т.п. На рис. 1 показан фрагмент из таблицы «Список жителей района» с некоторыми данными о каждом жителе (таблицу можно создать или скопировать из файла).

Список жителей района
Фамилия	Пол	Год рождения	Образование	Категория работы	Участие в работе общественных организаций	Стаж работы в выборных органах
Виноградов	м	1973	среднее	Рабочий
Боева	ж	1969	специальное	Рабочий		3
Авдеева	ж	1967	специальное	Торговля	КПРФ
Вавилов	м	1959	среднее	Рабочий		4
Климентьева	ж	1968	высшее	Служащий	НАШИ
Смирнова	ж	1956	специальное	Торговля	НАШИ	4
Шлыкова	ж	1963	высшее	Служащий		2
Федоров	м	1949	среднее	Рабочий	НАШИ	1

Рис. 1. Фрагмент таблицы Excel с данными о жителях района

Задание 1. Составить логическое выражение для поиска в списке жителей района претендентов на выдвижение в депутаты, которые должны в обязательном порядке обладать следующими параметрами: т.е. необходимо найти женщин, которые более 3-х лет являются членами общественной организации НАШИ.

Решение задачи состоит из следующих действий:

· Создать список жителей района, в котором должны быть столбцы с данными, показанные на рис. 1.

· Составить логическое выражение для отбора данных из исходной таблицы. На рис. 2 показана блок-схема алгоритма отбора данных из таблицы по поставленным условиям, что во многом облегчает создание формулы для отбора необходимых претендентов.

Рис. 2. Блок-схема алгоритма отбора данных в таблице по поставленным условиям

· Добавить к исходной таблице еще один столбец с наименованием «Результаты выбора», на рис. 3 показано, что наименование этого столбца занесено в ячейку I4.

· В ячейки I5:I12 ввести логическое выражение (функцию):

=ЕСЛИ(И(G5="НАШИ";C5="ж"; H5>3);"ПОДХОДИТ";"")

В этой формуле круглые скобки выделяют те действия, которые должны быть выполнены в каждой группе, знак точка с запятой обозначает завершение выполнения действия с данными, двойными кавычками выделяют текстовые переменные, которые находятся в ячейках таблицы, числовые данные не выделяют двойными кавычками. Общая конструкция алгоритма поддерживает условие - ЕСЛИ, это оператор имеет два возможных варианта выхода после проверки условия. Если при сравнении текущего значения переменной с величиной, которая записана в условии, выполнение завершается нахождением результата «истина» - ДА, то осуществляется проверка следующего условия и т.д. Если при сравнении текущего значения переменной условие не выполняется, то генерируется ответ «ложь» - НЕТ. Чтобы выполнить все поставленные условия отбора данных, необходимо для каждой строки таблицы проверить значения определенных данных (последовательный выбор данных из строк таблицы, и есть просмотр текущих значений переменных, значения которых сравнивают с заданным условием). В поставленной задаче, выдвинуто три условия, следовательно, требуется осуществить последовательно анализ выполнения каждого условия. По этой причине, все условия в созданной функции, объединяются логическим оператором – И. При положительном ответе на все три условия, должен быть отображен результат, в виде ключевого слова (в данном примере таким словом является – «ПОДХОДИТ»). в противном случае в ячейку для ответа заносят пустую запись, которая обозначается символами (“” – открытая и закрытая двойная кавычки, можно записывать любые текстовые выражения, например – «не будем приглашать»).

· Размножить, созданную функцию в ячейке I5 для ячеек, находящихся в столбце ниже неё, например, для ячеек I6:I12.

Рис. 3. Результат проведения отбора данных по заданному логическому выражению

В рассматриваемом примере, на рис. 3 показана книга Excel c исходными данными, вновь созданным столбцом в таблице с наименованием «Результаты выбора» и конечными сведениями при выполнении логических операций по заданной функции (в ячейке I10 появилось слово – «ПОДХОДИТ»). Следует отметить, что логическое выражение можно построить с использованием Мастера функций (выбрать - логические функции), но при этом придется вносить довольно много добавлений в функцию, которая строится для рассматриваемого примера.

Контрольное задание: подобрать в таблице фамилии всех рабочих, которые старше 35 лет и не состоят ни в каких общественных организациях.

Вопросы для самоконтроля

1. Для каких типов данных можно использовать логические выражения?

2. Для чего составляют блок-схему проведения поиска данных в списках по заданному условию?

3. Что подразумевается в логическом выражении под ключевыми словами «Истина» и «Ложь»?

4. Каким образом отображается результат выполнения логического выражения на листе Excel?