MS Excel и MS Access

Оглавление

Введение 3

1 Применение приложения MS Excel как базы данных 4

1. Подсчет данных по формулам 4
2. Сортировка и фильтрация 6
3. Структурирование и итоги 8
4. Консолидация и сводные таблицы 10
5. Диаграмма и документ «Слияние» 12

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

1. Создание базы данных 14
2. Типы запросов 15
3. Формирование запроса на выборку 16
4. Запрос на выборку с параметром 17
5. Отчет с группированием 17
6. Кнопочная форма 18

Заключение 20

Библиографический список 21

 

Введение

       В представленной работе представлено практическое применение приложения Office: MS Excel и MS Access.

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

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

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

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
3

 

1 Применение приложения MS Excel как базы данных 1.1 Подсчет данных по формулам

       По  исходным данным указанным в задании  составлена таблица «Больница» (таблица 1). Произведен расчет вычисляемых полей по формулам.

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

       При помощи кнопки вставить функцию возле  строки формул можно выбрать необходимую  для подсчета. Для подсчета поля «Цена» была использована логическая функция ЕСЛИ, которая проверяет, выполняется ли условие, и возвращает одно значение, если оно выполняется, и другое значение, если нет. Функция ЕСЛИ содержит 3 аргумента: логвыражение (любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ), Значение_если_истина (значение, которое возвращает если лог-выражение имеет значение ИСТИНА. Если не указано возвращается значение ИСТИНА), Значениееслиложь (значение, которое возвращается, если логвыражение имеет значение ЛОЖЬ. Если не указано возвращает значение ЛОЖЬ). Первый аргумент является проверяемым условием, другие два значения, записываемые в ячейку в случае выполнения или невыполнения условия. По условию задачи получилось, что Цена = ЕСЛИ(С4=Т00;100;С4-10%), т.е. если «Партия товара» равна 100, то значение, если истина - 100 (равна «Партии товара»), если же ложь, то - 10% от «Партии товара».

       Для расчета полей «Фамилия реализатора с максимальным количеством товара» и «Товар, имеющий минимальный срок реализации» были использованы вложенные функции.

4

 

       Столбец «Фамилия реализатора с максимальным количеством товара» заполняется с помощью вложенной функции МАКС в ЕСЛИ. Функция МАКС возвращает наибольшее из списка аргументов: число 1;число 2 и т.д. от 1 до 255 чисел, пустых ячеек, логических или текстовых значений, среди которых ищется максимальное. Было задано логическое условие, что если «Количество товара полученного на реализацию» наибольшее из всего списка, то вывести «Фамилию реализатора», если же нет, то оставить ячейку пустой. В итоге получилось выражение: =ECJIH(H4=MAKC($H$4:$H$23);F4;"").

       Знак $ у значения столбца и ячейки - абсолютная ссылка. Она необходима для того, чтобы при копировании формул адреса ячеек не менялись. Аналогично для поля «Товар, имеющий минимальный срок реализации» =ЕСЛИ(М4=МИН($М$4:$М$23);В4;""). Функция МИН возвращает минимальное значение из списка аргументов: число 1;число 2 и т.д. от 1 до 255 чисел, пустых ячеек, логических или текстовых значений, среди которых ищется минимальное значение.

       В столбце «Фамилия реализатора, работающего по доверенности» необходимо было задать логическое условие что, если у реализатора есть доверенность вывести его фамилию, если нет оставить пустую ячейку. При вычислении использована функция ЕСЛИ, получилось =EGnH(G4="ecTb";F4;"").

       Для столбца «Стоимость товара» применили  функцию ПРОИЗВЕД, которая возвращает произведение аргументов: =ПРОИЗВЕД(С4:Ш). Вычисляется как произведение «Цены» и «Партии товара». Аргументы: число 1;число 2 и т.д. от 1 до 255 чисел перемножаемых чисел, логических значений или чисел, представленных в текстовом виде.

       Количество  возвращенного товара как разность «Партии товара» и «Количества товара полученного на реализацию», =СУММ(С4;-Н4). Рассчитали при помощи функции СУММ, которая суммирует аргументы их количество возможно от 1 до 255. Логические и текстовые значения игнорируются.

       «Количество нереализованного товара», это есть количество возвращенного товара, поэтому формула для вычисления имеет вид: =14.

5

 

       «Продолжительность  нахождения товара до реализации» вычисляем  при помощи функции ДНЕЙ 360 между  «Датой реализации» и «Датой поступления»: =ДНЕЙ360(К4;Ь4). Данная функция вычисляет количество дней между двумя датами на основе 360-дневного года (двенадцать месяцев по 30 дней). Начальная дата («Дата поступления товара») и конечная дата («Дата реализации»). Начальная и конечная дата - это даты, между которыми необходимо определить количество дней.

       В таблице, для значения реального  времени поле текущая дата была использована функция СЕГОДНЯ, которая не имеет  аргумента и возвращает текущую  дату.

       Таблица в режиме формул (Формулы - Зависимости  формул- - значок: Показать формулы) отображена на таблице 2.

1.2 Сортировка и фильтрация

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

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

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

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

• расширенный фильтр для более сложных условий отбора.

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

       Автофильтр выполнен при помощи команды Данные - Фильтр. Условия отбора: «Доверенность» - «есть», «Партия товара» для значения 500 и «Продолжительность нахождения товара до реализации» - 84 дня. Результат автофильтрации показан в таблице 4.

       Расширенный фильтр выполнен по критерию сравнения  и по вычисляемому критерию.

       Для расширенного фильтра по критерию сравнения  формируем условие отбора. Копировали имена полей списка в другую область на том же листе, установив курсор в ячейке А26 - это область где формируется условие отбора. Блок ячеек А26:Р26 - имена полей области критерия, А27:Р27 - область значения критерия. В область значения критерия ввели: по полю «Партия товара» больше 500, «Доверенность» есть (вводим «е»), «Продолжительность нахождения товара до реализации» больше 80. Условие отбора сформировано. Далее в меню Данные

- Сортировка и фильтр - Дополнительно, в появившемся окне: Обработка - 
Скопировать результат в другое место, исходный диапазон - $А$3:$Р$23, 
диапазон условий - $А$26:$Р$27, поместить результат в диапазон - $А$29 и 
кнопка <ОК>. Сформировали в области условия отбора критерий сравнения — о 
товаре превышающем партию 500 штук, находящемся по доверенности у 
реализатора больше 80 дней.

       Для расширенного фильтра по вычисляемому критерию условие отбора будет следующим: «Партия товара»: =С4>СРЗНАЧ($С$4:$С$23), «Доверенность»

- есть («е»), «Фамилия реализатора с максимальным количеством товара» - 
Рогов. Затем по аналогии с расширенным фильтром по критерию сравнения при 
помощи тех же команд Данные - Сортировка и фильтр в диалоговом окне 
устанавливаем: Обработка - Скопировать результат в другое место, исходный диапазон - $А$3:$Р$23, диапазон условий - $А$41:$Р$42, поместить результат в диапазон - $А$44 и кнопка <ОК>. Сформируем в области условия отбора вычисляемый критерий — для реализатора Рогова товар, у которого находится по доверенности и партия его составляет больше среднего.

       Результат расширенного фильтра по критерию сравнения  и вычисляемому критерию представлен в таблице 5.

1.3 Структурирование и итоги

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

       Структурирование  выполнено в три уровня. Структура  таблицы создается со следующими элементами на служебном поле:

1. линии уровней структуры;
2. знаки «+» и «-»;
3. кнопки с номерами уровней.

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

Структура - Группировать - Столбцы. Результат  структурирования представлен в  таблице 6.