Лабораторная работа 6. Работа со списками

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

Задание 1. Сортировка списков.

Методические указания. Одной из типичных задач, выполняемых с помощью электронных таблиц (ЭТ), является ведение и анализ списков. Для достижения максимальной эффективности при работе со списками необходимо соблюдать следующие правила.

1. Каждый столбец списка должен содержать однородную информацию.

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

3. Список не должен содержать пустых строк и столбцов.

4. Список должен быть отделен от других данных, по крайней мере, одной пустой строкой и одним пустым столбцом.

5. Если всему списку, включая заголовки столбцов, присвоить имя База_данных, то при этом добавленные строки автоматически включаются в эту базу.

6. Если первая и остальные строки различаются параметрами (данные и форматирование), то первая принимается за заголовок.

Если задано имя базы - можно использовать функцию ИНДЕКС(). Например, ИНДЕКС(База_данных;3;4) или ИНДЕКС(База_данных;2;2). Эти функции возвращают значения элементов базы данных (БД), находящихся на пересечении третьей строки и четвертого столбца или второй строки и второго столбца.

Сортировка списка по столбцам производится по следующему алгоритму:

1. Выбрать любую ячейку в списке. При выделении одной ячейки Excel автоматически определяет границы списка и строку заголовков.

2. Выполнить команду Данные|Сортировка.

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

4. В диалоговом окне Сортировка диапазона в раскрывающемся списке Сортировать по выбрать Строка 1 и нажать радиокнопку по возрастанию или по убыванию. Щелкнуть OK.

Сортировка списка по строкам выполняется по алгоритму:

1. Выбрать любую ячейку в списке.

2. Выполнить команду Данные|Сортировка.

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

4. В диалоговом окне Сортировка диапазона в раскрывающемся списке Сортировать по выбрать столбец, по которому следует провести сортировку.

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

Сортировка по стандартному или пользовательскому спискам (только для первого уровня) производится в диалоговом окне Параметры сортировки с помощью раскрывающегося списка Сортировка по первому ключу.

Сортировка части списка. Если перед сортировкой была выделена одна ячейка, то Excel просматривает область вокруг нее и выделяет непрерывный интервал данных, который и сортирует. Для сортировки части списка следует выделять только нужные строки и столбцы.

Задание 2. Анализ списков с помощью автофильтра.

Методические указания. Анализ списков с помощью автофильтра выполняется по команде Данные|Фильтр|Автофильтр после выделения любой ячейки списка. Если для построения критерия нужен только один столбец, то следует выделить заголовок фильтруемого столбца, нажать клавиши Shift+Ctrl+стрелка вниз и выполнить команду Автофильтр. По такой же схеме выполняется построение критерия по нескольким столбцам.

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

Настройка пользовательского автофильтра для более сложного критерия выполняетсяв диалоговом окне Пользовательский автофильтр, котороевызываетсяпри выборе элемента Условие раскрывающегося списка автофильтра.

Примечание. В качестве шаблона в пользовательских критерияхможноиспользовать символы "* " для представления любой последовательности символов и "? " для представления любого отдельного символа.

Для удаления автофильтра для одного столбца следует раскрыть соответствующий список автофильтра и выбрать в нем пункт Все. Для отмены всех примененных автофильтров нужно выполнить команду Данные|Фильтр|Отобразить все. Для удаления всех автофильтров и их кнопок следует еще раз выполнить команду Данные|Фильтр|Автофильтр, удалив, таким образом, флажок рядом с названием команды.

Копирование фильтрованных данных в другую часть р/л или на другой р/л осуществляется стандартным способом.

Задание 3. Анализ списков с помощью расширенного фильтра и вычисляемые критерии.

Методические указания. Расширенный фильтрв отличие от автофильтра позволяет создавать:

1. Критерии с условиями по нескольким столбцам, связанными операцией ИЛИ.

2. Критерии с тремя и более условиями для заданного столбца, связанными, по крайней мере, одной операцией ИЛИ.

3. Вычисляемые критерии.

Примечания:

1. Текст для создания критерия надо вводить в следующем виде: ="=*<окончание слова>", или ="=<слово полностью>", или ="=<Первая буква фамилии>*".

2. Числа нужно вводить без знака "=", например, >=1000 или < 500.

