Как объединить текст в ячейках в Excel (Функция Сцепить). Как объединить две колонки в excel в одну


Как объединить две таблицы Excel по частичному совпадению ячеек

Из этой статьи Вы узнаете, как быстро объединить данные из двух таблиц Excel, когда в ключевых столбцах нет точных совпадений. Например, когда уникальный идентификатор из первой таблицы представляет собой первые пять символов идентификатора из второй таблицы. Все предлагаемые в этой статье решения протестированы мной в Excel 2013, 2010 и 2007.

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

Итак, есть два листа Excel, которые нужно объединить для дальнейшего анализа данных. Предположим, в одной таблице содержатся цены (столбец Price) и описания товаров (столбец Beer), которые Вы продаёте, а во второй отражены данные о наличии товаров на складе (столбец In stock). Если Вы или Ваши коллеги составляли обе таблицы по каталогу, то в обеих должен присутствовать как минимум один ключевой столбец с уникальными идентификаторами товаров. Описание товара или цена могут изменяться, но уникальный идентификатор всегда остаётся неизменным.

Трудности начинаются, когда Вы получаете некоторые таблицы от производителя или из других отделов компании. Дело может ещё усложниться, если вдруг вводится новый формат уникальных идентификаторов или самую малость изменятся складские номенклатурные обозначения (SKU). И перед Вами стоит задача объединить в Excel новую и старую таблицы с данными. Так или иначе, возникает ситуация, когда в ключевых столбцах имеет место только частичное совпадение записей, например, «12345» и «12345-новый_суффикс«. Вам-то понятно, что это тот же SKU, но компьютер не так догадлив! Это не точное совпадение делает невозможным использование обычных формул Excel для объединения данных из двух таблиц.

И что совсем плохо – соответствия могут быть вовсе нечёткими, и «Некоторая компания» в одной таблице может превратиться в «ЗАО «Некоторая Компания»» в другой таблице, а «Новая Компания (бывшая Некоторая Компания)» и «Старая Компания» тоже окажутся записью об одной и той же фирме. Это известно Вам, но как это объяснить Excel?

Выход есть всегда, читайте далее и Вы узнаете решение!

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

Выберите подходящий пример, чтобы сразу перейти к нужному решению:

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

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

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

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

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

  • Наводим указатель мыши на заголовок столбца B, при этом он должен принять вид стрелки, направленной вниз:Объединяем таблицы в Excel
  • Кликаем по заголовку правой кнопкой мыши и в контекстном меню выбираем Вставить (Insert):Объединяем таблицы в Excel
  • Даём столбцу имя SKU helper.
  • Чтобы извлечь первые 5 символов из столбца SKU, в ячейку B2 вводим такую формулу:

    =ЛЕВСИМВ(A2;5)=LEFT(A2,5)

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

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

  • Копируем эту формулу во все ячейки нового столбца.

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

Теперь при помощи функции ВПР (VLOOKUP) мы получим нужный результат:

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

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

  • Извлечь первые Х символов справа: например, 6 символов справа из записи «DSFH-164900». Формула будет выглядеть так:

    =ПРАВСИМВ(A2;6)=RIGHT(A2,6)

  • Пропустить первые Х символов, извлечь следующие Y символов: например, нужно извлечь «0123» из записи «PREFIX_0123_SUFF». Здесь нам нужно пропустить первые 8 символов и извлечь следующие 4 символа. Формула будет выглядеть так:

    =ПСТР(A2;8;4)=MID(A2,8,4)

  • Извлечь все символы до разделителя, длина получившейся последовательности может быть разной. Например, нужно извлечь «123456» и «0123» из записей «123456-суффикс» и «0123-суффикс» соответственно. Формула будет выглядеть так:

    =ЛЕВСИМВ(A2;НАЙТИ("-";A2)-1)=LEFT(A2,FIND("-",A2)-1)

