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

Построение и обработка списков

Что осваивается и изучается?

Списки. Сортировка списков. Автофильтры и расширенные фильтры. Подведение промежуточных итогов.

Задание 1.	На листе 1 создайте список (табличную базу данных) - телефонный справочник:
	Выполнить сортировку справочника:
	· по возрастанию номеров телефонов; · по алфавитному порядку фамилий.

Методические указания

v Создание списка

1. Создайте название, заголовки таблицы и границы.

2. Вставьте записи.

Важно. Убедитесь в том, что первая строка каждого столбца списка содержит название, а сами столбцы — однотипные данные, и что пустые строки или пустые столбцы в списке отсутствуют.

v Быстрая сортировка

1. Выделите одну ячейку в столбце, по которому требуется выполнить сортировку.

2. Нажмите кнопку , чтобы выполнить сортировку по возрастанию (от А до Я или от наименьшего числа до наибольшего).

3. Нажмите кнопку , чтобы выполнить сортировку по убыванию (от Я до А или от наибольшего числа до наименьшего).

Задание 2.

· Добавьте в телефонный справочник столбец «Примечания». В каждую запись справочника в столбец «Примечания» запишите одно из слов «очень важный», «важный», «необходимый». · Примените многоуровневую сортировку: выполнить сортировку справочника сначала по степени важности телефонов и затем по алфавитному порядку фамилий.

Методические указания

v Сортировка по указанным условиям

1. Выделите одну любую ячейку в таблице.

2. На вкладке Данные в группе Сортировка и фильтр нажмите Сортировать.

Откроется диалоговое окно Сортировка.

· В списке Сортировать по выберите первый столбец, по которому нужно выполнить сортировку.

· В списке Сортировка выберите пункт Значения.

· В списке Порядок выберите порядок сортировки.

·
Нажмите Добавить уровень, чтобы определить следующий столбец, по которому нужно выполнить сортировку.

 

 

Задание 3.

· В каждую запись справочника в столбец «Примечания» добавьте цвет выделения текста: · Примените многоуровневую сортировку по столбцу «Примечания», используя цвет ячейки.

Методические указания

v Сортировка по цвету ячейки

1. Выделите одну любую ячейку в таблице.

2. На вкладке Данные в группе Сортировка и фильтр нажмите Сортировать.

3. Откроется диалоговое окно Сортировка.

· В списке Сортировать по выберите первый столбец, по которому нужно выполнить сортировку.

· В списке Сортировка выберите пункт Цвет ячейки.

· Раскройте список Порядок и выберите цвет ячейки.

· Нажмите Добавить уровень и снова выберите этот же столбец:

Задание 4.	Используя автофильтр, выделите записи телефонного справочника с помощьювыбора значений и поиска:
	· найти телефон Андреева А.А.; · найти телефоны всех, кто проживает на улице Плеханова; · найти всех тех, у кого номера телефонов начинаются с «222»; · найти телефоны всех тех, у которых номер квартиры заканчивается числом «13».

Методические указания

C помощью фильтрации данных в таблице можно быстро находить нужные значения.

v Включение автофильтра

1. На вкладке Данные в группе Сортировка и фильтр щелкните элемент Фильтр.

Примечание. Чтобы после использования автофильтр выключить, щелкните элемент Фильтр ещё раз.

2. Щелкните стрелку в заголовке столбца, чтобы отобразить список, в котором можно выбрать фильтр.

v Фильтрация с помощью выбора значений и поиска

Самым быстрым способом фильтрации является выбор значений из списка.

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

2. Чтобы выполнить поиск по тексту в столбце, введите в поле Поиск текст или число. При этом можно использовать подстановочные знаки, например:

· звездочку (*)
· вопросительный знак (?)

 

Задание 5.

С помощью пользовательского автофильтра выделить записи телефонного справочника. · Выделить записи, у которых номер телефона больше 222–30–50 и меньше 230–50–50; затем среди выделенных записей выделить записи, в которых фамилии начинаются с буквы «Б». · Выделить записи, у которых номер телефона содержит во второй группе цифры 33 или 34, например, 260–33–40; затем среди выделенных записей выделить записи о тех, которые проживают на проспектах (а не на улицах). · Отобразить записи, в которых улица или проспект начинается с буквы «П». · Найти телефон Андреева, который проживает на проспекте Пушкина.

Методические указания

Выполнять фильтрацию можно по числовым или текстовым значениям или по цвету ячеек, к тексту или фону которых применено цветное форматирование.

Выполнять фильтрацию можно по одному или нескольким столбцам данных.

v Фильтрация данных с указанием условий

Используя условия, можно создавать пользовательские автофильтры, сужающие диапазон отображаемых данных.

1. Щелкните стрелку в заголовке столбца, чтобы отобразить список, в котором можно выбрать фильтр.

2. Выберите в списке элемент Числовые фильтры или Текстовые фильтры. Появится меню, с помощью которого можно выполнить фильтрацию с использованием различных условий.

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

Задание 6.

С помощью расширенного фильтра скопировать «очень важные» записи телефонного справочника. Список этих записей разместить на том же листе, начиная с ячейки А18.

Методические указания

Если при использовании автофильтров критерии фильтрации формулировались в диалоговом режиме, то при использовании расширенного фильтра придется задавать их заранее.

1. На листе вне списка данных создайте список с условиями отбора (маленькую таблицу, в которой в строке заголовков будет часть заголовков списка данных, а вместо самих данных – условия их отбора):

2. На вкладке Данные в группе Сортировка и фильтр щелкните элемент Дополнительно.

3. В поле Диапазон условий вставьте ссылку на список с условиями отбора.

4. Установите переключатель «скопировать результат в другое место » и в поле Поместить результат в диапазон введите ссылку на заданную ячейку А18:

 

Задание 7.	На листе 2 создайте список (табличную базу данных) реализации товаров следующего вида:
	Подведите промежуточные итоги по фирмам в стоимостном выражении.

Методические указания

С помощью команды Промежуточные итоги можно автоматически подсчитать промежуточные и общие итоги в списке для заданного столбца - данные в столбце группируются и по каждой группе подводятся итоги.

Важно:

§ Убедитесь в том, что первая строка каждого столбца диапазона данных, для которых рассчитываются промежуточные итоги, содержит название, а сами столбцы — однотипные данные, и что пустые строки или пустые столбцы в диапазоне отсутствуют.

§ Отсортируйте столбец, содержащий данные, по которым требуется выполнить группировку, - выделите этот столбец и на вкладке Данные в группе Сортировка и фильтр нажмите кнопку Сортировка от А до Я или Сортировка от Я до А.

v Вставка промежуточных итогов

1. Выделите ячейку в списке.

2. На вкладке Данные в группе Структура выберите Промежуточный итог

Появится диалоговое окно Промежуточные итоги.

3. В поле При каждом изменении в выберите столбец для подсчета итогов. В приведенном выше примере нужно выбрать столбец Фирма.

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

5. В поле Добавить итоги по установите флажок для каждого столбца, содержащего значения, по которым необходимо подвести итоги. В приведенном выше примере нужно выбрать флажок Объем продаж.

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

v Удаление промежуточных итогов

1. Выделите ячейку в диапазоне, который содержит промежуточные итоги.

2. На вкладке Данные в группе Структура выберите Промежуточный итог.

3. В диалоговом окне Промежуточные итоги нажмите кнопку Удалить все

 

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

Задание 1С.	На листе 1С, используя построенный телефонный справочник, отобразите и снабдите соответствующими заголовками записи, у которых:
	· номер дома начинается с «1»; · номер дома равен «13»; · номер квартиры равен 13; · номер дома равен «12», а номер квартиры равен «13»; · номер дома и номер квартиры равен «13» или «17».

 

Задание 2С.

На листе 2С, используя список «Реализация товаров», подведите промежуточные итоги в стоимостном выражении: · по месяцам; · по продукции.

 

Задание 3С.	Скопируйте на лист 3С файл Кадры.xlsx. Используя этот список:
	· отобразите список сотрудников, у которых не введена дата рождения; · отобразите список сотрудников, у которых не введена дата зачисления; · заполните пустые даты произвольными значениями; · дополните список полями «ФИО», «ВОЗРАСТ», и «СТАЖ» и запишите формулы, рассчитывающие соответствующие значения.

 

Задание 4С.	Используя список листа 3С на листе 4С, отобразите и снабдите соответствующими заголовками:
	· 5% служащих, больше всего отработавших на фирме; · три фамилии самых молодых служащих; · список сотрудников, родившихся в 1964 году; · список сотрудников, родившихся в мае месяце; · список сотрудников, у которых фамилия начинается с символа «А»; · список сотрудников, у которых фамилия и имя начинаются с символа «И»; · список сотрудников, у которых фамилия, имя и отчество начинаются с символа «И»; · список сотрудников, у которых фамилия и имя начинаются с одинакового символа; · список сотрудников, у которых фамилия, имя и отчество начинаются с одинакового символа; · список специальностей, служащих этой фирмы; · список значений поля ОБРАЗОВАНИЕ, отсортированный в соответствии с образованием, начиная с «высшее»; · список сотрудников, для этой фирмы, отсортированный в соответствии с занимаемой должностью.