МИНИСТЕРСТВО ОБЩЕГО ОБРАЗОВАНИЯ РФ ПЕНЗЕНСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ
КАФЕДРА «КОММУНИКАЦИОННЫЙ МЕНЕДЖМЕНТ»
Пояснительная записка
к курсовому проектированию по курсу «Информатика»
тема:
«Анализ доходов отдела фирмы, занимающейся розничной торговлей офисноймебелью»
Автор:студент III курса гр. 08юр1
Специальность:связи с общественностью, 030602 Беляков Д.А.
Руководитель:Аксенова Л.И.
Пенза
2010г.
Внастоящей пояснительной записке содержится решение задачи и анализа доходовотдела фирмы, занимающейся розничной торговлей детскими игрушками с помощьютабличного процессора MS Excel.
Сформированатаблица продаж, ввод данных предусмотрен с помощью формул, выполненасортировка, фильтрация данных в списке, сформированы промежуточные итоги поежемесячной прибыли от продажи всех видов товаров, годовой прибыли от продажикаждого вида товаров., построена диаграмма, показывающая ежемесячную прибыль откаждого вида товаров, и годовую прибыль от продажи каждого вида товаров.
Общиесведения о MicrosoftExcel
Microsoft Excel — ведущая программа обработкиэлектронных таблиц. Первая версия MS Excel появилась в1985 году и обеспечивала только простые арифметические операции в строку или встолбец. В 1993 году вышла пятая версия Excel, ставшая первым приложением Microsoft Office, которое включало язык Visual Basic for Applications (VBA). Начиная с Office 97 фирма Microsoft включаетVBA во все приложения пакета Microsoft Office. В настоящее время MS Excel представляет собой достаточно мощное средстворазработки информационных систем, которое включает как электронные таблицы (сосредствами финансового и статистического анализа, набором стандартныхматематических функций, доступных в компьютерных языках высокого уровня, рядомдополнительных функций, встречающихся только в библиотеках дорогостоящихинженерных подпрограмм), так и средства визуального программирования (Visual Basic for Applications). Электронные таблицы позволяютпроизводить обработку чисел и текста, задавать формулы и функции дляавтоматического выполнения, прогнозировать бюджет на основе сценария,представлять данные в виде диаграмм, публиковать рабочие листы и диаграммы вИнтернете. С помощью VBAможно автоматизировать всю работу, начиная от сбора информации, ее обработки досоздания итоговой документации как для офисного пользования, так и дляразмещения на Web-узле.
Популярностьтабличного процессора MS Excel позволяет предположить, что интереск нему будет расти и дальше. Поэтому рассмотрение тех или иных задач, которыеможно решить с использованием его возможностей, будет расширять областиприменения MS Excel как в учебном процессе, так и для автоматизациимногих расчетов в производственной сфере. В предлагаемой книге на различныхпримерах продемонстрированы широкие возможности MS Excel для решения математических, экономических и другихзадач. MS Excel используется здесь как средство для проведениярасчетов, визуализации их в виде таблиц, графиков, сводных таблиц и др.Представленные в книге примеры и задачи, а также большое количествоиндивидуальных заданий, предназначены для углубленного освоения возможностейэтого табличного процессора. Изложение в первую очередь ориентировано на MS Excel ХР, однако использовать материал книги можно, начинаяс версии MS Excel 97, практически без изменений. Книга состоит из 9глав, каждая из которых посвящена определенной тематике и использованиюсоответствующих возможностей MS Excel. В конце книги имеются триприложения. Материал книги может быть полезен:
• в качестве учебного пособия длястудентов математических и экономических специальностей, изучающих MS Excel в различных курсах информатики, информационныхтехнологий и систем обработки данных;
• преподавателямпри подготовке лекций и проведении практических и лабораторных работ;
• пользователям —для расширения профессиональных возможностей при использовании табличногопроцессора MS Excel.
Авторвыражает благодарность Юрию Эдуардовичу Зайцу за помощь, оказанную приподготовке рукописи к изданию.
Формированиетаблицы продаж в виде списка
Формированиетаблицы продаж в виде списка, это позволяет упростить фильтрацию данных. Спискив MS Excel— это таблицы, строки которых содержат однороднуюинформацию. Строки таблицы называются записями, а столбцы — полями записей.Столбцам присваиваются уникальные имена полей, которые заносятся в первуюстроку списка — строку заголовка. На рабочем листе выделяются следующиеобласти:
• диапазон данных— область, гдехранятся данные списка. Данные, связанные друг с другом, записываются вотдельные строки, каждому столбцу соответствует свое поле списка с уникальнымименем поля;
• диапазонкритериев — область на рабочем листе, в которой задаются критерии для поискаинформации. В диапазоне критериев указываются имена полей и отводится областьдля записи условий отбора;
• диапазон дляизвлечения — область, в которую MS Excel копирует выбранные данные из списка.Этот диапазон должен быть расположен на том же листе, что и список. Если извлеченнуюинформацию необходимо поместить на другой лист, ее копируют в буфер, а затемвставляют в нужное место.
Вводданных в список
Существуютследующие способы ввода данных в список:
• использование формы данных, котораяавтоматически создается после определения заголовка списка с помощью командменю Данные | Форма;
• ввод данных во вставляемые в списокпустые строки — в это случае имя диапазона списка переопределяется автоматически(непосредственно ввод данных);
• использование средства Автоввод и командыВыбрать из списка для ускорения работы;
• использование форм Access и дальнейший перенос данных на лист MS Excel;
• использование мастера шаблонов дляпреобразования рабочего листа MS Excel в форму;
• применение VBA — соответствующая программа будет предоставлять форму илиокно диалога для ввода данных и их последующего помещения в определенные ячейкирабочего листа MS Excel.
В ячейкурозничная цена ввести формулу =С2+С2*$1$2 с указанием ячейки с наценкой.Абсолютная ссылка $1$2 Они используются, когда необходимо сохранение адресовячеек при копировании формулы в другую ячейку. Для обозначения абсолютнойссылки перед номером строки и буквой столбца указывается знак доллара ($). Абсолютныессылки удобны, когда данные зависят от одной ячейки или группы ячеек, напримерпри использовании ячейки, содержащей курс доллара или размер процентной ставки.В ячейку прибыль ввести =E2*D2-C2*D2.
Созданиеформы для ввода информации командой
Данные-Форма
Поиск спомощью формы данных
Формаданных представляет собой средство для поиска и редактирования записей, которыеудовлетворяют простому или множественному критерию сравнения. В форме данныхусловия в критерии должны соответствовать логической операции и. Здесь нельзяиспользовать логическую операцию или и конструировать вычисляемые критерии. Вформе данных при вводе критерия для поиска некоторого фрагмента текста всегдасчитается, что шаблон поиска заканчивается символом *.
Поиск спомощью формы данных производится следующим образом:
• Поместите указатель ячейки в любоеместо внутри списка.
• Выберите команду Данные | Форма,затем нажмите кнопку Критерии
• В открывшемся окне введите критериипоиска в необходимых полях. Для перехода к записи, удовлетворяющей критерию,следует нажать кнопку Далее или Назад.
Выполнениесортировки
Выполнитьсортировку данных с помощью кнопок на Панели инструментов. Сортировкапредназначена для более удобного представления данных. Команда Данные |Сортировка открывает окно, в котором задаются ключи сортировки (столбцы илистроки) и порядок сортировки В MS Excel используется следующий порядоксортировки:
1. Числа (от -со до +00 ).
2. Текст и формулы.
3. Значение ложь.
4. Значение истина.
5. Значения ошибок.
6. Пустые значения.
Прииспользовании сортировки следует иметь в виду следующее:
Ппорядок сортировки данных в MS Excel зависит от национальных настроек MS Windows;
• если необходимоупорядочить числовые величины в алфавитном порядке, нужно перед числовыми величинамиставить апостроф, либо отформатировать числа как текст, либо ввести число какформулу (например, =«345»);
• при сортировкесписков, содержащих формулы, следует помнить, что относительные ссылки вформулах при перемещении записей могут привести к неправильным результатам,поэтому в списках лучше использовать формулы с абсолютными ссылками;
• для возврата кпервоначальному списку следует ввести перед базой дополнительное индексноеполе, содержащее возрастающую с любым шагом числовую последовательность(например, г, 2, з,… .). Тогда, выделив ячейку в столбце индексов и нажавкнопку. По возрастанию, вернемся к первоначальному списку;
• имеетсявозможность отсортировать данные в каком-либо заданном порядке (например, подням недели, месяцам и т. д.). Для этого в диалоговом окне Параметрысортировки, вызываемом командой Данные ( Сортировка | кнопка Параметры, служитполе Порядок сортировки по 1-му ключу (выбирается вариант порядка сортировки).Часто для этого нужно создавать собственный пользовательский список. Чтобывернуться к обычному порядку — выберите в поле Порядок сортировки по 1 -муключу элемент Не производится;
• даты и время должны быть введены всоответствующем формате либо с помощью функций даты или времени, т. к. длясортировки таких данных MS Excel использует внутреннее представлениеэтих величин;
• сортировка по полям, большим трех,осуществляется последовательными сортировками, начиная с ключей самого низшегоуровня;
• MS Excel может сортировать не только строки, но и столбцы.
Выполнениефильтрации данных в списке
Еслинеобходимо просмотреть в списке только часть данных, то можно использовать фильтры,для того чтобы спрятать данные, которые не удовлетворяют заданным вамикритериям. Эти данные не будут удалены, просто на экране они будут невидимы дотех пор, пока вы не отмените данный фильтр. В Excel для фильтрации по простым критериям (до двух условий)используется средство Автофилыпр (меню Данные / пункт Фильтр /Автофильтр), а для фильтрации по более сложным критериям, или если требуетсяскопировать записи в другое место или отобрать данные на основе вычисленногозначения — Расширенный фильтр. Фильтры могут быть использованы толькодля одного списка на листе.
Чтобыотфильтровать список с помощью расширенного фильтра, столбцы списка должныиметь заголовки. На листе также должно быть не менее трех пустых строк вышесписка. Эти строки будут использованы в качестве диапазона условий отбора. Дляиспользования расширенного фильтра необходимо:
•скопироватьиз списка заголовки фильтруемых столбцов;
•вставитьскопированные заголовки столбцов в первой пустой строке диапазона условийотбора;
• ввести в строки под заголовкамитребуемые критерии отбора. Между значениями условий и списком должна находитьсякак минимум одна пустая строка;
• указать ячейку в списке;
• выбрать команду Данные / Фильтр /Расширенный фильтр;
чтобыскопировать отфильтрованные строки в другую область листа, установитепереключатель Обработка в положение Скопировать результаты в другое место,затем в поле Поместить результат в диапазон укажите верхнюю левую ячейкуобласти вставки;
ввести вполе Диапазон критериев ссылку на диапазон условий отбора, включающий заголовкистолбцов.
Вусловия отбора расширенного фильтра может входить несколько условий,накладываемых на один столбец, несколько условий, накладываемых одновременно нанесколько столбцов, а также условия, накладываемые на возвращаемое формулойзначение. Чтобы задать для отдельного столбца три или более условий отбора,введите условия в ячейки, расположенные в смежных строках. Чтобы наложитьусловия отбора на несколько столбцов одновременно, введите условия в ячейки,расположенные в одной строке диапазона условий. Чтобы выбрать строки,удовлетворяющие одному из нескольких условий, наложенных на разные столбцы, введитеусловия в ячейки, расположенные в разных строках диапазона условий. Чтобыналожить сложное условие отбора, введите его составные части в отдельные строкидиапазона условий.
Формированиепромежуточных итогов по ежемесячной прибыли
Сформироватьпромежуточные итоги по ежемесячной прибыли от продажи всех видов товаров игодовой прибыли от продажи каждого вида товаров и разместить их на разныхлистах. Для решения этой проблемы Excel предлагает операцию подведения промежуточных итогов. Только послевыполнения сортировки данных таблицы можно использовать команду Итоги из менюДанные, чтобы представить различную итоговую информацию. Эта команда добавляетстроки промежуточных итогов для каждой группы элементов списка, при этом можноиспользовать различные функции для вычисления итогов на уровне группы. Крометого, команда Итоги создает общие итоги.
КомандаИтоги создает на листе структуру, где каждый уровень содержит одну из групп,для которых подсчитывается промежуточный итог. Вместо того чтобы рассматриватьсотни строк данных, можно закрыть любой из уровней и опустить тем самымненужные детали.
•Установиликурсор в любую ячейку таблицы с данными.
•Выбралипункт меню Данные-Итоги.
•В окнеПри каждом изменении в выбрали «Наименование» или «Дата продажи».
•В окнеОперация выбрали Сумма.
•В окнеДобавить итоги по выбрали Сумма (проверить, чтобы в остальных полях флажкиотсутствовали).
•Включилифлажок Итоги под данными. Если флажок этого параметра установлен, то итогиразмещаются под данными, если сброшен – над ними.
•НажалиОК, сняли выделение с таблицы и просмотреть результат на экране.
По промежуточнымитогам с помощью мастера диаграмм построить гистограммы.
Дляэтого:
•Указатьисходные данные, для чего на рабочем листе выделили в таблице блок ячеек. Настандартной панели инструментов нажать кнопку Мастер диаграмм, при этом наэкране появится диалоговое окно Тип диаграммы.
•Вдиалоговом окне Тип диаграммы на вкладке Стандартные в опции Тип выбратьнеобходимый тип. Нажать кнопку Далее. Тип диаграммы может быть изменен и послеее создания.
•Воткрывшемся диалоговом окне Источник данных диаграммы, на вкладке Диапазонданных убедиться, что в группе Ряды включена опция Столбцах. Данныеавтоматически разделяются на ряды данных и категории. Данные каждого рядавыделены своим цветом. Категории — это метки по оси X, расположенной горизонтально. Обратить внимание, что ввыделенном диапазоне данных заголовки строк таблицы автоматически становятсяметками оси X, а заголовки столбцов — именамирядов данных, которые отражаются в качестве элементов легенды диаграммы.Переключив опцию Строках, просмотреть, как изменится изображение диаграммы,после чего вновь включите опцию Столбцах. В этом же окне можно заново указатьисходный диапазон данных для построения диаграммы, нажав цветную кнопку справаот поля Диапазон, которая позволяет временно убрать диалоговое окно с экрана.Восстанавливается диалоговое окно повторным нажатием этой же кнопки.
•Навкладке Ряд отражаются адреса ячеек таблицы, задающие имя ряда данных, егозначения и подписи меток оси X.Используя вкладку Ряд, можно добавлять новые ряды данных и удалять сизображения диаграммы существующие.
•Просмотретьсодержимое вкладки Ряд и нажали Далее. •В открывшемся диалоговом окне Параметрыдиаграммы на вкладке Заголовки щелкнуть левой кнопкой мыши в поле Названиедиаграммы и ввести с клавиатуры название. В правой части окна можно увидеть,как будет выглядеть диаграмма. Нажать кнопку Далее.
•Воткрывшемся диалоговом окне Размещение диаграммы в группе Поместтьи диаграммуна листе включили опцию Имеющемся и нажали кнопку Готово.
•Врезультате выполненных действий на экране появилась созданная гистограмма,изображение которой может не удовлетворить пользователя, так как при построениидиаграммы при помощи Мастера диаграмм Excel использует собственные форматы и установки. •Дальнейший этап работы сдиаграммой предусматривает настройку ее отображения, что можно осуществлять какпри помощи главного или контекстного меню, так и при помощи панели инструментовДиаграммы, которая появляется на экране одновременно с построенной диаграммойили подключается путем использования команды меню Вид-Панелиинструментов-Диаграммы.
Созданиесвободной таблицы
СредствоСводная таблица представляет собой еще один инструмент организации и подведенияитогов данных. Этот инструмент комбинирует возможности работы со списками.
Своднаятаблица является специальным типом таблицы, которая подытоживает информацию изконкретных полей списка или базы данных. При создании сводной таблицы с помощьюМастера можно задать нужные поля, организацию таблицы (ее макет) и типвыполняемых вычислений Сводные таблицы обеспечивают удобный интерфейс дляанализа данных различной сложности и разного объема, а также возможностьбыстрой перестройки макета сводной таблицы фактически только с помощью мыши безприменения сложного программирования.
Своднаятаблица — это средство для упорядочивания информации. При создании своднойтаблицы пользователь распределяет информацию, указывая, какие элементы и вкаких полях будут содержаться. Для создания сводных таблиц используютсяразличные источники данных. Это могут быть списки и таблицы, расположенные нарабочих листах Excel, либовнешние источники данных (таблицы, созданные с помощью других программ).
Своднаятаблица является многомерной и всегда связана с источником данных. Онапредназначена только для чтения, а изменения можно вносить в исходные таблицы.При этом можно изменять форматирование сводных таблиц, выбирать различныепараметры вычислений. Для большей наглядности на основе сводной таблицы можносоздать диаграмму, которая будет перестраиваться при изменении сводной таблицы.По исходным данным с помощью мастера построить на новом листе сводную таблицу.
•Установитькурсор в любую ячейку с данными исходной таблицы.
•Выбратьпункт меню Данные-Сводная таблица.
•В группесоздать таблицу на основе данных, находящихся установили переключатель в Спискеили базе данных Microsoft Excel.
•В группеВид создаваемого отчета установить переключатель Сводная таблица и нажатькнопку Далее.
•Проверить,что в появившемся окне в опции Диапазон указан весь диапазон ячеек исходнойтаблицы, и нажать Далее.
•В опцииПоместить таблицу в установить переключатель в новый лист и нажать кнопкуМакет.
•Припомощи мыши перетащить необходимые поля, нажали ОК.
•НажатьГотово. Для управления просмотром данных сводной таблицы используются кнопки сострелками, с помощью которых можно выбрать, какие элементы выводить на экран. Спомощью подбора параметра (команда Сервис-Подбор Параметра...) определилиразмер надбавки на товар, чтобы получить желаемую прибыль. Средство Подбор параметрапредназначено для получения заданного значения в целевой ячейке путем подборазначений в ячейке-параметре. С помощью данного средства можно найти аргумент,при котором получается задаваемое решение. При этом ячейка-параметр должнасодержать значение, но не формулу и влиять на результат, который требуетсяполучить.
Списоклитературы:
1 Алейников А.Л… MS Excel 7.0 за 1 час. — М.: Финансы и статистика, 1997.
2. «Компьютерра» Компьютерныйеженедельник №3 за 1997 год.
3. Фролов, А.В Г.В. Фролов. Пакет MS Excel и его возможности. — М.: Диалог-МИФИ
4. Рудикова Л. B.Microsoft Excel для студента, — СПб.: БХВ- Петербург, 2006. -368 с:ил.