Задание №2

Решить общую (экономическую) задачу в MS Excel. Составить таблицу, записать составленные формулы и построить диаграмму. Тип диаграммы линейчатая. Исходные данные для построения диаграммы: данные столбца Расход. Легенда: данные столбца Наименование. К-79%. Р = 564 руб. R=30%.

Краткое описание среды MS Excel.

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

Текстовое описание заполнения таблицы.

Каждый студент самостоятельно заносит значения в ячейки под заголовками столбцов: Код (2 цифры), Наименование изделия, Продавец (Ф. И. О.), № счета (3 цифры), Цена. Остальные ячейки являются расчетными, включая итоговую строку.

Расчет ячеек:

1. ячейки столбца «Доход» рассчитывается как произведение ячеек столбца «Цена» и ячеек столбца «Количество»;

2. ячейки столбца «Расход» – это 79% от ячеек столбца «Доход»;

3. ячейки столбца «Прибыль» рассчитывается как разность ячеек столбцов «Доход» и «Расход»;

4. ячейки столбца «Реклама» принимают значение “Да”, если значение под столбцами «Количество» меньше 90 и «Прибыль» больше или равна 500 р., иначе “Нет”;

5. значения ячеек столбца «Расходы на рекламу» принимаются равными 564 р., если соседние ячейки под столбцом Реклама имеют значение “Да” иначе 30% от минимального значения, содержащегося в столбцах «Количество», «Расход» и «Прибыль» в расчетной строки;

6. ячейки столбца «Сумма» рассчитываются как сумма ячеек столбца «Прибыль» и ячеек столбца «Расходов на рекламу»;

7. Ячейки строки «Итого» рассчитываются как сумма ячеек соответствующих столбцов, за исключением ячеек столбца «Реклама», в которой должно содержаться число, выражающее количество ячеек принимающих значение “НЕТ”; в столбце «Реклама» и «Цена», в которой указывается средняя цена изделий;

8. ячейки строк «Минимальное значение» и «Максимальное значение» рассчитываются соответственно, как минимальное и максимальное значение ячеек соответствующих столбцов, за исключением столбца «Реклама», в котором ставится прочерк;

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

При составлении таблицы необходимо:

1. заполнить значениями не менее 15-20 строк;

2. в качестве «Наименования изделия» указать название приборов (приспособлений, материалов), с которыми вы будите в дальнейшем сталкиваться в своей профессии;

3. среди ячеек «Количество» должно присутствовать не менее 5 ячеек с количеством меньше 90 и не менее 5 ячеек с количеством больше или равным 90 единиц;

4. данные ячеек «Количество» и «Цена» должны быть подобраны так, чтобы среди ячеек столбца «Реклама» не менее 5 ячеек имели значение «Да» и не менее 5 ячеек имели значение «Нет»;

5. наименование таблицы выполнить полужирным подчеркнутым шрифтом методом объединения ячеек по центру;

6. шапка таблицы, строка «Итого», строка «Минимальное значение» и строка «Максимальное значение» должны быть выделены жирным обрамлением каждая в отдельности;

7. заголовки столбцов «Изделие», «Реквизиты продавца», «Расчеты», «Издержки на рекламу», «Сумма» - должны быть оформлены методом объединения ячеек с центрированием по вертикали и горизонтали;

8. ячейки под заголовками столбцов: «Цена», «Доход», «Расход на рекламу», «Прибыль», «Расход на рекламу», «Сумма» - должны иметь денежный формат с числом знаков после запятой 2 и обозначением p.;

9. ячейки под заголовками столбцов: «Код», «№ счета», «Количество» - должны иметь числовой формат, причем ячейки под заголовком столбца «Количество» должны быть отцентрированы по горизонтали;

10. остальные ячейки должны иметь текстовый формат;

11. ячейки, содержащие слова «Итого», «Минимальное значение» и «Максимальное значение» должна быть оформлены методом объединения 4 первых ячеек с выравниванием по правому краю.

После создания таблицы с расчетными значениями, необходимо привести таблицу с формулами.

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

Таблица

Товар

Реквизиты продавца

Расчет

Издержки на рекламу

Сумма

Код

Наименование товара

Единица измерений

Продавец

Номер счета

Адрес

Цена

Кол-во

Доход

Расход

Прибыль

Реклама

Расходы на рекламу

23

Гель д/волос

шт.

Золотарев В.В.

208

г. Орел ул. Полесская, 12, Офис №3

205,12р.

83

17 024,96р.

13 449,72р.

3 575,24р.

Да

564,00р.

3 011,24р.

29

Шампунь

шт.

Соседенко И.И.

259

г. Орел ул. Ленина, 21, Офис №4

149,25р.

126

18 805,50р.

14 856,35р.

3 949,16р.

Нет

1 184,75р.

2 764,41р.

35

Мыло туалетное

шт.

Мошкин А.Ю.

310

г. Орел Наугорское шоссе, 5, Офис №1

31,26р.

302

9 440,52р.

7 458,01р.

1 982,51р.

Нет

594,75р.

1 387,76р.

41

Мыло хозяйственное

шт.

Тюхов А.В.

361

г. Орел Наугорское шоссе, 5, Офис №2

23,48р.

452

10 612,96р.

8 384,24р.

2 228,72р.

Нет

668,62р.

1 560,11р.

47

Крем д/рук

шт.

Путин А.В.

412

г. Орел ул. Ленина, 21, Офис №6

78,98р.

81

6 397,38р.

5 053,93р.

1 343,45р.

Да

564,00р.

779,45р.

53

Бальзам д/рук

шт.

Медведев А.О.

463

г. Орел ул. Ватная, 2а, Офис №7

82,12р.

62

5 091,44р.

4 022,24р.

1 069,20р.

Да

564,00р.

505,20р.

59

Духи

шт.

Козлов А.В.

514

г. Орел Наугорское шоссе, 5, Офис №9

356,05р.

99

35 248,95р.

27 846,67р.

7 402,28р.

Нет

2 220,68р.

5 181,60р.

65

Туалетная вода

шт.

Тимошин А.В.

565

г. Орел ул. Полесская, 12, Офис №5

446,58р.

78

34 833,24р.

27 518,26р.

7 314,98р.

Да

564,00р.

6 750,98р.

71

Бальзам д/волос

шт.

Сурков В.В.

616

г. Орел ул. Ватная, 2а, Офис №3

348,48р.

152

52 968,96р.

41 845,48р.

11 123,48р.

Нет

3 337,04р.

7 786,44р.

77

Салфетки влажные

шт.

Волков П.О.

667

г. Орел ул. Ватная, 2а, Офис №4

78,09р.

363

28 346,67р.

22 393,87р.

5 952,80р.

Нет

1 785,84р.

4 166,96р.

83

Лак д/ногтей

шт.

Никитин А.В.

718

г. Орел Наугорское шоссе, 5, Офис №5

56,11р.

146

8 192,06р.

6 471,73р.

1 720,33р.

Нет

516,10р.

1 204,23р.

89

Крем д/век

шт.

Ершина Е.Е.

769

г. Орел ул. Ленина, 21, Офис №9

48,59р.

220

10 689,80р.

8 444,94р.

2 244,86р.

Нет

673,46р.

1 571,40р.

95

Крем д/лица

шт.

Миронова А.А.

820

г. Орел ул. Полесская, 12, Офис №7

123,26р.

151

18 612,26р.

14 703,69р.

3 908,57р.

Нет

1 172,57р.

2 736,00р.

98

Бальзам д/тела

шт.

Нелюбов Ю.А.

871

г. Орел Наугорское шоссе, 5, Офис №11

99,99р.

88

8 799,12р.

6 951,30р.

1 847,82р.

Да

564,00р.

1 283,82р.

Итого:

151,95р.

2403

265 063,82р.

209 400,42р.

55 663,40р.

9

14 973,81р.

40 689,59р.

Максимальное значение:

446,58р.

452

52 968,96р.

41 845,48р.

11 123,48р.

-

3 337,04р.

7 786,44р.

Минимальное значение:

23,48р.

62

5 091,44р.

4 022,24р.

1 069,20р.

-

516,10р.

505,20р.

Таблица в формульном виде

Товар			Реквизиты продавца			Расчет
Код	Наименование товара	Единица измерений	Продавец	Номер счета	Адрес	Цена	Кол-во	Доход	Расход	Прибыль
23	Гель д/волос	шт.	Золотарев В.В.	208	г. Орел ул. Полесская, 12, Офис №3	205,12	83	=G3*H3	=I3*0,79	=I3-J3
29	Шампунь	шт.	Соседенко И.И.	259	г. Орел ул. Ленина, 21, Офис №4	149,25	126	=G4*H4	=I4*0,79	=I4-J4
35	Мыло туалетное	шт.	Мошкин А.Ю.	310	г. Орел Наугорское шоссе, 5, Офис №1	31,26	302	=G5*H5	=I5*0,79	=I5-J5
41	Мыло хозяйственное	шт.	Тюхов А.В.	361	г. Орел Наугорское шоссе, 5, Офис №2	23,48	452	=G6*H6	=I6*0,79	=I6-J6
47	Крем д/рук	шт.	Путин А.В.	412	г. Орел ул. Ленина, 21, Офис №6	78,98	81	=G7*H7	=I7*0,79	=I7-J7
53	Бальзам д/рук	шт.	Медведев А.О.	463	г. Орел ул. Ватная, 2а, Офис №7	82,12	62	=G8*H8	=I8*0,79	=I8-J8
59	Духи	шт.	Козлов А.В.	514	г. Орел Наугорское шоссе, 5, Офис №9	356,05	99	=G9*H9	=I9*0,79	=I9-J9
65	Туалетная вода	шт.	Тимошин А.В.	565	г. Орел ул. Полесская, 12, Офис №5	446,58	78	=G10*H10	=I10*0,79	=I10-J10
71	Бальзам д/волос	шт.	Сурков В.В.	616	г. Орел ул. Ватная, 2а, Офис №3	348,48	152	=G11*H11	=I11*0,79	=I11-J11
77	Салфетки влажные	шт.	Волков П.О.	667	г. Орел ул. Ватная, 2а, Офис №4	78,09	363	=G12*H12	=I12*0,79	=I12-J12
83	Лак д/ногтей	шт.	Никитин А.В.	718	г. Орел Наугорское шоссе, 5, Офис №5	56,11	146	=G13*H13	=I13*0,79	=I13-J13
89	Крем д/век	шт.	Ершина Е.Е.	769	г. Орел ул. Ленина, 21, Офис №9	48,59	220	=G14*H14	=I14*0,79	=I14-J14
95	Крем д/лица	шт.	Миронова А.А.	820	г. Орел ул. Полесская, 12, Офис №7	123,26	151	=G15*H15	=I15*0,79	=I15-J15
98	Бальзам д/тела	шт.	Нелюбов Ю.А.	871	г. Орел Наугорское шоссе, 5, Офис №11	99,99	88	=G16*H16	=I16*0,79	=I16-J16
Итого:						=СРЗНАЧ(G3:G16)	=СУММ(H3:H16)	=СУММ(I3:I16)	=СУММ(J3:J16)	=СУММ(K3:K16)
Максимальное значение:						=МАКС(G3:G16)	=МАКС(H3:H16)	=МАКС(I3:I16)	=МАКС(J3:J16)	=МАКС(K3:K16)
Минимальное значение:						=МИН(G3:G16)	=МИН(H3:H16)	=МИН(I3:I16)	=МИН(J3:J16)	=МИН(K3:K16)

