Курс «Информатика» - 2016

Лабораторная работа №2

Тема: РАБОТА В MS EXCEL

Задание к лабораторной работе

Основная часть (max 6 баллов)

Выполните задание в соответствии со своим вариантом (вариант получить у преподавателя, задание см. ниже). При необходимости обратитесь к приведенным здесь примерам и разъяснениям.

ПРИМЕРЫ

Примечание. Черным цветом шрифта оформлены примеры заданий, фиолетовым – комментарии по их выполнению.

1. В качестве примера рассмотрим таблицу для начисления штрафов нерадивым водителям (см. рисунок).

2. Составьте формулу расчета значений в столбце Количество дней просрочки платежа. Если платеж был осуществлен в срок, отведенный для оплаты, то просрочка = 0.

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

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

Синтаксис:

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

Аргументы функции ЕСЛИ:

• логическое выражение – проверяемое условие в виде логического выражения, дающего в результате значение ИСТИНА или ЛОЖЬ;

• значение если истина – значение (или формула для вычисления значения), возвращаемое функцией ЕСЛИ в случае, когда логическое выражение выполняется (ИСТИНА);

• значение если ложь – значение (или формула для вычисления значения), возвращаемое функцией ЕСЛИ в случае, когда логическое выражение не выполняется (ЛОЖЬ).

Пример:

=ЕСЛИ(А2=0;100;1)

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

Если условие А2=0 выполняется, то в ячейку, в которой составлена эта формула (в текущую активную ячейку), записать значение 100, иначе (если условие не выполняется) записать 1.

В том случае, когда с помощью функции ЕСЛИ требуется провести более сложную проверку с несколькими логическими выражениями, то используется конструкция вложенных функций ЕСЛИ (в качестве аргументов "значение_если_истина" и "значение_если_ложь" можно использовать до 64 вложенных функций ЕСЛИ). Кроме того, для проверки многих условий можно использовать функции ПРОСМОТР, ВПР, ГПР и ВЫБОР.

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

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

Пример:

=ЕСЛИ(А2=0;100;ЕСЛИ(А2=10;-100;1))

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

Если условие А2=0 выполняется, то в ячейку, в которой составлена эта формула (в текущую активную ячейку), записать значение 100, иначе (если первое условие не выполняется) проверить второе условие: если условие А2=10 выполняется, то в текущую ячейку записать значение -100, иначе (если и первое, и второе условия не выполняются) записать 1.

---

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

3. Столбец Пени заполните расчетными формулами на основе правил расчета из диапазона A1:B5, составив единую формулу для всех строк таблицы. В формуле обязательно использовать ячейки B2:B5 – при изменении значений в этом диапазоне результаты вычислений в таблице должны автоматически изменяться.

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

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

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

Синтаксис:

=И(логическое_значение1; логическое_значение2;…)

При использовании функции И в качестве аргумента:

=ЕСЛИ(И(логическое_значение1; логическое_значение2;…))

Аргументы функции И:

• логическое_значение1 – первое проверяемое условие, вычисление которого дает значение ИСТИНА или ЛОЖЬ;

• логическое_значение2 – дополнительное проверяемое условие, вычисление которого дает значение ИСТИНА или ЛОЖЬ. Дополнительных условий может быть не более 255.

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

Пример:

Ячейка D1 содержит формулу =СУММ(B1:B4).

При копировании этой формулы в ячейку D2 (ниже) новая формула будет ссылаться на соответствующие строки (тот же столбец, но на одну строку ниже):

При копировании исходной формулы в ячейку E1 (вправо) новая формула будет ссылаться на соответствующие ячейки столбца E (те же строки, но на один столбец правее):

Если копируемые формулы содержат абсолютные адреса ячеек (абсолютные ссылки на ячейки), то ссылки в скопированных формулах не изменятся:

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

---

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

Для быстрого изменения формата ссылки можно использовать клавишу F4, которая последовательно переключает виды ссылок в формуле: A1$A$1A$1$A1. Если при копировании не достигнут предполагаемый результат, можно изменить ссылки в исходных формулах на относительные или абсолютные, а затем снова скопировать ячейки.

4. С помощью формул подсчитайте:

- итоговые суммы по выделенным столбцам (строка 20);

