Послідовне створення Excel, робота даних, видалення

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

Чому Excel потрібні підсумкові таблиці

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

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

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

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

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

У кожній комірці ми підсумовуємо дохід, використовуючи функцію The Summaw. Нарешті, додайте остаточні результати донизу та надішліть звіт до менеджера. Лідер просить регіонів влаштувати вгорі та товари - на лініях. Ви робите новий звіт за 5 хвилин і отримуєте нове завдання: надайте дані про прибуток, а не дохід, тобто з урахуванням витрат. Ну, ми трохи змінюємо формулу, і знову результат готовий за лічені хвилини. Нарешті, є запит на підготовку таких звітів для кожного продавця, щоб оцінити їх ефективність. І це не проблема для підсумкової таблиці, якщо ви знаєте, де розташовані необхідні початкові дані!

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

Створення та робота з консолідованими таблицями в Excel

Припустимо, у нас є таблиця з продажу джерела:

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

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

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

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

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

• Область значень є основною частиною ST, оскільки тут вказано, як обробляти стовпці та лінії. Зазвичай це підсумок - цей метод агрегації буде встановлений за замовчуванням за умови, що всі комірок діапазону приймають числові значення. У присутності щонайменше однієї порожньої комірки або з даними у форматі, який відрізняється від чисельного, замість суми значень клітин, їх кількість буде обчислена, тому цей факт повинен бути врахований. Для нашого прикладу підсумок буде проведено для кожного продукту у відповідному області. На додаток до підсумовування, ви можете встановити будь -який інший метод розрахунків, які мають близько двох десятків (обчислення середнього значення, частки тощо.). Найпростіший спосіб зробити це безпосередньо в підсумковій таблиці, натискаючи на ПКМ для будь -якої комірки та вибору необхідного методу розрахунків;
• Площа ліній - складається з унікальних значень клітин, розташованих у лівій колонці. Якщо вставити тут кілька полів, підсумкова таблиця більше не буде рівною, а багаторівневою;
• Область стовпця також містить значення поля, але вже відносно верхньої лінії стартової таблиці;
• Область фільтра дозволяє накладати обмеження на умови обчислення значень клітин. Зазвичай тут розміщується поле, згідно з яким зразок, встановлений у трьох інших областях, слід відфільтрувати.

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

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

Отже, крок -кровотечі інструкції щодо того, як зробити консолідовану таблицю в Excel:

• Зі списку полів у підсумковій таблиці ми перетягуємо поле "дохід" у область значень;
• Поле "регіон" (область покупця) перетягується в лінії;
• Поле "продукт" поставлено в область стовпців.

Готові лише три дії - і підсумкова таблиця, придатна для надсилання влади! Нам знадобилося близько 10 секунд, щоб його скласти.

Як виконати інші побажання директора?

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

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

Щоб отримати результати для кожного менеджера, достатньо розмістити поле "менеджера" в області фільтра, тоді селектор з’явиться вгорі, в якому потрібно вказати потрібне прізвище для отримання звіту. Є можливість поєднати звіт про кількох продавців.

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

Про джерело даних

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

• Основне обмеження пов'язане з обов'язковою наявністю імен над стовпцями, що беруть участь у розрахунках. Такі ідентифікатори необхідні для утворення отриманих звітів - при додаванні нових записів (рядків) до стартової таблиці формат не є необхідним для зміни формату ST, і результати будуть перераховані автоматично;
• Переконайтесь, що в клітинах ліній та стовпців, що беруть участь у зразку, вводяться числові параметри. Якщо вони порожні або містять текстові значення, ці рядки випадуть із розрахунків, що спотворюватиме результати розрахунків;
• Дотримуйтесь відповідності форматів ліній та вмісту клітин. Якщо він визначається як дата, то всі значення в стовпці повинні мати однаковий формат, інакше фільтрування та прорахунки будуть неправильними.

Якщо ви дотримуєтесь цих правил, то при побудові підсумкових проблем з таблицею не повинно виникати.

Оновлення даних у таблиці набору Excel

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

Ви можете оновити дані в нашій таблиці експозиції Excel двома способами:

• Натисніть кнопкою правої миші на будь -якій комірці та вибрати елемент "Оновлення" у контекстному меню;
• Використання вкладки "Дані" та кнопки "Оновити все".

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

Додавання стовпця або лінії до Excel

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

