Làm thế nào để gom dữ liệu từ nhiều ô vào trong 1 ô (để làm bảng giá)

[BNTT]

Tôi có một vấn đề như sau, nghĩ mãi không ra, nhờ các bạn giúp dùm.

Tôi có một cái database giá các mặt hàng (mỗi mã hàng nằm trong một hàng) như sau:

Tôi muốn gom hết các mã hàng có cùng chủng loại, cùng Serie, cùng đơn giá vào chung trong một ô như sau:

Ý tôi là muốn làm ra kết quả giống như trong cột C.

Bảng giá này chỉ dùng để in thôi, gom hết mấy cái mã số có cùng chủng loại, cùng Serie, cùng đơn giá lại với nhau cho nó tiết kiệm được giấy, chứ nếu in nguyên cái Database để báo giá thì hao giấy quá!

Xin nói thêm, Danh mục hàng hóa (database) của tôi hiện đang có gần 80.000 mã số hàng khác nhau (và có thể còn thêm nữa, chắc cũng phải trên 100.000), cho nên làm bằng cách gì cũng được, dùng hàm, dùng cột phụ, dùng VBA... chi cũng được hết, miễn là đạt yêu cầu, và đừng làm treo máy (hic hic)...

 

[DOSNET]

Dạng bài này anh thử tìm lại xem, chính xác là bác SA_DQ có một bài làm bằng VBA rồi, nhưng tìm lại thì nhọc quá...

 

[phamduylong]

BNTT dùng cái này thử xem. Nhấn Ctrl+z để chạy macro

Mã số​

​

|

Serie​

​

|

Chủng loại​

​

|

ĐVT​

​

|

Đơn giá​

​

|

Kết quả >>​

​

|

Chủng loại​

​

|

Serie​

​

|

Mã số​

​

|

ĐVT​

​

|

Đơn giá​

​

|
D01-267|DREAM|Gạch 25x40|m2|

75.000​

| |Gạch 25x40|DREAM|D01-267, D01-268, D01-271, D01-273, D01-278, D01-285|

m2​

|

75000​

|
D01-268|DREAM|Gạch 25x40|m2|

75.000​

| |Gạch 25x40|DUNY|DU216|

m2​

|

75000​

|
D01-270|DREAM|Gạch 30x30|m2|

75.000​

| |Gạch 25x40|DUNY|DU211, DU212, DU213|

m2​

|

76000​

|
D01-271|DREAM|Gạch 25x40|m2|

75.000​

| |Gạch 25x40|PERFECT|PE01-290, PE01-291, PE01-292, PE01-293, PE01-294, PE01-295, PE01-296|

m2​

|

75000​

|
D01-273|DREAM|Gạch 25x40|m2|

75.000​

| |Gạch 25x40|PLATINO|PA919, PA921, PA922, PA923, PA925|

m2​

|

85000​

|
D01-277|DREAM|Gạch 40x40|m2|

75.000​

| |Gạch 25x40|S&T|ST2540-410, ST2540-411, ST2540-412, ST2540-416|

m2​

|

78000​

|
D01-278|DREAM|Gạch 25x40|m2|

75.000​

| |Gạch 25x40|S&T|ST2540-414|

m2​

|

81000​

|
D01-284|DREAM|Gạch 30x30|m2|

75.000​

| |Gạch 30x30|DREAM|D01-270, D01-284|

m2​

|

75000​

|
D01-285|DREAM|Gạch 25x40|m2|

75.000​

| |Gạch 30x30|DUNY|DU214|

m2​

|

75000​

|
DU211|DUNY|Gạch 25x40|m2|

76.000​

| |Gạch 30x30|PERFECT|PE01-297|

m2​

|

75000​

|
DU212|DUNY|Gạch 25x40|m2|

76.000​

| |Gạch 30x30|PLATINO|PA924|

m2​

|

85000​

|
DU213|DUNY|Gạch 25x40|m2|

76.000​

| |Gạch 30x30|S&T|ST2540-413|

m2​

|

78000​

|
DU214|DUNY|Gạch 30x30|m2|

75.000​

| |Gạch 40x40|DREAM|D01-277|

m2​

|

75000​

|
DU215|DUNY|Gạch 40x40|m2|

75.000​

| |Gạch 40x40|DUNY|DU215|

m2​

|

75000​

|
DU216|DUNY|Gạch 25x40|m2|

75.000​

| |Gạch 40x40|PLATINO|PA920|

