Nối các mảng Excel có hoặc không có VBA (và một số tính năng lạ của Excel)

Gần đây, tôi cần tạo một ma trận được nối từ 2 ma trận [hoặc công thức được đặt tên nếu phù hợp với bạn], trong Excel. Việc thêm một ma trận vào một ma trận khác không được thực hiện dễ dàng trong Excel. Ngay cả khi hoàn thành, Excel vẫn thể hiện một hành vi rất đặc biệt. Đây là "câu chuyện":

Sau khi tạo một giải pháp VBA (mã ở dưới cùng), như mọi khi, tôi cố gắng thực hiện theo cách của Excel (noVBA). Sau khi đọc nhiều bài viết và thấy nhiều người nhầm lẫn Append for Concatenate, tôi đã nghĩ ra một cách hay để nối chúng bằng IF và INDEX(). Và mặc dù nó có vẻ hiệu quả, nhưng tôi đã nhận thấy những điều kỳ lạ. Tôi giải thích các bước để có được ý tưởng và đạt được các vấn đề:

• · (Dải ô được đặt tên hoặc Ma trận hoặc Mảng, tất cả đều đề cập đến cùng một thứ… ít nhất là ở đây.)
• · Tạo một mảng từ các phần tử có thể nhìn thấy và gọi nó là Randoms, một cột
• · Tạo một mảng khác từ các phần tử có thể nhìn thấy và gọi nó là smRandoms, kích thước khác với Randoms nhưng lại là Cột đơn
• · Tạo mảng chỉ số {1,2,3,…} sử dụng công thức RowsAll =Row(Indirect("1:"&(rows(Randoms)+rows(smRandoms))))

Bây giờ, ý tưởng là tạo mảng Đã tham gia chứa cả Randoms và smRandoms:

Đã tham gia (1 đến hàng (Ngẫu nhiên) ) =Randoms()

và

Đã tham gia (hàng (Ngẫu nhiên) +1 đến hàng (RowsAll)) = smRandoms

, đó là nối thêm mảng smRandoms vào mảng Randoms.

Một công thức xuất hiện trong tâm trí ngay lập tức là:

=if(RowsAll>rows(Random) , Index(smRandoms,RowsAll-rows(Randoms)) , Randoms )

Nhưng điều này không hoạt động !!! Kết quả là:

Đã tham gia (1 đến hàng (Ngẫu nhiên) -1 ) =Randoms()

và

Đã nối(hàng(Ngẫu nhiên) với hàng(RowsAll)) = #N/A !!(1)!!

Bây giờ sửa thành

=if(RowsAll>rows(Random) , Index(smRandoms,RowsAll-rows(Randoms)) , Index(Randoms,RowsAll) )

Có vẻ như hoạt động tốt hơn, nhưng một lần nữa kết quả là:

Đã tham gia (1 đến hàng (Ngẫu nhiên) -1 ) =Randoms()

và

Đã tham gia (hàng (Ngẫu nhiên) +1 đến hàng (RowsAll)) = smRandoms ()

NHƯNG Đã tham gia(hàng(Ngẫu nhiên)) = #N/A !!(2)!!

Thật ki quặc! Nó "ăn" một phần tử từ Random tạo ra #N/A!

thử nghiệm với

=IF(RowsAll>ROWS(Randoms) , "-" , INDEX(RowsAll,RowsAll))

sao chép Randoms ok ( từ 1 đến Rows(Randoms) , không có phần tử ăn ở đây)

thử nghiệm với

=IF(RowsAll>ROWS(Randoms) , INDEX(smRandoms,RowsAll-ROWS(Randoms)) , "-" )

sao chép smRandoms ok, nhưng vẫn có #N/A ở cuối "-"…

Vì vậy, câu hỏi chính ở đây là tại sao #N/A này lại xuất hiện ở giữa ma trận của tôi? Có phải do hành vi Index(), lỗi Excel, lỗi của tôi…? !!(2)!!

Cách giải quyết ở đây là đặt các smRandoms theo thứ tự ngược lại (ok, danh sách của tôi vẫn là danh sách, nhưng đây có phải là giải pháp không???) như thế này:

=IF(RowsAll>ROWS(Randoms),INDEX(smRandoms, ROWS(RowsAll)-RowsAll+1 ),INDEX(Randoms,RowsAll))

