Каталог Рефератов - Создание информационной системы средствами MS Excel и VBA

	Информационно-образоательный портал
	Рефераты, курсовые, дипломы, научные работы,



МЕНЮ\|

поиск

Создание информационной системы средствами MS Excel и VBA

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

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

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

Флажок Только уникальные записи предназначен для отображения только неповторяющихся строк.

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

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

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

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

9. Функции для анализа списка
Понятие функции в MS Excel.

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

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

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

Функций можно задавать с помощью диалогового окна "Мастер функций". Оно вызывается командой Вставка/Функция либо нажатием на кнопку Вставка функции на панели Стандартная.

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

Функции для анализа списка.

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

Например, мне нужно подсчитать количество книг, изданных после 1980г. в издательстве Лань, тираж которых больше 100000 и цена которых больше 50 рублей Для этого я сначала применила расширенный фильтр, тем самым отфильтровала БД по условию, показанному на рис.16.

Рис.16 Применение расширенного фильтра

Чтобы подсчитать количество книг, изданных после 1980 г. в издательстве Лань, тираж которых больше 100000 и цена которых больше 50 рублей, необходимо открыть диалоговое окно Мастер функций, найти функцию СЧЕТЕСЛИ, ввести в строку Диапазон весь диапазон ячеек отфильтрованного списка, а в строку Критерий - условие счета (рис.17)

Рис.17 Диалоговое окно функции СЧЕТЕСЛИ.

Применив функцию СЧЕТЕСЛИ, я подсчитала количество книг, изданных после 1980г. в издательстве Лань, тираж которых больше 100000 и цена которых больше 50 рублей (рис.18).

Рис.18 Применение функции СЧЕТЕСЛИ.

Функции БД.

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

Функции БД имеют обобщенное название Д-функции. Д-функции оперируют только с элементами диапазона, которые удовлетворяют заданным условиям.

У всех Д-функций один и тот же синтаксис:

1) база данных задает весь список, а не отдельный столбец;

2) поле определят столбец, в котором производится вычисление;

3) критерий - диапазон, содержащий условие БД.

В своей работе я применила следующие функции БД:

v БСЧЕТА - подсчитывает количество непустых ячеек в выборке из заданной БД по заданному критерию.

Допустим, мне необходимо подсчитать количество книг, изданных в издательстве Высшая школа. Для этого я вызываю диалоговое окно функции БСЧЕТА, в строке База данных ввожу всю БД, в строке Поле ввожу номер столбца, в котором производится расчет, а в строке Критерий ввожу условия счета (рис.19).

Рис.19 Диалоговое окно функции БСЧЕТА.

Выполнив все эти действия, я подсчитала количество книг, изданных в издательстве Высшая школа (рис.20).

Количество книг, изданных в издательстве Высшая школа
5

Рис.20 Применение функции БСЧЕТА
ДМАКС - возвращает максимальное значение поля (столбца) записей БД, удовлетворяющих указанным условиям.
Например, мне нужно найти самый большой тираж. Вызвав диалоговое окно функции ДМАКС, повторяю те же действия, что и при функции БСЧЕТА (рис.21).

Рис.21 Окно диалога функции ДМАКС.

Выполнив все необходимые действия, я нашла самый большой тираж (рис.22).

Самый большой тираж:
3500000

Рис.22 Применение функции ДМАКС.

v ДМИН - возвращает минимальное значение среди выделенных фрагментов БД.
К примеру, мне надо найти самую низкую цену. Как и при использовании предыдущих функций, я вывожу диалоговое окно функции ДМИН и ввожу в поля необходимые условия (рис.23)

Рис.23 Диалоговое окно функции ДМИН.

Запустив эту функцию, я нашла самую низкую цену (рис.24).

Самая низкая цена:
130

Рис.24 Функция ДМИН.

v ДСРЗНАЧ - возвращает среднее всех значений столбца или БД, которые удовлетворяют заданным условиям.

Допустим, мне надо найти среднюю цену. Для этого я открываю окно диалога функции ДСРЗНАЧ, ввожу в нее все необходимые условия, запускаю функцию (см. рис.25); и на рабочем листе выводится вычисленное значение (рис.26).

Рис.25 Диалоговое окно функции ДСРЗНАЧ.

Применение функции ДСРЗНАЧ
Средняя цена:
154,3

Рис.26 функция ДСРЗНАЧ
10. Подведение промежуточных итогов
Зачастую при работе с электронными таблицами или БД возникает необходимость подводить какие-либо итоги. Например, подсчитать количество, сумму, произведение и т.д. по какому-нибудь столбцу. С этой целью в MS Excel применяется специальная команда для подведения промежуточных итогов.

Команда Данные/Итоги… может быть использована для получения различной итоговой информации. Но прежде чем подводить итоги, нужно обязательно отсортировать список соответствующим образом (рис.27).

Рис.27 Сортировка списка по полю Издательство.

После сортировки можно подвести итоги по данной таблице.

К примеру, мне необходимо подсчитать количество книг, изданных в каждом издательстве. Для этого нужно набрать команду Данные/Итоги…, в появившемся диалоговом окне Промежуточные итоги ввести в поля критерии, показанные на рисунке 28:

Рис.28 Диалоговое окно команды Промежуточные итоги.

Применив данную функцию, я произвела подсчет по столбцу Издательство (рис.29).

	Издательство
Айрис-Пресс Количество	1
Альянс-В Количество	1
Аст Количество	1
Астрель-АСТ Количество	1
Бином Количество	1
БХВ-Санкт-Петербург Количество	1
Высшая школа Количество	5
Дрофа Количество	4
Лаборатория Базовых Знаний Количество	1
Лань Количество	1
Машиностроение Количество	2
Металлургия Количество	1
Наука Количество	3
Питер Количество	1
Просвещение Количество	8
Проспект Количество	1
Советский писатель Количество	1
Современное слово Количество	1
Феникс Количество	1
Центр гуманитарного образования Количество	1
Эксмо Количество	3
Общее количество	40

Рис.29 Пример подведения итогов

11. Проверка вводимых значений
MS Excel предлагает специальное средство, позволяющее проверять, удовлетворяют ли заданным условиям вводимые в список значения. Следует отметить, что проверке подвергаются только значения, вводимые пользователем непосредственно в ячейки.

Чтобы задать условия проверки данных, нужно выделить диапазон ячеек, к которому должны применяться эти условия, затем воспользоваться командой Данные/Проверка…. На экране появится окно диалога Проверка вводимых значений, содержащее три вкладки: Параметры, Сообщение для ввода, Сообщение об ошибке (рис.30).

Рис.30 Диалоговое окно Проверка водимых значений.

Для того чтобы в поле Издательство при вводе значения, не входящего в список допустимых (весь перечень наименований издательств), появлялось предупреждение об ошибке, нужно в Параметрах в Типе данных указать критерий Список, в Источнике - допустимые значения. В Сообщении для ввода и в Сообщении об ошибке выбрать вид сообщения и ввести текст предупреждения (рис.31).

Рис.31 Вкладка Сообщение об ошибке.

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

Рис.32 Пример проверки вводимых значений

12. Диаграммы и графики
Представление данных в графическом виде позволяет решать самые разнообразные задачи. Основное достоинство такого представления заключается в его наглядности. На графиках легко просматривается тенденция изменения, при этом можно определить скорость изменения тенденции. Различные соотношения, прирост, взаимосвязь различных процессов - все это легко можно увидеть на графике.

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

Построение и редактирование диаграмм и графиков.

Первый шаг построения диаграмм предполагает выбор типа будущего изображения. На данном этапе имеется возможность выбора стандартного или нестандартного типа диаграммы (рис. 33). После того как тип диаграммы определен, следует кнопку Далее> для продолжения процесса построения.

Рис.33 Диалоговое окно Мастер диаграмм.

На втором шаге необходимо выбрать источник данных для диаграммы (рис.34).

Рис.34 Выбор источника данных для диаграммы.

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

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

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

Задать название ряда можно в поле Имя, непосредственно введя его с клавиатуры или выделив на листе, временно свернув диалоговое окно.

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

В поле Подписи оси X вводятся единицы измерения оси X.

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

Рис.35 Параметры диаграммы.

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

Рис. 36 Окончание построения диаграммы.

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

1) Диаграмма "Стоимость книг".

Чтобы создать данную диаграмму, я открыла диалоговое окно "Мастер диаграмм", на вкладке Стандартные выбрала тип диаграммы - Круговой и указала ее вид. В поле Диапазон ввела диапазон ячеек, по данным которых должна строиться диаграмма. Затем на вкладке Заголовок ввела текст заголовка диаграммы ("Стоимость книг"), на вкладке Легенда указала расположение легенды (снизу), а на вкладке Подписи данных указала те критерии, которые необходимо отразить на диаграмме (значения). Выполнив все эти условия, я построила необходимую диаграмму (рис.37).

2) Диаграмма "Подведение промежуточных итогов (Количество издательств)".

При создании этой диаграммы я выполнила ту же последовательность действий, как и при построении предыдущей. Наглядное изображение этой диаграммы можно увидеть на рисунке 38.

3)Диаграмма "Тираж книг в каждом издательстве".

Алгоритм построения этой диаграммы такой же, как и у двух предыдущих. Отличия лишь в том, что тип этой диаграммы не "Круговая", а "Гистограмма". И на вкладке Подписи данных помимо основного заголовка диаграммы ("Тираж книг в каждом издательстве") указаны еще заголовки осей координат (X - "Издательство", Y - "Тираж). В общей сложности эта диаграмма выглядит следующим образом (рис.39).

4)Диаграмма "Годы издания книг".

Построение данной диаграммы аналогично с построением предыдущих. Разница лишь в том, что в области Тип я выбрала диаграмму Кольцевую и на вкладке Заголовок ввела текст заголовка диаграммы ("Годы издания книг"). Наглядное изображение диаграммы на рисунке 40.

Рис. 37 Диаграмма "стоимость книг".

Рис. 38 Диаграмма "Тираж книг в каждом издательстве".

Рис.39 Диаграмма "Годы издания книг".

Страницы: 1, 2

© 2003-2013
Рефераты бесплатно, курсовые, рефераты биология, большая бибилиотека рефератов, дипломы, научные работы, рефераты право, рефераты, рефераты скачать, рефераты литература, курсовые работы, реферат, доклады, рефераты медицина, рефераты на тему, сочинения, реферат бесплатно, рефераты авиация, рефераты психология, рефераты математика, рефераты кулинария, рефераты логистика, рефераты анатомия, рефераты маркетинг, рефераты релиния, рефераты социология, рефераты менеджемент.