Tìm các dòng cuối của các cột và tính kết quả tổng cột Protfit, đếm số ngày cột Date

Nguyễn Hồng Quang

Thành viên mới
Xin chào tất cả các thành viên của tuhocvba.net thân mến!
Hôm nay tôi tiếp tục chia sẻ tới các bạn 1 kết quả trong công việc mà tôi đã hoàn thành dựa vào việc áp dụng thành công lý thuyết học được từ tuhocvba.net
Tôi tạo bài viết này với phần nhiều mong muốn là tạo được nguồn cảm hứng cho các bạn có nhu cầu tự học vba, cũng như là 1 tham khảo thêm cho phương pháp tư duy thực hành sử dụng code vba trong công việc.
Trước khi vào bài viết tôi xin chân thành cảm ơn các bạn tuhocvba;vbano1;Euler;bvtvba; giaiphapvba, bởi các bài viết chất lượng của các bạn đã cung cấp cho tôi những lý thuyết cơ bản gồm:
phần lý thuyến vòng lặp (for….to….) trên diễn đàn ;tôi tham khảo ở đây:

phần lý thuyến code tìm dòng cuối trên diễn đàn ;tôi tham khảo ở đây:

phần lý thuyến gán biến trên diễn đàn ;tôi tham khảo ở đây:
phần lý thuyết tìm cột theo số trên diễn đàn ;tôi tham khảo ở đây:
các video VBA02 Logic For Next; VBA08 find last row trên kênh youtube của tuhocvba

Nào Chúng ta cùng bắt đầu!!!
A/Bối cảnh , đặc điểm công việc thực tế của tôi:

Mỗi ngày , tôi có 1 bản dữ liệu (được kết xuất từ phần mềm của Công ty), sau khi được đồng nghiệp biến đổi, dữ liệu này được trình bày theo kiểu dạng dàn ngang trên 1 sheet, có 2 cặp tiêu đề lặp đi lặp lại ở tất cả các cột ( tiêu đề: day/month và tiêu đề: Profit), số lượng dòng ở mỗi cặp (2 cột) là không giống nhau lúc tăng lúc giảm, chi tiết như file ảnh dưới đây:
Bạn cần đăng nhập để thấy đa phương tiện
Và với kiểu trình bày dữ liệu nhận được từ đồng nghiệp này, tôi cần tạo ra kết quả Total ở sau dòng cuối của các cột Profit,và tạo kết quả đếm số lượng ngày ở sau dòng cuối của các cột Day/Month; tạo Format các kết quả dạng bôi đậm (bold = True). Dưới đây là file ảnh khi công việc hoàn thành:
Bạn cần đăng nhập để thấy đa phương tiện

B/Các hướng tư duy tổng quát
1/ Tư duy theo hướng người dùng sử dụng công thức (Sum, Count) trực tiếp trên bảng tính, kết hợp thao tác thủ công bàn phím và trỏ chuột.
Nếu theo hướng này, các bạn chắc hẳn cũng đã đoán ra các bước thực hiện rồi phải không nào? Quá đơn giản các bạn nhỉ? (…đưa trỏ chuột đên ô cuối của từng cột, nhập công thức Sum(…), count(…), Ctrl+B, cột tiếp theo và thao tác cho đến cột cuối cùng…)

2/Tư duy theo hướng áp dụng VBA để tạo kết quả trên bảng tính. Nếu theo hướng này, các bạn sẽ nảy sinh những suy nghĩ khái quát gì?
(về phía tôi, khi nhìn bảng tính này)
Điều thứ 1: tôi nhận thấy việc xuất hiện của các kết quả trong vùng sẽ có tính chất lặp, nếu làm thủ công tính chất lặp ở đây chính là các thao tác chuyển tới các cột kế tiếp để tính toán ; dựa vào tính chất lặp tôi nghĩ tới việc đưa vào code của mình vòng lặp (for….to….), phần lý thuyết vòng lặp (for….to….) trên diễn đàn các bạn có thể tham khảo ở đây
Điều thứ 2: tôi nghĩ đến điểm đầu và điểm cuối trên bảng tính (1 hình ảnh tư duy của tôi như sau).
Bạn cần đăng nhập để thấy đa phương tiện
Như trong hình ảnh trên, các bạn có nhận thấy rằng:
+Điêm đầu luôn bắt đầu từ số 1,
+ Điểm cuối là các dòng cuối (vậy là rõ ràng chúng ta cần áp dụng code tìm dòng cuối), phần lý thuyết code tìm dòng cuối trên diễn đàn các bạn có thể tham khảo ở đây. hoặc xem video VBA08 find last row trên kênh youtube của tuhocvba
+ Còn 1 điểm cuối quan trong hơn cả là cột cuối. (các bạn có thể vào google gõ tìm cột cuối), tương tự như lastrow nó có dạng phổ cập như sau:
Mã:
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
Tổng quát lại: chúng ta sử dụng VBA để có lần lượt các thông số của các điểm đầu cuối, và truy cập vào các vùng trên bảng tính dựa vào các thông số đó để tạo ra kết quả mong muốn

C/ Các bước thực hiện áp dụng VBA để tạo kết quả trên bảng tính
Bước 1 : Tìm các điểm cuối

Trước khi bắt đầu làm việc với VBA, Một câu hỏi thú vị cần trả lời là chúng ta cần áp dụng code để tìm ra dòng cuối trước hay tìm cột cuối trước? .
Các bạn có nhận thấy rằng nếu tìm dòng cuối trước thì rõ ràng là chúng ta sẽ cần có nhiều kết quả của các dòng cuối ứng với mỗi cột trong bảng tính ( ví dụ dòng cuối cột A, cột B, cột C….). Tuy nhiên để làm được việc này VBA cần phải có 1 điểm dừng. Nếu không có điểm dừng VBA sẽ tìm đến tận cột XFD chăng??? (cột cuối cùng của Excel 2010 đấy các bạn à).
Vậy là tôi sẽ đi tìm cột cuối của bảng tính trước các bạn nhé!
1.1 Thủ tục gán biến
Mã:
 Dim i as long, buf As String, lastrow As Long
    Dim LastCol As Intege
phần lý thuyết gán biến trên diễn đàn các bạn có thể tham khảo ở đây
1.2 Tìm cột cuối của bảng tính
Mã:
' Find cot cuoi cua bang tinh
    With ActiveSheet
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
Kết quả chạy code này là 12:
Bạn cần đăng nhập để thấy đa phương tiện
1.3 Tìm các dòng cuối của bảng tính
Trước khi vào CODE. Các bạn xem lại phần lý thuyết code tìm dòng cuối trên diễn đàn ở đây. ; hoặc xem VBA08 find last row trên kênh youtube của tuhocvba
Và với lý thuyết chúng ta dễ dàng tìm được 1 dòng cuối ở 1 cột được chỉ định ;ví dụ ta tìm ở cột L:
Mã:
lastrow=.cells(.rows.count,"L").End(xlUp).Row
Kết quả chạy code này là 20:
Bạn cần đăng nhập để thấy đa phương tiện
Tuy nhiên thực tế ở bảng này, chúng ta cần tới 12 kết quả của dòng cuối (từ dòng cuối cột A cho đến cột thứ 12 là cột L).
Có 1 cách dễ dàng là ta đặt ra 12 cái biến từ lr1,lr2,…lr12, và tạo ra 12 cái dòng code cho từng cột chỉ định từ cột A đến cột L. Nếu Làm cách này thì thà chúng ta làm thủ công còn nhanh hơn phải không các bạn?
Nhưng các bạn để ý; từ suy nghĩ giản đơn trên, chúng ta có được mấu chốt ở đây là thay vì đặt ra 12 cái biến và chạy 12 dòng code. Chúng ta đặt 1 biến và chạy 1 dòng code, khác biệt là dòng code này chạy lặp 12 lần. Vậy là rõ ràng ; chúng ta cần kết hợp với vòng lặp (for….to….) để tìm ra các dòng cuối
Trước khi vào CODE. Các bạn xem lại 1 lần nữa phần lý thuyết vòng lặp (for….to….) ở đây.
và xem video VBA02 Logic For Next trên kênh youtube của tuhocvba
ở đây có 2 cách kết hợp for...to...:
cách 1:

Mã:
For i = 1 to lastcol ‘ lastcol có kết quả = 12
lastrow = .Cells(Rows.Count, i).End(xlUp).Row ‘ Tìm dòng cuối chạy từ I đến lastcol
Do thời gian có hạn nên tôi không đi thêm bất cứ chi tiết nào cho cách 1, các bạn có thể tự nghiên cứu và phát triển thêm theo cách 1, rất mong nhận được chia sẻ từ các bạn
cách 2 (tôi ưu tiên thực hiện cách này vì tôi muốn áp dụng phần lý thuyết tìm cột theo số, mà tôi học được ở đây ):
Tôi xin tóm lược như sau: Trước khi dùng vòng lặp (for….to….); ta sử dụng 1 biến trung gian để chuyển đổi số của biến i sang Text. Ví dụ khi i = 1, ta chuyển đổi 1 thành
A, khi I = 2 thành B… khi i=12 thành L. Ta có Code của biến trung gian như sau
Mã:
 buf = Cells(1, i).Address(False, False) 'Buf dung de Chuyen doi Cot tu dang so sang dang text
buf = Left(buf, Len(buf) - 1) 'Buf dung de Chuyen doi Cot tu dang so sang dang text
Biến trung gian hoạt động rất tuyệt vời; chúng ta lắp biến này vào vòng lặp, code như sau:
Mã:
 ' Su dung vong lap for...to ket hop cac bien so LastCol, Lastrow
    For i = 1 To LastCol
    buf = Cells(1, i).Address(False, False) 'Buf dung de Chuyen doi Cot tu dang so sang dang text
    buf = Left(buf, Len(buf) - 1) 'Buf dung de Chuyen doi Cot tu dang so sang dang text
    ' Find dong cuoi theo tung Lastcol, luc nay Lastcol da duoc chuyen doi sang text thong qua Buf
    lastrow = .Cells(Rows.Count, buf).End(xlUp).Row
-------------------------------------
(Tôi Sẽ viết tiếp ở bài dưới)
 
Sửa lần cuối:

Nguyễn Hồng Quang

Thành viên mới
(Viết tiếp bài trên)
-----------------------------------
Bước 2 : Truy cập vào các dòng cuối và Thực hiện các phép tính, bôi đậm các kết quả tính toán
Bạn cần đăng nhập để thấy đa phương tiện
2.1 Thực thi lệnh tính tổng cột Profit và đếm số lượng ngày cột Date/Month
Như vậy là khi vòng lặp trên được thực hiện lần lượt từ i=1 to lastcol; chúng ta sẽ có kết quả của các thông số đầu cuối; việc Tiếp theo chúng ta áp dụng phương pháp truy cập Cells(row,col) kết hợp các thông số ấy để tạo ra kết quả mong muốn (điều này đã được tôi tổng quát ở phần đầu)
1 điểm cần lưu ý; Như ở bảng tính này ;trong 1 cặp tiêu đề luôn có 2 cột đó là cột (Date/Month) luôn là cột thứ 1(số 1 là số lẻ); cột (Profit) luôn là cột thứ 2 (số 2 là số chẵn) ;nên tôi nghĩ tới việc áp dụng hàm Mod để phân biệt cột chẵn; cột lẻ và từ đó sử dụng If …Else để tạo kết quả theo từng tiêu đề.
Mã:
 ' Tao cong thuc cho dong cuoi cho tung cot
