Как выполнить поиск, изменение и удаление записей

Поможем в ✍️ написании учебной работы

Имя

Поможем с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой

Нажимая кнопку "Продолжить", я принимаю политику конфиденциальности

СОДЕРЖАНИЕ

ВВЕДЕНИЕ

1. БАЗЫ ДАННЫХ В EXCEL.

1.1. Как создать базу данных.

1.2. Как выполнить поиск, изменение и удаление записей.

1.3. Обработка данных в БД.

1.4. Обмен данными.

2.1. Проектирование базы данных.

2.1.1. Структура базы данных

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

2.2. Создание базы данных.

2.2.1 Создание заголовка таблицы и первой строки.

2.2. Заполнение таблицы с помощью Мастера форм.

2.3. Ведение базы данных

2.3.1. Редактирование полей.

2.2. Редактирование записей

2.4.Начальная обработка данных.

2. 4.1. Добавление суммы по столбцам

2.4.2. Добавление суммирования по критерию.

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

2.5.1. Простая сортировка по полю.

2.5.2 Сортировка по нескольким полям.

2.6. Формы представления информации, содержащейся в базе данных.

2.6.1. Добавление промежуточных итогов.

2.6.2. Работа со структурой

2.7. Анализ информации, содержащейся в базе данных.

2.7.1 Вычисление статистических характеристик

2.7.2. Работа с функциями из раздела Базы данных

2.8. Выборочное использование данных

2.8.1. Пользовательский автофильтр.

2.8.2 Расширенный фильтр.

2.9. Графическое представление данных.

ВВЕДЕНИЕ

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

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

Примерами пакетов ведения электронных таблиц являются Supercalc , Lotus 1-2-3 и MS Excel. В данной работе мы будем рассматривать работу с базами данных в MS Excel.

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

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

(б) качество документации, которая поставляется с пакетом;

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

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

БАЗЫ ДАННЫХ В EXCEL.

База данных (date base) – это совокупность хранимых в памяти компьютера данных, которые отображают состояние некоторой предметной области. Данные взаимосвязаны и специальным образом организованы.

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

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

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

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

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

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

Как создать базу данных.

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

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

База данных создается в обычной электронной таблице, но с выполнением таких правил:

ÿСтрока заголовков столбцов (верхняя строка списка ) должна быть заполнена именами полей.

ÿКаждая запись должна размещаться в отдельной строке.

ÿПервую запись необходимо разместить в строке, следующей непосредственно за строкой заголовков.

ÿСледует избегать пустых строк между записями.

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

Фамилия	Имя	Отчество	Высш. матем.	Информ.	История Укр.	Физика	Ср.балл	Стипендия
Абдельгадир	Мусса	Ибрагимович	4	5	3	3	3,75	0

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

Ср.балл	Стипендия
=СРЗНАЧ(E2:H2)	=ЕСЛИ(I2<4;0;ЕСЛИ(I2<4,5;12;15))

Примечание. В ячейки E2:H2 таблицы вводятся оценки, а в ячейку I2 – средний балл. Стипендия вычисляется по формуле: если Ср.балл меньше 4, стипендия не начисляется (=0); если Ср.балл больше 4, но меньше 4.5, начисляется стипендия 12 гр., а если больше 4.5, то – 15гр.

Затем необходимо отметить обе строки ( с именами полей и формулами ) и выбрать команду Форма из меню Данные. После этого Excel анализирует введенную в первые две строки информацию и создает форму с данными, в которой сверху вниз перечислены все имена полей в левой части и элементы первой записи в текстовых окнах рядом с ними. Текстовые окна у вычисляемых полей отсутствуют.

Чтобы ввести новую запись, нужно щелкнуть на кнопке Добавить и ввести данные в пустые поля. Для перехода к очередному полю следует нажимать клавишу <Tab> .При заполнении БД необходимо соблюдать следующие правила:

ÿНельзя нажимать клавишу Enter для перехода к следующему полю, так как при этом добавится неполная запись!

ÿЧтобы отредактировать значение в пройденном поле, следует нажимать клавиши <Shift + Tab> для возврата в нужное поле.

ÿЧтобы заменить ранее введенное значение, необходимо просто начать вводить нужное в этом поле.

ÿЧтобы отредактировать только некоторые символы значения поля, следует нажимать клавишу < – > или щелкнуть I-образным указателем мыши на том месте, куда необходимо поместить курсор.

ÿПри вводе даты необходимо придерживаться известного программе формата

ÿПри вводе чисел с лидирующими нулями (кроме почтовых индексов), вводится апостроф перед первым нулем, что воспринимается Excel как текстовая метка (при этом апостроф в БД не вводится).

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

ÿПосле завершения ввода записей следует нажать клавишу <Esc > или щелкнуть на кнопке Закрыть , чтобы закрыть форму данных.

ÿСохранить рабочую таблицу командой Сохранить из меню Файл или щелкнуть на инструменте Сохранить на стандартной панели инструментов.

Обработка данных в БД.

Любая информация должна быть упорядочена. Хорошая БД – в том числе. Каждая БД имеет некоторый предпочтительный порядок поддержания и просмотра записей. Записи можно расположить, например, в алфавитном порядке фамилий или названий фирм. Для определения рейтинга студентов список группы удобно расположить по убыванию среднего балла. Однако при добавлении новых записей Excel включает их в самый конец БД, добавляя новые строки и нарушая прежний порядок. Это не единственная проблема с упорядочиванием записей, так как всегда может возникнуть необходимость в другом, особенном порядке. Другими словами, при работе с данными требуется гибкость упорядочения записей для различных целей. Процесс упорядочения БД называется сортировкой.

Для сортировки элементов в БД необходимо выполнить такие действия:

Ø Щелкнуть на любой ячейке БД.

Ø Выбрать команду Сортировка из меню Данные, в результате чего Excel раскроет диалоговое окно Сортировка диапазона.

Ø Щелкнуть на стрелке в группе «Сортировать по» и выбрать главный ключ сортировки ( поле, по которому должна быть выполнена сортировка ).

Ø Выбрать восходящее «по возрастанию» или нисходящее «по убыванию» упорядочение , щелкнув по переключателю справа .

Ø Если нужно выполнить еще одно упорядочивание внутри первого, щелкнуть на стрелке в следующей группе «Затем по» и выбрать второе поле сортировки и затем – восходящий или нисходящий порядок.

Ø При необходимости дальнейшего иерархического упорядочения выбрать поле и порядок упорядочения в последнем списке «В последнюю очередь, по» .

Ø Для отказа от выбора полей и порядка сортировки достаточно , не выходя из этого диалогового окна , снова открыть список полей и выбрать «( не сортировать )».

Ø Щелкнуть на ОК или нажать Enter .

Excel отсортирует отмеченные записи. При необходимости отказаться от ошибочной сортировки следует выбрать Правка – Отменить Сортировку или нажать <Ctrl + Z> для восстановления записей БД в прежнем порядке.

№	Фамилия	Имя	Отчество	Высш. матем.	Информ.	История Укр.	Физика	Ср.балл	Стипендия
4	Грант	Анатолий	Семенович	4	5	5	5	4,75	15
2	Бирюкова	Галина	Олеговна	5	4	4	5	4,5	15
3	Вовченко	Александра	Александровна	3	4	5	4	4	12
1	Абдельгадир	Мусса	Ибрагимович	4	5	3	3	3,75	0

В приведенном примере записи упорядочены по убыванию значений в поле Ср.балл. Прежний порядок записей в БД можно проследить по первому полю «№».

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

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

Для активизации нужного фильтра следует выполнить такие действия:

Ø Щелкнуть где-либо в БД.

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

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

После этого Excel покажет только записи , содержащие в этом поле выбранное значение (все остальные будут временно скрыты). Отфильтрованные записи можно скопировать на другой лист рабочей книги или выдать на печать. Для этого нужно просто:

Ø отметить клетки;

Ø выбрать команду Копировать из меню Правка (или нажать <Ctrl + C>);

Ø переместить табличный курсор в первую ячейку таблицы на новом листе, щелкнув по ярлычку «Лист2» в нижней части экрана;

Ø нажать Enter.

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

№	Фамилия	Имя	Отчество	Высш. матем.	Информ.	История Укр.	Физика	Ср.балл	Стипендия
2	Бирюкова	Галина	Олеговна	5	4	4	5	4,5	15
4	Грант	Анатолий	Семенович	4	5	5	5	4,75	15

Так выглядит БД, отфильтрованная по полю «Стипендия» и значению 15, т.е. содержащая записи, у которых в этом поле стоит значение 15. В этот список вошли только студенты, получающие стипендию в размере 15 гр.

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

При необходимости сузить круг поиска можно продолжить фильтрацию , выбрав значение из раскрывающегося списка другого поля. Excel предоставляет возможность просмотреть только первые 10 записей с помощью опции Первые 10 команды Автофильтр. Эту опцию целесообразно использовать при работе с числовыми полями для вывода на экран только десяти записей ( например, с наибольшими или наименьшими значениями в данном поле). Более того, с помощью этой опции можно вывести на экран любое количество записей, указанное пользователем в диалоговом окне «Наложение условия по списку», которое появляется в ответ на выбор указанной опции.

№	Фамилия	Имя	Отчество	Высш матем.	Информ.	История Укр.	Физика	Ср.балл	Стипендия
2	Бирюкова	Галина	Олеговна	5	4	4	5	4,5	15
3	Вовченко	Александра	Александровна	3	4	5	4	4	12
4	Грант	Анатолий	Семенович	4	5	5	5	4,75	15
7	Иванов	Иван	Иванович	4	4	4	4	4	12
9	Клочко	Георгий	Константинович	5	4	3	4	4	12

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

Дополнительно к фильтрации БД по записям, содержащим определенное значение в поле, можно создавать собственные автофильтры, позволяющие фильтровать БД по записям с более общим критерием, таким как , например, фамилии, начинающиеся с буквы «А», или значения средних баллов в пределах от 4 до 5. Для создания собственного фильтра нужно:

Ø щелкнуть на кнопке раскрывающегося списка в названии поля ;

Ø выбрать опцию Условие ;

Ø в появившемся диалоговом окне Пользовательский автофильтр выбрать необходимый оператор сравнения в первой строке или в обеих строках, если условие составное , т.е. представляет собой результат логических операций типа «и» и «или»; в текстовые окна справа ввести значения (текст или число ), относительно которых должно проводиться сравнение значений в записях БД.

Так для выбора списка студентов, фамилии которых начинаются с буквы «А» необходимо в первой строке диалогового окна Пользовательский автофильтр щелкнуть «равно» и ввести в текстовое окно «А*» (без кавычек ). Получим:

№	Фамилия	Имя	Отчество	Высш. матем.	Информ.	История Укр.	Физика	Ср.балл	Стипендия
1	Абдельгадир	Мусса	Ибрагимович	4	5	3	3	3,75	0
5	Антонова	Вера	Борисовна	3	4	4	3	3,5	0

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

Получим:

№	Фамилия	Имя	Отчество	Высш. матем	Информ.	История Укр.	Физика	Ср.балл	Стипендия
2	Бирюкова	Галина	Олеговна	5	4	4	5	4,5	15
3	Вовченко	Александра	Александровна	3	4	5	4	4	12
4	Грант	Анатолий	Семенович	4	5	5	5	4,75	15
7	Иванов	Иван	Иванович	4	4	4	4	4	12
9	Клочко	Георгий	Константинович	5	4	3	4	4	12

Одним из наиболее мощных средств Excel по работе с БД являются сводные таблицы , которые полезны как для анализа, так и для обобщения информации, хранящейся в БД, на рабочих листах, во внешних файлах. Сводные таблицы позволяют выводить информацию с различной степенью детализации. Для создания сводных таблиц в Excel имеется специальный инструмент Мастер сводных таблиц , в зависимости от версии позволяющий выполнять работу в 3 или в 4 шага.

Разумеется, создавать сводные таблицы имеет смысл только по БД, содержащим значительный объем информации. Расширим первоначальную БД хотя бы до 15 записей и введем новое поле «Группа». Упорядочим список по алфавиту и скорректируем порядок, т.е. данные в поле «№».Получим такую БД:

№	Фамилия	Имя	Отчество	Группа	Высш. матем.	Информ.	История Укр.	Физика	Ср.балл	Стипендия
1	Абдельгадир	Мусса	Ибрагимович	219	4	5	3	3	3,75	0
2	Антонова	Вера	Борисовна	219	3	4	4	3	3,5	0
3	Бирюкова	Галина	Олеговна	219	5	4	4	5	4,5	15
4	Борисова	Нина	Павловна	221	5	5	4	5	4,75	15
5	Вовченко	Александра	Александровна	221	3	4	5	4	4	12
6	Горец	Анатолий	Владимирович	221	3	3	5	4	3,75	0
7	Грант	Анатолий	Семенович	223	4	5	5	5	4,75	15
8	Дмитренко	Петр	Павлович	223	3	4	3	3	3,25	0
9	Дмитренко	Виталий	Игоревич	223	4	4	5	4	4,25	12
10	Замовский	Эдуард	Федорович	235	3	3	4	3	3,25	0
11	Иванов	Иван	Иванович	235	4	4	4	4	4	12
12	Клочко	Георгий	Константинович	235	5	4	3	4	4	12
13	Новиков	Олег	Валентинович	241	3	4	3	3	3,25	0
14	Прокопенко	Виталий	Викторович	241	4	3	3	4	3,5	0
15	Соловьев	Руслан	Анатольевич	241	3	4	5	4	4	12

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

Ø Выполнить команды Данные ––Сводная таблица .

Ø В первом диалоговом окне « Мастер сводных таблиц – шаг 1 из 4 » установить переключатель в положение, определяющее, где находятся данные для сводной таблицы: в нашем случае следует щелкнуть на первом положении переключателя « В списке или базе данных Microsoft Excel ».

Ø Нажать кнопку Далее, в результате чего появится второе диалоговое окно «Мастер сводных таблиц- шаг 2 из 4».

Ø В поле «Диапазон» указать, в каком диапазоне находятся исходные данные для сводной таблицы ( в нашем случае это $A$1:$K$16 ) и нажать кнопку Далее.

Ø В третьем диалоговом окне «Мастер сводных таблиц – шаг 3 из 4» необходимо указать структуру сводной таблицы, т.е. определить, данные какого поля должны использоваться в качестве заголовков строк и каких – в качестве заголовков столбцов, для чего перетащить их названия , представленные в окне в виде кнопок, в соответствующие области «Строка» ( в нашем случае –«Группа» ) и «Столбец» (в нашем случае – ничего ).

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

Ø Задать правило, по которому должна осуществляться обработка, щелкнув дважды по кнопке, перемещенной в область «Данные» , и выбрав в появившемся диалоговом окне «Вычисление поля сводной таблицы» нужную операцию ( для примера о стипендии – «Сумма», в других примерах – «Среднее» ), в результате чего в поле «Имя» появятся названия операции и поля, по которому она будет выполняться.

Ø В диалоговом окне « Мастер сводных таблиц – шаг 4 из 4 » нужно задать некоторые параметры, определяющие вид сводной таблицы:

§ в поле окна «Поместить таблицу в» указать адрес левой верхней ячейки таблицы на текущем листе или оставить поле пустым , чтобы таблица разместилась в начале нового рабочего листа;

§ указать название сводной таблицы в поле «Название таблицы»;

§ установить флажки «Общие итоги по столбцам» и «Общие итоги по строкам», если нужно ;

§ для создания дополнительной копии данных установить флажок «Сохранить данные с макетом таблицы»;

§ установить флажок «Автоматически форматировать таблицу» для использования средств автоформата Excel.

Ø Для вывода сводной таблицы на экран после этого щелкнуть на кнопке Готово.

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

Сумма по полю Стипендия
Группа	Фамилия	Всего
219		15
221	Абдельгадир	0
	Вовченко	12
	Грант	15
221 Всего		27
223	Горец	0
	Иванов	12
	Клочко	12
223 Всего		24
235		12
241		27
Общий итог		105

В приведенной сводной таблице выполнена детализация по полю «Группа 221» и по полю «Группа 223», по остальным группам детализация не задавалась.

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

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

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

Добавление итогов осуществляется после предварительной сортировки БД по нужному полю (например, по номеру группы) следующей последовательностью действий:


Группа	Данные	Всего
219	Среднее по полю Высш.матем.	4
	Среднее по полю Информ.	3,666666667
	Среднее по полю История Украины	3,333333333
	Среднее по полю Физика	4
221	Среднее по полю Высш.матем.	3,666666667
	Среднее по полю Информ.	4,666666667
	Среднее по полю История Украины	4,333333333
	Среднее по полю Физика	4
223	Среднее по полю Высш.матем.	4
	Среднее по полю Информ.	3,666666667
	Среднее по полю История Украины	4
	Среднее по полю Физика	4
235	Среднее по полю Высш.матем.	3,333333333
	Среднее по полю Информ.	3,666666667
	Среднее по полю История Украины	4,333333333
	Среднее по полю Физика	3,333333333
241	Среднее по полю Высш.матем.	3,666666667
	Среднее по полю Информ.	4,333333333
	Среднее по полю История Украины	4
	Среднее по полю Физика	4

Ø Выбрать Данные –- Итоги, в результате чего БД будет автоматически выделена и на экране появится диалоговое окно « Промежуточные итоги ».

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

Ø В строке «Операция» , щелкнув на стрелке справа , выбрать в появившемся перечне возможных операций необходимую ( например, «Среднее» ).

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

Ø Убедиться, что параметр «Заменить текущие итоги» установлен и щелкнуть на кнопке ОК.

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

Excel можно перевести в режим структуры , чтобы отобразить на экране только часть итоговой информации, что очень важно при работе с БД, особенно большими. Щелчками на маленьких кнопках со знаком «минус» и с цифрами «1», «2», «3» можно прятать или выводить на экран различные уровни структуры .БД. Чтобы убрать данные по группе 219 в приведенной БД, достаточно щелкнуть по кнопке со знаком «минус» слева от строки 33 с промежуточными результатами по этой группе (см. Рис.1.1). Чтобы убрать с экрана все, кроме промежуточных и общих итогов , нужно щелкнуть по кнопке второго уровня (с цифрой «2»).Чтобы убрать все, кроме общего итога, нужно щелкнуть по кнопке «1». Чтобы возвратить на экран все записи, нужно щелкнуть по кнопке «3».

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

БДФункция(база_данных;поле;критерий)

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

База_данных – это интервал ячеек, формирующих БД.

Поле определяет столбец, используемый функцией. Аргумент поле может быть задан как текст с названием столбца в двойных кавычках (например, «Информ.»)или как число, задающее положение столбца в БД (например, 7 для того же поля ).

Критерий – это ссылка на интервал ячеек , задающих условия для функции. Функция возвращает данные из списка, которые удовлетворяют условиям, определенным диапазоном критериев. Диапазон критериев включает копию названия столбца, для которого выполняется подведение итогов. Ссылка на критерий может быть введена как интервал ячеек (например, B24:B25).

В приведенном примере подсчитывается количество оценок каждого вида по результатам экзамена по информатике в рассматриваемой БД. В ячейках G19:G22 использованы БДФункции БСЧЕТ, которая просматривает в БД в интервале ячеек A1:K16 записи в 7-ом столбце с именем «Информ.» и подсчитывает количество пятерок по критерию в диапазоне B24:B25, четверок – в C24:C25, троек и двоек – в D24:D25 и E24:E25.

	A	B	C	D	E	F	G
19					Информатика	К-во 5	=БСЧЁТ(A1:K16;7;B24:B25)
20						К-во 4	=БСЧЁТ(A1:K16;7;C24:C25)
21						К-во 3	=БСЧЁТ(A1:K16;7;D24:D25)
22						К-во 2	=БСЧЁТ(A1:K16;"Информ.";E24:E25)
23
24		Информ.	Информ.	Информ.	Информ.
25		=5	=4	=3	=2
26

С помощью БДФункций ДМАКС и ДМИН можно найти максимальный и минимальный элемент столбца для записей, удовлетворяющих критерию. Функции БДСУММ и ДСРЗНАЧ позволяют найти сумму и среднее значения элементов указанного поля, соответствующих записям, которые удовлетворяют критерию.

1.4. Обмен данными.

Каждый раз, когда две программы Windows работают с общими данными, используется Буфер обмена. Любой выделенный объект, например блок текста в Word-документе или интервал в электронной таблице либо другой фрагмент данных этих программ, может быть вырезан или скопирован щелчком мыши на кнопке Удалить в буфер или Копировать в буфер на панели инструментов. После этого Windows хранит эту информацию в памяти компьютера, пока пользователь не укажет место вставки и не вставит копию в активный документ. При этом неважно, принадлежат ли источник копирования и целевой документ к одному типу или это различные документы, например, таблица Excel и текст Word,- процедура выглядит совершенно одинаково. Следует помнить, что в любой момент времени Буфер обмена способен хранить только один объект.

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

Порядок действий:

Ø В электронной таблице выделить интервал, который необходимо скопировать.

Ø Щелкнуть правой кнопкой мыши на выделенной области и в появившемся контекстном меню выбрать Копировать (или щелкнуть на панели инструментов кнопку копирования).

Ø Переключиться в документ, в который следует скопировать данные выделенной области (это может быть новая БД или документ Word).

Ø Выбрать Правка – Специальная вставка, в результате чего появится диалоговое окно «Специальная вставка».

Ø Выбрать опцию Связать, щелкнув мышью на нужном положении переключателя.

Ø Убедившись, что в поле Как выделен подсветкой пункт Лист Microsoft Excel Объект, щелкнуть на кнопке OK.

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

Для примера создадим БД, содержащую поля «№», «Фамилия», «Имя», «Отчество», «Группа» и «Стипендия», такие же, как в нашей БД. Дополним новую БД полями «Дата рождения» и «Возраст», задав соответствующую формулу для вычисления возраста: (СЕГОДНЯ() – «Дата рождения»)/365 (лет).

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

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

Теперь изменим в исходной БД оценку студента Горца по информатике на «5», что повысит его средний балл и обеспечит стипендией в размере 12гр., а также скорректируем имя и отчество студента Прокопенко: как видим, данные изменились автоматически как в новой БД, так и в данном Word-документе.

Литература:

1. Методические указания к проведению вычислительной практики №1

2. Конспект лекций по курсу «Информатика и компьютерная техника».

3. Руденко В.Д., Макарчук О.М., Патланжоглу М.О. Практичний курс інформатики. /За ред. Мадзігона В.М.-К.: Фенікс, 1997.-307 с.

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

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

Структура базы данных

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

№ поля	Имя поля	Тип поля	Тип данных	Длина поля
1	ФИО	Символьный	Исходные	20 символов
2	Бригада	Символьный	Исходные	12 символов
3	Специальность	Символьный	Исходные	15 символов
4	Оклад	Числовой	Исходные	4 символа
5	Премия	Числовой	Вычисляемые	4 символа
6	Начислено	Числовой	Вычисляемые	4 символа
7	Налог	Числовой	Вычисляемые	8 символов
8	К выплате	Числовой	Вычисляемые	8 символов

Рис.2.1 Структура проектируемой базы данных.

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

Ведение базы данных.

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

Редактирование полей.

Добавим к существующей базе поле, которое отражает порядковый номер записей в базе. Для этого установим курсор в ячейку первого столбца и активизируем пункт меню Вставка Столбцы. В появившемся пустом столбце запишем название поля и заполним его. Результат поместим в новый файл. Удаление полей осуществим с помощью команд меню Правка Удалить Удалить столбец. При этом курсор необходимо установить в поле , которое собираемся удалить. На Рис. 2.6 показан вид базы данных с добавленным полем №, а на Рис.2.7 показана база данных, из которой удалили поля № и «Бригада».

Редактирование записей

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

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

2.4.Начальная обработка данных.

2. 4.1. Добавление суммы по столбцам.

Добавим в числовых полях суммирование по столбцам.

Формулы для вычисления сумм в ячейках F27, G27,H27,I27,J27 будут выглядеть соответствующим образом.

F27=СУММ(F7:F26); G27=СУММ(G7:G26); H27=СУММ(H7:H26);

I27=СУММ(I7:I26) J27=СУММ(J7:J26)

2.4.2. Добавление суммирования по критерию.

Используем функцию СУММЕСЛИ для добавления суммы по заданному условию. В соответствии со справкой Мастера формул функция может быть представлена в общем виде как СУММЕСЛИ(диапазон; условие; диапазон_суммирования) В нашем случае просуммируем премиальный фонд ветеранам труда (стаж должен превышать 10 лет). Расчетная формула примет вид:

G27=СУММЕСЛИ(E7:E26;>10;G7:G26)

Результат использования формулы приведен на Рис. 2.10.

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

Простая сортировка по полю.

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

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

Рис.2.11. Пиктограммы сортировки.

При этом курсор должен быть установлен в поле, которое будем сортировать. Для сортировки по возрастанию по полю «Стаж» установим курсор на ячейку в этом поле и выберем направление сортировки «по возрастанию». Результат сортировки представим на Рис.2.12.

Рис.2.12. Сортировка по полю «Стаж» по возрастанию.

Расширенный фильтр.

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

СОДЕРЖАНИЕ

ВВЕДЕНИЕ

1. БАЗЫ ДАННЫХ В EXCEL.

1.1. Как создать базу данных.

1.2. Как выполнить поиск, изменение и удаление записей.

1.3. Обработка данных в БД.

1.4. Обмен данными.

2.1. Проектирование базы данных.

2.1.1. Структура базы данных

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

2.2. Создание базы данных.

2.2.1 Создание заголовка таблицы и первой строки.

2.2. Заполнение таблицы с помощью Мастера форм.

2.3. Ведение базы данных

2.3.1. Редактирование полей.

2.2. Редактирование записей

2.4.Начальная обработка данных.

2. 4.1. Добавление суммы по столбцам

2.4.2. Добавление суммирования по критерию.

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

2.5.1. Простая сортировка по полю.

2.5.2 Сортировка по нескольким полям.

2.6. Формы представления информации, содержащейся в базе данных.

2.6.1. Добавление промежуточных итогов.

2.6.2. Работа со структурой

2.7. Анализ информации, содержащейся в базе данных.

2.7.1 Вычисление статистических характеристик

2.7.2. Работа с функциями из раздела Базы данных

2.8. Выборочное использование данных

2.8.1. Пользовательский автофильтр.

2.8.2 Расширенный фильтр.

2.9. Графическое представление данных.

ВВЕДЕНИЕ

(б) качество документации, которая поставляется с пакетом;

БАЗЫ ДАННЫХ В EXCEL.

Как создать базу данных.

База данных создается в обычной электронной таблице, но с выполнением таких правил:

ÿСтрока заголовков столбцов (верхняя строка списка ) должна быть заполнена именами полей.

ÿКаждая запись должна размещаться в отдельной строке.

ÿПервую запись необходимо разместить в строке, следующей непосредственно за строкой заголовков.

ÿСледует избегать пустых строк между записями.

Фамилия	Имя	Отчество	Высш. матем.	Информ.	История Укр.	Физика	Ср.балл	Стипендия
Абдельгадир	Мусса	Ибрагимович	4	5	3	3	3,75	0

Ср.балл	Стипендия
=СРЗНАЧ(E2:H2)	=ЕСЛИ(I2<4;0;ЕСЛИ(I2<4,5;12;15))

ÿНельзя нажимать клавишу Enter для перехода к следующему полю, так как при этом добавится неполная запись!

ÿЧтобы заменить ранее введенное значение, необходимо просто начать вводить нужное в этом поле.

ÿПри вводе даты необходимо придерживаться известного программе формата

Как выполнить поиск, изменение и удаление записей.

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

Форма данных служит не только для создания БД, но и для поиска нужной записи, для ее редактирования или для дальнейшего удаления ненужной записи.

Если в БД сравнительно немного записей, поиск нужной записи можно выполнить следующим образом:

Ø установить табличный курсор где-либо в БД;

Ø выполнить последовательность команд Данные – Форма ;

Ø нажимать кнопку Далее в появившейся форме или клавишу Enter, пока не появится нужная запись, или воспользоваться полосой прокрутки справа от списка имен полей, щелкая на кнопке со стрелкой вниз в нижней ее части ;

Ø для перемещения к предыдущей записи нажать <Shift + Enter> или щелкнуть на стрелке вверху полосы прокрутки;

Ø для перемещения к первой записи нажать <Ctrl + PgUp> или передвинуть бегунок прокрутки в самый верх полосы прокрутки;

Ø для перехода на последнюю запись нажать <Ctrl + PgDn> или передвинуть бегунок полосы прокрутки в самый низ;

Ø для перехода по БД на 10 записей вперед нажать <PgDn> , на 10 записей назад – <PgUp> .

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

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

Ø Щелкнуть на первой записи .

Ø Выбрать Данные – Форма.

Ø Щелкнуть на кнопке Критерии в появившейся форме, после чего очистятся все поля формы.

Ø Ввести требуемые критерии поиска в соответствующие поля формы.

Ø Нажать кнопку Далее или клавишу Enter , чтобы начать поиск.

Ø Excel отобразит форму данных с первой найденной записью, а чтобы просмотреть все следующие , следует нажимать кнопку Далее; при необходимости пройтись по найденным записям в обратном порядке следует щелкать по кнопке Назад.

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

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

Для редактирования значения поля в текущей записи необходимо перейти в него, нажимая клавиши <Tab> или <Shift + Tab> (или с помощью мыши) , и ввести новое значение. Для очистки поля целиком выделить его и нажать клавишу <Del>.

Для удаления записи из БД щелкнуть на кнопке Удалить в окне формы данных. При этом, однако, следует помнить, что невозможно восстановить удаленную таким образом запись с помощью команды Отменить. Поэтому Excel выдаст окно предупреждения с таким сообщением:« Запись, выведенная на экран, будет удалена ». Можно подтвердить свое решение об удалении записи, щелкнув на кнопке ОК , или отменить, щелкнув на кнопке Отмена .

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

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

Обработка данных в БД.

Для сортировки элементов в БД необходимо выполнить такие действия:

Ø Щелкнуть на любой ячейке БД.

Ø Щелкнуть на ОК или нажать Enter .

№	Фамилия	Имя	Отчество	Высш. матем.	Информ.	История Укр.	Физика	Ср.балл	Стипендия
4	Грант	Анатолий	Семенович	4	5	5	5	4,75	15
2	Бирюкова	Галина	Олеговна	5	4	4	5	4,5	15
3	Вовченко	Александра	Александровна	3	4	5	4	4	12
1	Абдельгадир	Мусса	Ибрагимович	4	5	3	3	3,75	0

Для активизации нужного фильтра следует выполнить такие действия:

Ø Щелкнуть где-либо в БД.

Ø отметить клетки;

Ø выбрать команду Копировать из меню Правка (или нажать <Ctrl + C>);

Ø нажать Enter.

№	Фамилия	Имя	Отчество	Высш. матем.	Информ.	История Укр.	Физика	Ср.балл	Стипендия
2	Бирюкова	Галина	Олеговна	5	4	4	5	4,5	15
4	Грант	Анатолий	Семенович	4	5	5	5	4,75	15

№	Фамилия	Имя	Отчество	Высш матем.	Информ.	История Укр.	Физика	Ср.балл	Стипендия
2	Бирюкова	Галина	Олеговна	5	4	4	5	4,5	15
3	Вовченко	Александра	Александровна	3	4	5	4	4	12
4	Грант	Анатолий	Семенович	4	5	5	5	4,75	15
7	Иванов	Иван	Иванович	4	4	4	4	4	12
9	Клочко	Георгий	Константинович	5	4	3	4	4	12

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

Ø щелкнуть на кнопке раскрывающегося списка в названии поля ;

Ø выбрать опцию Условие ;

№	Фамилия	Имя	Отчество	Высш. матем.	Информ.	История Укр.	Физика	Ср.балл	Стипендия
1	Абдельгадир	Мусса	Ибрагимович	4	5	3	3	3,75	0
5	Антонова	Вера	Борисовна	3	4	4	3	3,5	0

Получим:

№	Фамилия	Имя	Отчество	Высш. матем	Информ.	История Укр.	Физика	Ср.балл	Стипендия
2	Бирюкова	Галина	Олеговна	5	4	4	5	4,5	15
3	Вовченко	Александра	Александровна	3	4	5	4	4	12
4	Грант	Анатолий	Семенович	4	5	5	5	4,75	15
7	Иванов	Иван	Иванович	4	4	4	4	4	12
9	Клочко	Георгий	Константинович	5	4	3	4	4	12

