Практическая работа №22
«Использование логических функций»
Цель работы: научиться использовать логические функции в Excel
Задание № 1.
1. Подсчитайте количество отличных, хороших и т. д. оценок на основании зачетной ведомости, представленной табл. 8.
2. Произведите расчет, используя операцию «Присвоение имени блоку ячеек».
Ход работы:
1. На новом листе рабочей книги создайте таблицу по образцу табл. 8.
2. Заполните данными столбцы: первый, второй, третий и четвертый.
Таблица 6.8
A | B | C | D | E | F | G | H | I | |
1 | № | Фамилия, имя, отчество | № зачетной книжки | Оценка | Кол-во 5 | Кол-во 4 | Кол-во 3 | Кол-во 2 | Неявка |
2 |
| Демидов М.И. | 119 | 5 |
|
|
|
|
|
3 |
| Иванов И.П. | 120 | 7 |
|
|
|
|
|
4 |
| Кукушкин В.Л. | 121 | 3 |
|
|
|
|
|
5 |
| Орлов А.П. | 131 | 4 |
|
|
|
|
|
6 |
| Петров К.Н. | 145 | 5 |
|
|
|
|
|
7 |
| Сидоров В.О. | 149 | 2 |
|
|
|
|
|
8 |
| Фролов В.А. | 156 | н/я |
|
|
|
|
|
3. В шестой, седьмой, восьмой, девятый и десятый столбцы введите формулы, для этого воспользуйтесь Мастером функций из панели инструментов Стандартная:
установите курсор в первую ячейку столбца отличных оценок (D2) и активизируйте Мастер функций;
в первом диалоговом окне выберите категорию функции и название функции;
Логические функции | |
Имя функции: | ЕСЛИ |
щелкните на кнопке ;
во втором диалоговом окне установите курсор в поле Логическое выражение и щелкните мышью в рабочей области Excel на ячейке D2 (Оценка «5»);
с клавиатуры введите ;
в поле Значение_если_истина введите ;
в поле Значение_если_ложь введите ;
нажмите кнопку ;
методом протягивания скопируйте формулу по столбцу «Кол-во 5».
4. С помощью Мастера функций аналогичным способом введите формулы в столбцы «Кол-во 4», «Кол-во 3» и т. д., изменяя соответственно значение поля Логическое выражение, соответственно на
«D2 = 4», «D2 = 3» и т. д.
5. Чтобы подсчитать сумму всех пятерок, четверок и т. д. и результаты представить в виде отдельной таблицы, нужно по каждому столбцу «Кол-во оценок» задать имена блокам соответствующих ячеек. Для этого выполните следующие действия:
выделите блок ячеек Е2:Е8 столбца «Количество 5»;
выполните команду меню Вставка > Имя > Присвоить;
в диалоговом окне Присвоение имени в строке Имя введите слово Отлично и щелкните на кнопке Добавить;
далее выделите ячейки F2:F8 столбца «Количество 4» и выполните команду Вставка > Имя > Присвоить;
в диалоговом окне Присвоение имени в строке Имя введите слово Хорошо;
аналогичные действия выполните с остальными столбцами табл. 8, создав имена блоков ячеек: Удовлетворительно, Неудовлетворительно, Неявка.
6. Создайте таблицу Итоги сессии (табл.6.9).
Таблица 6.9
Количество отличных оценок |
|
Количество хороших оценок |
|
Количество удовлетворительных оценок |
|
Количество неудовлетворительных оценок |
|
Неявки |
|
ИТОГО |
|
7. Введите формулу подсчета количества полученных оценок определенного вида, используя имена блоков ячеек с помощью Мастера функций:
установите курсор в ячейку подсчета количества отличных оценок;
щелкните по кнопке Мастера функций на панели инструментов Стандартная;
в первом диалоговом окне выберите категорию функции Математические, имя функции Сумм и щелкните на кнопке ;
во втором диалоговом окне установите курсор в строку Число1 и введите команду Вставка > Имя > Вставить;
в диалоговом окне Вставка имени выберите имя блока ячеек Отлично и щелкните на кнопке ;
повторите аналогичные действия для подсчета количества других оценок.
8. Посчитайте количество всех полученных оценок, используя кнопку Автосумма на стандартной панели инструментов.
Задание №2. Определить, в какой из заданных интервалов попадает зарплата каждого сотрудника НИИ, представленная в табл. 6.10.
Ход работы:
1. Создайте новую рабочую книгу.
2. Создайте таблицу из восьми столбцов, в которой содержатся сведения о семи сотрудниках НИИ: №, Ф.И.О., ежемесячная зарплата (табл. 6.10).
3. Создайте таблицу, содержащую четыре интервала числовых значений зарплат: 1000 — 2000, 2000 — 3000, 3000 — 4000, 4000—6000 (табл. 6.11).
4. Чтобы определить, попадает ли значение зарплаты из столбца С в заданный интервал, нужно использовать логическую функцию ЕСЛИ с заданием сложного условия И. Для этого необходимо выполнить следующее:
установить курсор в ячейку D2;
щелкнуть на значке Вставка функции Стандартной панели инструментов;
в окне Мастера функций выбрать Категорию функции Логические, в окне Вид функции — выбрать функцию ЕСЛИ, нажать кнопку ;
в адресной строке рабочего окна в раскрывающемся списке выбрать функцию И;
установить курсор в поле Логическое 1;
на рабочем поле Excel щелкнуть на ячейке С2;
с клавиатуры ввести >;
на рабочем поле Excel щелкнуть на ячейке А10;
установить курсор в поле Логическое 2;
на рабочем поле Excel щелкнуть на ячейке С2;
с клавиатуры ввести <;
на рабочем поле Excel щелкнуть на ячейке В10;
не закрывая окно Функции И, щелкнуть на слове Если в адресной строке рабочего окна — откроется окно функции Если;
в поле Значение_если_истина с клавиатуры ввести ;
в поле Значение_если_ложь с клавиатуры ввести ;
нажать кнопку .
Пример выполнения практической работы.
Таблица 6.10
A | B | C | D | E | F | G | H | |
1 | № | Ф.И.О | Зарплата | 1 ин | 2 ин | 3 ин | 4 ин | Проверка |
2 | 1 | Кузнецов | 5896 | 1 | 1 | |||
3 | 2 | Свиридов | 3990 |
| 1 | 1 | ||
4 | 3 | Молотов | 2098 | 1 | 1 | |||
5 | 4 | Иванов | 1980 | 1 | 1 | |||
6 | 5 | Петров | 2346 | 1 | 1 | |||
7 | ИТОГО | 1 | 2 | 1 | 1 | 5 |
Таблица № 6.11
| А | В | |
Интервалы | |||
10 | 1 ин | 1000 | 2000 |
11 | 2 ин | 2000 | 3000 |
12 | 3 ин | 3000 | 4000 |
13 | 4 ин | 4000 | 6000 |
5. Формулу из ячейки D2 операцией автозаполнения скопировать по столбцу D, ссылки на ячейки А10 и В10 нужно сделать абсолютными.
6. Аналогичным образом введите формулы в столбцы Е, F, G.
7. Для подсчета числа попаданий в каждый интервал выполните следующие действия:
выделите блок D2:D6;
нажмите кнопку Автосумма на Стандартной панели инструментов;
повторите это действие для каждого столбца.
8. Значения столбца Проверка получите, используя операцию Автосумма для значений блоков строк D2:G2, D3:G3 и т. д.
9. Значение ячейки Итого столбца Проверка должно совпадать с количеством сотрудников.