ПРОГРАММНЫЕ_СРЕДСТВА_EXCEL

30.Сдайте работу преподавателю.

Практическая работа №7 СОЗДАНИЕ СТРУКТУРЫ ДОКУМЕНТА. РАСЧЕТ ИТОГОВ

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

Структурирование.

1.Загрузите программу Excel 2007.

2.Откройте файл электронной таблицы Начисление зарплаты.xlsx (Начисление зарплаты за январь 2007г.).

3.Добавьте после колонки Фамилия И.О. графу Подразделение и заполните ее данными (Бухгалтерия, Отдел статистики, Отдел маркетинга).

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

выделите тело таблицы (диапазон ячеек А4:J15) без заголовков и итоговых значений;

выполните команду меню Данные/Сортировка и фильтр/ , укажите последовательность и порядок сортировки диапазона: Сортировать по – Столбец С, Затем по – Столбец А, в диалоге Параметры сортировки укажите режим Сортировать строки диапазона, <ОК>.

5.Выполните автоматическое структурирование таблицы:

выделите всю таблицу и выполните команду Данные///Создание структуры;

с помощью кнопок «+» и «-» научитесь сворачивать и разворачивать созданную структуру таблицы до разных уровней.

6.Удалите структуру командой Данные/ / /Удалить структуру.

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

выделите все ячейки таблицы и выполните команду Данные//;

в диалоге Промежуточные итоги в поле При каждом изменении в –

укажите Столбец С (колонка Подразделение), в поле Операция - укажите Сумма, в поле Добавить итоги по – отметьте все столбцы кроме А, В и С

(Таб.Номер, Фамилия И.О., Подразделение);

установите режимы Заменить текущие итоги и Итоги по данным; <ОК>.

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

9. Добавьте на панель быстрого доступа кнопки из категории Данные: Скрыть детали, Отобразить детали, Разгруппировать, Группировать; из категории Все команды: Символы структуры документа.

10.Выделяя отдельные блоки таблицы (строки или колонки) и используя кнопки Группировать и Разгруппировать на панели быстрого доступа разработайте структуру таблицы, показанную на рисунке.

11.Сохраните таблицу. Сдайте работу преподавателю.

Практическая работа №8 РАБОТА СО СВЯЗАННЫМИ ТАБЛИЦАМИ

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

1.Загрузите программу Excel 2007.

2.Разработайте 3 таблицы, содержащие сведения о поквартальных объемах продаж продукции предприятия за три года по филиалам. Выполните следующие действия:

на Листе1 новой рабочей книги сформируйте таблицу

Поквартальные объемы продаж в

2004 году по образцу, представленному на рисунке, для

Филиала 1;

заполните произвольными числовыми значениями (от 10 до 100)

колонки 1 кв, 2 кв, 3 кв, 4 кв;

используя функцию суммирования,

рассчитайте значения в колонке Всего за 2004г и строке Итого:; на все ячейки с числовыми данными установите числовой формат с одним

десятичным знаком; сформируйте аналогичную таблицу Филиал 2 путем копирования таблицы

Филиал 1 в блок ячеек A16:F28, измените название Филиал 1 на Филиал

2;

используя диалог Найти и заменить внесите изменения в числовые данные колонок 1 кв, 2 кв, 3 кв, 4 кв в таблице Филиал 2 (обратите внимание на автоматическое изменение значений в ячейках с формулами);

на Листе2 текущей рабочей книги сформируйте похожую таблицу

Поквартальные объемы продаж в 2005 году для Филиала 1 и Филиала 2 с

помощью копирования исходных таблиц с Листа1, измените числовые данные колонок 1 кв, 2 кв, 3 кв, 4 кв, а также значение года в заголовках;

на Листе3 текущей рабочей книги сформируйте еще одну таблицу

Поквартальные объемы продаж в 2006 году для Филиала 1 и Филиала 2 с

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

3.Сохраните рабочую книгу под именем Продажи.xlsx.

4.Добавьте в рабочую книгу дополнительный Лист4.

5.На Листе4 разработайте итоговую таблицу Общий объем продаж

продукции за период 2004-2006г

(см.рисунок). Выполните следующие действия:

в ячейку В1 введите заголовок таблицы;

в ячейках В3:Е3 сформируйте шапку таблицы; значения колонки Продукция скопируйте из любой таблицы с Листа1(2,3)

(рекомендуется использовать режим Специальная вставка – без рамки); в ячейке С4 колонки Филиал 1 и рассчитайте суммарное значение объема

продаж по первому филиалу за три года по формуле

=Лист1!F4+Лист2!F4+Лист3!F4

для этого можно использовать полуавтоматический режим ввода формулы: в ячейку С4 ввести с клавиатуры знак = , перейти на Лист1 и щелкнуть по ячейке F4 (годовое значение для Товара1 по первому филиалу),ввести знак +, перейти на Лист2, щелкнуть на ячейке F4 (годовое значение для Товара1 по первому филиалу), ввести знак +, перейти на Лист3, щелкнуть на ячейке F4 (годовое значение для Товара1 по первому филиалу), Enter – программа автоматически возвращает курсор на Лист4;

для расчета остальных значений в колонке Филиал 1 скопируйте формулу из ячейки С4 на нижележащие ячейки;

по аналогии в ячейке D4 колонки Филиал 2 рассчитайте суммарное

значение объема продаж по второму филиалу за три года по формуле

=Лист1!F18+Лист2!F18+Лист3!F18,

скопируйте формулу на нижележащие ячейки; используя функцию суммирования, рассчитайте значения в колонке Всего

и строке Итого:

Внимание! Подобным образом можно установить связь между таблицами, находящимися в разных файлах рабочих книг. В этом случае в формулах в ссылку на ячейку добавляется имя файла (заключается в [ ]), например: =[Книга1.xls]Лист1!$F$4+[Книга2.xls]Лист1!$F$4, где адрес ячейки является абсолютным. При копировании такой формулы предварительно абсолютные ссылки нужно заменить на относительные. Для этого можно использовать режим F4 - трехкратное нажатие клавиши F4 меняет абсолютную ссылку на ячейку на относительную.

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

6.Сохраните рабочую книгу.

7.Сдайте работу преподавателю.

Практическая работа № 9 ПОСТРОЕНИЕ И РЕДАКТИРОВАНИЕ ДИАГРАММ

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

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

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

Отчет сводной диаграммы представляет собой интерактивную сводку данных в формате диаграммы. Его создание отличается от обычных диаграмм Excel 2007. После создания отчета сводной диаграммы можно просматривать разные уровни детализации (подробностей) и изменять макет диаграммы, перетаскивая еѐ поля и элементы.

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

С помощью средства построения диаграмм на вкладке Вставка/ Иллюстрации/

можно создать организационную диаграмму, иллюстрирующую отношения подчиненности, например, между руководителями отделов и сотрудниками предприятия.

Диаграмма состоит обычно из следующих элементов: координатные оси X и Y (осей Y может быть две), ряды данных, заголовок диаграммы, заголовки осей,

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

Задача 1.

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

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

1.Таблицу заполните следующим образом:

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

нижеприведенными формулами:

Доход = Цена_товара*Реализовано

Себестоимость=Постоянные_издержки+Переменные_издержки*Реализовано

Прибыль = Доход–Себестоимость

2.Рабочему листу с таблицей дайте имя Пример.

3.Сохраните созданную таблицу в Моих документах под именем Dohod.xlsx.

4. Построение диаграммы:

для удобства построения установите масштаб просмотра 75%;

выделите диапазон ячеек A7:D8 (по данным, содержащимся в этих ячейках, должна быть построена диаграмма);

щелкните по вкладке Вставка/Диаграммы/

;

в проявившемся окне щелкните значок

Объемная круговая;

автоматически на рабочем листе появляется круговая диаграмма.

Для добавления поля Название диаграммы

выделите созданную диаграмму и перейдите на вкладку Макет/Подписи/ . В

списке команд выберите: Название по центру с перекрытием или Над диаграммой. Задание основных характеристик области названия диаграммы

происходит с помощью вкладки Макет/Подписи/ /Дополнительные

параметры заголовков.

Для добавления легенды в диаграмму перейдите к вкладке Макет/Подписи/ . В списке команда выберите расположение легенды в области диаграммы

