Создание базы данных в excel практическая работа

Обновлено: 06.07.2024

Урок-практикум "Создание базы данных в Excel и работа с ней". Разработка содержит конспект урока, раздаточный материал для учеников, лист Excel с заданием.

Просмотр содержимого документа
«Практ работа Excel (созд.БД)»

Практическая работа

по теме: « Microsoft Excel ».

Создание базы данных в Excel и работа с ней.

В электронной таблице Excel создать базу данных, внести в нее представленные ниже записи.

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

Внести информацию о курсе доллара.

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

Год издания

Кол-во страниц

Область знаний

Цена, $

Выручка, руб.

Курс $

Олимпиады по информатике

Красноярское кн. изд-во

Найти подготовленный мною файл (С:\Книжная ярмарка \ Книги.х ls ).

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

Скопировать все формулы.

Осуществить сортировку записей в базе данных; Выполнить команду меню Данные, Сортировка.

В окне Сортировка диапазона выбрать поле для сортировки (Сортировать по), например «Автор».

Указать, как следует выполнять сортировку: по возрастанию или по убыванию.

Для работы с базой данных в Excel имеется специальная форма, которая позволяет просматривать записи, добавлять новые, удалять записи, осуществлять поиск. Для работы с формой надо выполнить команду Данные, Форма. Остановимся на кнопке Критерии и выполним следующее задание:

В поле «Автор» ввести букву «С», просмотреть кнопкой Далее.

В поле «Цена, $» ввести «

В поле «Область знаний» ввести «Программирование», в поле «Цена, $» — «Просмотреть, сколько таких книг.

13. Добавить в базу данных новые записи, используя форму, - внести информацию нескольких книгах, (1.«Информатика. Учебник 5», М. Бином, Босова Л., 2004, 160с, $2,9; 2. («Информатика. Базовый курс 7»,М. Бином, Угринович Н., 2003, 155с., $3; 3. «Задачи по программированию», М. «Наука», Абрамов С.А., 1989, 223с., $4,6)

Работа с кнопкой Критерии.

Сколько на ярмарке было представлено книг по математике?

Сколько было представлено книг стоимостью больше 100 рублей?

Сколько всего было представлено книг по информатике?

Работа с таблицей, использование автосуммирования.

Каково общее количество книг, представленных на ярмарке?

Сколько на ярмарке было продано книг и на какую сумму?

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

Просмотр содержимого документа
«Урок. Создание БД в Excel»

Практическая работа

по теме: « Microsoft Excel ».

Создание базы данных в Excel и работа с ней.

знакомство с функциями ЭТ по созданию баз данных и работе с ней;

закрепление знаний по работе с формулами в ЭТ и соответствующих умений.

обучающие – ввести понятия «база данных», «запись», «поле»; рассмотреть приёмы работы с БД; закрепить работу с формулами;

развивающие – развитие операционного мышления, понятийного мышления;

воспитательная – воспитание информационной культуры (поиск информации, обмен между приложениями Windows ).

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

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

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

Что это такое? Запишите в тетради: база данных — это совокупность информации об однотипных объектах, некое хранилище данных.

База данных состоит из записей. Запись — это информация об одном объекте.

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

Карточка-задание 1.

В электронной таблице Excel создать базу данных, внести в нее представленные ниже записи.

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

Внести информацию о курсе доллара.

Год издания

Кол-во страниц

Область знаний

Цена, $

Выручка, руб.

Курс $

Олимпиады по информатике

Красноярское кн. изд-во

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

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

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

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

Найти подготовленный мною файл (С:\Книжная ярмарка \ Книги.х ls — путь для поиска файла записывается на доске) Прил.

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

Скопировать все формулы.

Карточка-задание 2.

Осуществить сортировку записей в базе данных;

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

В окне Сортировка диапазона выбрать поле для сортировки Сортировать по), например «Автор».

Указать, как следует выполнять сортировку: по возрастанию или по убыванию.

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

В поле «Автор» ввести букву «С», просмотреть кнопкой Далее.

В поле «Цена, $» ввести «

В поле «Область знаний» ввести «Программирование», в поле «Цена, $» — «Просмотреть, сколько таких книг.

Карточка-задание 3.

Добавить в базу данных новые записи, используя форму, - внести информацию о книгах, имеющихся в кабинете информатики (3-5 записей).

Карточка-задание 4.

Работа с кнопкой Критерии.

Сколько на ярмарке было представлено книг по математике?

Сколько было представлено книг стоимостью больше 100 рублей?

Сколько всего было представлено книг по делопроизводству?

Работа с таблицей, использование автосуммирования.

Каково общее количество книг, представленных на ярмарке?

Сколько на ярмарке было продано книг и на какую сумму?

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

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

Карточка-задание 5.

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

Посмотреть список посетителей-студентов.

Посмотреть список посетителей с высшим образованием

Посмотреть список посетителей, пришедших по пригласительным билетам.

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

Посмотреть, какие мероприятия пользовались большей популярностью.

(База данных должна содержать следующие поля: «Ф.И.О.», «Род занятий», «Образование», «Регион», «Место жительства», «Наличие пригласительного билета», «Кол-во купленных книг», «Посещаемые мероприятия».)

Итак, вы создали структуру базы данных, но остается открытой задача: как эту информацию собрать для базы данных?

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

В пакете Microsoft Office есть специальная программа для создания базы данных и работы с ними – Access. Тем не менее, многие пользователи предпочитают использовать для этих целей более знакомое им приложение – Excel. Нужно отметить, что у этой программы имеется весь инструментарий для создания полноценной базы данных (БД). Давайте выясним, как это сделать.

Процесс создания

База данных в Экселе представляет собой структурированный набор информации, распределенный по столбцам и строкам листа.