Издержки на рекламу		Сумма
Реклама	Расходы на рекламу
=ЕСЛИ((H3<90)*И(K3>=500);"Да";"Нет")	=ЕСЛИ(L3="ДА";564;МИН(I3:K3)*30/100)	=K3-M3
=ЕСЛИ((H4<90)*И(K4>=500);"Да";"Нет")	=ЕСЛИ(L4="ДА";564;МИН(I4:K4)*30/100)	=K4-M4
=ЕСЛИ((H5<90)*И(K5>=500);"Да";"Нет")	=ЕСЛИ(L5="ДА";564;МИН(I5:K5)*30/100)	=K5-M5
=ЕСЛИ((H6<90)*И(K6>=500);"Да";"Нет")	=ЕСЛИ(L6="ДА";564;МИН(I6:K6)*30/100)	=K6-M6
=ЕСЛИ((H7<90)*И(K7>=500);"Да";"Нет")	=ЕСЛИ(L7="ДА";564;МИН(I7:K7)*30/100)	=K7-M7
=ЕСЛИ((H8<90)*И(K8>=500);"Да";"Нет")	=ЕСЛИ(L8="ДА";564;МИН(I8:K8)*30/100)	=K8-M8
=ЕСЛИ((H9<90)*И(K9>=500);"Да";"Нет")	=ЕСЛИ(L9="ДА";564;МИН(I9:K9)*30/100)	=K9-M9
=ЕСЛИ((H10<90)*И(K10>=500);"Да";"Нет")	=ЕСЛИ(L10="ДА";564;МИН(I10:K10)*30/100)	=K10-M10
=ЕСЛИ((H11<90)*И(K11>=500);"Да";"Нет")	=ЕСЛИ(L11="ДА";564;МИН(I11:K11)*30/100)	=K11-M11
=ЕСЛИ((H12<90)*И(K12>=500);"Да";"Нет")	=ЕСЛИ(L12="ДА";564;МИН(I12:K12)*30/100)	=K12-M12
=ЕСЛИ((H13<90)*И(K13>=500);"Да";"Нет")	=ЕСЛИ(L13="ДА";564;МИН(I13:K13)*30/100)	=K13-M13
=ЕСЛИ((H14<90)*И(K14>=500);"Да";"Нет")	=ЕСЛИ(L14="ДА";564;МИН(I14:K14)*30/100)	=K14-M14
=ЕСЛИ((H15<90)*И(K15>=500);"Да";"Нет")	=ЕСЛИ(L15="ДА";564;МИН(I15:K15)*30/100)	=K15-M15
=ЕСЛИ((H16<90)*И(K16>=500);"Да";"Нет")	=ЕСЛИ(L16="ДА";564;МИН(I16:K16)*30/100)	=K16-M16
=СЧЁТЕСЛИ(L3:L16;"Нет")	=СУММ(M3:M16)	=СУММ(N3:N16)
-	=МАКС(M3:M16)	=МАКС(N3:N16)
-	=МИН(M3:M16)	=МИН(N3:N16)

Диаграмма.

Диаграмма строится с помощью Мастера диаграмм.

Тип диаграммы линейчатая.

Исходные данные для построения диаграммы: данные столбца Расход.

Легенда: данные столбца Наименование.

По данным диаграммы видно, что самый большой «расход» у товара «Бальзам д/волос», а самый маленький – «Бальзам д/рук».