Контрольная работа: Excel 97 в качестве базы данных

СОДЕРЖАНИЕ

Введение

Microsoft Excel – самаяраспространеннаяв мире программаэлектронныхтаблиц. ПрограммаExcel 97 расширенамногими новымиполезнымивозможностямии полностьюинтегрированас другими программамиMicrosoft Office 97.

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

ПрограммуExcel 97 можно использоватьдля храненияданных. Этапрограммаобладает многимивозможностями, присущимисовременнымпрограммамдля работы сбазами данных.Так, например,Excel 97 имеет средствадля обработкиданных и осуществленияих фильтрации.Однако по своимвозможностямExcel 97 уступаетспециализированнымпрограммам, предназначеннымдля работы сбазами, такимкак Microsoft Access, Oracle, SQL. Вчастности,Excel 97 не можетработать сочень большимибазами данных.Это связанос тем, что программаExcel 97 загружаетв память всюбазу данных, независимоот ее объема.Специализированныеже программызагружаюттолько те данные, которые необходимы.

Созданиеи ФильтрацияСписков

Р/>
исунок1. Обычный списокв Excel

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

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

При осуществлениифильтрациисписка согласноустановленномукритерию илиправилам частьего данныхоказываются“спрятанными”(на экране ихне видно). Предположим, что у нас имеетсясписок всехклиентов некойфирмы из Россиии стран СНГ. Мыможем отфильтроватьнаш список так, что в нем останутсяклиенты толькоиз Украины. Илиже этот списокможно отфильтроватьтак, что в немостанутсятолько те клиенты, заказы которыхпревышаютнекоторуюзаранее установленнуюсумму. Для каждогоконкретногосписка имеетсямного возможныхпутей его фильтрации.

Фильтрацияданных в спискахExcel осуществляетсяс помощью двухинструментов:Автофильтри Расширенныйфильтр.

ИспользованиеинструментаАвтофильтр.

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

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

Все записи

Первые десять записей

Фильтрация данных согласно определенному условию.

Фильтрацияпо определенномузначению.

Параметрыфильтрацииопределяютсятипом данных, с которым ведетсяработа. Если выбрать изменю Автофильтранекотороезначение, тов списке останутсятолько те записи, в ячейках которыхзаписано именноэто значение.Рассмотримконкретныйпример. Еслищелкнуть настрелке АвтофильтраF3, содержащейимя поля Среднийпоказатель, и из появившегосяменю выбрать71%, то в представленномсписке останутсявидны толькостуденты Лепини Якушев, посколькутолько у нихсредние показателиравны 71%.

Если выбратьимя одного изстудентов изсписка Автофильтра(Рисунок 2), тов списке останетсятолько записьс результатамиэтого студента, а остальныезаписи окажутсяскрытыми.

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

Использованиефильтра Первые10

Е/>
слипосле включенияинструментаАвтофильтр, выбрать командуПервые 10, топоявится диалоговоеокно Наложениеусловия посписку. В первомполе слеваможно определитьколичествовыводимыхзаписей.

Рисунок 2 Списоксо стрелкамии открытым менюинструментаАвтофильтра

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

В третьем поледиалоговогоокна Наложениеусловия посписку можноопределить, будет ли выведеноуказанноеколичествоэлементов илиже доля элементовсписка (процент).

При осуществленииоперации фильтрацииадреса ячеекне изменяются.Например, еслибудут отфильтрованыстроки 6 и 7, топосле строки5 будет виднастрока 8.

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

Выборочнаяфильтрация.

Можно такжеопределитьи свои собственныекритерии фильтрацииданных. Длятого чтобыосуществитьвыборочнуюфильтрацию, необходимосначала включитьинструментАвтофильтр, выбрав командыДанные |Фильтр |Автофильтр.Затем щелкнутьна стрелкеАвтофильтраи из появившегосяменю выбратькоманду Условие.Появится диалоговоеокно ПользовательскийАвтофильтр.В раскрывающихсясписках в левыхполях диалоговогоокна Пользовательскийавтофильтримеется рядпараметров:

Равно

Не равно

Больше

Больше или равно

Меньше

Меньше или равно

Начинается с

Не начинается с

Заканчивается на

Не заканчивается

Содержит

Не содержит

П/>
рименениедвух параметров, объединенныхфункцией И/ИЛИпозволяетсузить областьпоиска (Рисунок3).

Рисунок 3. Выборочнаяфильтрацияс использованиемдвух параметров.

Расширенныйфильтр представляетпрактическинеограниченныевозможностидля фильтрацииданных. Он позволяетвыполнятьфильтрациюданных помногочисленнымкритериям водном или несколькихполях.

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

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

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

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

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

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

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

Созданиесобственногосписка автозаполнения

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

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

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

Выделить весь список.

Выбрать из меню Сервис команду Параметры. В появившемся диалоговом окне Параметры перейти на вкладку Списки.

Щелкнуть на кнопке Импорт.

Щелкнуть на кнопке Ok

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

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

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

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

Выделить ячейки, подлежащие сортировке.

Выбрать из меню Данные команду Сортировка. В диалоговом окне Сортировка диапазона щелкнуть на кнопке Параметры. Появится диалоговое окно Параметры сортировки

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

Сортировка“слеванаправо”

