[ad_1]
Dữ liệu
Tôi lấy dữ liệu mẫu từ trang internet của Excel-dễ dàng. Đây là một trang internet hay chứa các hướng dẫn thân thiện với người mới bắt đầu về các chức năng cơ bản của Excel và VBA. Tập dữ liệu chứa các bản ghi về doanh số bán rau quả ở các quốc gia cụ thể trong năm 2016 và 2017. Tập dữ liệu chứa sáu trường: ID đơn đặt hàng, Sản phẩm, Danh mục, Số tiền, Ngày và Quốc gia. Tôi chia tập dữ liệu này thành 2 tệp csv có tên results1.csv Và results2.csv để tạo bảng trụ.
Tôi đã lấy tập dữ liệu này vào RawData bảng tính. Tôi đã tạo một phạm vi động được gọi là raw_data_source với công thức sau:
=OFFSET(RawData!$A$1, 0, 0, COUNTA(RawData!$A:$A), COUNTA(RawData!$1:$1))
Lý do tạo phạm vi động là kích thước (số hàng) của tập dữ liệu ở các tệp CSV khác nhau là khác nhau và tôi muốn sử dụng toàn bộ tập dữ liệu làm nguồn của bảng tổng hợp.
Bảng tổng hợp và biểu đồ
Bộ dữ liệu trong raw_data_source phạm vi trong RawData trang tính được sử dụng làm nguồn dữ liệu để tạo hai bảng và biểu đồ tổng hợp đơn giản.
Phần đầu tiên mô tả tổng doanh số bán rau quả theo từng sản phẩm có thể lọc theo quốc gia với cài đặt trường như được hiển thị.
Phần thứ hai mô tả tổng doanh số bán rau quả theo quốc gia.
1. Tự động làm mới các bảng tổng hợp dựa trên tập dữ liệu mới
Trong bước này, tôi muốn tự động hóa việc làm mới tập dữ liệu từ tệp tôi đã chỉ định. Tôi đã tạo một trình giữ chỗ trong Admin trang tính để đặt đường dẫn của dữ liệu CSV có tập dữ liệu mà tôi muốn lấy và làm mới các bảng tổng hợp. Tôi đặt tên cho ô A2 BẰNG filepath như hình dưới đây:
Mã cho bước này được đưa ra dưới đây trong UpdateRawData chương trình con. Tôi đã khai báo các biến wb, ws_admin, ws_rawdataVà filepath để biết tên của sổ làm việc, trang Quản trị, trang RawData và đường dẫn của tệp chứa tập dữ liệu tương ứng. Đầu tiên tôi xóa nội dung của bảng RawData. Sau đó, tôi đi đến sổ làm việc nguồn dựa trên tên tệp, chọn trang tính tương ứng, sao chép nội dung của nó, quay lại bảng tính ws_rawdata sheet và dán nội dung dưới dạng giá trị. Cuối cùng, tôi làm mới sổ làm việc bằng mã wb.RefreshAll đã làm mới toàn bộ sổ làm việc bao gồm các trang tính có bảng tổng hợp và biểu đồ tương ứng.
Sub UpdateRawData()Dim wb As Workbook
Dim ws_admin As Worksheet
Dim ws_rawdata As Worksheet
Dim filepath As String
Set wb = ThisWorkbook
Set ws_admin = wb.Worksheets(“Admin”)
Set ws_rawdata = wb.Worksheets(“RawData”)
'Clear Rawdata sheet
ws_rawdata.Activate
Cells.Clear
'get file path
filepath = ws_admin.Vary(“filepath”)
Utility.DisplayAlerts = False
'Open supply file and choose all contents
Dim src_wb As Workbook
Dim src_ws As Worksheet
Set src_wb = Workbooks.Open(filepath)
Set src_ws = src_wb.Sheets(1)
src_ws.UsedRange.Choose
'Copy all
Choice.Copy
'Paste all
ws_rawdata.Vary(“A1”).PasteSpecial xlPasteValues
'Shut supply file
src_wb.Shut SaveChanges:=False
wb.RefreshAll
Utility.DisplayAlerts = True
Finish Sub
Tôi đã liên kết chương trình con này với Làm mới tập tin và biểu đồ cái nút. Sau khi tôi chỉ định tên tệp trong trình giữ chỗ và nhấp vào nút, tập dữ liệu và bảng tổng hợp sẽ được cập nhật tự động.
2. Tạo bảng màu bằng VBA
Tôi đã tạo một bảng trong Admin trang chứa danh sách các loại trái cây và rau quả cụ thể có sẵn trong tập dữ liệu. Trong cột F, tôi đã chỉ định mã màu thập lục phân dự kiến cho màu của từng loại trái cây hoặc rau quả. Tôi muốn sử dụng những màu này để cập nhật màu trong biểu đồ trụ. Đầu tiên, tôi muốn tô cột F bằng màu tôi đã chỉ định trong mỗi ô.
Mã màu thập lục phân
Mã thập lục phân cho mỗi màu là số thập lục phân gồm 6 chữ số (0 đến 9 hoặc A đến F) với cơ số 16. Trong mã màu thập lục phân RRGGBB, mỗi cặp hai chữ cái đại diện cho các sắc thái khác nhau của màu đỏ, xanh lá cây và xanh lam. Trong hệ thống Đỏ Xanh Xanh (RGB), giá trị của mỗi sắc thái nằm trong khoảng từ 0 đến 255.
Ví dụ: đối với mã màu thập lục phân ffab23Tôi đã tính mã RGB tương ứng với phép tính sau. ffab23 trong hệ thập lục phân chuyển thành (255, 171, 35) trong hệ thống RGB tương ứng với các thành phần màu Đỏ, Xanh lục và Xanh lam.
Điều này cũng có thể được hình dung bằng cách đi tới tùy chọn Màu tùy chỉnh trong Excel như hiển thị bên dưới:
Trong Excel VBA, sử dụng &H kết hợp với một giá trị ngụ ý rằng đó là số thập lục phân và Val() hàm trả về số thập phân tương ứng. Trong Cửa sổ ngay bên dưới, r, gVà b biểu thị các giá trị thập phân tương ứng cho từng sắc thái của Đỏ, Xanh lục và Xanh lam tương ứng.
Trong đoạn mã bên dưới, tôi đã tạo một phạm vi được đặt tên color_code_range cho bảng chứa mã màu thập lục phân cho từng loại trái cây hoặc rau quả. Tôi lặp qua từng ô trong vùng chọn, lấy các thành phần màu đỏ, xanh lá cây và xanh lam theo số thập phân và sơn bên trong ô bằng cùng mã màu RGB.
Sub refresh_color_table()Dim wb As Workbook
Dim ws_admin As Worksheet
Set wb = ThisWorkbook
Set ws_admin = wb.Sheets(“Admin”)
ws_admin.Vary("color_code_range").Choose
Dim c As Vary
Dim r, g, b As Lengthy
Dim Hex As String
For Every c In Choice
Hex = c.Worth
r = Val("&H" & Mid(Hex, 1, 2))
g = Val("&H" & Mid(Hex, 3, 2))
b = Val("&H" & Mid(Hex, 5, 2))
c.Inside.Coloration = RGB(r, g, b)
Subsequent c
Finish Sub
Khi tôi chạy chương trình con trên, cột F được sơn cùng màu với mã màu như minh họa:
Nếu mã màu được thay đổi và mã được chạy lại, nó sẽ tạo ra các màu mới trong bảng. Điều đó không tuyệt vời sao?
3. Làm việc với từ điển trong VBA
Trong bước tiếp theo, tôi muốn chỉ định màu trong biểu đồ trụ dựa trên các màu tùy chỉnh mà tôi đã chọn ở trên. Với mục đích này, tôi đã tạo một từ điển chứa tên sản phẩm làm khóa và mã màu thập lục phân tương ứng làm giá trị.
Điều kiện tiên quyết để tạo đối tượng từ điển trong VBA là kích hoạt trước Microsoft Scripting Runtime. Để thực hiện việc này, bạn có thể đi tới Công cụ -> Tài liệu tham khảo -> Chọn hộp bên cạnh Microsoft Scripting Runtime và nhấp vào okay.
Trong đoạn mã bên dưới, tôi đã tạo một đối tượng từ điển có tên là colorMap. Tôi đã lặp qua Phạm vi E2:F10 trong Admin tờ giấy. Tôi đã thêm nội dung trong cột E làm khóa và nội dung trong cột F làm giá trị tương ứng của chúng.
Sub create_dict()Dim wb As Workbook
Dim ws_admin As Worksheet
Set wb = ThisWorkbook
Set ws_admin = wb.Sheets(“Admin”)
Dim colorMap As Dictionary
Set colorMap = New Dictionary
Dim i As Integer
For i = 2 To 10
If Not colorMap.Exists(Vary("E" & i).Worth) Then
colorMap.Add Vary("E" & i).Worth, Vary("F" & i).Worth
Finish If
Subsequent i
For Every Key In colorMap.Keys()
Debug.Print Key & ": " & colorMap(Key)
Subsequent Key
Finish Sub
Trong vòng lặp for thứ hai ở trên, tôi lặp qua từng khóa của từ điển colorMap và in các khóa và giá trị trong cửa sổ ngay lập tức như hiển thị bên dưới:
4. Quản lý các thành phần biểu đồ trục bằng VBA
Dựa trên những kiến thức đã học được từ các bước trước, tôi muốn tiến thêm một bước nữa và cập nhật các thành phần biểu đồ trụ bằng VBA. Trong trường hợp này, tôi muốn tự động đặt tiêu đề biểu đồ dựa trên giá trị ô và áp dụng màu sắc của các loại trái cây và rau quả cụ thể được xác định trong phần Admin trang tính vào các biểu đồ trục.
Ở bước này tôi đã chỉ định Plot1 Và Plot2 trang tính dưới dạng một mảng được gọi là sheetNames. tôi đã tuyên bố chartObj dưới dạng ChartObject. Trong mỗi trang tính, tôi lặp qua từng ChartObject trong số tất cả các ChartObject.
Ghi chú: Đối tượng biểu đồ đóng vai trò là nơi chứa đựng một Đồ thị đối tượng trong VBA kiểm soát kích thước và hình thức của biểu đồ được nhúng trong trang tính. Nó là thành viên của Đối tượng biểu đồ bộ sưu tập. Điều quan trọng là phải hiểu sự khác biệt trong các phương thức và thuộc tính của từng đối tượng này trong VBA.
Sau khi xem qua từng biểu đồObj, tôi đặt tiêu đề cho từng biểu đồ dựa trên giá trị trong ô E1. Tiếp theo, tôi lặp qua từng chuỗi trong Bộ sưu tập chuỗi tổng thể của đối tượng Biểu đồ. Tôi đã gán tên chuỗi (tức là tên trái cây hoặc rau quả) cho một biến có tên itemNamevà nhận được mã màu tương ứng từ colorMap từ điển. Tương tự như bước 2, tôi lấy các thành phần màu đỏ, xanh lá cây và xanh lam của mã màu ở dạng số thập phân và lấp đầy thanh chuỗi bằng các màu RGB.
Sub refresh_plots()Dim wb As Workbook
Dim ws_admin As Worksheet
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws_admin = wb.Sheets("Admin")
Dim colorMap
Set colorMap = CreateObject("Scripting.Dictionary")
Dim i As Integer
For i = 2 To 10
If Not colorMap.Exists(Vary("E" & i).Worth) Then
colorMap.Add Vary("E" & i).Worth, Vary("F" & i).Worth
Finish If
Subsequent i
Dim sheetNames As Variant
Dim sheetName As Variant
sheetNames = Array("Plot1", "Plot2")
Dim hex_color_code As String
Dim r, g, b As Integer
Dim chartObj As ChartObject
For Every sheetName In sheetNames
Set ws = wb.Sheets(sheetName)
For Every chartObj In ws.ChartObjects
chartObj.Chart.HasTitle = True
chartObj.Chart.ChartTitle.Textual content = ws.Vary("E1").Worth
For Every Collection In chartObj.Chart.SeriesCollection
itemName = Collection.Identify
hex_color_code = colorMap(itemName)
r = Val("&H" & Mid(hex_color_code, 1, 2))
g = Val("&H" & Mid(hex_color_code, 3, 2))
b = Val("&H" & Mid(hex_color_code, 5, 2))
Collection.Format.Fill.ForeColor.RGB = RGB(r, g, b)
Subsequent Collection
Subsequent chartObj
Subsequent sheetName
Finish Sub
Một minh họa về việc sử dụng mã này được mô tả dưới đây.
Phần kết luận
Trong bài đăng này, tôi đã minh họa cách người ta có thể tùy chỉnh và tự động hóa cách làm việc với các bảng và biểu đồ tổng hợp bằng VBA. Tôi đã chứng minh khả năng tự động hóa bốn nhiệm vụ chính: làm mới các bảng và biểu đồ tổng hợp bằng bộ dữ liệu mới; tạo bảng màu dựa trên mã màu thập lục phân; cách làm việc với từ điển trong VBA; và quản lý cũng như cập nhật các thành phần biểu đồ trục bằng VBA. Ở bước thứ hai, tôi đã trình bày chi tiết cách chuyển đổi mã màu thập lục phân sang mã màu RGB tương ứng bằng cả Excel và VBA, đồng thời sử dụng kỹ thuật này trong các bước tiếp theo.
Mã và tệp Excel cho bài đăng này có trong GitHub này kho. Cảm ơn bạn đã đọc!
[ad_2]
Source link