Як перетворити зведену таблицю в звичайну

Інструкція

Відкрийте документ Excel з даними, з яких ви хочете зробити зведену таблицю. Клацніть покажчиком миші по одному з елементів таблиці, обов'язкова умова - щоб в цьому осередку було записано якесь значення. Найзручніше - привласнити одну відповідне ім'я, наприклад, «назва», «кількість» і т.п.

В панелі меню у верхній частині вікна знайдіть кнопку «Дані» (для Excel 2000, ХР, 2003) або вкладку «Вставка» (для Excel 2007) та натисніть її. Відкриється список, в якому активуйте кнопку «Зведена таблиця». Відкриється «Майстер створення зведених таблиць», він допоможе налаштувати всі необхідні параметри.

Виберіть джерело даних для зведеної таблиці і тип документа. Вікно майстра розділене на дві частини. У верхній половині вкажіть, звідки потрібно брати інформацію, наприклад, з файлу Excel або окремої бази даних. У нижній частині за замовчуванням відзначений пункт «Зведена таблиця», тому можна нічого не міняти. Натисніть кнопку «Далі» і переходите до другого етапу роботи майстра.

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

Натисніть кнопку «Готово» на останньому екрані настройки таблиці. Ви можете вибрати місце, де буде розташована ваша зведена таблиця. За замовчуванням це «Новий лист», і це кращий з варіантів. Також можна вибрати пункт «Існуючий лист», але в цьому випадку можуть бути складності з циклічними посиланнями і виведенням на екран.

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

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

Корисна порада

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

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

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

Інструкція

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

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

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

Вихідний матеріал - таблиця з декількома десятками і сотнями рядків, кілька таблиць в одній книзі, кілька файлів. Нагадаємо порядок створення: «Вставка» - «Таблиці» - «Зведена таблиця».

А в даній статті ми розглянемо, як працювати зі зведеними таблицями в Excel.

Як зробити зведену таблицю з декількох файлів

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

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

Ми просто створюємо зведений звіт на основі даних в декількох діапазонах консолідації.

Набагато складніше зробити зведену таблицю на основі різних за структурою вихідних таблиць. Наприклад, таких:

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

Майстер зведених таблиць при таких вихідних параметрах видасть помилку. Так як порушено одне з головних умов консолідації - однакові назви стовпців.

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

Відкривається заготівля Зведеного звіту до Списку полів, які можна відобразити.

Покажемо, наприклад, кількість проданого товару.

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

Деталізація інформації в зведених таблицях

Із звіту (див.вище) ми бачимо, що продано ВСЬОГО 30 відеокарт. Щоб дізнатися, які дані були використані для отримання цього значення, клацаємо два рази мишкою по цифрі «30». Отримуємо детальний звіт:

Як оновити дані в зведеній таблиці Excel?

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

Ефективно використовувати час:

Курсор повинен стояти в будь-якому осередку зведеного звіту.

або:

Права кнопка миші - оновити.

щоб налаштувати автоматичне оновлення зведеної таблиці при зміні даних, робимо по інструкції:

Зміна структури звіту

Додамо в зведену таблицю нові поля:

Після зміни діапазону в зведенні з'явилося поле «Продажі».

Як додати в зведену таблицю обчислюване поле?

Іноді користувачеві недостатньо даних, що містяться в зведеній таблиці. Міняти вихідну інформацію не має сенсу. У таких ситуаціях краще додати обчислюється (призначене для користувача) поле.

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

Інструкція по додаванню призначеного для користувача поля:

Угруповання даних у зведеному звіті

Для прикладу порахуємо витрати на товар в різні роки. Скільки було витрачено коштів у 2012, 2013, 2014 і 2015. Угрупування за датою в зведеній таблиці Excel виконується наступним чином. Для прикладу зробимо просту зведену за датою поставки та суму.

Клацаємо правою кнопкою миші по будь-яку дату. Вибираємо команду «Групувати».

У діалозі задаємо параметри угруповання. Початкова і кінцева дата діапазону виводяться автоматично. Вибираємо крок - «Роки».

Отримуємо суми замовлень по роках.

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

Зіткнувся нещодавно з проблемою:

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

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

Сводние_

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

Після створення зведеної таблиці і налаштування її структури виділіть всю таблицю і скопіюйте її в буфер обміну. Далі перейдіть на вкладку Головна (Ноті) і клацніть на кнопці Вставка (Insert), а потім виберіть у спадному меню команду Вставити значення (Insert Values), як показано на рис. 6.19.

