Норми відбору

Код ознаки

Назва ознаки товарів

Норма відбору, %

Показники якості:

Норми знижок

№ запису

Дата

Ознака товару

Назва

Ціна

Знижки, %

Гатунок

Прихід

№ з/п

Код ознаки

Назва товару

№ заявки

Дата заявки

Організація

Маса поступило, кг

Ціна

3. Опис технології рішення задачі

1. Основні засоби MS Excel для рішення задачі:

• для обробки даних: функції категорій: “Логічні: ЕСЛИ (), ЕСЛИ(И()); “Математичні”: СУММ(), СУММЕСЛИ(), СЧЕТЕСЛИ(); “Статистичні”: СЧЕТЗ(), СРЗНАЧ(), СРЗНАЧА(); “Посилань і масивів”: ВПР(), ГИПЕРССЫЛКА() та звичайні зв’язки; Текстовий оператор конкатенации.   Амперсанд (&)

• для організації роботи користувача: майстер для перевірки даних які вводяться (Данные / Проверка…); текстові функції для автоматичного заповнення діапазонів клітинок “Для перевірки властивостей та значень”: ЕТЕКСТ(); умовне форматування даних (Формат / Условное фороматирование),; автофільтр (Данные / Фильтр Автофильтр), вибір із списку.

2. Послідовність вирішення задачі

2.1. Задача вирішується у два етапи

1 –й етап – формування шаблону робочої книги.

2 - й етап – обробка даних

2.2. Послідовність роботи на 1 – му етапі

1) Створити шаблон робочої книги у складі аркушів:

1. Початок - для організації автоматичних переходів -;

2. НДБД - для формування та збереження нормативно-довідкових даних;

3. БОД - для формування збереження оперативних даних;

4. Прихід - для формування та збереження вхідної форми “Реєстр товарів, що поступили на перевірку”;

5. Переоцінка - для формування та збереження вихідної форми “Аналіз результатів первірки якості товарів”.

2). Створити структуру таблиць за взірцями 1- 4:

НДБД - взірець 1

КодОзнаки

НазваОзнаки Товарів

НормаВідбору, %

ПоказникиЯкості:

БОД взірець 2

КодЗапису

Дата

ОзнакаТовару

Назва

Ціна

Знижки, %

Гатунок

Прихід - взірець 3

№ з/п

КодОзнаки

Назва товару

№ заявки

ДатаЗаявки

Організація

Маса поступило, кг

Ціна

Переоцінка – взірець 4

№ з/п

ДатаЗаявки

№ заявки

КодОзнаки

Назва товару

Організація

Маса поступило, кг

Ціна

Вартість, грн

Гатунок

Нова ціна, грн

Нова вартість, грн

% знижки

Втрати від знижки, грн

3) Заповнити структуру таблиць даними, формулами та ефектами оформлення

НДБД

1. У полі НазваОзнаки товарів ввести їх ознаки.

2. У полі ПоказникиЯкості для кожної групи товарів, виділених за ознакою передбачити їх перелік

Прихід

Формули:

- у полі “КодОзнаки” (для автоматичного формування значення № з/п) при введені даних:

- рядок 1 - =ЕСЛИ(ЕТЕКСТ(B2);0+1;"")

- рядок 2 та наступні - =ЕСЛИ(ЕТЕКСТ(B2);0+1;"")

Ефекти:

- у полях № з/п та КодОзнаки (для перевірки даних, які вводяться) за взірцями 5 - 6

Взірець 5	Взірець 6

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

БОД

Формули:

– у діапазонах клітинок полів Код запису, Дата, Ознака товару, Назва, Ціна (для автоматичного формування записів у базі даних із вхідної форми Прихід) за взірцем 7

Взірець 7

A B C 1 Код запису Назва Ціна 2 =ПрихідП1!A3 =ПрихідП1!B3 =ПрихідП1!C3 3 =ПрихідП1!A4 =ПрихідП1!B4 =ПрихідП1!C4 ... ... ... ... 8 =ПрихідП1!A9 =ПрихідП1!B9 =ПрихідП1!C9

- у полі Знижка, % (для автоматичного розрахунку відсотку знижки в залежності від ціни):

=ЕСЛИ(E2<=15;0,05;ЕСЛИ(E2>=20;0,25;0,15))

Ефекти:

- у полі Знижка, % (для відображення різним кольором та накресленням відсотку знижки в залежності від ціни).

На цьому ж аркуші під головною таблицею бази даних створити форму "Область для оцінки якості товарів” (для автоматичної обробки результатів перевірки якості товарів і визначення гатунку) за взірцем 8

Взірець 8
Область для оцінки якості товарів
Ознака товару		СТ	Норма відбору, %			?
Показники якості				Висновки
№ з/п	Назва		Бали
1	Смак і запах			?
2	Консистенція
3	Вигляд
4	Посол
5	Колір
6	Маркування
	Всього балів			?
	Гатунок			?	Заповнити гатунок

Формули:

1. У клітинці ? (Норми відбору) (для відображення розміру відсотку):

=ЕСЛИ($B$19=НДБД!$A$2;НДБД!C2;ЕСЛИ($B$19=НДБД!$A$9;НДБД!C9;ЕСЛИ($B$19=НДБД!$A$16;НДБД!C16;ЕСЛИ($B$19=НДБД!$A$24;НДБД!C24;""))))

2. У полях Показники якості для автоматичного формування необхідного списку показників із НДБД:

• для - № з/п

=ЕСЛИ($B$19=НДБД!$A$2;НДБД!D2;ЕСЛИ($B$19=НДБД!$A$9;НДБД!D9;ЕСЛИ($B$19=НДБД!$A$16;НДБД!D16;ЕСЛИ($B$19=НДБД!$A$24;НДБД!D24;""))))

• для – Назва

=ЕСЛИ($B$19=НДБД!$A$2;НДБД!E2;ЕСЛИ($B$19=НДБД!$A$9;НДБД!E9;ЕСЛИ($B$19=НДБД!$A$16;НДБД!E16;ЕСЛИ($B$19=НДБД!$A$24;НДБД!E24;""))))

3. У полі Висновки:

- для перевірки вірності оцінки по показнику Смак і запах:

=ЕСЛИ(C22<=37;"Від 37до 40 для гатунку 1-й 41 і вище для гатунку 2 - й")

• для перевірки значення показника Всього балів:

=ЕСЛИ(C30>100;"Знижуйте бали по показникам:2-6";C30)

• значення показника Ґатунок:

=ЕСЛИ(И($C$30<=88;$C$30<=100);2;1)

Ефекти:

• у клітинці установити перевірку вірності введення даних за взірцем 9

Взірець 9

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

Переоцінка

3) Для автоматичного впровадження записів до основної вихідної таблиці із вхідної форми та бази даних БОД використати технологію зв’язування таблиць за прикладом формування таблиці БОД.

4) У полях: Нова вартість, грн., % знижки, Втрати від знижки, грн. Ввести необхідні математичні формули для обчислення показників.

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

Аналіз втрат від переоцінки товарів
1	Всього вартість, грн
2	Всього вартість після переоцінки, грн
3	Абсолютні втрати від переоцінки, грн
4	Відносні втрати, %
5	Середня ціна до переоцінки, грн
6	Середня ціна після переоцінки, грн
7	Абсолютна знижка середньої ціни за рахунок переоцінки, грн
8	Відносна знижка середньої ціни, %
9	Найбільша вхідна ціна (товару - назва)
10	Найбільша нова ціна (товару - назва)
11	Середня знижка ціни, %
12	Кількість товарів з гатунком 2
13	Доля товарів 2-го гатунку у партії,%

Ефекти:

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

2.3. Послідовність роботи на 2 – му етапі

1) Заповнити вхідну форму Прихід оперативними даними.

2) Перейти до аркушу БОД.

3) До клітинки для розміщення ознаки товару ввести необхідне значення.

4) Перейти до головної таблиці і створити авто фільтр по полю Ознака товару (при бажанні – можливо й для поля Дата).

5) Перейти до форми «Область для оцінки якості товарів”.

6) До діапазону клітинок поля Бали ввести значення показників якості для вибраного товару.

7) Перейти до головної таблиці і ввести значення визначеного ґатунку для вибраного товару.

Повторити дії 3 – 7 для товарів вибраних для перевірки.

8) Перейти до аркушу Переоцінка і провести аналіз обчислених показників.

9) Зробити висновки про переоцінку товарів.