m2​

|

85000​

|
PE01-290|PERFECT|Gạch 25x40|m2|

75.000​

| | | | | | |
PE01-291|PERFECT|Gạch 25x40|m2|

75.000​

| | | | | | |
PE01-292|PERFECT|Gạch 25x40|m2|

75.000​

| | | | | | |
PE01-293|PERFECT|Gạch 25x40|m2|

75.000​

| | | | | | |
PE01-294|PERFECT|Gạch 25x40|m2|

75.000​

| | | | | | |
PE01-295|PERFECT|Gạch 25x40|m2|

75.000​

| | | | | | |
PE01-296|PERFECT|Gạch 25x40|m2|

75.000​

| | | | | | |
PE01-297|PERFECT|Gạch 30x30|m2|

75.000​

| | | | | | |
PA919|PLATINO|Gạch 25x40|m2|

85.000​

| | | | | | |
PA920|PLATINO|Gạch 40x40|m2|

85.000​

| | | | | | |
PA921|PLATINO|Gạch 25x40|m2|

85.000​

| | | | | | |
PA922|PLATINO|Gạch 25x40|m2|

85.000​

| | | | | | |
PA923|PLATINO|Gạch 25x40|m2|

85.000​

| | | | | | |
PA924|PLATINO|Gạch 30x30|m2|

85.000​

| | | | | | |
PA925|PLATINO|Gạch 25x40|m2|

85.000​

| | | | | | |
ST2540-410|S&T|Gạch 25x40|m2|

78.000​

| | | | | | |
ST2540-411|S&T|Gạch 25x40|m2|

78.000​

| | | | | | |
ST2540-412|S&T|Gạch 25x40|m2|

78.000​

| | | | | | |
ST2540-413|S&T|Gạch 30x30|m2|

78.000​

| | | | | | |
ST2540-414|S&T|Gạch 25x40|m2|

81.000​

| | | | | | |
ST2540-415|S&T|Gạch 40x40|m2|

78.000​

| | | | | | |
ST2540-416|S&T|Gạch 25x40|m2|

78.000​

| | | | | | |

 

[rollover79]

Cách của bác Long về tốc độ em nghĩ có vẻ chưa ổn lắm. Không rõ bác BNTT test thử với dữ liệu thật chưa. Bác BNTT cho em hỏi thêm chút là cùng 1 Serie thì có thể có nhiểu Chủng loại được không? Trong ví dụ của bác thì 1 Serie em chỉ thấy có 1 Chủng loại.

 

[BNTT]

Cách của bác Long về tốc độ em nghĩ có vẻ chưa ổn lắm. Không rõ bác BNTT test thử với dữ liệu thật chưa. Bác BNTT cho em hỏi thêm chút là cùng 1 Serie thì có thể có nhiểu Chủng loại được không? Trong ví dụ của bác thì 1 Serie em chỉ thấy có 1 Chủng loại.

Một Serie có nhiều chủng loại chứ. Ví dụ: DREAM có 25x40 và 30x30... Bạn không thấy à?

Còn về tốc độ, tuy chưa nhanh lắm, nhưng với tôi như vậy là quá tốt rồi. Tôi vừa test bảng giá Gạch Đồng Tâm: 1740 mã hàng, chạy mất 55 giây. OK! Cảm ơn Thầy Long rất nhiều.

Nhưng thưa thầy Long, tánh em thì cứ hay là... được voi đòi tiên (chuyện này sư nương của em rành lắm). Em muốn có thêm đơn giá loại 2 được không thầy? Giá loại 2 này, có thứ có, có thứ không có. Em xin mạo muội gửi lên đây Bảng giá bán lẻ Gạch Đồng Tâm với 1740 mã hàng, đầy đủ giá loại 1 và loại 2 (nhiều nhiều một tí để test cho nó sướng...), nhờ Thầy giúp dùm em thêm một cột giá loại 2 (Serie nào có giá loại 2 thì điền vào, không có thì để trống).

Thêm một tí nữa. Hình như code của Thầy luôn luôn tạo bảng giá từ ô A1 của Sheet BangGia. Có thể cho em một cái tùy chọn ô bắt đầu tạo không? Ví dụ em đứng ở ô nào trước khi nhấn Crtl+Z, thì sẽ bắt đầu tạo bảng giá từ ô đó?

