Дрига Е.С., Хрусталева Л.С., Буковцева Л.В.

Дрига Е.С., Хрусталева Л.С., Буковцева Л.В.

 

 

Microsoft Excel 2003

Практикум по выполнению индивидуальных

лабораторных заданий для студентов 1-го курса

Всех специальностей всех форм обучения

 

Хабаровск 2006

ББК У.в6

Х12

 

 

Microsoft Excel 2003: практикум по выполнению индивидуальных лабораторных заданий для студентов 1-го курса всех специальностей всех форм обучения / сост. Е. С. Дрига, Л. С. Хрусталева, Л. В. Буковцева. – Хабаровск: РИЦ ХГАЭП, 2006. – 40 с.

 

Рецензент Д.В. Тимошенко, доцент кафедры ДВС ТОГУ, кандидат технических наук

 

Утверждено издательско-библиотечным советом академии в качестве лабораторного практикума

 

 

© Хабаровская государственная академия экономики и права, 2006

Введение

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

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

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

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

Практикум содержит основные понятия и средства для создания документов в MS Excel 2003, позволяет приобрести основные навыки работы при создании таблиц.

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

Библиографический список поможет студентам найти ответы на контрольные вопросы.

 

Создание рабочего листа

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

 

Ввод данных

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

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

Ввод данных завершается нажатием клавиши Enter или кнопки с изображением галочки в строке формул. Для завершения ввода данных можно использовать клавиши управления курсором. В этом случае после ввода можно сместиться в нужном направлении. Отменить ввод данных позволяют клавиша Esc и кнопка с крестиком в строке формул.

Введенные числовые значения автоматически выравниваются по правому краю, а текстовые данные – по левому краю.

 

Ввод формул

Excel распознает в ячейке формулу, если значение в ячейке начинается со знака равенства (=), знака плюс (+) или знака минус (−). Ввод формулы начинается с ввода знака равенства, а затем самой формулы. Активная ячейка и строка формул отображают формулу, так как она введена. Ввод формулы заканчивается нажатием на клавишу Enter; активная ячейка отобразит результат вычисления формулы. Строка формул продолжает показывать саму формулу, когда эта ячейка становится активной.

 

Автозаполнение ячеек

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

Для повторного ввода (копирования) уже существующих данных:

- ввести первый элемент последовательности, выделить его;

-

Активная ячейка

перетащить маркер заполнения в нужном направлении (рис. 1).

Рис. 1. Элемент активной ячейки

Для ввода линейной прогрессии:

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

- выделить обе ячейки и перетащить маркер заполнения.

Для автозаполнения списка данных необходимо:

1. Создать список:

- выбрать команду Сервис, Параметры, Списки;

- в поле Списки выбрать пункт Новый список;

- щелкнуть на кнопке Добавить и создать список, разделяя каждый элемент списка нажатием клавиши Enter.

2. Ввести список:

- ввести первый элемент списка в ячейку;

- выделить эту ячейку;

- перетащить маркер заполнения на нужное количество строк или столбцов.

Контрольные вопросы

1. В каких областях деятельности человека могут использоваться электронные таблицы?

2. Перечислите элементы окна программы Microsoft Excel, их назначение.

3. Как добавить новый рабочий лист?

4. Как переименовать рабочий лист?

5. Как скопировать или переместить рабочий лист?

6. Как закрепить области рабочего листа?

7. Как создать арифметическую прогрессию?

8. Какие основные функции маркера автозаполнения?

9. Как создать список для автозаполнения?

Перемещение и копирование

В Excel содержимое каждой ячейки может быть перемещено или скопировано.

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

Если во время выполнения перемещения удерживать нажатой клавишу Ctrl, выполнится копирование.

 

Вставка ячеек

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

Вставка ячеек также связана с выделением, поскольку количество вставляемых ячеек зависит от количества ячеек в выделенном диапазоне.

 

Удаление ячеек

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

Контрольные вопросы

1. Что входит в понятие редактирования таблицы?

2. Перечислите способы копирования содержимого ячеек?

Форматирование чисел

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

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

Чтобы присвоить числовой формат ячейке или группе ячеек, можно воспользоваться командой Ячейки меню Формат, командой Формат ячеек контекстного меню. В любом случае открывается вкладка Число диалогового окна Формат ячеек. Форматы объединены в категории: Общий, Числовой, Денежный, Финансовый, Дата, Время, Процентный, Дробный, Экспоненциальный, Текстовый, Дополнительный, Все форматы.

После выбора категории принадлежащие к ней форматы отображаются в поле справа.

В Excel существует более удобный способ форматирования ячеек – посредством кнопок панели инструментов форматирования.

Кнопка Денежный формат служит для присвоения денежного стиля.

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

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

Изменение ширины столбца

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

2. Команды Формат, Столбец, Автоподбор ширины определят оптимальную ширину столбца. Ширина определяется с учетом длины содержимого ячеек. При этом для каждого столбца устанавливается своя оптимальная ширина.

3. Единицей измерения ширины столбцов является значение, равное ширине символа стандартного шрифта листа. По умолчанию ширина столбца равна 8,43 символа. Изменить это значение можно в диалоговом окне Ширина столбца, которое открывается при вызове команды Формат, Столбец, Ширина.

 

Изменение высоты строки

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

2. При недостаточной высоте строки содержимое ячеек (как числа, так и текст) урезается. В этом случае можно задать автоматический подбор оптимальной высоты строки с помощью команды Формат, Строка, Автоподбор высоты.

3. Точное значение высоты строки устанавливается в диалоговом окне Высота строки, которое открывается при вызове команды Формат, Строка, Высота.

Использование рамок

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

Можно заключать в рамку каждую ячейку выделенного диапазона. В диалоговом окне Формат ячеек на вкладке Граница щелкнуть на кнопках внешние и внутренние.

Удалить рамку можно двумя способами. Первый способ заключается в использовании команды Правка, Очистить, Форматы. В этом случае удаляются и другие параметры форматирования ячейки. Чтобы удалить только рамку, следует отменить параметры, установленные на вкладке Граница диалогового окна Формат ячеек.

 

3.8. Цветовое оформление

Цвет и узор заполнения ячеек устанавливается на вкладке Вид диалогового окна Формат ячеек.

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

Вкладка Вид содержит две палитры: для выбора цвета фона – Цвет и выбора узора заполнения – Узор. По умолчанию цвет фона не задан.

Вычисления в таблице

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

Формулы

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

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

1. Выделить ячейку, в которую надо ввести формулу.

2. Ввести знак равенства (=), с которого должна начинаться формула.

3. Ввести знаки математических операций и ссылки на ячейки, над которыми нужно произвести вычисления.

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

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

1. Выделить ячейку, в которую нужно ввести формулу.

2. Ввести знак равенства (=), с которого должна начинаться формула.

3. Щелкнуть левой кнопкой мыши по ячейке, ссылку на которую необходимо ввести в формулу.

4. Ввести знак математической операции.

5. Щелкнуть левой кнопкой мыши по ячейке следующей ячейке, ссылку на которую необходимо ввести в формулу.

6. Ввести знак операций и так далее.

7. Нажать клавишу Enter или кнопу с изображением галочки, отображающуюся в строке формул, чтобы завершить ввод.

Ссылки на ячейки в формулах

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

Абсолютные ссылки применяются в таких случаях, когда нужно использовать ссылки на конкретные ячейки независимо от того, в какой ячейке находится сама формула. Чтобы создать абсолютную ссылку на столбец, строку или ячейку, необходимо перед соответствующей ссылкой поместить знак доллара ($), например, $В$45, $О$1. При копировании формул абсолютные ссылки не изменяются.

Помимо относительных и абсолютных ссылок существуют также смешенные, которые являются комбинацией абсолютной и относительной ссылок. Например, $С1, В$27.

Ошибки в формулах

В табл. 3 представлены значения ошибок и их возможные причины.

Таблица 3

Ошибка	Описание
#ДЕЛ/0	В формуле присутствует операция деления на ноль
#Н/Д	Формула ссылается на неопределенные данные
#ИМЯ?	В формуле используется неопределенное имя
#ПУСТО!	Формула содержит ссылку на пустое пересечение ячеек
#ЧИСЛО!	Формула использует неверное значение в качестве числового аргумента встроенной функции или возвращает результат, не попадающий в область допустимых значений
#ССЫЛКА!	В формуле используется недопустимая ссылка на ячейку
#ЗНАЧ!	Формула содержит недопустимый аргумент или операцию

Чтобы легче было найти ошибку, можно установить режим отображения формул, а не результатов. Для этого следует активизировать опцию Формула на вкладке Вид диалогового окна Параметры, меню Сервис. Ширина столбцов автоматически увеличится с целью улучшения обзора. Режим отображения результатов вычисления восстанавливается при выключении опции Формула.

 

Функции в формулах

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

- ввод вручную;

- использование кнопки Автосуммирования для вычисления суммы ячеек диапазона;

- использование Мастера функций, который управляет пошаговым построением функции.

Ввод функций вручную

Чтобы ввести функцию в активную ячейку, надо ввести знак равенства (=), затем имя функции, например СУММ, и открывающуюся скобку. После этого указать в качестве аргумента конкретную ячейку или диапазон, выделив его с помощью мыши или клавиатуры, и ввести закрывающуюся скобку. Нажать клавишу Enter, чтобы завершить ввод. В ячейке появится результат вычисления формулы.

Построение диаграмм

Важным элементом при анализе и выводе на печать результатов в Excel являются диаграммы. На каждой диаграмме можно выделить основные элементы (рис. 2).

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

Оба типа диаграмм связаны с данными рабочего листа и автоматически обновляются при изменении данных.

 

Рис.2. Основные элементы диаграммы

 

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

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

2. Для вызова мастера диаграмм выполнить команду Вставка, Диаграмма или нажать кнопку Мастер диаграмм на панели инструментов Стандартная.

3. В появившемся диалоговом окне мастера диаграмм (шаг 1 из 4) выбрать тип и вид диаграммы. Нажать кнопку Далее.

4. Кнопка Просмотр результатов позволяет предварительно просмотреть данные на текущей выбранной диаграмме.

5. В диалоговом окне мастера диаграмм (шаг 2 из 4) в поле Диапазон отображается адрес диапазона ячеек, выбранных для построения диаграммы в пункте 1. Задать ряды данных, которые будут отображаться на диаграмме можно на вкладке Ряд. Для перехода к следующему шагу нажать кнопку Далее.

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

7. На последнем, четвертом, шаге мастера диаграмм выбрать место размещения диаграммы – на текущем (имеющемся) листе или на отдельном. Нажать кнопку Готово.

Для корректировки диаграммы после нажатия на кнопку Готово захватить диаграмму мышью за область диаграммы и перетащить на новое место.

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

Контрольные вопросы

1. Перечислите основные этапы построения диаграмм в электронной таблице?

2. Перечислите основные типы диаграмм.

3. Как выделить для построения диаграмм несмежные области данных?

4. Как отредактировать построенную диаграмму?

5. Что означают понятия: а) ряд данных; б) категория; в) легенда?

6. Как отредактировать на диаграмме: а) название диаграммы; б) названия осей Х и Y; в) легенду; г) изменить тип диаграммы; д) добавить новые данные?

 

Задание параметров страницы

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

Вкладка Страница. На ней задаются параметры формата страницы. Формат печатного листа выбирается в поле списка Размер бумаги, в котором представлены все доступные для принтера форматы. В этом же диалоговом окне можно выбрать ориентацию страницы: Книжная или Альбомная.

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

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

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

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

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

Печать компонентов листа

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

- сетку;

- примечания;

- заголовки строк и столбцов.

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

Контрольные вопросы

1. Каким образом можно просмотреть документ перед печатью?

2. Каким образом можно устанавливать разделители страницы?

3. Какие параметры можно изменить на вкладках Страница, Поля, Колонтитулы, Лист диалогового окна Параметры страницы?

4. Как выполняется центрирование данных на странице?

Библиографический список

1. Берлинер Э.М., Глазырина И.Б., Глазырин Б.Э. Microsoft Office XP. – М.: Бином, 2001. – 432 с.

2. Виктор Пасько. Microsoft Office 2000. (руссифицированная версия). – Киев: Издательская группа BHV, 2000. – 784 с.

3. Вилетт Эдвард, Кроудер Дэвид, Кроудер Рондо. Microsoft Office 2000. Библия пользователя. / Пер с англ. – М.: Издательский дом «Вильямс», 2001.

4. Долженков И. А., Колесников Ю. В. Самоучитель Microsoft © Excel 2003. – СПБ.: БХВ – Петербург, 2004. – 432 с.: ил.

5. Информатика. Базовый курс. / С.В. Симонович и др. – СПб.: Питер, 2004. – 640 с.

6. Карпов Б. Microsoft Office 2000: Справочник. – СПб.: Издательство «Питер», 2000. – 448 с.: ил.

7. Новиков Ф.А., Ященко А.Д. Microsoft Office 2000 в целом. – СПб.: БХВ – Санкт-Питербург,1999. – 728 с.

