Модуль 2. Основные возможности работы с электронной таблицей Microsoft Excel.

Лабораторная работа №11. Создание и оформление таблиц на рабочем листе Excel.

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

Краткое описание теоретической части.

Формула в EXCEL – это такая комбинация констант (значений), ссылок на ячейки, имен, функций и операторов, по которой из заданных значений выводится новое.

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

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

Относительная (A1) – указывает, как найти другую ячейку, начиная поиск с ячейки, в которой расположена формула.

Абсолютная ($A$1) – указывает, как найти ячейку на основании её точного местоположения на рабочем листе.

Смешанная (A$1, $A1) – указывает, как найти другую ячейку на основе сочетания абсолютной ссылки на строку и относительной на столбец и наоборот.

Функция – это специальная, заранее созданная формула, которая выполняет операции над заданным значением (значениями) и возвращает одно или несколько значений.

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

Перед вами стоит задача рассчитать заработную плату работников организации. Форма оплаты – оклад. Расчет необходимо оформить в виде табл. 1 и форм табл. 3 и 4.

Таблица 1

Лицевой счет
Таб. номер	Фамилия	Разряд	Должность	Отдел	Кол- во льгот	Факт. время (дн.)	Начис- лено з/п	Удер- жано	З/п к выдаче

Таблица 2

Справочник работников
Таб. номер	Фамилия	Должность	Отдел	Дата поступления на работу
	Алексеева	Нач.отдела		15.04.05
	Иванов	Ст.инженер		1.12.99
	Петров	Инженер		20.07.97
	Сидоров	Экономист		2.08.03
	Кукушкин	Секретарь		12.10.90
	Павленко	Экономист		1.06.87
	Давыдова	Инженер		15.11.97

Таблица 3

Ведомость начислений
Начислено Таб. номер	По окладу	Премия	Всего

Таблица 4

Ведомость удержаний
Удержано Таб. номер	Подоход- ный налог	Пенсион- ный налог	Исполнительные листы	Всего

При расчете следует использовать данные табл. 2

Использовать следующие формулы для расчета:

начисленной зарплаты ЗП = ЗП окл + ПР;

начисленной зарплаты по окладу ЗП окл = ОКЛ * ФТ/Т;

размера премии ПР = ЗП окл * %ПР;

удержаний из зарплаты У = У пн + У пф + У ил;

удержания подоходного налога У пн = (ЗП - МЗП * Л) * 0,12;

удержания пенсионного налога У пф = ЗП * 0,01;

удержания по исполнительным листам У ил = (ЗП - У пн) * %ИЛ;

зарплаты к выдаче ЗПВ = ЗП – У,

где:

ОКЛ – оклад работника в соответствии с его разрядом;

ФT – фактически отработанное время в расчетном месяце (дн.);

Т – количество рабочих дней в месяце;

%ПР – процент премии в расчетном месяце;

МЗП – минимальная зарплата;

Л – количество льгот;

%ИЛ – процент удержания по исполнительным листам.

Оклад работника зависит от его квалификации (разряда). Эта зависимость должна быть представлена в виде табл. 5.

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

 

Таблица 5 Таблица 6

Разрядная сетка		Справочник по исп. листам
Разряд	Оклад		Таб. Номер	% удерж.

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

1. Загрузите систему WINDOWS. На панели задач нажмите кнопку «Пуск», выберите пункт «Все программыàMicrosoft Office» загрузите программу Microsoft Office Excel 2007. На экране появится пустое окно электронной таблицы.

2. Рассмотрите внимательно таблицу, найдите строку с пунктами меню, панели инструментов, строку ввода с адресом текущей ячейки (находится под строкой меню), строку формул.

3. Введите заголовок табл. 1. Для этого установите указатель в ячейку A1. Введите текст: «Лицевой счет». Вводимая информация будет видна в строке формул (под строкой меню). По окончании набора нажмите клавишу Enter. Затем отметьте диапазон ячеек A1:Н1, выполните команды Главная /Формат/ Формат ячеек далее поставьте флажки Объединение ячеек,. выравнивание по горизонтали по центру, выравнивание по вертикали по центру

4. Установите для диапазона ячеек шапки табл. 1 режим переноса текста при достижении правого края. Для этого:

выделить диапазон ячеек A2:J2;

выполните команды Главная/Формат/Формат ячеек/выравнивание;

в диалоговом окне команды на вкладке Выравнивание в установить флажок Переносить по словам;

щелкнуть по кнопке OK.

5. Введите шапку табл. 1. Установите указатель в ячейку, куда будет вводиться информация, наберите требуемый текст и нажмите Enter. В случае ошибочно набранного текста нажмите F2 и исправьте ошибку. Чтобы первые буквы текста вторых строк не печатались прописными, необходимо в меню Сервис/Автозамена снять соответствующий флажок. Ввод текста шапки таблицы производите в соответствии со следующими рекомендациями:

Текущая клетка	Набираемый текст
A2	Таб. номер
B2	Фамилия
C2	Разряд
D2	Должность
E2	Отдел
F2	Кол-во льгот
G2	Факт. время (дн.)
H2	Начислено з/п
I2	Удержано
J2	З/п к выдаче

6. Разлинуйте табл. 1. Для этого необходимо выделить диапазон ячеек A2:H9 выполните команды Главная /Формат/ Формат ячеек и нажать кнопку пиктографического меню Границы. Выберите кнопку с типом линии для одновременного рисования внутренних и внешних границ в диапазоне ячеек. После нажатия на выбранную кнопку вокруг и внутри выделенного диапазона появятся рамки.

7. Введите заголовок табл. 5. Установите указатель в ячейку B11. Введите текст: «Разрядная сетка». По окончании набора нажмите Enter или переместите указатель в другую ячейку при помощи клавиш-стрелок.

8. Заполните шапку табл. 5 в соответствии с приведенными ниже рекомендациями:

Текущая клетка	Набираемый текст
B12	Разряд
C12	Оклад

9. Разлинуйте табл. 5. Для этого необходимо выделить диапазон ячеек В12:C30 выполните команды Главная àФорматà Формат ячеек и нажать кнопку пиктографического меню Границы. Выберите кнопку с типом линии для одновременного рисования внутренних и внешних границ вокруг ячеек.

10. Введите заголовок табл. 6. Установите указатель в ячейку Е11. Введите текст: «Справочник по исполн. листам», нажмите Enter.

11. Заполните шапку табл. 6 в соответствии с приведенными ниже рекомендациями:

Текущая клетка	Набираемый текст
Е12	Таб. номер
F12	% удерж.

12. Разлинуйте табл. 6 (диапазон ячеек E12:F15).

13. Введите заголовок табл. 2. Для этого установите указатель в ячейку L1. Введите текст «Справочник работников», нажмите Enter. Затем выделите диапазон ячеек L2:P2, нажмите кнопку Объединить и поместить в центре.

14. Установите для диапазона ячеек шапки табл. 2 режим переноса текста при достижении правого края для диапазона L2:P2 (см. п. 4).

15. Заполните табл. 2 в соответствии с приведенными ниже рекомендациями:

Текущая клетка	Набираемый текст
L2	Табельный номер
M2	Фамилия
N2	Должность
O2	Отдел
P2	Дата поступления на работу

16. Разлинуйте табл. 2 (диапазон ячеек L2:P9)

17. Введите заголовок табл. 3. Для этого установите указатель в ячейку A32. Введите текст «Ведомость начислений», нажмите Enter. Затем выделите диапазон ячеек A32:D32, выполните команды Главная àФорматà Формат ячеек далее поставьте флажки Объединение ячеек,. выравнивание по горизонтали по центру, выравнивание по вертикали по центру.

18. Установите для диапазона ячеек шапки табл. 3 режим переноса текста при достижении правого края для диапазона А33:D33 (см. п. 4).

19. Установите ширину столбца А равную 10. Для этого:

· установить указатель в ячейку А33;

выполнить команду Главная àФорматà Ширина à Ширина Столбца /;

· в поле ввода Ширина столбца ввести значение 10;

· щелкнуть по кнопке OK.

20. В ячейку А33 введите границу, разделяющую ячейку по диагонали. Для этого выполните команду Главнаяà Формат / Формат Ячейки и на вкладке Граница в группе Отдельные выберите тип линии – диагональ.

21. Заполните шапку табл. 3 в соответствии с приведенными ниже рекомендациями:

Текущая клетка	Набираемый текст
А33	пять пробелов, Начисл. Таб., 13 пробелов, номер
В33	По окладу
С33	Премия
D33	Всего

22. Разлинуйте табл. 3 (диапазон ячеек А33:D40).

23. Введите заголовок табл. 4. Для этого установите указатель в ячейку A42. Введите текст «Ведомость удержаний», нажмите Enter. Выделите диапазон ячеек A42:Е42, нажмите кнопку пиктографического меню Объединить и поместить в центре.

24. Установите для диапазона ячеек шапки табл. 4 режим переноса текста при достижении правого края для диапазона А43:Е43 (см. п. 4).

25. В ячейку А43 введите границу, разделяющую ячейку по диагонали (см. п.16).

26. Заполните шапку табл. 4 в соответствии с приведенными ниже рекомендациями:

Текущая клетка	Набираемый текст
А43	пять пробелов, Удерж. Таб., 13 пробелов, номер
В43	Подоходный налог
С43	Пенсионный налог
D43	Исполнительные листы
E43	Всего

27. Разлинуйте табл. 4 (диапазон ячеек А43:Е50).

28. Заполните таблицу «Лицевой счет» на основании данных, приведенных в табл. 1 (см. стр. 6).

29. Заполните таблицу «Справочник работников» на основании данных, приведенных в табл. 2 (см. стр. 6).

30. Заполните числами колонку B в таблице «Разрядная сетка», используя функцию автозаполнения. Для этого введите «1» в ячейку B13. Затем установите указатель в ячейку B13 на маркер в правом нижнем углу. Указатель мыши примет форму креста. Удерживая клавишу Ctrl и левую клавишу мыши, протащите указатель по диапазону B14:B30. Диапазон ячеек B14:B30 заполнился числами от 1 до 18.

31. Заполните колонку С в таблице «Разрядная сетка». Для этого введите «800» в ячейку C13. Установите указатель в ячейку C13. Затем выберите в меню команду Главная/Редактирование/Прогрес­сия. В появившемся окне Прогрессия выберите расположение «по столбцам», тип «арифметическая», введите в поле ввода Шаг 400, в поле ввода Предельное значение 7600 и щелкните по кнопке OK.

32. Заполните таблицу «Справочник по исполнительным листам» на основании данных, приведенных в табл. 6 (см. стр. 7).

33. Заполните колонку А в формах табл. 3, 4, скопировав в нее табельные номера из таблицы «Лицевой счет».

Для этого выделите диапазон ячеек А3:А9. Нажмите правую кнопку мыши. Выберите в меню команду Копировать. Установите указатель в ячейку А34. Нажмите правую клавишу мыши. Выберите в меню команду Вставить. Затем установите указатель в ячейку А44. Нажмите правую клавишу мыши и выберите в меню команду Вставить.

34. Введите дополнительную информацию.

Текущая клетка	Вводимые данные
E20	Размер мин. з/п
F21
E22	Кол. раб. дней в месяце
F23
E24	% премии
F24	Меньше 5 лет
G24	От 5 до 10 лет
H4	Больше 10 лет
F25
G25
H25
E26	Размер прожит. Мин-ма
F27

35. Установите курсор в клетку B3 и введите формулу заполнения фамилии на основании данных «Справочника работников» (ссылки на ячейки и диапазоны ячеек вводите, выделяя ячейки мышью, для ввода знаков $ нажимайте F4 после ввода каждого диапазона или ссылки, по окончании ввода формулы нажмите Enter):

= ВПР(A3;$K$3:$O$9;2;ложь)

Знак $ фиксирует координаты ячеек и диапазонов (при копировании формул они не изменяются).

В случае возникновения ошибки определите источник возникновения ошибки. Для этого установите указатель в ячейку с формулой и выберите в меню команду Сер­вис/Зависимо­сти/Источ­ник ошибки.

36. Скопируйте формулу определения фамилии в диапазон ячеек B3:B9. Для этого подведите курсор к ячейке В3. Нажмите правую кнопку мыши. Выберите в меню команду Копировать. Выделите диапазон В4:В9. Нажмите правую кнопку мыши. Выберите в меню команду Вставить.

37. Аналогично заполните диапазоны ячеек D3:D9 и E3:E9 (столбцы «Должность» и «Отдел») на основании данных «Справочника работников»).

38. Установите курсор в клетку В34 и наберите на клавиатуре формулу расчета начислений по окладу (текст формулы вводите без переноса в одну строку):

= ВПР(ВПР(A34;$A$3:$J$9;3;ложь);$B$13:$C$30;2; ложь)*

ВПР(A34;$A$3:$J$9;7;ложь) /$F$23

39. Скопируйте формулу начисления ЗП по окладу в диапазон В35:В40.

40. В ячейку С34 введите формулу расчета премии. Размер премии зависит от выслуги лет, определяемой как разность между текущей датой и датой поступления на работу. Соответственно формула для расчета премии будет иметь следующий вид:

=ЕСЛИ((СЕГОДНЯ()-ВПР(A34;$K$3:$O$9;5;ложь))/365<5;В34*$F$25/100;ЕСЛИ((СЕГОДНЯ()-ВПР(A34;$K$3:$O$9;5;ложь))/365<10;В34*$G$25/100; В34*$H$25/100))

Скопируйте формулу в ячейки С35:С40.

41. В ячейку D34 введите формулу расчета начисленной ЗП:

=B34+C34

Скопируйте формулу в ячейки D35:D40.

42. В ячейку H3 введите формулу нахождения начисленной ЗП из таблицы «Ведомость начислений»:

=ВПР(A3;$A$34:$D$40;4;ложь)

Скопируйте формулу в ячейки H4:H9.

43. В ячейку B44 введите формулу расчета подоходного налога:

=(ВПР(A44;$A$3:$J$9;8;ложь)-$F$21*ВПР(A44;$A$3:$J$9;6;

ложь)) *0,12

Скопируйте формулу в ячейки B45:B50.

44. В ячейку С44 введите формулу расчета пенсионного налога:

=ВПР(A44;$A$3:$J$9;8;ложь)*0,01

Скопируйте формулу в ячейки С45:С50.

45. В ячейку D44 введите формулу расчета удержания по исполнительным листам:

=ЕСЛИ(ЕНД(ВПР(A44;$E$13:$F$15;2;ложь));0;(ВПР(A44;

$A$3:$J$9;8;ложь)-B44)*ВПР(A44;$E$13:$F$15;2;ложь)/100)

Скопируйте формулу в ячейки D45:D50.

46. В ячейку Е44 введите формулу расчета общей суммы удержания: =B44+C44+D44

Скопируйте формулу в ячейки E45:E50.

47. В ячейку I3 введите формулу нахождения общей суммы удержания из таблицы «Ведомость удержаний»:

=ВПР(A3;$A$44:$E$50;5;ложь)

Скопируйте формулу в ячейки I4:I9.

48. Вычислите сумму к выдаче с помощью формулы массивов {=H3:H9 - I3:I9}. Для этого выделите блок J3:J9, нажмите клавишу =, выделите блок H3:H9, нажмите клавишу «-», выделите блок I3:I9, нажмите клавиши Ctrl +Shift+ Enter.

49. Используя автосуммирование, рассчитайте итоги в табл.1. Для этого в ячейку А10 введите текст «Итого:», установите указатель в ячейку H10 и нажмите кнопку пиктографического меню Главная/Редактирование/ сумма. (Знак суммы ∑). Если выбранный командой блок окажется верным – H3:H9, нажмите Enter. В противном случае выделите блок H3:H9, нажмите Enter.

Повторите указанные действия для ячеек I10, J10.

50. Рассчитайте сумму начисленной заработной платы по отделу 1. Для этого в ячейку I11 введите «Итого по отделу 1». В ячейку J11 введите формулу:

=СУММЕСЛИ(E3:E9;1;J3:J9)

