Краткие теоретические сведения

Список данных - это набор строк таблицы, содержащий связанные данные. Список данных может рассматриваться как база данных, в которой строки выступают в качестве записей, а столбцы являются полями. Первая строка списка при этом должна содержать названия полей (столбцов). Команды для обработки списков данных как базы данных представлены в меню ДАННЫЕ.

Команда меню ДАННЫЕ/ СОРТИРОВКА предназначена для упорядочения записей базы данных. Базу данных можно сортировать по одному или нескольким ключам (значениям ячеек столбцов) в порядке возрастания или убывания. При сортировке по второму (или третьему) ключу элементы списка с одинаковым первым ключом будут упорядочены соответствующим образом.

Для сортировки месяцев и дней надели в соответствии с их логическим, а не алфавитным порядком, следует использовать пользовательский порядок сортировки с помощью команды ДАННЫЕ/ СОРТИРОВКА /ПАРАМЕТРЫ/ СОРТИРОВКА ПО ПЕРВОМУ КЛЮЧУ.

Команда ДАННЫЕ/ ФОРМА предлагает удобную форму для ввода, удаления, просмотра записей базы данных, а также просмотра записей, удовлетворяющих определенному критерию.

Команда ДАННЫЕ/ ФИЛЬТР предназначена для выборки записей, удовлетворяющих определенным условиям. Команда ДАННЫЕ/ ФИЛЬТР/ АВТОФИЛЬТР наиболее быстро и удобно обрабатывает данные. Условия фильтрации записей можно задавать для нескольких полей. На одно поле можно наложить до двух условий.

Если требуется наложить три или более условий необходимо использовать команду ДАННЫЕ/ ФИЛЬТР/ РАСШИРЕННЫЙ ФИЛЬТР. Эта команда позволяет копировать отфильтрованные записи в другое место, а также фильтровать данные на основе вычисленного значения.

Команда ДАННЫЕ/ ИТОГИ предназначена для получения промежуточных результатов. Промежуточные вычисления могут быть отображены в таблице с помощью нескольких типов вычислений одновременно.

Задание 1. Сортировка базы данных

1. Создать ведомость начисления заработной платы: Таблица 1.

· Отчисления в ПФ= Начислено* 10%;

· Подоходный налог= (Начислено- МРП)* 5%;

· Удержано в кредит= Начислено* Задолженность по кредиту;

· К выдаче= Начислено- Отчисления в ПФ- Подоходный налог- Удержано в кредит;

· Подсчитать итоговую сумму по полям «Начислено» и «К выдаче».

Результаты вычислений отображены в таблице 2.

2. Выделить диапазон ячеек А3: Н13 и вызвать команду Данные/ Сортировка.

3. В окне Сортировка диапазона указать, что первым ключом сортировки будет поле Ф.И.О. (по возрастанию).

4. Выполнить сортировку: первый ключ - Удержано в кредит (По возрастанию), второй ключ- К выдаче (по убыванию). Таблица будет выглядеть следующим образом:

Таблица 2.

Задание 2. Форма базы данных

1. Добавить две записи к базе данных «Ведомость начисления заработной платы». Для этого выделить диапазон ячеек А14: Н14, поместить курсор на рамку выделенного диапазона и переместить его в 16 строку (перед тем, как выполнять это задание, отсортируйте базу данных по полю № п/п по возрастанию).

2. Выделить диапазон А3: Н13 и вызвать команду Данные/ Форма. В появившемся диалоговом окне щелкнуть по кнопке Добавить и ввести следующие данные:

Для ввода новой записи использовать кнопку Добавить или клавишу Enter. Ввести формулы для вычисляемых полей (таб. 3).

3. Удалить запись под номером 7. С помощью полосы прокрутки или кнопок Далее или Назад найти нужную запись и щелкнуть кнопку Удалить. Использую кнопку Восстановить можно отменить предыдущее действие.

4. Просмотреть записи, в которых нет удержаний по кредитам. Для этого установить бегунок полосы прокрутки в начало и щелкнуть кнопку Критерии. В поле Удержано в кредит ввести число ноль и используя кнопку Далее просмотреть записи, удовлетворяющие введенному критерию. Кнопкой Закрыть закончить диалог.

Таблица 3.

Задание 3. Автофильтр

1. Выделить диапазон ячеек А3: Н14 и вызвать команду Данные/ Фильтр/ Автофильтр.

2. Отфильтровать записи, в которых фамилия начинается с букв «А» или «Р». Для этого щелкнуть кнопку открытия списка в поле Ф.И.О. В выпадающем списке щелкнуть параметр Условие. Открывшееся диалоговое окно заполнить следующим образом: Ф.И.О. начинается с А или Р, ОК. Результаты фильтрации отображены в таблице 4.

Таблица 4.

1. Вернуться к полной базе данных, щелкнув кнопку открытия списка в поле Ф.И.О, а затем параметр Все.

2. Отфильтровать записи, в которых отчисления в пенсионный фонд больше 3000 тенге, но меньше 5000 тенге. Для этого в выпадающем списке поля Отчисления в ПФ щелкнуть параметр Условие. А затем открывшееся диалоговое окно заполнить следующим образом: Удержания в ПФ больше 3000 и меньше 5000, ОК. Результаты фильтрации отображены в таблице 5.

