 ПОЗНАВАТЕЛЬНОЕ Сила воли ведет к действию, а позитивные действия формируют позитивное отношение Как определить диапазон голоса - ваш вокал Игровые автоматы с быстрым выводом Как самому избавиться от обидчивости Противоречивые взгляды на качества, присущие мужчинам Вкуснейший "Салат из свеклы с чесноком" Натюрморт и его изобразительные возможности Применение, как принимать мумие? Мумие для волос, лица, при переломах, при кровотечении и т.д. Как научиться брать на себя ответственность Зачем нужны границы в отношениях с детьми? Световозвращающие элементы на детской одежде Как победить свой возраст? Восемь уникальных способов, которые помогут достичь долголетия Классификация ожирения по ИМТ (ВОЗ) Глава 3. Завет мужчины с женщиной
Оси и плоскости тела человека - Тело человека состоит из определенных топографических частей и участков, в которых расположены органы, мышцы, сосуды, нервы и т.д.
| Призначення кнопок форми роботи із списками 12
Теоретичні відомості Поняття списку в табличному процесорі MS EXCEL Списком називають набір рядків таблиці, що містить пов’язані між собою дані. Розрізняють списки користувача і списки – бази даних. Списки користувача створюються користувачем для подальшого автозаповнення таблиці даними. Елементами списку користувача можуть бути або дані, що вже набрані у таблиці, або дані, які вводяться користувачем безпосередньо під час створення списку. Створення списку користувача на основі існуючих даних: · виділяється діапазон комірок, який містить елементи майбутнього списку користувача; · вибирається пункт меню Сервис/Параметры..., · на вкладниці Списки натискається кнопка Импорт і Ok. Створення нового списку користувача · вибирається пункт меню Сервис/Параметры...; · на вкладинці Списки вибирається Новый список; · в зоні Элементы списка вводяться елементи списку, починаючи з першого. Після введення чергового елемента натискається клавіша Enter; · по закінченню введення всіх елементів списку натискається кнопка Добавить і Ok. Списком (базою даних) називають набір даних, що містить інформацію про певні об’єкти. У Microsoft Excel такою базою даних є таблиця, рядки в якій, починаючи з другого, називають записами, стовпчики — полями. Перший рядок бази даних містить назви полів (стовпчиків). Запис являє собою набір полів одного об’єкта, а поле – це певна категорія інформації Список (база даних) Microsoft Excel подано такими параметрами: · діапазоном бази даних або іменем діапазону та · адресами назв полів або їх порядковими номерами у базі даних. Наприклад, на рис. 1. наведений фрагмент списка про співробітників деякого підприємства. Діапазоном бази даних є комірки A2:G22 (або ім’я діапазону – База, яке було надане вказаному діапазону), а адресами назв полів є A2, В2 і так далі до адреси G2. Список містить інформацію про кожного співробітника: номер особистої справи, ПІБ, посаду, стать, рік народження і т.д. – це поля списка. Записом є окремий рядок, що описує конкретного співробітника. Так, у рядку 5 показаний запис про співробітника с особистим номером 1266, а в рядку 8 –співробітника з особистим номером 1270.
Рис. 1. Вікно Excel зі списком – відомостями про співробітників підприємства Над списками Microsoft Excel можна виконувати такі дії: · сортувати; · фільтрувати; · групувати й підводити проміжні підсумки по групах; · будувати зведені звіти й діаграми; · проводити статистичні обчислення; · шукати в списку дані, відповідні до певних критеріїв. Створення списку ( бази даних) Під час створення списку – бази даних у середовищі Microsoft Excel слід дотримуватися певних правил: · на одному робочому аркуші не можна розміщувати більше одного списку; · розмір списку не може перевищувати розміри аркуша Excel, тобто число стовпців не може бути більш 256, а число рядків – 65536; · список має бути відокремлений від інших даних робочого аркуша щонайменше одним порожнім стовпчиком і одним порожнім рядком; · список не може містити порожніх рядків (стовпчиків); · перший рядок таблиці повинний містити імена полів списку, що описують призначення відповідного стовпця; · одна комірка має містити назву одного стовпця (на можна використовувати об’єднані комірки); · заголовки полів мають бути унікальними, тобто не може бути повторів назв заголовків полів; · бажано, щоб формати заголовків полів відрізнялися від форматів записів; · не може бути порожнього рядка або навіть порожньої комірки між заголовками полів і записами; · в усіх рядках списку в однакових стовпчиках мають розташовуватися однотипні дані. Введення даних до списку( бази даних) Для ведення великих списків, що постійно поповнюються, для зручності їх заповнення, а також для організації пошуку даних за яким-небудь критерієм в Microsoft Excel використовуються форми (маски даних), у яких відображаються значення тільки одного запису. Для поповнення списку за допомогою форми (маски введення) треба клацнути на будь-якій комірці рядка заголовка й вибрати команду Данные→ Форма. Діалогове вікно, що відкрилося (рис. 2), містить поля, назви й кількість яких відповідає створеним заголовкам стовпців. У правому верхньому куті форми відображений індикатор номера поточного запису (рядка таблиці) і кількість записів у списку, без врахування рядка заголовка. Одночасно у формі може виводитися до 32 полів списку. Ширина полів відповідає самому широкому стовпцю заголовка. У кожне поле слід ввести дані та натиснути кнопку Добавить для занесення значень даного запису до списку. Далі ввести наступний запис.
Рис. 2. Діалогове вікно форми для роботи із списками Excel Для переходу між полями можна користуватися покажчиком миші, або клавішами Tab – для переходу вниз по списку і Shift+Tab – для переходу нагору. Якщо поле списку містить формулу, то у формі виводиться її результат. Змінювати це поле у формі не можна. При зміні запису, що містить формулу, результат формули не буде обчислений до натискання клавіші Enter або кнопки Закрыть. Праворуч у формі розташовані кнопки керування списком. Перелік кнопок не фіксований. Він змінюватися залежно від ситуації обробки записів. Коротка інформація про кнопки надана в таблиці 1. Таблиця 1. Призначення кнопок форми роботи із списками
Пошук записів у діалоговому вікні форми (див. рис. 3) здійснюється за допомогою кнопки Критерии. У результаті Excel очистить усі поля у формі даних і замінить номер запису словом Критерії. Критерій пошуку вводиться в одне або кілька полів, за якими потрібно знайти збіг. Наприклад, зі списку потрібно знайти співробітника, прізвище якого починається на літеру “В” і оклад менше або дорівнює 16 000 грн. (рис. 3). Перегляд результатів пошуку здійснюється за кнопкою Далее. При цьому Excel відобразить форму даних з першим знайденим записом (рис. 3). Перегляд наступного запису виконується також за кнопкою Далее. При необхідності виконати пошук записів списку, що задовольняють новому критерію, у вікні форми слід вибрати кнопку Критерии, потім – Очистить та ввести новий критерій.
Рис. 3. Пошук даних у списку за допомогою форми
Сортування записів Сортування – впорядкування інформації у списку у відповідності із значенням або типом інформації. Існує 2 типи впорядкування: · По возрастанию – числа розташовуються у порядку збільшення від найменшого до найбільшого; текст сортується за абеткою; дати – від найбільш ранньої дати до самої пізньої дати. · По убыванию - числа сортуються від найбільшого до найменшого; текст – у порядку, зворотному алфавітному; дати – від самої пізньої дати до найбільш ранньої дати. Слід розрізняти сортування, виконане за одним стовпцем, і сортування за декількома стовпцями. У першому випадку, записи впорядковуються на підставі обраного поля за зростанням (або спаданням) значень у цьому полі незалежно від місця розташування даного поля. У другому випадку, записи впорядковуються за більш складним алгоритмом, тобто спочатку рядки списку групуються за значеннями одного стовпця, а у середині кожної групи рядків, що містять однакове значення, впорядковуються за значеннями другого стовпця і так далі.
Для сортування списку за одним полем, можна скористатися піктограмами Фільтрація даних Фільтрація є одним із способів керування даними списку, який полягає у прихованні всіх записів, крім тих, які відповідають умовам, заданим користувачем. Для відбору записів, що задовольняють певним умовам, використовується фільтр. Microsoft Excel надає дві команди фільтрації: Автофильтр, призначений для простих критеріїв, і Расширенный фильтр, що використовується для більш складних критеріїв. Автофильтр Для використання Автофильтру, необхідно встановити курсор на будь-яку комірку списку та виконати Данные→Фильтр→Автофильтр. У рядку заголовків списку праворуч з’являються кнопки зістрілкою вниз Все – відображає всі елементи, що містяться в стовпці; Первые 10... – відображає перші 10 рядків з максимальними або мінімальними значеннями комірок поточного поля. Користувач також може сам задати значення перегляду записів у діалоговім вікні Наложение условия по списку (рис. 7); Условие...– відображає елементи, що задовольняють одній або двом умовам.
Рис. 6. Фрагмент вікна списку при встановленому автофільтрі із розкритим меню відбору значень поля
Рис. 7. Діалогове вікно Наложение условия по списку Критерій (Условие) використовується для формування користувацького автофильтра (рис. 8). При цьому задається критерій відбору, що складається із двох умов, зв’язаних між собою логічними операторами И або ИЛИ. Кожна з умов містить дві частини. В першій частині задаются оператори порівняння (равно, не равно, больше, больше или равно, меньше, меньше или равно) та приналежности (начинается с, не начинається с, заканчавается на, не заканчивается на, содержит, не содержит). У другий – вибирається зі списку або вводиться одне із значень поля, що є числовою, текстовою або логічною константою. При відборі записів за умовами фільтра Excel не розрізняє рядкові й прописні букви.
Рис. 8. Діалогове вікно Пользовательский автофильтр При введенні значення константи можна вказувати не весь її вміст, а тільки частину, у вигляді шаблону, застосовуючи символ “*” для кодування будь-якої кількості символів, і символ “?” – для кодування будь-якого припустимого одиночного символу, який до того ж може бути опущений. Після фільтрації списку номера відфільтрованих рядків виводяться контрастним кольором (за замовчуванням – блакитним), змінюється також колір стрілки кнопки поля, до якого був застосований фільтр, а в рядку стану з’являється повідомлення про кількість знайдених рядків (рис. 9). При необхідності фільтрації списку за декількома полями, спочатку, використовуючи команду Автофильтр, список фільтрується по одному полю, а потім одержаний список фільтрується по іншому полю і т.д.
Рис. 9. Вікно Excel після застосування автофильтра Для скасування дії автофільтра слід встановити курсор у будь-якій комірці списку та виконати Данные→Фильтр→Автофильтр. Можна також для всіх стовпців списку задати в меню критерій пошуку Все або, що простіше, виконати команду Данные→Фильтр→Отобразить все: Однак у цих випадках кнопки меню автофільтра у заголовках стовпців списку залишаться. При великій кількості умов фільтрації або при необхідності побудови складних логічних умов, у тому числі за критеріями, що обчислюються, застосування Автофильтра неможливо. У цих випадках використовується Расширенный фильтр. Розширений фільтр Розширений фільтр – набагато більш гнучкий інструмент роботи з даними, ніж автофільтр. Однак, перед тем як використовувати його, необхідно створити діапазон критеріїв, у якому описуються всі умови пошуку. Діапазон критеріїв повинен відповідати таким вимогам: · діапазон повиний включати як мінімум два рядки. У першому рядку розміщаються назви полів списку. Другий рядок (або рядки) містить критерії відбору записів, визначені користувачем. Щоб уникнути помилок назви полів списку слід копіювати, а не вводити вручну; · значення умов фільтрації, розміщених в одному рядку, об’єднуються логічним оператором И; · якщо на значення поля необхідно накласти кілька умов відбору, поєднаних логічним оператором И, то в області назви поля необхідно повторити ім’я цього поля потрібну кількість разів; · значення умов, заданих у різних рядках, пов’язані логічним оператором ИЛИ. · порожній рядок критерію означає, що умови відбору відсутні; · діапазон умов може розміщуватись в будь-якому місці робочого листа або навіть на окремому робочому листі. Однак при цьому діапазон умов повинний бути відділений початкового списку хоча б одним порожнім рядком або стовпцем; · діапазон умов може містити не всі поля списку, а тільки ті, які використовуються при створенні умов фільтрації. Після того, як створений діапазон умов, можна використовувати засоби розширеного фільтра. Для цього слід встановити курсор у будь-яку комірку списку та виконати Данные→Фильтр→Расширенный фильтр.
Рис. 10. Діалогове вікно Расширенный фильтр На екрані з’явиться діалогове вікно (рис. 10), в якому слід установити перемикач обробки на одне з можливих значень, які визначають, куди розмістити результат: Фильтровать список на месте (діє за замовчуванням) або Скопировать результат в другое место. Потім у текстовому полі Исходный диапазон слід задати адресу оброблюваної бази даних (шляхом виділення всіх її комірок, включаючи заголовки полів). Ця адреса, як правило, встановлюється за замовчуванням, і користувач може її підтвердити або змінити за своїм розсудом. У текстовому полі Диапазон условий необхідно задати відповідні посилання на комірки, що містять умови відбору записів (діапазон критеріїв). Якщо був обраний перемикач Скопировать результат в другое место, то активізується текстове поле Поместить результат в диапазон, де необхідно ввести адресу лівої верхньої комірки діапазону результату фільтрації. Після заповнення запропонованої форми, уведення параметрів фільтрації підтверджується кнопкою OK. Excel відфільтрує список відповідно до заданих умов, не відображаючи записів, які не задовольняють критеріям фільтра. При розміщенні результатів фільтрації на тому ж місці для відображення схованих записів буде потрібно скасувати дії фільтра. При використанні розширеного фільтра доцільніше розміщувати діапазони умов вище й правіше початкової бази даних, якщо вона містить не більш 10-20 стовпців і не більш 200-250 записів. В іншому випадку діапазон умов доцільніше розміщати на новому аркуші. Результат вибірки розширеного фільтра переважніше розміщувати в новому місці під діапазоном умов. Якщо вибірка розміщується на тому ж листі, що й початкова база даних, то, для полегшення порівняльного аналізу, їх краще розташовувати на одному рівні по горизонталі. Після зміни значень діапазону умов результат вибірки розширеного фільтра автоматично не змінюється. Потрібно його повторне застосування. Застосування критеріїв розширеного фільтра розглянемо на прикладі списку співробітників підприємства. Необхідно відобразити інформацію про співробітників-жінок, оклад яких знаходиться у межах від 10 000 до 20 000 грн. включно. Діапазон критеріїв та результат відбору відображений на рис. 11.
Рис. 11. Діапазон критеріїв та результат застосування розширеного фільтра В діапазоні критеріїв можуть використовуватися критерії, що обчислюються, які являють собою умови, що включають більш складні операції, ніж просте порівняння значення стовпця з константою. Застосовуючи критерій, що обчислюється, необхідно дотримуватися правил: · заголовок над критерієм, що обчислюється, не повинний збігатися із заголовком якого-небудь поля списку. Він може бути порожньою коміркою або іншим текстовим полем, відмінним від назв полів аналізованого списку; · посилання на комірки у середині списку повинні бути відносними, а посилання на комірки поза списком – абсолютними; · у формулі, що обчислює умову, слід використовувати посилання на цілий стовпець списку; · кількість умов, використовуваних для фільтрації списку, не обмежена й може застосовуватися в комбінації з будь-якими іншими умовами, які не обчислюються; · формула, що обчислює, являє собою логічну формулу, яка повертає значення истина або ложь. Застосування критеріїв, що обчислюються, розглянемо на такому прикладі: необхідно відобразити інформацію про співробітників-жінок, оклад яких нижче середнього окладу співробітників підприємства. Діапазон критеріїв та результат відбору відображений на рис. 12.
Рис. 12. Діапазон умов та результат застосування розширеного фільтра із критерієм, що обчислюється Формування підсумків Для одержання різних підсумкових відомостей по певних групах даних і в цілому за списком використовується спеціальна операція, що активізується командою Данные→Итоги. Аналогом операції є спеціальна вбудована функція ПРОМЕЖУ ТОЧНЫЕ.ИТОГИ. Перед виконанням проміжних підсумків по одному або декільком полям списку, обов’язковим є сортування значень по цих полях, в протилежному випадку ефект підведення проміжних підсумків досягнутий не буде. Технологія одержання проміжних підсумків така: 1. 2. Встановити курсор у межах списку та виконати Данные→Итоги. 3. У діалоговому вікні Промежуточные итоги (рис. 13) у списку При каждом изменении в… вибирається ім’я поля, за яким необхідно підбити підсумок. У списку Операция обирається одна з одинадцяти запропонованих для використання операцій, яка буде застосовуватися до значень полів, відзначених у списку Добавить итоги по. Перелік доступних операцій наведений у таблиці 2. При необхідності можна встановити прапорці в нижній частині діалогового вікна. Якщо встановлений прапорець Таблиця 2. Функції, доступні для підведення проміжних підсумків списку
4. Формування підсумків за заданими параметрами виконується по натисненню кнопки ОК. Після створення проміжних підсумків Excel автоматично створює структуру документа. Застосування операції підбиття підсумків розглянемо на прикладі: необхідно визначити середню заробітну плату співробітників підприємства в залежності від статі та рівня освіти. Попередньо список впорядкований за зростанням по полях Стать та Освіта. Параметри налагодження та результат підбиття підсумків відображений на рис. 12, 13. Результати підсумків можна відображати у розгорнутому та згорнутому вигляді. Для їх відкриття слід натиснути значок Маленькі кнопочки
Рис. 13. Результати підбиття підсумків Зведені таблиці Зведені таблиці є одним з потужних інструментів аналізу й обробки даних, за допомогою якого можна швидко й легко узагальнити великі обсяги даних, надаючи їх у різних розрізах. По суті, зведена таблиця являє собою звіт, який генерується на основі інших існуючих таблиць, списків або зовнішньої бази даних. При цьому вихідна інформація може перебувати як на робочому аркуші, так і в зовнішньому файлі. Даний звіт містить цифри, агреговані в різних розрізах. Звіти зведених таблиць припускають різні варіанти форматування, сортування, фільтрації, угруповання даних, одержання різних підсумків за будь-яким бажаним ступенем деталізації. Користувач може легко керувати стовпцями й рядками, переміщаючи їх з однієї області в іншу, фільтрувати значення в області даних, що дозволяє оперативно аналізувати дані в різних розрізах. Кращим способом ознайомлення зі зведеними таблицями є наочний розгляд технології їх створення, а також методів і засобів їх реорганізації. Розглянемо створення зведеної таблиці на прикладі аналізу обліку кредитних договорів. Є дані про укладені кредитні договори юридичних і фізичних осіб по декільком філіям банку (рис. 14). За допомогою зведених таблиць необхідно одержати відповіді на такі питання: · Скільки укладено договорів у кожній філії банку і яка частка кожної філії в загальному обсязі?
Рис. 14. Таблиця кредитних договорів банку з юридичними й фізичними особами Створення зведеної таблиці здійснюється командою меню Данные→Сводная таблица... Подальша робота пов’язана з послідовним вибором або виконанням покрокових дій, що пропонуються в діалогових вікнахМастера сводных таблиц и диаграмм. Крок 1. Вибір джерела даних.
Крок 2. Визначення інтервалу початкових даних. Вигляд вікна Мастера сводных таблиц и диаграмм на кроці 2 залежить від джерела даних, яке було обрано на першому кроці. Якщо джерелом даних є таблиця поточної книги, то у діалозі (рис. 16) необхідно вказати діапазон початкових даних даних, призначений для побудови зведеної таблиці. Якщо до початку побудови зведеної таблиці встановити табличний курсор у межі необхідного діапазону початкових даних, то він виділиться за замовчуванням. За необхідності даний інтервал можна змінити, скориставшись для цього кнопкою Якщо джерелом даних є таблиця з іншого файлу, то за допомогою кнопки Якщо передбачається формувати зведену таблицю із зовнішніх джерел даних (у т.ч. створених не на основі таблиць Excel, можливо розміщених на вилучених комп’ютерах мережі), то вікно кроку 2 має вигляд, представлений на рис. 17. Використовуючи кнопку
Рис. 16. Діалогове вікно кроку 2 Майстра зведених таблиць і діаграм
Рис. 17. Діалогове вікно кроку 2 Майстра зведених таблиць і діаграм у випадку роботи із зовнішніми джерелами даних
Крок 3. Формування таблиці. Тут визначається місце розташування зведеної таблиці, її ім’я, структура й зовнішній вигляд. Розташування зведеної таблиці вказується за допомогою двох перемикачів (рис. 18). Перемикач
Рис. 18. Діалогове вікно кроку 3 Майстра зведених таблиць і діаграм
Рис. 19. Вікно Параметры сводной таблицы
Таблиця 3. Значення параметрів зведеної таблиці
Структура зведеної таблиці визначається у діалоговому вікні настроювання макета зведеної таблиці (рис. 20), що відкривається кнопкою · Страница – значення цієї області виступають у якості елементів сторінки зведеної таблиці. · Строка – значення виступають у якості елементів рядка у зведеній таблиці, тобто в якості заголовків рядків таблиці. · Столбец – значення виступають у якості елементів стовпця у зведеній таблиці, тобто в якості заголовків стовпців таблиці. · Данные – у цій області відображаються детальні й підсумкові поля, які становлять уміст таблиці
Рис. 20. Вікно формування структури зведеної таблиці Формування структури зведеної таблиці здійснюється шляхом перетаскування полів бази даних на відповідні області макета. Не обов’язково використовувати всі поля. Важливо розмістити на макеті тільки ті з них, які дозволяють одержати шукані підсумкові дані (рис. 21). Невикористані поля бази даних у зведеній таблиці не відображаються. При необхідності, дані можна переміщати з однієї області в іншу. Якщо на макет вони були занесені помилково, їх можна “скинути”. При перетаскуванні полів в область даних Майстер зведених таблиць застосовує до даних різні функції. Загальний перелік функцій такий же, як і при використанні засобу Промежуточные итоги (табл. 2). За замовчуванням встановлюється функція Сумма для числових даних і функція Счет для нечислових значень.
Рис. 21. Вікно після завершення формування структури зведеної таблиці По завершенню формування макета слід натиснути кнопку
Рис. 22. Фрагмент вікна сформованої зведеної таблиці
Поля, які були розміщені в області макета Страница, Строка і Столбец (рис. 22), відображаються у вигляді списків, що розкриваються. Так, для елемента Галузь області Страница із списку можна вибрати параметр з ім’ям Всі, що забезпечить виведення відомостей по всіх галузях бази даних (у нашому прикладі: для юридичних осіб – медицина, промисловість, торгівля, для фізичних осіб – підприємець), а можна вказати тільки певні параметри, що забезпечить відображення відповідних даних. Для виведення інформації тільки про клієнтів – юридичних осіб необхідно розкрити у зведеній таблиці список поля Статус і вибрати прапорець деталізації тільки поруч зі значенням Ю (так у базі даних закодовані юридичні особи). На екрані присутній Список полей сводной таблицы, який відображається за допомогою кнопки Для формування макета зведеної таблиці не обов’язково на останньому кроці Майстра зведених таблиць відкривати діалог створення макета, вибираючи кнопку
Рис. 23. Макет зведеної таблиці на робочому листі Excel У зв’язку з тим, що зведена таблиця є особливим типом діапазону даних, усередині неї не можна вставляти рядки або стовпці, що містять вирази, елементами яких є дані цієї таблиці. Для цієї мети використовують поля, що обчислюються. Поле, що обчислюється, є виразом, у якому використані інші поля зведеної таблиці. Воно повинне перебувати тільки в області даних зведеної таблиці й не може використовуватися в області сторінки, рядка або стовпця. Наприклад, необхідно розрахувати плату банку за витрати при оформленні кредиту, що становить 0,5% від суми кредиту. Для створення поля, що обчислюється, необхідно встановити курсор у будь-яку комірку зведеної таблиці та на панелі інструментів Сводные таблицы розкрити список Сводная таблица й вибрати команди: Формулы→Вычисляемое поле... У вікні, що розкрилося (рис. 24), у полі Имя слід ввести назву поля, що обчислюється (у прикладі – Плата банку за оформлення) та у поле Формула ввести формулу розрахунків (=Сума кредиту*0,5%). Формула може містити поля зведеної таблиці, вбудовані функції й константи, зв'язані знаками операцій. Для вставки у вираз полів зведеної таблиці в списку Поля треба виділити необхідне поле та натиснути кнопку
Рис. 24. Вікно формування поля, що обчислюється, у зведеній таблиці
Консолідація даних Іншим інструментом узагальнення й аналізу даних є консолідація. Наприклад, якщо потрібно узагальнити дані (знайти суму, загальну кількість, середнє, максимум, мінімум і ін.) з однієї тої ж комірки на різних аркушах робочої книги або різних книг, з декількох однотипних таблиць на тому самому або декількох аркушах книги або декількох книг, потрібно консолідувати ці комірки, і тоді задана операція буде виконуватися автоматично. Таким чином, консолідація даних дозволяє зібрати в одній області інформацію з декількох джерел даних. Консолідувати дані можна декількома способами: · за розташуванням даних; · за категоріями; · за допомогою тривимірних посилань у формулах. Метод консолідації за розташуванням даних використовується, якщо дані займають однакове число комірок з однаковим розташуванням у декількох місцях однієї робочої книги. Метод консолідації за категоріями (або по написах) використовується, якщо потрібно узагальнити дані таблиць, розташованих на різних аркушах робочої книги, що мають однакові заголовки стовпців і рядків, але різні макети діапазонів даних. Метод тривимірних формул дозволяє об’єднати комірки за допомогою формул, використовуючи в них посилання на комірки, розташовані на різних аркушах робочої книги або в різних книгах. Розглянемо реалізацію методу консолідації на прикладі. Нехай узагальнені дані необхідно розташувати на окремому аркуші робочої книги. Для виконання консолідованого знаходження суми числових даних необхідно виділити на новому аркуші одну або кілька комірок та виконати команду Данные→Консолидация. У вікні Консолидация (рис. 25) в полі Функция вибрати функцію для узагальнення даних (у нашім випадку – Сумма). У полі Ссылка треба вказати перший діапазон комірок, що буде брати участь у консолідації. Його не слід уводити безпосередньо, необхідний діапазон треба виділити. Для включення діапазону в групу оброблюваних даних натискають кнопку Додати. Обраний діапазон відобразиться у полі Список діапазонів. Аналогічно виконують уведення інших діапазонів даних, що беруть участь в узагальненні. Залежно від обраного способу консолідації встановлюють необхідні значення групи прапорців Использовать как имена. Якщо консолідація виконується за розташуванням даних, то прапорці не встановлюються. Якщо консолідація виконується за категоріями, то встановлюються прапорці, що відповідають написам верхнього рядка й значенню першого стовпця. Для створення зв’язку між джерелом даних і узагальненими даними необхідно встановити прапорець Создать связь с исходными данными, тоді при кожній зміні початкових даних автоматично оновлюється таблиця консолідації. Закриття діалогове вікно кнопкою ОК приведе до об’єднання даних в одну або кілька спочатку виділених комірок. Консолідація буде завершена.
Рис. 25. Консолідація даних за категоріями При консолідації за категоріями, коли з великих таблиць необхідно узагальнити не всі стовпці, попередньо потрібно сформувати найменування стовпців консолідованої таблиці (шляхом копіювання їх з початкової таблиці), а потім їх виділити. Подальші дії відповідають раніше наведеній схемі. Інший варіант консолідації – за розташуванням – вимагає повної ідентичності початкових таблиць і розміщення їх на різних аркушах. Консолідація може бути виконана й на основі однієї таблиці.
12 |
Щоб виконати сортування записів списку необхідно виділити в ньому будь-яку комірку й виконати команду Данные→Сортировка. Excel автоматично визначить розмір списку, виділить зоголовок й відкриє діалогове вікно настроювання сортування (рис. 4), у якому слід указати, які поля будуть визначати новий порядок записів, а потім вказати тип впорядкування для кожного з цих полів. Excel дає можливість відсортувати дані списку за один раз не більше, ніж по трьом полям.
Щоб застосувати користувацький порядок сортування, у діалоговому вікні Сортировка диапазона слід скористатися кнопкою Параметри. Цей режим дозволяє встановити порядок сортування по первинному ключу, тобто звичайний або один з нестандартних порядків сортування, обумовлених користувачем. Наприклад, можна обрати впорядкування даних за днями тижня й по місяцях (рис. 5). Якщо встановити перемикач Учитывать регистр, можна задати облік кодування рядкових і прописних букв. Наприклад, слова, що починаються із прописної букви, будуть розташовуватися раніше слів, що починаються з рядкової букви. Крім того, можна визначити напрямок сортування по рядках або стовпцях, встановивши перемикач у положення Строки диапазона, Столбцы диапазона.
Сортировка по возрастанию або 
Сортировка по убыванию, розташованими на панелі інструментів Стандартная. Для сортування списку за більш, ніж трьома стовпцями, попередньо необхідно відсортувати записи за найменш важливими стовпцями, а потім повторити сортування по найважливіших полях у порядку збільшення їх значимості.
, натискання яких приводить до відкриття меню умов відбору для відповідного поля. Дане меню (рис. 6) містить список усіх унікальних значень, що є в даному стовпці, команди сортування таблиці, а також перелік критеріїв пошуку:
Впорядкувати список за полями, по яких будуть підбиватися підсумки.
, то автоматично будуть вилучені старі існуючі підсумкові значення й замінені новими, розрахованими по інших формулах. Якщо встановлений прапорець 
, то після кожного проміжного підсумку вставляється розрив сторінки (тобто кожна група значень даних виводиться на окремому робочому аркуші). Якщо встановлений прапорець 
, то проміжні підсумки розміщаються під даними. Кнопка 
прибирає задані параметри проміжних підсумків.
, що знаходиться ліворуч підсумкового рядка; значок 
закриє відповідні поточні відомості.
, відображені у лівому верхньому куті після підбиття проміжних підсумків (рис. 13), дозволяють виводити на екран різні рівні деталізації даних.
На даному етапі (рис. 15) необхідно вибрати, що є джерелом даних для зведеної таблиці. Це може бути список або база даних Microsoft Excel, зовнішнє джерело даних, на-приклад файли dbase, дані SQL Server, декількох об’єднаних діапазонів робочого листа або списків з декількох листів робочої книги або іншої зведеної таблиці. Крім того, встановлюється вигляд створюваного звіту: зведена таблиця (обирається за замовчуванням) або зведена діаграма. Кнопка 
дозволяє перейти на наступний крок.
.
необхідно вибрати файл, що містить потрібну таблицю. Автоматично у полі Диапазон з’явиться нове посилання на вказаний діапазон комірок.
можна вказати в якості джерела бази даних, запити, куби OLAP.
встановлюється за замовчуванням і пропонує розмістити таблицю на новому аркуші. Вибір перемикача 
, означає, що зведена таблиця буде розміщена на поточному робочому листі, при цьому у полі введення необхідно вказати ліву верхню комірку діапазону, де повинна бути розміщена зведена таблиця. У вікні, що відкривається кнопкою 
( рис. 19) можна надати ім’я зведеної таблиці, а також, за необхідності, встановити різні змінювані параметри зовнішнього вигляду таблиці.
. В правій частині вікна згруповані найменування полів бази даних. У центрі розташовується макет зведеної таблиці, а зверху наведена ілюстрована схема його заповнення. Макет зведеної таблиці складається з фіксованих областей:
, а потім 
. На робочому листі з’явиться зведена таблиця (рис. 22).
Отобразить/Спрятать список полей на панелі інструментів Сводные таблицы. Поля, включені у зведену таблицю, відображаються в цьому списку напівжирним шрифтом.
. Для додавання створеного поля у зведену таблицю натиснути кнопку 
.
