Excel. Анализ фирмы оптовой торговли продуктами питания. Обеспечение безопасности

Оглавление

 

Введение……………………..…………………………………………………………....….2

1. Решение задач анализа деятельности фирмы оптовой торговли продуктами питания с использованием MicrosoftExcel……………………………………………………....3

 

1. Основы  для создания и обработки базы данных……………………………….…..….3

 

2. Исходные данные для решения  задач……………………………………….………….3

 

3. Создание базы данных для анализа  деятельности фирмы……………………………4

 

4. Теоретические сведения…………………………………………………….…………..5

 

5. Технология  решения задач анализа деятельности фирмы с помощью MicrosoftExcel………………………………………………………………………..….9

 

Вывод…….………………………………………………………………………………....21

2. Информационная безопасность базы данных фирмы……………………………….21

 

2.1 Безопасность в Microsoft Office 2010…………………………………………………21

2.2 Теоретические основы  для обеспечения безопасности……………………………..22

2.3.Технология решения  задач защиты безопасности деятельности  фирмы с помощью MicrosoftExcel…..………………………………………………………………………….30

Вывод………………………………………………………………………….....................35

Заключение………………………………………………………………………………....36

Список используемой литературы…………………………………………..……………36

 

 

 

 

 

 

 

 

Введение

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

Цель курсовой работы:

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

Краткая справка о Microsoft Excel:

Microsoft Excel 2010 — одна из программ пакета Microsoft Office, представляющая из себя программируемый табличный калькулятор, позволяющий:

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

 

 

1. Решение задач анализа деятельности фирмы оптовой торговли продуктами питания с использованием MicrosoftExcel.

 

1. Основы для создания и обработки базы данных.

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

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

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

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

2. Исходные данные для решения задач

Склад	пшено	сахар	гречка	молоко	творог	ряженка	Стоим.доставки	Стоим."утруски"	Стоимость
склад1	21	17	12	11	20	19
склад2	31	19	14	12	22	17
склад3	23	21	17	15	19	15
склад4	32	21	14	12	21	17
склад5	27	19	12	11	17	14
склад6	29	17	11	9	27	15
склад7	21	15	10	8	24	14
Итого
Мин
Макс

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

 

 

 

 

 

 

Во второй таблице приведено количество упаковок товара:

продукты
товар	Кв.1	Кв.2	Кв.3	Кв.4	Сумма	Мин	Макс	Среднее	Затраты на доставку	Затраты от некупл. товаров	Общий доход
пшено	12	11	14	12
гречка	11	12	11	14
творог	16	14	15	15
сахар	14	12	12	14
молоко	11	10	11	10
ряженка	10	8	7	9
Итого

 

3. Создание базы данных для анализа деятельности фирмы

Довольно часто возникает  необходимость хранить и обрабатывать данные, представленные в виде таблиц. Информация, хранящаяся в таблицах, организована в виде строк и столбцов. Каждая строка таблицы, называемая записью, содержит данные об одном объекте. В  столбце, называемом полем, содержатся сведения о каком-либо свойстве всех объектов, хранящихся в таблице. Таким  образом, база данных представляет собой  совокупность различных записей, обладающих определенными свойствами. В первой строке любой базы данных обязательно  должны быть указаны имена полей. Максимальный размер базы данных в Microsoft Excel определяется возможностями версии Excel.Дозапись строк в базу данных можно осуществлять двумя способами:

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

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

  

 

 

 

1.4 Теоретические  сведения

Макросы

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

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

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

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

Консолидация  данных

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

Существует два основных способа консолидации данных:

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

Использование формул и функций

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

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

1. Формула всегда начинается со знака равенства. Если знак равенства опустить, Excel предположит, что в данную ячейку введен текст, и отобразит в ячейке именно то, что было в нее введено.
2. В формуле можно использовать любые арифметические операторы: сложение (+), вычитание (-), умножение (*), деление (/), вычисление процентов (%) или возведение в степень (^).
3. В любую часть формулы можно включить адрес ячейки. При вычислениях Excel вместо адреса подставит значение, содержащееся в соответствующей ячейке так, словно оно было введено непосредственно в формуле.

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

Абсолютные ссылки

Некоторые ссылки в формулах записываются в абсолютной форме - например, $С$3.

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

Для того, чтобы превратить относительную ссылку в абсолютную, достаточно в режиме редактирования формулы установить курсор непосредственно за ссылкой и нажать клавишу <F4>. 
Впрочем, можно вставить в ссылку знаки доллара и при помощи обычных приемов редактирования. При помощи символа абсолютной адресации можно легко изменять способ адресации ячеек. Например, $B11 обозначает, что при копировании формул будет изменяться только адресация строки ячейки, а при обозначении B$11 - только столбца. Такая адресация называется смешанной.

При вводе формулы в  строке формул, можно быстро перебрать  по кругу относительный, смешанный  и абсолютный адреса. Просто указать на какой-нибудь адрес и нажимать <F4>, чтобы по кругу перебрать все четыре варианта.

Использование имен для абсолютной адресации

Другой способ абсолютной адресации заключается в назначении имен ячейкам и использовании  их в формулах. Например, назначив ячейки B11 имени курс можно ввести следующую формулу «=В16*Курс». При копировании этой формулы будет соблюдаться абсолютная адресация ячейки.

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

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

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

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

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

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

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

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

Обычно проще создать  список с промежуточными итогами, используя в настольном приложении Excel команду Промежуточные итоги в группе Структура на вкладке Данные. Но если такой список уже создан, его можно модифицировать, изменив формулу с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Синтаксис: ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции,ссылка1,[ссылка2],...])

Номер_функции - Число от 1 до 11, которое указывает, какую функцию следует использовать при вычислении итогов внутри списка. Например: 4 –МАКС; 5 – МИН; 9 – СУММ.

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

 

Присвоение имён ячейкам

Чаще гораздо удобнее  работать с ячейками или массивами  ячеек, когда им присвоены имена, особенно если они находятся на разных листах рабочей книги, то бывает легче  от руки вписать имя в формулу, чем выделять их каждый раз на других листах. Теперь вместо выделения ячеек  в формулы можно вводить их имена. Диаграммы Диаграммы используются для  представления рядов числовых данных в графическом виде. Они призваны облегчить восприятие больших объемов  данных и взаимосвязей между различными рядами данных.Чтобы создать диаграмму  в приложении Excel, сначала требуется ввести данные, которые будут использоваться для ее построения, на листе.  Приложение Microsoft Excel поддерживает различные типы диаграмм, позволяя представить данные в наиболее понятном для целевой аудитории виде. При  создании новой диаграммы или  изменении существующей можно выбрать  любой тип или подтип диаграммы. Кроме того, можно создать смешанную диаграмму, используя несколько типов диаграмм. Круговые диаграммы показывают размер элементов в одном ряду данных, пропорционально сумме элементов. Значения данных в круговой диаграмме показываются как доля целого круга. Так как в виде круговой диаграммы можно представить только один ряд данных, данные листа должны быть упорядочены в виде одного столбца или одной строки. Можно также добавить столбец или строку названий категорий, если этот столбец или строка являются первым столбцом или первой строкой выбранного диапазона. Диаграмма состоит из множества  элементов. Некоторые из них отображаются по умолчанию, а другие можно добавлять  при необходимости. Отображение  элементов диаграммы можно изменить путем их перемещения в другое место диаграммы, изменения их размера  или формата. Кроме того, ненужные элементы можно удалить. Поле со списком Представляет собой сочетание  текстового поля и раскрывающегося  списка. Поле со списком компактнее обычного списка, однако для того чтобы  отобразить список элементов, пользователь должен щелкнуть стрелку. Поле со списком  следует использовать, когда требуется  обеспечить возможность ввести в  список или выбрать в нем только один элемент. В этом элементе управления отображается текущее значение, независимо от того, каким образом оно было введено. В свойствах формат объекта, формируется список по диапазону. В поле Связь с ячейкой вводится ссылка на ячейку, в которой хранится выбранный элемент списка. Связанная ячейка возвращает номер выбранного элемента списка. Первый элемент в диапазоне ячеек возвращает значение 1, второй — 2 и так далее. Полоса прокрутки Используется для просмотра  диапазона значений с помощью  кнопок со стрелками для прокрутки  или путем перетаскивания ползунка полосы прокрутки. Можно перемещаться по странице значений (с заранее  заданным интервалом), щелкая область  между ползунком и одной из стрелок для прокрутки. Как правило, пользователь также может вводить текст непосредственно в связанную ячейку или текстовое поле. Полосу прокрутки удобно использовать для ввода или изменения большого диапазона значений, а также в тех случаях, когда точность не важна. Сводные таблицы Сводные таблицы – это  мощный инструмент для анализа данных. Они позволяют представить итоговые значения, вычисленные на основе данных в выбранных столбцах, в удобном  для анализа виде. С помощью  сводных таблиц можно быстро объединять и сравнивать большие объемы информации. Можно менять местами строки и  столбцы для получения различных  итогов по исходным данным, а также  отображать детальные сведения в  нужных областях. Любая сводная таблица  содержит четыре области: область страницы, область столбцов, область строк  и область данных. Каждой из этих областей соответствует область  макета таблицы в диалоговом окне Макет из Мастера сводных таблиц и диаграмм. 1.5 Технология  решения задач анализа деятельности фирмы с помощью Microsoft Excel

