Excel không sắp xếp số đúng công thức

Dữ liệu của tôi đã được sao chép vào một trang tính mới và sau đó tại một số điểm được sao chép lại. Không có bộ lọc hiển thị nào và nó được sắp xếp chính xác cho đến khi tôi thêm hai hàng dữ liệu. Tại thời điểm đó, trừ khi tôi sắp xếp từ nơi khác trong trang tính thay vì từ tiêu đề, nó sẽ thiếu hai hàng. Cuối cùng, tôi đã chuyển đến hộp thoại Bộ lọc nâng cao và thấy rằng một phạm vi đã được nhập vào một thời điểm nào đó và nó được chuyển sang mặc dù không còn bộ lọc nào. khó hiểu. Khi tôi cập nhật phạm vi để bao gồm hai hàng bổ sung của mình, phạm vi đó đã được sắp xếp chính xác. Tôi không thể xóa cài đặt Bộ lọc nâng cao, vì vậy tôi đã học cách làm việc với cài đặt này khi thêm hàng. Tôi tin rằng nếu tôi sao chép dữ liệu sang một trang tính khác mà không có tiêu đề, tôi có thể giải quyết được sự cố. Một thử nghiệm cho một thời điểm khác

Tuần trước, tôi nghe được từ một người đang gặp sự cố khi sắp xếp một số số trong Excel. Anh ấy đã gửi cho tôi một tệp mẫu nhỏ hiển thị một số ngày và số không được sắp xếp chính xác

Dự đoán đầu tiên của tôi là dữ liệu đã được sao chép từ một trang web - điều đó có thể gây ra một số hành vi lạ khi bạn dán dữ liệu đó vào Excel. Kiểm tra nhanh bằng hàm COUNT và COUNTA cho thấy không có giá trị nào trong ô C3. C6 là số thực – chúng là văn bản

Nỗ lực khắc phục nhanh số 1

Có một trang trên trang web của tôi hiển thị một vài giải pháp để sửa các số không cộng lại hoặc sắp xếp không chính xác

Một cách khắc phục nhanh, luôn hoạt động hiệu quả, là sao chép một ô trống, sau đó dán vào các ô số, sử dụng thao tác Thêm

Nó hoạt động hầu hết thời gian, nhưng không hoạt động trên những con số này

Nỗ lực khắc phục nhanh #2

Điều tiếp theo tôi đã thử là Chuyển văn bản thành cột. Thật nhanh chóng và dễ dàng, nhưng không thay đổi những số "văn bản" này thành số thực

Đào sâu hơn

Khi các bản sửa lỗi nhanh không thành công, tôi đoán rằng có các ký tự ẩn trong các ô và hàm LEN cho thấy dự đoán của tôi là chính xác. Mỗi ô có 5 ký tự – không chỉ có 3 số hiển thị. Bạn có thể xem kết quả LEN ở cột D, trong ảnh chụp màn hình bên dưới

Tiếp theo, tôi sử dụng hàm CODE, với hàm MID, để hiển thị từng ký tự trong chuỗi. Đây là công thức trong ô E3

=IFERROR[CODE[MID[$C3,E$2,1]],””]

Tôi đã sao chép công thức và tìm ra thủ phạm – có hai ký tự ẩn ở cuối mỗi số, với mã 160 – một khoảng trắng không phá vỡ

Tìm và thay thế các khoảng trống không bị phá vỡ

Để nhanh chóng loại bỏ các khoảng trắng không ngắt, bạn có thể sử dụng tính năng Tìm và Thay thế

• Chọn các ô có chứa các ký tự ẩn
• Trên Ribbon, bấm vào tab Trang đầu
• Nhấp vào nút Tìm & Chọn [ở ngoài cùng bên phải]
• Nhấp vào lệnh Thay thế
• Đối với Tìm gì, nhấn phím Alt và trên bàn phím số, nhấn 0160 [không có gì xuất hiện trong hộp]
  LƯU Ý. Nếu bạn đang sử dụng bàn phím không có bàn phím số riêng, hãy sử dụng các phím đặc biệt để kích hoạt phần “bàn phím số” của bàn phím
• Để trống hộp “Thay thế bằng”
• Nhấp vào Thay thế tất cả

Khắc phục bằng Macro

Nếu thường xuyên gặp sự cố này, bạn có thể sử dụng macro để thay thế tất cả các khoảng trắng không ngắt bị ẩn trong một dải ô đã chọn. Có mã mẫu trên trang web của tôi và bạn có thể sao chép mã đó vào sổ làm việc của riêng mình và chạy mã đó khi cần thiết

Vào link này để lấy code. Sửa Kí Tự Ẩn Bằng VBA

Xem video

Để xem chức năng CODE hoạt động như thế nào, trong một ví dụ khác, bạn có thể xem video ngắn này

Và để biết thêm các mẹo sắp xếp nâng cao trong Excel, hãy truy cập trang Sắp xếp nâng cao bổ trợ Excel trên trang Ngữ cảnh của tôi

Cách sắp xếp danh sách trong Excel theo hàng hoặc cột. Mẹo chỉ ra cách tránh những sai lầm đau đớn khi sắp xếp bảng tính của bạn. Cách sắp xếp nhiều cột, hàng hoặc sắp xếp theo thứ tự tùy chỉnh. Video và sổ làm việc Excel

Tránh rắc rối sắp xếp. Kiểm tra dữ liệu của bạn

1] Tạo bản sao lưu dữ liệu của bạn

Đảm bảo tạo một bản sao lưu tệp Microsoft Excel của bạn, trước khi bạn bắt đầu sắp xếp dữ liệu. Sau đó, bạn có thể quay lại phiên bản đã lưu, nếu có vấn đề gì xảy ra

Mẹo. Để có bản sao lưu nhanh chóng và dễ dàng, hãy tải công cụ Sao lưu Excel miễn phí của tôi. Công cụ này tạo một bản sao lưu trong thư mục hiện tại và KHÔNG ảnh hưởng đến sổ làm việc đang hoạt động. Công cụ sao lưu có định dạng xlam nên rất dễ cài đặt trên máy tính của bạn - giống như bất kỳ tệp Excel nào khác

2] Kiểm tra dữ liệu của bạn

Trước khi bạn sắp xếp dữ liệu trong Excel, hãy đảm bảo không có hàng hoặc cột trống nào trong phạm vi dữ liệu trên trang tính. Các bước dưới đây cho thấy làm thế nào để làm điều đó

Tại sao việc kiểm tra dữ liệu của bạn lại quan trọng?

• Nếu có một hàng trống hoặc cột trống trong dữ liệu, một phần dữ liệu có thể được sắp xếp, trong khi dữ liệu khác không được sắp xếp
• Bạn có thể kết thúc với tên và số điện thoại không khớp hoặc đơn đặt hàng có địa chỉ khách hàng sai
• Kiểm tra các công thức bao gồm tên trang tính, trong tham chiếu đến các ô trên trang tính hiện tại. Để biết ví dụ về công thức, hãy xem Bài toán sắp xếp VLOOKUP bên dưới

Cách kiểm tra hàng hoặc cột trống

Để giúp ngăn chặn sự cố sắp xếp, hãy làm theo các bước sau trước khi bạn sắp xếp dữ liệu Excel

1. Chọn một ô trong cột bạn muốn sắp xếp
2. Nhấn Ctrl + A, để chọn toàn bộ vùng
3. Kiểm tra khu vực đã chọn để đảm bảo rằng tất cả dữ liệu được bao gồm
   • Ví dụ: trong ảnh chụp màn hình bên dưới, cột ẩn E trống
   • Sau khi nhấn Ctrl+A, các cột F, G [ẩn], H và I không được chọn

3] Sửa hàng và cột trống

1. Nếu một số dữ liệu KHÔNG được chọn, hãy tìm các hàng hoặc cột trống
• Bạn có thể cần bỏ ẩn các hàng hoặc cột để tìm các hàng hoặc cột trống
2. Sau khi bạn tìm thấy một hàng hoặc cột trống
   • Nếu hàng hoặc cột trống không cần thiết, hãy xóa nó
   • Nếu cần hàng hoặc cột trống, hãy nhập ít nhất một mục vào hàng hoặc cột đó
     • Ví dụ: nhập "x" vào tiêu đề cột, làm trình giữ chỗ

Sau đó, sau khi bạn sửa bất kỳ cột hoặc hàng trống nào

• Nhấn Ctrl + A lần nữa để xem toàn bộ vùng đã được chọn chưa
  • Nếu không, hãy tìm các hàng hoặc cột trống khác và xóa hoặc điền chúng
  • Nếu toàn bộ khu vực được chọn, dữ liệu có thể được sắp xếp một cách an toàn

Sắp xếp nhanh với các nút sắp xếp

Trong Excel, bạn có thể nhanh chóng sắp xếp dữ liệu của mình bằng cách sử dụng các nút A-Z [thứ tự tăng dần] và ZA [thứ tự giảm dần] trên tab Dữ liệu của Dải băng

Làm theo các bước sau để sắp xếp bằng các nút lệnh Quick Sort

1. Chọn một ô trong cột bạn muốn sắp xếp
2. Trên Dải băng Excel, bấm vào tab Dữ liệu
3. Nhấp vào Sắp xếp từ A đến Z [nhỏ nhất đến lớn nhất] hoặc Sắp xếp Z đến A [lớn nhất đến nhỏ nhất]

Kiểm tra kết quả

Ngay sau khi sắp xếp và trước khi bạn làm bất cứ điều gì khác

• Kiểm tra dữ liệu đã sắp xếp, xem dữ liệu đã được sắp xếp đúng chưa

Nếu bất cứ điều gì có vẻ không chính xác, hoặc không theo thứ tự

• Nhấp ngay vào nút Hoàn tác trên thanh công cụ

Sắp xếp các nút trên Thanh công cụ truy cập nhanh

Nếu bạn sắp xếp thường xuyên, bạn có thể thêm các nút Sắp xếp vào Thanh công cụ truy cập nhanh [QAT] của mình. Điều đó làm cho việc sắp xếp nhanh trong Excel trở nên dễ dàng hơn. Có các bước "cách thực hiện" trên trang Thanh công cụ truy cập nhanh. chỉ ra cách thêm các nút

Sau đó, để sử dụng các biểu tượng Sắp xếp đó, hãy làm theo các bước trong phần Sắp xếp nhanh bằng các nút A-Z, nhưng thay vào đó hãy sử dụng các nút QAT

Sắp xếp hai hoặc nhiều cột

Nếu bạn muốn sắp xếp 2 hoặc nhiều cột trong bảng Excel, hãy sử dụng hộp thoại Sắp xếp, nơi bạn có thể thiết lập sắp xếp nhiều cấp

Trong ví dụ này, bảng chứa dữ liệu cá nhân và nó sẽ được sắp xếp theo 3 cột

1. Đầu tiên, theo giới tính
2. Tiếp theo, theo tiểu bang
3. Cuối cùng, theo năm sinh

Hãy làm theo các bước sau để sắp xếp tập hợp dữ liệu theo 3 cột một cách an toàn

1. Chọn tất cả các ô trong danh sách.
   Đây là cách sắp xếp an toàn nhất. Trong hầu hết các trường hợp, bạn có thể chọn một ô và Excel sẽ phát hiện chính xác phần còn lại của danh sách -- nhưng điều đó không chắc chắn 100%. Một số dữ liệu có thể bị bỏ sót.
2. Trên Dải băng Excel, bấm vào tab Dữ liệu
3. Trong nhóm Sắp xếp & Lọc, bấm vào nút Sắp xếp
4. Nhấn nút Add Level để thêm bậc sắp xếp đầu tiên
5. Từ trình đơn thả xuống Sắp xếp theo, chọn cột đầu tiên bạn muốn sắp xếp. Trong ví dụ này, Giới tính sẽ là cột đầu tiên được sắp xếp

Ghi chú. Nếu danh sách thả xuống hiển thị các chữ cái Cột thay vì tiêu đề, hãy thêm dấu kiểm vào Dữ liệu của tôi có tiêu đề

1. Từ trình đơn thả xuống Sort On, hãy chọn tùy chọn mà bạn muốn. Chúng tôi đang sắp xếp các giá trị trong cột Giới tính, vì vậy hãy để cài đặt mặc định là Giá trị
2. Tiếp theo, từ trình đơn thả xuống Đơn hàng, hãy chọn một trong các tùy chọn. Danh sách các tùy chọn Thứ tự sẽ phụ thuộc vào những gì bạn đã chọn trong cột Sort On. Bởi vì chúng tôi đã chọn các giá trị, các tùy chọn Thứ tự là A đến Z, Z đến A và Danh sách tùy chỉnh. Chúng tôi sẽ chọn từ A đến Z
3. Nếu bạn đang sắp xếp trên nhiều cột, hãy nhấp vào nút Thêm cấp độ để thêm cấp độ tiếp theo và chọn các tùy chọn từ các hộp thả xuống của nó

   Ở đây, chúng tôi đã chọn Giới tính, Bang và Năm sinh làm các trường sắp xếp và tất cả đều được sắp xếp theo Giá trị. Vì cột BirthYr chỉ chứa các số nên các tùy chọn Thứ tự của nó hơi khác so với các tùy chọn cột văn bản

4. Sau khi bạn đã chọn tất cả các mức Sắp xếp và các tùy chọn của chúng, hãy nhấp vào OK

Dữ liệu sẽ được sắp xếp theo thứ tự mà bạn đã chỉ định. Trong ảnh chụp màn hình bên dưới

• Cột giới tính được sắp xếp đầu tiên, vì vậy tất cả tên nữ đều ở trên cùng
• Tiếp theo, cột Bang được sắp xếp, vì vậy phụ nữ từ Alabama đứng đầu danh sách
• Cuối cùng, BirthYr được sắp xếp, với năm sinh sớm nhất ở đầu mỗi tiểu bang

Sắp xếp theo thứ tự tùy chỉnh

Trong hộp thoại Sắp xếp hoặc trên Dải băng Excel, bạn có thể chọn thứ tự sắp xếp, chẳng hạn như từ A đến Z hoặc Lớn nhất đến Nhỏ nhất. Ngoài các tùy chọn tiêu chuẩn này, bạn có thể sắp xếp theo thứ tự tùy chỉnh, chẳng hạn như thứ tự tháng hoặc thứ tự ngày trong tuần. Trong ví dụ này, chúng tôi sẽ sắp xếp một cột có tên ngày trong tuần, sử dụng lệnh Ribbon Excel

Xem các bước để thực hiện sắp xếp tùy chỉnh trong video Sắp xếp thứ tự tùy chỉnh hoặc làm theo hướng dẫn bằng văn bản bên dưới video

Sắp xếp theo thứ tự tùy chỉnh

Trong hộp thoại Sắp xếp hoặc trên Dải băng Excel, bạn có thể chọn thứ tự sắp xếp, chẳng hạn như từ A đến Z hoặc Lớn nhất đến Nhỏ nhất. Ngoài các tùy chọn tiêu chuẩn này, bạn có thể sắp xếp theo thứ tự tùy chỉnh, chẳng hạn như thứ tự tháng hoặc thứ tự ngày trong tuần. Trong ví dụ này, chúng tôi sẽ sắp xếp một cột có tên ngày trong tuần, sử dụng lệnh Ribbon Excel

Để sắp xếp theo thứ tự tùy chỉnh, hãy làm theo các bước sau

1. Chọn một ô trong cột bạn muốn sắp xếp
2. Nhấn Ctrl + A, để chọn toàn bộ vùng
3. Kiểm tra khu vực đã chọn để đảm bảo rằng tất cả dữ liệu được bao gồm
4. Trên Dải băng Excel, bấm vào tab Trang đầu
5. Trong nhóm Chỉnh sửa, nhấp vào mũi tên trên Sắp xếp & Lọc
6. Nhấp vào Đơn đặt hàng tùy chỉnh
7. Trong hộp thoại Sắp xếp, chọn cột Ngày trong hộp Sắp xếp theo
8. Từ trình đơn thả xuống Đơn hàng, chọn Danh sách tùy chỉnh
9. Trong hộp thoại Tùy chỉnh, chọn một danh sách tùy chỉnh rồi bấm OK hai lần để đóng hộp thoại

Cột Ngày được sắp xếp theo thứ tự ngày trong tuần, thay vì thứ tự bảng chữ cái, vì vậy Chủ nhật xuất hiện ở đầu danh sách

Sắp xếp một hàng

Thay vì sắp xếp dữ liệu theo cột, bạn có thể sắp xếp dữ liệu theo hàng. Trong ví dụ này, chúng tôi sẽ sắp xếp bảng doanh số hàng tháng, vì vậy tháng có tổng doanh số lớn nhất ở bên trái. Để làm điều này, chúng tôi sẽ sử dụng menu bật lên nhấp chuột phải

Bạn có thể xem các bước trong video Sắp xếp theo hàng ngắn này và đọc hướng dẫn chi tiết bên dưới

Sắp xếp một hàng

Thay vì sắp xếp dữ liệu theo cột, bạn có thể sắp xếp dữ liệu theo hàng. Trong ví dụ này, chúng tôi sẽ sắp xếp bảng doanh số hàng tháng, vì vậy tháng có tổng doanh số lớn nhất ở bên trái. Để làm điều này, chúng tôi sẽ sử dụng menu bật lên nhấp chuột phải

Để sắp xếp theo hàng, hãy làm theo các bước sau

1. Chọn một ô trong hàng bạn muốn sắp xếp
2. Nhấn Ctrl + A, để chọn toàn bộ vùng
3. Kiểm tra khu vực đã chọn để đảm bảo rằng tất cả dữ liệu được bao gồm
4. Nhấp chuột phải vào một ô trong hàng mà bạn muốn sắp xếp
5. Trong menu bật lên, nhấp vào Sắp xếp, sau đó nhấp vào Sắp xếp tùy chỉnh
6. Trong hộp thoại Sắp xếp, chọn cột Ngày trong hộp Sắp xếp theo
7. Từ trình đơn thả xuống Đơn hàng, chọn Danh sách tùy chỉnh
8. Ở đầu hộp thoại Sắp xếp, bấm vào nút Tùy chọn
9. Trong hộp thoại Tùy chọn sắp xếp, bên dưới Định hướng, chọn Sắp xếp từ trái sang phải
10. Bấm OK, để đóng hộp thoại Tùy chọn
11. Từ trình đơn thả xuống Sắp xếp theo, hãy chọn hàng bạn muốn sắp xếp
12. Không có tiêu đề nào, vì vậy hãy chọn số Hàng chính xác
13. Chọn tùy chọn Sort On và Order, sau đó bấm OK

Dữ liệu được sắp xếp theo các giá trị trong hàng đã chọn.

Sắp xếp dữ liệu theo biểu tượng ô

Xem video ngắn này để biết các bước thêm biểu tượng ô và sắp xếp theo biểu tượng của ô đã chọn. Có ghi các bước bên dưới video

Khi bạn tạo một bảng Excel đã đặt tên hoặc áp dụng Bộ lọc Tự động cho một danh sách, mỗi ô tiêu đề sẽ có một mũi tên thả xuống. Nhấp vào mũi tên đó và bạn sẽ thấy nhiều tùy chọn sắp xếp và lọc dữ liệu

Nếu bạn thêm các biểu tượng định dạng có điều kiện vào một trong các cột, bạn cũng có thể sắp xếp theo các biểu tượng đó. Trong ảnh chụp màn hình bên dưới, các biểu tượng đèn giao thông đang được thêm vào cột Số lượng

Sắp xếp theo biểu tượng ô đã chọn

Sau khi thêm các biểu tượng, cách nhanh nhất để sắp xếp theo một biểu tượng cụ thể là

1. Nhấp chuột phải vào ô chứa biểu tượng bạn muốn ở đầu danh sách
2. Trong menu bật lên, nhấp vào Sắp xếp
3. Nhấp vào Đặt biểu tượng ô đã chọn lên trên cùng

Danh sách được sắp xếp, để di chuyển tất cả các mục có biểu tượng đã chọn lên đầu danh sách

Các mục khác không được sắp xếp và các mục đã được chuyển lên đầu danh sách được để lại theo thứ tự ban đầu, trong nhóm đó

Sắp xếp theo tiêu đề Danh sách thả xuống

Sau khi bạn đã thêm các biểu tượng ô, bạn cũng có thể sắp xếp theo biểu tượng từ danh sách thả xuống trong tiêu đề

1. Nhấp vào mũi tên thả xuống trong ô tiêu đề
2. Nhấp vào Sắp xếp theo màu
3. Bấm vào một biểu tượng, để di chuyển nó lên đầu danh sách

Vấn đề sắp xếp VLOOKUP

Lúc đầu, công thức VLOOKUP có thể trả về kết quả đúng, nhưng sau đó hiển thị kết quả không chính xác nếu danh sách các mục được sắp xếp. Điều này có thể xảy ra nếu tham chiếu đến giá trị Tra cứu bao gồm tên trang tính. Ví dụ

=VLOOKUP['Phiếu đặt hàng'. B5, Sản phẩm. $B$2. $C$6,2,SAI]

GHI CHÚ. Sự cố này cũng có thể xảy ra với các hàm khác, chẳng hạn như công thức tra cứu INDEX/MATCH

Xem video này để biết các bước khắc phục sự cố và tải xuống tệp mẫu bài toán Sắp xếp VLOOKUP để làm theo. Hướng dẫn bằng văn bản có trên trang Cách sử dụng Excel VLOOKUP - Ví dụ

Sắp xếp sổ làm việc mẫu

Để thử các kỹ thuật sắp xếp, hãy lấy sổ làm việc mẫu Sắp xếp. Tệp ở định dạng tệp xlsx và được nén. Nó không chứa bất kỳ macro nào

Tại sao Excel không sắp xếp số chính xác?

Sắp xếp có làm rối công thức trong Excel không?

Tại sao Excel của tôi không lọc đúng cách?