Тим самим ви видалите зведену таблицю і замініть її статичними значеннями, отриманими на основі останнього стану зведеної таблиці. Отримані значення стають основою створюваної згодом зведеної таблиці.

Рис 6.19. Команда Вставити значення застосовується для отримання на основі зведеної таблиці звичайної таблиці зі статичними значеннями

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

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

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

Створення зведеної Ексель таблиці вимагає дотримання певних умов:

Дані вписуються в таблицю, де є стовпці і списки з назвами.
Відсутність незаповнених форм.
Відсутність прихованих об'єктів.

Як зробити зведену таблицю в excel: покрокова інструкція

Для створення зведеної таблиці необхідно:

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

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

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

Вибираємо конкретного продавця. Затискаємо мишку і переносимо поле «Продавець» в «Фільтр звіту». Нове поле відзначається галочкою, і вид таблиці трохи змінюється.

Категорію «Товари» ми поставимо в вигляді рядків. В «Назви рядків» ми переносимо необхідне нам поле.

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

Стовпець «Одиниці», будучи в головній таблиці, відображав кількість товару проданого певним продавцем за конкретною ціною.

Для відображення продажів, наприклад, по кожному місяцю, потрібно поле «Дата» поставити на місце «Назви стовпців». Виберіть команду «Групувати», натиснувши на дату.

Вказуємо періоди дати і крок. Підтверджуємо вибір.

Бачимо таку таблицю.

Зробимо перенесення поля «Сума» до області «Значення».

Стало видно відображення чисел, а нам необхідний саме числовий формат

Для виправлення, виділимо осередки, викликавши вікно мишкою, виберемо «Числовий формат».

Числовий формат ми вибираємо для наступного вікна і відзначаємо «Роздільник груп розрядів». Підтверджуємо кнопкою «ОК».

Оформлення зведеної таблиці

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

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

Окремо настроюються і параметри поля. На прикладі ми бачимо, що певний продавець Рома в конкретному місяці продав сорочок на конкретну суму. Натисканням мишки ми в рядку «Сума по полю ...» викликаємо меню і вибираємо «Параметри полів значень».

Далі для зведення даних в поле вибираємо «Кількість». Підтверджуємо вибір.

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

Тепер міняємо таблицю і робимо так, щоб фільтр спрацьовував по місяцях. Поле «Дата» ми переносимо в «Фільтр звіту», а там де «Назви стовпців», буде «Продавець». Таблиця відображає весь період продажів або за конкретний місяць.

Виділення осередків у зведеній таблиці призведе до появи такої вкладки як «Робота зі зведеними таблицями», а в ній будуть ще дві вкладки «Параметри» і «Конструктор».

Насправді розповідати про налаштування зведених таблиць можна ще дуже довго. Проводьте зміни під свій смак, домагаючись зручного для вас користування. Не бійтеся натискати і експериментувати. Будь-яка дія ви завжди зможете змінити натисканням сполучення клавіш Ctrl + Z.

Сподіваюся, ви засвоїли весь матеріал, і тепер знаєте, як зробити зведену таблицю в excel.

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

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

Зведена таблиця в Excel

Для прикладу використовуємо таблицю реалізації товару в різних торгових філіях.

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

Найраціональніше рішення - це створення зведеної таблиці в Excel:

Виділяємо осередок А1, щоб Excel знав, з якою інформацією доведеться працювати.
В меню «Вставка» вибираємо «Зведена таблиця».
Відкриється меню «Створення зведеної таблиці», де вибираємо діапазон і вказуємо місце. Так як ми встановили курсор в осередок з даними, поле діапазону заповниться автоматично. Якщо курсор стоїть в порожньому осередку, необхідно прописати діапазон вручну. Зведену таблицю можна зробити на цьому ж аркуші або на іншому. Якщо ми хочемо, щоб зведені дані були на існуючій сторінці, не забувайте вказувати для них місце. На сторінці з'являється наступна форма:
Сформуємо табличку, яка покаже суму продажів по відділах. У списку полів зведеної таблиці вибираємо назви стовпців, які нас цікавлять. Отримуємо підсумки по кожному відділу.

Просто, швидко і якісно.

Важливі нюанси:

Перший рядок заданого для зведення даних діапазону повинна бути заповнена.
У базовій табличці кожен стовпець повинен мати свій заголовок - простіше налаштувати зведений звіт.
В Excel як джерело інформації можна використовувати таблиці Access, SQL Server та ін.

