Tăng tốc tính toán

[trongdungs]

files excel này mới có bộ khung, chưa có dữ liệu mà chạy như rùa rồi. Các bác giúp em cải tiến công thức để tăng tốc độ tính toán lên với.


em xin cám ơn trước.

http://www.mediafire.com/file/yj5m502dj8x2e8u/Vinhloi.zip

đã gần 50 lượt khách ghé thăm mà chưa bác nào giúp được. vậy là không có cách nào thì phải. Buồn thật.

 

[SA_DQ]

Hoàn toàn đồng ý với bạn thôi!

Nhưng lý do bạn tách làm các workbook khác nhau là gì vậy bạn?

(Mình xin góp vài ý nhỏ với bạn:

(*) File của bạn xài, nên tên các trang tính không nên dùng các từ trùng lắp; Cụ thể từ 'BC_' trong các trang tính; Cũng không nên xài tiếng Việt có dấu trong tên các trang tính. Nếu không, khi dùng macro tác động vô trang tính nào, ta lại fải dùng tên cúng cơm của nó kia đấy!)

(*) Khi format màu để làm nổi rõ vấn đề, ta cũng không nên lèo 1 cái, format cả dòng màu vàng như vậy; Dữ liệu đến cột nào ta format đến cột đó thôi. Nhứt là file đang hoành tráng như của bạn. Điều quan trọng là tài nguyên đang bị giới hạn)

 

[trongdungs]

Cám ơn góp ý của bác, em sẽ sửa theo.

Nhưng lý do bạn tách làm các workbook khác nhau là gì vậy bạn?

việc tách workbook nhằm mục đích: trong huyện có 10 xã, 1 xã lại có 7 báo cáo, nếu để trong 1 workbook thì workbook đó có tới 70 worksheet báo cáo + worksheet dữ liệu. chưa kể có những huyện có 19 xã (như huyện Long Thành). Vậy em mới muốn tách 1 workbook chứa dữ liệu. 10 workbook chứa báo cáo xã, trong mỗi workbook báo cáo xã sẽ có 7 worksheet chứa 7 báo cáo của xã đó.

 

[SA_DQ]

Việc tách workbook nhằm mục đích: trong huyện có 10 xã, 1 xã lại có 7 báo cáo, nếu để trong 1 workbook thì workbook đó có tới 70 worksheet báo cáo + worksheet dữ liệu. chưa kể có những huyện có 19 xã (như huyện Long Thành). Vậy em mới muốn tách 1 workbook chứa dữ liệu. 10 workbook chứa báo cáo xã, trong mỗi workbook báo cáo xã sẽ có 7 worksheet chứa 7 báo cáo của xã đó.

Nếu có macro hỗ trợ thì ngoài 2 trang sẵn có, ta chi cần 7 trang cho B/C xã &
Công việc của macro sẽ lấy dữ liệu của xã mà ta yêu cầu nạp vô 7 B/c này.

Với B/C huyện cũng vậy;

Nếu cần thiết trích ra thành workBook thì cũng xong số liệu B/c cho xã hay huyện đó, rồi ta mới trích chép ra.

Lúc đó số liệu giữa các WorkBook không liên kết với nhau; Số liệu trích chép ra không còn 1 tẹo công thức nào.

Nhưng lúc nào cần B/c nào ta lại chạy macro để tạo B/c mới.

 

[SA_DQ]

Ta tiếp tục nha,

làm theo ý tưởng đó của bác cũng được.

Để làm B/C (báo cáo) lên trang có tên là Xa, mình đã lập DS (danh sách) duy nhất từ cột 'D' của 'CSDL'; DS này hiện có 9 hàng dữ liệu.
Mình làm 1 Validation tại [B1] của 'Xa' từ DS đã lập.
Macro sau đây sẽ tô nền màu lam cho tất cả các ô trên cột 'D' của trang 'CSDL'

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim Sh As Worksheet, Rng As Range, sRng As Range
 Dim MyAdd As String
 
 Set Sh = Sheets("CSDL")
 Set Rng = Sh.Range(Sh.[d6], Sh.[d65500].End(xlUp))
 Rng.Interior.ColorIndex = 0
 If Not Intersect(Target, [B1]) Is Nothing Then
   Set sRng = Rng.Find([B1].Value, , xlFormulas, xlWhole)
   If Not sRng Is Nothing Then
      MyAdd = sRng.Address
      Do
         sRng.Interior.ColorIndex = 35
         Set sRng = Rng.FindNext(sRng)
      Loop While Not sRng Is Nothing And sRng.Address <> MyAdd
   End If
 End If
End Sub

Khi chọn tại [B1] trang 'Xa' là X. Hưng Hội (cho giống với nội dung trong B/C hiện có. )
Tiếp theo mình tìm bên trang 'CSDL' trị đang có ở Xa![D4] mà không thấy;
Vậy bạn giúp giải thích xem số đó có từ đâu? (Hay nói cách khác, bạn chỉ cho mình cách mà bạn hình thành số liệu B/C trong các trang tính mà ta thỏa thuận gán tên Xa, 2010, 2011,. . . 2015.
(Hiểu được mình mới viết tiếp được) & chờ tin bạn đồng hương.

 

[trongdungs]

Bác xem file này. em làm hoàn thiện báo cáo xã hưng hội năm 2010 với đầy đủ số liệu. Cách thức lấy số liệu như thế nào, bác có thể xem trong công thức. em đã đặt tên cho công thức nên rất dễ hiểu. Bác có thể thử với xã khác, năm khác bằng cách thay giá trị vào ô B1, B2 trong worksheet HungHoi_2010.

Ủa mà sao bác biết quê em.

 

[ChanhTQ@]

Bạn chịu khó giải thích dùm cách tính của bạn ở bảng HungHoi10 giúp nha

Tại HH:

|C|D|E|F
10|||ĐNN|ĐLN
12|||PL1|PL2
15|DLN|25 604.65||24 583.15
16| LUN |0.00|| 14
17| HNK |93.38|| 15
18| CLN |678.58|| 16
19| RPH |0.00|| 17

Nhưng ở CSDL ta có số liệu như sau:

|C|...L|M|N|O|P|Q|R|S
4||NNP|DLN| LUN | HNK | CLN | RPH | RDD | RSX
7|PL2|..||12|13|14|15|16|17

Theo mình hiểu thì số liệu 2 bảng còn lệch nhau; hay mình hiểu chưa đúng chổ nào.
Rất mong được giải thích thêm chổ này & chân thành cảm ơn!

 

[trongdungs]

Ở đây có sự đảo cách bố trí hàng và cột giữa CSDL và báo cáo (HH10):
Bên HH10 các PL1, PL2, ....PL43 được xếp theo hàng ngang ở dòng 12 nhưng bên CSDL nó lại được bố trí ở cột B . và cột C bên HH10 được bố trí ở hàng 4 bên CSDL . PL1, PL2.. và DLN, LUN, HNK ......là các điều kiện để lấy dữ liệu. Số liệu 2 bảng hoàn toàn khớp nhau nên từ CSDL mới có kết quả ở HH10.

 

[SA_DQ]

Để tiện trong việc rút trích dữ liệu từ mà, xin đề nghị bạn việc sau

Các mã đều có độ dài như nhau; Ví dụ mã các loại đất nên là:
DGT,DTL, DNL, DVT, DVH, DYT, DGD, DTT, DNC, DXH, DCH, PNK, ONT, ODT, CSD, DTD, DBT, DDL, DNT ,. . . .
Cũng như vậy việc phân loại đất cũng nên là PL01,. . . . PL09, PL10,. . . .PL43

Điều này rất tiện cho việc tự động hóa bằng macro về sau. Rất mong bạn chấp thuận & chỉnh sửa;

Cách chỉnh cũng không mấy khó khăn nếu ta thuần thục lệnh Find and Replace với tùy chọn Match entire cell contents được ta đánh dấu. (Để chỉnh phân loại đất)

(Đây quả là kinh nghiệm xương máu qua hơn 1 buổi loay hoay vật lộn như thằng mù, khà, khà,. . . !)

 

[trongdungs]

em làm bác HYen17 vất vả quá. Việc đổi mã cho có độ dài giống nhau là hợp lý. em giơ 2 tay tán thành.

 

[SA_DQ]

Việc này fải trách bạn mới được:

Ai đời giả lập dữ liệu trùng 2 hay ba dòng bao giờ!

Ví dụ Loại PL14 của năm 2010 không bao giờ có 2 dòng trong trang CSDL, phải không? Vậy mà bạn chép đa số thành hai dòng; Thậm chí có cái còn 3 dòng nữa.
Với VLOOKUP() thì nó chỉ lấy anh chàng đầu tiên mà nó gặp; Còn trong VBA ta có thể lấy hết các số liệu trùng lặp này. Và như vậy dữ liệu chép đè lung tung. Làm mình mất thời gian nhiều mới fát hiện ra.

Đây macro của bạn đây, xin mời thử với tổ hợp fím tắc: {CTRL}+{SHIFT}+H

Option Explicit
[B]Sub hh10()[/B]
 Dim Cls As Range, Rng As Range, sRng As Range, Rng0 As Range
 Dim Sh As Worksheet, Jj As Byte, CotDL, CotBC As Byte
 Const Dat As String = "DHT PNN NNP":        Const PC As String = "@"
 Dim MyAdd As String, PLoai As String, MaDat As String
 
 Sheets("HH10").Select:          Set Sh = Worksheets("CSDL")
 Set Rng = Range([E12], [iv12].End(xlToLeft))
1 'Ghi PLoai Dat Vo Chuoi:'
 For Each Cls In Rng
   PLoai = PLoai & Cls.Value                [COLOR=RoyalBlue]          'PL##'[/COLOR]
 Next Cls
 PLoai = PLoai
2 'Ghi Ma Dát Vo Bién:'
 Set Rng = Sh.Range(Sh.[L4], Sh.[iv4].End(xlToLeft))
 For Each Cls In Rng
23   MaDat = MaDat & PC & Cls.Value              [COLOR=RoyalBlue]   'D##'[/COLOR]
 Next Cls
3 ' Tao Vòng Lap Duyet Hét Các Hàng Cua Trang B/C Theo Ma Dát:'
 Set Rng = Sh.Range(Sh.[d5], Sh.[d65500].End(xlUp))
 For Jj = 15 To [C65500].End(xlUp).Row
   Set sRng = Rng.Find([B1].Value)
   If Not sRng Is Nothing Then
      MyAdd = sRng.Address
      Do
         If Sh.Cells(sRng.Row, "K") = [B2].Value Then
            CotBC = InStr(PLoai, sRng.Offset(, -1).Value) \ 4
            CotDL = InStr(MaDat, Cells(Jj, "C").Value) \ 4
            
            Cells(Jj, "E").Offset(, CotBC).Value = _
                  Sh.Cells(sRng.Row, "L").Offset(, CotDL).Value
         
         End If
         Set sRng = Rng.FindNext(sRng)
      Loop While Not sRng Is Nothing And sRng.Address <> MyAdd
   End If
 Next Jj
[B]End Sub[/B]

Bạn bỏ công dò lại giúp nha, theo file đính kèm.

 

[trongdungs]

Ai đời giả lập dữ liệu trùng 2 hay ba dòng bao giờ!

Ví dụ Loại PL14 của năm 2010 không bao giờ có 2 dòng trong trang CSDL, phải không? Vậy mà bạn chép đa số thành hai dòng; Thậm chí có cái còn 3 dòng nữa.

Hì hì, chỗ này là do bác chưa hiểu PL14 nói riêng hay PL nói chung sẽ lặp lại rất nhiều lần ở CSDL thế nên cơ sở dữ liệu mới lên hàng ngàn dòng.

ví dụ :
Trạm cấp nước sạch xây mới lấy từ đất giáo dục đất lúa, đất trồng cây lâu năm
nhà máy nước xây mới trên đất ở đất trồng cây lâu năm
đào mới kênh T trên đất lúa.
trạm cấp nước + nhà máy nước + kênh đều thuộc nhóm PL12
bác xem file đính kèm sẽ rõ hơn.

cám ơn bác HYyen17. Bây giờ em phải nghiên cứu thành phẩm này đã.

 

[SA_DQ]

Trong macro mình còn sơ sót 1 chổ quan trọng; Nó sẽ làm tính toán bị sai lệch

Chổ sai là dòng lệnh mình vừa đánh số 23 & đã sữ;

Bạn giúp mình sửa trong file trước khi chạy lại thử nghiêm nha.

(Lỗi do coi 2 mã có độ dài cùng bằng 4; Nhưng thực ra độ dài mã sau có 3 hà!)

 

[trongdungs]

Chổ sai là dòng lệnh mình vừa đánh số 23 & đã sữ;

có phải là dòng này không bác
23 MaDat = MaDat & PC & Cls.Value 'D##'

chỗ này phải sửa thành thế nào, em chưa hiểu. bác chỉ cho em luôn đi.

Kết quả tính toán đang bị sai có phải do đoạn này không bác

1 'Ghi PLoai Dat Vo Chuoi:'
For Each Cls In Rng
PLoai = PLoai & Cls.Value 'PL##'
Next Cls
PLoai = PLoai
2 'Ghi Ma Dát Vo Bién:'
Set Rng = Sh.Range(Sh.[L4], Sh.[iv4].End(xlToLeft))
For Each Cls In Rng
23 MaDat = MaDat & PC & Cls.Value 'D##'
Next Cls

 

[SA_DQ]

Theo như bạn nói có thể có nhiều dòng cùng PL02, PL013,. . . như vậy fải cộng dồn; Nhưng macro đang chưa cộng dồn mà là lấy duy nhất dòng cuối của mã nào đó có trùng lắp.

Chúng ta fải kiện toàn lại trang CSDL tinh tươm rồi tiếp.

Hiện tại trong trang CSDL này chỉ cần 1 hay 2 xã & có các năm 2010,2011 hoặc thêm 2012 nữa là OK.
Nếu rảnh rổi làm giúp mình cái này nha! Cảm ơn trước.

Bữa nào fải xuống Bạc liêu nhậu mới được, khà, khà,. . .
​

Bạn có thể xem thông tin tất cả các xã hay bất cứ năm nào chỉ cần 1 hay 2 lần nhấp chuột tại [B1] hay [B2]
của trang 'BCXa'

Thử vài lần & cho biết ý kiến để chỉnh sửa theo ý nha!

Chúc vui!

 

[trongdungs]

Em đã test rồi và thấy:
- Kết quả của những ô màu trắng là chính xác
- Những ô mà có màu nền (xanh và vàng) thì kết quả chưa đúng do những ô này sử dụng cách tính khác (hơi rắc rối) với những ô không có màu nền. Do đó bác HYen17 có thể cho marco loại bỏ những ô này ra không tính toán mà để em nhập bằng tay công thức cho những ô này thì tốt quá.
- Còn vấn đề tách các báo cáo của các xã thành từng file riêng biệt có khả thi không bác?

 

[SA_DQ]

Vấn đề (1) - giải ngay dưới đây, vấn đề (2) cần thảo luận thêm:

(1) Bạn sửa lại dòng lệnh trong macro như hướng dẫn dưới đây:

 [COLOR=YellowGreen]           With Cells(Jj, "E").Offset(, CotBC)[/COLOR]
               [B]If .Interior.ColorIndex < 9 Then .Value = .Value _
                  + Sh.Cells(sRng.Row, "L").Offset(, CotDL).Value[/B]
 [COLOR=YellowGreen]           End With
         End If
         Set sRng = Rng.FindNext(sRng)
      Loop While Not sRng Is Nothing And sRng.Address <> MyAdd
   End If
 Next Jj[/COLOR]
[COLOR=SeaGreen][B]End Sub[/B][/COLOR]

(2) Tách B/C (báo cáo các xã) có khả thi chứ; Vì giờ còn mấy công thức nữa đâu, ngoài các mảnh đất màu mỡ của bạn!
Hình như điều này có thể nhờ macro giúp. Nhưng luật bất thành văn của mình là không làm giúp ai những macro đại loại như vậy; Lý do ư: Dễ có chuyện làm ơn mang oán lắm, vì hư dữ liệu đích khi thao tác bất cản, khì, khì,. . . . (Thông cảm cho mình nha & bạn có thể nhờ người khác trong cộng đồng, hay tham khảo các bài viết đã có về việc này)

 

[trongdungs]

ok. cảm ơn bác nhiều. Bây giờ em tiếp tục cuộc hành trình. Thời gian để đến đích cũng khá lâu đây. hic hic

chủ đề này chắc close tại đây!

 

[ChanhTQ@]

chủ đề này chắc close tại đây!

Vẫn còn tiếc là chưa xem được form báo cáo huyện của bạn, như bạn đã đề cập trong bài nào trước đây của topic.

Chúc vui vẽ & thành công!