Как сделать из одного столбца два в excel?

В этой статье Вы найдёте несколько способов, как разбить ячейки или целые столбцы в Excel 2010 и 2013. Приведённые примеры и скриншоты иллюстрируют работу с инструментами «Текст по столбцам» и «Мгновенное заполнение», кроме этого Вы увидите подборку формул для разделения имён, текстовых и числовых значений. Этот урок поможет Вам выбрать наилучший метод разбиения данных в Excel.

• Разбиваем ячейки при помощи инструмента «Текст по столбцам»
• Как разбить объединённые ячейки в Excel
• Разделяем данные в Excel 2013 при помощи инструмента «Мгновенное заполнение»
• Формулы для разбиения столбцов (имен и других текстовых данных)

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

Инструмент «Текст по столбцам» действительно очень удобен, когда нужно разделить данные из одного столбца по нескольким в Excel 2013, 2010, 2007 или 2003.

«Текст по столбцам» позволяет разбивать значения ячеек, отделённые разделителями, или выделять данные фиксированной ширины (когда все значения содержат определённое количество символов). Давайте рассмотрим эти варианты подробнее:

Разбиваем текстовые данные с разделителями по столбцам в Excel

Предположим, есть список участников, приглашённых на конференцию или какое-то другое мероприятие. На рисунке ниже видно, что в столбце Participant (Участник) перечислены имена участников, государство и ожидаемая дата прибытия:

Необходимо разбить этот текст на отдельные столбцы, чтобы таблица имела следующие данные (слева направо): First Name (Имя), Last Name (Фамилия), Country (Страна), Arrival Date (Ожидаемая дата прибытия) и Status (Статус).

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

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

В нижней части диалогового окна находится область Data preview (Образец разбора данных). Прежде чем нажать Next (Далее) будет разумным пролистать это поле и убедиться, что Excel правильно распределил все данные по столбцам.

Разбиваем текст фиксированной ширины по нескольким столбцам

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

К примеру, есть список товаров с ID и наименованием, причем идентификатор товара – это 9 символов, которые стоят перед наименованием этого товара:

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

Таким образом объединение ячеек будет отменено, но удовольствие от результата будет испорчено тем, что все данные останутся в левом столбце. Думаю, Вы догадались, что нужно снова использовать функцию Text to Columns (Текст по столбцам), чтобы разбить данные из одного столбца на два или более столбцов.

Разделяем данные на несколько столбцов в Excel 2013 при помощи мгновенного заполнения

Если Вы уже обновились до Excel 2013, то можете воспользоваться преимуществами нового инструмента «Мгновенное заполнение» и заставить Excel автоматически заполнять (в нашем случае – разбивать) данные, при обнаружении определенной закономерности.

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

Как видите, я ввёл только пару имён в столбец B, и «Мгновенное заполнение» автоматически заполнило остальные ячейки именами из столбца A. Если вы довольны результатом, просто нажмите Enter, и весь столбец будет заполнен именами. Очень умный инструмент, не правда ли?

Если «Мгновенное заполнение» включено, но не предлагает никаких вариантов, которые соответствуют определённому шаблону, Вы можете запустить этот инструмент вручную на вкладке Data (Данные) > Flash Fill (Мгновенное заполнение) или нажав сочетание клавиш Ctrl+E.

Пример 1

Самая распространённая ситуация, когда могут понадобится эти формулы – это необходимость разделить имена из одного столбца по нескольким. На рисунке ниже показано, какого результата мы пытаемся достичь:

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

Для тех, кому интересно, что означают эти формулы, я попробую объяснить более подробно.

SEARCH (ПОИСК) или FIND (НАЙТИ) – это абсолютно идентичные функции, которые выполняют поиск позиции определенной текстовой строки в заданной ячейке. Синтаксис формулы:

В качестве аргументов Вы должны указать: что нужно найти, где нужно искать, а также позицию символа, с которого следует начать поиск. В нашем примере SEARCH(» «,A2,1) или ПОИСК(» «;A2;1) говорит о том, что мы хотим найти символ пробела в ячейке A2 и начнём поиск с первого символа.

Замечание: Если поиск начинается с первого символа, Вы можете вообще пропустить аргумент start_num (нач_позиция) в формуле и упростить её до такого вида:

LEFT (ЛЕВСИМВ) и RIGHT (ПРАВСИМВ) – возвращает левую или правую часть текста из заданной ячейки соответственно. Синтаксис формулы:

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

LEN (ДЛСТР) – считает длину строки, то есть количество символов в заданной ячейке. Синтаксис формулы:

Следующая формула считает количество символов в ячейке A2:

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

Пример 2

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

=MID(A2,FIND(» «,A2,1)+1,FIND(» «,A2,FIND(» «,A2,1)+1)-(FIND(» «,A2,1)+1))
=ПСТР(A2;НАЙТИ(» «;A2;1)+1;НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1)-(НАЙТИ(» «;A2;1)+1))

=RIGHT(A2,LEN(A2)- FIND(» «,A2,FIND(» «,A2,1)+1))
=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1))

Функция MID (ПСТР) – извлекает часть текстовой строки (то есть заданное количество символов). Синтаксис:

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

Пример 3

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

=MID(A2,FIND(» «,A2,1)+1,FIND(» «,A2,FIND(» «,A2,1)+1)-(FIND(» «,A2,1)+1))
=ПСТР(A2;НАЙТИ(» «;A2;1)+1;НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1)-(НАЙТИ(» «;A2;1)+1))

=RIGHT(A2,LEN(A2)-FIND(» «,A2,FIND(» «,A2,1)+1))
=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1))

Пример 4

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

=MID(A2,SEARCH(» «,A2,1)+1,FIND(» «,A2,FIND(» «,A2,1)+1)-(FIND(» «,A2,1)+1))
=ПСТР(A2;ПОИСК(» «;A2;1)+1;НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1)-(НАЙТИ(» «;A2;1)+1))

=RIGHT(A2,LEN(A2)- FIND(» «,A2,FIND(» «,A2,1)+1))
=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1))

Пример 5

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

А вот пример реальных данных из Excel 2010. Данные из первого столбца разбиты на три отдельных столбца:

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: /> Перевел: Антон Андронов

Правила перепечаткиЕще больше уроков по Microsoft Excel

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

Как быстро выделить всю таблицу в excel

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

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

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

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

1. Вызовите команду поиска Главная (вкладка) → Редактирование (группа) → Найти и выделить → Найти. Или нажмите комбинацию горячих клавиш Ctrl + F.
2. Убедитесь, что в поле Найти пусто.
3. Нажмите кнопку Параметры.
4. Перейдите в Формат, в окне формата выберите вкладку Выравнивание и поставьте флажок объединение ячеек.
5. ОК.
6. В окне Найти и заменить нажмите Найти все.
7. Появятся адреса всех объединенных ячеек. Их можно выбрать по отдельности, или все сразу нажав Ctrl + A.

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

Как сделать из одного столбца два в excel?

1. Выделите все ячейки на листе. Можно щелкнуть на треугольнике, расположенном на пересечении заголовков строк и столбцов, либо горячей комбинацией Ctrl + A (один или два раза).
2. Повторно примените команду Главная (вкладка) → Выравнивание (группа) → Объединить и поместить в центре.

Использование мыши вместе с клавишами клавиатуры имеет одно преимущество перед использованием только клавиатуры – она ​​позволяет выбирать несмежные листы, как показано на рисунке выше, а также смежные листы.

Выберите строки с помощью мыши

Целую строку также можно выбрать с помощью:

1. Поместите указатель мыши на номер строки в заголовке строки – указатель мыши изменится на черную стрелку, указывающую вправо.
2. Нажмите один раз левой кнопкой мыши .

Несколько строк можно выбрать с помощью:

1. Поместите указатель мыши на номер строки в заголовке строки.
2. Нажмите и удерживайте левую кнопку мыши .
3. Перетащите указатель мыши вверх или вниз, чтобы выбрать нужное количество строк.

Если 2 столбца, которые Вы сравниваете, находятся на разных листах, то есть в разных таблицах, кликните правой кнопкой мыши выделенный диапазон и в контекстном меню выберите Delete Row (Удалить строку):

Excel поиск одинаковых значений в двух столбцах • Вэб-шпаргалка для интернет предпринимателей!

1. Нажмите на ячейку листа в столбце, который нужно выбрать, чтобы сделать его активной ячейкой.
2. Нажмите и удерживайте клавишу Ctrl на клавиатуре.
3. Нажмите и отпустите клавишу Пробел на клавиатуре.
4. Отпустите клавишу Ctrl .
5. Все ячейки в выбранном столбце должны быть выделены, включая заголовок столбца.

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

Дополнительные статьи

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

Как выделить каждую вторую строку в Excel — wikiHow

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

Если 2 столбца, которые Вы сравниваете, находятся на разных листах, то есть в разных таблицах, кликните правой кнопкой мыши выделенный диапазон и в контекстном меню выберите Delete Row (Удалить строку):

Выделение таблицы, списка или листа

Чтобы выделить список или таблицу, щелкните любую содержащуюся в них ячейку и нажмите клавиши CTRL+A.

Чтобы выделить весь лист, нажмите кнопку выделить все в верхнем левом углу.

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

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

Как добавить столбец в Эксель: самые простые способы

1. Зажимаем клавишу Shift на клавиатуре, устанавливаем курсор в верхнюю левую ячейку и кликаем левой кнопкой мыши.
2. Не отпуская клавишу Shift, прокручиваем лист к концу таблицы, если она не умещается по высоте в экран монитора. Устанавливаем курсор в нижнюю правую ячейку табличной области и опять кликаем левой кнопкой мыши.

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

Сравниваем 2 столбца в Excel и находим повторяющиеся записи при помощи формул

Вариант А: оба столбца находятся на одном листе

Если Вы хотите найти дубликаты в столбце B, поменяйте ссылки, чтобы формула приняла такой вид:

Вариант В: два столбца находятся на разных листах (в разных книгах)

Здесь Sheet3 – это название листа, на котором расположен 2-ой столбец, а $A$1:$A$10000 – это адреса ячеек от 1-ой до последней в этом 2-ом столбце.

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

Способ 1: простое выделение

• Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15 );
• На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
• Во всплывающем диалоговом окне выберите в левом выпадающем списке “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
• После этого в выделенной колонке будут подсвечены дублирующиеся строки:

Когда мы ищем совпадения между двумя столбцами в Excel, нам может потребоваться визуализировать найденные совпадения или различия в данных, например, с помощью выделения цветом. Самый простой способ для выделения цветом совпадений и различий – использовать “Условное форматирование” в Excel. Рассмотрим как это сделать на примерах ниже.

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

1. В программе Эксель можно добавить не один, а сразу несколько новых столбцов в таблицу.
2. Для этого, выделите по горизонтали несколько ячеек. А именно столько, сколько Вы хотите добавить новых столбцов.
3. Далее, если вы выделяли ячейки не через координатную панель, тогда смотрите как добавлять столбцы Выше.
4. Ну, а, в том случае, если Вы выделяли ячейки через координатную панель, то нажмите на правую кнопку компьютерной мыши.
5. Затем, перед Вами откроется окно.
6. В этом окне нажмите на «Вставить…».
7. После этого, слева от выбранных ячеек должны появится новый столбцы.

1. Для того, чтобы добавить новый столбец в конце таблицы этим способом Вам будет нужно сделать из простой таблицы «умную».
2. Для этого, выделите все ячейки Вашей таблицы.
3. Затем, нажмите на вкладку «Главная» в программе Excel и нажмите на «Форматировать как таблицу».
4. Перед Вами откроется список стилей оформления таблицы.
5. В этом списке выберите тот стиль оформления таблицы, который Вам подходит и нажмите на него.
6. После этого, перед Вами откроется окно.
7. В этом окне (в том случае, если Вы правильно выделили ячейки) нужно посмотреть есть ли галочка рядом с «Таблица с заголовками» (если её нет, то Вам будет нужно её поставить).
8. Далее, Вам будет нужно нажать «ОК».
9. После этого, Ваша таблица должна стать «умной».

И теперь, для того, чтобы добавить ещё один столбец в таблицу в программе Эксель Вам будет просто нужно заполнить одну из ячеек, которая находится справа от таблицы.

Этот столбец при заполнении станет частью «умной» таблицы.

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

Как добавить столбец в Excel (способ №2):

1. Для того, чтобы добавить столбец в Эксель этим способом Вам будет нужно нажать на ячейку, слева от которой Вам нужно добавить новый столбец.
2. Далее, Вам будет нужно нажать на «Вставить» на вкладке «Главная».
3. Перед Вами откроется выпадающий список. В этом выпадающем списке Вам будет нужно нажать на «Вставить столбцы на лист».
4. После этого, новый столбец появится слева от выбранной ячейки.

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