Question Pourquoi utiliser CONCATENATE vs & dans Excel?


j'ai remarqué que "EXEC MySproc," & "arg, " & "arg2" est essentiellement le même que =CONCATENATE("EXEC MySproc,", "arg,", "arg2")   y compris la capacité à utiliser les évaluations et les fonctions Quelle est l’usecase pour utiliser =CONCATENATE() contre &?


39
2017-07-05 21:49


origine


Dans le cas d'une longue séquence de concaténations de chaînes, CONCATENATE () peut être linéaire plutôt que quadratique dans la longueur de la chaîne finale. voir joelonsoftware.com/2001/12/11/back-to-basics - Pieter Geerkens
Anecdotique, mais j'utilise souvent CONCATENATE lors de la jonction de trois chaînes, pour la seule raison que je suis plus habitué à séparer les arguments par des virgules qu'avec les esperluettes ... mais bien sûr, ce n'est pas une raison valable. - Alexandre d'Entraigues
@PieterGeerkens: Je n'ai aucune idée de ce que vous voulez dire. OK, une longue séquence de effectué indépendamment Les concaténations de chaînes peuvent être linéaires ou quadratiques dans la longueur de la chaîne finale, en fonction de la manière dont vous stockez les chaînes en mémoire. Avez-vous des preuves (ou tout raison de croire) CONCATENATE() et & sont implémenté en interne différemment dans Excel? - G-Man


Réponses:


C'est pour ceux qui aiment taper plus. Probablement le même public qui fait des choses comme =Sum((A1*A2)-A3) au lieu de =(A1*A2)-A3. (Oui, ces personnes existent, et je ne sais pas pourquoi elles le font)

Il offre également de formidables possibilités de créer votre toute première fonction définie par l'utilisateur avec VBA pour créer quelque chose de plus utile que Concatenate ().

Excel 2016 (avec un abonnement Office 365) a une nouvelle fonction appelée TextJoin (), qui prend un délimiteur et une plage comme arguments et est beaucoup plus rapide que la saisie des esperluettes et des délimiteurs en tant que chaînes de texte. Maintenant, cela est utile.

enter image description here


53
2017-07-05 22:33



Au moins SUM peut prendre une plage. Merci pour le pointeur sur TEXTJOIN. Le travail n’a pas encore été mis à jour en 2016, mais quand je le ferai, je pourrai enfin retirer mon UDF. - Dranon
Tu ne voulais pas dire =Sum(A1,A2) (comme alternative à =A1+B1)? - xehpuk
@xehpuk Non je n'ai pas. Certaines personnes enveloppent une fonction Sum () autour de calculs simples. Ils pourraient utiliser =A1+A2 mais pour une raison quelconque, ils écrivent =Sum(A1+A1). Ou =Sum(A1-A2) où ils pourraient nous =A1-A2. Ou =Sum(A1*A2) où ils pourraient utiliser =A1*A2. Certaines personnes mettent toutes sortes de formules dans une fonction Sum () et j'essaie de comprendre pourquoi. - teylyn
@IllusiveBrian c'est parce que vous maintenez toujours la touche Maj enfoncée lorsque vous appuyez sur cette barre d'espace. - Mathieu Guindon
-1. Bien que la réponse soit drôle, elle est également inutile. La question n'était pas "Pourquoi les gens utilisent-ils CONCATENATE au lieu de &?" mais "pourquoi devrait-on faire CONCATENATE au lieu de &?". La réponse est "c'est tout à fait équivalent, l'aide en ligne Excel indique que & devrait être utilisé à la place". L'explication correcte (et utile), par ex. de Aganju, est-ce et est venu plus tard et CONCATENATE a été laissé pour des raisons de compatibilité, ce qui est une raison tout à fait valable. Tout cela "les gens sont stupides et veulent taper beaucoup" est totalement dénué de tout intérêt; Là sont raisons pour lesquelles ils font cela et ce n'est pas leur stupidité. - AnoE


C'est probablement parce qu'ils utilisent le bouton Insérer une fonction.

enter image description here

Je le fais parfois lorsque je mange de la main gauche et que je ne fais que des formules de bas niveau, ou lorsque j'utilise un appareil tactile et que je ne peux pas me donner la peine de basculer entre les symboles, les lettres et les chiffres sur l'écran tactile. .


22
2017-07-06 12:40



+1 Yup. J'ai toujours utilisé le bouton, c'était suffisant, donc il n'y a pas d'incitation à apprendre les opérateurs. - kubanczyk
Pour concaténer des cordes, je dois me rappeler dot . pour perl, pipe-pipe || pour SQL, pas de caractère pour cmd et bash, + pour Java Ai-je vraiment besoin & juste pour Excel? - kubanczyk
@kubanczyk si vous vous en souvenez ., || et + alors vous pouvez vous souvenir &. C'est plus facile que concatenate, esp. si on ne parle pas couramment l'anglais. Il est également utilisé dans VBA - phuclv
@ LưuVĩnhPhúc Maîtrise de l'anglais - à ma connaissance, ces noms de fonctions sont localisés (par exemple, VERKETTENen allemand Excel) - Hagen von Eitzen
@HagenvonEitzen une autre bonne raison d'utiliser l'opérateur sur la fonction! - Mathieu Guindon


Il n'a que des raisons historiques et de compatibilité. Les anciennes versions d'Excel ne prenaient pas en charge un seul format, et d'autres outils de feuille de calcul (tels que Google Docs, Apple Numbers ou Open Office) ne prenaient pas en charge l'autre.

Choisissez celui que vous préférez.

Notez que selon votre format d'enregistrement, Excel a besoin de plus d'espace pour enregistrer CONCATENATE() que &.


