Tự động trích ngang dữ liệu trong Excel

Ví dụ, chúng ta có một mẫu nhật ký chi tiền mặt (hình 1) với quy ước nhập liệu là nếu một chứng từ có nhiều tài khoản đối ứng thì nhập trên nhiều dòng khác nhau nhưng các cột ngày, số chứng từ và nội dung phải giống nhau.
Nội dung trích xuất từ tài liệu:
Tự động trích ngang dữ liệu trong ExcelTự động trích ngang dữ liệu trong ExcelVí dụ, chúng ta có một mẫu nhật ký chi tiền mặt(hình 1) với quy ước nhập liệu là nếu một chứng từcó nhiều tài khoản đối ứng thì nhập trên nhiều dòngkhác nhau nhưng các cột ngày, số chứng từ và nộidung phải giống nhau. Nhiệm vụ được chia nhỏ thành3 thủ tục macro để tiện cho việc bảo trì, sửa đổi mãlệnh sau này khi cần thiết (xin được bỏ qua bướctrình bày cách tạo, lưu giữ và quản lý module, cácthao tác này khá đơn giản). Một số điểm cần lưu ýtrước khi trình bày mã lệnh của các thủ tục: Để gán giá trị của một cell vào biến, hãy di  chuyển đến cell này và dùng thuộc tính value của cell hiện hành gán cho biến đã khai báo (Bien=ActiveCell.Value). Xong các lệnh gán, nhớ quay trở về cell cũ trước khi di chuyển. Sử dụng địa chỉ kiểu tương đối khi di chuyển cell  bằng thuộc tính Offset (Offset(Row, Column)). Dùng một macro thứ tư gọi lần lượt 3 macro trên  để hình thành một quá trình hoàn chỉnh, gán phím tắt cho macro này để tiện sử dụng.1. Trích ngang dữ liệu theo tài khoản phát sinh.Trong thủ tục này, căn cứ vào số hiệu tài khoản tạicột TK, số tiền tương ứng được trải ra theo chiềungang, tiền của tài khoản nào được điền vào cột mangđúng số hiệu tài khoản đó.Sub TrichNgang()Dim Taikhoan As StringDim ThutuDong, SoCot As IntegerDim Sotien As LongRange(“D2”).SelectLặp đến dòng cuối của danh sáchDo Until ActiveCell.Value = “”Taikhoan = ActiveCell.ValueActiveCell.Offset(0, 1).Range(“A1”).SelectSotien = ActiveCell.ValueRange(“F1”).SelectSoCot = 2Thực hiện cho đến cột tài khoản cuối cùng. Nếu tìmthấy số hiệu tài khoảnthì điền số tiền lên dòng trên cùng của chứng từ vàthoát vòng lặp.Do Until ActiveCell.Value = “”If ActiveCell.Value = Taikhoan ThenActiveCell.Offset(ThutuDong + 1,0).Range(“A1”).SelectActiveCell.Value = SotienExit DoElseActiveCell.Offset(0, 1).Range(“A1”).SelectEnd IfSoCot = SoCot + 1LoopTrường hợp không tìm thấy tài khoản thì điền số hiệutài khoản vào cột cuối cùng và điền số tiền vào đúngdòng đầu tiên của chứng từ. Bằng không dời con trỏxuống đầu dòng dưới và thêm thứ tự dòng 1 đơn vị.If ActiveCell.Value = “” ThenActiveCell.Value = TaikhoanActiveCell.Offset(ThutuDong + 1,0).Range(“A1”).SelectActiveCell.Value = SotienEnd IfActiveCell.Offset(1, -SoCot).Range(“A1”).SelectThutuDong = ThutuDong + 1LoopEnd Sub2. Mang số tiền từ các dòng dưới cộng vào dòng đầuđối với những chứng từ có hơn một dòng phát sinh.Đối với một chứng từ chi đối ứng với nhiều tàikhoản, bạn phải cộng dồn số tiền của từng tài khoảnvào cột tổng cộng và trích ngang số tiền này vào cáctài khoản tương ứng trên cùng một dòng.Sub CungCTu()Dim Ngay, Ngay2 As DateDim Chungtu, Chungtu2, Taikhoan, Taikhoan2,Noidung, Noidung2 As StringDim ThutuDong, SoCot, SoDong As IntegerDim Sotien, Sotien2 As LongLặp đến dòng cuối của danh sáchRange(“A2”).SelectNgay = ActiveCell.ValueActiveCell.Offset(0, 1).Range(“A1”).SelectChungtu = ActiveCell.ValueActiveCell.Offset(0, 1).Range(“A1”).SelectNoidung = ActiveCell.ValueActiveCell.Offset(0, 1).Range(“A1”).SelectTaikhoan = ActiveCell.ValueActiveCell.Offset(1, -3).Range(“A1”).SelectLưu các dữ liệu cần thiết vào biến. Lặp cho đến dòngcuối của danh sáchDo Until ActiveCell.Value = “”Ngay2 = ActiveCell.ValueActiveCell.Offset(0, 1).Range(“A1”).SelectChungtu2 = ActiveCell.ValueActiveCell.Offset(0, 1).Range(“A1”).SelectNoidung2 = ActiveCell.ValueIf Ngay = Ngay2 And Chungtu = Chungtu2 AndNoidung = Noidung2 ThenSoDong = SoDong + 1ActiveCell.Offset(0, -2).Range(“A1”).SelectThutuDong = ThutuDong + 1ActiveCell.Offset(0, 3).Range(“A1”).SelectTaikhoan2 = ActiveCell.ValueActiveCell.Offset(0, 1).Range(“A1”).SelectSotien2 = ActiveCell.ValueRange(“E1”).SelectSoCot = 5Lặp đến cột tài khoản cuối cùng. Nếu tìm thấy sốhiệu tài khoản thì cộng số tiền các dòng dưới lêndòng trên cùng của chứng từ, rồi thoát vòng lặp.Do Until ActiveCell.Value = “”If ActiveCell.Value = Taikhoan2 ThenActiveCell.Offset(ThutuDong - SoDong + 1,0).Range(“A1”).SelectActiveCell.Value = Sotien2ActiveCell.Offset(0, -SoCot + 5).Range(“A1”).SelectActiveCell.Value = ActiveCell.Value + Sotien2Exit DoElseActiveCell.Offset(0, 1).Range(“A1”).SelectEnd IfSoCot = SoCot + 1LoopActiveCell.Offset(1, -4).Range(“A1”).SelectElseSoDong = 0ActiveCell.Offset(0, -2).Range(“A1”).SelectThutuDong = ThutuDong + 1End IfLưu giữ các giá trị hiện tại để tiếp tục so sánh trongvòng lặpNgay = Ngay2Chungtu = Chungtu2Noidung = Noidung2ActiveCell.Offset(1, 0).Range(“A1”).SelectNếu một chứng từ có nhiều dòng, phải dời con trỏđến đúng dòng cuối.If SoDong > 1 ThenActiveCell.Offset(SoDong - 1,0).Range(“A1”).SelectEnd IfLoopEnd Sub3. Xóa các dòng thừa (dòng thứ hai trở đi) ở nhữngchứng từ có nhiều tài khoản đối ứng, đồng thời xóacột TK (tài khoản).Sub XoaDong()Dim Ngay, Ngay2 As DateDim Chungtu, Chungtu2, Noidung, Noidung2 AsStringLưu giữ các giá trị ở dòng đầu để so sánh trong vònglặpRange(“A2”).SelectNgay = ActiveCell.ValueActiveCell.Offset(0, 1).Range(“A1”).SelectChungtu = ActiveCell.ValueActiveCell.Offset(0, 1).Range(“A1”).SelectNoidung = ActiveCell.ValueActiveCell.Offset(1, -2).Range(“A1”).SelectSo sánh lần lượt dòng trên với dòng dưới, nếu xácđịnh là trùng nhau thì xóa các dòng thừa phía dưới.Do Until ActiveCell.Value = “”Ngay2 = ActiveCell.ValueActiveCell.Offset(0, 1).Range(“A1”).SelectChungtu2 = ActiveCell.ValueActiveCell.Offset(0, 1).Range(“A1”).SelectNoidung2 = ActiveCell.ValueActiveCell.Offset(0, -2).Range(“A1”).SelectIf Ngay = Ngay2 And Chungtu = Chungtu2 AndNoidung = Noidung2 ThenSelection.EntireRow.DeleteActiveCell.Offset(-1, 0).Range(“A1”).SelectElseEnd IfNgay = Ngay2Chungtu = Chungtu2Noidung = Noidung2ActiveCell.Offset(1, 0).Range(“A1”).SelectLoopXóa cột số hiệu tài khoản (TK)Columns(“D:D”).SelectSelect ...