– сверху, справа, слева и т.д. Для области легенды можно менять все основные характеристики – цвет заливки, границы, объем, тень и т.д. В этом случае перейдите на вкладку Макет/Подписи/ /Дополнительные параметры легенды. В окне Формат легенды на вкладках Параметры легенды, Заливка, Цвет границы,

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

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

кнопке . С помощью вкладки Конструктор/Расположение/ можно указать место размещения диаграммы – на имеющемся листе или на отдельном

листе Диаграмма 1.

Вкладка Конструктор/Стили диаграмм изменяет стиль созданной диаграммы, заливку и контур фигуры и позволяет применить различные эффекты. Для форматирования отдельных элементов диаграммы можно воспользоваться вкладкой Формат/Текущий фрагмент, предварительно выбрав объект форматирования

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

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

5. Сохраните таблицу с диаграммой.

Впредыдущем примере перед построением

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

Задача 2.

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

1.Построение диаграммы:

для удобства построения установите масштаб просмотра 75%.

щелкните по вкладке Вставка/Диаграммы/ ; в проявившемся окне щелкните значок График с маркерами;

на вкладке Конструктор в группе Данные щелкните по кнопке ;

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

=A7:D8;

щелкните по кнопке <Ок>, после чего в выделенной области появится диаграмма.

2. Сохраните таблицу с диаграммами.

Задача 3.

Построить диаграмму, отражающую объем реализации по месяцам на отдельном листе, тип диаграммы – гистограмма.

1.Построение диаграммы:

выделите диапазон ячеек A7:D8 (по данным, содержащимся в этих ячейках, должна быть построена диаграмма);

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

Гистограмма.

2.Сохраните таблицу с диаграммами.

Настройка параметров для предварительно выделенной диаграммы выполняется с помощью вкладок Конструктор (изменение вида диаграммы), Макет (изменение объектов диаграммы), Формат (изменение оформления элементов диаграммы).

Задача 4.

Для диаграммы на листе Гистограмма внести следующие изменения:

изменить тип диаграммы на линейчатую,

изменить названия диаграммы Объем реализации за первый квартал,

добавить подписи к осям Y (тыс. руб.) и X (месяцы квартала),

добавить подписи на диаграмму к столбцу данных за Февраль,

отформатировать текст диаграммы - название диаграммы оформить шрифтом вида Times New Roman, стиль – жирный, размер 14 пунктов, текст обвести рамкой;

провести форматирование оси Y: установить минимальное значение – 100, максимальное значение – 500, цена основных делений – 100, промежуточных – 20;

легенду разместить в правом верхнем углу области диаграммы, заключить в рамку с тенью;

добавить на диаграмму полную сетку из пунктирных линий.

 столбцы диаграммы оформить различной текстурой.

1.Измените тип диаграммы:

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

вкладку Конструктор/Тип/ . В окне Изменение типа диаграммы в

левой части выберите тип диаграммы (Линейчатая), а в правой уже саму диаграмму для замены.

для того чтобы задать пропорциональный размер диаграммы перейдите к вкладке Формат/Размер/ . В окне Размер и свойства на вкладке Размер

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

2.Измените названия диаграммы:

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

повторите щелчок еще раз, контурная линия исчезнет, появится мигающий курсор ввода текста;

удалив старый текст Реализовано, введите новый текст Объем реализации за первый квартал;

после внесения изменений либо нажмите клавишу ESC, либо щелкните мышью вне поля названия;

3.Добавьте названия к осям диаграммы Y - тыс. руб., Х – Месяцы квартала;

оси диаграммы – это две (X и Y), а иногда и три (дополнительно Z) прямые линии под прямым углом друг к другу, относительно меток которых и строится диаграмма;

выберите вкладку Макет/Подписи/ /Название основной горизонтальной оси/Название под осью;

выделите название горизонтальной оси диаграммы, после щелчка мышью вокруг текста появится контурная линия с маркерами;

повторите щелчок еще раз, контурная линия исчезнет, появится мигающий курсор ввода текста;

введите текст Месяцы квартала;

нажмите клавишу ESC, либо щелкните мышью вне поля названия;

для задания основных параметров шкалы горизонтальной оси диаграммы

перейдите к вкладке Макет/Оси/ /Основная горизонтальная

ось/Дополнительные параметры основной горизонтальной оси.

