Как СОЗДАТЬ ряд Ячеек + Макрос VBA

Итог: Изучите два разных способа быстрого объединения ряда ячеек. Это включает в себя метод Ctrl + щелчок левой кнопкой мыши и бесплатный макрос VBA, который позволяет быстро и легко создавать формулы объединения или Ampersand.

Уровень мастерства: Средний

Create Concatenate Formula with Ctrl+Left-click Excel
Create Concatenate Formula for Range of Cells with Concatenate Macro in Excel

Сцепление: хорошее и плохое

Функция CONCATENATE может быть очень полезна для объединения значений нескольких ячеек в одну ячейку или формулу. Одно из популярных применений — создание формул VLOOKUP на основе нескольких критериев.

Однако вы не можете объединить диапазон ячеек, ссылаясь на диапазон в функции CONCATENATE. Это затрудняет и отнимает много времени при написании формул, если у вас много ячеек, которые нужно объединить.

Concatenate Individual vs Range of Cells Formula Error Comparison

Вариант № 1: Ctrl + щелчок левой кнопкой мыши, чтобы выбрать несколько ячеек

Вы можете удерживать клавишу Ctrl при выборе ячеек для добавления в формулу CONCATENATE. Это экономит время при вводе запятой после каждого выбора ячейки.

На следующем снимке экрана показано, как использовать сочетание клавиш Ctrl + щелчок левой кнопкой мыши. Вам не нужен макрос для этого, он встроен в Excel.

Create CONCATENATE Formula with Ctrl Left Click in Excel

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

Вариант № 2: CONCATENATE макроса диапазона

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

Create CONCATENATE and AMPERSAND Formulas in Excel GIF

Макрос Concatenate использует InputBox, который позволяет вам выбирать диапазон ячеек. Затем он создает формулу Concatenate или Ampersand, создавая аргумент для каждой ячейки в выбранном диапазоне.

Вы можете назначить макрос кнопке на ленте или комбинации клавиш. Макрос позволяет очень быстро создавать формулы.

Как работает макрос?

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

  1. Выберите ячейку, в которую нужно ввести формулу, и запустите макрос.
  2. Появляется InputBox и предлагает вам выбрать ячейки, которые вы хотите объединить. Вы можете выбрать диапазон ячеек с помощью мыши или клавиатуры.
  3. Нажмите ОК
  4. Макрос разделяет диапазон на ссылки на одну ячейку, поэтому эта ссылка на диапазон (A2: C2) превращается в (A2, B2, C2).
  5. Формула Concatenate или Ampersand создается в активной ячейке.

 

Опции макроса Concatenate

  1. Тип формулы. Макрос «Concatenate» позволяет создать формулу «Concatenate» или «Ampersand».
  2. Символ разделителя — Вы также можете добавить символ разделителя между каждой ячейкой. Это удобно, если вы хотите добавить запятые, пробелы, тире или любой символ между соединенными ячейками.
  3. Абсолютные ссылки — макрос также дает вам возможность сделать ссылки на ячейки абсолютными (привязанными). Это добавит знак $ перед буквой столбца или номером строки. Это удобно, если вы копируете формулу в определенном направлении и не хотите, чтобы относительные ссылки на ячейки менялись.

 

Create CONCATENATE and AMPERSAND Formulas with Separator

Функция Concatenate или формулы Ampersand

Клетки также могут быть объединены с помощью символа
Ampersand (&). Это альтернатива использованию функции CONCATENATE. Следующие две формулы приведут к одному и тому же результату.

= CONCATENATE(А2,В2,С2)

= А2&В2&С2

Concatenate vs Ampersand Formulas in Excel

Тот, который вы используете, — это вопрос личных предпочтений. Функция Concatenate может иметь небольшое преимущество, поскольку вы можете использовать трюк Ctrl + щелчок левой кнопкой мыши, чтобы быстро добавить несколько ячеек в формулу.

Опять же, макрос позволяет вам создать либо Concatenate , либо формулу Ampersand.

Код VBA

Вот код для макросов Concatenate и Ampersand .

Option Explicit

' Следующие 4 макроса используются для вызова макроса Concatenate_Formula.
' Макрос Concatenate_Formula имеет различные параметры, и эти 4 макроса
' запустите макрос Concatenate_Formula с различными параметрами. Ты 'захочешь
' назначить любой из этих макросов кнопке ленты или сочетанию клавиш.

Sub Ampersander()
    ' Создает базовую формулу Ampersander без параметров
    Call Concatenate_Formula(False, False)
End Sub

Sub Ampersander_Options()
    ' Создает формулу Ampersander и предлагает пользователю варианты
    ' Опции - это абсолютные ссылки и символ-разделитель.
    Call Concatenate_Formula(False, True)
End Sub

Sub Concatenate()
    ' Создает базовую формулу CONCATENATE без опций
    Call Concatenate_Formula(True, False)
