Автофильтрация

1. Выделите блок ячеек таблицы, начиная с имён заголовков полей вниз до конца записей , и скопируйте их в новую рабочую книгу с названием «Фильтрация».

2. Переименуйте Лист1, присвоив ему имя «автофильтр №1».

3. Установите курсор в область списка и выполните команду Данные – Фильтр - Автофильтр.

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

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

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

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

5. Результат фильтрации скопируйте на новый лист, присвоив ему имя – «автофильтр №2».

6. На листе «Фильтрация» результат автофильтрации отменить, установив указатель мыши в область списка и выполнив команду Данные – Фильтр - Автофильтр.

7. Сформулируйте выборку – для группы 133 получите сведения о сдаче экзамена по предмету П1 на оценки 3 и 4.

8. Результат сохраните на новом листе, присвоив ему имя «автофильтр №3».

Форма данных

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

10. Установите курсор в область списка и выполните команду Данные - Форма.

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

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

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

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

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

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

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

15. По аналогии сформулируйте условия отбора записей, указанные в задании №7.

Практическая работа № 7. (не делаем)

Цель работы. создание базы данных средствами Excel. Сортировка данных, выборка по различным критериям, поиск записи. Автоматическое подведение итогов.

Задание: Создайте таблицу по предложенному образ, заполните базу данных (до 20 записей). Выполните различные виды сортировки данных

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

1. Создайте таблицу по предложенному образцу.

   № п/п

   Фамилия

   Имя

   Отчество

   Дата рождения

   Адрес

   Телефон

   Оклад

   Налоги

   Сумма к выдаче

   Город

   Улица

   Дом

   Корпус

   Кварт.

   Проф.

   Пенсион.

   Подоход.

2. Для ячеек «Дата рождения» установите формат Дата (Формат – Ячейка – Число).

3. Для ячеек «Дом», «Квартира» установите числовой формат.

4. Для ячеек «Телефон» установите формат Номер телефона (Формат – Ячейка – Дополнительный – Номер телефона).

5. Для ячеек «Оклад», «Налоги», «Сумма к выдаче» установите Денежный формат.

6. В ячейку столбца «Налоги профсоюзные» внесите формулу для подсчёта налогов.

7. В ячейку столбца «Налоги пенсионные» внесите соответствующую формулу.

8. В ячейку столбца «Налоги подоходные» внесите формулу «12 % от оклада за вычетом минимальной заработной платы и пенсионного налога». Минимальную заработную плату принять равной 140 рублям.

9. Внесите первую запись. Начиная со второй записи, заполните таблицу, используя команду меню Данные – Форма. Перед использованием команды выделите первую запись таблицы и прилегающую к ней строку заголовка. Таблица должна содержать не менее 20 записей.

10. Выполните сортировку данных по фамилии (Данные – Сортировка) результат сортировки сохранить на Листе 2.

11. Отсортируйте исходные данные по возрастанию окладов, результат сохраните на Листе3.

12. Получите список людей проживающих по улице Мира (Данные – Фильтр – Автофильтр) результат сохраните на Листе 4.

13. Получите список людей, телефоны которых начинаются на 35, результат сохраните на Листе5.

Практическая работа№ 8.

Цель работы. Работа с матрицами. Работа с панелью инструментов. Создание структуры таблицы.

Задание: введите исходную матрицу; умножьте её на число; исходную и полученную при умножении матрицы перемножьте между собой; для исходной матрицы найдите: определитель и обратную матрицу, а так же транспонированную матрицу. Создайте собственную панель инструментов, в которой разместите все кнопки, которые были использованы при работе. Любую кнопку в созданной вами панели инструментов отредактируйте. Все расчётные данные представьте в виде структурной таблицы.

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

1. Введите в диапазон ячеек В3:D5 произвольные числовые значения, которые будут представлять исходную матрицу.

2. Умножьте исходную матрицу (В3:D5) на число 10. Число 10 занесите в отдельную ячейку F4. Выделите блок ячеек H3:J5 и введите в строку формул формулу = В3:D5*F4, а затем представить формулу в виде массива, для этого нажмите комбинацию клавиш Ctrl+Shift+Enter.