Đây thực sự là một cách giải quyết vì các phần tử của smRandoms có thứ tự ngược lại trong Đã tham gia!

Một câu hỏi khác là tại sao nó không hoạt động với Randoms đơn giản và nó cần index(Randoms, …) !!(1)!!

Có vẻ như tôi sẽ gắn bó với giải pháp VBA, nhưng tôi vẫn băn khoăn về hành vi của excel.

'----------------MÃ TẠI ĐÂY---------

' AppendArrays: Nối các mảng dọc Excel lần lượt...

' Sử dụng Transpose để nối thêm những cái Ngang cho đến khi nó được sửa

' Đã thêm vào Error-Resume-Next-0 để xử lý các trường hợp Array() VS Variant()

' bởi Apostolos55

Hàm công khai AppendArrays(ParamArray Aprm()) dưới dạng biến thể

Dim Dum(), Asm, NewSz As Long, Icr As Long, Ilcl As Long, Ai, SmSz As Long

' Tìm và sửa Kích thước cho mảng sắp xếp lại

Khi gặp lỗi Tiếp tục Tiếp theo

SmSz = 1

Cho Mỗi Asm Trong Tháng Tư

NewSz = UBound(Asm, 1) - LBound(Asm, 1) + 1 + NewSz

Nếu LBound(Asm, 1) < SmSz Thì SmSz = 0

NewSz = NewSz + Asm.Count

Tiếp theo

Nếu SmSz = 0 Thì NewSz = NewSz - 1

ReDim Dum(SmSz Đến NewSz, 1 Đến 1)

' Nối được thực hiện ở đây

Icr = SmSz

Cho Mỗi Asm Trong Tháng Tư

Đối với mỗi Ai trong Asm:

Dum(Icr, 1) = Ái:

Icr = Icr + 1:

Tiếp theo

Tiếp theo

AppendArrays = Dum: Dum = Array()

chức năng kết thúc

'--------------------- HẾT MÃ-------------------

Câu hỏi cũng được hỏi ở đây

Câu trả lời:

Gần đây, tôi cần tạo một ma trận được nối từ 2 ma trận [hoặc công thức được đặt tên nếu phù hợp với bạn], trong Excel.

Hãy thử chức năng dưới đây.

Andreas.

CHỈNH SỬA:

dòng
Đối với i = 1 Đến UBound(Dữ liệu)
Đối với j = 1 Đến UBound(Dữ liệu, 2)
được trao đổi, bây giờ nó lưu trữ dữ liệu theo hàng.


Hàm nối thêm (ParamArray Args()) dưới dạng biến thể
'Kết hợp tất cả các đối số đã truyền vào một mảng, phải được gọi là công thức ma trận
'Kết quả là một mảng hai chiều có cùng kích thước với công thức ma trận
'Đối số có thể là tham chiếu ô, giá trị đơn hoặc mảng ma trận
'Ví dụ: {=Nối(A1:A5,"x",{7;8;9})}

Kết quả mờ (), Dữ liệu
Dim a As Long, Size As Long
Dim i As Long, j As Long, r As Long, c As Long

'Bước 1: Xác định kích thước của mảng kết quả
Đối với a = 0 Đến UBound(Args)
Nếu TypeOf Args(i) Là Phạm vi Thì
'tham chiếu ô => "A1:B3"
Kích thước = Kích thước + Args(i).Rows.Count * Args(i).Columns.Count
ElseIf IsArray(Args(i)) Then
'mảng => "{1,2,3}"
Kích thước = Kích thước + UBound(Args(i)) - LBound(Args(i)) + 1
Khác
'giá trị
Kích thước = Kích thước + 1
kết thúc nếu
Tiếp theo

'Bước 2: Cấp phát mảng kết quả
Nếu TypeOf Application.Caller là phạm vi thì
'Lấy kích thước của công thức ma trận
Với Application.Caller
Kết quả ReDim (1 đến .Rows.Count, 1 đến .Columns.Count)
Kết thúc với
Nếu Kích thước = 0 Thì
'Điền vào chuỗi rỗng
For i = 1 To UBound(Kết quả)
Đối với j = 1 Đến UBound(Kết quả, 2)
Kết quả(i, j) = ""
Tiếp theo
Tiếp theo
Tới Điểm Thoát
kết thúc nếu
Khác
Nếu Kích thước = 0 Thì
'Trả về mảng rỗng
Tới Điểm Thoát
Khác
'Trả về onyl một cột
Kết quả ReDim (1 đến Kích thước, 1 đến 1)
kết thúc nếu
kết thúc nếu