18
2017-07-05 23:00



Ouaip. Je ne savais pas que & était le remplacement pour CONCATENATE jusqu'à maintenant. J'utilise rarement Excel, mais quand je le fais, c'est généralement pour quelque chose qui nécessite une concaténation - gabe3886


À mon avis, l'utilisation sélective de concaténer et esperluettes, peut conduire à des formules plus claires.

Cette concaténation de détails d'adresse, en utilisant un mélange de CONCATENATE et & me semble plus clair:

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

Que le exclusif utilisation de &:

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

Et l'utilisation exclusive de CONCATENATE

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

Ensuite, je soutiendrais qu’un UDF comme BuildAddress serait une meilleure solution (et serait mieux placé pour gérer les subtilités du formatage d’adresses dans les domaines de l’internationalisation - bien que je ne l’aie pas encore implémenté) ...

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

Mais peut-être un autre exemple, qui comprend l'utilisation de &  dans la chaîne littérale, démontre mieux la difficulté de lire une formule qui se force à exclusivement utiliser des opérateurs:

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

Peut-être mieux écrit comme:

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

Mais le performance est ce qui compte, et en fonction du nombre d’arguments concaténés et de la longueur de chaque argument, le CONCATENATE La fonction semble surpasser l’opérateur de concaténation d’un facteur compris entre 4 et 6. Certes, cet exemple est extrême, 255 arguments étant concaténés, 10 000 fois. Je déconseille d'utiliser une longueur de chaîne d'argument supérieure à 32, ou vous risquez de manquer de mémoire / de crash Excel.

Voici un mécanisme de chronométrage grossier:

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

Et les résultats, en fonction de la longueur des chaînes d'argument:

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

Mais alors, nous n'avons même pas discuté de l'éléphant dans la pièce. Vous créez une commande SQL en utilisant la concaténation. Ne fais pas ça. Vous exécutez une procédure stockée qui accepte des paramètres. À moins que vous ayez assaini vos entrées (et je suppose que vous ne l'avez pas fait), la construction d'une chaîne SQL à l'aide de la concaténation demande une attaque par injection SQL. Vous pourriez aussi bien exposer un UDF appelé JohnnyDropTables...


15
2017-07-07 00:35



Vous faites une bonne quantité d'hypothèses sur cette dernière partie ... - Taylor Ackley


C'est une différence de domaine sémantique. Concatenate est le nom d'une fonction de tableur. The Ampersand est une concaténation opérateur emprunté à Visual Basic. Les personnes qui n'ouvrent jamais utilisent VBA trouveraient une fonction beaucoup plus facile à utiliser que la syntaxe VBA. C'est la même raison pour laquelle il existe une touche de raccourci, une icône et une option de menu pour enregistrer, ce qui rend le logiciel plus facile à utiliser.


4
2017-07-07 13:23





J'utilise les deux

Pour les longues listes que je pourrais avoir besoin de revoir visuellement, une virgule occupe moins d'espace oculaire qu'une esperluette.

Il est plus facile de lire une liste de cellules séparées par des virgules que séparées par une esperluette, d'autant plus qu'une perluète ressemble (après un jour de 15 heures) à un $.

Cela fournit un rôle significatif pour CONCATENATE.

Mais - d'accord - il n'y a pas d'avantage de vitesse, ni de différence fonctionnelle.


1
2017-07-07 06:43





Un cas particulier d'utilisation est que =CONCATENATE(A1:A10) est beaucoup plus court que =A1&A2&A3&A4&A4&A6&A7&A8&A9&A10. Il est également beaucoup plus évident de corriger (en fait l'exemple & la version a une erreur délibérée).

Je l'ai essayé d'abord, mais j'utilisais Excel, qui est en allemand. TEXTKETTE se comporte comme je le décris, mais la documentation montre que c'est une fonction nouvelle, et remplace VERKETTEN (qui sera l'équivalent allemand de CONCATENATE).


0
2017-07-06 14:47



@Vylix: Non, je veux dire que le CONCATENATE la forme est plus évidemment correcte. Si vous voulez concaténer tous les éléments d'une plage, donner beaucoup de distance Moins sujettes à erreur, que de donner chaque élément un par un. - Martin Bonner
L’exemple spécifique du & la forme a une erreur délibérée. - Martin Bonner
@MartinBonner =CONCATENATE(A1:A10)  ne fonctionne pas dans Excel. Si vous avez une telle fonction, il s'agit d'un fichier UDF personnalisé, pas d'Excel natif. Mais il ne peut pas s'agir d'un fichier UDF portant le même nom que la fonction native. - teylyn
@MartinBonner Votre argument pourrait tenir pour une fonction comme SUM et des opérateurs comme +, mais il ne s'applique pas aux fichiers Excel CONCATENATE fonction. =CONCATENATE(A1:A2) est jamais le même résultat que =A1&A2 - ThunderFrame
=CONCATENATE(A1:A10) donne le résultat de A1, de toute évidence, non seulement plus courte mais différente - phuclv


RTFM Microsoft dit utiliser le &.

BTW, vous obtiendrez des réponses différentes lorsque vous utilisez l'opérateur SUM.

enter image description here


-6
2017-07-06 13:23



"CONCATENATE peut ne pas être disponible dans les futures versions d'Excel." Haha, d'accord. Ils supportent toujours = alternatives - entrer des fonctions comme @CONCATENATE(A1,A2) et des calculs comme +A5+A6 ou -A5+A6. - Džuris
"Read The F'n Manual" n'est pas exactement ce que sois gentil est à propos. - Mathieu Guindon
@ Mat'sMug Le F peut aussi représenter Fine :) - DavidPostill♦