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

1. Как создать формулу, используя встроенный редактор формул?

2. Как набрать простую формулу?

3. Как отредактировать формулу?

4. Какие стили используются при наборе формул? Как изменить стиль набора?

5. Как изменить размер символов в формуле?

Лабораторная работа 6

Создание таблицы средствами табличного процессора Excel

1. Цель и задачи лабораторной работы

Лабораторная работа предназначена:

• для изучения и получения навыков создания простых и структурированных таблиц средствами Excel;

• использования абсолютной и относительной адресации в ссылках;

• практического освоения расчетов в таблицах;

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

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

2. Содержание лабораторной работы

Лабораторная работа состоит из нескольких заданий.

Задание 1. Построение простой электронной таблицы

Сформировать электронную таблицу из 12 строк по приведенному образцу .

	А	В	С	D
1	Фамилия	Зарплата	Налог	Выплатить
2		руб.	12%	руб.
3
4
5
6
7
8
9
10
11
12

Задание 2. Создание структурированной таблицы с подведением итогов.

3. Рекомендуемые правила и приемы работы с электронными таблицами

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

1. Копирование формул:

• выделить блок ячеек и выполнить вкладка Главная - группа Редактирование - кнопка- – ;

• «протягиванием» мышью, для чего выделить мышью блок формул, в правом нижнем углу выделенной области найти тонкий «+» и показать протягиванием тонкого «+» место для копирования.

2. Перенос блока ячеек:

• выделить мышью блок;

• найти на краю выделенной области двойную стрелку;

• показать протягиванием место для перемещения.

3. Подведение итогов по группе смежных ячеек:

• выделить блок ячеек, по которому подводятся итоги;

• поставить указатель в свободную ячейку после группы;

• щелкнуть на значке в группеРедактирование вкладкиГлавная,далее нажать кнопку .

4. Для вставки строки (столбца) выделить строку (столбец), перед которымбудет выполнена вставка, и выполнить команду вкладкаГлавная - группаЯчейки - кнопка.

5. При использовании в формулах встроенных стандартных функций использовать Мастер функций, для этого выбрать вкладку Формулы, нажать кнопку– или выбрать кнопкув группеРедактирование и нажать на кнопку- .

6. При заполнении ячеек могут быть использованы встроенные функции:

• суммирование СУММ, СУММЕСЛИ;

• определение среднего значения СРЗНАЧ;

• определение максимального значения и минимального значения МАКС иМИН;

• определение количества значений СЧЕТ, СЧЕТЕСЛИ;

• логические функции ЕСЛИ, И, ИЛИ,вложенныеЕСЛИ и др.

7. При построении вложенных функций использовать список функций в строке формул.

4. Последовательность выполнения работы

Выполнить задание 1, для чего:

1. Открыть файл для формирования таблицы.

2. Заполнить строки 1 и 2 и столбец А. Все заголовки и фамилии вводить с первой позиции ячейки.

Столбец В(«Зарплата») заполнить значениями от 8000 руб. до 30000 руб.

3. В ячейке С3 и D3 ввести формулы:

С3 = В3 * С$2 (для указания размера налога использовать абсолютный адрес)

D3 = B3 – C3

4. Скопировать формулы из строки 3 в остальные строки таблицы.

5. Просуммировать данные столбца В(в ячейкеВ15), используя функциюСУММ. В ячейкеА15набратьИтого.

6. Скопировать эту формулу в ячейки С15иD15.

7. Сохранить заполненную таблицу под именем ZPL1.xls.

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

9. Установить налог 15% в ячейке С2. Сравнить полученные итоговые данные с предыдущими значениями.

1. Вставить новые графы «Премия»и«Всего начислено»после графы«Зарплата».Самостоятельно задать формулы для их вычисления, исходя из того, что премия составляет определенный процент от зарплаты (процент премии задать в ячейке ), а«Всего начислено»- это«Зарплата» + «Премия».

Отредактировать формулу вычисления налога (Всего начислено * Е$2).

Отредактировать формулу вычисления столбца Выплатить (=Всего начислено - Налог).

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

2. Дополнить таблицу еще тремя строками, включив их между, например, 5 и 6 строками (приняты три новых сотрудника). Заполнить эти строки. Фамилии и зарплату ввести, формулы – скопировать.

3. Сохранить таблицу под именем ZPL2.xls.

4. В созданном файле ZPL2.xlsв ставить перед колонкой«Налог»еще две колонки«Пенсионный фонд»и«Налогооблагаемая база».Установить, что в пенсионный фонд удерживается в размере 1% от начислений зарплаты и премии. Отчисления в пенсионный фонд не входят в налогооблагаемую базу, т.е. «Налогооблагаемая база»вычисляется как«Зарплата» + «Премия» - «Пенсионныйфонд». Внести все необходимые изменения в формулы.

5. Изменить алгоритм расчета подоходного налога с учетом прогрессивной шкалы налогообложения. Если налогооблагаемая база меньше определенной величины (например, 12000), то принимается ставка 12%, если больше – 20%. Формула должна использовать функцию ЕСЛИ.

6. Проанализировать полученные результаты.

7. Изменить формулу расчета столбца Выплатить (=Налогооблагаемая база - Налог).

8. Вставить перед колонкой «Фамилия»новую графу«Табельный номер»и заполнить ее значениями: 100, 101, 102 и т.д.(в ячейкеА3набрать 100, выделить блок ячеек с А3 до конца списка фамилий, выполнитьвкладка Главная - группаРедактирование - кнопка– – ).

9. Под строкой «Итого»вставить еще две строки для вычисления среднего и максимального значения начисленной и выданной зарплаты. Для этого использовать встроенные статистические функцииМАКСиСРЗНАЧ, указать диапазон для работы этих функций. Набрать названия строк с полученными данными.

1. Задать имена для диапазонов ячеек, поочередно выделяя соответствующие столбцы (вкладкаФормулы - группаОпределенные имена - кнопка):

Зарплата – для столбца с начисленными зарплатами;

Премия – для столбца с премиями;

Налог – для столбца с налогами;

Пенсионный фонд – для столбца с отчислениями в пенсионный фонд.

1. Выделить всю таблицу, зайти в вкладка Формулы - группаОпределенные имена -кнопка . Применить все имена из списка. Проверить изменения в формулах.

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

1. Сохранить таблицу в своей папке под именем ZPL3.xls.

2. Отчитаться перед преподавателем, продемонстрировав три созданных файла.

Выполнить задание 2, для чего:

1. В созданном файле ZPL3.xlsдля создания структуры добавить после фамилии столбец«Отдел»и заполнить его, чередуя названия отделов, например, бухгалтерия, плановый, экономический.

2. Отсортировать таблицу по отделам, а внутри отделов – по фамилиям. Для этого:

• выделить таблицу без строк с итоговыми данными, выполнить вкладка Данные- группаСортировка и фильтр - кнопка- ;

• в поле Сортироватьпо указатьСтолбец C (Отдел)- отметить опциюОт А до Я(По возрастанию);

• добавить новый уровень кнопкой ;

• в поле ЗатемпоуказатьСтолбец B (Фамилия) -отметить опциюОт А до Я(По возрастанию);

• нажать кнопку ОК.

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

вкладка Данные - группа Структура - кнопка- -. Научиться сворачивать и разворачивать полученную (горизонтальную) структуру до разных уровней, используя кнопки «+» и «-».

4. Сохранить файл с созданной структурой под именем ZPLSTR.xls.

5. В созданном файле ZPLSTR.xlsудалить созданную структуру (вкладкаДанные - группаСтруктура – кнопка -.

6. Удалить строки с вычисленными данными Итого, Макс, Срзнач.

7. Подвести промежуточные итоги по отделам, используя формулу суммирования. Для этого:

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

• в поле При каждом изменении в:указатьСтолбец C (Отдел);

• в поле Операция:указатьСумма;

• в поле Добавить итоги по: отметить все столбцы, кроме столбцов А, В и С;

• отметить опции Заменить текущие итогииИтоги под данными.

• нажать кнопку ОК.

8. Изучить полученную структуру и формулы для подведения промежуточных итогов.

9. Применить кнопки-на вкладке Структура. Проанализировать полученный вид документа. Сохранить изменения в файлеZPLSTR1.xls.

10. Отчитаться перед преподавателем, продемонстрировав два файла.

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

1. Что может находиться в ячейке таблицы?

2. Что такое адрес ячейки? Какие бывают адреса?

3. Как выделить группу (диапазон) ячеек? Как задать адрес группы (дтапазона) ячеек?

4. Что такое формула? Из чего состоит формула?

5. Что такое МАСТЕР ФУНКЦИЙ? Для чего он используется? Как его вызвать?

6. Что такое имя диапазона? Для чего вводят имена диапазонов?

7. Как установить имя диапазона? Как применить имя диапазона в таблице?

8. Как упорядочить данные в таблице?

9. Как скопировать формулу без изменения адресов в ней?

1. Как скопировать формулу с изменениями адресов в ней?

2. Как скопировать значение ячейки без изменения (варианты)?

3. Как вставить (удалить) строку (столбец), несколько строк (столбцов)?

4. Как создать (удалить) структуру таблицы?

5. Как установить (отменить) фильтр?

6. Как выполнить подведение итогов? Какие итоги называются промежуточными? Какой итог называется общим?

7. Как сворачивать и разворачивать разделы списка после подведения итогов?

8. Как отменить итоги?

9. Как изменить имя листа Excel?

Лабораторная работа 7