Итог: Изучите два разных способа быстрого объединения ряда ячеек. Это включает в себя метод Ctrl + щелчок левой кнопкой мыши и бесплатный макрос VBA, который позволяет быстро и легко создавать формулы объединения или Ampersand.
Уровень мастерства: Средний
Сцепление: хорошее и плохое
Функция CONCATENATE может быть очень полезна для объединения значений нескольких ячеек в одну ячейку или формулу. Одно из популярных применений — создание формул VLOOKUP на основе нескольких критериев.
Однако вы не можете объединить диапазон ячеек, ссылаясь на диапазон в функции CONCATENATE. Это затрудняет и отнимает много времени при написании формул, если у вас много ячеек, которые нужно объединить.
Вариант № 1: Ctrl + щелчок левой кнопкой мыши, чтобы выбрать несколько ячеек
Вы можете удерживать клавишу Ctrl при выборе ячеек для добавления в формулу CONCATENATE. Это экономит время при вводе запятой после каждого выбора ячейки.
На следующем снимке экрана показано, как использовать сочетание клавиш Ctrl + щелчок левой кнопкой мыши. Вам не нужен макрос для этого, он встроен в Excel.
Вероятно, это самый быстрый способ добавить несколько ячеек в формулу CONCATENATE . Это удобный способ, если вы объединяете несколько ячеек, но это может занять много времени, если вы объединяете много ячеек вместе.
Вариант № 2: CONCATENATE макроса диапазона
К сожалению, не существует простого способа выбрать весь диапазон, который вы хотите объединить. Поэтому я написал макрос, который позволяет объединить диапазон. Следующий скриншот показывает макрос в действии.
Макрос Concatenate использует InputBox, который позволяет вам выбирать диапазон ячеек. Затем он создает формулу Concatenate или Ampersand, создавая аргумент для каждой ячейки в выбранном диапазоне.
Вы можете назначить макрос кнопке на ленте или комбинации клавиш. Макрос позволяет очень быстро создавать формулы.
Как работает макрос?
Макрос в основном разделяет ссылку на диапазон, заданную с помощью InputBox, а затем создает формулу в активной ячейке. Вот резюме высокого уровня:
- Выберите ячейку, в которую нужно ввести формулу, и запустите макрос.
- Появляется InputBox и предлагает вам выбрать ячейки, которые вы хотите объединить. Вы можете выбрать диапазон ячеек с помощью мыши или клавиатуры.
- Нажмите ОК
- Макрос разделяет диапазон на ссылки на одну ячейку, поэтому эта ссылка на диапазон (A2: C2) превращается в (A2, B2, C2).
- Формула Concatenate или Ampersand создается в активной ячейке.
Опции макроса Concatenate
- Тип формулы. Макрос «Concatenate» позволяет создать формулу «Concatenate» или «Ampersand».
- Символ разделителя — Вы также можете добавить символ разделителя между каждой ячейкой. Это удобно, если вы хотите добавить запятые, пробелы, тире или любой символ между соединенными ячейками.
- Абсолютные ссылки — макрос также дает вам возможность сделать ссылки на ячейки абсолютными (привязанными). Это добавит знак $ перед буквой столбца или номером строки. Это удобно, если вы копируете формулу в определенном направлении и не хотите, чтобы относительные ссылки на ячейки менялись.
Функция Concatenate или формулы Ampersand
Клетки также могут быть объединены с помощью символа
Ampersand (&). Это альтернатива использованию функции CONCATENATE. Следующие две формулы приведут к одному и тому же результату.
= CONCATENATE(А2,В2,С2)
= А2&В2&С2
Тот, который вы используете, — это вопрос личных предпочтений. Функция 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 & Chr(34) & sSeparator & Chr(34) & 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(" & sArgs & ")"
Else
rOutput.Formula = "=" & sArgs
End If
End If
End Sub
Как использовать код
Добавьте кнопки макроса на ленту
В приведенных выше примерах я добавил этот код в свою книгу личных макросов. Затем я добавил кнопки макросов на ленту для каждого из 4 макросов. Я создал новую группу на вкладке Формулы и добавил к ней кнопки.
Как только кнопки макросов окажутся на ленте, вы можете щелкнуть их правой кнопкой мыши и выбрать «Добавить на панель быстрого доступа», чтобы добавить их в QAT.
Назначить сочетание клавиш для макросов
Вы также можете назначить сочетание клавиш для макроса, чтобы сделать этот процесс очень быстрым. Когда InputBox открыт, фокус возвращается на рабочий лист, что означает, что вы можете выбрать диапазон ввода с помощью клавиатуры. В следующем скриншоте я создаю объединенную формулу только с помощью клавиатуры.
Я запускаю макрос, помещая кнопку макроса на панель быстрого доступа, а затем нажимаю сочетание клавиш Alt + Button Position для QAT. Ознакомьтесь с этой статьей о том, как использовать сочетания клавиш QAT для получения более подробной информации.
Дополнительные ресурсы
Concatenate с разрывами строк — Дейв Брунс из ExcelJet имеет отличную статью и видео о том, как добавить разрывы строк в формулу конкатенации. Отличный совет для присоединения почтовых адресов.
Комбинируйте ячейки без сцепления — Дебра Далглиш в Contextures объясняет, как создавать формулы Ampersand.
Concatenate нескольких ячеек с использованием Transpose — Chandoo имеет интересный подход к этой проблеме с помощью функции TRANSPOSE.
Заключение
Этот инструмент должен значительно ускорить и упростить создание формул Concatenate или Ampersand. Это может быть не то, что вы используете каждый день, но это здорово иметь в вашем наборе инструментов.
Пожалуйста, оставьте комментарий ниже с любыми вопросами.