Как сцепить много ячеек в excel через запятую

Объединение текста из двух или нескольких ячеек в одну

Данные из нескольких ячеек можно объединить в одну с помощью символа "амперсанд" (&) или функции СЦЕП.

Объединение данных с помощью символа "амперсанд" (&)

Выделите ячейку, в которую вы хотите вставить объединенные данные.
Введите = (знак равенства) и выберите первую ячейку, которую нужно объединить.
Введите символ & и пробел, заключенный в кавычки.
Выберите следующую ячейку, которую нужно объединить, и нажмите клавишу ВВОД. Пример формулы: =A2&" "&B2.

Объединение данных с помощью функции СЦЕП

Выделите ячейку, в которую вы хотите вставить объединенные данные.
Введите выражение = СЦЕП(.
Выберите первую ячейку, которую нужно объединить.

Для разделения объединяемых ячеек используйте запятые, а для добавления пробелов, запятых и другого текста — кавычки.
Введите закрывающую скобку в конце формулы и нажмите клавишу ВВОД. Пример формулы: =CONCAT(A2, " Семья").

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

СЦЕП

Функция ОБЪЕДИНИТЬ

Использование функции мгновенного заполнения в Excel

Объединение данных из нескольких источников (Power Query)

Полные сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Поиск ошибок в формулах

Сочетания клавиш и горячие клавиши в Excel

Текстовые функции (справка)

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Объединяйте ячейки без потери данных: в столбцах, в строках или в одну ячейку

Язык видео: английский. Субтитры: русский, английский. (Внимание: видео может не отражать последние обновления. Используйте инструкцию ниже.)

Объединение столбцов, строк или нескольких ячеек в Excel можно выполнить разными способами. Команда «Объединить и поместить в центре» (Merge and Center) полезна для создания аккуратных и легко читаемых таблиц, но она сохраняет содержимое только верхней левой ячейки, а остальные значения удаляются. Использование оператора «&» или функции СЦЕПИТЬ (CONCATENATE) помогает объединить содержимое ячеек в одну строку, но не объединяет ячейки и может занять немало времени.

Инструмент «Объединить данные» помогает объединить ячейки в больших таблицах без потери данных:

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

Перед началом работы добавьте инструмент «Объединить данные» в Excel

«Объединить данные» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

Скачать XLTools для Excel

– пробный период дает 14 дней полного доступа ко всем инструментам.

Объединить данные и объединить ячейки в Excel – в чём разница

При объединении нескольких ячеек (merge), вы создаёте одну большую ячейку на их месте.
При объединении данных нескольких ячеек (combine), вы соединяете, сцепляете значения этих ячеек в одну текстовую строку.

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

Внимание: значения в объединённой цепочке сохраняют формат ячеек, напр. 10%, 12-мар-2015, $40, т.д. Если объединяемые ячейки содержат ссылки или формулы, то в объединённой цепочке будет использован результат их вычислений.

Как объединить ячейки в одну ячейку без потери данных

Выберете диапазон смежных ячеек для объединения Нажмите кнопку Объединить данные на вкладке XLTools.
Выберите Объединить данные: в одну ячейку.
Задайте подходящий разделитель.
Установите флажок Объединить ячейки после слияния данных.
При необходимости отметьте дополнительные опции:
- Пропустить пустые ячейки, если они есть в выбранном диапазоне, чтобы избежать пустых значений в объединённой строке.
- Переносить текст по словам, чтобы переносить текст в ячейке.
Нажмите ОК Готово. Диапазон объединён в одну ячейку, все значения объединены в этой ячейке.

Как объединить ячейки в строках без потери данных (или объединить столбцы)

Выберите диапозон Нажмите кнопку Объединить данные на вкладке XLTools.
Выберите Объединить данные: по строкам.
Задайте подходящий разделитель.
Установите флажок Объединить ячейки после слияния данных.
При необходимости отметьте дополнительные опции:
- Пропустить пустые ячейки, если они есть в выбранном диапазоне, чтобы избежать пустых значений в объединённой строке.
- Переносить текст по словам, чтобы переносить текст в ячейке.
Нажмите ОК Готово. Все ячейки и их значения объединены по строкам.

Как объединить ячейки в столбцах без потери данных (или объединить строки)

Выберите диапозон Нажмите кнопку Объединить данные на вкладке XLTools.
Выберите Объединить данные: по столбцам.
Задайте подходящий разделитель.
Установите флажок Объединить ячейки после слияния данных.
При необходимости отметьте дополнительные опции:
- Пропустить пустые ячейки, если они есть в выбранном диапазоне, чтобы избежать пустых значений в объединённых строках.
- Переносить текст по словам, чтобы переносить текст в ячейке.
Нажмите ОК Готово. Все ячейки и их значения объединены по столбцам.

Как собрать данные ячеек в одну ячейку (без объединения ячеек)

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

Выберите диапозон Нажмите кнопку Объединить данные на вкладке XLTools.
Выберите Объединить данные: в одну ячейку.
Задайте подходящий разделитель.
При необходимости отметьте дополнительные опции:
- Очистить содержимое ячеек после слияния, чтобы убрать значения из исходных ячеек. Сравните на скриншоте результат с применением этой опции и без.
- Пропустить пустые ячейки, если они есть в выбранном диапазоне, чтобы избежать пустых значений в объединённой строке.
- Переносить текст по словам, чтобы переносить текст в ячейке.
Нажмите ОК Готово. Все значения объединены в одной ячейке.

Как объединить данные Excel по строкам (или собрать столбцы в один)

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

Выберите диапозон Нажмите кнопку Объединить данные на вкладке XLTools.
Выберите Объединить данные: по строкам.
Задайте подходящий разделитель.
При необходимости отметьте дополнительные опции:
- Очистить содержимое ячеек после слияния, чтобы убрать значения из исходных ячеек. Сравните на скриншоте результат с применением этой опции и без.
- Пропустить пустые ячейки, если они есть в выбранном диапазоне, чтобы избежать пустых значений в объединённой строке.
- Переносить текст по словам, чтобы переносить текст в ячейке.
Нажмите ОК Готово. Все значения объединены по строкам в ячейках слева.

Как объединить данные Excel по столбцам (или собрать строки в одну)

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

Выберите диапозон Нажмите кнопку Объединить данные на вкладке XLTools.
Выберите Объединить данные: по столбцам.
Задайте подходящий разделитель.
При необходимости отметьте дополнительные опции:
- Очистить содержимое ячеек после слияния, чтобы убрать значения из исходных ячеек. Сравните на скриншоте результат с применением этой опции и без.
- Пропустить пустые ячейки, если они есть в выбранном диапазоне, чтобы избежать пустых значений в объединённой строке.
- Переносить текст по словам, чтобы переносить текст в ячейке.
Нажмите ОК Готово. Все значения объединены по столбцам в ячейках сверху.

Для чего пропускать пустые ячейки при объединении ячеек и их данных

Если ваши исходные данные содержат пустые ячейки, рекомендуем не учитывать их при объединении ячеек и их данных. Это помогает избежать пустых значений в объединённых текстовых строках. Сравните результат с применением опции «Пропустить пустые ячейки» и без неё:

Как задать свой знак разделителя при объединении ячеек и их данных

Надстройка XLTools «Объединить данные» позволяет задать любой разделитель меужду значениями в объединённой текстовой строке:

Запятая
Запятая и пробел
Точка с запятой
Точка с запятой и пробел
Пробел
Новая строка
Или выберите Другой и задайте свой знак разделителя, напр., вертикальную черту

Как применить несколько разных разделителей в одной объединённой цепочке

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

Шаг 1. Объедините столбцы 'First Name' и 'Last Name':
1. Выделите столбцы Нажмите кнопку Объединить данные.
2. Укажите Объединить данные: по строкам.
3. Задайте запятую в качестве разделителя.
4. Отметьте опции Объединить ячейки…, Очистить содержимое…, Пропустить пустые….
5. ОК Готово, имена и фамилии объединены в цепочки типа "John Smith".
Шаг 2. Объедините результат со столбцом 'Order ID':
1. Выделите столбцы Нажмите кнопку Объединить данные.
2. Укажите Объединить данные: по строкам.
3. Задайте Другой разделитель и напечатайте ": order #".
4. Отметьте опции Объединить ячейки…, Очистить содержимое…, Пропустить пустые….
5. ОК Готово, все данные объединены в цепочки типа "John Smith: order #123" .

Как программе excel объединить ячейки. Объединение нескольких ячеек с данными. Как объединить ячейки в Excel стандартными средствами

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

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

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

Выполните следующие шаги:

Выделите ячейки, щелкните по ним правой клавишей мыши;
Выберите пункт «Формат ячеек…», появится новое окно;
Перейдите в меню «Выравнивание», далее – «Отображение»;
Установите птичку возле строчки «Объединение ячеек» и нажмите «Enter».

Теперь две ячейки объединятся в одну. Правда, здесь есть один момент. Если вы объединяли ячейки по горизонтали, то останется только та информация, которая была в левой; если объединяли по вертикали – то останется информация, которая была в верхней ячейке. Все, что было написано во второй ячейке, просто удалится.

Соединение данных из разных ячеек

Но в экселе 2010 и других версиях программы можно объединять ячейки таким образом, чтобы все написанное сохранялось. Допустим, у вас три столбика: в первом указано имя, во втором – фамилия, в третьем – отчество. Требуется объединить их в одной ячейке (например, сделать Иван Петров Иванович). Если ФИО не одно, а много, то переписывать все заново – слишком долго. Есть два варианта объединения ячеек, с помощью которых сделать это намного быстрее.

Вариант первый:

Сделайте четвертый столбик, назвав его ФИО;
На следующей строчке введите «=A2&B2&C2» (то есть те ячейки, в которых стоят первые фамилия, имя и отчество) и нажмите «Enter»;
Получится следующее – «ИванПетровИванович». То есть, ФИО будет написано без пробелов;
Если нужно установить пробелы, вставьте знак «&” “&», то есть, формула будет такой – «A2&” “&B2&” “&C2». В результате получится «Иван Петров Иванович»;
Чтобы поставить разделитель, можно использовать любой знак, например запятую. Получится такая формула«A2&” “&С2&”, “&В2», в итоге ФИО будут выглядеть так: «Иван Иванович, Петров»;
Чтобы использовать формулу для других ячеек, скопируйте первую формулу, выделите строчки под ней (напротив которых стоят разделенные фамилия, имя и отчество) и нажмите «Enter».

Вариант второй:

Аналогично применяется формула «=СЦЕПИТЬ(A2;B2;С2)»;
Если нужно установить пробел, то «=СЦЕПИТЬ(A2,» «,B2)»;
Если нужна запятая – «=СЦЕПИТЬ(A2,», «,B2)».

Как разделить объединенные ячейки

Щелкните по ячейке левой клавишей мыши. На вкладке «Главная» в разделе «Выравнивание» загорится кнопка «Объединить и поместить в центре»;
Чтобы убрать соединение, просто нажмите эту кнопку.

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

Как найти объединенные ячейки

Перейдите на вкладку «Главная», в раздел «Редактирование»;
Выберите «Найти и выделить», затем «Найти»;
Выберите «Параметры», нажмите строчку «Формат»;
Откройте меню «Выравнивание», в разделе «Отображение» пометьте галочкой строчку «Объединение ячеек»;
Кликните «ОК»;
Затем используйте один из двух вариантов:

Для того чтобы найти очередную объединенную ячейку, щелкните «Найти далее». В экселе будет выделена нужная ячейка;
Для того чтобы найти сразу все такие ячейки, щелкните «Найти все». Появятся все объединенные ячейки. Можно нажать на любую ячейку в списке – тогда она будет выделена в экселе, и вы увидите, где она расположена.

Важные выводы

Все данные, которые имеются в ячейках, теряются (за исключением верхней левой в выделенных ячейках, в которых потери нет), это касается любых направлений выделения;
Объединение ячеек вполне можно вернуть обратно, что не приведет к потери данных. Информация, которая содержалась в них, будет расположена в левой верхней ячейке (она будет расположена сплошным текстом).

Объединение ячеек в Эксель 2010 и других версиях программы помогает ускорить работу с документом и создать более наглядную картину. Используя эту функцию, вы легко научитесь правильно структурировать таблицы.

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

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

Основные способы объединения

Так как версии Excel обновляются и меняются их меню, не всегда и не у всех пользователей получается быстро объединить нужную область привычным способом. Однако все же имеется универсальный метод, который работает во всех версиях программы. Рассмотрим, как объединить ячейки в Экселе через контекстное меню (самый простой способ):

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

Есть и другой способ, который в зависимости от версии Excel несколько отличается. К примеру, в программе 2003 года панель инструментов поможет объединить несколько ячеек. В панели инструментов «Форматирование» имеется специальная кнопка, которая осуществляет объединение ячеек, при этом еще и выравнивает содержимое по центру, что хорошо подходит для создания шапки таблицы, то есть заголовков.

В Экселе 2007 и 2010 гг. аналогичную кнопку можно найти на вкладке «Главная» — «Выравнивание». При выборе этого меню и нажатии стрелки правее можно не только объединить и разместить по центру данные, но и помещать по строкам, а также вовсе отменять объединение ячейки в Экселе.

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

Слияние без потери данных

Объединить ячейки обычно получается с потерей или изменой . То есть при слиянии клеток с текстом информация заменяется данными с верхней левой ячейки объединяемого диапазона, при этом данные других клеток просто теряются. Чтобы знать, как объединить ячейки в Еxcel без потери данных, предстоит ознакомиться с еще одной полезной командой — текстовая функция «Сцепить».

С помощью такой функции можно соединить до 255 текстовых строк в одно целое, сцепить можно текстовые и числовые данные, ссылки и сочетание этих элементов. К примеру, в 1-й ячейке будет фамилия, а во 2-й — имя человека, чтобы осуществить слияние данных, нужно указать в 3-й клетке следующую формулу: =СЦЕПИТЬ(A1;» «;B1). Кавычки в этом случае означают пробел, то есть любые знаки пунктуации , но заключаются в кавычки.

В меню Еxcel «Главная» в разделе «Библиотека функций» необходимо выбрать «Текстовые», после чего кликнуть на функцию «СЦЕПИТЬ». В появившемся окне следует указать обязательный аргумент и необязательные аргументы, причем ячейки должны быть разделены запятыми (см. фото). После этого подтвердить операцию — объединить указанные данные.

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

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

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

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

Сейчас мы разберем все возможные варианты. Первые два способа подойдут в случае, если нужно склеить пустые или почти пустые ячейки (частично заполненные). Другие же соединят не только сами ячейки, но и их содержимое.

Самый простой способ

1 . Выделяем то, что нужно объединить. Для этого нажимаем левую кнопку мышки на крайней ячейке и, не отпуская мышку, тянем до тех пор, пока нужные ячейки не обрисуются рамкой.

2 . Нажимаем на кнопку на вкладке «Главная» (вверху).

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

Еще один простой способ

1 . Выделяем нужные ячейки и жмем по одной из них правой кнопкой мышки. Появляется контекстное меню, в котором выбираем пункт «Формат ячеек».

2 . В окошке выбираем вкладку «Выравнивание» и ставим галочку напротив пункта «Объединение ячеек», после чего нажимаем OK.

Объединение ячеек с данными

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

Чтобы этого не произошло, объединять их нужно по-другому. Есть несколько способов: через сцепление данных, при помощи программы Блокнот и через макрос.

Способ первый (через сцепление)

Шаг 1: склеиваем данные

Сначала нужно склеить данные, то есть соединить их друг с другом. Делается это в новой ячейке через функцию «Сцепить» или через знак &. Покажу на примере оба варианта, а вы уж выбирайте, какой больше нравится.

Склеивание через функцию «СЦЕПИТЬ» . Допустим, у нас есть отдельно имя, фамилия и отчество. Все эти данные нужно объединить, а для этого сначала склеить их.

1 . Щелкаем по пустой ячейке.

2 . Печатаем знак = и большими буквами слово СЦЕПИТЬ

3 . Открываем скобку и щелкаем по первой ячейке с данными, затем печатаем точку с запятой и щелкаем по второй, затем опять точку с запятой - и по третьей. После это закрываем скобку.

4 . Нажимаем клавишу Enter на клавиатуре.

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

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

Склеивание через знак & . Находится этот символ там же, где цифра 7 на клавиатуре. Набирать его нужно на английской раскладке при зажатой клавише Shift.

Принцип почти такой же, как и при склеивании через функцию СЦЕПИТЬ:

1 . Щелкаем в пустой ячейке и печатаем в ней знак =

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

Как и в предыдущем случае, любой дополнительный символ (разделитель) должен быть указан в кавычках.

Шаг 2: убираем дубли

Не важно, каким способом, но данные мы склеили.

Если нужно получить такой же результат и для остальных ячеек, эту «формулу» можно растянуть:

Щелкаем по ячейке с результатом
Наводим на маленький черный квадратик внизу справа
Когда курсор примет вид тонкого черного плюса, нажимаем левую кнопку мышки и, не отпуская ее, растягиваем по другим ячейкам

У меня получилось вот что:

Подробнее о «растягивании» можно узнать из вот этого урока .

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

Иногда это подходит, но что делать, если нужны только склеенные данные? Выход есть!

1 . Выделяем новые (склеенные) ячейки.

2 . Щелкаем по любой из них правой кнопкой мышки и из меню выбираем «Копировать».

3 . Выделяем какие-нибудь другие пустые ячейки.

4 . Нажимаем по любой из них правой кнопкой мышки и выбираем «Специальная вставка».

5 . В окошке выбираем пункт «Значения» (вверху) и нажимаем ОК.

В итоге получаем те же самые склеенные данные, но уже не в виде формулы (которая без оригинала не работает), а как обычный текст.

Собственно, теперь все предыдущие данные можно удалить, а эти (новые) скопировать и вставить на их место.

Способ второй (через Блокнот)

Честно говоря, мне этот способ даже больше нравится - он быстрее.

1 . Выделяем в Экселе ячейки с нужными данными (можно выделить целые столбы/строки).

2 . Копируем их (правая кнопка мыши - Копировать).

3 . Открываем программу Блокнот: Пуск - Все программы - Стандартные - Блокнот. Или открываем Пуск и печатаем слово блокнот в поле для поиска (внизу).

4 . Вставляем в окно программы скопированные данные (правой кнопкой мышки по пустому месту - Вставить).

5 . Копируем символ табуляции.

Для этого в пустой строчке Блокнота (перейти на нее можно, нажав на Enter) нажимаем один раз клавишу Tab на клавиатуре.

Указатель сразу перемещается чуть правее. Зажимаем левую кнопку мышки и выделяем этот отрезок, который визуально похож на десяток пробелов. Затем копируем (правой кнопкой по выделенному - Копировать).

6 . Вверху программы Блокнот нажимаем на пункт «Правка» и выбираем «Заменить…».

7 . В первое поле («Что:») вставляем скопированный символ табуляции, а во второе поле («Чем:») вставляем необходимый нам разделитель, например, жмем клавишу пробел.

8 . Нажимаем на кнопку «Заменить все» и закрываем это маленькое окошко.

После этого данные в Блокноте немного изменятся - текст как будто бы чуть-чуть сожмется.

9 . Выделяем все это в Блокноте и копируем.

10 . Переходим в программу Excel и вставляем скопированные данные (удалив предыдущие).

Способ третий (макрос)

Еще один вариант объединения ячеек в Экселе без потери данных. Он чуть более сложный - для тех, кто знает, что такое Visual Basic.

Данный способ я позаимствовал из вот этой статьи . Кстати, очень клёвый сайт, советую всем, кто работает с программой Excel.

1 . Открываем вкладку «Вид» и нажимаем на кнопку «Макросы».

2 . Печатаем название для макроса, нажимаем «Создать».

3 . Откроется редактор Visual Basic. Вставляем туда следующий код:

Sub MergeToOneCell()
Const sDELIM As String = " " "разделитель в данном случае пробел.
Dim rCell As Range
Dim sMergeStr As String
If TypeName(Selection) "Range" Then Exit Sub "если выделяются не ячейки, тогда программа выходит
With Selection
For Each rCell In .Cells
sMergeStr =sMergeStr & sDELIM & rCell.Text "процесс сбора текста из ячеек
Next rCell
Application.DisplayAlerts = False "выключаем обычное предупреждение о потере текста
.Merge Across:=False "объединение ячеек
Application.DisplayAlerts = True
.Item(1).Value = Mid(sMergeStr, 1 + Len(sDELIM)) " добавляем к объединенным ячейкам суммированный текст
End With
End Sub

4 . Закрываем редактор Visual Basic.

Теперь, чтобы объединить ячейки с данными, нужно их выделить, после чего на вкладке «Вид» нажать кнопку «Макросы» и в новом окошке на «Выполнить».

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

Выполните следующие шаги:

Выделите ячейки, щелкните по ним правой клавишей мыши;
Выберите пункт «Формат ячеек…», появится новое окно;
Перейдите в меню «Выравнивание», далее – «Отображение»;
Установите птичку возле строчки «Объединение ячеек» и нажмите «Enter».

Соединение данных из разных ячеек

Вариант первый:

Сделайте четвертый столбик, назвав его ФИО;
На следующей строчке введите «=A2&B2&C2» (то есть те ячейки, в которых стоят первые фамилия, имя и отчество) и нажмите «Enter»;
Получится следующее – «ИванПетровИванович». То есть, ФИО будет написано без пробелов;
Если нужно установить пробелы, вставьте знак «&” “&», то есть, формула будет такой – «A2&” “&B2&” “&C2». В результате получится «Иван Петров Иванович»;
Чтобы поставить разделитель, можно использовать любой знак, например запятую. Получится такая формула«A2&” “&С2&”, “&В2», в итоге ФИО будут выглядеть так: «Иван Иванович, Петров»;
Чтобы использовать формулу для других ячеек, скопируйте первую формулу, выделите строчки под ней (напротив которых стоят разделенные фамилия, имя и отчество) и нажмите «Enter».

Вариант второй:

Аналогично применяется формула «=СЦЕПИТЬ(A2;B2;С2)»;
Если нужно установить пробел, то «=СЦЕПИТЬ(A2,» «,B2)»;
Если нужна запятая – «=СЦЕПИТЬ(A2,», «,B2)».

Как разделить объединенные ячейки

Щелкните по ячейке левой клавишей мыши. На вкладке «Главная» в разделе «Выравнивание» загорится кнопка «Объединить и поместить в центре»;
Чтобы убрать соединение, просто нажмите эту кнопку.

Как найти объединенные ячейки

Перейдите на вкладку «Главная», в раздел «Редактирование»;
Выберите «Найти и выделить», затем «Найти»;
Выберите «Параметры», нажмите строчку «Формат»;
Откройте меню «Выравнивание», в разделе «Отображение» пометьте галочкой строчку «Объединение ячеек»;
Кликните «ОК»;
Затем используйте один из двух вариантов:

Для того чтобы найти очередную объединенную ячейку, щелкните «Найти далее». В экселе будет выделена нужная ячейка;
Для того чтобы найти сразу все такие ячейки, щелкните «Найти все». Появятся все объединенные ячейки. Можно нажать на любую ячейку в списке – тогда она будет выделена в экселе, и вы увидите, где она расположена.

Важные выводы

Все данные, которые имеются в ячейках, теряются (за исключением верхней левой в выделенных ячейках, в которых потери нет), это касается любых направлений выделения;
Объединение ячеек вполне можно вернуть обратно, что не приведет к потери данных. Информация, которая содержалась в них, будет расположена в левой верхней ячейке (она будет расположена сплошным текстом).

Иногда эксель удивляет своими требованиями делать что-то через э… макросы. Вот недавно, встала задача разбить несколько объединенных ячеек по одной, чтобы можно было нормально отсортировать весь диапазон. Думаю, никому не надо объяснять, как просто убрать объединение ячеек, разбив их по одной (я имею ввиду формат-выравнивание-убрать галку с «объединение ячеек»). Но, к сожалению, эксель обладает очень интересным свойством при отмене объединения ячеек не спрашивать, как именно мы хотели бы их вернуть в нормальное, разъединенное состояние, а тупо пишет в верхнюю левую ячейку значение, которое было в объединенной, а все остальные ячейки оставляет незаполненными. Ну, естественно, ни о какой нормальной сортировке по прежде объединенным ячейкам говорить не приходится. И хорошо бы их было всего штук 200-300. А если их шестьдесят тысяч, разбитых на группы по 10 ячеек? Вручную шесть тысяч значений потом копировать в оставшиеся пустыми ячейки?

Типичная картина — хотим отсортировать список пофамильно, чтобы Кузнецов шел все-таки после Иванова. Пример, разумеется, очень упрощенный.

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

А нам то надо, чтобы в каждой ячейке было соответствующее значение, иначе при сортировке получится черти что!

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

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

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

Итак, создаем макрос с кодом:

ABAP

Sub UnMerge_And_Fill_By_Value() " разгруппировать все ячейки в Selection и ячейки каждой бывшей группы заполнить значениями из их первых ячеек Dim Address As String Dim Cell As Range If TypeName(Selection) "Range" Then Exit Sub End If If Selection.Cells.Count = 1 Then Exit Sub End If Application.ScreenUpdating = False For Each Cell In Intersect(Selection, ActiveSheet.UsedRange).Cells If Cell.MergeCells Then Address = Cell.MergeArea.Address Cell.UnMerge Range(Address).Value = Cell.Value End If Next End Sub

SubUnMerge_And_Fill_By_Value () "разгруппироватьвсеячейкивSelectionиячейкикаждойбывшейгруппызаполнитьзначениямиизихпервыхячеек

DimAddressAs String

DimCellAs Range

If TypeName (Selection) & lt ; & gt ; "Range" Then

Exit Sub

EndIf

If Selection. Cells. Count = 1 Then

Exit Sub

EndIf

Application. ScreenUpdating= False

For EachCellIn Intersect (Selection, ActiveSheet. UsedRange) . Cells

If Cell. MergeCellsThen

Address= Cell. MergeArea. Address

Cell. UnMerge

Range (Address) . Value = Cell. Value

EndIf

В результате выполнения макроса все выделенные объединенные ячейки разбиваются по одной, и заполняются именно тем значением, которое было в объединенной. Сортируй-не хочу.

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

Ну и если, уже после того, как ячейки будут пересортированы, потребуется снова объединить ячейки с одинаковыми значениями (забудем на минуту, что объединенные ячейки — это зло, т.к. в случае, если требуется визуальная наглядность — то без них не обойтись), то нам опять придется использовать макрос. В интернете ссылок куча — но у меня заработал только один .

Тоже на всякий случай приведу код:

ABAP

Sub MergeCls() Dim ri As Integer, r2 As Integer, Col As Integer r1 = ActiveCell.Row r2 = ActiveCell.Row Col = ActiveCell.Column Do If Cells(r1, Col) Cells(r2 + 1, Col) Then If r1 r2 Then Range(Cells(r1 + 1, Col), Cells(r2, Col)).ClearContents With Range(Cells(r1, Col), Cells(r2, Col)) .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With End If r1 = r2 + 1 End If r2 = r2 + 1 Loop Until Cells(r2, Col) = "" End Sub

SubMergeCls ()

DimriAs Integer, r2As Integer, ColAs Integer

r1= ActiveCell. Row

r2= ActiveCell. Row

Col= ActiveCell. Column

If Cells (r1, Col) & lt ; & gt ; Cells (r2+ 1 , Col) Then

If r1& lt ; & gt ; r2Then

Range (Cells (r1+ 1 , Col) , Cells (r2, Col) ) . ClearContents

With Range (Cells (r1, Col) , Cells (r2, Col) )

Как просто в Excel большое количество ячеек объединить в список через запятую | Excel - как это работает?

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

В этой статье мы рассмотрим один очень интересный и полезный прием как с помощью формулы СЦЕПИТЬ (см. обзор) мы можем преобразовать большое количество ячеек с отдельными словами в в последовательный список, разделенный любыми знаками между собой (запятой, пробелом, двоеточием, тире и т.д.).

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

Вы можете применить функцию СЦЕПИТЬ следующим образом:

1. Выберите пустую ячейку, в которую вы поместите результат сцепления, и введите формулу = СЦЕПИТЬ (B1: М1 & ",") с диапазоном объединяемых данных, где в кавычках указываем тот знак, которым мы хотим отделить слова. Если Вы просто нажмете Enter, то в результате получится не то, что нам надо.

2. Поэтому делаем следующее. Выделяем внутри формулы выражение
B1: J1 & "," и нажимаем клавишу F9 .

3. Затем удаляем фигурные скобки { и } из формулы

4. Нажимаем Enter и....

Отлично, у нас все получилось, но что делать если наша информация, которую нам необходимо собрать расположена не в строках, а столбцах?
И Вы подумаете, так есть же способ преобразовать столбцы в ячейки (транспонирование), обзор которой мы разбирали ранее (ссылка), но зачем делать дополнительные операции когда можно их избежать. И тут нам опять поможет формула СЦЕПИТЬ, но уже в паре с формулой ТРАНСП.

Итак, у нас есть список, расположенный в столбце, и нам его надо собрать в последовательный список разделенный допустим запятой.

1 . Выберите пустую ячейку рядом с первыми данными списка, например ячейкой C1, и введите эту формулу = СЦЕПИТЬ (ТРАНСП (A1: A7) & ","), далее как и в предыдущем примере выделяем условие функции СЦЕПИТЬ в данном случае у нас это ТРАНСП (A1: A7) & "," в формуле, нажмаем F9 и удаляем фигурные скобки из формулы и нажимаем Enter.

Важно знать: Excel допускает комбинировать до 255 текстовых аргументов одновременно

Я надеюсь, что данный прием был полезным и когда потребуется быстро решить подобную задачу Вы будете готовы!

https://t.me/Excel_how_it_works

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

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

Процедура объединения

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

Способ 1: объединение с помощью контекстного меню

Самый распространенный способ объединения столбцов – это использование инструментов контекстного меню.

Выделяем первый сверху ряд ячеек колонок, которые мы хотим объединить. Кликаем по выделенным элементам правой кнопкой мыши. Открывается контекстное меню. Выбираем в нем пункт «Формат ячеек…».

Открывается окно форматирования ячеек. Переходим во вкладку «Выравнивание». В группе настроек «Отображение» около параметра «Объединение ячеек» ставим галочку. После этого жмем на кнопку «OK».

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

После проведения форматирования по образцу столбцы таблицы будут объединены в один.

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

Способ 2: объединение с помощью кнопки на ленте

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

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

Переходим во вкладку «Главная», если в данный момент находимся в другой вкладке. Жмем на пиктограмму в виде треугольника, остриём направленного вниз, справа от кнопки «Объединить и поместить в центре», которая расположена на ленте в блоке инструментов «Выравнивание». Открывается меню. Выбираем в нем пункт «Объединить по строкам».

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

Способ 3: объединение с помощью функции

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

Выделяем любую ячейку в пустом столбце на листе Excel. Для того, чтобы вызвать Мастер функций, кликаем по кнопке «Вставить функцию», расположенной около строки формул.

Открывается окно с перечнем различных функций. Нам нужно среди них найти наименование «СЦЕПИТЬ». После того как находим, выделяем этот пункт и нажимаем на кнопку «OK».

После этого открывается окно аргументов функции СЦЕПИТЬ. Её аргументами являются адреса ячеек, содержимое которых нужно объединить. В поля «Текст1», «Текст2» и т.д. нам нужно внести адреса ячеек самого верхнего ряда объединяемых столбцов. Сделать это можно вписав адреса вручную. Но, гораздо удобнее поставить в поле соответствующего аргумента курсор, а затем выделить ячейку, подлежащую объединению. Точно таким же образом поступаем и с другими ячейками первой строки объединяемых колонок. После того, как координаты появились в полях «Тест1», «Текст2» и т.д., жмем на кнопку «OK».

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

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

" ";

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

=СЦЕПИТЬ(B3;C3)

была изменена на следующую:

=СЦЕПИТЬ(B3;" ";C3)

Как видим, между словами появляется пробел, и они больше не являются слипшимися. При желании вместе с пробелом можно поставить запятую или любой другой разделитель.

Но, пока мы видим результат только для одной строки. Чтобы получить объединенное значение столбцов и в других ячейках, нам нужно скопировать функцию СЦЕПИТЬ на нижний диапазон. Для этого устанавливаем курсор в нижний правый угол ячейки, содержащей формулу. Появляется маркер заполнения в виде крестика. Зажимаем левую кнопку мыши и протягиваем его вниз до конца таблицы.

Как видим, формула скопирована на диапазон ниже, а соответствующие результаты отобразились в ячейках. Но мы просто вынесли значения в отдельный столбец. Теперь же нужно объединить первоначальные ячейки и вернуть данные в изначальное место расположения. Если просто объединить или удалить исходные столбцы, то формула СЦЕПИТЬ будет нарушена, и мы все равно утратим данные. Поэтому поступим немного по-другому. Выделяем колонку с объединенным результатом. Во вкладке «Главная» жмем на кнопку «Копировать», размещенную на ленте в блоке инструментов «Буфер обмена». Как альтернативное действие, можно после выделения столбца набрать на клавиатуре сочетание клавиш Ctrl+C.

Устанавливаем курсор на любую пустую область листа. Кликаем правой кнопкой мыши. В появившемся контекстном меню в блоке «Параметры вставки» выбираем пункт «Значения».

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

Выделяем первый столбец изначального диапазона, который нужно будет объединить с другими колонками. Жмем на кнопку «Вставить» размещенную на вкладке «Главная» в группе инструментов «Буфер обмена». Можно вместо последнего действия нажать на клавиатуре сочетание клавиш Ctrl+V.

Выделяем первоначальные столбцы, которые следует объединить. Во вкладке «Главная» в блоке инструментов «Выравнивание» открываем уже знакомое нам по предыдущему способу меню и выбираем в нем пункт «Объединить по строкам».

После этого, возможно, несколько раз появится окошко с информационным сообщением о потере данных. Каждый раз жмите на кнопку «OK».

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

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

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

Урок: Мастер функций в Excel

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

Так, большинство пользователей предпочитают использовать объединение через контекстное меню, как наиболее интуитивно понятное. Если же нужно произвести слияние столбцов не только в таблице, но и по всему листу, то тут на помощь придет форматирование через пункт меню на ленте «Объединить по строкам». Если же нужно произвести объединение без потерь данных, то с этой задачей можно справиться, лишь использовав функцию СЦЕПИТЬ. Хотя, если задачи сохранения данных не ставится, а тем более, если объединяемые ячейки пустые, то использовать данный вариант не рекомендуется. Это связано с тем, что он довольно сложный и его реализация занимает относительно много времени.

Мы рады, что смогли помочь Вам в решении проблемы.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

ДА НЕТ

Макрос слияния (сцепки, соединения) текста из ячеек в «Excel»

Макрос слияния (сцепки) текста из ячеек в «Excel» без потери данных.

Макрос соединения текста в Excel

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

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

Для больших объемов сцепки (слияния текста) требуются значительные трудозатраты.

Достойных аналогов этой функции, к сожалению, найти не удалось.

На помощь пришел язык VBA для написания макросов в «Excel».

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

Макрос слияния выглядит следующим образом:

Sub Sliyanie()
Dim txt As String ‘Объявляем переменную ТХТ
txt = «» ‘присваиваем объявленной переменной пустое значение
Dim cell
For Each cell In Selection ‘ для всех ячеек в выделении выполняем следующие действия
txt = txt + cell.text ‘к значению переменной txt добавляем значение ячейки
Next
MsgBox (txt) ‘выводим итоговое значение
End Sub

Это самый простой вариант макроса слияния текстовых значений ячеек в выделенном диапазоне.

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

Например:

Фрагмент макроса сцепки текста

Добавить форму UserForm с двумя полями TextBox. В одном поле указывать разделитель ( «/», «;», «.», и т.д.) а в другое поле выводить результат для удобства копирования.

Можно на форму UserForm вывести кнопки активации макроса «Соединить» и для ленивых кнопку «Копировать» — для копирования содержимого поля с результатом в буфер обмена.

Макрос копирования по ссылке: http://ruexcel.ru/ctrlc/

Можно добавить чек бокс (checkbox) для выбора учитывать пустые ячейки или пропускать их.

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

Как объединить текст из нескольких ячеек в одну в Excel - Как

Содержание:

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

Concatenate - это просто причудливый способ не сказать «объединить» или «объединить вместе», и для этого в Excel есть специальная функция CONCATENATE. Эта функция позволяет объединить текст из разных ячеек в одну ячейку. Например, у нас есть рабочий лист, содержащий имена и контактную информацию. Мы хотим объединить столбцы «Фамилия» и «Имя» в каждой строке в столбец «Полное имя».

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

= СЦЕПИТЬ (

Теперь мы вводим аргументы для функции СЦЕПИТЬ, которые сообщают функции, какие ячейки нужно объединить. Мы хотим объединить первые два столбца, сначала с именем (столбец B), а затем с фамилией (столбец A). Итак, нашими двумя аргументами для функции будут B2 и A2.

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

= СЦЕПИТЬ (B2; A2)

Вы также можете щелкнуть ячейку, чтобы ввести ее в функцию СЦЕПИТЬ. В нашем примере после ввода имени функции и открывающей скобки мы щелкаем ячейку B2, вводим запятую после B2 в функции, щелкаем ячейку A2, а затем вводим закрывающую скобку после A2 в функции.

Когда вы закончите добавлять ссылки на ячейки в функцию, нажмите Enter.

Обратите внимание, что между именем и фамилией нет пробела. Это потому, что функция СЦЕПИТЬ объединяет именно то, что содержится в аргументах, которые вы ей передаете, и не более того. В B2 нет пробела после имени, поэтому пробел не добавлялся. Если вы хотите добавить пробел, любую другую пунктуацию или детали, вы должны указать функции СЦЕПИТЬ, чтобы она включала их.

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

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

Нажмите Ввод.

Так-то лучше. Теперь между именем и фамилией есть пробел.

Теперь вы, вероятно, думаете, что вам нужно ввести эту функцию в каждую ячейку столбца или вручную скопировать ее в каждую ячейку столбца. На самом деле нет. У нас есть еще один изящный трюк, который поможет вам быстро скопировать функцию СЦЕПИТЬ в другие ячейки столбца (или строки). Выберите ячейку, в которую вы только что ввели функцию СЦЕПИТЬ. Маленький квадрат в правом нижнем углу выбранного объекта называется маркером заполнения. Маркер заполнения позволяет быстро копировать и вставлять содержимое в соседние ячейки в той же строке или столбце.

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

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

Вы также можете объединить текст из нескольких ячеек с помощью оператора амперсанда (&). Например, вы можете ввести = B2 & "" & A2, чтобы получить тот же результат, что и = СЦЕПИТЬ (B2; ""; A2). Нет никакого реального преимущества в использовании одного перед другим. хотя использование оператора амперсанда приводит к сокращению записи. Однако функция СЦЕПИТЬ может быть более удобочитаемой, что упрощает понимание того, что происходит в ячейке.

Распределить содержимое ячейки по соседним столбцам

Вы можете разделить содержимое ячейки и разделить компоненты на несколько смежных ячеек. Например, если ваш рабочий лист содержит столбца Полное имя , вы можете разделить этот столбец на два столбца — столбцы Имя и Фамилия .

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

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

Выберите ячейку, диапазон или весь столбец, содержащий текстовые значения, для разделения.
На вкладке Data в группе Data Tools щелкните Text as Columns.
Следуйте инструкциям мастера Преобразовать текст в столбцы , чтобы указать, как разделить текст на отдельные столбцы.

Эта функция недоступна в Excel в Интернете.

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

Разделить ячейки в Excel с помощью функции «Текст в столбцы»

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

Как разделить ячейки в Excel с помощью функции «Текст» на столбцы?

Чтобы узнать, как использовать функцию «Текст в столбцы» в Excel, вам необходимо выполнить шаги, которые мы приводим ниже. Во-первых, прежде чем использовать функцию «Текст в столбцы», вы должны решить, хотите ли вы сохранить исходные данные или заменить их. Вам нужно начать с открытия листа, который нуждается в реорганизации данных в Microsoft Excel.

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

Теперь выберите ячейки в столбце, который вы хотите использовать. На панели инструментов щелкните вкладку «Данные», а затем нажмите «Текст в столбцы». Если все имена или данные содержат один и тот же разделитель, например пробел, запятую, нажмите «Разделить». Затем нажмите «Далее».

В зависимости от разделителя выберите параметр в флажках окна с именем «Мастер», чтобы преобразовать текст в столбцы.Имена могут быть разделены пробелом, поэтому снимите флажки и установите флажок «Пробел». Оставьте все остальные параметры по умолчанию и нажмите «Далее».

Вы сможете просмотреть преобразованные данные

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

Чтобы сделать это правильно, вы измените данные в ячейке $ B $ 1. Если вы согласны перезаписать исходные данные, оставьте для всех параметров значения по умолчанию. Все, что вам нужно сделать, это нажать «Готово».

Как разделить ячейки с помощью Calc и более ранних версий Excel

В этом разделе содержится подробная информация об объединении ячеек в Microsoft Excel и Sun OpenOffice Calc.Прежде всего, вам нужно открыть Calc или Excel. Теперь выберите объединенную ячейку, которую вы хотите разделить. Объединенная ячейка охватывает несколько строк или столбцов для информации.

Аналогичным образом нажмите кнопку «Объединить ячейки» на панели форматирования. Ниже вы можете увидеть пример кнопки в каждой версии Excel и Calc.

Разделить ячейки в Excel, если вы являетесь пользователем Microsoft Excel 2000

К сожалению, разделить ячейку в Microsoft Excel 2000 не так просто, как нажать кнопку слияния, как мы объясняли ранее.Эта функция появилась в более поздних версиях Excel 2000. Чтобы разделить ячейку в Excel 2000, выполните следующие действия.

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

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

В Microsoft Excel вы также можете разделить не объединенную ячейку с помощью параметра «Текст в столбцы».Теперь выберите ячейки, которые вы хотите разделить на две ячейки. На вкладке «Данные» нажмите «Текст в столбцах».

Когда вы находитесь в мастере преобразования текста в столбцы, чтобы разделить текст на ячейки на основе запятой, пробела или другого символа, выберите параметр «Разделенные». Если вы хотите разделить текст на определенное количество длин символов, выберите «Фиксированная ширина». Затем нажмите кнопку «Далее».

Для "Разделить" вы должны выбрать символ, который вы хотите использовать для разделения данных.Для параметра «Фиксированная ширина» выберите, где вы хотите разделить текст, щелкнув раздел «Предварительный просмотр данных», который появляется в окне мастера. Наконец, нажмите на кнопку «Далее».

На последнем этапе вам нужно выбрать определенные параметры форматирования текста для разделенного текста, и вам просто нужно нажать кнопку «Готово».

сообщить об этом объявлении.

Как удалить год из даты в Microsoft Excel

Есть несколько способов удалить год из даты, отображаемой в Microsoft Excel. Вы можете использовать настраиваемое сотовое форматирование, чтобы скрыть его, или использовать различные функции, такие как объединение, чтобы полностью удалить его. Вот как.

Пользовательское форматирование

Хотя значение даты в ячейке Excel технически является числом, Microsoft Excel использует определенный тип форматирования для его отображения.То же самое относится и к значениям валюты, где символы валюты добавляются к значению, чтобы показать, что они связаны с деньгами. Когда вы добавляете дату в Excel в распознаваемом формате, Excel автоматически меняет формат номера ячейки на тип «Дата».

Вы можете настроить этот тип для отображения значения даты различными способами. Например, вы можете переключаться между «11/02/2021» (в формате ДД/ММ/ГГГГ, обычно используемом в Великобритании) и «2021-02-11» 11 февраля 2021 года.

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

Для этого выберите ячейки, содержащие исходные значения даты, затем выберите вкладку «Главная» на ленте. Оттуда щелкните стрелку рядом с раскрывающимся меню числового формата и выберите «Другие числовые форматы».

В меню «Формат ячеек» выберите понравившийся стиль форматирования из параметра «Дата».После выбора выберите «Пользовательский».

В опции «Пользовательский» вы увидите стиль форматирования даты, отображаемый в виде текста в поле «Тип». Например, «11.02.2021» в этом поле будет отображаться как «ДД / ММ / ГГГГ».

Чтобы удалить год, обязательно удалите все ссылки на «ГГГГ» или «ГГ» в поле «Тип», а также все лишние разделители, такие как тире или вперед. Например, для «11.02.2021» потребуется удалить «/ врожденный» из поля типа «ДД/ММ/ГГГГ», оставив «ДД/ММ» на месте.

После внесения изменений нажмите кнопку «ОК».

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

Объединить функцию

Конкатенения — это термин, который по существу означает объединение двух или более частей данных. Что касается конкатенации Microsoft Excel, вы можете использовать ее для добавления различных текстовых строк, чисел или значений ячеек вместе в новую ячейку.

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

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

 = Объединить (день (A2), "/", месяц (A2))

Значения, возвращаемые по дню и месяцу, разделяются специальным разделителем, например запятой или косой чертой, чтобы показать, что возвращаемое значение является датой. Каждое значение (день, разделитель и месяц) отделяется запятой в функции Concatenate.

В этом примере ячейка A2 содержит значение даты (11.12.2021) в формате ДД/ММ/ГГГГГ. Используя conatenate с днем и месяцем, день (11) и месяц (12) значения даты в A2 помещаются в другую ячейку, разделенную разделителем, который в данном случае является косой чертой.

Вы можете настроить эту формулу, чтобы изменить порядок значений дня и месяца (например, Mm / dd) или использовать другой символ-разделитель. Вы также можете Используйте дескриптор заполнения Чтобы скопировать эту формулу в другие ячейки столбца, удалите год из нескольких значений даты.

СВЯЗАНЫ С: Как автоматически заполнять последовательные данные в Excel с помощью дескриптора заполнения

Как разделить ячейку в Excel

Microsoft Office Excel - программа для редактирования данных. Информация в файле представлена в виде таблицы, состоящей из строк и столбцов. Наименьшей единицей измерения в документе Excel является ячейка. Эти элементы имеют условный номер, определяемый их отношением к столбцу и строке, например A1 или D13. Вы можете изменить ширину и высоту ячеек, придав им желаемый вид, чтобы форма, размер и соотношение сторон соответствовали вашим требованиям.Кроме того, вы можете объединить соседние элементы с обеих сторон или отменить разделение, чтобы настроить структуру таблицы. К сожалению, поскольку ячейка является минимальной файловой единицей в Excel, ее нельзя разделить.

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

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

Содержание

1 Как разделить ячейку в электронной таблице Excel с помощью планирования структуры
2 Как разделить ячейки, объединенные во время планирования структуры
3 Как визуально разделить ячейку в электронной таблице Excel, как разделить пункт по диагонали
4 Как разбить данные ячейки по столбцам в электронной таблице Excel с разделителем
5 Как распределить данные ячейки по столбцам в электронной таблице Excel с помощью мгновенного заполнения
6 Как распределить данные ячейки по столбцам в электронной таблице Excel по формулам
- 6.1 Как разделить имя и фамилию на 2 столбца
- 6.2 Как разделить имя, фамилию и отчество на 3 столбца
- 6.3 Как разделить данные, если они разделены запятыми
- 6.4 Другие формулы
7 Результат

Как разбить ячейку в электронной таблице Excel с помощью планирования структуры

Excel имеет определенную структуру таблицы - это необходимо для облегчения навигации по данным, и чтобы не было ошибок в формулах и расчетах, проблем с подключением и чтением.Каждая ячейка имеет свой индивидуальный номер, определяющий ее положение на осях цифр и латинских букв. Например, буквенно-цифровой адрес первого элемента в таблице — A1. Одна строка соответствует одной ячейке таблицы и наоборот. Это означает, что он является минимальным элементом и не может быть разделен на два или более независимых элемента.

В некоторых случаях пользователю Excel необходимо, чтобы два или более значения в одном столбце пересекались с одной из строк. Например, когда у той или иной вещи несколько названий или номеров, а остальные данные умещаются в одну ячейку.Аналогично и в случае со строками, если в одном столбце указаны первичные категории (например, «разработчики», «дизайнеры» и т. д.), а во втором — второстепенные («программисты» — Иван, Петр). Несмотря на то, что редактор не может сделать это напрямую, ограничение можно обойти. Для этого вам потребуется:

Заранее спланируйте, какое максимальное количество значений будет содержать строка или столбец;
При подготовке электронной таблицы Excel к работе объединяйте ячейки столбца или строк, которые будут использоваться как отдельные ячейки;
Таким образом, "разделенные" ячейки будут самостоятельными элементами, а "целые" ячейки будут объединены, то есть результат будет наглядным (но все равно будет соответствовать требованиям Excel).

Пример: в столбце A и строках 1-5 у вас 5 имен, а в следующем столбце B указаны должности этих людей в вашей компании. Если один или несколько человек занимают 2 должности, впишите вторую в столбец С, а для остальных просто объедините В1 и С1, В2 и С2 и так далее. Аналогично в случаях, когда одному значению в первом столбце соответствует более 2-х в следующем. Каждая ячейка будет иметь адрес в Excel и останется полностью функциональной.

Как разделить ячейки, объединенные при планировании структуры

Если после действий, описанных в предыдущем пункте, вы решили, что вся страница должна вернуться в предыдущее состояние, а ячейки должны быть разбиты:

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

Как визуально разбить ячейку в таблице Excel, как разбить элемент по диагонали

Если просто визуально разбить ячейку, не меняя ее свойств и адресов в Excel, необходимо:

Поместить курсор на выделенном элементе или выделить несколько (или весь лист).
Откройте вкладку «Главная», в области «Ячейки» нажмите «Формат».
Откроется выпадающее меню, в котором следует выбрать опцию «Формат ячеек».
В новом окне нужно перейти на вкладку "Граница" - здесь можно нарисовать необходимые границы ячеек (вертикальные, горизонтальные и диагональные линии, несколько вариантов линий и множество цветов).
Есть еще один вариант - щелкните правой кнопкой мыши по выделенным ячейкам, чтобы вызвать контекстное меню, затем выберите "Формат ячеек", перейдите на вкладку "Граница" и таким же образом нарисуйте линии.
Одной или нескольким выбранным ячейкам будет присвоена указанная разметка.

Чтобы создать ячейку, которая будет содержать названия строк и столбцов одновременно, необходимо сделать следующее:

В окне «Формат ячеек» на вкладке «Граница» провести любую диагональную линию, идущую от от верхнего левого до нижнего правого угла.
Применить форматирование.
Введите текст "вверху" ячейки (он только визуально разбит), который будет соответствовать строке, например "имя").
Выровнять по левому или правому краю, точнее установить пробелом.
При редактировании элемента нажмите Alt + Enter, чтобы перейти на новую строку, а затем введите текст столбца, например «количество»;
Если текст локализован или выглядит неправильно, необходимо изменить его положение с помощью пробела или изменить размер и соотношение сторон ячеек.

Как разделить данные ячейки на столбцы в электронной таблице Excel с помощью разделителя

Если у вас есть ячейки, заполненные определенными данными, и вам нужно разделить их на столбцы, используйте функцию разделения. Идеально, когда элементы содержат информацию, например, о приглашенных людях — имя, фамилия, номер билета, их город или страна, дата прибытия. Если все это было перенесено из текстового документа, форматирования не будет, для удобства работы в Excel данные необходимо разбить по соответствующим столбцам — «имя», «имя» и так далее.

Делается это так:

Создать новые, пустые столбцы, если они подходят для того, который содержит информацию, не так много их (должно быть не меньше количества категорий данных), иначе информация будет записана в другие уже завершены. Поместите курсор мыши после нужного столбца в строку латинских символов и щелкните правой кнопкой мыши по рамке таблицы, в контекстном меню выберите «Вставить». Если вы хотите добавить перед этим несколько пустых столбцов, выберите номер, аналогичный нужному справа (нажмите на ячейку с буквой и перетащите выделение).
Выберите столбец, который вы хотите разделить. Откройте "данные" - "Работа с данными" - "Текст в виде столбцов".
В новом окне (текст столбцов Master Distribution) выберите формат данных. При необходимости столбцы информации разных категорий разделяются пробелами или запятыми, выберите "с разделителями", если он имеет фиксированный объем данных - "Фиксированная ширина" (например, цифровой идентификатор - рассмотрим этот вариант позже), нажмите "Далее".
Далее укажите разделители, используемые в текстовом массиве в столбце.Укажите их в «Символ-разделитель есть» (если несколько, напишите все в поле «Другое»). Также укажите «Рассматривать последовательные разделители как один», если в строке есть несколько типов (например, два пробела в строке или точка, обозначающая сокращение слова, а не конец предложения, за которым следует запятая).
Отрегулируйте разделитель строки, если в тексте есть символы, заключенные в кавычки, и есть разделители от другого элемента, но их нельзя разорвать. К ним относятся такие предложения, как «Россия, Москва» — адрес в этом случае должен оставаться полным.Если не установить ограничитель, "Россия" и "Москва" будут в разных столбцах.
Выберите формат данных. По умолчанию установлено «Общее». Если ваша информация содержит даты или суммы денег, укажите соответствующие столбцы, в которых она будет размещена. Здесь же можно указать, где будут размещаться те или иные данные. Щелкните значок выбора диапазона справа от «Пробел для» и в качестве первого столбца введите слева от пустых, которые должны быть заполнены.К сожалению, данные нельзя переместить в другую книгу Excel или даже на другой лист, но вы может разделить его на текущий, а затем скопировать в нужное место.
Нажмите «Готово», и все настройки будут применены. Сохраните документ, чтобы не потерять его.

Как распределить данные ячеек по столбцам в электронной таблице Excel с помощью мгновенного заполнения

Начиная с 2013 года, Microsoft Office Excel предлагает возможность повторного использования мгновенного заполнения. С помощью этой функции вы можете заставить своего редактора автоматически распределять данные по ячейкам столбцов, как только он увидит шаблон ввода данных.

Вариант работает следующим образом: Excel начинает анализировать данные, введенные в ячейки листа, и пытается определить, откуда они берутся, чему соответствуют и есть ли в них закономерность. Итак, если у вас есть фамилии и имена людей в столбце A и у вас есть имена людей в B, вы будете использовать инструмент «Мгновенное заполнение» для расчета этого правила и предложите автоматически разделить все значения в столбце B.

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

Убедитесь, что у вас включено "мгновенное заполнение" - оно находится в "Файл" - выберите "Настройки" - "Дополнительно" - "Автозаполнение мгновенно" (поставьте галочку, если его там нет) ).
Начните вводить данные из другого в одну из колонок, и редактор сам предложит раздать информацию в большом количестве. Если предложение Excel вам подходит, нажмите Enter.
Если функция активна, но не работает в конкретном шаблоне, запустите инструмент вручную в "Данные" - "Мгновенная заливка" или нажмите Ctrl+"E".

