Задания для индивидуальной работы. Цель работы:научиться работать со списками - базами данных Excel
Лабораторная работа № 6.
Работа со списками
Цель работы:научиться работать со списками - базами данных Excel.
Задание 1.Изучите сортировку списков. Методические указания.
Одной из типичных задач, выполняемых с помощью электронных таблиц (ЭТ), является ведение и анализ списков. Для достижения максимальной эффективности при работе со списками необходимо соблюдать следующие правила:
1. Каждый столбец списка должен содержать однородную информацию.
2. Первая строка или две верхних строки списка должны содержать метки, описывающие назначение соответствующего столбца.
3. Список не должен содержать пустых строк и столбцов.
4. Список должен быть отделен от других данных по крайней мере одной пустой строкой и одним пустым столбцом.
5. Если всему списку, включая заголовки столбцов, присвоить имя База_данных,то при этом добавленные строки автоматически включаются в эту базу.
6. Если первая и остальные строки различаются параметрами (данные и форматирование), то первая принимается за заголовок.
Если задано имя базы, можно использовать функцию ИНДЕКС().Например, ИНДЕКС(База^цанных;3;4)или ИНДЕКС(База_данных;2;2).Эти функции возвращают значения элементов базы данных (БД), находящихся на пересечении третьей строки и четвертого столбца или второй строки и второго столбца.
Сортировка списка по столбцампроизводится по следующему алгоритму:
1.Выбрать любую ячейку в списке. При выделении одной ячейки Excel автоматически определяет границы списка и строку заголовков.
2.Выполнить команду Данные|Сортировка.
3.Вдиалоговом окне Сортировка диапазонанажать кнопку Параметры.В диалоговом окне Параметры сортировкиустановить опцию столбцы диапазонаи щелкнуть ОК.Если необходимо, то в списке Сортировка по первому ключувыбрать нужный порядок сортировки.
4. В диалоговом окне Сортировка диапазонав раскрывающемся писке Сортировать повыбрать Строка 1и нажать радиокнопку по возрастаниюили по убыванию.Щелкнуть ОК.
Сортировка списка по строкамвыполняется по алгоритму:
1.Выбрать любую ячейку в списке.
2.Выполнить команду Данные|Сортировка.
3.В диалоговом окне Сортировка диапазонащелкнуть кнопку Параметры,в диалоговом окне Параметры сортировкиустановить опцию Строки диапазонаи щелкнуть ОК.Если необходимо, то в списке Сортировка по первому ключувыбрать нужный порядок сортировки.
4.В диалоговом окне Сортировка диапазонав раскрывающемся списке Сортировать повыбрать столбец, по которому следует провести сортировку.
Сортировка по нескольким столбцампроизводится путем выбора нужных столбцов в диалоговом окне Сортировка диапазонав раскрывающихся списках Сортировать по, Затем по, В последнюю очередь, по.Сортировка более чем по трем столбцам делается последовательно: сначала наименее важные столбцы, затем следующие по важности.
Сортировка по стандартному или пользовательскому спискам(только для первого уровня) производится в диалоговом окне Параметры сортировкис помощью раскрывающегося списка Сортировка по первому ключу.
Сортировка части списка/Еслиперед сортировкой была выделена одна ячейка, то Excel просматривает область вокруг нее и выделяет непрерывный интервал данных, который и сортирует. Для сортировки части списка следует выделять только нужные строки и столбцы.
Задание 2.Изучите анализ списков с помощью автофильтра.
Методические указания.
Анализ списков с помощью автофильтра выполняется по команде Данные|Фильтр|Автофильтрпосле выделения любой ячейки списка. Если для построения критерия нужен только один столбец, то следует выделить заголовок фильтруемого столбца, нажать клавиши Shift+Ctrl+стрелка внизи выполнить команду Автофильтр.По такой же схеме выполняется построение критерия по нескольким столбцам.
Поиск пустых ячеек.Если столбец содержит пустые ячейки, то в нижней части раскрывающегося списка автофильтра появляются элементы Пустыеи Непустые.Для поиска пустых ячеек в качестве критерия отбора следует задать элемент Пустые.Для того чтобы скрыть пустые ячейки, следует задать элемент Непустые.
Настройка пользовательского автофильтра для более сложного критериявыполняется в диалоговом окне Пользовательский автофильтр,которое вызывается при выборе элемента Условиераскрывающегося списка автофильтра.
Примечание. В качестве шаблона в пользовательских критериях можно использовать символы «*» для представления любой последовательности символов и «?» для представления любого отдельного символа.
Для удаления автофильтрадля одного столбца следует раскрыть соответствующий список автофильтра и выбрать в нем пункт Все.Для отмены всех примененных автофильтров нужно выполнить команду Данные|Фильтр|Отобразить все.Для удаления всех автофильтров и их кнопок следует еще раз выполнить команду Данные|Фильтр|Авто-фильтр,удалив таким образом флажок рядом с названием команды.
Копирование фильтрованных данныхв другую часть р/л или на другой р/л осуществляется стандартным способом.
Задание3. Изучите анализ списков с помощью расширенного фильтра и вычисляемых критериев.
Методические указания.
Расширенный фильтр в отличие от автофильтра позволяет создавать:
1. Критерии с условиями по нескольким столбцам, связанными операцией ИЛИ.
2. Критерии с тремя и более условиями для заданного столбца, связанными по крайней мере одной операцией ИЛИ.
3. Вычисляемые критерии.
Примечания:
1.Текстдля создания критерия надо вводить в следующем виде: ="=*<окончание слова>", или ="=<слово полностью>", или ="=<Первая буква фамилии>*".
2.Числанужно вводить без знака «=», например, >=1000 или < 500.
Создание расширенного фильтравыполняется по следующему алгоритму:
1. Копировать строку заголовков столбцов и поместить ее справа от списка, отделив хотя бы одним пустым столбцом. Заголовки в диапазоне условий должны в точности совпадать с заголовками столбцов в списке.
2.Под скопированной строкой заголовков столбцов в строках, начиная со второй, вводить в соответствующие столбцы нужные критерии. Диапазон условий должен содержать по крайней мере две строки. В диапазоне условий нужно использовать заголовки только тех столбцов, которые используются в условиях отбора.
3.Операция ИЛИсоздается с помощью строк, а операция И — с помощью одноименных столбцов. Одноименный столбец должен быть создан в скопированной строке заголовков столбцов рядом с заголовком основного столбца.
4. Анализ списка с помощью расширенного фильтра выполняется по команде Данные|Фильтр|Расширенный фильтр. Вдиалоговом окне Расширенный фильтрвводятся Исходныйдиапазон (определяется автоматически, если выделена любая ячейка списка) и Диапазон условий(выделяется с помощью мыши).
Для выполнения лабораторной работы необходимо создать список, состоящий не менее чем из 50 строк, или использовать готовую БД. Список может иметь примерно такой вид. Назовем р/л, на котором расположен этот список, — Поставки.
Рассмотрим пример использования расширенного фильтра: найти все события сбыта продукции от 3000 до 5000 ед. в 2005 и 2006 годах. Действуем по алгоритму:
1. На новый р/л копировать приведенный выше список.
2. Копировать строку заголовка справа от списка.
| Год | Дата | Месяц | Объем | Поставщик | Продукция | Район | Сбыт | Цена за 1 ед. |
| 05.01.2005 | Январь | Малафеев | Напитки | Западный | ||||
| 12.01.2005 | Январь | Малафеев | Напитки | Западный | ||||
| 19.01.2005 | Январь | Малафеев | Напитки | Западный | ||||
| 26.01.2005 | Январь | Малафеев | Бакалея | Западный | ||||
| 02.01.2006 | Январь | Кудрин | Напитки | Западный | ||||
| 05.01.2006 | Январь | Малафеев | Напитки | Центр | ||||
| 10.01.2006 | Январь | Малафеев | Напитки | Западный | ||||
| 15.01.2006 | Январь | Антипов | Напитки | Западный | ||||
| 20.01.2006 | Январь | Малафеев | Бакалея | Западный | ||||
| 09.02.2005 | Февраль | Антипов | Бакалея | Западный | ||||
| 16.02.2005 | Февраль | Антипов | Бакалея | Западный | ||||
| 23.02.2005 | Февраль | Малафеев | Консервы | Северный | ||||
| 02.03.2005 | Февраль | Антипов | Молоко | Западный | ||||
| 09.03.2005 | Февраль | Антипов | Молоко | Западный | ||||
| 06.04.2005 | Март | Антипов | Бакалея | Нахичевань | ||||
| 13.04.2005 | Март | Антипов | Бакалея | Нахичевань | ||||
| 20.04.2005 | Март | Малафеев | Молоко | Западный | ||||
| 27.04.2005 | Март | Малафеев | Молоко | Западный | ||||
| 04.04.2006 | Март | Антипов | Бакалея | Нахичевань | ||||
| 08.12.2008 | Декабрь | Малафеев | Мясо | Центр | ||||
| 13.12.2008 | Декабрь | Малафеев | Мясо | Центр | ||||
| 13.12.2009 | Декабрь | Антипов | Мясо | Западный | ||||
| 19.12.2009 | Декабрь | Медведев | Напитки | Нахичевань | ||||
| 25.12.2009 | Декабрь | Медведев | Напитки | Нахичевань |
3.Вставить новый столбец рядом со столбцом Сбыти копировать заголовок Сбытв новый столбец.
4.В первой строке ниже заголовка ввести в столбцы Сбыт<3000 и>5000, в столбец Годввести 2005.
5.Во вторую строку ниже заголовка в столбец Годввести 2006, в столбцы Сбытввести ту же информацию.
6.Выполнить п. 4 алгоритма создания расширенного фильтра.
7.Результат работы фильтра должен иметь вид, представленный в таблице:
| Год | Дата | Месяц | Объем | Поставщик | Продукция | Район | Сбыт | Цена за 1 ед. |
| 05.01.2005 | Январь | Малафеев | Напитки | Западный | ||||
| 05.01.2006 | Январь | Малафеев | Напитки | Центр | ||||
| 16.02.2005 | Февраль | Антипов | Бакалея | Западный | ||||
| 27.04.2005 | Март | Малафеев | Молоко | Западный | ||||
| 04.04.2006 | Март | Антипов | Бакалея | Нахичевань |
При расширенном фильтре:
| Год | Дата | Месяц | Объем | Поставщик | Продукция | Район | Сбыт | Сбыт |
| >3000 | <5000 | |||||||
| >3000 | <5000 |
Вычисляемые критерии.Три правила помогут избежать ошибок при использовании вычисляемого критерия:
1.Заголовок столбца над вычисляемым критерием не должен совпадатьс заголовком какого-либо столбца в анализируемом списке. Он может быть пустым или содержать любой другой текст.
2.Ссылки на ячейки вне списка должны быть абсолютными.
3.Ссылки на ячейки внутри списка должны быть относительными, например, =H2>=$V$10, где V10- результат вычисления по формуле.
Добавим в приведенный пример к расширенному фильтру вычисляемый критерий. В ячейке V2 вычислим средний объем сбыта с помощью функции =СРЗНАЧ(Н2:Н25), ав ячейки U2и U3введем формулу =H2>=$V$2. При этом диапазон расширенного фильтра увеличится и станет равным К1:Ш.
Результат работы фильтра с вычисляемым критерием должен быть таким:
| Год | Дата | Месяц | Объем | Поставщик | Продукция | Район | Сбыт | Сбыт |
| 27.04.2005 | Март | Малафеев | Молоко | Западный |
Использование формы данныхпроизводится по алгоритму:
1.Выделить любую ячейку в списке.
2.Выполнить команду Данные|Форма,нажать кнопку Критерии.
3.Заполнить поля ввода для создания списка критериев. Вводить можно только простые критерии.
4.Нажать кнопку Далее.
5. Пролистать выделенные записи, используя кнопки Далееи Назад.
Задание 4.Изучите работу с итогами.
Методические указания.
Для анализа строк, прошедших через фильтр, можно воспользоваться командой Данные|Итоги,предварительно скопировав результаты на чистый р/л. Рассмотрим работу с итогами на примере. Пусть с помощью Автофильтраиз списка требуется выделить поставки Бакалеии Напитковв объемах от 3000 до 5000 ед.в Западныйрайон и Центр.
Выполним следующий алгоритм:
1.Копировать результаты работы фильтра на новый р/л.
2.Выбрать ячейку в столбце Год.
3.Выполнить команду Сортировка.
4.Выполнить команду Данные|Итоги.
5.В диалоговом окне Промежуточные итогив списке При каждом изменении ввыбрать столбец Год.В списке операция выбрать Сумма, всписке Добавить итоги по — Сбыт.
6.Флажок Заменить текущие итогиубрать, флажок Итоги под даннымиустановить.
7.ОК.
При создании итогов производится группировка списка. Вычисления итогов производится с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ(),которая в отличие от функции СРЗНАЧигнорирует все скрытые ячейки в отфильтрованном списке.
Примечание. Функция ПРОМЕЖУТОЧНЫЕ ИТОГИ()(категория Математические)возвращает промежуточный итог в список. Обычно проще создать промежуточные итоги с помощью команды Данные|Итоги.Но если список с промежуточными итогами уже создан, его можно модифицировать, редактируя формулу с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ().
Синтаксис функции:
ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер__функции;ссылка1;ссылка2;...),где
• номер функции— это число от 1 до 11, которое указывает, какую функцию использовать при вычислении итогов внутри списка.
| Номер функции | Операция |
| СРЗНАЧ | |
| СЧЕТ | |
| СЧЁТЗ | |
| МАКС | |
| МИН | |
| ПРОИЗВЕД | |
| СТАНДОТКЛОН | |
| СТАНДОТКЛОШТ | |
| СУММ | |
| ДИСП | |
| ДИСПР |
• ссылка1;ссылка2;...— от 1 до 29 интервалов или ссылок, для которых подводятся итоги.
Примечания:
1.Если уже имеются формулы подведения итогов внутри аргументов ссылка1;ссылка2;...(вложенные итоги), то эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.
2.Функция ПРОМЕЖУТОЧНЫЕ.ИТОГЩ)игнорирует все скрытые строки, которые получаются в результате фильтрации списка. Это важно в том случае, когда нужно подвести итоги только для видимых данных, которые получаются в результате фильтрации списка.
Так, функция ПРОМЕЖУТОЧНЫЕ ИТОГИ(9;СЗ:С5)подведет итоги для ячеек СЗ:С5,используя функцию СУММ.
Для создания нескольких итоговых формул в одном столбцеследует при выполнении алгоритма сбросить флажок Заменить текущие итоги.
Создание вложенных итоговвыполняется по следующему алгоритму:
1.Выделить любую ячейку списка.
2.Выполнить команду Данные|Итоги,щелкнуть кнопку Убрать все.
3.Найти, например, все поставки мяса.
4.Отсортировать полученный список сначала по поставщикам,затем по продукции.
5.Выполнить команду Данные|Итоги. Всписке При каждом изменении ввыбрать столбец Поставщик. Всписке операция— Сумма.В списке Добавить итоги по — Объем.
6.ОК.
7.Выполнить команду Данные|Итоги.В списке При каждом изменении ввыбрать столбец Продукция. Всписке операция— Сумма.В списке Добавить итоги по — Объем.Флажок Заменить текущие итоги— сбросить.
8.ОК.
При вычислении вложенных итогов ранжирование должно соответствовать выполненной сортировке.
Задания для индивидуальной работы
1. Сортируйте список по столбцам в алфавитном порядке.
2. Сортируйте часть списка по строкам в алфавитном порядке.
3. Сортируйте список по четырем столбцам: Год, Месяц, Дата, Поставщик.
4. Сортируйте столбцы списка по первому ключу, определяемому полными названиями дней недели.
5. Сортируйте строки списка по столбцам Поставщик, Продукция, Район в указанном порядке.
6. В списке Поставки выделите в разных частях несколько ячеек и в контекстном меню выполнить команду Очистить содержимое. Найдите образовавшиеся пустые ячейки.
7. Найдите все поставки бакалеи и напитков Антиповым в 2005 и 2006 годах.
8. Найдите все поставки мяса и консервов с объемом от 500 до 1000 для поставщиков, фамилии которых оканчиваются на -ев.
9. Найдите в 2005 и 2006 годах все поставки объемом больше 6000 и меньше 10Q0 для поставщиков, фамилии которых начинаются с буквы А по букву М включительно.
10.Найдите все поставки напитков и молока с объемом от 1000 до 3000 для поставщиков, фамилии которых оканчиваются на -ов.
11.Найдите все поставки молока и бакалеи с объемом от 5000 до 6000 для поставщиков, фамилии которых оканчиваются на -ин.
12.Найдите в 2007 и 2008 годах все поставки объемом больше 6000 и меньше 1000 в Нахичевань и Северный район.
13.Найдите в 2006 и 2009 годах все поставки мяса объемом больше 2000 и меньше 1000 для поставщиков, фамилии которых начинаются с буквы К по букву М включительно.
14.Копируйте результаты работы пользовательского фильтра предыдущего задания на новый р/л и удались автофильтр.
15.С помощью расширенного фильтра найдите все события сбыта от 3000 до 7000 единиц в 2006 и 2007 годах мяса и напитков в Центре и Северном районах.
16.С помощью расширенного фильтра найдите все события поставки в 2005 и 2008 годах бакалеи и напитков объемом от 1000 до 5000 единиц в Западный и Северный районы поставщиками, фамилии которых оканчиваются на -ов и -ин.
17.С помощью расширенного фильтра найдите все события поставки в 2005 и 2008 годах бакалеи и напитков объемом от 1000 до 5000 единиц в Западный и Северный районы. Объем сбыта этих поставок должен превышать на 25% средний объем сбыта.
18.С помощью формы найти все события поставки напитков поставщиком Малафеевым в 2005 году в Западный район. 
С помощью формы найдите все события сбыта бакалеи поставщиком Антиповым в 2006 году в Нахичевань.
19.Найдите итоги по годам и поставкам в 2007 и 2008 годах все объемы больше ,6000 и меньше 1000 в Нахичевань и Северный район.
20.Найдите итоги всех событий поставки в 2005 и 2008 годах бакалеи и напитков объемом от 1000 до 5000 единиц в Западный и Северный районы поставщиками, фамилии которых оканчиваются на -ов и -ин.
21.Найдите итоги всех событий сбыта от 3000 до 7000 единиц в 2006 и 2007 годах мяса и напитков в Центре и Северном районе.
22.Найдите количество сделок по мясу и средний объем сделок по мясу с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИО.
23.Найдите объем сбыта по напиткам и средний объем сбыта по напиткам с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИО.
24.Создайте вложенные итоги по сумме объемов поставок поставщиками и по суммам поставленных объемов продукции.
Контрольные вопросы
1. Какие правила необходимо соблюдать при работе со списками?
2. При каком условии добавленные к списку строки автоматически включаются в этот список?
3. Как выполняется сортировка списка по строкам и столбцам?
4. Как выполняется сортировка более чем по трем столбцам ?
5. По какой команде выполняется анализ списков с помощью авто-фильтра?
6. Как найти пустые ячейки с помощью автофильтра?
7. Как выполняется настройка пользовательского автофильтра для сложного критерия?
8. Как производится удаление Автофильтра?
9. Что позволяет создавать расширенный фильтр?
10.Как нужно вводить в расширенный фильтр текст и числа?
11.Как выполняется создание расширенного фильтра?
12.Для чего используются вычисляемые критерии и какие три правила необходимо соблюдать при использовании вычисляемого критерия?
13.Как создается вычисляемый критерий?
14.В каких случаях применяются формы данных?
15.По какому алгоритму выполняется анализ строк с помощью команды Итоги?
16.Для чего используется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИО?
17. Что нужно предпринять, чтобы подвести итоги только для видимых
данных, которые получаются в результате фильтрации списка?
18. Как создаются вложенные итоги?