Методические рекомендации для выполнения индивидуальных заданий
Раздел 3. Офисные программы. Табличные процессоры
Тема 3.3. Работа со списками. Промежуточные итоги
Понятие списка, способы заполнения списка
При работе с большими объемами информации для хранения, поиска, сортировки и изменения данных используются базы данных. База данных (БД) - это организованная структура, предназначенная для хранения информации. СУБД (системы управления базами данных) – это комплекс программных средств, предназначенных для организации и ведения БД. Основными объектами любой БД являются таблицы.
Для создания и ведения баз данных в Microsoft Excel служат списки (специальным образом организованные таблицы).
При создании списков следует придерживаться следующих правил:
- на одном листе не должно быть более одного списка;
- если на листе, содержащий список, располагаются и другие данные, то они должны быть отделены от списка пустыми строкой и столбцом;
- каждый столбец должен содержать информацию одного типа, а весь столбец должен быть по одной теме;
- одна или две (лучше одна) верхние строчки списка должны содержать заголовки;
- список не должен содержать полностью пустых строк или столбцов.
Строку данных списка часто называют записью, а столбец – полем данных.
Заполнение списка производится:
] прямым заполнением
Информация вводится с клавиатуры. В процессе заполнения заголовки списка можно закрепить, разделив окно при помощи "мышки" или выполнив
меню Окно ® команда Разделить;
меню Окно ® команда Закрепить области.
] при помощи формы для заполнения (рис.8.1)
Меню Данные ® команда Форма
Рис. 1
Сортировка списка
Сортировка списка – изменение порядка следования строк в зависимости от накладываемых условий
Сортировка списка производится при помощи нажатия кнопки сортировка по возрастанию (по убыванию) или при помощи
Меню Данные ® команда Сортировка
Рис. 2
Отбор информации.
Для анализа списка используют фильтры.
Отфильтровать список – значит скрыть все строки за исключением тех, которые удовлетворяют заданным условиям отбора.
Автофильтр применяется к одному полю данных, при этом число условий не может быть больше двух. Допускается последовательное применение автофильтра.
Инициализация автофильтра.
Установить курсор в любую ячейку списка и выполнить
Меню Данные ® команда Фильтр ® команда Автофильтр
Функции автофильтра
1. Для нахождения заданного числа наибольших или наименьших элементов списка используют команду Первые десять.
Рис. 3
2. Для задания более сложных условий используют команду Условие.
Рис. 4
Пример
Имеется следующая информация:
| A | B | C | D | E | F | |
| Ф.И.О. | Оклад | Возраст | ||||
| Иванов | ||||||
| Петров | ||||||
| Сидоров | ||||||
| Федоров |
1. Выполнить сортировку в алфавитном порядке фамилий
2. Отобрать 2-х сотрудников, имеющих наименьшие оклады
3. Найти сотрудников, имеющих оклад более 300, но менее 600.
Расширенный фильтр
Применяется к двум и более полям данных или когда число условий более двух.
Меню Данные ® команда Фильтр ® команда Расширенный фильтр
Рис. 5
Исходный диапазон - исследуемый список (блок ячеек).
Диапазон условий
v в первой строке - наименование исследуемых столбцов, которые должны совпадать с наименованиями в списке;
v в последующих - условия (логический оператор без левой части). Если условия расположены в одной строке, то подразумевается соединитель И; если – в разных строках, то – ИЛИ. Строк с условиями м.б. и более двух.
Пример
1. Отобрать сотрудников, имеющих оклад более 300 или чей возраст не превышает 40.
| A | B | C | D | E | F | |
| Ф.И.О. | Оклад | Возраст | Оклад | Возраст | ||
| Иванов | >300 | |||||
| Петров | <40 | |||||
| Сидоров | ||||||
| Федоров |
 |
Резудьтат – все строки
2. Отобрать сотрудников, имеющих оклад более 300 и чей возраст не превышает 40.
| A | B | C | D | E | F | |
| Ф.И.О. | Оклад | Возраст | Оклад | Возраст | ||
| Иванов | >300 | <40 | ||||
| Петров | ||||||
| Сидоров | ||||||
| Федоров |
 |
Результат
| A | B | C | D | E | F | |
| Ф.И.О. | Оклад | Возраст | Оклад | Возраст | ||
| Иванов | >300 | <40 | ||||
| Федоров | ||||||
Вычисляемые условия
Вычисляемое условие является разновидностью расширенного фильтра. В этом случае диапазон условий в первой строке должен содержать любое название отличное от названий столбцов списка или пустую ячейку, во второй – логический оператор (вводить после знака =), в левой части которого должна быть координата первой ячейки исследуемого столбца, а в правой – координата ячейки вычисляемого значения с абсолютными ссылками.
Правила для диапазона вычисляемого условия:
1. Заголовок вычисляемого условия должен отличаться от любого заголовка столбца.
2. Ссылки на ячейки вне списка д.б. абсолютными (ссылки в вычисляемом условии также).
3. Ссылки на ячейки списка д.б. относительными.
Пример
Найти всех сотрудников, у которых заработная плата выше среднего оклада.
| A | B | C | |
| среднее | |||
| =СРЗНАЧ(B5:B8) | =B5>$A$2 | ||
| Ф.И.О. | Оклад | Возраст | |
| Иванов | |||
| Петров | |||
| Сидоров | |||
| Федоров |
 |
Результат
| A | B | C | |
| среднее | |||
| =СРЗНАЧ(B5:B8) | =B2>$E$4 | ||
| Ф.И.О. | Оклад | Возраст | |
| Иванов | |||
| Федоров | |||
Команда Итоги и ее функции
Меню Данные ® команда Итоги
Назначение окон:
· "При каждом изменении в: " – выбор переменной группировки.
· "Операция" – выбор функции.
· "Добавить итоги по:" – выбор переменной для которой применить "Операцию".
Назначение функций (флажков):
· "Заменить текущие итоги" – флажок устанавливают для замены одной операции другой без добавления подгрупп.
· "Конец страницы между группами" - флажок устанавливают для вывода данных каждой группы на отдельной странице.
· "Итоги под данным" - флажок устанавливают для вывода итоговых результатов в конце данных (в противном случае – над данными).
Кнопка "Убрать все" – удалить промежуточные итоги.