8. Рычков В. Самоучитель Excel 2003. – СПб.: Питер, 2005.

9. Самволоков Л.В. Решение бизнес-задач в Microsoft Office. – М.: Издательство Бином, 2001.

10. Фигурнов В.Э. IBM для пользователя. – М.: Финансы и статистика, 2000.

11. Шуремов Е.Л., Тимакова Н.А. Практикум по экономической информатике. – М.: Изд-во «Перспектива», 2000.

12. Экономическая информатика: Учебник для вузов/ Под ред. д.э.н., проф. В.В. Евдокимова. – СПб.: Питер, 2000. – 592 с.: ил.

13. Экономическая информатика: Учебник для вузов/ Под ред. П.В.Конюховского. – СПб.: Питер, 2001. – 560 с.

8. Индивидуальные задания

Все задания выполняются и сохраняются в одной рабочей книге. Задания 1, 2, 3 выполняются по таблицам в Приложении 1. Номер варианта соответствует порядковому номеру студента в журнале преподавателя. Условия по выполнению задания 4 являются общими.

Задание 1. Создание и редактирование таблиц

1. Создать в своей папке новую рабочую книгу.

2. Построить на Листе1 таблицу, соответствующую Вашему варианту (Приложение 1).

3. Выполнить вычисления согласно предложенным формулам в Приложении 1. Вычислить итоги по столбцам. Результаты поместить в ячейки, в которых находятся символы (ххх).

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

5. Поменять местами последний и предпоследний столбцы (рис. 3).

6. Установить альбомную ориентацию листа, поля по 4 см, горизонтальное и вертикальное центрирование.

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

8. Сохранить и распечатать созданную таблицу.

9. Скопировать таблицу на Лист2.

10. Поменять местами последний и предпоследний столбцы на втором листе.

11. Удалить строку, содержащую номера столбцов, на втором листе.

12. Установить на втором листе режим отображения формул (рис. 4).

13. Отредактировать названия столбцов, содержащих формулы, добавив слова «режим отображения формул» (рис. 4).

14. Присвоить Листу1 имя «Значения», Листу2 – «Формулы».

15. Для листа «Формулы» задать альбомную ориентацию страницы, поля по 2 см, горизонтальное и вертикальное центрирование, создать колонтитул (см. пункт 7).

16. Распечатать рабочий лист «Формулы».

17. Поменять местами лист «Значения» и лист «Формулы».

18. Скопировать трижды таблицу (вместе с заголовком) с рабочего листа «Значения» на Лист3, которому присвоить имя «Форматирование».

19. Добавить новый рабочий лист и присвоить ему имя «Диаграммы».

20. Сохранить рабочую книгу.

Рис. 3. Таблица, созданная средствами программы MS Excel

 

Рис. 4. Таблица в режиме отображения формул

Задание 2. Форматирование таблиц

1. Перейти на лист «Форматирование».

2. Выполнить форматирование первой таблицы вручную:

- расположить заголовок по центру таблицы;

- выделить заголовок шрифтом Arial Black размером 11 пт, задать двойное подчеркивание;

- выполнить обрамление таблицы: внешние границы – двойные линии, внутренние – пунктирные;

- установить высоту строк в таблице равную 25 пунктам;

- задать выравнивание ячеек по горизонтали и по вертикали – по центру;

- выбрать наиболее подходящий числовой формат:

для денежных величин – «Денежный» или «Финансовый», для отображения процентов – «Процентный», для числовых – установить число десятичных знаков равное двум.

3. Отформатировать вторую таблицу с помощью Автоформата.

4. Создать стиль ячеек (имя стиля – фамилия студента).

Стиль должен включать:

- числовой формат с нулевым количеством цифр после запятой;

- шрифт Monotype Corsiva, размером 12 пунктов;

- узор ячеек.

5. Вставить диалоговое окно «Стиль» после третьей таблицы.

6. Применить созданный стиль к третьей таблице листа «Форматирование».

7. Сохранить рабочую книгу.

8. Создать колонтитул, как в задании 1.

9. Распечатать лист «Форматирование».

 

Задание 3. Построение диаграмм

1. На имеющемся рабочем листе «Диаграммы» построить разные типы стандартных диаграмм (рис. 5):

- гистограмму (ряды данных в столбцах);

- линейчатую диаграмму по этим же рядам данных (ряды данных в строках);

- круговую диаграмму по одному из столбцов с расчетными данными.

2. В диаграммах должны присутствовать:

- заголовок диаграммы;

- легенда с именами рядов данных;

- название осей;

- значения и доли (для круговой диаграммы)

3. Выполнить форматирование диаграмм:

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

4. Создать колонтитулы, как в задании 1.

5. Распечатать рабочий лист «Диаграммы».

 

Рис. 5. Гистограмма, линейчатая и круговая диаграммы

 

6. Построить нестандартную диаграмму любого вида (рис. 6). При размещении диаграммы выбрать пункт: Поместить диаграмму на отдельном листе.

7. Отменить режим добавления легенды.

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

+

Рис. 6. Нестандартная диаграмма

 

Задание 4. Работа со встроенными функциями MS Excel

1. Добавить в книгу новый рабочий лист. Присвоить имя «Табель».

2. Построить табель учета отработанных часов сотрудниками магазина за первую половину месяца (табл. 3). Продолжить заполнение граф «Числа месяца» обозначениями: «8» – рабочий день, «б» – дни по болезни, «о» – отпуск, «п» – дни прогула.

3. Для ввода фамилий сотрудников использовать автозаполнение, создав предварительно список фамилий для Автозаполнения. Фамилия первого сотрудника – это Ф.И.О. студента, выполняющего задание.

Таблица 3

4. Определить количество:

- отработанных часов (функция СУММ);

- дней, пропущенных по болезни (функция СЧЕТЕСЛИ, критерий − текст "б");

- прогулов (функция СЧЕТЕСЛИ, критерий − текст "п");

- дней отпуска (функция СЧЕТЕСЛИ, критерий − текст "о");

- отработанных дней (функция СЧЕТ);

- общее количество дней отпуска каждой категорией работников (функция СУММЕСЛИ).

5. Отформатировать таблицу.

6. Создать колонтитул.

7. Ввести на новый рабочий лист данные для расчета заработной платы сотрудников за первую половину месяца (табл. 4). Таблица 4

8. Построить на новом рабочем листе дополнительные таблицы (табл. 5, табл. 6). Рассчитать заработную плату каждого сотрудника, используя следующие данные:

- Начислено = Оклад / Количество рабочих дней * Количество отработанных дней. Данные о количестве рабочих дней и количестве отработанных рабочих дней находятся на листе «Табель».

- 20 % РК (районный коэффициент) = Начислено * 20 %.

- 30 % ДВК (дальневосточный коэффициент) = Начислено * 30 %.

- Индивидуальная надбавка = Начислено * % надбавки. Индивидуальная надбавка зависит от должности, стажа работы и оклада. (Для вычисления индивидуальной надбавки использовать функции ЕСЛИ, И, ИЛИ).

Дополнительные таблицы для решения задачи:

Таблица 5

Должность

% надбавки

директор, гл. бухгалтер

Стаж работы >= 15 лет

директор, гл. бухгалтер

Стаж работы < 15 лет

остальные сотрудники

Стаж >= 10 лет или оклад <3000

Таблица 6

№ п/п

Ф.И.О. сотрудника

Должность

Стаж работы в организации (лет)

Количество детей

Оклад

Совокупный доход

Среднедневной заработок

A

директор

15 000

176 538

B

гл. бухгалтер

10 000

62 308

C

бухгалтер

8 000

53 169

D

бухгалтер

8 000

51 920

F

продавец

4 000

37 800

E

продавец

4 000

40 230

G

продавец

3 000

33 231

H

уборщица

2 000

15 508

- Отпуск = Среднедневной заработок * Количество дней отпуска

- ПВНТС (больничный) за счет средств работодателя = кол-во дней по болезни * среднедневной заработок * Р.

Р = 100 %, если стаж работы ≥ 8, Р = 80 %, если 5 ≤ стаж < 8, Р = 60 %, если стаж работы < 5 лет. Для вычисления ПВНТС использовать вложенные функции ЕСЛИ.

- НДФЛ (налог) = (Итого по кредиту счета 70 – Вычет 1 – Вычет 2 * Количество детей) * 13%. Вычет 1 предоставляется на работающего до момента достижения суммы совокупного годового дохода = 20 000. Вычет 2 предоставляется на каждого ребенка до достижения суммы совокупного дохода = 40 000 (табл. 7). Для вычисления НДФЛ использовать функцию ЕСЛИ. Таблица 7

Сумма, не облагаемая налогом

на работающего

на ребенка

400,00р.

600,00р.

- Аванс = Итого по кредиту счета 70 * 40 %