Автоматизация экономических задач средствами информационных компьютерных технологий

статистический финансовый запрос access

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

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

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

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

· удовлетворение персональных информационных потребностей человека, как для профессиональной, так и для бытовой сферы;

· снижение трудоемкости процессов использования информационных ресурсов и повышение их надежности и оперативности;

· производство информации для ее анализа человеком и принятия на его основе решения по выполнению какого-либо действия.

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

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

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

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

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

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

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

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

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

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

1. Статистический анализ и прогнозирование в MS Excel

1.1 Использование равномерного распределения

Постановка задачи и ее математическая формулировка

Необходимо сгенерировать в MS Excel на Лист1 столбец из 300 случайных величин, подчиняющихся закону Равномерное распределение. Параметры между 20 и 60.

Описание входной и выходной информации

Для того, чтобы создать последовательность, моделирующую реальные данные, необходимо воспользоваться функцией из Пакета анализа Генерация случайных чисел. Для этого сначала следует задать на рабочем листе MSExcel диапазон, который будет содержать данную последовательность. Затем выбрать команду Анализ данных на вкладке Данные. В появившемся окне диалога в списке Инструменты анализа выбрать элемент Генерация случайных чисел в соответствии с рисунком 1.1.

Рисунок 1.1. Окно диалога Анализ данных В окне диалога Генерация случайных чисел в поле Число переменных необходимо ввести единицу (рис. 1.2). Это будет означать, что число столбцов, которые будут заполнены последовательностью смоделированных данных, будет равно единице. В поле Число случайных чисел ввести 300. Затем в списке Распределение выбрать элемент Равномерное, в поля Между и ввести значения 20 и 60. В поле Случайное рассеивание можно ввести некоторую величину, в случае, если необходимо создать несколько одинаково распределенных последовательностей с отличающимися значениями. Если достаточно одной последовательности, оставить это поле пустым. В поле Выходной интервал ввести ссылку на первую ячейку диапазона, который должен быть заполнен последовательностью и нажать кнопку ОК для генерации последовательности.

Рисунок. 1.2. Окно диалога Генерация случайных чисел

Результаты расчетов, графики и диаграммы

Результатом этих действий будет столбец из 300 случайных значений от 20 до 60 распределенных равномерно в соответствии с рисунком 1.3.

Рисунок 1.3. Сгенерированная последовательность случайных чисел, распределенных равномерно

1.2 Гистограмма

Постановка задачи и ее математическая формулировка

Построить гистограмму для ваших данных, используя в качестве интервала карманов диапазон целых чисел от 18 до 54 с шагом 3.

Описание входной и выходной информации

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

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

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

В списке Инструменты анализа выбрать пункт Гистограмма. В поле Входной интервал указать диапазон $C$ 1:$C$ 300. В поле Интервал карманов ввести диапазон $E$ 1:$E$ 37. Если не ввести Интервал карманов, MSExcel по умолчанию создаст равномерно распределенный диапазон. В качестве Выходного интервала ввести ссылку на левую верхнюю ячейку диапазона, в который будут помещены результаты. Установить переключатель Вывод графика в соответствии с рисунком 1.4 и 1.5.

При построении гистограмм можно использовать дополнительные возможности, установив соответствующие флажки в окне диалога Гистограмма:

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

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

Рисунок 1.4. Окно диалога Гистограмма

Результаты расчетов, графики и диаграммы

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

1.3 Сглаживание данных. Скользящее среднее

Постановка задачи и ее математическая формулировка

Построить Скользящее среднее для 4 интервалов.

Описание входной и выходной информации

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

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

Для того, чтобы воспользоваться методом Скользящее среднее в окне диалога Анализ данных следует выбрать средство Скользящее среднее в соответствии с рисунком 1.6 и 1.7.

Рисунок 1.6 Окно диалога Скользящее среднее Рисунок 1.7 Результат работы метода скользящее среднее

1.4 Сглаживание данных. Экспоненциальное сглаживание

Постановка задачи и ее математическая формулировка

Построить Экспоненциальное сглаживание с фактором затухания 0,3.

Описание входной и выходной информации

Чтобы построить Экспоненциальное сглаживание, в диалоговом окне команды Анализ данных выбрать элемент Экспоненциальное сглаживание. Ниже представлены заполненные поля ввода окна диалога Экспоненциальное сглаживание в соответствии с рисунком 1.8.