Và.. thêm một tí xíu nữa thôi, có cách nào để bắt buộc kết quả xuất ra (trong cột C: các mã số) phải xuống hàng nguyên cả cái mã số không? Tức là, những mã số nào đứng giữa hai dấu phẩy (hoặc là mã số cuối cùng), phải xuống hàng nguyên cả mã số nếu như không đủ chỗ... Hiện tại thì có nhiều mã số bị ngắt ra một nửa hàng trên, một nửa hàng dưới, không tiện lắm khi dò mã số tìm giá.

Cảm ơn Thầy trước.

------------------------------------------------------------------------
P/S: Bạn nào đang có nhu cầu làm nhà, mà muốn ốp lát gạch Đồng Tâm, có thể tham khảo giá bán lẻ ở trong file đính kèm tôi gửi theo đây. Đó là giá do nhà máy đưa ra. Còn dĩ nhiên giá tôi bán thì... (PR tí)... giảm cho thành viên GPE 10%!

 

[ndu96081631]

Góp ý về code:
- Với yêu cầu này, bạn BNTT chỉ cần bố trí lại dử liệu 1 tí là nhanh ngay:
- Đặt cột Chủng loại, Serie và Đơn giá nằm sát nhau
- Dùng Advanced Filter lọc Unique 3 cột này (chỉ chọn vùng lọc là 3 cột này thôi)
- Copy dử liệu lọc sang khác
Vậy là đã được 70% kết quả rồi ---> Khúc còn lại chẳng có gì là khó ---> Chứ mà For thì chậm lắm
Bạn thử xem ---> Tôi nghĩ dùng tay làm cũng được (chỉ có chổ "ghép chuổi" là cần tí code thôi)

 

[phamduylong]

Thêm một tí nữa. Hình như code của Thầy luôn luôn tạo bảng giá từ ô A1 của Sheet BangGia. Có thể cho em một cái tùy chọn ô bắt đầu tạo không? Ví dụ em đứng ở ô nào trước khi nhấn Crtl+Z, thì sẽ bắt đầu tạo bảng giá từ ô đó?

Macro mới cho phép tự chọn ô tạo bảng. Nhưng ô chọn phải thuộc sheet BangGia, nếu không sẽ báo lỗi: Chon 1 ô trong sheet BangGia !
Có thể chọn 1 ô đầu tiên làm bảng giá trong vùng A2:Z1000. Nếu chọn ngoài vùng đó sẽ báo lỗi Column < AA, 1 < Row < 1000
Sau khi tạo bảng xong: tự động định dạng, kẻ khung, bề rộng cột, bề cao dòng, ...
Chú ý:
- Macro xóa tất cả dữ liệu và định dạng trong vùng chữ nhật với ô đầu là ô chọn, dòng cuối là dòng cuối bảng, cột cuối là cột thứ 6 tính từ ô chọn.
- Macro lấy dữ liệu từ sheet Data và ghi vào các cột CWB (cột 101 > 106) nên nếu có dữ liệu trong các cột này sẽ bị mất.

Và.. thêm một tí xíu nữa thôi, có cách nào để bắt buộc kết quả xuất ra (trong cột C: các mã số) phải xuống hàng nguyên cả cái mã số không? Tức là, những mã số nào đứng giữa hai dấu phẩy (hoặc là mã số cuối cùng), phải xuống hàng nguyên cả mã số nếu như không đủ chỗ... Hiện tại thì có nhiều mã số bị ngắt ra một nửa hàng trên, một nửa hàng dưới, không tiện lắm khi dò mã số tìm giá.

Mã số bị ngắt xuống dòng do:
- Mã số có ký tự trắng (mã ASCII=32): thay ký tự trắng bằng khoảng trắng không ngắt dòng (ASCII=160)
- Mã số có ký tự - (ASCII=45): thay ký tự - bằng ký tự – không ngắt dòng (ASCII=150) nét gạch – dài hơn gạch -

 

[BNTT]

Ôi, cảm ơn Thầy rất nhiều. Macro này với em như vậy là quá tuyệt rồi. Hồi chiều em chạy cái macro cũ hết hơn 50 giây, mà sao cái mới này có 3 giây là đã xong rồi.

Dĩ nhiên là chưa hết "ý muốn" đâu ạ, em còn muốn có thêm vài cột nữa (ví dụ như số viên trong mỗi thùng, bởi vì có khi người ta muốn biết điều đó, hoặc nếu như không phải là gạch, thì có cái quy cách, chẳng hạn như bồn chứa nước, sẽ có chiều rộng, chiều dài, chiều cao, v.v...). Nhưng để em thiết kế lại cái database cho hợp lý hơn đã rồi em sẽ nhờ Thầy tiếp.

 

[BNTT]

Góp ý về code:
- Với yêu cầu này, bạn BNTT chỉ cần bố trí lại dử liệu 1 tí là nhanh ngay:
- Đặt cột Chủng loại, Serie và Đơn giá nằm sát nhau
- Dùng Advanced Filter lọc Unique 3 cột này (chỉ chọn vùng lọc là 3 cột này thôi)
- Copy dử liệu lọc sang khác
Vậy là đã được 70% kết quả rồi ---> Khúc còn lại chẳng có gì là khó ---> Chứ mà For thì chậm lắm
Bạn thử xem ---> Tôi nghĩ dùng tay làm cũng được (chỉ có chổ "ghép chuổi" là cần tí code thôi)

Trong database chính, có một form chuyên dùng để tra cứu nhanh giá của một mặt hàng cụ thể nào đó, có thể tìm theo chủng loại, tìm theo serie, hoặc tìm theo nhà sản xuất... em đã làm theo cách mà anh nói trên đây.

Cái em bí chính là cái chuyện "làm bằng tay" đó. Em đã từng dùng hàm CONCATENATE nhưng thấy nó mất thì giờ quá, nên mới đưa vấn đề lên đây. Và nói thật, cái macro của Thầy Long quá tuyệt, it là đối với riêng em.

 

[BNTT]

Có 2 vấn đề nho nhỏ, thầy Long ơi.

• Không thể dùng công thức trong Sheet Data.
  
  
• Số trong cả 2 đơn giá phải là số nguyên.

Nếu như trong Sheet Data có công thức hoặc có một đơn giá nào đó lẻ, thì Macro sẽ báo lỗi.

Có cách nào khắc phục được 2 chuyện này không Thầy? Ví dụ, cho phép dùng công thức và tự động làm tròn số của đơn giá (nếu thấy nó lẻ) ?

 

[ptm0412]

Tìm chỗ nào có cái này:

gia1 = Cells(2, 105)
gia2 = Cells(2, 106)

Thì thay bằng:

gia1 = Round(Cells(2, 105), 0)
gia2 = Round(Cells(2, 106), 0)

Tìm tiếp:

gia1 = Cells(r, 105)
gia2 = Cells(r, 106)

và thay bằng:

gia1 = Round(Cells(r, 105), 0)
gia2 = Round(Cells(r, 106), 0)

Đúng là!! nhõng nhẽo vua!

 

[rollover79]

Em xin góp thêm 1 code cho vui nhưng chưa có thời gian test, bác thử test lại giúp xem có vấn đề gì ko nhé.

 

[phamduylong]

Có 2 vấn đề nho nhỏ, thầy Long ơi.

• Không thể dùng công thức trong Sheet Data.
• Số trong cả 2 đơn giá phải là số nguyên.

Nếu như trong Sheet Data có công thức hoặc có một đơn giá nào đó lẻ, thì Macro sẽ báo lỗi.

Có cách nào khắc phục được 2 chuyện này không Thầy? Ví dụ, cho phép dùng công thức và tự động làm tròn số của đơn giá (nếu thấy nó lẻ) ?

Đã khắc phục các lỗi trên. BNTT kiểm tra lại.

 

[BNTT]

Cảm ơn Thầy. Thầy ơi, có chuyện này. Mở luôn cái file thầy gửi lên làm cho Thầy coi luôn nha.

Thầy sửa đơn giá 1 của nhóm hàng đầu tiên (serie 100x100CM, đang có 8 mã hàng) thành một cái giá mà có số lẻ (196,680.500 chẳng hạn). Xong nhấn chạy macro. Kết quả là: Serie 100x100CM sẽ bị tách ra làm 8 hàng riêng lẻ, mặc dù chúng có cùng Serie, cùng chủng loại, cùng đơn giá 1 và đơn giá 2 (lẽ ra chúng phải được gộp lại thành 1 nhóm).

Đó là cái dễ thấy nhất, nằm ngay trên đầu của Data. Em đã thử thêm vài Serie khác, hễ đơn giá mà có số lẻ là chúng đều bị tách riêng ra mỗi em một dòng hết.

Nhờ Thầy xem lại dùm em.

 

[ptm0412]

