Урок №10. Тема: Работа с базами данных в Excel

Вид материалаУрок

Содержание


Создание баз данных в EXCEL. Размер базы данных. Сохранение базы данных.
Ввод данных с помощью формы базы данных.
Поиск данных с помощью средства автофильтра. Восстановление исходной базы после поиска.
Сортировка базы данных.
Сортировка данных.
Лабораторная работа №10.
База данных
Сортировка по 4-м и более полям
Подобный материал:

УРОК №10.

Тема: Работа с базами данных в Excel.


Цели:
      1. Обучающая: познакомить учащихся с понятием базы данных, её использованием и применением в MS Excel.
      2. Развивающая: продолжить обучение учащихся логически мыслить, принимать правильное решение.
      3. Воспитательная: способствовать воспитанию самостоятельности, активности учащихся.

Ход урока
        1. Организационный момент.

Проверка присутствующих и готовности, учащихся к занятию.
        1. Актуализация.
  1. Что такое диаграмма?
  2. Назовите виды диаграмм.
  3. Назовите основные элементы диаграмм.
  4. Из каких шагов состоит процесс создания диаграмм.
  5. Можно после построения диаграмм изменить ее внешний вид.
  6. Охарактеризуйте параметры перекрытие и ширина зазора.
  7. Каким образом можно изменить порядок рядов данных.
  1. Новая тема.

Понятия базы, записи, поля данных. Системы управления базами данных. Примеры.

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

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

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

Создание баз данных в EXCEL. Размер базы данных. Сохранение базы данных.

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

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

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

Ввод данных с помощью формы базы данных.

В Excel предусмотрено мощное, удобное и в то же время простое средство для облегчения ввода в базу данных – форма ввода данных. Для того чтобы воспользоваться формой данных:
  • выделите диапазон базы данных;
  • выберите команду меню Данные/Форма. Откроется диалоговое окно с именем рабочего листа, на котором находится база данных;


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

Поиск данных с помощью средства автофильтра. Восстановление исходной базы после поиска.

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

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

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

Сортировка базы данных.

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

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

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

Сортировка по возрастанию/по убыванию.

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



Сортировка в особом порядке.

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



Чтобы воспользоваться собственным порядком сортировки, его нужно предварительно создать:
  1. С помощью команды Сервис/параметры вызвать окно Параметры;
  2. перейти на вкладку Списки;
  3. в поле Элементы списка вывести элементы, разделяя их нажатием клавиши Enter;
  4. после ввода всех элементов нажать кнопку Добавить и закрыть окно Параметры.

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


Тема: Создание баз данных.

Цель: научиться создавать базы данных, рассмотреть различные виды сортировок баз данных.
  1. Запустить Excel.
  2. Создайте следующую базу данных.

    Фамилия

    Имя

    Отчество

    Отдел

    Оклад

    Колбасова

    Людмила

    Павловна

    ОК

    4685

    Башинская

    Ирина

    Федоровна

    Бухгалтерия

    5642

    Сафиуллина

    Лейсан

    Рифовна

    ОЭ

    4200

    Валиева

    Амина

    фаридовна

    ОЭ

    3854

    Канавалова

    Альбина

    Митрофановна

    ОК

    4500

    Закирова

    Эльвира

    Альбертовна

    ОК

    4250

    Гатина

    Фаина

    Робертовна

    Бухгалтерия

    5210

    Андрианова

    Светлана

    Васильевна

    ОЭ

    3210

    Королева

    Маргарита

    Ивановна

    ОК

    4800

    Баштабай

    Ольга

    Васильевна

    ОЭ

    4010

    Роева

    Елена

    Михайловна

    Бухгалтерия

    6550

    Шубина

    Елена

    Михайловна

    Бухгалтерия

    5200

    Кудряшова

    Нина

    Александровна

    ОЭ

    4500
  3. выделите диапазон базы данных;
  4. выберите команду меню Данные/Форма. Откроется диалоговое окно с именем рабочего листа, на котором находится база данных. Нажмите кнопку Добавить и добавьте кого-нибудь в список.
  5. Нажмите кнопку Удалить и удалите из списка Сафиуллину.
  6. Отсортируйте данные по возрастанию. Для этого выделите диапазон базы данных. Выберите команду Данные/ Сортировка и укажите следующие параметры:

.
  1. Создайте собственный порядок сортировки. Для этого:
    • С помощью команды Сервис/параметры вызвать окно Параметры;
    • перейти на вкладку Списки;
    • в поле Элементы списка вывести элементы, разделяя их нажатием клавиши Enter;
    • после ввода всех элементов нажать кнопку Добавить и закрыть окно Параметры.
  2. Сохраните файл под названием «База данных.xls».
  3. Результаты работы покажите учителю.

ЗАДАНИЕ.
  1. Создайте следующую базу данных по вариантам.



    База данных

    Пояснения

    1

    Отдел кадров (Фамилия, Имя, Отчество, Оклад, Пол, Дата рождения, Возраст, Дата приема на работу, Отдел, Должность)

    Поле Возраст необходимо рассчитывать по формуле.

    2

    Деканат (Фамилия, Имя, Отчество, Дата рождения, Группа, Факультет, Специальность, Предмет, Дата сдачи экзамена, Оценка)

    Значения поля оценка: Отлично, Хорошо и т.д.

    3

    Нагрузка преподавателя (ФИО, Ученая степень, Должность, Кафедра, Название предмета, Специальность, Группа, Факультет, Вид занятия, Количество часов).

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

    4

    Продажа (Менеджер, Клиент, Вид сделки, Товар, Количество, Цена, Сумма, Дата).

    Значение поля Вид сделки: поставка, продажа

    5

    Поставки (Дата поставки, Поставщик, Количество продукции, Способ перевозки, Транспортные издержки на единицу товара, Цена единицы товара без транспортных расходов, Стоимость перевозимого товара, Общие транспортные расходы).

    Значение поля Способ перевозки: ж/д, самолет и т.п.

    Поле Общие транспортные расходы необходимо рассчитывать по формуле.
  2. Отсортируйте базу данных по следующим полям:



Сортировка по 4-м и более полям

Сортировка в особом порядке

1

Фамилия, Имя, Отчество, Дата рождения

Отдел

2

Отдел, Фамилия, Имя, Отчество

Фамилия

3

Дата рождения, Фамилия, Имя, Отчество

Отдел

4

Оклад, Фамилия, Имя, Отчество, Отдел

Возраст

5

Фамилия, Имя, Отчество, Дата рождения, Факультет

Факультет

6

Предмет, Дата сдачи экзамена, Фамилия, Имя, Отчество

Предмет

7

Предмет, Оценка, Фамилия, Имя, Отчество

Группа

8

Факультет, Предмет, Оценка, Группа, Фамилия, Имя, Отчество

Оценка

9

Кафедра, Должность, Ученая степень, ФИО

Ученая степень

10

Кафедра, ФИО, Факультет, Группа

Должность

11

Название предмета, Кафедра, Должность, Ученая степень, ФИО

Вид занятия

12

Вид занятия, Название предмета, Факультет, Группа, ФИО

Название предмета

13

Менеджер, Клиент, Товар, Количество

Товар

14

Клиент, Менеджер, Товар, Дата

Клиент

15

Товар, Менеджер, Клиент, Сумма

Менеджер

16

Дата, Менеджер, Товар, Клиент, Количество

Товар

17

Поставщик, Способ перевозки, Стоимость перевозимого товара, Дата поставки

Способ перевозки

18

Способ перевозки, Поставщик, Дата, Общие транспортные расходы

Поставщик

19

Поставщик, Способы перевозки, Дата, Транспортные издержки на единицу товара

Способ перевозки

20

Дата, Способ перевозки, Поставщик, Общие транспортные расходы, Количество поставленной продукции

Поставщик