Sắp Xếp Dữ Liệu Trong Excel Bằng Vba / Top 7 # 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,…

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

Phần mềm Excel cung cấp nhiều giải pháp sắp xếp dữ liệu nhanh, cho phép bạn phân loại tập hợp dữ liệu qua biểu tượng sắp xếp dữ liệu trong thanh ribbon hoặc qua hộp thoại phân loại dữ liệu. Nếu bạn đang thắc mắc nguyên nhân chúng tôi muốn nhắc đến cách sắp xếp dữ liệu trong Excel sử dụng VBA thay vì áp dụng hai cách trên, thì câu trả lời rất đơn giản: đây là giải pháp cực kỳ hữu ích khi bạn cần định dạng và sắp xếp các nhóm dữ liệu theo ngày hay theo tuần theo thứ tự nhất định.

Trong bài viết này, chúng tôi sẽ hướng dẫn các bạn Cách sắp xếp dữ liệu trong Excel sử dụng VBA.

Nắm được phương pháp chúng tôi trong Excel VBA

Khi sắp xếp dữ liệu qua VBA, trước tiên bạn cần hiểu được phương pháp Range.Sort. “Range” là dữ liệu bạn cần sắp xếp. Ví dụ, nếu muốn sắp xếp dữ liệu vào các ô A1:A10, thì “Range” chính là vùng đó (“A1:A10”).

Bên cạnh đó, bạn có thể tạo một Range có tên và dùng thay cho các tham chiếu ô. Ví dụ, nếu tôi tạo một vùng có tên “DataRange” cho các ô A1:A10, như vậy, tôi có thể sử dụng Range (“DataRange”).

– Key – ở đây bạn cần xác định cột muốn sắp xếp. Ví dụ, nếu muốn sắp xếp cột A, bạn cần sử dụng lệnh key:=Range(“A1”).

– Order – Ở đây, bạn chọn sắp xếp dữ liệu theo thứ tự chiều nhỏ đến lớn hoặc theo thứ tự từ chiều lớn đến nhỏ. Ví dụ, nếu muốn phân loại theo thứ tự từ nhỏ đến lớn, bạn sử dụng lệnh Order:=xlAscending.

– Header – Ở đây, bạn chọn tập hợp dữ liệu có tiêu đề hoặc không có tiêu đề. Nếu có tiêu đề, việc sắp xếp dữ liệu sẽ bắt đầu từ dòng thứ hai của tập hợp dữ liệu, thay vì từ dòng thứ nhất. Đối với dữ liệu có tiêu đề, bạn sử dụng lệnh Header:=xlYes.

Sub SortDataWithoutHeader()Range(“A1:A12”).Sort Key1:=Range(“A1”), Order1:=xlAscending, Header:=xlNoEnd SubỞ đây, tôi chọn vùng dữ liệu là Range(“A1:A12”).Trong trường hợp dữ liệu có thể được thay đổi và bổ sung hay xóa bớt các giá trị, bạn có thể sử dụng đoạn code sau để chương trình tự động điều chỉnh theo các ô có điền dữ liệu trong tập hợp dữ liệu.Sub SortDataWithoutHeader()Range(“A1”, Range(“A1”).End(xlDown)).Sort Key1:=Range(“A1”), Order1:=xlAscending, Header:=xlNoEnd Sub

Lưu ý rằng thay bằng Range(“A1:A12”), tôi vừa sử dụng Range(“A1”, Range(“A1”).End(xlDown)).

Như vậy, ta sẽ kiểm tra được các ô có điền dữ liệu liên tục đến cuối cột và bao hàm nó trong dữ liệu được sắp xếp. Trong trường hợp có các ô trống, chương trình sẽ chỉ xét dữ liệu cho đến ô trống đầu tiên.

Ngoài ra, bạn có thể tạo vùng theo tên và sử dụng vùng tên đó thay cho các tham chiếu ô. Ví dụ, nếu vùng được đặt tên là DataSet, đoạn code của bạn sẽ có được viết như sau:

Sub SortDataWithoutHeader()

Range(“DataRange”).Sort Key1:=Range(“A1”), Order1:=xlAscending, Header:=xlNo

End Sub

Bây giờ, chúng ta sẽ giải thích nhanh các thông số được sử dụng trong ví dụ trên:

– Key1:=Range(“A1”) – A1 là mã cho biết cột đang sắp xếp dữ liệu.– Order1:=xlAscending – chỉ thứ tự hiện tại là xlAscending. Nếu muốn sắp xếp dữ liệu theo thứ tự từ lớn đến nhỏ, sử dụng lệnh xlDescending.– Header:= xlNo – chỉ dữ liệu hiện tại không có tiêu đề. Đây là giá trị mặc định. Vì vậy kể cả khi bạn bỏ đi giá trị này, dữ liệu sẽ được sắp xếp theo theo trường hợp không có tiêu đề.

Phân loại cột đơn có tiêu đề

