Файл: Лабораторный практикум в Excel.doc

Методические указания по выполнению работы

Скопировать лабораторную работу № 4 (Error: Reference source not found). Оформить лист в соответствии с ПРИЛОЖЕНИЕ 3.

Для выполнения лабораторных работ 5-12 исходную таблицу надо преобразовать в базу данных или список.

Преобразование таблицы в список

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

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

2. Список отделяется от данных, расположенных на рабочем листе, со всех сторон пустыми строками и столбцами.

3. В столбце могут быть данные только одного типа.

В приведенном примере строка «ИТОГО» не входит в список, т.к. отделена от списка пустой строкой. Чтобы сделать строку скрытой, ее надо выделить, щелкнуть по ней правой кнопкой мышки и в контекстном меню выбрать команду «Скрыть»

Автофильтрация данных

Поставить курсор в любое место списка и выбрать группу Данныекнопка Фильтр

В списке в строке заголовков появятся кнопки «Автофильтрации».

Фильтрация по точному значению

1. Нажать кнопку «Автофильтра» в любом столбце.

2. В открывшемся списке выбрать нужное точное значение и щелкнуть по нему левой кнопкой мыши (Например: Поставщик=Альтаир).

3. Все строки, которые не содержат «Альтаир» будут скрыты.

4. Полученный результат скопировать и поместить на свободное место.

5. Для возврата к полному списку нажать кнопку «Автофильтрации» в том столбце, по которому проводилась фильтрация и выбрать «Все».

Фильтрация по шаблону.

Под шаблоном будем понимать любой набор символов, которые содержатся (или не должны содержаться) в строке. Фильтрация проводится только по символьным столбцам.

1. Нажать кнопку в любом символьном столбце и выбрать «Условия…»

2. Выбрать в верхней строке нужный оператор сравнения и установить требуемое значение.

Выполнить два вида фильтрации по шаблону:

- по начальным символам (оператор сравнения «начинается с»);

- по подстроке (оператор сравнения «содержит»).

Фильтрация по диапазону значений

Фильтрация выполняется так же, как и по шаблонам, но отличается следующим:

- фильтрация выполняется по числовому столбцу;

- нужно установить два оператора сравнения («больше или равно» и «меньше или равно»), связанных логической функцией «И».

В данном случае фильтрация проводится по диапазону 100<=Цена<=200.

Лабораторная работа № 3

Фильтрация данных. Расширенный фильтр

Цель работы: овладение навыками применения расширенного фильтра.

Задание:

Выполнить фильтрацию исходной таблицы с помощью расширенного фильтра по следующим условиям:

1. Текстовые условия:

- по двум значениям (критерий «ИЛИ»).

2. Числовые условия:

- по числовому значению;

- по диапазону значений (критерий «И» в одном столбце);

- по критерий «И» в двух столбцах;

3. Вычисляемые условия.

Методические указания по выполнению работы

Скопировать лист с лабораторной работой № 5 (Error: Reference source not found) и оформить его в соответствии с ПРИЛОЖЕНИЕ 3.

При работе расширенного фильтра условия отбора задаются в отдельном диапазоне листа – диапазоне условий. Диапазон условий располагается над или под таблицей.

Создание диапазона условий

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

Для фильтрации по текстовым условиям надо поместить в диапазон условий:

- ="=точное значение" – фильтрация по точному значению;

- ="*подстрока* - фильтрация по подстроке;

- начальные символы – фильтрация по начальным символам.

Для задания числовых условий используются символы «=», «>», «<»

Если условия отбора стоят в одной строке, то они связываются функцией «И». Если условия отбора стоят в разных строчках, то они связываются функцией «ИЛИ».

Создание вычисляемого условия отбора

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

Название столбца, в котором расположено условие должно отличатся от столбцов таблицы.

Диапазон условий имеет вид

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

$I$10:$I$16 – диапазон ячеек, которые используются для вычисления условия. В данном случае ссылки должны быть абсолютные.

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

1. Создать область условий.

2. Поставить курсор в таблицу и выбрать группу «Данные» кнопка «Дополнительно»

3. В окне «Расширенный фильтр» переключатель поставить в положение «скопировать результат в другое место.

4. В поле «Исходный диапазон» указать диапазон ячеек, которые занимает таблица (Если курсор стоит в таблице и таблица преобразована в список диапазон выделяется автоматически).

5. В поле «Диапазон условий» указать диапазон ячеек, в которых расположена область условий.

6. Поставить переключатель в положение «скопировать результат в другое место».

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

Лабораторная работа № 4
Сортировка данных

Цель работы: овладение навыками сортировки данных

- по одному и нескольким столбцам

- по пользовательскому списку.

Задание:

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

2. Выполнить сортировку по двум столбцам.

3. Создать пользовательский список (настраиваемый список).

4. Выполнить сортировку по пользовательскому списку.

Методические указания по выполнению работы

Скопировать лист с 5 лабораторной работой и оформить его в соответствии с Приложением 3.

Сделать 4 копии исходной таблицы (по числу видов сортировки).

Сортировка по одному столбцу

Поставить курсор в таблицу. Выбрать группу «Данные» кнопка «Сортировка»

В окне «Сортировка» установить флажок для подписи «Мои данные содержат заголовки».

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

Сортировка по двум (нескольким столбцам)

Перед сортировкой по двум столбцам необходимо выбрать столбец с одинаковыми группами записей, иначе сортировка по двум столбцам не будет иметь смысла! Рассмотрим, например, как выполняется сортировка по двум столбцам.

Задача.

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

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

Исходная таблица

Начинаем сортировать…выбираем первый столбец для сортировки в поле «Сортировать по» столбец «Должность», далее кнопка «Добавить уровень» и «Затем по» выбираем столбец «Оклад».

Так выглядит отсортированная таблица по двум столбцам по должности и окладу.

Сортировка по пользовательскому (настраиваемому) списку

В сортировке такого вида, пользователь сам определяет порядок сортировки, для этого:

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

2. Выделить пользовательский список.

3. Нажать кнопку «Offyce», далее выбрать «параметры Excel» и «Изменить списки»

4. На вкладке «Списки» выбираем «Импорт списка из ячеек» и выделяем диапазон составленного списка

и нажимаем кнопку «Импорт», далее кнопка «Добавить», таким образом, наш список появляется в окне «Списки», нажать кнопку «ОК».

5. Возвращаемся к группе «Данные», «Сортировка» Сортируем по сотрудникам и в группе «Порядок», выбираем наш настраиваемый список.