Hướng dẫn: Làm thế nào để tạo 1 công thức cho nhiều Sheet?

[uocmo_kchodoi]

Xin chào các bạn, chắc hẳn những bạn thường xuyên sử dụng Excel đã ít nhiều gặp trường hợp có 1 file Excel với rất nhiều Sheet khác nhau nhưng cấu trúc phần tiêu đề giống hệt nhau (cấu trúc giống nhau), chỉ khác nhau ở số lượng dòng dữ liệu trên mỗi sheet. Nếu phải làm từng Sheet một sẽ rất mất thời gian phải không? Mình sẽ hướng dẫn các bạn cách đặt công thức và chạy công thức cho tất cả các sheet này (đương nhiên công thức cho các cột ở các sheet khác nhau cũng cùng một cấu trúc). Vì công thức chạy bằng VBA nên nếu bạn chưa biết cách sử dụng VBA bạn có thể xem tại link này nhé https://vnkienthuc.com/vba-la-gi-hu...-va-cach-them-cac-add-ins-trong-excel.t77197/

Mình lấy ví dụ cho dễ hiểu nhé. Giả sử mình có file Excel là bảng điểm của 3 lớp với mỗi sheet là 1 lớp (đây là ví dụ mang tính chất tham khảo chứ bình thường không ai bố trí dữ liệu như thế này các bạn ạ). Trong đó cấu trúc của các sheet là giống hệt nhau, chỉ khác nhau ở dữ liệu các dòng và số lượng các dòng (các lớp khác nhau sẽ có số lượng học viên khác nhau).

Sheet LopA có 5 học viên như hình dưới

Sheet LopB có 4 học viên như hình dưới

Sheet LopC có 10 học viên như hình dưới

Và Sheet LopD có 5 học viên như hình dưới

Với chỉ 5 sheet này, để tính điểm trung bình, với mỗi sheet tại ô H6 các bạn chỉ cần nhập công thức ở dòng dữ liệu đầu tiên, sau đó kéo xuống hết dữ liệu sẽ ra kết quả. Tuy nhiên đây chỉ là một ví dụ 5 sheet, giả sử file excel không phải có 5 sheet mà có tới hàng trăm sheet thì các bạn làm như thế nào?

Với bạn nào biết cách nhập nhanh dữ liệu đồng thời cho nhiều Sheet, các bạn sẽ thực hiện gộp tất cả các sheet thành 1 nhóm (group – click chọn tên sheet đầu tiên, giữ phím Shift rồi click chọn tên sheet cuối cùng) rồi nhập công thức vào ô H6, lúc này tất cả ô H6 của tất cả các sheet sẽ được nhập công thức tính điểm trung bình.

Nhưng lúc này, làm thế nào để điền đầy công thức của các dòng tiếp theo trong các sheet? Nếu vẫn đang Group các sheet mà kéo công thức tại Sheet LopA thì điều gì sẽ xảy ra? Lúc này xảy ra 3 trường hợp

• Trường hợp thứ nhất, các sheet có số lượng dữ liệu (bản ghi) ít hơn so với Sheet LopA (trong ví dụ này sẽ là Sheet LopB), sẽ xuất hiện các dòng chứa công thức thừa (dòng xuất hiện lỗi #DIV/0!) như hình dưới

• Trường hợp thứ 2 là Sheet có số dòng dữ liệu (bản ghi) nhiều hơn so với số dòng dữ liệu của Sheet LopA (Trong ví dụ này là Sheet LopC), các dòng có chỉ số lớn hơn chỉ số dòng lớn nhất của Sheet LopA sẽ không được điền công thức như hình dưới

• Và trường hợp cuối cùng, các sheet có số dòng dữ liệu (bản ghi) bằng số dòng dữ liệu của Sheet LopA sẽ được điền công thức đầy đủ như hình dưới

Như vậy với cách Group các sheet lại với nhau, các bạn chỉ thực hiện được khi tất cả các sheet có số lượng dòng dữ liệu (bản ghi) bằng nhau.

Để thực hiện được một cách nhanh chóng và áp dụng cho tất cả các trường hợp sheet có số lượng dòng dữ liệu dài ngắn khác nhau, các bạn làm như sau

Mở cửa sổ VBA và nhập vào đoạn mã sau. (Lưu ý: Tốt nhất trước khi chạy đoạn mã này, các bạn tắt hết các file Excel khác, chỉ để lại file Excel này thôi nhé)

Sub FILL_PASTE_DELETE()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Range("H1:H" & sh.Cells(sh.Rows.Count, "A").End(xlUp).Row).Formula = "=AVERAGE(E6:G6)"
Next sh
End Sub

Và đây là kết quả

Vậy là dù số dòng dữ liệu trên các sheet nhiều ít khác nhau thì công thức vẫn được điền đầy đủ cho tất cả các dòng dữ liệu trên tất cả các sheet.

Giờ mình sẽ phân tích đoạn mã trên để các bạn nắm được nhé

Sub FILL_PASTE_DELETE()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Range("H1:H" & sh.Cells(sh.Rows.Count, "A").End(xlUp).Row).Formula = "=AVERAGE(E6:G6)"
Next sh
End Sub

• Sub FILL_PASTE_DELETE() và End Sub: là bắt đầu và kết thúc của một đoạn chương trình (thủ tục VBA)
• Dim sh As Worksheet: Mình khai báo một biến sh để duyệt chạy tên tất cả các Sheet trên file Excel
• For Each sh In ActiveWorkbook.Worksheets: Đây là câu lệnh thực hiện vòng lặp, thực hiện việc duyệt lần lượt qua tất cả các sheet có trên file Excel
• sh.Range(“H6:H” & sh.Cells(sh.Rows.Count, “A”).End(xlUp).Row): Mình cần đặt công thức ở cột H, công thức bắt đầu đặt ở ô H6 và phải xác định được vùng dữ liệu từ ô H6 đến ô cuối cùng chứa dữ liệu của cột H để đặt công thức. Đoạn mã này sẽ giúp thực hiện việc này. Ví dụ, sheet LopA có 5 dòng dữ liệu thì sẽ cho vùng dữ liệu là H6:H10, sheet LopB có 4 dòng dữ liệu sẽ cho vùng H6:H9, tương tự như vậy, sheet LopC cho vùng H6:H15, sheet LopD cho vùng H6:H10
• sh.Range(“H1:H” & sh.Cells(sh.Rows.Count, “A”).End(xlUp).Row).Formula =: Đây là đoạn mã thực hiện đặt công thức cho các vùng đã xác định được ở ý trên.
• “=AVERAGE(E6:G6)”: Đây chính là công thức cần đặt tại ô H6, công thức luôn luôn được đặt trong cặp nháy kép và bắt đầu bằng dấu “=”. Chỉ cần nhập công thức với E6:G6 (tương đương với dòng đầu tiên của vùng đã xác định được) VBA sẽ tự động điền công thức vào các ô thuộc dòng tiếp theo của vùng đã xác định được ở trên. Việc nhập công thức trong VBA giống hệt việc nhập công thức trong một ô Excel với lưu ý bên dưới

Lưu ý:

• Với những công thức có chứa ký tự dạng Text trong VBA, chuỗi Text các bạn phải đặt trong 2 cặp nháy kép (“”) nhé. Nhắc lại công thức luôn luôn được đặt trong cặp nháy kép và bắt đầu bằng dấu “=” các bạn nhé
• Khi hiểu các thứ ở trên, khi làm trên 1 file khác, các bạn sẽ thay đổi dòng, cột đầu tiên cần nhập công thức sao cho đúng với nhu cầu thực tế của các bạn nhé

Vẫn ví dụ trên, nếu muốn tất cả các sheet tại ô K1 xuất hiện dòng thông báo số học viên tương ứng của lớp đó, chẳng hạn sheet LopA tại K1 là “So HV: 5”, mình thực hiện đoạn code sau

Sub FILL_PASTE_DELETE()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Range("H6:H" & sh.Cells(sh.Rows.Count, "A").End(xlUp).Row).Formula = "=AVERAGE(E6:G6)"
sh.Range("K1").Formula = "=""So HV: ""&COUNTA(A:A)-5"
Next sh
End Sub

Kết quả như hình dưới

Thật đơn giản phải không nào. Chúc các bạn thành công nhé!