Списки и базы данных в MS Excel

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

Обработка списков в MS Excel

Цель: научиться вводить и обрабатывать данные в списках.

Постановка задачи: составить список. Обработать данные, осуществить сортировку и поиск данных в списке.

Ход работы

Создание таблицы. На листе 1 рабочей книги создайте список (рис.1). Столбец Дата рождения имеет формат Дата, столбец Оклад – денежный формат:

Рис. 1.

Введите еще 5 записей в таблицу (рис.2).

Рис.2

Использование команды Специальная вставка для изменения массива чисел. Предположим, оклады сотрудников увеличились на 20% (т.е. увеличились в 1,2 раза). Поэтому все значения в столбце Оклад необходимо увеличить в 1,2 раза. Введем в какую-нибудь ячейку на свободном месте рабочего листа, например, в ячейку М1, число 1,2. Скопируем его в буфер обмена (в контекстном меню, вызванном на ячейке М1, выбрать Копировать). Выделим диапазон с окладами, в контекстном меню выделенного диапазона выберите команду Специальная вставка. В диалоговом окне выбрать операцию умножить. Все оклады увеличатся в 1,2 раза. Удалим содержимое М1 (больше оно не понадобится).

Начисление премии. Начислим каждому сотруднику премию в размере 10% от оклада (при этом оставим возможность изменения процента премии). Вставим две пустые строки перед таблицей: выделим 1 и 2 строки, в контекстном меню выделенных строк выбрать команду Вставить. В ячейку F1 введем слово Премия, в G1 – 10%. Вторую строку оставляем пустой, чтобы список был ограничен пустыми ячейками. После столбца Оклад добавьте два пустых столбца, дайте заголовки Премия и Всего, заполните соответствующими формулами: в ячейку I4 введем =H4*$G$1, в ячейку J4 введем =СУММ(H4:I4). Скопируйте формулы в ячейки I5:I11 и J5:J11.

Продолжите заполнение таблицы (рис.3). Скопируйте формулы в добавленные ячейки столбцов Премия и Всего.

Рис. 3

Закрепление областей таблицы. Просматривать таблицу неудобно, особенно если она содержит много записей: если перейти к последним записям, то с экрана исчезнут заголовки столбцов; если нужно посмотреть телефоны, то пропадают фамилии. Нужно, чтобы заголовки столбцов (3-я строка) и фамилии (столбец А) постоянно присутствовали на экране. Для этого выделите ячейку В4, перейдите на вкладку Вид, в группе Окно из раскрывающегося списка Закрепить области выберите команду Закрепить области. На рабочем листе появятся горизонтальная и вертикальная разделительные полосы. Теперь можно просматривать последние строки и столбцы, не теряя из виду информацию, содержащуюся в заголовках столбцов и первом столбце. Чтобы убрать закрепление, выполните команду: Вид - Закрепить области - Снять закрепление областей.

Дополните таблицу записями (рис.4). Столбцы Адрес и Телефон заполните самостоятельно.

Рис. 4

Индексация списка. Используется для восстановления исходного порядка записей при работе со списками. Вставьте пустой столбец перед списком. В ячейку А3 введите №, в А4 ввести 1, протянуть маркер автозаполнения ячейки А4 правой кнопкой мыши до последней записи, в контекстном меню выбрать команду Заполнить. Столбец А заполнится порядковыми номерами.

Сортировка. Списки MS Excel можно сортировать по одному или нескольким ключам, а также в пользовательском порядке. Для списков ключ – это поле.

Для сортировки по одному ключу выделите какую-либо ячейку в столбце, по которому производится сортировка, на вкладке Главная в разделе Редактирование из раскрывающегося списка кнопки Сортировка и фильтр (или на вкладке Данные в разделе Сортировка и фильтр) используйте кнопку Сортировка от А до Я (для сортировки по возрастанию) или кнопку Сортировка от Я до А (для сортировки по убыванию).

Задания. Отсортируйте список:

1. по полю Пол по возрастанию.

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

3. по отделам по возрастанию, затем по полу – по убыванию, по фамилиям – по возрастанию.

4. по фамилиям, именам, отчествам – по возрастанию.

При совпадении данных в одном столбце можно использовать вложенную сортировку по двум, трем и т.д. (до 64) столбцам. Для этого на вкладке Главная в разделе Редактирование из раскрывающегося списка кнопки Сортировка и фильтр используйте команду Настраиваемая сортировка (или на вкладке Данные в разделе Сортировка и фильтр нажать кнопу Сортировка) (см. рис. 5). Столбец сортировки выбирается из списка столбцов. Порядок сортировки также выбираются из списка.

Для добавления к сортировке следующего столбца нажмите кнопку Добавить уровень, а затем выберите порядок сортировки. Можно одновременно осуществлять сортировку по 64 столбцам.

Рис. 5

Задания. Отсортируйте список:

1. по полю Пол по возрастанию.

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

3. по отделам по возрастанию, затем по полу – по убыванию, по фамилиям – по возрастанию.

