41825

Работа с данными в программе Microsoft Excel

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

Информатика, кибернетика и программирование

Цель данной лабораторной работы - научиться создавать с помощью пакета Excel тип рабочей таблицы, соответствующий базе данных. А также научиться работать с данными таблицы Excel как с базой данных.

Русский

2013-10-25

277 KB

16 чел.

PAGE   \* MERGEFORMAT 1

Лабораторная работа №8.

Работа с данными в программе Microsoft Excel

8.1 Цель работы

Цель данной лабораторной работы - научиться создавать с помощью пакета Excel тип рабочей таблицы, соответствующий базе данных. А также научиться работать с данными таблицы Excel как с базой данных.

8.2 Приборы и материалы

Для выполнения лабораторной работы необходим персональный компьютер, функционирующий под управлением операционной системы семейства WINDOWS. Должна быть установлена программа Microsoft Excel.

8.3 Базы данных

В Excel можно создавать и другой тип рабочей таблицы - базу данных. База данных не столько вычисляет новые значения, сколько размещает огромные объёмы информации в связанном виде.

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

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

Каждая колонка в базе данных содержит информацию определённого типа. Каждый столбец известен в базе данных под именем поле. Данные, записанные в строках базы данных, называют записями. Каждая запись (строка) состоит из значений полей.

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

Чтобы заставить Excel правильно отсортировать записи в базе данных, вы должны указать, какие поля будут определять новый порядок записей, (такие поля называются ключами сортировки). Более того, Вы должны указать какой тип сортировки должен выполняться в этих полях. Существует два типа упорядочения: возрастающий, при котором текст располагается в алфавитном порядке (от А до Я), а числа - в порядке увеличения (от наименьшего к наибольшему), и убывающий, который противоположен алфавитному порядку и порядку нумерации (от Я до А, а числа - от набольшего к меньшему).

Когда вы используете возрастающий порядок сортировки с полем ключа, содержащим много различных типов значения, Excel помещает в первую очередь числа (от наименьшего к наибольшему), а только потом текстовые элементы (в алфавитном порядке). За текстовыми элементами следуют логические значения (сначала TRUE/ Истина , затем FALSE/ Ложь), сообщение об ошибках и, наконец, чистые клетки. При использовании убывающего порядка Excel упорядочивает элементы в обратном порядке: числа идут первыми, но упорядочены от наибольшего к наименьшему затем следуют текстовые элементы от  Я до А, логическое FALSE предшествует логическому TRUE.

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

8.3.2 Фильтрование данных

Чтобы отфильтровать базу можно воспользоваться командой на ленте «Данные» «Фильтр». В отфильтрованных данных отображаются только строки, соответствующие заданным условиям, а ненужные строки скрываются.

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

После отбора данные в этом подмножестве можно копировать, искать, изменять, форматировать, преобразовывать в диаграммы и выводить на печать; при этом их местонахождение и порядок не изменятся.

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

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

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

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

Рисунок 1 – Вид окна Excel с раскрывшимся списком

В раскрывающемся списке можно выбирать команды для сортировки и фильтрации данных.

Чтобы выполнить отбор диапазона ячеек с помощью сложных условий отбора, воспользуйтесь командой «Дополнительно» в группе «Сортировка и фильтр» на вкладке «Данные». Существует несколько важных функциональных отличий команды «Дополнительно» от команды «Фильтр». Вместо меню «Автофильтр» отображается диалоговое окно «Расширенный фильтр». Расширенные условия отбора вводятся в отдельный диапазон условий листа над диапазоном ячеек или таблицей, которые требуется отфильтровать. В Microsoft Office Excel в качестве источника расширенных условий отбора используется отдельный диапазон условий в диалоговом окне «Расширенный фильтр».

Создание расширенного фильтра.

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

2. Введите в строки под заголовками столбцов условия отбора.

3. Введите в поле «Диапазон условий» ссылку на диапазон условий отбора, включающий заголовки столбцов.

4. Чтобы показать результат фильтрации, скрыв ненужные строки, установите переключатель в положение «Фильтровать список на месте». Чтобы скопировать отфильтрованные строки в другую область листа, установите переключатель в положение «Скопировать результат в другое место».

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

Внимание!!! Если присвоить диапазону имя «Критерии», то ссылка на диапазон будет автоматически появляться в поле «Диапазон условий». Можно также определить имя «База_данных» для диапазона фильтруемых данных и имя «Извлечь» для области вставки результатов, и ссылки на эти диапазоны будут появляться автоматически в полях «Исходный диапазон» и «Поместить результат в диапазон» соответственно.

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

8.4 Порядок выполнения работы

Внимательно изучить методические указания к лабораторной работе.

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

После включения компьютера и загрузки системы запустите программу Excel, развернув её окно на весь экран.

Откройте файл База данных.xlsx.

Отсортируйте записи по полю «Фамилии».

Отсортируйте записи сначала по полю «Группа», а затем по полю «Фамилия». В результате сортировки должны получиться списки студентов в группах, составленные по алфавиту.

Оставьте на экране такие записи, где представлен адрес студентов.

Снимите фильтр.

Оставьте на экране только одну запись.

Снимите фильтр.

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

Снимите фильтр.

Оставьте на экране только те записи, в которых номер студенческого билета состоит из шести цифр.

Снимите фильтр.

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

Снимите фильтр.

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

Покажите результаты преподавателю.

Снимите фильтр.

Создайте расширенный фильтр. Объясните необходимость создания расширенного фильтра.

Оформить результаты лабораторной работы в виде отчета. Файл с отчетом поместите в свою папку и в папку на сервере.

8.5 Оформление отчета

Отчет должен содержать следующее:

Вашу фамилию имя отчество и номер группы.

Цель работы.

Письменные ответы на два (по заданию преподавателя) контрольных вопроса.

Выводы.

8.6 Контрольные вопросы

Как создать базу данных в Excel ?

Что значит отсортировать?

Что значит отфильтровать?

Какие фильтры можно создавать для данных в пакете Excel?

Как сортировать записи в базе данных?

Как фильтровать записи в базе данных?

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


 

А также другие работы, которые могут Вас заинтересовать

20776. Устройство вертикально-сверлильного станка и его настройка на обработку отверстий 1.74 MB
  Станок 2Н135 рис. Стол 2 имеет Тобразные пазы для крепления тисков приспособлений или детали. Рис. Краткая техническая характеристика станка 2Н135 Размеры рабочей поверхности стола мм ширина х на длину 450x500 Наибольший диаметр сверления в стали мм 35 Конус Морзе шпинделя №4 Наибольшее вертикальное перемещение стола мм 300 Число ступеней частоты вращения шпинделя 12 Частота вращения шпинделя мин1 315; 45; 63; 90; 125; 180; 250; 355; 500; 710; 1000; 1400 Число ступеней подач шпинделя 9 Подачи шпинделя мм об 01; 014; 02;...
20777. Ряды Динамики. Установление вида ряда динамики 1.63 MB
  Установление вида ряда динамики. Основная цель статистического изучения динамики коммерческой деятельности состоит в выявлении и измерении закономерностей их развития во времени. Это достигается посредством построения и анализа статистических рядов динамики.
20778. Индексный метод. Статистические индексы 262.5 KB
  Статистические индексы. Индексы широко применяются в экономических разработках государственной и ведомственной статистики. Индивидуальные и общие индексы. В зависимости от степени охвата подвергнутых обобщению единиц изучаемой совокупности индексы подразделяются на индивидуальные элементарные и общие.
20779. Выборочное наблюдение 1.05 MB
  Проведение исследования социально экономических явлений выборочным методом складывается из ряда последовательных этапов: 1 обоснование в соответствии с задачами исследования целесообразности применения выборочного метода; 2 составление программы проведения статистического исследования выборочным методом; 3 решение организационных вопросов сбора и обработки исходной информации; 4 установление доли выборки т. части подлежащих обследованию единиц генеральной совокупности; 5 обоснование способов формирования выборочной совокупности; 6...
20780. Изучение статистической связи 666.23 KB
  N 130 ПОЛОЖЕНИЕ О ПОРЯДКЕ ПРЕДСТАВЛЕНИЯ ГОСУДАРСТВЕННОЙ СТАТИСТИЧЕСКОЙ ОТЧЕТНОСТИ В РОССИЙСКОЙ ФЕДЕРАЦИИ I. ОБЩИЕ ПОЛОЖЕНИЯ Настоящее Положение разработано в соответствии с Законом Российской Федерации Об ответственности за нарушение порядка представления государственной статистической отчетности Временным положением о Государственном комитете Российской Федерации утвержденным Постановлением Президиума Верховного Совета РСФСР от 27 апреля 1991 года N 11171 и во исполнение постановления Верховного Совета Российской Федерации от 13 мая...
20781. Общая теория статистики 199.97 KB
  Отдельные объекты или явления образующие статистическую совокупность называются единицами совокупности. Например при проведении переписи торгового оборудования единицей наблюдения является торговое предприятие а единицей совокупности их оборудование прилавки холодильные агрегаты и т. Вариация это многообразие изменяемость величины признака у отдельных единиц совокупности наблюдения. Любое статистическое наблюдение осуществляется с помощью оценки и регистрации признаков единиц совокупности в соответствующих учетных документах.
20782. Калорифер воздушный распылительной сушильной установки 1.05 MB
  Поверхностные теплообменные аппараты, в свою очередь, делятся на рекуперативные и регенеративные. В рекуперативных аппаратах теплообмен между различными теплоносителями происходит через разделительные стенки.
20783. ПСИХОМЕТРИЧЕСКАЯ ОЦЕНКА МЕТОДИКИ ДИАГНОСТИКИ РАБОТОГОЛЬНОЙ ЗАВИСИМОСТИ Б.КИЛЛИНЖЕР 364 KB
  Диагностика работогольной зависимости с помощью альтернативных методик (метод экспертных оценок, опросник Б.Киллинжер), адаптация и создание психометрического паспорта опросника Б.Килинжер: определение валидности опросника Б.Киллинжер; проведение процедуры point analysis (выявление дифференциальной силы каждого из утверждений опросника Б.Киллинжер)...
20784. Показатели вариации 930.28 KB
  Различие индивидуальных значений признака внутри изучаемой совокупности в статистике называется вариацией признака. Средняя величина это абстрактная обобщающая характеристика признака изучаемой совокупности но она не показывает строения совокупности которое весьма существенно для ее познания. Средняя величина не дает представления о том как отдельные значения изучаемого признака группируются вокруг средней сосредоточены ли они вблизи или значительно отклоняются от нее. В некоторых случаях отдельные значения признака близко...