Методические указания по работе в MS Excel

© Хохлов Алексей Евгеньевич

доцент кафедры «Информационно-вычислительные системы»

Пензенского государственного университета,

сертифицированный пользователь «1С: Профессионал»

(Конт. тел. 36-82-38, , E-mail: hohlov59@mail.ru)

1. Общие принципы работы

В настоящее время основной системой ведения электронной документации является Microsoft Office. Одной из наиболее важных частей этой системы является табличный процессор Excel (наряду с текстовым процессором Word).

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

Каждая книга состоит из листов (обычно из трех). Каждый лист – это электронная таблица. Строки этой таблицы нумеруются от 1 до 65536, столбцы обозначаются буквами A, B, C, …, AA, AB, AC, … и т. д. На пересечении строк и столбцов находятся ячейки. Каждая ячейка имеет свой адрес, например A5, C23 и т.д. Текущая ячейка выделяется жирным прямоугольником. В неё можно заносить данные следующих типов: число, текст, формула, дата, время. Обычно Excel сам распознает типы данных. Дата и время интерпретируются как разновидность чисел и определяются как количество дней с начала века. Формулы в Excel начинаются с символа «=». Формулы могут включать в себя числа, адреса и имена ячеек, функции, текст в кавычках, а также знаки арифметических операций и знаки сравнения. Шрифт и размер шрифта в ячейках можно менять. Формат ячейки также можно менять через меню Формат,например, можно задать денежный формат без копеек.

Упражнение 1

1. Создайте заготовку ведомости начисления зарплаты следующего вида:

Для этого введите в ячейки формулы, так как это показано на рисунке:

2. Удаление, автозаполнение, адресация

Содержимое ячейки можно удалить клавишей Delete. Можно удалять и целиком столбцы, строки и даже лист через меню Правка \ Удалить.При этом существующие записи сдвигаются для заполнения очищенного пространства. Эффективным средством для создания последовательностей (например, названий месяцев) и, главное, для копирования формул является режим Автозаполнения. Для этого надо выделить ячейку, навести курсор на правый нижний угол ячейки, при этом курсор приобретет вид небольшого крестика и затем, не отпуская левой кнопка мыши, переместить мышь в нужном направлении (обычно вниз или вправо). В результате такой операции формула из исходной ячейки скопируется во все нижние или правые ячейки до той ячейки, где была отпущена левая кнопка мыши.

При этом все адреса исходной ячейки соответствующим образом изменятся. Такие адреса называются относительными. Но часто бывает необходимо не изменять адрес той или иной ячейки при автозаполнении. Тогда в исходной формуле заменяют относительный адрес на абсолютный. Для этого в формуле подводят курсор на этот адрес и нажимают клавишу F4. Адрес ячейки дополнятся знаками $. Например, относительный адрес H2 превращается в абсолютный адрес $H$2. В Excel есть множество функций, наиболее часто из них используется функции суммирования СУММ(<диапазон>). В качестве диапазона указывают адрес начальной ячейки и через двоеточие адрес конечной ячейки. Чтобы просуммировать значение какого-либо столбца необходимо выделить ячейку под этим столбцом и на панели инструментов нажать кнопку Σ. Аналогично суммируются и значения в строках.

Упражнение 2

Заполните ведомость начисления зарплаты

Для этого введите формулы как показано на рисунке

1. Именование и вставка, мастер функций

Каждый лист рабочей книги можно переименовать, нажав правую кнопку мыши на ярлычке листа (в левом нижнем углу экрана).

Каждой ячейке также можно присвоить своё имя, при этом она приобретает свойство абсолютной адресации. Для присвоения имени надо выделить нужную ячейку, вызвать пункты меню Вставка \ Имя \ Присвоить.В появившемся окне проверить устраивает ли вас предложенные имя ячейки и адрес ячейки. В случае необходимости изменить имя и адрес. Затем нажать кнопку Добавитьи кнопку OK. Теперь имя этой ячейки можно использовать в формулах. Аналогично можно также присвоить имя и диапазону ячеек.

Из одного листа можно обращаться к ячейкам или диапазонам другого листа. В этом случае имя листа отделяется от адреса (или имени) ячейки восклицательным знаком, например, «Лист!$D$4:$D$10».

Excel позволяет пользоваться богатейшим набором функций. Их вызов происходит через Мастер функций путем нажатия кнопки f_x на панели инструментов. Работу каждой функции можно изучить самостоятельно через систему помощи.

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

Упражнение 3

1. Переименуйте Лист1 в «Ведомость», а Лист2 в «Нормы».
2. Измените заголовок ведомости, назовите её «Расчетно-платежная ведомость».
3. На листе «Нормы» заполните следующую таблицу, используя режим Автозаполнения для ввода месяцев

1. На листе «Ведомость» вставьте графы «Кол. детей», «Льгота», «НДФЛ»(вместо «Удержано»), «Подпись».
2. Поместите в ячейку D17 значение ставки НДФЛ и присвоим этой ячейки имя «Ставка».
3. Занесем в эту ячейку число 0.13 и зададим формат этой ячейки – Процентный.
4. Сумму начислений для первой строки будем рассчитывать по формуле

=G5/Нормы!$B$3*F5

1. НДФЛ рассчитываем с учетом наличия или отсутствия льгот по формуле

=ЕСЛИ(E5="Да";0;(H5-(400+D5*600))*Ставка)

1. Первую строку графы «К выдаче» исправьте самостоятельно.
2. Заполните остальные строки с помощью Автозаполнения.
3. Подсчитайте суммы по графам «Начислено», «НДФЛ», «К выдаче».
4. Таблица с формулами должна выглядеть примерно так

Результирующая таблица может иметь примерно следующий вид

1. Сортировка и фильтрация

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

При фильтрации отображаются только те записи, которые соответствуют заданным критериям. Самый простой способ фильтрации через меню Данные \ Фильтр \ Автофильтр.

Упражнение 4

1. Удалите итоговую строку. (Если этого не сделать, то она будет участвовать в сортировке и фильтрации наравне с другими строками, что, естественно, неправильно)
2. Отсортируйте таблицу в возрастающем и убывающем порядке по каждому из столбцов.
3. Восстановите первоначальный порядок таблицы.
4. Покажите в таблице только сотрудников: а) у кого нет детей, б) с зарплатой менее 5000 р., в) с невыходами на работу.

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

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

Упражнение 5

С помощью мастера диаграмм постройте гистограмму распределения полученной зарплаты.

Вид диаграммы – гистограмма. Тип – обычная.

Диапазон данных - $J$5:$J$12. Ряды в столбцах. На закладке Ряд: Имя - $J$4, Ряд – «К выдаче», Подписи по X - $B$5:$B$12.

На закладке Заголовки:Название диаграммы – Зарплата сотрудников, Ось Х – Фамилии сотрудников, Ось Y – Суммы к выдаче. На закладке Подписи данных: Включать в подписи - значения.

Поместить диаграмму на листе – имеющемся.

После нажатия кнопки Готовона экране появляется построенная диаграмма. Необходимо расположить так чтобы она не закрывала исходную таблицу с расчетно-платежной ведомостью. В итоге должно получится примерно следующее

1. Применение форм

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

Упражнение 6

Задание1: С помощью счетчика на панели Формы организуйте управление ставкой налога.

Выберите Счетчик на панели формы и перенесите его поближе к ячейке Ставка налога.Уменьшите его размер. Щелкнув по счетчику правой кнопкой, выберите Формат объекта.На закладке Элемент управления задайте: Текущее значение – 13, Минимальное значение – 0, Максимальное значение – 50, Шаг изменения – 1, Связь с ячейкой - $D$18. Здесь следует иметь ввиду, что дробные значения этих параметров задать невозможно, в то же время все приведенные значения измеряются в процентах. Выход из этой ситуации заключается в том, что в формуле расчета НДФЛ (столбец I) ставку налога надо просто поделить на 100. После всех настроек и исправлений испытайте работу счетчика.

Задание 2: С помощью полосы прокрутки на панели Формы организуйте управление ставкой налога. Все настройки проведите самостоятельно.

В итоге должно получится примерно следующее.