Контрольная работа по excel функция если - Word и Excel

Практическое занятие №
5

Примеры

Работа с формулами в MS Excel

Создание
формулы

Логические
функции

ЛОЖЬ

НЕ

ИСТИНА

И

ИЛИ

Практическое занятие № 5

Примеры

Работа с формулами в MS Excel

Создание формулы

Логические функции

ЛОЖЬ

НЕ

ИСТИНА

И

ИЛИ

Практическое занятие №
5

Создание
формулы

Логические
функции

Результат функции

Формула

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

Важная информация

Проверяем соответствует ли число заданным критериям с помощью функции ЕСЛИ в Excel

Проверяем сразу несколько критериев

Вычисляем комиссию

Пример 4: Использование логических операторов (AND/OR) в функции IF в Excel

Как убрать ошибки при использовании функции ЕСЛИ в Excel

Результат функции

Формула

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

Важная информация

Проверяем соответствует ли число заданным критериям с помощью функции ЕСЛИ в Excel

Проверяем сразу несколько критериев

Вычисляем комиссию

Пример 4: Использование логических операторов (AND/OR) в функции IF в Excel

Как убрать ошибки при использовании функции ЕСЛИ в Excel

А вот еще интересные статьи:

Вариант 2

Вариант 3

Вариант 4

Вариант 5

Вариант 6

Вариант 7

Вариант 8

Вариант 10

Вариант 2

Вариант 3

Вариант 4

Вариант 5

Вариант 6

Вариант 7

Вариант 8

Вариант 10

Книга продаж: Ксероксы

Книга продаж: Ксероксы

Решить задачу путёмпостроения электронной
таблицы. Исходные данные для заполнения таблицы
подобрать самостоятельно (не менее 10 строк).

Вариант 1

Таблица содержит следующие данные об учениках
школы: фамилия, возраст и рост ученика. Сколько
учеников могут заниматься в баскетбольной
секции, если туда принимают детей с ростом не
менее 160 см? Возраст не должен превышать 13 лет.

1. Режим формул

		Максимальный возраст	13
		Минимальный рост	160
Фамилия	Возраст	Рост	Результат
Иванов	14	170	=ЕСЛИ((И(B4<=$D$1;C4>$D$2));»Принят»;»Не принят»)
Петров	11	150	=ЕСЛИ((И(B5<=$D$1;C5>$D$2));»Принят»;»Не принят»)
Сидоров	12	155	=ЕСЛИ((И(B6<=$D$1;C6>$D$2));»Принят»;»Не принят»)
Смирнов	13	165	=ЕСЛИ((И(B7<=$D$1;C7>$D$2));»Принят»;»Не принят»)
Воронин	12	165	=ЕСЛИ((И(B8<=$D$1;C8>$D$2));»Принят»;»Не принят»)
Воробьёв	12	162	=ЕСЛИ((И(B9<=$D$1;C9>$D$2));»Принят»;»Не принят»)
Снегирёв	11	152	=ЕСЛИ((И(B10<=$D$1;C10>$D$2));»Принят»;»Не принят»)
Соколов	13	158	=ЕСЛИ((И(B11<=$D$1;C11>$D$2));»Принят»;»Не принят»)
Макаров	13	161	=ЕСЛИ((И(B12<=$D$1;C12>$D$2));»Принят»;»Не принят»)
Павлов	14	167	=ЕСЛИ((И(B13<=$D$1;C13>$D$2));»Принят»;»Не принят»)
Итого	=СЧЁТ ЕСЛИ (D4:D13;»принят»)

Вариант 2

Каждому пушному зверьку в возрасте от 1-го до 2-х
месяцев полагается дополнительный стакан молока
в день, если его вес меньше 3 кг. Количество
зверьков, возраст и вес каждого известны.

Выяснить сколько литров молока в месяц
необходимо для зверофермы. Один стакан молока
составляет 0,2 литра.

Режим формул

			Один стакан молока, л	0,2
Номер	Возраст	Вес	Доп-ный стакан	Л в день	Л в месяц
1	1,5	2,5	=ЕСЛИ((И(B3<2;B3>1;C3<3));»Нужен»;»Не нужен»)	=ЕСЛИ(D3=»Нужен»;0,2+0,2;0,2)	=E3*30
2	1,2	2,2	=ЕСЛИ((И(B4<2;B4>1;C4<3));»Нужен»;»Не нужен»)	=ЕСЛИ(D4=»Нужен»;0,2+0,2;0,2)	=E4*30
3	1,6	2,6	=ЕСЛИ((И(B5<2;B5>1;C5<3));»Нужен»;»Не нужен»)	=ЕСЛИ(D5=»Нужен»;0,2+0,2;0,2)	=E5*30
4	1,8	2,7	=ЕСЛИ((И(B6<2;B6>1;C6<3));»Нужен»;»Не нужен»)	=ЕСЛИ(D6=»Нужен»;0,2+0,2;0,2)	=E6*30
5	2,5	3,5	=ЕСЛИ((И(B7<2;B7>1;C7<3));»Нужен»;»Не нужен»)	=ЕСЛИ(D7=»Нужен»;0,2+0,2;0,2)	=E7*30
6	3	3,7	=ЕСЛИ((И(B8<2;B8>1;C8<3));»Нужен»;»Не нужен»)	=ЕСЛИ(D8=»Нужен»;0,2+0,2;0,2)	=E8*30
7	1,1	2,2	=ЕСЛИ((И(B9<2;B9>1;C9<3));»Нужен»;»Не нужен»)	=ЕСЛИ(D9=»Нужен»;0,2+0,2;0,2)	=E9*30
8	2,2	3	=ЕСЛИ((И(B10<2;B10>1;C10<3));»Нужен»; «Не нужен»)	=ЕСЛИ(D10=»Нужен»;0,2+0,2;0,2)	=E10*30
9	2,8	3,6	=ЕСЛИ((И(B11<2;B11>1;C11<3));»Нужен»; «Не нужен»)	=ЕСЛИ(D11=»Нужен»;0,2+0,2;0,2)	=E11*30
10	3	2,8	=ЕСЛИ((И(B12<2;B12>1;C12<3));»Нужен»; «Не нужен»)	=ЕСЛИ(D12=»Нужен»;0,2+0,2;0,2)	=E12*30
Итого	= СУММ (F3:F12)

Вариант 3

Если вес пушного зверька в возрасте от 6-ти до
8-ми месяцев превышает 7 кг, то необходимо снизить
дневное потребление витаминного концентрата на
125 г. Количество зверьков, возраст и вес каждого
известны. Выяснить на сколько килограммов в
месяц снизится потребление витаминного
концентрата.

Режим формул

			Снижение дневного концентрата, кг	0,125
Номер	Возраст	Вес	Доп-ный концентрата	Л в день	Л в месяц
1	6,8	6,7	=ЕСЛИ((И(B3<8;B3>6;C3<7));»Нужен»;»Не нужен»)	=ЕСЛИ(D3=»Нужен»;0-0,125;0)	=E3*30
2	7,4	6,9	=ЕСЛИ((И(B4<8;B4>6;C4<7));»Нужен»;»Не нужен»)	=ЕСЛИ(D4=»Нужен»;0-0,125;0)	=E4*30
3	6,8	6,6	=ЕСЛИ((И(B5<8;B5>6;C5<7));»Нужен»;»Не нужен»)	=ЕСЛИ(D5=»Нужен»;0-0,125;0)	=E5*30
4	7,3	6,9	=ЕСЛИ((И(B6<8;B6>6;C6<7));»Нужен»;»Не нужен»)	=ЕСЛИ(D6=»Нужен»;0-0,125;0)	=E6*30
5	8,1	7,5	=ЕСЛИ((И(B7<8;B7>6;C7<7));»Нужен»;»Не нужен»)	=ЕСЛИ(D7=»Нужен»;0-0,125;0)	=E7*30
6	7,6	7,1	=ЕСЛИ((И(B8<8;B8>6;C8<7));»Нужен»;»Не нужен»)	=ЕСЛИ(D8=»Нужен»;0-0,125;0)	=E8*30
7	7	6,8	=ЕСЛИ((И(B9<8;B9>6;C9<7));»Нужен»;»Не нужен»)	=ЕСЛИ(D9=»Нужен»;0-0,125;0)	=E9*30
8	8,3	7,3	=ЕСЛИ((И(B10<8;B10>6;C10<7));»Нужен»;»Не нужен»)	=ЕСЛИ(D10=»Нужен»;0-0,125;0)	=E10*30
9	7,8	7,2	=ЕСЛИ((И(B11<8;B11>6;C11<7));»Нужен»;»Не нужен»)	=ЕСЛИ(D11=»Нужен»;0-0,125;0)	=E11*30
10	6,5	6,4	=ЕСЛИ((И(B12<8;B12>6;C12<7));»Нужен»;»Не нужен»)	=ЕСЛИ(D12=»Нужен»;0-0,125;0)	=E12*30
Итого	=СУММ(F3:F12)

Вариант 4

В доме проживают 10 жильцов. Подсчитать, сколько
каждый из них должен платить за электроэнергию и
определить суммарную плату для всех жильцов.
Известно, что 1 кВт электроэнергии стоит Х рублей,
а некоторые жильцы имеют 50% скидку при оплате.

Режим формул

	Стоимость 1 кВт	X
Жилец	Скидка 50%	Сумма к оплате
1	есть	=ЕСЛИ(B3=»есть»;0,5*C$1;$C$1)
2	есть	=ЕСЛИ(B4=»есть»;0,5*C$1;$C$1)
3	нет	=ЕСЛИ(B5=»есть»;0,5*C$1;$C$1)
4	нет	=ЕСЛИ(B6=»есть»;0,5*C$1;$C$1)
5	нет	=ЕСЛИ(B7=»есть»;0,5*C$1;$C$1)
6	нет	=ЕСЛИ(B8=»есть»;0,5*C$1;$C$1)
7	есть	=ЕСЛИ(B9=»есть»;0,5*C$1;$C$1)
8	нет	=ЕСЛИ(B10=»есть»;0,5*C$1;$C$1)
9	нет	=ЕСЛИ(B11=»есть»;0,5*C$1;$C$1)
10	есть	=ЕСЛИ(B12=»есть»;0,5*C$1;$C$1)
Итого	= СУММ (C3:C12)

Вариант 5

Торговый склад производит уценку хранящейся
продукции. Если продукция хранится на складе
дольше 10 месяцев, то она уценивается в 2 раза, а
если срок хранения превысил 6 месяцев, но не
достиг 10 месяцев, то — в 1,5 раза. Получить
ведомость уценки товара, которая должна включать
следующую информацию: наименование товара, срок
хранения, цена товара до уценки, цена товара
после уценки.

Режим формул

Продукт	Хранится на складе (мес.)	Цена до уценки	Цена после уценки
конфеты	12	100	=ЕСЛИ(B2>10;C2/2;ЕСЛИ(B2>6;C2/1,5;C2))
печенье	11	75	=ЕСЛИ(B3>10;C3/2;ЕСЛИ(B3>6;C3/1,5;C3))
пряники	7	30	=ЕСЛИ(B4>10;C4/2;ЕСЛИ(B4>6;C4/1,5;C4))
зефир	8	80	=ЕСЛИ(B5>10;C5/2;ЕСЛИ(B5>6;C5/1,5;C5))
хлеб	5	8	=ЕСЛИ(B6>10;C6/2;ЕСЛИ(B6>6;C6/1,5;C6))
масло	6	60	=ЕСЛИ(B7>10;C7/2;ЕСЛИ(B7>6;C7/1,5;C7))
макароны	10	15	=ЕСЛИ(B8>10;C8/2;ЕСЛИ(B8>6;C8/1,5;C8))
йогурт	9	10	=ЕСЛИ(B9>10;C9/2;ЕСЛИ(B9>6;C9/1,5;C9))
колбаса	8	140	=ЕСЛИ(B10>10;C10/2;ЕСЛИ(B10>6;C10/1,5;C10))
сыр	9	110	=ЕСЛИ(B11>10;C11/2;ЕСЛИ(B11>6;C11/1,5;C11))

Вариант 6.

В сельскохозяйственном кооперативе работают 10
сезонных рабочих. Собирают помидоры. Оплата
труда производится по количеству собранных
овощей. Дневная норма сбора составляет K
килограммов. Сбор 1 кг помидоров стоит Х рублей.
Сбор каждого килограмма сверх нормы
оплачивается в 2 раза дороже. Сколько денег в день
получит каждый рабочий за собранный урожай?

Режим формул

	Дневная норма	10
	1 кг помидоров(руб.)	30
Рабочий	Собрал	Деньги за день
1	8	=ЕСЛИ(B4<C$1;B4C$2;C$2C$1+(B4-10)*60)
2	9	=ЕСЛИ(B5<C$1;B5C$2;C$2C$1+(B5-10)*60)
3	7	=ЕСЛИ(B6<C$1;B6C$2;C$2C$1+(B6-10)*60)
4	8	=ЕСЛИ(B7<C$1;B7C$2;C$2C$1+(B7-10)*60)
5	13	=ЕСЛИ(B8<C$1;B8C$2;C$2C$1+(B8-10)*60)
6	5	=ЕСЛИ(B9<C$1;B9C$2;C$2C$1+(B9-10)*60)
7	11	=ЕСЛИ(B10<C$1;B10C$2;C$2C$1+(B10-10)*60)
8	6	=ЕСЛИ(B11<C$1;B11C$2;C$2C$1+(B11-10)*60)
9	15	=ЕСЛИ(B12<C$1;B12C$2;C$2C$1+(B12-10)*60)
10	12	=ЕСЛИ(B13<C$1;B13C$2;C$2C$1+(B13-10)*60)

Вариант 7.

Если количество баллов, полученных при
тестировании, не превышает 12, то это
соответствует оценке “2”; оценке “3”
соответствует количество баллов от 12 до 15; оценке
“4” -16 до 20; оценке “5” — свыше 20 баллов. Составить
ведомость тестирования, содержащую сведения
фамилия, количество баллов, оценка.

Режим формул

Фамилия	Количество баллов	Оценка
Воробьёв	10	=ЕСЛИ(B2<12;2;ЕСЛИ(B2<15;3;ЕСЛИ(B2<20;4;5)))
Сидоров	11	=ЕСЛИ(B3<12;2;ЕСЛИ(B3<15;3;ЕСЛИ(B3<20;4;5)))
Иванов	17	=ЕСЛИ(B4<12;2;ЕСЛИ(B4<15;3;ЕСЛИ(B4<20;4;5)))
Смирнов	25	=ЕСЛИ(B5<12;2;ЕСЛИ(B5<15;3;ЕСЛИ(B5<20;4;5)))
Синичкин	22	=ЕСЛИ(B6<12;2;ЕСЛИ(B6<15;3;ЕСЛИ(B6<20;4;5)))
Фадеев	18	=ЕСЛИ(B7<12;2;ЕСЛИ(B7<15;3;ЕСЛИ(B7<20;4;5)))
Петров	14	=ЕСЛИ(B8<12;2;ЕСЛИ(B8<15;3;ЕСЛИ(B8<20;4;5)))
Пучков	9	=ЕСЛИ(B9<12;2;ЕСЛИ(B9<15;3;ЕСЛИ(B9<20;4;5)))
Андреев	19	=ЕСЛИ(B10<12;2;ЕСЛИ(B10<15;3;ЕСЛИ(B10<20;4;5)))
Васильев	13	=ЕСЛИ(B11<12;2;ЕСЛИ(B11<15;3;ЕСЛИ(B11<20;4;5)))

Вариант 8.

Компания по снабжению электроэнергией взимает
плату с клиентов по тарифу: K рублей за 1 КВт/ч и Х
рублей за каждый КВт/ч сверх нормы, которая
составляет 50 КВт/ч. Услугами компании пользуются
10 клиентов. Подсчитать плату для каждого клиента.

Режим формул

		Стоимость 1 кВт (руб.)	K
		Норма, кВт/ч	50
		Стоимость 1 кВт сверх нормы	X
Жилец	Потратил	Сумма к оплате
1	40	=ЕСЛИ(B5<D$2;B5D$1;D$2D$1+(B5-D$2)*D$3)
2	45	=ЕСЛИ(B6<D$2;B6D$1;D$2D$1+(B6-D$2)*D$3)
3	42	=ЕСЛИ(B7<D$2;B7D$1;D$2D$1+(B7-D$2)*D$3)
4	51	=ЕСЛИ(B8<D$2;B8D$1;D$2D$1+(B8-D$2)*D$3)
5	68	=ЕСЛИ(B9<D$2;B9D$1;D$2D$1+(B9-D$2)*D$3)
6	47	=ЕСЛИ(B10<D$2;B10D$1;D$2D$1+(B10-D$2)*D$3)
7	55	=ЕСЛИ(B11<D$2;B11D$1;D$2D$1+(B11-D$2)*D$3)
8	23	=ЕСЛИ(B12<D$2;B12D$1;D$2D$1+(B12-D$2)*D$3)
9	70	=ЕСЛИ(B13<D$2;B13D$1;D$2D$1+(B13-D$2)*D$3)
10	46	=ЕСЛИ(B14<D$2;B14D$1;D$2D$1+(B14-D$2)*D$3)
Итого	= СУММ(C5:C14)

Вариант 9.

10 спортсменов-многоборцев принимают участие в
соревнованиях по 5 видам спорта. По каждому виду
спорта спортсмен набирает определенное
количество очков. Спортсмену присваивается
звание мастера, если он набрал в сумме не менее K
очков. Сколько спортсменов получило звание
мастера?

Режим формул


	Виды спорта	Звание мастера	K
Номер спортсмена	1	2	3	4	5	Всего очков
1	15	10	25	15	30	= СУММ (B4:F4)
2	20	9	21	14	29	= СУММ (B5:F5)
3	15	5	19	10	25	= СУММ (B6:F6)
4	18	6	22	13	30	= СУММ (B7:F7)
5	11	7	17	11	26	= СУММ (B8:F8)
6	8	10	24	15	25	= СУММ (B9:F9)
7	21	7	15	9	27	= СУММ (B10:F10)
8	19	8	25	15	20	= СУММ (B11:F11)
9	20	10	24	13	24	= СУММ (B12:F12)
10	16	4	20	10	28	= СУММ (B13:F13)
Звание получили	= СЧЁТ ЕСЛИ (G4:G13;»>90″)

Вариант 10.

10 учеников проходили тестирование по 5 темам
какого-либо предмета. Вычислить суммарный (по
всем темам) средний балл, полученный учениками.
Сколько учеников имеют суммарный балл ниже
среднего?

Режим формул

Фамилия	Тема 1	Тема 2	Тема 3	Тема 4	Тема 5	Ср.балл	Результат
Яковлев	5	4	5	5	5	=СУММ(B4:F4)/5	=ЕСЛИ(G4<G$14;»Ниже среднего»;»Выше среднего»)
Смирнов	4	5	4	4	5	=СУММ(B5:F5)/5	=ЕСЛИ(G5<G$14;»Ниже среднего»;»Выше среднего»)
Павлов	4	4	5	4	4	=СУММ(B6:F6)/5	=ЕСЛИ(G6<G$14;»Ниже среднего»;»Выше среднего»)
Иванов	5	4	5	3	4	=СУММ(B7:F7)/5	=ЕСЛИ(G7<G$14;»Ниже среднего»;»Выше среднего»)
Сидоров	4	4	3	3	3	=СУММ(B8:F8)/5	=ЕСЛИ(G8<G$14;»Ниже среднего»;»Выше среднего»)
Борисов	3	4	4	3	4	=СУММ(B9:F9)/5	=ЕСЛИ(G9<G$14;»Ниже среднего»;»Выше среднего»)
Петров	4	4	5	4	5	=СУММ(B10:F10)/5	=ЕСЛИ(G10<G$14;»Ниже среднего»;»Выше среднего»)
Макаров	5	4	4	5	5	=СУММ(B11:F11)/5	=ЕСЛИ(G11<G$14;»Ниже среднего»;»Выше среднего»)
Соколов	4	5	4	4	4	=СУММ(B12:F12)/5	=ЕСЛИ(G12<G$14;»Ниже среднего»;»Выше среднего»)
Воронин	4	3	3	3	4	=СУММ(B13:F13)/5	=ЕСЛИ(G13<G$14;»Ниже среднего»;»Выше среднего»)
				Суммарный ср. балл	=СУММ(G4:G13)/10	=СЧЁТЕСЛИ(H4:H13;»Ниже среднего»)

Вариант 11.

Билет на пригородном поезде стоит 5 монет, если
расстояние до станции не больше 20 км; 13 монет,
если расстояние больше 20 км, но не превышает 75 км;
20 монет, если расстояние больше 75 км. Составить
таблицу, содержащую следующие сведения: пункт
назначения, расстояние, стоимость билета.
Выяснить сколько станций находится в радиусе 50
км от города.

Режим формул

Пункт назначения	Расстояние	Стоимость билета
1	25	=ЕСЛИ(B3<20;5;ЕСЛИ(B3<75;13;20))
2	15	=ЕСЛИ(B4<20;5;ЕСЛИ(B4<75;13;20))
3	80	=ЕСЛИ(B5<20;5;ЕСЛИ(B5<75;13;20))
4	90	=ЕСЛИ(B6<20;5;ЕСЛИ(B6<75;13;20))
5	30	=ЕСЛИ(B7<20;5;ЕСЛИ(B7<75;13;20))
6	50	=ЕСЛИ(B8<20;5;ЕСЛИ(B8<75;13;20))
7	45	=ЕСЛИ(B9<20;5;ЕСЛИ(B9<75;13;20))
8	10	=ЕСЛИ(B10<20;5;ЕСЛИ(B10<75;13;20))
9	85	=ЕСЛИ(B11<20;5;ЕСЛИ(B11<75;13;20))
10	18	=ЕСЛИ(B12<20;5;ЕСЛИ(B12<75;13;20))
	Станций в 50 км	=СЧЁТЕСЛИ(B3:B12;»<50″)

Вариант 12.

Телефонная компания взимает плату за услуги
телефонной связи по следующему тарифу: 370 мин в
месяц оплачиваются как абонентская плата,
которая составляет 200 монет. За каждую минуту
сверх нормы необходимо платить по 2 монеты.
Составить ведомость оплаты услуг телефонной
связи для 10 жильцов за один месяц.

Режим формул

		Норма в месяц, мин	370
		Абонентская плата	200
		Мин. Сверх нормы	2
Жилец	Потратил, мин	Начислено
1	360	=ЕСЛИ(B5>D$1;D$2+(B5-D$1)*D$3;D$2)
2	300	=ЕСЛИ(B6>D$1;D$2+(B6-D$1)*D$3;D$2)
3	350	=ЕСЛИ(B7>D$1;D$2+(B7-D$1)*D$3;D$2)
4	400	=ЕСЛИ(B8>D$1;D$2+(B8-D$1)*D$3;D$2)
5	390	=ЕСЛИ(B9>D$1;D$2+(B9-D$1)*D$3;D$2)
6	200	=ЕСЛИ(B10>D$1;D$2+(B10-D$1)*D$3;D$2)
7	410	=ЕСЛИ(B11>D$1;D$2+(B11-D$1)*D$3;D$2)
8	450	=ЕСЛИ(B12>D$1;D$2+(B12-D$1)*D$3;D$2)
9	365	=ЕСЛИ(B13>D$1;D$2+(B13-D$1)*D$3;D$2)
10	250	=ЕСЛИ(B14>D$1;D$2+(B14-D$1)*D$3;D$2)
	Итого за месяц	= СУММ (C5:C14)

Вариант 13.

Покупатели магазина пользуются 10% скидками,
если покупка состоит более, чем из пяти
наименований товаров или стоимость покупки
превышает K рублей. Составить ведомость,
учитывающую скидки: покупатель, количество
наименований купленных; товаров, стоимости
покупки, стоимость покупки с учетом скидки.
Выяснить сколько покупателей сделали покупки,
стоимость которых превышает K рублей.

Режим формул

		Кол-во наим. тов. необх. для скидки	5
		Стоимость для скидки	K
Покупатель	Кол-во наименований купл. товаров	Стоимость	Стоим. Со скидкой
Яковлев	3	60	=ЕСЛИ(B4>D$1;C4-C40,1;ЕСЛИ(C4>D$2;C4-C40,1;C4))
Смирнов	1	12	=ЕСЛИ(B5>D$1;C5-C50,1;ЕСЛИ(C5>D$2;C5-C50,1;C5))
Павлов	6	65	=ЕСЛИ(B6>D$1;C6-C60,1;ЕСЛИ(C6>D$2;C6-C60,1;C6))
Иванов	5	40	=ЕСЛИ(B7>D$1;C7-C70,1;ЕСЛИ(C7>D$2;C7-C70,1;C7))
Сидоров	2	100	=ЕСЛИ(B8>D$1;C8-C80,1;ЕСЛИ(C8>D$2;C8-C80,1;C8))
Борисов	4	35	=ЕСЛИ(B9>D$1;C9-C90,1;ЕСЛИ(C9>D$2;C9-C90,1;C9))
Петров	3	26	=ЕСЛИ(B10>D$1;C10-C100,1;ЕСЛИ(C10>D$2;C10-C100,1;C10))
Макаров	1	22	=ЕСЛИ(B11>D$1;C11-C110,1;ЕСЛИ(C11>D$2;C11-C110,1;C11))
Соколов	4	55	= ЕСЛИ (B12>D$1;C12-C120,1;ЕСЛИ(C12>D$2;C12-C120,1;C12))
Воронин	8	120	=ЕСЛИ(B13>D$1;C13-C130,1;ЕСЛИ(C13>D$2;C13-C130,1;C13))
	Покупателей потративших больше 50 руб.	= СЧЁТЕСЛИ(C4:C13;»>50″)

Вариант 14.

Компания по снабжению электроэнергией взимает
плату с клиентов по тарифу: K рублей за 1 кВт/ч за
первые 500 кВт/ч; K2 рублей за 1 кВт/ч, если
потребление свыше 500 кВт/ч, но не превышает 1000
кВт/ч; K3 рублей за 1 кВт/ч, если потребление свыше
1000 кВт/ч. Услугами компании пользуются 10
клиентов. Подсчитать плату для каждого клиента и
суммарную плату. Сколько клиентов потребляет
более 1000 кВт/ч.

Режим формул

	Стоимость 1кВт/ч	свыше 500кВт/ч	K
	от 500 до 1000кВт/ч	K2
	свыше 1000кВт/ч	K3
Клиент	Потребляет, кВт/ч	Плата
1	450	=ЕСЛИ(B5<500;B5D$1;ЕСЛИ(B5>500;B5D$2;B5*D$3))
2	650	=ЕСЛИ(B6<500;B6D$1;ЕСЛИ(B6>500;B6D$2;B6*D$3))
3	400	=ЕСЛИ(B7<500;B7D$1;ЕСЛИ(B7>500;B7D$2;B7*D$3))
4	1100	=ЕСЛИ(B8<500;B8D$1;ЕСЛИ(B8>500;B8D$2;B8*D$3))
5	750	=ЕСЛИ(B9<500;B9D$1;ЕСЛИ(B9>500;B9D$2;B9*D$3))
6	1050	=ЕСЛИ(B10<500;B10D$1;ЕСЛИ(B10>500;B10D$2;B10*D$3))
7	380	=ЕСЛИ(B11<500;B11D$1;ЕСЛИ(B11>500;B11D$2;B11*D$3))
8	620	=ЕСЛИ(B12<500;B12D$1;ЕСЛИ(B12>500;B12D$2;B12*D$3))
9	480	=ЕСЛИ(B13<500;B13D$1;ЕСЛИ(B13>500;B13D$2;B13*D$3))
10	1200	=ЕСЛИ(B14<500;B14D$1;ЕСЛИ(B14>500;B14D$2;B14*D$3))
	Суммарная плата	= СУММ(C5:C14)
	Клиентов потреб.>1000кВт/ч	= СЧЁТЕСЛИ(B5:B14;»>1000″)

Вариант 15.

При температуре воздуха зимой до -20°С
потребление угли тепловой станцией составляет K
тонн в день При температуре воздуха от -30° С до -20°
С дневное потребление увеличивается на 5 тонн,
если температура воздуха ниже -30° С, то
потребление увеличивается еще на 7 тонн.
Составить таблицу потребления угля тепловой
станцией за неделю. Сколько дней температура
воздуха была ниже -30° С?

Режим формул

		Потреб. При t>-20	K
День	Температура	Потребление угля,т
1	-35	=ЕСЛИ(B4>-20;D$2;ЕСЛИ(B4>-30;D$2+5;D$2+7))
2	-32	=ЕСЛИ(B5>-20;D$2;ЕСЛИ(B5>-30;D$2+5;D$2+7))
3	-28	=ЕСЛИ(B6>-20;D$2;ЕСЛИ(B6>-30;D$2+5;D$2+7))
4	-25	=ЕСЛИ(B7>-20;D$2;ЕСЛИ(B7>-30;D$2+5;D$2+7))
5	-22	=ЕСЛИ(B8>-20;D$2;ЕСЛИ(B8>-30;D$2+5;D$2+7))
6	-21	=ЕСЛИ(B9>-20;D$2;ЕСЛИ(B9>-30;D$2+5;D$2+7))
7	-19	=ЕСЛИ(B10>-20;D$2;ЕСЛИ(B10>-30;D$2+5;D$2+7))
8	-16	=ЕСЛИ(B11>-20;D$2;ЕСЛИ(B11>-30;D$2+5;D$2+7))
9	-10	=ЕСЛИ(B12>-20;D$2;ЕСЛИ(B12>-30;D$2+5;D$2+7))
10	-5	=ЕСЛИ(B13>-20;D$2;ЕСЛИ(B13>-30;D$2+5;D$2+7))
	За неделю,т	= СУММ (C4:C13)*7
	Темп.<-30,дней	= СЧЁТЕСЛИ(B4:B13;»<-30″)

Приложение1

Источник

Тема Табличный процессор EXCEL-2003

Логические функции

Задание:

1. Логическая функция ЕСЛИ

=ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь) — принимает одно из двух указанных значений, в зависимости от
выполнения условия.

Если логическое выражение верно, то функция принимает первое значение.
Если логическое выражение не верно, то функция принимает второе значение.

Пример: Студенты сдали экзамен. Требуется
сделать заключение: если оценка 5, 4 или 3, то экзамен сдан, иначе –
переэкзаменовка.

Запустить Excel и создать таблицу по образцу.

В ячейку D2 ввести формулу =ЕСЛИ(С2>=3;”Экзамен сдан”;”Переэкзаменовка”)

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

2. Логическая функция И

И(логическое_значение1;
логическое_значение2;…) — принимает значение ИСТИНА,
если все аргументы имеют значение ИСТИНА; принимает значение ЛОЖЬ, если хотя бы
один аргумент имеет значение ЛОЖЬ.

Пример: Для студентов, сдававших экзамен,
вывести сообщение «Оценка получена», для студентов, проигнорировавших экзамен,
вывести сообщение «Не пришел».

Скопировать таблицу на Лист2 и удалить формулы в ячейках D2:D11. Изменить оценки и двум студентам вместо
оценок ввести «неявка».

В ячейку D2 ввести формулу =ЕСЛИ(И(С2>=2;С2<=5);”Оценка получена”;”Не пришел”)

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

3. Логическая функция ИЛИ

ИЛИ(логическое_значение1;
логическое_значение2; …) —
принимает значение ИСТИНА, если хотя бы один из аргументов имеет значение
ИСТИНА; принимает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

Пример: Для студентов, сдавших экзамен на 3, 4
или 5, вывести сообщение «Оценка положительная», остальным студентам – «Очень
плохо».

Скопировать таблицу на Лист3 и удалить формулы в ячейках D2:D11.

В ячейку D2 ввести формулу =ЕСЛИ(ИЛИ(С2=3;С2=4;С2=5);”Оценка положительная”;”Очень
плохо”)

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

4. Статистическая функция СЧЕТЕСЛИ

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

где:

—
Диапазон – блок ячеек, содержащий проверяемые значения;

—
Критерий – константа или условие проверки;

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

Пример: Необходимо
подсчитать количество различных оценок по результатам экзамена.

Вставить
Лист4, переместить его на место (за Лист3). Скопировать на этот лист диапазон
ячеек А1:С11 с листа 1. Оформить ячейки столбца D по образцу.

В ячейку Е2 ввести формулу =СЧЕТЕСЛИ(С2:С11;5). В ячейку Е3
ввести формулу, используя мастер функций.

По
аналогии ввести формулы в ячейки Е4 и Е5. При вводе формулы в ячейку Е6 в поле Критерий
ввести неявка.

Показать
результаты преподавателю, сохранить файл с именем Стат-ф-4.

5. Математическая функция СУММЕСЛИ

=СУММЕСЛИ( Диапазон; Критерий [;
Диапазон суммирования ] )

где:

— Диапазон
– диапазон проверяемых значений;

— Критерий
– константа или условие проверки;

— Диапазон
суммирования – обрабатываемый диапазон. Если он отсутствует, то 1-й
аргумент функции становится и проверяемым и обрабатываемым диапазоном;

— [ ]
– указание на то, что данный аргумент функции может отсутствовать.

Вначале
рассмотрим пример использования этой функции с двумя аргументами.

Вставить
Лист5. Разместить листы по порядку. Оформить таблицу по образцу.

В
ячейку А10 ввести текст Сумма, если >4000. В ячейку С10 ввести
формулу =СУММЕСЛИ(С2:С8;»>4000″).

Из
примера видно, что суммироваться будут значения только тех ячеек диапазона С2:С8,
которые отвечают условию «>4000». В данном случае диапазон C2:C8 и проверяется
и обрабатывается.

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

В
ячейку А11 ввести текст Сумма для НР. В ячейку С11 ввести формулу =СУММЕСЛИ(А2:А8; «НР»;С2:С8).

В данном случае производится суммирование ячеек диапазона С2:С8
только в тех случаях, если в соседней ячейке диапазона А2:А8 находится
запись «НР». Здесь диапазон А2:А8 проверяется, а диапазон С2:С8
обрабатываются.

Показать
результаты преподавателю, сохранить файл с именем Мат-ф-5.

6. Вложенная функция ЕСЛИ

Вставить
Лист6. Разместить листы по порядку. Оформить таблицу по образцу.

Предположим, что премия начисляется при следующих условиях:

—
если средний балл меньше 3, то премия равна 0,

—
если средний балл больше 3, но меньше 4.5, то
премия равна 500р,

—
если средний балл больше 4.5, то премия равна 800р.

Поместить курсор в ячейку D2 и вызвать
Мастера функций.

На первом шаге в левом окне выбрать категорию Логические.
В правом окне выбрать функцию ЕСЛИ.

Щелкнуть
по кнопке ОК. Появится окно функции ЕСЛИ.

На втором шаге Мастера функций в поле Логическое_выражение
надо ввести условие, во втором поле — значение в случае Истина, а в третьем
поле — значение в случае Ложь.

Значение среднего балла находится в ячейке С2. Поэтому надо
щелкнуть мышью по кнопке с красной стрелочкой в верхнем поле справа. Диалоговое
окно свернется в строку.

В ячейке D2 будет отображено начало
формулы. Надо щелкнуть по ячейке С2, а затем по кнопке с красной стрелочкой для
возврата в окно функции ЕСЛИ. В формулу будет помещен адрес ячейки С2. Если
щелкнуть нечаянно по другой ячейке, то адрес этой ячейки также будет помещен в
формулу. Повнимательнее!

Теперь с помощью клавиатуры надо дополнить условие С2<3.
Можно было в первом поле ввести это условие с клавиатуры, не пользуясь кнопкой
с красной стрелочкой (кнопкой свертки).

Во второе поле ввести значение для случая, когда C2<3, то есть, ввести 0.

Нажать клавишу Tab или щелкнуть мышкой в третьем поле.

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

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

В первое поле нового окна ввести второе условие C2<4,5, во второе поле ввести значение 500, а в третье поле ввести
значение 800.

Щелкнуть по кнопке ОК.

В ячейку D2 будет помещен результат расчета, а в строке
формул отображена расчетная формула с вложенной функцией ЕСЛИ. Скопировать эту
формулу в ячейки D3:D5 и посмотреть
результаты расчета.

Показать преподавателю и сохранить файл с именем Если-влож.

Вставить Лист7. Разместить листы по порядку. Скопировать на
Лист7 таблицу с Листа6, удалить в столбце Премия формулы. Добавить в
список фамилию Чернов, средний балл 1,5. Исправить Белову средний
балл 0,8.

Определить премию в зависимости от среднего балла при
следующих условиях:

если <1, вывести сообщение «штраф»,

если >1, но <2, то премия равна 0,

если >2, но <3, то премия равна 100,

если >3, но <4, то премия равна 200,

если >4, то премия равна 300.

Получить решение, показать преподавателю и сохранить файл с
именем Если-сам.

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

Источник

Контрольная работа «Логические функции»

Вариант 1

1. Торговый агент в качестве оплаты своих услуг получает процент от суммы совершенной сделки.

Вознаграждение составит:

5 % от суммы сделки, если сумма сделки до 10000 руб.,

8 % – от 10000 руб. до 20000 руб.,

12 % – 20000 руб. и более.

Построить в Excel формулу с использованием логических функций для автоматического расчета сумм выплат торговому агенту. Числовые данные представить в денежном формате.

№ п/п	Вид сделки	Сумма сделки (в руб.)	Процент комиссионных (в руб.)
1	Продажа телевизора	9 900 р.
2	Продажа стиральной машины	12 300 р.
3	Кредит на комплект бытовой техники для кухни	85 000 р.
4	Продажа пылесоса	4 500 р.
5	Продажа электромясорубки	3 450 р.
ИТОГО

2. Рассчитайте 10 значений функции y=(a*x2-1)/(a*x2+1) для значений от x=1 с шагом h=2 при a=3 и построить график этой функции.

3. Подсчитайте, используя функции категории «Дата и время» сколько дней прошло со дня вашего рождения до сегодняшнего дня.

Вариант 2

1. Студентам, успешно сдавшим сессию (без 3) начисляется стипендия в размере 750 руб., студентам-отличникам – 1000 руб., староста получает доплату в размере 100 руб.

Построить автоматизированную ведомость начисления стипендии всем категориям студентов, если в сессию они сдавали 4 экзамена. Формула расчета составляется с использованием логических функций. Числовые данные представить в денежном формате.

№ п/п	ФИО	Математика	История	Информатика	Экономическая теория	Размер стипендии	Примечание
1	Будницкий	3	4	4	4
2	Румянцев	5	5	5	5
3	Глумов	5	4	5	5		староста
4	Малинин	3	3	4	4
5	Аликшеев	5	5	5	5

2. Рассчитать 10 значений функции y=2*x2+x для значений от x=1 с шагом h=1 и построить график этой функции.

Вариант 3

1. Стоимость размещения рекламы в журнале в расчете за 1 см2 составляет:

на обложке 50 руб.,

на всех остальных страницах – 40 руб.

Если площадь объявления превышает 100 см2, клиенту предоставляется скидка 5 %

Построить в Excel формулу для расчета стоимости размещения рекламы с учетом скидки с использованием логических функций. Числовые данные представить в денежном формате.

код	Вид страницы	Цена за 1 см2	Скидка
01	Обложка	50 р.	5%
02	Страница	40 р.

№ п/п	Клиент	Размер объявления в см2	Тип страницы	Стоимость размещения с учетом скидки
1	РИА Компас	20	01
2	Энерготехсервис ЗАО	50	02
3	Эрго ООО	200	02
4	Ювел ООО	120	01
Итого

2. Построить таблицу значений функции y=sin(2*x2+x) для x[-;] с шагом h=/4 и график этой функции.

Вариант 4

1. В течение недели проводились занятия с группой студентов: проведено 5 лабораторных занятий и контрольная работа.

Студент допускается к зачету, если он выполнил все лабораторные и балл за контрольную работу выше 3.

Студент получает зачет-автомат, если выполнил все лабораторные и балл за контрольную работу не менее 4.

Студент допускается к зачету, если он выполнил 80% от числа всех лабораторных и балл за контрольную работу выше 4

Во всех остальных случаях студент к зачету не допущен.

Составить автоматизированную ведомость допуска к зачету при поставленных условиях. При составлении формулы в графе «Допуск к зачету» использовать логические функции.

№ п/п	ФИО	1 лб	2 лб	3 лб	4 лб	5 лб	Контрол. работа	Допуск к зачету
1	Булимов	+	+	+	—	—	4
2	Дворников	+	+	+	—	+	5
3	Исаков	+	+	+	+	—	3
4	Рыбкина	+	+	+	+	+	4
5	Лужбина	+	+	+	+	+	5
6	Демиденко	+	+	+	+	+

2. Построить таблицу значений функции y=cos(2*x) 2 для x[-;] с шагом h=/5 и график данной функции.

Вариант 5

1. По договору с банком процентные ставки по вкладам до востребования составляют 4% годовых, по срочным вкладам со сроком 1 год для сумм до 50 тыс. руб. составляют 9%, свыше 50 тыс. руб. – 12% годовых,

Составить автоматизированную ведомость расчета по процентам при поставленных условиях.

Числовые данные представить в денежном формате.

Код вклада	Тип вклада	% годовой ставки
01	Срочный до 50 тыс. руб.	9%
02	Срочный свыше 50 тыс. руб.	12%
03	до востребования	4%
№ п/п	Клиент	Код вклада	Сумма вклада (в руб.)	Сумма % по вкладу
1	Гальямов	03	10 000 р.
2	Уварова	01	30 000 р.
3	Терехова	01	60 000 р.
4	Булдаков	02	80 000 р.
5	Давыдов	03	35 000 р.
6	Фомин	02	110 000 р.

2. Построить таблицу значений функции y=20*x 2 + x5 для x[-10;10] с шагом h=2 и график этой функции.

Вариант 6

1. Вычислить в Excel предельные расходы на рекламу, принимаемые при налогообложении:

если объем выручки от реализации продукции составил менее 3 000 000 руб., тогда лимит расходов на рекламу составляет 2% от выручки,
если объем выручки от реализации продукции составил от 3 000 000 до 60 000 000 руб., тогда лимит расходов на рекламу вычисляется как сумма 40 000+1% с суммы выручки, превышающей 3 000 000 руб.,
если объем выручки от реализации продукции превысил 60 000 00, лимит расходов на рекламу вычисляется как сумма 550 000+0,5% суммы выручки, превышающей 60 000 000 руб.

Для вычисления указанных сумм использовать логические функции. Числовые данные представить в денежном формате.

№ п/п	№ квартала	Объем продаж в руб.	Предельные расходы на рекламу
1	I	6 900 900 р.
2	II	26 200 000 р.
3	III	60 000 000 р.
4	IV	85 000 000 р.
Итого за год

2. Построить таблицу значений функции y=6*x 3 – x2 для x[-8;8] с шагом h=0,2 и график этой функции.

Вариант 7

1. Бригада рабочих выполняла работы на условиях почасовой оплаты в соответствии тарифами почасовой оплаты труда работника соответствующего разряда: 1-й разряд – 150 руб./час., 2-й разряд – 100 руб./час., 3-й разряд – 50 руб./час.

Если работник выполняет работу повышенной сложности или во вредных условиях труда, то ему начисляется надбавка 5% к начисленной сумме.

Составьте в Excel автоматизированную расчетную таблицу с учетом всех факторов и выполните начисления. Применить встроенные логические функции. Числовые данные представить в денежном формате.

	Разряд	1-й	2-й	3-й
	Стоимость часа	150 р.	100 р.	50 р.
Доплата за особые условия труда	5%

№ п/п	ФИО	Разряд	Отработано часов	Начислено за часы	Особые условия труда	Сумма доплаты	Итого к выплате
1	Булимов	1	140		+
2	Дворников	2	122
3	Исаков	3	144		+
4	Рыбкина	2	166
5	Лужбина	3	124		+
6	Демиденко	1	120
ИТОГО

Построить таблицу значений функции y=2*x 2 + x4 для x[-10;2] с шагом h=0,5 и график этой функции.

Вариант 8

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

8-ми часовой рабочий день и 5-дневная рабочая неделя.
6-ти часовой рабочий день и 6-дневная рабочая неделя.

Числовые данные представить в денежном формате.

код	Тип недели
А	5 дней по 8 час.
В	6 дней по 6 час.

№ п/п	ФИО	Тип недели	Оклад	Стоимость 1 часа работы	Отработано часов	Начислено за часы
1	Булимов	А	3000 р.		80
2	Дворников	А	4000 р.		48
3	Исаков	В	2000 р.		48
4	Рыбкина	В	2300 р.		80
5	Лужбина	А	3500 р.		60
6	Демиденко	В	3460 р.		50
ИТОГО

2. Построить таблицу значений функции y=sin(2*x -1) 2 для x[-2;0] с шагом h=/3 и график данной функции.

Вариант 9

1. Продавец в качестве доплаты получает процент от суммы совершенной покупки.

Вознаграждение составит:

3 % от суммы сделки, если сумма сделки до 5000 руб.,

5 % – от 5000 руб. до 10000 руб.,

10 % – 10000 руб. и более.

№ п/п	Наименование проданного товара	Сумма покупки (в руб.)	Процент доплаты (в руб.)
1	Телевизора	5 900 р.
2	Стиральная машина	10 300 р.
3	Электроплита	15 000 р.
4	Пылесос	4 500 р.
5	Миксер	2 450 р.
ИТОГО

2. Рассчитайте 10 значений функции y=(8*x2-4)/(8*x2+4) для значений от x=1 до x=10 с шагом h=0,5 и построить график этой функции.

Вариант 10

1. Студентам, успешно сдавшим сессию (без 3) начисляется стипендия в размере 750 руб., студентам-отличникам – 1000 руб. Построить автоматизированную ведомость начисления стипендии всем категориям студентов, если в сессию они сдавали 4 экзамена. Формула расчета составляется с использованием логических функций. Числовые данные представить в денежном формате.

Составить формулу автоматического вычисления количества студентов, получивших 5, 4, 3 по каждому предмету соответственно.

№ п/п	ФИО	Математика	История	Информатика	Экономическая теория	Размер стипендии
1	Шумахер	3	4	4	4
2	Рузвельт	5	5	5	5
3	Фомин	5	4	5	5
4	Шеремет	3	3	4	4
5	Казанцев	5	5	5	5
Кол-во уч-ся на «5»
Кол-во на «4»
Кол-во на «3»

2. Рассчитать 10 значений функции y=8*x2-3x для значений от x=-10 до х=10 с шагом h=0,2 и построить график этой функции.

Вариант 11

1. Стоимость размещения рекламы в печатном издании в расчете за 1 см2 составляет:

на обложке 40 руб.,

на всех остальных страницах – 30 руб.

Если площадь объявления превышает 80 см2, клиенту предоставляется скидка 3 %

код	Вид страницы	Цена за 1 см2	Скидка
01	Обложка	40 р.	3%
02	Страница	30 р.

№ п/п	Клиент	Размер объявления в см2	Тип страницы	Стоимость размещения с учетом скидки
1	РИА Компас	20	01
2	Энерготехсервис ЗАО	50	02
3	Эрго ООО	100	02
4	Ювел ООО	80	01
Итого

2. Построить таблицу значений функции y=sin(2*x2) для x[-2;0] с заданным шагом по 10 точкам и график этой функции.

Вариант 12

1. В течение недели проводились занятия с группой студентов: проведено 4 лабораторных занятия и контрольная работа.

Студент допускается к зачету, если средний балл за лабораторные задания и оценка за контрольную работу выше 3.

Студент получает зачет-автомат, если средний балл за лабораторные задания свыше 4,5 и балл за контрольную работу не менее 4.

Во всех остальных случаях студент к зачету не допущен. Средний балл рассчитывается с округлением до ближайшего меньшего целого числа (применить соответствующую функцию Excel)

Составить автоматизированную ведомость допуска к зачету при поставленных условиях.

При составлении формулы в графе «Допуск к зачету» использовать логические функции.

№ п/п	ФИО	1 лб	2 лб	3 лб	4 лб	Средний балл по лаб. раб.	Контрол. работа	Допуск к зачету
1	Булимов	3	3	3	3		4
2	Дворников	4	4	4	5		5
3	Исаков	5	5	5	5		5
4	Рыбкина	3	4	3	4		3
5	Лужбина	3	2	2	3		5
6	Демиденко	3	2	2	3		3

2. Построить таблицу значений функции y=sin(2*x-3) 2 для x[-;0] с шагом h=/5 и график данной функции.

Вариант 13

1. По договору с банком процентные ставки по вкладам до востребования составляют 7% годовых, по срочным вкладам со сроком 1 год для сумм до 80 тыс. руб. составляют 9%, свыше 80 тыс. руб. – 10% годовых,

Составить автоматизированную ведомость расчета по процентам при поставленных условиях. Числовые данные представить в денежном формате.

Код вклада	Тип вклада	% годовой ставки
01	Срочный до 80 тыс. руб.	9%
02	Срочный свыше 80 тыс. руб.	10%
03	до востребования	7%
№ п/п	Клиент	Код вклада	Сумма вклада (в руб.)	Сумма % по вкладу
1	Гальямов	03	70 000 р.
2	Уварова	01	70 000 р.
3	Терехова	01	80 000 р.
4	Булдаков	02	90 000 р.
5	Давыдов	03	45 000 р.
6	Фомин	02	110 000 р.

2. Построить таблицу значений функции y=x 2 + 10x5 для x[-10;10] с шагом h=2 и график этой функции.

Вариант 14

1. Вычислить в Excel предельные расходы на рекламу, принимаемые при налогообложении:

если объем выручки от реализации продукции составил менее 2 млн. руб., тогда лимит расходов на рекламу составляет 2% от выручки,
если объем выручки от реализации продукции составил от 2 млн. до 50 млн. руб., тогда лимит расходов на рекламу вычисляется как сумма 40 тыс.+1% с суммы выручки, превышающей 2 млн. руб.,
если объем выручки от реализации продукции превысил 50 млн., лимит расходов на рекламу вычисляется как сумма 530 тыс.+0,5% суммы выручки, превышающей 50 млн. руб.

№ п/п	Предприятие	Объем продаж в руб.	Предельные расходы на рекламу
1	Ларде ООО	18 900 900 р.
2	Алтан НПФ	12 200 000 р.
3	Алтан	40 000 000 р.
4	Администрация края	75 000 000 р.

2. Построить таблицу значений функции y=6*x 3 – x2 для x[-5;5] с шагом h=0,1 и график этой функции.

Вариант 15

1. Бригада рабочих выполняла работы на условиях почасовой оплаты в соответствии тарифами почасовой оплаты труда работника соответствующего разряда: 1-й разряд – 200 руб./час., 2-й разряд – 150 руб./час., 3-й разряд – 80 руб./час.

Если работник выполняет работу повышенной сложности или во вредных условиях труда, то ему начисляется надбавка 3% к начисленной сумме.

	Разряд	1-й	2-й	3-й
	Стоимость часа	200 р.	150 р.	80 р.
Доплата за особые условия труда	3%

№ п/п	ФИО	Разряд	Отработано часов	Начислено за часы	Особые условия труда	Сумма доплаты	Итого к выплате
1	Булимов	1	240		+
2	Дворников	2	172
3	Исаков	3	154		+
4	Рыбкина	2	176
5	Лужбина	3	146		+
6	Демиденко	1	134
ИТОГО

Построить таблицу значений функции y=x 2 -2 x4 для x[-10;0] с шагом h=0,2и график этой функции.

Вариант 16

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

8-ми часовой рабочий день и 5-дневная рабочая неделя.
6-ти часовой рабочий день и 6-дневная рабочая неделя.

Числовые данные представить в денежном формате.

код	Тип недели
I	5 дней по 8 час.
II	6 дней по 6 час.

№ п/п	ФИО	Тип недели	Оклад	Стоимость 1 часа работы	Отработано часов	Начислено за часы
1	Булимов	I	3000 р.		80
2	Дворников	II	4000 р.		48
3	Исаков	I	2000 р.		48
4	Рыбкина	I	2300 р.		80
5	Лужбина	II	3500 р.		60
6	Демиденко	II	3460 р.		50
IИТОГО

2. Построить таблицу значений функции y=sin(3*x +2) 2 для x[-;0] с шагом h=/6 и график данной функции.

Источник

Варианты контрольных работ

по EXCEL

Вариант 1

Составьте таблицу начисления заработной платы работникам МП «КЛАСС». Результаты округлите до 2-х знаков после запятой.

N п/п	Ф. И. О.	Тарифный разряд	Процент выполнения плана	Тарифная ставка	Заработная плата с премией
1	Пряхин А. Е.	3	102
2	Войтенко А.Ф.	2	98
3	Суворов И. Н.	1	114
4	Абрамов П. А.	1	100
5	Дремов Е. Л.	3	100
6	Сухов К. О.	2	94
7	Попов Т. Г.	3	100
	Итого

Формулы для расчетов:

Тарифная ставка определяется исходя из следующего:

- 1200 руб. для 1 разряда;
- 1500 руб. для 2 разряда;
- 2000 руб. для 3 разряда.

Размер премиальных определяется исходя из следующего:

— выполнение плана ниже 100% — премия не назначается (равна нулю);

— выполнение плана 100-110% — премия 30% от Тарифной ставки;

— выполнение плана выше 110% — премия 40% от Тарифной ставки.

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

Используя расширенный фильтр, сформируйте список работников, выполнивших и перевыполнивших план.
Используя функцию категории «Работа с базой данных» БДСУММ, подсчитайте суммы заработной платы работников в зависимости от тарифного разряда.
Постройте объемную круговую диаграмму начисления заработной платы работникам.

Проанализируйте динамику поступления товаров от поставщиков:

Поставщики	2004г. (млн.руб.)	2005г. (млн.руб.)	Превышение (млн.руб.)	В % к 2004г.	Удельный вес в 2004г.	Удельный вес в 2005г.	Изменение удельного веса
СП «Изотоп»	16,6	16,9
АОЗТ «Чипы»	23,4	32,1
ООО «Термо»	0,96	1,2
АО «Роника»	7,5	6,4
СП «Левел»	16,7	18,2
Всего

Формулы для расчетов:

Изменение удельного веса определяется исходя из следующего:

- «равны«, если Уд. вес 2005г. равен уд. весу 2004г.;
- «больше«, если Уд. вес 2005г. больше уд. веса 2004г.;
- «меньше«, если Уд. вес 2005г. меньше уд. веса 2004г.

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

Используя расширенный фильтр, сформируйте список поставщиков, у которых удельный вес в 2004 и 2005 годах не превышал 0,5.
Используя функцию категории «Работа с базой данных» БСЧЕТ, подсчитайте количество поставщиков, у которых значение превышение не больше 0,5млн. руб.
Постройтеобъемнуюгистограмму динамики удельного веса поступления товаров в 2004 — 2005 гг. по поставщикам.

Рассчитайте начисление стипендии студентам по итогам сессии. Результаты округлите до 2-х знаков после запятой.

N п/п	Ф.И.О.	Информатика	Математика	Ин. Язык	Надбавка	Начисление стипендии
1	Авдеева А.В.	5	4	5
2	Бесков Р.О.	4	3	3

Формулы для расчетов:

Размерстипендии составляет 2 МРОТ (минимальный размер оплаты труда). Стипендия не назначается, т. е. равна «0», если есть хотя бы одна «2».

Надбавка рассчитывается исходя из следующего:

50%, если все экзамены сданы на «5»;
25%, если есть одна «4» (при остальных «5»).

Для заполнения столбца Надбавка используйте функцию ЕСЛИ из категории «Логические».

Используя расширенный фильтр, сформируйте список студентов, сдавших все экзамены только на 4 и 5.
Используя функцию категории«Работа с базой данных» БСЧЕТ, подсчитайте количество студентов, не получивших надбавку.
Постройтеобъемнуюкруговую диаграмму начисления стипендии.

Рассчитайте доход от реализации колбасных изделий АОЗТ «Мясная лавка». Результаты округлите до 2-х знаков после запятой, используя функцию ОКРУГ.

Наименование изделий	Объем производства (т)	Цена за кг (руб.)	Торгово-сбытовая скидка (%)	Цена со скидкой (руб.)	Сумма с учетом скидки (руб.)
Колбаса пермская, п/к, 1с	6	59
Колбаса одесская, п/к, 1с	12	83
Колбаса краковская, п/к, в/с	4	90
Колбаски охотничьи, п/к, в/с	2	99
Колбаса сервелат п/к, в/с	3	110
ИТОГО

Формулы для расчетов:

Торгово-сбытовая скидка рассчитывается исходя из следующего:

- 0.5%, если Цена за кгменее 60 руб.;
- 5%, если Цена за кгот 60 до 80 руб.;
- 8%, если Цена за кг более 80 руб.

Для заполнения столбца Торгово-сбытовая скидка используйте функцию ЕСЛИ из категории «Логические».

Используя расширенный фильтр, сформируйте список наименований изделий, объем производства которых составляет от 5 до 10 тонн.
Используя функцию категории «Работа с базой данных» БДСУММ, подсчитайте сумму от реализации колбасных изделий, у которых торгово-сбытовая скидка больше или равна 8%.
Постройтеобъемнуюгистограмму изменения цены по изделиям.

Заполните накопительную ведомость по переоценке основных средств производства (млн. руб.).

N п/п	Наименование объекта	Балансовая стои-мость	Износ	Остаточная стоимость	Восстановительная полная стоимость	Восстановительная остаточная стоимость
1	Заводоуправ-ление	13457	589,3
2	Диспетчерская	187,4	51,4
3	Цех 1	932,6	226,1
4	Цех 2	871,3	213,8
5	Цех 3	768,8	134,9
6	Склад 1	576,5	219,6
7	Склад 2	344,6	98,4
8	Склад 3	567,4	123,5
9	Склад 4	312,6	76,8
	Итого

Формулы для расчетов:

Восстановительная полная стоимость = балансовая стоимость * k

Восстановительная остаточная стоимость = остаточная стоимость * k

Коэффициентkопределяется исходя из следующего:

- k = 3.0, если Балансовая стоимость больше 500 млн. руб.;
- k = 2.0, в остальных случаях.

Для заполнения столбца Восстановительная полная стоимость используйте функцию ЕСЛИ из категории «Логические».

Используярасширенный фильтр, сформируйте список наименований объектов, балансовая стоимость которых находится в пределах от 400 до 800 млн. руб.
Используя функцию категории «Работа с базой данных» БДСУММ, подсчитайте суммы восстановительной остаточной стоимости, износ объектов по которой составит не больше 100 млн. руб.
Постройтеобъемнуюгистограмму восстановительной полной и остаточной стоимостей по всем объектам.

Рассчитайте стоимость продукции с учетом скидки. Результаты округлите до 2-х знаков после запятой.

Номенклат. номер	Наименование прдукции	Количество (шт.)	Цена (тыс.руб)	Стоимость (тыс.руб.)	% скидки	Сумма скидки (тыс. руб.)	Стоимость с учетом скидки (тыс. руб.)
202	Монитор	5	12
201	Клавиатура	25	0,25
213	Дискета	100	0,02
335	Принтер	2	10
204	Сканер	1	8
	Итого

Формулы для расчетов:

Процент скидки определяется исходя из следующего:

- 1%, если Стоимостьменее 60 тыс. руб.;
- 7%, если Стоимостьот 60 до 100 тыс. руб.;
- 10%, если Стоимостьбольше 100 тыс. руб.

Для заполнения столбца Процент скидки используйте функцию ЕСЛИ из категории «Логические».

Используярасширенный фильтр, сформируйте список наименований продукции с теми номенклатурными номерами, по которым стоимость с учетом скидки находится в пределах от 5 до 10 тыс. руб.
Используя функцию категории «Работа с базой данных» БДСУММ подсчитайте общую сумму скидки для продукции с ценой больше 5тыс. руб.,
Постройте объемную гистограмму изменения стоимостей по наименованиям продукции.

Рассчитайте сумму вклада с начисленным процентом. Результаты округлите до 2-х знаков после запятой.

№ лицевого счета	Вид вклада	Сумма вклада (тыс. руб.)	Остаток вклада с начисленным %
Остаток входящий	Приход	Расход	Остаток исходящий
S3445	Срочный	45		4
F7654	Праздничный	54	6
R5467	До востребования	76	5	9
S8976	Срочный	53		3
R3484	До востребования	15	12	3
S7664	Срочный	4	5	5
	Итого:

Формулы для расчетов:

Остаток вклада с начисленным % рассчитывается исходя из следующего:

- Остаток исходящий + 2% от Остатка исходящего, для вклада до востребования;
- Остаток исходящий +5% от Остатка исходящего, для вклада праздничный;
- Остаток исходящий + 3% от Остатка исходящего,для вклада срочный.

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

Используярасширенный фильтр, сформируйте список номеров лицевых счетов, по которым имеется исходящий остаток больше 50 тыс. руб.

Используя функцию категории «Работа с базой данных» БДСУММ, подсчитайте по срочному виду вклада общую сумму остатков вкладов с начисленным процентом, если сумма расхода по данному вкладу меньше 5 тыс. руб.
Постройтеобъемнуюгистограмму изменения суммы вкладов.

Рассчитайте начисленную заработную плату сотрудникам малого предприятия.

Номер п/п	Ф. И. О.	Дата поступления на работу	Стаж работы	Зарплата (руб.)	Надбавка (руб.)	Премия (руб.)	Всего начислено (руб.)
1	Моторов А.А.	10.04.91		3000
2	Унтура О. И.	12.06.98		2500
3	Дискин Г. Т.	02.03.95		2000
4	Попова С. А.	17.02.92		1500
5	Скатт О. И.	15.01.99		1000
	Итого

Формулы для расчетов:

Стаж работы (полное число лет) = (Текущая дата – Дата поступления на работу)/ 365. Результат округлите до целого.

Надбавка рассчитывается исходя из следующего:

0, если Стаж работы меньше 5 лет;
5% от Зарплаты, если Стаж работы от 5 до 10 лет;
10% от Зарплаты, если Стаж работы больше 10 лет.

Для заполнения столбца Надбавка используйте функцию ЕСЛИ из категории «Логические».

Премия = 20% от (Зарплата + Надбавка).

Используярасширенный фильтр, сформируйте список сотрудников со стажем работы от 5 до 10 лет.
Используя функцию категории «Работа с базой данных» БСЧЕТ, определите количество сотрудников, у которых зарплата больше 1000 руб., а стаж работы больше 5 лет.
Постройтеобъемнуюгистограмму начисления зарплаты по сотрудникам.

Вариант 9

Рассчитайте доходы фирмы за два указанных года. Результаты округлите до 2-х знаков после запятой.

№ п/п	Модели фирм- производителей компьютеров	Доходы, млн. долл. 2003г.	Доходы, млн. долл. 2004г.	Торговая доля от продажи 2003г.	Торговая доля от продажи 2004г.	Оценка доли от продажи
2	Apple	80,2	84,5
3	NEC	78,6	90,5
4	Olivetti	41,3	66,0
5	Toshiba	70,0	104,9
	Всего:

Формулы для расчетов:

Торговая доля от продажи = Доход каждой модели / Всего

Оценкадоли от продажи определяется исходя из следующего:

- «равны«, если Доли от продажи 2003г. и 2004г. равны;
- «превышение«, если Доля от продажи 2003г. больше 2004г.;
- «уменьшение«, если Доля от продажи 2003г. меньше 2004г.

Для заполнения столбца используйте функцию ЕСЛИ из категории «Логические».

Используярасширенный фильтр, сформируйте список моделей фирм-производителей компьютеров, доходы от продаж которых и в 2003, и в 2004 годах составляли бы больше 70 млн. у. е.
Используя функцию категории «Работа с базой данных» БСЧЕТ, подсчитайте количество моделей фирм-производителей компьютеров, торговая доля от продажи которых меньше 30 %.

Постройтеобъемнуюгистограмму доходов фирмы 2003-2004гг.

Рассчитайте начисление комиссионных сотрудникам малого предприятия:

Номер п/п	Ф. И. О.	Выручка, руб.	Комиссионные, руб.
1	Моторов А.А.	100000
2	Турканова О. И.	550000
3	Басков Г. Т.	340000
4	Попова С. А.	60600
5	Антонов П. П.	23800
6	Суслова Е. И.	5000
	Итого

Формулы для расчетов:

Комиссионные рассчитываются исходя из следующего:

2%, если Выручка менее 50000 руб.;
3%, если Выручка от 50000 до 100000 руб.;
4%, если Выручка более 100000 руб.

Для заполнения столбца Комиссионные используйте функцию ЕСЛИ из категории «Логические».

Используярасширенный фильтр, выдайте список сотрудников, объем выручки у которых составляет от 50000 руб. до 100000 руб.
Используя функцию категории «Работа с базой данных» БСЧЕТ, определите количество сотрудников, у которых выручка менее 50000 руб.
Постройтеобъемнуюгистограмму объема продаж по сотрудникам и круговую диаграмму начисления размера комиссионных.

Вариант 11

Рассчитайте стоимость перевозки

Код товара	Вес, брутто	Тариф за кг, у.е.	Сумма оплаты за перевозки	Издержки	Всего за транспорт
948XT	920	0,3
620LT	420	12,7
520KT	564	5,77
900PS	210	5,95
290RT	549	3,98
564ER	389	34,7
764NT	430	12,9
897VC	653	34,6

Формулы для расчетов:

Сумма оплаты за перевозки для каждого товара = Вес * Тариф;

Издержки рассчитываются исходя из следующего:

для веса более 400 кг – 3% от Суммы оплаты;
для веса более 600 кг – 5% от Суммы оплаты;
для веса более 900 кг – 7% от Суммы оплаты.

Для заполнения столбца Издержки используйте функцию ЕСЛИ из категории «Логические».

Всего за транспорт = Сумма оплаты за перевозки — Издержки.

Используярасширенный фильтр, сформируйте список кодов товаров, сумма оплаты за перевозки для которых составляет от 1000 до 4000 у.е.
Используяфункцию категории «Работа с базой данных» БСЧЕТ, определите сколько видов (кодов) товаров имеют тариф за кг от 5 до 30 у.е.
Постройтеобъемную круговую диаграмму, отражающую сумму оплаты перевозок для каждого кода товаров.

Вариант 12

Заполните ведомость по налогам сотрудников предприятия.

№ п/п	ФИО	Всего начислено, руб.	Пенсионный фонд, руб.	Налогооблагаемая база, руб.	Налог, руб.
1	Иванов А.Л.	3800
2	Иванов С.П.	4550
3	Дутова О.П.	1000
4	Карпов А.А.	6050
5	Клыков О.Н.	4880
6	Львов Г.В.	6600
7	Миронов А.М.	7950
Итого

Формулы для расчетов:

Налог определяется исходя из следующего:

12% от Налогооблагаемой базы, если Налогооблагаемая база меньше 1000 руб.;
20% от Налогооблагаемой базы, если Налогооблагаемая база больше 1000 руб.

Для заполнения столбца Налог используйте функцию ЕСЛИ из категории «Логические».

Пенсионный фонд = 1% от «Всего начислено».

Налогооблагаемая база = Всего начислено — Пенсионный фонд

Итого = сумма по столбцам Всего начислено, Пенсионный фонд и Налог

Используярасширенный фильтр, сформируйте список сотрудников, у которых «Всего начислено» составляет от 350 руб. до 5000 руб.
Используя функцию категории «Работа с базой данных» БСЧЕТ, определите количество сотрудников, у которых налог меньше 800 руб.
Постройте объемную круговую диаграмму начислений по сотрудникам.

Вариант 13

Формирование цен:

Артикул товара	Оптовая цена (руб.)	Розничная цена (руб.)	Цена со скидкой (руб.)	Ценовая категория
23456А	1500
56789А	2300
985412В	4580
56789С	5620
456856В	2280
45698А	2450
7895621В	6540

Коэффициент опта	0,1
Коэффициент скидки	0,15

1. Формулы для расчетов:

Розничная цена = Оптовая цена + Оптовая цена * Коэффициент опта

Цена со скидкой = Розничная цена – Розничная цена * Коэффициент скидки

Ценовая категорияопределяется исходя из следующего:

«нижняя», если розничная цена ниже 2000 рублей;
«средняя», если цена находится в пределах от 2000 до 5000 рублей;
«высшая», если цена выше 5000 рублей.

Для заполнения столбца Ценовая категория используйте функцию ЕСЛИ из категории «Логические».

Используярасширенный фильтр сформируйте список товаров оптовая цена которых находится в диапазоне от 3000 до 6000 рублей.
Используя функцию категории «Работа с базой данных» БСЧЕТ определите количество товаров, которые попадают в среднюю ценовую категорию.
Постройте объемную гистограмму, на которой отобразите оптовые и розничные цена по каждому виду товаров.

Вариант 14

Продажа принтеров:

№ п/п	Модели	Цена, $	Заказано (шт)	Продано (шт)	Объем продаж, $	Комиссионные, $
1	Принтер лазерный Ч/Б	430	60	52
2	Принтер лазерный Ц/В	2000	10	2
3	Принтер струйный Ч	218	56	50
4	Принтер струйный Ч/Б	320	40	32
Итого

Формулы для расчетов:

Комиссионные определяются в зависимости от объема продаж:

2%, если объем продаж меньше 5000$;
3%, если объем продаж от 5000$ до 10000$;
5%, если объем продаж более 10000$.

Для заполнения столбца Комиссионные используйте функцию ЕСЛИ из категории «Логические».

Объем продаж = Цена * Количество (Продано)

Итого = сумма по столбцам Продано,Объем продаж и Комиссионные.

Используярасширенный фильтр, сформируйте список моделей принтеров, объем продаж которых составил более 10000$.
Используя функцию категории «Работа с базой данных» БДСУММ, определите объем продаж у принтеров лазерных (ЧБ и ЦВ).
Постройте объемную круговую диаграмму объема продаж принтеров.

Вариант 15

Смета на приобретение канцелярских товаров:

№ п/п	Наименование	Кол-во, Шт.	Цена, руб.	Стоимость, руб.	Cкидка, руб	Стоимость с учетом скидки, руб.
1	Тетради простые в клетку	150	3.00
2	Ручки шариковые с синим стержнем	70	11.50
3	Карандаши простые, НВ	100	6.00
4	Ластики	20	2.00
5	Линейки пластмассовые, 35 см.	10	8.10
Итого

Формулы для расчетов:

Скидка определяется исходя из следующего:

0% от Стоимости, если Количество меньше 50;
2% от Стоимости, если Количество от 50 до 100;
5%, от Стоимости, если Количество более 100.

Для заполнения столбца Скидка используйте функцию ЕСЛИ из категории «Логические».

Стоимость с учетом скидки = Стоимость – Скидка

Итого = сумма по столбцу Стоимость с учетом скидки.

Используярасширенный фильтр, выдайте список канцелярских товаров, цена которых составляет больше 5 руб.
Используя функцию категории «Работа с базой данных» БСЧЕТ, подсчитайте количество канцелярских товаров, у которых цена более 7 руб.
Постройте объемную круговую диаграмму, характеризующую сумму скидки.

Вариант 16

Текущее состояние дел в книжной торговле:

