47354

Фільтрація та консолідація даних в середовищі MS Excel

Лекция

Информатика, кибернетика и программирование

Пошук та фільтрація – це операції, які найчастіше виконуються над базами даних (списками). Для цього програмою MS Excel надається набір засобів.

Украинкский

2014-03-30

319.52 KB

8 чел.

ЛЕКЦІЯ 7

Тема: Фільтрація та консолідація даних в середовищі MS Excel

Мета: Ознайомити з засобами відбору даних в середовищі MS Excel, поняттям консолідації даних у списках, методами зв’язування комірок і діапазонів та консолідації

План

  1.  Засоби відбору даних за визначеним критерієм.
  2.  Способи зв’язування комірок та діапазонів.
  3.  Консолідація даних.
  4.  Засоби відбору даних за визначеним критерієм.

Пошук та фільтрація – це операції, які найчастіше виконуються над базами даних (списками). Для цього програмою MS Excel надається набір засобів.

До засобів MS Excel для пошуку та фільтрації можна віднести:

  1.  форму даних для пошуку та зміни записів списку;
  2.  автофільтр для вибірки даних на основі простих критеріїв;
  3.  розширений фільтр для вибірки даних на основі складних критеріїв.

Фільтрування даних – це швидкий і зручний спосіб пошуку даних у діапазоні клітинок або в таблиці та роботи з ними. Наприклад, можна виконати фільтрування, щоб переглянути лише вказані значення, найбільші чи найменші значення або швидко відобразити повторювані значення.

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

Також можна виконувати фільтрування за декількома стовпцями. Фільтри можуть бути складені, тобто кожний доданий фільтр базується на поточному фільтрі та ще більше звужує підмножину даних, що відображається.

Критерій – це умова, яка задається за допомогою даних, які або знаходяться в таблиці, або вводяться користувачем, і деяких операцій, у відповідності з якими відбувається фільтрація або пошук потрібних даних.

Excel підтримує декілька типів критеріїв. Зокрема, критерії на основі порівняння, критерії у вигляді зразка-шаблону, критерій на основі множини умов.

Критерій, що ґрунтується на операціях порівняння – це один із найрозповсюдженіших способів визначення умов вибірки або пошуку. Такі критерії дозволяють знаходити точні відповідності й орієнтований переважно на числові дані.

У табл. 7.1. подані оператори, які використовуються в критеріях на основі порівняння та результати їх застосування.

Таблиця 7.1.

Оператори, які використовуються в критеріях на основі порівняння

Оператор

Дія

= (дані)

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

=

Вибираються записи з порожніми полями

<> (дані)

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

<>

Вибираються записи з непорожнім полем

< (дані)

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

> (дані)

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

<= (дані)

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

>= (дані)

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

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

Критерії у вигляді зразка-шаблону дозволяють здійснити вибірку текстових значень, які підходять під шаблон, і застосовується тільки до тексту або чисел відформатованих як текст. У шаблоні поряд зі звичайними символами, можуть бути використані спеціальні символи шаблону. Зокрема:

«?» – замінює символ, в позиції якого порівняння при пошуку або фільтрації не здійснюється;

«*» – дозволяє задати необмежену послідовність символів, порівняння за якими не здійснюється, починаючи з позиції, в якій знаходиться вказаний символ;

«~», за якою слідує «?» або «*» – відміняє спеціальну дію символів-шаблонів «?» та «*».

Критерії на основі множини умов дозволяє об’єднати декілька умов за допомогою логічних операцій.

Серед операцій, які використовуються в критеріях на основі множини умов такі:

І(AND) – відбираються дані, які задовольняють всім умовам, що включені у даний критерій;

АБО(OR) – відбираються дані, які задовольняють хоча б одну умову, що включена у даний критерій.

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

Використовуючи автофільтр, можна створити три типи фільтрів: за списком значень, за форматом або за умовами. Ці типи фільтрів – взаємовиключні для кожного діапазону клітинок або стовпця таблиці. Наприклад, можна фільтрувати за кольором клітинки або за списком чисел, але не за обома параметрами відразу, чи за піктограмою або за користувацьким фільтром, але не за тим та іншим водночас.