Одним словом, Вы можете использовать такие функции Excel, как ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT), ПСТР (MID), НАЙТИ (FIND), чтобы извлекать любые части составного индекса. Если с этим возникли трудности – свяжитесь с нами, мы сделаем всё возможное, чтобы помочь Вам.

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

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

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

Добавляем в главной таблице вспомогательный столбец и называем его Full ID (столбец C), подробнее о том, как это делается рассказано ранее в этой статье.

В ячейке C2 запишем такую формулу:

=СЦЕПИТЬ(A2;"-";B2)=CONCATENATE(A2,"-",B2)

Здесь A2 – это адрес ячейки, содержащей код группы; символ «—» – это разделитель; B2 – это адрес ячейки, содержащей код товара. Скопируем формулу в остальные строки.

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

Теперь объединить данные из наших двух таблиц не составит труда. Мы будем сопоставлять столбец Full ID первой таблицы со столбцом ID второй таблицы. При обнаружении совпадения, записи из столбцов Description и Price второй таблицы будут добавлены в первую таблицу.

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

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

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

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

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

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

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

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

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

В результате мы имеем вот такую таблицу:

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

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

В главную таблицу (лист Store) вставляем новый столбец Supp.SKU.

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

Далее при помощи функции ВПР (VLOOKUP) сравниваем листы Store и SKU converter, используя для поиска соответствий столбец Our.SKU, а для обновлённых данных – столбец Supp.SKU.

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

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

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

3. Переносим данные из таблицы поиска в главную таблицу

В нашей главной таблице есть ключевой столбец с точным совпадением с элементами таблицы поиска, так что теперь эта задача не вызовет сложностей :)

При помощи функции ВПР (VLOOKUP) объединяем данные листа Store с данными листа Wholesale Supplier 1, используя для поиска соответствий столбец Supp.SKU.

Вот пример обновлённых данных в столбце Wholesale Price:

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

Всё просто, не так ли? Задавайте свои вопросы в комментариях к статье, я постараюсь ответить, как можно скорее.

Оцените качество статьи. Нам важно ваше мнение:

office-guru.ru

Как объединить строки в Excel 2010 и 2013 без потери данных

Это руководство рассказывает о том, как объединить несколько строк в Excel. Узнайте, как можно быстро объединить несколько строк в Excel без потери данных, без каких-либо макросов и надстроек. Только при помощи формул!

Объединение строк в Excel – это одна из наиболее распространённых задач в Excel, которую мы встречаем всюду. Беда в том, что Microsoft Excel не предоставляет сколько-нибудь подходящего для этой задачи инструмента. Например, если Вы попытаетесь совместить две или более строки на листе Excel при помощи команды Merge & Center (Объединить и поместить в центре), которая находится на вкладке Home (Главная) в разделе Alignment (Выравнивание), то получите вот такое предупреждение:

The selection contains multiple data values. Merging into one cell will keep the upper-left most data only. (В объединённой ячейке сохраняется только значение из верхней левой ячейки диапазона. Остальные значения будут потеряны.)

Объединяем строки в Excel

Если нажать ОК, в объединённой ячейке останется значение только из верхней левой ячейки, все остальные данные будут потеряны. Поэтому очевидно, что нам нужно использовать другое решение. Далее в этой статье Вы найдёте способы объединить нескольких строк в Excel без потери данных.

Как объединить строки в Excel без потери данных

Задача: Имеется база данных с информацией о клиентах, в которой каждая строка содержит определённые детали, такие как наименование товара, код товара, имя клиента и так далее. Мы хотим объединить все строки, относящиеся к определённому заказу, чтобы получить вот такой результат:

Объединяем строки в Excel

Когда требуется выполнить слияние строк в Excel, Вы можете достичь желаемого результата вот таким способом:

Как объединить несколько строк в Excel при помощи формул

Microsoft Excel предоставляет несколько формул, которые помогут Вам объединить данные из разных строк. Проще всего запомнить формулу с функцией CONCATENATE (СЦЕПИТЬ). Вот несколько примеров, как можно сцепить несколько строк в одну:

  • Объединить строки и разделить значения запятой:

    =CONCATENATE(A1,", ",A2,", ",A3)=СЦЕПИТЬ(A1;", ";A2;", ";A3)

  • Объединить строки, оставив пробелы между значениями:

    =CONCATENATE(A1," ",A2," ",A3)=СЦЕПИТЬ(A1;" ";A2;" ";A3)

  • Объединить строки без пробелов между значениями:

    =CONCATENATE(A1,A2,A3)=СЦЕПИТЬ(A1;A2;A3)

Уверен, что Вы уже поняли главное правило построения подобной формулы – необходимо записать все ячейки, которые нужно объединить, через запятую (или через точку с запятой, если у Вас русифицированная версия Excel), и затем вписать между ними в кавычках нужный разделитель; например, «, « – это запятая с пробелом; » « – это просто пробел.

Итак, давайте посмотрим, как функция CONCATENATE (СЦЕПИТЬ) будет работать с реальными данными.

Объединяем строки в Excel

  1. Выделите пустую ячейку на листе и введите в неё формулу. У нас есть 9 строк с данными, поэтому формула получится довольно большая:

    =CONCATENATE(A1,", ",A2,", ",A3,", ",A4,", ",A5,", ",A6,", ",A7,", ",A8)=СЦЕПИТЬ(A1;", ";A2;", ";A3;", ";A4;", ";A5;", ";A6;", ";A7;", ";A8)

  2. Скопируйте эту формулу во все ячейки строки, у Вас должно получиться что-то вроде этого:Объединяем строки в Excel
  3. Теперь все данные объединены в одну строку. На самом деле, объединённые строки – это формулы, но Вы всегда можете преобразовать их в значения. Более подробную информацию об этом читайте в статье Как в Excel заменить формулы на значения.

Оцените качество статьи. Нам важно ваше мнение:

office-guru.ru

Объединение текста и чисел - Excel

Примечание:  Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .

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

Используйте числовой формат для отображения текста до или после числа в ячейке

Если столбец, который вы хотите отсортировать содержит числа и текст — 15 # продукта, продукт #100 и 200 # продукта — не может сортировать должным образом. Можно отформатировать ячейки, содержащие 15, 100 и 200, чтобы они отображались на листе как 15 # продукт, продукт #100 и 200 # продукта.

Использование пользовательского числового формата для отображения номера с текстом, не изменяя порядок сортировки число. Таким образом изменение способа отображения номера без изменения значения.

Выполните следующие действия.

  1. Выделите ячейки, формат которых требуется изменить.

  2. На вкладке Главная в группе число щелкните стрелку. Кнопка вызова диалогового окна в группе "Число"

  3. В списке категории выберите категорию, например настраиваемыеи нажмите кнопку встроенный формат, который похож на то, которое вы хотите.

  4. В поле Тип измените коды числовых форматов в формате, который вы хотите создать.

    Для отображения текста и чисел в ячейке, заключите текст в двойные кавычки (» «), или чисел с помощью обратной косой черты (\) в начале.

    Примечание: изменение встроенного формата не приводит к удалению формат.

Для отображения

Используйте код

Принцип действия

12 как Продукт №12

"Продукт № " 0

Текст, заключенный в кавычки (включая пробел) отображается в ячейке перед числом. В этом коде "0" обозначает число, которое содержится в ячейке (например, 12).

12:00 как 12:00 центральноевропейское время

ч:мм "центральноевропейское время"

Текущее время показано в формате даты/времени ч:мм AM/PM, а текст "московское время" отображается после времени.

-12 как -12р. дефицит и 12 как 12р. избыток

0.00р. "избыток";-0.00р. "дефицит"

Это значение отображается в формате "Денежный". Кроме того, если в ячейке находится положительное значение (или 0), после него будет показан текст "(излишек)", а если ячейка содержит отрицательное значение, после него будет показан текст "(недостаток)".

Объединение текста и чисел из разных ячеек в одной ячейке с помощью формулы

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

Для объединения чисел с помощью функции СЦЕПИТЬ или функции ОБЪЕДИНЕНИЯ, текст и TEXTJOIN и амперсанд (&) оператор.

Примечания: 

  • В Excel 2016Excel Mobile и Excel Online с помощью функции ОБЪЕДИНЕНИЯ заменена функции СЦЕПИТЬ . Несмотря на то, что функция СЦЕПИТЬ по-прежнему доступен для обеспечения обратной совместимости, следует использовать ОБЪЕДИНЕНИЯ, так как функции СЦЕПИТЬ могут быть недоступны в будущих версиях Excel.

  • TEXTJOIN Объединение текста из нескольких диапазонах и/или строки, а также разделитель, указанный между каждой парой значений, который будет добавляться текст. Если разделитель пустую текстовую строку, эта функция будет эффективно объединять диапазоны. TEXTJOIN в Excel 2013 и более ранние версии не поддерживается.

Примеры

Примеры различных на рисунке ниже.

Внимательно посмотрите на использование функции текст во втором примере на рисунке. При присоединении к числа в строку текста с помощью оператор объединения, используйте функцию текст , чтобы управлять способом отображения чисел. В формуле используется базовое значение из ячейки, на который указывает ссылка (в данном примере.4) — не форматированное значение, отображаемое в ячейке (40%). Чтобы восстановить форматов чисел используйте функцию текст .

Примеры объединения текста и чисел

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

support.office.com

Как объединить текст в ячейках в Excel (Функция Сцепить)

Главная  /  Офис  /  Как объединить текст в ячейках в Excel (Функция Сцепить)
  • 18.11.2015
  • Просмотров: 30162
  • Excel
  • Видеоурок

В этом уроке расскажу как объединить текст в ячейках в Excel с помощью функции Сцепить. Оба эти способа, которые рассмотрим, позволяют сцепить в единый массив несколько ячеек. Ячейки при этом могут быть как текстовые, так и числовые.

Функция сцепить в Excel

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

Задача: Есть таблица с колонками Имя, Отчество и Фамилия. Необходимо сделать единый массив из этих значений. По сути нужно объединить все три ячейки в одну - ФИО.

Как объединить текст в ячейках в Excel Функция сцепить

Все формулы начинаются со знака =. Далее вводим название самой функции СЦЕПИТЬ. При вводе названия функции Excel выдает подсказку, которой вы можете можете воспользоваться. Рядом с названием появляется описание ее предназначения.

Как объединить текст в ячейках в Excel Функция сцепить

После ввода названия функции вводиться открывающая скобка (. В подсказке сейчас вы можете видеть описание синтаксиса - что необходимо вводить для этой формулы.

Как объединить текст в ячейках в Excel Функция сцепить

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

Как объединить текст в ячейках в Excel Функция сцепить

После этого, согласно синтаксису, ставим точку с запятой ; - этот символ служит разделителем. Повторяем предыдущий шаг для столбцов с отчеством и фамилией. В конце закрываем функцию с помощью закрывающей скобки ).

Как объединить текст в ячейках в Excel Функция сцепить

После нажатия клавиши Enter вы получите результат сцепления.

Как объединить текст в ячейках в Excel Функция сцепить

Этот результат не совсем читабелен, потому что между значениями нет пробелов. Сейчас мы будем его добавлять. Для того, чтобы добавить пробелы, мы немного изменим формулу. Пробел - это разновидность текста и для того, чтобы ввести его в формулу, необходимо ввести кавычки и между ними вставить пробел. Вся конструкция будет выглядеть таким образом " ". Осталось дело за малым - сцепить все знаком ;.

Как объединить текст в ячейках в Excel Функция сцепить

Точно таким же образом можно вводить дополнительные символы. Например, поставить запятую или точку. Конструкция в этом случае будет такая ".". Осталось только протянуть функцию на все строки (об этом расскажу в конце статьи), а так как эта ячейка содержит формулу, а не значения, то необходимо скопировать значения в новую колонку через функцию Специальной вставки.

