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

Размещено на http://www.allbest.ru/

Междисциплинарная курсовая работа

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

Содержание

Введение

Глава 1. Используемые возможности MS Excel

Глава 2. Практическая реализация

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

2.2 Задача 1

2.3 Задача 2

2.4 Задача 3

2.5 Задача 4

2.6 Задача 5

2.7 Задача 6

2.8 Задача 7

Глава 3. Защита документа

3.1 Задачи

3.2 Выполнение работы

3.2.1 Задача 1

3.2.2 Задача 2

3.2.3 Задача 3

3.2.4 Задача 4

3.2.5 Задача 5

Выводы

Литература

коммунальный предприятие цена excel



Введение

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

Учет расходов ведется как по трате самих ресурсов, так и по затраченным средствам. Коммунальными предприятиями, присутствующих в подсчетах, являются МосГаз, МосЭнерго, МГТС и МосТепло. В зависимости от формы собственности предприятия (частное или государственное) и льготной категории (есть или нет), цены на ресурсы варьируются.

Задачи

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

2. Доказать совпадение суммарных расходов предприятий с итоговыми подсчетами

3. Реализовать удобный подсчет итоговых сумм

4. Рассчитать плату при увеличении цен

5. Реализовать удобное отображение предприятий по потреблению электроэнергии и воды

6. Реализовать удобный просмотр таблицы и добавление новых записей

7. Создать сводную таблицу, построить диаграмму для наглядности.



Глава 1. Используемые возможности MS Excel

Функции

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

Основные категории:

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

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

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

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

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

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

· Текстовые функции предоставляют пользователю возможность обработки текста.

· Логические функции предназначены для проверки одного или нескольких условий.

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

Рис. 1. Окно "Мастер функций".

Подробнее о некоторых функциях:

· Функция ЕСЛИ. Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ. Функция ЕСЛИ используется при проверке условий для значений и формул.

Синтаксис

ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)

· Функция МАКС. Возвращает наибольшее значение из набора значений.

Синтаксис. МАКС(число1;число2; …).Число1, число2,… -- от 1 до 255 чисел, среди которых требуется найти наибольшее.

· Функция МИН. Возвращает наименьшее значение в списке аргументов. Синтаксис. МИН(число1;число2; …).Число1, число2,… -- от 1 до 255 чисел, среди которых требуется найти наименьшее.

· Функция СРЗНАЧ. Вычисляет среднее арифметическое для значений заданных в списке аргументов.

Cинтаксис СРЗНАЧ (число1;число2; …). Число1, число2,… -- от 1 до 255 чисел, для которых вычисляется среднее.

· Функция ИНДЕКС. Возвращает значение или ссылку на значение из таблицы или диапазона. Функция ИНДЕКС имеет две синтаксические формы: ссылка и массив.

Сортировка

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

Варианты сортировки:

· по тексту (от А к Я или от Я к А),

· по числам (от наименьших к наибольшим или от наибольших к наименьшим),

· по датам и времени (от старых к новым или от новых к старым),

· по настраиваемым спискам (таким как состоящий из элементов "Большой", "Средний" и "Маленький"),

· по формату, включая цвет ячеек, цвет шрифта,

· по значкам.

Большинство сортировок применяются к столбцам, но возможно также применить сортировку к строкам.

Простая сортировка по полю

Для обычной сортировки базы данных по одному полю ( по возрастанию или по убыванию) необходимо воспользоваться вкладкой Данные, Сортировка (выбрав пиктограммы от А к Я или от Я к А соответственно).

Сортировка по нескольким полям

Для проведения более сложной сортировки (по нескольким полям) во вкладке Данные, Сортировка откроем окно диалога "Сортировка" (Рис.2). Чтобы сортировка выполнялась по двум или трем полям в окне дополнительно добавляем уровни необходимое количество раз. Далее выбираем столбец, по которому будет осуществляться сортировка, вариант сортировки и ее порядок.



Рис. 2. Окно "Сортировка".

Фильтрация

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

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

Варианты фильтрации:

· автофильтр для простых условий отбора,

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

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

Автофильтр

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

Расширенный фильтр

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

Подключаем его, перейдя во вкладку Данные, Сортировка и фильтр, Дополнительно (рис. 3).

Рис. 3. Окно "Расширенный фильтр"

Промежуточные итоги

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

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

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



Рис. 4. Окно "Промежуточные итоги"

Сводная таблица

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

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

Для добавления диаграммы необходимо перейти во вкладку Вставка, команда Сводная таблица. И в появившемся окне выбрать параметры создания таблицы (рис. 6).

Рис. 6. Окно "Создание сводной таблицы"



Макросы

Если требуется периодическое выполнение задачи в Microsoft Excel, можно автоматизировать задачу с помощью макроса. Макрос -- это последовательность команд и функций, хранящаяся в модуле Visual Basic. Ее можно выполнять всякий раз, когда необходимо выполнить данную задачу.

Запись макросов. При записи макроса Microsoft Excel сохраняет информацию о каждом шаге выполнения последовательности команд. Последующий запуск макроса вызывает повторение ("воспроизведение") команд. Если при записи макроса была допущена ошибка, сделанные исправления также будут записаны. Visual Basic хранит каждый записанный макрос в отдельном модуле, присоединенном к книге.

Упрощение запуска макроса. Макрос можно запустить, выбрав его из списка в диалоговом окне Макрос. Чтобы макрос запускался после каждого нажатия определенной кнопки или сочетания клавиш, можно назначить макросу кнопку на панели инструментов, сочетание клавиш или графический объект на листе.

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

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

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

Перед работой с макросами убедитесь, что

1. в параметрах Excel, Основные стоит галочка на показ вкладки Разработчик на ленте,

2. в параметрах Excel, Центр управления безопасностью, Параметры центра управления безопасностью, Параметры макросов стоит галочка на включение всех макросов.

Диаграммы

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

Виды диаграмм:

1. Гистограммы. Используются для демонстрации изменений данных за определенный период времени или для иллюстрирования сравнения объектов.

2. Графики. Позволяют изображать непрерывное изменение данных с течением времени в едином масштабе; таким образом, они идеально подходят для изображения трендов изменения данных с равными интервалами.

3. Круговые диаграммы. Демонстрируют размер элементов одного ряда данных пропорционально сумме элементов. Точки данных на круговой диаграмме выводятся в виде процентов от всего круга.

4. Линейчатые диаграммы. Иллюстрируют сравнение отдельных элементов.

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

6. Точечные диаграммы. Показывают отношения между численными значениями в нескольких рядах данных или отображает две группы чисел как один ряд координат x и y.

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

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

9. Кольцевые диаграммы. Отображают отношение частей к целому, но может содержать более одного ряда данных (в отличие от круговых).

10. Пузырьковые диаграммы. Могут отображаться данные столбцов электронной таблицы, при этом значения по оси X выбираются из первого столбца, а соответствующие значения по оси Y и значения, определяющие размер пузырьков, выбираются из соседних столбцов.

11. Лепестковые диаграммы. Можно сравнить статистические значения нескольких рядов данных.

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



Глава 2. Практическая реализация

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

Исходными данными являлась таблица с частью организаций с приведенными данными по расходам коммунальных услуг, а также цены на коммунальные услуги. Значения данных для остальных предприятий были добавлены с помощью функции СЛЧИС, выводящей случайное значение от 0 до 1 с умножением и сдвигом через сложение (для того, чтобы случайное число выдавалось в желаемом диапазоне, например: диапазон значений на электроэнергию колеблется от 1500 до 6500). После чего все данные были скопированы и вставлены обратно с помощью Специальной вставки (для того, чтобы убрать из ячеек формулы, которые вычислялись заново при каждом изменении в таблице). Минусом этой операции являются дробные части случайно сгенерированных чисел, в связи с чем есть незначительные расхождения в вычислениях.

2.2 Задача 1

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

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