Таблица 5.

3. Отфильтровать записи, в которых Удержано в кредит равно нулю или больше 5000 тенге. Для этого диалоговое окно параметра Условие поля Удержано в кредит заполним следующим образом: Удержано в кредит равно 0 или больше 5000, ОК. Результаты фильтрации отображены в таблице 6.

Таблица 6.

Задание 4. Расширенный фильтр

1. Отфильтровать записи, в которых фамилия начинается с букв «В», «Е» или «К». Перед тем выполнить это задание нужно отключить Автофильтр командой Данные/ Фильтр/ Автофильтр.

2. Создать диапазон условий. Для этого выделить диапазон А3: Н3 и скопировать его в ячейку А17. Затем в ячейку В18 ввести букву В, в ячейку В19 ввести букву Е, а в ячейку В20- букву К (таб.7). Условия расположенные в разных строках, читаются как «В или Е или К».

3. Выделить диапазон ячеек А3: Н14 и вызвать команду Данные/ Фильтр/ Расширенный фильтр. В окне Расширенный фильтр включить переключатель Фильтровать список на месте. В поле Исходный диапазон должно быть $А$3: $Н$14. В поле Диапазон условий ввести $B$17: $B$20, ОК. Результаты фильтрации отображены в таблице 8.

Таблица 7.

Таблица 8.

4. Отфильтровать записи, в которых Начислено больше 30000 тенге, но меньше 60000 тенге. Для этого скопировать содержимое ячейки С17 в ячейку D17.

5. Ввести в ячейку С18 условие > 3000, а в ячейку D18 условие < 60000 (таб. 9). Условия, расположенные в одной строке, читаются как «больше 30000 и меньше 60000».

Таблица 9.

6. Выделить диапазон ячеек А3: Н14 и вызвать команду Данные/ Фильтр/ Расширенный фильтр. В окне Расширенный фильтр включить переключатель Фильтровать список на месте.

7. В поле Исходный диапазон должно быть $А$3: $Н$14. В поле Диапазон условий ввести $С$17: $D$18, ОК.

8. Результаты фильтрации отображены в таблице 10.

Таблица 10.

9. Отфильтровать записи, в которых К выдаче меньше 20000 тенге, но больше 30000 тенге. Отфильтрованные записи скопировать в другое место.

10. Скопировать содержимое ячеек А3: Н3 в ячейку А22.

11. Ввести в ячейку Н18 условие < 20000, в ячейку Н19 условие > 30000.

12. Выделить диапазон ячеек А3: Н14 и вызвать команду Данные/ Фильтр/ Расширенный фильтр. В окне Расширенный фильтр включить переключатель Скопировать результат в другое место.

13. В поле Исходный диапазон должно быть $А$3: $Н$14.

14. В поле Диапазон условий ввести $Н$17: $H$19.

15. В поле Поместить результат в диапазон ввести $A$22: $H$34, ОК..

16. Результаты фильтрации отображены в таблице 12.

Таблица 11.

Таблица 12.

Задание 5. Промежуточные результаты

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

Таблица 13.

2. Для выполнения этого задания нужно отсортировать базу по полю Задолженность по кредиту по возрастанию. Результаты сортировки отображены в таблице 14.

3. Выделить диапазон ячеек А3: Н14 и вызвать команду Данные/ Итоги.

4. В окне Промежуточные итоги в выпадающем списке поля При каждом изменении в: выбрать Задолженность по кредиту.

5. В поле Операция выбрать функцию Сумма.

6. В поле Добавить итоги по: отметить поле К выдаче.

7. Включить переключатель Итоги под данными, ОК. Результаты отображены в таблице 15.

Задания к выполнению самостоятельных работ

Составить таблицу по предложенному преподавателем варианту и выполнить следующие действия:

1. Отсортировать таблицу по текстовому полю, по двум ключам, по трем ключам.

2. Использовать команду ДАННЫЕ/ ФОРМА для добавления двух записей, удаления одной записи. С помощью кнопки Критерии просмотреть записи, удовлетворяющие введенному условию.

3. Установить автофильтр, выполнить выборку записей по различным условиям, включающим функции «И» и «ИЛИ».

4. С помощью расширенного фильтра выполните те же действия.

5. Получить промежуточные итоги с помощью команды ДАННЫЕ/ ИТОГИ.

Таблица 14.

Таблица 15.

Задания к выполнению самостоятельных работ

Задание 1. Список участников соревнований

Задание 2. Ведомость сбыта продукции

Задание 3. Список

Задание 4. Сведения о результатах экзаменационной сессии

Задание 5. Ведомость движения товаров в магазине

Контрольные вопросы:

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

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

3. Возможности команды ДАННЫЕ/ФОРМА?

4. Назначение автофильтра?

5. Возможности расширенного фильтра?

6. Назначение команды ДАННЫЕ/ФИЛЬТР/ОТОБРАЗИТЬ ВСЕ?

7. Какую команду необходимо выполнить перед выполнением команды ДАННЫЕ/ИТОГИ?

8. Как удалить промежуточные итоги?