Cách lập chỉ mục và sắp xếp giá trị bằng cách sử dụng cột trợ giúp trong Excel.
Trong bài viết này, chúng ta sẽ tìm hiểu Cách lập chỉ mục và sắp xếp giá trị bằng cách sử dụng cột trợ giúp trong Excel.
Tình huống:
Đôi khi làm việc với dữ liệu không người lái. Chúng tôi cần lấy phiên bản ngắn hơn theo dữ liệu yêu cầu từ dữ liệu bảng dựa trên cột trợ giúp.
Ở đây trước tiên chúng ta sẽ hiểu cách lấy cột trợ giúp và sau đó lấy dữ liệu bắt buộc nhỏ được sắp xếp dựa trên dữ liệu bảng.
Làm thế nào để lập chỉ mục các giá trị trong phạm vi?
Công thức chung: –
|
= ( list , “⇐” & f_value ) + ( (list) * (f_value) ) |
list: danh sách các số và văn bản f_value: giá trị đầu tiên của phạm vi Giải thích:
-
Hàm COUNTIF đếm số lượng giá trị trong danh sách trả về. Hàm COUNT nhận số lượng các số trong phạm vi.
-
Hàm ISTEXT kiểm tra xem giá trị trong ô đã chọn có phải là văn bản hay không. Trả về TRUE cho văn bản và FALSE cho số.
Ví dụ:
Tất cả những điều này có thể khó hiểu. Hãy hiểu cách lấy cột trợ giúp bằng công thức được giải thích. Đây là cột trợ giúp dựa trên các giá trị sắp xếp theo thứ tự (số đầu tiên và sau đó theo bảng chữ cái). Đối với điều này, chúng tôi sẽ sử dụng công thức để lấy Chỉ mục hoặc cột xếp hạng làm cột trợ giúp cho dữ liệu Chi phí được hiển thị bên dưới.
Tại đây để tìm các giá trị trong phạm vi. Đối với mục đích tính toán, chúng tôi sử dụng phạm vi được đặt tên cho mảng cố định D5: D12 làm chi phí.
Sử dụng công thức:
|
= ( expense , “⇐” & D5 ) + ( (expense) * (D5) ) |
Công thức trông giống như được hiển thị trong ảnh chụp nhanh ở trên. Giá trị và mảng được cung cấp dưới dạng tham chiếu ô và phạm vi đã đặt tên trong công thức.
Như bạn có thể thấy Chỉ số của ô đầu tiên trong phạm vi là 5, có nghĩa là nếu chúng ta đặt Chi phí theo thứ tự sắp xếp, Điện sẽ được đặt ở vị trí thứ 5. Bây giờ sao chép công thức trong các ô khác bằng cách sử dụng tùy chọn kéo xuống hoặc sử dụng phím tắt Ctrl + D như hình bên dưới để lấy Chỉ mục hoặc xếp hạng cho các giá trị còn lại.
Như bạn có thể thấy, bây giờ chúng ta có một danh sách chỉ số hoặc thứ hạng được sắp xếp theo cách thức của Chi phí. Bây giờ chúng ta sẽ sử dụng cột Xếp hạng này làm cột trợ giúp để có được phiên bản ngắn hơn của bảng có cùng thứ tự trong danh sách hoặc bảng mới.
Làm thế nào để sắp xếp các giá trị bằng cách sử dụng cột trợ giúp trong Excel?
Sắp xếp số trong excel bằng cách sử dụng cột trợ giúp phải có số chỉ mục và phạm vi bắt buộc để sắp xếp. Cột người trợ giúp có thể là bất kỳ thứ tự bắt buộc nào. Ví dụ: nếu bạn cần lấy danh sách theo thứ tự ngẫu nhiên bắt buộc, chỉ cần đặt thứ hạng tương ứng với giá trị trong cột chỉ mục hoặc cột xếp hạng sẽ hoạt động như cột trợ giúp.
Bây giờ chúng ta sẽ tạo công thức bằng các hàm trên. Hàm MATCH sẽ trả về chỉ số của kết quả phù hợp thấp nhất từ phạm vi. Hàm INDEX lấy chỉ mục hàng làm đối số và trả về kết quả bắt buộc tương ứng của nó. Hàm này tìm
Công thức chung:
|
= ( return_array , ( (relative_reference) , Index , 0 ) ) |
return_array: mảng trả về giá trị từ Tham chiếu tương đối: tạo thứ tự tăng dần trong công thức. Có thể được sử dụng với bất kỳ mảng nào Chỉ số: Mảng chỉ số của bảng 0: khớp chính xác Giải thích:
(rel_reference) trả về một giá trị theo độ dài của công thức được mở rộng. Nếu được áp dụng trong ô đầu tiên thì nó sẽ là một, sau đó là thứ hai và tiếp tục cho đến khi nó kéo dài.
-
Hàm MATCH khớp chỉ mục với giá trị hàng để nhận chúng theo thứ tự tăng dần bằng cách sử dụng hàm ROWS.
-
Hàm INDEX trả về Chỉ số phù hợp với giá trị tương ứng.
Ví dụ:
Tất cả những điều này có thể khó hiểu. Vì vậy, chúng ta hãy hiểu công thức này bằng cách chạy nó trên ví dụ được hiển thị bên dưới. Ở đây, chúng tôi đã xếp hạng dữ liệu theo thứ tự ngẫu nhiên để lấy ba giá trị đầu tiên dựa trên cột Chỉ mục. Sử dụng công thức để nhận giá trị đầu tiên của bảng ngắn được yêu cầu.
Sử dụng công thức:
|
= ( B5:B13 , ( ROWS (D5:D5),D5:D13,0)) |
Công thức trông giống như được hiển thị trong ảnh chụp nhanh ở trên. Mảng giá trị được cung cấp dưới dạng phạm vi được đặt tên và tham chiếu ô.
Như bạn có thể thấy giá trị đầu tiên là số từ phạm vi đi ra là “Sữa” chỉ số tương ứng là 1. Bây giờ sao chép công thức vào các ô khác bằng cách sử dụng tùy chọn kéo xuống hoặc sử dụng phím tắt Ctrl + D như hình bên dưới để lấy phần còn lại của các giá trị.
Như bạn có thể thấy, các giá trị được lập chỉ mục theo thứ tự tăng dần. Chúng tôi đã thu được tất cả các giá trị vì nó cũng chứng minh công thức hoạt động tốt. Chúng ta có thể trích xuất các giá trị số bằng cách sử dụng cùng một công thức. Như bạn có thể thấy, chúng tôi có các giá trị ngắn hơn và được sắp xếp từ bảng bằng cách sử dụng cột trợ giúp được lập chỉ mục tương ứng.
Dưới đây là một số lưu ý quan sát được hiển thị bên dưới.
Ghi chú:
-
Công thức chỉ hoạt động với cả số và văn bản.
-
Công thức bỏ qua giá trị văn bản khi so sánh số và bỏ qua số khi khớp giá trị văn bản.
-
Cột trợ giúp có thể là bất kỳ loại thứ tự cột bắt buộc nào.
Hy vọng bài viết này về Cách lập chỉ mục và sắp xếp giá trị với cột trợ giúp trong Excel là giải thích. Khám phá thêm các bài viết về công thức tra cứu trong Excel tại đây. Nếu bạn thích blog của chúng tôi, hãy chia sẻ nó với bạn bè của bạn trên Facebook. Và bạn cũng có thể theo dõi chúng tôi trên Twitter và Facebook. Chúng tôi rất muốn nghe ý kiến của bạn, hãy cho chúng tôi biết cách chúng tôi có thể cải thiện, bổ sung hoặc đổi mới công việc của mình và làm cho nó tốt hơn cho bạn. Viết thư cho chúng tôi tại [email protected]