Как в офисе. Как объединить таблицы в excel.

При выполнении определенных задач в Excel иногда необходимо работать с несколькими таблицами, которые к тому же связаны между собой. Это означает, что данные из одной таблицы переносятся в другие таблицы, и при изменении данных значения во всех строках связанных таблиц пересчитываются.

Как объединить ячейки в Excel

Таблицы в Excel основаны на несколько ином принципе, чем в Word. Если в последней ячейки можно разделять, то в Excel ячейка — это неделимая единица. Проблема объединения двух столбцов под общим заголовком встречается довольно часто, поэтому важно знать, как объединить ячейки в Excel, поскольку в этом случае необходимо объединить часть таблицы.

В таблице вы увидите кнопку объединения. Для этого выберите диапазон, который необходимо объединить, и щелкните по нему. Выполняется слияние. Если нажать на стрелку рядом с кнопкой, появится меню. Используйте команду «Объединить и отцентрировать», чтобы отцентрировать содержимое новой области. Если вы попытаетесь объединить диапазон со значением, будут сохранены только данные первого из объединенных адресов. Вы увидите предупреждающее сообщение. Слияние приведет к потере остальных данных. Команда Merge Per Row создает объединенные ячейки в каждой строке. Команда Объединить ячейки аналогична первой команде, за исключением того, что текст не центрируется. Последний пункт отвечает на вопрос, как разгруппировать ячейки в Excel.

Метод 2 – использование меню

В новых версиях Office классическое меню заменено вкладками. А обычные окна отображаются с помощью маленьких значков в правом нижнем углу вкладки. Не забудьте предварительно выделить ячейки, которые нужно объединить, и нажмите на этот значок. Отобразится окно выравнивания. Установите флажок рядом с пунктом «Объединить ячейки». Здесь вы также можете задать параметры выравнивания данных в соответствующих выпадающих полях.

Если вам нужно повторить существующее слияние, вы можете использовать буфер обмена. Комбинация клавиш для объединения ячеек в Excel выглядит следующим образом:

CTRL+C — копирование в буфер обмена. CTR+V — вставить из буфера обмена.

Переместите указатель мыши в выбранную позицию. Скопируйте, нажав CRTL+C. Признаком того, что данные были скопированы, является «бегущий муравей» — покадровая анимация вдоль его границ. Поместите курсор в нужную ячейку. Нет необходимости выбирать одинаковое количество адресов, Excel автоматически выберет нужное количество и выполнит команду. Однако данные будут потеряны. Нажмите клавиши для вставки из буфера. То же самое слияние отображается в выбранном месте.

Метод 4 – формат по образцу

В Excel, как и в любой другой программе пакета Office, есть очень удобная кнопка «Форматировать с помощью шаблона». Он находится в разделе «Буфер обмена». Установите курсор на объединенный блок и нажмите на эту кнопку. Переместите указатель мыши в нужное положение и щелкните. Форматирование воспроизведено полностью. Во время выполнения команды рядом с указателем мыши появляется символ кисти. После щелчка символ исчезает и появляется новый объединенный фрагмент.

Это актуальная проблема. В каждом из предыдущих методов Excel удаляет данные. С помощью функции «Слияние» они сохраняются. Нажмите на символ Fx рядом со строкой формул. Затем отобразится окно Импорт функций.

Введите название «Link» в поле поиска и нажмите на кнопку «Поиск». Выделите найденную функцию и нажмите «OK». Отобразится окно настроек. Установите диапазон вручную или выберите его с помощью мыши. Обратите внимание, что функция должна находиться по адресу, отличному от консолидируемых адресов. Если вам нужно поместить консолидированные данные в объединенную область, предварительно выполните объединение, а затем вставьте туда функцию. Не существует сочетаний клавиш, позволяющих объединять ячейки в Excel без потери данных, поэтому необходимо использовать другие методы.

Еще один способ объединить текст из нескольких ячеек Excel — использовать оператор «&» для объединения строк. Чтобы вставить формулу, установите курсор в то место, где должен появиться новый текст, и нажмите символ «=». Затем нажмите на первый адрес, нажмите &, нажмите на второй и т.д. Объединение текста из двух разных ячеек в одну ячейку в Excel 2003-2010 выполняется аналогичным образом.

Примечания: Решения, описанные в этой статье, являются универсальными. Вы можете адаптировать их для дальнейшего использования с любой стандартной формулой, такой как VLOOKUP, MATCH, HLOOKUP и т.д.

Ключевой столбец в одной из таблиц содержит дополнительные символы

Рассмотрим две таблицы. Столбцы первой таблицы содержат номенклатурный номер (SKU), название пива (Beer) и его цену (Price). Вторая таблица содержит SKU и количество бутылок на складе. Пиво может быть любым продуктом, и в реальной жизни количество колонн может быть гораздо больше.

Объединяем таблицы в Excel

Создайте вспомогательный столбец в таблице с дополнительными символами. Вы можете поместить его в конце таблицы, но лучше всего поместить его справа от ведущей колонки, чтобы он был виден.

Ключевым столбцом таблицы в нашем примере является столбец A с данными SKU, и нам нужно извлечь из него первые 5 символов. Добавим вспомогательный столбец и назовем его SKU helper:

  • Наведите указатель мыши на заголовок столбца B так, чтобы он стал похож на стрелку, направленную вниз:
  • Щелкните правой кнопкой мыши на заголовке и выберите в контекстном меню пункт Вставить:

Объединяем таблицы в Excel

Здесь A2 — адрес ячейки, из которой нужно извлечь символы, а 5 — количество символов, которые нужно извлечь.

Готово! Теперь у нас есть ключевые колонки со значениями точного совпадения — справочная колонка SKU в главной таблице и колонка SKU в таблице, в которой будет выполняться поиск.

Теперь мы используем функцию VLOOKUP, чтобы получить желаемый результат:

Объединяем таблицы в Excel

Другие формулы

  • Извлеките первые X символов с правой стороны: например, 6 символов с правой стороны из записи «DSFH-164900». Тогда формула выглядит следующим образом:

Короче говоря, вы можете использовать такие функции Excel, как LEFT, RIGHT, MID, FIND для извлечения любой части составного индекса. Если у вас возникли проблемы с этим — свяжитесь с нами, мы сделаем все возможное, чтобы помочь вам.

Данные из ключевого столбца в первой таблице разбиты на два или более столбца во второй таблице

Предположим, что искомая таблица содержит столбец идентификаторов. Ячейки этого столбца содержат записи вида XXXX-YYYY, где XXXX — код товарной группы (мобильные телефоны, телевизоры, видеокамеры, фотоаппараты), а YYYY — код товара внутри группы. В основной таблице есть два столбца: один с кодами групп продуктов (group), а другой — с кодами продуктов (ID). Коды групп товаров нельзя просто отбросить, поскольку один и тот же код товара может повторяться в разных группах.

Объединяем таблицы в Excel

Добавьте вспомогательный столбец в основную таблицу и назовите его Full ID (столбец C). Более подробную информацию см. в предыдущей части этой статьи.

Запишите следующую формулу в ячейке C2:

Теперь легко объединить данные из наших двух таблиц. Мы сопоставим столбец Full ID первой таблицы со столбцом ID второй таблицы. Если совпадение найдено, записи из столбцов «Описание» и «Цена» второй таблицы будут перенесены в первую таблицу.

Данные в ключевых столбцах не совпадают

Ниже приведен пример: у вас есть небольшой магазин и вы получаете товары от одного или нескольких продавцов. У каждого из них своя номенклатура, которая отличается от вашей. В результате запись «Case-Ip4S-01» может соответствовать записи «SPK-A1403» в файле Excel, который вы получите от поставщика. Эти различия возникают случайно, и не существует общего правила для автоматического преобразования «SPK-A1403» в «Case-Ip4S-01».

Объединяем таблицы в Excel

Плохая новость: данные, содержащиеся в этих двух таблицах Excel, должны быть отредактированы вручную, прежде чем их можно будет впоследствии объединить.

Хорошая новость: этот процесс нужно выполнить только один раз, а полученную вспомогательную таблицу можно сохранить для дальнейшего использования. Затем можно автоматически объединить таблицы, что значительно экономит время.

1. Создаём вспомогательную таблицу для поиска.

Создайте новый рабочий лист Excel и назовите его конвертер SKU. Скопируйте весь столбец Our.SKU из листа Store в новый лист, удалив дубликаты и оставив только уникальные значения.

Затем добавьте колонку Supp.SKU и вручную найдите совпадения между колонками Our.SKU и Supp.SKU (вам поможет колонка Description). Это утомительная работа, но вы будете рады, что нам придется сделать это только один раз :-).

В итоге мы получаем следующую таблицу:

Объединяем таблицы в Excel

2. Обновляем главную таблицу при помощи данных из таблицы для поиска.

Добавьте новый столбец Supp.SKU в основную таблицу (лист Store).

Затем мы сравниваем конвертерные таблицы Store и SKU с помощью VLOOKUP для сопоставления и используем столбец Our.SKU для сопоставления и столбец Supp.SKU для обновленных данных.

Столбец Supp.SKU заполняется кодами оригинальных производителей.

Объединяем таблицы в Excel

Примечание: Если столбец Supp.SKU содержит пустые ячейки, вам необходимо включить коды SKU, соответствующие пустым ячейкам, в таблицу преобразования SKU, чтобы найти соответствующий код в таблице поставщиков. Затем повторите шаг 2.

Наша основная таблица имеет ключевой столбец, который точно соответствует записям в таблице поиска, поэтому эта задача больше не является сложной.

Мы используем VLOOKUP для сопоставления данных листа магазина с данными оптового поставщика 1, используя для сопоставления столбец Supp.SKU.

Вот пример обновленных данных в колонке «Оптовая цена»:

Оцените статью
Бизнес блог