Для отримання найкращих результатів при створенні таблиці або списку не слід поєднувати в одному стовпці різні формати зберігання даних, наприклад текстовий і числовий або числовий і формат дати, оскільки для кожного стовпця доступний лише один тип команди фільтрування. Якщо у стовпці змішано декілька форматів зберігання, команда, яка відображається, матиме формат, що зустрічається найчастіше.

Для того, щоб застосувати Автофільтр необхідно виділити список або активізувати одну довільну його комірку та виконати команду Фільтр колекції Сортування й фільтр групи Редагування вкладки Основне або натиснути кнопку Фільтр групи Сортування й фільтр вкладки Дані.

При використанні автофільтру рядок заголовків списку перетворюється у групу списків, що розкриваються, які дозволяють задати умови фільтрації для кожного з полів (рис. 7.1.).

Рис. 7.1. Розкритий список автофільтру для текстових даних

За допомогою команд списку фільтру заголовків полів списку можна:

  1.  впорядкувати інформацію – команди групи сортування;
  2.  використати поле Пошук для введення тексту або чисел для пошуку;
  3.  визначити або зняти прапорці для відображення значень, знайдених у стовпці даних;
  4.  використати додаткові умови для пошуку значень, які відповідають певним умовам.

Вибір значень зі списку та пошук – це найшвидші способи фільтрування. Список значень певного стовпця формується автоматично і відображається, якщо розкрити список заголовку цього стовпця. Список значень може містити до 10000 текстових елементів. У списку текстових значень можна встановити або зняти прапорці для одного або кількох текстових значень, за якими потрібно виконати фільтрування. Якщо серед комірок діапазону або стовпця таблиці є порожні комірки, то серед елементів списку відображатиметься додатковий елемент Пусті, за допомогою якого можна знайти порожні комірки, або навпаки відобразити заповнені, вимкнувши прапорець цього пункту.

Для відображення відфільтрованих даних потрібно натиснути кнопку ОК у кінці розкритого списку.

У поле Пошук можна вводити текстові дані, які використовуються як шаблони пошуку. При цьому можна використовувати символи підстановки – «?» та «*».

Якщо ж списку команд вибрати пункт Текстові фільтри та вибрати одну із запропонованих команд або команду Користувацький фільтр…, відкриється діалогове вікно Користувацький автофільтр (рис. 7.2.).

Рис. 7.2. Діалогове вікно Користувацький автофільтр

При виконанні команд порівняння у діалоговому вікні перше поле операції визначається автоматично, у полі праворуч необхідно ввести текст або вибрати текстове значення зі списку. Наприклад, щоб виконати фільтрування за текстом, який містить слово «таблиця» у будь-якому місці, введіть дзвін. При цьому можна використовувати символи-шаблони.

У діалоговому вікні Користувацький автофільтр можна додати ще одну умову для фільтрування. Для того, щоб відфільтрувати стовпець таблиці так, щоб обидві умови мали бути істинні, потрібно встановити перемикач І. Для того, щоб відфільтрувати стовпець таблиці або виділений фрагмент так, щоб одна або обидві умови були істинні, потрібно встановити перемикач АБО. У другому рядку діалогового вікна потрібно вибрати оператор та текст порівняння.

Після фільтрування даних у діапазоні клітинок чи в таблиці можна або повторно застосувати фільтр, щоб отримати оновлені результати, або очистити фільтр, щоб знову відобразити всі дані. Для цього потрібно натиснути кнопку Застосувати повторно групі Сортування й фільтр вкладці Дані.

Для того, щоб визначити чи застосовано фільтр, потрібно переглянути піктограму у заголовку стовпця. Стрілка списку, що розкривається, означає, що фільтрування увімкнено, але не застосовано. Кнопка «Фільтр» означає, що фільтр застосовано.

При фільтруванні даних числового типу та типу Дата й час крім списку значень та поля Пошук, використовуються команди Фільтри чисел та Фільтри дат відповідно. Крім стандартних команд порівняння список команди Фільтри чисел містить спеціальні команди фільтрування за найбільшим або найменшим числами, а також за числами, більшими або меншими за середнє значення.

Для того, щоб здійснити фільтрування за найбільшими або найменшими значеннями серед команд пункту Фільтри чисел потрібно вибрати команду Перші 10…. У діалоговому вікні Автофільтр для добору найкращої десятки (рис. 7.3.) потрібно:

  1.  у полі ліворуч вибрати пункт найбільших або найменших;
  2.  у полі посередині ввести число, що визначатиме необхідну кількість шуканих чисел;
  3.  у полі праворуч вибрати варіант елементів списку для того, щоб виконати фільтрування за числом або варіант % від кількості елементів для того, щоб виконати фільтрування за відсотками.

Рис. 7.3. Діалогове вікно Автофільтр для добору найкращої десятки

Для того, щоб відфільтрувати за числами, більшими за середнє значення, необхідно вибрати команду Більше середнього пункту Фільтри чисел. Для того, щоб відфільтрувати за числами, меншими за середнє значення, необхідно вибрати команду Менше середнього пункт Фільтри чисел.

Якщо діапазон комірок відформатовано вручну або умовно за кольором клітинок або шрифту, можна фільтрувати за цими кольорами. Також можна фільтрувати за набором піктограм, створених за допомогою умовного форматування. Для цього достатньо у списку Автофільтру потрібно вибрати пункт Фільтрування за кольором, а потім, залежно від типу форматування, вибрати один із варіантів Фільтрування за кольором клітинки, Фільтрування за кольором шрифту або Фільтрування за піктограмою клітинки. А потім вибрати колір комірки, колір шрифту або піктограму, за якими здійснюється відбір даних.

Після аналізу отриманих в результаті фільтрації даних можна відмінити фільтри, тобто їх очистити.

Для того, щоб очистити фільтр для одного стовпця списку, у заголовку стовпця потрібно натиснути кнопку Фільтр  та вибрати пункт Видалити фільтр із <ім’я стовпця>. Для того, щоб виділити всіх фільтрів в аркуші, потрібно натиснути кнопку Очистити групи Сортування й фільтр вкладки Дані. Для того, щоб відмінити роботу з автофільтром достатньо ще один натиснути кнопку Фільтр групи Сортування й фільтр вкладки Дані, яка працює як вмикач/вимикач цієї дії.

У випадку, коли необхідно виконати дійсно складний пошук/фільтрацію, потрібно скористатися іншим засобом MS Excelрозширеним фільтром.

Основною особливістю, яка відрізняє розширений фільтр від засобів, які розглядалися раніше, – це необхідність допоміжного списку, який містить критерії відбору. Це може дещо уповільнити процес фільтрації, але дозволяє задавати складні критерії.

Для того, щоб застосувати розширений фільтр потрібно визначити діапазон даних списку, виділивши весь діапазон або одну комірку в межах списку. Потім натиснути кнопку Додатково виконати команду групи Сортування й фільтр вкладки Дані, у результаті чого відкривається діалогове вікно Розширений фільтр, у якому потрібно визначити параметри фільтрації.

Рис. 7.4. Діалогове вікно Розширений фільтр

Крім того, у діалоговому вікні Розширений фільтр за допомогою перемикачів групи Обробка вказати місце розміщення результату відбору: фільтрувати список на місці – фільтрація з приховуванням рядків або скопіювати результат до іншого розташування для перенесення результатів фільтрування до іншого, визначеного у полі Діапазон для результату, місця. При цьому У полі Вихідний діапазон потрібно вказати місцезнаходження списку, що фільтрується, а в полі Діапазон умов вказати діапазон критеріїв, що створений заздалегідь.

Після визначення всіх перелічений параметрів потрібно натиснути кнопку ОК для здійснення операції фільтрування.

За допомогою діалогового вікна Розширений фільтр можна знайти унікальні значення зі стовпця даних і вставити їх у нове місце. Для цього у вказаному діалоговому вікні потрібно визначити прапорець Лише унікальні записи.

При роботі з розширеним фільтром умови, що записуються в одному рядку для різних стовпців об’єднуються в один критерій оператором І (AND), умови, що записуються в різних рядках незалежно від стовпців об’єднуються оператором АБО(OR).

З відфільтрованими даними можна здійснювати більшість дій, які доступні для звичайних даних. Їх можна копіювати, сортувати, обчислювати за ними підсумки і будувати на їх основі діаграми.

  1.  Способи зв’язування комірок та діапазонів.

Часто в роботі з декількома робочими аркуша чи робочими книгами виникає необхідність зв’язування їх між собою. При цьому існують різні способи зв’язування робочих книг, листів та комірок між собою.

Найпростіший вид зв’язку – це зв’язок між значеннями двох різних комірок. Зв’язані таким чином комірки завжди мають однакове значення, за умови, що включений режим автоматичного перерахунку.

При зміні значення однієї зі зв’язаних комірок (джерела) значення іншої (приймача) також змінюється. Ця можливість використовується коли одні і ті ж дані присутні на декількох робочих листах. Якщо використовувати зв’язування комірок, то достатньо ввести дані на одному із робочих аркушів, а на останніх воно відображається автоматично.

Інший випадок застосування зв’язування – це об’єднання даних з декількох джерел на одному робочому листі.

Аркуші діаграм не містять комірок, тому не можуть бути зв’язаними з іншими робочими аркушами. Тим не менше, іноді потрібно пояснити діаграму додатковими цифрами, які містять комірки робочої книги. Для цього потрібно використовувати зв’язане зображення комірок.

Аркуші і робочі книги MS Excel можуть бути зв’язані і більш складним способом, ніж просто використання одного і того ж значення в декількох комірках. Існує можливість узагальнення даних – консолідація, з різних джерел на одному робочому аркуші.

На фізичному рівні зв’язок між комірками здійснюється за допомогою звичайних посилань. Для того, щоб дві комірки мали однакове значення, в комірку-приймач заноситься формула, яка є посиланням на комірку-джерело, наприклад =С21, якщо комірка знаходиться на одному аркуші, або =Аркуш1!С21, якщо на різних. При кожному перерахунку листа в комірку-приймач заноситься поточне значення комірки-джерела.

Дві комірки можуть бути зв’язані так, що значення комірки-приймача обчислюється шляхом певних математичних дій над значенням комірки-джерела. Робота зі зв’язаними комірками значно спрощується, якщо використовувати іменовані діапазони і комірки.

При використанні різних способів зв’язування комірок відбувається одна і таж дія – в комірку-приймач заноситься посилання на комірку-джерело, але ці способи мають різний інтерфейс і зручні у використанні в різних ситуаціях. Зв’язування комірок можна здійснити за допомогою команд, шляхом перетягування або введенням формули в комірку.

Для зв’язування комірок за допомогою команд виділена комірка копіюється за допомогою комбінації клавіш Ctrl+V або кнопки Копіювати групи Буфер обміну вкладки Основне, а потім в потрібне місце вставляється посилання на неї за допомогою кнопки Вставити зв’язок категорії Інші параметри вставлення або команди Спеціальне вставлення… колекції Вставити групи Буфер обміну вкладки Основне. Як правило це, такий спосіб не найшвидший, але він дозволяє вставити посилання у будь-яке місце робочого аркуша будь-якої книги, що відкрита. Аісля виконання команди Спеціальне вставлення… відкривається діалогове вікно Спеціальне вставлення (рис. 7.5.). Для зв’язування комірки-джерела і комірки-приймача використовується кнопка Вставити зв’язок цього діалогового вікна.

Рис. 7.5. Діалогове вікно Розширений фільтр

Аналогічні дії можна виконати використовуючи відповідні команди контекстного меню комірок джерела та приймача даних.

Два діапазони можна зв’язати, перетягуючи один із них при натиснутій правій кнопці миші. При цьому на екрані відображається контекстне меню, в якому є відповідні команди для перенесення, копіювання виділеного діапазону, а також для встановлення зв’язку між діапазонами.

Якщо діапазон-джерело та діапазон-приймач знаходяться в різних робочих книгах, перед встановленням зв’язку необхідно розмістити книги так, щоб відображалися обидва діапазону. Для цього потрібно книги відкрити та використовуючи діалогове вікно Розташування вікон (рис. 7.6.), яке відкривається після натиснення кнопки Упорядкувати всі групи Вікно вкладки Вигляд.

Рис. 7.6. Діалогове вікно Розташування вікон

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

Після розміщення вікон книг потрібно перетягнути початковий діапазон утримуючи праву кнопку миші, при цьому вказівник миші потрібно помістити на границю виділеного діапазону. Вибираючи команду Зв’язати з контекстного меню, яке відображається після того, як права кнопка миші була відпущена після переміщення вказівника миші, можна створити зв’язок між діапазонами.

Створити зв’язок між діапазонами різних робочих аркушів однієї робочої книги шляхом перетягування можна також через ярлик цього аркуша, при цьому потрібно утримувати клавішу Alt.

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

У загальному випадку посилання містить повну назву книги, аркуша і номер комірки, наприклад: 'Е:\Практика\[приклад.xls]Лист1'!С56.

В апострофах вказується шлях до аркуша, на якому знаходиться потрібна комірка, що складається з власне шляху до файлу, назви робочої книги і назви аркуша. Назва робочої книги вказується в квадратних дужках. Ім’я (адреса) комірки вказується поза апострофами після знаку оклику.

Посилання, яке містить всю інформацію про місцезнаходження робочої книги на диску та про місце комірки на в цій книзі називається зовнішнім.

Якщо книга-джерело закрита, то в тексті посилання завжди присутній шлях до неї. Якщо книга-джерело розміщена в тій же папці, що й книга-приймач, то в текст посилання можна ввести тільки ім’я файлу, шлях додається автоматично. Якщо книга-джерело відкрита, всі посилання містять тільки ім’я книги без шляху до неї. Шлях до книги додається до тексту посилання при закритті книги.

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

У процесі роботи з робочою книгою, яка містить як внутрішні, так і зовнішні зв’язки, виникає низка задач, які зв’язані з управлінням цими зв’язками. При переміщені або перейменуванні однієї із початкових книг посилання у книзі-приймачі стануть некоректними, і вони повинні бути змінені відповідним чином.

Найпростіший спосіб змінити зв’язок між комірками – відредагувати посилання в рядку формул. Для цього достатньо активізувати відповідну комірку та використовуючи, наприклад, рядок формул відредагувати потрібне посилання. Якщо необхідно виправити зв’язки зразу багатьох комірок, зручніше і швидше створити їх заново ніж відредагувати вручну.

Автоматично змінити велику кількість посилань можна тільки у випадку, якщо необхідно змінити ім’я початкової книги або шлях до неї. Для цього використовується діалогове вікно Зміна зв’язків, яке відкривається після натиснення кнопки Змінити зв’язки групи Підключення вкладки Дані. Якщо у робочої книги немає посилань на іншу книгу, команда буде не доступною.

Для зміни зв’язку потрібно виділити його та натиснути кнопку Змінити…, у результаті чого відкриється діалогове вікно Змінити джерело з доступом до файлової структури. За допомогою цього діалогового вікна можна змінити книгу-джерело.

Рис. 7.7. Діалогове вікно Зміна зв’язків

Після виконання всіх вказаних дій у всіх посиланнях ім’я і шлях старої книги-джерела будуть замінені на шлях та ім’я нової книги. Діалогове вікно Зміна зв’язків дозволяє встановити поведінку середовища при відкритті робочої книги зі зв’язками. Для цього потрібно натиснути кнопку Запит на оновлення зв’язків…, відкриється відповідне діалогове вікно, в якому і визначається реакція на оновлення даних.

Рис. 7.8. Діалогове вікно Запит на оновлення зв’язків

Якщо немає необхідності у зв’язку, то його краще за все видалити, особливо якщо зв’язок зовнішній. Для видалення зв’язку достатньо видалити посилання з комірки-приймача.

Якщо необхідно розірвати зв’язок, але при цьому зберегти поточне значення зв’язаної комірки, то потрібно натиснути кнопку Розірвати зв’язок діалогового вікна Зміна зв’язків.

Зображення комірки – це звичайний растровий малюнок, на якому зображена комірка так само, як вона виглядає на робочому листі. При цьому на зображенні відображається не тільки вмістиме комірки, але й фон комірки, рамка, лінії сітки і т.д.

Зображення комірок може бути зв’язане з початковими комірками так, що воно буде оновлюватися при кожній зміні значення або формату комірки. Таке зображення називається зв’язаним. Зв’язок із зображенням з коміркою здійснюється так само, як і при зв’язку комірок – за допомогою посилання.

Для створення зображення комірок призначені команди Копіювати як рисунок… – відкривається діалогове вікно Копіювання рисунка (рис. 7.9.), та команда Спеціальне вставлення – відкривається діалогове вікно Спеціальне вставлення. За допомогою параметрів цих діалогових вікно можна створити зображення та зв’язане зображення комірок.

Рис. 7.9. Діалогове вікно Копіювання рисунка

  1.  Консолідація даних.

Під консолідацією у MS Excel розуміють узагальнення однорідних даних з різних джерел. При консолідації одна комірка аркуша-результату зв’язана з декількома комірками. У цьому випадку значення консолідованих комірок будуть оновлюватися при зміні початкових комірок і відповідати їх поточним даним. Значення таких комірок обчислюється на основі значень комірок, з якими вона зв’язана і може визначатися різними способами – від простого додання до обчислення дисперсії. Крім того існує можливість використання консолідації так, що результуючі комірки будуть відірвані від початкових даних і не залежатимуть від їх змін.

У MS Excel передбачено два основних метода консолідації даних – консолідація за фізичним розміщенням (за розташуванням) комірок і консолідація за заголовками (за категорією). Методи консолідації розрізняються способом, яким задається зв’язок початкових комірок з результуючими. При консолідації за фізичним розміщенням вказуються номери комірок, в яких містяться початкові дані. Цей метод слід використовувати, коли дані з кількох джерел упорядковані однаково. Наприклад, коли є низка аркушів витрат, які створено за одним шаблоном. При консолідації за заголовками використовуються заголовки рядків і стовпців. Тобто, консолідуються дані, які розміщені в рядках і стовпцях з однаковими назвами. Цей метод слід використовувати, коли дані з кількох джерел упорядковані по-різному, але мають однакові підписи рядків і стовпців.

При консолідації даних за розташуванням (фізичним розміщенням) вказується тільки місцезнаходження комірок, які містять початкові даних. Для того, щоб використати цей метод консолідації спочатку потрібно:

  1.  впорядкувати дані в так, щоб кожний діапазон даних був відформатований як список і в ньому немає порожніх рядків і стовпців;
  2.  розмістити кожний діапазон на окремому аркуші, і не розташовувати їх на тому ж аркуші, де будуть розміщуватися консолідовані дані;
  3.  переконатися, що всі діапазони мають однаковий макет.

Після цього потрібно перейти на той аркуш робочої книги, де будуть розміщені консолідовані дані, та активізувати верхню ліву комірку діапазону області консолідації. При цьому необхідно переконатися у тому, що праворуч і внизу залишилася достатня кількість комірок для об’єднаних даних, щоб не замінити існуючі на аркуші дані.

Потім натиснути кнопку Консолідація групи Знаряддя даних вкладки Дані. У діалоговому вікні Консолідація (рис. )у полі Функція потрібно вибрати функцію, яка використовується для об’єднання даних. Використовуючи поле Посилання додати до області Список діапазонів вибрати всі потрібні діапазони з початковими даними, що підлягають консолідації. Якщо початкові діапазони знаходяться в інших робочих книгах при їх виборі потрібно використовувати кнопку Огляд…. Кожний початковий діапазон включається до області Список діапазонів, якщо натиснути кнопку Додати.

Рис. 7.10. Діалогове вікно Консолідація

Якщо необхідно налаштувати автоматичне оновлення консолідованих даних після кожної змінити початкових даних, потрібно увімкнути прапорець Створювати зв’язки з вихідними даними.

Для того, щоб здійснити консолідацію даних за заголовками (категорією) достатньо додатково у діалоговому вікні встановити відповідні прапорці в розділі Використовувати як імена поруч із тими полями, які вказуватимуть на розташування підписів у вихідних діапазонах: у рядку вище, у стовпці ліворуч або обидва. Рядки та стовпці, підписи яких не збігаються з підписами інших вихідних аркушів, після об’єднання буде розташовано в окремих рядках або стовпцях. Якщо назви рядків не визначені, але визначені назви стовпців, то самі стовпці консолідуються за іменами, а комірки в стовпцях консолідуються за розміщенням.

Дані можна об’єднувати не тільки за розташуванням і категорією, а й з використанням формул та звітів зведених таблиць. У якості посилання у формулі, яка виконує консолідацію можна використовувати тривимірні посилання.

Тривимірна адреса (посилання) – це посилання, яка вказує на один і той же діапазон на декількох листах однієї книги. Виглядає тривимірне посилання наступним чином: Аркуш1:Аркуш5!В3:В8.

Для того, щоб створити тривимірне посилання потрібно:

  1.  активізувати книгу, яка містить початкові дані;
  2.  виділити листи, які повинен захопити тримірний діапазон. Для цього потрібно виділити перший робочий лист натиснувши ліву кнопку миші на його ярлику. Потім утримуючи клавішу Shift виділити останній робочий лист. Ярлики виділених листів підсвічуються іншим, зазвичай білим, кольором;
  3.  виділити потрібний діапазон або комірку.

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


 

А также другие работы, которые могут Вас заинтересовать

46385. Бухгалтерский и налоговый учет в строительстве. Учебное пособие 362.5 KB
  ПБУ 2 94 Учет договоров контрактов на капитальное строительство; Руководство Минстроя РФ по составлению договоров подряда на строительство в РФ; ПБУ 10 99 Расходы организации ; ПБУ 9 99 Доходы организации; МСФО 11 Договоры подряда; Глава 25 НК РФ и Методические рекомендации по применению главы Расходы подрядчика связанные с получением заключением договоров на строительство которые могут быть отдельно выделены и существует уверенность в том что договор будет заключен могут относиться к данному договору и...
46387. ТРАКТОРЫ И АВТОМОБИЛИ 993 KB
  Эффективные показатели двигателя Основные параметры цилиндра и двигателя. Тепловой баланс двигателя.Построение теоретических характеристик двигателя
46388. ИСПЫТАНИЕ ОБРАЗЦА НА РАСТЯЖЕНИЕ 361 KB
  Статической вязкостью называется способность материала поглощать энергию идущую на деформирование образца.2 При испытании образца рис.1 на испытательной машине получают первичную диаграмму растяжения в координатах: нагрузка удлинение образца рис.
46389. Изучение схемотехники усилителей электрических сигналов с использованием биполярных и полевых транзисторов 268.5 KB
  Для всех схем включения транзистора снять АЧХ. АЧХ на биполярном транзисторе при включении его с общим эмиттером. Ширина полосы пропускания = 1 kHz – 400 kHz АЧХ на биполярном транзисторе при включении его с общим коллектором. АЧХ на биполярном транзисторе при включении его с общим эмиттером c включенным конденсатором С2.
46390. ЖИТТЄВИЙ ЦИКЛ КЛІТИН. МІТОЗ 875 KB
  Виготовлення тимчасових препаратів корінців проростків пофарбованих ацетокарміном Визначення рівня мітотичної активності мерістематичної тканини Мета: Навчитися фіксувати і фарбувати хромосоми в клітинах рослинних мерістематичних тканин що активно діляться розрізняти фази мітозу в клітинах корінців проростків різних сільськогосподарських культур та розраховувати мітотичний індекс; Матеріали обладнання та реактиви: 1 корінці 5ти денних проростків різних сільськогосподарських культур фіксовані протягом 24 годин через кожні...
46391. Розробка функціональної схеми МПС 179.5 KB
  Розробити функціональну схему МПС яка забезпечує виконання наступних функцій: Роздільне керування записом та читанням пам’яті і ЗП за допомогою сигналів МЕMR MEMW I OR i I OW; Ввід вивід даних у послідовному форматі по 3м каналам; 3 Обробку запитів на переривання від 5ти джерел; Керування клавіатурою; Прямий доступ до пам’яті від 3ти джерел; Обмін даними у паралельному форматі між ЗП та МПС по 6ти каналам у режимі синхронний ввід вивід. Загальний опис МПС Дана МПС не має у своєму складі системного контролера отже...
46392. Магнетизм, електромагнітні коливання і хвилі. Оптика, теорія відносності. Елемен- ти атомної фізики, квантової механіки і фізики твердого тіла. Фізика ядра та елементарних часток 7.63 MB
  Він побудований у відповідності з робочою програмою цієї частини курсу дотриманням вимог загальноприйнятих найменувань і позначення фізичних величин та одиниць їх вимірювання у системі SI; нумерація формул і малюнків проведена в межах кожного розділу. Цей момент дорівнює нулю в рівноважному положенні контура а в деякому положенні він – максимальний.1 де І – сила струму в контурі S – його площа – одиничний вектор нормалі до площини контура напрямок якого визначається за правилом свердлика. Відношення максимального обертового моменту до...
46393. Сутність, складові та засади організації місцевих фінансів 443 KB
  Сутність складові та засади організації місцевих фінансів Сучасне поняття місцеві фінанси ґрунтується на ідейнотеоретичних засадах що формувалися протягом досить тривалого часу: 1. Він представляв собою збірник місцевих законів що вміщував норми державного земельного кримінального проце суального та спадкового права. розвиток поглядів на сутність місцевих фінансів їх склад та принципи організації проходив від представлення їх як: 1. Фінансового господарства...