№	Фамилия	Имя	Отчество	Группа	Высш. матем.	Информ.	История Укр.	Физика	Ср.балл	Стипендия
1	Абдельгадир	Мусса	Ибрагимович	219	4	5	3	3	3,75	0
2	Антонова	Вера	Борисовна	219	3	4	4	3	3,5	0
3	Бирюкова	Галина	Олеговна	219	5	4	4	5	4,5	15
4	Борисова	Нина	Павловна	221	5	5	4	5	4,75	15
5	Вовченко	Александра	Александровна	221	3	4	5	4	4	12
6	Горец	Анатолий	Владимирович	221	3	3	5	4	3,75	0
7	Грант	Анатолий	Семенович	223	4	5	5	5	4,75	15
8	Дмитренко	Петр	Павлович	223	3	4	3	3	3,25	0
9	Дмитренко	Виталий	Игоревич	223	4	4	5	4	4,25	12
10	Замовский	Эдуард	Федорович	235	3	3	4	3	3,25	0
11	Иванов	Иван	Иванович	235	4	4	4	4	4	12
12	Клочко	Георгий	Константинович	235	5	4	3	4	4	12
13	Новиков	Олег	Валентинович	241	3	4	3	3	3,25	0
14	Прокопенко	Виталий	Викторович	241	4	3	3	4	3,5	0
15	Соловьев	Руслан	Анатольевич	241	3	4	5	4	4	12

Ø Выполнить команды Данные ––Сводная таблица .

§ указать название сводной таблицы в поле «Название таблицы»;

§ установить флажки «Общие итоги по столбцам» и «Общие итоги по строкам», если нужно ;

§ для создания дополнительной копии данных установить флажок «Сохранить данные с макетом таблицы»;

§ установить флажок «Автоматически форматировать таблицу» для использования средств автоформата Excel.

Ø Для вывода сводной таблицы на экран после этого щелкнуть на кнопке Готово.

Сумма по полю Стипендия
Группа	Фамилия	Всего
219		15
221	Абдельгадир	0
	Вовченко	12
	Грант	15
221 Всего		27
223	Горец	0
	Иванов	12
	Клочко	12
223 Всего		24
235		12
241		27
Общий итог		105


Группа	Данные	Всего
219	Среднее по полю Высш.матем.	4
	Среднее по полю Информ.	3,666666667
	Среднее по полю История Украины	3,333333333
	Среднее по полю Физика	4
221	Среднее по полю Высш.матем.	3,666666667
	Среднее по полю Информ.	4,666666667
	Среднее по полю История Украины	4,333333333
	Среднее по полю Физика	4
223	Среднее по полю Высш.матем.	4
	Среднее по полю Информ.	3,666666667
	Среднее по полю История Украины	4
	Среднее по полю Физика	4
235	Среднее по полю Высш.матем.	3,333333333
	Среднее по полю Информ.	3,666666667
	Среднее по полю История Украины	4,333333333
	Среднее по полю Физика	3,333333333
241	Среднее по полю Высш.матем.	3,666666667
	Среднее по полю Информ.	4,333333333
	Среднее по полю История Украины	4
	Среднее по полю Физика	4

Ø Убедиться, что параметр «Заменить текущие итоги» установлен и щелкнуть на кнопке ОК.

БДФункция(база_данных;поле;критерий)

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

База_данных – это интервал ячеек, формирующих БД.

	A	B	C	D	E	F	G
19					Информатика	К-во 5	=БСЧЁТ(A1:K16;7;B24:B25)
20						К-во 4	=БСЧЁТ(A1:K16;7;C24:C25)
21						К-во 3	=БСЧЁТ(A1:K16;7;D24:D25)
22						К-во 2	=БСЧЁТ(A1:K16;"Информ.";E24:E25)
23
24		Информ.	Информ.	Информ.	Информ.
25		=5	=4	=3	=2
26

1.4. Обмен данными.

Порядок действий:

Ø В электронной таблице выделить интервал, который необходимо скопировать.

Ø Выбрать опцию Связать, щелкнув мышью на нужном положении переключателя.

Ø Убедившись, что в поле Как выделен подсветкой пункт Лист Microsoft Excel Объект, щелкнуть на кнопке OK.

Литература:

1. Методические указания к проведению вычислительной практики №1

2. Конспект лекций по курсу «Информатика и компьютерная техника».

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

Структура базы данных

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

№ поля	Имя поля	Тип поля	Тип данных	Длина поля
1	ФИО	Символьный	Исходные	20 символов
2	Бригада	Символьный	Исходные	12 символов
3	Специальность	Символьный	Исходные	15 символов
4	Оклад	Числовой	Исходные	4 символа
5	Премия	Числовой	Вычисляемые	4 символа
6	Начислено	Числовой	Вычисляемые	4 символа
7	Налог	Числовой	Вычисляемые	8 символов
8	К выплате	Числовой	Вычисляемые	8 символов

Рис.2.1 Структура проектируемой базы данных.

Дата: 2019-05-28, просмотров: 149.

12 Следующая ⇒