2.2.5. Работа с таблицей Excel как с базой данных
Обработка информации в электронных таблицах Excel или списках. Основные понятия и требования к спискам.
Список представляет собой электронную таблицу с большим объемом взаимосвязанной информации (список товаров на складах или список номеров телефонов и адресов абонентов). Список — это набор строк электронной таблицы со взаимосвязанными однотипными данными постоянного формата. Другими словами список — это плоская база данных, а строки и столбцы списка соответствуют записям и полям в базе данных. Образец списка представлен на рисунке
К спискам в Excel предъявляются более строгие требования, чем к обычным электронным таблицам. Количество столбцов в списке должно быть постоянным, а количество строк переменным. Это позволяет добавлять , удалять или переставлять строки таблицы или записи списка (базы данных).
Наличие пустых строк и столбцов в списке является недопустимым. Данные в списке должны иметь постоянный формат. Первая строка в списке содержит названия столбцов или имена полей как в базах данных.
К средствам, которые предназначены для обработки и анализа данных в списке относятся команды из меню Данные: Сортировка, Фильтр, Форма, Итоги, Проверка. При выполнении этих команд, редактор автоматически распознает список как базу данных и осуществляет обработку и анализ данных в списке как в базе данных.
При применении команды сортировка можно отсортировать записи по одному или нескольким полям. С помощью фильтров (Автофильтра и Расширенного фильтра) можно быстро найти (отфильтровать) необходимые данные в списках по одному, двум или нескольким параметрам поиска. Командой Итоги можно упорядочить данные в списках с помощью итоговых значений.
Для добавления новых записей в список, удаления и поиска существующих записей в списках применяется команда Форма. Для проверки данных при вводе используется средство, которое называется проверкой ввода (команда Проверка).
При создании списка необходимо выполнить определенные требования:
Чтобы редактор автоматически распознавал список как базу данных и обрабатывал данные при выполнении команд обработки необходимо на рабочем листе располагать один список
Формат шрифта заголовков (подписей) столбцов или имен полей в списках должен отличаться от формата шрифта записей. Обычно шрифту заголовкам столбцов назначается полужирный шрифт, а ячейкам для заголовков присваивается текстовый формат
Ячейки под заголовками столбцов необходимо отформатировать в соответствии с данными, которые будут вводиться в эти ячейки (например, установить денежный формат, выбрать выравнивание и т.д.)
Для обеспечения автоматического форматирования введенных данных в список целесообразно активизировать команду «Расширение форматов и формул». Для этого необходимо установить флажок «Расширять форматы и формулы в диапазонах данных» в окне диалога «Параметры» на вкладке «Правка», которое открывается командой «Параметры» в меню Сервис.
В списке не должно быть пустых записей (строк) и полей (столбцов), даже для отделения имен полей от записей следует использовать границы ячеек, а не пустые строки
После выполнения подготовительных работ по созданию списка можно переходить к введению данных в список. Более полная информация о работе с таблицей как с базой данных изложена на страничке Обработка данных в списках Excel
Глава 8. Работа с базами данных в Excel
Базой данных (БД) в Excel мы будем называть ЭТ, организованную по определенным правилам. БД в Excel располагается на одном листе. Основными операциями в ней являются операции сортировки (упорядочения ) и фильтрации (отбор данных по различным критериям).8.1. Основные понятия и правила создания бд
Записи – строки в БД. Между записями в БД не может быть пустых строк.
Поля — столбцы в БД. Все ячейки столбца должны иметь однотипные данные (текстовые, числовые и др.) и одинаковый формат.
Имя поля – уникальный заголовок поля ( например, Фамилия или город). Заголовок БД – первая строка БД. После заголовка не может быть пустой строки. Приведем пример фрагмента БД.
Формы в БД
БД может вводиться с клавиатуры, как обычная таблица, так и с помощью форм. Форма вызывается командами меню Данные, Форма и представляет собой бланк, с помощью которого можно редактировать и дополнять записи, выполнять поиск записей в БД по заданному критерию (рис. 8.2).
Номер первой записи
Добавление записи
Удаление записи
Назад к записи
Вперед к записи
Поиск записи по критерию
Выход из формы.
Сортировка записей в бд по критериям
Записи в БД можно сортировать по значениям ячеек одного или нескольких полей по возрастанию или убыванию.
Порядок сортировки:
Поместить курсор в любую ячейку БД и выполнить команды меню Данные, Сортировка. Появляется окно Сортировка диапазона .
- Выбрать необходимое поле для сортировки.
Выбрать переключатель — по возрастанию или убыванию.
Щелкнуть кнопку ОК.
Примечание. Сортировка по нескольким полям производится с использованием дополнительных пунктов окна Сортировка диапазона.
8.2. Применение команды Автофильтр Команда Автофильтр выполняет отбор записей по критериям одного поля.
Порядок фильтрации:
Поместить курсор мыши в любой ячейке БД.
Выполнить в меню команду Данные, выбрать Фильтр и щелкнуть Автофильтр. После этого в каждом столбце БД появляются кнопки для раскрывающихся списков признаков отбора .
В
Рис. 8.3.
ыбрать поле для критерия фильтрации (например, полеГод рождения ) и щелкнуть кнопку раскрытия. Раскрывается список, в котором выбрать критерий (например,Для возврата к исходной таблице (в режиме фильтрации) выполнить команды: Данные, Фильтр, Отобразить все..
Выход из режима Автофильтр – выполнить повторно команды Данные, Фильтр, Автофильтр .
Дополнительные пункты раскрывающегося списка команды Автофильтр : Все, Первые 10 , Условие…:( рис.8.3)
Пункт Все – позволяет отменить фильтрацию по данному столбцу .
Пункт Первые 10 – вызывает диалоговое окно , с помощью которого можно вывести заданное количество записей с наибольшим (или наименьшим) значением критерия по данному полю.
Пункт Условие – вызываем диалоговое окно , в котором можем установить выборку записей с использованием логических функций И , а также ИЛИ.
8.3. Применение команды Расширенный фильтр
Команда Расширенный фильтр позволяет выполнить отбор записей по критериям нескольких столбцов. Запишем общие положения применения команды Расширенный фильтр:
Перед БД создать таблицу условий отбора, состоящую из скопированного заголовка БД и необходимого количества строк, в которых записать условия отбора. В условия отбора может входить несколько условий, накладываемых на столбцы. Поместить курсор мыши в любой ячейке БД.
Выполнить команды Данные, Фильтр, Расширенный фильтр. Использовать диалоговое окно Расширенный фильтр для указания исходного диапазона БД, диапазона условий и куда выводить результаты отбора.
Выход из режима Расширенный фильтр – команды Данные, Фильтр, Отобразить все.
Пример. Ниже представлена подготовленная часть БД с диапазоном условий :
Рис.
8.4. БД и условия отбора расширенного
фильтра
Рис. 8.6. Результаты фильтрации по команде Расширенный фильтр
Работа с таблицей как с базой данных в среде Excel (сортировка, фильтрация, использование форм, получение итогов).
Для управления большими массивами данных используются специальные программы, предназначенные для работы с базами данных. В Excel также имеются средства для обработки данных, организованных по аналогичному принципу, – это, так называемые, функции списка. В виде списка можно представить таблицу, строки которой содержат однородную информацию. Список характеризует не содержимое таблицы, а способ ее организации. Отдельные записи (комплекты данных списка) должны быть однородны по строкам и/или столбцам. В таблице не должно быть объединенных ячеек! Однако только однородность данных списка по строкам делает возможным применение функции фильтров (если данные однородны по столбцам, таблицу следует транспонировать). Примером базы данных является список адресов, в котором указаны названия и адреса различных фирм или фамилии людей.
Excel распознает списки автоматически. Поэтому если надо, например, отсортировать данные в списке по определенному критерию, достаточно выделить любую ячейку списка. После активизации функции сортировки Excel автоматически выделит всю область списка.
При автоматическом определении (и выделении) списка признаком конца области списка считается первая пустая строка. Поэтому предварительно следует удалить из списка пустые строки или выделить вручную область, для которой должна быть применена функция списка. Предварительное выделение области необходимо также в том случае, если функция должна быть применена только к определенной части списка. Функции списка нельзя использовать, если выделены несмежные области.
При автоматическом выделении области списка Excel в поисках меток (названий) столбцов сравнивает содержимое первой и второй строк области списка. Если данные в этих строках различаются по типу, Excel принимает первую (верхнюю) строку в качестве строки названий. Данные этой строки будут исключены из обрабатываемой области списка.
Если Excel не обнаружит различий между типами данных в первой и второй строках, то на экране появится сообщение о том, что программа не смогла обнаружить названий для образования меток столбцов. В этом случае программа выдаст запрос, следует ли использовать в качестве меток столбцов данные первой строки выделенной области. Пользователь может согласиться нажатием [ОК] или отменить выполнение операции.
Основными возможностями при работе с базами данных являются:
организация ввода данных;
просмотр данных;
поиск данных по заданному критерию;
сортировка данных;
фильтрация данных;
подведение итогов.
Для работы с таблицей как с базой данных в Excel используется пункт меню Данные. Эта команда корректно работает только с таблицами, в которых названия колонок занимают одну строку.
Удобным средством для работы с таблицей является форма (окно формы открывается по команде Данные►Форма. В окне формы (рис.5.15) отображаются все поля одной записи базы данных: слева располагаются названия полей, рядом с ними их значения, доступные для редактирования. Если поле вычисляемое, то оно не доступно для редактирования.
Работа с формой предполагает:
1. Перемещение по полям записи мышью или клавишей [Tab];
2. Перемещение по записям с помощью полосы прокрутки и кнопок [Назад] и [Далее];
3. Ввод или просмотр записей. Достоинством ввода информации через форму является автоматическое копирование формул и автоматическая поддержка форматов данных.
4. Удаление и добавление записей с помощью кнопок [Добавить] и [Удалить]. При добавлении новой записи, она всегда добавляется в конец таблицы.
5. Редактирование значений полей в записях.
6. Поиск данных по критерию с помощью кнопки [Критерии], после нажатия которой в появившемся окне в соответствующих полях задаются критерии. Задание критериев позволяет просматривать через окно Формы только те записи, которые удовлетворяют некоторым условиям поиска (критериям).
Перед тем как начать поиск записей по критерию, рекомендуется сделать текущей первую запись базы данных. Просмотр отобранных записей по критерию осуществляется с помощью кнопок [Назад] и [Далее]. При задании критериев поиска можно использовать символы подстановки:
*– для обозначения произвольного количества символов;
?– для обозначения одного символа.
Например, при необходимости выбора всех записей с фамилиями студентов, начинающихся с буквы «К», в качестве критерия поиска следует ввести К*. А при задании критерия К?рсанов, будут выбираться фамилии Кирсанов, Керсанов, Корсанов и т.п.
В критериях при поиске числовых значений можно использовать операторы сравнения: =, <, >, <>, <=, >=.
Чтобы задать несколько критериев поиска, следует указать их в различных полях, тогда они объединяются логическим И. Задание нескольких критериев позволяет сузить область поиска.
Упорядочение табличных данных в выделенных строках по алфавиту, величине или дате называется сортировкой. Столбец, определяющий порядок данных, называются ключом сортировки. Сортировка производится в убывающем или возрастающем порядке. Для сортировки в Excel используется команда Данные►Сортировка. Можно задавать три уровня сортировки одновременно: за одну сортировку можно выполнить сортировку сначала по первому уровню, потом в полученном списке – по второму, а затем – по третьему (рис. 5.16).
Если сортировка ведется по нескольким ключам, то строки с одинаковыми значениями в столбце, указанном в поле Сортировать по, сортируются в порядке, определяемом столбцом, указанным в поле Затем по. Строки с одинаковыми значениями в первых двух столбцах сортируются по столбцу, указанному в поле В последнюю очередь, по.
Excel дает возможность выборочно работать с данными, удовлетворяющими условиям (критериям) поиска, которые задает пользователь. Процесс выбора данных называется фильтрацией. Фильтрация происходит в пределах предварительно выделенного диапазона или заданной таблицы.
В Excel существует возможность фильтрации данных с помощью Автофильтра и Расширенного фильтра.
Использование автофильтра
С помощью функции автофильтра выбор отдельных записей можно производить непосредственно в самой таблице. При этом для выбора данных можно задавать целый ряд различных критериев. Активизация функции автофильтра происходит путем выбора команды Данные►Фильтр► Автофильтр. Фильтрация таблицы оставляет на экране для обработки только те записи, которые удовлетворяют критериям, остальные строки становятся скрытыми. Команда вызова Автофильтра помещает кнопки раскрывающихся списков в названия полей, при помощи этих кнопок задаются критерии отбора. Стрелки кнопок раскрывающихся списков тех полей, которые задействованы в критерии, меняют цвет с черного на голубой.
Если задать критерии отбора в нескольких столбцах (полях), то они связываются между собой по принципу логического И.
Автофильтрпредоставляет несколько видов фильтрации данных:
1. Выбор записей с заданным значением поля происходит путем выбора в раскрывающемся списке значения поля для поиска точного соответствия.
2. Выбор записей по условию производится командой Условие, в результате чего открывается диалоговое окно Пользовательский автофильтр, где задают критерии с участием одного или двух условий с использованием шаблонов(”?” и “*”) , операций сравнения и логических операций И, ИЛИ.
3. Выбор первых наибольших или наименьших n значений позволяет выполнить команда Первые 10.
4. Команда Все восстанавливает на экране все скрытые фильтром строки таблицы.
Использование расширенного фильтра
Расширенный фильтр предоставляет широкие возможности поиска и фильтрации. Он позволяет не только применять операции И, ИЛИ, но и составлять вычисляемые критерии. Кроме того, отфильтрованные данные могут быть скопированы в заданный диапазон рабочего листа.
При работе с расширенным фильтром создаются три области:
1. Исходный диапазон. Область, где хранятся исходные данные. Содержит диапазон, подлежащий фильтрации, т.е. исходную таблицу обязательно вместе с шапкой таблицы.
2. Диапазон условий. Область на рабочем листе, где задаются критерии поиска информации. Диапазон условий применяется для задания условия отбора записей и формируется из строки заголовков полей, которые будут ключевыми при отборе записей, и строки или строк критериев.
При создании диапазона условий необходимо пользоваться следующими рекомендациями:
В верхней строке диапазона критериев должны быть расположены имена, в точности, совпадающие с заголовками столбцов исходного диапазона. Можно перечислить все имена, но это необязательно, достаточно указать только те, которые определяют условия отбора. Точное соответствие имен полей исходного диапазона и имен, указанных в диапазоне условий, проще всего обеспечить копированием из самой таблицы.
Ниже должна располагаться хотя бы одна строка, где задаются условия. В условия отбора расширенного фильтра может входить несколько условий, накладываемых на один столбец, несколько условий, накладываемых одновременно на несколько столбцов, а также условия, накладываемые на вычисляемое формулой значение.
Для объединения критериев с помощью логического И нужно указать задаваемые критерии в одной строке, а для объединения критериев с помощью логического ИЛИ следует представить критерии в разных строках. Следует также учитывать, что в случае необходимости вместе с критерием в ячейку надлежит ввести оператор сравнения. Для обозначения точного соответствия поля записи заданному критерию при задании критериев знак равенства (=) не используется.
3. Выходной диапазон. Область, в которую Excel копирует выбранные из таблицы данные. Этот диапазон должен быть расположен на том же листе, что и исходный. Извлеченную информацию можно поместить на другой лист копированием результата фильтрации. Задание выходного диапазона необязательно, т. к. существует опция “Фильтровать список на месте”. В этом случае фильтрация преобразует исходную таблицу в набор записей, удовлетворяющих условиям выбора.
Диапазон условий и выходной диапазон рекомендуется отделять от исходного диапазона, хотя бы одной пустой строкой (если они будут располагаться ниже или выше исходного диапазона) или одним пустым столбцом (если они будут располагаться левее или правее исходного диапазона). Если же предполагается, что исходный диапазон со временем будет расширяться, то диапазон условий рекомендуется разместить выше списка.
Часто бывает необходимо посчитать промежуточные и общие итоги в таблице. При этом таблица должна быть отсортирована по столбцам, которые являются группировочными признаками. Команда Данные►Итоги добавляет строки промежуточных итогов для каждой группы элементов. Допускается использование различных функций (например, среднего значения, количества строк или пустых ячеек, стандартное отклонение и т.д.) для вычисления итогов в пределах каждой группы.
В диалоговом окне Промежуточные итоги поле При каждом изменении в используется для указания столбца, по которому следует сгруппировать данные для подведения итогов; поле Операция позволяет использовать различные функции; поле Добавить итоги по: позволяет отметить все поля, по которым будут подводиться итоги. Установка флажка Заменить текущие итоги позволяет заменить в таблице уже существующие итоги. Установленный флажок Конец страницы между группами автоматически вставляет конец страницы перед каждой группой данных, для которой вычисляются итоги. Флажок Итоги под данными дает возможность поместить строки промежуточных и общих итогов под соответствующими данными. Кнопка [Убрать все] позволяет восстановить исходный вид экрана до подведения итогов.
Связанныминазываются таблицы, в которых данные из одних таблиц используются в других таблицах или участвуют в вычислениях, производимых в других таблицах. Связь устанавливается в результате задания внешней ссылки на другие листы той же или другой рабочей книги. Связь играет большую роль в тех случаях, когда значения одной таблицы зависят от значений в другой таблице. При изменении значений в одной таблице изменяются и значения в тех ячейках, которые содержат ссылки на эту таблицу, т.е. речь идет о динамической связи. Внешняя ссылка на ячейку, находящуюся на другом листе этой же рабочей книги состоит из имени листа и адреса ячейки, разделенных восклицательным знаком:
Имя_листа!Адрес_ячейки
Например, Товары!В5. Ссылка на ячейку, находящуюся в другой книге имеет вид:
Создание и работа с базой данных в MS Excel
Практическая работа №8
Тема занятия: Создание и работа с базой данных в MS Excel.
Цель занятия: Научиться создавать базы данных в MS Excel. Изучить основные способы обработки данных в базе данных MS Excel.
План занятия:
Создание базы данных в MS Excel. Добавление, изменение и поиск данных в базе данных MS Excel с помощью форм. Функции работы с базой данных. Создание базы данных в MS Excel.
База данных — интервал ячеек, формирующих список или базу данных. База данных представляет собой список связанных данных, в котором строки данных являются записями, а столбцы — полями. Верхняя строка списка содержит названия всех столбцов.
• Создайте новую рабочую книгу.
• Лист 1 переименуйте в База данных.
• На листе База данных Создайте таблицу следующего вида:
|
A |
B |
C |
D |
E |
|
|
1 |
Дерево |
Высота |
Возраст |
Урожай |
Доход |
|
2 |
|
||||
|
3 |
|||||
|
4 |
Дерево |
Высота |
Возраст |
Урожай |
Доход |
|
5 |
Яблоко |
18 |
20 |
14 |
105,00 грн. |
|
6 |
Груша |
12 |
12 |
10 |
96,00 грн. |
|
7 |
Вишня |
13 |
14 |
9 |
105,00 грн. |
|
8 |
Яблоко |
14 |
15 |
10 |
75,00 грн. |
|
9 |
Груша |
9 |
8 |
8 |
76,80 грн. |
|
10 |
Яблоко |
8 |
9 |
6 |
45,00 грн. |
|
11 |
Яблоко |
9 |
6,4 |
5 |
45,04 грн. |
|
12 |
Груша |
10 |
15 |
14 |
96,00 грн. |
|
13 |
Вишня |
11 |
8 |
10 |
105,00 грн. |
|
14 |
Яблоко |
13 |
9 |
9 |
75,00 грн. |
|
15 |
Груша |
14 |
20 |
10 |
76,80 грн. |
|
16 |
Яблоко |
9 |
12 |
14 |
45,00 грн. |
|
17 |
Яблоко |
8 |
14 |
10 |
96,00 грн. |
|
18 |
Груша |
16 |
15 |
9 |
105,00 грн. |
|
19 |
Вишня |
13 |
8 |
10 |
75,00 грн. |
|
20 |
Яблоко |
14 |
9 |
8 |
76,80 грн. |
|
21 |
Груша |
9 |
20 |
14 |
45,00 грн. |
|
22 |
Яблоко |
8 |
12 |
10 |
45,04 грн. |
2. Добавление, изменение и поиск данных в базе данных MS Excel с помощью форм.
Открытие формы
• Выделите диапазон ячеек А4:Е22
• Выполните Данные→Форма.
• В окне формы с помощью колеса прокрутки можно просматривать данные внесенные в базу данных.
Добавления данных
• В окне формы нажмите кнопку Добавить
• Заполните поле Дерево – Абрикос; Высота – 12; Возраст – 16; Урожай – 100; Доход – 120.
• Нажмите Enter.
Задание: Добавьте произвольно 10 новых записей в базу данных.
Удаление данных
Для удаления какой либо записи найдите ее в списке данных и нажмите кнопку Удалить.
Поиск данных в базе данных
Найдем все деревья названия которых начинаются на букву Я приносящих доход больше 10 гривен.
- В окне формы нажмите кнопку
Критерии
Дерево
- Введите Я* В поле
Доход
- Введите >10 Нажмите
Enter.
- Для просмотра других результатов отбора данных по заданному критерию нажмите кнопку
Далее.
Задание: Найдите все данные о деревьях, урожай которых превышает 20, а название заканчивается на «А».
3.Функции работы с базой данных.
Пример1: Определим суммарную высоту всех яблок.
- В ячейку А2 введите Яблоко Выделите ячейку G2 Выполните
Вставка→Функция.
- Выберите категорию функций
Работа с базой данных
БДСУММ.
ОК.
База данных
- Укажите диапазон ячеек содержащих данные и поля вашей базы данных без диапазона критериев, например А4:Е23. В графе
Поле
- Введите адрес ячейки с подзаголовком поля
Высота,
- Например В4. В поле
Критерий
- Введите диапазон ячеек А1:А2. Нажмите
ОК.
Задание: Определите суммарный доход со всех Вишен.
Пример2: Определим сколько всего имеется деревьев яблонь и вишен.
- В ячейку А3 введите
Вишня,
Яблоко
- Выделите ячейку I2 Выполните
Вставка→Функция.
- Выберите категорию функций
Работа с базой данных
БСЧЁТ.
ОК.
База данных
- Укажите диапазон ячеек содержащих данные и поля вашей базы данных без диапазона критериев, например А4:Е23. В графе
Поле
- Введите адрес ячейки с подзаголовком поля
Высота,
- Например В4. В поле
Критерий
- Введите диапазон ячеек А1:А3. Нажмите
ОК.
Задание: Подсчитайте сколько всего в нашей базе данных деревьев превышающих высоту 11 метров.
Пример3: Определим средний доход со всех деревьев высотой больше 12
- В ячейку В2 введите >12 Выделите ячейку K2 Выполните
Вставка→Функция.
- Выберите категорию функций
Работа с базой данных
ДСРЗНАЧ.
ОК.
База данных
- Укажите диапазон ячеек содержащих данные и поля вашей базы данных без диапазона критериев, например А4:Е23. В графе
Поле
- Введите адрес ячейки с подзаголовком поля
Доход,
- Например Е4 В поле
Критерий
- Введите диапазон ячеек B1:B2. Нажмите
ОК.
Задание: Определите среднюю высоту деревьев старше 10 лет
Контрольные вопросы:
Что такое база данных Что такое поле? Для чего необходим диапазон критериев? Как добавить новую запись в базу данных? Как найти нужную запись в базе данных по заданному критерию? Какие функции работы с базами данных вы знаете?
Вывод: В данной работе мы рассмотрели основные способы создания и работы с базами данных в MS Excel. Выполнив данную работу можно сказать о том что, средства электронной таблицы Excel позволяют не только создавать таблицы содержащие данные, но эти средства также позволяют проводить сложнейшие аналитические вычисления, опираясь на указанные пользователем критерии.
Урок информатики по теме «Работа в Excel как с базой данных»
Общие цели:
дать понятие о БД, структуре и назначении;
выяснить требования, предъявляемые к организации данных в БД, операции сортировки и фильтрации.
Ход занятия
Организационный момент
Вводный тренинг Психологический настрой на работу.
Учитель предлагает взяться за руки, улыбнуться друг другу. Пожелать удачи.
Формирование групп методом случайного выбора и распределение ролей в группе (аналитик, эксперт, спикер, режиссер)
Погружение в тему
Целеполагание
Учитель «Когда мы взялись за руки, у нас образовалась какая-то единая структура, у такой структуры есть название
Тема урока «Базы данных»
Мозговой штурм «Зачем нужны базы данных?»
Учащиеся предлагают свои варианты, учитель фиксирует на доске.
Учитель предлагает учащимся сформулировать цели урока:
Учащиеся предлагают свои варианты:
— выяснить, что такое База данных,
— как создать базу данных
— возможности программы Excel при создании базы данных
— научится слушать друга, защищать свою позицию
— выполнять операции с БД
Групповая работа
Каждой группе предлагается задание
1 группа: подпиши элементы интерфейса
2 группа: найти соответствие (элементы таблицы)
3 группа: перепутанная цепочка (восстанови алгоритм: алгоритм ввода формулы, создания диаграммы)
Защита заданий
Рефлексия. Оценивание
Спикер (ученик В) защищает работу группы
Наблюдатель (ученик А) оценивает работу группы на листах взаимооценивания
Рефлексия проводится в рабочих тетрадях, там же даны критерии для самооценки работы в группе
Работа с текстом
Учащимся предлагается текст «Базы данных». Учащиеся в группах обсуждают основные понятия «база данных, тип данных, поле, запись, сортировка, фильтрация»
БАЗЫ ДАННЫХ В EXCEL
Для хранения и обработки больших объемов информации используются базы данных.
Каждая база данных хранит информацию о большом количестве объектов одинакового типа (организациях, людях, книгах и т. д. ). Объекты одного типа обладают одинаковым набором свойств, поэтому база данных хранит для каждого объекта значения этих свойств.
База данных позволяет упорядоченно хранить данные о большом количестве однотипных объектов, обладающих одинаковым набором свойств.
База данных (date base) – это совокупность хранимых в памяти компьютера данных, которые отображают состояние некоторой предметной области. Данные взаимосвязаны и специальным образом организованы.
Excel умеет складывать, вычитать, умножать, делить и выполнять множество других операций. Excel дает возможность предварительно проанализировать последствия принятия тех или иных решений при конкретных обстоятельствах. Excel позволяет автоматизировать не только расчеты как таковые, но позволяет создавать и работать с разнообразными картотеками, системами учета, базами данных и т. п.
Вопросы сбора данных, их хранения, учета и обработки можно решить, имея систему управления списками. Термин список используется в Excel для обозначения базы данных.
База данных – это особый тип рабочей таблицы, в которой не столько вычисляются новые значения, сколько размещаются большие объемы информации в связанном виде.
Например, можно создать базу данных с фамилиями, именами, адресами и номерами телефонов ваших знакомых или список группы со всей информацией об итогах сессии и о размере соответствующей стипендии или ее отсутствии.
Базы данных удобно представлять в виде таблицы. В каждой строке таблицы размещаются значения свойств одного объекта, а каждый столбец таблицы хранит значения определенного свойства всех объектов. Столбцы табличной базы данных называют полями. Строки таблицы называются записями (т. е. это записи об объекте). Запись хранит набор значений, содержащихся в полях базы данных.
Каждое поле имеет ряд свойств: имя поля, тип поля, которых определяется хранимыми данными. Типы используемых данных: числовой, текстовый, денежный, дата и время и т. д.
Если с файлом работает сразу несколько пользователей, желательно контролировать тип вводимой ими информации и свести к минимуму ошибки ввода. В Excel выполнение подобных условий проверяется при помощи средства, которое называется проверкой ввода.
Для этого надо:
Выделить ячейки столбца, для которого устанавливается проверка ввода.
На ленте Данные в группе Работа с данными выбрать команду Проверка данных.
На вкладке Параметры в области Условие проверки выбрать Тип данных: Любое значение (используется для отмены проверки ввода), Целое число, Действительное, Список, Дата, Время, Длина текста и Другой (формат, для которого можно задать собственную формулу, например, «м» or «ж»). При выборе значения внизу окна появляются дополнительные поля для ввода условий или ограничений – например, минимального и максимального допустимого значения.
На вкладке Сообщение для ввода можно установить флажок Отображать подсказку, если ячейка является текущей и ввести сообщение, чтобы оно появлялось на экране при выделении ячеек.
На вкладке Сообщение об ошибке можно установить флажок Выводить сообщение об ошибке, чтобы задать тип сообщения об ошибке, которое появится при вводе в ячейку недопустимого значения.
Весь материал — смотрите архив.
Практическая работа № 7. «Базы данных в ms Excel»
Цель работы: Научиться создавать базы данных в MS Excel. Изучить возможности работы с базами данных.
Задание:
Оформление базы данных.
Создание формы.
Сортировка элементов базы данных.
Фильтрация данных.
Суммирование чисел в базе данных.
Пример выполнения задания:
Создать базу данных сотрудников предприятия, заголовок которой имеет вид:
№ | Фамилия | Имя | Отчество | Должность | Код |
1 | Иванов | Иван | Иванович | Менеджер | 3750 |
Заполнить базу данных на 15 сотрудников.
Оформление базы данных производится так же, как и оформление любой таблицы в MS Excel. Только следует помнить, что таблица для базы данных не должна иметь пустых строк и пустых столбцов, а также не допускается объединение ячеек для данной таблицы.
В нашем случае заголовок имеет вид:
Теперь в ячейку A4 запишем:
=СТРОКА(А4)-3
и растянем эту формулу до ячейки А18.
Далее заполняем ячейки B4:F18 данными, получим:
Рассмотрим работу с формой базы данных, для этого добавим при помощи формы еще двух сотрудников. Выполним: \Данные\Форма…
получим диалоговое окно вида:
Теперь если мы хотим добавить запись в базу данных, то нажмем кнопку «Добавить» и получим:
Заметим, что поле ввода номера не доступно, так как там заложена формула. Мы заполняем только доступные нам поля и получаем:
Заметим, что для перехода из одного поля ввода в другое необходимо нажимать клавишу TAB.
Теперь для того, чтобы добавить еще одного сотрудника, снова нажмите кнопку «Добавить» и введите данные этого сотрудника.
Для завершения добавлений в базу данных необходимо нажать клавишу «Закрыть». Произойдет выход из режима «Форма», а также добавление сотрудников в базу данных.
Обращаем ваше внимание на то, что сотрудники добавляются не по алфавиту, а в конце списка.
В результате наша база данных приобретет вид:
Теперь рассмотрим сортировку базы данных.
Сортировать базу данных в MS Excel можно по столбцу любого типа в порядке возрастания или убывания. Допускается задание от одного до трех критериев сортировки.
Установите курсор в любую ячейку базы данных. В меню «Данные» выберите пункт «Сортировка…»:
Получим диалоговое окно вида:
Теперь заполним поле «Сортировать по». Для начала отсортируем нашу базу данных по «Фамилиям» в порядке возрастания, получим:
Обратите внимание, что заданная нами формула для «№» позволяет сохранять правильную нумерацию независимо от сортировки.
Теперь отсортируем нашу базу данных по двум столбцам. Сначала отсортируем по «Должности», а потом по «Коду». Для этого в диалоговом окне сортировки наберем:
В результате получим:
В результате такой сортировки на первые позиции встали все «Координаторы», причем между собой они отсортированы по «Коду». В конце у нас «Менеджеры», которые тоже отсортированы между собой по «Коду».
Фильтрация базы данных предусмотрена для того, чтобы быстро извлекать из документа записи, которые соответствуют указанным критериям, а затем переносить эту информацию в другие части листа или применять в отчетах.
Для извлечения информации из базы данных по заданному критерию установите курсор в любую ячейку базы данных и выполните:
\Данные\Фильтр\Автофильтр
В результате вы получите:
Обратите внимание, что в строке заголовка появились кнопки со стрелками.
Теперь с помощью фильтра оставим всех «Менеджеров» с «Кодом» из диапазона от 3755 до 3760. Для этого нажмем кнопку со стрелочкой в столбце «Должность» и выберем «Менеджер»:
Далее нажмем кнопку со стрелочкой в столбце «Код» и выберем «Условие», в результате получим диалоговое окно вида:
Заполнив это окно так, как показано на рисунке, получим:
Обратите внимание на то, что вы не удаляли ничего, а просто выбрали лишь те элементы, которые вам необходимы. Стрелочки в столбцах, по которым задавались критерии для фильтрации, выделены цветом, так что если вам необходимо отменить данную фильтрацию, достаточно у выделенных стрелочек выбрать раздел «Все».
Если вы хотите снять фильтр, то выберите:
\Данные\Фильтр\Автофильтр
Примечание. Для того чтобы не только извлечь, но и переместить данные, выполните команду: \Данные\ Фильтр\Расширенный фильтр… В открывшемся диалоговом окне включите опцию «Скопировать результат в другое место» и укажите, куда копировать результат.
Вы можете подводить промежуточный и окончательный итоги, анализируя любой список базы данных, содержащий числовую информацию. Для получения промежуточных итогов весь список должен быть разбит на отдельные группы записей. Чтобы программа Excel распознала эти группы, список следует отсортировать.
Для того чтобы изучить эту возможность, добавим в нашу базу данных еще один столбец «Зарплата» и отсортируем ее по «Фамилиям», получим:
Установим курсор в любую ячейку базы данных и выполним:
\Данные\Итоги…
получим диалоговое окно вида:
Укажем подводить промежуточные итоги при каждом изменении «Должности», операция «Сумма», а итоги подводить только по «Зарплате» и нажмем кнопку «ОК», получим:
Теперь отменим итоги, для чего выполним \Данные\Итоги… и в появившемся диалоговом окне нажмем кнопку «Убрать все». Отсортируем базу данных по «Должности» и вновь установим итоги по тому же принципу, что и ранее, получим:
Для
того чтобы вывести только промежуточные
итоги, необходимо щелкнуть по кнопке
со знаком «-» (
),
которая расположена в левой части окна.
В результате получим:
Оставшийся минус в левой части окна позволяет вам увидеть только итоговый результат.
Зачетное задание:
План-конспект по информатике и икт по теме: План урока по теме «Работа с таблицей как с базой данных в MS Excel»
План занятия
№ занятия 23,24
Тема занятия: «Работа с таблицей как с базой данных».
Дата проведения 02 марта 2018
Дисциплина: Информационные технологии в профессиональной деятельности
Профессия: 29.01.04. «Художник по костюму»
Тип занятия: комбинированный
Вид занятия: урок-практикум
Уровень усвоения учебной информации второй, воспроизведение
Цели занятия:
- Образовательная
Познакомить обучающихся с основными понятиями темы; сформировать у обучающихся умения по обработке данных, представленных в виде списка средствами программы Microsoft Excel;
углубить знания обучающихся по теме: обработка данных средствами Microsoft Excel;
- Воспитательная
Содействие формированию общих компетенций: ОК 2, ОК 3, ОК 4, ОК 5
Воспитание в обучающихся интереса к информационным технологиям, к возможностям программных продуктов;
- Развивающая
Продолжить формирование у обучающихся аналитического мышления, памяти, выделять главное в представленной информации, систематизировать информацию
Методы обучения: словесные, наглядные и практические.
Междисциплинарные и внутрипредметные связи: Тема «Базы данных»
Средства обучения: персональные компьютеры с установленными ОС Windows и пакетом Microsoft Office 2010 по количеству обучающихся, раздаточный материал, презентация, тест.
По итогам обучения обучающийся должен:
Иметь представление о базах данных, их роли в современном мире;
Знать: правила оформления баз данных (списков) в среде Excel, способы ввода данных в списки, приемы обработки записей базы данных;
Уметь: создавать списки в среде Excel, вводить данные списка, обрабатывать записи базы данных (производить сортировку, поиск и фильтрацию записей).
Ход занятия
Этап занятия | Познавательная деятельность обучающихся | время | Деятельность преподавателя | Деятельность обучающихся | Формируемые ОК | Средства обучения и контроля |
1.Организационный | α1 | 2 | -приветствие — проверка готовности обучающихся и кабинета к учебному занятию; — проверка посещаемости занятия | -приветствие — готовность к учебному занятию; — участие в проверке посещаемости занятия | ОК 2 | беседа |
2.Мотивационный компонент | α2 | 5 | — задает вопросы о назначении и возможностях программы Microsoft Excel; — предлагает решить проблемную ситуацию: в техникум пришел запрос о подтверждении факта обучения студента в данном учреждении, а в техникуме всего 1200 студентов; — совместно с обучающимися озвучивает наиболее рациональный способ решения задачи (в электронном варианте списка всех студентов отобрать того, кто указан в данном запросе) и выясняет, что такой список должен содержать определенную информацию и представляет собой таблицу, которую также можно сформировать и в программе Microsoft Excel — подводит обучающихся к формулировке темы и цели занятия — совместно с обучающимися формулирует тему и цели занятия | — отвечают на заданные вопросы; — предлагают свои варианты решения задачи; — уяснение и запись темы занятия в тетрадь; — осмысление цели занятия | ОК 2 | Вопросно-ответное общение |
3.Актуализация опорных знаний | α2 | 10 | Предлагает выполнить тесты по теме «Microsoft Excel» на компьютерах и объясняет, что в строке ответ нужно указать только номер правильного ответа Фиксирует результаты выполнения теста | Каждый на компьютере выполняет тестовые задания, после выполнения задания показывают преподавателю результат | ОК 2, ОК 3 | Компьютерный тест |
4.Формирование знаний 4.1. Объяснение темы | α2 | 18 | — объясняет тему, используя слайды презентации по плану:
Показывает приемы оформления базы данных, сортировки и фильтрации записей в MS Excel Задает вопрос о том, есть ли необходимость использования баз данных в профессии «Художник по костюму», если они нужны то просит привести примеры | Слушают, записывают основные понятия в тетрадь — отвечают на вопрос, приводят примеры (база данных клиентов, своих изделий и т.д) | ОК 2, ОК 4 | Презентация, объяснение Вопросно-ответное общение |
4.2. Первичное закрепление полученных знаний, умений | α2 | 10 | Предлагает выполнить практическое задание с адаптацией Практическая работа №10.1 Объясняет, что учебные материалы в электронном виде по теме есть у каждого на Рабочем столе в файле под именем «Теория списки в Excel» Консультирует обучающихся по возникшим вопросам в ходе выполнения задания | Выполняют на компьютере практическую работу №10.1 При возникновении вопросов обращаются к преподавателю за консультацией | ОК 5 | Практическая работа №10.1 с разъяснениями |
4.3. Применение полученных знаний на практике | α2 | 30 | Дает задание выполнить Практическую работу №10.2 Консультирует обучающихся по возникшим вопросам в ходе выполнения задания Проверяет результаты выполнения работы Правильные ответы по ПР высвечиваются на экране и преподаватель предлагает каждому сверить свои работы и оценить выполнение задания | Выполняют на компьютере практическую работу По выполненной работе составляют отчет Каждый обучающийся сверяет свои ответы с эталоном ответов, отмечают ошибки, оценивают и после этого предъявляют тетради преподавателю | ОК 5 | Практическая работа №10. эталон, |
5.Подведение итогов урока | α1 | 2 | подводит итоги работы на уроке, выставляет в журнал оценки и комментирует их | слушают и задают вопросы | ОК 2 | Беседа |
6. Рефлексия | α2 | 2 | Предлагает дополнить выражения обучающимся — я сегодня узнал… — в начале урока были поставлены задачи… — у меня ещё остались следующие вопросы по изученной теме… — мне было сложно … | Обучающиеся дополняют выражения, задают вопросы по теме, если они есть | ОК 5 | беседа |
7.Информирование о домашнем задании | α1 | 1 | — подготовка сообщения на тему «Применение программы MS Excel в бизнесе» (с примерами) | — уяснение и запись темы задания в тетрадь | ОК 2 | объяснение |
- Ефимова О.В. Microsoft Excel. Электронные таблицы. Тетрадь 1,2 Интеллект-Центр
- www.infourok.ru