Сегодня существует древняя методика под названием консолидация данных. Если вам нужно объединить данные из нескольких листов или нескольких книг, Consolidation может обработать задание, если ваши данные имеют определенный формат. Узнайте больше об этом древнем инструменте.
В Excel есть два древних инструмента консолидации.
Чтобы понять их, представьте, что у вас есть три набора данных. У каждого есть имена внизу слева и месяцы вверху. Обратите внимание, что имена разные, и в каждом наборе данных разное количество месяцев.
Вы хотите объединить их в единый набор данных. Переверните страницу, чтобы обсудить эти два метода.
Первый инструмент - это команда «Консолидировать» на вкладке «Данные». Перед запуском команды выберите пустой раздел книги. Используйте кнопку RefEdit, чтобы указать на каждый из ваших наборов данных, а затем нажмите Добавить. В левом нижнем углу выберите верхний ряд и левый столбец.
Когда вы нажимаете OK, создается расширенный набор всех трех наборов данных. Первый столбец содержит любое имя в любом из трех наборов данных. Строка 1 содержит любой месяц в любом наборе данных.
Обратите внимание на три неприятных момента на приведенном выше рисунке. Ячейка A1 всегда остается пустой. Данные в A не отсортированы. Если человек отсутствовал в наборе данных, тогда ячейки остаются пустыми, а не заполняются 0.
Заполнить ячейку A1 достаточно просто. Сортировка по имени включает использование Flash Fill, чтобы получить фамилию в столбце N. Вот как заполнить пустые ячейки 0:
- Выделите все ячейки, которые должны иметь числа: B2: M11.
- Выберите Дом, Найти и выбрать, Перейти к специальному.
- Выберите «Пробелы» и нажмите «ОК». У вас останутся выделенными все пустые ячейки.
-
Введите 0, а затем Ctrl + Enter.
Перейти к специальному
Результат: красиво оформленный итоговый отчет.
Смотреть видео
- Консолидация - древняя функция Excel
- Укажите несколько диапазонов для объединения
- Используйте ярлыки в верхней строке и левом столбце
- Раздражения: A1 всегда пусто, столбец A не отсортирован, в данных есть пробелы
- Перейти к Special, Пробелы, 0, Ctrl + Enter
- Consolidate может указывать на внешние книги
Стенограмма видео
Изучите Excel из подкаста, выпуск 2046 - Объедините рабочие листы!
Я буду подкастировать все свои советы из этой книги, нажмите «i» в правом верхнем углу, чтобы перейти к списку воспроизведения!
Хорошо, у меня есть классный, классный старый древний трюк. У меня есть три набора данных: Q1, Q2, Q3, все они имеют похожую форму, и что у них есть имена внизу слева, месяцы идут вверху, но не совсем одинаковой формы. В первом квартале январь-февраль-март, во втором квартале - 5 месяцев, я думаю, мы поленились и не придумали, как это сделать в конце июня, и кто-то наконец сделал это в конце августа, а в четвертом квартале есть 4 месяца. Хорошо, первый идет A: D, второй - A: F, третий - A: E. Разные имена, некоторые имена одинаковые, например, Майкл Сили присутствует во всех них, но другие имена приходят и уходят. Хорошо, это потрясающая функция, она существует всегда. Я помню, как делал это еще в 1995 году, Data, Consolidates!
Хорошо, мы собираемся использовать функцию СУММ, я никогда не использовал другие, но я думаю, они есть. Функция СУММ, первое, что мы собираемся сделать, это вернуться к Q1 и указать на этот диапазон, эти четыре столбца, щелкнуть Добавить, а затем перейти к Q2, выбрать эти столбцы, нажать Добавить, а затем Q4, выбрать эти столбцы . Хорошо, установите этот флажок для использования меток в верхней строке и левом столбце, эта кнопка «Обзор» означает, что эти наборы данных могут находиться в разных книгах! Создайте ссылки на исходные данные, об этом мы поговорим в конце. Когда я нажимаю ОК, они получат каждое имя, которое есть в любом из 3 списков, месяцы в любом из 3 списков, и теперь у нас есть этот удивительный суперсет, хорошо, неприятности!
Это отличная функция, но вот вещи, которые меня просто взломали. Мне не дают метку в A1, они не утруждают себя сортировкой данных по убыванию, а если у кого-то нет записи в первой, они дают мне пробелы вместо нулей. Хорошо, чтобы заполнить пустые поля нулями, нажмите Home, Find and Select, Go To Special, выберите пустые поля, нажмите OK, введите ноль, Ctrl + Enter заполнит их. Достаточно легко отсортировать данные, Data, AZ и он отсортирует данные, хорошо. Создавайте ссылки, боже, это никогда не работает правильно, хорошо, создавайте ссылки, для работы Create Links это должно быть во внешней книге. Хорошо, я собираюсь нажать «Обзор» здесь, я создал рабочую книгу под названием OtherWorkbook, и данные находятся в формате A1: D7, нажмите «Добавить», хорошо, и вот первая книга. Следующие данные находятся в G1: L8, поэтому я просматриваю OtherWorkbook, G1: L8,нажмите "Добавить", хорошо. Итак, теперь у меня есть две ссылки на другие книги: верхняя строка, левый столбец, создание ссылок на исходные данные. В справке Excel сказано, что после использования ссылки «Создать» для исходных данных вы никогда не сможете редактировать эти диапазоны, снова нажмите «ОК», и вот что мы получим.
Хорошо, во-первых, похоже, что он дал нам результаты, он дал нам результаты, но здесь есть дополнительный столбец B, и у нас есть Group и Outline. А когда мы переходим к просмотру номер 2, ах. Итак, в идеале у вас должна быть одна рабочая тетрадь под названием «Январь», а другая рабочая тетрадь - под названием «Февраль», и она покажет вам здесь январь, а здесь февраль. Вот сумма для Майка Сили, это формулы, указывающие на те продажи, а затем вот сумма этих двух, хорошо, это странно.
Если вы действительно используете это все время, я хочу услышать от вас в комментариях на YouTube, я уверен, что есть более простой способ сделать это для меня, никогда не делал этого в своей жизни, хорошо однажды до сегодняшнего дня, а затем сегодня, просто так, чтобы я мог это объяснить, хорошо. Однако консолидировать, когда мы объединяем листы из текущей книги, отличный трюк. Завтра мы собираемся сравнить несколько диапазонов консолидации, сводные таблицы, но все эти приемы есть в книге, щелкните «i» в правом верхнем углу, чтобы перейти к этой книге.
Консолидация, древняя, древняя функция в Excel, вы указываете несколько диапазонов для консолидации, я всегда устанавливаю флажок для верхней строки и левого столбца. Результаты отличные, но A1 пуст, столбец A не отсортирован, а в данных есть пробелы, используйте Go To Special Blanks, введите 0, Ctrl + Enter, чтобы заполнить эти пробелы. И последний пример, консолидация может указывать на внешние книги, может быть полезен!
Хорошо, эй, я хочу поблагодарить за то, что зашел, увидимся в следующий раз на другом сетевом трансляции от!
Скачать файл
Загрузите образец файла здесь: Podcast2046.xlsm
