Лабораторная работа №5 Фильтрация записей

Цель работы: ознакомиться со способом фильтрации записей спи­ска, автофильтрации, работой с формой данных.

Методика выполнения работы

1. Создайте новую рабочую книгу с названием “Фильтрация”.

1. Скопируйте в новую рабочую книгу таблицу, созданную в рабо­те № 4 (см. табл. 3.6).

2. Переименуйте Лист1, присвоив ему имя “Автофильтр №1”.

4. Чтобы применить Автофильтрацию, установите курсор в об­ласть шапки таблицы и выполните команду Данные—>Фильтр—>Автофильтр.

4. Сформируйте условия отбора: для преподавателя А1 выбрать сведения о сдаче экзамена на положительную оценку, вид занятий – Лекция. Для этого выполните следующие действия:

• в столбце Таб № препод. нажмите кнопку Фильтр, из списка усло­вий отбора выберите А1;

• в столбце Оценка нажмите кнопку Фильтр, из списка условий отбора выберите Условие и в диалоговом окне сформируйте условие отбора >2;

• в столбце Вид занятий нажмите кнопку Фильтр, из списка условий отбора выберите Лекция.

6. Результат фильтрации скопируйте на новый лист, присвоив ему имя “Автофильтр №2”.

7. На листе “Автофильтр №1” результат автофильтрации отмени­те, установив указатель мыши в область списка и выполнив команду Данные —> Фильтр —> Автофильтр.

8. Сформулируйте выборку: для группы З-3230 получите сведения о сдаче экзамена по предмету П1 на оценки 3 и 4.

9. Результат сохраните на новом листе, присвоив ему имя “Авто­фильтр №3”.

10. Скопируйте исходную таблицу на новый рабочий лист, пере­именовав его в Форма данных.

11. Установите курсор в область шапки таблицы и выполните команду Дан­ные —>Форма.

12. В окне Форма данных просмотрите записи списка и внесите не­обходимые изменения по своему усмотрению с помощью кнопок <Предыдущая> и <Следующая>.

13. С помощью кнопки <Создатъ> добавьте новые записи.

14. В окне Форма данных сформируйте условия отбора записей. Для этого нажмите кнопку <Критерии>, название которой поменяется на <Правка>. В пустых строках имен полей списка введите критерии:

    • в строку Табл. № препод. введите А1;

    • в строку вид занятия введите Лекция;

    • в строку оценка введите условие > 2.

15. Просмотрите отобранные записи нажатием на кнопку <Предыдущая> или <Следующая>.

16. По аналогии сформулируйте условия отбора записей, указан­ные в п. 8.

Лабораторная работа №6 Использование логических функций

Задание № 1

1. Подсчитайте количество отличных, хороших и т. д. оценок на основании зачетной ведомости, представленной в табл. 3.7.

1. Произведите расчет, используя операцию “Присвоение имени блоку ячеек”.

Методика выполнения работы

1. На новом листе рабочей книги создайте таблицу по образцу табл.3.7.

2. Заполните данными столбцы A, B, C, D.

Таблица 3.7

	А	В	С	D	E	F	G	H	I
1	№ п/п	Фам., имя, отчество	№ зач. книжки	Оцен- ка	Кол-во 5	Кол-во 4	Кол-во 3	Кол-во 2	Неявка
2	1	Демидов М.И.	З-3230/04	5
3	2	Иванов И. П.	З-3230/05	4
4	3	Кукушкин В. Л.	З-3230/07	3
5	4	Орлов А. П.	З-3230/11	4
6	5	Петров К.Н.	З-3230/13	5
7	6	Сидоров В.О.	З-3230/15	2
8	7	Фролов В А.	З-3230/18	0

3. В столбцы E, F, G, H, I введи­те формулы, для этого:

   • установите курсор в первую ячейку столбца количества отличных оценок (E2) и выполните Вставка —> Функция—> категория Логические—>функция = ЕСЛИ, убрать появившееся диалоговое окно Аргументы функции с табл. 3.7 (если оно ее закрывает);

   • в диалоговом окне Аргументы функции установите курсор в поле Лог._ выражение и щелкните мышью в рабочей области Excel на ячейке D2;

   • появится адрес ячейки D2 и с клавиатуры введите < = 5 >, т.е. сформируется логическое выражение D2=5;

   • в поле Значение_если_истина введите<1>;

   • в поле Значение_если_ложъ введите< 0>;

   • щелкните на кнопке <ОК>.

Методом протягивания скопируйте формулу по столбцу E - “Кол-во 5”.

4. С помощью Мастера функций аналогичным способом введите формулы в столбцы “Кол-во 4“, “Кол-во 3” и т. д., изменяя соответст­венно значение поля Логическое выражение на D2 = 4, D2 = 3, D2 = 2. Для подсчета количества неявившихся на экзамен необходимо задавать логическое выражение D2=0;

5. Чтобы подсчитать сумму всех пятерок, четверок и т. д. и резуль­таты представить в виде отдельной таблицы, нужно по каждому столб­цу “Кол-во оценок” задать имена блокам соответствующих ячеек. Для этого выполните следующие действия:

• выделите блок ячеек Е2:Е8 столбца “Кол-во 5”;

• выполните команду меню Вставка —> Имя —> Присвоить;

• в диалоговом окне Присвоение имени в строке Имя введите слово Отлично и щелкните на кнопке Добавить и затем <ОК>;

• далее выделите ячейки F2:F8 столбца “Кол-во 4” и выполните команду Вставка —> Имя —> Присвоить;

• в диалоговом окне Присвоение имени в строке Имя введите слово Хорошо и щелкните на кнопке <Добавить> и затем <ОК>;

• аналогичные действия выполните с остальными столбцами табл. 3.7, создав имена блоков ячеек Удовлетворительно, Неудовлетворитель­но, Неявка.

6. Создайте таблицу Итоги сессии (табл.3.8).

Таблица 3.8

ИТОГИ СЕССИИ
Количество отличных оценок
Количество хороших оценок
Количество удовлетворительных оценок
Количество неудовлетворительных оценок
Неявки
ИТОГО

7. Введите формулу подсчета количества полученных оценок определенного вида:

• установите курсор в ячейку подсчета количества отличных оценок и выполните Вставка —> Функция—> категория Математические —>функция = СУММ;

• щелкните на кнопке <ОК>;

• в диалоговом окне Аргументы функции установите курсор в строку Число1 и выполните Вставка —> Имя—>Вставить;

• в диалоговом окне Вставка имени выберите имя блока ячеек Отлично и щелкните на кнопке <ОК>;

• повторите аналогичные действия для подсчета количества других оценок.

8. Подсчитайте ИТОГО – количество всех полученных оценок, используя кнопку Автосумма на стандартной панели инструментов.

Кнопка Автосумма – это символ Σ на стандартной панели инструментов. Если его нет, необходимо выполнить Вид —> Панели инструментов—> поставить флажок на кнопку Стандартная.

Для подсчета ИТОГО:

• щелкнуть на ячейку, где должен быть результат ИТОГО;

• щелкнуть на кнопку Автосумма;

• выделить группу ячеек с количеством всех оценок;

• <Enter>.

Задание № 2. Определить, в какой из заданных интервалов попадает зарплата каждого сотрудника НИИ, представленная в табл. 3.9.