Klecova_Informacionnye_tehnologii_elektronnye_t

затем в разделе Разместить диаграмму выполнить одно из следующих действий (рис.1.24): либо выбрать параметр на отдельном листе для вывода диаграммы на лист диаграммы (чтобы заменить предложенное имя диаграммы, необходимо ввести новое имя в поле на отдельном листе); либо выбрать параметр на имеющемся листе и выбрать лист в поле на имеющемся листе для вывода диаграммы в виде внедренной диаграммы на листе (после этой операции Excel удалит лист диаграммы [1]).

Рис.1.24

Для того, чтобы создать диаграмму необходимо:

1.Выделить ячейки, содержащие данные для построения диаграммы.

2.В группе Диаграммы (рис.1.25) на вкладке Вставка вы-

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

31

либо для просмотра доступных видов диаграмм открыть диалоговое окно Вставка диаграммы (выбрать вид диаграммы и затем щелкнуть пункт Все виды диаграмм).

Рис.1.25

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

Виды и подвиды диаграмм представлены на рис.1.26.

32

Рис. 1.26

33

Сортировка

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

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

Сортировка по возрастанию или кнопку Сортировка по убыванию на вкладке Данные, раздел Сортировка и фильтр (рис.1.27). В результате данные будут расположены в заданном порядке по столбцу, в котором выделена ячейка.

Рис.1.27

При выборе кнопки Сортировка на вкладке Данные, раздел Сортировка и фильтр открывается диалоговое окно Сор-

тировка (рис.1.28).

Рис.1.28

34

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

вает флажок Мои данные содержат заголовки. Но если про-

грамма по какой-то причине не заметила строки заголовка, то пользователь может сам установить эту опцию.

Excel также предполагает, что необходимо отсортировать данные по столбцу, содержащему активную ячейку и включает в поле Сортировать по либо заголовок этого столбца (если есть строка заголовков), либо букву, обозначающую столбец [1].

В поле Сортировка задается тип сортировки. Для сортировки по текстовым, числовым значениям или значениям даты и времени необходимо выбрать вариант Значения. Для сортировки по типам форматирования необходимо выбрать вариант Цвет ячей-

ки, Цвет шрифта или Значок ячейки.

В разделе Порядок необходимо задать последовательность, в которой необходимо осуществлять сортировку. Для текстовых значений – От А до Я или От Я до А, для числовых значений

– По возрастанию или По убыванию, для сортировки по датам и времени – От старых к новым или От новых к ста-

рым, для сортировки на основе пользовательских – вариант На-

страиваемый список.

В Excel также возможна сортировка по нескольким столбцам (В Excel2007 можно задать до 64 уровней сортировки). Для этого в диалоговом окне Сортировка (см. рис.1.17) необходимо нажать на кнопку Добавить уровень. И для этого уровня задать столбец с данными для сортировки, тип сортировки и порядок сортировки.

Последовательность сортировки данных в Excel [1,3]

1.Ячейки упорядочиваются в соответствии с их содержимым, а не с форматом.

2.Числовые данные имеют больший приоритет относительно текстовых значений.

3.Приоритеты символов при сортировке алфавитных и алфа- витно-цифровых символов следующие: 0123456789(пробел)!$%&()*,./:;?@[\]^-{}~+<=>abc…xyzабв….эюя

35

4.Апострофы и дефисы игнорируются.

5.Логические значения размещаются после текстовых значений, и ЛОЖЬ считается меньше чем ИСТИНА.

6.Значения ошибки выводятся после логических значений.

7.Пустые ячейки попадают в конец списка как при сортировке по возрастанию, так и при сортировке по убыванию.

Закрепление областей

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

Для закрепления областей в разделе Окно вкладки Вид предназначена кнопка Закрепить области (рис.1.29). Кнопка открывает меню из нескольких команд: Закрепить области, За-

крепить верхнюю строку, Закрепить первый столбец, Снять закрепление областей.

Рис.1.29.

Транспонирование таблицы

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

36

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

Пример оформления отчета

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

Постановка задачи

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

Была разработана следующая структура таблицы Excel для расчета страховых взносов (рис.1.30):

Рис.1.30

Табельный номер Фамилия, имя, отчество

37

Дата рождения Начислено Взносы в фсс Взносы нсипз Внозы на ффомс Взносы тфомс

Взносы накопительная часть пенсии Взносы страховая часть пенсии Итого взносов

Для столбца Дата рождения был задан формат ячеек «дата».

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

Константы

Были сделаны константы: ставки взносов и дата (01.01.1965), в зависимости от которой применяются разные ставки взносов в пенсионный фонд. Они были размещены в ячейках С3-С8, D7-D8, E7. При обращении к ним в формулах будут использоваться абсо-

лютные ссылки ($C$3-$C$8, $D$7-$D$8, $E$&).

Расчетные формулы

Втаблице применены следующие расчетные формулы:

–«фсс» = «начислено» * «ставка взноса фсс»

–«нсипз» = «начислено» * «ставка взноса нсипз»

–«ффомс» = «начислено» * «ставка взноса ффомс»

–«тфомс» = «начислено» * «ставка взноса тфомс»

–«накопит» = ЕСЛИ «дата рождения»< 01/01/1965, то «начислено» * «ставка взноса накопит до 1965», иначе «начислено» * «ставка взноса накопит после 1965»

38

–«страх» = ЕСЛИ «дата рождения»< 01/01/1965, то «начислено» * «ставка взноса страх до 1965», иначе «начислено» * «ставка взноса страх по-

сле 1965»

–«всего взносов»= Сумма «фсс, нсипз, ффомс, тфомс, накопит, страх»

–«итого взносов: фсс»= Сумма «фсс»

–«итого взносов: нсипз»= Сумма «нсипз»

–«итого взносов: ффомс»= Сумма «ффомс»

–«итого взносов: тфомс»= Сумма «тфомс»

–«итого взносов: накопит»= Сумма «накопит»

–«итого взносов: страх»= Сумма «страх»

Внешний вид таблицы приведен на рис. 1.31.

Рис.1.31

39

Запись макросов

Для того чтобы записать макрос, необходимо:

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

–разработчик→макросы→запись макроса;

–осуществить действия, необходимые для выполнения операции;

–разработчик→макросы→остановить запись.

Создание кнопок

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

брать опцию Все команды, в левом списке выбрать элемент Кнопка (элемент управления). Нажатием на кнопку Добавить элемент будет отображен в правом списке (перемещен на панель быстрого доступа) (рис.1.32). Для сохранения операции необходимо нажать на кнопку ОК.

Рис.1.32

40