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

На Листе1 в ячейку D12 ввести число 25. В ячейку С15 ввести формулу: =D12*2. Т.к. в формуле, содержащейся в ячейке С15, имеется относительная ссылка на ячейку D12, то Excel воспринимает эту формулу следующим образом: значение ячейки, находящейся одним столбцом правее меня и тремя строками выше меня, умножить на два. Скопировать формулу из ячейки С15 в ячейки С16, С20, D15, F15, E18. Проанализировать, как изменилась формула при копировании!!!

На Листе2 в ячейку D12 ввести число 25. В ячейку С15 ввести формулу: =$D$12*2. Т.к. в формуле, содержащейся в ячейке С15, имеется абсолютная ссылка на ячейку D12, то Excel воспринимает эту формулу следующим образом: значение ячейки D12 умножить на два. Скопировать формулу из ячейки С15 в ячейки С16, С20, D15, F15, E18. Проанализировать, как изменилась формула при копировании!!!

На Листе3 в ячейку D12 ввести число 25. В ячейку С15 ввести формулу: =D$12*2. Т.к. в формуле, содержащейся в ячейке С15, имеется cмешанная ссылка на ячейку D12 (относительная ссылка на столбец и абсолютная ссылкана строку), то Excel воспринимает эту формулу следующим образом: значение ячейки, находящейся одним столбцом правее меня в 12 строке, умножить на два. Скопировать формулу из ячейки С15 в ячейки С16, С20, D15, F15, E18. Проанализировать, как изменилась формула при копировании!!!

На Листе4 в ячейку D12 ввести число 25. В ячейку С15 введите формулу: =$D12*2. Т.к. в формуле, содержащейся в ячейке С15, имеется смешанная ссылка на ячейку D12 (абсолютная ссылка на столбец и относительная ссылка на строку), то Excel воспринимает эту формулу следующим образом: значение ячейки, находящейся в столбце D тремя строками выше меня, умножить на два. Скопировать формулу из ячейки С15 в ячейки С16, С20, D15, F15, E18. Проанализировать, как изменилась формула при копировании!!!

14. Создание простой таблицы. Разработать ведомость заработной платы.

· На Листе5 в ячейку А1 занести текст «Минимальный размер оплаты труда, руб.»

· В ячейку А2 занести число 720

· В ячейку А4 занести текст «Ведомость заработной платы кафедры экономики.

· В ячейки A6:K6 занести шапку таблицы в следующем виде:

№ п/п

фио

Разряд

Оклад

Надбавка

КТУ

Премия

Общий заработок

Подоходный налог

Налог в ПФ

К выдаче

КТУ – Коэффициент трудового участия

· В ячейки А7:А13 занести порядковые номера, используя автозаполнение.

· Заполнить произвольно столбцы ФИО и Разряд (целые числа в диапазоне от 4 до 17).

· В ячейку А14 занести текст «Итого».

· В ячейку А16 занести текст «Премиальный фонд, руб.».

· В ячейку А17 занести число 100000.

· В ячейку D7 занести формулу для вычисления оклада сотрудника (Оклад = МРОТ * Разряд). (Внимание! МРОТ для всех сотрудников один и содержится в конкретной ячейке А2, а разряд у каждого сотрудника свой. При составлении формулы используйте верный тип адресации). Скопируйте формулу из ячейки D7 в ячейки, содержащие оклады остальных сотрудников.

· В ячейке D14 вычислить общую сумму окладов, для чего выделить блок D7:D14 и выбрать на панели инструментов пиктограмму Автосуммирование – . Скопировать полученную формулу в E14:K14.

· Занести в ячейку E7 формулу для вычисления надбавки сотрудника в размере 30% от его оклада, затем скопировать ее в ячейки, содержащие надбавки остальных сотрудников.

· В ячейки F7:F13 ввести произвольные значения КТУ (целые числа от 1 до 5).

· В ячейку G7 ввести формулу для вычисления премии (Премия = Премиальный фонд *КТУ / Общая сумма КТУ). При составлении формулы обратить внимание на вид адресации. Адреса каких ячеек не должны меняться при копировании формулы? Скопировать ее в другие ячейки столбца. Обратите внимание, что итоговая сумма премий всех сотрудников должна быть равна премиальному фонду. Измените значение премиального фонда в ячейке А17 со 100000 на 250000. Что изменилось в таблице и почему?

· Заполнить остальные графы таблицы. Подоходный налог берется в размере 13%, а в пенсионный фонд отчисляется 1% от общего заработка.

· Отформатировать таблицу. В нужных местах числовые данные представить в денежном формате. Изменить ширину столбцов, ширину строк, задать цветовое оформление таблицы, рамку (использовать насколько типов рамок), изменить шрифт для наиболее выгодного, с вашей точки зрения, представления данных на экране.

· Разместить заголовок таблицы по центру. Для этого выделить блок А4:K4 и выбрать пиктограмму Центрировать по выделению - (или меню Формат \ Ячейки, закладка Выравнивание, отображение: объединить ячейки, выравнивание во горизонтали: по центру).

· Поставить защиту на ячейки, содержащие оклады.

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

15. Сохранить рабочую книгу на диске.Следует помнить, что при необходимости можно сохранить файл в нужном формате (например, в формате более ранних версий (Excel 5.0) и др.). В этом случае нужно указать в диалоговом окне при сохранении нужный тип файла.

Лабораторная работа №14. Функции в EXCEL

Цель работы: освоение основных приемов работы с функциями в Excel.

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

Мастер функций упрощает процесс вставки функции в формулу. Для того, чтобы запустить Мастер функций, можно выбрать команду Функция в меню Вставка или воспользоваться пиктограммой панели инструментов Стандартная. Функции сгруппированы по категориям, таким как «Финансовые», «Математические и тригонометрические», «Статистические», «Логические» и др. В нижней части окна мастера функций отображается синтаксис, а также определение выделенной функции.

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

После выбора функции из списка появляется диалоговое окно выбранной функции, которое содержит поля для ввода аргументов. В нижней части окна функции отображается описание того аргумента, в поле ввода которого стоит курсор. Аргументы могут быть числами, текстами, логическими величинами или ссылками. Задаваемые аргументы должны иметь допустимые для данного аргумента значения. Аргументы могут быть как константами, так и формулами. Эти формулы, в свою очередь, могут содержать другие функции. Функции, являющиеся аргументом другой функции, называются вложенными. В формулах Microsoft Excel можно использовать до семи уровней вложения функций.

1. Функции даты и времени. Следуя описанным выше правилам использования встроенных функций Excel, выполнить следующие действия:

· В ячейку А1 вставить функцию СЕГОДНЯ, которая возвращает текущую дату.

· В ячейку В1 вставить функцию ДЕНЬНЕД. В открывшемся окне в качестве аргумента указать адрес А1. Выбрать подходящее значение аргумента Тип. Что возвращает эта функция?

· Определить в какой день недели вы родились?

2. Текстовые функции. Выполнить следующие операции с текстовой информацией:

· В ячейку А3 ввести текст «годовой отчет». В ячейку А4 вставить функцию ПОДСТАВИТЬ. В качестве первого аргумента указать адрес ячейки А3, в качестве второго - «годовой», третьего - «недельный».

· В ячейке А6, используя функцию ПОВТОР, получить текст,состоящий из 50 символов «#».

3. Логические функции.

Функция ЕСЛИ имеет три аргумента.

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

Логическое выражение – это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ (например, А4>8, D3<=B2, F6=12). Если логическое выражение выполняется (т.е. логическое выражение имеет значение ИСТИНА), то функция возвращает значение второго аргумента (значение_если_истина), в противном случае (логическое выражение имеет значение ЛОЖЬ) - значение третьего аргумента (значение_если_ложь). Второго и третьего аргумента может не быть. Тогда функция возвращает значение логического выражения (истина, или ложь).

3.1.Создать таблицу анализа температуры на Листе2.

Ввести заголовок «Анализ среднесуточной температуры за вторую неделю мая».

Создать таблицу:

Таблица 6. – Анализ среднесуточной температуры за вторую неделю мая

День недели	Температура	Анализ
день	ночь	среднесуточная
понедельник
вторник
…
воскресение
Норма среднесуточной температуры в мае

· Названия дней недели ввести, используя автозаполнение. Заполнить произвольными значениями ячейки, содержащие дневные и ночные температуры.

· Вычислить значение ячейки, содержащей среднесуточную температуру понедельника, используя функцию СРЗНАЧ из категории Статистические. Скопировать формулу в ячейки для вторника и т.д.

· Вычислить значение ячейки, содержащую анализ среднесуточной температуры понедельника, используя функцию ЕСЛИ (если среднесуточная температура больше или равна норме, то значение – тепло, иначе – холодно). Скопировать формулу в ячейки для остальных дней недели. (Внимание! Адресация!)

· Сохранить таблицу.

3.2. Создать таблицу анализа поведения курса доллара в течение года на Листе3.

· Ввести заголовок «Анализ поведения курса доллара в течение года»

· Создать шапку таблицы в следующей форме:

Месяц

Средний курс доллара

Поведение курса доллара

· С помощью автозаполнения ввести названия месяцев с января по декабрь.

· Произвольно ввести значения среднего курса доллара по месяцам.

· Вычислить значение ячейки, содержащей поведение курса доллара в феврале, используя функцию ЕСЛИ (если средний курс доллара в текущем месяце больше, чем в предыдущем, то значение - вырос, если меньше – упал, если равен – без изменений). В данном случае функция ЕСЛИ будет содержать одну вложенную функцию ЕСЛИ. Т.к. в задаче имеется три взаимоисключающих условия, то необходимо проверить два из них, если оба проверяемых условия окажутся ложными, то третье – автоматически является истинным. Для того, чтобы сформировать такую функцию необходимо выполнить следующее:

- с помощью Мастера функций вставить функцию ЕСЛИ;

- в диалоговом окне функции ЕСЛИ занести в поле ввода первого аргумента соответствующее логическое выражение, в поле ввода второго аргумента – соответствующее значение;

- поставить курсор в поле ввода третьего аргумента и вставить вложенную функцию ЕСЛИ с помощью кнопки Функция, находящейся слева от строки формул. После этого появится новое окно вложенной функции ЕСЛИ, которое необходимо заполнить.

Просмотрите полученную формулу в строке формул.

· Скопировать формулу для всех остальных месяцев.

· Сохранить таблицу.

3.3. Создать рейтинг-лист группы с нарастающим итогом и выставлением итоговой оценки на Листе4.

· Ввести заголовок «Рейтинг-лист».

· Ввести в ячейку А3 текст – «базовый рейтинг», в ячейку С3 – число 100.

· Ввести шапку таблицы:

ФИО	Баллы за тему	Баллы за тему	Сумма баллов	Оценка	Рейтинг по предмету
тема1	тема2	тема3	тема4

· Заполнить ячейки Ф.И.О., баллы за тему и баллы за экзамен (для 10 человек). За каждую тему можно получить от 0 до 15 баллов, за экзамен - от 0 до 40 баллов.

· Ввести формулу для вычисления суммы баллов.

Оценка выставляется следующим образом: если сумма баллов больше 84, то значение оценки равно 5, иначе, если сумма баллов больше 72, то значение - 4, иначе, если сумма баллов больше 53, то значение - 3, иначе - 2.

Чтобы реализовать эту схему, в ячейку для оценки нужно занести формулу с функцией ЕСЛИ. В качестве первого аргумента ввести условие Сумма баллов> 84. (Внимание! Под суммой баллов подразумевается адрес ячейки, где хранится сумма баллов, например, для первой фамилии из введенного списка). Если полученная сумма больше 84, то есть значение логической функции - ИСТИНА, то по условию оценка должна быть равна 5, таким образом, в качестве второго аргумента нужно ввести значение 5. В противном случае (логическое условие не выполняется) в качестве третьего аргумента необходимо вставить еще одну функцию ЕСЛИ. Для этого надо переключиться в третью строку окна функции и мышью нажать кнопку Функция, находящуюся слева от строки формул. Далее выбрать из предложенного списка функцию ЕСЛИ и аналогично заполнить аргументы для проверки второго логического условия, затем третьего и т.д. В последней вложенной функции в качестве третьего аргумента указать 2. (Внимание! Максимально возможное число вложенных функций в Excel - 7).

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

· Вычислить рейтинг по предмету в соответствии со следующим:

В данном случае в качестве аргументов функции ЕСЛИ будут использоваться формулы.

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

· Под таблицей вычислить среднюю сумму баллов, используя функцию Автосуммирование, и среднюю оценку, используя функцию СРЗНАЧ из раздела Статистические.

· Оформить таблицу.

· Сохранить таблицу с рейтинг-листом на диске.

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

· На Листе5 создать шапку исходной таблицы:

Дата добавления: 2014-12-27