Зведена таблиця в Excel – зручний інструмент аналізу та представлення даних. Але що робити, якщо дані перебувають у різних джерелах? Розберемо, як зробити зведену таблицю з кількох аркушів в Excel.
Навіть якщо є ERP та BI-системи, без Excel фінансовому директору не обійтися. Різні розрахунки, зведені таблиці, Зручні графіки - в Excel можна зробити практично все що завгодно. Але треба знати, як це зробити.
Зведена таблиця в Excel засобами Power Query
Для початку слід зазначити, що Excel може працювати з вихідними таблицями різного розміру. Але заголовки та шапка цих таблиць мають бути однакові. Це потрібно для того, щоб програма правильно інтерпретувала використовувані дані. В іншому випадку може виникати помилка.
Допустимо, необхідно, дані для якої треба взяти з різних аркушів. Така ситуація може виникати у випадку, якщо в компанії є кілька розподілених точок збуту, складських приміщень, різні замовники однакових груп товарів. У такому разі звіти надаватимуть різні філії. І щоб їх адекватно проаналізувати, є сенс створити загальну для всієї компанії таблицю даних, на підставі якої надалі буде побудовано зведену таблицю.
У цьому випадку необхідно створити новий чистий лист у програмі Excel.
У цьому аркуші перейдіть у вкладку «Дані».
Далі натиснути «Створити запит», у списку вибрати «З файлу» і потім – «З книги». Створимо зведену таблицю з кількох аркушів на прикладі – два магазини надіслали звіти про наявність у них ящиків різного кольору на стелажах. Дані кожного магазину зберігаються на одному аркуші. З цих листів сформовано «Книжку1», з якою ми працюємо.
У вікні, що з'явиться, треба вказати книгу, звідки програма повинна взяти дані і натиснути кнопку «Імпорт».
З'явиться вікно під назвою "Навігатор". У ньому треба вибрати аркуш, з якого буде взято дані. Вказати можна будь-який аркуш.
Програма покаже дані у вікні попереднього перегляду, які потрібно взяти із зазначеного аркуша.
У прикладі видно, що зазначений лист містить безліч осередків з даними «null». Це неправильно, оскільки програма оброблятиме й ці осередки. Щоб скоротити область значень, що обробляються і видалити такі нульові осередки, необхідно виправити Вихідний файл. Для цього потрібно перейти у вихідну таблицю та натиснути Ctrl+End. Буде виділено останній активний осередок таблиці. Треба видалити всі осередки правіше і нижче таблиці, домагаючись того, щоб при натисканні «Ctrl + End» ставав активним нижній правий осередок таблиці.
Після цього джерело даних не міститиме зайвої інформації.
Можна видалити рядки «Навігація» та «Змінений тип». І розпочати редагування даних у розділі «Джерело». У головному вікні редактора відображатиметься перелік усіх аркушів цієї книги. У нашому випадку «Лист1» та «Лист2».
Потім у рядку Data натиснути іконку з двома стрілками, як вказано на малюнку.
У вікні зняти галочку з пункту «Використовувати вихідне ім'я стовпця як префікс». І натиснути "ОК". З'явиться таблиця, в якій зібрані всі дані.
Таблиця буде перебудована. Дублюючий рядок із «шапкою» можна видалити. Для цього у фільтрі стовпця Склад зняти галочку з пункту Склад і натиснути ОК. Потім у цьому фільтрі натиснути «Видалити порожні». Відповідні рядки будуть видалені.
У вікні «Завантажити в» поставити перемикач у позицію «Тільки створити підключення» і натиснути кнопку «Завантажити». З'явиться запит, на підставі якого будуватиметься зведена таблиця.
У вікні встановити перемикач у положення «Використовувати зовнішнє джерело даних» і натиснути кнопку «Вибрати підключення».
У вікні, що з'явиться, вибрати ім'я сформованого запиту, в нашому випадку – «зведена» і натиснути кнопку «Відкрити».
З'явиться конструктор зведеної таблиці. Дані можна додавати та переміщати як у звичайній зведеній таблиці.
Зведена таблиця у версіях Excel до 2016 року
У програмах Excel, виданих до 2003 року включно, зведені таблиці з різних джерел створювалися через опцію «побудувати зведену за кількома діапазонами консолідації». Однак така побудова таблиці не дає можливості повноцінно аналізувати весь обсяг отриманої інформації. Таблиця, побудована таким чином, може використовуватися тільки для найпростішого аналізу даних.
У версіях з 2007 року треба було додавати спеціальний «Майстер зведених таблиць», де нові діапазони даних по одному вказувалися через діалогові вікна. Отримані зведені таблиці також мали досить обмежену функціональність.
Що таке зведені таблиці Excel? Ймовірно, непосвячена людина припустить найочевидніше - це таблиці, у яких зібрані (зведені) дані з інших розрізнених джерел, відповідають деякому запиту. Насправді цей висновок не зовсім вірний.
Зведені таблиці Excel – це інструмент, який збирає за заданим алгоритмом у зручні таблиці-звіти інформацію найчастіше з однієї таблиці бази даних! (Можливість використання джерела кількох діапазонів консолідації існує, але в цій статті розглядатися не буде.)
За допомогою зведених таблиць із хаотичного набору рядків-записів бази даних можна за лічені секундивитягти потрібні, відсікти в них зайву інформацію, додати недостатню і представити користувачеві в красивому та зручному для сприйняття вигляді.
Якщо ви постійно працюєте у програмі MS Excel з великими обсягами інформації і нічого не знаєте про зведені таблиці, то вважайте, що ви «забиваєте цвяхи» калькулятором, не знаючи його справжнього призначення!
Коли слід використовувати зведені таблиці?
По-перше, тоді, коли працюєш з великим обсягом статистичних даних, аналізувати які дуже трудомістко за допомогою сортування та фільтрів.
По-друге, тоді, коли через певні проміжки часу доводиться повторювати (перераховувати) звіти у зв'язку зі змінами джерела – таблиці бази даних.
По-третє, під час аналізу змін даних бази у межах різних часових періодів чи інших критеріїв.
Це основні ситуації, у яких розкривається вся справді чарівна сила інструменту зведені таблиці Excel.
Створення зведених таблиць і з ними ніяк не впливає зміст джерела – базу даних!!! Зведені таблиці Excelдозволяють глянути на ці дані під різними ракурсами, не змінюючи нічого в базі!
Це заключна і найважливіша стаття оглядового циклу про зберігання інформації та управління великими табличними обсягами даних.
У попередніх статтях циклу ми створили, навчилися, застосовувати та вводити.
Створення шаблону зведеної таблиці.
Зведену таблицю створюватимемо в тій же книзіExcel, де є джерело інформації — таблиця бази даних, але в іншому аркуші.
Продовжуємо роботу з навчальною міні-базою БД2 «Випуск металоконструкцій ділянкою №2», з якою працювали у всіх статтях цього циклу.
1. Відкриваємо в MS Excel файл.
2. Активуємо («клацаємо мишкою») будь-яку комірку всередині таблиці бази.
3. Виконуємо команду головного меню програми "Дані" - "Зведена таблиця ...". Ця команда запускає роботу міні-сервісу "Майстер зведених таблиць".
4. Не довго розмірковуючи над варіантами вибору положень перемикачів у вікнах «Майстра…», налаштовуємо їх (точніше – не чіпаємо їх) так, як показано нижче на знімках екрана, рухаючись між вікнами за допомогою кнопок «Далі».
На другому кроці «Майстер» сам вибере діапазон, якщо ви правильно підготували базу даних і виконали п.2 цього розділу статті.
5.
На третьому кроці «Майстра…» натискаємо кнопку «Готово». Шаблон зведеної таблиці сформовано та розміщено на новому аркуші того ж файлу, де розташована база даних!
Крім поки порожній шаблонна новоствореному аркуші файлу Excelви бачите панель «Зведені таблиці» зі своїм інструментарієм та вікно «Список полів зведеної таблиці», що з'являється при активації будь-якого осередку всередині зведеної таблиці. З'являються вони автоматично, я їх просто розташував поряд із шаблоном та підкоригував розміри. Якщо панелі "Зведені таблиці" ви не бачите, викличте її через головне меню командою "Вид" - "Панелі інструментів" - "Зведена таблиця"
У MS Excel 2007 і новіших версіях «Майстер…» скасовано тому, що 99% відсотків користувачів ніколи не змінюють запропонованих налаштувань перемикачів і проходять ці три кроки, просто погоджуючись із запропонованими варіантами (ми теж так вчинили).
У MS Excel 2007 за командою «Зведена таблиця» випадає діалогове вікно «Створення зведеної таблиці», в якому достатньо вказати джерело даних для аналізу та місце розташування зведеної таблиці.
Створення робочих зведених таблиць Excel.
У створеному шаблоні є чотири зони, які для створення зведеної таблиці слід помістити назви полів-стовпців таблиці бази даних, перетягнувши їх за допомогою лівої кнопки миші з вікна «Список полів зведеної таблиці». Нагадую, що у базі даних полем називається стовпець із назвою.
Увага!!!
Елементами вікна "Список полів зведеної таблиці" є заголовки полів бази даних!
1. Якщо помістити один із елементів «Списку полів зведеної таблиці» в саму верхню зонушаблону, де він стане «Полем сторінок», то ми отримаємо зручний фільтр записів цього поля.
2. Якщо перетягнути один елемент (або два, рідше – три і більше) "Списку полів зведеної таблиці" в ліву зону шаблону, де він стане «Полем рядків» заголовки рядківзведеної таблиці.
3. Якщо перетягнути один елемент (або два, рідше – три і більше) «Списку…» у верхню зону шаблону, де він стане «Полем стовпців», то ми отримаємо із записів цього поля таблиці бази даних заголовки стовпцівзведеної таблиці.
4. Якщо перетягнути один елемент (або два, рідше – три і більше) «Списку …» до центральної області шаблону, де він стане "Елементом даних", то ми отримаємо із записів цього поля бази даних значеннязведеної таблиці.
Значення – елементи даних – розташуються у суворій відповідності до правил двомірних таблиць, тобто на перетині відповідних заголовків рядків та заголовків стовпців!
Не дуже зрозуміло? Перейдемо до практичних прикладів – все стане зрозумілим!
Завдання №9:
Скільки всього тонн металоконструкцій виготовлено за кожним замовленням?
Для відповіді на це запитання виконаємо лише дві дії! Схема цих дій показано попередньому малюнку синіми стрілками.
1. Перетягніть елемент «Замовлення» з вікна «Список полів зведеної таблиці» в зону «Поля рядків» порожнього шаблону.
2.
Перетягнемо мишею елемент "Загальна маса, т" з вікна "Список полів зведеної таблиці" в зону "Елементи даних" шаблону. Результат – на екрані зліва. Думаю, пояснення не потрібні.
Заголовок «Елементів даних» «Сума по полю Загальна маса, т» розташувався вищезаголовка «Поля рядків» і ліворуч від місця, де може розташуватися заголовок «Поля стовпців». Зверніть на це увагу!
Завдання №10:
Скільки тонн металоконструкцій виготовлено за кожним замовленням за датами?
Продовжуємо роботу.
3. Для відповіді на запитання задачі №10 достатньо додати до нашої зведеної таблиці елемент "Дата" з вікна "Список полів зведеної таблиці" в зону "Поля стовпців" шаблону.
4.
Записи можна згрупувати, наприклад, по місяцях. Для цього на панелі «Зведені таблиці» натискаємо вкладку «Зведена таблиця» та вибираємо «Група та структура» — «Групувати…». У вікні «Групування» робимо налаштування відповідно до скріншота зліва.
Оскільки всі записи нашої бази даних зроблено у квітні, то після угруповання ми бачимо всього два стовпці – «квітень» та «Загальний результат». Коли у вихідній базі даних з'являться записи, датовані травнем та червнем, у зведеній таблиці додадуться відповідні два стовпці. 
Завдання №11:
Коли і скільки тонн і штук марок виготовлено за замовленням №3?
5. Для початку перетягнемо поле «Замовлення» з вікна «Список полів зведеної таблиці» або прямо з самої зведеної таблиці до верху аркуша в зону «Поля сторінок» шаблону.
7. Поле "Кількість, шт" додамо в область "Елементи даних".
Необхідна відповіді питання завдання конфігурація зведеної таблиці Excel сформована.
8. Натиснемо на кнопку зі стрілкою вниз в комірці B1 і в списку, що випав, замість запису «(Все)» виберемо «3», відповідну замовленню №3, що цікавить нас, і закриємо список, натиснувши кнопку «OK».
Відповідь на запитання завдання на знімку екрана нижче цього тексту.
Форматування зведеної таблиці.
Якщо зведену таблицю потрібно роздрукувати і показати комусь, або просто трохи порадувати себе, то її потрібно відформатувати - надати гідний зовнішній вигляд і переформулювати деякі «неадекватні» заголовки.
Заголовки «Сума по полю Загальна маса, т» і «Сума по полю Кількість, шт» звучать, начебто, і зрозуміло, але якось не по-російськи. Перейменуємо їх у більш милозвучні «Загальна маса виробів, т» та «Кількість виробів, шт».
1. Продовжимо роботу із створеною таблицею. Активуємо мишею комірку B5.
2. На панелі інструментів «Зведена таблиця» натискаємо кнопку «Параметри поля» (виділено праворуч на знімку, розташованому нижче).
3. У вікні "Обчислення поля зведеної таблиці" змінюємо ім'я і тиснемо кнопку "OK".
4.
За аналогічним алгоритмом перейменовуємо і другий заголовок поля, попередньо вставши мишею на комірку B6.
5. На панелі «Зведена таблиця» натискаємо кнопку «Формат звіту» (виділено ліворуч на знімку, розташованому вище – над п.3).
6. У вікні «Автоформат» вибираємо із запропонованих варіантів форматування, наприклад, «Звіт 6» і натискаємо «OK» .
Зовнішній вигляднашої зведеної таблиці суттєво перетворився.
Висновок.
Звертаю вашу увагу на кілька важливих моментів!
При зміні джерела (наприклад, додавання чергового запису до бази даних) у самій зведеній таблиці зміни автоматично не наступлять!
Для того, щоб зміни в базі відображалися в зведеній таблиці, необхідно щоразу «вручну» натискати кнопку «Оновити дані» (значок кнопки – « ! ») на панелі «Зведені таблиці»!
Поміщайте в область «Полів сторінок» поля, що містять текстову інформаціюяка може стати заголовком для формованої зведеної таблиці.
В області "Полів рядків" та "Полів стовпців" розміщуйте поля, що містять текстову інформаціюяка може стати заголовками для рядків та стовпцівстворюваної зведеної таблиці.
У зоні «Елементів даних» розміщуйте переважно цифрову інформацію.
Дійте, розташовуючи поля так, як би ви діяли, роблячи це на аркуші папері! Не бійтеся помилитись. Все легко виправляється.
Створені двома-трьома рухами миші в одному з попередніх розділів цієї статті зведені таблиці дуже швидко відповіли на дуже непрості питання! Якщо замовлення виготовляються протягом кількох місяців, а кількість найменувань марок перевищує кілька тисяч, то скільки вам знадобиться часу для вирішення розглянутих вище завдань? Година? День? Зведені таблиці Excel роблять це миттєво, багаторазово і без помилок!
Про зведені таблиці Excel написано багато хороших "товстих" і "тонких" книг. Насамперед це книги Білла Джелена («Містера Excel») та Майкла Александера. Тим, хто зацікавився цією темою, рекомендую їх прочитати.
Звичайно, докладно розповісти про цей чудовий інструмент у короткій статті дуже складно, практично неможливо. З одного боку мені хотілося торкнутися більше практично важливих аспектів, але з іншого – в ознайомлювальній статті не хотілося «залазити в нетрі», відлякуючи користувачів обсягом та складністю інформації. Якщо цей «чарівний інструмент» MS Excel викликає інтерес у читачів блогу, то в майбутньому цю тему буде продовжено випуском невеликої брошури з важливими та цінними практичними рекомендаціями.
Обов'язково візьміть на зброю зведені таблиці Excel! Ваша цінність як фахівця збільшиться багаторазово!
На цьому цикл із шести статей, що являють собою загальний огляд теми обробки великих обсягів інформації в Excelзавершено.
Прошу поважають праця автора підписатися на анонси статей у вікні, розташованому наприкінці кожної статті або у вікні вгорі сторінки!
Шановні читачі, запитання, відгуки та зауваження залишайте в коментарях внизу сторінки.
Джерелом даних для зведеної таблиці служить список даних, де, зазвичай, кожен стовпець виступає ролі поля зведеної таблиці. Але якщо до вас прийшла, тільки на вигляд схожа на зведену, таблиця (вона відформатована і зовні нагадує її, але використовувати інструменти роботи зі зведеними таблицями неможливо). І вам необхідно перетворити її на список даних, тобто. виконати зворотну операцію. У цьому пості ви дізнаєтесь, як перетворити зведену таблицю з двома змінними на список даних.
На малюнку показано принцип, який я описав. Тобто. в діапазоні A2:E5 знаходиться вихідна зведена таблиця, яка перетворюється на список даних (діапазон H2:J14). Друга таблиця представляє той самий набір даних, лише іншому ракурсе. Кожне значення вихідної зведеної таблиці виглядає у вигляді рядка, що складається з пункту поля рядка, поля стовпця та відповідного значення. Таке відображення даних буває корисним, коли необхідно відсортувати та маніпулювати даними іншими способами.
Для того, щоб реалізувати можливість створення такого списку, ми скористаємося інструментами зведеної таблиці. Додамо кнопку Майстер зведених таблицьв панель швидкого доступу, яка недоступна нам на стрічці, але залишилася як рудимент від більш ранніх версій Excel.
Перейдіть по вкладці Файл -> Параметри. У діалоговому вікні, що з'явилося. ПараметриExcel,у вкладці Панель швидкого доступу у лівому полі знайдіть пункт Майстер зведених таблиць та діаграмі додайте його до правого. Натисніть кнопку ОК.
Тепер на панелі швидкого доступу з'явиться новий значок.
Клацніть на цій вкладці, щоб запустити Майстер зведених таблиць.
На першому етапі майстра необхідно вибрати тип джерела даних зведеної таблиці. Встановлюємо перемикач У кількох діапазонах консолідаціїі тиснемо Далі.
На кроці 2а вкажіть, як створювати поля сторінки. Помістіть перемикач Створити поля сторінки -> Далі.
На кроці 2б, у полі Діапазонвиберіть діапазон, який містить дані, і клацніть Додати.У нашому випадку це буде місце вихідної зведеної таблиці A1: E4.
На третьому кроці необхідно визначитися, куди необхідно помістити зведену таблицю, та натисніть кнопку Готово.
Excel створить зведену таблицю даних. У лівій частині екрана ви побачите область Список полів зведеної таблиці.Приберіть усі пункти з полів рядків та стовпців. Докладніше про я писав у попередній статті.
У вас вийде невелика зведена таблиця, що складається з одного осередку, що містить суму всіх значень вихідної таблиці.
Двічі клацніть по цьому осередку. Excel створить новий аркуш, який міститиме таблицю зі списком значень.
Заголовки цієї таблиці є загальну інформаціюймовірно, ви захочете зробити їх більш інформативними.
Зведена таблиця застосовується швидкого аналізу великого обсягу даних. Вона дозволяє поєднувати інформацію з різних таблиць та аркушів, підрахувати загальний результат. Цей універсальний аналітичний інструмент значно розширює можливості програми Excel.
Можна сформувати нові підсумки за вихідними параметрами, змінивши рядки та стовпці місцями. Можна зробити фільтрацію даних, показавши різні елементи. А також наочно деталізувати область.
Зведена таблиця в Excel
Наприклад використовуємо таблицю реалізації товару у різних торгових філіях.
З таблички видно, у якому відділі, що, коли і яку суму було продано. Щоб знайти розмір продажу по кожному відділу, доведеться порахувати вручну на калькуляторі. Або зробити ще одну таблицю Excel, де за допомогою формул показати підсумки. Такими методами аналізувати інформацію є непродуктивним. Недовго й помилитись.
Найраціональніше рішення - це створення зведеної таблиці в Excel:
Просто, швидко та якісно.
Важливі нюанси:
- Перший рядок заданого для зведення даних діапазону має бути заповнений.
- У базовій табличці кожен стовпець повинен мати свій заголовок - простіше налаштувати звіт.
- В Excel як джерело інформації можна використовувати таблиці Access, SQL Serverта ін.
Як зробити зведену таблицю з кількох таблиць
Часто потрібно створювати зведені звіти з кількох таблиць. Є кілька табличок з інформацією. Потрібно об'єднати в одну загальну. Для науки придумаємо залишки на складах у двох магазинах.
Порядок створення зведеної таблиці з кількох аркушів такий самий.
Створимо звіт за допомогою майстра зведених таблиць:
Як бачите всього в кілька кліків, можна створювати складні звіти з декількох аркушів або таблиць різного обсягу інформації.
Як працювати зі зведеними таблицями в Excel
Почнемо з найпростішого: додавання та видалення стовпців. Наприклад розглянемо зведену табличку продажів з різних відділів (див. вище).
Праворуч від зведеної таблиці ми мали область завдань, де ми вибирали стовпці у списку полів. Якщо вона зникла, просто клацаємо мишею по табличці.
Додамо до зведеної таблиці ще одне поле для звіту.Для цього встановимо галочку навпроти "Дати" (або навпроти "Товара"). Звіт одразу змінюється – з'являється динаміка продажів щодня у кожному відділі.
Згрупуємо дані у звітіпо місяцях. Для цього клацаємо правою кнопкоюмиші по полю "Дата". Натискаємо "Групувати". Вибираємо "по місяцях". Виходить зведена таблиця такого виду:
Щоб змінити параметри у зведеній таблицідостатньо зняти галочки навпроти наявних полів рядків і встановити в інших полів. Зробимо звіт щодо найменувань товарів, а не відділів.
А ось що вийде, якщо ми приберемо "дату" і додамо "відділ":
А ось такий звіт можна зробити, якщо перетягнути поля між різними областями:
Щоб назва рядка зробити назвою стовпця, вибираємо цю назву, клацаємо по спливаючому меню. Натискаємо "перемістити в назву стовпців". У такий спосіб ми перемістили дату в стовпці.
Поле "Відділ" ми проставили перед найменуваннями товарів. Скориставшись розділом меню «Перемістити на початок».
Покажемо деталі щодо конкретного продукту. Приклад другої зведеної таблиці, де відображені залишки на складах. Виділяємо комірку. Клацаємо правою кнопкою миші - "розгорнути".
У меню вибираємо поле з даними, які необхідно показати.
Коли натискаємо зведену таблицю, стає доступною закладка з параметрами звіту. З її допомогою можна змінювати заголовки, джерела даних, групувати інформацію.
Перевірка правильності виставлених комунальних рахунків
За допомогою зведених таблиць Excel легко перевірити, наскільки обслуговуючі організації нараховують квартплату. Інший позитивний момент – економія. Якщо ми щомісячно контролюватимемо, скільки витрачається світла, газу, то зможемо знайти резерв для економії коштів на оплату квартири.
Для початку пропонуємо скласти зведену таблицю тарифів з усіх комунальних платежів. Для різних міст дані будуть свої.
Наприклад ми зробили зведену табличку тарифів для Москви:
Для навчальних цілей візьмемо сім'ю із 4 осіб, які проживають у квартирі 60 кв. м. Щоб контролювати комунальні платежі, необхідно створити таблиці для розрахунку кожного місяця.
Перший стовпець = першому стовпцю зі зведеної таблиці. Другий – формула для розрахунку виду:
Тариф * кількість осіб / показання лічильника / площа
Наші формули посилаються на аркуш, де розташована зведена таблиця з тарифами.
Якщо при розрахунку комунальних платежів застосовуються пільги, їх також можна внести до формул. Інформацію щодо нарахувань вимагайте у бухгалтерії своєї обслуговуючої організації. Коли змінюються тарифи – просто змініть дані у осередках.
Зведена таблиця – інструмент, який допомагає проаналізувати дані компанії. Створити таку таблицю можна у різних текстових редакторах.
Для створення необхідно вибрати заголовки та для рядків та стовпці зі створеного звіту. Найзручніше звіти робити в Екселі. Ви можете застосувати фільтр до всього звіту або окремої його частини.
Зведена таблиця в excel.
Насамперед розберемо версію 2007 року. У деяких користувачів ще залишилася стара версія редактора. Для початку потрібно створити таблицюдля побудови звіту. У ній не повинно бути порожніх осередків та рядків. Розберемо процес створення звіту на найпростішому прикладі:
- Запускаємо ексель 2007 року. На верхній панелі вибираємо вкладку "Вставка".
- Вибираємо "Створення рекомендованих зведених таблиць".
- З'явиться вікно, де будуть представлені варіанти рамок. Ви можете попередньо подивитися, як виглядатиме ваш звіт, якщо натиснете на макет. Після цього можна вибрати основний. Натискаєте на Ок та звіт з'явиться на аркуші. Крім цього, ексель сам визначить порядок розташування рядків.
- У програмі можна відфільтрувати та впорядкувати значення. Для цього є значок на панелі.
Це найпростіший приклад того, як можна зробити зведену таблицю старої версіїексель.
Зведені таблиці в Excel 2010 та 2013 року – приклад роботи в Excel
Створення звіту в ексель 2010 та 2013 практично однаково. Процес у цих версіях трохи відрізняється від створення у версії 2007 року. Але, деякі моментисхожі: перед початком роботи необхідно створити просту таблицю, в якій вказати всі дані, необхідні для вас. Верхній рядок має містити назву стовпців.
Тепер можна робити звіт у екселі:
Що можна робити з листом:
- Вам буде доступна панель інструментів для керування значеннями. Усі поля та їх заголовки будуть перераховані у вкладці «Виберіть поля для додавання звіту». Для додавання потрібних пунктів необхідно вибрати потрібні поля та список буде створено.
- Є можливість керувати розташуванням полів. Ви можете перетягувати стовпці у потрібні вікна, а також сортувати у зручному для вас порядку. Також можна фільтрувати пункти. Для цього потрібно перетягнути пункт у вікно Фільтр. Якщо вам необхідно зробити розрахунок окремим полем, то перетягніть його у вікно «Значення».
- Ви можете скористатися додатковими опціями. Знайти їх можна за таким принципом: "Параметри" "Робота зі зведеними таблицями". Там дуже багато інструментів, які мають велику кількість налаштувань та додаткових функцій.
Як створити зведену таблицю за допомогою майстра зведених таблиць
Інструмент "Майстер зведених таблиць" не відображається на головній панелі. Саме тому його потрібно спочатку активувати. Для цього переходимо "Файл" "Параметри" "Панель швидкого доступу". Перед вами відкриється список"Вибрати команди з", відзначаєте "Команди на стрічці". Тепер вибираєте майстри зведених таблиць та додаєте його. Після всіх дій на головній панелі з'явиться піктограма вибраної функції.
Тепер можна перейти і до самого процесу створення зведеного звіту за допомогою майстра:
Якщо джерелом було обрано простий звіт, у якому є всі необхідні дані для звіту, вам залишається тільки зробити його. Задаєте діапазон, тобто вибираєте даніпростий таблиці. Вибираєте місце для розміщення та натискаєте на кнопку «Готово».
Якщо ви вибираєте джерело, дані якого необхідно консолідувати з інших джерел, тоді необхідно поставити галочкунавпроти цього пункту. Тепер потрібно вказати, як ви створюватимете поля сторінки. Також потрібно буде вказати діапазон. Для цього спочатку виділяємо один діапазон і натискаємо на кнопку «додати». Також додаються наступні значення.
Щоб не заплутатися у діапазонах, їм потрібно давати імена. Для цього потрібно виділити один із діапазонівсписку та вказати кількість полів для нього, після чого кожному полю даєте ім'я. І так із усіма діапазонами.
Зведені таблиці Excel 2010 з кількох аркушів
Якщо інформація у звітах однотипна, то найлегше скористатися майстром зведених таблиць. Якщо звіт має складну структуру, діяти треба по-іншому. Наприклад, є дві таблиціодна містить прихід товару, а інша – кількість проданого товару в різних магазинах. Головне, щоб були однакові заголовки, менше часу переноситимете їх в один звіт. У цьому випадку звіт робиться так:
Тепер потрібно створити зведений звіт:
- Вставлення.
- Зведений звіт.
- Вписуємо діапазон та місце.
Але що робити, якщо потрібно створити звіт з кількох таблиць. Вручну працювати складно. Для цього можна скористатисяспеціальною надбудовою ЕXCEL. Завантажується вона на сайті розробника.
Отже, ми розібрали, як створювати зведені таблиціу excel. Потрібно виявити трохи посидючості та уважності і тоді результат не забариться.
Завантаження...