MS Excel и MS Access

       Автоструктурирование работает, когда электронная таблица содержит 
формулы.      Выполнили при      помощи      Данные      -      Группировать.

Автоструктурирование выполнено по строкам - «Наименование товара» до значения «Итог», а структурная часть по столбцам выполнена для «Дата поступления товара» до «Даты его реализации», для «Партии товара» и его «Цены», для столбцов от «Фамилии реализатора» и до «Товара, имеющий минимальный срок реализации». Проведя, автосруктурирование по строкам и по столбцам упрощен просмотр столбца «Стоимости товара» и его итог, а так же «Фамилии реализатора работавшего по доверенности». Автоструктурирование отражено в таблице 7.

       Выполнены промежуточные итоги в три  уровня: 1-ый уровень итогов -средняя цена по каждому товару, 2-ой уровень - средняя цена по каждой партии товара по каждому товару, 3-ий уровень - средняя цена по количеству товара полученного на реализацию по каждой партии товара по каждому товару.

       Перед подведением итогов отсортировали  данные, по которым будет производиться  их подсчет (все значения по возрастанию). Отсортировали при помощи Данные - Сортировка. Сортировать по: установили - товар; порядок - по возрастанию. Затем добавили уровень и отсортировали аналогично «Партию товара» и «Количество товара, полученного на реализацию». При помощи команды Данные - Структура - Промежуточные итоги создаем промежуточные итоги по столбцам «Товар», «Партия товара», «Количеству товара, полученного найти реализацию». В появившемся окне при каждом изменении в - «Товар», операция - среднее, добавить итоги по - «Цена». Снова Данные - Структура -Промежуточные итоги, т.е. создаем второй уровень. Далее при каждом изменении в - «Партия товара», операция - среднее, добавить итоги по - «Цена». Создаем третий уровень: Данные - Структура - Промежуточные итоги. При каждом изменении в - «Количество товара полученного на реализацию», операция -среднее, добавить итоги по - «Цена». Результат представлен в таблице 8. 

1.4 Консолидация и сводные таблицы

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

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

       В работе произвели консолидирование данных по расположению. Исходную таблицу сократили до трех столбцов: «Товар», «Партия товара», «Количество товара полученного на реализацию». Скопировали полученную таблицу и увеличили «Партию товара» на 100 штук. Установили курсор в ячейку 12,   выполнили   команду Данные  -  Работа  с   данными -  Консолидация,   в

Ю

 

появившемся окне Функция - Сумма, установили флажки - подписи левой строки, значение левого столбца. В поле ссылка - исходный диапазон данных, добавить. Затем поле ссылка - блок ячеек с измененной партией товара и <ОК>. Результат отражен в таблице 9.

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

       Сводные таблицы выполняются при помощи команды Вставка - Сводные таблицы (Список полей сводной таблицы указан на рис. 1). В появившемся окне выбираем поля для добавления в отчет: «Товар», «Партия товара», «Цена» и его «Стоимость». В названии столбцов «Партия товара» и его «Стоимость», их значения. В строках- «Товар». По каждому «Товару» подвели итоги по «Партии товара» и его «Стоимости»: среднее и максимальное значение цены для каждого товара.

1.5 Диаграмма и документ «Слияние»

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

1. внедренные диаграммы — сохраняются на рабочем листе вместе с данными;
2. диаграммные листы — диаграмма в формате полного экрана на новом листе.

       Диаграмма создана на основе итоговых данных подведенных по каждому товару, т.е. просчитали сумму всем полям, кроме «Дата поступления товара» и «Дата реализации товара». Выделили диапазон итоговых данных по" каждому «Товару», затем меню Вставка - Диаграмма - Гистограмма. Полученная диаграмма отражает зависимость количество товара полученного на реализацию и количество товара нереализованного.

Результат отражен  в таблице 12.

       В Excel возможно производить слияние с документом Word. Для того чтобы выполнить слияние необходимо таблице присвоить имя в нашем случае «Оптоваяторговля». В файле Word печатаем Справку, которая указана в задании. Затем воспользуемся меню - Рассылки - Начать слияние - Пошаговый мастер слияния. В появившемся окне: выбор типа документа - каталог, далее открытие документа - текущий документ, далее выбор получателя - использование списка, в появившемся диалоговом окне выбрали документ Excel, лист «Слияние». Выделяем поле слияния (в данном случае «Фамилия реализатора») в документе Word на вкладке Рассылки выбираем: «Вставить поле слияния» - фамилия. Аналогично вставляем поля слияния «Товар», «Дата продажи», «Количество товара», «Дата поступления». Выделяем ячейку в поле «Доверенность» - есть, затем вкладка Рассылки - Изменить список получателей, Фильтр - Отбор записей по полю «Доверенность» - Значение - есть. Получается, что справка будет отправлена реализаторам, работавшим по доверенности.

2 Работа системы управления базами данных MS Access

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

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

1. таблица - это объект, соответствующий понятию «таблица» в теории реляционных баз данных. Для каждой таблицы в Access можно определить первичный ключ и один или несколько индексов с целью увеличения скорости доступа к данным;
2. запрос - объект, содержащий текст SQL запроса, имеющий уникальное имя в определенной базе данных. Создать запрос можно с помощью мастера и в режиме конструктора. В первом случае в интерактивном режиме предлагается выбрать имя таблицы и поля для выборки. Во втором случае можно выбрать несколько таблиц или запросов, связать их графическим способом и определить поля выборки;
3. форма - это специальный объект-контейнер для других интерфейсных компонентов, таких как поля ввода и отображения данных, кнопки и др. На форме располагают компоненты для ввода, корректировки, просмотра и группировки данных, в зависимости от специфики приложения. Форму также можно создать двумя способами - в режиме конструктора и с помощью мастера. В первом случае набором

13

 

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

4. отчёт - объект, предназначенный для создания документа, который впоследствии может быть распечатан либо включён в документ другого приложения. Этот документ содержит результаты выборки из базы данных в виде структурированной информации (например в виде таблицы или списка). MS Access обладает богатыми возможностями по оформлению и форматированию отчетов. Те же два режима создания есть и у отчета. В режиме мастера имеется возможность задать множество параметров, практически полностью определяющих желаемый внешний вид отчета. Для создания нестандартных отчетов лучше пользоваться конструктором;
5. макрос - это объект, представляющий собой последовательность макрокоманд для автоматизации наиболее часто выполняемых действий при работе с базой. Макрокоманды выбираются из имеющегося списка, а параметры задаются разработчиком. Выполнить макрос можно по нажатию на кнопку и программным методом в коде;
6. модуль - контейнер программного кода на VBA. Для их редактирования и просмотра используется оболочка Редактора Visual Basic. Весь программный код приложения содержится в наборе модулей.

2.1 Создание базы данных

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

14

 

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

       Через вкладку Главная - Вид - Режим таблиц переходим в режим Таблицы и заполняем её согласно исходным данным. Результат представлен в таблице 13.

2.2 Типы запросов

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

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

3. запросы на изменение:

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

б) на добавление новых записей в таблицу;

15

 

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

г) на изменение значений каких-либо полей в отобранных записях 
таблицы.

4. запросы с параметром - это такие запросы, в которых критерии отбора задаются пользователем при каждом запуске запроса. То есть запрос с параметрами можно получить из любого трех предыдущих типов запросов, введя в него параметры для отбора записей. При запуске такого запроса на экран выводится окно, в которое следует ввести значение параметра выборки, для каждого из параметров;
5. SQL - запросы - это запросы, которые составляются (программистами) из последовательности SQL - инструкций. Эти инструкции задают, что надо сделать с входным набором данных для генерации выходного набора. Все запросы Access строит на основе SQL - запросов, чтобы посмотреть их, необходимо в активном окне проектирования запроса выполнить команду Вид/SQL.

2.3 Формирование запроса на выборку

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

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

       В окне базы данных во вкладке Создание в группе Другие - Мастер запросов. В появившемся окне Новый запрос выбрали Простой запрос далее кнопка <ОК>, в появившемся окне в строке Таблицы/запросы выбрали таблица «Оптоваяторговля». В окне «Доступные поля» выбираем поля: «Товар», «Фамилия реализатора», «Количество товара», «Полученного на реализацию», «Дата реализации» и переводим эти поля в окно «Выбранные поля», затем «Далее». В строке параметра «Задайте имя» ввели «Реализация товара» и «Готово». Результат запроса на выборку представлен в таблице 14.