Рисунок 1.8 Окно диалога Экспоненциальное сглаживание

Результаты расчетов, графики и диаграммы

Результаты экспоненциального сглаживания представлены ниже в соответствии с рисунком 1.9.

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

1.5 Прогнозирование данных при помощи линии тренда

Постановка задачи и ее математическая формулировка

При помощи линии тренда спрогнозировать данные на 3 периода вперед, определить историю данных на 1 период назад, дать представление о степени достоверности отображаемых на гистограмме данных.

Описание входной и выходной информации

Линии тренда являются статистическим инструментом, при помощи которого можно анализировать тренды и делать прогнозы, а также давать представление о степени достоверности отображаемых на диаграмме данных. Линию тренда можно добавить к любому ряду данных, использующему ненормированные плоскости диаграммы. Линия тренда связывается с рядом данных, к которому была добавлена, и автоматически пересчитывается и обновляется при изменении значений любых точек ряда данных. При создании линии тренда на основе данных диаграммы применяется один из пяти типов аппроксимирующих линий: линейная, логарифмическая, полиноминальная, степенная и экспоненциальная. При построении линии тренда предоставляется возможность выбирать значения пересечения линии тренда с осью Y, добавлять к диаграмме уравнение аппроксимации и величину достоверности аппроксимации (R-квадрат). Чем ближе значение величины достоверности аппроксимации к 1, тем достовернее прогноз данных.

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

В появившемся окне диалога на вкладке Параметры линии тренда указать тип аппроксимирующей линии; установить параметры линии тренда: автоматическое (для линии тренда используется название ряда данных), другое (позволяет ввести новое название линии тренда в текстовое поле), прогноз вперед на и назад на (соответственно прогнозируются данные вперед на указанное число периодов или определяется история данных назад на указанное число периодов); пересечение кривой с осью Y в точке (по умолчанию вычисляется на основе данных); показывать уравнение на диаграмме (Выводится уравнение аппроксимации на диаграмму в виде несвязанного текстового поля); поместить на диаграмму величину достоверности аппроксимации (осуществляется вывод на диаграмму величины R² в виде несвязанного текстового воля).

Рис. 1.10. Окно диалога Формат линии тренда

Результаты расчетов, графики и диаграммы

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

Рис. 1.11. Прогнозирование данных

2. Финансовые расчеты и оптимизационные задачи в MS Excel

2.1 Расчеты с использованием финансовых функций в MS Excel

Постановка задачи и ее математическая формулировка

Фонд размером 21 млн руб. был сформирован за два года за счет отчислений 770 000 руб. в начале каждого месяца. Определить годовую ставку процента.

Описание входной и выходной информации

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

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

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

Затем следует вызвать Мастера функций командой на вкладке Формула Вставка-Функция — категория Финансовые.

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

СТАВКА (кпер; плт; пс; бс; тип)

Функция ВСТАВКА возвращает процентную ставку по аннуитету за один период.

Кпер — общее число периодов выплат займа или инвестиции.

Плт — выплата, производимая в каждый период и не меняющаяся за все время выплаты займа или инвестиции.

Бс — требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент опущен, он полагается равным 0 (будущая стоимость займа, например, равна 0). Например, если предполагается накопить 50 000 руб. для оплаты специального проекта в течение 18 лет, то 50 000 руб. это и есть будущая стоимость. Можно сделать предположение о сохранении заданной процентной ставки и определить, сколько нужно откладывать каждый месяц.

Тип — число 0 или 1, обозначающее, когда должна производиться выплата.

Результаты расчетов, графики и диаграммы

Решение заданной задачи реализовано в соответствии с рисунками 2.1, 2.2.

Рисунок 2.1 — Окно диалога функции СТАВКА Результатом расчетов является ставка процента за один период в соответствии с рисунком 2.2 в ячейке В8.

Рисунок 2.2 Формула для функции СТАВКА Чтобы вычислить годовую ставку процента, необходимо полученный результат увеличить в 12 раз.

Рисунок 2.3 Результат вычислений

2.2 Расчеты с использованием подбора параметра в MS Excel

Постановка задачи и ее математическая формулировка

