Объединить и разделить значения столбцов, если они существуют
У меня есть таблица с тремя столбцами, значения которых могут быть разделены запятой, а могут и не быть. Например:
По сути, результат, который я ищу, заключается в том, что следующая ячейка содержит для каждого случая:
[Х]А, [Х]С
[X]D, [X]E, [X]F
[Х]А, [Х]Х, [Х]Z
[X]Z
Будучи [X] фиксированным значением, которое я добавляю к каждому случаю.
Можно ли это сделать с помощью стандартной формулы Excel?
Ответы 4
Я понимаю, что вы пытаетесь отформатировать свои данные так, чтобы каждое значение в вашей таблице, включая те, которые разделены запятыми, имело префикс [X]. Вы определенно можете сделать это с помощью формулы Excel. Вот как вы можете этого добиться:
- Настройте свои данные:
Допустим, ваши данные находятся в столбцах A, B и C, начиная со строки 2 (A2, B2, C2 и т. д.).
- Используйте эту формулу:
В новом столбце (давайте использовать столбец D) начните с D2 и введите следующую формулу:
=TEXTJOIN(", ", TRUE, IF(A2<>"", "[X]" & SUBSTITUTE(A2, ",", ", [X]"), ""), IF(B2<>"" , "[X]" & SUBSTITUTE(B2, ",", ", [X]"), ""), IF(C2<>"", "[X]" И ПОДСТАВИТЬ(C2, ",", ", [X]"), ""))
- Перетащите формулу вниз:
Перетащите формулу вниз от D2, чтобы сопоставить строки с вашими данными.
IF() не требуется: =TEXTJOIN(",",1,"[X]"&SUBSTITUTE(TOROW(A2:C2,1),", ",",[X]")) или =BYROW(A2:C5,LAMBDA(α, TEXTJOIN(",",1,"[X]"&SUBSTITUTE(TOROW(α,1),", ",",[X]"))))Становится все лучше и лучше!
Попробуйте использовать следующую формулу динамического массива:
=BYROW(A2:C5,LAMBDA(α,
LET(δ, CONCAT(α&", "),
TEXTJOIN(", ",1,"[X]"&TEXTSPLIT(δ,", ",,1)))))
Пояснения:
- Использование функции
BYROW(), которая применяет пользовательскиеLAMBDA()вычисления к каждой строке массива, чтобы вернуть в качестве вывода один единственный массив. - Итак, примененная здесь пользовательская функция внутри функции
LET(), которая также помогает сократить избыточные вычисления, уменьшает использование повторяющихся формул. - Переменная
δопределяется для объединенияCONCAT(α)всех ячеек в строках в одну через запятую-разделитель. - Затем мы разделяем то же самое с помощью функции
TEXTSPLIT()по столбцам и игнорируем пробелы, которые могут или не могут вернуться. - Наконец, перенос внутри функции
TEXTJOIN()для переноса всего с помощью запятой-разделителя и пробела, а также с разделенным выводом было объединено фиксированное значение[X], чтобы получить желаемый вывод в виде одного массива.
Кроме того, не используя вспомогательную функцию LAMBDA(), можно использовать следующее, чтобы скопировать ее:
=TEXTJOIN(", ",1,"[X]"&TEXTSPLIT(CONCAT(TOROW(A2:C2,1)&", "),", ",,1))
Соединение-разделение-соединение
=LET(data,A2:C11,delimiter,", ",prefix,"X",
dp,delimiter&prefix,
BYROW(data,LAMBDA(r,LET(
j,TEXTJOIN(delimiter,,IF(ISERROR(r),"",IF(r = "","",r))),
IF(j = "","",prefix&TEXTJOIN(dp,,TEXTSPLIT(j,delimiter)))))))
Другой способ использования Office 365:
=ARRAYTOTEXT("[X]"&TEXTSPLIT(ARRAYTOTEXT(TOROW(A1:C1,1)),", "))
Я не знал о следующем «недостатке» ARRAYTOTEXT: его разделитель состоит из разделителя списка Excel и пробела, т. е. если разделителем списка является точка с запятой (;), формула не работает.
Приятно знать, @VBasic2008. Я действительно думаю - поскольку ОП использует запятую в качестве разделителя в своем образце - эта запятая будет разделителем, но спасибо, что опубликовали это. Я не знал.
Хороший подход. Кстати, вы можете сделать 3 за один раз:
=TEXTJOIN(", ", TRUE, IF(A2:C2<>"", "[X]" & SUBSTITUTE(A2:C2, ",", ", [X]"), ""))