Chú nhỏ này, làm phiền Thầy Long quá đi. Nếu chấp nhận giá là số thập phân, chỉ cần khai báo biến lại:
gia1 As Double, gia2 As Double là xong chuyện.
Còn công thức, đâu phải là hông cho đánh công thức? Thực ra thì chính vì oánh công thức (chiết khấu 5%, 10%; hoặc lãi 5%, 3%) thì kết quả thường là thập phân. Chính cái thập phân mới gây nên chuyện.

Nếu không chịu số thập phân, thì hoặc là làm tròn sẵn bên Data, hoặc là sửa thêm dòng code này:

If Cells(r, 102) = serie And Cells(r, 103) = loai And Cells(r, 104) = dvi AndCells(r, 105) = gia1 Then

thành

If Cells(r, 102) = serie And Cells(r, 103) = loai And Cells(r, 104) = dvi And Round(Cells(r, 105), 0) = gia1 Then

 

[phamduylong]

Thầy sửa đơn giá 1 của nhóm hàng đầu tiên (serie 100x100CM, đang có 8 mã hàng) thành một cái giá mà có số lẻ (196,680.500 chẳng hạn). Xong nhấn chạy macro. Kết quả là: Serie 100x100CM sẽ bị tách ra làm 8 hàng riêng lẻ, mặc dù chúng có cùng Serie, cùng chủng loại, cùng đơn giá 1 và đơn giá 2 (lẽ ra chúng phải được gộp lại thành 1 nhóm).

Lỗi do câu này:

If Cells(r, 102) = serie And Cells(r, 103) = loai And Cells(r, 104) = dvi And Cells(r, 105) = gia1 Then

Chỉnh lại :

If Cells(r, 102) = serie And Cells(r, 103) = loai And Cells(r, 104) = dvi And Round(Cells(r, 105), 0) = gia1 Then

 

[BNTT]

Ôi, lão Cheettit này, cái chuyện code, thì em hỏng dám rờ vô (hông biết thì đứng mà dòm, mó tay mó chân nó hư tùm lum)... Còn chuyện sử dụng hàm là vầy:

Cái Database chính của em (gần 80.000 mã hàng) có nhiều cột lắm, không cần thiết phải copy hết sang Data để làm bảng giá. Mà copy từng cột riêng lẻ thì em... lười. Nên hồi trưa, với cái bản Macro thứ 2 mà Thầy Long gửi, em chỉ copy Mã số gạch thôi, rồi dùng VLOOKUP để lấy dữ liệu từ bên Database chính cho những cột còn lại trong Sheet Data... Xong, qua bên BangGia, nhấn chạy macro, nó hổng cho (lúc đó đơn giá chưa có số lẻ nghen), mà báo lỗi gì đó, tô mấy hàng màu vàng trong VB code... Nhưng nếu chịu khó copy trực tiếp từ bên Database chính sang, thì lại OK. Chính vì thế nên em mới nghĩ rằng cái Macro này hổng chơi với công thức.

Nhưng thôi, đó là chuyện cũ. Cái Thầy Long mới gửi lên tối nay đã cho chơi với VLOOKUP rồi.

 

[phamduylong]

Lỗi do dòng này :
If Cells(r, 102) = serie And Cells(r, 103) = loai And Cells(r, 104) = dvi And Cells(r, 105) = gia1 Then

Chỉnh lại

If Cells(r, 102) = serie And Cells(r, 103) = loai And Cells(r, 104) = dvi And Round(Cells(r, 105), 0) = gia1 Then

Đã chỉnh lại BangGiaDongTam1.zip trong bài 13

 

[ptm0412]

Nhưng thôi, đó là chuyện cũ. Cái Thầy Long mới gửi lên tối nay đã cho chơi với VLOOKUP rồi.

Thấy rồi em ơi, thuật toán là copy data ra 1 vùng tạm nằm ở cột 101, sort lại theo thứ tự, rồi xử trên vùng tạm này. Bài 2 của Thầy Long là copy và paste, công thức vlookup vẫn còn, thường lookup value ưa có uýnh $A2 để copy xuống cũng được, mà ngang qua cũng được:

=VLOOKUP($A2;'Data (2)'!$A$2:$F$1741;2;0)

Khi copy qua sheet banggia, nó vẫn còn nguyên là $A2, vlookup bị #N/A. Code xử hông được.

Sang bài sau thì Thầy Long sửa lại là Paste special - value, nên hết lỗi.