Санкт-Петербургский государственный электротехнический

Санкт-Петербургский государственный электротехнический

университет ”ЛЭТИ”

ИНФОРМАТИКА

 

Методические указания

К лабораторным работам

В среде табличного процессора

EXCEL 2007

 

 

для студентов, магистрантов и аспирантов

всех специальностей

факультета экономики и менеджмента

 

 

Санкт-Петербург

Издательство СПбГЭТУ “ЛЭТИ”

 

 

Утверждены редакционно-издательским советом СПбГЭТУ

“ЛЭТИ”

 

СОСТАВИТЕЛИ

 

кандидат технических наук, доцент И..Б. Никифоров

ассистент С.А.Кожевников

 

Рецензенты:

 

кандидат технических наук, доцент А.В. Тимофеев

кандидат технических наук, доцент К.П.Голоскоков (ИНЖЭКОН)

 

Подготовлено

на кафедре менеджмента и систем качества

 

Печатается в авторской редакции

СОДЕРЖАНИЕ

 

ВВЕДЕНИЕ.......................................................................................4

ЛАБОРАТОРНАЯ РАБОТА № 1. 7

Создание и оформление таблиц на одном.. 7

рабочем листе. 7

 

ЛАБОРАТОРНАЯ РАБОТА № 2. 24

Графическое представление табличных данных. 24

 

ЛАБОРАТОРНАЯ РАБОТА № 3. 38

Структурирование, консолидация данных, 38

построение сводных таблиц и диаграмм.. 38

 

ЛАБОРАТОРНАЯ РАБОТА № 4. 57

Использование сценариев модели “что-если”, 57

средств подбора параметра и поиска решения. 57

для анализа данных. 57

 

ЛАБОРАТОРНАЯ РАБОТА № 5. 69

Создание, редактирование и использование шаблонов. 69

 

ЛАБОРАТОРНАЯ РАБОТА № 6. 77

Математические функции МОБР, МОПРЕД и МУМНОЖ. 77

Запись макросов с помощью макрорекордера. 77

и способы выполнения макросов. 77

Список литературы.. 93

ВВЕДЕНИЕ

 

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

Версия Microsoft Office Excel 2007, помимо новых возможностей, отличается еще и новым интерфейсом, а, следовательно, и новыми методами и приемами работы.

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

Основное новшество интерфейса программы Microsoft Excel 2007 – лента. Это широкая полоса, которая расположена в верхней части окна и содержит все команды. На ленте выделены основные задачи для каждого приложения, а каждая задача представлена вкладкой. С помощью ленты можно быстро находить необходимые команды, которые упорядочены в логические группы, собранные на вкладках. Каждая вкладка связана с видом выполняемого действия. Чтобы увеличить рабочую область, некоторые вкладки выводятся на экран только по мере необходимости.

Чтобы можно было исследовать большие объемы данных на листах, Office Excel 2007 поддерживает листы размером до одного миллиона строк и 16-ти тысяч столбцов. Так сетка Office Excel 2007 состоит из 1 048 576 строк и 16 384 столбцов, что обеспечивает увеличение числа строк на 1500% и столбцов на 6300% по сравнению с приложением Microsoft Office Excel 2003.

Основные технические характеристики и ограничения листа и книги MS Office EXCEL 2007

Параметр	Максимальное значение
Количество открытых книг	Ограничено объемом доступной оперативной памяти и ресурсами системы
Количество листов в книге	Ограничено объемом доступной оперативной памяти (по умолчанию 3 листа)
Общее количество знаков в ячейке	32 767 знаков
Количество числовых форматов в книге	От 200 до 250, в зависимости от установленной языковой версии Excel
Пределы масштабирования	от 10 до 400 процентов
Количество уровней отмены
Количество вычисляемых ячеек в надстройке «Поиск решения»
Количество цветов в книге	16 миллионов цветов (32-битовый спектр с полным доступом к 24-битовому спектру)

 

ЛАБОРАТОРНАЯ РАБОТА № 1

Создание и оформление таблиц на одном

Рабочем листе

 

Цель лабораторной работы

Лабораторная работа служит для получения практических навыков по созданию простых таблиц:

· ввод данных (констант и формул) в таблицу, в том числе использование автозаполнения;

· редактирование рабочего листа (копирование, перемещение, удаление и редактирование данных);

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

 

Основные сведения о построении формул

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

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

В формулах используются следующие арифметические операторы: ^ возведение в степень, * умножение, / деление, + сложение, - вычитание;

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

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

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

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

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

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

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

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

 

 

СУММЕСЛИ

Функция СУММЕСЛИ суммирует ячейки, отвечающие заданному критерию.

СУММЕСЛИ(диапазон;условие;диапазон_суммирования)

Диапазон – определяет интервал вычисляемых ячеек.

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

Диапазон_суммирования – фактические ячейки для суммирования. Суммируются те ячейки диапазона, которые удовлетворяют условию. Если диапазон суммирования отсутствует, то суммируются ячейки аргумента «диапазон».

СЧЕТЕСЛИ

Функция СЧЕТЕСЛИ подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих заданному критерию.

СЧЕТЕСЛИ(диапазон;критерий)

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

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

ВПР

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

ВПР(искомое_значение;табл_массив;номер_столбца;интерваль­ный_просмотр)

Искомое_значение – это значение, которое должно быть найдено в первом столбце таблицы. Искомое_значение может быть значением, ссылкой или текстовой строкой.

Табл_массив – это таблица с информацией, в первом столбце которой ищется искомое значение.

Номер_столбца – это номер столбца в таблице, из которого должно быть взято соответствующее значение.

Интервальный_просмотр – это логическое значение, которое определяет, нужно ли искать точное или приближенное значение. Если этот аргумент имеет значение ИСТИНА или опущен и точное значение не найдено, то возвращается приблизительно соответствующее значение, а именно: наибольшее значение, которое меньше, чем искомое_значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ВПР ищет точное значение. Если таковое не найдено, то возвращается значение ошибки #Н/Д.

ЕСЛИ

Функция ЕСЛИ возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь)

Логическое_выражение – это любое выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.

Значение_если_истина – это значение, которое возвращается, если логическое_выражение имеет значение ИСТИНА. Если логическое_выражение имеет значение ИСТИНА и значение_если_истина опущено, то возвращается значение ИСТИНА. Значение_если_истина может быть другой формулой.

Значение_если_ложь – это значение, которое возвращается, если логическое_выражение имеет значение ЛОЖЬ. Если логичес­кое_выражение имеет значение ЛОЖЬ и значение_если_ложь опущено, то возвращается значение ЛОЖЬ. Значение_если_ложь может быть другой формулой.

ЕНД

Функция ЕНД проверяет значение ячейки.

ЕНД(значение)

Если значение ячейки ошибка #Н/Д, то функция возвращает значение ИСТИНА, в противном случае – ЛОЖЬ.

 

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

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

 

Таблица 1

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

 

Таблица 2

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

Таблица 3

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

 

Таблица 4

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

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

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

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

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

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

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

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

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

- удержания по исполнительным

листам У _ил = (ЗП - У _пн) * %ИЛ;

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

 

где:

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

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

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

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

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

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

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

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

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

 

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

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

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

 

Формирование таблиц

 

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

Примечание. В дальнейшем подобная информация будет выводиться в виде: выполните команду Главная/Выравнивание/Объе­динить и поместить в центре.

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

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

· выполните команду Главная/Выравнивание/Перенос текста;

5. Выполните команды Главная/Выравнивание/Выровнять по середине и Главная/Выравнивание/По центру.

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

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

 

7. Разлинуйте табл. 1. Для этого:

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

· выполните команду Главная/Шрифт, щелкните стрелку рядом с кнопкой Рамки , а затем выберите пункт Все границы.

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

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

 

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

 

10. Разлинуйте табл. 5. Для этого:

· выделите диапазон ячеек В12:C30;