Задание №1. Рассчитать стоимость товаров для всех складов без учёта доставки и "утруски".

Для решения данной задачи воспользуемся методом консолидации и автоматизируем расчёт с помощью макроса (Разработчик – Запись макроса):

 

 

 

 

 

 

Используем прием «консолидация по расположению» (Данные – Консолидация):

Запись макроса для отмены предыдущих действий:

Задание №2.Рассчитать сумму, минимальное и максимальное значение  для каждого товара по всем складам, для  доставки и "утруски".

Для решения данной задачи воспользуемся функциями СУММ, МИН, МАКС. Автоматизация расчёта с  помощью макроса:

Запись макроса для отмены предыдущих действий:

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

 

Задание №3. Вычислить итоговую сумму всех товаров на всех складах с учетом доставки и утруски.

Для решения данной задачи воспользуемся функцией СУММ:

Задание №4.Перевести итоговую сумму в иены по курсу 37,1. Для решения данной задачи используем абсолютную адресацию:

Перевести итоговую сумму в белорусские рубли по курсу 12,7. Для решения данной задачи используем прием «имена» (Формулы – Присвоить имя): 

 

Задание №5.Вывести товары, не имеющие «утруски».Для решения данной задачи используем автофильтр (Данные – Фильтр). Условие выборки - столбец«стоимость «утруски»:

 

Результат решения задачи представлен в таблице. Мы вывели все товары, не имеющие «утруски»:

Задание №6.Рассчитать сумму, максимальную стоимость товаров и максимальное количество упаковок на складах за первый и четвертый квартал.

Для решения данной задачи используем функцию промежуточные итоги (ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции,ссылка1,[ссылка2],...])) и после этого применяем автофильтр:

 

Задание №7.Рассчитать стоимостьтоваров по каждому складу. Для решения данной задачи используем промежуточные итоги (Данные – Структура – Промежуточный итог)

Задание №8. Отсортировать значения стоимости товаров по складам по убыванию. Для решения данной задачи используем сортировку (Сортировка - Настраиваемая сортировка):

Результат решения задачи представлен на скриншоте. Мы отсортировали склады по уменьшению стоимости товаров:

Задание №9. Показать отчет за 1 квартал со складов, на которых запасы сахара равны 12 упаковкам или стоимость пшена не меньше 30 тыс.руб.Для решения данной задачи используем расширенный фильтр (Данные – Дополнительно). Результат решения задачи представлен на скриншоте:

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

Результат решения задачи представлен в таблице. Мы получили сумму пшена по каждому кварталу в тысячах рублей (3 столбец) и в упаковках (4 столбец), а также общий итог:

Задание №11.Вывести «Москва, Стремянный пер. д.36 РЭА им.Г.В.Плеханова, имя студента, группа». Для решения данной задачи используем прием «имена»(Формулы – Присвоить имя):

Результат решения задачи представлен в таблице:

Задание №12.Показать процентное соотношение товаров по всем складам и общий доход по каждому продукту. Для решения данной задачи используем круговую диаграмму и гистограмму (Вставка – Диаграмма – Круговая/Гистограмма):

Результат решения задачи представлен на диаграммах:

Задание №13.Вывести связной список «номер склада – стоимость». Для решения данной задачи используеминструмент "поле со списком" (Разработчик – вставка – форма – поле со списком):

Для отображениязначения стоимости всех товаров в выбранном складе, воспользуемся функцией ИНДЕКС (ИНДЕКС(массив, номер_строки, [номер_столбца]).

Результат решения задачи представлен на скриншоте:

Задание №14.Вывести  отдельно от основной таблицы значение «утруски». Для решения данной задачи используеминструмент «полоса прокрутки» (Разработчик – вставка – форма – полоса прокрутки):

Для отображениязначения стоимости с учетом «утруски», воспользуемся функцией ЕСЛИ(ЕСЛИ(лог_выражение;значение_если_истина; значение_если_ложь):

Для отображениязначения«утруски», воспользуемся функцией ИНДЕКС:

Примечание: Если J3 – истина, то прибавляем к стоимости со склада, значение«утруски», иначе получаем только значение стоимости.