Объединяем текст в ячейках через амперсанд

Теперь давайте рассмотрим еще один способ, с помощью которого можно объединить текстовые значения из разных ячеек в одной. В этом случае мы будем использовать специальный знак амперсанд & - графическое сокращение латинского союза et (и).

Задача: Есть таблица с сотрудниками. Колонки Имя, Отчество и Фамилия. Сейчас они отдельные. Необходимо соединить все значения в строке в одну ячейку ФИО.

Как объединить текст в ячейках в Excel Функция сцепить

Использовать будем формулы. Становимся на ячейку в столбце с ФИО. Далее вводим начало формулы - знак =. После этого щелкаем по первому значения в строке, которое мы хотим добавить. В нашем случае это значение в колонке Имя.

Как объединить текст в ячейках в Excel Функция сцепить

Для того, чтобы добавить к значению имени значение отчества, мы будем использовать знак & (в английской раскладке Shift+7). Ставим его после значения A2и теперь уже щелкаем по ячейке в колонке Отчество.

Как объединить текст в ячейках в Excel Функция сцепить

Аналогично добавляем значение из колонки Фамилия.

Как объединить текст в ячейках в Excel Функция сцепить

После нажатия на Enter текстовые данные будут объединены, но между значениями не будет пробела.

Как объединить текст в ячейках в Excel Функция сцепить

Добавлять пробел мы будем с помощью введения дополнительного амперсанда и двойных кавычек. В этом случае конструкция пробела выглядит следующим образом: " " - между кавычками ставится одинарный пробел. Щелкаем по ячейке в колонке ФИО два раза левой клавишей мыши, чтобы подкорректировать формулу, и через знак & добавляем к формуле пробел.

Как объединить текст в ячейках в Excel Функция сцепить

После нажатия на клавишу Enter, вы получите необходимый результат.

Как объединить текст в ячейках в Excel Функция сцепить

Теперь просто протягиваем формулу на остальные ячейки в колонке. Для этого щелкаем один раз клавишей мыши по ячейке с формулой и тянем ее, зажав левую клавишу мыши, за нижний правый угол вниз до последней ячейки.

Как объединить текст в ячейках в Excel Функция сцепить

Если вам нужны только значения без формул, то копируем и вставляем их рядом через Специальную вставку.

На этом все. Если остались вопросы или вы знаете другие способы объединения, то обязательно пишите в комментариях ниже.

Не забудьте поделиться ссылкой на статью ⇒

Шапка на каждой странице Excel

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

  • 24.12.2015
  • Просмотров: 40408
  • Excel
Читать полностью Переключение листов Excel

Сегодня расскажу про быстрое переключение между листами Excel. Это может вам понадобиться, когда в открытом документе слишком много листов и добраться к нужному не так-то просто. В Экселе на этот случай есть несколько решений, поэтому вы сможете выбрать для себя наиболее подходящий.

  • 28.12.2015
  • Просмотров: 8333
  • Excel
Читать полностью Текст по столбцам в Excel

В этом уроке расскажу как сделать разбивку текста по столбцам в Excel. Данный урок подойдет вам в том случае, если вы хотите произвести разбивку текста из одного столбца на несколько. Сейчас приведу пример. Допустим, у вас есть ячейка "A", в которой находится имя, фамилия и отчество. Вам необходимо сделать так, чтобы в первой ячейке "A" была только фамилия, в ячейке "B" - имя, ну и в ячейке "C" отчество.

  • 15.12.2015
  • Просмотров: 3153
  • Excel
  • Видеоурок
Читать полностью Как свернуть Outlook в трей

В этом уроке расскажу как свернуть Outlook в трей. Многие пользователи, которые только начинают работать с программой Microsoft Outlook, сталкиваются с проблемой, когда программа, которую они сворачивают, остается на панели задач.

  • 22.09.2015
  • Просмотров: 2198
  • Outlook
Читать полностью Стаж работы в Excel

