Начисление налогов с использованием условий

Практическая работа №3.

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

Создайте электронную таблицу учета платы за квартиру согласно образцу:

 

		Квартплата:
Тариф за 1 кв. м:		5 р.
Срок оплаты:		10 мар.
Пени за 1 день:		1,5 р.
№ квартиры	Фамилия квартирос.	Площадь кв. м.	Сумма	Дата оплаты	Просрочка	Штраф	Итого

 

Для этого:

1) Выровняйте все заголовки по центру, как по вертикали, так и по горизонтали, при определении формата ячейки примените опцию переноса слов.

2) Столбец «№ квартиры»: 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25 – центральное выравнивание.

3) Столбец «Фамилия квартиросъемщика»: Иванов, Петров, Сидоров, Краснов, Белов все остальные Куропаткин1, Куропаткин2 ….

4) Столбец «Площадь»: 70; 69,5; 69 и. т.д. (каждая последующая на 0,5 кв.м. меньше предыдущей) – выравнивание центральное.

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

6) Столбец «Дата оплаты»: с 10 февраля, каждая следующая квартира произвела оплату на день позже предыдущей. Формат: Дата, полная форма.

7) Столбец «Просрочка»: если оплата была произведена до срока оплаты включительно, то автоматически ставится 0 (ноль); если позже срока, то должно подсчитываться количество дней просрочки («Дата оплаты» – (минус) «Срок оплаты»). Срок оплаты может изменяться преподавателем во время работы с вашей таблицей. Данные выровняйте по центру.

8) Столбец «Штраф»: для каждой квартиры умножается значение из графы «Пени за 1 день» на значение из графы «Просрочка». Формат денежный, без копеек. Примерный результат выполнения заданий 1)-8) можно увидеть на рис. 1.

9) В конце ведомости должна автоматически подсчитываться следующая статистика по квартирам: общая сумма графы «Итого» (формат рублевый без копеек), «Средняя площадь», «Максимальная просрочка».Для того, чтобы проверить выполнение этого пункта измените срок оплаты например, на 15 февраля 1998 г. и произведите необходимые расчеты, используя функции меню Формулы → Библиотека функций → Автосумма. Результат вычисления см. на рис. 2.

Немного теории.

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

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

 

ЕСЛИ (условие; формула 1; формула2),

 

где : условие - логическое выражение, например С7<3 000 000;

   формула1 – действие, которое будет выполнять программа Excel, если условие выполняется;

   формула2 – действие, которое будет выполнять программа Excel, если условие не выполняется;

 

Например, чтобы разместить в ячейке С12 максимальное из двух значений, содержащихся в ячейках С10 и С11, достаточно ввести в ячейку С12 формулу:

= ЕСЛИ (С10>C11; C10; C11).

 

Часто приходится использовать сложные условия и вложенные логические функции, например в ячейке А1 содержится дата выполнения расчета. Требуется в заголовке рабочей таблицы (например, в ячейке B4) указать номер квартала. Очевидно, что номер квартала определяется в зависимости от месяца, а для выполнения поставленной задачи потребуется формула:

= ЕСЛИ(А1>=1; A1<=3); “1-ый квартал”; ЕСЛИ(И((А1>3; A1<=6); “2-ой квартал”; ЕСЛИ (И((А1>6; A1<=9); “3-ий квартал”); “4-ый квартал”)))

 

Примечание 1: Результат выполнения заданий 1)-8) представлен в таблице на рис.1. При выполнении п.7) задания столбец «Просрочка» заполнялся формулой: =ЕСЛИ(Е7<$C$3;0;E7-$C$3).

Примечание 2: При вычислении значений в столбце «Просрочка» может возникать ошибка #ЗНАЧ, связанная с неправильным пониманием табличным процессором формата ячеек, входящих в формулу для вычисления. В таком случае рекомендуется выделить по очереди ячейки, входящие в формулу и выбрать для каждой: Главная → Число →Дата → 14.03.2001(верхняя строка в окне «Тип»). Далее вернуться к исходному порядку вычислений.

 

Рис. 1. Таблица вычисления квартплаты, срок оплаты 10 марта 1998 г.

 

 

                                   Рис. 2. Таблица вычисления квартплаты, срок оплаты 15 февраля 1998 г.

Практическая работа №4.

Тема: Составление условий в электронных таблицах.

Часть I.

Задание 1.

Составьте электронную таблицу результатов сдачи экзаменов.

Для этого:

1) Заполните данными электронную таблицу:

Результаты экзаменов по математике
Студент	Экзамен1	Экзамен2	Экзамен3	Экзамен4	Среднее	Пропуски	Сдал/Не сдал
Азимов	87	90	79	96		2
Булавин	92	94	94	97		5
Гаврилов	96	95	95	80		0
Пашков	85	87	87	88		4
Рейн	81	88	88	85		1

 

2) Создайте формулу, которая возвращала бы текстовое значение Сдал, если студент по результатам экзаменов набирает средний балл выше 75 и при этом пропустил не больше 5 занятий без уважительных причин. Для этого в ячейку H4 введите формулу =ЕСЛИ(И(G4<5;F4>75); «Сдал»; «Не сдал») и скопируйте ее в ячейки H4:H8. Из табл. на рис. 1 видно, что только один студент не сдал экзамен, поскольку он имеет больше 5 пропусков занятий.

3) Если в формуле вместо функции И использовать функцию ИЛИ, то все студенты сдали бы экзамен, поскольку у всех средний балл выше 75. Измените в формуле функцию И на ИЛИ и посмотрите как изменились результаты вычислений.

4) Результаты выполнения задания покажите преподавателю.

На рис. 1 представлена результирующая таблица к заданию 1, п.1)-2):

 

 

Рис. 1. Результаты выполнения задания 1, п. 1)-2).

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

Задание 2.

Создайте на рабочем листе таблицу «Примеры условий»:

1	A	B	C
2	Исходные данные		Результат
3	2	3	=ЕСЛИ(А13>B13;A13;B13)
4	5	1	=ЕСЛИ(А14>B14; “да”; “нет”)
5	Слово	4	=ЕСЛИ(ИЛИ(ЕПУСТО(А15);ЕТЕКСТ(А15)); “да”; “нет”)
6	15	-3	=ЕСЛИ(B16<0;1;ЕСЛИ(В16=0;0;А16/B16))

1) В ячейку С13 введите большее из значений, содержащихся, в соседних слева ячейках.

2) В ячейку С14 введите слово “Да”, если значение ячейки А14 больше значения ячейки B14. В противном случае введите слово “Нет”.

3) Создайте формулу, записывающую в ячейку С15 слово “Да”, если ячейка А15 пуста или содержит текст. В противном случае в ячейке С15 должно появляться слово “Нет”.

4) Сформируйте формулу, определяющую значение ячейки С16 как:

- 1, если содержимое ячейки B16 меньше 0;

- 0, если содержимое ячейки B16 равно 0;

- формулу A16/B16, если B16, больше 0.

Результат выполнения задания 2, см. рис.2:

                                                    Рис. 2. Таблица с результатами выполнения задания 2.

5) Результат выполнения задания покажите преподавателю.

Часть II.

Задание 1. Составьте электронную таблицу для решения любого квадратного уравнения с помощью дискриминанта:

Решение квадратного уравнения
Коэффициент а		Значение дискриминанта
Коэффициент b
Коэффициент c
Корень x1
Корень x2

Запишите в соответствующие ячейки формулы и условия для вычисления дискриминанта и корней уравнения со ссылкой на ячейки с исходными данными. Проверьте работу таблицы на примерах:

 (два корня 1; 0,2)

 (один корень 0,25)

(корней нет).

Для этого:

1) Запишите в ячейки B3:B5 значения коэффициентов a,b,c. В ячейку С3 введите формулу: = B4^2-4*B3*B5.

2) В ячейку B7 введите формулу =(-$B$4+КОРЕНЬ($C$3))/(2*$B$3); В ячейку B8 введите формулу =(-$B$4-КОРЕНЬ($C$3))/(2*$B$3).

3) В ячейку С7 введите формулу =ЕСЛИ(C3>0;" уравнение имеет два корня";" "); в ячейку С8 введите формулу =ЕСЛИ(C3=0;"уравнение имеет один корень";" "); в ячейку С9 введите формулу =ЕСЛИ(C3<0;"уравнение не имеет действительных корней"; " ").

4) Произведите вычисления для трех указанных примеров. Результат вычислений покажите преподавателю.

Результат выполнения задания см. рис. 3: а); б; в).

 

  

 

 

Рис. 3а). Вычисление корней квадр. уравн. для D>0.       Рис. 3б). Вычисление корней квадр. уравн. для D=0.

5) Ответьте на вопрос, почему в ячейках B7 и B8 определяется ошибка #ЧИСЛО?

6) Как можно изменить ссылки в формулах в ячейках B7 и B8, чтобы результат вычислений не изменился?

7) Произведите самостоятельные вычисления для примеров квадратных уравнений, указанных преподавателем.

 

Рис. 3в). Вычисление корней квадр. уравн. для D<0.

Задание 2.

Решение квадратного уравнения с использованием утилиты «Подбор параметра».

Найдем решение уравнения  с помощью утилиты «Подбор параметра». У соответствующего уравнения два корня:  и . Для того, чтобы воспользоваться утилитой нужно:

1) Перейдите на новый лист в своем рабочем файле. В ячейку А1 запишите заголовок: «Решение квадратного уравнения
с помощью утилиты «Подбор параметра»».

2) В редакторе Microsoft Equation (приложение Microsoft Word) наберите формулу  и скопируйте ее
на рабочий лист в Excel.

2) В ячейку С5 введите начальное приближение для искомого корня (значение 0).

3) В ячейку С7 введите формулу =C5^2-4*C5+3 для вычисления по значению переменной полинома, соответствующего решаемому уравнению (см. рис. 4):

Рис. 4. Ввод исходных данных и запуск утилиты «Подбор параметра».

