Chuyển dữ liệu bảng 2 chiều thành 1 chiều! (1 người xem)

[pjsoga]

Em có 1 vùng dữ liệu 2 chiều cần chuyển về dạng 1 chiều như att file. Nếu dùng công thức chắc sẽ không làm được, vậy em nhờ các bác có thể dùng VBA chuyển giúp em được không ạ. Em xin cảm ơn!

 

[Ba Tê]

Em có 1 vùng dữ liệu 2 chiều cần chuyển về dạng 1 chiều như att file. Nếu dùng công thức chắc sẽ không làm được, vậy em nhờ các bác có thể dùng VBA chuyển giúp em được không ạ. Em xin cảm ơn!

Sub này cho dữ liệu và kết quả như trong file của bạn.
[GPECODE=vb]Public Sub GPE()
Dim sArr(), dArr(), I As Long, J As Long, K As Long, Rws As Long, Col As Long
With Sheet1
sArr = .Range("B2:F7").Value
Rws = UBound(sArr, 1)
Col = UBound(sArr, 2)
ReDim dArr(1 To Rws * Col, 1 To 3)
For I = 2 To Rws
For J = 2 To Col
If sArr(I, J) <> Empty Then
K = K + 1
dArr(K, 1) = sArr(I, 1)
dArr(K, 2) = sArr(1, J)
dArr(K, 3) = sArr(I, J)
End If
Next J
Next I
If K Then .Range("B15").Resize(K, 3) = dArr
End With
End Sub[/GPECODE]

 

[siwtom]

Em có 1 vùng dữ liệu 2 chiều cần chuyển về dạng 1 chiều như att file. Nếu dùng công thức chắc sẽ không làm được, vậy em nhờ các bác có thể dùng VBA chuyển giúp em được không ạ. Em xin cảm ơn!

Lập trình là đúng rồi.

Làm chơi thôi.

1. Chọn D15 hoặc ô bất kỳ ở dòng 15, và:
Đặt name data

$C$3:$F$7

Đặt name pos

=SMALL(IF(data="";"";100*(ROW(data)-2)+COLUMN(data)-2);ROWS($1:1))

2. Công thức cho D15

=INDEX(data;INT(pos/100);MOD(pos;100))

3. Công thức cho C15

=INDEX($C$2:$F$2;MOD(pos;100))

4. Công thức cho B15

=INDEX($B$3:$B$7;INT(pos/100))

Các công thức kéo xuống cho tới khi gặp lỗi. Nếu cần thì đổi các ký tự ";" trong công thức thành ","

 

[VetMini]

Nói chuyện ngoài lề một chút: cái này không hẳn là mảng 2 chiều -> 1 chiều.

Trong ngôn ngữ CSDL, đây là bài toán chuẩn hoá bậc 1 (1st order normailsation). Cách làm là tách rời các tầng của dòng (flatten table) để cho các dòng trở nên có độ dài đồng dạng, tức là số cột bằng nhau.

Trong bài này, các dòng a, b, c, ... không đồng dạng có số cột (1-Jan, 2-Jan, ...) không chuẩn với nhau. Người ta chuẩn hoá bằng cách tách thành nhiều dòng đồng có 3 cột dạng in hệt nhau.

 

[pjsoga]

Thank bác bate đã giúp em!
Thank bác Siwtom đã làm công thức giúp em, bản thân em thích dùng công thức hơn vì VBA em không biết. ^^
Thank bác VietMini vì đã giúp em hiểu dữ liệu em đang làm. Cái này chắc được dạy bài bản ở trường lớp IT. Em không học trên lớp nên giơf toàn đi mò. ^^

 

[pjsoga]

Bác Siwtom có thể giúp tiếp em vụ này không ạ?

 

[siwtom]

Bác Siwtom có thể giúp tiếp em vụ này không ạ?

1. Bạn có vấn đề thì hỏi tất cả mọi người nhé
2. Bạn nên miêu tả vấn đề. Ví dụ chưa chắc đã nói hết về dữ liệu. Bảng 2 bạn có a, b và sắp xếp. Thế nếu có
b x
a x
b y
a y
b z
a z

thì kết quả Bang tong vẫn thế?

Thế nếu Bang 1 có vd.
K d

nhưng cột 1 Bang 2 không có d thì Bang tong ra sao?

Miêu tả, miêu tả nữa, miêu tả mãi.

 

[pjsoga]

Em nghĩ bác hiểu được luôn ý em. ^^
Em chú thích như sau:
3 trường dữ liệu là khác nhau.
Bàng 1 Ví dụ: tương ứng giá trị L = a,
Bảng 2 tường ứng a = x, y, z ( 3 giá trị), (sắp xếp thế nào không quan trọng.)
Bảng tổng: tương ứng với bao nhiêu giá trị của a ở bảng 2 ta có bấy nhiêu giá trị L ở bảng tổng ( 3 giá trị), tương tự với các giá trị khác ở bảng 1.


Em kết bác vì công thức phía trên nên hỏi riêng ạ. ^^

 

[pjsoga]

Dear bác PTM 0412!
http://www.giaiphapexcel.com/forum/showthread.php?93112-Ghép-2-bảng-thành-1-bảng-với-1-khóa-chính
Em không trả lời được ở đề tài do vi phạm nội quy.
Vấn đề bác phân tích đúng ý em.
Bác có thể giúp em luôn dữ liệu như em đưa ra. (Nếu dữ liệu lớn hơn thì điều chỉnh lại địa chỉ trong code) Và dữ liệu nằm luôn tại vị trí trong cùng 1 sheet.
File này khác 1 chút ở bảng 2 có thêm 1 hoặc nhiều trường dữ liệu nữa.
Em cảm ơn bác!

 

[siwtom]

Em nghĩ bác hiểu được luôn ý em. ^^
Em chú thích như sau:
3 trường dữ liệu là khác nhau.
Bàng 1 Ví dụ: tương ứng giá trị L = a,
Bảng 2 tường ứng a = x, y, z ( 3 giá trị), (sắp xếp thế nào không quan trọng.)
Bảng tổng: tương ứng với bao nhiêu giá trị của a ở bảng 2 ta có bấy nhiêu giá trị L ở bảng tổng ( 3 giá trị), tương tự với các giá trị khác ở bảng 1.


Em kết bác vì công thức phía trên nên hỏi riêng ạ. ^^

Tôi không biết những bảng thật của bạn thế nào nhưng bạn định vị Bang tong "ở đó" thì có lẽ không được. Anh ptm0412 có hỏi mà bạn vẫn để ở B15 thì nếu dữ liệu thật của Bang 1 có > 8 dòng thì Bang 1 "đè" lên Bang tong?