- максимальный и минимальный штрафы, среднюю сумму к оплате (результаты запишите ниже);

- количество штрафов, выписанных водителю Гришаину П.О. (результат запишите ниже).

Для выполнения этого задания необходимо использовать следующие функции: СУММ, МАКС, МИН, СРЗНАЧ, СЧЁТЕСЛИ.

Функция СУММ вычисляет сумму всех чисел, указанных в качестве аргументов. Каждый аргумент может быть диапазоном ячеек, ссылкой на ячейку, массивом, константой, формулой или результатом другой функции.

Синтаксис:

=СУММ(число1;число2;…)

Пример:

=СУММ(A4:C6;D6)

Приведенная в примере формула позволит вычислить сумму значений всех ячеек в диапазоне A4:C6 и плюс значение ячейки D6.

Функции МАКС, МИН, СРЗНАЧ позволяют в заданном диапазоне определить максимальное, минимальное и среднее арифметическое значения соответственно.

Функция СЧЁТЕСЛИ позволяет подсчитать количество ячеек в диапазоне, которые соответствуют одному указанному пользователем критерию. Например, можно подсчитать количество всех ячеек, которые начинаются с определенной буквы или содержат числа, большие или меньшие указанного значения.

Синтаксис:

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

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

Для выполнения этого задания необходимо использовать возможности Специальной вставки.

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

Для использования специальной вставки необходимо выделить нужный диапазон ячеек и скопировать его в буфер обмена. Далее на вкладке Главная в группе Буфер обмена в меню кнопки Вставить выбрать пункт Специальная вставка и в открывшемся диалоговом окне выбрать параметры вставки. Если выбран пункт «Вставить → все», то специальная вставка работает так же, как обычная вставка.

ВАРИАНТ 1

1. Создайте электронную таблицу (рабочую книгу MS Excel) из 4-х рабочих листов и назовите рабочие листы:

Таблица, График, Подбор параметра, Доп.

---

Сохраните рабочую книгу в папке H:\Lab2\

2. На листе Таблица создайте таблицу для начисления заработной платы сотрудников (см. рисунок). ФИО сотрудников могут быть вымышленными.

3. Сотрудники цеха получают сдельную оплату труда в зависимости от количества изготовленных изделий, тариф за одно изделие составляет 500 рублей. Остальные сотрудники получают оклад, равный тарифу. Заполнить столбец Оклад единой формулой «ЕСЛИ» для всех сотрудников с учетом отдела.

4. Столбец Премия, % заполнить на основе правил расчета из диапазона B1:C5, составив единую формулу «ЕСЛИ» для всех строк таблицы (в формуле обязательно использовать ячейки C2:С5 – проценты будут меняться при проверке работы; при необходимости использовать абсолютные адреса). Премия рассчитывается по следующему принципу:

• если оклад <= 20000 руб., то премия составляет 5% от оклада;

• если оклад от 20000 до 25000 руб. (20000<…<=25000), то премия составляет 8% от оклада;

• если оклад от 25000 до 30000 руб. (25000<…<=30000), то премия составляет 10% от оклада;

• если оклад >30000, то премия составляет 6% от оклада.

5. В столбце Премия, руб. вычислить сумму премии, составив единую формулу «ЕСЛИ» для всех строк таблицы (в формуле обязательно использовать ячейки C2:С5 – проценты будут меняться при проверке работы).

6. Столбцы Доход, Начисления, Сумма к выдаче заполнить расчетными формулами. Сумма к выдаче рассчитывается как Доход – Начисления.

7. Отформатируйте таблицу по своему усмотрению, но так, чтобы максимальное количество данных было видно на экране.

8. С помощью соответствующих формул подсчитайте:

- итоговые суммы по отмеченным в образце столбцам (строка 19);

- максимальный и минимальный оклады, средний доход по фирме (результаты запишите в строках 21-23);

- количество сотрудников цеха (результат запишите в строке 24).

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

9. Сделайте копию основной таблицы (диапазон А7:К17) ниже исходной (вставьте только значения – исходная таблица будет меняться при проверке работы), отформатируйте копию таблицы. Отсортируйте копию таблицы по фамилиям и по сумме к выдаче в порядке возрастания (это одна «двойная» сортировка ). Перед отсортированной копией поместите соответствующий заголовок.

ВАРИАНТ 2

1. Создайте электронную таблицу (рабочую книгу MS Excel) из 4-х рабочих листов и назовите рабочие листы:

Таблица, График, Подбор параметра, Доп.

Сохраните рабочую книгу в папке H:\Lab2\

1. На листе Таблица создайте таблицу с результатами сдачи нормативов по физкультуре (см. рисунок). ФИО студентов могут быть вымышленными.

2. Столбцы Количество «2» (количество двоек), Количество «н» (количество прогулов) заполните расчетными формулами.

3. Столбец Бонус, баллов заполнить на основе правил из диапазона B1:С5, составив единую формулу «ЕСЛИ» для всех строк таблицы. В формуле обязательно использовать ячейки C2:C5 (их значения будут меняться при проверке работы), при необходимости использовать абсолютные адреса. Бонус рассчитывается по следующему принципу:

---

• если студент не пропустил ни одного занятия, то бонус составляет 3 балла;

• если студент пропустил 1 занятие, то бонус составляет 2 балла;

• если студент пропустил 2 занятия, то бонус составляет 1 балл;

• если студент пропустил 3 и более занятий, то он не получает бонуса.

4. Столбец Всего баллов рассчитывается как сумма баллов по всем нормативам плюс бонусные баллы.

5. Столбец Зачет или пересдача? заполнить на основе правил из диапазона L1:M6, составив единую формулу «ЕСЛИ» для всех строк таблицы. В формуле обязательно использовать ячейки M2:M6 (их значения будут меняться при проверке работы), при необходимости использовать абсолютные адреса. Зачет ставится по следующему принципу:

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

• если студент имеет двойки, но при этом он не пропустил ни одного занятия и сумма баллов за все нормативы выше или равна 12 – студенту назначается пересдача нормативов;

• если студент сдал нормативы без двоек, но при этом пропускал занятия и сумма баллов за все нормативы выше или равна 12 – студент отправляется пересдавать нормативы и отрабатывать прогулы;

• если студент имеет двойки и прогулы, либо если у студента сумма баллов за все нормативы меньше 12 – ставится незачет.

6. Отформатируйте таблицу по своему усмотрению, но так, чтобы максимальное количество данных было видно на экране.

7. С помощью соответствующих формул подсчитайте:

- Средний балл для каждого норматива (строка 22);

- максимальную и минимальную сумму баллов, среднее количество двоек и прогулов (результаты запишите в строках 24-27);

- количество студентов, отправляющихся на отработку прогулов (результат запишите в строке 28).

8. Сделайте копию основной таблицы (диапазон A10:M20) ниже на этом же листе (вставьте только значения), отформатируйте копию таблицы. Отсортируйте копию таблицы по сумме баллов и по фамилиям студентов в порядке возрастания (это одна «двойная» сортировка ). Перед отсортированной копией поместите соответствующий заголовок.

ВАРИАНТ 3

1. Создайте электронную таблицу (рабочую книгу MS Excel) из 4-х рабочих листов и назовите рабочие листы:

Таблица, График, Подбор параметра, Доп.

Сохраните рабочую книгу в папке H:\Lab2\

1. На листе Таблица создайте таблицу для расчетов с арендаторами (см. рисунок). Названия компаний и ФИО контактных лиц могут быть вымышленными.

1. Цена аренды зависит от арендуемого зала. Тариф за один час аренды каждого из залов приведен в (ячейках B1:C7). Заполнить столбец Цена аренды по тарифу, руб. на основе правил расчета цены из диапазона B1:C7, составив единую формулу «ЕСЛИ» для всех строк таблицы, при необходимости использовать абсолютные адреса (в формуле обязательно использовать ячейки C2:С7 – их значения будут меняться при проверке работы).

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

3. Заполнить столбец Скидка, % на основе правил расчета скидки из диапазона E1:F5, составив единую формулу «ЕСЛИ» для всех строк таблицы, при необходимости использовать абсолютные адреса (в формуле обязательно использовать ячейки F2:F5 – проценты будут меняться при проверке работы). Скидка рассчитывается по следующему принципу:

---