Робота з командою Форма
Команда Форма відображає на екрані форму, що являє собою вікно діалогу, призначене для перегляду та редагування записів у базі даних, а також для додавання нових і видалення існуючих записів. Крім того, за допомогою форми ви можете здійснити пошук конкретних записів на підставі складних критеріїв.
Кожному стовпцю таблиці у формі відповідає поле, найменування якого визначається введеним вами заголовком стовпця. Крім таких полів, у формі можуть бути присутніми поля, що обчислюються, за допомогою яких ви можете виконувати різноманітні обчислення. Значення цих полів автоматично заповнюються Excel на підставі інформації, що міститься в базі даних. Ви не можете безпосередньо ввести чи змінити значення в полях, що обчислюються, тому в режимі редагування ці поля у формі не відображаються. Крім того, ви можете самостійно визначити деякі з полів таблиці як редаговані. У результаті ці поля також не відображатимуться у формі в режимі редагування.
Рис. 8.28. Форма
Крім полів, форма містить ряд кнопок й індикатор номера запису. Кнопка Добавитьпризначена для введення в базу даних нового запису. В результаті натискання цієї кнопки Excel очистить усі поля форми, після чого ви можете ввести необхідні дані. Уведений запис додається в кінець бази даних.
8.1.6.3. Пошук у базі даних
Пошук у базі даних проводиться так само, як і пошук у таблиці за допомогою команди Правка/Найти.
Після введення даних вам може знадобитися впорядкувати їх. Процес упорядкування записів у базі даних називається сортуванням. Порядок сортування записів визначається конкретним завданням. Під час сортування змінюється порядок проходження записів у базі даних чи таблиці. У такий спосіб відбувається зміна бази даних. Ви повинні мати можливість від-
новити вихідний порядок проходження записів. Універсальним
засобом для цього є введення порядкових номерів записів. У поєднанні із засобами Excel з відновлення даних це цілком захистить вашу базу від утрат у разі випадкових збоїв у роботі.
Команда Данные/Сортировкавстановлює порядок рядків у таблиці відповідно до вмісту конкретних стовпців.
Сортування за зростанням передбачає такий порядок:
· числа;
· текст, включаючи текст із числами (поштові індекси, номери автомашин);
· логічні значення;
· значення помилок;
· порожні комірки.
Сортування за убуванням відбувається в зворотному порядку. Винятком є порожні комірки, що завжди розташовуються в кінці списку.
Найшвидше і найпростіше сортування за убуванням чи зростанням проводиться за допомогою відповідних кнопок 
.
У разі вибору Данные/Сортировкавідкривається вікно діалогу Сортировка диапазона, що дає можливість вам вказати поля для сортування і визначити критерій сортування. За допомогою списку, що розкривається,Сортировать по ви можете вибрати стовпець для сортування. Порядок сортування встановлюється перемикачами за зростанням чи за убуванням.
Під час сортування за зростанням текстові дані впорядковуються за абеткою від А до Я. Числові дані впорядковуються за зростанням значень від мінімального до максимального. Дати впорядковуються від найбільш ранньої дати до найбільш пізньої. У разі вибору перемикача за убуванням порядок сортування змінюється на протилежний.
Два додаткові розділи Затем по і В последнюю очередь дають змогу визначити порядок вторинного сортування для записів, у яких є значення, що збігаються.
Перемикач Идентифицировать поля по вказує, чи є в сортованому списку рядок заголовків, який потрібно виключити із сортування. За наявності в списку міток стовпців виберіть перемикач Подписям, у противному випадку — Обозначениям столбцов листа.
Рис. 8.29. Вікно сортування
Вікно діалогу містить кнопку Параметры, в результаті натискання якої відкривається вікно діалогу Параметры сортировки. За допомогою цього вікна ви можете:
· визначити користувацький порядок сортування для стовпця, зазначеного в списку, що розкривається,Сортировать по;
· зробити сортування сприйнятливим щодо використання великих і малих літер;
· змінити напрямок сортування (замість сортування згори донизу встановити сортування зліва направо).
8.1.6.5. Фільтрація даних у списку
Фільтрація списку дає можливість знаходити й відбирати для обробки частину записів у списку, таблиці, базі даних.
У відфільтрованому списку виводяться на екран лише ті рядки, що містять певне значення чи відповідають певним критеріям. При цьому інші рядки виявляються схованими.
В Excel для фільтрації даних використовуються команди Автофильтрі Расширенный фильтр. У випадку простих критеріїв для вибірки потрібної інформації досить команди Автофильтр у комірках виділених заголовків повинні з’явитися нові елементи: 
. З їх допомогою можна встановлювати критерії фільтрації даних.
Рис. 8.30. Вибір критерію
Рис. 8.31. Умови критерію
Під час використання складних критеріїв варто застосовувати команду Расширенный фильтр.
Обидві команди викликаються командою Данные/Фильтр.
8.1.6.6. Підбиття підсумків у базі даних
Один зі способів обробки й аналізу бази даних полягає в підбитті різних підсумків. За допомогою команди Данные/ Итоги можна вставити рядок підсумків у список, здійснивши підсумовування даних у потрібний спосіб. У разі вставляння рядків підсумків Excel автоматично поміщає в кінець списку даних рядок загальних підсумків.
Після виконання команди Данные/Итоги ви можете виконати такі операції:
· вибрати одну чи кілька груп для автоматичного підбиття підсумків щодо цих груп;
· вибрати функцію для підбиття підсумків;
· вибрати дані, за якими потрібно підбити підсумки.
Крім підбиття підсумків по одному стовпцю, автоматичне підбиття підсумків дає можливість:
· виводити один рядок підсумків щодо кількох стовпців;
· виводити багаторівневі, вкладені рядки підсумків щодо кількох стовпців;
· виводити багаторівневі рядки підсумків з різними способами обчислення для кожного рядка;
· ховати чи показувати детальні дані в цьому списку
Команда Итоги доповнює базу даних новими рядками, що містять спеціальну функцію.
Синтаксис: ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функції; посилання)
Номер_функції — це число від 1 до 11, що вказує, яку функцію використовувати під час обчислення підсумків усередині списку.
| Номер функції
| Функція
|
|
| СРЗНАЧ
|
|
| СЧЕТ
|
|
| СЧЁТЗ
|
|
| МАКС
|
|
| МИН
|
|
| ПРОИЗВЕД
|
|
| СТАНДОТКЛОН
|
|
| СТАНДОТКЛОНП
|
|
| СУММ
|
|
| ДИСП
|
|
| ДИСПР
|
Посилання — це інтервал чи посилання, для якого підбиваються підсумки.
Якщо список із проміжними підсумками вже створений, його можна модифікувати, редагуючи формулу з функцією ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Вставка проміжних підсумків
Для кожного товару можна ввести в таблицю проміжні підсумки — суми по полях: Кількість, Сума закупівлі, Сума реалізації, Прибуток і середнє значення поля Закупівельна ціна (грн).
Спочатку заповнюються поля, що містять вихідні дані: «Найменування», «Фірма-постачальник», «Ціна (грн)» і «Кількість». Поля, що обчислюються, таблиці заповнюються формулами. Для створення формули в полі «Ціна ($)» використовується ім’я, присвоєне комірці, що містить числове значення поточного курсу валюти. Для створення формул у полі «Сума реалізації» використовується часткова заборона модифікації адреси комірок (за допомогою значка $ перед номером рядка), що містять роздрібну ціну для кожного з товарів.
Проміжні підсумки заповнюються через меню Данные/Итоги.../Промежуточные итоги:
Рис. 8.32. Виведення проміжних підсумків
Попередньо необхідно виділити ділянку таблиці, в якій ви збираєтеся підбивати підсумки, включивши в неї рядок з назвами полів. У наведеному вище прикладі — це заголовок таблиці («№ п/п»; «Найменування»; ... «Валовий дохід») і всі записи щодо товарів (запис — це один рядок у таблиці, що починається з порядкового номера запису і закінчується цифрою валового доходу).
1. У списку При каждом изменении в: установити значення «Найменування».
2. У списку Операція встановити значення Сума.
3. У списку Добавить итоги по: відзначити елементи: «Сума реалізації», «Сума закупівлі», «Кількість», «Валовий дохід».
4. Звести прапорці: Заменить поточные итоги і Итоги под данными.
5. Такі формули мають бути записані в комірки таблиці (табл. 8.5).
Такий вигляд матиме результат обчислення формул (табл. 8.6):
Таблиця 8.5
| | A
| B
| C
| D
| E
| F
| G
| H
| I
|
|
| № п/п
| Найменування
| Фірма- постачальник
| Ціна, грн
| Ціна, $
| В
| Сума закупівлі
| Сума реалізації
| Валовий дохід
|
|
|
| Банани
| Frutis
|
| =D6/kurs
|
| =D6*F6
| =F6*I$2
| =H6-G6
|
|
|
| Банани
| SUMP
| 2.32
| =D7/kurs
|
| =D7*F7
| =F7*I$2
| =H7-G7
|
|
|
| Банани
| Forum
| 1.98
| =D8/kurs
|
| =D8*F8
| =F8*I$2
| =H8-G8
|
|
|
| Банани
| Like
| 1.86
| =D9/kurs
|
| =D9*F9
| =F9*I$2
| =H9-G9
|
 10
|
| Банани
| UFO
| 2.05
| =D10/kurs
|
| =D10*F10
| =F10*I$2
| =H10-G10
|
|
|
|
| середн.:
| =ПРОМІЖНІ.ПІДСУМКИ (1;D6:D10)
| Разом:
| =ПРОМІЖНІ.ПІДСУМКИ (9;F6:F10)
| =ПРОМІЖНІ.ПІДСУМКИ (9;G6:G10)
| =ПРОМІЖНІ. ПІДСУМКИ (9;H6: H10)
| =ПРОМІЖНІ. ПІДСУМКИ (9;I6: I10)
|
|
|
| Виноград
| Frutis
|
| =D12/kurs
|
| =D12*F12
| =F12*I$3
| =H12-G12
|
| |
|
|
|
|
|
|
|
|
|
 25
|
| Підсумок:
|
|
| Підсумок:
| =ПРОМІЖНІ.ПІДСУМКИ (9;F6:F22)
| =ПРОМІЖНІ.ПІДСУМКИ (9;G6:G22)
| =ПРОМІЖНІ. ПІДСУМКИ (9;H6:H22)
| =ПРОМІЖНІ. ПІДСУМКИ (9;I6:I22)
|
Таблиця 8.6
|
| Сьогодні курс $=
| 1.3
|
|
|
| Роздрібна ціна
|
|
|
|
|
|
|
|
| Банани
| 2.90 грн
|
|
| Розрахунок валового доходу від реалізації товарів
| Виноград
| 5.20 грн
|
|
|
|
|
|
|
|
| Ананас
| 6.80 грн
|
| № п/п
| Наймену- вання
| Фірма постачальник
| Ціна, грн
| Ціна, $
| Кількість
| Сума закупівлі
| Сума реалізації
| Валовий дохід
|
|
| Банани
| Frutis
| 2.00 грн
| $ 1.54
|
| 400.00 грн
| 580.00 грн
| 180.00 грн
|
|
| Банани
| SUMP
| 2.32 грн
| $ 1.78
|
| 185.60 грн
| 232.00 грн
| 46.40 грн
|
|
| Банани
| Forum
| 1.98 грн
| $ 1.52
|
| 326.70 грн
| 478.50 грн
| 151.80 грн
|
|
| Банани
| Like
| 1.86 грн
| $ 1.43
|
| 595.20 грн
| 928.00 грн
| 332.80 грн
|
|
| Банани
| UFO
| 2.05 грн
| $ 1.58
|
| 246.00 грн
| 348.00 грн
| 102.00 грн
|
 Банани
