48451 (Работа с Microsoft Оffice. MS Word, MS Excel), страница 4

2. Вхідні дані

В роботі в якості вхідних даних використовувалася інформація з власних спостережень і з прайс-листів магазинів.

3. База даних

Відповідно до завдання для виконання роботи сформована база даних, що містить інформацію про запропоновані до роздрібного продажу загальних зошитів у магазинах, що розташовани в центральної частині міста Дніпропетровськ, район проспекту Карла Маркса, за один день 26 квітня 2001 року.

У базу включено 52 об'єкта.

Інформація про кожну одиницю товару містить такі реквізити:

• порядковий номер;

• код магазину;

• код фірми виробника;

• тип обгортки зошита;

• кількість аркушів у зошиті;

• ціна зошита у гривнах.

База сформована засобами MS Exсel у виді двомірної таблиці.

У кожному рядку таблиці розміщена інформація, що ставиться до одной одиниці товару (одного зошита).

У стовпчиках таблиці інформація розташована в такому порядку:

Стовпчик А - номер один по одному,

Стовпчик B - код магазину,

Стовпчик С - код фірми виробника,

Стовпчик D - тип обгортки зошита,

Стовпчик Е - кількість аркушів у зошиті,

Стовпчик F - ціна зошита у гривнах.

Дані Бази даних розміщені на листі книги MS Excel, який має назву "База".

Приклад відображення інформації про об'єкти в створеній базі даних поданий на рис.2.

А	В	С	D	E	F
№	Магазин	Виробник	Тип	Кількість аркушів	Ціна, грн
1	1	1	Picture	60	2,10
2	1	1	Safari	60	2,10
3	1	1	Gwiazdy Filmy	60	2,10

Рис.2. Відображення інформації про об'єкти в створеній базі даних.

Зміст бази даних подано в Додатку А.

Крім цього, на листі "Підприємці" подана інформація про магазини, де були проведені спостереження (рис. 3) і про виробників загальних зошитів (рис. 4).

№	Код Магазину	Назва підприємства	Адреса	Примітка
1	1	Карандаш	ул. Кирова, 4
2	2	Фор-Т	ул. Жуковского, 3
3	3	Пилот	пр. К.Маркса, 29/31
4	4	СП "Мир", Экспоцентр	ул. Дзержинского,	Будинок кніги
5	5	ООО "Penline"	пр. К.Маркса	Будинок наукової кніги

Рис. 3. Магазини.

№	Код виробника	Назва виробника	Страна	Примітки
1	1	Uni Pap	Польша
2	2	Top - 2000	Польша
3	3	SCP a.s	Словакія
4	4	"Понінківський" БК	Україна
5	5	"Бріск"	Україна	Харків
6	6	"Восход" МПО	Росія
7	7	Globus Cor.	USA
8	8	Z@Z	Польша

Рис. 4. Виробники.

У якості параметра порівняння в роботі використовувалися кількість аркушів загального зошита. Так зошити були поділені між чотирма групами: 48, 60, 80 і 96 аркушів.

Опрацювання даних виконане засобами MS Excel.

На листі "Розрахунки1" представлені розрахунки:

• середньої ціни зошитів за кількістю аркушів;

• мінімальна і максимальна ціна зошита за кількістю аркушів;

• середня ціна зошитів, у яких від 48 до 96 аркушів;

• Розкид цін для зошитів, у яких від 48 до 96 аркушів, (мінімальна і максимальна ціна).

Результати розрахунків листа "Розрахунки1" і вивід листа у режимі формул представлені у Розрахунковій частині.

На листі "Розрахунки2" представлені розрахунки:

• середня ціна зошитів за кількістю аркушів для кожного виробника;

• середня ціна зошитів, у яких від 48 до 96 аркушів, для кожного виробника.

Причім для тих виробників у яких не спостерігалися зошити з тою чи іншою кількістю аркушів стоїть знак "-".

Результати розрахунків листа "Розрахунки2" і вивід листа у режимі формул представлені у Розрахунковій частині.

На листі "Розрахунки3" представлені розрахунки кількості одиниць асортименту у різних продавців.

Результати розрахунків листа "Розрахунки2" і вивід листа у режимі формул представлені у Розрахунковій частині.

Крім цих основних листів MS Excel бази даних є ще один за назвою "Допоміж" де записані умови для використання функцій MS Excel.

3. Інструментальні засоби MS Excel використовувані для рішення задачі

Для виконання розрахунків завдання використані такі вмонтовані функції MS Excel:

• СУММ(число1;число2; ...)

• МАКС (ячейка1:ячейка2)

• МІН (ячейка1:ячейка2)

• СУММЕСЛИ(интервал; критерий;сумм_интервал)

• СЧЁТЕСЛИ(интервал;критерий)

• ДМИН(база_данных;поле;критерий)

• ДМАКС(база_данных;поле;критерий)

• ДСРЗНАЧ(база_данных;поле;критерий)

Функція СУМ(число1;число2; ...) підсумовує всі числа в інтервалі.

• Число1, число2, ... - це від 1 до 30 аргументів, для яких потрібно визначити суму.

• Враховуються числа, логічні значення і текстові представлення чисел, що безпосередньо введені в список аргументів.

• Якщо аргумент є масивом чи посиланням, то тільки числа враховуються в масиві чи посиланні. Порожні осередки, логічні значення, тексти і значення помилок у масиві чи посиланні ігноруються.

• Аргументи, що є значеннями помилок текста, не перетвореними в числа, викликають помилки.

Функція МІН повертає найменше значення з набору даних і має такий синтаксис:

=МІН(число1;число2;...)

• Число1, число2,...- це від 1 до 30 чисел, серед яких шукається мінімальне значення.

• Можна задавати аргументи, що є числами, порожніми осередками, логічними значеннями або текстовими уявленнями чисел. Аргументи, що є значеннями помилки або текстами, не що перетворяться в числа, викликають значення помилок.

• Якщо аргумент є масивом або посиланням, то враховуються тільки числа. Порожні осередки, логічні значення або тексти в масиві або посиланні ігноруються.

• Якщо аргументи не містять числа, то функція МІН повертає 0.

Функція МАКС повертає найбільше значення з набору даних і має такий синтаксис:

=МАКС(число1;число2;…)

Значення аргументу задаються таким же уявою як і у функції МИН.

Функція СУММЕСЛИ(итервал; критерий; сум_интервал) Підсумовує осередки, специфіковані заданим критерієм.

• Интервал - це інтервал осередків, що обчислюються.

• Критерий - це критерій у формі числа, чи вираження у віді тексту, що визначає, яка осередок додається. Наприклад, критерій може бути виражений як 32, "32", ">32", "яблука".

• Сум_интервал - це фактичні осередки для підсумовування. Осередки в сум_інтервал сумуются, тільки якщо відповідні осередки в аргументі Интервал задовольняють Критерий. Якщо сум_інтервал опущений, то сумуются осередки в аргументі інтервал.

Функція СЧЁТЕСЛИ(интервал; критерий) підраховує кількість осередків усередині інтервалу, що задовольняють заданому критерію.

Значення аргументу задаються таким же чином як і у функції СУММЕСЛИ.

Функція ДМИН(база_даних; поле; критерий) повертає найменше число в стовпці чи списку бази даних, що задовольняє заданим умовам.

• База_даних - це інтервал осередків, що формують базу даних. База даних являє собою список зв'язаних даних, у якому рядки даних є записами, а стовпці - полями. Верхній рядок списку містить назви кожного стовпців.

• Поле визначає стовпець, використовуваний функцією. Аргумент «поле» може бути заданий як текст із назвою стовпця в подвійних лапках, наприклад "Вік" чи "Врожай" , чи як число, що задає положення стовпця в списку: 1 - для першого поля, 2 - для другого поля і так далі.

Критерий - це інтервал осередків, що містить умови, що задаються.

Функція ДМАКС(база_даних; поле; критерий) підсумовує числа в стовпці чи списку бази даних, що задовольняють заданим умовам.

Значення аргументу задаються таким же чином як і у функції ДМИН.

Функція ДСРЗНАЧ(база_даних; поле; критерий) усереднює значення в стовпці чи списку бази даних, що задовольняють заданим умовам.

Значення аргументу задаються таким же уявою як і у функції ДМИН.

1. Розрахунок середньої ціни товарної одиниці та розкид цін

Розрахунки приведені у Базі даних на листу "Розрахунки1" та мають вигляд представлений на рис. 5:

Кількість записів в базі даних			51
Кількість аркушів	Середня ціна зошита	Розкід цін
		мінімальна		максімальна
48	1,73 грн.	1,32 грн.		2,00 грн.
60	2,17 грн.	1,68 грн.		2,50 грн.
80	3,06 грн.	2,28 грн.		4,20 грн.
96	3,15 грн.	2,76 грн.		3,50 грн.
Середня ціна зошітів, у яких від 48 до 96 аркушів				2,47 грн.
Розкід цін для зошітів, у яких від 48 до 96 аркушів
		мінімальна		1,32 грн.
		максімальна		4,20 грн.

Рис. 5. Вид листа "Розрахунки1"

На рис. 6 представлене вигляд листа "Розрахунки1" у режимі формул.

Кількість записів в базі даних		=СЧЁТЕСЛИ(База!B:B;"1")+СЧЁТЕСЛИ(База!B:B;"2")+СЧЁТЕСЛИ(База!B:B;"3")+СЧЁТЕСЛИ(База!B:B;"4")+СЧЁТЕСЛИ(База!B:B;"5")
Кількість аркушів	Середня ціна зошита	Розкід цін
		мінімальна		максімальна
48	=СУММЕСЛИ(База!E:E;A5;База!F:F)/СЧЁТЕСЛИ(База!E:E;A5)	=ДМИН(База!$1:$65536;База!$F$1;Допоміж!A3:A4)		=ДМАКС(База!$1:$65536;База!$F$1;Допоміж!A3:A4)
60	=СУММЕСЛИ(База!E:E;A6;База!F:F)/СЧЁТЕСЛИ(База!E:E;A6)	=ДМИН(База!$1:$65536;База!$F$1;Допоміж!A6:A7)		=ДМАКС(База!$1:$65536;База!$F$1;Допоміж!A6:A7)
80	=СУММЕСЛИ(База!E:E;A7;База!F:F)/СЧЁТЕСЛИ(База!E:E;A7)	=ДМИН(База!$1:$65536;База!$F$1;Допоміж!A9:A10)		=ДМАКС(База!$1:$65536;База!$F$1;Допоміж!A9:A10)
96	=СУММЕСЛИ(База!E:E;A8;База!F:F)/СЧЁТЕСЛИ(База!E:E;A8)	=ДМИН(База!$1:$65536;База!$F$1;Допоміж!A12:A13)		=ДМАКС(База!$1:$65536;База!$F$1;Допоміж!A12:A13)
Середня ціна зошітів, у яких від 48 до 96 аркушів					=СУММ(База!F:F)/C1
Розкід цін для зошітів, у яких від 48 до 96 аркушів
			мінімальна		=МИН(База!F:F)
			максімальна		=МАКС(База!F:F)

Рис. 6. Вид листа "Розрахунки1" у режимі формул.