'Bước 3: Copy dữ liệu vào mảng kết quả
r = 0
c = 1
Đối với a = 0 Đến UBound(Args)
Dữ liệu = Args(a)
Nếu IsArray(Dữ liệu) Thì
'tham chiếu ô => "A1:B3"
'mảng => "{1,2,3}"
Đối với j = 1 Đến UBound(Dữ liệu, 2)
Đối với i = 1 Đến UBound(Dữ liệu)
r = r + 1
Nếu r > UBound(Kết quả) Thì
c = c + 1
Nếu c > UBound(Result, 2) Then GoTo ExitPoint
r = 1
kết thúc nếu
Kết quả(r, c) = Dữ liệu(i, j)
Tiếp theo
Tiếp theo
Khác
'giá trị
r = r + 1
Nếu r > UBound(Kết quả) Thì
c = c + 1
Nếu c > UBound(Result, 2) Then GoTo ExitPoint
r = 1
kết thúc nếu
Kết quả(r, c) = Dữ liệu
kết thúc nếu
Tiếp theo

'Bước 4: Xóa các trường còn lại
r = r + 1
For c = c To UBound(Kết quả, 2)
Cho r = r Tới UBound(Kết quả)
Kết quả(r, c) = ""
Tiếp theo
r = 1
Tiếp theo

Điểm thoát:
Nối = Kết quả
chức năng kết thúc


Câu trả lời:

Xin chào sát thủ Andreas

Tôi đánh giá rất cao sự đóng góp của bạn và thời gian của bạn, cảm ơn bạn. Mã này rất hay nhưng không thực hiện chính xác những gì nó nói. Nó sẽ cố gắng khớp tất cả dữ liệu trong mảng kết quả (nếu trường hợp này xảy ra) nhưng nó đọc dữ liệu theo hàng và ghi chúng vào cột. Vì vậy, nó rất tốt cho danh sách nhưng không tốt cho mảng. (Của tôi vì nó dành cho mảng dọc 1 chiều). Tôi sẽ sớm quay lại vấn đề này với nhiều bình luận hơn và nhiều bài kiểm tra hơn.

Trong khi đó, nó không trả lời 2 câu hỏi chính liên quan đến quy trình Excel.

Câu hỏi 1): Tôi tin rằng đó là lỗi khi đưa vào một công thức nhiều Mảng có kích thước khác nhau. Bất cứ ai có thể xác minh điều này?

Câu 2): Không biết, trường hợp trên rõ ràng là không nhiều vì các phần tử được lấy thông qua hàm Index. Ngoài ra, thông thường phần tử đó đã được xử lý khi hiệu ứng từ Mảng thứ 2 biến nó thành #N/A...

Tôi tin rằng đó là Excel sâu và nên được trả lời bởi một người có kiến ​​​​thức từ bên trong, nhưng tất nhiên tôi có thể nhầm.

Có bất kỳ mã VBA nào để nối các mảng nhiều chiều không? Có lẽ với sự lựa chọn theo hướng của append?

Mọi người hãy dành thời gian cho bạn, đó là một vấn đề rất thú vị nhưng tôi không vội.

Cya

Câu trả lời:

Mã này rất đẹp nhưng nó đọc dữ liệu theo hàng và ghi chúng vào cột.

Câu hỏi 1): Tôi tin rằng đó là lỗi khi đưa vào một công thức nhiều Mảng có kích thước khác nhau. Bất cứ ai có thể xác minh điều này?

Câu 2): Không biết, trường hợp trên rõ ràng là không nhiều vì các phần tử được lấy thông qua hàm Index. Ngoài ra, thông thường phần tử đó đã được xử lý khi hiệu ứng từ Mảng thứ 2 biến nó thành #N/A...

Tôi đã thay đổi mã ở trên, bây giờ nó lưu trữ dữ liệu theo hàng.

1) Vâng, đây là một vấn đề với các công thức.

2) Không.

Bạn có thể nối các mảng nhiều chiều, nhưng có rất nhiều khả năng, câu hỏi đặt ra là kết quả sẽ như thế nào?

Nếu chúng ta gặp tình huống này (kiểu CSV):

1;2;3
4,5;6

AC
b;d

Bạn mong đợi kết quả gì?

Andreas.