Trong ví dụ trước, tập hợp dữ liệu không có tiêu đề.

Đối với dữ liệu có tiêu đề, bạn cần chỉ ra trong đoạn code, để chương trình sắp xếp dữ liệu bắt đầu từu dòng thứ hai của tập hợp dữ liệu.

Giả sử bạn có tập hợp dữ liệu như sau:

Sub SortDataWithHeader()Range(“DataRange”).Sort Key1:=Range(“C1”), Order1:=xlDescendingEnd Sub

Lưu ý rằng tôi vừa tạo một vùng có tên – ‘DataRange’, và sử dụng vùng có tên này trong đoạn code.

Sắp xếp nhiều cột có tiêu đề

Trong các phần trước của bài viết này, chúng ta đã nghiên cứu Cách sắp xếp cột đơn (có và không có tiêu đề).

Bây giờ là các thao tác bạn cần thực hiện để sắp xếp dữ liệu theo nhiều cột.

Đây là đoạn code sắp xếp đồng thời nhiều cột.

Sub SortMultipleColumns()With ActiveSheet.Sort.SortFields.Add Key:=Range(“A1”), Order:=xlAscending.SortFields.Add Key:=Range(“B1”), Order:=xlAscending.SetRange Range(“A1:C13”).Header = xlYes.ApplyEnd WithEnd Sub

Trong ví dụ trên, đầu tiên dữ liệu được sắp xếp theo mã bang (cột A). Sau đó, trong dữ liệu mã bang, dữ liệu tiếp tục được sắp xếp theo tên Cửa hàng (cột B). Thứ tự này được xác định theo mã mà bạn muốn đề cập.

Lưu ý rằng tôi vừa tạo một vùng có tên (“DataRange”) và dùng trong đoạn code trên thay cho các tham chiếu ô.

Ngoài ra, đoạn code này chỉ sắp xếp các cột theo thứ tự từ nhỏ đến lớn.

Thao tác thực hiện:

Giả sử bây giờ bạn muốn sắp xếp hai cột đầu tiên (‘Sate’ và ‘Store’) theo thứ tự từ nhỏ đến lớn, với cột ‘Sales’ theo thứ tự từ lớn đến nhỏ.

Đoạn code sẽ như sau:

Bây giờ, chúng ta sẽ quan sát công cụ visual Marker (mũi tên và ô có màu) trong tiêu đề khi sắp xếp dữ liệu.

Như vậy, tôi vừa thêm một bảng tính mới và thay đổi một số nội dung trong bảng này (bạn có thể tải file ví dụ và thực hành theo):

Bảng backend sẽ có dạng như sau:

Lưu ý rằng đoạn code này thường được áp dụng để xây dựng dữ liệu và tập tin workbook. Nếu thay đổi cấu trúc dữ liệu, bạn sẽ phải thay đổi cả đoạn code trên.

Cách Sắp Xếp Dữ Liệu Trong Excel

Sắp xếp dữ liệu dạng văn bản

Lưu ý: các bạn chọn Expand the selection thì cột bạn chọn sẽ được sắp xếp và các dữ liệu khác theo dòng sẽ được sắp xếp theo, nếu không thì chỉ có cột bạn chọn mới được sắp xếp.

Kết quả sau khi các bạn chọn kiểu sắp xếp Sort A to Z.

Nếu bạn chọn Sort Z to A thì kết quả sẽ được như sau:

Sắp xếp dữ liệu dạng số

Chọn cột dữ liệu số cần sắp xếp, trong thẻ Home các bạn chọn Sort & Filter và chọn một trong hai kiểu sắp xếp số: Sort Smallest to Largest (sắp xếp từ số nhỏ nhất đến số lớn nhất) và Sort Largest to Smallest (sắp xếp từ số lớn nhất đến số nhỏ nhất).

Như vậy dữ liệu đã được sắp xếp, vì ở trên chọn Sort Smallest to Largest nên kết quả các bạn sẽ được như sau:

Sắp xếp dữ liệu ngày tháng

Tương tự như sắp xếp dữ liệu dạng văn bản và dạng số, để sắp xếp dữ liệu theo ngày tháng các bạn cũng thực hiện như sau:

Kết quả sau khi sắp xếp Sort Oldest to Newest như sau:

Sắp xếp dữ liệu theo tiêu chí cụ thể

Các bạn có thể sắp xếp theo nhiều cột dữ liệu, các bạn thực hiện như sau:

Lưu ý: Trong quá trình sắp xếp dữ liệu khi xuất hiện hộp thoại cảnh báo Sort Warning thì các bạn chọn Expand the selection và chọn Sort.

Bước 2: Xuất hiện hộp thoại Sort các bạn thực hiện tùy chỉnh như sau:

(1) Chọn cột ưu tiên sắp xếp đầu tiên trong ô Sort by, chọn tiêu chí sắp xếp trong phần Sort On, chọn quy tắc sắp xếp trong phần Order.

