Bạn cần biết dấu thăng có tác dụng gì trong công thức Excel

[Starlink]

Đập tan băm trong nháy mắt!

    Toán tử phạm vi tràn của Excel (#) cho phép các công thức tự động điều chỉnh theo những thay đổi trong kích thước phạm vi tràn.
    Bạn cần sử dụng Excel cho Microsoft 365 trên Windows hoặc Mac để sử dụng công cụ tiện dụng này.
    Các hàm nổi tiếng như UNIQUE, COUNTIF và SORTBY có thể được sử dụng với toán tử phạm vi tràn để tạo ra các danh sách động, có thể sắp xếp được.

Sử dụng ký hiệu thăng (#)—còn được gọi là toán tử phạm vi tràn—trong công thức Excel là một cách để yêu cầu chương trình xem xét tất cả các kết quả trong phạm vi tràn. Do đó, ngay cả khi phạm vi tràn tăng hoặc giảm, công thức chứa # sẽ tự động phản ánh sự thay đổi này.

1. Tác dụng của ký hiệu thăng (#) trong công thức Excel

Bạn chỉ có thể tận dụng toán tử phạm vi tràn của Excel nếu bạn đang sử dụng Excel cho Microsoft 365 trên Windows hoặc Mac.

Hãy tưởng tượng bạn điều hành một khu bảo tồn động vật và bảng tính của bạn chứa một bảng Excel được định dạng có tên là Animals_Admitted, hiển thị những động vật hiện đang được bạn chăm sóc.

Để có thể tận dụng tối đa không gian có tại khu bảo tồn, bạn cần biết mình có bao nhiêu loài động vật thuộc từng loại và tổng thể bạn có bao nhiêu loài động vật khác nhau.

Vì các hàm bạn sắp sử dụng tạo ra các mảng bị tràn và các mảng bị tràn không hoạt động trong các bảng Excel được định dạng, nên bạn cần nhập công thức vào các vùng trong bảng tính không được định dạng dưới dạng bảng Excel.

Để xem hiện có bao nhiêu loài động vật trong khu bảo tồn của bạn, hãy nhập tiêu đề cột Động vật và Số lượng vào ô D1 và E1 (các ô phía trên nơi chứa mảng đầu tiên của bạn).

Bây giờ, trong ô D2, hãy nhập:

Mã nguồn [Chọn] Expand

=UNIQUE(Animals_Admitted[Animal])
Trong đó UNIQUE là hàm Excel liệt kê các mục duy nhất trong một phạm vi, Animals_Admitted là tên của bảng chứa dữ liệu gốc và [Animal] là tên được gán cho cột thứ ba của bảng đó.

Khi bạn nhấn Enter, bạn sẽ nhận được một mảng tràn liệt kê từng mục duy nhất trong cột Animal. Bạn biết đây là một mảng tràn vì một đường màu xanh bao quanh kết quả bất cứ khi nào bạn chọn một trong các ô bị ảnh hưởng.

Kết quả của hàm UNIQUE được sắp xếp theo thứ tự mà mỗi mục xuất hiện lần đầu tiên trong dữ liệu gốc.

Bây giờ, đã đến lúc Excel đếm số lượng từng loại động vật trong bảng gốc của bạn và đây chính là lúc sử dụng dấu thăng giúp mọi việc dễ dàng hơn rất nhiều.

Để thực hiện việc này, bạn sẽ cần sử dụng hàm COUNTIF. Tuy nhiên, vì bạn muốn Excel đếm tất cả các loài động vật được trả về bởi hàm UNIQUE trong cột D—ngay cả khi một số loài động vật nhất định được thêm vào hoặc xóa khỏi danh sách này sau đó—bạn cần thêm dấu thăng sau tham chiếu tiêu chí.

Vì vậy, trong ô E2, hãy nhập:

Mã nguồn [Chọn] Expand

=COUNTIF(Animals_Admitted[Animal],D2#)
Trong đó COUNTIF là hàm đếm số lần xuất hiện, Animals_Admitted[Animal] là phạm vi chứa từng loài động vật trong bảng gốc của bạn và D2# cho Excel biết rằng tiêu chí cho hàm COUNTIF là một mảng tràn bắt đầu từ ô D2 và do đó có thể thay đổi kích thước.

Thay vì nhập công thức theo cách thủ công, nếu bạn sử dụng chuột để chọn các ô cho mỗi đối số, công thức sẽ tự động áp dụng tên cột (còn gọi là tham chiếu có cấu trúc) và thêm dấu thăng nếu có thể.

Bây giờ, hãy tưởng tượng rằng có một con nhím được đưa vào nơi trú ẩn của bạn và hiện tại đây là con nhím duy nhất bạn đang chăm sóc.

Để thêm một hàng vào bảng đã định dạng, hãy nhấp và kéo nút ở góc dưới bên phải xuống dưới.

Vì bạn đã tham chiếu đến tiêu đề bảng được định dạng trong cột D và sử dụng toán tử phạm vi tràn trong công thức COUNTIF ở cột E, nên hedgehog sẽ tự động được thêm vào danh sách ở cột D và số lượng hedgehog ở cột E hiển thị chính xác là "1".

Bây giờ, bạn muốn tạo một danh sách sắp xếp các loài động vật theo số lượng.

Sau khi nhập các tiêu đề trên vào ô F1 và G1, trong ô F2 hãy nhập:

Mã nguồn [Chọn] Expand

=SORTBY(D2#:E2#,E2#,-1)
Trong đó SORTBY là hàm Excel sắp xếp một phạm vi theo các giá trị trong một phạm vi hoặc mảng khác, D2#:E2# cho Excel biết rằng mảng—chiếm các cột D và E—chứa hai cột mảng tràn bắt đầu từ ô D2 và E2, E2# là mảng tràn để sắp xếp và -1 cho Excel biết sắp xếp dữ liệu theo thứ tự giảm dần.

Kết quả của hàm SORTBY đầu tiên được sắp xếp theo thứ tự bạn chỉ định trong công thức SORTBY. Tuy nhiên, nếu bất kỳ biến nào có cùng giá trị sau lần sắp xếp ban đầu này, thì chúng sẽ được sắp xếp theo thứ tự mà mỗi mục xuất hiện lần đầu trong dữ liệu gốc.

Vì bạn đã sử dụng các dấu thăng đó trong công thức, nên bạn có thể hoàn toàn yên tâm rằng danh sách của bạn sẽ mở rộng và thu hẹp theo dữ liệu trong bảng gốc.

Cuối cùng, bạn cũng cần thống kê cơ bản tổng số loài động vật trong khu bảo tồn của mình.

Để thực hiện việc này, sau khi nhập tiêu đề có liên quan vào ô H1, trong ô H2, hãy nhập

Mã nguồn [Chọn] Expand

=COUNTA(D2#)
Trong đó COUNTA là công thức Excel đếm số ô trong một phạm vi không trống và D2# cho Excel biết rằng phạm vi là một mảng tràn. Nói cách khác, chúng ta đang yêu cầu Excel đếm số lượng động vật mà hàm UNIQUE trả về trong mảng tràn bắt đầu từ ô D2 và số này có thể thay đổi.

Trong bài kiểm tra cuối cùng, hãy giả sử chú rùa London được thả. Sau khi xóa London khỏi dữ liệu gốc, vì chú là chú rùa duy nhất trong khu bảo tồn, ô H2 sẽ cho bạn biết chính xác rằng hiện chỉ có 18 loài động vật duy nhất được bạn chăm sóc và "Turtle" không còn xuất hiện trong các danh sách khác mà bạn đã tạo.

2. Toán tử phạm vi tràn (#): Con trỏ cuối cùng

Trước khi bắt đầu, có một số điểm cần lưu ý.

Đầu tiên, ngoài việc tham chiếu đến một phạm vi tràn trong cùng một bảng tính, bạn cũng có thể tham chiếu đến một phạm vi tràn trong một bảng tính khác. Ví dụ, để tham chiếu đến một mảng tràn bắt đầu từ ô A2 trên bảng tính 2, bạn có thể nhập:

Mã nguồn [Chọn] Expand

=Sheet2!A1#
Thứ hai, bạn có thể tham chiếu một phạm vi tràn trong một sổ làm việc khác. Mặc dù Microsoft nói rằng sổ làm việc được tham chiếu phải mở để không trả về lỗi #REF!, nhưng khi tôi thử nghiệm với một sổ làm việc đã đóng, tham chiếu vẫn tiếp tục hoạt động. Chỉ cần đảm bảo bạn lưu cả hai sổ làm việc và kết nối chúng với cùng một tài khoản OneDrive để đảm bảo bạn không mất bất kỳ dữ liệu nào.

Cuối cùng, để tránh công thức của bạn trả về lỗi #SPILL!, hãy đảm bảo tất cả các ô mà mảng sắp tràn đều không có dữ liệu khác và chưa được hợp nhất.

Bây giờ, cho dù bạn chỉ muốn mở rộng kiến thức về các công thức và hàm của Excel hay đã nộp đơn xin việc đòi hỏi chuyên môn về Excel, bạn có thể tự tin khẳng định rằng bạn biết dấu thăng có tác dụng gì trong các công thức của Microsoft Excel!