Por que usar CONCATENATE vs & no Excel?

Question

Notei que "EXEC MySproc," & "arg, " & "arg2" é essencialmente o mesmo que =CONCATENATE("EXEC MySproc,", "arg,", "arg2") , incluindo a capacidade de usar avaliações, funções etecetera. Qual é o usecase para usar =CONCATENATE() vs & ?

    

Answer 1

É para pessoas que gostam de digitar mais. Provavelmente o mesmo público que faz coisas como =Sum((A1*A2)-A3) em vez de =(A1*A2)-A3 . (Sim, essas pessoas existem e eu não sei porque elas fazem isso)

Ele também fornece oportunidades maravilhosas para criar sua primeira Função Definida pelo Usuário com o VBA para criar algo mais útil do que Concatenate ().

O Excel 2016 (com uma assinatura do Office 365) tem uma nova função chamada TextJoin () , que usa um delimitador e um intervalo como argumentos e é muito mais rápido do que digitar o" e "comercial e os delimitadores como strings de texto. Agora, isso é útil.

    

Answer 2

Provavelmente, porque eles estão usando o botão Inserir função.

Eu às vezes faço isso quando estou comendo com a mão esquerda e estou apenas fazendo algumas fórmulas de baixo nível, ou quando estou usando um dispositivo de toque e não poderia ser incomodado alternando entre os símbolos, letras e números a tela de toque.

    

Answer 3

Tem apenas razões históricas e de compatibilidade. Versões anteriores do Excel não suportavam um formato, e outras ferramentas de planilhas (como o Google Docs, o Apple Numbers ou o Open Office) não suportavam o outro.

Escolha o que você preferir.

Observe que, dependendo do formato salvo, o Excel precisa de mais espaço para salvar CONCATENATE() do que & .

    

Answer 4

Na minha opinião, o uso seletivo de "concatenate" e "e" comercial pode levar a fórmulas mais claras.

Esta concatenação de detalhes de endereço, usando uma mistura de CONCATENATE e & parece mais clara para mim:

=CONCATENATE(StreetNumber," ",StreetName," ",StreetType) & CHAR(10) & CONCATENATE(CityName," ",StateCode," ",ZipCode) & CHAR(10) & CountryName

Do que o uso exclusivo de & :

=StreetNumber & " " & StreetName & " " & StreetType & CHAR(10) & CityName & " " & StateCode & " " & ZipCode & CHAR(10) & CountryName

E o uso exclusivo de CONCATENATE

=CONCATENATE(StreetNumber," ",StreetName," ",StreetType,CHAR(10),CityName," ",StateCode," ",ZipCode,CHAR(10),CountryName)

Então, novamente, eu diria que uma UDF como BuildAddress seria uma solução melhor (e estaria melhor posicionada para lidar com as sutilezas da formatação de endereços em domínios de internacionalização - embora eu não tenha implementado isso) ...

Public Function BuildAddress(ByVal StreetNumber As String, ByVal StreetName As String, _
    ByVal StreetType As String, ByVal CityName As String, ByVal StateCode As String, _
    ByVal ZipCode As String, ByVal CountryName As String) As String


    BuildAddress = StreetNumber & " " & StreetName & " " & StreetType & vbCrLf & _
                   CityName & " " & StateCode & " " & ZipCode & vbCrLf & _
                   CountryName

End Function

Mas talvez outro exemplo, que inclua o uso de & dentro de a string literal, demonstre melhor a dificuldade de ler uma fórmula que se força a exclusivamente usar operadores :

=A1&"A2&A3&A4"&A5

Pode ser melhor escrito como:

=CONCATENATE(A1,"A2&A3&A4",A5)

Mas o desempenho é o que importa, e dependendo do número de argumentos sendo concatenados e do tamanho de cada argumento, a função CONCATENATE parece superar o operador de concatenação por um fator de entre 4 e 6. Reconhecidamente, este exemplo é extremo, com 255 argumentos sendo concatenados, 10.000 vezes. Eu não recomendo usar um comprimento de string de argumento maior que 32, ou você pode ficar sem memória / travar o Excel.

Aqui está um mecanismo cronometrado crasso:

Option Explicit

Const ConcatenationOperatorFormula As String = _
  "=$A$1&$A$2&$A$3&$A$4&$A$5&$A$6&$A$7&$A$8&$A$9&$A$10&$A$11&$A$12&$A$13&$A$14&$A$15&$A$16&$A$17&$A$18&$A$19&$A$20&$A$21&$A$22&$A$23&$A$24&$A$25&$A$26&$A$27&$A$28&$A$29&$A$30&$A$31&$A$32&$A$33&$A$34&$A$35&$A$36&$A$37&$A$38&$A$39&$A$40&$A$41&$A$42&$A$43&$A$44&$A$45&$A$46&$A$47&$A$48&$A$49&$A$50&$A$51&$A$52&$A$53&$A$54&$A$55&$A$56&$A$57&$A$58&$A$59&$A$60&$A$61&$A$62&$A$63&$A$64&$A$65&$A$66&$A$67&$A$68&$A$69&$A$70&$A$71&$A$72&$A$73&$A$74&$A$75&$A$76&$A$77&$A$78&$A$79&$A$80&$A$81&$A$82&$A$83&$A$84&$A$85&$A$86&$A$87&$A$88&$A$89&$A$90&$A$91&$A$92&$A$93&$A$94&$A$95&$A$96&$A$97&$A$98&$A$99&$A$100&" & _
  "$A$101&$A$102&$A$103&$A$104&$A$105&$A$106&$A$107&$A$108&$A$109&$A$110&$A$111&$A$112&$A$113&$A$114&$A$115&$A$116&$A$117&$A$118&$A$119&$A$120&$A$121&$A$122&$A$123&$A$124&$A$125&$A$126&$A$127&$A$128&$A$129&$A$130&$A$131&$A$132&$A$133&$A$134&$A$135&$A$136&$A$137&$A$138&$A$139&$A$140&$A$141&$A$142&$A$143&$A$144&$A$145&$A$146&$A$147&$A$148&$A$149&$A$150&$A$151&$A$152&$A$153&$A$154&$A$155&$A$156&$A$157&$A$158&$A$159&$A$160&$A$161&$A$162&$A$163&$A$164&$A$165&$A$166&$A$167&$A$168&$A$169&$A$170&$A$171&$A$172&$A$173&$A$174&$A$175&$A$176&$A$177&$A$178&$A$179&$A$180&$A$181&$A$182&$A$183&$A$184&$A$185&$A$186&$A$187&$A$188&$A$189&$A$190&$A$191&$A$192&$A$193&$A$194&$A$195&$A$196&$A$197&$A$198&$A$199&$A$200&" & _
  "$A$201&$A$202&$A$203&$A$204&$A$205&$A$206&$A$207&$A$208&$A$209&$A$210&$A$211&$A$212&$A$213&$A$214&$A$215&$A$216&$A$217&$A$218&$A$219&$A$220&$A$221&$A$222&$A$223&$A$224&$A$225&$A$226&$A$227&$A$228&$A$229&$A$230&$A$231&$A$232&$A$233&$A$234&$A$235&$A$236&$A$237&$A$238&$A$239&$A$240&$A$241&$A$242&$A$243&$A$244&$A$245&$A$246&$A$247&$A$248&$A$249&$A$250&$A$251&$A$252&$A$253&$A$254&$A$255"

Const ConcatenateFunctionFormula As String = _
  "=CONCATENATE($A$1,$A$2,$A$3,$A$4,$A$5,$A$6,$A$7,$A$8,$A$9,$A$10,$A$11,$A$12,$A$13,$A$14,$A$15,$A$16,$A$17,$A$18,$A$19,$A$20,$A$21,$A$22,$A$23,$A$24,$A$25,$A$26,$A$27,$A$28,$A$29,$A$30,$A$31,$A$32,$A$33,$A$34,$A$35,$A$36,$A$37,$A$38,$A$39,$A$40,$A$41,$A$42,$A$43,$A$44,$A$45,$A$46,$A$47,$A$48,$A$49,$A$50,$A$51,$A$52,$A$53,$A$54,$A$55,$A$56,$A$57,$A$58,$A$59,$A$60,$A$61,$A$62,$A$63,$A$64,$A$65,$A$66,$A$67,$A$68,$A$69,$A$70,$A$71,$A$72,$A$73,$A$74,$A$75,$A$76,$A$77,$A$78,$A$79,$A$80,$A$81,$A$82,$A$83,$A$84,$A$85,$A$86,$A$87,$A$88,$A$89,$A$90,$A$91,$A$92,$A$93,$A$94,$A$95,$A$96,$A$97,$A$98,$A$99,$A$100," & _
  "$A$101,$A$102,$A$103,$A$104,$A$105,$A$106,$A$107,$A$108,$A$109,$A$110,$A$111,$A$112,$A$113,$A$114,$A$115,$A$116,$A$117,$A$118,$A$119,$A$120,$A$121,$A$122,$A$123,$A$124,$A$125,$A$126,$A$127,$A$128,$A$129,$A$130,$A$131,$A$132,$A$133,$A$134,$A$135,$A$136,$A$137,$A$138,$A$139,$A$140,$A$141,$A$142,$A$143,$A$144,$A$145,$A$146,$A$147,$A$148,$A$149,$A$150,$A$151,$A$152,$A$153,$A$154,$A$155,$A$156,$A$157,$A$158,$A$159,$A$160,$A$161,$A$162,$A$163,$A$164,$A$165,$A$166,$A$167,$A$168,$A$169,$A$170,$A$171,$A$172,$A$173,$A$174,$A$175,$A$176,$A$177,$A$178,$A$179,$A$180,$A$181,$A$182,$A$183,$A$184,$A$185,$A$186,$A$187,$A$188,$A$189,$A$190,$A$191,$A$192,$A$193,$A$194,$A$195,$A$196,$A$197,$A$198,$A$199,$A$200," & _
  "$A$201,$A$202,$A$203,$A$204,$A$205,$A$206,$A$207,$A$208,$A$209,$A$210,$A$211,$A$212,$A$213,$A$214,$A$215,$A$216,$A$217,$A$218,$A$219,$A$220,$A$221,$A$222,$A$223,$A$224,$A$225,$A$226,$A$227,$A$228,$A$229,$A$230,$A$231,$A$232,$A$233,$A$234,$A$235,$A$236,$A$237,$A$238,$A$239,$A$240,$A$241,$A$242,$A$243,$A$244,$A$245,$A$246,$A$247,$A$248,$A$249,$A$250,$A$251,$A$252,$A$253,$A$254,$A$255)"

