Đôi khi ta muốn chuyển dữ liệu từ bảng 2 chiều thành 1 cột duy nhất. Có thể dùng công thức để làm chuyện này:
Giả sử ta có dữ liệu như sau (5 dòng 4 cột):
|
Tại 1 ô bất kỳ ta dùng công thức:
=INDEX($A$1:$D$5;INT((ROW(1:1)-1)/4)+1;MOD(ROW(1:1)-1;4)+1)
và fill xuống đến khi nào đủ thì ngưng. Trong đó số 4 là số cột.
Tuy nhiên, nếu dữ liệu có ô trống như sau:
|
Thì kết quả sẽ có những ô có giá trị 0. Và đó là điều ta không muốn.
Ta có thể dùng VBA với 1 chút thủ thuật như sau:
- Dựa vào tính chất của hàm Index (của Excel): Nếu để trống tham số dòng sẽ lấy nguyên cột, nếu để trống tham số cột lấy nguyên dòng.
- Sử dụng hàm Filter của VBA loại bỏ ô trống.
1. Nếu muốn sắp theo thứ tự từng dòng:
2. Nếu muốn sắp theo thứ tự từng cột:
Kết quả:
Test1|Test2|
a1|a1|
a2|b1|
a3|d1|
b1|a2|
b2|b2|
b4|c2|
c2|d2|
c3|e2|
d1|a3|
d2|c3|
d4|e3|
e2|b4|
e3|d4|
Với dữ liệu nhiều dòng, dùng Dict có thể nhanh hơn, nhưng tôi muốn giới thiệu 1 thuật toán khác lạ nhưng đơn giản (sưu tầm & chế biến) chạy bằng mảng Array thông thường.
Giả sử ta có dữ liệu như sau (5 dòng 4 cột):
A
|
B
|
C
|
D
|1
|
a1
|
a2
|
a3
|
a4
|2
|b1|b2|b3|b4|3
|c1|c2|c3|c4|4
|d1|d2|d3|d4|5
|e1|e2|e3|e4|Tại 1 ô bất kỳ ta dùng công thức:
=INDEX($A$1:$D$5;INT((ROW(1:1)-1)/4)+1;MOD(ROW(1:1)-1;4)+1)
và fill xuống đến khi nào đủ thì ngưng. Trong đó số 4 là số cột.
Tuy nhiên, nếu dữ liệu có ô trống như sau:
A
|
B
|
C
|
D
|1
|
a1
|
a2
|
a3
||2
|b1|b2| |b4|3
| |c2|c3| |4
|d1|d2| |d4|5
| |e2|e3| |Thì kết quả sẽ có những ô có giá trị 0. Và đó là điều ta không muốn.
Ta có thể dùng VBA với 1 chút thủ thuật như sau:
- Dựa vào tính chất của hàm Index (của Excel): Nếu để trống tham số dòng sẽ lấy nguyên cột, nếu để trống tham số cột lấy nguyên dòng.
- Sử dụng hàm Filter của VBA loại bỏ ô trống.
1. Nếu muốn sắp theo thứ tự từng dòng:
PHP:
Sub test1()
Dim i As Long, SourceArr, tmp, ResultArr
On Error GoTo Goback
[j1:j20].ClearContents
With Range("A1:D5")
.SpecialCells(4).Value = "~"
SourceArr = .Value
For i = 1 To UBound(SourceArr, 1)
tmp = tmp & "|" & Join(Application.Index(SourceArr, i), "|")
Next
ResultArr = Filter(Split(Mid(tmp, 2), "|"), "~", False)
Cells(1, 10).Resize(UBound(ResultArr) + 1) = Application.Transpose(ResultArr)
Goback:
.Replace what:="~~", replacement:=""
End With
End Sub
2. Nếu muốn sắp theo thứ tự từng cột:
PHP:
Sub test2()
Dim i As Long, SourceArr, tmp, ResultArr
On Error GoTo Goback
[j1:j20].ClearContents
With Range("A1:D5")
.SpecialCells(4).Value = "~"
SourceArr = .Value
For i = 1 To UBound(SourceArr, 2)
tmp = tmp & "|" & Join(Application.Transpose(Application.Index(SourceArr, , i)), "|")
Next
ResultArr = Filter(Split(Mid(tmp, 2), "|"), "~", False)
Cells(1, 10).Resize(UBound(ResultArr) + 1) = Application.Transpose(ResultArr)
Goback:
.Replace what:="~~", replacement:=""
End With
End Sub
Kết quả:
a1|a1|
a2|b1|
a3|d1|
b1|a2|
b2|b2|
b4|c2|
c2|d2|
c3|e2|
d1|a3|
d2|c3|
d4|e3|
e2|b4|
e3|d4|
Với dữ liệu nhiều dòng, dùng Dict có thể nhanh hơn, nhưng tôi muốn giới thiệu 1 thuật toán khác lạ nhưng đơn giản (sưu tầm & chế biến) chạy bằng mảng Array thông thường.
File đính kèm
Lần chỉnh sửa cuối: