Создайте разделенный запятыми список содержимого ячейки, исключая пробелы

Question

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

=CONCATENATE(A2,",",C2",",D2,",",F2)

Это сделало бы это так, чтобы ...

• A2 = "Мэтью"
• C2 = "Марка"
• D2 = "Люк"
• F2 = "Джон"

приведет к Matthew,Mark,Luke,John .

Но мы сталкиваемся с проблемами с чем-то вроде ...

• A2 = "Иисус"
• C2 = ""
• D2 = "Мэри"
• F2 = "Джозеф"

что приведет к Jesus,,Mary,Joseph .

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

Конечно, это можно сделать с помощью достаточного количества вложенных IF, но я действительно хочу избежать этого, если это возможно. Можно ли это сделать с помощью собственных функций Excel или, возможно, с формулой массива? Или нужно прибегнуть к VB Script для чего-то подобного?

Answer 1

В Excel 2016 вы можете использовать TEXTJOIN:

=TEXTJOIN(delimiter,ignore_blanks?,values_to_be_joined)

Итак, в вашем случае вы бы использовали эту формулу:

=TEXTJOIN(",",TRUE,A2,C2,D2,F2)

Answer 2

Как вы сказали, вложенные IF - это не ваша чашка чая, но я не вижу другого способа сделать это без решения VBA, указанного @nixda.

Для решений, которые я представляю, я предполагаю, что ваши данные похожи на следующее:

 |    A    |
-+---------+
1| Matthew |
2| Mark    |
3| Luke    |
4| John    |

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

=CONCATENATE(A1;IF(ISBLANK(A2);"";"," & A2);IF(ISBLANK(A3);"";"," & A3);IF(ISBLANK(A4);"";"," & A4))

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

= A1 & IF(ISBLANK(A2); ""; "," & A2) & IF(ISBLANK(A3); ""; "," & A3) & IF(ISBLANK(A4); ""; "," & A4)

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

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

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

• Ячейки перед активной ячейкой пусты?
• Активная ячейка пуста?
• Ничего из вышеперечисленного не правда? (условие по умолчанию)

Необходимое форматирование, если вы начали рассуждение с самого начала, выглядит следующим образом:

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

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

=CONCATENATE(A1;IF(ROWS(A1:A1)=COUNTBLANK(A1:A1);A2; IF(ISBLANK(A2);"";"," & A2));IF(ROWS(A1:A2)=COUNTBLANK(A1:A2);A3; IF(ISBLANK(A3);"";"," & A3));IF(ROWS(A1:A3)=COUNTBLANK(A1:A3);A4; IF(ISBLANK(A4);"";"," & A4)))

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

Интервал в ROWS и COUNTBLANK - это ячейки за активной ячейкой.

Answer 3

Я полагаю, вы ищете простое решение, а не улучшить свои навыки.

Вы можете использовать надстройку, если это разрешено (то есть у вас есть разрешение на установку надстроек). Я настоятельно рекомендую ASAP Utilities , которая бесплатна для некоммерческого использования.

Вот что вы можете сделать с одной из их функций

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

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

Answer 4

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

Установите ячейку AA2 (или Sheet2!A2 , или где вы хотите его поставить)

=IF(A2="", "", A2&",")

(где x & y - это просто более короткий способ CONCATENATE(x, y)), и установите ячейки AC2 , AD2 и AF2 аналогичным образом.  Установите AZ2 на

=AA2 & AC2 & AD2 & AF2

Тогда ваш окончательный результат

=IF(AZ2="", "", LEFT(AZ2, LEN(AZ2)-1))

Объяснение:

Ячейки AA - AF добавляют запятые к непустым значениям.  Итак, используя ваш пример,

Matthew         Mark            Luke            John

приведет к

Matthew,        Mark,           Luke,           John,

в то время как

Jesus                           Mary            Joseph

приведет к

Jesus,                          Mary,           Joseph,

(обратите внимание на отсутствие запятой в столбце C). 

AZ2 - это простое объединение вышеупомянутого: Jesus,Mary,Joseph, поэтому мы удалили лишнюю запятую после Jesus но добавили одну в конце.  LEFT(AZ2, LEN(AZ2)-1) - это все AZ2 за исключением последнего символа.  И нам нужно проверить, является ли AZ2 нулевым, чтобы избежать ошибки, если все четыре входных ячейки пусты.

Answer 5

Я предполагаю, что ваши данные имеют заголовки и начинаются в A2.

1. Перейти к любой запасной колонке, я буду использовать B в этом примере
2. В ячейку B2 введите просто = A2
3. В ячейку B3 введите = IF(A3 = "", B2, CONCATENATE(B2, ",", A3)
4. Скопируйте столбец B из B3 (не B2!) до последней строки, содержащей данные в столбце A

Вуаля, ваш список через запятую находится в последней ячейке в столбце B :)

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

Answer 6

Если вы хотите общее решение, лучшим ответом будет VBA.

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

=SUBSTITUTE(CONCATENATE(A2,",",C2",",D2,",",F2),",,",",")

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

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(A2,",",C2,",",D2,",",F2),",,,,,,,,,,,,,,,,",","),",,,,,,,,",","),",,,,",","),",,,",","),",,",",")

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

Answer 7

Просто объедините Concatenate() с if() в функции массива:

=arrayformula(concatenate(if(not(isempty(a2:f2)),a2:f2&", ","")))

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

=arrayformula(concatenate(if(not(isempty(a2:d2)),a2:d2&", ","")))&if(not(isempty(f2)),f2,"")

Ура!