- •3.1. Вимоги щодо оформлення БД в MS Excel
- •3.2. Перетворення БД у Smart-таблицю
- •3.3. Методи прискореного введення даних. Списки, що розгортаються
- •Приклад 1. Створити для заповнення таблиці Sales списки, що розгортаються:
- •3.4. Перевірка коректності введення даних
- •3.5. Проведення обчислень з елементами Smart-таблиці
- •3.6. Фільтрація записів
- •Візуалізована фільтрація — зрізи
- •Приклад 3. За допомогою зрізів у таблиці Sales відібрати записи:
- •Автофільтр
- •Приклад 4. За допомогою автофільтру у таблиці Sales відібрати записи:
- •3.7. Зв’язування таблиць. Функція ВПР та її аналоги
- •Результат витягується за одним полем зв’язку із правих стовпчиків — ВПР
- •Приклад 5. У полі Month Name отримати за номером місяця його назву.
- •Результат витягується за одним полем зв’язку із лівих стовпчиків — ИНДЕКС+ПОИСКПОЗ
- •Приклад 6. У полі Product отримати за кодом товару його назву.
- •Результат витягується за кількома полями зв’язку — ВПР+СЦЕПИТЬ
- •Приклад 7. У полі Discount_% отримати значення знижки з таблиці Information about Discounts за сегментом, країною та групою дисконтування
- •Результат витягується за кількома полями зв’язку — СУММЕСЛИМН
- •Приклад 8. У полі Discount_% отримати значення знижки з таблиці Information about Discounts за сегментом, країною та групою дисконтування.
- •Результат витягується із «перехресної» таблиці — ИНДЕКС+ПОИСКПОЗ
- •Приклад 9. У полі Discount_% отримати значення знижки з таблиці Information about Discounts Piv за сегментом, країною та групою дисконтування.
- •3.8. Зведені таблиці
- •3.9. Динамічна візуалізація даних
30
3. Аналіз даних в MS Excel. Smart-таблиці
3.1. ВИМОГИ ЩОДО ОФОРМЛЕННЯ БД В MS EXCEL
Частина рядків прихована
1.В діапазоні не повинно бути об’єднаних комірок — діапазон А1:G701.
2.Перший рядок таблиці (А1: G1) містить назви (імена) полів, всі інші рядки містять дані.
3.Під кожною назвою поля у стовпчику таблиці розташовані однотипні дані, які являють собою значення відповідного поля. Кожен рядок таблиці являє собою запис.
4.При занесенні БД на лист необхідно, щоб вона з усіх сторін відокремлювалася порожніми рядками й стовпчиками (виняток: БД може починатись з першого рядка та стовпчика).
5.У БД не повинно міститися порожніх рядків та стовпчиків.
6.Бажано, щоб на робочому аркуші була лише одна БД, а інша інформація розташовувалася або над таблицею, або під нею.
3.2. ПЕРЕТВОРЕННЯ БД У SMART-ТАБЛИЦЮ
1.Click у будь-якій комірці БД → C T R L + L → обов’язково встановити прапорець
Т А Б Л И Ц А С З А Г О Л О В К А М И .
В результаті:
навколо Smart-таблиці з’являється рамочка, що визначає границі БД;
діапазон спеціальним чином форматується.
2.Доцільно надати кожній Smart-таблиці змістовне ім’я: вкл. К О Н С Т Р У К Т О Р гр. С В О Й С Т В А поле И М Я Т А Б Л И Ц Ы ввести нове ім’я кл. E N T E R
3.Якщо є активною якась комірка Smart-таблиці, доступна
конт. гр. вкладок Р А Б О Т А С Т А Б Л И Ц А М И , до якої належить вкл. К О Н С Т Р У К Т О Р :
Ім’я |
|
Перетворення Smart-таблиці |
Smart-таблиці |
|
у звичайний діапазон |
Кнопка зміни розміру Smart-таблиці.
Зауваження: Після додавання нових записів та/або полів, діапазон Smart-таблиці розширюється
автоматично.
к.е.н., доц. Сільченко М.В.
31
Якщо є активною якась комірка Smart-таблиці та ніякі рядки не закріплені, під час вертикальної прокрутки аркуша зі Smart-таблицею заголовки стовпчиків змінюють свій вигляд:
заголовки стовпчиків замінюються назвами полів
заголовки інших стовпчиків не змінюються
Переходи між елементами Smart-таблиці:
2 cl на межі табл. курсору або C T R L + ( )
активізується перша комірка рядка (остання комірка рядка, перша комірка стовпчика або остання комірка стовпчика) Smart-таблиці (А4, G4, D1 або D701)
C T R L + H O M E
перехід в верхній лівий кут Smart-таблиці (А1)
C T R L + E N D
перехід в нижній правий кут Smart-таблиці (G701)
Smart-таблиця у діапазоні А1:G701, активною є комірка D4.
Виділення елементів Smart-таблиці:
C T R L + A
виділяється уся таблиця
C T R L + A → C T R L + A
виділяється весь аркуш
S H I F T +2 cl на межі табл. курсору або S H I F T +C T R L +
виділяються комірки від поточної до початку (кінця) рядка (стовпчика) Smart-таблиці
S H I F T +C T R L + H O M E
виділяються комірки від поточної до верхнього лівого кута Smart-таблиці (А1:D4)
S H I F T +C T R L + E N D
виділяються комірки від поточної до нижнього правого кута Smart-таблиці (D4:G701)
S H I F T + П Р О Б І Л
виділення поточного рядка (4)
C T R L + П Р О Б І Л
виділення поточного стовпчика (D)
ПРАКТИЧНЕ ЗАВДАННЯ
1.У файлі SmТ-1-створ, розрахунки, СУММЕСЛИ.xlsx перетворити ті таблиці, що являють собою бази даних, у Smart-таблиці. Надати такі імена (оберіть для відповідних Smart-таблиць):
Sales, Prod_Ctlg, Inf_Disc, UnCOGS_Sgm, Month_Num_Name.
Чи все вийшло зробити?
к.е.н., доц. Сільченко М.В.