Приложение:
Общий вид условной функции следующий:
ЕСЛИ (< условие > , < выражение 1 > , <выражение 2 >)
<условие> - это логическое выражение, которое может принимать значение ИСТИНА или ЛОЖЬ,
<выражение 1 > , < выражение 2 > могут быть числами, формулами или текстом.
Условная функция, записанная в ячейку таблицы, выполняется так: если <условие> истинно, то значение данной ячейки определит <выражение 1 >, в противном случае <выражение 2 >. Логические выражения строятся с помощью операций отношения >(меньше), <(больше), <=(меньше или равно), >= (больше или равно), = (равно), <>(неравно).
Если задание 2.3 сделано правильно, то должна получиться следующая таблица:
Содержание отчета:
4.1. Наименование и цель работы
4.2. Выполненное задание
4.3. Ответы на контрольные вопросы
5. Контрольные вопросы:
5.1. Перечислите основные виды адресации в электронных таблицах. Чем один вид адресации отличается от другого?
5.2 В каких случая применяют абсолютные ссылки?
5.4 Как вводится знак абсолютной адресации?
Лабораторная работа №25
Наименование: Сортировка данных , фильтр, работа с листами рабочей книги.
Цель работы: ознакомление с общими сведениями об управлении листами рабочей книги, удалении и переименовании листов; составление формул, содержащих адреса ячеек на разных листах рабочей книги; самостоятельное составление таблицы на основе данных из другой таблицы, расположенной на другом листе рабочей книги.
1. Литература:
Фуфаев Э.В., Фуфаева Л.И. Пакеты прикладных программ: учеб. Пособие для студ. сред. проф. образования / под ред. Э.В. Фуфаев, Л.И. Фуфаева. – Москва: Издательский центр «Академия», 2008. – 352 с.
Задание:
Задание 1. Подготовьте ведомость на выдачу заработной платы. Для выполнения упражнения понадобятся три листа:
- на первом разместите сведения о начислениях;
- на втором – ведомость на выдачу заработной платы;
- на третьем – ведомость на выдачу компенсаций на детей.
1. Создайте заготовки таблицы самостоятельно (см. образец на рис.1).
2. Заголовок размещен в двух строках таблицы, применен полужирный стиль начертания шрифта, весь текст выровнен по центру, а «Налоги» – по центру выделения;
3. Измените ширину столбца (в зависимости от объема вводимой информации) и обрамление таблицы. В данном случае использовано сложное обрамление, когда снята часть рамок. Важно по предложенному образцу определить реальное положение ячеек и выполнить соответствующее обрамление, выделяя различные блоки ячеек.
Образец таблицы:
Рис.1.
4. Заполните заголовки столбцов таблицы по рис.1. В ячейку А1 наберите №, в В1 – «Фамилия, имя, отчество», в С1 – «Оклад», в D1 – «Налоги», в G1 – «Сумма к выдаче», в Н1 – «Число детей». Теперь выделите ячейки А1 и А2 и выполните команду Формат®Ячейки, затем во вкладке Выравниваниеактивизируйте переключатели Переносить по словам и Объединение ячеек и нажмите ОК. Проделайте точно такие же действия с ячейками (В1, В2), (С1,С2), (G1,G2), (Н1,Н2).
5. Выделите ячейки D1:F1 и выполните команду Формат®Ячейки, затем во вкладке Выравнивание в группе выравнивание по горизонтали выберите поцентру выделения и нажмите ОК.
6. В ячейку D2 наберите «проф.», в ячейку E2 – «пенс.», в ячейку F2 – «подоход.».
7. Заполните ячейки столбца А последовательностью чисел 1, 2, …,10.
8. Задайте формат числа «денежный» для ячеек, содержащих суммы. Можно сделать это до ввода данных в таблицу: выделите соответствующие ячейки и установите для них формат числа «денежный».
9. Теперь заполните столбцы D и E формулами для расчета профсоюзного и пенсионного налогов. Примем профсоюзный и пенсионный налоги, составляющими по 1% от оклада. Удобно ввести формулу в одну ячейку, а затем распространить ее на оба столбца. Самое важное – не забыть про абсолютные ссылки, так как профсоюзный и пенсионный налоги нужно брать от оклада, т.е. ссылаться только на столбец С «Оклад». Примерный вид формулы: =$С3*1% или =$С3*0,01 или =$С3*1/100. После ввода формулы в ячейку D3 ее нужно распространить вниз (протянув за маркер выделения) и затем вправо на один столбец. Теперь формулами уже заполнены столбцы D и E.
10. Столбец F следует заполнить формулой для расчета подоходного налога. Подоходный налог вычисляется по формуле: 12% от Оклада за вычетом минимальной заработной платы и пенсионного налога. Примерный вид формулы: =(С3-Е3-110)*12% или =(С3-Е3-110)*12/100 или =(С3-Е3-110)*0,12. После ввода формулы в ячейку F3, ее нужно распространить вниз.
11. Для подсчета Суммы к выдаче примените формулу, вычисляющую разность оклада и налогов. Примерный вид формулы в ячейке G3: =С3-D3-E3-F3, которую следует распространить вниз столбца.
12. Заполните столбцы «Фамилия, имя, отчество», «Оклад» и «Число детей» после того, как ввели все формулы. Результат будет вычисляться сразу же после ввода данных в ячейку. При желании можно воспользоваться режимом формы для заполнения таблицы. Заполнение таблицы можно производить следующим образом.
13. Наберите любые несколько фамилий (сотрудников предприятия) в произвольном порядке или см. образец заполненной таблицы на рис.2.
14. В столбце С наберите оклады сотрудников согласно штатному расписанию (надерите любые числа или см. образец заполненной таблицы на рис.2).
15. В последнем столбце «Число детей» проставьте любые числа от 0 до 3.
16. После ввода всех данных желательно выполнить их сортировку (перед сортировкой необходимо выделить все строки от фамилий до сведений о детях). В окончательном виде таблица будет соответствовать образцу на рис. 2.
Образец заполненной таблицы:
Рис.2.
17. Поскольку мы собираемся в дальнейшем работать сразу с несколькими листами, имеет смысл переименовать их ярлычки в соответствии с содержимым. Переименуем активный в настоящий момент лист. Для этого выполните команду Формат®Лист®Переименовать и в поле ввода Имя листавведите новое название листа, например, «Начисления».
Задание 2.
1. Перейдите с помощью мыши на Лист 2. Переименуйте его в «Детские». На этом листе создайте ведомость на получение компенсации на детей на основе таблица начислений. Подготовьте пустую таблицу, состоящую из трех столбцов.
2. Наберите заголовки столбцов: в ячейку А1 – «ФИО», в ячейку В1 – «Сумма», в С1 – «Подпись» (см. образец на рис.3). Выполните обрамление таблицы.
3. Список фамилий внесите автоматически с помощью связи межу листами. Для этого выделите ячейку А2 листа «Детские» и введите в нею формулу: =Начисления!В3, где имя листа обозначается восклицательным знаком, а В3 – адрес ячейки, в которой размещена первая фамилия сотрудника на листе «Начисления». Эту формулу можно не набирать в ручную, а перейти на лист «Начисления», выделит мышью ячейку, содержащую первую фамилию списка и нажмите Enter. Вернитесь на лист «Детские» и распространите полученную формулу вниз столбца А.
4. В ячейке В2 аналогичным образом поместите формулу: =Начисления!Н3* 53.10, где Н3 – адрес первой ячейки на листе «Начисления», содержащей число детей. Заполните этой формулой остальные ячейки столбца В. Примените к ним денежный формат числа.
5. Для того, чтобы список в таблице на листе «Детские» состоял только из сотрудников, имеющих детей, установите фильтр по наличию детей. Для этого выполните команду Данные®Фильтр®Автофильтр и в раскрывающемся списке «Сумма» выберите критерий (Условие). В левом верхнем поле окна пользовательского автофильта выберите больше или равно, а в верхнем правом поле выберите минимальное ненулевое число из списка (в нашем случае это 53,13). Нажмите ОК.
6. Вставьте в самом верхе дополнительную строку и наберите заголовок этого листа: «Ведомость на получение детской компенсации». В окончательном варианте отфильтрованная таблица должна иметь следующий вид:
Рис.3.