Задания для индивидуальной работы. Цель работы:научиться работать со списками - базами данных 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. Как создаются вложенные итоги?