Чащевсего производитсясортировкастрок (при этомизменяетсяпорядок следованиязаписей в столбцах).Однако сортироватьможно и данныев строке (приэтом будетпроизводитсяперестановкастолбцов).

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

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

Выбрать из меню Данные команду Сортировка. В диалоговом окне Сортировка диапазона щелкнуть на кнопке Параметры. Появится диалоговое окно Параметры сортировки.

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

Для закрытия диалогового окна Параметры сортировки щелкнуть на кнопке Ok

В диалоговом окне Сортировка диапазона в поле Сортировать по выбрать номер строки, подлежащей сортировке.

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

структурированиелиста.

Структурированиеиспользуетсядля выделенияважных данных.Так, например, при созданиидокументовв текстовомредакторе Wordвсе заголовкиоформляютсяспециальнымобразом длятого, чтобыоблегчитьвосприятиеинформации.Программа Excelможет выполнитьструктурированиелиста автоматическии “вручную”.

Автоматическоеструктурированиелиста

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

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

Выбрать команду Структура в меню Данные, а затем – Автоструктура.

Структурированиелиста вручную

Выделить строки или столбцы, содержащие детальные данные. Детальные строки или столбцы обычно подчинены строке или столбцу, содержащему соответствующие итоговые данные. Например, если строка 6 содержит суммы для строк с 3 по 5, то выделите строки 3 — 5.

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

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

Структураможет иметьдо 8 уровнейдетальныхданных, гдекаждый «внутренний»уровень предоставляетдетальныеданные дляпредыдущего«внешнего».

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

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

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

Рисунок 4. Частисводной таблицы

На рисунке 4представленпример своднойтаблицы:

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

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

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

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

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

Поле столбца— это поле исходногосписка илитаблицы, помещенноев область столбцов.В этом примере«Кварталы»является полемстолбца, включающимдва элементаполя «КВ2» и«КВ3». Внутренниеполя столбцовсодержат элементы, соответствующиеобласти данных; внешние полястолбцоврасполагаютсявыше внутренних(в примере показанотолько однополе столбца).

Областью данныхназываетсячасть своднойтаблицы, содержащаяитоговые данные.В ячейках областиданных отображаютсяитоги для элементовполей строкиили столбца.Значения вкаждой ячейкеобласти данныхсоответствуютисходным данным.В этом примерев ячейке C6 суммируютсявсе записиисходных данных, содержащиеодинаковоеназвание продукта, распространителяи определенныйквартал («Мясо»,«ТОО Мясторг»и «КВ2»).

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

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

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

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

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

Далее следовать инструкциям мастера сводных таблиц.

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

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

Получениевнешних данныхс помощью MicrosoftQuery

Запроспозволяетполучить данные, содержащиесяво внешней базеданных. Дляпримера предположим, что в базе данныхMicrosoft Access требуетсянайти данныепо продажепродукта врегионе.Чтобынайти ответна этот вопрос, нужно получитьсведения избазы данных, которая можетбыть организованав виде различныхтаблиц. Каждаятаблица содержитсвои данные.Например, однатаблица можетсодержатьсведения пообъему продажи категориипродукта. Другаятаблица можетсодержатьсведения орегионе. Microsoft Query позволяетполучить данныеизо всех этихтаблиц.

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

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

Созданиедиаграммы

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

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

Можно создатьлибо внедреннуюдиаграмму, либо листдиаграммы.

Внедреннаядиаграмма-объект, расположенныйна листе исохраняемыйвместе с листомпри сохранениикниги. Внедренныедиаграммы такжесвязаны с даннымии обновляютсяпри измененииисходных данных.

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

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

Выделить ячейки, содержащие данные, которые должны быть отражены на диаграмме. Если необходимо, чтобы в диаграмме были отражены и названия строк или столбцов, надо выделить также содержащие их ячейки.

Нажать кнопку Мастер диаграмм.

Далее следовать инструкциям мастера.

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

Н/>
арисунке 5 изображенадиаграмма, полученнаяна основе списка, представленногона Рисунке 1данного реферата.

Рисунок5. Диаграмма, построеннаяна основе списка, изображенногона Рисунке 1.

Заключение.

Исходя из всеговышесказанного, можно сделатьвывод, что программуMicrosoft Excel вполнеможно использоватьв качествепростой базыданных. Данныездесь организованыв виде строки столбцов, чтосоответствуетполям и записямв “настоящей”базе данных, есть средствафильтрации, сортировкии даже созданияи выполнениязапросов квнешним источникамданных. Крометого, применениеVisual Basic for Application вкачестве языкадля разработкимакросов иприложенийв Excel делаетприменениеэтой программыв качестве базыданных ещепривлекательней.Но, хотелосьбы еще разподчеркнуть, что для базданных большихразмеров, атакже такихбаз данных, которые предъявляютповышенныетребованияк защите ицелостностисвоих данныхExcel не применим.

Литература.

Льюис Паттерсон. Microsoft Excel 97. Освой самостоятельно за 24 часа: Пер. с англ. – М.: ЗАО “Издательство БИНОМ”,1998.

Электронные таблицы Excel 4.0: Практ. Пособ./Пер. с нем. – М.: ЭКОМ.,1994

Нелсон Стефен. Путеводитель по Microsoft Excel 97/ Пер. с нгл. –М.: Издательский отдел “Русская Редакция” ТОО “Channel Trading Ltd.”, 1994.