Lúc trước Bang 2 có 2 cột, bây giờ có 3 cột (tập tin đính kèm trong bài #). Trong tương lai có 10, 20 cột? Dữ liệu là giả nhưng cấu trúc thì phải nói ra chứ.

Bảng 1 luôn có 2 cột?

 

[pjsoga]

- Vâng, dữ liệu của 2 có 2 cột thôi ( nhưng nếu có thêm cột nữa thì nó cũng liên kết với bàng 2 qua cột " Syb")
- Nếu dữ liệu của em nhiều quá em đặt xuống dưới nữa, hoặc đặt lệch cột sang bên phải thôi. Ví dụ từ: I:K

 

[siwtom]

- Vâng, dữ liệu của 2 có 2 cột thôi ( nhưng nếu có thêm cột nữa thì nó cũng liên kết với bàng 2 qua cột " Syb")
- Nếu dữ liệu của em nhiều quá em đặt xuống dưới nữa, hoặc đặt lệch cột sang bên phải thôi. Ví dụ từ: I:K

Tôi định vị Bang tong ở I5
----------
Một trong những hướng tối ưu code có thể là duyệt mang 2 (Bang 2) 1 lần và nhớ các chỉ số dòng có từ khóa (vd. có từ khóa "a": khi xét "L a" ta duyệt Bang 2 thi thấy từ khóa "a" có ở các dòng 1, 2, 3). Khi gặp lại từ khóa đã duyệt thì chỉ cần đọc ra các vị trí có từ khóa chứ không duyệt Bang 2 nữa (vd. khi xét "M a" thì đọc ra các chỉ số 1, 2, 3)

Tất nhiên nếu các từ khóa lặp lại nhiều trong cột 2 Bang 1 và nếu Bang 2 có độ lớn càng "khủng" thì có thể sẽ tiết kiệm được càng nhiều thời gian.

Nhưng tốc độ không phải là cái quan trọng nhất. Nếu phải bỏ thêm vài phần nghìn giây mà code đơn giản hơn thì nên dùng code đơn giản hơn.

 

[pjsoga]

Thành thật cảm ơn bác!
Nhưng đọc code em chả hiểu gì. Nếu là công thức chắc em hiểu mà modify được, code này nếu thêm dòng thêm cột là tắt ngóm. Em cố tìm hiểu bằng công thức thêm vậy.

 

[siwtom]

Thành thật cảm ơn bác!
Nhưng đọc code em chả hiểu gì. Nếu là công thức chắc em hiểu mà modify được, code này nếu thêm dòng thêm cột là tắt ngóm.

Thêm dòng không thể ngóm.
Thêm cột thì ngóm, tức kết quả vẫn chỉ có 3 cột. Chính vì thế mà tôi hỏi đi hỏi lại bạn cấu trúc thế nào, có bao nhiêu cột để viết cho thích hợp. Bạn cam kết là có 2 cột nên tôi viết theo kiểu "lười" cho 2 cột. Lỗi ở bạn thôi. Tôi không thể thống nhất đi thống nhất lại với bạn về cấu trúc dữ liệu của bạn được.

Em cố tìm hiểu bằng công thức thêm vậy.

Lựa chọn luôn thuộc về bạn. Không ai ép bạn.

 

[VetMini]

Tôi đã từng nhắc là vấn đề của bạn thuộc về cách chuẩn hoá CSDL.

Nếu là tôi thì chẳng cốt kiết gì cả. Tôi dùng phần mềm DBase (Access chẳng hạn) để import dữ liệu, dùng kỹ thuật UNPIVOT để đổi nhiều cột thành hàng, UNION chúng lại rồi export trở về Excel.

Nếu bạn cứ phải làm việc với loại dữ liệu thế này thì nên học Access cho kịp lúc. Nên nhớ rằng nếu cái gì cũng Excel được thì Microsoft bán mấy phần mềm khác cho ai?

 

[VMH0307]

Em cố tìm hiểu bằng công thức thêm vậy.

Nếu bạn muốn thử công thức thì thử xem sao nhé (làm chơi thôi, chứ code trong trường hợp này dễ hiểu và nhanh hơn cả)
Bạn đặt các Name sau:
Tạo các mảng dữ liệu ban đầu

ArrCode=($B$6:$B$9)

ArrSyb1=($C$6:$C$9)

ArrSyb2=($E$6:$E$11)

Arrtex=($F$6:$F$11)

Tạo "ma trận" dữ liệu

DataTab=ArrCode&TRANSPOSE(ArrSyb2&Arrtex)

Tạo các chỉ số hàng cột để lấy dữ liệu từ DataTab
Hàng

IDrow=ROWS(ArrSyb1)+1-LARGE((ArrSyb1=TRANSPOSE(ArrSyb2))*(ROWS(ArrSyb1)+1-ROW(INDIRECT("1:"&ROWS(ArrSyb1)))),ROW(INDIRECT("1:"&ROWS(ArrSyb1)*ROWS(ArrSyb2))))

Cột

IDcol=MOD(LARGE((ArrSyb1=TRANSPOSE(ArrSyb2))*(TRANSPOSE(ROW(INDIRECT("1:"&ROWS(ArrSyb2))))+(ROWS(ArrSyb1)-ROW(INDIRECT("1:"&ROWS(ArrSyb1))))*10^6),ROW(INDIRECT("1:"&ROWS(ArrSyb1)*ROWS(ArrSyb2)))),10)

Tại ô đầu tiên bên trái Bảng tổng dùng công thức sau:

=MID(INDEX(DataTab,INDEX(IDrow,ROW(1:1)),INDEX(IDCol,ROW(1:1))),COLUMN(A:A),1)

Enter, copy sang phải, xuống dưới đến khi lỗi thì thôi.
P/s: qua đó thấy dùng công thức tối mù mù hơn nhiều so với code.

 

[pjsoga]

Cám ơn các bác, bác tttmuahmuah em châm cứu cả buổi mà không hiều nổi. Em đành làm thêm 1 bảng trung gian rồi làm theo cách cũ của bác Siwtom

 

[siwtom]

Cám ơn các bác, bác tttmuahmuah em châm cứu cả buổi mà không hiều nổi. Em đành làm thêm 1 bảng trung gian rồi làm theo cách cũ của bác Siwtom

Cách cũ của tôi là cách nào vậy?
Nếu bạn nói ngay là Bang 1 và cả Bang 2 có số cột thay đổi thì tôi đã làm cho bạn rồi. Còn nếu bạn đã nói là cả 2 Bang đều có 2 cột thì do lười nên tôi viết cho 2 cột.

 

[pjsoga]

Cái chuyển mảng 2 chiều sang 1 chiều ý bác.
Bác xem trong att file nhé.

Bác lại qua giúp em vụ này nhé. ^^
http://www.giaiphapexcel.com/forum/showthread.php?93230-Dò-tìm-tổng-khoảng-giá-trị-lân-cận-liên-tục
Thank bác!

 

[siwtom]

Cái chuyển mảng 2 chiều sang 1 chiều ý bác.
Bác xem trong att file nhé.

Bác lại qua giúp em vụ này nhé. ^^
http://www.giaiphapexcel.com/forum/showthread.php?93230-Dò-tìm-tổng-khoảng-giá-trị-lân-cận-liên-tục
Thank bác!

Các cao thủ không giúp thì bạn xem tạm các công thức dưới đây. Tôi viết nhưng ngại nhất là tạo dữ liệu và test.
Tôi phủi tay, không đền bù nếu có lỗi.
---------
A. Yêu cầu:
1. Bảng 1 và Bảng 2 phải có dòng dữ liệu đầu tiên (dưới tiêu đề) trên cùng dòng, gọi là dòng START - ở tập tin ví dụ là dòng 6
2. Tôi dùng 2 cột phụ là A và B, vậy Bảng 1 bắt đầu từ cột C.
3. Bảng Tỗng có dòng dữ liệu đầu tiên ở dòng START

B. Chọn cell ở dòng START (vd. chọn L6 là ô sẽ nhập công thức) và đặt các Name

FirstCellBang1

=[COLOR=#ff0000]$C$6[/COLOR]

bang2

=[COLOR=#ff0000]$G$6:$I$11[/COLOR]

codeBang1

=[COLOR=#ff0000]$C$6:$D$9[/COLOR]

sybBang1

=[COLOR=#ff0000]$E$6:$E$9[/COLOR]

sybBang2

=[COLOR=#ff0000]$G$6:$G$11[/COLOR]

posBang1

=LOOKUP(ROW()-ROW(FirstCellBang1);[COLOR=#ff0000]$A$5:$A$8[/COLOR];ROW(sybBang1)-ROW(FirstCellBang1)+1)

posBang2

=SMALL(arr;ROW()-ROW(FirstCellBang1)+1-INDEX([COLOR=#ff0000]$A$5:$A$8[/COLOR];posBang1))

arr

=IF(sybBang2=INDEX(sybBang1;posBang1);ROW(sybBang2)-ROW(FirstCellBang1)+1;"")

cong thuc L6

=IF(COLUMN(A:A)>COLUMNS(codeBang1);INDEX(bang2;posBang2;COLUMN(A:A)-COLUMNS(codeBang1));INDEX(codeBang1;posBang1;COLUMN(A:A)))

Kéo sang phải cho đủ cột và kéo xuống tới khi gặp lỗi

Công thức cho cột phụ:
a. công thức cho B6

=COUNTIF(sybBang2;[COLOR=#ff0000]$E6[/COLOR])

Kéo xuống tới dòng cuối của bảng 1

b. Công thức cho A5

=SUM([COLOR=#ff0000]$B$5:$B5[/COLOR])

Kéo xuống
----------------
Tôi đặt nhiều name chủ ý để cho bạn dễ chỉnh sửa. Vì nếu không và dữ liệu của bạn được định vị ở chỗ khác so với trong tập tin ví dụ thì bạn sẽ phải chỉnh sửa nhiều trong các công thức trên sheet và trong name.
Khi vị trí dữ liệu thay đổi thì chỉ sửa những chỗ đỏ đỏ.