51. Самостоятельно введите в ячейку J12 формулу для расчета суммарной начисленной заработной платы по отделу 2.

52. Рассчитайте количество работников отдела 1. Для этого в ячейку I13 введите «Работает в 1 отделе». В ячейку J13 введите формулу:

=СЧЕТЕСЛИ(E3:E9;1)

53. Самостоятельно введите в ячейку J14 формулу для расчета количества работников отдела 2.

54. Аналогично рассчитайте суммарную начисленную заработную плату и количество работников по каждой должности.

55. Отформатируйте колонки с результатами вычислений по формулам. Для этого выделите диапазон B36:D40 и нажимайте кнопку пиктографического меню ”Уменьшить разрядность” (см. Главная, раздел Число), до тех пор, пока в форматируемых областях не окажутся целые значения. Повторите указанные действия для диапазонов B46:Е50, H5:J10.

56. Выполните выравнивание. Для этого выделите диапазон A2:J9 и нажмите кнопку пиктографического меню По центру. Повторите указанные действия для диапазонов H10:J10, E12:F15, B33:D33, A34:D40, B43:E43, A44:E50.

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

· выделите диапазон несмежных ячеек (выделить первый диапазон, и затем, удерживая клавишу Ctrl, выделите остальные) – A1, K1, B11, E11, A32, A42;

· выберите из меню команду Главная/ Формат/Формат Ячеек;

· на вкладке Шрифт выберите из списка «Шрифт» Arial Cyr, начертание – курсив, размер – 14, из списка «Цвет» – синий;

· щелкните по кнопке OK.

58. Измените цвет символов шрифта в справочных таблицах с синего на зеленый, используя кнопку пиктографического меню Цвет шрифта (см. Главная, раздел Шрифт).

59. Выполните оформление таблиц цветом и узором. Для этого:

· выделите диапазон несмежных ячеек A2:J2, A33:D33, A43:E43;

· выберите в меню Главная, раздел Шрифт цвет заливки «голубой»;

· щелкните по кнопке OK.

Для диапазонов А3:А9, А34:А40, А44:А50 установите цвет «бледно голубой».

Самостоятельно установите любой цвет фона для диапазонов B12:C12, Е12:F12.

Если выбранное оформление не понравилось, отмените его. Для этого нажмите кнопку пиктографического меню Цвет заливки и выберите «нет заливки».

60. Выполните условное форматирование для диапазона ячеек J3:J9. Если значение суммы З/П к выдаче меньше прожиточного минимума, то необходимо значение вывести красным цветом с двойным подчеркиванием, иначе значение выводить синим цветом.

Для этого необходимо:

· выделить диапазон J3:J9;

· выбрать в меню команду Главная /Условное форматирование (см. раздел Стили);

· в диалоговом окне команды Условное форматирование для формирования условия форматирования выбрать Создать правило, затем «форматировать ячейки» группе в первом поле ввода из списка «значение», во втором – операцию «меньше», а в третье поле ввода ввести формулу, ссылку на ячейку $F$27;

· щелкнуть по кнопке Формат;

· в диалоговом окне Формат ячеек на вкладке Шрифт в списке Подчеркивание выбрать «двойное по значению», а в списке Цвет выбрать красный;

· в поле для второго операнда (после «и») в диалоговом окне команды “Условное форматирование” для формирования второго условия форматирования выбрать в первом поле ввода из списка «значение», во втором – операцию «больше или равно», а в третье поле ввода ввести формулу, ссылку на ячейку =$F$27;

· щелкнуть по кнопке Формат;

· в диалоговом окне Формат ячеек на вкладке Шрифт в списке Цвет выбрать синий;

· щелкнуть по кнопке OK.

61.Для диапазона ячеек G3:G9 установите следующие форматы: если работник проработал целый месяц вывести значение зеленым цветом, если проработал меньше месяца вывести значение оранжевым цветом с одинарным подчеркиванием.

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

1. Как ввести формулу?

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

3. Как распространить формулу?

4. Как вставить формулу с помощью Мастера функций?

5. Какие ссылки вы знаете?

Дата защиты ____________ подпись преподавателя______________