Составление и анализ отчета о продажах средствами MS Exel.

ОГЛАВЛЕНИЕ

ВВЕДЕНИЕ
1.ТЕОРЕТИЧЕСКАЯ ЧАСТЬ
1.1 Проверка вводимых данных
1.2 Логические функции
1.3 Фильтрация данных
1.4 Копирование данных из Excel в Word
1.4.1 Вставка статических данных
1.4.2 Вставка со связью
1.4.3 Внедрение объектов на рабочий лист Excel
1.4.4 Внедрение на рабочий лист Excel документов Word
1.4.5 Внедрение других типов документов
1.4.6 Способы внедрения объектов Excel в документ Word
1.5 Создание промежуточных и общих итогов
1.6 Работа со сводными таблицами
1.7 Диаграммы в Excel 2007
1.7.1 Объекты диаграмм в Excel 2007 и их свойства
1.7.2 Редактирование диаграмм
1.7.3 Форматирование диаграмм
2.ПРАКТИЧЕСКАЯ ЧАСТЬ
2.1 Создание именованных блоков
2.2 Создание таблицы с вводом данных в столбцы отдел, должность и пол с помощью списков
2.3 Расчет новых окладов, увеличив их на 25% всем, кроме тех, кто подал заявление об увольнении
2.4 Выбор из таблицы сотрудников, увольняющихся до 15.12.2005, с помощью расширенного фильтра
2.5 Создание уведомления о дате увольнении средствами Word
2.6 Подсчет количества мужчин и женщин, работающих в каждом отделе
2.7 Создание сводной таблицы, в которой нужно определить по каждой должности количество женщин и мужчин
2.8 Построение гистограммы с накоплением по результатам, полученным на листе итоги
ЗАКЛЮЧЕНИЕ
ЛИТЕРАТУРА

Составление и анализ отчета о продажах средствами MS Exel.

При формировании критерия отбора расширенного фильтра возможны следующие варианты.1. Критерий отбора содержит одно или несколько условий, накладываемых на один столбец (одно поле). Если критерий содержит несколько условий, то они связываются логической операцией Или.2. Критерий отбора содержит несколько условий, накладываемых на несколько столбцов (полей) одновременно. Здесь возможны следующие варианты:а) необходимо наложить несколько условий отбора на несколько столбцов, причем эти условия должны связываться логической операцией ИЛИ. Тогда условия отбора задаются в разных строках критерия; б) необходимо одновременно наложить несколько условий отбора на несколько полей, причем условия отбора должны быть связаны логической операцией и. Тогда все условия задаются в одной строке критерия; в)необходимо несколько условий наложить на несколько полей, причем связываться они могут обеими логическими операциями И и ИЛИ.3. Вычисляемый критерий. Условия отбора могут содержать формулу. Полученное в результате вычисления формулы значение будет участвовать в сравнении. Правила формирования вычисляемого критерия следующие:в диапазоне критерия нельзя указывать имена полей. Следует ввести новое имя заголовка или оставить ячейку пустой;при создании формул вычисляемых критериев следует использовать первую строку списка (не строку заголовков), т. е. первую ячейку в сравниваемом столбце;если в формуле используются ссылки на ячейки списка, они задаются как относительные;если в формуле используются ссылки на ячейки вне списка, они задаются как абсолютные;вычисляемые критерии можно сочетать с невычисляемыми;не следует обращать внимание на результат, выдаваемый формулой в области критерия (обычно ИСТИНА или ЛОЖЬ).1.4 Копирование данных из Excel в WordЧаще всего для работы используется комбинация двух программ: электронной таблицы и текстового процессора. В этом разделе описываются типы связей, которые можно создать в программе Microsoft Word при копировании в нее данных Excel [1]. Данные из Excel в Word можно вставить двумя способами. Как статическую информацию. Если данные в Excel изменятся, то это никак не отразится на скопированных данных в Word. Как связь. Если данные в Excel изменятся, то эти изменения отразятся на скопированных данных в Word. Различные варианты вставки в Word предлагает диалоговое окно Специальная вставка, открытое в Word с помощью команды Главная - Буфер обмена – Вставить - Специальная вставка. Если в Word для вставки использовать стандартную команду вставки Главная - Буфер обмена - Вставить, способ вставки будет зависеть от того, что вы копируете и вставляете. Например, диапазон ячеек Excel будет вставлен как статическая таблица, тогда как диаграмма Excel будет вставлена как связь. 1.4.1 Вставка статических данныхДалеко не всегда требуется устанавливать связь при копировании данных. Например, если нужно подготовить отчет в текстовом процессоре и необходимо вставить таблицу данных из рабочего листа Excel, то нет никакой необходимости устанавливать связь. Чтобы вставить диапазон ячеек Excel в Word как статические данные, нужно выделить и скопировать в Excel этот диапазон ячеек, перейдите в Word и выбрать команду Главная - Буфер обмена - Вставить (или нажмите комбинацию клавиш <Ctrl+V>). Скопированный диапазон будет вставлен в Word как таблица без установки связи с рабочей книгой Excel. Для управления процессом вставки используется диалоговое окно Специальная вставка. На Рисунке 6 показано это окно, когда в Excel скопирован диапазон ячеек. После выбора одной из опций вставки скопированные данные будут вставлены в документ Word без создания связи (если вы не установили переключатель Связать).Рисунок 6 Диалоговое окно Специальная вставка Word при копировании в Excel диапазона ячеекДиалоговое окно Специальная вставка Word предлагает следующие варианты вставки скопированного диапазона ячеек. Двоичный лист Microsoft Office Excel (объект). Вставленный диапазон ячеек можно редактировать так же, как в Excel. После двойного щелчка на вставленном диапазоне лента команд Word будет заменена лентой команд Excel. Текст в формате RTF. Диапазон ячеек вставляется как таблица с сохранением некоторых атрибутов форматирования. Неформатированный текст. Вставляется необработанная информация без всякого форматирования, при этом содержимое ячеек отделяется друг от друга табуляцией. Рисунок (метафайл Windows). Диапазон ячеек вставляется как рисунок. Точечный рисунок. Диапазон ячеек вставляется как рисунок. Метафайл Windows (EMF). Диапазон ячеек вставляется как рисунок. Формат HTML. Диапазон ячеек вставляется как таблица с сохранением всех атрибутов форматирования. Текст в кодировке Юникод. Вставляется необработанная информация без всякого форматирования, при этом содержимое ячеек отделяется друг от друга табуляцией. Если скопирована в Excel диаграмма, то диалоговое окно Специальная вставка Word предлагает другие варианты вставки этой диаграммы. Варианты, предлагаемые для вставки скопированной диаграммы, следующие. Диаграмма Microsoft Olfice Excel (объект). Вставленную диаграмму можно редактировать так же, как в Excel. После двойного щелчка на вставленной диаграмме лента команд Word будет заменена лентой команд Excel. Рисунок (метафайл Windows). Диаграмма вставляется как рисунок. Точечный рисунок. Диаграмма вставляется как растровое изображение. Метафайл Windows (EMF). Диаграмма вставляется как рисунок. Рисунок (GIF). Диаграмма вставляется как изображение в формате GIF. Рисунок (PNG). Диаграмма вставляется как изображение в формате PNG. Рисунок (JРЕG). Диаграмма вставляется как изображение в формате JPEG. Графический объект Microsoft Office. Вставленная диаграмма сохраняет связь с диапазоном Excel, ее можно редактировать в Word. 1.4.2 Вставка со связьюЕсли копируемые данные подлежат изменению, то лучше установить связь с документом-источником. Тогда, если данные вставлены в приложение с использованием опции Связать диалогового окна Специальная вставка, при внесении изменений в документ-источник они появятся в документе-приемнике (возможно, с задержкой в несколько секунд). Лучший способ проверить, так ли это на самом деле, — вывести оба приложения на экран, внести изменения в документ-источник и наблюдать за тем, как они появляются в документе-приемнике.Связь с документом-источником можно легко разорвать случайно. Например, связь разорвется, если вы переместили документ-источник в другую папку или переименовали его. В таких случаях необходимо восстановить связь вручную. На Рисунке 7 показано диалоговое окно Специальная вставка Word, когда в Excel скопирован диапазон ячеек и установлен переключатель Связать. Отметим, что все варианты вставки, за исключением варианта Метафайл Windows (EMF), доступны при создании связи.Рисунок 7 Установка связи в диалоговом окне Специальная вставка Word при копировании в Excel диапазона ячеекДля установки связи с Excel при копировании диаграммы также установите в диалоговом окне Специальная вставка Word переключатель Связать. Если вы вставляете со связью в Word диаграмму Excel в варианте Графический объект Microsoft Office, то установится связь только с данными, на основе которых создана диаграмма. Все изменения, внесенные в документе-источнике и не связанные с данными (такие, как форматирование диаграммы или изменение ее типа), на диаграмме, вставленной в Word, не отразятся. Однако, выделив в Word диаграмму, вы можете отформатировать ее самостоятельно с помощью команд вкладки Работа с диаграммами.Для редактирования (или удаления) связи выполните команду Office - Подготовить - Изменить ссылки на файлы, чтобы открыть диалоговое окно Связи. Здесь, предварительно выбрав в списке исходный документ, можно изменить документ-источник, щелкнув на кнопке Сменить источник, или разорвать связь, щелкнув на кнопке Разорвать связь. После разрыва связи данные остаются в документе-источнике, но связи с документом-источником больше не имеют.1.4.3 Внедрение объектов на рабочий лист ExcelМетод связывание и внедрение объектов, или Object Linking and Embedding (OLE), позволяет вставлять объекты из других программ и, если необходимо, редактировать их с помощью средств этих программ. В качестве объекта OLE можно выбрать один из перечисленных ниже элементов. Текстовые документы, созданные в других программах, например в текстовом процессоре. Рисунки и векторные изображения, созданные в других программах. Информация, взятая из специального служебного OLE-приложения, такого как Microsoft Equation. Звуковые файлы. Файлы, содержащие видеоизображение и анимацию. Большинство (но не все) наиболее важных приложений Windows поддерживают технологию OLE. Техника внедрения документов часто используется, когда документы должны быть переданы другим пользователям. Если создан один документ, то отпадает необходимость в пересылке нескольких файлов. Более того, этот способ позволяет избежать проблем, которые могут возникнуть из-за разрыва связей. Для внедрения объекта в рабочую книгу Excel выберите команду Вставка – Текст – Вставить объект, чтобы открыть диалоговое окно Вставка объекта. Это окно имеет две вкладки: первая предназначена для создания новых объектов, вторая — для создания объектов на основе существующих файлов.1.4.4 Внедрение на рабочий лист Excel документов WordЧтобы внедрить пустой документ Word, находясь в окне Excel, выберите команду Вставка – Текст - Вставить объект. В открывшемся диалоговом окне Вставка объекта щелкните на вкладке Новый и из списка Тип объекта выберите Документ Microsoft Office Word. В результате на рабочем листе Excel будет создан чистый документ Word, в который можно непосредственно вводить текст. Обратите внимание на то, что лента команд Excel заменена лентой команд Word, обеспечивая доступ ко всем средствам Word. Можно изменить размер документа, при этом слова будут автоматически перенесены на новые строки. Для внедрения копии существующего документа Word в диалоговом окне Вставка объекта перейдите на вкладку Из файла, щелкните на кнопке Обзор и на жестком диске найдите нужный файл. Документ Word будет вставлен на рабочий лист. Двойной щелчок на этом документе отобразит ленту команд Word.1.4.5 Внедрение других типов документовМожно внедрять множество различных типов объектов, включая аудио- и видеоклипы, MIDI-фрагменты и даже целые презентации из Microsoft PowerPoint. Щелчок на этом объекте приведет к воспроизведению песни на плейере МРЗ, установленном по умолчанию. В пакет Microsoft Office включено несколько приложений, которые могут быть полезны. Например, в Excel можно внедрить объект Microsoft Equation, в котором в привычном виде привести формулы, используемые на рабочем листе. Некоторые типы объектов, приведенные в диалоговом окне Вставка объекта, при вставке на рабочий лист Excel могут оказаться очень удобными и полезными. 1.4.6 Способы внедрения объектов Excel в документ WordОбъекты Excel (диапазоны ячеек, диаграммы или целые книги) в документ Word можно вставить тремя способами. Скопировать объект Excel и вставить его в Word с помощью диалогового окна Специальная вставка. Открыть существующий файл Excel в Word с помощью диалогового окна Вставка объекта. Создать новую рабочую книгу Excel непосредственно в Word с помощью диалогового окна Вставка объекта.1.5 Создание промежуточных и общих итоговПри анализе данных в больших таблицах часто возникает необходимость вычислять промежуточные и полные итоги [3].Промежуточные итоги – это удобный способ обобщения и анализа данных на рабочем листе. При подведении промежуточных итогов таблица разбивается на несколько групп строк с одинаковыми значениями одного поля и по каждой группе подводятся итоги, а затем – общий итог по всей таблице. В качестве итога могут вычисляться максимальное или минимальное значения в группе, сумма, среднее, количество элементов, стандартное отклонение и т. п. Подведение промежуточных итогов позволяет обойтись практически без ручного ввода формул.  Excel автоматически создает формулу, добавляет строку (или строки) для записи промежуточных итогов и подставляет адреса ячеек данных. Результирующая таблица является наглядной и удобной для последующей обработки. После подведения промежуточных итогов таблица представляется структурой, то есть она разбивается на группы. Разбиение таблицы на группы позволяет управлять степенью детализации отображаемых данных. Excel может показывать только итог для каждой группы или полностью содержимое группы. Перед подведением промежуточных итогов имеет смысл произвести сортировку по тем столбцам, по которым подводятся итоги, чтобы все записи с одинаковыми полями этих столбцов попали в одну группу.Чтобы добавить итоги для списка данных нужно:Выделить исходный диапазон ячеек.Выбрать команду Данные – Структура – Промежуточные итоги для вызова соответствующего диалогового окна.В списке При каждом изменении в выбрать поле, по значениям которого нужно группировать строки (каждый раз, когда такое изменение найдено, вставляется промежуточный итог). В списке Операция выбрать функцию, при помощи которой будут подводиться итоги.В группе флажков Добавить итоги по к указать, по каким полям должны вычисляться итоги. Отметить только те поля, которые нужно просуммировать.Excel вставит в рабочий лист строки с промежуточными итогами. Общий итог по всей таблице появится в ее нижней части. Размещение строк промежуточных итогов регулируется с помощью флажков в нижней части диалогового окна. По умолчанию все старые промежуточные итоги должны заменяться новыми (флажок Заменить текущие итоги), также можно разделить группы итогов на страницы (флажок Конец страницы между группами) и добавить общие итоги под таблицей данных (флажок Итоги под данными).Чтобы удалить итоги с листа, нужно вызвать окно добавления промежуточных итогов и нажать кнопку Убрать все.1.6 Работа со сводными таблицамиТакой инструмент, как Сводная таблица позволяет мгновенно представить информацию из списка Excel или из внешней базы данных в виде динамичной структуры с итоговыми значениями [4]. Отчеты в виде сводных таблиц (или просто сводные таблицы) позволяют вам, комбинируя значения из разных полей, выполнять не менее чем двумя способами перекрестное регулирование данных или получать итоговые данные на основе существующих записей. Можно, например, проанализировать объемы продаж в подразделении каждого штата, определить объемы продаж каждого менеджера, а затем на основе полученной информации составить рейтинг продавцов. Эта "сложная" задача выполняется путем изменения расположения полей в отчете и применения средства фильтрации. Сводные таблицы динамичны потому, что их использование позволяет мгновенно менять внешний вид отчета, а также создавать ссылки на исходные данные, чтобы при изменении данных таблица обновлялась автоматически. Для импорта значений данных и создания сводной таблицы можно использовать любую базу данных, доступную в Excel. Сводные таблицы вполне подходят для больших списков, поскольку их можно быстро фильтровать (чтобы представить на рассмотрение пользователей только определенные значения), а также быстро группировать данные (чтобы скрыть или отобразить необходимые разделы списка). Для создания сводной таблицы перенесите поля (в основном, заголовки данных) в заранее определенные области ее структуры, Поле в большинстве случаев выполняет роль фильтра данных; их ценность заключается в том, что для каждого поля можно указать те значения, какие потребуется отобразить на сводной таблице. Каждое поле имеет собственную команду Все, используемую для отображения всех возможных значений поля. Например, если эта команда выбрана для поля Месяцы, вставленного в область строк, то в сводной таблице будут отображены значения для каждого месяца. Если же следует представить только определенные значения, то можно указать их по отдельности в полях, расположенных в области строки и в области столбцов (для краткости эти поля еще называют, соответственно, полями строк и полями столбцов). Поле в области страницы (или поле страницы) позволяет выбирать только одно значение. Именно ему должны соответствовать все те записи данных, которые отображаются в текущий момент на листе. Создав сводную таблицу можно использовать средство группировки Excel с целью объединить записи в одну структурную единицу или продемонстрировать соотношение данных. Ограничения, накладываемые на сводные таблицы: В списке каждый столбец должен иметь имя, поскольку в сводной таблице эти имена становятся именами полей. Так как в сводной таблице итоговые значения создаются на основе имеющихся полки, из списка необходимо удалить все автоматически рассчитываемые суммы. При создании сводной таблицы используется весь список, даже скрытые ячейки. Если нет необходимости в том, чтобы эти ячейки попадали в сводную таблицу, список следует отфильтровать на другой лист, используя команду Расширенный фильтр. В Excel можно создать сводную таблицу, содержащую максимум 8000 итоговых элементов. В области страницы может поместиться только 256 полей, в области данных — также только 256 полей. Другие области ограничены разве что общим размером сводной таблицы. 1.7 Диаграммы в Excel 2007Если электронная таблица содержит большое количество числовых данных, то проанализировать их (сравнить, оценить их изменение с течением времени, установить соотношение между ними и др.) достаточно трудно. Провести анализ большого количества числовых данных значительно легче, если эти данные изобразить графически. Для графического изображения числовых данных используют диаграммы [6].Диаграмма (греч. – чертеж) – это графическое изображение, в котором числовые данные представляются в виде геометрических фигур. Диаграммы в Excel 2007 строятся по данным, представленным в электронной таблице.В Excel 2007 можно построить диаграммы 11 типов. Каждый из этих типов диаграмм имеет несколько видов. Их можно просмотреть, а также выбрать один из них, открыв списки соответствующих кнопок на вкладке Вставка в группе Диаграммы или открыв окно Вставка диаграммы (Рисунок 8) выбором кнопки открытия диалогового окна этой же группы.Рисунок 8 Окно Вставка диаграммыИз всех типов диаграмм чаще всего используют гистограммы, графики, круговые, линейчатые и точечные диаграммы.К типу диаграмм Гистограмма относятся такие виды:гистограмма с группировкой, гистограмма с накоплением, нормированная гистограмма с накоплением;объемные гистограммы;цилиндрические диаграммы;конические диаграммы;пирамидальные диаграммы.Гистограммы целесообразно создавать тогда, когда нужно сравнить значения нескольких наборов данных, графически изобразить отличия значений одних наборов данных по сравнению с другими, показать изменения данных с течением времени.В гистограмме с группировкой прямоугольники, которые являются графическими изображениями числовых данных из разных наборов, располагаются рядом друг с другом, а в гистограмме с накоплением – друг на друге. Это дает возможность в гистограмме с накоплением оценить суммарные данные и вклад каждой составляющей в общую сумму. В нормированной гистограмме с накоплением вертикальная ось имеет шкалу в процентах. Это дает возможность оценить процентную часть данных в общей сумме.К типу диаграмм Линейчатая относятся такие виды:линейчатая с группировкой, линейчатая с накоплением, нормированная линейчатая с накоплением; объемные линейчатые;горизонтальные цилиндрические;горизонтальные конические;горизонтальные пирамидальные.Линейчатые диаграммы аналогичны гистограммам и отличаются от них лишь горизонтальным расположением геометрических фигур.Линейчатые диаграммы удобно использовать, если горизонтальное расположение геометрических фигур выглядит нагляднее, чем вертикальное.Например, если вдоль горизонтальной оси откладываются возрастающие промежутки времени или температуры, если на диаграмме нужно изобразить много элементов данных, если подписи лучше воспринимаются в горизонтальном положении и др.К типу диаграмм Круговая относятся плоские и объемные круговые диаграммы.Круговые диаграммы целесообразно использовать тогда, когда нужно отобразить части одного целого, сравнить соотношение частей и частей к целому.