LR_informatika / Excel(lab) №1

нет ЕСЛИ(И(A2>A1;A2>A3); ––––да  A2;

нет A3)).

Задание 5. Премия. Начислим премии работникам, последовательно усложняя условия.

5.1. Пусть, текущий месяц март и женщинам устанавливается премия в размере 300 руб. к 8 марта. Иными словами

п ремия1=ЕСЛИ(пол=женский, то 300р, иначе 0р).

D3 =ЕСЛИ(B3=”ж”;300;0).

5.2. Такое решение будет правильным только в марте. Если оставить как есть, женщинам будет назначаться премия ежемесячно. Чтобы этого избежать, нужно учесть значение текущего месяца (клетка В1).

премия2 =ЕСЛИ(женщина И март, то 300р, иначе 0р)

Е3 =ЕСЛИ(И(B3=”ж”;B$1=3);300;0).

5.3. Пусть также в феврале и мужчинам назначается премия в 300р.

премия3 = ЕСЛИ((женщина И март) ИЛИ (мужчина И февраль), то 300р, иначе 0р)

F3 =ЕСЛИ(ИЛИ(И(B3=”ж”;B$1=3); И(B3=”м”;B$1=2));300;0).

5.4. Премии мужчинам и женщинам различны: мужчинам 10% зарплаты, женщинам 300 руб. независимо от заработка.

а) премия4 = ЕСЛИ(женщина И март, то 300р, ЕСЛИ(мужчина И февраль, то 10% от зарплаты, иначе 0р))

G3 =ЕСЛИ(И(B3=”ж”;B$1=3);300;ЕСЛИ(И(B3=”м”;B$1=2);10%*С3;0)). – это вложение функций ЕСЛИ

или несколько проще, если разбить выражение на два слагаемых

б) премия4 =ЕСЛИ(женщина И март, то 300р)+ЕСЛИ(мужчина И февраль, то 10% зарплаты)

G3 =ЕСЛИ(И(B3=”ж”;B$1=3);300;0) + ЕСЛИ(И(B3=”м”;B$1=2);10%*С3;0). – это сумма функций ЕСЛИ

5.5. Положим, кроме того, всем работникам назначается премия к Новому году в размере 500р. (представим как сумму).

премия5 =ЕСЛИ(женщина И март, то 300)+ЕСЛИ(мужчина И февраль, 10% зарплаты)+ЕСЛИ(январь, 500)

H3 =ЕСЛИ(И(B3=”ж”;B$1=3);300;0) + ЕСЛИ(И(B3=”м”;B$1=2);10%*С3;0) + ЕСЛИ(B$1=1;500;0).

5.6. Решить самостоятельно. Премия 300р. женщинам (независимо от месяца), зарабатывающим менее 1000р.

I3=ЕСЛИ(. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

5.7. Решить. Премия всем в мае. Зарабатывающим до 1000р – премия 500р, с 1000р (т.е. при >=1000) – премия в 20% от зарплаты. Сначала как вложение функций ЕСЛИ, затем как сумму

а) J3=ЕСЛИ( . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

б) J3=ЕСЛИ( . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

5.8. Решить. Премия (всем независимо от месяца)в размере зарплаты, но не менее 500р и не более 1000р.

а) K3=ЕСЛИ(С3<500;500; ЕСЛИ(С3>1000; . . . . . ; . )) – решить как вложение ЕСЛИ

б) K3=ЕСЛИ(С3<500;500;0) + ЕСЛИ(С3>1000; . . . ;0) + ЕСЛИ(И(С3 . . . . . ;С3 . . . . . ); С3;0) – как их сумму

З

	A	B	C	D	E	F	G	H
1	Деталь:	10	Брак:	20	Премия:	10%	Налог:	12%
2	Сдельная зарплата
3	Рабочий	Детали	Брак	Зарплата	Премия	Налог	Выдать
4	Петр	10	6	?	?	?	?
5	Иван	15		?	?	?	?
6	Олег	30		?	?	?	?

адание 6. Сдельная зарплата. Известны: стоимость обработки 1 детали (10р), стоимость испорченной детали (20р). Последняя вычитается из зарплаты. Премия назначается рабочим, которые не имели брака и обработали более 10 деталей. Если брака много, зарплата может оказаться отрицательной и налог, конечно, не взимается. Тарифы в формулах должны быть заданы адресами.

зарплата = Оплата_за_детали – Вычеты_за_брак D4=      

премия =ЕСЛИ(брак=0 И деталей>10; то Расчет_премии; иначе 0) E4=ЕСЛИ(       

налог=ЕСЛИ(зарплата>0; то Расчет_налога; иначе 0) F4=ЕСЛИ(       

выдать = зарплата + премия – налог G4=    

Усложним. Если изготовлено >20 деталей, кроме обычной премии дается еще 500р.

	A	B
1	X	Y
2	-6	?
3	-4	?
4	-2	?
5	0	?
6	2	?
7	4	?
8	6	?
9	8	?
10	10	?
11	12	?
12	14	?
13	16	?

E4=ЕСЛИ(...........................................................................................................

Задание 7. Вычислить значения кусочно-ломаной функции Y(X) в диапазоне изменения аргумента Х от -6 до +16 с шагом 2. Х и Y находятся в столбцах А и В.

Здесь Y=ЕСЛИ(X<0;5+X;ЕСЛИ(И(0<=X;X<10);5;10–0,5•X))

или в адресах для первого значения Y и Х (клетки В2 и А2):

B2 =ЕСЛИ(A2<0;5+A2;ЕСЛИ(И(0<=A2;A2<10);5;10–0,5*A2)) или проще

B

тип диаграммы: “Точечная, на которой значения

соединены отрезками”

2 =ЕСЛИ(A2<0;5+A2;ЕСЛИ(A2<10;5;10–0,5*A2)).

Упрощение возможно поскольку, если в первом ЕСЛИ условие A2<0 неверно, значит обязательно верно противоположное A20, т.о., выяснять это больше не нужно. Вообще, вложенных функций ЕСЛИ должно быть на единицу меньше возможных диапазонов данных. В нашем случае, поскольку ось Х разбита на три подмножества, должно быть два ЕСЛИ.

Задание 8. Расчеты с вкладчиками банка. Пусть годовой банковский процент так зависит от вклада: при вкладе до 10т.руб. составляет 10%, при вкладе до 30т – 15%, до 60т – 20%, если выше – 30%. Т.о.

д

	A	B	C
1	Вклад	Процент
2	10	10%
3	30	15%
4	60	20%
5	от 60	30%
6	Расчеты в банке
7	Клиент	Вклад	Доход
8	Петр	40	?
9	Иван	5	?
10	Олег	100	?
11	ВСЕГО	?	?

4

оход =ЕСЛИ(вклад<10; то 10%; иначе

ЕСЛИ(вклад>=10 И вклад<30; то 15%; иначе

ЕСЛИ(вклад>=30 И вклад<60; то 20%; иначе 30%))) • вклад или проще

доход=ЕСЛИ(вклад<10; 10%;ЕСЛИ(вклад<30;15%;ЕСЛИ(вклад<60;20%;30%)))•вклад

Записать С8, используя только адреса и сделав необходимое замораживание ссылок

C8=ЕСЛИ(B8<A$2;B$2;ЕСЛИ(B8<      

ФУНКЦИИ ПОИСКА (функции горизонтального и вертикального просмотра).

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

Функция ГПР. Используется для поиска данных в горизонтальной таблице.

Формат функции: =ГПР(что_искать; где_искать; из_какой_строки_взять_результат; как_искать).

Здесь как_искать (тип поиска) это: 0 – поиск точный.

1 – поиск интервальный, если не найдено точное значение, искомым считается ближайшее меньшее

Поиск ведется всегда в первой строке блока поиска (“где искать”). Результат извлекается из параллельной нижележащей строки с указанным номером относительно первой строки блока (нумерация строк ведется внутри блока, начиная с №1).

Имеется вертикальный аналог функции ГПР – функция ВПР (для работы с вертикально расположенными тарифами)

= ВПР(что_искать; где_искать; из_какого_солбца_взять_результат; как_искать).

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

Задание 9. Повременная оплата с учетом разряда. Рассчитать заработную плату, зависящую от числа отработанных дней в месяце, разряда и премии. Для вычисления собственно зарплаты (область D7:D9) нужно число дней умножить на тариф, зависящий от разряда рабочего

зарплата=тариф_по_разряду • дней.

Для розыска разрядного тарифа понадобится функция ГПР. Так, формула для Петра

зарплата_Петра=ГПР(разряд_Петра; тарифная_сетка; строка_“тариф”; поиск_точный) • дней или

D7 =ГПР(C7;B$2:F$4;2;0)*B7. (=100р•10дн) – строка результата “тариф” имеет №2 внутри блока поиска, строка “премия” ­– №3. Премия также зависит от разряда. Напишите формулу E7=ГПР(. . . . . . . . . . . . . . . . . . . . . . . . . . . .

Другая постановка. Пусть премия дается только при отработке >5 дней

E7=ЕСЛИ(B7>5;ГПР(…);0)*D7 или иначе E7=ГПР(…)*D7*ЕСЛИ(B7>5;1;0). Здесь ГПР(…) это

Еще задача. Отработавшим >25 дней к премии добавляется еще 10% E7= . . . . . . . . . . . . . . . . . . . . . . . . . . .

Задание 10. Конвертирование валюты. При внешнеторговых операциях расчет с поставщиками выполняется в долларах, а с внутренними покупателями – в рублях и нужно конвертировать в рубли исходную (в момент поступления) и текущую (“на сегодня”) цены товара. Информация хранится в двух таблицах: таблице курса доллара (столбцы А,В) и таблице расчета рублевого эквивалента товара. Содержимое первого – последовательные значения дат и цены $. В клетке E1 предъявляется текущая дата. В основной таблице содержатся сведения о дате закупки товара и его закупочной цене в $ (столбцы D и F). В столбце G вычисляется рублевая цена товара на момент покупки, в H – его сегодняшняя рублевая цена. Для столбцов G, H понадобится функция ВПР с четвертым аргументом =1, т.е. поиск даты в курсовой таблице будет не точным, а интервальным, поскольку некоторых дат там нет и стоимость доллара тогда берется равной курсу ближайшей предыдущей даты, для которой она имеется (так, для 9 и 10 января берется курс за 8-е, для 13.янв – за 12-е).

Цена_закупки_в_руб. = Курс_$_на_день_закупки • цена_закупки_в_$ или

Цена_закупки_в_руб. = ВПР(дата_закупки; курсовая_таблица; столбец _“цена_$”;

поиск_интервальный) • цена_закупки_в_$ или

G3 =ВПР(D3;A$2:B$10;2;1)*F3. – нижняя граница (B10) блока поиска берется с запасом для ввода новых дат и курсов $.

А

В

C

D

E

F

G

H

I

J

	A	B	C
1	Доход	% налога
2	15	12%
3	35	18%
4	свыше	25%
5	Вычисление налогов
6	Имя	Доход	Налог
7	Иван	10	?
8	Петр	50	?
9	Анна	30	?

1

Дата

Цена $

Сегодня

13.янв

2

04.янв

20р

Дата

закупки

Товар

Цена($)

закупки

Цена (р)

закупки

Цена (р)

сегодня

Цена

+30%

Цена+

40/50%

3

05.янв

21р

04.янв

Стол

100$

?р

?р

?р

?р

4

06.янв

22р

06.янв

Стул

50$

?р

?р

?р

?р

5

07.янв

23р

09.янв

Тумба

80$

?р

?р

?р

?р

6

08.янв

24р

10.янв

Шкаф

100$

?р

?р

?р

?р

7

11.янв

25р

11.янв

Палас

70$

?р

?р

?р

?р

8

12.янв

26р

12.янв

Софа

100$

?р

?р

?р

?р

9

10

Вычислить рублевый эквивалент товара на сегодня (столбец H):

Цена_сегодня_в_руб.=Курс_$_на_сегодня • цена_закупки_в_$. Записать H3=ВПР(……………………………….…………….

Усложним задачу. Цена_сегодня, найденная в такой постановке, дает себестоимость товара на сегодня. Чтобы иметь прибыль надо продать его дороже (например на 30%). Напишите формулу в столбце I (не используя содержимое столбца H):

I3=ВПР(

Усложним задачу. Пусть прибыль начисляется в зависимости от цены_закупки. На товар ценой менее 100$ она составляет 50%, на остальные – 40%. Напишите формулу в столбце J (можно использовать H):

J3=ЕСЛИ(F3

Еще. Товар, закупленный более 100 дней назад продается по себестоимости H, остальное, как в J.

К3= ЕСЛИ(

Замечания: 1. Даты следует вводить в числовом виде, так 4.1 будет автоматически преобразовано в 04.янв.

2. Единицы валюты (р и $) непосредственно (руками) вводить нельзя. Они устанавливаются форматированием данных.

5

Задание 11. Прогрессивный налог. Вычислить годовой подоходный налог с граждан. С дохода до 15т.руб. бе­рется налог в размере 12%, с дохода до 35т – 18%, с дохода свыше 35т – 25%. Причем более высокий налог берется с той частью дохода, которая находится в соответствующем диапазоне. Например, с дохода в 60т, налог будет таков:

таким образом, для дохода в 60т. руб.

налог=12%•15+18%•20+25%•25=11,65т.

Формула в общем виде: налог=ЕСЛИ(доход<15; 12%•доход;

ЕСЛИ(доход<35; 12%•15+18%•(доход-15); 12%•15+18%•(35-15)+25%•(доход-35)))

Записать формулу вычисления налога, используя только адреса (а не константы) и “заморозив” нужные ссылки.

C

	A	В	C	D	E	F	G
1	СТИПЕНДИЯ					МЗ:	100р
2	Студент	экзамены		зачеты		Средний балл	Стипен- дия
3		логика	этика	химия	право
4	Петр	5	3	+	+	?	?р
5	Иван	3	4	+		?	?р
6	Саша	5	5		+	?	?р
7	Вера	4	5	+	+	?	?р
8	сдали	?ст	?ст	?ст	?ст	?ст
9	не сдали	?ст	?ст	?ст	?ст	?ст
10	среднее	для всех:				?	?р
11	среднее	для сдавших:				?	?р
12	отличников и хорошистов:					?ст

7=ЕСЛИ(B7<A$2;B$2*B7;ЕСЛИ(B7<A$3; . . . . . . . . . . . . . . .