У Криса есть рабочий лист с именами клиентов в столбцах от A до F. В столбец G он хочет включить формулу, которая будет брать все имена из шести столбцов имен и объединять их в одну длинную строку с символами //между каждым именем. Возможно, не будет имен во всех шести столбцах и не должно быть посторонних разделителей//в начале и в конце.
Объединить текст в Excel очень просто. Например, если у вас есть что-то в ячейке A2, и вы хотите объединить это с тем, что находится в ячейке B2, вы можете сделать это с помощью следующей формулы:
= A2 & B2
Вы можете включить разделители//между двумя значениями, просто добавив их в нужное место:
= A2 & «//» & B2
Это довольно просто. Используя этот подход, вы можете объединить все шесть имен, используя следующую формулу:
= A2 & «//» & B2 & «//» & C2 & «//» & D2 & «//»& E2 &»//»& F2
Ситуация усложняется, когда вы понимаете, что в некоторых из этих ячеек может ничего не быть. Таким образом, формула приведет либо к конечным, либо к конечным разделителям//либо к двойным разделителям (////) где-то в середине результата.
Очевидным решением является использование операторов IF чтобы проверить содержимое ячеек имен перед их объединением. Однако это может привести к появлению удивительно длинных формул. Например, следующая формула будет правильно выполнять проверку и объединение:
= IF (RIGHT (CONCATENATE (IF (A3 = «», «», CONCATENATE (A3, «//»)) , ЕСЛИ (B3 = «», «», СЦЕПИТЬ (B3, «//»)), ЕСЛИ (C3 = «», «», СЦЕПИТЬ (C3, «//»)), ЕСЛИ (D3 = «», «», СЦЕПИТЬ (D3, «//»)), ЕСЛИ (E3 = «», «», СЦЕПИТЬ (E3, «//»)), ЕСЛИ (F3 = «», «», F3)), 2 ) = «//», LEFT (CONCATENATE (IF (A3 = «», «», CONCATENATE (A3, «//»)), IF (B3 = «», «», CONCATENATE (B3, «//» )), ЕСЛИ (C3 = «», «», СЦЕПИТЬ (C3, «//»)), ЕСЛИ (D3 = «», «», СЦЕПИТЬ (D3, «//»)), ЕСЛИ (E3 = » «,» «, СЦЕПИТЬ (E3,»//»)), ЕСЛИ (F3 =» «,» «, F3)), LEN (СЦЕПИТЬ (ЕСЛИ (A3 =» «,» «, СЦЕПИТЬ (A3,»//»)), ЕСЛИ (B3 =» «,» «, СЦЕПИТЬ (B3,»//»)), ЕСЛИ (C3 =» «,» «, СЦЕПИТЬ (C3,»//»)), ЕСЛИ (D3 = «», «», СЦЕПИТЬ (D3, «//»)), ЕСЛИ (E3 = «», «», СЦЕПИТЬ (E3, «//»)), ЕСЛИ (F3 = «», «», F3 ))) — 2), СЦЕПИТЬ (ЕСЛИ (A3 = «», «», СЦЕПИТЬ (A3, «//»)), ЕСЛИ (B3 = «», «», СЦЕПИТЬ (B3, «//»)) , ЕСЛИ (C3 = «», «», СЦЕПИТЬ (C3, «//»)), ЕСЛИ (D3 = «», «», СЦЕПИТЬ (D3, «//»)), ЕСЛИ (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)
Источник: