При выполнении определенных задач в 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 и количество бутылок на складе. Пиво может быть любым продуктом, и в реальной жизни количество колонн может быть гораздо больше.
Создайте вспомогательный столбец в таблице с дополнительными символами. Вы можете поместить его в конце таблицы, но лучше всего поместить его справа от ведущей колонки, чтобы он был виден.
Ключевым столбцом таблицы в нашем примере является столбец A с данными SKU, и нам нужно извлечь из него первые 5 символов. Добавим вспомогательный столбец и назовем его SKU helper:
- Наведите указатель мыши на заголовок столбца B так, чтобы он стал похож на стрелку, направленную вниз:
- Щелкните правой кнопкой мыши на заголовке и выберите в контекстном меню пункт Вставить:
Здесь A2 — адрес ячейки, из которой нужно извлечь символы, а 5 — количество символов, которые нужно извлечь.
Готово! Теперь у нас есть ключевые колонки со значениями точного совпадения — справочная колонка SKU в главной таблице и колонка SKU в таблице, в которой будет выполняться поиск.
Теперь мы используем функцию VLOOKUP, чтобы получить желаемый результат:
Другие формулы
- Извлеките первые X символов с правой стороны: например, 6 символов с правой стороны из записи «DSFH-164900». Тогда формула выглядит следующим образом:
Короче говоря, вы можете использовать такие функции Excel, как LEFT, RIGHT, MID, FIND для извлечения любой части составного индекса. Если у вас возникли проблемы с этим — свяжитесь с нами, мы сделаем все возможное, чтобы помочь вам.
Данные из ключевого столбца в первой таблице разбиты на два или более столбца во второй таблице
Предположим, что искомая таблица содержит столбец идентификаторов. Ячейки этого столбца содержат записи вида XXXX-YYYY, где XXXX — код товарной группы (мобильные телефоны, телевизоры, видеокамеры, фотоаппараты), а YYYY — код товара внутри группы. В основной таблице есть два столбца: один с кодами групп продуктов (group), а другой — с кодами продуктов (ID). Коды групп товаров нельзя просто отбросить, поскольку один и тот же код товара может повторяться в разных группах.
Добавьте вспомогательный столбец в основную таблицу и назовите его Full ID (столбец C). Более подробную информацию см. в предыдущей части этой статьи.
Запишите следующую формулу в ячейке C2:
Теперь легко объединить данные из наших двух таблиц. Мы сопоставим столбец Full ID первой таблицы со столбцом ID второй таблицы. Если совпадение найдено, записи из столбцов «Описание» и «Цена» второй таблицы будут перенесены в первую таблицу.
Данные в ключевых столбцах не совпадают
Ниже приведен пример: у вас есть небольшой магазин и вы получаете товары от одного или нескольких продавцов. У каждого из них своя номенклатура, которая отличается от вашей. В результате запись «Case-Ip4S-01» может соответствовать записи «SPK-A1403» в файле Excel, который вы получите от поставщика. Эти различия возникают случайно, и не существует общего правила для автоматического преобразования «SPK-A1403» в «Case-Ip4S-01».
Плохая новость: данные, содержащиеся в этих двух таблицах Excel, должны быть отредактированы вручную, прежде чем их можно будет впоследствии объединить.
Хорошая новость: этот процесс нужно выполнить только один раз, а полученную вспомогательную таблицу можно сохранить для дальнейшего использования. Затем можно автоматически объединить таблицы, что значительно экономит время.
1. Создаём вспомогательную таблицу для поиска.
Создайте новый рабочий лист Excel и назовите его конвертер SKU. Скопируйте весь столбец Our.SKU из листа Store в новый лист, удалив дубликаты и оставив только уникальные значения.
Затем добавьте колонку Supp.SKU и вручную найдите совпадения между колонками Our.SKU и Supp.SKU (вам поможет колонка Description). Это утомительная работа, но вы будете рады, что нам придется сделать это только один раз :-).
В итоге мы получаем следующую таблицу:
2. Обновляем главную таблицу при помощи данных из таблицы для поиска.
Добавьте новый столбец Supp.SKU в основную таблицу (лист Store).
Затем мы сравниваем конвертерные таблицы Store и SKU с помощью VLOOKUP для сопоставления и используем столбец Our.SKU для сопоставления и столбец Supp.SKU для обновленных данных.
Столбец Supp.SKU заполняется кодами оригинальных производителей.
Примечание: Если столбец Supp.SKU содержит пустые ячейки, вам необходимо включить коды SKU, соответствующие пустым ячейкам, в таблицу преобразования SKU, чтобы найти соответствующий код в таблице поставщиков. Затем повторите шаг 2.
Наша основная таблица имеет ключевой столбец, который точно соответствует записям в таблице поиска, поэтому эта задача больше не является сложной.
Мы используем VLOOKUP для сопоставления данных листа магазина с данными оптового поставщика 1, используя для сопоставления столбец Supp.SKU.
Вот пример обновленных данных в колонке «Оптовая цена»:
