Dlaczego warto używać CONCATENATE vs & w Excelu?

Zauważyłem, że "EXEC MySproc," & "arg, " & "arg2"jest to w zasadzie to samo, co =CONCATENATE("EXEC MySproc,", "arg,", "arg2") włączenie umiejętności korzystania z ocen, funkcji etecetera. Jaki jest przykład użycia =CONCATENATE()vs &?

To jest dla ludzi, którzy lubią pisać więcej. Prawdopodobnie ta sama publiczność, która robi takie rzeczy =Sum((A1*A2)-A3)zamiast =(A1*A2)-A3. (Tak, ci ludzie istnieją i nie wiem, dlaczego to robią)

Zapewnia również wspaniałe możliwości utworzenia pierwszej funkcji zdefiniowanej przez użytkownika za pomocą VBA, aby stworzyć coś bardziej użytecznego niż Concatenate ().

Excel 2016 (z subskrypcją Office 365) ma nową funkcję o nazwie TextJoin () , która przyjmuje argumenty i zakres jako argumenty i jest znacznie szybsza niż wpisywanie ampersandów i separatorów jako ciągów tekstowych. Teraz TO jest przydatne.

Prawdopodobnie dlatego, że używają przycisku Wstaw funkcję.

Czasami robię to, gdy jem lewą ręką i po prostu robię jakieś formuły niskiego poziomu lub gdy używam urządzenia dotykowego i nie mogę sobie pozwolić na przełączanie między symbolami, literami i cyframi na ekranie dotykowym .

Ma to wyłącznie względy historyczne i kompatybilności. Poprzednie wersje programu Excel nie obsługiwały jednego formatu, a inne narzędzia do arkuszy kalkulacyjnych (takie jak Dokumenty Google, Numery Apple lub Open Office) nie obsługiwały drugiego.

Wybierz, co wolisz.

Pamiętaj, że w zależności od formatu zapisu program Excel potrzebuje więcej miejsca do zapisania CONCATENATE()niż &.

Moim zdaniem selektywne stosowanie konkatenatu i ampersand może prowadzić do wyraźniejszych formuł.

To powiązanie z danymi adresowymi, stosując mieszaninę z CONCATENATEa &wydaje mi się jaśniej:

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

Niż wyłączne wykorzystanie &:

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

I wyłączne wykorzystanieCONCATENATE

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

Z drugiej strony twierdzę, że takie UDF BuildAddressbyłoby lepszym rozwiązaniem (i byłoby lepiej przygotowane do obsługi subtelności formatowania adresów w domenach internacjonalizacji - chociaż tego nie zaimplementowałem) ...

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

Ale być może kolejnym przykładem, że obejmuje stosowanie & w dosłownym strun, lepiej pokazuje trudność czytania formułę, która sama dba siły do wyłącznie używać operatorów:

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

Może być lepiej napisany jako:

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

Ale liczy się wydajność , a zależnie od liczby konkatenowanych argumentów i długości każdego argumentu CONCATENATEfunkcja wydaje się przewyższać operator konkatenacji od 4 do 6. Trzeba przyznać, że ten przykład jest ekstremalny, z Łączono 255 argumentów, 10 000 razy. Nie zaleca się używania łańcucha argumentów o długości większej niż 32, ponieważ może zabraknąć pamięci / awarii programu Excel.

Oto prosty mechanizm pomiaru czasu:

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

I wyniki, zgodnie z długością ciągów argumentów:

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

Ale nawet nie rozmawialiśmy o słoniu w pokoju. Budujesz polecenie SQL za pomocą konkatenacji. Nie rób tego Wykonujesz procedurę składowaną, która akceptuje parametry. O ile nie zdezynfekowałeś danych wejściowych (i domyślam się, że tego nie zrobiłeś), budowanie łańcucha SQL za pomocą konkatenacji wymaga ataku typu SQL-injection. Równie dobrze możesz wystawić UDF o nazwie JohnnyDropTables...

Jest to różnica domen semantycznych. Concatenate to nazwa funkcji arkusza kalkulacyjnego. Ampersand jest operatorem konkatenacji zapożyczonym z Visual Basic. Ludzie, którzy nigdy nie otwierają używają VBA, znaleźliby funkcję o wiele łatwiejszą w użyciu niż składnia VBA. Jest to ten sam powód, dla którego istnieje klawisz skrótu, ikona i opcja menu do zapisania, co ułatwia korzystanie z oprogramowania.

Używam obu.

W przypadku długich list, które mogą wymagać przeglądu wizualnego, przecinek zajmuje mniej miejsca niż znak ampersand.

Łatwiej jest odczytać listę komórek oddzielonych przecinkami niż oddzielonych znakami ampersand, zwłaszcza, że ​​znak ampersand (po 15 godzinach dnia) wygląda zbyt podobnie do $.

Zapewnia to znaczącą rolę CONCATENATE.

Ale - zgadzam się - nie ma korzyści prędkości ani żadnej różnicy funkcjonalnej.

Jednym szczególnym przypadkiem użycia jest to, że =CONCATENATE(A1:A10)jest znacznie krótszy niż =A1&A2&A3&A4&A4&A6&A7&A8&A9&A10. Jest również znacznie bardziej poprawny (w rzeczywistości przykładowa &wersja ma celowy błąd).

Próbowałem tego pierwszy, ale korzystałem z biurowego programu Excel, który jest w języku niemieckim. TEXTKETTEzachowuje się tak, jak opisałem, ale dokumentacja pokazuje, że jest to funkcja newish i zastępuje VERKETTEN(która będzie niemieckim odpowiednikiem CONCATENATE).

Nie widziałem tutaj prawdziwych odpowiedzi, ale mam wgląd. (dla każdego, kto może w przyszłości wyszukać tę odpowiedź)

„Concatenate” to starsza, starsza funkcja, która robi te same czynności, co „&”, „&” dodano później dla zachowania spójności w językach programowania. Jednak „Concatenate” został zastąpiony przez „Concat” w celu obsługi zakresów, dzięki czemu można łączyć A1: A10 bez potrzeby tworzenia własnych skryptów. „&” Nadal nie zezwala na zakres i bierze pierwszą komórkę w ten sam sposób, co funkcje „konkatenacji”. Więc łącząc zakres, „Concat” daje tę dodatkową zmianę funkcji w stosunku do poprzednich 2 stylów, które są po prostu inaczej zakodowane. Określanie „Połącz” i „&” w zasadzie jako preferencję, gdy używasz nieciągłych wymaganych ciągów łączonych na temat tego, jak chcesz to pisać.

https://support.office.com/en-ie/article/concat-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2

RTFM Microsoft mówi używać &.

BTW, otrzymasz różne odpowiedzi, korzystając z operatora SUM.