Практическая работа
Тема: «Использование функций и логических формул в MS Excel»
Цель: научиться применять логические выражения для записи условий, в которых сравниваются числа, функции, формулы, текстовые или логические значения.
Порядок работы
Задание 1.
1. Подсчитайте количество отличных, хороших и т. д. оценок на основании зачетной ведомости, представленной табл. 7.
2. Произведите расчет, используя операцию «Присвоение имени блоку ячеек».
Методика выполнения работы
1. На новом листе рабочей книги создайте таблицу по образцу табл. 7.
2. Заполните данными столбцы: первый, третий, четвертый и пятый.
Таблица № 1.
3. В пятый, шестой, седьмой, восьмой, девятый столбцы введите формулы. Для этого воспользуйтесь вкладкой Формулы: установите курсор в первую ячейку столбца отличных оценок (D2) и выберите категорию функции и название функции:
Категория: Логические функции
Имя функции: ЕСЛИ
Установите курсор в поле Логическое выражение и щелкните мышью в рабочей области Excel на ячейке D2 (Оценка «5»); с клавиатуры введите = 5; в поле Значение если истина введите 1; в поле Значение_если_ложь введите 0; нажмите кнопку «Ок»; методом автокопирования протяните формулу по столбцу «Кол-во 5».
4. Аналогичным способом введите формулы в столбцы «Кол-во 4», «Кол-во 3» и т.д., изменяя соответственно значение поля Логическое выражение, соответственно на «D2 = 4», «D2 = 3» и т. д.
5. Чтобы подсчитать сумму всех пятерок, четверок и т. д. и результаты представить в виде отдельной таблицы, нужно по каждому столбцу «Кол-во оценок» задать имена блокам соответствующих ячеек. Для этого выполните следующие действия:
ü выделите блок ячеек Е2:Е8 столбца «Количество 5»;
ü выполните команду Формулы - Определенные имена - Присвоить имя; в диалоговом окне Создание имени в строке Имя введите слово Отлично и щелкните на кнопке «Ок»;
ü далее выделите ячейки F2:F8 столбца «Количество 4» и выполните команду Формулы - Определенные имена - Присвоить имя; в диалоговом окне Создание имени в строке Имя введите слово Хорошо и щелкните на кнопке «Ок»;
ü аналогичные действия выполните с остальными столбцами табл. 1, создав имена блоков ячеек: Удовлетворительно, Неудовлетворительно, Неаттестация.
6. Создайте таблицу Итоги сессии (табл.2).
Таблица № 2.
7. Введите формулу подсчета количества полученных оценок определенного вида, используя имена блоков ячеек с помощью Мастера функций:
ü установите курсор в ячейку подсчета количества отличных оценок и воспользуйтесь вкладкой Формулы; в первом диалоговом окне выберите категорию функции Математические, имя функции Сумм; в диалоговом окне установите курсор в строку Число1 и выберите имя блока ячеек Отлично и щелкните на кнопке «ОК»;
ü повторите аналогичные действия для подсчета количества других оценок.
8. Посчитайте количество всех полученных оценок, используя функцию Автосумма.
Задание 2.
Определить, в какой из заданных интервалов попадает зарплата каждого сотрудника НИИ, представленная в табл. 3.
Методика выполнения работы
1. Перейдите на новый лист электронной книги.
2. Создайте таблицу из восьми столбцов, в которой содержатся сведения о пяти сотрудниках НИИ: № п/п, Ф.И.О., ежемесячная зарплата (табл. 3).
3. Создайте таблицу, содержащую четыре интервала числовых значений зарплат: 3000 — 5000, 5000 — 8000, 8000 — 12000, 12000—20000 (табл. 4).
Таблица № 3.
Таблица № 4.
4. Чтобы определить, попадает ли значение зарплаты из столбца С в заданный интервал, нужно использовать логическую функцию ЕСЛИ с заданием сложного условия И. Для этого необходимо выполнить следующее:
ü установить курсор в ячейку D2;
ü выбрать вкладку Формулы – Библиотека функций - Логические;
ü выбрать функцию ЕСЛИ и нажать «Ок»;
ü выбрать функцию И из категории Логические;
ü установить курсор в поле Логическое значение 1; на рабочем поле Excel щелкнуть на ячейке С2; с клавиатуры ввести >; на рабочем поле Excel щелкнуть на ячейке С10; установить курсор в поле Логическое 2; на рабочем поле Excel щелкнуть на ячейке С2; с клавиатуры ввести <; на рабочем поле Excel щелкнуть на ячейке D10;
ü не закрывая окно Функции И, щелкнуть на слове ЕСЛИ в адресной строке рабочего окна откроется окно функции ЕСЛИ; в поле Значение_если_истина с клавиатуры ввести 1;
ü в поле Значение_если_ложъ с клавиатуры ввести 0; нажать кнопку «Ок».
5. Формулу из ячейки D2 операцией автозаполнения скопировать по столбцу D, ссылки на
ячейки С10 и D10 нужно сделать абсолютными.
6. Аналогичным образом введите формулы в столбцы Е, F, G.
7. Для подсчета числа попаданий в каждый интервал выполните следующие действия:
выделите блок D2:D6; нажмите кнопку Автосумма; повторите это действие для каждого столбца.
8. Значения столбца Проверка получите, используя операцию Автосумма для значений блоков строк D2:G2, D3:G3 и т. д.
9. Значение ячейки Итого столбца Проверка должно совпадать с количеством сотрудников.
Задание 3. В выводе работы укажите, для чего используются логические функции и какие условия с можно оценивать с их помощью.