/>СОДЕРЖАНИЕ
ВВЕДЕНИЕ
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 и MSExcel.В данной работе мы будем рассматривать работу с базами данных в MSExcel.
Есливозникает потребность в приобретении пакета ведения электронных таблиц, следуетубедиться в том, что они, кроме основных характеристик, которые будут детальнорассмотрены ниже, владеют следующими возможностями:
(а)средстваобучения, предлагаемые основным разработчиком или независимой консультационнойкомпанией (доступность и цена);
(б)качество документации, которая поставляется с пакетом;
(в)интеграция с другими функциями (например, передачи данных из электронных таблицв текстовый процессор или введение информации из базы данных).
Дляработы с электронными таблицами необходимо, как правило, не меньшее 512 Кбайтоперативной памяти. Ввод и вывод данных осуществляются через видеотерминал иклавиатуру с применением для получения твердых копий принтера. Сферыиспользования электронных таблиц ограничены только воображением пользователя, атакже количеством «строк и столбцов» таблицы.
1. БАЗЫ ДАННЫХ В EXCEL.
База данных (date base) – это совокупность хранимых впамяти компьютера данных, которые отображают состояние некоторой предметнойобласти. Данные взаимосвязаны и специальным образом организованы.
При такоминформационном отображении предметных сред упор делается не на сами объекты и их свойства, а на отношения междуними, что соответствует так называемой реляционной точке зрения на базы данных.
Excel умеетскладывать, вычитать, умножать, делить и выполнять множество других операций. Excel дает возможность предварительнопроанализировать последствия принятия тех или иных решений при конкретных обстоятельствах.Excel позволяет автоматизироватьне только расчеты как таковые, но позволяет создавать и работать сразнообразными картотеками, системами учета, базами данных и т.п.
Вопросы сбораданных, их хранения, учета и обработки можно решить, имея систему управлениясписками. Термин список используется в Excel для обозначения базы данных.
База данных – этоособый тип рабочей таблицы, в которой не столько вычисляются новые значения,сколько размещаются большие объемы информации в связанном виде.
Например, можносоздать базу данных с фамилиями, именами, адресами и номерами телефонов вашихзнакомых или список группы со всей информацией об итогах сессии и о размере соответствующейстипендии или ее отсутствии.
База данныхпредставляет собой последовательность записей, содержащуюоднозначно определенную по категориям и последовательности информацию. Под каждуюкатегорию данных в записи отводится отдельное поле, которомуприсваивается имя и отводится столбец.
/>1.1. Как создать базу данных.
Создание базыданных обеспечивает интеграцию данных и возможность централизованногоуправления данными, снабжая информацией определенных пользователей.
Создание базыданных (БД) начинается с проектирования БД, т.е. с определения ее структуры:количества полей, их имен, типа каждого поля (символьный, числовой, дата…),длины каждого поля ( максимального количества символов ), типа данных(исходные, т.е. неизменяемые, или вычисляемые). Возможность использовать вычисляемыеполя — основная особенность баз данных в Excel.
База данных создаетсяв обычной электронной таблице, но с выполнением таких правил:
ÿСтрока заголовков столбцов (верхняя строка списка ) должнабыть заполнена именами полей.
ÿКаждая запись должна размещаться в отдельной строке.
ÿПервую запись необходимо разместить в строке, следующейнепосредственно за строкой заголовков.
ÿСледует избегать пустых строк между записями.
Создание иподдержка базы данных в Excel упрощается, если вместо привычного для работы в Excel перехода от ячейки к ячейке привводе данных использовать встроенную форму (бланк ) данных.Чтобы создать форму данных для новой базы, следует ввести сначала строку (илистолбец ) с именами полей и поместить в следующей строке запись ¾ образец заполнения базы.Фамилия Имя Отчество
Высш.
матем. Информ. История Укр. Физика Ср.балл Стипендия Абдельгадир Мусса Ибрагимович 4 5 3 3 3,75
При этомнеобходимо отформатировать каждый элемент поля, помня, что таким же образомбудут отформатированы все последующие элементы в этом поле. В вычисляемые полявводятся расчетные формулы. Так в приведенном примере в столбцы «Ср.балл» и«Стипендия» введены формулы:Ср.балл Стипендия =СРЗНАЧ(E2:H2) =ЕСЛИ(I2Примечание. В ячейки E2:H2 таблицы вводятся оценки, а в ячейкуI2 – средний балл. Стипендия вычисляетсяпо формуле: если Ср.балл меньше 4, стипендия не начисляется (=0); если Ср.баллбольше 4, но меньше 4.5, начисляется стипендия 12 гр., а если больше 4.5, то –15гр.
Затем необходимоотметить обе строки ( с именами полей и формулами ) и выбрать команду Формаиз меню Данные. После этого Excel анализирует введенную в первые две строки информацию и создает форму сданными, в которой сверху вниз перечислены все имена полей в левой части иэлементы первой записи в текстовых окнах рядом с ними. Текстовые окна увычисляемых полей отсутствуют.
Чтобы ввестиновую запись, нужно щелкнуть на кнопке Добавить и ввести данные в пустыеполя. Для перехода к очередному полю следует нажимать клавишу Tab>.При заполнении БД необходимособлюдать следующие правила:
ÿНельзя нажимать клавишу Enter для перехода к следующему полю, таккак при этом добавится неполная запись!
ÿЧтобы отредактировать значение в пройденном поле, следуетнажимать клавиши Shift+Tab> для возврата в нужное поле.
ÿЧтобы заменить ранее введенное значение, необходимо простоначать вводить нужное в этом поле.
ÿЧтобы отредактировать только некоторые символы значения поля,следует нажимать клавишу или щелкнуть I-образным указателем мыши на томместе, куда необходимо поместить курсор.
ÿПри вводе даты необходимо придерживаться известного программеформата
ÿПри вводе чисел с лидирующими нулями (кроме почтовыхиндексов), вводится апостроф перед первым нулем, что воспринимается Excel как текстовая метка (при этомапостроф в БД не вводится).
ÿПосле заполнения всей записи необходимо нажать клавишу Enter или щелкнуть на кнопке Добавить,в результате чего Excel вставит новую записьпоследней в БД и выведет на дисплей пустую форму для ввода следующей записи.
ÿПосле завершения ввода записей следует нажать клавишу Esc> или щелкнуть на кнопке Закрыть, чтобы закрыть форму данных.
ÿСохранить рабочую таблицу командой Сохранить из меню Файлили щелкнуть на инструменте Сохранить на стандартной панелиинструментов./>1.2.Как выполнить поиск, изменение и удаление записей.
Доступ к даннымобеспечивает система управления данными. В ее функции входят поиск,исправление, добавление и удаление данных. Причем операция поиска являетсяглавной.
Форма данныхслужит не только для создания БД, но и для поиска нужной записи, для еередактирования или для дальнейшего удаления ненужной записи.
Если в БДсравнительно немного записей, поиск нужной записи можно выполнитьследующим образом:
Ø установить табличный курсоргде-либо в БД;
Ø выполнить последовательностькоманд Данные – Форма ;
Ø нажимать кнопку Далеев появившейся форме или клавишу Enter, пока не появится нужная запись, или воспользоваться полосой прокруткисправа от списка имен полей, щелкая на кнопке со стрелкой вниз в нижней еечасти ;
Ø для перемещения к предыдущейзаписи нажать Shift+Enter> или щелкнуть на стрелке вверху полосы прокрутки;
Ø для перемещения к первойзаписи нажать Ctrl+PgUp> или передвинуть бегунок прокрутки в самый верх полосыпрокрутки;
Ø для перехода на последнююзапись нажать Ctrl+PgDn> или передвинуть бегунок полосы прокрутки в самый низ;
Ø для перехода по БД на 10 записейвперед нажать PgDn>, на 10 записей назад – PgUp> .
Для поиска нужнойзаписи в большой БД следует использовать форму данных и специальные критериипоиска. Excel,используя заданные критерии для нахождения требуемых записей в списке, найдет иотберет только те записи, для которых выполнены условия, заданные критериями.
Для задания критериев и выполненияпоиска удовлетворяющих им записей небходимо выполнить такие действия:
Ø Щелкнуть на первой записи .
Ø Выбрать Данные – Форма.
Ø Щелкнуть на кнопке Критериив появившейся форме, после чего очистятся все поля формы.
Ø Ввести требуемые критериипоиска в соответствующие поля формы.
Ø Нажать кнопку Далее иликлавишу Enter, чтобы начать поиск.
Ø Excel отобразит форму данных с первойнайденной записью, а чтобы просмотреть все следующие, следует нажимать кнопку Далее;при необходимости пройтись по найденным записям в обратном порядке следуетщелкать по кнопке Назад.
Чтобы изменитькритерий поиска, если необходимо, нужно сначала очистить форму данных, щелкнув снована кнопке Критерии. Затем – выбрать нужные текстовые окна и очиститьстарый критерий перед вводом нового или просто заменить критерий при условиииспользования тех же полей.
Чтобы вернуться ктекущей записи, проигнорировав результаты поиска по критерию, следует щелкнутьна кнопке Правка, которая появляется на месте кнопки Критериипосле перехода в режим создания критерия.
Для редактированиязначения поля в текущей записи необходимо перейти в него, нажимая клавишиTab> или Shift+Tab> (или с помощью мыши), иввести новое значение. Для очистки поля целиком выделить его и нажать клавишу Del>.
Для удалениязаписи из БД щелкнуть на кнопке Удалить в окне формы данных. Приэтом, однако, следует помнить, что невозможно восстановить удаленную такимобразом запись с помощью команды Отменить. Поэтому Excel выдаст окно предупреждения с такимсообщением:« Запись, выведенная на экран, будет удалена ». Можноподтвердить свое решение об удалении записи, щелкнув на кнопке ОК, илиотменить, щелкнув на кнопке Отмена .
Редактированиеполей осуществляется обычными средствами Excel, предназначенными для работы состолбцами электронной таблицы с последующей корректировкой формы данных и всехзаписей.
Для добавленияновых записей в уже сформированную БД при наличии строк с итоговымирезультатами ( например, среднее значение по полю или сумма элементов столбца )нужно предварительно вставить обычными средствами Excel ( с помощью меню Вставка )необходимое количество новых строк. В противном случае Excel сообщит, что «расширение БДневозможно». После дополнения БД необходимо в обязательном порядкеоткорректировать формулы в итоговых строках, распространив их на вновьвведенные данные, так как иначе эти данные не будут учтены и результаты останутсяпрежними, т.е. неправильными. />1.3.Обработка данных в БД.
Любая информация должнабыть упорядочена. Хорошая БД – в том числе. Каждая БД имеет некоторыйпредпочтительный порядок поддержания и просмотра записей. Записи можнорасположить, например, в алфавитном порядке фамилий или названий фирм. Дляопределения рейтинга студентов список группы удобно расположить по убываниюсреднего балла. Однако при добавлении новых записей 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
В приведенном примере записиупорядочены по убыванию значений в поле Ср.балл. Прежний порядок записей в БДможно проследить по первому полю «№».
Для сортировкизаписей в БД по одному полю проще пользоваться инструментом Сортировать повозрастанию или Сортировать по убыванию на панели инструментов. Для этого нужноустановить курсор в клетке с именем поля и щелкнуть на соответствующеминструменте. При этом необходимо помнить, что сортируется вся БД и по всемэлементам выбранного поля, в том числе и по итоговым строкам.
Форма данныхявляется удобным средством поиска записей в БД, пока критерии поиска просты.Однако гораздо более удобным инструментом этого назначения являются фильтры. Фильтр обрабатывает весь список в электронной таблице и отображает наэкране только те данные, которые требуется. Преимущество этого подхода заключаетсяв том, что с такой выборкой можно работать автономно как с новой таблицей, незагружая экран избыточной информацией.
Для активизации нужного фильтраследует выполнить такие действия:
Ø Щелкнуть где-либо в БД.
Ø Выбрать Данные – Фильтр –Автофильтр, в результате чего 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 5 Антонова Вера Борисовна 3 4 4 3 3,5
Для отбора только студентов, имеющихсредний балл в пределах от 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 2 Антонова Вера Борисовна 219 3 4 4 3 3,5 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 7 Грант Анатолий Семенович 223 4 5 5 5 4,75 15 8 Дмитренко Петр Павлович 223 3 4 3 3 3,25 9 Дмитренко Виталий Игоревич 223 4 4 5 4 4,25 12 10 Замовский Эдуард Федорович 235 3 3 4 3 3,25 11 Иванов Иван Иванович 235 4 4 4 4 4 12 12 Клочко Георгий Константинович 235 5 4 3 4 4 12 13 Новиков Олег Валентинович 241 3 4 3 3 3,25 14 Прокопенко Виталий Викторович 241 4 3 3 4 3,5 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 Абдельгадир Вовченко 12 Грант 15 221 Всего 27 223 Горец Иванов 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).
Ø Выбрать Правка – Специальнаявставка, в результате чего появится диалоговое окно «Специальная вставка».
Ø Выбрать опцию Связать,щелкнув мышью на нужном положении переключателя.
Ø Убедившись, что в поле Каквыделен подсветкой пункт Лист MicrosoftExcelОбъект, щелкнуть на кнопке OK.
В результате наэкране появится копия интервала из электронной таблицы. Над этим материаломнельзя выполнять операции Word, хотя он будет похож на Word-таблицу после вставки в Word-документ.
Для примера создадим БД, содержащуюполя «№», «Фамилия», «Имя», «Отчество», «Группа» и «Стипендия», такие же, как внашей БД. Дополним новую БД полями «Дата рождения» и «Возраст», задавсоответствующую формулу для вычисления возраста: (СЕГОДНЯ()– «Дата рождения»)/365 (лет).
Возраст, таким образом, будетменяться, с каждым днем увеличиваясь на 1/365, так как выражение в скобкеопределяет разность между днем сегодняшним и датой рождения в днях. Можноввести в БД еще одно поле, вычисляющее возраст в днях.
Эти все операции выполняются поправилам действий над датами в Excel.Свяжем новую БД с исходной, чтобы можно было учитывать любые изменения,возникающие в исходной БД в общих полях, без дополнительной корректировки новойБД.
Теперь изменим в исходной БД оценкустудента Горца по информатике на «5», что повысит его средний балл и обеспечитстипендией в размере 12гр., а также скорректируем имя и отчество студента Прокопенко:как видим, данные изменились автоматически как в новой БД, так и в данном Word-документе.
Литература:
1. Методическиеуказания к проведению вычислительной практики №1
2. Конспект лекцийпо курсу «Информатика и компьютерная техника».
3. Руденко В.Д.,Макарчук О.М., Патланжоглу М.О. Практичний курс інформатики. /За ред. МадзігонаВ.М.-К.: Фенікс, 1997.-307 с./>/>/>2.1.Проектирование базы данных.
На этапе проектирования базы данныхзадаем структуру базы, определяем количество, наименование и типы полей базы,определяем для вычисляемых данных формулы, по которым они вычисляются./>/> 2.1.1. Структура базы данных
В соответствии с заданием база данныхдолжна содержать следующие поля:
№ поля
Имя поля
Тип поля
Тип данных
Длина поля 1 ФИО Символьный Исходные 20 символов 2 Бригада Символьный Исходные 12 символов 3 Специальность Символьный Исходные 15 символов 4 Оклад Числовой Исходные 4 символа 5 Премия Числовой Вычисляемые 4 символа 6 Начислено Числовой Вычисляемые 4 символа 7 Налог Числовой Вычисляемые 8 символов 8 К выплате />/>/>/>/>/>/>Числовой />/>/>/>/>/>/>Вычисляемые 8 символов
Рис.2.1 Структурапроектируемой базы данных./>/>/> 2.1.2. Определение формул длявычисляемой части базы данных.В создаваемой базе нескольковычисляемых полей Определим зависимости, по которым вычисляются значения в этихполях. Введем условные обозначения, которые будем использовать при составленииформул:
Премия – П;
Оклад – О;
Стаж- С;
Начисленнаясумма — НС;
Подоходный налог- ПН;
1.Премия.
В соответствии с условиями премияначисляется сотрудникам, проработавшим определенное время на фирме. Длясотрудников со стажем от 2-х до 5-ти лет премия составит 15% оклада, со стажем более5 лет 25% оклада.
/>
При использовании Мастера функции логическоевыражение для вычисления премии приобретет вид:
П= Если (С 5;0.25*O;0.15*O))
2.Начисленнаясумма. Значение начисленной суммы определяется как результат сложения значенияоклада и премии.
НС=П+О.
3.Подоходный налог.
Подоходный налог определяется взависимости от величины начисленной суммы: не облагаются налогом суммы до 70грв. включительно; при начисленной сумме более 250 грв. подоходный налогсоставляет 20% от суммы; в остальных случаях подоходный налог равен 10% отсуммы.
ПН=/>/> />
Прииспользовании Мастера функций логическое выражение примет вид:
ПН=Если(НС250;0.2*НС;0,1*НС))
4. Квыплате.Значение определяется как разность Начисленной суммы и Подоходного налога.
К_вып=НС-ПН
В результатепроектирования базы данных получен эскиз таблицы следующего вида. A B C D E F G H I 1 ФИО Бригада Специальность Оклад Стаж Премия Начисленная сумма Подоходный налог
К
выплате 2
Рис. 2.2.Эскиз таблицы для заполнения базы данных.
В таблице исходныеданные отмечены синим цветом, а вычисляемые значения – красным. />/>/>2.2.Создание базы данных./>/> 2.2.1 Создание заголовка таблицы ипервой строки.
Для создания таблицыраскрываем приложение Excel Microsoft Office. После ввода названиятаблицы заполняем заголовки столбцов и форматируем их. Для этого выбираемкоманды меню Формат Столбец Ширина и устанавливаем необходимые значения( в соответствии с количеством символов в каждом поле, оговоренном в структуребазы данных). После ввода заголовков столбцов выбираем соответствующий видформатирования. Для этого используем пункты меню Формат Ячейки иактивизируем соответствующие вкладки: Число, Выравнивание, Шрифт. В числовыхполях задаем 2 знака после точки, выравнивание в тестовых полях устанавливаемпо правому краю ячейки, выравнивание числовых полей выполняем по центру.
После форматирования заголовковстролбцов приступаем к заполнению 1-й строки. Вводим исходные данные и ввычисляемых полях записываем расчетные формулы.
/>/>/>2.2.Заполнение таблицы с помощью Мастера форм.
Дальнейшеезаполнение данных выполняем с помощью пунктов меню Данные Форма (вэтот момент курсор должен быть установлен на ячейке в области создаваемой таблицы). При этом открывается окно диалогаМастера форм с заполненной первой записью исходной базы данных.
Щелкая на клавише«Добавить» в окне диалога и последовательно заполняя пустые поля исходнымиданными, создаем исходную базу данных. />/> 2.3.Ведение базы данных.
Ведение базыданных заключается в корректировке существующих данных, добавлении новых,удалении полей, содержащих ненужную или ошибочную информацию. В соответствии с заданием выполним операции по редактированию, добавлению,удалению информации. При работе в среде электронных таблиц для этого могут бытьиспользованы различные средства./>/>/> 2.3.1. Редактирование полей.
Добавим к существующейбазе поле, которое отражает порядковый номер записей в базе. Для этогоустановим курсор в ячейку первого столбца и активизируем пункт меню ВставкаСтолбцы. В появившемся пустом столбце запишем название поля и заполним его.Результат поместим в новый файл. Удаление полей осуществим с помощью командменю Правка Удалить Удалить столбец. При этом курсор необходимоустановить в поле, которое собираемся удалить. На Рис. 2.6 показан вид базыданных с добавленным полем №, а на Рис.2.7 показана база данных, из которойудалили поля № и «Бригада»./>/> 2.2. Редактирование записей
Для удаления записей из базыданных необходимо эти записи выделить и активизировать команды меню ПравкаУдалить Строку. В результате выполнения этих действий строка, вкоторой был установлен курсор будет удалена. Для добавления строк в базу данныхнеобходимо выполнить следующие действия: активизировать команды меню ВставкаСтроки. В результате в базу данныхбудет добавлена пустая строка над строкой, в которой находился курсор. Далеезаносим нужные сведения в добавленную строку.
в ячейкесохранится откорректированная информация. Добавим к исходной базе строку,содержащую информацию о рабочем Васечкине и исправим фамилию Иванов наИванченко в 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.5. Сортировка базы данных./>/>/> 2.5.1. Простая сортировка по полю.
Для обычной сортировки базы данных поодному полю ( по возрастанию или по убыванию) необходимо воспользоваться пунктомменю Данные Сортировка или пиктограммами на графическом меню.
Сортировка по возрастанию Сортировка по убыванию
Рис.2.11. Пиктограммысортировки.
При этом курсор долженбыть установлен в поле, которое будем сортировать. Для сортировки повозрастанию по полю «Стаж» установим курсор на ячейку в этом поле и выберемнаправление сортировки «по возрастанию». Результат сортировки представим наРис.2.12.
Рис.2.12.Сортировка по полю «Стаж» по возрастанию./>/>/> 2.5.2 Сортировка по нескольким полям.
Для проведенияболее сложной сортировки (по нескольким полям) откроем окно диалога «Сортировкадиапазона» (см. Рис.2.13). Для этого выполним команды меню Данные Сортировка.Чтобы сортировка выполнялась по двум или трем полямв окне диалогадля каждого диапазона задаем направление сортировки. Наименование диапазонов выбираемв окне диалога, раскрывая список наименований (щелкаем последовательно по областямокна диалога «Сортировать по», «Затем по», «В последнюю очередь по»), и указываемнаправление сортировки по каждому полю (убывание/возрастание). В нашем примеревыполним сортировку по трем критериям: по полю «Стаж», затем по полю«Специальность» и в последнюю очередь по полю «Оклад».Для всех трех критериевзадаем направление сортировки «По возрастанию». Результат выполнения сортировкипо нескольким критериям поместим на Рис.2.14. />/>/>2.6. Формыпредставления информации, содержащейся в базе данных./>/>/> 2.6.1. Добавление промежуточныхитогов.
Добавление промежуточных иокончательных итогов выполняется после сортировки исходной базы по выбранномуполю. Выполним сортировку исходной базы по полю «Бригада» и добавимпромежуточные и общий итоги. Для добавления итогов откроем окно диалога«Промежуточные итоги». Для этого выполняем команды меню Данные Итоги. Вокне диалога зададим поле, в котором будет отслеживаться изменение значений(например поле «Бригада»). Затем укажем в строке «Операция» тот вид операции,который нужен для выполнения задания(Сумма, Среднее и т.д.). В списке окнадиалога «Добавить итоги по» укажем, каких еще полях базы данных необходимовыполнить аналогичные действия. Окно диалога с установленными параметрами показанона Рис.2.15.
Результат выполнения операции подобавлению итогов отобразим на Рис.2.16. Аналогичные действия выполним длядобавления итогов по полю «Специальность». Отсортируем исходную базу по полю«Специальность» по возрастанию и при каждом изменении в этом поле добавимпромежуточные итоги. Определим суммарные значения начисленной суммы иподоходного налога по специальностям. Для этого в окне диалога «Промежуточныеитоги» выберем поле, изменения в котором приведут к добавлению итогов втаблицу. Это поле «Специальность», и установим операцию для выполнения(«Сумма»). Установим в окне диалога, для каких полей будет выполняться этаоперация (Начислено, По/нал). Результаты отобразим на Рис.2.17./>/>2.7.Анализ информации, содержащейся в базе данных./>/>2.7.1 Вычисление статистическиххарактеристик
Простейшаяматематическая обработка числовых полей выполняется с помощью Мастера функций(раздел статистических функций МАКС, СРЗНАЧ, МИН). Для более удобной работыпредставим результаты в числовом и формульном виде. Для представления в формульномвиде воспользуемся меню MS EXCEL Сервис Параметры Вид Параметры окна Формулы.Результаты выполнения этого пункта отчета представлены на />/>2.7.2. Работа с функциями из разделаБазы данных
В разделе Базы данных из Мастерафункций представлено более 10 специальных функций для работы с базами данных. Взадании выполнялось суммирование денежных выплат для рабочих специальности«Маляр» со стажесм более 10 лет. Для этого использовалась функцияБДСУММ(база_данных; поле; критерий). В качестве диапазона исследуемых данныхбыло выбрана исходная база. Суммирование производилось по полю «К выплате», илипо 10-му столбцу базы, при этом учитывались ограничения по специальности и постажу, которые записаны в ячейках B28: С29. Результат вырполнения этой операции показан на Рис.2.22./>2.8.Выборочное использование данных
Выборочное использованиеданных представляет систему отбора данных из исходной базы по какому-либопризнаку и дальнейшую обработку этих данных. Обычно применяют для этих целейпользовательский автофильтр и расширенный фильтр./> 2.8.1. Пользовательский автофильтр.
Подключение пользовательскогоавтофильтра происходит при активизации меню Данные-Фильтр-Пользовательскийавтофильтр. Затем устанавливается критерий для фильтрации данных(длявыбранного поля раскрываем список, который помещен в строке заголовков полейбазы данных. Если необходимо убрать пользовательский автофильтр активизируемкоманду Данные Фильтр Автофильтр.
Выберем из исходной базы записи,которые соответствуют следующему критерию отбора: Специальность «сварщик».Установим этот критерий в окне диалога на Рис. 2.23.
Рис. 2.23 Установка критерия фильтрации.
Далее установимфильтр для выбора информации из исходной базы по полю стаж. Необходимо отобратьрабочих со стажем от 5 до 10 лет Установим фильтр используя команды меню ДанныеФильтр Автофильтр и раскрываем список в поле, по которому предполагаетсявыполнять фильтрацию. Выберем из списка параметр «Условие». В открывшемся окнедиалога установим критерии для фильтра… Для поля стаж используем операторы«больше или равно» и меньше или равно» и задаем числовые значения соответственно5 и 10.
Рис. 2.26.Результат работы фильтра по полю стаж./> 8.2 Расширенный фильтр. />/>/>Возможности расширенного фильтра значительно больше, чем упользовательского. Подключаем его при помощи команды меню Данные Фильтр Раширенный С помощьюрасширенного фильтра выберемиз исходной базы записи, у которых в поле «специальность» будут значения«сварщик» или «маляр» и стаж этих рабочих должен превышать 10 лет./>/>/>/>Для создания круговой диаграммы выберемтаблицу данных, для которых эта диаграмма будет строиться. Ввиду того, чтоисходная база содержит 20 строк, построим диаграмму для предварительноотфильтрованной базе по полю № бригады (Бригада №1). Укажем диапазоны данных,которые будем использовать. Для этого выделим столбцы ФИО и К выплате. Выделяемпри нажатой клавише CTRL.Далее с помощью мастера диаграмм устанавливаем вид выводимой информации на поледиаграммы (легенда, заголовок, доли значений и т.д.) и место расположениядиаграммы ( на отдельном листе или рядом с таблицей.
Круговаядиаграмма распределения выплат по бригаде №1.
Для построения следующей даграммы изперечня возможных видов в списке Мастера диаграмм выберем разновидность«График». Для всех сотрудников фирмы построим график распределения денежныхвыплат.