· щелкните правой кнопкой мыши и выберите пункт Формат ячеек;

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

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

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

 

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

 

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

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

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

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

 

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

 

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

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

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

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

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

· выполните команду Главная/Ячейки/Формат/Ширина столбца;

· в окне Ширина столбца введите значение 10 и нажмите ОК.

21. В ячейку А33 введите границу, разделяющую ячейку по диагонали. Для этого выполните команду Главная/Шрифт, щелкните стрелку рядом с кнопкой Рамки , выберите пункт Другие границы, в диалоговом окне Формат ячеек на вкладке Граница в группе Отдельные выберите тип линии – диагональ (слева вниз направо). На вкладке Главная в группе Выравнивание нажмите кнопки пиктографического меню По верхнему краю и Выровнять текст по левому краю.

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

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

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

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

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

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

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

 

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

 

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

 

Ввод в таблицу формул

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

=ВПР(А3;$L$3:$P$9;2;ложь)

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

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

37. Скопируйте формулу определения фамилии в диапазон ячеек B4:B9. Для этого выделите ячейку, содержащую копируемую формулу, а затем перетащите маркер заполнения по диапазону, который нужно заполнить.

Примечание. Маркер заполнения – это небольшой черный квадрат в правом нижнем углу выделенной ячейки или диапазона . При наведении на маркер заполнения указатель принимает вид черного креста.

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

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

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

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

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

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

=ЕСЛИ((СЕГОДНЯ()‑ВПР(A34;$L$3:$P$9;5;ЛОЖЬ))/365<5;

B34*$F$25/100;ЕСЛИ((СЕГОДНЯ()‑ВПР(A34;$L$3:$P$9;5;

ЛОЖЬ))/365<10;B34*$G$25/100;B34*$H$25/100))

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

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

=B34+C34

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

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

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

=(ВПР(A44;$A$3:$J$9;8;ЛОЖЬ)-$F$21*ВПР(A44;$A$3:$J$9;6; ЛОЖЬ))*0,12

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

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

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

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

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

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

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

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

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

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

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

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

50. Используя автосуммирование, рассчитайте итоги в табл.1. Для этого в ячейку А10 введите текст «Итого:», установите указатель в ячейку H10 и выполните команду Формулы/Библиотека функций/Автосумма. Если выбранный командой блок окажется верным – H3:H9, нажмите Enter. В противном случае выделите блок H3:H9 и нажмите Enter.

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

 

Получение итоговых данных

 

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

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

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

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

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

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

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

 

Стилевое оформление таблиц

 

56. Отформатируйте колонки с результатами вычислений по формулам. Для этого выделите диапазон B36:D40 и на вкладке Главная в группе Число нажимайте кнопку пиктографического меню Уменьшить разрядность до тех пор, пока в форматируемых областях не окажутся целые значения. Повторите указанные действия для диапазонов С36:С40; D36:D40. Аналогично уменьшите разрядность в соответствующих столбцах таблиц «Ведомость удержаний» и «Лицевой счет».

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

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

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

· выберите из контекстного меню команду Формат ячеек;

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

· нажмите OK.

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

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

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

· выберите из контекстного меню команду Формат ячеек;

· на вкладке Шрифт выберите цвет «синий», а на вкладке Заливка узор для заполнения «тонкий, перевернутый, диагональный, штриховой»;

· нажмите OK.

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

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

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

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

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

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

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

· на вкладке Главная в группе Стили нажать Условное форматирование;

· в диалоговом окне команды Условное форматирование выбрать Правила выделения ячеек и выбрать Другие правила;

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

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

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

· нажать ОК;

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

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

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

· дважды нажать OK.

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

63. Сохраните результаты лабораторной работы в файле lab1.xlsx.

ЛАБОРАТОРНАЯ РАБОТА № 2

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

23. На новом листе «График_Ганта» постройте простую диаграмму Ганта, отображающую во времени начала работ проекта и их продолжительность. Исходные данные содержатся в приведенной ниже таблице.

 

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

· выделите диапазон ячеек А2:В8 и выполните команду Вставка / Диаграммы / Линейчатая/ Линейчатая с накоплением;