Как распределить данные ячеек по столбцам в электронной таблице Excel с помощью формул

В Excel есть формулы, которые помогут вам разбить данные и сделать их более функциональными. Обычно бывает достаточно команд «влево», «посередине», «вправо», «искать», «искать» и «длина». Давайте посмотрим, когда они нужны и как их использовать.

Как разбить имя и фамилию на 2 столбца

Одним из самых частых случаев является необходимость разделить имя и фамилию в столбце А на В и С соответственно.Для этого нужно сделать так, чтобы редактор сам находил пробел между значениями и ломал все автоматически. Используйте шаблон "=ЛЕВЫЙ(A2;ПОИСК(";A2;1)-1)". Ищет в поиске пробелы, затем берет их в качестве разделителя и выводит, например, фамилии слева от двух столбцов и фамилии справа.Также и с другими значениями, которые разделены пробелами, эта формула не подходит для более сложных ячеек, в том числе имен с фамилиями и отчествами, суффиксами и прочими данными.

Как разбить имя, фамилию и отчество на 3 столбца

Если вы хотите разбить полное имя из трех значений на столбцы (каждый может быть только первой буквой):

Используйте формулу "= ЛЕВЫЙ (A2; НАЙТИ ("; A2; 1) -1)" для разделения имени;
Используйте "= PSTR (A2, НАЙТИ (" "; A2; 1) +1; НАЙТИ (" "; A2; НАЙТИ ("";A2;1)+1)-(НАЙТИ("";A2;1)+1))"найти отчество (пишется как "Иван Иванович")
Использовать"=ERR(A2;ДЛСТР(A2 ) - НАЙТИ (" "; A2; НАЙТИ ("; A2; 1) +1)) ", чтобы извлечь фамилию.