Создание расширенного фильтра выполняется по следующему алгоритму:

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

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

3. Операция ИЛИ создается с помощью строк, а операция И – с помощью одноименных столбцов. Одноименный столбец должен быть создан в скопированной строке заголовков столбцов рядом с заголовком основного столбца.

4. Анализ списка с помощью расширенного фильтра выполняется по команде Данные|Фильтр|Расширенный фильтр. В диалоговом окне Расширенный фильтр вводятся Исходный диапазон (определяется автоматически, если выделена любая ячейка списка) и Диапазон условий (выделяется с помощью мыши).

Для выполнения лабораторной работы необходимо создать список, состоящий не менее, чем из 50 строк, или использовать готовую БД. Список может иметь примерно такой вид. Назовем р/л, на котором расположен этот список, – Поставки.

Рассмотрим пример использования расширенного фильтра: найти все события сбыта продукции от 3000 до 5000 ед. в 2005 и 2006 годах. Действуем по алгоритму.

1. На новый р/л копировать приведенный выше список.

2. Копировать строку заголовка справа от списка.

3. Вставить новый столбец рядом со столбцом Сбыт и копировать заголовок Сбыт в новый столбец.

4. В первой строке ниже заголовка ввести в столбцы Сбыт <3000 и >5000, в столбец Год ввести 2005.

5. Во вторую строку ниже заголовка в столбец Год ввести 2006, в столбцы Сбыт ввести ту же информацию.

6. Выполнить п. 4 алгоритма создания расширенного фильтра.

7. Результат работы фильтра должен иметь вид.

при расширенном фильтре

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

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

2. Ссылки на ячейки вне списка должны быть абсолютными.

3. Ссылки на ячейки внутри списка должны быть относительными, например, =H2>=$V$10, где V10 – результат вычисления по формуле.

Добавим в приведенный пример к расширенному фильтру вычисляемый критерий. В ячейке V2 вычислим средний объем сбыта с помощью функции = СРЗНАЧ(H2:H25), а в ячейки U2 и U3 введем формулу =H2>=$V$2. При этом диапазон расширенного фильтра увеличится и станет равным K1:U3.

Результат работы фильтра с вычисляемым критерием должен быть таким.

Использование формы данных производится по алгоритму.

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

2. Выполнить команду Данные|Форма, нажать кнопку Критерии.

3. Заполнить поля ввода для создания списка критериев. Вводить можно только простые критерии.

4. Нажать кнопку Далее.

5. Пролистать выделенные записи, используя кнопки Далее и Назад.

Задание 4. Работа с итогами.

Методические указания. Для анализа строк, прошедших через фильтр, можно воспользоваться командой Данные|Итоги, предварительноскопировав результаты на чистыйр/л. Рассмотрим работу с итогами на примере. Пусть с помощью Автофильтра из списка требуется выделить поставки Бакалеи и Напитков в объемах от 3000 до 5000 ед. в Западный район и Центр. Выполним следующий алгоритм.

1. Копировать результаты работы фильтра на новый р/л.

2. Выбрать ячейку в столбце Год.

3. Выполнить команду Сортировка.

4. Выполнить команду Данные|Итоги.

5. В диалоговом окне Промежуточные итоги в списке При каждом изменении в выбрать столбец Год. В спискеоперациявыбрать Сумма, в списке Добавить итоги по – Сбыт.

6. Флажок Заменить текущие итоги убрать, флажок Итоги под данными установить.

7. ОК.

При создании итогов производится группировка списка.Вычисления итогов производится с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ(), которая в отличие от функции СРЗНАЧ() игнорирует все скрытые ячейки в отфильтрованном списке.

Примечание. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() (категория Математические) возвращает промежуточный итог в список. Обычно проще создать промежуточные итоги с помощью команды Данные|Итоги. Но если список с промежуточными итогами уже создан, его можно модифицировать, редактируя формулу с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ().

Синтаксис функции:

ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции;ссылка1;ссылка2;...), где

номер функции – это число от 1 до 11, которое указывает, какую функцию использовать при вычислении итогов внутри списка.

ссылка1;ссылка2;... – от 1 до 29 интервалов или ссылок, для которых подводятся итоги.

Поиск по сайту: