Tại sao excel sắp xếp số dưới dạng văn bản?

Hãy tưởng tượng bạn có một danh sách nhân viên. Nó có thể có tên, họ và chức danh công việc của họ. Tất cả những thứ này đều dễ sắp xếp xung quanh – chúng đều là văn bản thuần túy. Adam đến trước Bethany. Smith đến sau Johnson. Kế toán đến trước Kỹ thuật viên

Maybe these employees have an employee number that is based on when they were hired or what division they were recruited from. This might have a different structure like “P-”. It might make sense to sort the employees by department number first and then by recruit date… Let’s see if Excel is smart enough to do this on it’s own.

Đang thử sắp xếp bình thường

Chọn các cột dữ liệu bạn muốn bị ảnh hưởng bởi sắp xếp. Nếu bạn đang làm theo ví dụ, thì đó là A. D. Sau đó nhấp vào nút Sắp xếp trên tab Dữ liệu của menu

Chọn sắp xếp theo trường Mã số nhân viên và nhấp vào OK

Hãy xem nó hoạt động tốt như thế nào. Thoạt nhìn, có vẻ như nó đã thực hiện thủ thuật… Các số đầu tiên sau chữ “P” đã được sắp xếp, nhưng khoan đã…

Vì một số lý do, Excel nghĩ rằng “P51” xuất hiện trước “P5”. Và “P42-16” được sắp xếp trước “P42-5”. Sao có thể như thế được?

Vì các ô chứa văn bản cũng như số, nên Excel coi toàn bộ ô như một chuỗi văn bản. Nó sắp xếp theo thứ tự các “chữ cái” xuất hiện thay vì toàn bộ số (e. g. “1” trong “P42-16” đứng trước “5” trong “P42-5”)

Chúng tôi sẽ cần thực hiện thêm một số công việc để Excel thực hiện việc đặt giá thầu của chúng tôi…

Tạo cột sắp xếp

Để giải quyết vấn đề khó chịu khi xử lý các con số trong văn bản của Excel, chúng ta sẽ tạo một vài cột để sắp xếp theo. Họ sẽ giữ nguyên ô ban đầu, nhưng hãy để chúng tôi sắp xếp lại dữ liệu theo cách chúng tôi muốn

Chọn cột bên phải ô hỗn hợp bạn muốn sắp xếp (Mã số nhân viên trong ví dụ này). Nhấp chuột phải vào nhãn cột và nhấp vào Chèn để thêm cột

Nhấn CTRL-Y để lặp lại hành động và thêm một cột khác

Tìm dữ liệu sắp xếp

Chúng ta sẽ sử dụng kết hợp các hàm xem xét dữ liệu văn bản để lấy các số ra khỏi chuỗi hỗn hợp (một bài viết khác sử dụng chiến thuật phân tích chuỗi tương tự để thay đổi ký hiệu). Trước tiên, chúng ta sẽ xử lý số sau chữ “P” trong ví dụ, sử dụng hàm MID() . Cú pháp của MID như sau

=MID(text, start_num, num_chars)

Hàm MID có thể nhận một phần của chuỗi, nhưng chúng ta cần cho nó biết vị trí bắt đầu và kết thúc của chuỗi. Một số số có một chữ số và một số khác có hai chữ số, vì vậy việc tìm kiếm ký tự thứ hai không dễ dàng như vậy. Chúng ta cần tập trung vào những gì làm cho các con số giống nhau. Trong trường hợp này, tất cả chúng đều đứng sau chữ “P” và kết thúc ngay trước dấu “-” (gạch nối)

Ký tự bắt đầu trong trường hợp này luôn là ký tự thứ hai (sau chữ “P”), vì vậy chúng tôi có thể chỉ định trực tiếp ký tự này, vì vậy start_num là 2

Số lượng ký tự trong số đầu tiên mỗi lần là khác nhau, vì vậy chúng ta cần phải lén lút hơn. Chúng ta sẽ đếm số ký tự trước dấu gạch nối bằng hàm FIND(). Cú pháp của FIND như sau

=FIND(find_text, within_text, [start_num])

Trong trường hợp này, chúng tôi đang tìm dấu gạch nối, vì vậy find_text là “-“. Lần này chúng ta không cần start_num

Hàm FIND sẽ báo cáo vị trí của dấu gạch nối trong Mã số nhân viên, nhưng chúng tôi cần cho MID biết số lượng ký tự cần lấy từ chuỗi, vì vậy chúng tôi sẽ phải trừ 2 từ kết quả TÌM. 1 để đứng trước dấu gạch ngang và 1 để giảm chữ “P” ở đầu