Те же формулы можно использовать для таких слов, как «Иванов Иван Младший» (западный стиль) или других, содержащих суффикс.

Как разделить данные при разделении запятыми

Если данные в ячейках записаны как «Black, Bob Mark» (полное имя впереди — запятая обязательна на английском языке), вы можете разделить их на простое «Bob Mark White» следующим образом :

Использование" = PSTR (A2, ПОИСК (", A2,1) +1, НАЙТИ (" ", A2, НАЙТИ (" "; A2,1) +1) - (НАЙТИ (" "; A2; 1) + 1))", чтобы выделить имя;
Используйте "= ERR (A2; DLSTR (A2) - FIND (" "; A2; FIND (" "; A2; 1) +1)) ", чтобы извлечь отчество;
Используйте" = ЛЕВЫЙ (A2, НАЙТИ (""; A2; 1) -2) ", чтобы извлечь фамилию."

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

Вы можете работать в Excel с другими типами данных, а также с другими типами данных. Еще одним распространенным примером являются адреса. Если ячейка содержит такую информацию, как "Россия, Москва, улица Арбат", вы можете разделить значения на другие элементы, используя в качестве разделителя запятую, точку или любой другой символ.Чтобы разбить такой адрес на 3 части (страна, город, улица):

Используйте "=ЛЕВЫЙ(A2,ПОИСК(", "; A2) - 1) "для разделения страны;
Использование" = PSTR (A2, ПОИСК (","; A2) +2; ПОИСК (","; A2; ПОИСК (","; A2) + 2) - ПОИСК (","; А2) -2) "выделить город;
Использовать" = ERR(A2; ДЛСТР(А2) - (ПОИСК (","; А2; ПОИСК (","; А2) +1) +1) ) "отделить улицу.

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

Результат

Microsoft Office Excel предлагает множество возможностей для работы как с сеткой таблицы, так и с ее содержимым.Хотя нет функции разделения ячейки на несколько частей, можно добиться результата, спланировав структуру и сгруппировав элементы. Если форматирование вам не подходит, вы можете отменить его на всем листе.При использовании фреймов вы можете разделить элемент по диагонали, чтобы получить имена столбцов в правом верхнем углу и строки в левом нижнем углу. Если вы хотите распределить массив информации по ячейкам других столбцов, воспользуйтесь формулами, функциями «Моментальное заполнение» или «Текст по столбцу».

Как расположить в Excel по алфавиту: сортировка по алфавиту столбцов и строк

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

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

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

Сортировка в алфавитном порядке и сохранение строк без изменений

отфильтровать и расположить по алфавиту

Сортировка в алфавитном порядке по нескольким столбцам

Расположите строки в алфавитном порядке

Проблемы с сортировкой по алфавиту в Excel

Excel в алфавитном порядке с формулами

Как расставить по фамилии
Расставить каждую строку по алфавиту отдельно
Сортировать каждый столбец по алфавиту

как расположить по алфавиту в Excel

вообще, есть 3 основных способа сортировки по алфавиту в Excel: кнопка a-z или z-a, функция сортировки и фильтр.Подробное руководство по каждому методу вы найдете ниже.