Номер п/п	Название	Автор	Цена опт	Цена розничн.	Кол-во	Оплачено	Продано	Приход	Расход	Баланс
1	Практическая работа с MSExcel	Долженков	80	90	30	10	8
2	Excel одним взглядом	Вострокнутов	30	35	50	30	28
3	Шпаргалка по Excel	Столяров	20	25	40	20	35
4	Разработка приложений в Access 98	Нортон	150	165	6	6	2
5	Access 98. Библиотека ресурсов	О`Брайен	140	155	5	0	2
6	Excel 98. Библиотека ресурсов	Уэллс	140	155	5	0	1
7	Access 7.0 в примерах	Гончаров	70	80	15	10	15

Формулы для расчетов:

Приход = Продано * Цена розничная

Расход = Оплачено * Цена оптовая * 0,8 + Анализ продаж, где

Анализ продаж определяется исходя из следующего:

если Продано > Оплачено, то Анализ продаж = (Продано – Оплачено) * Цена оптовая;
0, в остальных случаях.

Для заполнения столбца Расход используйте функцию ЕСЛИ из категории «Логические».

Баланс = Приход — Расход

Используярасширенный фильтр, сформируйте список названий книг, оптовая цена которых находится в пределах от 20 руб. до 70 руб.
Используя функцию категории «Работа с базой данных» БСЧЕТ, определите, сколько книг имеют розничную цену более 80 руб.
Постройте объемную круговую диаграмму, характеризующую показатель Оплачено.

Вариант 17

Движение пассажирских самолетов из аэропорта:

Номер рейса	Самолет	Кол-во пассажиров	Аэропорт назначения	Расстояние	Цена билета, руб.	Скидка	Цена билета со скидкой	Стоимость за рейс
ПК 662	ЯК-40	32	Кызыл	840	3200
СЛ 2029	АН-24	48	Надым	1320	4300
СЛ 2021	АН-24	48	Нижневартовск	750	2300
СЛ 5006	АН-24	48	Нижневартовск	750	2300

СЛ 2031	АН-24	48	Салехард	1560	5400
СЛ 2025	АН-24	48	Стрежевой	720	2300
СЛ 2039	АН-24	48	Сургут	900	2800
СП 5002	АН-24	48	Томск	280	600
СП 2015	АН-24	48	Ханты-Мансийск	1100	4000

Формулы для расчетов:

Скидка определяется исходя из следующего:

0% от Цены билета, если Расстояние меньше 800 км;
2% от Цены билета, если Расстояние от 800 км до 1100 км;
3% от Цены билета, если Расстояние более 1100 км.

Для заполнения столбца Скидка используйте функцию ЕСЛИ из категории «Логические».

Цена билета со скидкой = Скидка * Цена билета

Стоимость за рейс со скидкой = Цена билета со скидкой * Количество пассажиров

Используярасширенный фильтр, сформируйте список городов для которых расстояние до Новосибирска более 900 км.
Используя функцию категории «Работа с базой данных» БДСУММ, определите общую стоимость со скидкой рейсов СЛ 2031 и СП 5002.
Постройте объемную круговую диаграмму, характеризующую цену билета со скидкой.

Вариант 18

Ведомость доходов железных дорог (руб.):

Номер ж.д.	Объем перевозок, руб.	Удельный вес	Доходная ставка за 10т/км	Средняя дальность перевозок	Сумма доходов
1010	5800		20,3	400
1011	1200		30,3	500
1012	3500		20,5	640
1013	4700		18,5	700
1014	3600		21,4	620
2000	3400		20,7	720
2010	4500		32,4	850
2110	4100		28,7	700
Итого

1. 1. 1. Формулы для расчетов:

Сумма доходов = Объем перевозок * Доходная ставка / 10 * Удельный вес * k, где

k равно:

0.3, если средняя дальность перевозок больше 650 км;
0.2,если средняя дальность перевозок меньше 650 км.

Удельный вес = Объем перевозок / Итог объема перевозок * 100

Итого = сумма по столбцу Объем перевозок

1. 1. 1. Используярасширенный фильтр, определите у какой железной дороги объем перевозок больше 4000 руб.
    2. Используя функцию категории «Работа с базой данных» БДСУММ, определите общую сумму доходов железной дороги 1012 и 2110.
    3. Постройтеобъемную круговую диаграмму, характеризующую сумму доходов каждой железной дороги.

Вариант 19

Кондиционеры из Японии

№ п/п	Модель	Длина (см)	Ширина (см)	Высота (см)	Цена розн. ($)	Цена розн. (т.руб.)	Скидка (т.руб.)	Цена розн. со скидкой	Объем (куб.см.)
1	FTY256VI	75	25	18	1400
2	FTY356VI	75	25	18	1750
3	FTY456VI	105	30	19	2390
4	FTY606VI	105	30	19	2830
5	LS-PO960HL	79	23	14	960
6	LS-S1260HL	88	30	18	1100
7	LS-D2462HL	108	29	18	1800

Формулы для расчетов:

Скидка определяется исходя из следующего:

0%, если Цена розничная ($) меньше 2000$;
3%, если Цена розничная ($) больше 2000$.

Для заполнения столбца Скидка используйте функцию ЕСЛИ из категории «Логические».

Цена розничная (руб.) = Цена розничная ($) * Курс доллара.

Цена розничная со скидкой (руб.) = Цена розничная (руб.) * Скидка

Используярасширенный фильтр, сформируйте список моделей кондиционеров, имеющих розничную цену более 2000$.
Используя функцию категории «Работа с базой данных» БСЧЕТ, определите, у скольких моделей кондиционеров длина составляет от 80 см до 105 см.
Постройте объемную круговую диаграмму по объемам кондиционеров.

Вариант 20

Объем реализации товара

№ магазина	Товар 1	Товар 2	Товар 3	Объем реализации, тыс.руб.	Комиссионные, тыс.руб.	Удельный вес, %
Магазин № 15	41	43	39
Магазин №28	138	140	141
Магазин №30	234	137	138
Магазин №45	139	335	237
Магазин №58	52	150	53
Итого

Формулы для расчетов:

Комиссионные определяются исходя из следующего:

— 2%, если объем реализации менее 300 тыс.руб.
— 5%, если объем реализации более 300 тыс.руб.

Для заполнения столбца Комиссионные используйте функцию ЕСЛИ из категории «Логические».

Объем реализации = Товар 1 + Товар 2 + Товар 3

Удельный вес = Объем реализации каждого магазина / Итог объема реализации * 100

Используярасширенный фильтр, сформируйте список магазинов, имеющих объем реализации более 400 тыс.руб.
Используя функцию категории «Работа с базой данных» БСЧЕТ, определите суммарный объем реализации в магазинах № 28 и № 30
Постройте объемнуюкруговую диаграмму удельного веса по каждому маггазину.

Вариант 21

Внутренние затраты на исследования и разработки по секторам деятельности:

Секторы деятельности	млн.руб., 1998г.	в % к итогу, 1998г.	млн.руб. 1999г.	в % к итогу, 1999г.	млн.руб. 2000г.	в % к итогу, 2000г.	Характеристика затрат 2000г.
Государствен.	6465,9		13828,8		18363,3
Предпринимат.	17296,6		27336,0		52434,5
Высш. образование	1297,1		2090,4		2876,2
Частный бесприбыльный	22,4		51,3		73,7
Максим. затраты
Средние затраты
Всего:	25082,0	100,0	43306,5	100	73747,7	100

Формулы для расчетов:

«в % к итогу, 1998» = «млн. руб., 1998» / Всего по графе «млн.руб., 1998» _*100

«в % к итогу, 1999» = «млн. руб. 1999» / Всего по графе «млн. руб. 1999» * 100

«в % к итогу, 2000» = «млн. руб. 2000» / Всего по графе «млн. руб. 2000» * 100

Максимальные затраты1998= МАХ («млн.руб., 1998»)

Максимальные затраты1999= МАХ («млн.руб. 1999»)

Средние затраты2000 = СРЗНАЧ («млн.руб. 2000»)

Характеристика затрат 2000 года рассчитывается исходя из следующего:

«повысились», если затраты в 2000 году (млн. руб.) больше, чем соответствующие затраты в 1999 году;
«снизились», если затраты 2000 году (млн. руб.) меньше, чем соответствующие затраты в 1999 году.

Для заполнения столбца Характеристика затрат используйте функцию ЕСЛИ из категории «Логические».

Используярасширенный фильтр, составьте список секторов деятельности с затратами на исследования в 2000 году в размерах от 1500 до 20000 млн. руб.

Используя функцию категории «Работа с базой данных» БДСУММ, определите общую сумму затрат на исследования в предпринимательском и частном секторах деятельности.
Построить объемную гистограмму, отражающую затраты на исследования в 1998-2000 году по секторам экономики.

Вариант 22

Модель	Название	Стоимость (руб.)	Цена (руб.)	Кол-во (шт.)	Сумма (руб.)	Ценовая категория
C100GLS	Персональный	827		564
C110GLS	Персональный	993		632
C200GLS	Персональный+	1429,5		438
C210GLS	Персональный+	1715,86		645
C300GLS	Деловой	2410		437
C310GLS	Деловой	2965,3		534
C400GLS	Профессиональный	4269,65		409
C410GLS	Профессиональный	5123,5		395
C420GLS	Профессиональный+	6415		298
C500GLS	Профессиональный+	7377,9		328
	Итого:
	Средняя стоимость
	Коэффициент	1,3

Формулы для расчетов:

Цена = Стоимость * Коэффициент

Сумма = Цена * Кол-во

Итого = сумма по графе «Сумма»

Средняя стоимость = СРЗНАЧ (Стоимость)

Ценовая категориярассчитывается исходя из следующего:

«средняя», если цена находится в пределах от 1 до 5 тысяч рублей;
«высшая», если цена выше 5 тысяч рублей.

Для заполнения графы Ценовая категория используйте функцию ЕСЛИ из категории «Логические».

Используярасширенный фильтр, выведите модели и наименования ксероксов, чья цена находиться в пределах от 2 до 6 тысяч рублей.
Используя функцию категории «Работа с базой данных» БДСУММ, вычислите общую сумму от продажи ксероксов с названиями “Профессиональный” и “Профессиональный+ ”.
Постройте объемную круговую диаграмму, отражающую количество проданных ксероксов всех моделей.

Вариант 23

5 крупнейших компаний России по объему реализации продукции в 1999 году

Компания	Объем реализации, млн. руб.	Прибыль после налогообложения, млн. руб.	Уровень рентабельности, %	Характеристика рентабельности
НК «Лукойл»	268207,0	30795,0
ОАО «Сургутнефтегаз»	80827,0	30931,9
РАО «Норильский никель»	66819,2	36716,4
НК «Юкос»	52013,7	6265,3
АвтоВАЗ	47999,1	1686,6
Средний уровень рентабельности
Максимальная прибыль

1. Формулы для расчетов:

Уровень рентабельности = Прибыль после налогообложения / Объем реализации*100

Средний уровень рентабельности = среднее значение по графе «Уровень рентабельности»

Максимальная прибыль = максимальное значение по графе «Прибыль после налогообложения»

Характеристика рентабельности рассчитывается исходя из следующего:

средняя, если уровень рентабельности до 30%;
высокая, если уровень рентабельности выше 30%.

Для заполнения графыХарактеристика рентабельности используйте функцию ЕСЛИ из категории «Логические».

1. Используярасширенный фильтр, составьте список компаний с уровнем рентабельности от 15 до 40%.

2. Используя функцию категории «Работа с базой данных» БСЧЕТ, подсчитайте общее количество компаний с прибылью более 30000 млн.руб.
3. Постройте объемную круговую диаграмму, отражающую объем реализации продукции каждой компании из приведенного списка.

Вариант 24

Книга продаж: Факсы

Модель	Название	Стоимость (руб.)	Цена (руб.)	Кол-во (шт.)	Сумма (руб.)	Сфера применения
F100G	Персональный	1607,96		564
F150G	Персональный	1840		420
F200G	Персональный+	1729,55		634
F250G	Персональный+	2075,66		432
F300G	Деловой	2550,55		297
F350G	Деловой	2760,66		437
F400G	Профессиональный	3512,8		324
F450G	Профессиональный	3815,35		289
F500G	Профессиональный+	4878,34		211
F550G	Профессиональный+	5614,11		108
	Итого:
	Максимальная цена
	Коэффициент	1,3

Формулы для расчетов:

Цена = Стоимость * Коэффициент

Сумма = Цена * Кол-во

Итого = сумма по графе «Сумма»

Максимальная цена = максимальное значение по графе «Цена»

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

«коммерческие фирмы», для моделей Профессиональный;
«широкоеприменение» – все остальные модели факсов.

Для заполнения графыСфера применения используйте функцию ЕСЛИ из категории «Логические».

Используярасширенный фильтр, выведите модели и наименования факсов, которых было продано от 300 до 500 штук.
Используя функцию категории «Работа с базой данных» БДСУММ, вычислите общую сумму от продажи факсов с наименованиями «Персональный» и «Персональный +».
Постройте объемную круговую диаграмму, отражающую стоимость проданных факсов всех моделей.

Вариант 25

Некоторые крупнейшие компании России по рыночной стоимости (капитализации) на 1 сентября 2000 года

Компания	Капитализация компании, руб.	Цена (котировка) обыкновенной акции, долл.	Число обыкновенных акций, шт.	Оценка котировки акций
ОАО «Сургутнефтегаз»		0,3863	35725994705
НК «Лукойл»		16,0694	738351391
ОАО «Газпром»		0,3167	23673512900
НК «Юкос»		1,6711	2236991750
Мобильные телесистемы		1,4250	1993326150
Ростелеком		2,3550	700312800
Аэрофлот		0,2057	1110616299
Максимальная цена, долл.
Курс ЦБ на 01.09.2000 (руб/долл)		27,75

Формулы для расчетов:

Капитализация компании = Число обыкновенных акций / Цена *Курс ЦБ/ 1000000

Максимальная цена акции = максимальное значение по графе Цена обыкновенной акции (выберите соответствующую функцию в категории «Математические).

Оценка котировки акцийопределяется исходя из следующего:

- «спад», если цена котировки устанавливается ниже отметки 1;
- «подъем», если цена котировки устанавливается выше отметки больше 10;
- «стабильно», если цена котировки устанавливается на отметке от 1 до 10.

Для заполнения графыОценка котировки акцийиспользуйте функцию ЕСЛИ из категории «Логические».

Используярасширенный фильтр, составьте список компаний, у которых число обыкновенных акций находиться в пределах от 1000000000 до 20000000000 шт.
Используя функцию категории «Работа с базой данных» БСЧЕТ, подсчитайте количество компаний, у которых цена за 1 акцию превышает 1 доллар.
Постройте объемную круговую диаграмму, отражающую уровень капитализации компаний.

Вариант 26

Производительность труда в пяти крупнейших компаниях России в 1999 году

Компания	Отрасль	Объем реализации, млн.руб.	Численность занятых, тыс.чел.	Производи-тельность труда, тыс.руб/чел	Характери-стика производи-тельности
ОАО «Газпром»	Нефтяная и нефтегазовая промышлен.	30599,0	298,0
НК «ЛУКойл»	Нефтяная и нефтегазовая промышлен.	268207,0	120,0
РАО «ЕЭС России»	Эл/энергетика	247477,0	669,5
ОАО «Сургутнефтегаз»	Нефтяная и нефтегазовая промышлен.	80827,0	70,1
РАО «Норильский никель»	Нефтяная и нефтегазовая промышлен.	66819,2	102,7
Средняя производительность труда
Максимальный объем реализации

1. Формулы для расчетов:

Производительность труда = Объем реализации / Численность занятых

Средняя производительность труда = среднее значение по графе «Средняя производительность труда»

Максимальный объем реализации = максимальное значение по графе «Объем реализации»

Характеристика производительности определяется исходя из следующего:

«выше средней», если производительность труда больше, чем средняяпроизводительность труда;
«ниже средней», если производительность труда меньше, чем средняя производительность труда.

Для заполнения графы Характеристика производительности используйте функцию ЕСЛИ из категории «Логические».

1. Используярасширенный фильтр, составьте список компаний, с численностью занятых более 150 тыс. чел.
2. Используя функцию категории «Работа с базой данных» БСЧЕТ, подсчитайте общее количество компаний с производительностью более 1000 тыс.руб./чел.
3. Постройте объемную круговую диаграмму, отражающую распределение численности занятых по компаниям.

Вариант 27

ВВП и ВНП 15 ведущих государств мира

Страны	ВВП_1998, млрд.долл.	Численность населен_1998 млн.чел.	ВВП на душу нас-я_1998, тыс.долл.	ВВП_1999, млрд.долл.	Участие страны в про- изводстве мирового ВВП, %	Прирост ВВП, млрд.долл.	Оценка изменения ВВП
Австралия	364,2	18,8		395
Аргентина	344,4	36,1		282
Великобрит.	1357,4	59,1		1437
Бельгия	247,1	10,2		248
Германия	2142,0	82,1		2115
Испания	551,9	39,3		597
Италия	1171,0	57,6		1173
Канада	598,8	30,6		639
Нидерланды	382,5	15,7		394
США	8210,6	270		9256
Франция	1432,9	58,8		1435
Швейцария	264,4	7,1		260
Швеция	225	8,9		239
Ю.Корея	297,9	46,4		407

Япония	3783,1	126,3		4349
Всего

Формулы для расчетов:

ВВП на душу населения_1998= ВВП_1998/ Численность населен_1998

Прирост ВВП = ВВП_1999 — ВВП_1998

Участие страны в производстве мирового ВВП= ВВП_1999 /Всего(ВВП_1999) *100

Всего(ВВП_1999) = сумма по графе «ВВП_1999»

Оценка изменения ВВП определяется исходя из следующего:

«ухудшение», если наблюдается отрицательный прирост ВВП;
«развитие», если наблюдается положительный прирост ВВП;
«стабильность» — для нулевого значения ВВП.

Используярасширенный фильтр, составьте список стран с численностью от 50 до 150 млн.чел.
Используя функцию категории «Работа с базой данных» БСЧЕТ, подсчитайте общее количество стран с отрицательным показателем прироста ВВП.
Построить объемную гистограмму, на которой отразите показатель ВВП в 1998 и 1999 годах для первых пяти стран списка.

Вариант 28

Распределение занятого в экономике регионов населения по формам собственности в 1998 году

Регионы (районы)	Всего занято в экономике, тыс.чел.	Гос. и муницип., тыс.чел	Обществ. организац., тыс.чел.	Частная, тыс.чел.	Другие, тыс.чел.	Преобла-дание собствен-ности в регионе
Калининградск. обл.	399,6	161,2	2,4	174,6	61,3
Северный	2368,3	1097,5	11,5	743,9	515,4
Северо-Западн.	3605,4	1402,2	29,6	1649,3	524,2
Центральный	13277,2	5097,7	108,6	5640,1	2430,9
Волго-Вятский	3586,5	1373,3	36,9	1528,9	647,4
Центрально-Черноземный	3151,4	1169,0	23,3	1561,9	397,1
Поволожский	7028,9	2754,3	52,2	2693,6	1528,8
Сев.-Кавказск.	6110,7	2237,9	55,2	2962,1	855,5
Уральский	8459,6	3476,5	62,1	3167,7	1753,3
Зап-Сибирск.	6429,5	2487,9	31,0	2501,9	1471,7
Вост-Сибирск.	3604,6	1531,1	16,0	1311,9	745,5
Дальневосточн.	3157,4	1467,3	16,5	1127,6	546,0
Итого

Формулы для расчетов:

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

Уд.вес_гос_собств. = Гос. и муницип / Итого «Гос. и муницип.» * 100

Уд.вес_ обществ.организац. = Обществ.организац. / Итого «Обществ. организац.» * 100

и т.д. по всем формам собственности

Преобладание собственности в регионе определяется исходя из следующего:

«преобладание частной»для регионов, где частная собственность превышает государственную;
«преобладание государственной»,для регионов, где государственная собственность превышает частную.

Для заполнения графы Преобладание собственности в регионе используйте функцию ЕСЛИ из категории «Логические».

Итого «Всего занято в экономике» = сумма по графе «Всего занято в экономике»

Итого «Гос. и муницип.» = сумма по графе «Гос. и муницип.»

Итого «Обществ. организац.» = сумма по графе «Обществ. организац.»

и т.д. по всем формам собственности.

Используярасширенный фильтр, составьте список регионов с долей населения, занятого на предприятиях с частной формой собственности, от 10% до 25%.
Используя функцию категории «Работа с базой данных» БДСУММ, подсчитайте общее количество человек, работающих в государственном секторе, с долей занятого населения в них более 10%.
Постройте объемную круговую диаграмму, отражающую доля населения в частном секторе регионов России от Урала до Дальнего Востока.

Вариант 29

Таблица народонаселения некоторых стран:

Страна	Площадь, тыс. км2	Население, тыс. чел.	Плотность населения, чел./км2	В % от населения всего мира	Место в мире по количеству населения
Россия	17 075	149 000
США	9 363	252 000
Канада	9 976	27 000
Франция	552	56 500
Китай	9 561	1 160 000
Япония	372	125 000
Индия	3 288	850 000
Израиль	14	4 700
Бразилия	2 767	154 000
Египет	1 002	56 000
Нигерия	924	115 000
Весь мир		5 292 000

Формулы для расчетов:

Плотность населения = Население / Площадь

В % от населения всего мира = Население каждой страны / Весь мир * 100

Место в мире по количеству населения рассчитайте исходя из следующего:

1 место, если Население больше 1000000 тыс.;
2 место, если Население больше 800000 тыс.;
3место — остальные.

Для заполнения столбца Плотность населенияиспользуйте функцию ЕСЛИ из категории «Логические».

Используярасширенный фильтр, сформируйте список стран с площадью более 5000 тыс.км².

Используя функцию категории «Работа с базой данных» БСЧЕТ, подсчитайте количество стран с плотностью населения от 100 до 300 чел/км².
Постройте объемную круговую диаграмму, отражающую площадь для всех стран.

Вариант 30

Средние розничные цены на основные продукты питания по городам Западной Сибири в январе 2001 г. (рублей за килограмм).

Продукты	Новосибирск	Барнаул	Томск	Омск	Кемерово	Средняя цена	Оценка средней цены
Говядина	56,67	54,57	59,1	42,79	45,67
Птица	53,54	45,34	48,2	48,1	48,31
Колбаса	85	76,87	66,73	71,63	81
Масло слив.	73,16	62,34	60,75	60,45	60,97
Масло раст.	28,45	19,76	23,1	23	22,25
Творог	47,57	41,75	37,94	39,49	38,24
Молоко	9,73	7,42	9,75	8	9,88
Яйцо (10шт.)	16	15,6	16	15,58	15,61
Сахар	17	14,47	14,73	14,23	15,54
Мука	7,29	5,76	6,53	6,15	6,76
Картофель	5	3,31	3,74	4,54	3,32
Итого:

1. Формулы для расчетов:

Среднюю цену рассчитайте с помощью функции СРЗНАЧ из категории «Математические».

Оценку средней цены продуктов определите исходя из следующего:

дорогие продукты,если цена>40 рублей за килограмм;
недорогиепродукты, в ином случае.

3.Используярасширенный фильтр, сформируйте список продуктов, у которых средние цены имеют значение от 20 до 40 рублей.

4. Используя функцию категории «Работа с базой данных» БСЧЕТ подсчитайте количество продуктов, для которых средняя цена больше 50 рублей.

5.Постройте объемную гистограмму по данным о ценах на муку по всем городам.

Вариант 31

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

Наименование нефтепродукта	Производство, тыс. тонн	Облагаемая реализация, тыс. тонн	Ставка налога с оборота на 1 тонну	Налог с оборота	Место по производству нефтепродуктов
Автобензин	1610	730	150
Мазут	4300	4200	3
Топливо диз.	50	50	14
Керосин	35	35	14
Итого

1. Формулы для расчетов:

Сумма налога с оборота = Ставка налога * Облагаемая реализация.

Итого = сумма по графе Налог с оборота.

Место по производству нефтепродуктов определяется исходя из следующего:

1 место, если Производство > 3000 тыс.тонн;
2 место, если Производство>1000 тыс.тонн;
3 место, если Производство>40 тыс.тонн .

Для заполнения столбца Место по производству нефтепродуктов используйте функцию ЕСЛИ из категории «Логические».

1. Используярасширенный фильтр, сформируйте список нефтепродуктов, производство которых составляет от 1000 до 5000 тыс. т.
2. Используя функцию категории «Работа с базой данных» БСЧЕТ, подсчитайте количество нефтепродуктов, у которых ставка налога с оборота меньше 10.
3. Постройте объемную круговую диаграмму ставок налога с оборота по каждому виду нефтепродукта.

Вариант 32

Выполните анализ основных показателей финансово-экономической деятельности промышленных предприятий по данным, приведенным в таблице.

Классы предприятий по основным фондам, млрд. руб.	Количество	Объем товарной продукции, млрд. руб.	Численность, тыс. чел.	Место по объему товарной продукции
0 – 1	25	53,525	4,343
1 – 5	57	488,95	21,380
5 – 10	28	390,693	20,830
10 – 50	44	1964,749	68,631
50 – 100	10	901,538	55,899
100 – 200	5	717,813	40,625
> 200	4	103,033	71,880
Итого:

Формулы для расчетов:

Место каждого предприятия по объему товарной продукции определяется исходя из следующего:

1 место, если Объем больше 1000 млрд.руб.
2 место, если Объем больше 800 млрд.руб.
3 место, если Объем больше 600 млрд.руб.

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

Используярасширенный фильтр, сформируйте список классов предприятий, объем товарной продукции у которых находится в интервале от 200 до 900 млрд. руб.

Используя функцию категории «Работа с базой данных» БДСУММ подсчитайте общий объем товарной продукции тех предприятий, у которых численность меньше 50 тыс. чел.
Постройте объемную круговую диаграмму распределения численности предприятий по классам.

Вариант 33

В таблице представлена группировка работающего населения по уровню образования по данным переписей 1970, 1979 и 1989 гг. (в тыс. человек).

Уровень образования	1970	1979	1989
Высшее законченное	7544	13486	20200
Высшее незаконченное	1457	1541	1900
Среднее специальное	12123	21007	33100
Среднее общее	18347	37293	52600
Неполное среднее	35976	35307	22800
Итого
Номер места

1. Формулы для расчетов:

Итого = сумма по столбцам 1970, 1979, 1989.

Номер места работающего населения по итогам каждого года, определяется исходя из следующего:

1 место, если Итого за год > 120000 тыс. человек;
2 место, если Итого за год > 100000 тыс. человек;
3 место – в ином случае.

Для заполнения строки Номер места, используйте функцию ЕСЛИ из категории «Логические».

Используярасширенный фильтр, сформируйте список уровней образования за 1989 г., по которым численность работающего населения составляла от 20000 до 40000 тыс. чел.
Используя функцию категории «Работа с базой данных» БСЧЕТ, подсчитайте количество уровней образования, по которым в 1979 г. численность работающего населения составляла больше 20000 тыс. чел.

Постройтеобъемную гистограмму соотношения уровней образования по каждому году.

Адрес публикации: https://www.prodlenka.org/metodicheskie-razrabotki/466829-varianty-kontrolnoj-raboty-v-programme-excel

Источник

Задание

Выполните
расчет ведомости заработной платы
сотрудников по данным, приведенным в
таблице. Таблицу данных и диаграмму
отобразите на одном листе в текстовом
процессоре MS
Word.
Выполните задание по варианту.

Решение

1.
Сформировать пустую таблицу (рис. 12).
Ввести данные в столбцы A,
B,
C.
Сформировать вспомогательную таблицу,
в которой указаны: разряд,

тариф, план, премия.

2.
Рассчитать зарплату рабочих, используя
логическую функцию ЕСЛИ.
Расчет заработной платы выполняется с
учетом разряда рабочего. Для расчета
зарплаты в ячейку D2
введите формулу в виде =С2*ЕСЛИ(В2=10;$С$13;
$С$14).
В данном случае логическим выражением
для функции ЕСЛИ
будет сравнение разряда сотрудника,
указанного в столбце В,
с числом 10.

Рис.12.
Таблицы исходных данных

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

Рис. 13. Окно
аргументов функции ЕСЛИ

Таким
образом, происходит определение разряда
каждого сотрудника, и отработанные часы
умножаются либо на 100, либо на 150 в
зависимости от указанного разряда.
Следует отметить, что в данном примере
используется простейший способ построения
логического выражения. Например, если
в столбце В
(Разряд) для одного из сотрудников
указать неверное значение, отличное от
числа 10, то расчет его зарплаты произойдет
по 11 разряду. Будет использоваться
(Значение_если_ложь) = 150. Чтобы этого не
произошло, можно настроить условия
ввода данных в ячейки с помощью команд
Данные/Проверка
и вкладки Сообщение
об ошибке (рис.
14).

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

Р
ис.
14. Настройка
проверки вводимых значений и окна
сообщения об ошибке

3. Премия
начисляется в том случае, если сотрудник
выполнил или перевыполнил план. Для
этого в ячейку Е2
введите формулу в виде =Если(С2>=$D$13;$E$13;”без
премии”). В этом случае в логическом
выражении сравниваются отработанные
сотрудником часы со значением плана,
которое указано в ячейке D13,
и при выполнении условия функция ЕСЛИ
принимает значение соответствующее
содержимому ячейки Е13.
Если условие не выполняется (сотрудник
отработал меньшее количество часов,
чем значение плана) значением функции
будет фрагмент текста, который будет
помещен в соответствующую ячейку.

4. Добавьте
на лист значения итоговых сумм. Укажите
для соответствующих ячеек денежный
формат.

5.
Постройте круговую диаграмму по значениям
отработанных часов. Окончательный вид
ведомости и диаграммы показан на рис.15.

6.
Скопируйте таблицы и диаграмму из Excel
в Word.

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

Дополнительные
возможности — выделение
одного сектора из круговой диаграммы.

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

Для
начала создайте базовую круговую
диаграмму. Затем выберите круговую
диаграмму и медленно дважды щелкните
на секторе, который хотите отделить.
Сдвиньте этот сектор от центра диаграммы,
вот вы и вырезали кусочек (рис. 16).

Рис.
16. Измененные
диаграммы

Остальные
сектора остаются на месте. Можно повторить
эти действия и с ними. Если необходимо
отделить все секторы одновременно,
выделите всю диаграмму и передвигайте
один из секторов от центра (рис. 16). Чем
дальше вы перетащите секторы, тем меньше
они станут.

Собрать
круговую диаграмму можно выполнить
обратное действие, щелкните на сектор
диаграммы и перетащите его к центру,
чтобы совместить все секторы.

Самостоятельная
работа

Выполните
расчет заработной платы сотрудников
по данным, приведенным в таблице (рис.
12). Данные для тарифов, плана и премии
взять из вариантов. Таблицы данных и
диаграмму отобразите на одном листе в
текстовом процессоре MS
Word.

Таблица 5

Вариант	Задание
Тариф 10 разряд	Тариф 11 разряд	План	Премия
1	50	70	95	1200
2	50	80	100	1500
3	150	200	110	3000
4	200	250	105	2000
5	90	100	90	800
6	100	120	95	600
7	100	130	101	900
8	120	150	105	1250
9	350	500	100	550
10	200	300	90	750

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

Источник

На чтение 3 мин Просмотров 4.1к. Опубликовано 07.12.2021

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

Содержание

Результат функции
Формула
Аргументы функции
Важная информация
Примеры
Проверяем соответствует ли число заданным критериям с помощью функции ЕСЛИ в Excel
Проверяем сразу несколько критериев
Вычисляем комиссию
Пример 4: Использование логических операторов (AND/OR) в функции IF в Excel
Как убрать ошибки при использовании функции ЕСЛИ в Excel

Результатом функции будет указанное вами значение, указать это самое значение можно для двух исходов(истина или ложь)


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

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

значение_если_истина — значение, которое вернет функция ЕСЛИ в случае, если проверяемый аргумент оказался истиной.

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

Максимум проверяемых аргументов может быть 64;

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

Если вместо аргумента вы оставите пустое место, результатом выполнения функции будет 0, то есть.

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

Тоже самое, но для аргумента «Истины»:

Итак, давайте рассмотрим различные ситуации.

В проверяемом аргументе функции, при работе с обычными числами, вы указываете оператор(или операторы) чтобы проверить, соответствует ли число каким-либо критериям. Вот список этих операторов:

Сразу же рассмотрим такую ситуацию:

Если число в столбце A больше либо равно 35, то результатом выполнения функции будет «Сдал», если же нет, то «Не сдал».

Итак, давайте рассмотрим ситуацию, когда вам нужно проверить, соответствует ли число сразу нескольким критериям. Мы помним, что максимальное число проверяемых аргументов — 64. Давайте попробуем проверить хотя бы 2 критерия.

В приведенном ниже примере мы проверяем два условия.

Меньше ли значение в ячейке чем число 35;

В случае, когда в результате первой проверки возвращается ЛОЖЬ, проверяется больше или равно значение в ячейке чем число 75.

Итак, с помощью этой функции мы можем даже посчитать комиссию, которую забирает себе конкретный продавец.

В ситуации описанной ниже, продавец не получает комиссию, если у него меньше 50-ти продаж. Если первое проверочное условие он прошел, тогда проверяем второе. Если у продавца меньше 100 продаж, его комиссия будет продажи*2%, а если больше, то — продажи*4%

Также, мы можем использовать функции И и ИЛИ для проверки по сразу нескольким критериям.

Допустим, как указано на картинке ниже, мы имеем такую табличку:

Наша задача — рассчитать у кого из студентов будет стипендия. Данные для выдачи стипендии будут сразу же в формуле:

=ЕСЛИ(И(B2>80;C2>80%); "Да"; "Нет")

Теперь давайте разберемся как мы можем фильтровать ошибки при использовании функции.

Формула:

=ЕСЛИ(ЕСЛИОШИБКА(A1);0;A1)

Теперь, если в результате выполнения функции мы получим ошибку, она будет отфильтрована и превращена в 0. А если ошибки не произойдет — мы просто получим значение.

Точно также можно использовать функцию ЕПУСТО:

=ЕСЛИ(ЕПУСТО(A1);0;A1)

Источник

Итоговая практическая контрольная работа

по теме «Электронные таблицы MS Excel»

для 9 класса Вариант 1

N п/п	Ф. И. О.	Тарифный разряд	Процент выполнения плана	Тарифная ставка	Заработная плата с премией
1	Пряхин А. Е.	3	102
2	Войтенко А.Ф.	2	98
3	Суворов И. Н.	1	114
4	Абрамов П. А.	1	100
5	Дремов Е. Л.	3	100
6	Сухов К. О.	2	94
7	Попов Т. Г.	3	100
	Итого

Формулы для расчетов:

Тарифная ставка определяется исходя из следующего:

1200 руб. для 1 разряда;
1500 руб. для 2 разряда;
2000 руб. для 3 разряда.

Размер премиальных определяется исходя из следующего:

— выполнение плана ниже 100% — премия не назначается (равна нулю);

— выполнение плана 100-110% — премия 30% от Тарифной ставки;

— выполнение плана выше 110% — премия 40% от Тарифной ставки.

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

Используя расширенный фильтр, сформируйте список работников, выполнивших и перевыполнивших план.
Используя функцию категории «Работа с базой данных» БДСУММ, подсчитайте суммы заработной платы работников в зависимости от тарифного разряда.
Постройте объемную круговую диаграмму начисления заработной платы работникам.

Вариант 2

Проанализируйте динамику поступления товаров от поставщиков:

Поставщики	2004г. (млн.руб.)	2005г. (млн.руб.)	Превышение (млн.руб.)	В % к 2004г.	Удельный вес в 2004г.	Удельный вес в 2005г.	Изменение удельного веса
СП «Изотоп»	16,6	16,9
АОЗТ «Чипы»	23,4	32,1
ООО «Термо»	0,96	1,2
АО «Роника»	7,5	6,4
СП «Левел»	16,7	18,2
Всего

Формулы для расчетов:

Изменение удельного веса определяется исходя из следующего:

«равны«, если Уд. вес 2005г. равен уд. весу 2004г.;
«больше«, если Уд. вес 2005г. больше уд. веса 2004г.;
«меньше«, если Уд. вес 2005г. меньше уд. веса 2004г.

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

Используя расширенный фильтр, сформируйте список поставщиков, у которых удельный вес в 2004 и 2005 годах не превышал 0,5.
Используя функцию категории «Работа с базой данных» БСЧЕТ, подсчитайте количество поставщиков, у которых значение превышение не больше 0,5млн. руб.
Постройте объемную гистограмму динамики удельного веса поступления товаров в 2004 — 2005 гг. по поставщикам.

Вариант 3

N п/п	Ф.И.О.	Информатика	Математика	Ин. Язык	Надбавка	Начисление стипендии
1	Авдеева А.В.	5	4	5
2	Бесков Р.О.	4	3	3
3	Вегелина М. А.	5	5	5
4	Медведев И.Н.	4	5	5
5	Малащук С.А.	3	3	2
6	Соловьев Г.М.	4	5	4
7	Тарасов О.Л.	4	4	4
	Средний балл

Формулы для расчетов:

Размер стипендии составляет 2 МРОТ (минимальный размер оплаты труда). Стипендия не назначается, т. е. равна «0», если есть хотя бы одна «2».

Надбавка рассчитывается исходя из следующего:

50%, если все экзамены сданы на «5»;
25%, если есть одна «4» (при остальных «5»).

Для заполнения столбца Надбавка используйте функцию ЕСЛИ из категории «Логические».

Используя расширенный фильтр, сформируйте список студентов, сдавших все экзамены только на 4 и 5.
Используя функцию категории «Работа с базой данных» БСЧЕТ, подсчитайте количество студентов, не получивших надбавку.
Постройте объемную круговую диаграмму начисления стипендии.

Вариант 4

Наименование изделий	Объем производства (т)	Цена за кг (руб.)	Торгово-сбытовая скидка (%)	Цена со скидкой (руб.)	Сумма с учетом скидки (руб.)
Колбаса пермская, п/к, 1с	6	59
Колбаса одесская, п/к, 1с	12	83
Колбаса краковская, п/к, в/с	4	90
Колбаски охотничьи, п/к, в/с	2	99
Колбаса сервелат п/к, в/с	3	110
ИТОГО

Формулы для расчетов:

Торгово-сбытовая скидка рассчитывается исходя из следующего:

0.5%, если Цена за кг менее 60 руб.;
5%, если Цена за кг от 60 до 80 руб.;
8%, если Цена за кг более 80 руб.

Для заполнения столбца Торгово-сбытовая скидка используйте функцию ЕСЛИ из категории «Логические».

Используя расширенный фильтр, сформируйте список наименований изделий, объем производства которых составляет от 5 до 10 тонн.
Используя функцию категории «Работа с базой данных» БДСУММ, подсчитайте сумму от реализации колбасных изделий, у которых торгово-сбытовая скидка больше или равна 8%.
Постройте объемную гистограмму изменения цены по изделиям.

Вариант 5

Заполните накопительную ведомость по переоценке основных средств производства (млн. руб.).

N п/п	Наименование объекта	Балансовая стои-мость	Износ	Остаточная стоимость	Восстановительная полная стоимость	Восстановительная остаточная стоимость
1	Заводоуправ-ление	13457	589,3
2	Диспетчерская	187,4	51,4
3	Цех 1	932,6	226,1
4	Цех 2	871,3	213,8
5	Цех 3	768,8	134,9
6	Склад 1	576,5	219,6
7	Склад 2	344,6	98,4
8	Склад 3	567,4	123,5
9	Склад 4	312,6	76,8
	Итого

Формулы для расчетов:

Восстановительная полная стоимость = балансовая стоимость * k

Восстановительная остаточная стоимость = остаточная стоимость * k

Коэффициент k определяется исходя из следующего:

k = 3.0, если Балансовая стоимость больше 500 млн. руб.;
k = 2.0, в остальных случаях.

Используя расширенный фильтр, сформируйте список наименований объектов, балансовая стоимость которых находится в пределах от 400 до 800 млн. руб.
Используя функцию категории «Работа с базой данных» БДСУММ, подсчитайте суммы восстановительной остаточной стоимости, износ объектов по которой составит не больше 100 млн. руб.
Постройте объемную гистограмму восстановительной полной и остаточной стоимостей по всем объектам.

Вариант 6

Рассчитайте стоимость продукции с учетом скидки. Результаты округлите до 2-х знаков после запятой.

Номенклат. номер	Наименование прдукции	Количество (шт.)	Цена (тыс.руб)	Стоимость (тыс.руб.)	% скидки	Сумма скидки (тыс. руб.)	Стоимость с учетом скидки (тыс. руб.)
202	Монитор	5	12
201	Клавиатура	25	0,25
213	Дискета	100	0,02
335	Принтер	2	10
204	Сканер	1	8
	Итого

Формулы для расчетов:

Процент скидки определяется исходя из следующего:

1%, если Стоимость менее 60 тыс. руб.;
7%, если Стоимость от 60 до 100 тыс. руб.;
10%, если Стоимость больше 100 тыс. руб.

Для заполнения столбца Процент скидки используйте функцию ЕСЛИ из категории «Логические».

Используя расширенный фильтр, сформируйте список наименований продукции с теми номенклатурными номерами, по которым стоимость с учетом скидки находится в пределах от 5 до 10 тыс. руб.
Используя функцию категории «Работа с базой данных» БДСУММ подсчитайте общую сумму скидки для продукции с ценой больше 5тыс. руб.,
Постройте объемную гистограмму изменения стоимостей по наименованиям продукции.

Вариант 7

Рассчитайте сумму вклада с начисленным процентом. Результаты округлите до 2-х знаков после запятой.

№ лицевого счета	Вид вклада	Сумма вклада (тыс. руб.)	Остаток вклада с начисленным %
Остаток входящий	Приход	Расход	Остаток исходящий
S3445	Срочный	45		4
F7654	Праздничный	54	6
R5467	До востребования	76	5	9
S8976	Срочный	53		3
R3484	До востребования	15	12	3
S7664	Срочный	4	5	5
	Итого:

Формулы для расчетов:

Остаток вклада с начисленным % рассчитывается исходя из следующего:

Остаток исходящий + 2% от Остатка исходящего, для вклада до востребования;
Остаток исходящий +5% от Остатка исходящего, для вклада праздничный;
Остаток исходящий + 3% от Остатка исходящего, для вклада срочный.

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

Используя расширенный фильтр, сформируйте список номеров лицевых счетов, по которым имеется исходящий остаток больше 50 тыс. руб.
Используя функцию категории «Работа с базой данных» БДСУММ, подсчитайте по срочному виду вклада общую сумму остатков вкладов с начисленным процентом, если сумма расхода по данному вкладу меньше 5 тыс. руб.
Постройте объемную гистограмму изменения суммы вкладов.

Вариант 8

Рассчитайте начисленную заработную плату сотрудникам малого предприятия.

Номер п/п	Ф. И. О.	Дата поступления на работу	Стаж работы	Зарплата (руб.)	Надбавка (руб.)	Премия (руб.)	Всего начислено (руб.)
1	Моторов А.А.	10.04.91		3000
2	Унтура О. И.	12.06.98		2500
3	Дискин Г. Т.	02.03.95		2000
4	Попова С. А.	17.02.92		1500
5	Скатт О. И.	15.01.99		1000
	Итого

Формулы для расчетов:

Надбавка рассчитывается исходя из следующего:

0, если Стаж работы меньше 5 лет;
5% от Зарплаты, если Стаж работы от 5 до 10 лет;
10% от Зарплаты, если Стаж работы больше 10 лет.

Для заполнения столбца Надбавка используйте функцию ЕСЛИ из категории «Логические».

Премия = 20% от (Зарплата + Надбавка).

Используя расширенный фильтр, сформируйте список сотрудников со стажем работы от 5 до 10 лет.
Используя функцию категории «Работа с базой данных» БСЧЕТ, определите количество сотрудников, у которых зарплата больше 1000 руб., а стаж работы больше 5 лет.
Постройте объемную гистограмму начисления зарплаты по сотрудникам.

Вариант 9

Рассчитайте доходы фирмы за два указанных года. Результаты округлите до 2-х знаков после запятой.

№ п/п	Модели фирм- производителей компьютеров	Доходы, млн. долл. 2003г.	Доходы, млн. долл. 2004г.	Торговая доля от продажи 2003г.	Торговая доля от продажи 2004г.	Оценка доли от продажи
2	Apple	80,2	84,5
3	NEC	78,6	90,5
4	Olivetti	41,3	66,0
5	Toshiba	70,0	104,9
	Всего:

Формулы для расчетов:

Торговая доля от продажи = Доход каждой модели / Всего

Оценка доли от продажи определяется исходя из следующего:

» равны«, если Доли от продажи 2003г. и 2004г. равны;
«превышение«, если Доля от продажи 2003г. больше 2004г.;
«уменьшение«, если Доля от продажи 2003г. меньше 2004г.

Для заполнения столбца используйте функцию ЕСЛИ из категории «Логические».

Используя расширенный фильтр, сформируйте список моделей фирм-производителей компьютеров, доходы от продаж которых и в 2003, и в 2004 годах составляли бы больше 70 млн. у. е.
Используя функцию категории «Работа с базой данных» БСЧЕТ, подсчитайте количество моделей фирм-производителей компьютеров, торговая доля от продажи которых меньше 30 %.

Постройте объемную гистограмму доходов фирмы 2003-2004гг.

Вариант 10

Рассчитайте начисление комиссионных сотрудникам малого предприятия:

Номер п/п	Ф. И. О.	Выручка, руб.	Комиссионные, руб.
1	Моторов А.А.	100000
2	Турканова О. И.	550000
3	Басков Г. Т.	340000
4	Попова С. А.	60600
5	Антонов П. П.	23800
6	Суслова Е. И.	5000
	Итого

Формулы для расчетов:

Комиссионные рассчитываются исходя из следующего:

2%, если Выручка менее 50000 руб.;
3%, если Выручка от 50000 до 100000 руб.;
4%, если Выручка более 100000 руб.

Для заполнения столбца Комиссионные используйте функцию ЕСЛИ из категории «Логические».

Используя расширенный фильтр, выдайте список сотрудников, объем выручки у которых составляет от 50000 руб. до 100000 руб.
Используя функцию категории «Работа с базой данных» БСЧЕТ, определите количество сотрудников, у которых выручка менее 50000 руб.
Постройте объемную гистограмму объема продаж по сотрудникам и круговую диаграмму начисления размера комиссионных.

Вариант 11

Рассчитайте стоимость перевозки

Код товара	Вес, брутто	Тариф за кг, у.е.	Сумма оплаты за перевозки	Издержки	Всего за транспорт
948XT	920	0,3
620LT	420	12,7
520KT	564	5,77
900PS	210	5,95
290RT	549	3,98
564ER	389	34,7
764NT	430	12,9
897VC	653	34,6

Формулы для расчетов:

Сумма оплаты за перевозки для каждого товара = Вес * Тариф;

Издержки рассчитываются исходя из следующего:

для веса более 400 кг – 3% от Суммы оплаты;
для веса более 600 кг – 5% от Суммы оплаты;
для веса более 900 кг – 7% от Суммы оплаты.

Для заполнения столбца Издержки используйте функцию ЕСЛИ из категории «Логические».

Всего за транспорт = Сумма оплаты за перевозки — Издержки.

Используя расширенный фильтр, сформируйте список кодов товаров, сумма оплаты за перевозки для которых составляет от 1000 до 4000 у.е.
Используя функцию категории «Работа с базой данных» БСЧЕТ, определите сколько видов (кодов) товаров имеют тариф за кг от 5 до 30 у.е.
Постройте объемную круговую диаграмму, отражающую сумму оплаты перевозок для каждого кода товаров.

Вариант 12

Заполните ведомость по налогам сотрудников предприятия.

№ п/п	ФИО	Всего начислено, руб.	Пенсионный фонд, руб.	Налогооблагаемая база, руб.	Налог, руб.
1	Иванов А.Л.	3800
2	Иванов С.П.	4550
3	Дутова О.П.	1000
4	Карпов А.А.	6050
5	Клыков О.Н.	4880
6	Львов Г.В.	6600
7	Миронов А.М.	7950
Итого

Формулы для расчетов:

Налог определяется исходя из следующего:

12% от Налогооблагаемой базы, если Налогооблагаемая база меньше 1000 руб.;
20% от Налогооблагаемой базы, если Налогооблагаемая база больше 1000 руб.

Для заполнения столбца Налог используйте функцию ЕСЛИ из категории «Логические».

Пенсионный фонд = 1% от «Всего начислено».

Налогооблагаемая база = Всего начислено — Пенсионный фонд

Итого = сумма по столбцам Всего начислено, Пенсионный фонд и Налог

Используя расширенный фильтр, сформируйте список сотрудников, у которых «Всего начислено» составляет от 350 руб. до 5000 руб.
Используя функцию категории «Работа с базой данных» БСЧЕТ, определите количество сотрудников, у которых налог меньше 800 руб.
Постройте объемную круговую диаграмму начислений по сотрудникам.

Вариант 13

Формирование цен:

Артикул товара	Оптовая цена (руб.)	Розничная цена (руб.)	Цена со скидкой (руб.)	Ценовая категория
23456А	1500
56789А	2300
985412В	4580
56789С	5620
456856В	2280
45698А	2450
7895621В	6540

Коэффициент опта	0,1
Коэффициент скидки	0,15

Формулы для расчетов:

Розничная цена = Оптовая цена + Оптовая цена * Коэффициент опта

Цена со скидкой = Розничная цена – Розничная цена * Коэффициент скидки

Ценовая категория определяется исходя из следующего:

«нижняя», если розничная цена ниже 2000 рублей;
«средняя», если цена находится в пределах от 2000 до 5000 рублей;
«высшая», если цена выше 5000 рублей.

Для заполнения столбца Ценовая категория используйте функцию ЕСЛИ из категории «Логические».

Используя расширенный фильтр сформируйте список товаров оптовая цена которых находится в диапазоне от 3000 до 6000 рублей.
Используя функцию категории «Работа с базой данных» БСЧЕТ определите количество товаров, которые попадают в среднюю ценовую категорию.
Постройте объемную гистограмму, на которой отобразите оптовые и розничные цена по каждому виду товаров.

Вариант 14

Продажа принтеров:

№ п/п	Модели	Цена, $	Заказано (шт)	Продано (шт)	Объем продаж, $	Комиссионные, $
1	Принтер лазерный Ч/Б	430	60	52
2	Принтер лазерный Ц/В	2000	10	2
3	Принтер струйный Ч	218	56	50
4	Принтер струйный Ч/Б	320	40	32
Итого

Формулы для расчетов:

Комиссионные определяются в зависимости от объема продаж:

2%, если объем продаж меньше 5000$;
3%, если объем продаж от 5000$ до 10000$;
5%, если объем продаж более 10000$.

Для заполнения столбца Комиссионные используйте функцию ЕСЛИ из категории «Логические».

Объем продаж = Цена * Количество (Продано)

Итого = сумма по столбцам Продано, Объем продаж и Комиссионные.

Используя расширенный фильтр, сформируйте список моделей принтеров, объем продаж которых составил более 10000$.
Используя функцию категории «Работа с базой данных» БДСУММ, определите объем продаж у принтеров лазерных (ЧБ и ЦВ).
Постройте объемную круговую диаграмму объема продаж принтеров.

Вариант 15

Смета на приобретение канцелярских товаров:

№ п/п	Наименование	Кол-во, Шт.	Цена, руб.	Стоимость, руб.	Cкидка, руб	Стоимость с учетом скидки, руб.
1	Тетради простые в клетку	150	3.00
2	Ручки шариковые с синим стержнем	70	11.50
3	Карандаши простые, НВ	100	6.00
4	Ластики	20	2.00
5	Линейки пластмассовые, 35 см.	10	8.10
Итого

Формулы для расчетов:

Скидка определяется исходя из следующего:

0% от Стоимости, если Количество меньше 50;
2% от Стоимости, если Количество от 50 до 100;
5%, от Стоимости, если Количество более 100.

Для заполнения столбца Скидка используйте функцию ЕСЛИ из категории «Логические».

Стоимость с учетом скидки = Стоимость – Скидка

Итого = сумма по столбцу Стоимость с учетом скидки.

Используя расширенный фильтр, выдайте список канцелярских товаров, цена которых составляет больше 5 руб.
Используя функцию категории «Работа с базой данных» БСЧЕТ, подсчитайте количество канцелярских товаров, у которых цена более 7 руб.
Постройте объемную круговую диаграмму, характеризующую сумму скидки.

Вариант 16

Текущее состояние дел в книжной торговле:

Номер п/п	Название	Автор	Цена опт	Цена розничн.	Кол-во	Оплачено	Продано	Приход	Расход	Баланс
1	Практическая работа с MS Excel	Долженков	80	90	30	10	8
2	Excel одним взглядом	Вострокнутов	30	35	50	30	28
3	Шпаргалка по Excel	Столяров	20	25	40	20	35
4	Разработка приложений в Access 98	Нортон	150	165	6	6	2
5	Access 98. Библиотека ресурсов	О`Брайен	140	155	5	0	2
6	Excel 98. Библиотека ресурсов	Уэллс	140	155	5	0	1
7	Access 7.0 в примерах	Гончаров	70	80	15	10	15

Формулы для расчетов:

Приход = Продано * Цена розничная

Расход = Оплачено * Цена оптовая * 0,8 + Анализ продаж, где

Анализ продаж определяется исходя из следующего:

если Продано Оплачено, то Анализ продаж = (Продано – Оплачено) * Цена оптовая;
0, в остальных случаях.

Для заполнения столбца Расход используйте функцию ЕСЛИ из категории «Логические».

Баланс = Приход — Расход

Используя расширенный фильтр, сформируйте список названий книг, оптовая цена которых находится в пределах от 20 руб. до 70 руб.
Используя функцию категории «Работа с базой данных» БСЧЕТ, определите, сколько книг имеют розничную цену более 80 руб.
Постройте объемную круговую диаграмму, характеризующую показатель Оплачено.

Вариант 17

Движение пассажирских самолетов из аэропорта Новосибирск – Северный:

Номер рейса	Самолет	Кол-во пассажиров	Аэропорт назначения	Расстояние	Цена билета, руб.	Скидка	Цена билета со скидкой	Стоимость за рейс
ПК 662	ЯК-40	32	Кызыл	840	3200
СЛ 2029	АН-24	48	Надым	1320	4300
СЛ 2021	АН-24	48	Нижневартовск	750	2300
СЛ 5006	АН-24	48	Нижневартовск	750	2300
СЛ 2031	АН-24	48	Салехард	1560	5400
СЛ 2025	АН-24	48	Стрежевой	720	2300
СЛ 2039	АН-24	48	Сургут	900	2800
СП 5002	АН-24	48	Томск	280	600
СП 2015	АН-24	48	Ханты-Мансийск	1100	4000

Формулы для расчетов:

Скидка определяется исходя из следующего:

0% от Цены билета, если Расстояние меньше 800 км;
2% от Цены билета, если Расстояние от 800 км до 1100 км;
3% от Цены билета, если Расстояние более 1100 км.

Для заполнения столбца Скидка используйте функцию ЕСЛИ из категории «Логические».

Цена билета со скидкой = Скидка * Цена билета

Стоимость за рейс со скидкой = Цена билета со скидкой * Количество пассажиров

Используя расширенный фильтр, сформируйте список городов для которых расстояние до Новосибирска более 900 км.
Используя функцию категории «Работа с базой данных» БДСУММ, определите общую стоимость со скидкой рейсов СЛ 2031 и СП 5002.
Постройте объемную круговую диаграмму, характеризующую цену билета со скидкой.

Вариант 18

Ведомость доходов железных дорог (руб.):

Номер ж.д.	Объем перевозок, руб.	Удельный вес	Доходная ставка за 10т/км	Средняя дальность перевозок	Сумма доходов
1010	5800		20,3	400
1011	1200		30,3	500
1012	3500		20,5	640
1013	4700		18,5	700
1014	3600		21,4	620
2000	3400		20,7	720
2010	4500		32,4	850
2110	4100		28,7	700
Итого

Формулы для расчетов:

Сумма доходов = Объем перевозок * Доходная ставка / 10 * Удельный вес * k, где

k равно:

0.3, если средняя дальность перевозок больше 650 км;
0.2,если средняя дальность перевозок меньше 650 км.

Удельный вес = Объем перевозок / Итог объема перевозок * 100

Итого = сумма по столбцу Объем перевозок

Используя расширенный фильтр, определите у какой железной дороги объем перевозок больше 4000 руб.
Используя функцию категории «Работа с базой данных» БДСУММ, определите общую сумму доходов железной дороги 1012 и 2110.
Постройте объемную круговую диаграмму, характеризующую сумму доходов каждой железной дороги.

Вариант 19

Кондиционеры из Японии

№ п/п	Модель	Длина (см)	Ширина (см)	Высота (см)	Цена розн. ($)	Цена розн. (т.руб.)	Скидка (т.руб.)	Цена розн. со скидкой	Объем (куб.см.)
1	FTY256VI	75	25	18	1400
2	FTY356VI	75	25	18	1750
3	FTY456VI	105	30	19	2390
4	FTY606VI	105	30	19	2830
5	LS-PO960HL	79	23	14	960
6	LS-S1260HL	88	30	18	1100
7	LS-D2462HL	108	29	18	1800

Формулы для расчетов:

Скидка определяется исходя из следующего:

0%, если Цена розничная ($) меньше 2000$;
3%, если Цена розничная ($) больше 2000$.

Для заполнения столбца Скидка используйте функцию ЕСЛИ из категории «Логические».

Цена розничная (руб.) = Цена розничная ($) * Курс доллара.

Цена розничная со скидкой (руб.) = Цена розничная (руб.) * Скидка

Используя расширенный фильтр, сформируйте список моделей кондиционеров, имеющих розничную цену более 2000$.
Используя функцию категории «Работа с базой данных» БСЧЕТ, определите, у скольких моделей кондиционеров длина составляет от 80 см до 105 см.
Постройте объемную круговую диаграмму по объемам кондиционеров.

Вариант 20

Объем реализации товара

№ магазина	Товар 1	Товар 2	Товар 3	Объем реализации, тыс.руб.	Комиссионные, тыс.руб.	Удельный вес, %
Магазин № 15	41	43	39
Магазин №28	138	140	141
Магазин №30	234	137	138
Магазин №45	139	335	237
Магазин №58	52	150	53
Итого

Формулы для расчетов:

Комиссионные определяются исходя из следующего:

— 2%, если объем реализации менее 300 тыс.руб.
— 5%, если объем реализации более 300 тыс.руб.

Для заполнения столбца Комиссионные используйте функцию ЕСЛИ из категории «Логические».

Объем реализации = Товар 1 + Товар 2 + Товар 3

Удельный вес = Объем реализации каждого магазина / Итог объема реализации * 100

Используя расширенный фильтр, сформируйте список магазинов, имеющих объем реализации более 400 тыс.руб.
Используя функцию категории «Работа с базой данных» БСЧЕТ, определите суммарный объем реализации в магазинах № 28 и № 30
Постройте объемную круговую диаграмму удельного веса по каждому маггазину.

Вариант 21

Внутренние затраты на исследования и разработки по секторам деятельности:

Секторы деятельности	млн.руб., 1998г.	в % к итогу, 1998г.	млн.руб. 1999г.	в % к итогу, 1999г.	млн.руб. 2000г.	в % к итогу, 2000г.	Характеристика затрат 2000г.
Государствен.	6465,9		13828,8		18363,3
Предпринимат.	17296,6		27336,0		52434,5
Высш. образование	1297,1		2090,4		2876,2
Частный бесприбыльный	22,4		51,3		73,7
Максим. затраты
Средние затраты
Всего:	25082,0	100,0	43306,5	100	73747,7	100

Формулы для расчетов:

«в % к итогу, 1998» = «млн. руб., 1998» / Всего по графе «млн.руб., 1998» _*100

«в % к итогу, 1999» = «млн. руб. 1999» / Всего по графе «млн. руб. 1999» * 100

«в % к итогу, 2000» = «млн. руб. 2000» / Всего по графе «млн. руб. 2000» * 100

Максимальные затраты1998 = МАХ («млн.руб., 1998»)

Максимальные затраты1999 = МАХ («млн.руб. 1999»)

Средние затраты2000 = СРЗНАЧ («млн.руб. 2000»)

Характеристика затрат 2000 года рассчитывается исходя из следующего:

«повысились», если затраты в 2000 году (млн. руб.) больше, чем соответствующие затраты в 1999 году;
«снизились», если затраты 2000 году (млн. руб.) меньше, чем соответствующие затраты в 1999 году.

Для заполнения столбца Характеристика затрат используйте функцию ЕСЛИ из категории «Логические».

Используя расширенный фильтр, составьте список секторов деятельности с затратами на исследования в 2000 году в размерах от 1500 до 20000 млн. руб.
Используя функцию категории «Работа с базой данных» БДСУММ, определите общую сумму затрат на исследования в предпринимательском и частном секторах деятельности.
Построить объемную гистограмму, отражающую затраты на исследования в 1998-2000 году по секторам экономики.

Вариант 22

Книга продаж: Ксероксы

Модель	Название	Стоимость (руб.)	Цена (руб.)	Кол-во (шт.)	Сумма (руб.)	Ценовая категория
C100GLS	Персональный	827		564
C110GLS	Персональный	993		632
C200GLS	Персональный+	1429,5		438
C210GLS	Персональный+	1715,86		645
C300GLS	Деловой	2410		437
C310GLS	Деловой	2965,3		534
C400GLS	Профессиональный	4269,65		409
C410GLS	Профессиональный	5123,5		395
C420GLS	Профессиональный+	6415		298
C500GLS	Профессиональный+	7377,9		328
	Итого:
	Средняя стоимость
	Коэффициент	1,3

Формулы для расчетов:

Цена = Стоимость * Коэффициент

Сумма = Цена * Кол-во

Итого = сумма по графе «Сумма»

Средняя стоимость = СРЗНАЧ (Стоимость)

Ценовая категория рассчитывается исходя из следующего:

«средняя», если цена находится в пределах от 1 до 5 тысяч рублей;
«высшая», если цена выше 5 тысяч рублей.

Для заполнения графы Ценовая категория используйте функцию ЕСЛИ из категории «Логические».

Используя расширенный фильтр, выведите модели и наименования ксероксов, чья цена находиться в пределах от 2 до 6 тысяч рублей.
Используя функцию категории «Работа с базой данных» БДСУММ, вычислите общую сумму от продажи ксероксов с названиями “Профессиональный” и “Профессиональный+ ”.
Постройте объемную круговую диаграмму, отражающую количество проданных ксероксов всех моделей.

Вариант 23

5 крупнейших компаний России по объему реализации продукции в 1999 году

Компания	Объем реализации, млн. руб.	Прибыль после налогообложения, млн. руб.	Уровень рентабельности, %	Характеристика рентабельности
НК «Лукойл»	268207,0	30795,0
ОАО «Сургутнефтегаз»	80827,0	30931,9
РАО «Норильский никель»	66819,2	36716,4
НК «Юкос»	52013,7	6265,3
АвтоВАЗ	47999,1	1686,6
Средний уровень рентабельности
Максимальная прибыль

Формулы для расчетов:

Уровень рентабельности = Прибыль после налогообложения / Объем реализации*100

Средний уровень рентабельности = среднее значение по графе «Уровень рентабельности»

Максимальная прибыль = максимальное значение по графе «Прибыль после налогообложения»

Характеристика рентабельности рассчитывается исходя из следующего:

средняя, если уровень рентабельности до 30%;
высокая, если уровень рентабельности выше 30%.

Для заполнения графы Характеристика рентабельности используйте функцию ЕСЛИ из категории «Логические».

Используя расширенный фильтр, составьте список компаний с уровнем рентабельности от 15 до 40%.
Используя функцию категории «Работа с базой данных» БСЧЕТ, подсчитайте общее количество компаний с прибылью более 30000 млн.руб.
Постройте объемную круговую диаграмму, отражающую объем реализации продукции каждой компании из приведенного списка.

Вариант 24

Книга продаж: Факсы

Модель	Название	Стоимость (руб.)	Цена (руб.)	Кол-во (шт.)	Сумма (руб.)	Сфера применения
F100G	Персональный	1607,96		564
F150G	Персональный	1840		420
F200G	Персональный+	1729,55		634
F250G	Персональный+	2075,66		432
F300G	Деловой	2550,55		297
F350G	Деловой	2760,66		437
F400G	Профессиональный	3512,8		324
F450G	Профессиональный	3815,35		289
F500G	Профессиональный+	4878,34		211
F550G	Профессиональный+	5614,11		108
	Итого:
	Максимальная цена
	Коэффициент	1,3

Формулы для расчетов:

Цена = Стоимость * Коэффициент

Сумма = Цена * Кол-во

Итого = сумма по графе «Сумма»

Максимальная цена = максимальное значение по графе «Цена»

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

«коммерческие фирмы», для моделей Профессиональный;
«широкое применение» – все остальные модели факсов.

Для заполнения графы Сфера применения используйте функцию ЕСЛИ из категории «Логические».

Используя расширенный фильтр, выведите модели и наименования факсов, которых было продано от 300 до 500 штук.
Используя функцию категории «Работа с базой данных» БДСУММ, вычислите общую сумму от продажи факсов с наименованиями «Персональный» и «Персональный +».
Постройте объемную круговую диаграмму, отражающую стоимость проданных факсов всех моделей.

Вариант 25

Некоторые крупнейшие компании России по рыночной стоимости (капитализации) на 1 сентября 2000 года

Компания	Капитализация компании, руб.	Цена (котировка) обыкновенной акции, долл.	Число обыкновенных акций, шт.	Оценка котировки акций
ОАО «Сургутнефтегаз»		0,3863	35725994705
НК «Лукойл»		16,0694	738351391
ОАО «Газпром»		0,3167	23673512900
НК «Юкос»		1,6711	2236991750
Мобильные телесистемы		1,4250	1993326150
Ростелеком		2,3550	700312800
Аэрофлот		0,2057	1110616299
Максимальная цена, долл.
Курс ЦБ на 01.09.2000 (руб/долл)		27,75

Формулы для расчетов:

Капитализация компании = Число обыкновенных акций / Цена *Курс ЦБ/ 1000000

Оценка котировки акций определяется исходя из следующего:

«спад», если цена котировки устанавливается ниже отметки 1;
«подъем», если цена котировки устанавливается выше отметки больше 10;
«стабильно», если цена котировки устанавливается на отметке от 1 до 10.

Для заполнения графы Оценка котировки акций используйте функцию ЕСЛИ из категории «Логические».

Используя расширенный фильтр, составьте список компаний, у которых число обыкновенных акций находиться в пределах от 1000000000 до 20000000000 шт.
Используя функцию категории «Работа с базой данных» БСЧЕТ, подсчитайте количество компаний, у которых цена за 1 акцию превышает 1 доллар.
Постройте объемную круговую диаграмму, отражающую уровень капитализации компаний.

Вариант 26

Производительность труда в пяти крупнейших компаниях России в 1999 году

Компания	Отрасль	Объем реализации, млн.руб.	Численность занятых, тыс.чел.	Производи-тельность труда, тыс.руб/чел	Характери-стика производи-тельности
ОАО «Газпром»	Нефтяная и нефтегазовая промышлен.	30599,0	298,0
НК «ЛУКойл»	Нефтяная и нефтегазовая промышлен.	268207,0	120,0
РАО «ЕЭС России»	Эл/энергетика	247477,0	669,5
ОАО «Сургутнефтегаз»	Нефтяная и нефтегазовая промышлен.	80827,0	70,1
РАО «Норильский никель»	Нефтяная и нефтегазовая промышлен.	66819,2	102,7
Средняя производительность труда
Максимальный объем реализации

Формулы для расчетов:

Производительность труда = Объем реализации / Численность занятых

Средняя производительность труда = среднее значение по графе «Средняя производительность труда»

Максимальный объем реализации = максимальное значение по графе «Объем реализации»

Характеристика производительности определяется исходя из следующего:

«выше средней», если производительность труда больше, чем средняя производительность труда;
«ниже средней», если производительность труда меньше, чем средняя производительность труда.

Используя расширенный фильтр, составьте список компаний, с численностью занятых более 150 тыс. чел.
Используя функцию категории «Работа с базой данных» БСЧЕТ, подсчитайте общее количество компаний с производительностью более 1000 тыс.руб./чел.
Постройте объемную круговую диаграмму, отражающую распределение численности занятых по компаниям.

Вариант 27

ВВП и ВНП 15 ведущих государств мира

Страны	ВВП_1998, млрд.долл.	Численность населен_1998 млн.чел.	ВВП на душу нас-я_1998, тыс.долл.	ВВП_1999, млрд.долл.	Участие страны в про- изводстве мирового ВВП, %	Прирост ВВП, млрд.долл.	Оценка изменения ВВП
Австралия	364,2	18,8		395
Аргентина	344,4	36,1		282
Великобрит.	1357,4	59,1		1437
Бельгия	247,1	10,2		248
Германия	2142,0	82,1		2115
Испания	551,9	39,3		597
Италия	1171,0	57,6		1173
Канада	598,8	30,6		639
Нидерланды	382,5	15,7		394
США	8210,6	270		9256
Франция	1432,9	58,8		1435
Швейцария	264,4	7,1		260
Швеция	225	8,9		239
Ю.Корея	297,9	46,4		407
Япония	3783,1	126,3		4349
Всего

Формулы для расчетов:

ВВП на душу населения_1998= ВВП_1998/ Численность населен_1998

Прирост ВВП = ВВП_1999 — ВВП_1998

Участие страны в производстве мирового ВВП = ВВП_1999 /Всего(ВВП_1999) *100

Всего(ВВП_1999) = сумма по графе «ВВП_1999»

Оценка изменения ВВП определяется исходя из следующего:

«ухудшение», если наблюдается отрицательный прирост ВВП;
«развитие», если наблюдается положительный прирост ВВП;
«стабильность» — для нулевого значения ВВП.

Используя расширенный фильтр, составьте список стран с численностью от 50 до 150 млн.чел.
Используя функцию категории «Работа с базой данных» БСЧЕТ, подсчитайте общее количество стран с отрицательным показателем прироста ВВП.
Построить объемную гистограмму, на которой отразите показатель ВВП в 1998 и 1999 годах для первых пяти стран списка.

Вариант 28

Распределение занятого в экономике регионов населения по формам собственности в 1998 году

Регионы (районы)	Всего занято в экономике, тыс.чел.	Гос. и муницип., тыс.чел	Обществ. организац., тыс.чел.	Частная, тыс.чел.	Другие, тыс.чел.	Преобла-дание собствен-ности в регионе
Калининградск. обл.	399,6	161,2	2,4	174,6	61,3
Северный	2368,3	1097,5	11,5	743,9	515,4
Северо-Западн.	3605,4	1402,2	29,6	1649,3	524,2
Центральный	13277,2	5097,7	108,6	5640,1	2430,9
Волго-Вятский	3586,5	1373,3	36,9	1528,9	647,4
Центрально-Черноземный	3151,4	1169,0	23,3	1561,9	397,1
Поволожский	7028,9	2754,3	52,2	2693,6	1528,8
Сев.-Кавказск.	6110,7	2237,9	55,2	2962,1	855,5
Уральский	8459,6	3476,5	62,1	3167,7	1753,3
Зап-Сибирск.	6429,5	2487,9	31,0	2501,9	1471,7
Вост-Сибирск.	3604,6	1531,1	16,0	1311,9	745,5
Дальневосточн.	3157,4	1467,3	16,5	1127,6	546,0
Итого

Формулы для расчетов:

Уд.вес_гос_собств. = Гос. и муницип / Итого «Гос. и муницип.» * 100

Уд.вес_ обществ.организац. = Обществ.организац. / Итого «Обществ. организац.» * 100

и т.д. по всем формам собственности

Преобладание собственности в регионе определяется исходя из следующего:

«преобладание частной» для регионов, где частная собственность превышает государственную;
«преобладание государственной», для регионов, где государственная собственность превышает частную.

Итого «Всего занято в экономике» = сумма по графе «Всего занято в экономике»

Итого «Гос. и муницип.» = сумма по графе «Гос. и муницип.»

Итого «Обществ. организац.» = сумма по графе «Обществ. организац.»

и т.д. по всем формам собственности.

Используя расширенный фильтр, составьте список регионов с долей населения, занятого на предприятиях с частной формой собственности, от 10% до 25%.
Используя функцию категории «Работа с базой данных» БДСУММ, подсчитайте общее количество человек, работающих в государственном секторе, с долей занятого населения в них более 10%.
Постройте объемную круговую диаграмму, отражающую доля населения в частном секторе регионов России от Урала до Дальнего Востока.

Вариант 29

Таблица народонаселения некоторых стран:

Страна	Площадь, тыс. км2	Население, тыс. чел.	Плотность населения, чел./км2	В % от населения всего мира	Место в мире по количеству населения
Россия	17 075	149 000
США	9 363	252 000
Канада	9 976	27 000
Франция	552	56 500
Китай	9 561	1 160 000
Япония	372	125 000
Индия	3 288	850 000
Израиль	14	4 700
Бразилия	2 767	154 000
Египет	1 002	56 000
Нигерия	924	115 000
Весь мир		5 292 000

Формулы для расчетов:

Плотность населения = Население / Площадь

В % от населения всего мира = Население каждой страны / Весь мир * 100

Место в мире по количеству населения рассчитайте исходя из следующего:

1 место, если Население больше 1000000 тыс.;
2 место, если Население больше 800000 тыс.;
3 место — остальные.

Для заполнения столбца Плотность населения используйте функцию ЕСЛИ из категории «Логические».

Используя расширенный фильтр, сформируйте список стран с площадью более 5000 тыс.км².

Используя функцию категории «Работа с базой данных» БСЧЕТ, подсчитайте количество стран с плотностью населения от 100 до 300 чел/км².
Постройте объемную круговую диаграмму, отражающую площадь для всех стран.

Вариант 30

Продукты	Новосибирск	Барнаул	Томск	Омск	Кемерово	Средняя цена	Оценка средней цены
Говядина	56,67	54,57	59,1	42,79	45,67
Птица	53,54	45,34	48,2	48,1	48,31
Колбаса	85	76,87	66,73	71,63	81
Масло слив.	73,16	62,34	60,75	60,45	60,97
Масло раст.	28,45	19,76	23,1	23	22,25
Творог	47,57	41,75	37,94	39,49	38,24
Молоко	9,73	7,42	9,75	8	9,88
Яйцо (10шт.)	16	15,6	16	15,58	15,61
Сахар	17	14,47	14,73	14,23	15,54
Мука	7,29	5,76	6,53	6,15	6,76
Картофель	5	3,31	3,74	4,54	3,32
Итого:

1. Формулы для расчетов:

Среднюю цену рассчитайте с помощью функции СРЗНАЧ из категории «Математические».

Оценку средней цены продуктов определите исходя из следующего:

дорогие продукты, если цена40 рублей за килограмм;
недорогие продукты, в ином случае.

3.Используя расширенный фильтр, сформируйте список продуктов, у которых средние цены имеют значение от 20 до 40 рублей.

5.Постройте объемную гистограмму по данным о ценах на муку по всем городам.

Вариант 31

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

Наименование нефтепродукта	Производство, тыс. тонн	Облагаемая реализация, тыс. тонн	Ставка налога с оборота на 1 тонну	Налог с оборота	Место по производству нефтепродуктов
Автобензин	1610	730	150
Мазут	4300	4200	3
Топливо диз.	50	50	14
Керосин	35	35	14
Итого

Формулы для расчетов:

Сумма налога с оборота = Ставка налога * Облагаемая реализация.

Итого = сумма по графе Налог с оборота.

Место по производству нефтепродуктов определяется исходя из следующего:

1 место, если Производство 3000 тыс.тонн;
2 место, если Производство1000 тыс.тонн;
3 место, если Производство40 тыс.тонн .

Используя расширенный фильтр, сформируйте список нефтепродуктов, производство которых составляет от 1000 до 5000 тыс. т.
Используя функцию категории «Работа с базой данных» БСЧЕТ, подсчитайте количество нефтепродуктов, у которых ставка налога с оборота меньше 10.
Постройте объемную круговую диаграмму ставок налога с оборота по каждому виду нефтепродукта.

Вариант 32

Классы предприятий по основным фондам, млрд. руб.	Количество	Объем товарной продукции, млрд. руб.	Численность, тыс. чел.	Место по объему товарной продукции
0 – 1	25	53,525	4,343
1 – 5	57	488,95	21,380
5 – 10	28	390,693	20,830
10 – 50	44	1964,749	68,631
50 – 100	10	901,538	55,899
100 – 200	5	717,813	40,625
200	4	103,033	71,880
Итого:

Формулы для расчетов:

Место каждого предприятия по объему товарной продукции определяется исходя из следующего:

1 место, если Объем больше 1000 млрд.руб.
2 место, если Объем больше 800 млрд.руб.
3 место, если Объем больше 600 млрд.руб.

Используя расширенный фильтр, сформируйте список классов предприятий, объем товарной продукции у которых находится в интервале от 200 до 900 млрд. руб.
Используя функцию категории «Работа с базой данных» БДСУММ подсчитайте общий объем товарной продукции тех предприятий, у которых численность меньше 50 тыс. чел.
Постройте объемную круговую диаграмму распределения численности предприятий по классам.

Вариант 33

Уровень образования	1970	1979	1989
Высшее законченное	7544	13486	20200
Высшее незаконченное	1457	1541	1900
Среднее специальное	12123	21007	33100
Среднее общее	18347	37293	52600
Неполное среднее	35976	35307	22800
Итого
Номер места

Формулы для расчетов:

Итого = сумма по столбцам 1970, 1979, 1989.

Номер места работающего населения по итогам каждого года, определяется исходя из следующего:

1 место, если Итого за год 120000 тыс. человек;
2 место, если Итого за год 100000 тыс. человек;
3 место – в ином случае.

Для заполнения строки Номер места, используйте функцию ЕСЛИ из категории «Логические».

Используя расширенный фильтр, сформируйте список уровней образования за 1989 г., по которым численность работающего населения составляла от 20000 до 40000 тыс. чел.
Используя функцию категории «Работа с базой данных» БСЧЕТ, подсчитайте количество уровней образования, по которым в 1979 г. численность работающего населения составляла больше 20000 тыс. чел.

Постройте объемную гистограмму соотношения уровней образования по каждому году.

Источник

Введение

Программа MS Excel, являясь лидером на рынке программ
обработки электронных таблиц, определяет тенденции развития в этой области.
Вплоть до версии 4.0 программа Excel представляла собой фактический стандарт с
точки зрения функциональных возможностей и удобства работы. Теперь на рынке
появилась версия 5.0, которая содержит много улучшений и приятных
неожиданностей.

К значительным достижениям в новой версии программы Excel
можно отнести появление трехмерных документов (блокнотов). Установление связей
между файлами и таблицами значительно упростилось по сравнению с прежними
версиями. Контекстные меню значительно расширены, а дополнительные программные
инструменты облегчают решение сложных прикладных задач.

Следует также упомянуть о различных помощниках (Ассистентах),
которые помогают пользователю задавать функции и выдают рекомендации, если
существует более простой метод решения текущей задачи. В программу Excel
встроена удобная подсистема помощи, которая в любой момент готова выдать
необходимую справку.

Описанные до сих пор новшества касаются в основном комфорта в
работе и быстрого освоения программы. Одним из важнейших функциональных
расширений программы, предназначенным для профессионалов, является встроенная в
Excel Среда программирования Visual Basic (VBA) для решения прикладных задач.
Благодаря VBA фирме Microsoft удалось не только расширить возможности языка
макрокоманд Excel 4.0, но и ввести новый уровень прикладного программирования,
поскольку VBA позволяет создавать полноценные прикладные пакеты, которые по
своим функциям выходят далеко за рамки обработки электронных таблиц. Кроме
этого, следует назвать следующие важные новшества программы Excel 5.0: менеджер
файлов, который выдает подробную информацию о всех файлах; диалоговые
окна-регистры; отдельная пиктограмма для форматирования; — появление механизма
Drag & Plot, предназначенного для быстрой активизации диаграмм.

1. Теоретическая часть

Формулы

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

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

В качества аргументов в формуле обычно используются числа и
адреса ячеек. Для обозначения арифметических операций могут использоваться
следующие символы: + (сложение); (вычитание); * (умножение); / (деление).

Формула может содержать ссылки на ячейки, которые расположены
на другом рабочем листе или даже в таблице другого файла. Однажды введенная
формула может быть в любое время модифицирована. Встроенный Менеджер формул
помогает пользователю найти ошибку или неправильную ссылку в большой таблице.

Кроме этого, программа Excel позволяет работать со сложными
формулами, содержащими несколько операций. Для наглядности можно включить текстовый
режим, тогда программа Excel будет выводить в ячейку не результат вычисления
формулы, а собственно формулу.

Элементы формулы

Функции. Функция ПИ() возвращает значение числа Пи: 3,142…

Ссылки (или имена). A2 возвращает значение ячейки A2.

Константы. Числа или текстовые значения, введенные
непосредственно в формулу, например 2.

Операторы. Оператор ^ возводит число в степень, а звездочка
(*) выполняет умножение.

О функциях в формулах

Функции — заранее определенные формулы, которые
выполняют вычисления по заданным величинам, называемым аргументами, и в
указанном порядке. Эти функции позволяют выполнять как простые, так и сложные
вычисления. Например, функция ОКРУГЛ округляет число в ячейке A10.

Структура функции

Структура функции начинается со знака равенства (=), за ним
следует имя функции, открывающая скобка, список аргументов, разделенных
запятыми, закрывающая скобка.

