Як зробити зведену таблицю в Excel 2003, 2007, 2010 з формулами: покрокова інструкція

Люди, які хоч раз шукали роботу через інтернет і не тільки, напевно часто натрапляли на вакансії, де основною вимогою до претендента було впевнене знання пакету програм Microsoft Office, і зокрема програми Microsoft Excel.

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

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

Покрокова інструкція по створенню зведених таблиць з формулами в програмі Microsoft Excel

Що таке зведені таблиці в Excel і для чого вони потрібні?

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

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

Малюнок 1. Що таке зведені таблиці в Excel і для чого вони потрібні?

• Погодьтеся, такий графічний спосіб представлення даних дуже простий і зручний для читання? Однак на графічної складової зручності зведеної таблиці не закінчуються.
• Якщо уважно подивитися, то в шапці таблиці поряд з кожним заголовком можна помітити стрілку. Якщо на неї натиснути, то можна буде впорядкувати дані таблиці за обраними параметрами.
• Наприклад, можна вибрати тільки продавця Катю і подивитися продані нею товари, дати продажу і суми. Точно так само можна впорядкувати продажу по датам. Вибравши дату, Ви побачите продавців, які в той день продали товари і загальну суму денної виручки.

Що таке формули в Excel і для чого вони потрібні?

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

Для формул в Microsoft Excel застосовуються стандартні математичні оператори:

Малюнок 1. Що таке формули в Excel і для чого вони потрібні?

• Під час множення завжди необхідно використовувати знак « *». Його упущення, як в письмовому варіанті обчислень, неприпустимо. Іншими словами, якщо Ви запишіть (4+7)8, То даний запис програма не зможе розпізнати, так як після круглої дужки не варто знак множення « *».
• Програма Excel багатьма використовується замість калькулятора. Наприклад, якщо Ви введете в рядок формул 2+3, То в вибраній комірці відобразиться результат 5.

Малюнок 2. Що таке формули в Excel і для чого вони потрібні?

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

Малюнок 3. Що таке формули в Excel і для чого вони потрібні?

Створення бази даних для внесення її в зведену таблицю Excel 2003, 2007, 2010

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

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

Крок 1.

• Запустіть програму. На головній сторінці Ви побачите стандартне поле з осередками, куди необхідно буде водити нашу базу даних.
• Для початку необхідно створити чотири основних стовпця з заголовками.
• Для цього двічі клацніть по одній з верхніх осередків і впишіть в неї назва першого заголовка « Продавець" та натисніть Enter.
• Далі виконайте те ж саме з трьома сусідніми осередками в тому ж рядку, вписуючи в них назви заголовків.

Малюнок 1. Створення бази даних для внесення її в зведену таблицю Excel 2003, 2007, 2010

крок 2.

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

Малюнок 2. Створення бази даних для внесення її в зведену таблицю Excel 2003, 2007, 2010

крок 3.

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

Малюнок 3. Створення бази даних для внесення її в зведену таблицю Excel 2003, 2007, 2010

Як зробити зведену таблицю в Excel 2003, 2007, 2010 з формулами?

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

Крок 1.

• Насамперед необхідно виділити одну з комірок наявної у Вас стартовою таблиці. Далі в лівому верхньому кутку перейдіть на вкладку « вставка»І клацніть на кнопці« Зведена таблиця».

Малюнок 1. Як зробити зведену таблицю в Excel 2003, 2007, 2010 з формулами?

крок 2.

• Перед Вами відкриється віконце, в якому потрібно вибрати діапазон або ввести назву таблиці.
• Також у вікні є можливість вибору місця для звіту. Створимо його на новому аркуші.
• Позначте точками пункти, які вказані на скріншоті нижче і натисніть кнопку « ОК».

Малюнок 2. Як зробити зведену таблицю в Excel 2003, 2007, 2010 з формулами?

крок 3.

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

Малюнок 3. Як зробити зведену таблицю в Excel 2003, 2007, 2010 з формулами?

крок 4.

• Далі необхідно вирішити, що ми хочемо проаналізувати. Наприклад, ми хочемо зрозуміти, який саме продавець, продав який товар за певний період часу і на яку суму. Фільтрація даних у таблиці буде здійснюватися по продавцях.
• У правій колонці виберіть заголовок « Продавець», Затисніть на ньому ліву кнопку мишки і перетягніть в область« Фільтр звіту».
• Як можна помітити, зовнішній вигляд таблиці змінився, заголовок став зазначеним.

Малюнок 4. Як зробити зведену таблицю в Excel 2003, 2007, 2010 з формулами?

крок 5.

• Далі по тій же схемі в правій колонці необхідно затиснути ліву кнопку мишки на заголовку « Товари»І перетягнути його в область« назви рядків».

Малюнок 5. Як зробити зведену таблицю в Excel 2003, 2007, 2010 з формулами?

крок 6.

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

Малюнок 6. Як зробити зведену таблицю в Excel 2003, 2007, 2010 з формулами?

• У вікні, потрібно вибрати період угруповання (з якого числа по яке), і вибрати крок зі списку. Після чого слід натиснути « ОК».

Малюнок 7. Як зробити зведену таблицю в Excel 2003, 2007, 2010 з формулами?

• Далі Ваша таблиця перетворюється таким ось чином.

Малюнок 8. Як зробити зведену таблицю в Excel 2003, 2007, 2010 з формулами?

крок 7.

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

Малюнок 9. Як зробити зведену таблицю в Excel 2003, 2007, 2010 з формулами?

• Щоб поправити цю проблему, виділіть в зведеній таблиці необхідні комірки, клацніть правою кнопкою мишки зі списку клацніть по пункту « числовий формат».

Малюнок 10. Як зробити зведену таблицю в Excel 2003, 2007, 2010 з формулами?

• У віконці в розділі « умовні формати»Виберіть пункт« числовий»І за бажанням можете відзначити галочкою пункт« Роздільник груп розрядів».
• Далі натисніть « ОК». Як можна помітити, прості числа змінилися і тепер відображаються не просто 650, а 650,00.

Малюнок 11. Як зробити зведену таблицю в Excel 2003, 2007, 2010 з формулами?

крок 8.

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

Малюнок 12. Як зробити зведену таблицю в Excel 2003, 2007, 2010 з формулами?

• Точно таким же способом можна фільтрувати рядки і стовпці. Наприклад, найменування товарів та дати.
• Відзначивши галочками поля « штани»І« костюм», Легко з'ясувати, скільки з їх продажу вийшло виручки в цілому, або конкретними продавцями.

Малюнок 13. Як зробити зведену таблицю в Excel 2003, 2007, 2010 з формулами?

• Також в графі « значення»Можна задати певні параметри заголовку. На даний момент там встановлено параметр « сума».
• Тобто, якщо подивитися на таблицю, можна побачити, що продавцем Ромою в лютому було продано сорочок на загальну суму 1 800,00 рублів.
• Для того, щоб дізнатися їх кількість, необхідно клацнути мишкою по пункту « сума»І вибрати з меню рядок« Параметри полів значень».

Малюнок 14. Як зробити зведену таблицю в Excel 2003, 2007, 2010 з формулами?

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

Малюнок 15. Як зробити зведену таблицю в Excel 2003, 2007, 2010 з формулами?

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

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

ВІДЕО: Створення зведеної таблиці в Excel