Sắp xếp và lọc dữ liệu Excel theo tháng và ngày

Đây là cách bạn sắp xếp dữ liệu Excel theo tháng và ngày và không tính đến năm

Về cơ bản Excel sắp xếp các ngày như sau: Đầu tiên chúng được sắp xếp theo năm, sau đó theo tháng và cuối cùng là theo ngày. Trong hầu hết các trường hợp, thứ tự sắp xếp này cũng có ý nghĩa. Tuy nhiên, đôi khi yêu cầu sắp xếp không có năm. Một ví dụ cổ điển là danh sách sinh nhật. Thường thì năm nên để ở đây và chỉ sắp xếp theo ngày và tháng.

Bạn có thể sử dụng công thức này để sắp xếp danh sách Excel của mình theo thứ tự thời gian

Nếu bạn muốn sắp xếp bảng theo tháng và ngày ở dạng danh sách ngày sinh mà không tính đến năm và ngày đầu tiên nằm trong ô A2, hãy sử dụng công thức sau trong cột bổ trợ:

= TEXT (A2; "MM.DT.")

Bạn áp dụng công thức như sau:

Tạo một cột bổ trợ mà bạn muốn hiển thị kết quả (trong ví dụ "Công thức").

Bấm vào ô đầu tiên của cột phụ.

Trong trường hàm (f_NS) công thức: = TEXT (A2; "MM.DT.").

Đảm bảo rằng hàm đang tham chiếu đến ô đúng với ngày chính xác (trong ví dụ, ngày nằm trong ô B2).

Nhấn Enter hoặc nhấn dấu tích nhỏ màu xanh lá cây và thế là xong!

Sau đó, sao chép công thức này đủ xa trong cột phụ trợ.

Hình sau cho thấy việc sử dụng công thức này trong một bảng mẫu (giá trị ngày tháng nằm trong ô B2):

Sau đó, bạn sử dụng cột bổ trợ này trong hộp thoại "Dữ liệu". Sau đó chọn "Sắp xếp" để thiết lập thứ tự mong muốn.

Nếu bạn muốn sắp xếp theo ngày và sau đó là tháng, công thức sẽ như sau:

= TEXT (A2; "DD.MM.")

Thận trọng! Công thức cung cấp văn bản thay vì số trong Excel

Lưu ý rằng cả hai công thức đều tạo ra văn bản mà bạn không thể tính toán bằng. Vì vậy, khi thực hiện phân tích, bạn cần tham khảo các ô có giá trị ngày ban đầu.

Tại sao những vấn đề này lại phát sinh khi sắp xếp dữ liệu trong Excel?

Vấn đề với tác vụ này là Excel luôn tính đến năm khi sắp xếp ngày tháng. Vì vậy, ngày 31 tháng 12 năm 2009 là trước ngày 1 tháng 8 năm 2010. Do đó, bạn cần một cột bổ trợ, trong đó bạn phân tách các giá trị ngày tháng cho phù hợp.

Một cách tiếp cận khác giúp giải quyết các vấn đề với sắp xếp trong Excel

Nếu bạn gặp vấn đề với công thức này và việc sắp xếp tiếp theo, có một quy trình khác. Để làm điều này, bạn cũng cần thêm một cột bổ trợ trong đó bạn đặt các tiêu chí sắp xếp lại với nhau theo cách thủ công. Để thực hiện việc này, hãy tiến hành như sau:

1. Trong ô C4, ghi lại công thức sau:
   = THÁNG (B4) + NGÀY (B4) / 100
   Công thức này kết nối tháng và ngày với tiêu chí sắp xếp mong muốn.

   

2. Sao chép công thức xuống ô C13.

   

3. Bây giờ đánh dấu khu vực A3: C13.

   

4. Gọi mục “Sắp xếp” qua menu “Dữ liệu” trong “Sắp xếp và lọc” và do đó xuất hiện cửa sổ hộp thoại cùng tên.

   

5. Trong trường "Sắp xếp theo", hãy chọn mục nhập "Sắp xếp cột".

6. Trong trường "Sắp xếp theo", vui lòng chọn "Giá trị ô".“ đã sửa.

7. Trong trường "Đơn hàng" chọn mục nhập "Theo kích thước (tăng dần)" (xem hình).

   

8. Nếu bây giờ bạn xác nhận hộp thoại bằng cách nhấp vào nút "OK", danh sách ngày sinh sẽ được sắp xếp theo thứ tự tăng dần theo cột C phụ trợ. Bạn có thể xem kết quả trong hình sau:

   

Lưu ý cho các chuyên gia Excel về công thức cho thứ tự sắp xếp

Hàm MONTH () được sử dụng để đọc tháng từ giá trị ngày.

Hàm DAY () cũng xác định ngày từ giá trị ngày.

Chia giá trị hàng ngày cho 100 đảm bảo rằng tháng được sử dụng làm tiêu chí sắp xếp chính. Do đó, đặc tả ngày không bao giờ được lớn hơn 1 và do đó cũng không thể ảnh hưởng đến giá trị hàng tháng nhỏ nhất 1. Do đó, đặc tả ngày đảm nhận một vị trí phụ tương ứng cho logic sắp xếp.

Nếu bạn sử dụng danh sách được lọc, bạn chỉ có thể sử dụng các bộ lọc để hiển thị dữ liệu mà bạn muốn hiển thị. Nhưng làm thế nào để bạn lọc vào một ngày cụ thể hoặc một phạm vi ngày?

Cách lọc theo một ngày trong Excel

Hình sau hiển thị danh sách các số thứ tự lịch sử mà bạn muốn lọc:

Các bộ lọc tự động đã được đặt trong danh sách (các mũi tên nhỏ màu xám). Cột C của danh sách chứa các giá trị ngày tháng cho các bản ghi dữ liệu. Để lọc danh sách sao cho chỉ các bản ghi rơi vào ngày trong ô D1 được hiển thị, hãy làm như sau:

Lọc một ngày từ bảng

1. Nhấp vào bộ lọc trong cột C (mũi tên nhỏ hướng xuống dưới).

2. Excel lên đến phiên bản 2003: Chọn mục nhập "Do người dùng xác định" từ danh sách bộ lọc. Sử dụng cài đặt “Tương ứng” từ danh sách đầu tiên.

3. Excel 2007 trở lên: Chọn mục nhập “Bằng” từ danh sách bộ lọc trong “Bộ lọc ngày”.

   

4. Trong tất cả các phiên bản Excel, hãy nhập ngày mong muốn phía sau trường nhập "tương ứng với" (trong ví dụ là ngày 13 tháng 5 năm 2009).

   

5. Xác nhận lựa chọn của bạn bằng nút OK.

6. Excel sẽ áp dụng bộ lọc mong muốn ngay lập tức. Hình sau cho thấy kết quả:

   

Cách lọc danh sách trong Excel theo một năm

Nếu bạn có ngày trong danh sách, bạn không thể dễ dàng lọc theo năm. Bộ lọc tùy chỉnh “chứa năm 2011” sẽ không tìm thấy ngày từ năm 2011. Điều này là do Excel diễn giải các giá trị ngày tháng là các giá trị liên tục từ ngày 1 tháng 1 năm 1900 và chỉ hiển thị chúng ở dạng chúng ta quen thuộc thông qua định dạng.

Mẹo nhỏ để lọc theo năm trong Excel

Tuy nhiên, bạn cũng có thể lọc danh sách của mình theo những năm nhất định. Tuy nhiên, để thực hiện việc này, bạn phải thêm một cột bổ sung để trích xuất các năm từ các ngày tháng. Trong sổ làm việc được hiển thị, bạn có thể thấy danh sách được lọc theo năm 2011:

Để thực hiện việc lọc này dựa trên năm 2011, hãy thực hiện các bước sau:

1. Bao gồm cột mới "Năm" bằng cách nhập văn bản "Năm" vào ô D3.

2. Nhập công thức sau vào ô D4 để tính năm của hàng đầu tiên của danh sách:
   = NĂM (A4)

   

3. Sao chép công thức vào tất cả các hàng của danh sách để tìm tất cả các năm.

4. Bấm vào bất kỳ ô nào trong danh sách.

5. Sử dụng lệnh "Bộ lọc" trong vùng "Dữ liệu" để kích hoạt bộ lọc tự động cho danh sách (mũi tên nhỏ).

   

6. Sử dụng biểu tượng Bộ lọc Tự động trong cột “Năm” mới để chọn mục nhập “2011” và nhấp vào “Ok”.

   

Khi sử dụng các phiên bản Excel trước năm 2007, bạn sẽ tìm thấy lệnh tích hợp bộ lọc tự động trong "Dữ liệu" và "Bộ lọc".

Cách sắp xếp và lọc ngày "từ đến" trong Excel

Bất kể đó là ngày đặt hàng hay đơn hàng đã được hoàn thành, việc lọc các phạm vi ngày nhất định là điều cần thiết để lập kế hoạch nguồn lực chuyên nghiệp. Bằng cách lọc dữ liệu của mình, bạn có thể xem nhanh số lượng đơn đặt hàng hoặc nhiệm vụ rơi vào một khoảng thời gian nhất định.

Ví dụ: Lọc trong bảng Excel theo một ngày cụ thể

Ví dụ: bạn muốn lọc khoảng thời gian từ ngày 30 tháng 11 năm 2022-2023 đến ngày 5 tháng 12 năm 2022-2023 trong bảng được hiển thị.

Với chức năng Bộ lọc Tự động, bạn có thể thực hiện việc này theo các bước sau:

1. Bấm vào bất kỳ ô nào trong danh sách.

2. Chọn lệnh “Bộ lọc” từ tab “Dữ liệu” trong nhóm “Sắp xếp và Bộ lọc” trên ruy-băng.
   (Phiên bản Excel 2003: Kích hoạt lệnh menu Dữ liệu> Bộ lọc> Bộ lọc Tự động)

   

3. Biểu tượng Bộ lọc Tự động xuất hiện ở bên phải tiêu đề cột (mũi tên nhỏ). Nhấp vào biểu tượng bên cạnh "Ngày".

   

4. Trong menu xuất hiện, chọn lệnh "Bộ lọc ngày" và sau đó chọn chức năng "Giữa …".

   

5. Trong danh sách lựa chọn đầu tiên, hãy chọn "sau hoặc bằng" và nhập "30/11/2020" bên cạnh. Trong danh sách thứ hai, chọn "trước hoặc bằng" và nhập 12/05/2022-2023 bên cạnh. Sử dụng tùy chọn “Và”.

   

6. Nhấp vào nút OK để lọc danh sách cho phù hợp.

7. Kết quả là một danh sách được giới hạn trong khoảng thời gian từ ngày 30 tháng 11 năm 2022-2023 và ngày 5 tháng 12 năm 2022-2023. Tất cả các bản ghi dữ liệu không nằm trong khoảng thời gian này đều bị ẩn. Để thay đổi tiêu chí bộ lọc thành một phạm vi ngày khác, hãy gọi lại lệnh Bộ lọc Tự động. Sau đó, đặt các giá trị ngày mới cho giới hạn dưới và giới hạn trên.

   

Ảnh chụp màn hình được chụp bằng Excel phiên bản 2016.

Câu hỏi thường gặp

Làm cách nào để sắp xếp danh sách ngày sinh trong Excel?

Để làm điều này, chỉ cần sử dụng một cột phụ trợ mà bạn cũng giới thiệu. Trong cột bổ trợ này, bạn có thể hiển thị ngày mà không có năm. Sau đó, bạn có thể sử dụng chức năng sắp xếp để sắp xếp các sinh nhật theo đúng thứ tự.

Sắp xếp theo số trong Excel không hoạt động. Tôi có thể làm gì?

Nếu tất cả các tùy chọn để sắp xếp theo số không hoạt động, đôi khi vấn đề là với các mẫu bảng đang hoạt động trong nền. Đầu tiên, hãy cố gắng xóa dần tất cả định dạng của các ô bị ảnh hưởng. Thường thì đây là nơi mà vấn đề được tìm thấy. Nếu cách đó không hiệu quả, chỉ cần sao chép toàn bộ bảng và dán nó vào một trang tính mới bằng cách sử dụng chức năng dán "Giá trị và định dạng nguồn".

Excel không nhận dạng được ngày tháng. Giải pháp là gì?

Đôi khi Excel đứng ngang và chỉ đơn giản là không muốn nhận ra và hiển thị một ngày đã nhập như vậy. Nếu không có gì hoạt động, hãy tạo một cột mới bên cạnh cột cũ. Nhấp chuột phải vào cột mới và chọn "Định dạng ô …". Trong menu mở ra, chọn “Ngày” và nhấp vào “Ok”. Bây giờ hãy chọn và sao chép cột cũ không hoạt động và dán các giá trị đã sao chép vào cột mới bằng cách sử dụng chức năng dán "Giá trị (W)". Sau đó xóa cột cũ.