Hàm MID sẽ cung cấp một phần của Mã số nhân viên mà chúng ta muốn, nhưng nó vẫn ở dạng văn bản. Khi chúng tôi sắp xếp, chúng tôi cần nó phải là một số hoặc sắp xếp sẽ xử lý nó giống như trong lần thử đầu tiên. Để làm được điều đó, chúng tôi gói nó trong hàm VALUE(). Cú pháp của VALUE rất đơn giản

=VALUE(text)

Công thức cuối cùng cho ô sắp xếp đầu tiên (trong ví dụ B2) như sau

=VALUE(MID( A2 ,2,FIND(“-“, A2)-2))

Công thức phức tạp nên thay vì gõ lại, bạn có thể kéo nó xuống và Excel sẽ tự động thay đổi các ô khi cần thiết

Sắp xếp sau dấu phân cách

Bây giờ chúng ta cần trích xuất số sau dấu gạch nối trong Mã số nhân viên. Chúng tôi sẽ sử dụng một chiến lược tương tự, nhưng chúng tôi cần một vài công thức bổ sung. Chúng ta có thể sử dụng hàm MID, nhưng sẽ rườm rà vì các ô có độ dài khác nhau. Thay vào đó, hàm RIGHT() sẽ kéo các ký tự từ cuối chuỗi. Cú pháp của hàm RIGHT như sau

=RIGHT(text, [num_chars])

Hàm RIGHT cần biết chúng ta cần bao nhiêu ký tự, vì vậy hàm FIND là không đủ. Chúng ta cũng cần biết toàn bộ độ dài của chuỗi để tìm xem có bao nhiêu ký tự sau dấu gạch nối. Để làm được điều đó, chúng ta sẽ sử dụng hàm LEN. Cú pháp của hàm LEN rất dễ

=LEN(text)

Nếu trừ đi vị trí của dấu gạch ngang khỏi tổng độ dài của Mã số nhân viên, thì chúng ta sẽ có chính xác số lượng ký tự cần thiết cho hàm RIGHT. Cuối cùng, chúng tôi bọc toàn bộ công thức trong một hàm VALUE khác để đảm bảo rằng chúng tôi nhận được số thay vì văn bản

Công thức cuối cùng cho ô sắp xếp thứ hai (C2 trong ví dụ) như sau

=VALUE(RIGHT(A2,LEN(A2)-FIND("-",A2)))

Khi bạn chắc chắn rằng nó hoạt động, hãy kéo công thức xuống để điền vào các ô còn lại. Để dễ tìm hơn, bạn có thể đặt tên cho các cột – tôi gọi chúng là “EN Sort 1” và “EN Sort 2“

Bây giờ các cột đã được tạo, chúng ta có thể ẩn chúng khỏi chế độ xem một cách an toàn vì chúng không quan trọng để đọc dữ liệu. Chỉ cần chọn hai cột sắp xếp mới, nhấp chuột phải vào tiêu đề và nhấp vào “Ẩn” từ menu

Sắp xếp với các cột sắp xếp mới

Chọn các cột sắp xếp mới (B. C). Sau đó nhấp vào nút Sắp xếp trên tab Dữ liệu của menu

Chọn sắp xếp theo cột EN Sắp xếp 1. Nhấp vào nút “Thêm cấp độ” để chỉ định tiêu chí sắp xếp thứ hai. Chọn sắp xếp theo cột EN Sort 2 ở cấp độ thứ hai. Sau đó, nhấp vào OK

Kiểm tra dữ liệu của bạn. Xem nó cư xử độc đáo như thế nào?

Ví dụ sắp xếp chữ và số

Dưới đây là ví dụ sổ làm việc từ hướng dẫn. Bạn có thể xem dữ liệu trước khi nó được sắp xếp trên bảng tính đầu tiên và sau đó trên bảng tính thứ hai. Bạn cũng có thể tải xuống tệp mẫu bằng cách nhấp vào biểu tượng Excel ở dưới cùng bên phải

Nhận các mẹo và thủ thuật Excel mới nhất bằng cách tham gia bản tin

Tham gia bản tin để cập nhật những bài viết mới nhất. Đăng ký và bạn sẽ nhận được hướng dẫn miễn phí với 10 phím tắt tiết kiệm thời gian

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

Tại sao Excel cứ thay đổi định dạng ô của tôi thành văn bản?