Na przykład
A1:I
A2:am
A3:a
A4:boy
Chcę połączyć je wszystkie w jedną komórkę „Iamaboy”
Ten przykład pokazuje, że 4 komórki łączą się w 1 komórkę, ale mam wiele komórek (ponad 100). Nie mogę ich wpisywać pojedynczo, korzystając z A1 & A2 & A3 & A4tego, co mogę zrobić?
Odpowiedzi:
Przedstawiam wam moją funkcję ConcatenateRange VBA (dzięki Jean za radę dotyczącą nazewnictwa!). Zajmie zakres komórek (dowolny wymiar, dowolny kierunek itp.) I połączy je w jeden ciąg. Jako opcjonalny trzeci parametr możesz dodać separator (taki jak spacja lub przecinki z separatorem).
W takim przypadku napisałbyś to, aby go użyć:
=ConcatenateRange(A1:A4)
Function ConcatenateRange(ByVal cell_range As range, _
Optional ByVal separator As String) As String
Dim newString As String
Dim cell As Variant
For Each cell in cell_range
If Len(cell) <> 0 Then
newString = newString & (separator & cell)
End if
Next
If Len(newString) <> 0 Then
newString = Right$(newString, (Len(newString) - Len(separator)))
End If
ConcatenateRange = newString
End Function
ConcatenateRange! Głupia CONCATENATEfunkcja Excela nie zaakceptuje zakresu jako danych wejściowych, tylko listę pojedynczych komórek jako oddzielne argumenty ...
Else newString = newString & (separator & Space(1))as, muszę zachować stałą szerokość w wynikowym ciągu.
Jeśli wolisz to zrobić bez VBA, możesz spróbować następujących rozwiązań:
Komórka B999 będzie teraz zawierała konkatenowany ciąg tekstowy, którego szukasz.
Wewnątrz CONCATENATEmożesz użyć, TRANSPOSEjeśli go rozwiniesz ( F9), a następnie usuń otaczające {}nawiasy, jak to zaleca
=CONCATENATE(TRANSPOSE(B2:B19))
Staje się
=CONCATENATE("Oh ","combining ", "a " ...)
Może być konieczne dodanie własnego separatora na końcu, powiedzmy, że utwórz kolumnę C i transponuj tę kolumnę.
=B1&" "
=B2&" "
=B3&" "
W prostych przypadkach możesz użyć następnej metody, która nie wymaga tworzenia funkcji ani kopiowania kodu do kilku komórek:
W dowolnej komórce napisz następny kod
=Transpose(A1:A9)
Gdzie A1: A9 to komórki, które chcesz scalić.
F9Następnie komórka będzie zawierać ciąg:
={A1,A2,A3,A4,A5,A6,A7,A8,A9}
Źródło: http://www.get-digital-help.com/2011/02/09/concatenate-a-cell-range-without-vba-in-excel/
Aktualizacja: jedna część może być niejednoznaczna. Bez wychodzenia z komórki oznacza, że Twoja komórka działa w trybie edytora. Alternatywnie możesz nacisnąć F9, gdy jesteś w panelu edytora komórek (normalnie można go znaleźć nad arkuszem kalkulacyjnym)
Użyj już istniejącej Joinfunkcji VBA . Funkcje VBA nie są ujawniane w programie Excel, więc zawijam Joinfunkcję zdefiniowaną przez użytkownika, która ujawnia jej funkcjonalność. Najprostsza forma to:
Function JoinXL(arr As Variant, Optional delimiter As String = " ")
'arr must be a one-dimensional array.
JoinXL = Join(arr, delimiter)
End Function
Przykładowe użycie:
=JoinXL(TRANSPOSE(A1:A4)," ")
wprowadzane jako formuła tablicowa (za pomocą Ctrl- Shift- Enter).
Teraz JoinXLakceptuje tylko jednowymiarowe tablice jako dane wejściowe. W programie Excel zakresy zwracają tablice dwuwymiarowe. W powyższym przykładzie TRANSPOSEkonwertuje dwuwymiarową tablicę 4 × 1 na 4-elementową jednowymiarową tablicę (jest to udokumentowane zachowanie, TRANSPOSEgdy jest zasilana jednokolumnową dwuwymiarową tablicą).
W przypadku zakresu poziomego musiałbyś zrobić podwójną TRANSPOSE:
=JoinXL(TRANSPOSE(TRANSPOSE(A1:D1)))
Wewnętrzna TRANSPOSEkonwertuje dwuwymiarową tablicę 1 × 4 na dwuwymiarową tablicę 4 × 1, którą zewnętrzną TRANSPOSEkonwertuje następnie na oczekiwaną 4-elementową jednowymiarową tablicę.
To użycie TRANSPOSEjest dobrze znanym sposobem konwertowania tablic 2D na tablice 1D w programie Excel, ale wygląda okropnie. Bardziej eleganckim rozwiązaniem byłoby ukrycie tego w JoinXLfunkcji VBA.
Dla tych, którzy mają Excel 2016 (i przypuszczam, że następne wersje), istnieje teraz bezpośrednio funkcja CONCAT , która zastąpi funkcję CONCATENATE .
Tak więc prawidłowy sposób na zrobienie tego w programie Excel 2016 to:
=CONCAT(A1:A4)
który wyprodukuje:
Jestem chłopcem
W przypadku użytkowników starszych wersji programu Excel inne odpowiedzi są istotne.
CONCATfunkcji
W przypadku programu Excel 2011 na komputerze Mac jest inaczej. Zrobiłem to w trzech etapach.
=B1. Formuła dla następnego wiersza do wiersza N to =Concatenate(",",A2). Kończysz z:QA , Sekuli , Testowanie , Applitools , Badania wizualne , Automatyzacja testów ,Selen
=B1. Dla wszystkich innych wierszy do N formuła to =Concatenate(C1,B2). Otrzymujesz:QA, Sekuli QA, Sekuli, Testowanie QA, Sekuli, Testing, Applitools QA, Sekuli, Testowanie, Applitools, Testy wizualne QA, Sekuli, Testowanie, Applitools, Testowanie wizualne, Automatyzacja testów QA, Sekuli, Testowanie, Applitools, Testowanie wizualne, Automatyzacja testów, Selenium
Ostatnia komórka na liście będzie tym, czego chcesz. Jest to zgodne z programem Excel w systemie Windows lub Mac.
=CONCATENATE(B1;",";A2). Następnie musisz przeciągnąć prawą kropkę w komórce dla wszystkich wierszy, a ostatnia wartość będzie wynikiem
Używam tej CONCATENATEmetody, aby pobrać wartości z kolumny i zawijać wokół nich cudzysłowy kolumnami między nimi, aby szybko wypełnić WHERE IN ()klauzulę instrukcji SQL.
I zawsze po prostu wpisać =CONCATENATE("'",B2,"'",","), a następnie wybrać, które i przeciągnij go w dół, co powoduje =CONCATENATE("'",B3,"'",","),=CONCATENATE("'",B4,"'",",") itp następnie podkreślić, że całą kolumnę, kopia wklej do zwykłego edytora tekstu i wkleić z powrotem w razie potrzeby, a tym samym pozbawiając separację wiersza. Działa, ale znowu, tak jak jednorazowa okazja, nie jest to dobre rozwiązanie dla kogoś, kto potrzebuje tego cały czas.
Wiem, że to naprawdę stare pytanie, ale próbowałem zrobić to samo i natknąłem się na nową formułę w programie Excel o nazwie „TEXTJOIN”.
W przypadku pytania poniższa formuła rozwiązuje problem
=TEXTJOIN("",TRUE,(a1:a4))
Podpis „TEXTJOIN” jest wyjaśniony jako TEXTJOIN (separator, ignoruj_pusty, tekst1, [tekst2], [tekst3], ...)