Як зробити зведену таблицю з декількох таблиць

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

Порядок створення зведеної таблиці з декількох листів такий же.

Створимо звіт за допомогою майстра зведених таблиць:

Викликаємо меню «Майстер зведених таблиць і діаграм». Для цього клацаємо кнопку настройки панелі швидкого доступу і натискаємо «Інші команди». Тут на вкладці «Налаштування» знаходимо «Майстер зведених таблиць». Додаємо інструмент в панель швидкого доступу. Після додавання:
Ставимо курсор на першу табличку і натискаємо інструмент «Майстри». У вікні, відзначаємо, що створити таблицю хочемо в «декількох діапазонах консолідації». Тобто нам потрібно об'єднати декілька місць з інформацією. Вид звіту - «зведена таблиця». «Далі».
Наступний етап - «створити поля». «Далі».
Прописуємо діапазон даних, за якими будемо формувати зведений звіт. Виділяємо перший діапазон разом з шапкою - «додати». Другий діапазон разом з назвою стовпців - знову «додати».
Тепер в списку вибираємо перший діапазон. Ставимо галочку у одиниці. Це перше поле зведеного звіту. Даємо йому ім'я - «Магазин 1». Виділяємо другий діапазон даних - ставимо галочку у цифри «2». Назва поля - «Магазин 2». Натискаємо «Далі».
Вибираємо, де розташувати зведену таблицю. На існуючому аркуші або новому. Краще вибрати новий лист, щоб не було накладень і зсувів. У нас вийшло так:

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

Як працювати зі зведеними таблицями в Excel

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

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

Додамо в зведену таблицю ще одне поле для звіту. Для цього встановимо галочку навпроти «Дати» (або навпаки «Товару»). Звіт відразу змінюється - з'являється динаміка продажів по днях в кожному відділі.

Згрупуємо дані в звіті по місяцях. Для цього клацаємо правою кнопкою миші по полю «Дата». Натискаємо «Групувати». Вибираємо «по місяцях». Виходить зведена таблиця такого виду:

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

А ось що вийде, якщо ми приберемо «дату» і додамо «відділ»:

А ось такий звіт можна зробити, якщо перетягнути поля між різними областями:

Щоб назва рядка зробити назвою стовпчика, вибираємо цю назву, клацаємо по спливаючому меню. Натискаємо «перемістити в назву стовпців». Таким способом ми перемістили дату в стовпці.

Поле «Відділ» ми проставили перед найменуваннями товарів. Скориставшись розділом меню «перемістити в початок».

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

У меню, вибираємо поле з даними, які необхідно показати.

Коли натискаємо на зведену таблицю, стає доступною закладка з параметрами звіту. З її допомогою можна змінювати заголовки, джерела даних, групувати інформацію.

Перевірка правильності виставлених комунальних рахунків

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

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

Для прикладу ми зробили зведену табличку тарифів для Москви:

Для навчальних цілей візьмемо сім'ю з 4 чоловік, які проживають в квартирі 60 кв. м. Щоб контролювати комунальні платежі, необхідно створити таблиці для розрахунку на кожен місяць.

Перший стовпець \u003d на одну зі зведеної таблиці. Другий - формула для розрахунку виду:

Тариф * кількість осіб / показання лічильника / площа

Наші формули посилаються на лист, де розташована зведена таблиця з тарифами.

Завантажити все приклади зведеної таблиці

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

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

На малюнку показаний принцип, який я описав. Тобто в діапазоні A2: E5 знаходиться вихідна зведена таблиця, яка перетворюється в список даних (діапазон H2: J14). Друга таблиця представляє той же набір даних, тільки в іншому ракурсі. Кожне значення вихідної зведеної таблиці виглядає у вигляді рядка, що складається з пункту поля рядка, поля стовпця і відповідного їм значення. Таке відображення даних буває корисно, коли необхідно впорядкувати і маніпулювати даними іншими способами.

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

Перейдіть по вкладці Файл -\u003e Параметри. У діалоговому вікні параметриExcel,у вкладці Панель швидкого доступув лівому полі знайдіть пункт Майстер зведених таблиць і діаграмі додайте його в правий. Натисніть ОК.

Тепер на панелі швидкого доступу у вас з'явився новий значок.

Клацніть по цій вкладці, щоб запустити Майстер зведених таблиць.

На першому кроці майстра необхідно вибрати тип джерела даних зведеної таблиці. встановлюємо перемикач У декількох діапазонах консолідації і тиснемо Далі.

На кроці 2а вкажіть, як слід створювати поля сторінки. помістіть перемикач Створити поля сторінки -\u003e Далі.

На кроці 2б, в поле Діапазон виберіть діапазон, що містить дані, і клацніть Додати.У нашому випадку це буде місце розташування вихідної зведеної таблиці A1: E4.

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

Excel створить зведену таблицю з даними. У лівій частині екрана ви побачите область Список полів зведеної таблиці.Приберіть всі пункти з полів рядків і стовпців. Більш докладно про редагування полів рядків і стовпців в зведеній таблиці я писав у попередній статті.

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

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

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

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

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

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

Дізнайтеся про стандартні сценарії перетворення зведені таблиці в формули листа

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

Зміна розташування і видалення осередків

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

Вставка рядків або стовпців

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

Використання декількох джерел даних

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

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

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

Створення неоднорідного макета рядків або стовпців (асиметричне створення звіту)

Припустимо, вам потрібно створити звіт, що містить 2008 стовпець з ім'ям фактичні продажів 2009 стовпець з ім'ям прогнозованого продажів, але ви не хочете інші стовпці. Можна створити звіт, що містить тільки ті стовпці, на відміну від зведеної таблиці, яке вимагатиме симетричну звітів.

Створення власних формул куба і багатовимірних виразів

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

Перетворення осередки в формули, що використовують функцію куба

Примітка: Тільки за допомогою цієї процедури можна перетворити зведену таблицю інтерактивної аналітичної обробки (OLAP).

Перетворення осередки в формули, що використовують функцію ПОЛУЧІТЬ.ДАННИЕ.СВОДНОЙ.ТАБЛІЦИ

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

Переконайтеся, що доступна команда створити GetPivotData у групі Зведена таблиця на вкладці параметри.

Примітка: команда створити GetPivotData управляє параметром Використовувати функції GetPivotData для посилань в зведеній таблиці в категорії формули розділу Робота з формулами в діалоговому вікні параметри Excel.

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

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

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

Завершіть введення формули і натисніть клавішу ENTER.

Примітка: У разі видалення зі звіту будь-який з осередків, на які посилається формула ПОЛУЧІТЬ.ДАННИЕ.СВОДНОЙ.ТАБЛІЦИ формула повертає помилку # ПОСИЛАННЯ !.

Отже, таблиця в Excel - це прямокутна область листа, в якій кожен рядок являє собою набір даних, а в осередку на перетині цього рядка і кожного стовпця знаходиться одиниця даних. Кожному стовпцю присвоюється унікальне ім'я. Стовпці таблиці називаються полями, А рядки - записами. У таблиці не може бути записів, в яких немає даних ні в одному полі.

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

Виділити будь-яку клітинку, яка містить дані, які повинні будуть увійти в таблицю.
У стрічці меню вибрати вкладку вставка , В розкрилася групі команд таблиці необхідно вибрати команду Таблиця .

З'явиться діалогове вікно, в якому Excel автоматично запропонує межі діапазону даних для таблиці

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

Присвоєння імені таблиці

За замовчуванням при створенні таблиці Excel їй присвоюється стандартне ім'я: Таблица1, Таблица2 і т.д. Якщо є тільки одна таблиця, то можна обмежитися цим ім'ям. Але зручніше привласнити таблиці змістовне ім'я.

Виділити комірку таблиці.
на вкладці конструктор , у групі властивості ввести нове ім'я таблиці в поле ім'я таблиці натиснути клавішу Enter.

Вимоги до імен таблиць аналогічні вимогам до іменованих діапазонів.

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

Виділити комірку таблиці.
на вкладці конструктор вибрати потрібний оформлення в групі стилі таблиць .

Обчислення в таблицях

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

на вкладці конструктор у групі Параметри стилів таблиць , вибрати рядок підсумків .

У що з'явилася нової рядку підсумок вибрати поле, в якому потрібно обробити дані, і в спадному меню вибрати потрібну функцію.

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

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

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

Якщо замість імені поля на екрані в формулах вказані адреси осередків, необхідно змінити налаштування:

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

Перетворення таблиці в звичайний діапазон

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

на вкладці конструктор вибрати групу сервіс .
вибрати вкладку Перетворити в діапазон .

Натиснути на кнопку Так .