При указании диапазона ячеек в ms excel он используется как разделитель

Обновлено: 04.07.2024

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

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

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

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

[СЦЕПИТЬ + ТРАНСПОЗИРОВАТЬ] для объединения значений

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

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

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

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

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

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

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

Объединить текст с помощью параметра «По ширине заливки»

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

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

Прежде всего, не забудьте увеличить ширину столбца, в котором находится текст.
После этого выберите все ячейки.
В конце перейдите на вкладку "Главная" ➜ "Редактирование" ➜ "Заливка" ➜ "По ширине".

Это объединит текст из всех ячеек в первую ячейку выделения.

Функция TEXTJOIN для объединения значений

Если вы используете Excel 2016 (Office 365), существует функция TextJoin. Это может упростить объединение текста из разных ячеек в одну ячейку.

Синтаксис:

TEXTJOIN(разделитель, ignore_empty, text1, [text2], …)

разграничить текстовую строку для использования в качестве разделителя.
ignore_empty: true, чтобы игнорировать пустую ячейку, false, чтобы нет.
текст1 текст для объединения.
[text2] необязательный текст для объединения.

как им пользоваться

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

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

Объединение текста с Power Query

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

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

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

Код VBA для объединения значений

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

Обязательно укажите желаемое место в коде, где вы хотите объединить текст.

В конце

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

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

Какой из них вам понравился больше всего?

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

Подробнее о формулах Excel

56 мыслей

Оставить комментарий Отменить ответ

Очень полезная запись RE конкатенация диапазона ячеек в XLS; спасибо!

Все ваши решения превосходны.
Также преподается множество различных методов.
Хотя я очень хорошо владею Excel, я многому научился на вашем веб-сайте.
БОЛЬШОЕ СПАСИБО.
Боже благослови.

115750-760 ,765как преобразовать в столбец с последовательностью

Вы гений. Очень очень помогает мне. Большое спасибо.

Здравствуйте,
Возможно ли с помощью формулы, без vba и Power Tools, объединить 2 массива (сгенерированные формулой) в значение, не являющееся массивом, разделенное запятыми. Textjoin будет работать только с 1 массивом, но возвращает первый элемент, когда внутри формулы Textjoin есть разделение массива. Я также пытался использовать другую ячейку для деления без везения. Если вам нужно знать, почему я это делаю, я буду рад добавить больше деталей.
TQ, КР!

Большое спасибо за это, формула сработала идеально и сэкономила мне целую вечность. Спасибо 🙂

Спасибо за этот код VBA. Это прекрасно работает! Однако я хочу добавить в код еще один аспект и не могу в нем разобраться.

Я хочу, чтобы код находил ячейку на основе определенного текста, а затем выбирал все в списке под этой ячейкой. Это что-то легко выполнимое?

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

Я пишу бухгалтерскую электронную таблицу и хочу, чтобы столбец показывал сообщение об ошибке, если в столбцах, в которые я ввожу данные, или в столбцах расчета есть что-то неожиданное. В настоящее время это огромный длинный клубок вложенных операторов if и сообщений, соединенных операторами «&». Я хочу упростить это, настроив несколько столбцов, каждый из которых обнаруживает определенную ошибку и генерирует соответствующее сообщение, если находит ее. Это значительно упростит добавление дополнительной проверки ошибок в будущем — просто вставьте больше столбцов в эту часть таблицы.

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

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

Третий метод не работает, потому что я использую автономный Excel 2016, в котором нет TEXTJOIN. Это особенно расстраивает, потому что функция TEXTJOIN — это именно то, что мне нужно!

Однажды я собираюсь добавить в свою электронную таблицу некоторый код VBA, чтобы охватить такие вещи, как конец года (когда мне нужно отрезать данные за год и заменить их несколькими строками переносимых значений), но я никогда не программировал на VB (любой разновидности), так что это будет крупный проект. (Возможно, вместо этого я буду использовать код Python.) Проблема, которую я предвижу здесь, заключается в том, что код обычно выполняется, когда пользователь нажимает кнопку; формулы выполняются при изменении ячейки, от которой они зависят. Если я настрою программу VBA для выполнения каждый раз, когда я ввожу данные, и она будет вычислять это для каждой строки электронной таблицы, это приведет к огромным затратам на обработку! И если я (или кто-либо другой, использующий таблицу) не забуду нажать кнопку "Проверить на наличие ошибок", мы все равно не забудем проверить на наличие ошибок.

Поэтому остается Power Query. Я даже не слышал об этом инструменте, пока не прочитал вашу статью! Если (как я подозреваю) мне нужно щелкнуть что-то, чтобы обновить мой запрос, чтобы проверить наличие ошибок, тогда он страдает от той же проблемы, что и VBA. Но это стоит дальнейшего изучения. Спасибо, что обратили на это мое внимание.

Возможно, мне нужно перейти на Excel 2019. Теперь, когда я (я думаю) разобрался со всеми ошибками, возникшими при переходе с Excel 2007 на Excel 2016, это возможно. (Ошибки в основном касались изменения поведения SUMIF, когда диапазон данных и диапазон критерия были разной длины)

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

Возможно, сейчас это немного устарело. Однако, если приведенный выше VBA скорректирован, его можно использовать для возврата объединенных строк в виде обычной формулы, введите (что-то вроде) следующее в модуль VBA (Alt-F11, Insert-Module):

"Общая функция RANGECAT(rng1 как диапазон, необязательный rng2 как диапазон) как строка
Dim r1 как целое число, c1 как целое число, r2 как целое число, c2 как целое число
Dim cel как строка

cel = “”
Для c1 = 1 To rng1.Columns.Count
Для r1 = 1 To rng1.Rows.Count
cel = cel + rng1.Cells(r1, c1 )
Следующий r1
Следующий c1
Если rng2 равен нулю, то
Иначе
Для c2 = 1 To rng2.Columns.Count
Для r2 = 1 To rng2.Rows.Count
cel = cel + rng2.Cells(r2, c2)
Следующий r2
Следующий c2
Конец, если

RANGECAT = cel
Конец функции”

Комбинация «combineText = cel» возвращает найденное значение. При необходимости можно добавить дополнительные диапазоны (используя «Необязательный rng3 как диапазон, …» и повторяя rng2 для циклов).

У меня есть 1 запрос в VBA Пример: Режим доставки: Instant Air
И имя клиента: Джереми Лонсдейл
Синди Шнеллинг
Сьюзан Витторини
Тоби Браунхардт
Ральф Арнетт
Гарольд Энгл
Хелен Абельман
Гай Армстронг
Дженнифер Брэкстон
Джульетта Баптист

И мне нужен результат Instant Air
Рой Скария, Джереми Лонсдейл, Синди Шнеллинг, Сьюзан Витторини, Тоби Браунхардт, Ральф Арнетт, Гарольд Энгл, Хелен Абельман, Гай Армстронг, Дженнифер Брэкстон, Джульетта Баптист, Эрика Берн , Кристофер Шильд, Джой Смит, Эван Миннотт, Дженна Каффи,

Как и этот Instant Air Будет одна оставшаяся строка, все в одной строке с запятыми, как это возможно, дайте мне знать.

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

С уважением
Сандип Сингх

Привет, Пунит, когда я пытаюсь объединить ячейки, используя формулу Concatenate с разделителем «; «он возвращает «\» вместо «,» после того, как я нажму F9. Это как-то связано с настройками Excel? Я попробовал это на компьютере моего коллеги, и он работает как надо, и мы оба используем Excel 2010

В Excel есть два способа объединить содержимое нескольких ячеек:

Функция Excel CONCATENATE (или оператор амперсанда (&))
Функция Excel TEXTJOIN (новая функция в Excel, если у вас есть Office 365)

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

В своей базовой форме функция СЦЕПИТЬ может соединять 2 или более символов строк.

=CONCATENATE("Доброе","Утро") даст вам результат "Доброе утро".
=CONCATENATE("Доброе", ", "Утро") даст вам результат "Доброе утро".
=CONCATENATE(A1&A2) даст вам результат "Доброе утро" (где A1 содержит текст "Хорошо", а A2 – текст "Утро".

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

Например, в приведенном выше примере используется формула =СЦЕПИТЬ(A1:A5), в результате отображается только «Сегодня» и не объединяются все ячейки.

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

Примечание. Если вы используете Excel 2016, вы можете использовать функцию ОБЪЕДИНЕНИЕ СОЕДИНЕНИЕМ, созданную для объединения нескольких ячеек с помощью разделителя.

ОБЪЕДИНИТЬ диапазон Excel (без разделителя)

Вот шаги для объединения диапазона Excel без разделителя (как показано на рисунке):

Выберите ячейку, в которой вам нужен результат.
Перейдите к строке формул и введите =ТРАНСП(A1:A5)
- Исходя из региональных настроек, вы также можете попробовать =A1:A5 (вместо =ТРАНСПОНИРОВАТЬ(A1:A5))
Выделите всю формулу и нажмите F9 (это преобразует формулу в значения).
Удалите фигурные скобки с обоих концов.
Добавьте =CONCATENATE( в начало текста и завершите его круглой скобкой).
Нажмите Enter.

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

ОБЪЕДИНИТЬ диапазоны Excel (с разделителем)

Вот шаги для объединения диапазона Excel с пробелом в качестве разделителя (как показано на рисунке):

Выберите ячейку, в которой вам нужен результат.
Перейдите к строке формул и введите =ТРАНСП(A1:A5)&" "
- Исходя из региональных настроек, вы также можете попробовать =A1:A5 (вместо =ТРАНСПОНИРОВАТЬ(A1:A5)).
Выделите всю формулу и нажмите F9 (это преобразует формулу в значения).
Удалите фигурные скобки с обоих концов.
Добавьте =CONCATENATE( в начало текста и завершите его круглой скобкой).
Нажмите клавишу ВВОД.

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

ОБЪЕДИНЕНИЕ диапазонов Excel (с помощью VBA)

Ниже приведен пример пользовательской функции, которую я создал с помощью VBA (я назвал ее CONCATENATEMULTIPLE), которая позволит вам объединить несколько ячеек, а также указать разделитель/разделитель.

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

Вот шаги, чтобы скопировать этот код в Excel:

Теперь вы можете использовать эту функцию как любую обычную функцию рабочего листа в Excel.

СЦЕПИТЬ диапазоны Excel с помощью функции TEXTJOIN (доступно в Excel с подпиской на Office 365)

В Excel, входящем в состав Office 365, появилась новая функция — TEXTJOIN.

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

Вот синтаксис функции:

TEXTJOIN(разделитель, ignore_empty, text1, [text2], …)

разделитель — здесь вы можете указать разделитель (разделитель текста). Вы можете ввести его вручную или использовать ссылку на ячейку с разделителем.
ignore_empty – если значение TRUE, пустые ячейки будут игнорироваться.
текст1 — это текст, который необходимо соединить. Это может быть текстовая строка или массив строк, например диапазон ячеек.
[text2] – это необязательный аргумент, в котором можно указать до 252 аргументов, которые могут быть текстовыми строками или диапазонами ячеек.

Вот пример работы функции TEXTJOIN:

В приведенном выше примере в качестве разделителя указан пробел, который объединяет текстовые строки в формате A1:A5.

Подробнее о функции TEXTJOIN можно прочитать здесь.

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

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

БЕСПЛАТНАЯ КНИГА EXCEL

Получите электронную книгу «51 совет по Excel», чтобы резко повысить свою продуктивность и быстрее выполнять работу

54 мысли о «СЦЕПИТЬ диапазон Excel (с разделителем и без него)»

Большое спасибо брат!! из Филиппин

текстовое соединение — это круто

Textjoin = Больше никаких мучений с конкатенацией для многих столбцовых объединений. Спасибо!

Вау, это очень полезно, сэкономило мне МНОГО времени! Спасибо, чувак.

Ваш макрос только что спас мне жизнь! Спасибо

Привет!
Может ли кто-нибудь помочь мне с этой формулой или похожей, пожалуйста?

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

имя1 Имя2 Имя3
строка 0 1 2

=1 Имя2, 2 Имя3

Возможно ли это?
ТНК

Если у вас есть значения
Name1 Name2 Name3
0 1 2
в диапазоне A1:C2, вы можете вставить формулу
=IF(A2=0, ””,TEXTJOIN(” “,,A2,A1))
в ячейку A3 и заполните ее на две ячейки справа.
Затем вы получите
(Пустая ячейка), 1 Имя2, 2 Имя3
в ячейках A3:C3.

Большое спасибо. Это сработало отлично. У меня есть тысячи строк с разделителями «|», и эта функция отлично справилась. Благослови вас Бог.

Спасибо, очень полезно! Одно из предложений — убрать количество символов в разделителе (подходит для разделителей с несколькими символами, например, запятая с пробелом, например ", ").

CONCATENATEMULTIPLE = Left(Результат, Len(Результат) – Len(Разделитель))

Это более универсальное решение проблемы с разделителем пустых строк ("") в Rowan. Молодец и спасибо, что поделились.

Обратите внимание, что по какой-то причине в Excel MVBA 7.1 я не смог применить этот код напрямую. Мне пришлось применить простую математику из второго параметра в Len(), чтобы редактор VB принял код. То есть:

resLen = Len(Результат)
sepLen = Len(Разделитель)
tmpLen = resLen – sepLen

CONCATENATEMULTIPLE = Left(Result, tmpLen)

Большое спасибо за код и объяснение. Отличная функция 🙂

Здравствуйте, ищу код VBA для объединения всей строки, например (A1:A25 в A26). как это сделать проще всего...!!

Большое спасибо за множественный вариант – очень признателен

Concatenatemultiple — это фантастика! Единственное ограничение, которое я заметил, заключается в том, что если у вас нет разделителя (используя «»), то он обрезает последнее значение текста. Так с небольшой функцией if было исправлено:
If Separator = "" Then
CONCATENATEMULTIPLE = Left(Result, Len(Result))
Else
CONCATENATEMULTIPLE = Left(Result, Лен(Результат) – 1)
Конец, если

Спасибо за исправление. Я столкнулся с этим.

Код CONCATENATEMULTIPLE работает хорошо. Как насчет того, когда количество ячеек для конкатенации является переменным? Как будет выглядеть этот код?

Кто-нибудь знает способ сделать следующее:

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

Название проекта Результат
Проект1 Майк Проект1, Майк, Нил, Питер
Проект1 Нил
Проект1 Питер
Проект2 Майк Проект2, Майк, Нил, Питер
Проект2 Нил
Проект2 Питер

Спасибо, хорошая маленькая функция. Также адаптировано для использования значения «Len(Separator)»:

Функция CONCATENATEMULTIPLE(ссылка как диапазон, разделитель как строка) как строка

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

Для каждой ячейки в ссылке
Результат = Результат и Cell.Value и Разделитель
Следующая ячейка

CONCATENATEMULTIPLE = Left(Результат, Len(Результат) – Len(Разделитель))

Возможно, вы могли бы реализовать функцию TEXTJOIN из таблицы Google. Вот моя реализация:

Функция TEXTJOIN(разделитель как строка, skipEmpty как логическое значение, ссылка как диапазон) как строка
Dim i как целое число
Dim tmp как строка
для каждой ячейки в ссылке
If (Cell.Value "") Then
tmp = tmp & Cell.Value & separator
End If
Следующая ячейка
TEXTJOIN = Left(tmp, Len(tmp) – Len( разделитель))
Конец функции

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

Кто-нибудь знает способ сделать следующее:

СТОЛБЦ A содержит:
A
B
C

СТОЛБЦ B содержит:
10,
20,
30,
40

Что я хочу вывести в другой КОЛОННЕ:
A10
A20
A30
A40
B10
B20
B30
B40
C10
C20
C30
C40

Кто-нибудь знает, как это сделать?

Я хочу сделать то же самое. Вы поняли, как это сделать?

Одно из решений без VBA представлено выше.

Другое решение без VBA:
Введите формулу =СЧЁТЕСЛИ(A:A”?*”) в ячейку C1 (подсчитывает количество ячеек с текстом в столбце A)
Введите формулу =СЧЁТЕСЛИ( B:B”,>0″) в ячейку C2 (подсчитывает количество ячеек с числами >=0 в столбце B)
Введите формулу =ДВССЫЛ(АДРЕС(ЧАСТНОЕ(СТРОКА()-1,$C$2)+ 1,1)) в ячейку D1 и заполнять до тех пор, пока она не начнет выдавать нули.
Введите формулу =D1&ДВССЫЛ(АДРЕС(СЧЁТЕСЛИ(D$1:D1,D1),2)) в ячейку E1 и заполняйте до тех пор, пока в столбце D не появятся значения. Ваше решение должно находиться в столбце E.
PS: Формула в ячейке C1 дана как раз для составления пар с текстовыми данными.

Третье решение без VBA (в одну строку) можно выполнить с помощью формулы =ДВССЫЛ(АДРЕС(ЧАСТНОЕ(СТРОКА()-1,СЧЁТЕСЛИ(B:B">0″))+1,1))&ДВССЫЛ( ЕСЛИ(ОСТАТ(СТРОКА(),СЧЁТЕСЛИ(B:B">0″))0,АДРЕС(ОСТАТ(СТРОКА(),СЧЁТЕСЛИ(B:B">0″)),2),АДРЕС(СЧЁТЕСЛИ( B:B”,>0″),2))) в ячейку C1 и заполнять ее до тех пор, пока она не начнет давать лишние решения. Если вы объединяете текст в столбце B, замените все СЧЁТЕСЛИ(B:B">0″) в формуле на СЧЁТЕСЛИ(B:B">0") .

* Исправление:
Если вы объединяете текст в столбце B, замените все СЧЁТЕСЛИ(B:B,">0″) в формуле на СЧЁТЕСЛИ(B:B,"?*") .< /p>

Скопируйте значения из столбца A в столбец C, начиная с C2:C4. Скопируйте значения из столбца B в столбец D, начиная с D1, но с помощью Paste_Special>>Transpose, чтобы все выглядело как пустая таблица с буквами для строк и цифрами для столбцов. Теперь выберите ячейку D2 и введите формулу =$C2&D$1 (на этих местах должны стоять знаки доллара). Теперь заполните формулу до конца строк и столбцов. Теперь выберите все 12 значений и скопируйте их, не перемещая выделение, затем вставьте >> вставить_значения. Теперь вам нужно поместить значения в один столбец. Откройте новый лист, скопируйте 12 значений, выберите B1 и Paste_Special>>Transpose. Теперь вставьте формулу = ЕСЛИ (СТРОКА () * 1/4 = ЦЕЛОЕ (СТРОКА () * 1/4), СТРОКА () * 1/4, ЦЕЛОЕ (СТРОКА () * 1/4) + 1) в ячейку A1. Наиболее важным является число 4, потому что оно связано с количеством строк, заполненных значениями. *** В случае 50 строк с данными формула будет =ЕСЛИ(СТРОКА()*1/50=ЦЕЛОЕ(СТРОКА()*1/50),СТРОКА()*1/50,ЦЕЛОЕ(СТРОКА()* 1/50)+1).*** Заполните формулу (четверками) 12 раз (потому что у вас 12 значений).Теперь вставьте формулу =ДВССЫЛ(АДРЕС(СТРОКА()-(A5-1)*4,СТОЛБЦ()+(A5-1),4)) в ячейку B5 и заполните до B12. Наиболее важным в этой формуле является то, что ПЕРВЫЙ номер 4 играет ту же роль, что и в предыдущем, а A5 присутствует, потому что мы помещаем формулу в B5. *** В случае 50 строк с формулой данных будет = ДВССЫЛ (АДРЕС (СТРОКА () -(A51-1)*50,COLUMN()+(A51-1),4)) *** Число 4 в конце формулы является относительным адресом и не связано с вашим количеством строк.*** Столбец B — одно из решений без использования VBA.

Очевидно, что TEXTJOIN связан с Office 365, а НЕ с Excel 2016.

TEXTJOIN() НЕдоступна в моей настольной версии Excel 2016.

Привет, Корнан.. Ты прав! Я соответствующим образом отредактировал учебник.

Спасибо за это, идеальная замена формулы MCONCAT из ныне несуществующей (для всех пользователей 64-битной ОС) надстройки morefunc

Круто! Я искал для автообновления concatenate. Спасибо за код VBA.

Это здорово – спасибо!

Предлагаю внести одно изменение в код VBA: вместо использования
СЦЕПИТЬНЕСКОЛЬКО = Left(Результат, Длина(Результат) – 1) вы можете использовать
СЦЕПИТЬНЕСКОЛЬКО = Лево(Результат, Длина(Результат) – Лен(Разделитель)); это позволит использовать многосимвольные разделители.

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

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

Отлично! Это сделало именно то, что мне нужно, в сочетании с динамическими диапазонами.

В коде есть ошибка. ExcelConcatenate не равен CONCATENATEMULTIPLE, вы должны установить CONCATENATEMULTIPLE =

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

Очень-очень экономит время и интересно, брат, хорошие советы

Спасибо за комментарий.. Рад, что вам понравилось 🙂

Вы можете добавить образец документа?

Это действительно классное решение. Сэкономит много времени. Какой более продвинутый метод автоматически удаляет фигурные скобки?

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

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

По умолчанию Excel использует разделитель списка, определенный в региональных настройках на панели управления. В англоязычной версии Excel для США по умолчанию используется запятая (,) для разделителя списков, в то время как в других международных версиях может использоваться точка с запятой (;).

Это влияет на ввод функций в Excel. В США и таких странах, как Канада, Австралия, Великобритания и т. д., функции вводятся с аргументами, разделенными запятыми. В других странах, таких как Испания, Франция, Италия, Нидерланды, Германия, функции вводятся с точкой с запятой. Например, в США SUM будет вводиться следующим образом:

И вот так в Италии:

Примечание. Во многих случаях Excel автоматически переводит разделитель. Если вы откроете рабочий лист, созданный в Соединенных Штатах, Excel автоматически (и молча) изменит запятые на точки с запятой при открытии файла. Аналогичным образом, если вы измените разделитель списка при открытом листе, Excel изменит разделитель, используемый в формулах на этом листе.

Ошибка формулы с неправильным разделителем

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

Обратите внимание, что в этом диалоговом окне ничего не говорится о разделителях списков :)

Это особенно распространенная проблема, если вы находитесь в другом регионе и копируете пример формулы с веб-сайта в США.

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

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

В Windows этот параметр находится по адресу:

Панель управления > Регион и язык > Дополнительные настройки > Изменить формат даты, времени или чисел > дополнительные настройки

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

Настройки Excel

Excel имеет собственные настройки списка в меню «Параметры» > «Дополнительно» > «Использовать системные разделители»

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

Положение ячейки на листе определяется буквой ее столбца и номером строки (например, "A1"). Его часто называют адресом ячейки.
Все ячейки на листе разделены серыми линиями, известными как линии сетки, а группа смежных ячеек называется диапазоном ячеек.
Есть три типы оператора ссылки на ячейку, которые можно использовать для идентификации ячеек и диапазонов. Двоеточие, запятая и пробел.
Двоеточие ( : ) — используется для определения диапазона ячеек (например, "A1:D4"). Диапазон ячеек описывается с помощью ссылок на верхнюю левую ячейку и правую нижнюю ячейку, разделенных двоеточием.
Запятая ( , ) – используется для обозначения несмежных ячеек (например, "A1", "B2"). Запятая также известна как оператор объединения.
Пробел ( ) — используется для объединения пересечения двух блоков ячеек (например, "A1:D4 B2:C3"). При использовании этого оператора два блока ячеек должны перекрываться. Если нет, то появится сообщение об ошибке. Пробел также известен как оператор пересечения.
Диапазоны из нескольких ячеек обычно называются несмежными или несмежными диапазонами. Это диапазоны ячеек, которые не находятся в красивом прямоугольном блоке.

Ввод данных

В ячейки можно вводить данные трех разных типов. Иногда это число увеличивается до четырех, поскольку некоторые люди классифицируют даты и время как отдельный тип.
Числовой. Это в основном числа или значения. Сюда входят даты и время, которые обрабатываются как порядковые номера. Это позволяет манипулировать датами и временем в формулах, например добавлять и вычитать даты.
Текст. В основном это слова или любые текстовые данные. Они также известны как метки или строки. Текстовые записи считаются метками, поскольку с ними нельзя выполнять какие-либо вычисления.
Формулы. Все они начинаются со знака равенства (=) и позволяют выполнять математические операции.
Если вы выделите ячейку и начнете вводить текст, содержимое этой ячейки будет перезаписано.
>Вы можете вводить (и редактировать) содержимое ячеек либо с помощью строки формул, либо непосредственно с помощью ячейки.
Если вы не можете редактировать содержимое ячеек напрямую, проверьте (Инструменты > Параметры) (вкладка «Правка», «Редактировать непосредственно в ячейке»).

Выбор данных

Прежде чем вы сможете вводить данные в ячейку, вы должны сначала выбрать ячейку.
На листе может быть выбрано несколько ячеек одновременно, но в любой момент времени будет только одна активная ячейка. .
Активная ячейка всегда окружена черной рамкой.
Адрес активной ячейки всегда отображается в «поле имени» слева от строки формул.
Когда диапазон Ячейки выбраны, только верхняя левая ячейка считается активной ячейкой.
Если выбран диапазон ячеек, то активная ячейка определяется как белая.
Чтобы отменить выбор ячейки или диапазона ячеек щелкните в любом месте за пределами диапазона или нажмите любую из клавиш со стрелками.
Существует большое количество сочетаний клавиш, которые могут помочь вам выбрать данные, и, вероятно, наиболее полезной является (Ctrl + Shift + 8), которая выбирает текущую область .

Перемещение данных

Всякий раз, когда вы копируете ячейки, Excel сохраняет данные во временной памяти, называемой буфером обмена.
Данные будут оставаться там до тех пор, пока они не будут заменены другими данными. Он будет заменен только последующей командой «Вырезать» или «Копировать».
Сочетания клавиш (Ctrl + C), (Ctrl + X) и (Ctrl + V) соответствуют копированию, вырезанию и вставке данных соответственно и соответствуют другие приложения.
Вы также можете просто нажать Enter, чтобы вставить текущую запись из буфера обмена.

Сортировка данных

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

Удаление данных

Если вы хотите стереть содержимое ячейки, используйте меню (Правка > Очистить), а не удаляйте сами ячейки.
В качестве альтернативы вы можете нажать клавишу Delete.
Это гарантирует, что ваши формулы и форматирование чисел остается неизменным.

Отображение данных

Иногда, если число начинается с отрицательного знака, оно отображается только в виде дефиса, если ширина столбца недостаточна для всего числа. Просто нажмите F2 в ячейке или преобразуйте ее в формулу (префикс с равным).
Если вы использовали имена диапазонов строк и столбцов, чтобы обеспечить быстрый способ получить параметры в функциях рабочего листа, то "ЧИСЛО!" отображается, хотя получено правильное значение ??

Скрытие данных от печати

Необходимо создать распечатку, в которой необходимо скрыть определенные ячейки (не целую строку или столбец, вы можете щелкнуть правой кнопкой мыши и скрыть их) в области печати? Один из приемов — установить белый цвет шрифта в этих ячейках.
Выделите ячейки (Формат > Ячейки). В диалоговом окне "Формат ячеек" перейдите на вкладку "Шрифт"
Нажмите стрелку справа от списка "Цвет" и выберите белый цвет из списка
Нажмите "ОК", чтобы закрыть диалоговое окно и применить новый цвет шрифта< br /> Распечатайте лист, не показывая ненужные ячейки. Закончив печать, снова выберите ячейки и установите черный цвет (или автоматический).

Связывание ячейки

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

Добавление ссылок

Вы можете скопировать диапазон ячеек и вставить изображение на рабочий лист. Затем изображение автоматически обновляется при любых изменениях значений или форматирования. Удерживая нажатой клавишу Shift, выберите («Правка» > «Вставить ссылку на изображение»).

Важно

Excel по-разному обрабатывает числа и текст. Значения автоматически выравниваются по правому краю и отображаются в общем числовом формате. Текст автоматически выравнивается по левому краю.
При копировании ячеек будут скопированы их значения, формулы, форматирование и комментарии.
Ячейки не могут содержать более 32 000 символов.

Читайте также: