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

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

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

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

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

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

Но у нас возникают проблемы с чем-то вроде …

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

Что привело бы к Jesus,,Mary,Joseph .

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

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

Как вы сказали, вложенные 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)

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

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

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

  • Являются ли ячейки перед активной ячейкой пустыми?
  • Является ли активная ячейка пустой?
  • Разве это не так? (Условие по умолчанию)

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

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

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

 =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 – это ячейки за активной ячейкой.

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

 =TEXTJOIN(delimiter,ignore_blanks?,values_to_be_joined) 

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

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

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

Установите ячейку 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)) 

Объяснение:

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

 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 нулевым, чтобы избежать получения ошибки, если все четыре входные ячейки пусты.

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

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

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

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

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

Скриншот

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

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

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

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

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

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

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

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

Voila, ваш список, разделенный запятой, находится в последней ячейке в столбце B 🙂

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

Просто объедините 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,"") 

Ура!

  • Как вы обновляете файл Excel (формулы обновления и обновления данных) БЕЗ открытия файла?
  • Сравнение похожих текстовых строк в Excel
  • Как я могу выполнить эту функцию с помощью VBA в excel?
  • Почему Excel усекает мои 16-значные цифры?
  • График рассеяния Excel с ярлыками
  • Кнопка Microsoft Powerpivot не работает
  • Сравнение счетов в Windows Excel 7 между двумя разными листами и объединение в один список
  • Закройте формулу в выпадающем списке словом
  • Как переключить Scroll Lock в Excel 2010 без клавиши Scroll Lock на клавиатуре
  • Excel автоматически добавляет дополнительную строку с формулами для введенного количества лет
  • Как получить таблицу строк «один ко многим» путем слияния дублированных ячеек в большой нормализованной таблице?
  • Давайте будем гением компьютера.