Как сделать несколько столбцов из одного столбца в Excel
Обновлено: 21.11.2024
При работе с данными и электронными таблицами большое значение имеют удобочитаемость и структура.
Это упрощает просмотр данных и работу с ними. Один из лучших способов сделать ваши данные более удобочитаемыми – разбить их на части, чтобы облегчить доступ к нужной информации.
При вводе данных с нуля можно гарантировать, что мы структурируем данные, чтобы они были более удобочитаемыми. Однако иногда вам нужно работать с данными, созданными кем-то другим.
Если объем данных очень велик, обычно довольно сложно структурировать читабельность данных.
Например, у вас могут быть данные со списком имен, и вы можете расположить имена в алфавитном порядке фамилий.
В других случаях у вас может быть список адресов, но вы хотите правильно упорядочить эти данные, чтобы четко видеть, сколько людей проживает, скажем, в Нью-Йорке.
Лучший способ решить две вышеупомянутые проблемы – разбить один столбец на несколько столбцов.
В новых версиях Excel есть специальная функция, позволяющая делать это с помощью меню Данные.
Давайте посмотрим, как этого можно добиться в обоих приведенных выше случаях.
Оглавление
Как разделить один столбец на несколько столбцов
Допустим, у вас есть список имен, который вы хотите разделить на столбцы Имя и Фамилия.
- Выберите столбец, который вы хотите разделить
- На ленте данных выберите «Текст в столбцы» (в группе Инструменты данных). Откроется мастер преобразования текста в столбцы.
- Здесь вы увидите параметр, позволяющий задать способ разделения данных в выбранных ячейках. Убедитесь, что этот параметр выбран. Если вы не знакомы с термином 'разделитель', это символ, указывающий, как данные в ячейках отделены друг от друга, например, имя и фамилия в каждой ячейке. разделены пробелом. Это означает, что разделителем здесь является символ пробела.
- Нажмите "Далее".
- Вот настройки, которые необходимо выполнить на втором этапе мастера Tetx to Columns:
- По умолчанию разделитель Tab установлен. Но мы не хотим этим пользоваться. Мы хотим использовать разделитель Пробел. Поэтому снимите флажок с разделителя Tab и установите флажок Пробел (1).
- Есть также флажок, который позволяет вам указать, хотите ли вы рассматривать последовательные разделители как один (2). Это означает, что если у вас по ошибке есть два пробела между именами, вы хотите рассматривать их как один пробел?
- Вы можете увидеть, как ваши данные будут выглядеть после разделения, в области предварительного просмотра данных (3) в нижней части диалогового окна. Обратите внимание, что при выборе пробела мы получаем именно тот результат, который нам нужен.
- Наконец нажмите «Далее» (4)
- Теперь вы увидите параметр, в котором можно указать формат данных в столбцах. По умолчанию выбран параметр Общие, который гарантирует, что столбцы имеют тот же формат, что и исходные ячейки. Оставьте его с выбранным параметром Общие и нажмите "Готово".
- Выберите столбец, который вы хотите разделить
- На ленте данных выберите «Текст в столбцы» (в группе «Инструменты данных»). Откроется мастер преобразования текста в столбцы.
- Здесь вы увидите параметр, позволяющий задать способ разделения данных в выбранных ячейках. Убедитесь, что этот параметр выбран.
- Нажмите "Далее".
- По умолчанию разделитель табуляции отмечен флажком. Снимите все отмеченные разделители и выберите вариант «Другое». В маленьком поле рядом с этой опцией вам нужно указать символ-разделитель, который вы хотите использовать. Если вы хотите указать символ разрыва строки. Нажмите Ctrl + J на клавиатуре. Это покажет крошечную мигающую точку внутри коробки. Это означает, что был вставлен разделитель разрыва строки.
- Нажмите "Далее".
- Теперь вы увидите параметр, в котором можно указать формат данных в столбцах. По умолчанию выбран параметр "Общие", который гарантирует, что столбцы имеют тот же формат, что и исходные ячейки.
- Здесь мы также хотим, чтобы все столбцы отображались, начиная со столбца B, чтобы новые ячейки не перезаписывали существующий столбец. Рядом с местом назначения мы видим написанную ячейку «$ A $ 2». Мы можем изменить это, выбрав нужную ячейку назначения «$B$2» и нажав «Готово».
- Вы можете получить диалоговое окно с вопросом, хотите ли вы заменить данные, которые уже присутствуют в целевых ячейках. Нажмите "ОК".
- Нажмите объединенную ячейку. Если вы хотите разъединить несколько ячеек, выберите их все.
- В разделе инструментов Выравнивание вкладки Главная вы увидите раскрывающийся список рядом с параметром Объединить и центрировать. Нажмите на стрелку раскрывающегося списка и выберите "Разъединить ячейки".
- Это приведет к разделению объединенной ячейки на исходное количество ячеек.
- › Как делить числа в Microsoft Excel
- › Как найти день недели по дате в Microsoft Excel
- › Как умножать числа в Microsoft Excel
- › Как перемещать столбцы и строки в Microsoft Excel
- › Как считать символы в Microsoft Excel
- › Как разделить имя и фамилию в Microsoft Excel
- › 5 вещей, которые вы, вероятно, не знали о GIF-файлах
- › Сколько оперативной памяти требуется вашему ПК?
- COLUMNS(Data): функция COLUMNS внутри функции MOD возвращает общее количество столбцов, доступных в именованном диапазоне (данные).
- СТРОКА(A1)-1+СТОЛБЦ(Данные): комбинация функций СТРОКА и СТОЛБЦ здесь определяет дивиденд функции ОСТАТ.
- MOD(СТРОКА(A1)-1+СТОЛБЦ(Данные), СТОЛБЦ(Данные))+1: эта часть определяет номер столбца функции ИНДЕКС, и для вывода функция возвращает «1».
- 1+INT((ROW(A1)-1)/COLUMNS(Data)): номер строки функции ИНДЕКС определяется этой частью, где функция INT округляет результирующее значение до целочисленной формы.
- написание макроса с помощью VBA
- разработка скрипта Power Query
- создание формулы массива с помощью CTRL-Shift-Enter
- написание расширенных формул с использованием таких функций, как ИНДЕКС, СЧЕТЧИК, СТРОКИ и СТОЛБЦЫ.
- приобретение сторонних надстроек, запрограммированных именно для такого преобразования
- Найти: «lg» (исключить двойные кавычки)
- Заменить на: = (знак равенства)
- Заменить все
- 1. Дважды щелкните ячейку, в которую вы хотите поместить объединенные данные, и введите =
- 2. Щелкните ячейку, которую вы хотите объединить, введите & и щелкните другую ячейку, которую вы хотите объединить. Если вы хотите включить больше ячеек, введите & и щелкните другую ячейку, которую вы хотите объединить, и т. д.
- 3. Нажмите Enter, когда вы выбрали все ячейки, которые хотите объединить.
- 1. Дважды щелкните ячейку, в которую вы хотите поместить объединенные данные, и введите =
- 2. Нажмите на ячейку, которую хотите объединить.
- 3. На этот раз введите &», «&», прежде чем щелкнуть следующую ячейку, которую вы хотите объединить. Если вы хотите включить больше ячеек, введите &», «&», прежде чем щелкнуть следующую ячейку, которую вы хотите объединить, и т. д.
- 4. Нажмите Enter, когда вы выбрали все ячейки, которые хотите объединить.
- 1. Вставьте функцию =CONCATENATE, как указано в инструкциях выше.
- 2. Введите ссылки на ячейки, которые вы хотите объединить, разделив каждую ссылку знаком ,", ", (например, B2,", ",C2,", ",D2). Это создаст пробелы между каждым значением.
- 3. Нажмите клавишу ВВОД.
- 1. Наведите указатель мыши на правый нижний угол только что созданной объединенной ячейки.
- 2. Когда курсор изменится на символ +, перетащите его вниз по столбцу и отпустите.
- 1. Дважды щелкните ячейку, в которую вы хотите поместить объединенные данные.
- 2. Введите =TEXTJOIN, чтобы вставить функцию
- 3. Введите «,», ИСТИНА, а затем ссылки на ячейки, которые вы хотите объединить, разделяя каждую ссылку запятой (роль ИСТИНА состоит в том, чтобы игнорировать пустые ячейки, которые вы можете ввести)
- 4. Нажмите клавишу ВВОД.
- 1. Наведите указатель мыши на правый нижний угол только что созданной объединенной ячейки.
- 2. Когда курсор изменится на символ +, перетащите его вниз по столбцу как можно дальше и отпустите.
- 1. Выберите все ячейки, содержащие ваши данные.
- 2. Введите имя для этой группы данных в поле «Имя» (поле, расположенное слева от строки формул). В этом примере я назвал данные «_my_data»
- 3. Выберите пустую ячейку на листе Excel, в которой вы хотите разместить сложенные данные.Введите следующую формулу ИНДЕКС (не забудьте заменить ее именем ваших данных):
- Загрузите или подключите существующий бюджет на основе Excel или Google Таблиц.
- Предоставьте доступ к различным листам или даже диапазонам ячеек вашей электронной таблицы различным заинтересованным сторонам или отделам, участвующим в процессе составления бюджета.
- Автоматизируйте потоки коммуникаций и следите за отправкой данных, участниками и сроками.
- Просматривайте все внесенные изменения и решайте, какие из них следует объединить с таблицей, а какие отбросить.
- Устранение ошибок в вашем бюджете или ручное копирование и вставка данных между файлами.
Теперь у нас есть два столбца данных: имя в столбце A и фамилия в столбце B.
Важно отметить, что при разделении содержимого ячейки Excel не вставляет новые ячейки для хранения содержимого.
Поэтому новые ячейки перезапишут содержимое следующей ячейки справа. Поэтому вы должны убедиться, что вы оставили пустое место справа перед разделением.
У вас также есть возможность выбрать место назначения разделенных данных.
Вы можете указать это на шаге 7, введя место, где вы хотите, чтобы разделенные ячейки отображались в поле ввода назначения. Здесь также можно выбрать ячейку назначения.
Само собой разумеется, что количество столбцов, на которые будут разбиты ваши данные, зависит от выбранных вами разделителей.
Это означает, что если у вас есть запятая в качестве разделителя и в некоторых ячейках есть три слова, разделенные запятыми, ваши данные будут разделены на три столбца.
Как разделить несколько строк в ячейке на несколько ячеек
Теперь давайте обсудим, как действовать в случаях, когда у вас есть много информации, представленной в отдельных строках ячейки.
Возьмите, например, лист ниже.
Здесь вы можете увидеть полный адрес, указанный в каждой ячейке. Каждая часть адреса находится на отдельной строке ячейки. Разделение этого столбца на четыре разных столбца, которые могут отображать полное имя человека, улицу, город и страну, значительно упростит выявление закономерностей в данных.
К сожалению, разделить ячейки несколькими строками не так просто, как метод, описанный выше. Но это тоже не слишком сложно. Вот как вы можете решить эту проблему.
Вы можете увидеть, как ваши данные будут выглядеть после разделения, в области предварительного просмотра данных в нижней части диалогового окна.
Обратите внимание, что мы получаем именно тот результат, который нам нужен. У нас есть все имена в первом столбце, вторые строки (названия улиц) во втором столбце, названия городов в третьем столбце и названия стран в четвертом столбце.
После этого вы найдете столбцы с B по E, каждый из которых содержит отдельный элемент адреса, который присутствовал в столбце A.
Как разделить объединенную ячейку
Прежде чем мы закончим статью, мы хотим добавить еще один случай. Возможно, у вас есть несколько объединенных ячеек, и вы ищете способ разъединить или разделить эти ячейки. Мы также хотели бы решить эту проблему на тот случай, если вы зашли на нашу страницу в поисках решения. Вот шаги:
Обратите внимание, что в Excel нет возможности разбить необъединенную ячейку на ячейки меньшего размера (как это возможно в MS Word).
Мы обсудили, как разделить ячейки в Excel на отдельные ячейки, используя разные типы разделителей. Мы также кратко рассмотрели, как разделить ячейки, которые ранее были объединены.
Вышеупомянутые шаги были указаны при условии, что вы используете версии Excel с 2013 по 2019. Однако Microsoft продолжает обновлять свои меню, вкладки и параметры в каждой новой версии, которую она выпускает.
Поэтому мы не можем точно сказать, будут ли упомянутые нами методы работать в более поздних версиях Excel.
Бен Стоктон
Бен Стоктон
Писатель
Слишком много данных в одном столбце может затруднить чтение электронной таблицы Microsoft Excel. Чтобы улучшить его, вам следует рассмотреть возможность разделения столбца с помощью функций «Текст в столбцы» или «Мгновенное заполнение».
«Текст в столбцы» заменит один столбец несколькими столбцами с теми же данными. «Мгновенное заполнение» реплицирует данные, разделяя их на новые отдельные столбцы, оставляя исходный столбец нетронутым.
Как использовать текст в столбцах в Excel
Microsoft Excel включает специальную функцию, позволяющую разбивать очень длинные столбцы. Для этого столбцы разделяются разделителями, такими как запятые или точки с запятой, которые разделяют данные.
Эта функция работает с помощью функции «Текст в столбцы», доступ к которой можно получить на вкладке «Данные» на панели ленты Microsoft Excel.
Чтобы протестировать эту функцию, мы будем использовать набор данных (список сотрудников, показ имен, дат рождения и другую информацию) в одном столбце. Каждый раздел данных находится в отдельной ячейке, разделенной точкой с запятой.
Сначала вам нужно выбрать ячейки, содержащие ваши данные (ячейки с A1 по A12 в приведенном выше примере).
На вкладке "Данные" Excel нажмите кнопку "Текст в столбцы" в разделе "Инструменты данных".
Текст в столбцы" width="650" height="184" />
Откроется окно «Мастер преобразования текста в столбцы», в котором вы сможете начать разделение данных. В параметрах выберите переключатель «С разделителями» и нажмите «Далее», чтобы продолжить.
По умолчанию Excel попытается разделить данные одного столбца по каждой найденной вкладке. Это нормально, но в нашем примере мы используем данные, разделенные точкой с запятой.
Выберите разделитель в боковом меню. В нашем примере в качестве разделителя выбрана точка с запятой.
Вы можете увидеть, как будут выглядеть преобразованные данные, в разделе «Предварительный просмотр данных» в нижней части меню.
Когда будете готовы, нажмите "Далее", чтобы продолжить.
Теперь вам нужно установить типы ячеек для каждого столбца. Например, если у вас есть столбец с датами, вы можете установить соответствующий формат даты для этого столбца. По умолчанию для каждого столбца будет установлено значение «Общие».
При использовании этого параметра Excel попытается автоматически установить тип данных для каждого столбца. Чтобы установить их вручную, сначала нажмите на свой столбец в разделе «Предварительный просмотр данных». Оттуда выберите соответствующий тип данных в разделе «Формат данных столбца».
Если вы хотите полностью пропустить столбец, выберите его, а затем выберите параметр «Не импортировать столбец (пропустить)». Нажмите «Готово», чтобы начать преобразование.
Ваш единственный столбец будет разделять каждый раздел с помощью разделителей на отдельные столбцы с использованием выбранных вами параметров форматирования ячеек.
Как использовать мгновенное заполнение в Excel
Если вы хотите сохранить исходные данные без изменений, но при этом разделить данные, вы можете вместо этого использовать функцию "Быстрая заливка".
В нашем примере со списком сотрудников у нас есть строка заголовка с одним столбцом (столбец A) с точкой с запятой, разделяющей каждый бит данных.
Чтобы использовать функцию «Быстрое заполнение», начните с ввода заголовков столбцов в строке 1. В нашем примере «Идентификатор сотрудника» будет помещен в ячейку B1, «Имя» — в ячейку C1 и т. д.
Для каждого столбца выберите строку заголовка. Начните с B1 (в этом примере заголовок «Идентификатор сотрудника»), а затем в разделе «Инструменты данных» на вкладке «Данные» нажмите кнопку «Быстрое заполнение».
Flash Fill" width="650" height="310" />
Повторите действие для каждой из ячеек заголовка (C1, D1 и т. д.), чтобы автоматически заполнить новые столбцы соответствующими данными.
Если данные в исходном столбце отформатированы правильно, Excel автоматически разделит содержимое, используя исходную ячейку заголовка (A1) в качестве ориентира. Если вы получили сообщение об ошибке, введите следующее значение последовательности в ячейку под ячейкой заголовка, а затем снова нажмите кнопку "Быстрая заливка".
В нашем примере это будет первый пример данных в ячейке B2 ("101") после ячейки заголовка в B1 ("Идентификатор сотрудника").
Каждый новый столбец будет заполнен данными из исходного столбца, используя исходные первую или вторую строки в качестве ориентира для выбора правильных данных.
В приведенном выше примере длинный столбец (столбец A) был разделен на шесть новых столбцов (от B до G).
Поскольку расположение строк с 1 по 12 одинаковое, функция "Быстрое заполнение" позволяет копировать и разделять данные, используя строку заголовка и первый бит данных.
В Microsoft Excel существует несколько подходящих способов объединения нескольких столбцов в один. В этой статье вы узнаете, как применять различные подходы к объединению данных из нескольких столбцов в один столбец с примерами и надлежащими иллюстрациями.
Загрузить практическую рабочую тетрадь
Вы можете скачать книгу Excel, которую мы использовали для подготовки этой статьи.
6 подходов к объединению нескольких столбцов в один столбец в Excel
1. Использование функции CONCATENATE или CONCAT для объединения нескольких столбцов в Excel
На следующем рисунке три столбца представляют некоторые случайные адреса с разделенными частями. Мы должны объединить каждую строку, чтобы получить осмысленный адрес в столбце E под заголовком «Комбинированный текст».
Для этой цели мы можем использовать функцию СЦЕПИТЬ или СЦЕПИТЬ. В первой выходной ячейке E5 требуемая формула будет следующей:
Или
После нажатия Enter и использования Fill Handle для автозаполнения остальных ячеек в столбце E мы получим объединенный один столбец, как показано на рисунке ниже.
2. Использование амперсанда (&) для объединения нескольких столбцов в один столбец
Мы также можем использовать амперсанд (&), чтобы упростить конкатенацию или соединение текстов. Предположим, что у нас нет разделителя с текстами в ячейках, но при объединении текстов из строки нам придется вставить разделитель.
В выходной ячейке E5 необходимая формула с использованием амперсанда (&) будет выглядеть так:
Нажмите Enter, автоматически заполните весь столбец E, и вы сразу же соберете все объединенные тексты в один столбец.
3. Вставьте функцию TEXTJOIN для объединения нескольких столбцов в столбец в Excel
Если вы используете Excel 2019 или Excel 365, функция TEXTJOIN — еще один отличный вариант для достижения ваших целей.
Требуемая формула для объединения нескольких текстов с помощью функции СОЕДИНЕНИЕ ТЕКСТОВ в ячейке E5 будет следующей:
После того, как вы нажмете Enter и перетащите вниз к последней ячейке в столбце E, вы сразу получите объединенные тексты в один столбец.
4. Объединение нескольких столбцов в один столбец в Excel
Теперь в нашем наборе данных есть 4 случайных столбца в диапазоне от столбца B до столбца E. Под заголовком «Объединить столбец» мы последовательно сложим значения из 4-й, 5-й и 6-й строк. Одним словом, сложим все данные в один столбец.
📌 Шаг 1:
➤ Выберите диапазон ячеек (B4:E6), содержащих основные данные.
➤ Назовите его текстом в поле имени.
📌 Шаг 2:
➤ В выходной ячейке G5 введите следующую формулу:
📌 Шаг 3:
➤ Нажмите Enter, и вы получите первое значение из 4-й строки в ячейке G5.
И, наконец, вы увидите следующий вывод.
🔎 Как работает формула?
5. Использование Блокнота для объединения данных столбцов в Excel
Мы также можем использовать Блокнот, чтобы объединить несколько столбцов в один столбец. Давайте пройдемся по следующим шагам:
📌 Шаг 1:
➤ Выберите диапазон ячеек (B5:D9), содержащий основные данные.
➤ Нажмите CTRL+C, чтобы скопировать выделенный диапазон ячеек.
📌 Шаг 2:
➤ Откройте файл блокнота.
➤ Вставьте CTRL+V, чтобы вставить сюда выбранные данные.
📌 Шаг 3:
➤ Нажмите CTRL+H, чтобы открыть диалоговое окно "Заменить".
➤ Выберите вкладку между двумя текстами в блокноте и скопируйте ее.
➤ Вставьте его в поле "Найти".
📌 Шаг 4:
➤ Введите ", " в поле "Заменить на".
➤ Нажмите «Заменить все». Готово.
Все данные в файле блокнота будут выглядеть так, как показано на следующем рисунке.
📌 Шаг 5:
➤ Теперь скопируйте весь текст из блокнота.
📌 Шаг 6:
➤ И, наконец, вставьте его в выходную ячейку E5 в электронной таблице Excel.
Результирующие данные в столбце E теперь будут следующими:
6. Использование сценария VBA для объединения столбцов в один столбец в Excel
Мы также можем использовать метод VBA для объединения нескольких столбцов в один столбец. На следующем рисунке в столбце G показаны сложенные данные.
📌 Шаг 1:
➤ Щелкните правой кнопкой мыши имя листа в книге и нажмите "Просмотреть код".
Появится новое окно модуля, куда вам нужно будет просто вставить следующие коды:
📌 Шаг 2:
➤ После вставки кодов нажмите F5, чтобы запустить код.
➤ Назначьте имя макросу в диалоговом окне "Макрос".
➤ Нажмите "Выполнить".
📌 Шаг 3:
➤ Выберите основной диапазон данных (B4:E6) в поле "Выбрать диапазон".
➤ Нажмите OK.
📌 Шаг 4:
➤ Выберите выходную ячейку G5 после включения поля "Целевой столбец".
➤ Нажмите OK, и все готово.
Как показано на следующем рисунке, в выходном столбце будут показаны объединенные и сгруппированные данные.
Заключительные слова
Я надеюсь, что все эти простые методы, упомянутые выше, теперь помогут вам применять их в своих электронных таблицах Excel, когда это необходимо. Если у вас есть какие-либо вопросы или отзывы, пожалуйста, дайте мне знать в разделе комментариев. Или вы можете ознакомиться с другими нашими статьями, посвященными функциям Excel, на этом веб-сайте.
В этом руководстве по Excel мы рассмотрим самый простой способ разгруппирования данных.
Преимущество этого метода в том, что он динамический, поэтому при изменении исходных данных составная версия обновляется автоматически.
Если вы когда-нибудь пытались взять столбец данных в Excel, как показано ниже…
и преобразовать его во что-то вроде этого…
Для такого преобразования существует множество различных решений. Вот некоторые из этих решений:
В этом посте вы узнаете, что мы считаем «самым ленивым способом» распаковки данных. Но не позволяйте слову «ленивый» сбить вас с толку; это может быть САМЫЙ БЫСТРЫЙ и простой способ решить проблему.
Шаг 1. Настройте заголовки
В нашем образце файла мы создадим следующие заголовки в ячейках с C5 по E5.
Шаг 2. Создайте справочные формулы
Теперь создайте ссылки на ячейки в ячейках с C6 по E6, которые создадут структуру, представляющую то, как вы хотите, чтобы данные выглядели.
Шаг 3. Примените трюк с заменой «LG»
Замените знаки равенства в ячейках с C6 по E6 буквами «lg» (это мои инициалы; не стесняйтесь персонализировать этот прием своими инициалами.)
Шаг 4. Создайте ссылки на шаблоны
Нам нужно выяснить закономерность, по которой имена приложений появляются в исходном стеке данных. Мы видим, что в каждой третьей строке есть новое название приложения, а также новое значение продажи и новое значение прибыли в каждой третьей строке.
Используя наш трюк с заменой «LG», создайте вторую строку ссылок на ячейки в ячейках с C7 по E7, каждая со смещением на три строки.
Шаг 5. Создайте полный список литературы
Теперь, когда у нас есть установленный шаблон для работы с Excel, выделите все опорные ячейки (C6:E7). Нажмите и удерживайте маркер «Заполнить ряд» и перетащите его вниз. Наш первоначальный список заканчивается строкой 87. Мы создадим список, содержащий несколько ссылок после строки 87, чтобы можно было расширить список позже.
Обратите внимание, как Excel распознал шаблон ссылки на строку в каждом столбце и продолжил ссылку.
Шаг 6. Преобразование ссылок «LG» в реальные формулы
Выделите все ячейки, содержащие ссылки «LG», и выполните следующую операцию «Найти/Заменить» (Ctrl‑H):
По завершении закройте диалоговое окно "Найти и заменить".
Шаг 7. Изучите красивый список преобразованных данных
Обратите внимание, что в последних нескольких строках нового списка есть нули. Они будут автоматически обновляться при добавлении новых элементов в исходный список.
Если вы не хотите видеть нули, рассмотрите возможность применения к списку следующего пользовательского числового формата.
Активируйте диалоговое окно "Формат ячеек" (CTRL-1) -> Число (вкладка) -> Пользовательский (категория) -> Тип (поле) и введите следующее:
Пользовательские числовые форматы определяют, как отображаются положительные числа, отрицательные числа, нули и текст. Определив формат для положительных и отрицательных чисел, но опустив коды форматирования для нулей, Excel не сможет отобразить нулевое значение в одной из этих настраиваемых ячеек.
Дополнительный этап: добавление дополнительных данных
Поскольку мы включили дополнительные строки ссылок на ячейки, если мы добавим новую запись в конец исходного списка в столбце A…
… вновь добавленные данные автоматически появляются в нижней части нашего стека транспонированных данных.
Альтернатива формулы
Если вы хотите создать формулу, чтобы не полагаться на «ленивый» метод, следующая формула даст желаемый результат:
Давайте разберем эту формулу.
$A$4:$A$87 — исходный список элементов в столбце A.
ROWS($H$6:H6) будет подсчитывать строки в заданном диапазоне. Диапазон расширяется за счет блокировки первой ссылки и оставления второй относительной ссылки.Когда формула повторяется в нисходящих строках, количество строк увеличивается (1, затем 2, затем 3 и т. д.).
COUNTA($H$5:$J$5) вернет количество непустых ячеек в диапазоне H5:J5; в данном случае 3.
COLUMNS($H$5:H5) будет подсчитывать столбцы в указанном диапазоне. Диапазон расширяется за счет блокировки первой ссылки и оставления второй относительной ссылки. Когда формула повторяется в соседних столбцах, количество столбцов увеличивается (1, затем 2, затем 3).
Используя первый экземпляр формулы в ячейке H6 для получения первого приложения, первоначальные оценки выглядят следующим образом:
=ИНДЕКС($A$4:$A$87,1+(3-1)*(1-1)+1-1)
Что сводится к…
=ИНДЕКС($A$4:$A$87,1+(2)*(0)+0)
Дальнейшее сокращение до…
=ИНДЕКС($A$4:$A$87,1)
Это вернет элемент в первую ячейку списка; в данном случае ячейка A4 (приложение под названием «WenCal»).
Если мы посмотрим на это для формулы в той же строке, но для продаж, мы увидим следующую логику:
=ИНДЕКС($A$4:$A$87,1+(3-1)*(1-1)+2-1)
Что сводится к…
=ИНДЕКС($A$4:$A$87,1+(2)*(0)+1)
Дальнейшее сокращение до…
=ИНДЕКС($A$4:$A$87,2)
Это вернет элемент во второй ячейке списка; в данном случае ячейка A5 (цена продажи 14432).
Формула требует серьезного анализа, но подведем итог…
ROWS($H$6:H6)+(COUNTA($H$5:$J$5)-1)*(ROWS($H$6:H6) будет вычислять местоположение следующего приложения в исходном списке.< /p>
COLUMNS($H$5:H5)-1 добавит 0, 1 или 2 к предыдущему вычислению. Это компенсирует позицию, чтобы обеспечить возврат соответствующей стоимости продажи или прибыли.
Использование инструмента "Оценить формулу", расположенного на вкладке "Формулы", группы "Аудит формул" очень поможет в пошаговом анализе логики.
У вас возникают трудности при объединении двух или более столбцов Excel? Знание того, как объединить несколько столбцов в Excel без потери данных, поможет вам сэкономить время, объединить данные и сделать таблицу более аккуратной.
Прежде всего, вы должны знать, что существует несколько способов объединения данных из двух или более столбцов в Excel. Прежде чем мы приступим к изучению этих различных способов, давайте начнем с ключевого шага, который поможет в этом процессе — как объединить ячейки в Excel.
Как объединить несколько ячеек или столбцов в Excel без потери данных?
После того, как вы объедините ячейки, научиться объединять несколько столбцов Excel в один столбец станет интуитивно понятным.
Независимо от того, учитесь ли вы объединять две ячейки в Excel или десять, одно из основных преимуществ объединения заключается в том, что формулы не меняются. Вот следующие способы объединения ячеек или столбцов в Excel:
Используйте амперсанд (&), чтобы объединить две ячейки в Excel
Если вы хотите узнать, как объединить две ячейки в Excel, вот самый быстрый и простой способ сделать это без потери ваших данных.
Хотя это удобно для быстрого объединения данных в одну ячейку, объединенные данные не будут форматироваться. В некоторых случаях это может сделать данные неаккуратными или сложными для чтения (например, полные имена или адреса).
Если вы хотите добавить знаки препинания или пробелы (разделители), выполните следующие действия. В этом примере давайте поставим запятую и пробел между именем и фамилией, как вы видите в списке регистрации:
Как видите, теперь ваши объединенные данные получаются в более аккуратном формате, каждый фрагмент данных соответствующим образом разделен.
Используйте функцию СЦЕПИТЬ для объединения нескольких столбцов в Excel
Этот метод похож на метод амперсанда, а также позволяет форматировать объединенные данные. Во-первых, вам нужно использовать функцию СЦЕПИТЬ, чтобы объединить строку ячеек:
Теперь, когда вы успешно объединили свои ячейки, вы можете выполнить следующие простые шаги, чтобы объединить несколько столбцов:
Отпустив кнопку мыши, вы должны увидеть, что ваша объединенная ячейка стала объединенным столбцом, содержащим все данные из выбранных вами столбцов.
*Функция CONCAT — это еще одна формула, используемая для объединения данных из разных ячеек. Однако он ограничен двумя ссылками и не позволяет включать разделители.
Откройте для себя самые популярные методы, используемые для ручного или автоматического объединения нескольких электронных таблиц Excel и входных данных в один мастер-файл
Используйте функцию TEXTJOIN для объединения нескольких столбцов в Excel
Этот метод работает только с Excel 365, 2021 и 2019. Как вы, вероятно, заметили, эта функция полезна, когда вы хотите объединить две или более текстовых ячеек в Excel.
Следующие шаги покажут вам, как использовать функцию ОБЪЕДИНЕНИЕ СОЕДИНЕНИЕМ, еще раз используя комбинацию запятой и пробела для создания вашей первой объединенной ячейки:
Чтобы создать остальную часть комбинированного столбца, выполните действия перетаскивания, перечисленные ниже:
Теперь ваши столбцы данных успешно объединены в новый столбец.
Руководство по управлению версиями Excel для начинающих
Узнайте, что такое управление версиями Excel, какие функции управления версиями предлагает Excel и как их использовать для совместного использования, объединения и просмотра изменений Excel
Используйте формулу ИНДЕКС, чтобы сложить несколько столбцов в один столбец в Excel
Допустим, вы хотите создать стопку данных из нескольких столбцов, а не одну ячейку. Вы можете легко сделать это в нескольких ячейках и столбцах электронной таблицы, используя формулу ИНДЕКС:
Другие способы объединения нескольких столбцов в Excel: блокнот и сценарий VBA
Есть еще два способа объединить несколько столбцов в Excel. Это часто требует больше времени и использует другие инструменты как часть процесса. Однако они могут быть более полезными для пользователей, которые не хотят использовать формулы Excel.
Используйте Блокнот для объединения нескольких столбцов в Excel
Вы можете использовать Блокнот для извлечения, форматирования и замены данных из нескольких столбцов в Excel. Для этого вам нужно скопировать и вставить каждый столбец из вашего листа Excel в файл Блокнота. Затем используйте функцию «Заменить», чтобы добавить запятые между каждым значением. После завершения вы можете скопировать и вставить отформатированные данные обратно в Excel.
Используйте сценарий VBA для объединения двух или более столбцов в Excel
В качестве альтернативы методу объединения функций ИНДЕКС можно использовать скрипт VBA. Просто щелкните правой кнопкой мыши и выберите «Просмотр кода» в Excel, а затем скопируйте и вставьте код в новое окно. Нажмите «F5», чтобы запустить код и создать макрос. Затем вы можете применить это к своему Excel, выбрав диапазон данных и применив его к целевому столбцу.
Как объединить данные Excel со слоем?
Layer — это платформа для работы с электронными таблицами, работающая поверх Excel и Google Sheets. Это позволяет вам легко управлять и автоматизировать рабочие процессы электронных таблиц. С помощью слоя вы можете:
Зарегистрируйтесь и запланируйте вводный звонок, чтобы начать работу с Layer прямо сейчас.
Заключение
Как видите, в Excel легко объединить несколько столбцов. Независимо от того, просматриваете ли вы несколько файлов Excel или столбцы и ячейки, существует множество способов, которые подходят разным пользователям в зависимости от их технических возможностей или потребностей.
В результате вы можете не только отформатировать Excel в связную и бесшовную электронную таблицу, но и сэкономить время и оптимизировать свою производительность при оценке, управлении или обмене важными данными. Когда вы научитесь объединять несколько столбцов в Excel в один столбец, объединение или слияние ваших данных может стать одной быстрой и простой задачей.
Хейди увлекается технологиями, маркетингом и электронными таблицами. Помимо степени в области компьютерных наук, он имеет большой опыт разработки, запуска и масштабирования процессов контент-маркетинга в стартапах SaaS.
Первоначально опубликовано 10 января 2022 г., обновлено 20 февраля 2022 г.
Похожие сообщения
Шаблон планирования численности и рабочей силы
Управляйте и сохраняйте подробный обзор данных о вашей рабочей силе, рассчитывайте заработную плату и прогнозируйте будущие изменения, чтобы помочь вашему бизнесу работать бесперебойно
Читайте также: