Методические указания к лабораторной работе

"Управление данными в EXCEL"

1.ЦЕЛЬ РАБОТЫ

Целью данной работы является получение навыков при работе с таблицами как с базами данныхв EXCELе.

2. ЗАДАЧИ РАБОТЫ

Научиться :

            2.1 Создавать базы данных,

            2.2 Создавать формы данных и работать в них с записями,

            2.3 Сортировать записи в базах данных,

            2.4 Выбирать записи по различным критериям.

3. СОДЕРЖАНИЕ РАБОТЫ

             3.1 Создать новый лист рабочей книги “База данных” и поместить на нем

данные, указанные преподавателем, в виде базы данных. В первой строке

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

размещаться записи.

            3.2 Вызвать форму данных для вашей таблицы и отредактировать записи в ней по заданию п. 7.

            3.3 Выбрать записи по следующим условиям :

 - по единственному критерию в текстовом поле

 - больше одного из значений в числовом поле

 - по первой букве текстового поля

 - по двум критериям в числовых полях.

            3.4 Отсортировать базу данных в порядке убывания третьего столбца.

Одинаковые значения третьего столбцасортировать по возрастанию второго.

            3.5 Включить автофильтр и отфильтровать записи по следующим параметрам :

 - равенство в текстовом поле

 - неравенство в числовом поле

 - И интервал

 - ИЛИ интервал.

            3.6 Оформить отчет.

4. СОДЕРЖАНИЕ ОТЧЕТА

            4.1. Название работы и ее задачи.

            4.2. Основные этапы работы и результаты их выполнения

 в виде файла на дискете.

            4.3. Ответы на контрольные вопросы по пункту 8.

5. РЕКОМЕНДУЕМАЯ ЛИТЕРАТУРА

            5.1 Конспект лекций по курсу "Информатика".

            5.2 EXCEL 5.0 за 5 минут. / Мартин Альтхаус,Михаэль Ортлепп

 -М.:Фирма БИНОМ,1994 ,С.76-87.

            5.3 Грег ХарвейEXCEL 5.0 для "чайников"

-Киев : Диалектика,1995,С. 182-197

6. ОБЩИЕ  ПОЛОЖЕНИЯ

6.1 Общее понятие о базах данных

Базаданных - это любая совокупность связанной информации, объединенной вместе по определенному признаку. Например, базой данных является расписание полета самолетов. Большинство баз данных для хранения своей информации использует таблицы. Каждая таблица состоит из строкистолбцов, которые в компьютерных базах данных называются записями и полями.

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

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

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

6.2 Команда Форма

С помощью команды ДАННЫЕ \ ФОРМАможно вывести форму данных, которая представляет собой окно диалога, используемое для просмотра, изменения, добавления и удаления записейбазы данных, или для поиска конкретных записей на основе определяемых критериев поиска.

В этом окне диалога названия столбцов таблицы становятся именами полей в форме данных. Excel автоматически присваивает "горячие клавиши" каждому имени поля. Для того, чтобы переместиться в некоторое поле, следует нажать ALT вместе с буквой, подчеркнутой в имени поля. Поля cоответствуют каждому столбцу в списке. Все поля, доступные для редактирования, появляются в окне редактирования.

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

В диалоговом окне формы данных можно пользоваться следующими кнопками :

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

6.3 Сортировка записей

С помощью команды ДАННЫЕ \ СОРТИРОВКА можно упорядочить строки в базе данных в соответствии с содержимым определенных столбцов. Предположим, например, что база данных содержит данные о продажах, и имеет столбцы для имени продавца, количества товара, даты заказа и т.п. Можно воспользоваться командой СОРТИРОВКА для того, чтобы упорядочить список по именам продавцов, расположить данные по убыванию объемов продаж или в порядке возрастания даты заказа. Для этого нужно выделить сортируемые данные и определить требуемый порядок сортировки, или создать и применить пользовательский порядок сортировки.

Чтобы отсортировать базу данных целиком, нужно лишь выделить одну ячейку из списка и выбрать команду ДАННЫЕ \ СОРТИРОВКА . Microsoft Excel автоматически выберет весь список для сортировки. Microsoft Excel определяет расположение меток столбцов, даже если они занимают две строки, и исключает их из сортировки. Можно использовать метки столбцов для того, чтобы указать столбцы, по которым нужно отсортировать список. Microsoft Excel позволяет также сортировать только выделенные строки или столбцы, или данные только в одной строке или столбце.

После задания данных для сортировки и выбора команды на экране появиться окно диалога, в котором следует задать следующие поля:

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

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

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

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

Параметры-Выводит окно диалога Параметры Сортировки, в котором Вы можете:

-Определить пользовательский порядок сортировки для столбца, указанного в

 окне Сортировать.

-Определить сортировку с учетом регистра.

-Изменить ориентацию сортировки, то есть, вместо сортировки сверху вниз

 установить сортировку слева направо.

После установки всех необходимых полей и параметров следует нажать на кнопку ОК.

 Microsoft Excel использует следующие основные принципы при сортировке:

 -если сортировка ведется по одному столбцу, то строки с одинаковыми

значениями в этом столбце сохраняют прежнее упорядочивание;

 -строки с пустыми ячейками в столбце, по которому ведется сортировка,

располагаются в конце сортируемого списка;

 - параметры сортировки -- выбранный столбец, порядок (возрастающий или

убывающий) и направление сортировки (сверху вниз или слева направо) --

сохраняются от предыдущего применения сортировки до тех пор, пока их не

изменить, не выберать другую базу данных или не изменить метки столбцов;

 - если сортировка ведется по нескольким столбцам, строки с одинаковыми

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

 Если результат сортировки не тот, которого ожидали, обратите внимание на тип сортируемых данных. Убедитесь, что все числа введены как цифровые данные или как текст.Следует ввести все данные в одном столбце либо как числа либо кактекст.

6.4 Фильтрация или выборка данных

С помощью команды ДАННЫЕ \ ФИЛЬТРможно отыскать и использовать нужное подмножество данных в базе. В отфильтрованной базе выводятся на экран только те строки, которые содержат определенные значения или отвечают некоторым критериям выборки. При этом прочие строки будут временно скрыты. Подменю ФИЛЬТР в Microsoft Excel предоставляет возможность использовать как команду АВТОФИЛЬТР, так и команду УСИЛЕННЫЙ ФИЛЬТР если нужно использовать сложный критерий

для выборки данных .

            6.4.1 Применение АВТОФИЛЬТРА.

Команда АВТОФИЛЬТРпомещает кнопки раскрывающихся списков (кнопки со стрелкой) непосредственно в строку с метками столбцов, с их помощью можно выбрать элементы базы, которые следует вывести на экран. Например, можно вывести на экран все строки, содержащие некоторое значение в столбце, скажем, "все строки, которые содержат дату заказа 11.12.97 в столбце Дата Заказа".

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

В этом окне можно определить до двух критериев сравнения для одного и того же столбца при фильтрации данных. Microsoft Excel сравнит элементы списка с введенными ранее значениями и выведет лишь строки, удовлетворяющие критерию. Можно также использовать условные операторы И / ИЛИ для объединения или сравнения критериев в одном и том же столбце.

Для определения двух критериев выберите один из следующих вариантов:

-Нажмите переключатель "И" для объединения двух критериев в Вашем

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

 оба условия.

-Нажмите переключатель "ИЛИ" для использования двух различных

 критериев в Вашем фильтре. В этом случае выберуться данные, для которых

 выполняются хотя бы одно из условий.

            6.4.2 Применение УСИЛЕННОГО ФИЛЬТРА.

Команда УСИЛЕННЫЙ ФИЛЬТР позволяет отыскивать строки с помощью более сложных критериев, например, "продажи10.02.97 на сумму более, чем 500000 р., или продажи 11.02.97 на сумму более, чем 300000 р."

 При выборе этой команды в появившемся окне диалога следует установить следующие параметры:

Действие-Переключатель Фильтровать Список На Месте скрывает строки, которые не удовлетворяют указанному критерию.Переключатель Копировать На Другое Место копирует отфильтрованные данные на другой рабочий лист или на другое место на том же рабочем листе.

Интервал Списка-Определяет интервал, содержащий данные, подлежащий фильтрации.

Интервал Критериев-Определяет интервал ячеек на рабочем листе, который содержит нужные критерии. Microsoft Excel выводит ссылку на этот

интервал.

Копировать На-Определяет интервал ячеек, в который копируются строки, удовлетворяющие определеннымкритериям. Это поле активно только в том случае, если выбран переключатель Копировать На Другое Место.

Без Повторений-Выводит только строки, удовлетворяющие критерию и не содержащие повторяющихся элементов. Если интервал критериев не определен, то в этом случае все строки списка, содержащие дубликаты, будут скрыты.

            6.4.3 Работа с отфильтрованными данными

Когда строки скрываются с помощью команд АВТОФИЛЬТРи УСИЛЕННЫЙ ФИЛЬТР, рабочий лист переходит в режим фильтрации.

В этом режиме многие команды Microsoft Excel воздействуют только на видимые ячейки. После того, как применен фильтр для вывода только нужных строк, можно скопировать полученное подмножество данных в другое место для дальнейшего анализа.

Для того чтобы вернуться к первоначальному виду базы данных, следует

воспользоваться командой ДАННЫЕ \ ФИЛЬТР \ ПОКАЗАТЬ ВСЕ .

7. МЕТОДИЧЕСКИЕ РЕКОМЕНДАЦИИ

7.1 Редактирование и выбор записей в форме данных

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

 - перейти на третью запись;

 - перейти на начало и конец базы данных;

 - с помощью кнопки Создать ввести 2 новые записи;для перехода к

следующему полю пользуйтесь мышкой, после ввода значения последнего

поля нажмите клавишу ENTER;

 - удалить четвертую запись ;

 - изменить значение одного из полей второй записи;

 - проделать то же самое с последней записью, но потом измененное значение

восстановить.

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

 - для выбора по единственному критерию в текстовом поле следует ввести

одно конкретное значение в соответствующее поле

 - для выбора больше какого-либо значения в числовом поле поставить

оператор сравнения“>“ в соответствующем поле и написать нужное значение

 - для выбора по первой букве текстового поля ввести первую букву в

соответствующее первое поле

 - для выбора по двум критериям в числовых полях в двух любых числовых

полях ввести операторы сравнения и нужные значения.

После ввода параметров нажать клавишу ENTER. Для просмотра выбранных записей пользуйтесь кнопками Предыдущая и Следующая. Чтобы удалить предыдущий критерий воспользуйтесь кнопкой Очистить. Закройте форму данных.

7.2 Сортировка данных

Для того чтобы отсортировать базу данных в порядке убывания третьего столбца следует выбрать любую ячейку третьего поля базы данных и выполнить командуДАННЫЕ \ СОРТИРОВКА . Появиться окно диалога, в котором в поле Сортироватьдолжно автоматически выбраться имя третьего столбца . Указать порядок сортировкипо убыванию.

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

7.3 Фильтрация данных

 Выделить любую ячейку базы данных и выполнить команду ДАННЫЕ \ ФИЛЬТР \ АВТОФИЛЬТР. В строке с именами столбцов появяться кнопки раскрывающихся списков (кнопки со стрелкой) . С их помощью можно выбрать элементы базы, которые следует вывести на экран. Для того чтобы отфильтровать записи по равенству в текстовом поле следует выбрать из раскрывшихся списков соответствующее значение этого поля. Для выполнения других заданий нужно в соответствующих полях выбрать из списка Настройка.

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

8. КОНТРОЛЬНЫЕ ВОПРОСЫ

 8.1. Что такое база данных ?

 8.2. Как нужно изменить таблицу чтобы она имела вид базы данных ?

 8.3. Какие возможности по редактированию баз данных предоставляет команда ФОРМА ?

 8.4. Какие критерии можно задавать в команде ФОРМА ?

 8.5. Для чего нужна сортировка записей и как ее осуществить ?

 8.6. Как задать порядок вторичной сортировки ?

 8.7. Как отсортировать только один столбец в таблице ?

 8.8. Как осуществить сортировку по строкам ?

 8.9. Различаются ли верхний и нижний регистр при сортировке ?

8.10. Что можно сделать с помощью команды АВТОФИЛЬТР ?

8.11. Какие параметры нужно установить чтобы данные выбирались при

одновременном выполнении двух условий?

8.12. Какие параметры нужно установить чтобы данные выбирались при

выполнении хотя бы одного из двух условий?

8.10. Что можно сделать с помощью команды УСИЛЕННЫЙ ФИЛЬТР ?