Một số cách sử dụng VBA để trực quan hóa dữ liệu trên trang tính Excel

Trong bài viết lần này, Gitiho sẽ hướng dẫn bạn cách “thêm chút gia vị” cho 1 bảng dữ liệu dài đằng đẵng và cách thêm “chút màu sắc” cho trang tính Excel khi in, mục đích là giúp trang tính của bạn trở nên dễ nhìn và trực quan hơn. Chúng ta cùng bắt đầu với:

Cách thêm đường viền bên dưới khi giá trị thay đổi trong cột Excel

Bạn có một danh sách dữ liệu “siêu to khổng lồ”, bao gồm các giá trị lặp lại; và để phân loại các giá trị tốt hơn thì việc thêm đường viền cho bảng dữ liệu là một cách đơn giản mà hiệu quả nhất, đặc biệt là với những bảng dữ liệu có giá trị thay đổi như ví dụ dưới đây:

Rõ ràng là không có hàm tích hợp nào trong Excel có thể giúp bạn thêm đường viền bên dưới khi giá trị thay đổi, nhưng với mã VBA dưới đây thì mọi chuyện trở nên đơn giản hơn nhiều:

1. Nhấn phím Alt + F11 để bật cửa sổ Microsoft Visual Basic for Applications.

2. Nhấp vào Insert > Module, và dán mã bên dưới vào Mô-đun tập lệnh trống.

VBA: thêm đường viền bên dưới khi giá trị thay đổi

1 Sub AddBorderLineWhenValueChanges()
2 'UpdatebyExtendoffice20160918
3       Application.ScreenUpdating = False
4       Dim LastRow As Long
5       Dim xrg As Range
6       LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
7       For Each xrg In Range("A2:A" & LastRow)
8              If xrg <> xrg.Offset(1, 0) Then
9                  Range("A" & xrg.Row & ":K" & xrg.Row).Borders(xlEdgeBottom).LineStyle = xlContinuous
10            End If
11     Next xrg
12     Application.ScreenUpdating = True
13 End Sub

3. Nhấn phím F5 để thực thi mã. Sau đó, các đường viền đã được thêm bên dưới khi giá trị thay đổi trong lựa chọn A2:A

Lưu ý: Trong mã VBA, A2:A là phạm vi bạn muốn thêm đường viền bên dưới khi giá trị của nó thay đổi, bạn có thể thay đổi nó khi cần.

Cách thêm màu nền vào đầu trang hoặc chân trang khi in trang tính Excel

Bạn có thể thêm nội dung hoặc hình ảnh vào đầu trang hoặc chân trang của trang tính để khi in ra trông đẹp mắt hơn; nhưng bạn đã bao giờ thử thêm màu nền cho đầu trang hoặc chân trang chưa? Trên thực tế, không có chức năng tích hợp nào có thể xử lý thao tác đó nó, nhưng có 2 cách dưới đây có thể giúp bạn thực hiện điều đó.

Bằng cách sử dụng Fill Color

1. Nhấp vào Page Layout > Page Setup.

2. Trong hộp thoại Page Setup, dưới tab Margins, hãy nhập 0 vào các hộp Top, Header, Bottom và Footer.

3. Bấm OK. Sau đó, chọn hàng đầu tiên và nhấp vào Home > Merge & Center.

4. Tiếp tục chọn một màu từ danh sách Fill color để thêm màu nền vào các ô đã hợp nhất.

5. Lặp lại bước 2 và 3 để thêm màu nền cho hàng chân trang mà bạn muốn.

Bằng cách sử dụng VBA

Cách trên phức tạp quá ư? Đừng lo, với đoạn mã VBA dưới đây, bạn có thể nhanh chóng thêm màu nền vào đầu trang và chân trang cùng một lúc. Các bước cụ thể:

1. Nhấn phím Alt + F11 để bật cửa sổ Microsoft Visual Basic for Applications.

2. Nhấp vào Chèn> Mô-đun, sao chép và dán mã bên dưới vào mô-đun mới.

VBA: Thêm màu nền vào đầu trang và chân trang    

1 Sub FakeHeaderFooter()
2 'UpdatebyExtendoffice20180628
3    Dim I, J As Long
4   Dim xRg As Range
5    Dim xRow, xCol As Long
6    Dim xRows, xCols As Long
7    Dim xDivRow, xDivCol As Long
8    Dim xTopArr, xButtArr As Variant
9    Dim PageSize1, PageSize2 As Integer
10    On Error Resume Next
11    xTopArr = Array("Top Left", "", "", "Top Center", "", "", "", "")
12    xButtArr = Array("Bottom Left", "", "", "Bottom Center", "", "", "", "")
13    PageSize1 = 46
14    PageSize2 = 8
15    With ActiveSheet.PageSetup
16        .TopMargin = Application.InchesToPoints(0)
17        .BottomMargin = Application.InchesToPoints(0)
18        .HeaderMargin = Application.InchesToPoints(0)
19        .FooterMargin = Application.InchesToPoints(0)
20        .BlackAndWhite = False
21    End With
22    Set xRg = ActiveSheet.UsedRange
23    If xRg Is Nothing Then Exit Sub
24    xRows = xRg(xRg.Count).Row
25    xCols = xRg(xRg.Count).Column
26    If xRows < 46 Then xRows = 46
27    If xCol < 8 Then xCol = 8
28    xDivRow = Int(xRows / PageSize1)
29    xDivCol = Int(xCols / PageSize2)
30    If ((xRows Mod PageSize1) > 0) And (xDivRow <> 0) Then xDivRow = xDivRow + 1
31    If ((xCols Mod PageSize2) > 0) And (xDivCol <> 0) Then xDivCol = xDivCol + 1
32    If xDivRow = 0 Then xDivRow = 1
33    If xDivCol = 0 Then xDivCol = 1
34    Set xRg = Range("A1").Resize(xDivRow * PageSize1, xDivCol * PageSize2)
35    xRow = 1
36    xCol = 1
37    Cells.PageBreak = xlPageBreakNone
38    For I = 1 To xDivRow * PageSize1 Step PageSize1 + 1
39        For J = 1 To xDivCol * PageSize2 Step PageSize2
40            Cells(I, J).Resize(1, PageSize2) = xTopArr
41            Cells(I, J).Resize(1, PageSize2).Interior.ColorIndex = 34
42            Cells(I + PageSize1, J).Resize(1, PageSize2) = xButtArr
43            Cells(I + PageSize1, J).Resize(1, PageSize2).Interior.ColorIndex = 34
44            Rows(I + PageSize1 + 1).PageBreak = xlManual
45            Columns(J + PageSize2).PageBreak = xlManual
46        Next
47    Next
48 End Sub

3. Nhấn phím F5 để chạy mã, sau đó màu nền nhất định đã được thêm vào hàng đầu tiên và hàng thứ 47 của mỗi trang tính.

Lưu ý: trong mã, bạn có thể thay đổi các tập lệnh bên dưới để đáp ứng nhu cầu của bạn; ví dụ như thay đổi nội dung đầu trang và chân trang khi bạn cần:

xTopArr = Array("Top Left", "", "", "Top Center", "", "", "", "")

xButtArr = Array("Bottom Left", "", "", "Bottom Center", "", "", "", "")

Hoặc là thay đổi chỉ số màu nền:

Cells(I, J).Resize(1, PageSize2).Interior.ColorIndex = 34

Cells(I + PageSize1, J).Resize(1, PageSize2).Interior.ColorIndex = 34

Hi vọng bài viết trên sẽ giúp ích cho quá trình làm việc cùng bảng biểu trong Microsoft Excel. Và như thường lệ, đừng quên tham khảo khóa học Tuyệt đỉnh VBA - Viết code trong tầm tay tại Gitiho nhé!