Согласно специальной терминологии, строки БД именуются «записями». В каждой записи находится информация об отдельном объекте.

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

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

Создание таблицы

Итак, прежде всего нам нужно создать таблицу.

На этом создание каркаса БД закончено.

Присвоение атрибутов базы данных

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

«Данные»

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

Сортировка и фильтр

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

«Данные»

«Сортировка и фильтр»

Сортировку можно проводить практически по любому параметру:

имя по алфавиту;
дата;
число и т.д.

В поле «Сортировка» указывается, как именно она будет выполняться. Для БД лучше всего выбрать параметр «Значения».
В поле «Порядок» указываем, в каком порядке будет проводиться сортировка. Для разных типов информации в этом окне высвечиваются разные значения. Например, для текстовых данных – это будет значение «От А до Я» или «От Я до А», а для числовых – «По возрастанию» или «По убыванию».
Важно проследить, чтобы около значения «Мои данные содержат заголовки» стояла галочка. Если её нет, то нужно поставить.

После ввода всех нужных параметров жмем на кнопку «OK».

Поиск

При наличии большой БД поиск по ней удобно производить с помощь специального инструмента.

«Главная»

«Редактирование»

«Найти и выделить»

Закрепление областей

Удобно при создании БД закрепить ячейки с наименованием записей и полей. При работе с большой базой – это просто необходимое условие. Иначе постоянно придется тратить время на пролистывание листа, чтобы посмотреть, какой строке или столбцу соответствует определенное значение.

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

Выпадающий список

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

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

Конечно, Excel уступает по своим возможностям специализированным программам для создания баз данных. Тем не менее, у него имеется инструментарий, который в большинстве случаев удовлетворит потребности пользователей, желающих создать БД. Учитывая тот факт, что возможности Эксель, в сравнении со специализированными приложениями, обычным юзерам известны намного лучше, то в этом плане у разработки компании Microsoft есть даже некоторые преимущества.

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Цель работы: Научиться создавать базы данных в MS Excel. Изучить возможности работы с базами данных.

Оформление базы данных.

Сортировка элементов базы данных.

Суммирование чисел в базе данных.

Пример выполнения задания:

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

Заполнить базу данных на 15 сотрудников.

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

В нашем случае заголовок имеет вид:

Теперь в ячейку A4 запишем:

и растянем эту формулу до ячейки А18.

Далее заполняем ячейки B4:F18 данными, получим:

Рассмотрим работу с формой базы данных, для этого добавим при помощи формы еще двух сотрудников. Выполним: \Данные\Форма.

получим диалоговое окно вида:

Теперь если мы хотим добавить запись в базу данных, то нажмем кнопку «Добавить» и получим:

Заметим, что поле ввода номера не доступно, так как там заложена формула. Мы заполняем только доступные нам поля и получаем:

Заметим, что для перехода из одного поля ввода в другое необходимо нажимать клавишу TAB.

Теперь для того, чтобы добавить еще одного сотрудника, снова нажмите кнопку «Добавить» и введите данные этого сотрудника.

Для завершения добавлений в базу данных необходимо нажать клавишу «Закрыть». Произойдет выход из режима «Форма», а также добавление сотрудников в базу данных.

Обращаем ваше внимание на то, что сотрудники добавляются не по алфавиту, а в конце списка.

В результате наша база данных приобретет вид:

Теперь рассмотрим сортировку базы данных.

Сортировать базу данных в MS Excel можно по столбцу любого типа в порядке возрастания или убывания. Допускается задание от одного до трех критериев сортировки.

Установите курсор в любую ячейку базы данных. В меню «Данные» выберите пункт «Сортировка. »:

Получим диалоговое окно вида:

Теперь заполним поле «Сортировать по». Для начала отсортируем нашу базу данных по «Фамилиям» в порядке возрастания, получим:

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

Теперь отсортируем нашу базу данных по двум столбцам. Сначала отсортируем по «Должности», а потом по «Коду». Для этого в диалоговом окне сортировки наберем:

В результате получим:

В результате такой сортировки на первые позиции встали все «Координаторы», причем между собой они отсортированы по «Коду». В конце у нас «Менеджеры», которые тоже отсортированы между собой по «Коду».

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

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

\Данные\Фильтр\Автофильтр

В результате вы получите:

Обратите внимание, что в строке заголовка появились кнопки со стрелками.

Теперь с помощью фильтра оставим всех «Менеджеров» с «Кодом» из диапазона от 3755 до 3760. Для этого нажмем кнопку со стрелочкой в столбце «Должность» и выберем «Менеджер»:

Далее нажмем кнопку со стрелочкой в столбце «Код» и выберем «Условие», в результате получим диалоговое окно вида:

Заполнив это окно так, как показано на рисунке, получим:

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

Если вы хотите снять фильтр, то выберите:

\Данные\Фильтр\Автофильтр

Примечание. Для того чтобы не только извлечь, но и переместить данные, выполните команду: \Данные\ Фильтр\Расширенный фильтр. В открывшемся диалоговом окне включите опцию «Скопировать результат в другое место» и укажите, куда копировать результат.

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

Для того чтобы изучить эту возможность, добавим в нашу базу данных еще один столбец «Зарплата» и отсортируем ее по «Фамилиям», получим:

Установим курсор в любую ячейку базы данных и выполним:

\Данные\Итоги.

получим диалоговое окно вида:

Укажем подводить промежуточные итоги при каждом изменении «Должности», операция «Сумма», а итоги подводить только по «Зарплате» и нажмем кнопку «ОК», получим:

Теперь отменим итоги, для чего выполним \Данные\Итоги. и в появившемся диалоговом окне нажмем кнопку «Убрать все». Отсортируем базу данных по «Должности» и вновь установим итоги по тому же принципу, что и ранее, получим:

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

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