Hướng dẫn sort excel text and numbers - sắp xếp văn bản excel và số

Nhìn vào dữ liệu

Hãy để tưởng tượng bạn có một danh sách các nhân viên. Nó có thể có tên, họ và tiêu đề công việc của họ. Tất cả những thứ này đều dễ dàng tổ chức xung quanh - tất cả 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.

Có thể những nhân viên này có một số nhân viên dựa trên khi họ được thuê hoặc phân chia mà họ được tuyển dụng. Điều này có thể có một cấu trúc khác nhau như là P- P-. Có thể có ý nghĩa khi sắp xếp các nhân viên theo số bộ phận trước và sau đó là ngày tuyển dụng, hãy để Lừa xem liệu Excel có đủ thông minh để làm điều này trên chính nó hay không.

Thử một loại 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 theo dõi cùng với ví dụ, thì nó & nbsp; a: d. Sau đó nhấp vào nút & nbsp; Sắp xếp trên tab & nbsp; dữ liệu của menu.A:D. Then click on the Sort button on the Data tab of the menu.

Chọn sắp xếp theo trường & nbsp; số nhân viên và nhấp & nbsp; ok.Employee Number field and click OK.

Hãy để xem nó đã hoạt động tốt như thế nào.

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

Vì các ô chứa văn bản cũng như các số, Excel đối xử với toàn bộ ô như chuỗi văn bản. Nó sắp xếp theo thứ tự các chữ cái của người Viking xuất hiện thay vì toàn bộ số lượng (ví dụ: 1 1 trong số P42-16, xuất hiện trước khi 5 5 trong trò chơi P42-5).

Chúng tôi sẽ cần phải làm thêm một chút để làm cho Excel thực hiện đấu thầu của chúng tôi

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

Để có được cách xử lý các con số khó chịu của Excel, trong văn bản, chúng tôi sẽ tạo ra một vài cột để sắp xếp. Họ sẽ giữ cho ô ban đầu không thay đổi, nhưng chúng ta hãy sắp xếp lại dữ liệu chúng tôi thích như thế nào.

Chọn cột ở bên phải của ô hỗn hợp bạn muốn sắp xếp (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 & nbsp; chèn & nbsp; để thêm một cột.Employee Number in this example). Right-click the column labels and click Insert to add a column.

Nhấn Ctrl-y để lặp lại hành động và thêm một cột khác.CTRL-Y to repeat the action and add another column.

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

Chúng tôi sẽ sử dụng kết hợp các hàm xem dữ liệu văn bản để rút 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 cú pháp chuỗi tương tự để thay đổi ký hiệu). Đầu tiên, chúng tôi sẽ giải quyết số sau khi P P trong ví dụ, sử dụng chức năng & nbsp; mid () & nbsp; Cú pháp cho & nbsp; mid như sau:MID() function. The syntax for MID is as follows:

=MID(text, start_num, num_chars)

Mid & nbsp; function & nbsp; có thể tham gia một phần của chuỗi, nhưng chúng ta cần nói nó bắt đầu và kết thúc ở đâu. ký tự thứ hai. Chúng ta cần tập trung vào những gì làm cho các con số tương tự nhau. Trong trường hợp này, tất cả họ đều đến sau khi P P và kết thúc ngay trước khi một--(dấu gạch nối).MID function can take part of a string, but we need to tell it where to start and finish. Some of the numbers are single-digit and others are double-digit, so it’s not as easy as looking for the second character. We need to focus on what makes the numbers similar. In this case, they all come after the “P” and end right before a “-” (hyphen).

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

Số lượng ký tự trong số đầu tiên là khác nhau mỗi lần, vì vậy chúng ta cần lén lút hơn. Chúng tôi sẽ đếm số lượng ký tự trước khi Hyphen sử dụng hàm Find (). Cú pháp cho & nbsp; tìm như sau:FIND() function. The syntax for FIND is as follows:

=FIND(find_text, within_text, [start_num])

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

Hàm & nbsp; find & nbsp; sẽ báo cáo vị trí của dấu gạch nối trong số nhân viên, nhưng chúng ta cần nói & nbsp; mid có bao nhiêu ký tự để lấy từ chuỗi, vì vậy chúng ta sẽ phải trừ 2 từ & nbsp; tìm kết quả: 1 Để có được trước dấu gạch nối và 1 để giảm giá cho p p ở đầu.FIND function is going to report the location of the hyphen in the Employee Number, but we need to tell MID how many characters to pull from the string, so we’ll have to subtract 2 from the FIND result: 1 to get in front of the hyphen and 1 to discount the “P” at the beginning.

Chức năng Mid & nbsp; sẽ cung cấp một phần của số nhân viên mà chúng tôi muốn, nhưng nó vẫn sẽ là văn bản. Khi chúng tôi sắp xếp, chúng tôi cần nó là một số, hoặc loại sẽ đối xử với nó giống như nó đã làm trong lần thử đầu tiên. Đối với điều đó, chúng tôi bọc nó trong hàm A & nbsp; value (). Cú pháp cho & nbsp; giá trị rất đơn giản:MID function is going to provide the part of the Employee Number that we want, but it’s still going to be text. When we sort, we need it to be a number, or the sort will treat it the same as it did in the first attempt. For that, we wrap it in a VALUE() function. The syntax for VALUE is simple:

=VALUE(text)

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

= Value (mid (a2,2, find (Hồi-,, a2) -2)))VALUE(MID(A2,2,FIND(“-“,A2)-2))

Công thức rất phức tạp, vì vậy 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 khi phân tách

Bây giờ chúng ta cần trích xuất số sau dấu gạch nối trong 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 tôi có thể sử dụng chức năng & nbsp; giữa, nhưng nó sẽ rất cồng kềnh vì các tế bào có độ dài khác nhau. Thay vào đó, hàm & nbsp; right () sẽ kéo các ký tự từ cuối chuỗi. Cú pháp cho chức năng & nbsp; đúng như sau:Employee Number. We’re going to use a similar strategy, but we need a couple additional formulas. We could use the MID function, but it would be cumbersome since the cells are different lengths. Instead, the RIGHT() function will pull characters from the end of a string. The syntax for the RIGHT function is as follows:

=RIGHT(text, [num_chars])

Hàm & nbsp; đúng cần biết chúng ta cần bao nhiêu ký tự, vì vậy A & nbsp; tìm chức năng là không đủ. Chúng ta cũng cần biết toàn bộ độ dài của chuỗi để tìm hiểu có bao nhiêu ký tự sau dấu gạch nối. Đối với điều đó, chúng tôi sẽ sử dụng chức năng & nbsp; LEN. Cú pháp cho chức năng & NBSP; LEN rất dễ dàng:RIGHT function needs to know how many characters we need, so a FIND function isn’t enough. We also need to know the entire length of the string to find out how many characters are after the hyphen. For that, we’ll use the LEN function. The syntax for the LEN function is easy:

=LEN(text)

Nếu chúng tôi trừ vị trí của dấu gạch nối từ tổng độ dài của số nhân viên, chúng tôi sẽ có số lượng ký tự chính xác cần thiết cho chức năng phù hợp. Cuối cùng, chúng tôi bọc toàn bộ công thức trong một hàm giá trị khác & nbsp; để đảm bảo chúng tôi nhận được số thay vì văn bản.Employee Number, we’ll have the exact number of characters needed for the RIGHT function. Finally, we wrap the entire formula in another VALUE function to make sure we get the number instead of the text.

Công thức cuối cùng cho ô sắp xếp thứ hai (C2 & NBSP; trong ví dụ) như sau:2 in the example) is as follows:

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

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

Bây giờ các cột được tạo, chúng ta có thể ẩn chúng 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 ẩn từ menu.Hide” from the 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 & nbsp; sort & nbsp; trên tab & nbsp; data & nbsp; tab của menu.B:C). Then click on the Sort button on the Data tab of the menu.

Chọn sắp xếp theo cột & nbsp; en sort 1 & nbsp; cột. 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 & nbsp; en sort 2 & nbsp; ở cấp độ thứ hai. Sau đó, nhấp & nbsp; ok.EN Sort 1 column. Click the “Add Level” button to specify a second sort criteria. Choose to sort by the EN Sort 2 column in the second level. Then, click OK.

Kiểm tra dữ liệu của bạn. Xem nó cư xử đẹp như thế nào? Làm tốt lắm!

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

Dưới đây là ví dụ về sách bài tập 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 bảng tính thứ hai. & NBSP; 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 ở phía dưới 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 để ở trên đầu các 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 các số sắp xếp Excel là văn bản?

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