аналогично добавьте название к оси Y.

4.Добавьте подпись Февраль к столбцу диаграммы с данными за февраль:

щелкните на одном из столбцов диаграммы, при этом на каждом из столбцов появиться квадратик – признак выделения всех столбцов;

щелкните на столбце Февраль, он выделится маркерами;

выберите вкладку Макет/Подписи/ /Дополнительные

параметры подписей данных; в окне Формат подписей данных на вкладке Параметры подписи

установите опцию Значение;

щелкните по клавише <Закрыть> или нажмите клавишу ENTER;

снимите выделение, нажав клавишу ESC, либо щелкнув мышью вне столбца диаграммы Февраль.

5.Оформите текст названия диаграммы:

на панели инструментов форматирования, которая появляется при щелчке правой кнопкой мыши на названии диаграммы, установите нужные значения шрифтового оформления;

сделайте двойной щелчок мышью на названии диаграммы или выделите название диаграммы и выполните команду Формат заголовка диаграммы из контекстного меню;

на вкладке Стили границ установите стиль, цвет и толщину рамки самостоятельно; вкладка Заливка позволяет задать цвет фона, на котором будет выводиться название диаграммы.

6.Проведите форматирование оси Y:

выделите ось Y щелчком мышью;

в контекстном меню выберите команду Формат оси;

на вкладке Параметры оси установите Подписи оси – внизу, основные – наружу, промежуточные – внутрь; минимальное значение – 100, максимальное значение – 500, цена основных делений – 100, промежуточных – 20, цена деления – Сотни, флажок Отображать на диаграмме должен быть включен, <Закрыть >;

проанализируйте изменения, произошедшие на диаграмме. 7. Измените местоположение легенды, заключите ее в рамку:

выделите легенду и выполните команду Формат легенды из контекстного меню; на вкладке Параметры легенды установите режим - сверху;

на вкладках Цвет границы, Стили границ и Тень установите параметры по своему усмотрению, <Закрыть >;

снимите выделение, нажав клавишу ESC, либо щелкнув мышью вне легенды.

8. Добавьте на диаграмму полную сетку из пунктирных линий:

Примечание. По умолчанию мастер диаграмм размещает на диаграмме сетку из горизонтальных линий.

двойным щелчком активизируйте область диаграммы;

перейдите на вкладку Макет/Сетка/ /Горизонтальные линии сетки

по основной оси/Дополнительные параметры линий сетки на основной горизонтальной оси;

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

9. Наложите различную текстуру на столбцы диаграммы:

щелкните на одном из столбцов диаграммы, при этом на каждом из столбцов появиться квадратик – признак выделения всех столбцов; щелкните на столбце Январь и откройте диалог Формат точки данных; на вкладке Заливка установите флажок Рисунок или текстура и щелкните

по кнопке , выберите тип текстуры, <Закрыть>; оцените результат; аналогично оформите столбцы Февраль и Март.

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

Построение линий тренда. Тренд – это функция заданного вида, с помощью которой можно аппроксимировать построенный по данным таблицы график. Тренд служит для определения направления развития процесса, представленного на диаграмме, и обеспечивает прогноз на заданный период. В Excel существует несколько стандартных видов тренда: линейный, логарифмический, степенной, экспоненциальный, полиномиальный, линейная фильтрация. Линии тренда можно построить для диаграмм следующих типов: гистограмма, линейчатая диаграмма, график, диаграмма с областями, точечная диаграмма. Построение линии тренда выполняется в диалоге Линия тренда (команда Добавить линию тренда

контекстного меню выделенного ряда данных). Форматирование линий тренда выполняется в диалоге Формат линии тренда (двойной щелчок мышью на линии тренда).

Задача 5.

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

1.Откройте книгу Dohod.xlsx, перейдите на лист с таблицей.

2.По данным таблицы постройте гистограмму:

выделите блок ячеек A9:D11;

щелкните по вкладке Вставка/Диаграммы/ ; в проявившемся окне щелкните значок Гистограмма с группировкой;

самостоятельно задайте заголовок диаграммы Результаты деятельности предприятия и осей X – Период, Y – Сумма (руб.);

самостоятельно настройте легенду и линии сетки, задайте отображение