(2) Add Level: thêm cấp độ sắp xếp, cấp độ mặc định ở trên sẽ là cấp độ cao nhất, đầu tiên sẽ sắp xếp theo cột ở cấp độ trên, sau đó sẽ sắp xếp tiếp theo cột ở cấp độ tiếp theo.

(3) Delete Level: xóa cấp độ.

(4) My data has headers: nếu vùng dữ liệu bạn chọn đã bao gồm tiêu đề thì các bạn đánh dấu tích chọn mục này để Excel không sắp xếp tiêu đề. Nếu vùng dữ liệu bạn chọn không gồm tiêu đề thì các bạn bỏ qua không chọn mục này.

Như vậy dữ liệu của bạn đã được sắp xếp.

Tự tạo danh sách sắp xếp tùy chỉnh

Các bạn có thể tự tạo danh sách sắp xếp tùy chỉnh để các bạn có thể chọn trong quy tắc sắp xếp Order của hộp thoại Sort như sau:

Ví dụ các bạn muốn sắp xếp cột Lớp Chuyên, thì các bạn chọn cột Lớp Chuyên.

Bước 2: Xuất hiện hộp thoại Sort, trong phần Order các bạn chọn biểu tượng mũi tên quay xuống và chọn Custom List.

Bước 3: Trong hộp Custom Lists các bạn chọn New list trong Custom lists, sau đó các bạn nhập danh sách sắp xếp tùy chỉnh trong phần List entries, mỗi nội dung các bạn nhập trong một dòng. Sau đó nhấn Add để thêm vào Custom lists.

;

Để xóa danh sách sắp xếp tùy chỉnh các bạn chọn danh sách trong phần Custom Lists và chọn Delete.

Sau khi đã thêm xong các bạn chọn OK để đóng Custom Lists.

Bước 4: Như vậy danh sách tùy chỉnh sẽ được hiển thị trong phần Order, các bạn chỉ cần chọn cột sắp xếp trong phần Sort by và nhấn OK để sắp xếp.

Kết quả các bạn sẽ được như sau:

Hướng Dẫn Sắp Xếp Dữ Liệu Trong Excel 2007

chúng tôi sẽ hướng dẫn các bạn bằng ví dụ cụ thể với bảng dữ liệu sau:

Ví dụ 1: Sắp xếp theo thứ tự alphabet của cột Tên

Bước 2: Hộp thoại Sort Warning xuất hiện, có 2 lựa chọn:

– Expand the selection: các cột dữ liệu phụ thuộc (như Số lượng, Đơn vị tính…) sẽ bị thay đổi cách sắp xếp tương ứng theo cột Tên

– Continue with the current selection: chỉ có cột Tên bị thay đổi sắp xếp, các cột khác vẫn giữ nguyên

Thông thường trong các bảng Excel sẽ chọn Expand the selection. Sau đó chọn Sort.

Bước 3: Dữ liệu sẽ được sắp xếp theo đúng thự tự chữ cái abc

Bước 4: Bạn chỉ cần thay đổi lại cột STT là hoàn thành

Tương tự như vậy, nếu bạn muốn sắp xếp theo thứ tự từ Z đến A thì ở bước 1, bạn chọn Z to A

Ta sẽ có kết quả dữ liệu được sắp xếp từ Z đến A:

Ví dụ 2: Sắp xếp theo thứ tự tăng dần (hoặc giảm dần)

Đối với những cột dữ liệu định dạng dưới dạng number (như cột Số lượng, cột Thành tiền) thì cần sắp xếp tăng dần hoặc giảm dần. Các thao tác thực hiện tương tự với các bước hướng dẫn ở trên.

Ví dụ: Khi muốn sắp xếp tăng dần cột Thành tiền, bôi đen dữ liệu ở cột Thành tiền → vào Home chọn Sort & Filter → chọn Sort Smallest to Largest (từ bé đến lớn)

Và đây là dữ liệu sau khi đã được sắp xếp tăng dần của cột Thành tiền:

Hoặc giảm dần nếu chọn Sort Largest to Smallest (giảm dần, từ lớn đến bé)

Ví dụ 3: Sắp xếp theo điều kiện của 2 cột

Trường hợp bạn muốn sắp xếp tăng dần cột Thành tiền và A to Z cột Tên thì trước hết bạn cần xác định thứ tự ưu tiên muốn xếp cột nào trước.

Với những điều kiện trên, cột Thành tiền sẽ được sắp xếp tăng dần trước (từ bé đến lớn), sau đó với những cột Thành tiền có giá trị bằng nhau, sẽ được sắp xếp theo thứ tự A to Z của Tên. Cụ thể kết quả như sau:

Như ta đã thấy trong bảng dữ liệu trên, Bút chì màu và Nhãn vở có Thành tiền bằng nhau nên cột tên Bút chì màu và Nhãn vở được sắp xếp A to Z.