If i Mod 2 = 0 Then
Cells(lastrow + 1, i) = "=""Total: "" & FIXED(SUM(" & buf & "2:" & buf & lastrow & "),0)" ' Tinh tong cac cot Profit
Else
Cells(lastrow + 1, i) = "=COUNT(" & buf & "2:" & buf & lastrow & ") &"" days""" 'Dem so luong ngay cac cot Date/Month
End If
Note***:Ở đây các bạn có thắc mắc là làm cách nào để đưa các hàm sẵn có trên bảng tính vào trong code VBA không???. Gợi ý, là khi làm đến các hàm này tôi đã sử dụng macro trên bảng tính. Như hình ảnh sau
Bạn cần đăng nhập để thấy đa phương tiện
Về phần cách viết công thức này, nếu có thời gian tôi sẽ nói kỹ thêm ở các bài sau (tuy nhiên nó có 1 vài quy tắc chung nếu các bạn để ý trong các công thức tôi đã viết các bạn sẽ nhận ra)
2.2/Bôi đậm các kết quả đã được tính toán
Bước Cuối cùng là truy cập vào các dòng cuối và bôi đậm các kết quả đã được tính toán
Mã:
 ' Thuc thi lenh boi den cac dong cuoi tu cot 1 den cot cuoi cung
Cells(lastrow + 1, i).Font.Bold = True
Code hoàn thành cho ta 1 bảng tính như sau:
Bạn cần đăng nhập để thấy đa phương tiện
Và đây là toàn bộ code và link file của tôi. Rất vui được chia sẻ cùng các bạn trên diễn đàn tuhocvba.net
Mã:
Sub Taoketqua()
Dim i as long, buf As String, lastrow As Long
Dim LastCol As Integer
' Find cot cuoi cua bang tinh
With ActiveSheet
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
' Su dung vong lap for...to ket hop cac bien so LastCol, Lastrow
For i = 1 To LastCol
buf = Cells(1, i).Address(False, False) 'Buf dung de Chuyen doi Cot tu dang so sang dang text
buf = Left(buf, Len(buf) - 1) 'Buf dung de Chuyen doi Cot tu dang so sang dang text
' Find dong cuoi theo tung Lastcol, luc nay Lastcol da duoc chuyen doi sang text thong qua Buf
lastrow = .Cells(Rows.Count, buf).End(xlUp).Row

' Tao cong thuc cho dong cuoi cho tung cot
If i Mod 2 = 0 Then
Cells(lastrow + 1, i) = "=""Total: "" & FIXED(SUM(" & buf & "2:" & buf & lastrow & "),0)"
Else
Cells(lastrow + 1, i) = "=COUNT(" & buf & "2:" & buf & lastrow & ") &"" days"""
End If
' Thuc thi lenh boi den cac dong cuoi tu cot 1 den cot cuoi cung (For i = 1 To LastCol)
Cells(lastrow + 1, i).Font.Bold = True
Next i
End With
MsgBox ("Finish!!!")
End Sub
Link tải file tại đây nhé các bạn:
Các tham khảo về lý thuyết tôi xin trích dẫn thêm 1 lần nữa ở phần cuối bài này, gồm :
+phần lý thuyến vòng lặp (for….to….) trên diễn đàn ;tôi tham khảo ở đây:
+phần lý thuyến code tìm dòng cuối trên diễn đàn ;tôi tham khảo ở đây:
+phần lý thuyến gán biến trên diễn đàn ;tôi tham khảo ở đây:
+phần lý thuyết tìm cột theo số trên diễn đàn ;tôi tham khảo ở đây:
+các video VBA02 Logic For Next; VBA08 find last row trên kênh youtube của tuhocvba
----------------------------------------***----------------------------------------------------------------
Tôi vẫn đọc được ở đâu đó, Có nhiều bạn than thở rằng tự học VBA là khô khan, là khó tưởng tượng, là cần đến các lớp này nọ, , tuy nhiên các bạn thấy đó từ lý thuyết đến áp dụng vào thực tiễn nó cũng thật là gần gũi phải không các bạn. Quan trọng là các bạn phải tạo thói quen trực tiếp gõ code nhiều (các code đơn giản), tạo ra 1 Folder thực hành riêng ; đọc code và liên tưởng vào các bảng tính để chạy code. Khi đã có những thói quen ấy, mình tin rằng các bạn sẽ có được tư duy cơ bản về code trong VBA như mình. Chúc các bạn tìm thấy niềm vui trong VBA và trong cuộc sống
 
Sửa lần cuối:

Nguyễn Hồng Quang

Thành viên mới
Chào bạn! Cảm ơn bạn đã quan tâm bài viết. Mình đã cập nhập lại Phần khai báo biến , đã bổ sung thêm khai báo biến i. Trong bài viết có phần nào bạn chưa hiểu hoặc cần làm rõ hơn không?
 

Binana

Thành viên mới
Chào bạn! Cảm ơn bạn đã quan tâm bài viết. Mình đã cập nhập lại Phần khai báo biến , đã bổ sung thêm khai báo biến i. Trong bài viết có phần nào bạn chưa hiểu hoặc cần làm rõ hơn không?
1 cách khác cho mọi người tham khảo. em có sửa qua đoạn code của anh 1 chút
Mã:
Sub Taoketqua()
Dim lastRow&, LastCol&, i&, WF As Object
Set WF = Application.WorksheetFunction
' Find cot cuoi cua bang tinh
With ActiveSheet
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
' Su dung vong lap for...to ket hop cac bien so LastCol, Lastrow
For i = 1 To LastCol
lastRow = .Cells(Rows.Count, i).End(xlUp).Row
If cells(1,i) <> "" then
If Cells(1, i) = "day/month" Then
Cells(lastRow + 1, i) = WF.Count(Range(Cells(2, i), Cells(lastRow, i))) & " days"
Else
Cells(lastRow + 1, i) = "Total: " & WF.Sum(Range(Cells(2, i), Cells(lastRow, i)))
End If
End If
' Thuc thi lenh boi den cac dong cuoi tu cot 1 den cot cuoi cung (For i = 1 To LastCol)
Cells(lastRow + 1, i).Font.Bold = True
Next i
End With
MsgBox ("Finish!!!")
End Sub
 

uesegi teppei

Thành viên mới
Rất cảm ơn anh Nguyễn Hồng Quang, đã chia sẻ kỹ năng thực hành VBA trong công việc. Em đã đọc 2 bài của anh , em thấy cách diễn giải của anh khá phù hợp và thú vị với những người mới tiếp cận VBA như em (đặc biệt em rất thích các câu hỏi anh gài vào trong các đoạn, có cảm giác rất gần gũi và gợi mở tư duy). Đối với bài viết này có 2 vấn đề nhỏ em rất mong nhận được sự giải đáp:
1/ em rất mong anh dành thêm chút thời gian để trình bày kỹ hơn phần code khi thực hiện tìm các dòng cuối theo cách 2 của anh. Em vẫn chưa thực sự hiểu cách vận hành của đoạn code này của anh:
Mã:
    For i = 1 To LastCol
    buf = Cells(1, i).Address(False, False)
    buf = Left(buf, Len(buf) - 1)
    lastrow = .Cells(Rows.Count, buf).End(xlUp).Row
2/ em có đọc bài của anh và của Binana thấy phần trình bày công thức tính kết quả trong bảng tính khi viết trong VBA có 2 trường phái
của anh là:
Mã:
Cells(lastrow + 1, i) = "=""Total: "" & FIXED(SUM(" & buf & "2:" & buf & lastrow & "),0)"
của Binana là:
Mã:
Cells(lastRow + 1, i) = "Total: " & WF.Sum(Range(Cells(2, i), Cells(lastRow, i)))
Nếu được , anh cho em hỏi là ngoài 2 cách trên còn có cách nào để viết, và khi nào áp dụng từng cách khi viết code.
Một lần nữa cảm ơn chia sẻ quý báu của anh; chúc anh sức khỏe và có thêm những bài viết bổ ích như vậy
Anh gửi cho em xin đường link để tải file này nữa nhé
 
Sửa lần cuối:

Binana

Thành viên mới
1/ em rất mong anh dành thêm chút thời gian để trình bày kỹ hơn phần code khi thực hiện tìm các dòng cuối theo cách 2 của anh. Em vẫn chưa thực sự hiểu cách vận hành của đoạn code này của anh:
Mã:
    For i = 1 To LastCol
    buf = Cells(1, i).Address(False, False)
    buf = Left(buf, Len(buf) - 1)
    lastrow = .Cells(Rows.Count, buf).End(xlUp).Row
Mạn phép cho mình trả lời câu số 1 của bạn. Do mình chỉ sửa code của bạn chủ toppic thôi.
Biến buf:
Mã:
    buf = Cells(1, i).Address(False, False)
    buf = Left(buf, Len(buf) - 1)
    lastrow = .Cells(Rows.Count, buf).End(xlUp).Row
Cái dòng: buf = Cells(1, i).Address(False, False) là trả về tên cột tại dòng 1 (A1,B1,C1....)
dòng:
buf = Left(buf, Len(buf) - 1) để đưa biến buf ở trên về tên các cột (A,B,C....)
Còn
lastrow = .Cells(Rows.Count, buf).End(xlUp).Row bạn cứ hiểu là nó đếm số dòng của excell xong đi ngược từ dưới lên. đến dòng nào có dữ liệu thì dòng đó là lastrow
Tiện thể mình thấy nếu dữ liệu cách cột ( tức là có cột trống) thì đoạn code của bạn Quang vẫn có dữ liệu trả vào đó khi chạy code
 
Sửa lần cuối:

Nguyễn Hồng Quang

Thành viên mới
Đối với bài viết này có 2 vấn đề nhỏ em rất mong nhận được sự giải đáp
Chào bạn Teppei (rất thú vị, tên của bạn trùng với tên 1 nhân vật truyện tranh ưa thích của mình). Mình đã đọc các vấn đề của bạn, hiện tại mình đang bận; mình sẽ dành thời gian để giải đáp bạn sau nhé. Về link tải file mình đã cập nhập trên bài viết #2 ( gần phần code đầy đủ)
 

Nguyễn Hồng Quang

Thành viên mới
1 cách khác cho mọi người tham khảo. em có sửa qua đoạn code của anh 1 chút
Chào bạn Binana, mình thực sự thích thú và đánh giá cao phần chia sẻ của bạn ở 2 điểm chính.
1/Bạn đã cung cấp thêm 1 cách khai báo biến.
Mã:
Dim lastRow&, LastCol&, i&
2/ Cách bạn vận dụng lý thuyết khai báo biến Object để diễn giải Function của excel trong VBA.
Mã:
Dim lastRow&, LastCol&, i&, WF As Object
Set WF = Application.WorksheetFunction
..................................
Cells(lastRow + 1, i) = WF.Count(Range(Cells(2, i), Cells(lastRow, i))) & " days"
Về Lý thuyết khai báo cho biến Object các bạn tham khảo trên diễn đàn bài #4 của tuhocvba ở đây
Rất vui được biết đến bạn Binana trên diễn đàn. Chúc bạn ngày vui
 

uesegi teppei

Thành viên mới
Về những thắc mắc của em ở #6, bạn Binana đã tận tình giải đáp. Và để nắm chắc vấn đề, Em có đọc lại bài viết của anh (phần C mục 1/3);
phần này em đã hiểu rõ cách vận hành và thuộc đoạn code tìm dòng cuối, em xin trình bày mức độ hiểu của em như trong hình dưới đây.
Bạn cần đăng nhập để thấy đa phương tiện
Có lẽ em và anh Quang cũng cùng làm việc trong lĩnh vực liên quan đến những con số và các bảng biểu, các vùng dữ liệu kết xuất từ phần mềm, nên em gần đây đã cố gắng dành chút thời gian ít ỏi trong ngày để bắt đầu học tập kiến thức VBA theo hướng anh chỉ dẫn ở bài viết.
Em thấy có hứng thú hơn với việc học VBA và rất cảm ơn anh; các bạn trên diễn đàn đã tạo cơ hội để em tiếp cận 1 cách thức mới cho giải quyết công việc. Về phần viết công thức trong VBA em cũng đang nghiên cứu và sẽ có lời hỏi anh sau ở mục này. Chúc anh ngày vui
 

Nguyễn Hồng Quang

Thành viên mới
Chúc mừng teppei. Hình ảnh tư duy của bạn mạch lạc và hoàn toàn chính xác. Rất vui khi bạn có hứng thú với việc tự học VBA. Mình tin Bạn sẽ còn gặp nhiều lợi ích hơn nữa về thời gian xử lý công việc khi bạn chịu khó áp dụng những gì đã học. Hẹn gặp lại bạn trên diễn đàn
 
Top