У Криса есть рабочий лист с именами клиентов в столбцах от A до F. В столбце G он хочет включить формулу, которая возьмет все имена из шести столбцов имен и объединит их в одну длинную строку с символами // между именами. . Вполне возможно, что во всех шести столбцах не будет имен, и не должно быть посторонних разделителей // в начале или в конце.

Объединить текст в Excel очень просто. Например, если у вас есть что-то в ячейке A2, и вы хотите объединить это с тем, что находится в ячейке B2, вы можете сделать это с помощью следующей формулы:

=A2 & B2

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

=A2 & "//" & B2

Это довольно просто. Используя этот подход, вы можете объединить все шесть имен, используя следующую формулу:

=A2 & "//" & B2 & "//" & C2 & "//" & D2 & "//" & E2 & "//" & F2

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

где-то в середине результата.

Очевидным решением является использование операторов IF для проверки содержимого ячеек имен перед их объединением. Однако это может привести к появлению некоторых удивительно длинных формул. Например, следующая формула будет правильно выполнять проверку и объединение:

=IF(RIGHT(CONCATENATE(IF(A3="","",CONCATENATE(A3,"//")), IF(B3="","",CONCATENATE(B3,"//")),IF(C3="","",CONCATENATE(C3,"//")), IF(D3="","",CONCATENATE(D3,"//")),IF(E3="","",CONCATENATE(E3,"//")), IF(F3="","",F3)),2)="//",LEFT(CONCATENATE(IF(A3="","", CONCATENATE(A3,"//")),IF(B3="","",CONCATENATE(B3,"//")), IF(C3="","",CONCATENATE(C3,"//")),IF(D3="","",CONCATENATE(D3,"//")), IF(E3="","",CONCATENATE(E3,"//")),IF(F3="","",F3)), LEN(CONCATENATE(IF(A3="","",CONCATENATE(A3,"//")), IF(B3="","",CONCATENATE(B3,"//")),IF(C3="","",CONCATENATE(C3,"//")), IF(D3="","",CONCATENATE(D3,"//")),IF(E3="","",CONCATENATE(E3,"//")), IF(F3="","",F3)))-2),CONCATENATE(IF(A3="","",CONCATENATE(A3,"//")), IF(B3="","",CONCATENATE(B3,"//")),IF(C3="","",CONCATENATE(C3,"//")), IF(D3="","",CONCATENATE(D3,"//")),IF(E3="","",CONCATENATE(E3,"//")), IF(F3="","",F3)))

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

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

=MID(IF(ISTEXT(A3),"//"&A3,"") & IF(ISTEXT(B3),"//"&B3,"")

& IF(ISTEXT(C3),"//"&C3,"") & IF(ISTEXT(D3),"//"&D3,"") & IF(ISTEXT(E3),"//"&E3,"") & IF(ISTEXT(F3),"//"&F3,""),3,2000)

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

=MID( IF(ISTEXT(A3),"//"&A3,"") & IF(ISTEXT(B3),"//"&B3,"") & IF(ISTEXT(C3),"//"&C3,"") & IF(ISTEXT(D3),"//"&D3,"") & IF(ISTEXT(E3),"//"&E3,"") & IF(ISTEXT(F3),"//"&F3,""),3,2000)

Каждый отдельный оператор IF в формуле оценивает ячейку имени и либо ничего не возвращает («»), если ячейка не содержит текста, либо возвращает разделитель (//), за которым следует имя. Вся формула затем включается в оператор MID, который фактически обрезает первый разделитель // в строке.

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

=MID(IF(A3>"","//"&A3,"") & IF(B3>"","//"&B3,"") & IF(C3>"","//"& C3,"") & IF(D3>"","//"&D3,"") & IF(E3>"","//"&E3,"") & IF(F3>"","//"&F3,""),3,2000)

Это точно такая же техника, только немного короче. (И немного короче исходной формулы.)

Эта формула отлично работает при условии, что значения в ячейках имени являются текстовыми. Если столбцы с именами по какой-то причине содержат числовые значения, вы можете легко изменить формулу, чтобы использовать ISBLANK вместо ISTEXT, как показано здесь:

=MID(IF(ISBLANK(A3),"","//"&A3) & IF(ISBLANK(B3),"","//"&B3)

& IF(ISBLANK(C3),"","//"& C3) & IF(ISBLANK(D3),"","//"&D3)

& IF(ISBLANK(E3),"","//"&E3) & IF(ISBLANK(F3),"","//"&F3),3,2000)

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (2173) применим к Microsoft Excel 97, 2000, 2002 и 2003.