End Sub

Sub Concatenate_Options()
    ' Создает формулу CONCATENATE и предлагает пользователю варианты
    ' Опции - это абсолютные ссылки и символ-разделитель.
    Call Concatenate_Formula(True, True)
End Sub
'
Sub Concatenate_Formula(bConcat As Boolean, bOptions As Boolean)

Dim rSelected As Range
Dim c As Range
Dim sArgs As String
Dim bCol As Boolean
Dim bRow As Boolean
Dim sArgSep As String
Dim sSeparator As String
Dim rOutput As Range
Dim vbAnswer As VbMsgBoxResult
Dim lTrim As Long
Dim sTitle As String

    ' Установить переменные
    Set rOutput = ActiveCell
    bCol = False
    bRow = False
    sSeparator = ""
    sTitle = IIf(bConcat, "CONCATENATE", "Ampersand")
    
    ' Предложите пользователю выбрать ячейки для формулы
    On Error Resume Next
    Set rSelected = Application.InputBox(Prompt:= _
                    "Select cells to create formula", _
                    Title:=sTitle & " Creator", Type:=8)
    On Error GoTo 0
    
    ' Запускать только в том случае, если были выбраны ячейки и кнопка 'отмены не была нажата
    If Not rSelected Is Nothing Then
        
        ' Установить разделитель аргументов для конкатенации или 'формулы Ampersander
        sArgSep = IIf(bConcat, ",", "&")
        
        ' Запрашивать у пользователя абсолютные ссылки и параметры 'разделителя
        If bOptions Then
        
            vbAnswer = MsgBox("Columns Absolute? $A1", vbYesNo)
            bCol = IIf(vbAnswer = vbYes, True, False)
            
            vbAnswer = MsgBox("Rows Absolute? A$1", vbYesNo)
            bRow = IIf(vbAnswer = vbYes, True, False)
                
            sSeparator = Application.InputBox(Prompt:= _
                        "Type separator, leave blank if none.", _
                        Title:=sTitle & " separator", Type:=2)
        
        End If
        
        ' Создать строку ссылок на ячейки
        For Each c In rSelected.Cells
            sArgs = sArgs & c.Address(bRow, bCol) & sArgSep
            If sSeparator <> "" Then
                sArgs = sArgs &amp; Chr(34) &amp; sSeparator &amp; Chr(34) &amp; sArgSep
            End If
        Next
        
        ' Обрезать дополнительный аргумент разделитель и разделитель 'символов
        lTrim = IIf(sSeparator <> "", 4 + Len(sSeparator), 1)
        sArgs = Left(sArgs, Len(sArgs) - lTrim)

        ' Создать формулу
        ' Предупреждение - вы не можете отменить этот ввод
        ' Если требуется отменить, вы можете скопировать строку формулы
        ' в буфер обмена, затем вставьте в активную ячейку, используя Ctrl + V
        If bConcat Then
            rOutput.Formula = "=CONCATENATE(" &amp; sArgs &amp; ")"
        Else
            rOutput.Formula = "=" &amp; sArgs
        End If
        
    End If

End Sub

Как использовать код

Добавьте кнопки макроса на ленту

В приведенных выше примерах я добавил этот код в свою книгу личных макросов. Затем я добавил кнопки макросов на ленту для каждого из 4 макросов. Я создал новую группу на вкладке Формулы и добавил к ней кнопки.

Add Concatenate Macro Buttons to the Ribbon

Как только кнопки макросов окажутся на ленте, вы можете щелкнуть их правой кнопкой мыши и выбрать «Добавить на панель быстрого доступа», чтобы добавить их в QAT.

Назначить сочетание клавиш для макросов

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

Create CONCATENATE Formula with Keyboard Shortcut

Я запускаю макрос, помещая кнопку макроса на панель быстрого доступа, а затем нажимаю сочетание клавиш Alt + Button Position для QAT. Ознакомьтесь с этой статьей о том, как использовать сочетания клавиш QAT для получения более подробной информации.

Дополнительные ресурсы

Concatenate с разрывами строк — Дейв Брунс из ExcelJet имеет отличную статью и видео о том, как добавить разрывы строк в формулу конкатенации. Отличный совет для присоединения почтовых адресов.

Комбинируйте ячейки без сцепления — Дебра Далглиш в Contextures объясняет, как создавать формулы Ampersand.

Concatenate нескольких ячеек с использованием Transpose — Chandoo имеет интересный подход к этой проблеме с помощью функции TRANSPOSE.

Заключение

Этот инструмент должен значительно ускорить и упростить создание формул Concatenate или Ampersand. Это может быть не то, что вы используете каждый день, но это здорово иметь в вашем наборе инструментов.

Пожалуйста, оставьте комментарий ниже с любыми вопросами.

Оцените статью
Добавить комментарий