Разработка и создание базы данных в программе МSExcel

Случаи фильтрации: автофильтр и расширенный фильтр.

Фильтрация  с помощью автофильтра

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

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

1. Установить курсор внутри таблицы (т.е. щелкнуть левой кнопкой мыши по

любой ячейке внутри базы данных).

2. Ввести команду (в  строке меню) ДАННЫЕ, выбрать режим  ФИЛЬТР,

подрежим АВТОФИЛЬТР. В результате этих команд к каждой ячейке с

именем поля будет добавлена  серая кнопка со стрелочкой вниз

раскрывающегося списка, т.е. если щелкнуть мышкой по этой кнопке, то в

выбранном столбце появится ниспадающее меню.

3. Щелкнув по этой кнопке, раскрываем список столбца, по  которому будет 

производиться выборка.

 

 

 

 

 

 

4. Указываем требуемые  значения или выбираем строку  «условие», в этом 

случае появляется диалоговое окно «пользовательский автофильтор»

 

Условия для отбора записей  в определенном столбце могут  состоять из двух

самостоятельных частей, соединенных  логической связкой «и/или».

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

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

по кнопке автофильтра и выбрать в списке пункт «первые 10». В результате чего на экране появится диалоговое окно «наложение условия по списку», где задается нужное условие. Иногда надо скопировать строки, которые отвечают условиям отбора в другую часть рабочего листа или на другой лист. Для этого используют команды: КОПИРОВАТЬ, ВСТАВИТЬ меню ПРАВКА, предварительно выделив нужный диапазон ячеек. Для восстановления всех строк исходной таблицы щелкаем по кнопке автофильтра и выбираем пункт «все» - получаем исходную таблицу или используем команды: ДАННЫЕ — ФИЛЬТР — ОТОБРАЗИТЬ ВСЕ.

Для отмены режима фильтра  нужно установить курсор мыши внутри

таблицы и ввести команды: меню – Автофильтр V (убираем V).  
Например, необходимо выяснить, сколько ткани производства России было продано. Для этого удобно воспользоваться ресурсом автофильтра: Выделим любую ячейку списка. Меню ДАННЫЕ - ФИЛЬТР - АВТОФИЛЬТР. Список преобразует строку заголовка в выпадающие списки. Откроем выпадающий список в поле Тип товара и Производство. Список преобразуется и в нем будет представлена только информация, подходящей по нашим критериям. (лист «Автофильтр», Приложение 3).

Задание 4.

 

Допустим, теперь нужно найти  среднюю цену этих ткани (из Задания 3)  
В отсортированной базе данных выделяется диапазон, затем, щелкнув правой  
кнопкой мыши по строке состояния, вызываем контекстное меню и ставим  
вкладку в этом меню СРЕДНЕЕ. Полученные данные информируют, что средняя  
цена ткани производства России ____ pyб. (лист «Автофильтр», Приложение  
3).

Задание 5. «Расширенный фильтр».

 

 
Фильтрация  с помощью расширенного фильтра.

 

 
Выделим ячейки с именами  полей производство.Копируем их и  
вставим в ячейки D60 и С60. Зададим условие, что стройматериалы должны быть  
производства Финляндии и реализовываться в магазине ТКАНИ. В меню  
ДАННЫЕ выберем ФИЛЬТР — РАСШИРЕННЫЙ ФИЛЬТР. Задаем исходный  
диапазон и диапазон условий, т.е. выделяем ячейки с заданными условиями.  
Ставим переключатель Скопировать результат в другое место и Поместить  
результат в диапазон — щелкаем на ячейке А65. Нажимаем ОК. В этой ячейке появляется таблица с теми записями, которые соответствуют заданному диапазону условий (лист «Фильтр», Приложение 4).

Задание 6. «Добавление  итогов».

 

Иногда руководству организации  необходимо быстро посмотреть, сколько  
именно прибыли приносит тот или иной товар в общем ассортименте. Для этого и  
существует такой ресурс как ИТОГИ. Эта команда позволяет подсчитывать  
различные итоговые значения, как всей базы данных, так и ее отдельных  
элементов. Используя этот ресурс программы, подсчитаем сумму продаж ткани «Исходная база данных» на лист «Итоги». Отсортируем базу данных по  
Тканям. Затем в меню ДАННЫЕ выберем ИТОГИ; появится диалоговое окно  
ПРОМЕЖУТОЧНЫЕ ИТОГИ, в поле ПРИ КАЖДОМ ИЗМЕНЕНИИ В выберем  
Магазин, в поле операция выберем СУММА, и наконец в поле ДОБАВИТЬ  
ИТОГИ ПО поставим флажок напротив Сумма продаж. Убедимся, что в окне  
ПРОМЕЖУТОЧНЫЕ ИТОГИ включены или активированы флажки — заменить  
текущие итоги и итоги под данными. И нажмем кнопку ОК. Полученная база  
данных наглядно отображает информацию о суммах продаж по каждому магазину  
(лист «Итоги» и в Приложение 5).

 

Задание 7. «Сводная таблица».

 

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

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

Сводные таблицы создаются  с помощью МАСТЕРА сводных  таблиц и  
диаграмм.

 

Алгоритм создания сводной  таблицы.

1. Выделяем любую ячейку  из нужного диапазона, т.е. из  диапазона базы данных 

на листе «Сводная таблица». Выбираем в меню ДАННЫЕ — СВОДНЫЕ 

ТАБЛИЦЫ. Появляется «Мастер  сводных таблиц».

2. Выполняем первый шаг  Мастера, т.е. задаем тип источника  данных и тип 

создаваемого отчета. В  данном случае включаем переключатели. Имена 

переключателей: «в списке или  базе данных Microsoft Ехсеl> и «сводные

таблицы».

3. Нажимаем на кнопку  ДАЛЕЕ и переходим по второму  окну Мастера сводных таблиц, где указываем местоположение  исходных данных сводной таблицы.  В нашем случае: в поле, определяющем  местоположение данных должен  высвечиваться блок базы данных. Убедившись в том, что диапазон  верный— нажимаем на кнопку ДАЛЕЕ.

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

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

6. Чтобы реоргонизовать свободную таблицу надо просто перетащить одну или несколько кнопок полей в соответствующие области.

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

Сводная таблица — это  объект, т.е. при каждом изменении  параметра  
меняется и сама сводная таблица.

Результаты отражены на листе  «Сводная таблица» и в Приложении 6..

 

Задание 8. «Построение  диаграммы».

 

 

Для более наглядного представления  о деятельности фирмы можно  
представить сводную таблицу в виде диаграммы.

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

1. Установить соответсвующий переключатель в первом окне Мастера сводных таблиц или диаграмм.

2. Создать сводную диаграмму  на основе уже созданной сводной  таблицы. 

 

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

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

 

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

Чтобы обновить сводную таблицу, надо выделить в ней любую ячейку,  
выбрать в меню ДАННЫЕ — ОБНОВИТЬ ДАННЫЕ или нажать на  
соответствующую кнопку на панели сводной таблицы.

Например, на основе данных сводной таблицы построим диаграмму  
«Продажи» (лист «Диаграмма» и Приложение 7).

 

Имея файл в Excel, который надо отобразить в редакторе Word:  
1. Делаем прозрачными имена столбцов и строк. Обращаемся к меню Excel

ФАЙЛ — ПАРАМЕТРЫ СТРАНИЦЫ — диалоговое окно параметров

страницы — макет Листа  и включаем переключатель «заголовки строк и 

столбцов».

2. Создаем такой масштаб  изображения (60-70%), чтобы весь  результат 

уместился на листе.

3. Нажимаем на кнопку  Print Scrin.

4. Отправляем изображение  на панель задач (сворачиваем).

5. Открываем Word, нажимае на панели инструментов СТАНДАРТНАЯ –ВСТАВИТЬ. Появится изображение, растягиваем на всю страницу.

В Word изображение будет, как объект – оно будет живое.

Задание №9. «Элементы  управления».

 

Элементы управления (переключатели, счетчики, флажки и др.) - одно из  
самых удобных средств работы с информацией в Excel. Работая с элементами  
управления, используют принцип взаимодействия элементов управления и  
формул. Элемент управления привязывается к ячейке, а затем создается формула,  
использующая адрес связи для поиска информации или проведения вычислений.  
Для создания данных элементов используется панель инструментов - ФОРМЫ.

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

Скопируем на новый лист исходную базу данных и называем его  
«Элементы управления». На панели инструментов ФОРМЫ выберем ПОЛЕ СО  
СПИСКОМ и создадим его в нужном месте листа. Вызовем контекстное меню  
элемента управления и выберем команду ФОРМАТ ОБЪЕКТА, появиться  
диалоговое окно. В нём выбираем вкладку ЭЛЕМЕНТ УПРАВЛЕНИЯ и в поле  
ФОРМИРОВАТЬ СПИСОК ПО ДИАПАЗОНУ зададим диапазон данных,  
которые будут входить в раскрывающийся список. В поле СВЯЗЬ С ЯЧЕЙКОЙ  
введем ячейку А56, в ней будет отображаться номер выбранного значения из  
списка Для получения нужной информации в ячейки С59, D59, Е59, введем  
функцию ИНДЕКС. Например, в ячейке С59, куда мы занесем Класс товара,  
формула будет иметь вид: =ИНДЕКС(С6:С55;А56)

Аналогичные формулы введем в ячейки D59, Е59. Результаты  
использования элементов управления для анализа базы данных приведены на  
листе «Элементы управления» и в Приложении 8.

 

Заключение

 

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

Удобный ресурс Сортировка позволяет упорядочивать данные по Типу  
товара, а затем внутри - по Классу товара и Производство.

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

Фильтрация исходной базы данных в MS Excel позволяет создавать  
подмножество баз данных. Фильтрация позволяет сформировать информацию по различным критериям.

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

 

Список используемой литературы

 

1. Вострокнутов Е. MS Excel для Wiadows 95 одним взглядом. СПб. BNV,

1996.144с.

2. Нильсон К.- MS Excel 97. Справочник. СПб.: Питер, 1998. 316с.

3. Шаров Ю. Введение  в базы данных. М.: ABF, 1995. 384 с.

4. MS Excel 2000.

5. Симонович С.В., Евсеев  Г.А., Алексеев А.Г. Специальная  информатика. Уч.  
Пособие. — М. АСТ-Пресс, 1998.- 480с.