Được xuất bản vào ngày 30 tháng 4 năm 2026, 10:30 sáng EDT
Yasir là Kỹ sư cơ khí, người viết về công nghệ tại MUO, bao gồm Windows, Năng suất, Bảo mật và Internet. Niềm đam mê với các hệ thống tự động khiến anh không ngừng mày mò cả phần cứng và phần mềm.
Hành trình viết lách về công nghệ của anh bắt đầu từ năm cuối cấp kỹ sư, đưa anh đến với Android Police trước khi gia nhập MUO. Anh ấy tập trung vào việc làm cho công nghệ có thể truy cập được, cho dù anh ấy đang khắc phục sự cố của Windows, khám phá các công cụ năng suất hay giải thích các rủi ro bảo mật bằng tiếng Anh đơn giản. Đối với Yasir, cách tốt nhất để làm điều này là thực sự sử dụng các công cụ và gặp phải những vấn đề tương tự mà độc giả gặp phải.
Khi anh ấy không viết lách hay kỹ thuật, bạn sẽ thấy Yasir đang xem Những trò đùa phi thực tế và thực sự cười lớn trước những trò đùa mà anh ấy đã thấy hàng chục lần trước đây.
Hầu hết người dùng Excel mà tôi biết đều học các công thức giống như cách tôi đã làm — mỗi lần một hàm, xếp chồng lên những hàm họ đã biết. Hàm mảng động không thay thế những kỹ năng đó; họ chỉ khiến nhiều cách giải quyết trở nên không cần thiết. Tôi đã chạy 5 danh sách hàng đầu tự cập nhật của mình bằng TAKE và DROP được một thời gian và sự thay đổi tương tự cũng đã xảy ra với 4 chức năng bên dưới. Mỗi bước rút gọn quy trình gồm nhiều bước mà tôi thường thực hiện theo phản xạ thành một công thức duy nhất.
Liên quan
FILTER đã thay thế toàn bộ nghi thức gồm các cột trợ giúp và công thức mảng
Bây giờ một công thức thực hiện những gì nhiều hàm dùng để phân chia giữa chúng
Việc kéo các hàng khớp trong Excel cũ hơn có nghĩa là xây dựng công thức INDEX, MATCH, SMALL và IFERROR lồng nhau và nhập công thức đó bằng Ctrl + Shift + Enter. Nó đã hoạt động, nhưng việc duy trì nó sau này là một vấn đề. Tùy chọn khác là áp dụng Bộ lọc Tự động, sao chép các hàng hiển thị và dán chúng vào nơi khác dưới dạng giá trị tĩnh. Điều đó cũng ổn cho đến khi dữ liệu nguồn thay đổi.
Hàm FILTER thực hiện công việc tương tự trên một dòng. Trong bảng tính bán hàng của tôi, có 32 hàng theo khu vực, danh mục sản phẩm và nhân viên bán hàng, mỗi lần bán hàng Điện tử từ khu vực phía Tây trông như thế này:
=FILTER(A2:G33, (B2:B33="West")*(C2:C33="Electronics"))
Đối số đầu tiên là phạm vi bạn muốn trả về. Thứ hai là điều kiện và phép nhân giữa hai phép kiểm tra đóng vai trò là AND - cả hai đều phải đúng. Việc chuyển dấu hoa thị sang dấu cộng sẽ biến nó thành OR và kết quả sẽ tự động tràn ra. Việc thêm hàng mới vào nguồn sẽ cập nhật kết quả đầu ra bị tràn ngay khi bạn nhấn Enter.
Bạn có thể gói FILTER trong IFERROR bằng đối số thứ ba để xử lý các kết quả trống. =FILTER(range, condition, "No matches") giữ cho trang tính của bạn không hiển thị #CALC! lỗi khi không có hàng nào đủ điều kiện.
UNIQUE đã biến quy trình sao chép ba bước của tôi thành một ô duy nhất
Remove Duplicates thì tốt nhưng nó chưa bao giờ tự cập nhật cho tôi
Tùy chọn Xóa trùng lặp trong tab Dữ liệu phù hợp cho việc dọn dẹp một lần. Điều hấp dẫn là nó tạo ra một danh sách tĩnh. Khi bạn sao chép một cột sang vị trí mới, chạy hộp thoại hoặc sắp xếp kết quả, bạn sẽ phải làm lại tất cả những điều đó vào lần tiếp theo khi ai đó thêm hàng. Tôi đã làm điều đó nhiều lần đến mức không thể đếm nổi.
UNIQUE bỏ qua mọi bước đó. Trỏ nó vào cột nhân viên bán hàng trong bảng tính bán hàng của tôi trông như thế này:
=UNIQUE(D2:D33)
Kết quả là John Smith, Sarah Johnson, Mike Wilson, Lisa Brown, David Chen, Emma Davis, Tom Rodriguez và Amy Foster. Đó là tám cái tên không có hộp thoại. Gói nó dưới dạng =SORT(UNIQUE(D2:D33)) trả về cùng một danh sách được sắp xếp theo thứ tự bảng chữ cái. Đầu ra vẫn được kết nối với nguồn nên việc thêm tên mới vào dữ liệu sẽ tự động mở rộng danh sách tràn.
Thiết lập này cũng tạo nguồn sạch cho danh sách thả xuống xác thực dữ liệu. Nếu bạn tham chiếu phạm vi bị đổ bằng hàm băm như D35# , danh sách thả xuống của bạn sẽ tự phát triển khi tên mới xuất hiện trong dữ liệu nguồn.
SORTBY đã kết thúc việc xáo trộn sao chép-dán và sắp xếp của tôi vĩnh viễn
Sắp xếp chế độ xem mà không cần chạm vào dữ liệu nguồn
Việc sắp xếp trong Excel luôn tiềm ẩn một rủi ro nhỏ. Việc sắp xếp lại nguồn có thể phá vỡ các công thức tham chiếu các hàng cố định, vì vậy giải pháp dự phòng của tôi là sao chép một đoạn ở nơi nào đó an toàn và sắp xếp bản sao. SORT có ích nhưng chỉ khi tôi hài lòng hiển thị mọi cột mà tôi đã sắp xếp.
SORTBY loại bỏ hạn chế đó. Nó sắp xếp một phạm vi bằng cách sử dụng các giá trị từ một phạm vi khác và phạm vi thứ hai không nhất thiết phải xuất hiện trong đầu ra. Tôi đã sử dụng công thức sau để xếp hạng nhân viên bán hàng theo doanh thu trong tập dữ liệu của mình:
=SORTBY(D2:D33, G2:G33, -1)
Đối số đầu tiên là những gì bạn muốn trả về (nhân viên bán hàng), đối số thứ hai là những gì cần sắp xếp theo (doanh thu bán hàng) và -1 đặt thứ tự giảm dần. Các tên được xếp hạng từ doanh số cao nhất đến thấp nhất và cột doanh số không bao giờ xuất hiện trong kết quả trừ khi bạn yêu cầu.
Nó cũng kết hợp hoàn hảo với LỌC. Gói FILTER(D2:G33, B2:B33="North") bên trong SORTBY chỉ trả về các bản ghi của khu vực phía Bắc, được sắp xếp từ cao xuống thấp trong một lần. Dữ liệu nguồn không bị ảnh hưởng.
SORTBY chấp nhận nhiều cấp độ sắp xếp. Bạn có thể thêm nhiều cặp phạm vi/đơn hàng để sắp xếp theo khu vực trước, sau đó theo doanh thu bán hàng trong từng khu vực.
SEQUENCE đã thay thế các chốt điền và các thủ thuật ROW mà tôi từng tự hào
Tạo chuỗi mà không cần kéo một ô nào
Tạo một chuỗi được đánh số thường có nghĩa là kéo núm điều khiển điền cho đến khi tôi mất kiên nhẫn hoặc viết =ROW(A1) và sao chép nó xuống. Cả hai đều hoạt động nhưng không thay đổi kích thước khi dữ liệu cơ bản thay đổi.
SEQUENCE xử lý cùng một tác vụ trong một ô và tạo ra các hàng, cột hoặc lưới đầy đủ tùy thuộc vào các đối số. Cú pháp là:
=SEQUENCE(rows, [columns], [start], [step])
Để đánh số tám nhân viên bán hàng duy nhất trong dữ liệu của tôi bắt đầu từ 1001, tôi đã sử dụng công thức sau:
=SEQUENCE(8, 1, 1001, 1)
Điều đó trả về 1001 đến 1008 trong một cột. Đặt cột thành 1 và bước thành 1 sẽ giữ nó ở dạng danh sách dọc với các số nguyên liên tiếp. Nơi SEQUENCE giành được vị trí của nó là bên trong các chức năng khác. Việc xây dựng phạm vi ngày 31 ngày cho tháng 1 năm 2026 cần một công thức:
=DATE(2026, 1, SEQUENCE(31))
Việc cung cấp mảng bị tràn vào TEXT, INDEX hoặc bất kỳ hàm nào khác mong đợi một phạm vi sẽ thu gọn những gì từng cần cột trợ giúp. Tôi đã trình bày vấn đề này chi tiết hơn khi viết về cách sử dụng SEQUENCE để điền vào các cột ngày và nó vẫn giúp tôi tiết kiệm thời gian trên mỗi lịch hoặc lịch trình mà tôi xây dựng.
Các chức năng tôi muốn tìm hiểu tiếp theo
Không có phương pháp cũ nào bị hỏng. Chúng chỉ mất nhiều thời gian hơn và tạo ra các tệp khó bảo trì hơn cho cả tôi và bất kỳ ai khác mở sổ làm việc sau này. Đối với tôi, sự thay đổi là điều tôi mong muốn theo mặc định. Các bảng tính mới bắt đầu với tư duy mảng động và hộp công cụ cũ chỉ xuất hiện khi tôi đang chỉnh sửa tệp của người khác. Tiếp theo trong danh sách của tôi là TEXTSPLIT để tách các chuỗi, VSTACK và HSTACK để kết hợp các phạm vi và PIVOTBY và GROUPBY để bỏ qua hoàn toàn các bảng tổng hợp. Mỗi lần phát hành dường như sẽ loại bỏ một thói quen khác, đối với tôi điều đó là ổn.