Формула Excel для копирования всех данных ячеек одного столбца в одну ячейку, игнорируя пустые ячейки

Question

У меня есть данные в столбце Q, которые возвращаются с помощью другой формулы Excel, и я хочу, чтобы формула Excel получала o/p в ячейке R2, как показано ниже

Колонка Q

1234567810 1111 8787

{Пустая ячейка}

1234567811 1111 8787

{Пустая ячейка}

{Пустая ячейка}

{Пустая ячейка}

{Пустая ячейка}

{Пустая ячейка}

{Пустая ячейка}

1234567812 1111 8787

{Пустая ячейка}

{Пустая ячейка}

Ячейка R2

1234567810 1111 8787,1234567811 1111 8787,1234567812 1111 8787

Я пробовал формулу как =TEXTJOIN(",",TRUE,Q2:Q100) , но она показывает o/p как

1234567810 1111 8787,, 1234567811 1111 8787 ,,,,,,, 1234567812 1111 8787 ,,

Таким образом, это не игнорирует пустые значения ячеек, потому что пустые значения ячеек возвращаются через формулу Excel, которая фактически не делает их пустыми, а просто показывает их как пустые

Answer 1

Ответы в комментариях и ответы на другие вопросы выглядят довольно хорошо.  Но вот простой ответ, который не требует использования функции TEXTJOIN (которой нет в Excel 2013 и ниже) или использования VBA.

Установить «вспомогательный столбец»; например, столбец Z В Z2 введите

=IF(Q2<>"",Q2,"")

Вы можете подумать, что =Q2 будет эквивалентно, но если Q2 пусто, то =Q2 даст вам 0 ; см. Отображение пустых при обращении к пустым ячейкам в Excel.  В качестве альтернативы =Q2&"" , вероятно, будет так же хорошо.

В Z3 введите

=Z2&IF(AND(Z2<>"",Q3<>""),",","")&Q3

Это дает вам Q2 и Q3 в свернутой форме (список без пробелов), которую вы хотите.  Если Z2 (который равен Q2) и Q3 оба непустые, вы получите

Q2 & "," & Q3

то есть apple,banana .  Если Q2 или Q3 пусто, вы получите

Q2 & Q3

т.е. просто apple , просто banana или пустое.

Выберите Z3 и перетащите / заполните вниз до Z100 .  В каждой строке Zn это до сих пор добавляет Qn в список, вставляя запятую, только если Qn не является пустым.  (Здесь я спрятал столбцы S - Y )

Затем просто установите R2 в =Z100 .  (Или используйте =Z100&"" если есть вероятность, что весь список будет пустым.)

Answer 2

Отображение всей вашей информации (особенно те формулы в столбце Q) было бы полезно. Я подозреваю, что ваша формула в столбце Q НЕ возвращает пробел "" но возвращает пробел " " или какой-либо другой непечатный символ.

Если он возвращает пробел, у вас есть два варианта:

• Измените формулу столбца Q, чтобы она возвращала пробел, и используйте TEXTJOIN как вы делаете в своем вопросе.
• Используйте следующую формулу, которая эффективно превратит пробелы в пустые:

---

=TEXTJOIN(",",TRUE,TRIM(Q2:Q100))

Это формула массива, которая должна быть подтверждена нажатием Ctrl + Shift при нажатии Enter. Если вы сделаете это правильно, Excel поместит фигурные скобки {...} вокруг формулы.

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

Answer 3

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

=TEXTJOIN(",",TRUE,Q2,Q4,Q11)