4) Далее запустите утилиту «Подбор параметра», для этого выберите: Данные → Работа с данными → Подбор параметра (см. рис.4). В открывшемся диалоговом окне указываем целевая ячейка С7 (в поле Установить в ячейке), значение целевой ячейки 0 (поле Значение), изменяемая ячейка С5 (поле Изменяя значение ячейки). Диалоговое окно Подбор параметра (см. рис. 5):

 

Рис. 5. Ввод параметров поиска меньшего корня в диалоговом окне Подбор параметра.

5) После щелчка на ОК получаем вполне неплохой результат для меньшего корня уравнения (см. рис. 6):

Рис. 6. Результат поиска меньшего корня уравнения .

6) Чтобы найти больший корень необходимо перед активизацией утилиты Подбор параметра указать в изменяемой ячейке другое начальное приближение. Введите в ячейку С5 значение 5. В поле Значение окна Подбор параметра введите значение 0 (см. рис. 7):

 

 

Рис. 7. Ввод параметров поиска большего корня в уравнении .

 

Рис. 8. Результат поиска большего корня в уравнении .

7) Произведите самостоятельно вычисления, используя утилиту «Подбор параметра» для примеров квадратных уравнений из задания 1. Результат вычислений покажите преподавателю.

Немного теории:

В использованном методе есть недостатки: 1) нет уверенности в том, что найдены все корни уравнения; 2) даже если известно общее количество решения не всегда можно предугадать, какое из решений будет найдено; 3) неудобно каждый раз запускать утилиту и выполнять настройки. Если настройки разные - тут уж никуда не денешься. Если настройки одни и те же (меняется только начальное значение изменяемой ячейки), то желательно процесс автоматизировать, учитывая, что значение ячейки изменяется в рабочем документе, а не в окне настроек.

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

Часть III.

Начисление налогов с использованием условий.

1) Создайте электронную таблицу «Расходные материалы (картриджи)».

 

 

2) Выберите для столбцов нужную ширину.

3) Занесите в верхней части таблицы курс доллара по образцу.

4) Заполните таблицу формулами:

а) стоимость заказа в $ и рублях;

б) НДС вычисляется 20% от стоимости заказа в рублях;

в) скидки выбираются в зависимости от количества штук, 5% для кол. штук от 100 до 200; 10% - более 200 штук; для заказа менее 100 штук скидок нет.

г) столбец К оплате = Стоимость заказа в рублях – Скидки + НДС;

д) в конце таблицы рассчитайте общую сумму заказа.

Для этого:

1) Для расчета стоимости заказа в $ в ячейки H6 введите формулу: =F6*G6. Скопируйте эту формулу в ячейки H7:H27.

2) Для расчета стоимости заказа в рублях в ячейку I6 введите формулу: =30,5*H6. Скопируйте эту формулу в ячейки I7:I27.

3) Рассчитайте НДС. Для этого в ячейку J6 введите формулу:=0,2*I6. Скопируйте эту формулу в ячейки J7:J27.

4) Выделите с помощью курсора заголовок столбца J и выполните команду: Главная → Ячейки → Вставить→ Вставить столбцы на лист. Слева от столбцаJ произойдет вставка пустого столбца. Назовите его «Стоимость заказа в рублях2».

Примечание: вставка еще одного столбца с данными о стоимости заказа в рублях позволит избежать циклической ссылки, которая не позволяет рассчитать скидки с заданием условия. Если в столбце Скидки производить расчет напрямую (например, ввести в ячейку K6 формулу: =0,05*I6, в ячейку K11 формулу: =0,1*I11, в ячейку K8 – 0 (ноль)), без задания условия, то вычисления будут произведены корректно и дополнительный столбец для Стоимости в рублях можно не создавать.

5) Введите вручную (без копирования из ячеек I6:I27) стоимость заказа в рублях для кажд. устройства в пустой столбец J.

6) Для расчета скидок в ячейки L6 введите формулу: =ЕСЛИ(И(F6>=100;F6<=200);0,05*J6;ЕСЛИ(F6>100;0,1*J6;"скидок нет")). Скопируйте эту формулу в ячейки L7:L27.

7) Для расчета графы Итого к оплате в ячейку M6 введите формулу: =I6-L6+K6. Скопируйте эту формулу в ячейки M7:M27.

8) Рассчитайте итоговую стоимость заказа, используя вкладки меню Формулы → Автосумма → Сумма.

9) Результаты вычислений покажите преподавателю.

10) Выберите для столбцов нужный формат, оформите таблицу выравниваниями, обрамлениями и цветом.

11) Итоговый результат вычислений к заданию части III см. на рис. 9.

12) Произведите аналогичный расчет с другими данными курса доллара, предложенными преподавателем.

Рис. 9. Итоговая таблица «Расходные материалы (картриджи)» к п.1)-9) задания части III.

 

 

---

Дата добавления: 2018-04-05; просмотров: 1052; Мы поможем в написании вашей работы!

Поделиться с друзьями:

---

---

Мы поможем в написании ваших работ!