3. Умножьте две матрицы (матрицу В3:D5 и матрицу H3:J5) и результат получите в ячейках В8:D10. Для умножения матриц используется функция МУМНОЖ из категории Математические функции.

4. В ячейке В13 найдите определитель исходной матрицы (Мастер функций  категория функции Математические  функция МОПРЕД).

5. Найдите транспонированную матрицу в ячейках В15:D17 для исходной матрицы. Для транспонирования матриц используется функция ТРАНСП из категории Математические функции.

6. В ячейках В20:D22 вычислите матрицу обратную к исходной и докажите, что она является обратной (Мастер функций  категория функции Математические  функция МОБР. Эта функция возвращает обратную матрицу, а для доказательства надо исходную матрицу и обратную матрицу перемножить в ячейках H20:J22 и получить единичную матрицу).

7. Создайте структуру таблицы (меню Данные  команда Группа и структура  выделить часть таблицы, которая будет включена в структуру и активизировать команду Группировать).

Структура таблицы должна иметь вид дерева:

• 1 уровень Общая расчётная область;

• 2 уровень Умножение исходной матрицы на число.

• 3 уровень Умножение двух матриц.

• 4 уровень Нахождение определителя матрицы.

• 5 уровень Транспонирование матрицы.

• 6 уровень Нахождение обратной матрица и доказательство, что данная матрица является обратной.

Создайте собственную панель инструментов, поместив в неё все кнопки, которыми вы пользовались при выполнении предыдущих пунктов практической работы. Для создания собственной панели инструментов выполните следующие действия:

• В меню Вид  выберете команду Панели инструментов  Настройка;

• В диалоговом окне Настройка выберете вкладку Панели инструментов и нажмите кнопка Создать;

• В диалоговом окне Создание панели инструментов задайте имя Вашей панели, например «Личная», нажмите кнопку ОК, созданная панель отобразится на экране;

Для вставки в панель инструментов кнопок, которыми вы пользовались при выполнении практической работы, выполните следующее действие:

• В диалоговом окне Настройка выберете вкладку Команды, и в списке Категории выбираете нужную категорию, а в ней выбирается соответствующая команда и кнопка соответствующая данной команде вставляется, при помощи перетаскивания, в созданную вами панель инструментов.

9. На любой кнопке, из помещаемых в собственную панель инструментов, отредактируйте изображение. Для редактирования изображения на кнопке необходимо выполнить следующие действия:

• В меню Вид  выберете команду Панели инструментов  Настройка;

• В диалоговом окне Настройка выберете вкладку Команды, а из списка Категории выберете нужную команду;

• Вставьте в созданную вами панель выбранную команду, а в диалогов окне Настройка выберете кнопку Изменить выделенный объект. Далее отредактируйте изображение кнопки по точкам.

Практическая работа № 9

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

Задание: средствами Excel создать зачетную ведомость и подсчитать количество оценок: пятерок, четверок и т.д.

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

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

2. В шестой, седьмой, восьмой, девятый и десятый столбцы введите формулы логических функций. Для этого воспользуйтесь Мастером функций.

• установите курсор в первую ячейку столбца отличных оценок и активизируйте Мастер функций;

• в первом диалоговом окне выберите категорию функции и название функции;

Категория:	Логические функции
Имя функции:	ЕСЛИ

• щелкните на кнопке Готово;

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

• с клавиатуры введите «=5»;

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

• в поле Значение_если_ложь введите 0

• нажмите кнопку Готово;

• методом протягивания скопируйте формулу по столбцу «Количество 5» вниз.

3. С помощью Мастера функций аналогичным способом введите формулы в столбцы «Количество 4», «Количество 3», и т.д., изменяя значения поля Логическое выражение, соответственно: «D2=4», «D2=3» и т.д.

Таблица 8

A	B	C	D	E	F	G	H	I	J
№ п/п	Фамилия, имя, отчество	№ зачётной книжки	Оценка	Подпись экзаменатора	Кол-во 5	Кол-во 4	Кол-во 3	Кол-во 2	неявка
1	Демидов М. И.	119	5
2	Иванов И. П.	120	4
3	Кукушкин В. Л.	121	3
4	Орлов А. П.	131	4
5	Петров К. Н.	145	5
6	Сидоров Р. О.	149	2
7	Фролов В. А.	156	н/я

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

