Задания для самостоятельной работы. В рабочей книге каждый лист отвести для решения одной задачи
В рабочей книге каждый лист отвести для решения одной задачи. Каждому листу дать название задание 1, задание 2, …, задание 13.
Отчет по выполненной работе должен содержать распечатку каждой отформатированной расчетной таблицы и таблицы с включенными формулами.
Задание 1
В таблице представлены результаты 5 игр по футболу между двумя студенческими командами. В столбце В приведены число голов, забитых командой Авангард, в столбце С – командой Сатурн. Необходимо в столбце D получить название команды-победительницы каждой игры либо слово ничья.
| Игра | Авангард | Сатурн | Кто выиграл? |
| 15.10.2000 | |||
| 10.10.2001 | |||
| 12.09.2002 | |||
| 9.10.2003 | |||
| 14.09.2004 |
Задание 2
На основе записей в главном журнале бухгалтерии, приведенном в таблице Главный журнал, автоматически выведите баланс (разницу между дебетом и кредитом) по любому счету с начала текущего месяца. Номер счета вводится в отдельную ячейку.
Главный журнал
| Дата | Счет | Дебет | Кредит |
| 1.мар | |||
| 2.мар | |||
| 3.мар | |||
| 4.мар | |||
| 5.мар | |||
| 7.мар | |||
| 8.мар | |||
| 10.мар | |||
| 11.мар | |||
| 12.мар |
Балансовый отчет
| Счет | |
| Дебет | |
| Кредит | |
| Баланс |
Задание 3
Торговый агент получает процент от суммы совершенной сделки по следующим условиям:
- если объем сделки до 3000, то 5 %;
- если объем до 10000, то 2 %;
- если выше 10000, то 1,5 %.
| Торговый агент | Сумма сделки | Вознаграждение |
| Иванов И.И. | ||
| Петров П.П. | ||
| Сидоров О.Б. | ||
| Жукин Л.М. |
Задание 4
К юбилею предприятия планируется начислить премию сотрудникам исходя из их стажа работы:
- если стаж работы от 10 до 20 лет, то 2 оклада;
- свыше 20 лет, то 3 оклада.
| Сотрудник | Дата устройства на работу | Оклад | Премия |
| Иванов И.И. | 12.08.1983 | ||
| Петров П.П. | 12.05.2001 | ||
| Сидоров О.Б. | 23.05.1992 | ||
| Жукин Л.М. | 16.12.1977 |
Стаж рассчитать, используя функции работы с датами.
Задание 5
По результатам сданной сессии начислите студентам стипендию, если:
- все отличные оценки – максимальная стипендия;
- сессия сдана на 4 и 5 – средняя стипендия;
- все оценки хорошо – минимальная стипендия.
| Студент | Высш. матем. | Физика | История | Стипендия |
| Иванов И.И. | ||||
| Петров П.П. | ||||
| Сидоров О.Б. | ||||
| Жукин Л.М. |
Задание 6
Используя функции И и ИЛИ создайте форму, в которой автоматически будет выводиться информация о студентах, которые не сдали зачет по праву или одному из иностранных языков и поэтому не могут быть допущены к экзамену по Информатике.
| Студент | Право | Английский | Немецкий | Информатика |
| Иванов И.И. | зачтено | зачтено | ||
| Петров П.П. | зачтено | |||
| Сидоров О.Б. | зачтено | зачтено | ||
| Жукин Л.М. | зачтено | |||
| Пяткин Г.Л. | зачтено | |||
| Мишина П.Д. | зачтено | зачтено | ||
| Люкина Р.Д. | зачтено |
Задание 7
Провайдер Интернет-услуг установил следующую систему оплаты: при работе с 2 до 10 часов – 0,5$ в час; с 10 до 20 часов - 1$ в час; в остальное время суток – 0,75 $. Подготовить лист для определения стоимости 1 часа работы в Интернете, если известно, что время работы совпало с началом очередного часа и задано в формате время и что время работы находилось в одном из указанных интервалов суток. Предусмотреть пересчет стоимости работы по курсу в рублях, для чего в отдельной ячейке задать курс.
| Курс $ к рублю. | 28 600 | ||
| Клиент | Начало работы | Стоимость в $ | Стоимость в руб. |
| Петров П.П. | 19:00 | ||
| Сидоров О.Б. | 14:00 | ||
| Жукин Л.М. | 8:00 | ||
| Пяткин Г.Л. | 23:00 |
Задание 8
В библиотеке последний четверг каждого месяца – санитарный день. Для заданного года получить расписания санитарных дней на все месяцы. При решении задачи воспользоваться функциями работы с датами КОНМЕСЯЦА() и ДЕНЬНЕД(). Функция КОНМЕСЯЦА будет доступна, если выполнить следующие действия: Сервис/Надстройки/Пакет анализа. Теперь список функций пополнится. Синтаксис функции: КОНМЕСЯЦА(начальная_дата; число_месяцев). Число_месяцев – это число месяцев до или после указанной даты. Положительное значение аргумента означает будущую дату, отрицательное – прошедшую. Функция возвращает последнюю дату месяца, который отстоит на заданное число месяцев от текущей даты.
Условное форматирование
Цель: практические навыки применения условного форматирования в таблицах.
Краткие сведения
В Excel имеется средство, с помощью которого легко наложить формат (шрифт, границы, цвет) на ячейку или блок. В зависимости от значения, полученного в ячейке, ее формат будет меняться. Например, текст в ячейке можно окрасить в зеленый цвет, если объем продаж превосходит прогноз, и красным — если продажи упали.
Значения выделенных ячеек можно сравнивать с константой или с результатами вычислений по формуле. Чтобы определить условие форматирования при оценке данных в ячейках, не входящих в выделенный диапазон, или при проверке нескольких критериев, можно использовать логическую формулу. Если был выбран параметр формула, то указанная формула должна принимать значения ИСТИНА (1) или ЛОЖЬ (0). Формула должна начинаться со знака равенства (=). Вычисления по данной формуле могут производиться только на активном листе. Чтобы оценить данные на другом листе или в другой книге, на активном листе необходимо указать имя для данных на другом листе или в другой книге либо ввести ссылку на эти данные в ячейке активного листа, а затем сослаться на указанную ячейку или имя в формуле.
Например, чтобы оценить данные, содержащиеся в ячейке A5 на Листе1 книги Налоги.xls, в ячейку активного листа введите следующую ссылку, включая знак равенства (=): =[Налоги.xls]ЛИСТ1!$A$5.
При помощи формулы можно также оценивать критерии, не основанные на данных листа. Например, формула =ДЕНЬНЕД("12.5.99")=1 принимает значение ИСТИНА, если дата 12 мая 1999 г. является воскресеньем. Значение ячейки никак не влияет на истинность условия только в том случае, если в формуле нет специальных ссылок на форматируемые выделенные ячейки. Если же формула ссылается на выделенные ячейки, то в нее необходимо ввести ссылки на ячейки.
Ссылки на ячейки можно ввести в формулу, выделив ячейки непосредственно на листе. Выделение ячеек на листе вставляет абсолютные ссылки. Если необходимо, чтобы ссылки для каждой ячейки в выделенном диапазоне были согласованы, используйте относительные ссылки.
Даты и время рассматриваются как числа. Например, при сравнении содержимого ячейки с датой 7 января 2001 дата будет представлена в виде числа 36898.
В приведенном ниже примере условные форматы, примененные к ячейкам B3:E4, используются для анализа вклада ежеквартальных вложений в общий годовой итог. Если вложения, сделанные в течение квартала, составили 30 и более процентов от общего годового итога, то результаты отображаются полужирным шрифтом на сером фоне. Если же вложения, сделанные в течение квартала, составили 20 и менее процентов, то результаты отображаются полужирным белым шрифтом на черном фоне.
Выполните команду Формат / Условное форматирование… Заполните поля диалогового окна, как на рисунке 24. Для добавления второго условия нажмите кнопку А также >>.
Рис. 24. Условное форматирование
В формуле определяется относительная часть (номер строки) ссылки на ячейку $F3, чтобы каждая ячейка из диапазона B3:E4 сравнивалась с соответствующим значением итога в столбце F. Результат форматирования представлен на рисунке 25.
Рис. 25. Результаты форматирования
Теперь рассмотрим пример задания условного форматирования с помощью формулы. Для предыдущего примера выделите полужирным шрифтом на сером фоне все числа, кратные двум. На рисунке 26 приведена формула, которая должна оценивать все ячейки в диапазоне.
Рис. 26. Задание условного форматирования с помощью формулы
При вводе такой формулы в диалоговом окне Условное форматирование следует вводить ссылку только на активную ячейку в выделенном диапазоне. Microsoft Excel устанавливает ссылки для других ячеек относительно активной. Результат форматирования приведен на рисунке 27.
Рис. 27. Результат форматирования
Усложним задачу. Теперь нужно выделить полужирным шрифтом на сером фоне числа, кратные двум, но не кратные трем. На рисунке 28 показан пример заполнения диалогового окна.
Рис. 28. Логическая формула в условном форматирование
Введенная логическая формула И должна возвращать значение ИСТИНА или ЛОЖЬ. В ней фигурирует относительный адрес активной ячейки, входящей в блок. Результат форматирования представлен на рисунке 29.
Рис. 29. Результат форматирования