Будучи частиною моєї роботи на повний робочий день кілька років тому, однією з речей, з якими мені довелося мати справу, було поєднання даних з різних робочих книг, якими поділилися інші люди.
Одним із поширених завдань було об’єднати дані таким чином, щоб не було повторюваних записів.
Наприклад, нижче набір даних, який містить кілька записів для одного регіону.
І кінцевий результат повинен бути зведеним набором даних, де кожна країна подається лише один раз.
У цьому посібнику я покажу вам, як об’єднати повторювані рядки та підсумувати значення для створення єдиного зведеного набору даних.
Об’єднайте та підсумуйте дані за допомогою опції консолідації
Якщо вам потрібно лише консолідувати дані та додати всі значення для повторюваних записів, найкраще скористатися функцією консолідації в Excel.
Іншим методом є використання зведеної таблиці та узагальнення даних (розглянуто далі в цьому підручнику).
Припустимо, у вас є набір даних, як показано нижче, де назва країни повторюється кілька разів.
Хоча це унікальні записи, оскільки вартість продажів відрізняється, для цілей звітності ви можете видалити кілька екземплярів однієї країни та показати вартість продажів як одну зведену суму.
Нижче наведено кроки для цього.
- Скопіюйте заголовки вихідних даних і вставте їх туди, де потрібно консолідовані дані
- Виберіть клітинку під крайнім лівим заголовком
- Перейдіть на вкладку Дані
- У групі Інструменти даних натисніть піктограму Консолідація
- У діалоговому вікні "Об'єднати" виберіть "Сума" у спадному меню функції (якщо ще не вибрано за замовчуванням)
- Натисніть на піктограму вибору діапазону в полі Довідка.
- Виберіть діапазон A2: B9 (дані, крім заголовків)
- Установіть прапорець Лівий стовпець
- Натисніть ОК
Наведені вище кроки об’єднають дані, видаливши повторювані записи та додавши значення для кожної країни.
У підсумку ви отримаєте унікальний список країн разом із вартістю продажів із вихідного набору даних.
Я вирішив отримати SUM значень з кожного запису. Ви також можете вибрати інші варіанти, такі як кількість або середнє значення або макс/хв.
У цьому прикладі я показав вам, як об’єднати дані в одному наборі даних на аркуші. Ви також можете використовувати цю функцію для консолідації даних з кількох аркушів в одній книзі і навіть з кількох різних книг.
Об’єднання та сума даних за допомогою зведених таблиць
Зведена таблиця - це армійський ніж Швейцарії для нарізання та нарізання даних у Excel.
Він може легко дати вам підсумок, який являє собою комбінований набір даних без дублікатів, а також значення, які є сумою всіх подібних записів, і роблять набагато більше.
Недоліком цього методу порівняно з попереднім є те, що цей забирає більше кліків і на кілька секунд більше порівняно з попереднім.
Припустимо, у вас є набір даних, як показано нижче, де назва країни повторюється кілька разів, і ви хочете консолідувати ці дані.
Нижче наведено кроки для створення зведеної таблиці.
- Виберіть будь -яку клітинку в наборі даних
- Перейдіть на вкладку Вставка
- У групі Таблиці натисніть на опцію Зведена таблиця
- У діалоговому вікні Створення зведеної таблиці переконайтеся, що таблиця/діапазон правильні
- Натисніть на існуючий аркуш
- Виберіть місце, куди потрібно вставити отриману зведену таблицю.
- Натисніть OK
Наведені вище кроки вставлятимуть зведену таблицю у вибрану клітинку призначення.
Тепер ми можемо робити різноманітні речі за допомогою зведеної таблиці - включаючи консолідацію нашого набору даних та видалення дублікатів.
Нижче наведено кроки для цього.
- Натисніть будь -де в області зведеної таблиці, і вона відкриє панель зведеної таблиці праворуч
- Перетягніть поле "Країна" в область "Рядок"
- Перетягніть поле «Продажі» в область «Значення»
Наведені вище кроки узагальнюють дані та дають суму продажів для всіх країн.
Якщо це все, що вам потрібно, і у вас немає необхідності у зведеній таблиці, ви можете скопіювати дані та вставити їх як значення десь в іншому місці та видалити зведену таблицю.
Це також допоможе вам зменшити розмір книги Excel.
Отже, це два швидких і простих методу, які можна використовувати для консолідації даних, де вони об’єднуватимуть повторювані рядки та підсумовуватимуть усі значення в цих записах.
Сподіваюся, вам цей підручник був корисним!
