Xóa Dữ Liệu Trong Excel Bằng Vba / Top 8 # Xem Nhiều Nhất & Mới Nhất 2/2023 # Top View | Hoisinhvienqnam.edu.vn

Cách Sắp Xếp Dữ Liệu Trong Excel Bằng Vba

Sắp xếp là một việc thường xuyên được thực hiện khi thao tác với dữ liệu trong VBA. Lợi ích của việc sắp xếp giúp cho chúng ta quản lý dữ liệu tốt hơn, dễ dàng tra cứu, kiểm tra hơn. Trong bài học này chúng ta cùng tìm hiểu một số cách sử dụng lệnh Sort trong VBA:

Ví dụ 1: Sắp xếp không bao gồm tiêu đề

Cho bảng dữ liệu sau, hãy sắp xếp thứ tự nội dung trong bảng theo dữ liệu ở cột D

View the code on Gist.

Trong câu lệnh này, bảng tính giới hạn từ ô A2 tới dòng cuối của cột G (toàn bộ bảng tính từ cột A tới G, từ dòng 2 tới dòng cuối có chứa dữ liệu).

Nội dung sắp xếp là nội dung tại cột D, tính từ ô D2

Thứ tự sắp xếp là thứ tự tăng dần (nếu sắp xếp giảm dần thì dùng xlDescending thay cho xlAscending)

Thứ tự sắp xếp có thể biểu diễn dưới dạng số:

xlAscending là số 1

xlDescending là số 2

Ví dụ 2: Sắp xếp có bao gồm tiêu đề

Câu lệnh sắp xếp với dữ liệu trên có bao gồm tiêu đề như sau:

View the code on Gist.

Những nội dung cần chú ý:

Vùng bắt đầu của bảng dữ liệu: Bao gồm cả dòng tiêu đề (trong ví dụ này bắt đầu từ dòng thứ 1)

Header = xlYes để thể hiện vùng dữ liệu này có tiêu đề, Header = xlNo để thể hiện không có tiêu đề. Tuy nhiên nếu vùng dữ liệu được chọn không có chứa tiêu đề thì không cần xét tiêu chí này (như ví dụ 1)

Dữ liệu sắp xếp ở cột D vẫn bắt đầu từ ô D1 hoặc D2 đều được

Ví dụ 3: Sắp xếp thứ tự trong tất cả các sheet

Nếu yêu cầu sắp xếp là bắt buộc và áp dụng trên tất cả các sheet thì chúng ta có thể áp dụng nội dung này để có thể sắp xếp dữ liệu một cách nhanh chóng chỉ với 1 câu lệnh:

View the code on Gist.

Diễn giải cách thực hiện:

Sử dụng vòng lặp để chạy qua các đối tượng là WorkSheet nằm trong Workbook đang làm việc

Tại mỗi Sheet đi qua thực hiện lệnh lọc dữ liệu với vùng dữ liệu được xác định: cột A đến cột G, từ dòng 2 tới dòng cuối có chứa dữ liệu, sắp xếp tại cột D từ ô D2, sắp xếp theo thứ tự tăng dần (1 = xlAscending)

Như vậy chúng ta đã có thể nắm được cách thức thực hiện lệnh sắp xếp trong VBA rồi phải không nào. Câu lệnh sắp xếp khá đơn giản và đem lại hiệu quả cao, giúp chúng ta có thể quản lý dữ liệu tốt hơn trong khi làm việc trên Excel.

Tải về file mẫu tại địa chỉ: http://bit.ly/2B8IP5K

Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…

Gitiho tự hào khi được đồng hành cùng:

50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…

Hướng Dẫn Cách Sắp Xếp Thứ Tự Dữ Liệu Tự Động Trong Excel Bằng Vba

Ở bài học trước, chúng ta đã tìm hiểu về việc sử dụng chức năng Sort để sắp xếp thứ tự dữ liệu trong Excel.

Xem lại bài học tại: Sắp xếp dữ liệu theo nhiều điều kiện trong Excel

Trong bài này, hocexcel.online sẽ giúp các bạn ứng dụng tính năng này vào VBA để giúp chúng ta có thể tự động sắp xếp lại dữ liệu mỗi khi nhập thêm dữ liệu mới vào bảng. Chúng ta tìm hiểu bài học này thông qua bài tập ví dụ sau:

Cho bảng dữ liệu sau:

Xóa nội dung của dữ liệu đã nhập ở I2:M2 (làm mới dữ liệu)

Sắp xếp tự động các nội dung trong bảng dữ liệu

Bước 1: Record Macro thao tác lọc dữ liệu

Tại tab Developer, chọn chức năng Record Macro

Chúng ta có thể nhập dữ liệu bất kỳ ở vùng này

Ví dụ như sau:

Căn cứ theo yêu cầu của đề bài, chúng ta có thể xác định được trình tự lưu dữ liệu bao gồm:

Đưa dữ liệu từ vùng nhập mới vào Bảng dữ liệu

Xóa dữ liệu ở vùng nhập mới

Sắp xếp dữ liệu trong Bảng dữ liệu

Chúng ta mở cửa sổ VBA và tạo Sub Lưu dữ liệu như sau:

Code Lưu dữ liệu vào Bảng:

Tìm dòng cuối trong bảng (các bạn có thể tìm hiểu thêm kiến thức về VBA trong khóa học VBA101 của thầy Thanh)

Dim DongCuoi As Long DongCuoi = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row + 1

Lưu dữ liệu vào dòng cuối

Sheet1.Range(“A” & DongCuoi & “:” & “F” & DongCuoi).Value = Sheet1.Range(“I2:N2”).Value

Code xóa dữ liệu ở vùng nhập mới (lưu ý là ô M2 có chứa công thức = Đơn giá * Số lượng, vì vậy chúng ta không cần xóa công thức này)

Sheet1.Range(“I2:M2”).ClearContents

Code sắp xếp dữ liệu: chúng ta gọi Macro sắp xếp đã thu được ở trên

Call Macro3

* Kết quả dòng lệnh như sau:

Sau khi đã gán macro vào nút mũi tên, chúng ta thực hiện việc lưu dữ liệu. Và đây là kết quả của chúng ta:

Các bạn có thể thấy là dòng này sau khi thêm đã được tự động sắp xếp theo thứ tự vào trong bảng, chứ không phải xuất hiện ở cuối bảng như thông thường.

Như vậy là chúng ta đã hoàn thành được yêu cầu của đề bài rồi. Nội dung này được ứng dụng rất nhiều trong việc quản lý dữ liệu, nhất là những dữ liệu lớn, cần sắp xếp theo nhiều điều kiện: thời gian, phân nhóm …

VBA giúp ích rất nhiều trong công việc: giúp bạn tăng tốc trong quá trình xử lý trên Excel, giúp lập các báo cáo tự động, điều khiển các chương trình khác như Word, Power Point, Outlook … giúp biến file Excel của bạn trở thành 1 phần mềm chuyên nghiệp …

Cách Viết Code Sửa Dữ Liệu Trong Bảng Tính Excel Bằng Vba Chi Tiết Nhất 2022

Khi ứng dụng VBA trong Excel, chúng ta có thể nhập dữ liệu trên 1 Sheet rồi lưu sang Sheet khác. Đã có rất nhiều bài viết trên chúng tôi hướng dẫn bạn cách làm này. Nhưng làm thế nào để chúng ta có thể “nhặt” dữ liệu ra để sửa, sau khi sửa xong sẽ lưu lại đúng vị trí của dòng dữ liệu đó trong bảng tính? Trong bài viết này các bạn sẽ được tìm hiểu chi tiết cách làm.

Quy trình sửa dữ liệu trong lập trình VBA

Sửa dữ liệu thông thường, chúng ta chỉ việc vào trong bảng tính và sửa trực tiếp. Nhưng việc đó có một số hạn chế:

Trong trường hợp bảng tính được khóa (Protect Sheet) thì không sửa trực tiếp được.

Với bảng tính lớn, nhiều dữ liệu, việc sửa trực tiếp trong bảng đó sẽ dễ nhầm lẫn, sai vị trí.

Do đó chúng ta có thể sử dụng quy trình sửa dữ liệu trong lập trình VBA để khắc phục các hạn chế đó. Cụ thể:

Chọn dòng dữ liệu cần sửa

Đưa dữ liệu từ dòng đó sang 1 Form riêng để sửa (có thể là form trên Sheet hoặc UserForm trong VBA)

Sửa nội dung trong Form

Lưu lại nội dung từ Form vào đúng dòng dữ liệu đã chọn để sửa trong bảng dữ liệu

Cách viết code lấy dữ liệu cần sửa trong bảng tính Excel bằng VBA

Bước 1: Xây dựng cấu trúc bảng dữ liệu và Form sửa dữ liệu

Ví dụ chúng ta có bảng tính như sau:

Trong hình trên, chúng ta thấy có 2 phần:

Bảng dữ liệu nhân sự: là nơi chứa các thông tin về nhân viên trong công ty

Bảng Sửa thông tin nhân sự (Form Sửa thông tin): Là nơi mà chúng ta sẽ trích lọc thông tin của nhân viên cần sửa

Dấu X: Đại diện cho lệnh xóa nội dung bên trong Form Sửa thông tin

Mũi tên từ phải sang trái: Đại diện cho lệnh Lưu dữ liệu sau khi sửa. Khi bấm vào mũi tên này sẽ thực thi lệnh lưu nội dung từ Form sửa thông tin vào đúng vị trí dòng dữ liệu đã lấy ra để sửa.

Lưu ý: Form sửa dữ liệu bạn có thể đặt tại Sheet khác hoặc sử dụng UserForm. Trong ví dụ này làm chung trong 1 Sheet để dễ theo dõi.

Bước 2: Viết code lấy dữ liệu vào Form sửa thông tin

Gồm các lệnh:

Xác định vị trí dòng chứa thông tin cần sửa

Lấy nội dung tương ứng theo từng cột tại dòng chứa thông tin cần sửa vào Form

1. Khi bấm chuột chọn vào dòng cần sửa, chúng ta có câu lệnh xác định số dòng theo ô được chọn là:

chúng tôi

Gán 1 biến đại diện cho giá trị này là DongSua, ta có:

Sub LayDuLieu()

‘1. Lệnh gán biến xác định dòng cần sửa

Dim DongSua As Long DongSua = ActiveCell.Row

‘2. Lệnh lấy dữ liệu theo dòng cần sửa

2. Lệnh lấy dữ liệu theo dòng cần sửa gồm:

Số dòng cần sửa được gán vào ô J2

Mã nhân viên cần sửa gán vào ô H5

Tên nhân viên cần sửa gán vào ô J5

Ngày sinh của nhân viên cần sửa gán vào ô H8

Bộ phận của nhân viên cần sửa gán vào ô J8

Ngày vào làm của nhân viên cần sửa gán vào ô L8

Tất cả các nội dung trên đều được thực thi trong Sheet1 (là Sheet chứa bảng dữ liệu và Form sửa) nên có thể viết như sau:

Sub LayDuLieu()

‘1. Lệnh gán biến xác định dòng cần sửa

Dim DongSua As Long DongSua = ActiveCell.Row

‘2. Lệnh lấy dữ liệu theo dòng cần sửa

With Sheet1 .Range(“J2”).Value = DongSua .Range(“H5”).Value = .Range(“A” & DongSua).Value .Range(“J5”).Value = .Range(“B” & DongSua).Value .Range(“H8”).Value = .Range(“C” & DongSua).Value .Range(“J8”).Value = .Range(“D” & DongSua).Value .Range(“L8”).Value = .Range(“E” & DongSua).Value End With

Bây giờ chúng ta chỉ việc gán Macro LayDuLieu vừa viết ở trên vào Mũi tên thứ 1 (từ trái qua phải)

Khi đó muốn sửa dữ liệu nào, chúng ta chỉ cần chọn dòng cần sửa, sau đó bấm vào Mũi tên, dữ liệu sẽ được tự động đưa sang Form sửa:

Bước 3: Viết code xóa nội dung trong Form sửa

Trong trường hợp chúng ta muốn xóa nội dung trong Form sửa dữ liệu về lại trạng thái không có nội dung gì, chúng ta có thể sử dụng thuộc tính .ClearContent cho đối tượng Range như sau:

Sub XoaDuLieu()

With Sheet1 .Range(“J2, H5:L5, H8:L8”).ClearContents End With

Bước 4: Viết code lưu nội dung trong Form sửa trở lại dòng chứa dữ liệu cần sửa

Để lưu nội dung từ Form sửa trở lại bảng dữ liệu, chúng ta thực hiện viết giống như lệnh lấy dữ liệu từ Bảng sang Form sửa, nhưng thứ tự ngược lại như sau:

Sub LuuDuLieu()

‘Khai báo biến xác định dòng sửa = dòng lưu dữ liệu

Dim DongSua As Long DongSua = Sheet1.Range(“J2”).Value

‘Thực hiện lưu dữ liệu từ Form sửa vào bảng dữ liệu theo dòng sửa

With Sheet1 .Range(“A” & DongSua).Value = .Range(“H5”).Value .Range(“B” & DongSua).Value = .Range(“J5”).Value .Range(“C” & DongSua).Value = .Range(“H8”).Value .Range(“D” & DongSua).Value = .Range(“J8”).Value .Range(“E” & DongSua).Value = .Range(“L8”).Value End With

‘Gọi lệnh Xóa dữ liệu trong Form sửa sau khi đã hoàn thành việc lưu dữ liệu

Call XoaDuLieu

‘Mở thông báo bằng msgbox về việc đã lưu thành công