Const ARGUMENT_STRING_LENGTH As Long = 1

Sub test2()

  Dim start As Single

  'Disable app events to exclude UI/calculation artefacts
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

  Dim inputs As Range
  Set inputs = Range("A1:A255")

  Dim operatorRange As Range
  Set operatorRange = Range("B1:B10000")

  Dim functionRange As Range
  Set functionRange = Range("C1:C10000")

  'Reset the range values/formulas
  inputs.Value2 = ""
  operatorRange.Formula = ConcatenationOperatorFormula
  functionRange.Formula = ConcatenateFunctionFormula

  'Change the inputs to invalidate the calculation results
  inputs.Value2 = String(ARGUMENT_STRING_LENGTH, "B")

  'Time the calculation of operator formulas
  start = Timer
  operatorRange.Calculate
  Debug.Print "Operator Calculation", ARGUMENT_STRING_LENGTH, FormatNumber(Timer - start, 8)

  'Time the calculation of function formulas
  start = Timer
  functionRange.Calculate
  Debug.Print "Function Calculation", ARGUMENT_STRING_LENGTH, FormatNumber(Timer - start, 8)

  'Reset the range values to empty
  inputs.Value2 = ""
  operatorRange.Value2 = vbNullString
  functionRange.Value2 = vbNullString

  'Restore App defaults
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic

End Sub

E os resultados, de acordo com o tamanho das sequências de argumentos:

Method                 String Length   Seconds
---------------------  --------------  -----------
Function Calculation   1               0.06640625
Operator Calculation   1               0.21484380
Function Calculation   10              0.09765625
Operator Calculation   10              0.47265630
Function Calculation   32              0.17578130
Operator Calculation   32              1.17968800

Mas nem discutimos o elefante na sala. Você está criando um comando SQL usando concatenação. Não faça isso. Você está executando um procedimento armazenado que aceita parâmetros. A menos que você tenha higienizado suas entradas (e eu estou supondo que você não tenha), a construção de uma string SQL usando concatenação está solicitando um ataque de injeção de SQL. Você pode também expor uma UDF chamada JohnnyDropTables ...

    

Answer 5

É uma diferença de domínio semântico. Concatenar é o nome de uma função de planilha. O Ampersand é um operador de concatenação emprestado do Visual Basic. Pessoas que nunca abrem usam o VBA achariam uma função muito mais fácil de usar do que a Sintaxe do VBA. É a mesma razão pela qual há uma tecla de atalho, um ícone e uma opção de menu para salvar, o que torna o software mais fácil de usar.

    

Answer 6

Eu uso os dois.

Para listas longas que talvez precisem ser revisadas visualmente, uma vírgula ocupa menos espaço ocular do que um e comercial.

É mais fácil ler uma lista de células separadas por vírgulas do que separadas por e comercial, especialmente porque um e comercial parece (após um dia de 15 horas) muito parecido com um $.

Isso fornece um papel significativo para o CONCATENATE.

Mas - concordo - não há benefício de velocidade ou qualquer diferença funcional.

    

Answer 7

Um caso de uso específico é que =CONCATENATE(A1:A10) é muito menor que =A1&A2&A3&A4&A4&A6&A7&A8&A9&A10 . É também muito mais obviamente correto (na verdade, o exemplo & version tem um erro deliberado).

Eu tentei isso primeiro, mas eu estava usando o Office Excel, que é em alemão. TEXTKETTE se comporta como eu descrevo, mas a documentação mostra que é uma função nova, e substitui VERKETTEN (que será o equivalente alemão de CONCATENATE ).

    

Answer 8

RTFM A Microsoft diz que usa o & .

BTW, você receberá respostas diferentes ao usar o operador SUM.