2.3 Задача 2

Постановка задачи: Доказать совпадение суммарных расходов предприятий с итоговыми подсчетами.

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



2.4 Задача 3

Постановка задачи: Реализовать удобный подсчет итоговых сумм.

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

2.5 Задача 4

Постановка задачи: Рассчитать плату при увеличении цен. Подвести промежуточные итоги.

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



2.6 Задача 5

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

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



2.7 Задача 6

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

Решение: Шапка была закреплена при помощи функции "Закрепить области" в разделе "Вид". Добавление новых записей реализовано через кнопку с макросом, использующим встроенную функцию MS Excel. Макрос вызывает эту функцию, через которую можно удобно добавлять записи в таблицу, причем все формулы буду автоматически скопированы и расширены. Для наглядности отображения данных также создана диаграмма по сводной таблице.



2.8 Задача 7

Постановка задачи: Создать сводную таблицу, построить диаграмму для наглядности

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



Глава 3. Защита документа

3.1 Задачи

1. Добавление или удаление цифровой подписи в файлах Office

2. Защита элементов листа и книги

3. Что такое защищенный режим?

4. Включение и отключение параметров ActiveX в файлах Office

5. Почему надстройка завершает работу со сбоем?

3.2 Выполнение работы

3.2.1 Задача 1

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

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

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



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

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

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

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



После добавления такой подписи сам документ будет доступен только для чтения.

3.2.2 Задача 2

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

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

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

Можно скрыть формулы в нужных ячейках от посторонних глаз. Делается это в разделе "Защита" в "Формате ячеек".



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



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

3.2.3 Задача 3

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

Файл может открываться в защищенном режиме по нескольким причинам:

- файл получен из Интернет

- файл является вложением Outlook, а отправитель считается ненадежным (в соответствии с настройками политики Windows)

- файл открыт из небезопасного расположения

- файл заблокирован функцией блокировки файлов

- произошел сбой при проверке файла

- файл был принудительно открыт в режиме защищенного просмотра

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



3.2.4 Задача 4

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

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

При открытии файла с элементами ActiveX появится панель сообщений с кнопкой включения содержимого.

Также элементы ActiveX можно включить в разделе "Сведения", где будет предупреждение системы безопасности.

В среде MS Office можно изменить параметры элементов ActiveX. Делается это в "Параметрах" в "Центре управления безопасностью". Там есть соответствующая опция настроек.



3.2.5 Задача 5

Зачастую в среде MS Office можно увидеть сообщение такого рода: "В Word произошла серьезная ошибка с надстройкой: "исключение предотвращения выполнения данных при запуске надстройки". Эта надстройка несовместима с режимом предотвращения выполнения данных, и ее нужно отключить или обновить до последней версии. Отключить загрузку этой надстройки?"

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

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



Выводы

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

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

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

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

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

Например: использованные фильтры позволяют наглядно отобразить выбранные данные и работать только с ними, сводная таблица с диаграммой позволяют быстро оценить итоги по затратам предприятий без рассмотрения подробностей.

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

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

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



Литература

1. Хомоненко А.Д Учебник для высших учебных заведений "Базы данных", под редакцией профессора, 2011 г.

2. Дейт К.Дж. Введение в системы баз данных. - 8-е издание: Пер. с англ. - М.: Издательский дом "Вильямс", 2005. ISBN 5-8459-0788-8

3. Михайлов М.В., Базы данных: учебно-методический комплекс (для студентов, обучающихся по специальности 080801 "Прикладная информатика (в экономике)", 080800.62 "Прикладная информатика (бакалавр)"). - Горно-Алтайск, РИО ГАГУ, 2010. - 125 с.

4. Михайлов М.В., Методические указания для выполнения курсового проекта по дисциплине "Базы данных" по специальности 080801 "Прикладная информатика (в экономике)", 080801.62 "Прикладная информатика (бакалавр)". - Горно-Алтайск, 2009. - 17 с.

Размещено на Allbest.ru