Аргументы. Существуют различные типы аргументов: число,
текст, логическое значение (ИСТИНА и ЛОЖЬ), массивы, значение ошибки (например
#Н/Д), или ссылки на ячейку. В качестве аргументов используются константы
(постоянное (не вычисляемое) значение) формулы, или функции. В каждом
конкретном случае необходимо использовать соответствующий тип аргумента.

Вложенные функции.

В некоторых случаях может потребоваться использование функции
как одного из аргументов другой функции. Например, в следующей формуле функция
СРЗНАЧ вложена в функцию ЕСЛИ для сравнения среднего значения нескольких
значений с числом 50.

О ссылках в формулах

Ссылка указывает на ячейку или диапазон ячеек листа и
передает в Microsoft Excel сведения о расположении значений или данных, которые
требуется использовать в формуле. При помощи ссылок можно использовать в одной
формуле данные, находящиеся в разных частях листа, а также использовать в
нескольких формулах значение одной ячейки. Кроме того, можно задавать ссылки на
ячейки других листов той же книги и на другие книги. Ссылки на ячейки других
книг называются связями.

Стиль ссылок A1

По умолчанию Microsoft Excel использует стиль ссылок A1,
определяющий столбцы буквами (от A до IV, всего не более 256 столбцов), а
строки номерами (от 1 до 65536). Эти буквы и номера называются заголовками
строк и столбцов. Для ссылки на ячейку введите букву столбца, а следом номер
строки.

Относительные ссылки. Относительная ссылка в
формуле, например A1, основана на относительной позиции ячейки, содержащей
формулу, и ячейку, на которую указывает ссылка. При изменении позиции ячейки,
содержащей формулу, изменяется и ссылка. При копировании формулы вдоль строк и
вдоль столбцов ссылка автоматически корректируется. По умолчанию в новых
формулах используются относительные ссылки.

Абсолютные ссылки. Абсолютная ссылка
ячейки в формуле, например $A$1, всегда ссылается на ячейку, расположенную в
определенном месте. При изменении позиции ячейки, содержащей формулу,
абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль
столбцов абсолютная ссылка не корректируется. По умолчанию в новых формулах
используются относительные ссылки, и для использования абсолютных ссылок надо
выбрать соответствующий параметр.

Смешанные ссылки. Смешанная ссылка
содержит либо абсолютный столбец и относительную строку, либо абсолютную строку
и относительный столбец. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и
т.д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т.д. При изменении
позиции ячейки, содержащей формулу, относительная ссылка изменяется, а
абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль
столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка
не корректируется.

Стиль ссылок R1C1

Также можно использовать стиль ссылок, в котором нумеруются
как строки, так и столбцы. Стиль ссылок R1C1 полезен при вычислении положения
столбцов и строк в макросах. В стиле ссылок R1C1 Microsoft Excel указывает
положение ячейки буквой «R», за которой идет номер строки, и буквой «C», за
которой идет номер столбца.

При записи макроса Microsoft Excel записывает некоторые
команды с использованием стиля ссылок R1C1. Например, если записывается такая
команда как нажатие кнопки Автосумма для вставки формулы, суммирующей
диапазон ячеек, Microsoft Excel использует при записи формулы стиль ссылок
R1C1, а не A1.

Об операторах в формулах

Операторами обозначаются операции, которые следует выполнить
над операндами формулы. В Microsoft Excel включено четыре вида операторов:
арифметические, текстовые, операторы сравнения и операторы ссылок.

Типы операторов

Арифметические операторы. Служат для выполнения
арифметических операций, таких как сложение, вычитание, умножение. Операции
выполняются над числами. Используются следующие арифметические операторы.

Арифметический оператор	Значение (пример)
+ (знак плюс)	Сложение (3+3)
— (знак минус)	Вычитание (3-1) Отрицание (-1)
* (звездочка)	Умножение (3*3)
/ (косая черта)	Деление (3/3)
% (знак процента)	Процент (20%)
^ (крышка)	Возведение в степень (3^2)

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

Оператор сравнения	Значение (пример)
= (знак равенства)	Равно (A1=B1)
> (знак больше)	Больше (A1>B1)
< (знак меньше)	Меньше (A1<B1)
>= (знак больше или равно)	Больше или равно (A1>=B1)
<= (знак меньше или равно)	Меньше или равно (A1<=B1)
<> (знак не равно)	Не равно (A1<>B1)

Оператор ссылки. Для описания ссылок на диапазоны ячеек
используются следующие операторы.

Оператор ссылки	Значение (пример)	Ставится между ссылками на первую и последнюю ячейки диапазона. Такое сочетание является ссылкой на диапазон (B5:B15)
; (точка с запятой)	Оператор объединения. Объединяет несколько ссылок в одну ссылку (СУММ (B5:B15; D5:D15))
(пробел)	Оператор пересечения множеств, служит для ссылки на общие ячейки двух диапазонов (B7:D7 C6:C8)

Порядок выполнения действий в формулах

Формулы вычисляют значения в определенном порядке. Формула в
Microsoft Excel всегда начинается со знака равенства (=). Знак равенства
свидетельствует о том, что последующие знаки составляют формулу. Элементы,
следующие за знаком равенства, являются операндами, разделяемыми операторами
вычислений. Формула вычисляется слева направо, в соответствии с определенным
порядком для каждого оператора в формуле.

Приоритет оператора

Если в одной формуле используется несколько операторов, Microsoft
Excel выполняет операции в порядке, показанном в следующей таблице. Если
формула содержит операторы с одинаковым приоритетом — например операторы
деления и умножения — они выполняются слева направо.

Оператор	Описание
: (двоеточие) (один пробел) (запятая)	Операторы ссылок.
—	Знак «минус»
%	Процент
^	Возведение в степень
* и /	Умножение и деление
+ и —	Сложение и вычитание
&	Объединение двух текстовых строк в одну
= < > <= >= <>	Сравнение

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

Формулы представляют собой выражения, по которым выполняются
вычисления на странице. Формула начинается со знака равенства (=).

Создание простой формулы: =128+345

1. Щелкните ячейку, в которую требуется ввести формулу.

2. Введите = (знак равенства).

. Введите формулу.

. Нажмите клавишу ENTER.

Создание формулы, содержащей ссылки или имена:
=A1+23

1. Щелкните ячейку, в которую требуется ввести формулу.

2. В строку формул введите = (знак равенства).

. Выполните одно из следующих действий.

o Чтобы создать ссылку,
выделите ячейку, диапазон ячеек, место в другом листе или место в другой книге.
Можно переместить границу выделения, перетащив границу ячейки, или перетащить
угол границы, чтобы расширить выделение.

o Чтобы создать ссылку на
именованный диапазон, нажмите клавишу F3, выберите имя в поле Вставка имени
и нажмите кнопку ОК.

4. Нажмите клавишу ENTER.

Создание формулы, содержащей функцию: =СРЕДНЕЕ
(A1:B4)

1. Щелкните ячейку, в которую требуется ввести формулу.

2. Для того чтобы начать формулу с функции, нажмите
кнопку Вставка функции на панели формул

. Выберите функцию, которую нужно использовать. Можно
ввести запрос с описанием операции, которую требуется выполнить, в поле Поиск
функции (например, по словам «сложение чисел» будет найдена функция СУММ),
или выбрать категорию в поле или категорию.

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

. По завершении ввода формулы нажмите клавишу ENTER.

Создание формулы с вложением функций: =ЕСЛИ
(СРЕДНЕЕ(F2:F5)>50; СУММ (G2:G5); 0)

1. Щелкните ячейку, в которую требуется ввести формулу.

2. Для того чтобы начать формулу с функции, нажмите
кнопку Поместить функцию на панели формул.

. Выберите функцию, которую нужно использовать. Можно
ввести запрос с описанием операции, которую требуется выполнить (например,
«сложение чисел» возвращение функции СУММ) в поле Поиск функции, или
выбрать категорию в поле или категорию.

. Введите аргументы.

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

o Чтобы ввести другую
функцию в качестве аргумента, введите функцию в соответствующее окно аргумента.
Например, можно добавить СУММ (G2:G5) в текстовое поле значение_если_истина.

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

Для выполнения табличных вычислений нужны формулы. Поскольку
некоторые формулы и их комбинации встречаются очень часто, то программа Excel
предлагает более 200 заранее запрограммированных формул, которые называются
функциями.

Все функции разделены по категориям, чтобы в них было проще
ориентироваться. Встроенный Конструктор функций помогает на всех этапах работы
правильно применять функции. Он позволяет построить и вычислить большинство
функций за два шага.

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

И Возвращает значение ИСТИНА, если все аргументы имеют
значение ИСТИНА.

ЛОЖЬ Возвращает логическое значение ЛОЖЬ.

ЕСЛИ Выполняет проверку условия.

НЕ Меняет на противоположное логическое значение своего
аргумента.

ИЛИ Возвращает ИСТИНА, если хотя бы один аргумент имеет
значение ИСТИНА.

ИСТИНА Возвращает логическое значение ИСТИНА.

Возвращает логическое значение ЛОЖЬ.

Синтаксис ЛОЖЬ()

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

Синтаксис НЕ (логическое_значение)

Возвращает логическое значение ИСТИНА.

Синтаксис ИСТИНА()

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

Синтаксис

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

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

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

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

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

Как мы видим, Excel — это не более изощренный текстовый
редактор с сеткой, которая принуждает пользователя заносить информацию в
небольшие отдельные ячейки, вместо того, чтобы предложить все пространство
листа. Огромная разница между ячейками рабочей таблицы и страницами текстового
редактора состоит в том, что каждая ячейка позволяет не только редактировать и
форматировать текст, но и выполнять вычисления. Эти вычисления основаны на
формулах, которые пользователь создает в различных ячейках таблицы, зачастую
пользуясь мастером функций, очень облегчающим работу.

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

Поскольку таблицы содержат динамические формулы, их итоговые
значения всегда будут актуальны. А это очень важно в современных условиях.

. Практическая часть.

Рассмотрим следующую задачу.

Агентство по грузоперевозкам «Летучий голландец»
предоставляет услуги по перевозке грузов по различным маршрутам. Данные о
маршрутах, выполненных в течение недели, по каждому водителю приведены на рис.
1. Справочные данные о технических характеристиках автомобилей и протяженность
маршрутов приведены на рис. 2.

1. Построить таблицы по приведенным данным.

2. Выполнить расчет количества израсходованного топлива
каждым водителем и веса перевезенного груза, данные расчета занести в таблицу
(рис. 1).

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

. Сформировать и заполнить ведомость расхода горючего
каждым водителем за неделю (рис. 3)

. Результаты расчета количества израсходованного
топлива за неделю представить в графическом виде.

Описание алгоритма решения задачи.

1. Запустить табличный процессор MS Excel.

2. Создать книгу с именем «Летучий голландец»

. Лист 1 переименовать в лист с названием Данные о
маршрутах.

. На рабочем листе Данные о маршрутах создать
таблицу о выполненных маршрутах (рис. 1).

. Для проверки Ф.И.О. водителей введите эти данные в
столбец К. Выделите столбец D и выберете в строке меню команды ДанныеПроверка. В окне
Проверка вводимых значений выберете вкладку Параметры. Задайте следующие
параметры: Тип данных — Список; Источник — =$K$3:$K$9.

. Выделите ячейки А2:J11 и выберете в строке
меню команды ВставкаИмяПрисвоить. В окне Присвоение имени в поле Имя введите
Данные о маршрутах и нажмите ОК.

Сведения о выполненных маршрутах

№ п/п	Ф.И.О. водителя	Марка автомобиля	№ рейса	Выполнено рейсов, шт.	Протяженность рейсов, км	Расход топлива на 100 км, л	Израсходовано топлива, л	Грузоподъемность, т	Вес перевезенного груза, т
1	Соловьев В.В.	КАМАЗ	А112	4
2	Михайлов С.С.	ЗИЛ	С431	3
3	Кузнецов Я.Я.	МАЗ	А112	5
4	Иванов К.К.	МАЗ	М023	7
5	Сидоров А.А.	ЗИЛ	В447	2
6	Волков Д.Д.	КАМАЗ	С431	8
7	Быков Л.Л.	КАМАЗ	В447
	ИТОГО	х	х	х
	В СРЕДНЕМ	х	х	х

7. Лист 2 переименовать в лист с названием Справочная
информация.

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

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

Технические характеристики автомобилей		Протяженность рейсов
№ п/п	Марка автомобиля	Расход топлива на 100 км, л	Грузоподъемность, т	№ п/п	№ рейса	Протяженность рейсов, км
1	ЗИЛ	42	7	1	А112	420
2	КАМАЗ	45	16	2	В447	310
3	МАЗ	53	12	3	М023	225
				4	С431	250

10. На рабочем листе Данные о маршрутах заполнить
таблицу «Сведения о выполненных маршрутах».

Протяженность рейсов:

в ячейку F3 ввести формулу =E3*’Справочная информация’! H3

в ячейку F4 ввести формулу =E4*’Справочная информация’! H6

в ячейку F5 ввести формулу =E5*’Справочная информация’! H3

в ячейку F6 ввести формулу =E6*’Справочная информация’! H5

в ячейку F7 ввести формулу =E7*’Справочная информация’! H4

в ячейку F8 ввести формулу =E8*’Справочная информация’! H6

в ячейку F9 ввести формулу =E9*’Справочная информация’! H4

в ячейку F10 ввести формулу =СУММ (F3:F9)

в ячейку F11 ввести формулу =СРЗНАЧ (F3:F9).

Расход топлива на 100 км

в ячейку G3 ввести формулу =’Справочная информация’! C4

в ячейку G4 ввести формулу =’Справочная информация’! C3

в ячейку G5 ввести формулу =’Справочная информация’! C5

в ячейку G6 ввести формулу =’Справочная информация’! C5

в ячейку G7 ввести формулу =’Справочная информация’! C3

в ячейку G8 ввести формулу =’Справочная информация’! C4

в ячейку G9 ввести формулу =’Справочная информация’! C4

в ячейку G10 ввести формулу =СУММ (G3:G9)

в ячейку G11 ввести формулу =СРЗНАЧ (G3:G9)

Израсходовано топлива

в ячейку H3 ввести формулу =F3/100*G3. Размножить введенную в ячейка Н3
формулу для остальных ячеек (с Н4 по Н9) данного столбца.

в ячейку H10 ввести формулу =СУММ (H3:H9)

в ячейку H11 ввести формулу =СРЗНАЧ (H3:H9)

Грузоподъемность

в ячейку I4 ввести формулу =’Справочная информация’! D3

в ячейку I5 ввести формулу =’Справочная информация’! D5

в ячейку I6 ввести формулу =’Справочная информация’! D5

в ячейку I7 ввести формулу =’Справочная информация’! D3

в ячейку I8 ввести формулу =’Справочная информация’! D4

в ячейку I9 ввести формулу =’Справочная информация’! D4

в ячейку I10 ввести формулу =СУММ (I3:I9)

в ячейку I11 ввести формулу =СРЗНАЧ (I3:I9)

Вес перевезенного груза

в ячейку J3 ввести формулу =I3*E3. Размножить введенную в ячейка J3 формулу для остальных
ячеек (с J4
по J9) данного столбца.

в ячейку J10 ввести формулу =СУММ (J3:J9)

в ячейку J11 ввести формулу =СРЗНАЧ (J3:J9).

Сведения о выполненных маршрутах

№ п/п	Ф.И.О. водителя	Марка автомобиля	№ рейса	Выполнено рейсов, шт.	Протяженность рейсов, км	Расход топлива на 100 км, л	Израсходовано топлива, л	Грузоподъемность, т	Вес перевезенного груза, т
1	Соловьев В.В.	КАМАЗ	А112	4	1680	45	756	16	64
2	Михайлов С.С.	ЗИЛ	С431	3	750	42	315	7	21
3	Кузнецов Я.Я.	МАЗ	А112	5	2100	53	1113	12	60
4	Иванов К.К.	МАЗ	М023	7	1575	53	834.75	12	84
5	Сидоров А.А.	ЗИЛ	В447	2	620	42	7	14
6	Волков Д.Д.	КАМАЗ	С431	8	2000	45	900	16	128
7	Быков Л.Л.	КАМАЗ	В447	4	1240	45	558	16	64
	ИТОГО	х	х	х	9965	325	4737.15	86	435
	В СРЕДНЕМ	х	х	х	1423.57	46.43	676.74	12.29	62.14

11. Лист 3 переименовать в лист с названием Ведомость.

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

Выполнено рейсов

в ячейку D10 ввести формулу =’Данные о маршрутах’! E3

в ячейку D11 ввести формулу =’Данные о маршрутах’! E4

в ячейку D12 ввести формулу =’Данные о маршрутах’! E5

в ячейку D13 ввести формулу =’Данные о маршрутах’! E6

в ячейку D14 ввести формулу =’Данные о маршрутах’! E7

в ячейку D15 ввести формулу =’Данные о маршрутах’! E8

в ячейку D16 ввести формулу =’Данные о маршрутах’! E8

в ячейку D17 ввести формулу =СУММ (D10:D16).

Израсходовано топлива

в ячейку E10 ввести формулу =’Данные о маршрутах’! H3

в ячейку E11 ввести формулу =’Данные о маршрутах’! H4

в ячейку E12 ввести формулу =’Данные о маршрутах’! H5

в ячейку E13 ввести формулу =’Данные о маршрутах’! H6

в ячейку E14 ввести формулу =’Данные о маршрутах’! H7

в ячейку E15 ввести формулу =’Данные о маршрутах’! H8

в ячейку E16 ввести формулу =’Данные о маршрутах’! H9

в ячейку E17 ввести формулу =СУММ (E10:E16).

Ведомость расхода горючего

Ф.И.О. водителя	№ рейса	Выполнено рейсов, шт.	Израсходовано топлива, л
Соловьев В.В.	А112	4	756
Михайлов С.С.	3	315
Кузнецов Я.Я.	А112	5	1113
Иванов К.К.	М023	7	834.75
Сидоров А.А.	В447	2	260.4
Волков Д.Д.	С431	8	900
Быков Л.Л.	В447	4	558
ИТОГО		33	4737.15

13. На листе Ведомость создать диаграмму.

14. Выбрать в меню команды ВставкаДиаграмма. В окне
Мастер диаграмм (шаг 1 из 4) нажать кнопку Далее. В окне Мастер диаграмм (шаг 2
из 4) указать Диапазон =Ведомость!$B$10:$B$16; Ведомость!$E$10:$E$16. Ряды в-столбцах.
Нажать кнопку Далее. В окне Мастер диаграмм (шаг 3 из 4) Ввести название
диаграммы, подписать оси Х и У. В окне Мастер диаграмм (шаг 4 из 4) поместить
диаграмму на имеющемся листе. Нажать кнопку Готово.

Список литературы

формула
excel функция ведомость

1.
MS Office 2007/ шаг за шагом: Практ. пособ./ Пер. с англ. — М.: Изд-во «Эком».
2007. — 820 с.

.
Левин А. Самоучитель работы на компьютере. — 6-е изд./ М.: Изд-во «Нолидж»,
2009, — 656 с.

.
Excel 2007 для «чайников»: Пер. с англ. — М.: Издательский дом «Вильямс», 2008.
— 304 с.

Источник

Практическая работа №6. Применение функции «ЕСЛИ». Excel.

Теоретическая часть.

ЕСЛИ(<условие>; <выражение1>; <выражение2>)

Условие — это логическое выражение, которое может принимать
значение ИСТИНА или ЛОЖЬ. <выражение 1> и <выражение 2> могут быть числами,
формулами или текстами (текст записывается с помощью кавычек).

Условная функция, записанная в ячейку таблицы, выполняется так: если
условие истинно, то значение данной ячейки определит <выражение 1>, в
противном случае — <выражение 2>.

Логические выражения.

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

=
Равно

>
Больше

<
Меньше

>= Больше или равно

<= Меньше или равно <> Не равно

Результатом логического выражения является логическое
значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).

Следующая формула возвращает значение 10, если значение в
ячейке А1 больше 3, а в противном случае — 20:

=ЕСЛИ(А1>3;10;20)

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

=ЕСЛИ(А1>=4;»Зачет
сдал»;»Зачет не сдал»)

Можно использовать текстовые аргументы в функции ЕСЛИ,
чтобы при невыполнении условия она возвращала пустую строку вместо 0. Например:

=ЕСЛИ(СУММ(А1:А3)=30;А10;»»)

Аргумент логическое_выражение функции ЕСЛИ может
содержать текстовое значение. Например:

=ЕСЛИ(А1=»Динамо»;10;290)

Эта формула возвращает значение 10, если ячейка А1 содержит
строку «Динамо», и 290, если в ней находится любое другое значение.
Совпадение между сравниваемыми текстовыми значениями должно быть точным, но без
учета регистра.

Функции И (AND), ИЛИ (OR), НЕ (NOT) позволяют создавать
сложные логические выражения. Эти функции работают в сочетании с простыми
операторами сравнения. Функции И и ИЛИ могут иметь до 30 логических аргументов
и имеют синтаксис:

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

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

Функция НЕ имеет только один аргумент и следующий
синтаксис:

=НЕ(логическое_значение)

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

Приведем пример. Пусть Excel возвращает текст
«Прошел», если ученик имеет средний балл более 4 (ячейка А2), и
пропуск занятий меньше 3 (ячейка А3). Формула примет вид: =ЕСЛИ(И(А2>4;А3<3);»Прошел»;»Не
прошел»)

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

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

Вложенные функции ЕСЛИ

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

=ЕСЛИ(А1=100;»Всегда»;ЕСЛИ(И(А1>=80;А1<100);»Обычно»;ЕСЛИ(И(А1>=60;А1<80);»Иногда»;»Никогда»)))
Если значение в ячейке А1 является целым числом, формула читается
следующим образом: «Если значение в ячейке А1 равно 100, возвратить строку
«Всегда». В противном случае, если значение в ячейке А1 находится
между 80 и 100, возвратить «Обычно». В противном случае, если
значение в ячейке А1 находится между 60 и 80, возвратить строку
«Иногда». И, если ни одно из этих условий не выполняется, возвратить
строку «Никогда». Всего допускается до 7 уровней вложения функций
ЕСЛИ.

Практика.

Задание 1.

Заполните таблицу по образцу и в ячейку F2 введите
следующую формулу:

=ЕСЛИ(E2<500; “ГОДЕН”; “НЕ ГОДЕН”)

Калорийность = Белки*4+Углеводы*4+Жиры*9

Задание 2.

Составьте таблицу покупок: № товара,
наименование товара, цена, количество, стоимость. 5%-ая скидка на покупку
предоставляется в том случае, если стоимость покупки превысит 5000 руб.,
10%-ая, если стоимость покупки превысят 20000 руб. Напишите список не менее чем
из 10 наименований товаров и с использованием функции «ЕСЛИ» рассчитайте
итоговую сумму.

Задание 3.

Торговый склад производит уценку
хранящейся продукции. Если продукция хранится на складе дольше 10 месяцев, то
она уценивается в 2 раза, а если срок хранения превысил 6 месяцев, но не достиг
10, то – в 1,5 раза. Получить ведомость уценки товара, которая должна включать
следующую информацию: наименование товара, срок хранения, цена до уценки, цена
после уценки.

Данная задача использует сложную
структуру в логической функции – используется двойное условие, которое
записывается таким образом в ячейку столбца D:
=ЕСЛИ(C36>10;B36/2;ЕСЛИ(C36<7;B36;B36/1,5)), где в ячейке (С-) — сколько
месяцев хранится товар, а в соответствующей ячейке (В-) – цена до уценки.

Задача №3. Ведомость уценки товара.
Наименование	Цена до уценки	Хранение	Цена после уценки
товар1	50	16	25
товар2	100	10	66,666667
товар3	100	2	100
товар4	1000	5	1000
товар5	500	6	500
товар6	123	7	82
товар7	200	10	133,33333
товар8	45	3	45
товар9	10	8	6,6666667

Задание 4.

Составить таблицу для вычисления
количества лифтов в доме в зависимости от количества этажей.

Количество этажей — случайное целое число в интервале от
1 до 30.

Количество лифтов:

•
Менее 6 этажей – лифта нет;

•
От 6 до 10 этажей – 1 лифт;

•
От 11 до 18 этажей – 2 лифта;  От 19 до 30 этажей – 4 лифта.

Задание 5.

Составьте таблицу, по образцу. В
качестве фамилий и имён внесите данные своей группы. В ячейке «Средний балл»
используйте функцию «СРЗНАЧ». В ячейке «Статус ученика» составьте формулу, которая
выдаёт следующие значения:

•
если средний балл = 5, то «Отличник»; 
если средний балл от 4 до 5, то «Хорошист»;

•
в противном случае — «Троечник».

Источник

Вегелина М. А.

Медведев И.Н.

Малащук С.А.

Соловьев Г.М.

Тарасов О.Л.

Средний балл