Viết hàm nối mảng (5 người xem)

Liên hệ QC

Người dùng đang xem chủ đề này

Đang trực tuyến: 1 (Thành viên: 0, Khách: 1)
V

viethung78

Thành viên chính thức
Tham gia
3/6/16
Bài viết
88
Được thích
53
Từ Excel 365 và Excel 2021 có hàm mảng động quá hay. Nhưng hay và thiết thực hơn nhiều nếu nối được các mảng động này mà không phải tạo DataTable và PowerQuery.

Bài toán là có 2 mảng động cùng kích thước ngang, làm sao có thể viết hàm VBA để nối bảng chúng lại với nhau được. Tôi đã cố gắng nhưng chưa đạt được. Rất mong cộng đồng xắn tay trợ giúp. Xin cảm ơn!

1657700866444.png
1657700965447.png
 

File đính kèm

  • Range Union.xlsb
    Range Union.xlsb
    13.8 KB · Đọc: 4
  • 1657700883350.png
    1657700883350.png
    18.4 KB · Đọc: 1
viethung78 đã viết:
Từ Excel 365 và Excel 2021 có hàm mảng động quá hay. Nhưng hay và thiết thực hơn nhiều nếu nối được các mảng động này mà không phải tạo DataTable và PowerQuery.

Bài toán là có 2 mảng động cùng kích thước ngang, làm sao có thể viết hàm VBA để nối bảng chúng lại với nhau được. Tôi đã cố gắng nhưng chưa đạt được. Rất mong cộng đồng xắn tay trợ giúp. Xin cảm ơn!
Nhấp chuột vào đây để mở rộng...
Giải pháp khác là Multiple PivotTable, tổng hợp nhiều bảng với nhau.
1657702712604.png
 

File đính kèm

viethung78 đã viết:
Từ Excel 365 và Excel 2021 có hàm mảng động quá hay. Nhưng hay và thiết thực hơn nhiều nếu nối được các mảng động này mà không phải tạo DataTable và PowerQuery.

Bài toán là có 2 mảng động cùng kích thước ngang, làm sao có thể viết hàm VBA để nối bảng chúng lại với nhau được. Tôi đã cố gắng nhưng chưa đạt được. Rất mong cộng đồng xắn tay trợ giúp. Xin cảm ơn!

View attachment 278791
View attachment 278793
Nhấp chuột vào đây để mở rộng...
Thử code này xem sao. Code chưa bẩy lỗi và Test hết các trường hợp.
Mã: 
Function Union_Example(ParamArray args() As Variant)
    Dim sCell As Range, w As Integer, h As Integer
    Dim Arr, i As Integer, j As Integer, k As Integer
    Dim Rng As Range
    For i = LBound(args) To UBound(args)
        If TypeName(args(i)) = "Range" Then
            If Rng Is Nothing Then
                Set Rng = args(i)
            Else
                Set Rng = Union(Rng, args(i))
            End If
        End If
    Next i
    If Rng Is Nothing Then
       Union_Example = Null
       Exit Function
    End If
    If CheckRange(Rng, w, h) Then
        ReDim Arr(1 To h, 1 To w)
        k = 0
        For Each sCell In Rng.Areas
            For i = 1 To sCell.Rows.Count
                k = k + 1
                For j = 1 To sCell.Columns.Count
                    Arr(k, j) = sCell.Cells(i, j).Value2
                Next j
            Next i
        Next sCell
        Union_Example = Arr
    Else
        Union_Example = Null
    End If
End Function

Function CheckRange(ByVal Rng As Range, w As Integer, h As Integer) As Boolean
    Dim sCell As Range, sCol As Integer
    w = 0: h = 0
    CheckRange = True
    For Each sCell In Rng.Areas
        If sCol = 0 Then
            sCol = sCell.Columns.Count
        ElseIf sCol <> sCell.Columns.Count Then
            CheckRange = False
            Exit Function
        End If
        w = sCol
        h = h + sCell.Rows.Count
    Next sCell
End Function
Public Sub Test()
    Dim Arr
    Arr = Union_Example(Range("A2:B6"), Range("D8:E10"))
    If IsArray(Arr) Then
        Range("N14").Resize(UBound(Arr), UBound(Arr, 2)).Value = Arr
    Else
        MsgBox "Du lieu khong khop giua cac cot"
    End If
End Sub
 
leonguyenz đã viết:
Giải pháp khác là Multiple PivotTable, tổng hợp nhiều bảng với nhau.
View attachment 278799
Nhấp chuột vào đây để mở rộng...
Dùng Multi Pivot thì dùng Power Query còn hơn anh ạ
Bài đã được tự động gộp:

giaiphap đã viết:
Thử code này xem sao. Code chưa bẩy lỗi và Test hết các trường hợp.
Mã: 
Function Union_Example(ParamArray args() As Variant)
    Dim sCell As Range, w As Integer, h As Integer
    Dim Arr, i As Integer, j As Integer, k As Integer
    Dim Rng As Range
    For i = LBound(args) To UBound(args)
        If TypeName(args(i)) = "Range" Then
            If Rng Is Nothing Then
                Set Rng = args(i)
            Else
                Set Rng = Union(Rng, args(i))
            End If
        End If
    Next i
    If Rng Is Nothing Then
       Union_Example = Null
       Exit Function
    End If
    If CheckRange(Rng, w, h) Then
        ReDim Arr(1 To h, 1 To w)
        k = 0
        For Each sCell In Rng.Areas
            For i = 1 To sCell.Rows.Count
                k = k + 1
                For j = 1 To sCell.Columns.Count
                    Arr(k, j) = sCell.Cells(i, j).Value2
                Next j
            Next i
        Next sCell
        Union_Example = Arr
    Else
        Union_Example = Null
    End If
End Function

Function CheckRange(ByVal Rng As Range, w As Integer, h As Integer) As Boolean
    Dim sCell As Range, sCol As Integer
    w = 0: h = 0
    CheckRange = True
    For Each sCell In Rng.Areas
        If sCol = 0 Then
            sCol = sCell.Columns.Count
        ElseIf sCol <> sCell.Columns.Count Then
            CheckRange = False
            Exit Function
        End If
        w = sCol
        h = h + sCell.Rows.Count
    Next sCell
End Function
Public Sub Test()
    Dim Arr
    Arr = Union_Example(Range("A2:B6"), Range("D8:E10"))
    If IsArray(Arr) Then
        Range("N14").Resize(UBound(Arr), UBound(Arr, 2)).Value = Arr
    Else
        MsgBox "Du lieu khong khop giua cac cot"
    End If
End Sub
Nhấp chuột vào đây để mở rộng...
CODE NÀY KHI HAI MẢNG NGUỒN Ở SHEET KHÁC NHAU LÀ HÀM BÁO LỖI ANH AH
 
Lần chỉnh sửa cuối:
viethung78 đã viết:
Dùng Multi Pivot thì dùng Power Query còn hơn anh ạ
Bài đã được tự động gộp:


CODE NÀY KHI HAI MẢNG NGUỒN Ở SHEET KHÁC NHAU LÀ HÀM BÁO LỖI ANH AH
Nhấp chuột vào đây để mở rộng...
Sửa lại chút.
Mã: 
Function Union_Example(ParamArray args() As Variant)
    Dim sCell As Range, Rng As Range, w As Integer, h As Integer
    Dim Arr, i As Integer, j As Integer, k As Integer, n As Integer
    For i = LBound(args) To UBound(args)
        If TypeName(args(i)) = "Range" Then
            For Each sCell In args(i).Areas
                If w < sCell.Columns.Count Then w = sCell.Columns.Count
                h = h + sCell.Rows.Count
            Next sCell
        End If
    Next i
        ReDim Arr(1 To h, 1 To w)
        k = 0
    For i = LBound(args) To UBound(args)
        If TypeName(args(i)) = "Range" Then
            For Each sCell In args(i).Areas
                For n = 1 To sCell.Rows.Count
                    k = k + 1
                    For j = 1 To sCell.Columns.Count
                        Arr(k, j) = sCell.Cells(n, j).Value2
                    Next j
                    'Neu muon bo so 0 thi dung 3 dong lenh duoi day
                    'For j = sCell.Columns.Count + 1 To w
                    '    Arr(k, j) = ""
                    'Next j
                Next n
            Next sCell
        End If
    Next i
    Union_Example = Arr
End Function

Public Sub Test()
    Dim Arr
    Arr = Union_Example(Union(Sheet1.Range("A2:B6"), Sheet1.Range("D8:E10")), Sheet2.Range("C6:E10"))
    Sheet1.Range("N14").Resize(UBound(Arr), UBound(Arr, 2)).Value = Arr
End Sub
 
giaiphap đã viết:
Sửa lại chút.
Mã: 
Function Union_Example(ParamArray args() As Variant)
    Dim sCell As Range, Rng As Range, w As Integer, h As Integer
    Dim Arr, i As Integer, j As Integer, k As Integer, n As Integer
    For i = LBound(args) To UBound(args)
        If TypeName(args(i)) = "Range" Then
            For Each sCell In args(i).Areas
                If w < sCell.Columns.Count Then w = sCell.Columns.Count
                h = h + sCell.Rows.Count
            Next sCell
        End If
    Next i
        ReDim Arr(1 To h, 1 To w)
        k = 0
    For i = LBound(args) To UBound(args)
        If TypeName(args(i)) = "Range" Then
            For Each sCell In args(i).Areas
                For n = 1 To sCell.Rows.Count
                    k = k + 1
                    For j = 1 To sCell.Columns.Count
                        Arr(k, j) = sCell.Cells(n, j).Value2
                    Next j
                    'Neu muon bo so 0 thi dung 3 dong lenh duoi day
                    'For j = sCell.Columns.Count + 1 To w
                    '    Arr(k, j) = ""
                    'Next j
                Next n
            Next sCell
        End If
    Next i
    Union_Example = Arr
End Function

Public Sub Test()
    Dim Arr
    Arr = Union_Example(Union(Sheet1.Range("A2:B6"), Sheet1.Range("D8:E10")), Sheet2.Range("C6:E10"))
    Sheet1.Range("N14").Resize(UBound(Arr), UBound(Arr, 2)).Value = Arr
End Sub
Nhấp chuột vào đây để mở rộng...
VẪN BỊ LỖI TRƯỜNG HỢP CÁC BẢNG DỮ LIỆU Ở CÁC SHEET KHÁC NHAU ANH AH
 
Nếu đã xài 365 thì xài hàm mảng 365. Định dạng table cho 2 vùng có tên Data1 và Data2

=LET(d_1,Data1,d_2,Data2,r_1,ROWS(d_1),r_2,ROWS(d_2),r_0,r_1+r_2,IF(SEQUENCE(r_0)<=r_1,INDEX(d_1,SEQUENCE(r_1),{1,2}),INDEX(d_2,SEQUENCE(r_0)-r_1,{1,2})))

Nếu có hàm VStack thì càng đơn giản:

=VSTACK(Data1,Data2)

1657768653917.png

1657768682253.png

Nối 3, 4 bảng chỉ cần liệt kê

1657769066365.png
 
Lần chỉnh sửa cuối:
ptm0412 đã viết:
Nếu có hàm VStack thì càng đơn giản:
Nhấp chuột vào đây để mở rộng...

Còn hàm HSTACK nữa anh.

---
Khoản nối mảng này Google Sheets làm quá hay, chỉ cần liệt kê vào { } là xong. Nối ngang / dọc thì phụ thuộc vào dấu phân cách.

---
Chắc thớt mới nghĩ tới nối dọc xuống, chưa nghĩ ra còn nối rộng sang ngang. :D :D
 
Bạn tham khảo hàm S_ArrayMerge trong tệp dưới:

S_ArrayMerge hỗ trợ cả Sort sau khi nối mảng, nếu bạn gõ thêm hàm nhập tham số là I_Sort, ví dụ:
gõ: =S_ArrayMerge(A1:C100,G1:G100)
gõ: =S_ArrayMerge(I_Sort(1),A1:C100,G1:G100)
gõ: =S_ArrayMerge(I_Sort(1,False,False,False,False),A1:C100,G1:G100)

I_Sort: Index là Cột hoặc hàng, xếp từ nhỏ / lớn, xếp ngang / dọc, đảo mảng, sắp xếp phân biệt hoa thường

Có thể sử dụng S_ArrayMerge ở phiên bản Excel HĐH Windows bất kỳ.
 

File đính kèm

Lần chỉnh sửa cuối:
ptm0412 đã viết:
Nếu đã xài 365 thì xài hàm mảng 365. Định dạng table cho 2 vùng có tên Data1 và Data2

=LET(d_1,Data1,d_2,Data2,r_1,ROWS(d_1),r_2,ROWS(d_2),r_0,r_1+r_2,IF(SEQUENCE(r_0)<=r_1,INDEX(d_1,SEQUENCE(r_1),{1,2}),INDEX(d_2,SEQUENCE(r_0)-r_1,{1,2})))

Nếu có hàm VStack thì càng đơn giản:

=VSTACK(Data1,Data2)
...
Nhấp chuột vào đây để mở rộng...
Lão già nói chung cho mọi người hay nói riêng với đòi hỏi "hay và thiết thực hơn nhiều nếu..."?
Điều trước thì tốt. Điều sau thì quên đi. Một khi người ta nghĩ tới cái từ "code VBA" rồi là mọi thứ khác chỉ là đồ bỏ. 100 người lên GPE đủ 100.

giaiphap đã viết:
Thử code này xem sao. Code chưa bẩy lỗi và Test hết các trường hợp.
...
Nhấp chuột vào đây để mở rộng...
Bạn bị người ta dẫn trước từ "Union" ròi cho nên lạc lối. Ở GPE này, tôi đã đóng băng định kiến: "dân lên đây hỏi bài đều là chủ quan. Không cần phải theo họ".

Khi cần nhóm một nhóm Objects lại, kỹ thuật đơn giản nhất là dùng Collection. Range là Object, một nhóm Ranges dùng Collection là hiệu quả nhất.

befaint đã viết:
---
Khoản nối mảng này Google Sheets làm quá hay, chỉ cần liệt kê vào { } là xong. Nối ngang / dọc thì phụ thuộc vào dấu phân cách.
...
Nhấp chuột vào đây để mở rộng...
Google Sheets không phải phục vụ dân bí-zi-nịt cho nên tha hồ viết hàm chạy kiểu khoa học. Mà bạn là gốc khoa học cho nên thấy cái gì cũng hợp với mình. Anh Gú này không chịu trách nhiệm nếu bạn hiểu sai.

Anh bạn Joe McDaid của chúng ta bắt buộc phải rõ rệt bên này bên kia. Có nhiều tính năng không thể đâp ứng được vì chúng có khả năng lẫn lộn rồng rắn.
 
Lần chỉnh sửa cuối:
ptm0412 đã viết:
Nếu đã xài 365 thì xài hàm mảng 365. Định dạng table cho 2 vùng có tên Data1 và Data2

=LET(d_1,Data1,d_2,Data2,r_1,ROWS(d_1),r_2,ROWS(d_2),r_0,r_1+r_2,IF(SEQUENCE(r_0)<=r_1,INDEX(d_1,SEQUENCE(r_1),{1,2}),INDEX(d_2,SEQUENCE(r_0)-r_1,{1,2})))

Nếu có hàm VStack thì càng đơn giản:

=VSTACK(Data1,Data2)

View attachment 278832

View attachment 278833

Nối 3, 4 bảng chỉ cần liệt kê

View attachment 278835
Nhấp chuột vào đây để mở rộng...
Chừng Tháng 3, tháng 4 gì đấy em có đọc được trên mạng về 14 hàm mới của excel 365 như : Textbefore/ Textafter/ textsplit/ Vstack/Hstack/Torow.... nhưng khi em thử tìm trong excel 365 của mình lại không có, có thể chưa được cập nhật hay sao đó. Nhờ anh chỉ giúp em làm sao để cập nhật với ạ. (Em đang dùng lab công ty nên nếu cập nhật thì có cần admin cấp quyền hay tự mình làm được ạ?)
 
Đâu cần hỏi gì.
Gõ vào google: vstack function in excel
Là biết có ở đâu, bao giờ có, làm sao có.
 
DUNGHOA1963 đã viết:
Chừng Tháng 3, tháng 4 gì đấy em có đọc được trên mạng về 14 hàm mới của excel 365 như : Textbefore/ Textafter/ textsplit/ Vstack/Hstack/Torow.... nhưng khi em thử tìm trong excel 365 của mình lại không có, có thể chưa được cập nhật hay sao đó. Nhờ anh chỉ giúp em làm sao để cập nhật với ạ. (Em đang dùng lab công ty nên nếu cập nhật thì có cần admin cấp quyền hay tự mình làm được ạ?)
Nhấp chuột vào đây để mở rộng...
 
Lần chỉnh sửa cuối:
VetMini đã viết:
Trời nào? Không thấy tôi nhắc cái tên "Giô" hay sao?
Nhấp chuột vào đây để mở rộng...
H (horizontal) và V (vertical) rõ ràng là cặp trời sinh từ thuở khai thiên lập địa. Ông Descartes xài Hệ tọa độ vuông góc cũng phải xài 1 cặp trục tọa độ. Quartro và sau đó là Lotus 123 tạo biểu đồ trên máy tính cũng phải có 1 cặp. Hàm VLookup và HLookup cũng có từ xưa xửa xừa xưa.
Tên "Giô" hay "Giếc" gì cũng đâu có sáng tạo ra cái cặp đó cho VStack và HStack?
 
HeSanbi đã viết:
Bạn tham khảo hàm S_ArrayMerge trong tệp dưới:

S_ArrayMerge hỗ trợ cả Sort sau khi nối mảng, nếu bạn gõ thêm hàm nhập tham số là I_Sort, ví dụ:
gõ: =S_ArrayMerge(A1:C100,G1:G100)
gõ: =S_ArrayMerge(I_Sort(1),A1:C100,G1:G100)
gõ: =S_ArrayMerge(I_Sort(1,False,False,False,False),A1:C100,G1:G100)

I_Sort: Index là Cột hoặc hàng, xếp từ nhỏ / lớn, xếp ngang / dọc, đảo mảng, sắp xếp phân biệt hoa thường

Có thể sử dụng S_ArrayMerge ở phiên bản Excel HĐH Windows bất kỳ.
Nhấp chuột vào đây để mở rộng...
CẢM ƠN ANH ĐÃ TRỢ GIÚP NHÉ
Bài đã được tự động gộp:

ptm0412 đã viết:
Nếu đã xài 365 thì xài hàm mảng 365. Định dạng table cho 2 vùng có tên Data1 và Data2

=LET(d_1,Data1,d_2,Data2,r_1,ROWS(d_1),r_2,ROWS(d_2),r_0,r_1+r_2,IF(SEQUENCE(r_0)<=r_1,INDEX(d_1,SEQUENCE(r_1),{1,2}),INDEX(d_2,SEQUENCE(r_0)-r_1,{1,2})))

Nếu có hàm VStack thì càng đơn giản:

=VSTACK(Data1,Data2)

View attachment 278832

View attachment 278833

Nối 3, 4 bảng chỉ cần liệt kê

View attachment 278835
Nhấp chuột vào đây để mở rộng...
HÀM NÀY KHÔNG CẦN VBA NÊN TỐT QUÁ. CÓ THỂ DÙNG TRÊN EXCEL ONLINE. RẤT CẢM ƠN ANH.
 
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

Bài viết mới nhất

G
Xin Code xóa Module. (5 người xem)
Trả lời
2
Đọc
52
Hoàng Tuấn 868
H
L
Lỗi font khi convert từ pdf (1 người xem)
Trả lời
3
Đọc
107
lamvu0677
L
N
Khắc phục lỗi "The picture is too large and will be truncated" (1 người xem)
Trả lời
9
Đọc
5K
SonBDND
S
GPE-Trợ Lý
[GPE] TALKSHOW: PHÂN TÍCH BÁO CÁO ĐỘ NHẠY SỬ DỤNG EXCEL SOLVER (3 người xem)
Trả lời
0
Đọc
52
GPE-Trợ Lý
GPE-Trợ Lý
D
  • Question Question
Quản lý email outlook qua VBA cần giúp đỡ (3 người xem)
Trả lời
10
Đọc
172
nguyendang95
N
K
  • Question Question
nhờ ace giúp đỡ tự động filter và In hàng loạt từng mục trong piviot table (1 người xem)
Trả lời
1
Đọc
80
khkkh
K
K
Xin dax tính tỷ trọng doanh thu
Trả lời
18
Đọc
754
SteveNguyen1991
S
F
  • Question Question
Tự động Merge Mail bằng VBA
Trả lời
6
Đọc
182
Hoàng Tuấn 868
H
N
Một ví dụ về XLL add-in: Tương tác với Google Gemini dưới dạng công thức bảng tính
    • Thích
Trả lời
0
Đọc
85
nguyendang95
N
H
Nhờ anh/chị giúp đỡ em viết công thức trừ dữ liệu theo FiFo
Trả lời
1
Đọc
96
HieuCD
H
BNTT
LockXLS - Chương trình bảo vệ an toàn cho các file bảng tính Excel (1 người xem)
    • Thích
4 5 6
Trả lời
118
Đọc
73K
YêuCN
Y
vsitcnh
Hỗ trợ công thức tính điểm dựa theo điều kiện. (1 người xem)
Trả lời
14
Đọc
193
thuytrangbp28
T
micam111
  • Question Question
Cách chạy nhiều đồng hồ đếm giờ trong VBA (1 người xem)
Trả lời
4
Đọc
690
thuytrangbp28
T
B
Xin giải pháp chống sửa, xóa dữ liệu đã nhập vào từ ngày hôm trước.
    • Thích
2
Trả lời
26
Đọc
7K
Tuấn Bụng Bự
Tuấn Bụng Bự
Emyeuexcel
  • Question Question
In tất cả các file trong cùng 1 thư mục
Trả lời
3
Đọc
207
Hoàng Tuấn 868
H
1
Hỏi về đánh dấu hàng ngang hàng dọc bằng màu khi kích chuột vào 1 ô
Trả lời
7
Đọc
200
Gà Công Nghệ
Gà Công Nghệ
PhanTuHuong
Sách Lập trình VBA trong AutoCAD đã có mặt
    • Thích
    • Yêu thích
2
Trả lời
20
Đọc
1K
Mr.hieudoanxd
Mr.hieudoanxd
P
Excell code vba : Tìm Cell để sửa dữ liệu (1 người xem)
    • Buồn
2
Trả lời
26
Đọc
578
Phạm Trường 80
P
D
Nhờ anh em giúp đỡ.
    • Buồn
Trả lời
2
Đọc
93
SA_DQ
SA_DQ
00029591
Cần hỗ trợ công thức tổng hợp dữ liệu không cần cột phụ Vlookup
Trả lời
2
Đọc
102
DeTong
D
Xem thêm…

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Đếm ngược thời gian

000
Ngày
00
Giờ
00
phút
00
giây
TALK SHOW: CÙNG NHẢY LAM BA ĐA VỚI HÀM LAMBDA (λ) EXCEL 365
Back
Top Bottom