Как отсортировать столбец по алфавиту

Самый быстрый способ сортировки по алфавиту в Excel:

Выберите любую ячейку в столбце, который вы хотите отсортировать.
На вкладке «Данные» в группе «Сортировка и фильтр» щелкните от A до Z, чтобы отсортировать по возрастанию, или от Z до A, чтобы отсортировать по убыванию. Готово!,

к тем же кнопкам также можно получить доступ из вкладки «Главная»> группа «Редактирование»> «Сортировка и фильтрация»:

В любом случае Excel немедленно расположит ваш список в алфавитном порядке:

Совет

.После завершения сортировки и прежде чем делать что-либо еще, внимательно посмотрите на результаты. Если что-то выглядит не так, нажмите кнопку «Отменить», чтобы восстановить исходный порядок.,

Расставьте строки по алфавиту и соедините строки

Если в вашем наборе данных есть два или более столбца, вы можете использовать кнопку A-Z ИЛИ Z-A, чтобы расположить один из столбцов в алфавитном порядке, и Excel автоматически переместит данные в другие столбцы, сохраняя строки нетронутыми.

Как видно из отсортированной таблицы справа, связанная информация в каждой строке хранится вместе:

В некоторых ситуациях, в основном, когда выбрана только одна или несколько ячеек в середине набора данных, Excel не знает, какую часть данных сортировать, и запрашивает инструкции.Если вы хотите отсортировать всю таблицу, оставьте выбранным параметр «Расширить выбор» по умолчанию и нажмите «Сортировать»:

фильтровать и располагать по алфавиту в Excel

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

Добавить фильтр в таблицу просто:

выберите один или несколько заголовков столбцов.,
на вкладке Главная в группе редактирования нажмите Сортировка и фильтр > Фильтр.
маленьких раскрывающихся стрелок. Щелкните стрелку раскрывающегося списка для нужного столбца в алфавитном порядке и выберите Сортировка от А до Я:

столбец сразу располагается в алфавитном порядке, а маленькая стрелка вверх на кнопке фильтра указывает порядок сортировки (по возрастанию):

Для обратного порядка выберите «Сортировать от до А» в раскрывающемся меню «Фильтр».,

Чтобы удалить фильтр, просто нажмите кнопку «Фильтр» еще раз.

Как расположить несколько столбцов в алфавитном порядке

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

в качестве примера добавим еще один столбец в нашу таблицу, а затем отсортируем элементы по алфавиту сначала по региону, затем по имени:

Для этого выполните следующие действия:

выберите всю таблицу, которую вы хотите отсортировать.,
в большинстве случаев можно выбрать только одну ячейку, и Excel автоматически выберет остальные данные, но этот подход подвержен ошибкам, особенно когда в данных есть пробелы (пустые ячейки).
на вкладке Данные в группе Сортировка и фильтр нажмите Сортировка
, появится диалоговое окно «Сортировка» с автоматически созданным для вас первым уровнем сортировки в соответствии с Excel.
В раскрывающемся списке «Сортировать по» выберите столбец, который вы хотите расположить в алфавитном порядке первым, в нашем случае — «Регион»., Для двух других полей оставьте настройки по умолчанию: сортировка значений ячеек и порядок от a до Z:

Совет. Если в первом раскрывающемся меню вместо заголовков отображаются буквы столбцов, установите флажок Мои данные имеют заголовки.
Нажмите кнопку «Добавить уровень», чтобы добавить следующий уровень и выбрать параметры для другого столбца.
В этом примере второй уровень сортирует значения в столбце Имя в алфавитном порядке от А до Я:

Совет. Если вы сортируете по нескольким столбцам с одинаковыми критериями, нажмите «Копировать уровень» вместо «Добавить уровень».В этом случае вам нужно будет только выбрать другой столбец в первом поле.
При необходимости добавьте дополнительные уровни сортировки и нажмите OK.

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

Как отсортировать строки в алфавитном порядке в Excel

Если ваши данные расположены горизонтально, вы можете отсортировать их по алфавиту между строками.Это также можно сделать с помощью функции сортировки Excel. Вот как:

Выберите диапазон, который вы хотите отсортировать. Если в таблице есть метки строк, которые не следует перемещать, их следует опустить.,
перейдите на вкладку Данные > Сортировка и фильтрация группы и нажмите Сортировка:
В диалоговом окне «Сортировка» нажмите «Параметры…»
В небольшом диалоговом окне «Параметры сортировки» выберите «Сортировать слева направо» и нажмите «ОК», чтобы вернуться к сортировке
В раскрывающемся списке Сортировать по выберите номер строки, которую вы хотите отсортировать по алфавиту (строка 1 в этом примере)., Для двух других полей значения по умолчанию будут работать нормально, поэтому мы сохраним их (значения ячеек в сортировке в поле и от А до Я в поле порядка) и нажмем ОК:

в итоге первая строка в нашей таблице отсортирована в алфавитном порядке, а остальные данные расположены правильно, с сохранением всех соотношений между записями:

305e58b605”>

Проблемы с сортировкой по алфавиту в Excel

Функции сортировки в Excel великолепны, но если вы работаете с несовершенно структурированными данными, все может пойти не так.Вот два общих момента.

пустые или скрытые столбцы и строки

Если в ваших данных есть пустые или скрытые строки и столбцы, и вы выберете только одну ячейку перед нажатием кнопки «Сортировать», будет отсортирована только часть данных для первой пустой строки и/или столбца.

Простое исправление

— убрать пробелы и выявить все скрытые области перед сортировкой. Или сначала выберите всю таблицу, а затем установите в алфавитном порядке.,

Нераспознанные заголовки столбцов

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

При использовании диалогового окна сортировки убедитесь, что установлен флажок Мои данные имеют заголовки.,

Как сортировать по алфавиту в Excel с формулами

Microsoft Excel

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

Как расположить в Excel по алфавиту по фамилии

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

Опции сортировки Excel

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

в C2, имя извлечения:

= ВЛЕВО (A2, ПОИСК ("", A2) -1)

В D2 вытяните имя:

= ВПРАВО (A2, ДЛИН (A2)-ПОИСК ("", A2,1))

затем, соединенные части в обратном порядке, разделенные запятыми:

= D2 & "," & C2

подробное объяснение формул можно найти здесь, а пока сосредоточимся на результатах:

, потому что нам нужно расположить в алфавитном порядке имена, а не формулы, преобразовав их в значения.Для этого выделите все ячейки формул (E2:E10) и нажмите Ctrl+C, чтобы скопировать их. Щелкните правой кнопкой мыши выбранные ячейки, щелкните значения в опции вставки и нажмите Enter:

хорошо, вы почти у цели!, имя:

В случае необходимости вернуться к исходному формату имени, необходимо проделать еще немного работы:

снова разделить имена на две части по следующим формулам (где Е2 — имя, разделенное запятыми):

получить имя:

= ВПРАВО (E2, LEN (E2) - ПОИСК ("", E2))

Получить имя:

= ВЛЕВО (E2, ПОИСК ("", E2) - 2)

и соедините обе части:

= G2 & "" & h3

Преобразуйте формулы в значения еще раз, и все готово!,

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

Как расположить каждую строку в алфавитном порядке отдельно в Excel

в одном из предыдущих примеров мы обсуждали, как отсортировать строки в Excel по алфавиту с помощью диалогового окна «Сортировка». В этом примере мы имели дело с коррелированным набором данных. Но что, если каждая строка содержит независимую информацию? Как расположить в алфавитном порядке каждую строку?,

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

Предположим, у вас есть несколько строк данных, которые следует расположить в алфавитном порядке следующим образом:

для начала, скопируйте метки строк на другой лист или в другое место на том же листе, затем используйте следующую формулу массива, чтобы разместить каждую строку в алфавитном порядке (где B2: D2 — первая строка в исходной таблице):

= ИНДЕКС ($ B2: $ D2, ПОИСКПОЗ (СТОЛБЦЫ ($ B2: B2), СЧЁТЕСЛИ ($ B2: $ D2, "<=" & $ B2: $ D2), 0))

Помните, что правильный способ ввести формулу массива в Excel — нажать CTRL + SHIFT + ENTER.,

Если вы не очень довольны формулами массива Excel, выполните следующие действия, чтобы правильно ввести их на листе:

введите формулу в первую ячейку (в нашем случае G2) и нажмите Ctrl+Shift+Enter. Когда вы это сделаете, Excel закроет формулу в {скобках}. Не пытайтесь напечатать пряжку вручную, это не сработает.
, выберите ячейку формулы (G2) и перетащите маркер заполнения вправо, чтобы скопировать формулу в другие ячейки первой строки (в данном примере в ячейку I2).,
Выберите все ячейки формулы в первой строке (G2: I2) и перетащите маркер заполнения вниз, чтобы скопировать формулу в другие строки.

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

Если в вашем наборе данных есть пробелы, заключите формулу в ЕСЛИОШИБКА:

= ЕСЛИОШИБКА (ИНДЕКС ($B2:$D2, СОВПАДЕНИЕ (СТОЛБЦЫ ($B2:B2), СЧЁТЕСЛИ ($B2: $D2, "<=" & $B2: $D2), 0)), "")

К сожалению, нет простого решения для дубликатов.Если вы знаете такой, поделитесь, пожалуйста, в комментариях!,

Как работает эта формула

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

COLUMNS ($ B2: B2) предоставляет значение поиска. Из-за разумного использования абсолютных и относительных ссылок возвращаемое число увеличивается на 1, когда мы идем вправо.Это означает, что для G2 искомое значение равно 1, для h3 — 2, для I2 — 3,

ПОИСКПОЗ ищет значение поиска, вычисленное функцией СТОЛБЦЫ () в массиве поиска, возвращаемом функцией СЧЁТЕСЛИ(), и возвращает его относительное положение. Например, для G2 значение поиска равно 1, что является 3-й позицией в массиве поиска, поэтому ПОИСКПОЗ возвращает 3,

, наконец, ИНДЕКС получает фактическое значение на основе его относительного положения в строке. Для G2 он получает третье значение в диапазоне B2:D2, то есть Aria.,

Как отсортировать каждый столбец по алфавиту в Excel

Если вы имеете дело с независимыми подмножествами данных, организованными вертикально в столбцы, вы можете легко адаптировать приведенную выше формулу для индивидуальной алфавитной сортировки каждого столбца., Просто замените столбцы () на строки (), добавьте абсолютные координаты столбцов и относительные координаты строк, и ваша формула готова:

= ИНДЕКС (A $ 3: A $ 5, ПОИСКПОЗ (СТРОКИ (A $ 3: A3), СЧЁТЕСЛИ (A $ 3: A $ 5, "<=" & A $ 3: A $ 5), 0) )

помните, что это формула массива, которую нужно заполнить Ctrl+Shift+Enter:

Помимо предоставления решений задач, которые невозможно выполнить благодаря встроенным опциям сортировки excel, формулы имеют еще одно (хотя и спорное 🙂 преимущество — они делают сортировку динамической.Благодаря встроенным функциям вам придется использовать данные каждый раз, когда добавляются новые записи. С помощью формул вы можете добавлять новые данные в любое время, а отсортированные списки будут обновляться автоматически.

Если вы предпочитаете сделать новую алфавитную раскладку статической, замените формулы их результатами с помощью «Специальная вставка»> «Значения».

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

Как сортировать вкладки по алфавиту в Excel
как сортировать случайным образом в Excel
Как фильтровать и сортировать по цвету ячейки, цвету шрифта и значку
Как сортировать по строке, имени столбца и в пользовательском порядке

Экспорт в другие форматы в Numbers на Mac

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

Примечание: Если исходный файл защищен паролем, пароль применяется к копиям, экспортируемым в форматах PDF, Excel и Numbers '09, но вы можете изменить или удалить пароль.

Сохранение копии электронной таблицы Numbers в другом формате

При открытой электронной таблице выберите «Файл» > «Экспортировать в» > [ формат файла ] (используйте меню «Файл» в верхней части экрана).
Введите параметры экспорта:
- PDF: Эти файлы можно открывать (а иногда и редактировать) с помощью таких приложений, как Preview или Adobe Acrobat. Выберите макет страницы и качество изображения в файле PDF (чем выше качество, тем больше файл).Если вы добавили описания к изображениям, рисункам, звукам или видео, предназначенным для чтения вспомогательными технологиями (например, VoiceOver), они будут экспортированы автоматически. Чтобы включить комментарии, выберите «Подогнать каждый лист к одной странице», а затем установите флажок «Включить комментарии». Чтобы прикрепить теги доступности к большим таблицам, нажмите «Дополнительные параметры», затем выберите «Включено».
- Excel: Выберите, хотите ли вы создать отдельный лист для каждой таблицы или для каждого листа.Если вы создаете отдельный рабочий лист для каждой таблицы, вы можете включить массовую таблицу со ссылками на все ваши таблицы. Если файл должен быть совместим с более ранней версией Microsoft Excel (1997–2004 гг.), нажмите «Дополнительные параметры», затем выберите формат .xls во всплывающем меню.
- CSV: Содержимое ячеек отображается в экспортированном файле в виде значений, разделенных запятыми. Выберите, хотите ли вы создать отдельный файл для каждой таблицы или объединить все таблицы в один файл.Чтобы включить имена таблиц, выберите «Включить имена таблиц». Чтобы изменить кодировку текста, щелкните стрелку раскрывающегося списка рядом с дополнительными параметрами.
- TSV: Содержимое ячеек отображается в экспортированном файле в виде значений, разделенных табуляцией. Выберите, хотите ли вы создать отдельный файл для каждой таблицы или объединить все таблицы в один файл. Чтобы включить имена таблиц, выберите «Включить имена таблиц». Чтобы изменить кодировку текста, щелкните стрелку раскрывающегося списка рядом с дополнительными параметрами.
- Numbers '09: Этот формат можно открыть на Mac с помощью Numbers 2.0–2.3.
  
  Примечание: Если ваша электронная таблица содержит большие таблицы, ее нельзя экспортировать в формате Numbers '09.
Если есть поле пароля, выполните одно из следующих действий:
- Установите пароль: Установите флажок Требовать пароль для открытия и введите пароль.Это относится только к экспортированной копии.
- Оставьте пароль для исходной электронной таблицы: Убедитесь, что установлен флажок Требовать пароль для открытия.
- Использовать другой пароль в экспортированной копии: Установите флажок Требовать пароль для открытия, нажмите Изменить пароль и установите новый пароль.
- Экспортировать копию без пароля: Снимите флажок Требовать пароль для открытия.
Нажмите «Далее», затем введите имя электронной таблицы (без расширения имени файла, например .xls или .csv, так как оно будет добавлено к имени автоматически).
Введите один или несколько тегов (необязательно).
Чтобы выбрать место для сохранения электронной таблицы, нажмите всплывающее меню «Где», выберите местоположение, затем нажмите «Экспорт».

Чтобы увидеть больше местоположений, щелкните стрелку рядом с всплывающим меню «Где».

Как извлечь число или текст из Excel с помощью этой функции

Уильям Чарльз
0
5866
60

Microsoft Excel отлично справляется с числами и текстом, но если вы используете и то, и другое в одной ячейке, вы можете столкнуться с некоторыми трудностями.К счастью, вы можете извлекать числа или текст из ячеек, чтобы работать с данными более эффективно. Мы покажем вам несколько вариантов, в зависимости от формата, в котором в настоящее время хранятся ваши данные.

Числа, сохраненные в виде текста

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

Преобразование в число

Чтобы решить проблему, просто щелкните ячейку, щелкните окно предупреждения и выберите «Преобразовать в число». Это все! Это можно сделать для нескольких ячеек, выбрав их все и щелкнув окно с предупреждением, но это, вероятно, не так эффективно, как следующий метод.

Текст для столбцов

Если у вас есть много ячеек, которые необходимо восстановить, использование этого метода может занять огромное количество времени.Чтобы ускорить процесс, вы можете использовать текст Microsoft Excel в столбцах. Как преобразовать текстовые файлы Excel в электронные таблицы Excel. Как преобразовать текстовые файлы Excel в электронные таблицы Excel. Используйте Microsoft Excel для импорта данных из текстового файла в рабочий лист. Мастер импорта текста помогает импортировать данные из текста с разделителями и упорядочивать их аккуратно. функция. Выберите числа, которые вы хотите преобразовать, перейдите к Данные> Текст в столбцы и используйте мастер, чтобы убедиться, что числа отображаются правильно (в большинстве случаев вам просто нужно нажать Еще и конец без беспорядка с любыми из настроек).

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

Специальная вставка

Для работы этого метода необходимо ввести в ячейку цифру 1 (важно, чтобы она была в числовом формате). Выберите эту ячейку и скопируйте ее. Теперь выберите все числа, которые вы хотите преобразовать в числовой формат, и нажмите Edit > Paste Special .Выберите «Умножение» в разделе «Операция» и нажмите «ОК».

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

Извлечение чисел или текста из ячеек со смешанным форматом

Теперь мы подошли к самой сложной части: извлечению чисел из ячеек, которые содержат несколько форматов ввода.Если у вас есть число и единица измерения (например, «7 лезвий», как показано ниже), вы столкнетесь с этой проблемой. Чтобы решить эту проблему, мы рассмотрим несколько различных способов разбиения ячеек на числа и текст, что позволит работать с каждым из них по отдельности. Первый метод немного сложен, но очень хорошо работает с небольшими наборами данных.

ВЛЕВО / ВПРАВО и ПОИСК

Основная функция, которую мы здесь будем использовать, — ВЛЕВО, которая возвращает символы слева от ячейки. Как вы можете видеть в нашем наборе данных выше, у нас есть ячейки с одно-, двух- и трехсимвольными числами, поэтому нам придется вернуть один, два или три символа из левой ячейки.Сочетание ЛЕВОГО с функцией ПОИСК 4 Функции поиска Excel для эффективного поиска в электронных таблицах 4 Функции поиска Excel для эффективного поиска в электронных таблицах Поиск в большой электронной таблице Excel не всегда прост. Используйте формулы поиска, чтобы сэкономить время и эффективно искать электронные таблицы. мы можем вернуть все влево от места. Вот функция:

 = ВЛЕВО (A1, ПОИСК ("", A1, 1))

Это вернет все слева от местоположения.Использование маркера заполнения Как сэкономить время в Excel с помощью маркера заполнения Как сэкономить время в Excel с помощью маркера заполнения Освоение маркера заполнения — это первый шаг к тому, чтобы стать мастером Excel. чтобы применить формулу к остальным ячейкам, мы получаем это (вы можете увидеть формулу в панели функций в верхней части изображения):

Как видите, все числа теперь изолированы, поэтому вы можете манипулировать ими. Вы также хотите изолировать текст? Точно так же мы можем использовать функцию ПРАВИЛЬНО:

 = ПРАВИЛЬНО (A1, ДЛСТР (A1) -СМОТРЕТЬ ("", A1, 1))

Возвращает x символов справа от ячейки, где x — общее количество длина ячейки минус количество символов слева от пробела.

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

 = СЦЕПИТЬ (E1, F1)

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

Текст в столбцы

Функция Текст в столбцы полезна для столбцов, содержащих только числа, но она также может облегчить вашу жизнь, если у вас есть ячейки со смешанным форматом.Выберите столбец, с которым вы хотите работать, и нажмите Данные> Текст в столбцы . Затем вы можете использовать мастер, чтобы выбрать разделитель (обычно лучше всего использовать пробел) и разделить столбец по своему усмотрению.

Если у вас есть только одно- и двузначные цифры, параметр «Фиксированная ширина» также может быть полезен, поскольку он разделяет только первые два или три символа ячейки (вы можете создать несколько разделений, если хотите, но я напишу полное объяснение разделения фиксированной ширины для другой статьи).

Если ваш набор данных содержит много столбцов и вы предпочитаете не использовать текст для столбцов для каждого из них, вы можете легко добиться того же результата, используя быстрый экспорт и импорт. Сначала экспортируйте электронную таблицу в виде файла значений с разделителями-запятыми (CSV). Нажмите Файл > Сохранить как... и сохраните файл как CSV.

Теперь откройте новую электронную таблицу и нажмите Файл> Импорт… Выберите файл CSV и используйте мастер импорта, чтобы разделить данные на два столбца (вы выполните те же действия, что и для мастера преобразования текста в столбцы) .Выбрав «Пробел» в разделе «Разделители», вы указываете Microsoft Excel разделять данные везде, где он находит пробел, который изолирует числа и текст.

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

К сожалению, нет хорошего решения этой проблемы с использованием этого метода; вам просто нужно собрать клетки вместе.

Более сложные ситуации

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

Например, я нашел сообщение на форуме, где кто-то хотел извлечь числа из строки типа «45t * и 65/», чтобы она закончилась «4565.ROW ($1:$25)/10)

Честно говоря, понятия не имею, как это работает. Но, согласно сообщению на форуме, он будет брать числа из сложной строки чисел и других символов. Дело в том, что при наличии достаточного количества времени, терпения и усилий вы можете извлечь числа и текст практически из чего угодно! Вам просто нужно найти нужные ресурсы Нужна помощь с формулами Excel? 7 Консультационные ресурсы Нужна помощь с формулами Excel? 7 Ресурсы для консультантов Excel — золотой стандарт электронных таблиц.Если вы должны использовать Excel и все еще должны быть знакомы с ним, эти ресурсы быстро познакомят вас с основами и многим другим. .

Какую стратегию вы используете?

Теперь, когда вы лучше понимаете, как получать числа и буквы из ячеек смешанного формата, мы хотим знать, какие другие стратегии вы использовали для того же. Публикуйте свои сумасшедшие формулы, процессы или что-то еще, что может быть полезно в комментариях, чтобы мы все могли учиться на них! Чтобы получить больше удовольствия от работы с Excel, ознакомьтесь со статьей Как разделить имя и фамилию в Excel Как разделить имя и фамилию в Excel Как разделить имя и фамилию в Excel Работа с именами в Microsoft Excel? Вот удобный учебник, который покажет вам, как разделить имя и фамилию в Excel..

Blender уроки

Категории

Как сцепить много ячеек в excel через запятую

Объединение текста из двух или нескольких ячеек в одну

Объединение данных с помощью символа "амперсанд" (&)

Объединение данных с помощью функции СЦЕП

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

См. также

Объединяйте ячейки без потери данных: в столбцах, в строках или в одну ячейку

Перед началом работы добавьте инструмент «Объединить данные» в Excel

Объединить данные и объединить ячейки в Excel – в чём разница

Как объединить ячейки в одну ячейку без потери данных

Как объединить ячейки в строках без потери данных (или объединить столбцы)

Как объединить ячейки в столбцах без потери данных (или объединить строки)

Как собрать данные ячеек в одну ячейку (без объединения ячеек)

Как объединить данные Excel по строкам (или собрать столбцы в один)

Как объединить данные Excel по столбцам (или собрать строки в одну)

Для чего пропускать пустые ячейки при объединении ячеек и их данных

Как задать свой знак разделителя при объединении ячеек и их данных

Как применить несколько разных разделителей в одной объединённой цепочке

Как программе excel объединить ячейки. Объединение нескольких ячеек с данными. Как объединить ячейки в Excel стандартными средствами

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

Соединение данных из разных ячеек

Как разделить объединенные ячейки

Как найти объединенные ячейки

Важные выводы

Основные способы объединения

Слияние без потери данных

Самый простой способ

Еще один простой способ

Объединение ячеек с данными

Способ первый (через сцепление)

Шаг 1: склеиваем данные

Шаг 2: убираем дубли

Способ второй (через Блокнот)

Способ третий (макрос)

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

Соединение данных из разных ячеек

Как разделить объединенные ячейки

Как найти объединенные ячейки

Важные выводы

Как просто в Excel большое количество ячеек объединить в список через запятую | Excel - как это работает?

Вы можете применить функцию СЦЕПИТЬ следующим образом:

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

Процедура объединения

Способ 1: объединение с помощью контекстного меню

Способ 2: объединение с помощью кнопки на ленте

Способ 3: объединение с помощью функции

Помогла ли вам эта статья?

Макрос слияния (сцепки, соединения) текста из ячеек в «Excel»

Макрос слияния (сцепки) текста из ячеек в «Excel» без потери данных.

Макрос слияния выглядит следующим образом:

Например:

Макрос копирования по ссылке: http://ruexcel.ru/ctrlc/

Как объединить текст из нескольких ячеек в одну в Excel - Как

Содержание:

Распределить содержимое ячейки по соседним столбцам

Разделить ячейки в Excel с помощью функции «Текст в столбцы»

Как разделить ячейки в Excel с помощью функции «Текст» на столбцы?

Вы сможете просмотреть преобразованные данные

Как разделить ячейки с помощью Calc и более ранних версий Excel

Разделить ячейки в Excel, если вы являетесь пользователем Microsoft Excel 2000

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

Как удалить год из даты в Microsoft Excel

Пользовательское форматирование

Объединить функцию

Как разделить ячейку в Excel

Как разбить ячейку в электронной таблице Excel с помощью планирования структуры

Как разделить ячейки, объединенные при планировании структуры

Как визуально разбить ячейку в таблице Excel, как разбить элемент по диагонали

Как разделить данные ячейки на столбцы в электронной таблице Excel с помощью разделителя

Как распределить данные ячеек по столбцам в электронной таблице Excel с помощью мгновенного заполнения

Как распределить данные ячеек по столбцам в электронной таблице Excel с помощью формул

Как разбить имя и фамилию на 2 столбца

Как разбить имя, фамилию и отчество на 3 столбца

Как разделить данные при разделении запятыми

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

Результат

Как расположить в Excel по алфавиту: сортировка по алфавиту столбцов и строк

как расположить по алфавиту в Excel