49874 (Анализ доходов отдела фирмы, занимающейся розничной торговлей офисной мебелью)

МИНИСТЕРСТВО ОБЩЕГО ОБРАЗОВАНИЯ РФ ПЕНЗЕНСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ

КАФЕДРА «КОММУНИКАЦИОННЫЙ МЕНЕДЖМЕНТ»

Пояснительная записка

к курсовому проектированию по курсу «Информатика»

тема:

«Анализ доходов отдела фирмы, занимающейся розничной торговлей офисной мебелью»

Автор: студент III курса гр. 08юр1

Специальность: связи с общественностью, 030602 Беляков Д.А.

Руководитель: Аксенова Л.И.

Пенза

2010г.

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

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

Общие сведения о Microsoft Excel

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 предлагает операцию подведения промежуточных итогов. Только после выполнения сортировки данных таблицы можно использовать команду Итоги из меню Данные, чтобы представить различную итоговую информацию. Эта команда добавляет строки промежуточных итогов для каждой группы элементов списка, при этом можно использовать различные функции для вычисления итогов на уровне группы. Кроме того, команда Итоги создает общие итоги.

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

•Установили курсор в любую ячейку таблицы с данными.

•Выбрали пункт меню Данные-Итоги.

•В окне При каждом изменении в выбрали «Наименование» или «Дата продажи».