Сьогодні давня техніка називається Консолідація даних. Якщо вам потрібно поєднати дані з декількох аркушів або кількох книг, Consolidation може впоратися із завданням, якщо ваші дані мають певний формат. Дізнайтеся більше про цей старовинний інструмент.
У Excel є два стародавні інструменти консолідації.
Щоб зрозуміти їх, скажіть, що у вас є три набори даних. У кожного є імена в лівій частині та місяці вгорі. Зверніть увагу, що імена різні, і в кожному наборі даних є різна кількість місяців.
Ви хочете об'єднати їх в єдиний набір даних. Перегортайте сторінку для обговорення цих двох методів.
Першим інструментом є команда Consolidate на вкладці Data. Виберіть порожній розділ книги перед запуском команди. За допомогою кнопки RefEdit наведіть вказівник на кожен із ваших наборів даних, а потім натисніть Додати. У нижньому лівому куті виберіть верхній рядок та ліву колонку.
Після натискання кнопки ОК створюється набір усіх трьох наборів даних. Перший стовпець містить будь-яке ім'я в будь-якому з трьох наборів даних. Рядок 1 містить будь-який місяць у будь-якому наборі даних.
На наведеному малюнку зверніть увагу на три досади. Клітинку А1 завжди залишають порожньою. Дані в А не сортуються. Якщо в наборі даних відсутня людина, клітини залишаються порожніми, замість того, щоб заповнювати їх 0.
Заповнити комірку А1 досить просто. Сортування за назвою передбачає використання Flash Fill для отримання прізвища у стовпці N. Ось як заповнити порожні клітинки 0:
- Виділіть усі комірки, які повинні мати номери: B2: M11.
- Виберіть «Дім», «Знайти та вибрати», «Перейти до спеціального».
- Виберіть «Пробіли», а потім натисніть «OK». Вам залишаться всі вибрані порожні клітинки.
-
Введіть 0, а потім Ctrl + Enter.
Перейти до спеціального
Результат: добре оформлений підсумковий звіт.
Переглянути відео
- Консолідація - давня функція в Excel
- Вкажіть кілька діапазонів для консолідації
- Використовуйте мітки у верхньому рядку та лівому стовпці
- Прикрощі: A1 завжди пустий, стовпець A не сортується, порожні дані
- Перейдіть до Special, Blanks, 0, Ctrl + Enter
- Consolidate може вказувати на зовнішні робочі книги
Стенограма відео
Дізнайтеся Excel з подкасту, серія 2046 - Об’єднайте робочі аркуші!
Я підкастую всі мої поради з цієї книги, натисніть "i" у верхньому правому куті, щоб перейти до списку відтворення!
Добре, у мене є дивовижний, дивовижний старий давній фокус. У мене тут є три набори даних, Q1, Q2, Q3, всі вони мають однакову форму, і що вони мають імена внизу ліворуч, місяці проходять по верху, але не зовсім однакові. Q1 має січень-лютий-березень, Q2 має 5 місяців, я думаю, ми полінувались і не зрозуміли це робити наприкінці червня, а хтось нарешті зробив це наприкінці серпня, а потім у Q4 4 місяці. Гаразд, 1-й переходить A: D, 2-й A: F, 3-й A: E. Різні імена, деякі імена однакові, як у всіх Майкл Сілі, але інші імена приходять і йдуть. Гаразд, це дивовижна особливість, вона існує вічно. Я пам’ятаю, як це робив ще в 1995 році, навіть “Дані, консолідується”!
Добре, ми будемо використовувати функцію SUM, я ніколи не використовував жодної з інших, але, мабуть, вони там. Функція SUM, перше, що ми збираємося зробити, це повернутися до Q1 і вказати на цей діапазон, ці чотири стовпці, клацнути Додати, а потім перейти до Q2, виділити ці стовпці, клацнути Додати, а потім Q4, вибрати ці стовпці . Добре, поставте прапорець біля пункту Використовувати мітки у верхньому рядку та лівому стовпці, що кнопка «Огляд» означає, що ці набори даних можуть бути в різних книгах! Створіть посилання на вихідні дані, про що ми поговоримо наприкінці. Коли я натискаю кнопку ОК, вони збираються отримати кожне ім'я, яке є в будь-якому з 3-х списків, місяці в будь-якому з 3-х списків, і тепер у нас є ця дивовижна надмножина, гаразд, досади!
Це чудова функція, але ось речі, які мене просто зламали. Вони не дають мені мітку в А1, вони не заважають сортувати дані, що йдуть вниз, і якщо хтось не мав запису в першому, вони дають мені порожні місця замість нулів. Добре, щоб заповнити порожні місця нулями, «Домашня сторінка», «Знайти та вибрати», «Перейти до спеціального», виберіть «Пробіли», натисніть «ОК», введіть нуль, Ctrl + Enter заповнить їх. Досить просто сортувати дані, дані, AZ та він сортуватиме дані, добре. Створюйте посилання, боже, це ніколи не працює правильно, добре, створюйте посилання, щоб «Створити посилання» працювало, це повинно бути у зовнішній книзі. Гаразд, отже, я збираюся натиснути "Огляд тут", я створив робочу книгу під назвою "Інша робоча книга", а дані містяться у форматі A1: D7, натисніть "Додати", гаразд, і є перша. Наступні дані містяться в G1: L8, тому я перегляну Іншу книгу, G1: L8,натисніть Додати, добре. Тож тепер у мене є два посилання на інші книги, верхній рядок, лівий стовпець, створення посилань на вихідні дані. Довідка Excel говорить, що коли ви використовуєте Створити посилання на вихідні дані, ви ніколи не зможете редагувати ці діапазони, знову натисніть кнопку ОК, і ось що ми отримаємо.
Добре, по-перше, схоже, це дало нам результати, насправді дало нам результати, але тут є додаткова колонка B, і у нас є Group and Outline. І коли ми переходимо до подання номер 2, ах. Отже, в ідеалі у вас була б одна книга, яка називається січень, а інша книга лютий, і вона покаже вам, що тут січень, ось лютий. Ось загальна сума для Майка Сілі, це формули, що вказують на ці продажі, а потім ось сума цих двох, добре, це дивно.
Якщо ви насправді цим користуєтесь постійно, я хочу почути вас у коментарях на YouTube, я впевнений, що це простіший спосіб зробити це для мене, ніколи в житті цього не робив, колись і сьогодні, і сьогодні сьогодні, щоб я міг це пояснити, добре. Однак консолідуйте, коли ми консолідуємо аркуші з поточної книги, дивовижний, приголомшливий фокус. Завтра ми збираємося порівняти діапазон багаторазової консолідації, зведені таблиці, але всі ці трюки є в книзі, натисніть на «i» у верхньому правому куті, щоб перейти до цієї книги.
Консолідуй, давня, давня функція в Excel, ти визначаєш кілька діапазонів для консолідації, я завжди встановлюю прапорець для верхнього рядка та лівого стовпця. Результати чудові, але A1 порожній, стовпець A не сортується, а в даних є порожні місця, використовуйте Перехід до спеціальних пробілів, введіть 0, Ctrl + Enter, щоб заповнити ці порожні місця. І тоді той останній приклад там, consolidate, може вказувати на зовнішні книги, може бути корисним!
Гаразд, привіт, я хочу подякувати за заїзд, ми побачимось наступного разу для чергової трансляції від!
Завантажити файл
Завантажте зразок файлу тут: Podcast2046.xlsm
