VBA 없이 Excel에서 두 어레이를 연결할 수 있는 방법이 있습니까?
길이가 다른 두 배열의 연결을 반환하는 공식을 만들려고 합니다.저는 다른 공식의 일부를 위해 이 연결이 필요하며 가능하다면 "도움이 되는" 행을 피하고 싶습니다.
예제 데이터는 아래를 참조하십시오.
목표는 출력을 다음과 같이 되도록 하는 것입니다.{10;11;12;13;20;21;22}물론 이것은 공식에 쉽게 하드 코딩될 수 있지만 이러한 값은 동적이므로 옵션이 아닙니다.
다음을 시도했습니다.
{A1:A4;B1:B3}
하지만 이것은 분명히 유효한 Excel 구문이 아닙니다.
해결책이 있습니까?
Excel은 사용자가 설명하는 방식으로 배열을 직접 연결할 수 없습니다(즉, 단순히 배열을 뒤로 결합하는 것).그러나 도우미 기능을 사용하지 않고도 이 문제에 대한 (복잡한) 해결책이 있습니다.
할 은 기적으변합니다야해환로본다합니▁essential▁convert▁need▁youly를 변환하는 것입니다.{10;11;12;13}{10;11;12;13;0;0;0} 변환 환{20;21;22}{0;0;0;0;20;21;22}결과가 나오면 길이 7의 두 배열을 함께 추가하여 원하는 결과를 얻을 수 있습니다.
그러면 배열의 시작이나 끝에 0을 추가하는 방법은 무엇입니까?
곱셈()을 입니다.MMULTExcel 내장 기능)을 교묘하게 사용합니다.
프로그래밍에서 너무 주제를 벗어났다고 생각하기 때문에 왜 이것이 결과인지에 대해 모든 수학을 설명하지는 않겠지만 궁극적으로 다음 행렬 곱셈 방정식이 원하는 결과를 제공합니다.
[1 0 0 0] [10] [10]
[0 1 0 0] * [11] = [11]
[0 0 1 0] [12] [12]
[0 0 0 1] [13] [13]
[0 0 0 0] [ 0]
[0 0 0 0] [ 0]
[0 0 0 0] [ 0]
또는 Excel에서 이것을 입력하여 결과를 얻을 수 있습니다. (가독성을 높이기 위해 줄 바꿈을 추가했습니다.)
= MMULT({1,0,0,0;
0,1,0,0;
0,0,1,0;
0,0,0,1;
0,0,0,0;
0,0,0,0;
0,0,0,0},A1:A4)
이키를 가 나오는 을 알 수 .{10;11;12;13;0;0;0}.
수 것입니다.{0;0;0;0;20;21;22}:
= MMULT({0,0,0;
0,0,0;
0,0,0;
0,0,0;
1,0,0;
0,1,0;
0,0,1},B1:B3)
이 두 값을 합하면 원하는 최종 결과를 얻을 수 있습니다.{10;11;12;13;20;21;22}.
메모
이 시점에서 이 정보는 귀하의 요구사항/요구사항에 대한 충분한 정보일 수 있습니다.그러나 대규모 어레이의 경우 1과 0의 행렬을 공식으로 하드 코딩하는 것이 너무 번거로울 수 있습니다.이 경우 1과 0의 행렬을 하드 코딩하는 대신 자동으로 생성하는 방법을 계속 읽으십시오.
위에 표시된 1과 0의 큰 행렬을 어떻게 자동으로 생성합니까?
토론이 너무 길어지고 주제에서 벗어날 것 같아서 "왜"에 대해 설명하지 않고, 위의 1과 0의 첫 번째 행렬을 생성하는 공식이 여기 있습니다.
= (ROW(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(ROWS(A1:A4)+ROWS(B1:B3),1)))
=COLUMN(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(1,ROWS(A1:A4)))))+0
1과 0의 두 번째 행렬에 대한 공식은 약간 다릅니다.
= (ROW(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(ROWS(A1:A4)+ROWS(B1:B3),1)))
=(COLUMN(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(1,ROWS(B1:B3))))+ROWS(A1:A4)))+0
최종 공식
두 개의 (수직) 배열을 연결하는 최종 공식은 다음과 같습니다(가독성을 높이기 위해 여러 줄 바꿈 추가).
= MMULT(
(ROW(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(ROWS(A1:A4)+ROWS(B1:B3),1)))
=COLUMN(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(1,ROWS(A1:A4)))))+0,
A1:A4)
+MMULT(
(ROW(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(ROWS(A1:A4)+ROWS(B1:B3),1)))
=(COLUMN(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(1,ROWS(B1:B3))))+ROWS(A1:A4)))+0,
B1:B3)
최종 참고 사항/생각
이 공식을 사용하면 VBA를 사용하지 않고 어레이를 연결할 수 있다는 장점이 있습니다.단점은 배열을 연결하는 이 방법은 텍스트가 아닌 숫자로만 작동한다는 것입니다.(그 이유는MMULT숫자가 필요합니다.)
두 개의 어레이만 연결하면 되고 최신 버전의 Excel을 가지고 있다면, 이것이 어레이의 원래 순서를 유지하는 가장 짧은 답변이라고 생각합니다.
이 응답은 s 배열(두 목표 배열의 결합 길이)을 순환하고 if 문을 사용하여 배열 a의 요소를 출력할지 또는 배열 b의 요소를 출력할지 결정합니다.
이 공식을 복사하여 워크북에 붙여넣으려면 A2# 및 B2# 값을 연결하려는 두 배열로 변경하기만 하면 됩니다.
다중 회선 버전
=LET(
a, A2#,
b, B2#,
s, SEQUENCE(ROWS(a) + ROWS(b)),
IF(s > ROWS(a),
INDEX(b, s - ROWS(a)),
INDEX(a, s)
))
최소화된 버전:
=LET(a,A2#,b,B2#,s,SEQUENCE(ROWS(a)+ROWS(b)),IF(s>ROWS(a),INDEX(b,s-ROWS(a)),INDEX(a,s)))
두 개의 수직 어레이를 연결하는 솔루션이 있습니다(데이터가 숫자여야 한다는 제한 없이).
공식은 과 같습니다. ( 다은배공다예니입식조열음합▁(예다)▁here:)A1:A4그리고.C7:C9)
= INDEX(CHOOSE({1,2},A1:A4,C7:C9),
N(IF({1},
ROW(INDEX($A:$A,1):INDEX($A:$A,ROWS(A1:A4)+ROWS(C7:C9)))
-IF(ROW(INDEX($A:$A,1):INDEX($A:$A,ROWS(A1:A4)+ROWS(C7:C9)))
<=ROWS(A1:A4),0,ROWS(A1:A4)))),
N(IF({1},
2-(ROW(INDEX($A:$A,1):INDEX($A:$A,ROWS(A1:A4)+ROWS(C7:C9)))
<=ROWS(A1:A4)))
))
그리고 두 의 수평하는 배열 그고여두개기수예배의결배있공습다니식이열는하합을열평리예▁to(▁and:다있▁formula니습▁array▁combine▁(:A1:D1그리고.C3:E3)
= INDEX(CHOOSE({1;2},A1:D1,C3:E3),
N(IF({1},2-(COLUMN(INDEX($1:$1,1):INDEX($1:$1,COLUMNS(A1:D1)+COLUMNS(C3:E3)))
<=COLUMNS(A1:D1)))),N(IF({1},COLUMN(INDEX($1:$1,1):INDEX($1:$1,COLUMNS(A1:D1)+
COLUMNS(C3:E3)))-IF(COLUMN(INDEX($1:$1,1):INDEX($1:$1,COLUMNS(A1:D1)+COLUMNS(C3:E3)))
<=COLUMNS(A1:D1),0,COLUMNS(A1:D1)))))
, 짧은. 2019년 11월 19일 이후 버전인 엑셀, 엑셀365, 엑셀365가 있습니다.하나의 열에 이름이 지정된 경우Foo 한 은 그고다열이붙습다니름이은른리▁is▁and다붙입니다.Bar공식은 다음과 같습니다.
=SMALL((Foo,Bar),SEQUENCE(ROWS(Foo)+ROWS(Bar)))
여기서,SEQUENCEFoo 및 Bar의 총 행 수열이 1에서 1 사이인 배열을 반환합니다. SMALL시퀀스의 각 값에 대한 적절한 값을 반환합니다.그런 다음 Excel은 공식 아래에 필요한 수의 행으로 결과를 "스필"합니다.
TLDR 및 자체 가이드 - 다음은 워크북의 예입니다.
예, 2016년 프리오피스에서 어레이에 가입할 수 있는 방법이 있습니다.이것이 상상에 의해 응답되었다는 것을 압니다.위에 사람이 있지만, 다른 방법이 있습니다. 배열을 반환하고 읽기가 조금 더 쉽습니다(IMHO).사용 사례에 맞는 공식을 찾을 수 있도록 공식의 진화를 설명하겠습니다.사용 사례를 볼드체로 강조 표시하여 사용 사례를 신속하게 찾을 수 있도록 했습니다.이것이 다소 장황하다는 것을 알지만, 저는 해결책이 어떻게 작동하는지 알고 싶어하는 종류의 사람입니다. 그래서 저는 여러분에게 같은 예의를 주려고 노력할 것입니다.
은 내포된 공은내된값의니다합존에 합니다.IF 및 문INDEX/CHOOSE범위 할 수 있습니다.범위, 명명된 범위 및 테이블 열과 함께 작동합니다.는 모두 범위를 . 3개의 범위가 있습니다. 따라서 3가지 범위가 있습니다.IF진술, 하지만 만약 당신이 그렇게 많은 중첩된 것을 신경 쓴다면 이것은 (내 생각에) 64개의 범위까지 줄 수 있습니다.IF진술들.
예에서 는 "데이터 범위"입니다.A3:B6,A9:B11,A14:B19,그리고.A22:B32은 결 배 공 은 다 범 포 에 됩 니 다 함 위 과 음 열 식 ▁range 니 다 ▁formula ▁the ▁is ▁in ▁the 됩 포 ▁put ▁resulting 함 ▁arrayE3:E26그리고 a로 끝냈습니다.Ctrl+Shift+Enter배열 공식으로 만들기 위해.데이터를 원하는 곳으로 이동할 수 있습니다. 이러한 범위에 얽매이지 않고 범위를 적절히 대체하기만 하면 됩니다.
데이터가 연속된 범위에 있는 경우:
=IF(ROW()-ROW(E3)<ROWS(A3:A6),INDEX(A3:B6,ROW()-ROW(E3)+1,COLUMN()-COLUMN(E3)+1),
IF(ROW()-ROW(E3)<ROWS(A3:A6)+ROWS(A9:A11),INDEX(A9:B11,ROW()-ROW(E3)-ROWS(A9:A11),COLUMN()-COLUMN(E3)+1),
IF(ROW()-ROW(E3)<ROWS(A3:A6)+ROWS(A9:A11)+ROWS(A14:A19),INDEX(A14:B19,ROW()-ROW(E3)-ROWS(A3:A6)-ROWS(A9:A11)+1,COLUMN()-COLUMN(E3)+1),
INDEX(A22:B32,ROW()-ROW(E3)-ROWS(A3:A6)-ROWS(A9:A11)-ROWS(A14:A19)+1,COLUMN()-COLUMN(E3)+1))))
작동 방식:
- 그
IF은 셀 명 문 은 셀 에 서 출 범 력 위 의 맨 위 현 서 행 빼 을 첫 합 확 인 니 다 지 는 있 에 위 범 번 령 째 서 재 에 ▁by ▁statement ▁makes ▁from ▁in ▁are ▁row ▁range ▁range ▁the ▁subtract ▁cell ▁current ▁first ▁sure 명 ▁output 령 ▁that ▁we 합 니 ▁the ▁in 다 인 ▁the문 확 은 ▁of 지 셀E3첫 의 셀 와 비교합니다.A3:B6. - 의 첫 번째 입력 합니다.
A3:B6행및 열 오프셋이 지정된 경우E3. - 이 첫 행으로 합니다.
IF문 - 프로세스를 반복하지만 배열의 현재 행을 처음 두 범위의 길이와 비교합니다.은 더 이상 중첩되지 않은 경우에도 됩니다.IF진술들.
데이터가 연속된 범위에 있지 않으면 데이터가 원래 어느 범위에서 왔는지 또는 둘 다 표시하는 열이 필요합니다.
=IF(ROW()-ROW(E3)<ROWS(A3:A6),INDEX(CHOOSE({1,2,3},{1},A3:A6,B3:B6),ROW()-ROW(E3)+1,COLUMN()-COLUMN(E3)+1),
IF(ROW()-ROW(E3)<ROWS(A3:A6)+ROWS(A9:A11),INDEX(CHOOSE({1,2,3},{2},A9:A11,B9:B11),ROW()-ROW(E3)-ROWS(A3:A6)+1,COLUMN()-COLUMN(E3)+1),
IF(ROW()-ROW(E3)<ROWS(A3:A6)+ROWS(A9:A11)+ROWS(A14:A19),INDEX(CHOOSE({1,2,3},{3},A14:A19,B14:B19),ROW()-ROW(E3)-ROWS(A3:A6)-ROWS(A9:A11)+1,COLUMN()-COLUMN(E3)+1),
INDEX(CHOOSE({1,2,3},{4},A22:A32,B22:B32),ROW()-ROW(E3)-ROWS(A3:A6)-ROWS(A9:A11)-ROWS(A14:A19)+1,COLUMN()-COLUMN(E3)+1))))
작동 방식:
- 다을위 .
IF그리고.INDEX문은 위와 동일하게 유지됩니다. - A
CHOOSE각 범위에 대해 원하는 식별자가 있는 비연속 데이터 열 또는 정적 배열을 선택할 수 있는 문이 추가되었습니다.이 경우에는 숫자(1,2,3,4)를 사용했습니다. - 그
CHOOSE문장에는 원하는 만큼의 열이 있을 수 있습니다. 첫 번째 인수를 다음으로 변경하십시오.{1,2,3,4}네 개의 열에 대해 네 번째 열을 마지막 인수로 추가합니다. 열 다열도동작예수행다니합을업한일에음예▁((다)에도 하게 합니다.{1,2,3,4,5}다섯 번째 열을 마지막 인수로 추가합니다.
수직 데이터 대신 수평 데이터를 사용하는 경우TRANSPOSE앞의 예를 실행할 수 있습니다.그냥 둥지를 틀면 됩니다.TRANSPOSE 내의 CHOOSE다음과 같은 기능:
CHOOSE({1,2,3},{1},TRANSPOSE(A3:C3),TRANSPOSE(A4:C4)
명명된 범위 또는 테이블을 사용하여 공식을 크게 정리할 수 있습니다.이 예제는 연속적인 범위가 아닌 데이터를 허용하는 이전 예제를 기반으로 하며 데이터 출처를 보여주는 식별자 열을 제공합니다.
=IF(ROW()-ROW(E3)<ROWS(Table1),INDEX(CHOOSE({1,2,3},{1},Table1[Column1],Table1[Column2]),ROW()-ROW(E3)+1,COLUMN()-COLUMN(E3)+1),
IF(ROW()-ROW(E3)<ROWS(Table1)+ROWS(Table2),INDEX(CHOOSE({1,2,3},{2},Table2[Column1],Table2[Column2]),ROW()-ROW(E3)-ROWS(Table1)+1,COLUMN()-COLUMN(E3)+1),
IF(ROW()-ROW(E3)<ROWS(Table1)+ROWS(Table2)+ROWS(Table3),INDEX(CHOOSE({1,2,3},{3},Table3[Column1],Table3[Column2]),ROW()-ROW(E3)-ROWS(Table1)-ROWS(Table2)+1,COLUMN()-COLUMN(E3)+1),
INDEX(CHOOSE({1,2,3},{4},Table4[Column1],Table4[Column2]),ROW()-ROW(E3)-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)+1,COLUMN()-COLUMN(E3)+1))))
이것으로 충분하지 않은 경우, 명명된 값을 만들어 가독성을 위해 더 많은 하우스키핑을 수행할 수 있습니다.가장 먼저 할 수 있는 일은 각 테이블에서 데이터를 가져오기 시작하는 행을 정의하는 것입니다.이 예에서, 저는 이것들을 이름 지었습니다.Table2_UL,Table3_UL,그리고.Table4_UL이름 관리자의 코드 공식은 다음과 같습니다.
Table2_UL:=ROWS(Table1)Table3_UL:=Table2_UL+ROWS(Table2)Table4_UL:=Table3_UL+ROWS(Table3)
보시다시피, 각 구성 요소는 마지막 구성 요소를 기반으로 하므로 출력이 동적입니다.이제 훨씬 더 읽기 쉬운 공식이 나왔습니다.
=IF(ROW()-ROW(E3)<Table2_UL,INDEX(CHOOSE({1,2,3},{1},Table1[Column1],Table1[Column2]),ROW()-ROW(E3)+1,COLUMN()-COLUMN(E3)+1),
IF(ROW()-ROW(E3)<Table3_UL,INDEX(CHOOSE({1,2,3},{2},Table2[Column1],Table2[Column2]),ROW()-ROW(E3)-Table2_UL+1,COLUMN()-COLUMN(E3)+1),
IF(ROW()-ROW(E3)<Table4_UL,INDEX(CHOOSE({1,2,3},{3},Table3[Column1],Table3[Column2]),ROW()-ROW(E3)-Table3_UL+1,COLUMN()-COLUMN(E3)+1),
INDEX(CHOOSE({1,2,3},{4},Table4[Column1],Table4[Column2]),ROW()-ROW(E3)-Table4_UL+1,COLUMN()-COLUMN(E3)+1))))
하지만 그것만으로는 부족합니다.나는 그 모든 추잡한 언급들을 없애고 싶습니다.ROW()그리고.COLUMN()이름 관리자에 현재 행과 열을 추적하는 두 개의 값을 추가로 정의하여 이를 수행할 수 있습니다.
Output_CC:=COLUMN()-COLUMN(Sheet1!E3)+1Output_CR:=ROW()-ROW(Sheet1!E3)+1
마지막으로, 우리는 인간이 읽을 수 있는 것을 가지고 있습니다.
=IF(Output_CR-1<Table2_UL,INDEX(CHOOSE({1,2,3},{1},Table1[Column1],Table1[Column2]),Output_CR,Output_CC),
IF(Output_CR-1<Table3_UL,INDEX(CHOOSE({1,2,3},{2},Table2[Column1],Table2[Column2]),Output_CR-Table2_UL,Output_CC),
IF(Output_CR-1<Table4_UL,INDEX(CHOOSE({1,2,3},{3},Table3[Column1],Table3[Column2]),Output_CR-Table3_UL,Output_CC),
INDEX(CHOOSE({1,2,3},{4},Table4[Column1],Table4[Column2]),Output_CR-Table4_UL,Output_CC))))
만약 우리가 정말로 끝까지 끌고 가고 싶다면, 우리는 우리의 생각을 바꿀 수 있습니다.CHOOSE이름이 지정된 값에도 문을 표시합니다.이름 관리자의 각 입력 테이블에 대해 다음 작업을 수행하면 각 테이블에 고유한 이름을 지정할 수 있습니다.
Table1_IN:=CHOOSE({1,2,3},{1},Table1[Column1],Table1[Column2])
이제 공식을 정말 쉽게 읽을 수 있습니다.
=IF(Output_CR-1<Table2_UL,INDEX(Table1_IN,Output_CR,Output_CC),
IF(Output_CR-1<Table3_UL,INDEX(Table2_IN,Output_CR-Table2_UL,Output_CC),
IF(Output_CR-1<Table4_UL,INDEX(Table3_IN,Output_CR-Table3_UL,Output_CC),
INDEX(Table4_IN,Output_CR-Table4_UL,Output_CC))))
그러나 필터를 켜고 배열을 A-Z로 정렬할 수 없기 때문에 충분하지 않습니다."배열의 일부를 변경할 수 없습니다." 오류가 발생합니다.하지만 해결책이 있습니다!도우미 열이 필요하고 출력이 중복되어야 합니다.일반적인 기존 범위 또는 테이블에 복제할 수 있습니다.데이터를 정렬하고 필터링할 수 있도록 하려면 배열 출력의 왼쪽에 도우미 열을 만듭니다. 이 경우 다음과 같이 시작합니다.D3모든 텍스트 열과 마찬가지로 데이터의 순위를 매길 필요가 없으면 정적 번호 매기기(1, 2, 3, 4 등)를 만듭니다.예에서 열은 과 같습니다.G순위를 매길 숫자를 포함합니다.를 매길 을 위를매길있경다입다니력합공식을음우에 하세요.D3아래로 끌어다 놓습니다.
=RANK.EQ(G3,G$3:G$26,0)+COUNTIF(G$3:G3,G3)-1
를 최종인를다음변경니합다로으수로 합니다.1순위 상승이 필요한 경우.이제 데이터가 순위가 매겨진 경우 순서가 맞지 않는 순위가 지정되고, 순위가 매겨진 경우 정렬할 수 없는 배열이 옆에 정적 번호가 지정됩니다.이제 데이터를 범위 또는 테이블로 복제합니다.I에서시여하에서 I3데이터 집합의 길이만큼 정적 번호 매기기(즉, 1, 2, 3, 4)를 만듭니다.이제 셀에서 오른쪽으로.J3a를 입력합니다.VLOOKUP이는 소스 배열의 데이터를 나타냅니다.
=VLOOKUP($I3,$D$3:$G$26,COLUMNS($I$3:J3),FALSE)
수식을 아래로 끈 다음 오른쪽으로 끕니다.이제 정상 범위인 것처럼 데이터를 정렬하고 필터링할 수 있습니다.
이전에 이 스레드에 참여한 분들 덕분에 어레이 데이터를 추가하는 솔루션은 길이가 동일한 어레이를 사용하는 것에 있다는 것을 알게 되었습니다.
이 솔루션은 숫자뿐만 아니라 텍스트에서도 작동하지만 저는 텍스트를 사용했습니다.
아래 예제에서는 3개의 명명된 범위(각각 명명된 범위 1, 범위 2 및 범위 3)에 가입했습니다.저는 또한 각 범위에 있는 품목의 주문이 필요하지 않을 수도 있지만 그대로 유지되도록 했습니다.
엑셀365의 LET 기능을 사용하여 읽기 쉽게 만들었습니다.범위는 공식이 입력된 빈 열에 쏟아집니다.
=LET(
TotRows,ROWS(Range1)+ROWS(Range2)+ROWS(Range3),
A,INDEX(Range1,SEQUENCE(TotRows)),
B,INDEX(Range2,SEQUENCE(TotRows,,-ROWS(Range1)+1,1)),
C,INDEX(Range3,SEQUENCE(TotRows,,-ROWS(Range1)-ROWS(Range2)+1,1)),
X,IFERROR(IFERROR(A,B),C),
X)
D, E, F 등으로 더 많은 범위를 쉽게 추가할 수 있습니다.그런 다음 마지막에 IFERROR() 함수를 더 접습니다.X를 반환하지만 고유 목록을 추출하기 위해 당연히 유니크()로 포장하거나 정렬() 등을 할 수 있습니다.
유출된 범위를 결합하기 위해 이를 약간 수정하면 상황이 더 흥미롭고 역동적으로 변합니다(지정된 범위가 유출된 범위에 대한 참조로 대체됩니다(사용된 예제 및 공식은 첨부된 스크린샷 참조).
위에서 제가 입력한 내용과 결과는 다음과 같습니다. 공식을 사용하여 데이터와 출력의 스크린샷을 만듭니다.
행운을 빌어요.
숫자 배열의 경우:
=SMALL((A1:A4,B1:B4),ROW(INDIRECT("1:"&COUNT(A1:A4)+COUNT(B1:B4))))
또는
=SMALL((A1:A4,B1:B4),ROW(INDEX(A:A,1):INDEX(A:A,COUNT(A1:A4)+COUNT(B1:B4))))
수치 배열에 대해 MMULT 내에서 1과 0의 행렬을 달성하는 것(@Imaginary에서 언급한 바와 같이)Human072889), 아래 코드를 사용합니다.
{= --(((ROW()<=TRANSPOSE(ROW()))*(ROW()<10))*(TRANSPOSE(ROW($1:$11))<=ROW($1:$11)))}
그들의 입장에서는 훌륭한 해결책이지만, 이것은 주소 또는 간접 함수를 사용할 필요 없이 매트릭스 기능에 액세스하는 동안 코드를 더 적게 사용할 것입니다.두 가지 간단한 단계:
- (ROW()<10) 연산자 또는 값을 조정하여 행렬에서 원하는 행 위 및/또는 아래에 0을 만듭니다.
- 행 범위 "11"을 조정하여 두 배열이 결합된 후 존재할 총 행 수에 대응합니다.
그SEQUENCE()함수를 루프 카운터로 사용하여 배열을 연결할 수 있습니다.어레이를 다음과 같이 설정합니다.range1그리고.range2그들은 무엇이든 포함할 수 있습니다.
공식은 다음과 같습니다.
=IF(SEQUENCE(COUNTA(range1)+COUNTA(range2))<=COUNTA(range1),range1,INDEX(range2,SEQUENCE(COUNTA(range1)+COUNTA(range2))-COUNTA(range1)))
이것은 다음을 가정합니다.range1위에 쌓여 있습니다.한다면range15개의 요소를 가지고 있습니다.range26개의 요소를 가지고 있습니다, 그러면SEQUENCE()는 파는트입니다.SEQUENCE(11)= {1, 2, ..., 10, 11}.연결된 의 길이가 되고, 된배열길설다항수사있다음니습용의 할 수 .SEQUENCE()의 경우와 마찬가지로IF식: 카운터가 <=5이면 선택range1요소를 소요, 그지않경을 합니다.range2요소들.
그SEQUENCE()는 둘 다 매핑합니다.range1그리고.range2로 채워진 11개의 열로 된 배열로.FALSE 그서두 번째부 두 번째 .IF 표현용을 사용합니다.INDEX여섯 자리를 더 옮기다range2.
다음 공식도 작동합니다.
=IF(SEQUENCE(COUNTA(range1)+COUNTA(range2))<=COUNTA(range1),INDEX(range1,SEQUENCE(COUNTA(range1))),INDEX(range2,SEQUENCE(COUNTA(range1)+COUNTA(range2))-COUNTA(range1)))
또는
=IF(SEQUENCE(COUNTA(range1)+COUNTA(range2))<=COUNTA(range1),INDEX(range1,SEQUENCE(COUNTA(range1)+COUNTA(range2))),INDEX(range2,SEQUENCE(COUNTA(range1)+COUNTA(range2))-COUNTA(range1)))
샘플:
위의 Robert의 답변은 올바른 방향으로 저를 출발시켰지만, 저는 배열을 여러 열로 병합할 수 있어야 했습니다.
=LET(
TotRows,ROWS(Range1)+ROWS(Range2),
A,TRANSPOSE(INDEX(Range1,SEQUENCE(1,TotRows),SEQUENCE(COLUMNS(Range1)))),
B,TRANSPOSE(INDEX(Range2,SEQUENCE(1,TotRows,-ROWS(Range1)+1,1),SEQUENCE(COLUMNS(Range2)))),
X,IFERROR(A,B),
X)
이렇게 하면 지정된 범위에서 모든 열을 반환할 수 있습니다.
남은 유일한 문제는 두 번째 명명된 범위가 행을 반환할 수 없다는 것입니다.TotRows 정의에서 확인란을 추가하여 두 번째 명명된 범위에 행이 없을 경우 행을 추가하지 않았습니다.
=LET(
TotRows,ROWS(Range1)+IF(COUNT(Range2)=0,0,ROWS(Range2)),
A,TRANSPOSE(INDEX(Range1,SEQUENCE(1,TotRows),SEQUENCE(COLUMNS(Range1)))),
B,TRANSPOSE(INDEX(Range2,SEQUENCE(1,TotRows,-ROWS(Range1)+1,1),SEQUENCE(COLUMNS(Range2)))),
X,IFERROR(A,B),
X)
두 개의 수직 단일 열 목록/열에 대해 다음을 시도합니다.
https://drive.google.com/file/d/1x1SX4VGM7rQPMkJMVAtvDzpmYG2XHZWx/view?usp=sharing
(Google Sheets가 아닌 Excel에서 열기)
저는 LET 기능을 모범 사례로 간주하고 상황을 보다 쉽게 파악할 수 있도록 사용해 왔습니다.
사용되는 다른 동적 배열 기능은 SORT, UNIKE, FILTER, SEQUENCE, CHOSE 및 INDEX입니다.
이게 도움이 되길 바랍니다.
세인트
아래의 공식도 마찬가지입니다.
참고: List1 및 List2는 명명된 범위이며, K3# 및 M3#은 유출된 배열입니다(마지막 두 공식 참조).
모든 고유 항목:
=SORT(UNIQUE(LET(
RightList,FILTER(List2, NOT(ISBLANK(List2))),
LeftList,FILTER(List1, NOT(ISBLANK(List1))),
BothListCount,SEQUENCE(ROWS(LeftList)+ROWS(RightList),1,1),
INDEX(CHOOSE({1,2},LeftList,RightList),
N(IF({1},BothListCount IF(BothListCount<=ROWS(LeftList),0,ROWS(LeftList)))),
N(IF({1},2-(BothListCount<=ROWS(LeftList)))))
)))
두 목록 모두:
=SORT(UNIQUE(FILTER(List1,COUNTIF(List2,List1)>0)))
두 목록 모두에 없음
=SORT(UNIQUE(LET(
RightList, M3#,
LeftList,K3#,
BothListCount,SEQUENCE(ROWS(LeftList)+ROWS(RightList),1,1),
INDEX(CHOOSE({1,2},LeftList,RightList),
N(IF({1},BothListCount-IF(BothListCount<=ROWS(LeftList),0,ROWS(LeftList)))),
N(IF({1},2-(BothListCount<=ROWS(LeftList)))))
)))
목록 1에서 목록 2가 아닌 - 위 공식에서 K3#이라고도 함
=SORT(UNIQUE(FILTER(List1,(COUNTIF(List2,List1)=0)*NOT(ISBLANK(List1)))))
목록 2에서 목록 1이 아닌 - 위 공식에서 M3#이라고도 함
=SORT(UNIQUE(FILTER(List2,(COUNTIF(List1,List2)=0)*NOT(ISBLANK(List2)))))
두 기호로 합니다. "#". "#""와 같이 입력합니다.
Office 365 TextJoin()을 사용하여 한 단계로 수행할 수 있습니다.결과가 셀 A1의 문자열이라고 가정합니다.
10#11#12#13#20#21#22
그런 다음 다음 수식을 사용하여 단일 문자열 배열을 만듭니다.배열을 표시할 7개의 연속 셀을 선택한 후 Ctrl-SHFT-Enter를 클릭하여 가새를 가져옵니다.
{=TRIM(MID(
SUBSTITUTE(A1,"#",REPT(" ",99)),
(ROW(OFFSET($AB$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1))-1)*99+
((ROW(OFFSET($AB$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1)))=1),
99))}
다음 공식과 같이 숫자로 변환하여 배열을 합할 수도 있습니다.
{=SUM(1*TRIM(MID(SUBSTITUTE(A1,"#",REPT(" ",99)),(ROW(OFFSET($AB$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1))-1)*99+((ROW(OFFSET($AB$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1)))=1),99)))}= 109
Excel에서 완전히 예상치 못한 동작으로 많은 실험을 한 후, 저는 꽤 간단한 공식을 사용하게 되었습니다.
=IF(ROW()<=COUNTA(A:A),INDEX(A:A,ROW()),INDEX(B:B,ROW()-COUNTA(A:A)))
이죠.counta(a:a)공식이 더욱 빨라지도록 명명된 범위/셀에 할당할 수 있습니다.
또한 이것은 단순한 인덱스() 사용이기 때문에 (숫자, 텍스트, 날짜 등)에 관계없이 배열을 추가합니다!
포함Index(), ...에...◦ 순식간에IfError(...,0)크기가 다른 배열을 선택한 경우 문!(Excel이 IF(..., ..., ...) 문에서 잘못된 부분을 계산하지 않아야 하는데도 이상한 오류가 나타납니다.)
공식을 다음과 같이 확장합니다.
=IF(ROW()<=COUNTA(H:I),INDEX(H:I,ROW(),COLUMN(A1:B1)),INDEX(K:L,ROW()-COUNTA(H:I),COLUMN(A1:B1)) )
여러 열의 배열을 추가하는 중...
언급URL : https://stackoverflow.com/questions/46183237/is-there-a-way-to-concatenate-two-arrays-in-excel-without-vba
'programing' 카테고리의 다른 글
| 도커 mariadb에서 연합 엔진을 활성화하는 방법 (0) | 2023.06.21 |
|---|---|
| 인스턴트 기사 - 트윗 포함 (0) | 2023.06.21 |
| ORA-00060: 리소스를 기다리는 동안 교착 상태가 감지됨 (0) | 2023.06.21 |
| PowerShell의 여러 줄 댓글 (0) | 2023.04.22 |
| '[DBContext의 이름]' 형식의 개체를 만들 수 없습니다.설계 시 지원되는 다양한 패턴에 대하여 (0) | 2023.04.22 |