Часто підсумкову таблицю не зовсім зручні види для візуалізації. Наприклад, коли в рядках є кілька полів. Тоді один з них відображається повністю, а значення іншого повинні бути вказані у верхній частині таблиці. Якщо таких значень є мало (скажімо, у нашому прикладі регіонів продажів лише 6), то має сенс додати стовпці "області" області повороту до відповідної області, передаючи її з області лінії.

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

Формування підсумкової таблиці з декількох файлів (таблиці) Excel

Це складніше, але цілком повне завдання. Використовуйте Master ST, який потрібно додати на панель швидкого доступу.

Отже, ліворуч, біля елемента "Файл", натисніть на стрілку, спрямовану вниз, виберіть елемент "Інші команди". На середній панелі відкриється нове вікно, ми натискаємо на параметри "Усі команди". У списку "Майстер консолідованих таблиць" у списку, який відкривається, виберіть його та натисніть на кнопку "Додати". Відповідна значок з’явиться на верхній панелі.

Ми запускаємо майстра. Припустимо, нам потрібно поєднати дані з двох аркушів, травня та в червні. Основна умова полягає в тому, що вони повинні збігатися за структурою. Якщо так, то в першому вікні господаря ми вибираємо параметр «Таблиця речовини» та наступний параметр - «Кілька діапазонів консолідації».

На другому кроці ми вказуємо на майстра, що нам потрібно кілька полів. На третьому - виберіть діапазон комірок для підсумовування першого аркуша, натисніть "Додати" і зробіть те ж саме для другого листя Excel. Тепер вам потрібно дати імена на два поля. Ми робимо це спочатку для першого поля (натисніть на селектор "1", у стовпці "1 поле" ми вводимо "Мей"), потім, за аналогією, для другого. У цьому випадку потрібно буде вказати у списку діапазонів відповідного рядка, першого чи другого.

Після натискання кнопки "Готовий" відкривається нове вікно, в якому ми вказуємо "новий лист" і знову натисніть кнопку "Готовий".

УВАГА. Отримання багатовимірних таблиць незручно, оскільки важко орієнтуватися з великою кількістю полів, а необхідні розрахунки повинні проводитися, щоразу, що вказує на необхідні значення з верхньої області таблиці.

Як видалити мистецтво

Найпростіший випадок - коли ви створили консолідовану таблицю, надіслали результати шеф -кухару, і вам більше не потрібно. Якщо ви впевнені в цьому, просто виберіть таблицю та натисніть клавішу Delete. Простий і ефективно.

Але раптом у майбутньому може знадобитися структура таблиці? Excel має можливість видалити лише результати або дані з комірок. Поміркуйте, як це робиться.

Щоб видалити результати обчислення, ми виконуємо наступні кроки:

• Ми позначаємо будь -яку клітину ST;
• Виберіть вкладку "Аналіз" на верхній панелі;
• Зі списку доступних дій, виберіть "Очистити";
• Клацніть на точку "очистити все".

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

• Знову виберіть будь -яку комірку, натисніть на вкладку "Аналіз";
• Виберіть пункт меню "Дія", натисніть "Виберіть", позначте всю консолідовану таблицю мишею;
• Клацніть PCM всередині вибраної області;
• У контекстному меню виберіть пункт "Копіювати";
• Ми переходимо на вкладку "Home", знову натискаємо на PKM та виберете "Вставити";
• Виберіть вкладку "Вставити значення", в ньому ми зазначаємо параметр "Вставити як значення".

Як результат, підсумок таблиця буде стерта, зберігаючи результати.

ПОРАДА. Ви можете прискорити процедуру, використовуючи комбінації ключів. Щоб вибрати таблицю, використовуйте Ctrl + A, для копіювання - Ctrl + C. Потім натисніть Alt + E, Alt + S, Alt + V і завершіть процедуру, натиснувши Enter.

Щоб видалити консолідовані таблиці в Excel 2007/2010, потрібно використовувати інший алгоритм:

• Виберіть ST, призначений для видалення;
• Клацніть вкладку "Параметри";
• У "Групі дій" виберіть "Чистий" предмет, а потім - "Очистіть все".

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

У старих версіях програми для цього потрібно вибрати схему, натисніть на вкладку "Аналіз", виберіть групу даних та натисніть послідовно "очистити" та "очистити все".

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

Для версій, старших, ніж Excel 2010, вам потрібно вибрати схему, вибрати "Дії" на вкладці "Аналіз" та натисніть "Очистити" та "очистити все". Результат буде подібним.

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