| Середн:
| 2.04 грн
| Підсумок:
|
| 1 753.50 грн
| 2 566.50 грн
| 813.00 грн
|
|
|
|
|
|
|
|
|
|
|
|
| Ананаси
| Like
| 5.99 грн
| $ 4.61
|
| 359.40 грн
| 408.00 грн
| 48.60 грн
|
|
| Ананаси
| UFO
| 6.60 грн
| $ 5.08
|
| 363.00 грн
| 374.00 грн
| 11.00 грн
|
| Ананаси
| Середн:
| 6.04 грн
| Підсумок:
|
| 1 861.55 грн
| 2 142.00 грн
| 280.45 грн
|
|
| Разом:
|
|
| Підсумок:
| 1 517
| 4 865.02 грн
| 6 356.90 грн
| 1 491.88 грн
|
Створення діаграми закупівельних цін
За допомогою Майстра діаграм легко побудувати
діаграму аналізу закупівельних цін. Додаткова таблиця створюється на іншому аркуші, тому вона містить посилання на перший аркуш, який у даному прикладі має ім’я «вихідна
таблиця». Такий вигляд мають формули в комірках таблиці (табл. 8.7).
Зверніть увагу: в таблиці створені поля середньої вартості, що містять однакові значення. Так ми зможемо побудувати горизонтальну пряму на графіку.
Після цього легко побудувати діаграму аналізу закупівельних цін:
Рис. 8.33. Підсумкова діаграма
Лінії середніх цін потрібно відформатувати інакше, ніж
це робить майстер. Їх потрібно зробити того ж кольору, що
й лінії закупівельних цін, забрати маркери та зробити пунк-
тирними.
Таблиця 8.7
| | B
| C
| D
| E
| F
| G
| H
|
|
|
| =‘вихідна таблиця’! B6
| =‘вихідна таблиця’! B18
| =‘вихідна таблиця’! B12
| Банани
| Ананаси
| Град^-виноград
|
|
| =‘вихідна таблиця’! C12
| =‘вихідна таблиця’! D6
| =‘вихідна таблиця’ !D12
| =‘вихідна таблиця’! D18
| =СРЗНАЧ (C$5:C$9)
| =СРЗНАЧ (D$5:D$9)
| =СРЗНАЧ (E$5:E$9)
|
|
| =‘вихідна таблиця’! C13
| =‘вихідна таблиця’! D7
| =‘вихідна таблиця’! D13
| =‘вихідна таблиця’! D19
| =СРЗНАЧ (C$5:C$9)
| =СРЗНАЧ (D$5:D$9)
| =СРЗНАЧ (E$5:E$9)
|
|
| =‘вихідна таблиця’! C14
| =‘вихідна таблиця’! D8
| =‘вихідна таблиця’! D14
| =вихідна таблиця’! D20
| =СРЗНАЧ (C$5:C$9)
| =СРЗНАЧ (D$5:D$9)
| =СРЗНАЧ (E$5:E$9)
|
|
| =‘вихідна таблиця’! C15
| =‘вихідна таблиця’! D9
| =‘вихідна таблиця’! D15
| =‘вихідна таблиця’! D21
| =СРЗНАЧ (C$5:C$9)
| =СРЗНАЧ (D$5:D$9)
| =СРЗНАЧ (E$5:E$9)
|
|
| =‘вихідна таблиця’! C16
| =‘вихідна таблиця’! D10
| =‘вихідна таблиця’! D16
| =‘вихідна таблиця’! D22
| =СРЗНАЧ (C$5:C$9)
| =СРЗНАЧ (D$5:D$9)
| =СРЗНАЧ (E$5:E$9)
|
|
| максим.
| =МАКС(C5:C9)
| =МАКС(D5:D9)
| =МАКС(E5:E9)
|
|
|
|
|
| мінім.
| =МІН(C5:C10)
| =МІН(D5:D10)
| =МІН(E5:E10)
|
|
|
|
Таблиця 8.8
| | F
| G
| H
|
|
| середня вартість
|
|
| банани
| ананаси
| виноград
|
|
| =СРЗНАЧ(C$5:C$9)
| =СРЗНАЧ(D$5:D$9)
| =СРЗНАЧ(E$5:E$9)
|
|
| =СРЗНАЧ(C$5:C$9)
| =СРЗНАЧ(D$5:D$9)
| =СРЗНАЧ(E$5:E$9)
|
|
| =СРЗНАЧ(C$5:C$9)
| =СРЗНАЧ(D$5:D$9)
| =СРЗНАЧ(E$5:E$9)
|
|
| =СРЗНАЧ(C$5:C$9)
| =СРЗНАЧ(D$5:D$9)
| =СРЗНАЧ(E$5:E$9)
|
|
| =СРЗНАЧ(C$5:C$9)
| =СРЗНАЧ(D$5:D$9)
| =СРЗНАЧ(E$5:E$9)
|
| | | | | |
Таблиця 8.9
А такий вигляд матимуть результати цих формул:
|
| Закупівельна вартість
| Середня вартість
|
|
| Банани
| Ананаси
| Виноград
| Банани
| Ананаси
| Виноград
|
| Frutis
| 2.00 грн
| 4.00 грн
| 6.25 грн
| 2.04 грн
| 4.07 грн
| 6.04 грн
|
| SUMP
| 2.32 грн
| 3.65 грн
| 5.32 грн
| 2.04 грн
| 4.07 грн
| 6.04 грн
|
| Forum
| 1.98 грн
| 4.05 грн
| 6.03 грн
| 2.04 грн
| 4.07 грн
| 6.04 грн
|
| Like
| 1.86 грн
| 4.66 грн
| 5.99 грн
| 2.04 грн
| 4.07 грн
| 6.04 грн
|
| UFO
| 2.05 грн
| 3.98 грн
| 6.60 грн
| 2.04 грн
| 4.07 грн
| 6.04 грн
|
| максим.
| 2.32 грн
| 4.66 грн
| 6.60 грн
|
|
|
|
| мінім.
| 1.86 грн
| 3.65 грн
| 5.32 грн
|
|
|
|
8.1.7. Деякі спеціальні можливості
В Excel існують деякі цікаві можливості, що дають можливість спростити роботу і трохи автоматизувати її. Нижче наведені деякі з таких можливостей.
