Проект база данных средствами MS Excel

СОДЕРЖАНИЕ

Введение ……………………………………………..………………… 3

1. Формирование базы данных ……………………………………. 4

2. Сортировка………………………………………………………...5

3. Фильтрация………………………………………………………..6

4. Элементы управления…………………………………………….8

5. Сводная таблица, сводная диаграмма..…………………………11

Заключение………………..…………………………………………….13

Список литературы………..……………………………………………14

Приложения ……………………………………………………………..15

Введение

База данных представляет собой упорядоченную информацию, объединённую в единое целое. Как правило, это список – список телефонных номеров, товаров, деталей, покупателей и т. п. Список Excel можно представить как базу данных.

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

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

Максимальный размер базы данных в Excel 256 полей и 65535 записей.

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

1. Формирование базы данных

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

Создание и введение списка: вводим названия полей в одной строке таблицы. Эти названия (например, Группа товара, Цена, Марка, Продавец, Покупатель) будут служить заголовками соответствующих столбцов. Далее выделим все названия полей и применим к ним например формат, жирное начертание, шрифт Times New Roman, размер шрифта 12, чтобы они выделялись на фоне остальных записей базы данных. Вводим записи в базу данных строго под названиями полей, по одной записи в строке, не пропуская пустых строк.

Если необходимо добавить записи, это сделать можно вручную непосредственно на рабочий лист. Также ввод данных, поиск и удаление существующих записей удобно производить с помощью форм. Выделим любую ячейку или диапазон ячеек в базе данных и выбираем команду Данные → Форма→ Добавить, и вводим данные для новой записи в соответствующие текстовые поля, и так для каждой новой записи. Когда все записи набраны нажимаем кнопку Закрыть. Новые записи окажутся в нижней части таблицы.

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

Выделяем любую ячейку или диапазон ячеек в базе данных и выбираем команду Данные →Форма →Далее (или Назад), пока запись, которую нужно удалить, не появится в окне формы, и нажимаем кнопку Удалить. В появившемся информационным окне нажимаем кнопку Ок.

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

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

Для эффективной работы в Excel важно научиться быстро и правильно манипулировать данными. Для использования некоторых операций автоматической обработки данных часто бывает необходимо заранее расположить данные в таблице в строго определённой последовательности – выполнить сортировку исходных данных.Сортировка осуществляется на «месте» - непосредственно в таблице (в курсовой работе это будет выполняться на копиях на других листах). Можно сортировать как строки так и столбцы. Списки можно сортировать в возрастающем или убывающем порядке.

Список не трудно отсортировать по любому полю. Выделить любую ячейку в этом поле, а затем выбрать пункт Сортировка и фильтр в меню Главная. Также отсортировать данные можно с помощью пункта Сортировка и фильтр →Настраиваемая сортировка. Для этого:

1. Выделяем диапазон данных или любую ячейку в базе данных и выбираем пункт Сортировка и фильтрация → Настраиваемая сортировка.

2. В списке Сортировать по указываем название столбца, в котором нужно выполнить сортировку.

3. Указываем в списке Порядок положение: По возрастанию или По убыванию.

4. Если нужно провести дополнительную сортировку, выбираем пункт Добавить уровень, затем в появившемся поле в списках Затем по и выбираем порядок сортировки для каждого поля.

5. Чтобы установить дополнительные параметры сортировки, нажимаем кнопку Параметры.

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

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

8. Установим переключатель Сортировать в нужное положение: Строки диапазона – для сортировки по строкам диапазона или Столбцы диапазона – для сортировки по столбцам. Нажмите кнопку Ок.

Отсортируем базу данных «Сведения о продаже мебели» по полю «Группа товара» по алфавиту (приложение 2), на следующем листе проведем сортировку базы данных по двум полям: «Продавец» и «Сумма» (приложение 3).

3. Фильтрация

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

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

• Все - позволяет вывести все записи.

• Первые 10 – позволяет вывести заданное количество наибольших или наименьших элементов списка или таблицы.

• Условие – позволяет выводить на экран только те записи, которые соответствуют заданным условиям. Условия задаются в окне диалога Пользовательский автофильтр, в котором их можно группировать с помощью логических операторов И и ИЛИ.

• Пустые – позволяет вывести все записи, в которых заданное поле пустое.

• Не пустые – позволяет вывести все записи, в которых данное поле содержит данные.

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

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

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

Если необходимо отменить фильтрацию повторно выберите пункт Фильтр.

В базе данных «Сведения о продаже мебели» произведём фильтрацию по полю «Покупатель» (приложение 4) и автофильтр с условием по полю «Сумма»: отфильтруем полученные суммы 200.000руб и

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

1. Создать условия отбора;

2. Установить курсор внутри таблицы;

3. Данные дополнительно;

4. В поле диапазон условий ввести критерий.

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

Покупатель	Продажа
магазин АРГУС	сентябрь

фильтрации :

Из базы данных «Сведения о продаже мебели» будут выбраны данные, о том что было приобретено в сентябре магазином Аргус (приложение 6).

4. Элементы управления

Для использования элементов управления форм в Microsoft Office Excel необходимо включить вкладку Разработчик. Для этого выполните указанные ниже действия.

1. Нажмите кнопку Microsoft Office и выберите пункт Параметры Excel.

2. Откройте вкладку Основные, установите флажок Показывать вкладку "Разработчик" на ленте и нажмите кнопку ОК.

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

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

Настройка списка, связи с ячейкой и функции INDEX

В базе данных «Сведения о продаже мебели» следующие элементы управления (приложение 7):

Список фамилий продавцов:

1. Чтобы добавить список , откройте меню Разработчик, нажмите кнопку Вставить и щелкните в разделе Элементы управления формы пункт Список.

1. Щелкните список правой кнопкой мыши и выберите пункт Формат объекта. Вводим данные..

1. Чтобы указать для списка диапазон, вводим D7:D56 в поле Формировать список по диапазону.

2. Чтобы ввести число в ячейку A4 (в зависимости от того, какой элемент выбран в списке), введите A4 в поле Связь с ячейкой.

Примечание. В формуле INDEX() значение в ячейке A4 используется для возврата необходимого элемента списка.

В списке должен быть отображен перечень элементов. Чтобы воспользоваться списком, щелкните сначала любую ячейку за его пределами. Если теперь щелкнуть элемент списка, в ячейке А4 будет показан номер этого элемента в списке. В формуле INDEX в ячейке A3 этот номер будет использован для отображения названия элемента.

Вводим в ячейку A3 следующую формулу:

=INDEX(D7:D56,А3,0)

Формируем поля Кол-во, Цена, Стоимость, Группа товара и вводим: в ячейку В4 следующую формулу: =ИНДЕКС(G7:G56;A4);

в ячейку В3 следующую формулу: =ИНДЕКС(G7:G56;A4);

в ячейку С3 следующую формулу: =ИНДЕКС(С7:С56;A4);

в ячейку D3 следующую формулу: =B3*C3;

в ячейку E3 следующую формулу: =ИНДЕКС(A7:A56;A4);

в ячейку G1 создаём флажок  Стоимость без НДС (флажок позволяет реализовать логическое выражение «Истина» и «Ложь»), через функцию «Если» с применением флажка будем считать стоимость товара без НДС – для этого в ячейку G1 введём заголовок «Стоимость без НДС», в меню Разработчик выбрать пункт Вставить→Флажок,

и

=ЕСЛИ(J4=ИСТИНА;D3*1,2;D3)

усмтанавливаем с вязь с ячейкой J4 ( в ячейке J4 набираем слово ИСТИНА), для этого выделяем флажок и в строке формул пишем : =$J$4. Аналогично устанавливаем флажок в ячейку Н1. Далее вводим в ячейку G3 следующую формулу:

в

=ЕСЛИ(J4=ИСТИНА;C3*1,18;C3)

ячейку H3 формулу:

1. Сводная таблица, сводная диаграмма

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

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

1. Выберите ячейку из диапазона ячеек или укажите точку вставки внутри таблицы. Убедитесь, что в диапазон ячеек попадает заголовок столбца.

2. Выберите тип создаваемого отчета, выполнив одно из действий, описанных ниже.

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

1.Выберите пункт Выбрать таблицу или диапазон.

2.Введите в поле Таблица или диапазон диапазон ячеек.

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

Чтобы поместить отчет сводной таблицы на новый лист, начиная с ячейки A1, щелкните пункт На новый лист. Чтобы поместить отчет сводной таблицы на существующий лист, выберите пункт На существующий лист и введите первую ячейку диапазона, в который следует поместить отчет сводной таблицы. Нажмите кнопку ОК.

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

При создании сводной таблицы базы данных «Сведения о продаже мебели» были выбраны поля «Группа товара», «Продавец», «Продажа»

(приложение 8).

Для создания отчета сводной диаграммы на основе отчета сводной таблицы

1. Выберите нужный отчет сводной таблицы.

2. В меню Вставка в пункте Сводная таблица → Сводная диаграмма, выберите тип диаграммы.

Сводная диаграмма по базе данных «Сведения о продаже мебели» (приложение 9) была составлена по полям «Марка», «Продажа» и «Сумма»

Заключение

Довольно часто обрабатываемую информацию приходится представлять в виде таблиц. Для решения задач которые можно представить в виде таблиц, используют электронные таблицы. Электронные таблицы, прежде всего, ориентированы на решение экономических задач. Однако с их помощью можно решать и инженерные задачи. Задачи, решаемые в системах организационно-экономического управления, связаны с обработкой больших объемов информации, использованием графической интеграции данных в виде диаграмм и графиков, необходимостью группировки и сортировки данных по разным показателям, проведением анализа данных дальнейшего принятия решения, а также выводом на печать большего количества отпечатанных форм. Все эти задачи можно успешно решить, не владея при этом специальными знаниями в области программирования, применив в работе табличные процессоры (электронные таблицы).

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

СПИСОК ЛИТЕРАТУРЫ:

1. Дубина А.Г., Орлова С.С., Шубина И.Ю., Хромов А.В. Excel для экономистов и менеджеров. - СПб.: Питер, 2004..

2. Овчарено Е. К., Ильина О. П., Балдыбердин Е. В. Финансово-экономические расчеты в Excel. M., 1999

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

4. Лавренов С. М. Excel: Сборник примеров и задач - М.: Финансы и стати- стика, 2003.

Приложение 1

Приложение 2

Приложение 3

Приложение 4

Приложение 5

Приложение 6

Приложение 7

Приложение 8

Приложение 9

24