4. по фамилиям, именам, отчествам – по возрастанию.

5. по отделам по возрастанию, по полу - по убыванию, по убыванию количества детей, а для одинакового количества детей по алфавитному порядку фамилий.

Дополнительно:

1. используя функцию ГОД из категории ДАТА И ВРЕМЯ преобразуйте дату рождения в год рождения каждого работника, затем выполните сортировку по отделам, а внутри отделов - по возрастанию года рождения.

2. используя функцию МЕСЯЦ и ДЕНЬ из категории ДАТА И ВРЕМЯ, вычислите числовое значение месяца и дня рождения сотрудников. Составьте для каждого отдела график празднования дней рождений: отсортируйте список по отделам, внутри отделов – по месяцам рождений, внутри месяцев – по дням.

Часто после сортировки в алфавитном порядке индексация списка меняется. Чтобы пронумеровать отсортированный список по порядку, необходимо отсортировать по возрастанию только столбец №, а остальную информацию не сортировать. Для этого выделить содержимое столбца № вместе с заголовком, применить к нему сортировку от меньшего к большему. MS Excel выведет диалоговое окно Обнаружены данные вне указанного диапазона, в котором нужно установить переключатель сортировать в пределах указанного диапазона (см. рис. 6).

Рис. 6

Пользовательский порядок сортировки. Для такой сортировки необходимо задать пользовательский список и сортировать в соответствии с порядком элементов в этом списке. Для этого применяем к списку Настраиваемую сортировку, из раскрывающегося списка столбца Порядок выбрать Настраиваемый список (см. рис. 7). В появившемся окне Списки выбрать элемент НОВЫЙ СПИСОК (или щелкнуть по кнопке Добавить), ввести элементы списка в том порядке, в котором нужно сортировать данные в таблице. После завершения создания списка нажать ОК, пользовательский порядок сортировки отобразится в диалоговом окне Сортировка.

Рис. 7

Задание. Отсортируйте список в пользовательском порядке: СКБ, ОГТ, АСУП, ИВЦ.

Автофильтр. Отфильтровать список – это значит, показать только те записи, которые удовлетворяют заданному критерию. Для фильтрации списка на вкладке Главная в разделе Редактирование из раскрывающегося списка кнопки Сортировка и фильтр используйте команду Фильтр (или на вкладке Данные в разделе Сортировка и фильтр нажать кнопу Фильтр). В ячейках, содержащих заголовки столбцов, появляются кнопки со стрелкой, направленной вниз. Щелкнув по кнопке, получим информацию, содержащуюся в столбце (рис. 8). Выбрав одно значение из списка, кнопка изменит вид. Номера строк окрасятся в голубой цвет, что означает, что список подвергся фильтрации. Отменить отбор по критерию можно, еще раз щелкнув кнопку в столбце, по которому отфильтрованы записи, и выбрав пункт Выделить все. Чтобы полностью отменить режим фильтрации, повторно выбрать команду Фильтр.

Рис. 8

Для отбора по нескольким критериям, необходимо произвести фильтрацию по нескольким столбцам. Более сложные критерии отбора записей выбираются из раскрывающегося меню Числовые (Текстовые) фильтры.

Задания. Отфильтруйте список:

1. отобразить список бездетных мужчин из отдела АСУП. Отмените фильтрацию.

2. выберите трех самых молодых работников. Для этого в поле Дата рождения необходимо задать 3 наибольших элементов списка. Отмените фильтрацию.

3. выберите 20% работников с наименьшей зарплатой (в третьем поле в диалоговом окне укажите «% от количества элементов»). Отмените фильтрацию.

4. выберите двух работников с наибольшей зарплатой. Отмените фильтрацию.

5. выберите записи, относящиеся к мужчинам, родившимся до 1965 г

6. найдите записи сотрудников, не имеющих телефона.

Для каждого столбца можно создать критерий, состоящий из одного или двух условий, соединенных логическими операторами И, ИЛИ. В раскрывающемся меню Числовые (Текстовые) фильтры выбрать Настраиваемый фильтр и в диалоговом окне Пользовательский автофильтр (рис. 9) задать условие. При задании условия можно использовать шаблон.

Рис. 9

Задания. Отфильтруйте список:

1. отобразите список сотрудников отделов СКБ и АСУП. Отмените фильтрацию.

2. отобразите список сотрудников, имеющих оклад от 2000 до 2200 руб. Отмените фильтрацию.

3. выведите список мужчин, родившихся в период от 01.01.1960 до 31.12.1969 г. Отмените фильтрацию.

4. выберите из списка многодетных сотрудников (имеющих 3 и более детей). Отмените фильтрацию.

5. выберите из списка сведения о Райковой и Мортикове.. Отмените фильтрацию.

Задания. Осуществите поиск записей по следующим критериям:

1. женщины, имеющие более одного ребенка.

2. фамилии, начинающиеся на Г.

3. имена, начинающиеся на А.

4. сотрудников СКБ с окладом меньше 2000,00 р.