Msgbox “Luu du lieu thanh cong”

Như vậy là chúng ta đã hoàn thành được quy trình sửa dữ liệu trong bảng tính Excel bằng VBA rồi.

Trong trường hợp chúng ta muốn thực hiện với Worksheet đang khóa (Protect Sheet), chúng ta chỉ cần thêm câu lệnh:

Sheet1.Unprotect ở đầu đoạn code (trước khi thực hiện các macro Lấy dữ liệu, Lưu dữ liệu, ngay sau dòng Sub, trước các dòng lệnh khác)

Sheet1.Protect ở cuối đoạn code (trước dòng lệnh End Sub của các macro Lấy dữ liệu, Lưu dữ liệu)

Sub LayDuLieu()

Sheet1.Unprotect

Sheet1.Protect

Tìm Và Xóa Dữ Liệu Trùng Lặp Trong Excel

Trong quá trình làm việc với bảng tính Excel sẽ có những file Excel có khối lượng dữ liệu lớn nên việc trùng lặp dữ liệu rất hay xảy ra. Có những trường hợp dữ liệu trùng lặp là hữu ích, nhưng nhiều khi các giá trị trùng lặp không giúp ích cho các bạn mà nó còn làm rối dữ liệu trong bảng tính hơn. Vậy làm sao để tìm và loại bỏ các dữ liệu trùng lặp đó? Các bạn có thể sử dụng định dạng có điều kiện để tìm và tô màu nổi bật vùng dữ liệu trùng lặp hoặc sử dụng Visual Basic để tìm dữ liệu trùng lặp. Sau đó các bạn thực hiện xóa dữ liệu trùng lặp trong Excel.

TÌM DỮ LIỆU TRÙNG LẶP TRONG EXCEL

Cách 1: Sử dụng Highlight Cells Rules trong Định dạng có điều kiện

Bước 1: Chọn (bôi đen) vùng dữ liệu trên bảng tính Excel mà bạn muốn tìm dữ liệu trùng lặp.

Có 6 kiểu đánh dấu mà bạn có thể áp dụng để đánh dấu cho các giá trị trùng:

Light Red Fill with Dark Red Text: đổ nền đỏ nhạt với chữ màu đỏ đậm.

Yellow Fill with Dark Yellow Text: đổ nền màu vàng với chữ màu vàng đậm.

Green Fill with Dark Green Text: đổ nền màu xanh lá cây với chữ màu xanh đậm.

Light Red Fill: đổ nền màu đỏ nhạt.

Red Text: chữ màu đỏ.

Red Border: đường viền màu đỏ.

Sau khi đã chọn kiểu đánh dấu xong các bạn chọn OK để đánh dấu.

Dim ws As Worksheet Dim cell As Range Dim myrng As Range Dim clr As Long Dim lastcell As Range Dim lastrow As Long Set ws = ThisWorkbook.ActiveSheet 'Vung can danh dau gia tri trung nhau Set myrng = ws.Range("A1:C18") With myrng Set lastcell = .Cells(.Cells.Count) End With myrng.Interior.ColorIndex = xlNone clr = 3 For Each cell In myrng 'Kiem tra so gia tri trung nhau trong vung, neu co hai gia tri trung nhau tro len thi thuc hien 'Neu la o dau tien cua cac gia tri trung nhau trong vung If myrng.Find(what:=cell, lookat:=xlWhole, MatchCase:=False, after:=lastcell).Address = cell.Address Then 'Thiet lap mau cell.Interior.ColorIndex = clr Else 'Thiet lap mau tu o thu 2 voi cac gia tri trung nhau cell.Interior.ColorIndex = myrng.Find(what:=cell, lookat:=xlWhole, MatchCase:=False, after:=lastcell).Interior.ColorIndex End If End If Next

Lưu ý: Trong phần thiết lập vùng tìm giá trị trùng nhau, giá trị bôi đỏ A1:C18 các bạn thay bằng vùng dữ liệu cần tìm giá trị trùng nhau phù hợp với dữ liệu trong bảng tính Excel của bạn.

Sau khi đã tìm được các dữ liệu trùng lặp trong Excel, nếu các bạn muốn xóa dữ liệu trùng lặp thì các bạn có thể xóa thủ công dựa vào các vùng dữ liệu trùng lặp đã được đánh dấu. Hoặc các bạn có thể thực hiện các bước sau:

Bước 1: Chọn vùng dữ liệu cần xóa các giá trị trùng lặp.

Ví dụ cột Ngày công T2 có nhiều giá trị trùng nhưng chúng tôi không muốn xóa, vì vậy chúng tôi sẽ bỏ chọn trong cột Ngày công T2.