Определить необходимый уровень ставки купонных выплат, производящихся раз в полугодие, если курс покупки облигации — 87,91, облигации приобретены 1.03.01 и будут погашены 5.03.03; ожидаемая годовая ставка помещения (доход) — 14,45, номинал облигации (погашение) — 100, базис расчета — 1. Для решения задачи воспользоваться функцией ЦЕНА ().

Описание входной и выходной информации

Ниже приведен результат решения задачи при помощи функции СТАВКА в соответствии с рисунком 2.4.

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

Результат использования Подбора параметра приведен ниже в соответствии с рисунком 2.5.

Результаты расчетов, графики и диаграммы

Рис. 2.5 Результат работы Подбор параметра

2.3 Оптимизационные задачи в MS Excel

Постановка задачи и ее математическая формулировка

Четверо рабочих могут выполнять пять видов работ. Стоимость выполнения каждого вида работ составляет: для первого рабочего — 1, 6, 3, 5, 7; для второго — 5, 2, 7, 8, 3; для третьего — 3, 5, 1, 9, 2; для четвертого — 6, 4, 2, 10, 5. Составить план выполнения работ таким образом, чтобы каждый рабочий был занят не более чем на одной работе, а суммарная стоимость выполненных работ была минимальной.

Описание входной и выходной информации

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

ѕ Ассортимент продукции (максимизация выпуска товаров при ограничениях на сырье для производства этих товаров).

ѕ Составление штатного расписания для достижения наилучших результатов при наименьших расходах.

ѕ Планирование перевозок (минимизация затрат на транспортировку товаров при условии удовлетворения потребностей потребителей).

ѕ Составление смеси (получение заданного качества смеси при наименьших расходах) Все эти задачи обладают следующими свойствами:

1. Наличие единственной цели.

2. Наличие ограничений, выражающихся, как правило, в виде неравенств.

3. Наличие набора входных значений-переменных, непосредственно или косвенно влияющих на ограничения и на оптимизируемые величины.

Правильная формулировка ограничений является самой ответственной частью при создании модели для поиска решения, например:

1. Если в модели в наличии несколько периодов времени, величина материального ресурса на начало следующего периода должна равняться величине этого ресурса на конец предыдущего периода.

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

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

Ограничения имеют тот же синтаксис, что и логические формулы. Но, если в найденном решении логические формулы будут выполнены точно, то ограничения — с некоторой возможной погрешностью. Величина этой погрешности задается параметром Относительная погрешность и по умолчанию равна 0,1.

Ход решения.

Подготовку рабочего листа осуществляем в соответствии с рисунком 2.6, формулы для расчета приведены ниже в таблице.

Таблица: Формулы для расчета в задаче Рисунок 2.6 Подготовка рабочего листа для решения задачи Устанавливаем ограничения в окне Поиск решения, в соответствии с рисунком 2.7. В окне Параметры поиска решения необходимо также установить флажок Линейная модель.

Рисунок 2.7 Установка параметров в окне Поиск решения

Результаты расчетов, графики и диаграммы

Решение задачи представлено на рисунке 2.8.

Рисунок 2.8 Решение задачи

2.5 Анализ бизнес-ситуаций с помощью Диспетчера сценариев в MS Excel

Постановка задачи и ее математическая формулировка

Вас просят дать в долг Р руб. и обещают возвращать по А руб. в течение n лет (табл. Исходные данные). При какой процентной ставке эта сделка имеет смысл?

Таблица: Исходные данные

Описание входной и выходной информации

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

Рисунок 2.9 Подготовка рабочего листа

Для решения этой задачи используется функция ПС:

Функция ПС возвращает текущий объем вклада на основе постоянных периодических платежей. При выборе ставки в 0% сделка не будет выгодна. Поэтому, для нахождения минимальной процентной ставки, я воспользовалась функцией СТАВКА. Результаты вычислений приведены ниже, в соответствии с рисунком 2.10 и 2.11.

Рисунок 2.10 Использование функции ПС

Рисунок 2.11 Использование функции СТАВКА

Теперь необходимо использовать Диспетчер сценариев. Диспетчер сценариев вызывается с помощью команды Анализ «что если» на вкладке Данные.

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

Рисунок 2.12 Окно Диспетчера сценариев

Процесс изменения процентной ставки представлен на рисунке 2.13.

Рисунок 2.13 Процент изменения ставки

Результаты расчетов, графики и диаграммы

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

Рисунок 2.14 Измененная исходная таблица

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

Рисунок 2.15 Структура сценария

3. Структуризация и первичная обработка в MS Excel

3.1 Создание списка в режиме «Форма»

Постановка задачи и ее математическая формулировка

Создать список Доставка товаров на дом с использованием Формы.

Описание входной и выходной информации

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

При составлении списка Доставка товаров на дом можно выделить следующую информацию:

— Клиент;

— Товар;

— Стоимость;

— Дата заказа;

— Дата доставки;

— Стоимость доставки;

Экранная форма данных создается с помощью команды на вкладке «Данные» Форма. Перед вызовом команды курсор устанавливается в любой ячейке списка, либо выделяется весь список. Форма представляет собой аналог карточки для одной записи в соответствии с рисунком 3.2. В ней перечислены все поля и представлена информация для одной записи. Отображён номер текущей записи и общее их количество. С помощью кнопок «Назад» и «Далее» можно перебирать карточки. Кнопка «Добавить» добавляет пустой бланк в базу данных. Кнопка «Удалить» удаляет текущую запись из базы данных. Отменить удаление, сделанное в режиме формы нельзя. Редактирование самих данных производится непосредственно в полях ввода. «Вернуть» — отказ от сделанных изменений. Кнопка «Критерий» — предназначена для задания критериев поиска записей с указанным содержимым полей. При этом по кнопкам «назад» и «далее» будет отображаться информация согласно выбранному критерию.

3.2 Сортировка списков в MS Excel

Постановка задачи и ее математическая формулировка

Отсортировать исходный список по 3 ключам сортировки (по выбору).

Описание входной и выходной информации

Сортировка списка предназначена для более наглядного представления данных и осуществляется при помощи команды Сортировка на вкладке Данные. При сортировке данных списка можно задавать до 3-х ключей сортировки.

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

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

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

Результаты расчетов, графики и диаграммы

Исходный список сортируется по фамилии клиента в алфавитном порядке, затем по заказам, а затем по стоимости товара.

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

Постановка задачи и ее математическая формулировка

Применить к списку Автофильтр. Отобразить только «Первые 10…» записей.

Описание входной и выходной информации

Фильтрация списков позволяет отобрать записи по определённому критерию. Для фильтрации данных можно использовать 2 вида фильтров: Автофильтр и Расширенный фильтр.

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

Результаты расчетов, графики и диаграммы

Результатом работы Автофильтра является исходная таблица, содержащая 10 наибольших записей по полю «Стоимость».

3.4 Использование Расширенного фильтра в MS Excel

Постановка задачи и ее математическая формулировка

Применить к списку Расширенный фильтр. Диапазон условий задать самостоятельно в соответствии с предметной областью (не менее 3 условий).

Описание входной и выходной информации

Расширенный фильтр вызывается по команде на вкладке Данные — Фильтр — Расширенный фильтр. Перед его использованием необходимо подготовить список и создать диапазон критериев, где задаются условия отбора записей. Верхняя строка диапазона должна содержать заголовки полей, которые в точности совпадают с заголовками полей в области данных. Точность совпадения можно обеспечить путём копирования заголовков полей таблицы. В диапазон критериев можно заносить несколько условий, как в строке, так и в столбце.

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

Результаты расчетов, графики и диаграммы

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

3.5 Вычисление промежуточных итогов в MS Excel

Постановка задачи и ее математическая формулировка

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

Описание входной и выходной информации

Назначение промежуточных итогов состоит в обобщении данных. Для подведения промежуточных итогов в соответствии с рисунком 3.12 необходимо:

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

2. Указать, как будет идти группировка данных, выбрав значение в списке «При каждом изменении в». Предварительно столбец, по которому будет идти группировка, должен быть отсортирован.

3. Из списка «Операция» выбирается функция, которая будет использоваться при обработке данных.

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

3.6 Проведение сводного анализа в MS Excel

Постановка задачи и ее математическая формулировка

Проанализировать данные списка с использованием сводной таблицы.

Описание входной и выходной информации

Сводная таблица создаётся при помощи Мастера сводных таблиц (Данные — Сводная таблица) пошагово:

1. Указываем местонахождение исходных данных и тип создаваемого отчета (сводная таблица или сводная диаграмма).

2. Указываем диапазон, содержащий исходные данные.

3. При помощи кнопки «Макет» конструируем конфигурацию сводной таблицы.

4. Указывается место размещения сводной таблицы.

Результаты расчетов, графики и диаграммы

3.7 Выводы об особенностях структуризации и первичной обработки в MS Excel

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

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

Excel располагает мощными средствами коллективной работы, которые ориентированы в первую очередь на совместную работу нескольких приложений пакета в сетевой среде. Высокая степень совместимости приложений, включенных в пакет MicrosoftOffice, обусловлена поддержкой технологии связывания и внедрения OLE. Примером применения этой технологии может служить внедрение Excel — таблиц или PowerPoint — презентаций в документ, созданный средствами Word. При использовании этой технологии между объектами устанавливается динамическая связь. Двойной щелчок на объекте, вставленном в документ, приводит к запуску приложения, в котором он был создан.

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

4. Управление базами данных в СУБД MS Access

4.1 Разработка индивидуальной БД

Постановка задачи и ее математическая формулировка

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

Разработать исходные данные индивидуального примера, выполнить заполнение и проверку работоспособности БД.

Исходные данные:

Предметная область: Доставка товаров.

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

Количественные ограничения: БД содержит информацию о 20 клиентах, о 25 товарах, каждый клиент сделал 3 заказа.

Описание входной и выходной информации

Разработка информационно-логической модели БД.

Использование базы данных «Доставка на дом» позволит оперативно накапливать, систематизировать и эффективно обрабатывать информацию, формировать отчеты.

При традиционной организации деятельность организации по продаже и доставке товаров на дом находит свое отражение в следующих таблицах:

ѕ товары: содержит описание товара;

ѕ клиенты: включает подробную информацию о клиенте;

ѕ заказы: включает подробную информацию о заказе.

Целью создания БД является автоматизация учета информации о совершенных заказах и доставке товаров.

Функции проектируемой БД:

ѕ хранение информации о клиентах;

ѕ хранение информации о сформированных заказах;

ѕ хранение информации о товарах;

ѕ обновление и добавление информации;

ѕ анализ информации по различным срезам (клиент, товар, заказ);

ѕ выдача итоговой информации в виде отчетов.

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

· фамилия, имя, отчество клиента, дата рождения, адрес, телефон клиента;

· название товара, количество на складе, стоимость единицы, стоимость доставки;

· заказанный товар, количество, дата заказа и доставки, сумма к оплате.

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

В соответствии с понятиями реляционной БД будет соответствовать таблица.

Реальные отношения между информационными объектами являются отношения «многие-ко-многим». Такие отношения непосредственно не поддерживаются реляционными СУБД. Реальные отношения «многие-ко-многим» разбиваются на отношения «один-ко-многим» после ввода промежуточной таблицы Заказы. Для установления связей каждому объекту назначается ключевое поле.

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

На основании спроектированной информационно-логической модели можно приступить к непосредственному созданию БД с помощью СУБД Microsoft Access.

Создание таблиц БД с помощью Microsoft Access.

Новая БД создается с помощью команды «Создать» на вкладке «Файл». При этом сразу указывается имя БД и ее расположение. В окне БД на вкладке Таблицы выполняется разработка макетов таблиц.

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

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

Формирование схемы БД.

Схема БД формируется после окончания работы над макетами таблиц с помощью команды на вкладке «Работа с базами данных»

4.2 Проектирование запросов

Постановка задачи и ее математическая формулировка

Спроектировать запросы:

запросы на выборку, содержащие условие или условия отбора (с использованием операторов сравнения, логических операторов, оператора Between, Like) — не менее 5 запросов;

запросы с параметрами (с одним и несколькими параметрами — не менее 3 запросов;

запросы, содержащие вычисляемые поля;

запросы с групповыми операциями (используя статистические функции Sum, Max, Min, Avg, Count) — не менее 5 запросов;

запрос с повторяющимися записями — 1 запрос;

запрос записи без подчиненных — 1 запрос;

перекрестный запрос (с использованием Мастера и в режиме Конструктора) — не менее 2 запросов;

запросы на изменение (создание новых таблиц, обновление, добавление, удаление данных) — не менее 3 запросов;

При создании запросов из пунктов 4.2.1−4.2.8 необходимо описать источники запроса, поля, свойства запроса.

Разработать в базе данных по варианту запросы в режиме SQL:

запрос на выборку выбранных (указанных самостоятельно) полей по одной таблице;

запрос по связанным таблицам;

запросы с условиями отбора и параметрами;

запросы с фразой BETWEEN или IN;

запросы с фразой LIKE;

запрос с сортировкой;

запросы с вычислениями (без группировки);

запросы с фразой GROUP BY;

запросы с фразой HAVING;

запрос с подзапросом;

провести модификацию базы данных — добавить поле в таблицу (в соответствии с вариантом). Обновить поле соответствующими значениями;

обновить поле соответствующими значениями разработать перекрестный запрос;

разработать запрос-действие на создание таблицы;

разработать запрос на обновление поля в таблице;

разработать запрос на удаление таблицы;

Описание входной и выходной информации

«Стоимость товара» — позволяет отобразить информацию о предмете, стоимость которых >50 €. Для этого используем оператор сравнения.

«Сортировка клиентов» — позволяет сформировать список клиентов, по заданному условию. В данном случае выберем клиентов чьи фамилии находится в интервале от, А до Н.

«Клиенты проживающие по ул. Я. Коласа» — позволяет сформировать список клиентов, проживающие по ул. Советская. Создание использованием оператора Like аналогично Источник запроса — таблица «Клиенты».

«Товары, купленные с 1 марта 2012 по 10 марта 2012 года» — позволяет сформировать список товаров, заказанных с 1 марта 2012 по 10 марта 2012 года. Источник запроса — таблица «Заказы».

«Фамилии на — ая» — позволяет сформировать список клиентов, фамилии которых заканчиваются на «-ая».

«Покупка товаров по названиям» — позволяет отобразить наименования товаров, которые покупались и кто их купил. Создание в режиме Конструктора. Для определения параметра запроса в строку условие отбора для столбца «Наименование товара» вместо конкретного значения вводится фраза, заключенная в квадратные скобки [Введите наименование товара]. Эта фраза будет выводиться в виде приглашения в диалоговом окне каждый раз при выполнении запроса.

«Заказы, которые заказал один клиент» — позволяет отобразить товары, которые заказал один клиент. Создание в режиме Конструктора. Для определения параметра запроса в строку условие отбора для столбца «Фамилия» вводится фраза, заключенная в квадратные скобки [Введите Фамилию]. Эта фраза будет выводиться в диалоговом окне каждый раз при выполнении запроса.

«Залоговая стоимость в Рублях» — позволяет отобразить стоимость товара в Рублях. Создание в режиме Конструктора. Источник запроса — таблица «Товары». Для определения параметра запроса в строку условие отбора для столбца «Цена» вместо конкретного значения вводится фраза, заключенная в квадратные скобки [Введите курс Рубля]. Эта фраза будет выводиться в диалоговом окне каждый раз при выполнении запроса.

«Фамилия и инициалы» — позволяет отобразить список фамилий и инициалов всех клиентов. Создание в режиме Конструктора. В запрос включаются следующие поля «Фамилия клиента», «Имя клиента», «Отчество клиента». Для создания вычисляемого поля используется функция Left и оператор конкатенации строк &.

«Возраст клиентов» — позволяет рассчитать возраст клиентов. Используется Построитель выражений. Создание в режиме Конструктора.

«Сумма к оплате» — позволяет сформировать список содержащий цену купленных товаров, которые заказал каждый клиент + стоимость доставки. Создание в режиме Конструктора. Создается с использованием групповой операции Sum по полю цена. Создание запроса в режиме конструктора.

«Количество заказов клиента» — позволяет отобразить количество заказов, сделанных клиентами. Создание в режиме Конструктора. Создается с использованием групповой операции Count по полю Заказы. Создание запроса в режиме конструктора.

«Максимальная сумма к оплате» — позволяет высчитать максимальную цену, которую заплатит каждый клиент. Запрос создается с использованием операции Мах по полю «Сумма к оплате». Создание запроса в режиме конструктора.

«Средняя сумма к оплате» — позволяет высчитать среднюю цену, которую заплатит каждый клиент. Запрос создается с использованием операции Avg по полю «Сумма к оплате». Создание запроса в режиме конструктора.

«Минимальная сумма к оплате» — позволяет высчитать минимальную цену, которую заплатит каждый клиент. Запрос создается с использованием операции Min по полю «Сумма к оплате». Создание запроса в режиме конструктора.

«Запрос с повторяющимися записями» — позволяет отобразить список, где есть повторяющие записи. Запрос создаем с помощью Мастера. Исходная таблица — Товары.

«Клиенты не имеющие данные» — позволяет определить клиентов, которые не имеют данных. Чтобы создать запрос нужно выбрать в списке запрос «Записи без подчиненных» и нажать кнопку ОК. В списке таблиц выбрать таблицу, содержащую повторяющиеся записи без подчиненных «Клиенты», и нажать кнопку Далее.

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

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

«Запрос на создание новой таблицы» — позволяет создать Новую таблицу содержащую все данные из таблицы «Клиенты». Создается запрос на «Создание новой таблицы».

«Удаление клиента» — позволяет удалить информацию о клиенте. В качестве фамилии задать параметр.

«Запрос на обновление данных» — позволяет обновить данные. В результате этого запроса, стоимость доставки измениться на 5 процентов.

Запрос на выборку выбранных полей по одной таблице. «Клиенты» — позволяет отобразить фамилию, имя, отчество и телефон клиента. Выборка выполняется на основе SQL-запроса. Текст запроса в режиме SQL:

SELECT Фамилия, Имя, Отчество, Телефон FROM Клиенты.

Запрос с условие. «Стоимость > 50 €» — позволяет отобразить товары, стоимость которых более 50 €. Выборка выполняется на основе SQL-запроса. Текст запроса в режиме SQL:

SELECT Товары. [Наименование товара], Товары. [Стоимость единицы]

FROM Товары

WHERE (((Товары. [Стоимость единицы])>50));

Запрос с параметром позволяет отобразить информацию, о приобретении конкретного товара. Выборка выполняется на основе SQL-запроса. Текст запроса в режиме SQL:

SELECT Клиенты. Фамилия, Товары. [Наименование товара], Заказы. [Дата заказа]

FROM Товары INNER JOIN (Клиенты INNER JOIN Заказы ON Клиенты. Код = Заказы. Клиент) ON Товары. Код = Заказы. Товар

WHERE (((Товары. [Наименование товара])=[Введите наименование товара]));

Запросы с фразой Between. «Заказы с 1 по 10 марта» — позволяет отобразить совершенные заказы в период указанный выше. Выборка выполняется на основе SQL-запроса. Текст запроса в режиме SQL:

SELECT Заказы. Клиент, Заказы. Товар, Заказы. [Дата заказа]

FROM Заказы

WHERE (((Заказы. [Дата заказа]) Between #3/1/2012# And #3/10/2012#));

Запросы с фразой Like. «Клиенты по заданной фамилии». В данном случае выборка будет тех фамилий, которые заканчиваются на «ая». Выборка выполняется на основе SQL-запроса. Текст запроса в режиме SQL:

SELECT Клиенты. Фамилия, Клиенты. Имя, Клиенты. Отчество

FROM Клиенты

WHERE (((Клиенты. Фамилия) Like «*ая»))

Запрос с сортировкой. «По дате заказа» — отображает список заказов, сортированный по дате заказа. ORDER BY поле1 [ASC|DESC] позволяет управлять порядком вывода результирующей выборки: ASC — по возрастанию (по умолчанию); DESC — по убыванию. Выборка выполняется на основе SQL-запроса. Текст запроса в режиме SQL:

SELECT *

FROM Заказы

ORDER BY [Дата заказа] DESC;

Запрос с вычислением «Фамилия и инициалы» позволяет на основании данных поля Ф.И.О., содержащего информацию вида Иванов Иван Иванович, сформировать выражение в виде Иванов И.И.

Запросы с фразой GROUP BY. GROUP BY — образуются группы строк, имеющих одно и то же значение в указанном столбце. «Сумма к оплате» — позволяет отобразить долг всех клиентов. Все имена полей, приведенные в списке предложения SELECT, должны присутствовать и во фразе GROUP BY — за исключением случаев, когда имя столбца используется в итоговой функции. Выборка выполняется на основе SQL-запроса. Текст запроса в режиме SQL:

SELECT Клиенты. Фамилия, Sum (Заказы. [Сумма к оплате]) AS [Sum-Сумма к оплате]

FROM Товары INNER JOIN (Клиенты INNER JOIN Заказы ON Клиенты. Код = Заказы. Клиент) ON Товары. Код = Заказы. Товар

GROUP BY Клиенты. Фамилия

ORDER BY Клиенты. Фамилия;

Запросы с фразой HAVING. HAVING — фильтруются группы строк объекта в соответствии с указанным условием. «Сумма к оплате>100» — отображает фамилии тех клиентов, кто должен заплатить больше100 €. Выборка выполняется на основе SQL-запроса. Текст запроса в режиме SQL:

SELECT Клиенты. Фамилия, Sum (Заказы. [Сумма к оплате]) AS [Sum-Сумма к оплате]

FROM Товары INNER JOIN (Клиенты INNER JOIN Заказы ON Клиенты. Код=Заказы. Клиент) ON Товары. Код=Заказы. Товар

GROUP BY Клиенты. Фамилия

HAVING Sum (Заказы. [Сумма к оплате])>100;

Запрос с подзапросом. Подзапрос — инструмент создания временной таблицы, содержимое которой извлекается и обрабатывается внешним оператором. Текст подзапроса должен быть заключен в скобки. «Название товара» — позволяет отобразить книгу, тираж которой максимальный. Выборка выполняется на основе SQL-запроса. Текст запроса в режиме SQL:

SELECT Товары. Наименование_товара AS Выражение1, Товары. [Количество на складе]

FROM Товары

WHERE (((Товары. [Количество на складе])=(SELECT MAX ([Количество на складе])

FROM Товары)));

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

Текст запроса в режиме SQL:

UPDATE Товары SET Товары. [Стоимость доставки] = [Товары]! [Стоимость доставки]*(1+[Введите процент увеличения]/100)

WHERE (((Товары. [Наименование товара])=[Введитете наименование товара]));

Обновить поле соответствующими значениями. Обновление поля выполняется на основе SQL-запроса. Текст запроса в режиме SQL:

PARAMETERS [Старая_фамилия] Text (20), [Новая_фамилия] Text (20);

UPDATE Клиенты AS 1 SET Фамилия = [Новая_фамилия]

WHERE (Фамилия=[Старая_фамилия]);

Перекрестные запросы позволяют формировать результат выборки в виде сводной таблицы, где слово PIVOT определяет подписи столбцов, а GROUP BY — определяет подписи строк при выборке и группировании агрегированных данных статистической функцией в слове TRANSFORM.

«Стоимость каждого товара» — позволяет отобразить информацию о цене каждого заказанного товара для каждого клиента.

Создание перекрестного запроса выполняется на основе SQL-запроса. Текст запроса в режиме SQL:

TRANSFORM Min (Товары. [Стоимость единицы]) AS [Min-Стоимость единицы]

SELECT Заказы. [Клиент], Sum (Заказы. [Сумма к оплате]) AS [Итоговое значение Сумма к оплате]

FROM Товары INNER JOIN Заказы ON Товары. Код = Заказы. Товар

GROUP BY Заказы. [Клиент]

ORDER BY Заказы. [Клиент]

PIVOT Заказы. [Товар];

Запрос действия на создание таблицы. Команда CREATE TABLE — создание таблицы. «Создание таблицы» — позволяет создать таблицу с интересующими полями. Создание запроса-действия на создание таблицы выполняется на основе SQL-запроса. Текст запроса в режиме SQL:

CREATE TABLE Супер_Товары (Название_Товара TEXT (20), Стоимость CURRENCY);

Запрос на удаление таблицы. Команда DROP TABLE — удаление таблицы: DROP TABLE имя_таблицы [RESTRICT | CASCADE].

Запрос позволяет удалить таблицу Супер_товары. Удаление таблицы выполняется на основе SQL-запроса. Текст запроса в режиме SQL:

DROP TABLE Супер_Товары;

4.3 Проектирование форм в MS Access

Постановка задачи и ее математическая формулировка

Спроектировать следующие виды форм:

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

Кнопочную форму, содержащую информацию по различным срезам индивидуальной БД (с использованием Мастера для разработки кнопок и процедуры обработки событий). Добавить в кнопочную форму рисунок, надписи, изменить фон, шрифт по своему усмотрению. Привести описание форм, перечислив кнопки и соответствующие им действия.

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

Описание входной и выходной информации

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

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