Розрив зв'язку із зовнішнім ресурсом в Excel

Примітка: Відсутність команди Змінити зв'язки означає, що файл не містить пов'язаних даних.

• Щоб виділити кілька пов'язаних об'єктів, утримуючи клавішу CTRL, натисніть кожний пов'язаний об'єкт.
• Щоб виділити всі зв'язки, натисніть клавіші CTRL+A.

ТБД. Видалення імені певного посилання

Якщо посилання використовувало певне ім'я, ім'я автоматично не видаляється. Ви також можете видалити ім'я, виконавши такі дії:

1. На вкладці Формули у групі Певні імена натисніть кнопку Диспетчер імен.
2. У діалоговому вікні Диспетчер імен клацніть ім'я, яке потрібно змінити.
3. Клацніть ім'я, щоб виділити його.
4. Натисніть кнопку Видалити.
5. Натисніть кнопку ОК.

Додаткові відомості

Ви завжди можете поставити запитання експерту в Excel Tech Community або отримати підтримку у спільнотах.

Робота зі зведеними таблицями в Excel на прикладах

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

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

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

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

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

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

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

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

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

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

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

1. У комірці-мішені (там, куди переноситиметься таблиця) ставимо курсор. Пишемо = - переходимо на аркуш з даними, що переносяться – виділяємо перший осередок стовпця, який копіюємо. Введення. "Розмножуємо" формулу, простягаючи вниз за правий нижній кут комірки.
2. За таким самим принципом переносимо інші дані. У результаті двох таблиць отримуємо одну загальну.
3. Тепер створимо зведений звіт. Вставка - зведена таблиця - вказуємо діапазон та місце - ОК.

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

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

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

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

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

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

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

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

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

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

1. Курсор стоїть у будь-якому місці звіту. Робота зі зведеними таблицями – Параметри – Зведена таблиця.
2. Параметри.
3. У діалозі, що відкрився – Дані – Оновити при відкритті файлу – ОК.

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

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

1. На аркуші з вихідними даними вставляємо стовпець «Продаж». Тут ми відобразимо, який виторг отримає магазин від реалізації товару. Скористаємося формулою – ціна за 1* кількість проданих одиниць.
2. Переходимо на аркуш зі звітом. Робота зі зведеними таблицями – параметри – змінити джерело даних. Розширюємо діапазон інформації, яка має увійти до зведеної таблиці.

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

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

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

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

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

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

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

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

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

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

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

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

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

• Створити таблицю
• Форматування
• Функції Excel
• Формули та діапазони
• Фільтр та сортування
• Діаграми та графіки
• Зведені таблиці
• Друк документів
• Бази даних та XML
• Можливості Excel
• Налаштування параметри
• Уроки Excel
• Макроси VBA
• Завантажити приклади