Этот урок будет более полезен для кадровой службы, потому что именно ей приходится считать трудовой стаж. Нам на помощь придет специальная функция РАЗНДАТ в Excel. Это фантомная функция, поэтому ее нет в обычном диспетчере формул. Работает она только с датами и с ее помощью можно рассчитать определенный период и вывести его в днях, годах и месяцах.

  • 12.01.2016
  • Просмотров: 7370
  • Excel
Читать полностью

4upc.ru

Как объединить несколько файлов Excel в один

Вам когда-нибудь приходилось сводить несколько таблиц Excel в одну? Самое неприятное в этом процессе, обычно, заключается в том, что эти таблицы могут содержать сразу по несколько листов внутри каждого файла. Есть ли решение данной проблемы? Сегодня вы об этом узнаете.

Объединим несколько файлов Excel в один, воспользовавшись силой скрипта VBA

Этот способ сделает все за вас, но только вам придется немного под напрячься. Хорошо, если у вас есть хоть какие-то навыки программиста. Но, если вы полный чайник в Excel и, вообще, в компьютере, то переходите ко второму способу, либо, будьте очень внимательными.

Итак, приступим.

Как и в методе «Как объединить несколько файлов Ворд в один», во-первых, прежде чем дать команду объединить несколько таблиц в одну в Excel, вам нужно эти таблицы собрать в одну отдельную папку. Посмотрите на скриншоте, как я это сделал.как объединить несколько файлов эксель в один

Теперь запустим программу VBA. Прочитайте в «Запуск скрипта VBA в Word», потому что принцип в Excel тот же самый.

Теперь, когда вы готовы, вот сам код скрипта:

Sub GetSheets() Path = "Укажите пусть до папки с файлами Excel" Filename = Dir(Path & "*.xls") Do While Filename <> "" Workbooks.Open Filename:=Path & Filename, ReadOnly:=True For Each Sheet In ActiveWorkbook.Sheets Sheet.Copy After:=ThisWorkbook.Sheets(1) Next Sheet Workbooks(Filename).Close Filename = Dir() Loop End Sub

Прошу обратить внимание на две строчки.

  1. Path = «Укажите пусть до папки с файлами Excel». Конечно, надпись в кавычках нужно заменить. Например, я заменил на … и вот, что у меня получилось: Path = » D:\mrUnrealist\Documents\Новая папка»несколько файлов excel в один
  2. Filename = Dir(Path & «*.xls»). В кавычках указан формат файла. В Excel их, обычно, два: .xls и .xlsx. Нажмите на файл правой кнопкой мыши и посмотрите «Свойства» файла. В скобках указан правильный тип файла.несколько таблиц в одну excelобъединить несколько листов excel в один

Этот код подойдет, если нужно объединить все листы в один файл Эксель. Но, если вам необходимо объединить определенные листы некоторых файлов, переходите к следующему способу.

Функция «Переместить/скопировать» поможет объединить несколько листов Excel в один файл

В этом методе мы научимся копировать листы из одного файла Excel в другие.

  1. Откройте все файлы, из которых вы собираетесь копировать листы, и тот файл (это может быть и новая пустая книга Эксель), в котором будут эти листы собраны.
  2. Теперь откройте книгу, из которой будете копировать листы. Выберите те листы, которые вам нужны. Для множественного выбора держите зажатой клавиши CTRL (для выбора отдельных листов), либо SHIFT (для выбора всех вместе листов).
  3. Нажмите по имени листа правой кнопкой мыши и в контекстном меню выберите пункт «Переместить/скопировать».как объединить несколько файлов excel в один
  4. В окне «Переместить или скопировать» выберите из списка «Переместить выбранные листы в книгу» нужную вам книгу. Т.е. ту, где вы собираете все листы вместе. А в списке «Перед листом» укажите место, где эти листы будут вставлены.как сводить несколько таблиц excel в однуЕсли вы не желаете, чтобы ваши листы пропали из открытой книги, поставьте галочку «Создать копию».
  5. Нажмите на кнопку «ОК» и выбранные листы будут перемещены или скопированы.
  6. Повторяйте со второго пункта до тех пор, пока вы не получите должного результата.

На этом все. Подписывайтесь, вступайте в группу вКонтакте или ОК, комментируйте, и не забывайте делиться с другими!

v-ofice.ru

Объединение данных с нескольких листов

Консолидация по расположению

Для консолидации по расположению диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов.

  1. Откройте каждый из исходных листов и убедитесь в том, что данные на них расположены одинаково.

  2. На конечном листе щелкните верхнюю левую ячейку области, в которой требуется разместить консолидированные данные.

    Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.

  3. На вкладке Данные в группе Работа с данными нажмите кнопку Консолидация.

    Кнопка "Консолидация" на вкладке "Данные"

  4. Выберите в раскрывающемся списке функцию, которую требуется использовать для консолидации данных.

  5. Выделите на каждом листе нужные данные.

    Путь к файлу вводится в поле Все ссылки.

  6. После добавления данных из всех исходных листов и книг нажмите кнопку ОК.

Консолидация по категории

Для консолидации по категории диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов. Кроме того, категории должны быть названы одинаково. Например, если один из столбцов называется Сред., а другой — Среднее, консолидация не просуммирует эти столбцы.

  1. Откройте каждый из исходных листов.

  2. На конечном листе щелкните верхнюю левую ячейку области, в которой требуется разместить консолидированные данные.

    Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.

  3. На вкладке Данные в группе Работа с данными нажмите кнопку Консолидация.

    Кнопка "Консолидация" на вкладке "Данные"

  4. Выберите в раскрывающемся списке функцию, которую требуется использовать для консолидации данных.

  5. Установите флажки в группе Использовать в качестве имен, указывающие, где в исходных диапазонах находятся названия: подписи верхней строки, значения левого столбца либо оба флажка одновременно.

  6. Выделите на каждом листе нужные данные. Не забудьте включить в них ранее выбранные данные из верхней строки или левого столбца.

    Путь к файлу вводится в поле Все ссылки.

  7. После добавления данных из всех исходных листов и книг нажмите кнопку ОК.

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

Консолидация по расположению

Для консолидации по расположению диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов.

  1. Откройте каждый из исходных листов и убедитесь в том, что данные на них расположены одинаково.

  2. На конечном листе щелкните верхнюю левую ячейку области, в которой требуется разместить консолидированные данные.

    Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.

  3. На вкладке Данные в разделе Сервис нажмите кнопку Консолидация.

    Вкладка "Данные", группа "Сервис"

  4. Выберите в раскрывающемся списке функцию, которую требуется использовать для консолидации данных.

  5. Выделите на каждом листе нужные данные и нажмите кнопку Добавить.

    Путь к файлу вводится в поле Все ссылки.

  6. После добавления данных из всех исходных листов и книг нажмите кнопку ОК.

Консолидация по категории

Для консолидации по категории диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов. Кроме того, категории должны быть названы одинаково. Например, если один из столбцов называется Сред., а другой — Среднее, консолидация не просуммирует эти столбцы.

  1. Откройте каждый из исходных листов.

  2. На конечном листе щелкните верхнюю левую ячейку области, в которой требуется разместить консолидированные данные.

    Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.

  3. На вкладке Данные в разделе Сервис нажмите кнопку Консолидация.

    Вкладка "Данные", группа "Сервис"

  4. Выберите в раскрывающемся списке функцию, которую требуется использовать для консолидации данных.

  5. Установите флажки в группе Использовать в качестве имен, указывающие, где в исходных диапазонах находятся названия: подписи верхней строки, значения левого столбца либо оба флажка одновременно.

  6. Выделите на каждом листе нужные данные. Не забудьте включить в них ранее выбранные данные из верхней строки или левого столбца. Затем нажмите кнопку Добавить.

    Путь к файлу вводится в поле Все ссылки.

  7. После добавления данных из всех исходных листов и книг нажмите кнопку ОК.

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

support.office.com