· добавьте на диаграмму данные о продолжительности работ. Для этого выполните команду Конструктор/Данные/Вы­брать данные и в окне Выбор источника данных нажмите кнопку Добавить. В диалоговом окне Изменение ряда в поле Имя ряда щелчком мыши введите ссылку на ячейку С2 с именем «Продолжительность в днях», а в поле Значения введите мышью ссылку на диапазон ячеек С3:С8 с данными о продолжительности работ;

· два раза нажмите ОК;

· выделите ряд «Начало работы», вызовите контекстное меню и выберите команду Формат ряда данных. В левой части появившегося окна выберите Заливка, а в правой – Нет заливки;

· щелкните по кнопке Закрыть;

· вызовите контекстное меню вертикальной оси (категорий), и выберите команду Формат оси;

· в окне Формат оси в группе Параметры оси установите флажок обратный порядок категорий, а в группе Горизонтальная ось пересекает – флажок в максимальной категории. Нажмите кнопку Закрыть;

· вызовите контекстное меню легенды и удалите ее;

· вызовите контекстное меню горизонтальной оси (значений), щелкнув левой клавишей по одной из дат. В диалоговом окне Формат оси в группе Параметры оси измените минимальное значение с «авто» на «фиксированное», введя порядковый номер даты 01.02.11 в виде числа 40575; максимальное значение с «авто» на «фиксированное», введя порядковый номер даты 02.04.09 в виде числа 40635; цену основных делений введите 10, а цену промежуточных делений – 2;

· нажмите кнопку Закрыть;

· введите название диаграммы по команде Макет/Подписи /Название диаграммы/Над диаграммой;

· отредактируйте размеры шрифтов отдельных элементов и размеры области диаграммы.

 

Трендовый анализ

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

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

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

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

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

При подборе линии тренда к данным Excel автоматически рассчитывает значение R ². Можно отобразить это значение на диаграмме.

24. Вставьте новый лист Рабочей книги Excel и переименуйте его в «Линия_тренда».

25. Введите приведенную ниже таблицу.

 

 

26. Для этой таблицы сначала постройте диаграмму График. Для этого выделите диапазон ячеек A3:B9 и выполните команду Вставка/Диаграммы/График/График.

27. Для этого графика постройте линию тренда. Для этого:

· выделите график и из контекстного меню выберите команду Добавить линию тренда;

· в диалоговом окне Параметры линии тренда выберите вид линии тренда: полиномиаль­ная 4-й степени;

· в этом же окне установите: прогноз вперед на 1 период и поместить на диаграмму величину достоверности аппроксимации;

· нажмите кнопку Закрыть;

· покажите на диаграмме линии проекции по команде Макет/Анализ/Линии/Линии;

· удалите легенду;

· с помощью контекстного меню Формат линии тренда установите цвет линии – красный;

· введите название диаграммы «Динамика спроса », заливка текста произвольная;

· нажмите кнопку ОК.

28. Сохраните результаты лабораторной работы в файле с именем lab2.xlsx.

ЛАБОРАТОРНАЯ РАБОТА № 3

Цель лабораторной работы

Лабораторная работа служит для получения практических навыков по изучению следующих тем:

· создание и ведение списков;

· операции со списками (сортировка, фильтрация);

· манипулирование данными, расположенными на разных листах рабочей книги;

· консолидация данных, расположенных на разных листах рабочей книги;

· построение сводных таблиц и сводных диаграмм;

· структура таблицы (создание и удаление).

 

Основные сведения о списках, структуре рабочего листа, консолидации и сводных таблицах

Список – это таблица, содержащая упорядоченный набор данных, база данных на рабочем листе.

Столбцы списка называются полями, строки – записями.

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

· каждый столбец должен содержать информацию одного типа;

· одна или две верхние строки списка должны содержать мнемонические названия столбцов, эти строки называют «строками заголовка списка»;

· список не должен содержать пустые строки и столбцы без названия, такие строки и столбцы делят список на два;

· для списка обычно отводится отдельный лист.

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