• выделите ячейки 1-7 столбца «Количество пятерок»;

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

• В диалоговом окне Присвоение имени в строке Имя введите слово ОТЛИЧНО и щелкните на кнопке Добавить;

• выделите ячейки 1-7 столбца «Количество четверок» и выполните команду Вставка – Имя – Присвоить;

• в диалоговом окне Присвоение имени в строке Имя введите слово ХОРОШО;

• аналогичные действия выполните с остальными столбцами таблицы 8, создав имена блоков ячеек: УДОВЛЕТВОРИТЕЛЬНО, НЕУДОВЛЕТВОРИТЕЛЬНО, НЕЯВКА.

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

   6. Введите формулу подсчёта количества полученных оценок определённого вида, используя имена блоков ячеек с помощью Мастера функций:

• установите указатель мыши в клетку подсчета количества отличных оценок;

• щелкните по кнопке Мастера функций;

• в первом диалоговом окне выберите : категорию функции - Математические , имя функции - СУММ и щелкните на кнопке ОК;

• во втором диалоговом окне установите в строку Число 1 курсор и введите команду Вставка - Имя - Вставить;

• В диалоговом окне Вставка имени выберите имя блока ячеек ОТЛИЧНО и щелкните на кнопке ОК;

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

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

Таблица 9

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

Практическая работа № 10.

Цель работы: ознакомиться с применением логических функций для решения задач.

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

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

1. Создать новую рабочую книгу.

2. Создать таблицу из восьми столбцов, в которой содержатся сведения о семи сотрудниках НИИ: № п/п, Ф.И.О., ежемесячная зарплата (Таблица 7).

3. Также создать таблицу, содержащую четыре интервала числовых значений зарплат (1000 – 2000, 2000 – 3000, 3000 – 4000, 4000 - 6000) (Таблица №8).

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

• установить курсор в ячейку D2;

• щелкнуть на значке Вставка функции Стандартной панели инструментов;

• в окне Мастера функций выбрать Категорию функции Логические, в окне Вид функции - выбрать функцию ЕСЛИ, нажать кнопку ОК;

• в строке формул в раскрывающемся списке выбрать функцию И;

• установить курсор в поле Логическое 1;

• на рабочем поле щелкнуть на ячейке C2;

• с клавиатуры ввести >=;

• на рабочем поле щелкнуть на ячейке А10;

• установить курсор в поле Логическое 2;

• ввести С2<В10;

• не закрывая окно Функции И щелкнуть на слове Если в строке формул – откроется окно функции Если;

• в поле Значение_если_истина с клавиатуры ввести 1;

• в поле Значение_если_ложь с клавиатуры ввести 0;

• нажать кнопку ОК.

Пример выполнения практической работы.

Таблица №7

	A	B	C	D	E	F	G	H
1	№ п/п	Ф.И.О.	Зарплата	1ин	2ин	3ин	4ин	проверка
2	1	Кузнецов	5896	0	0	0	1	1
3	2	Свиридов	3990	0	0	1	0	1
4	3	Молотов	2098	0	1	0	0	1
5	4	Иванов	1980	1	0	0	0	1
6	5	Петров	2346	0	1	0	0	1
7	ИТОГО			1	2	1	1	5

ЕСЛИ(И(C2>=A10;C2<B10);1;0)

Таблица №8

		A	B
		Интервалы
10	1 ин	1000	2000
11	2 ин	2000	3000
12	3ин	3000	4000
13	4 ин	4000	6000

1. Рис.13

4. Для последнего интервала большая граница тоже должна быть включена в диапазон исследуемых значений.

5. Чтобы формулу из ячейки D2 автозаполнением скопировать по столбцу D, ссылки на ячейки А10 и В10 нужно сделайте абсолютными;

6. Аналогичным образом введите формулы в столбцы E, F, G.

7. Для подсчета числа попаданий в каждый интервал выполните следующие действия:

• выделите блок D2:D6;

• нажмите кнопку Автосумма на Стандартной панели инструментов;

• повторите это действие для каждого столбца.

8. Значения столбца Проверка получить, используя операцию Автосумма для значений блоков строк D2:G2, D3:G3 и т.д.

9. Значение ячейки Итого столбца Проверка должно совпадать с количеством сотрудников.

Задания для самостоятельной работы