Cách sử dụng hàm BYROW trong Microsoft Excel

BYROW là tiêu chuẩn vàng cho các phép toán theo hàng trong Excel. Bằng cách đặt các công thức của bạn trong một phạm vi tràn duy nhất, bạn loại bỏ nguy cơ ghi đè thủ công, giữ cho các tệp của bạn gọn nhẹ và làm cho dữ liệu dễ mở rộng hơn. Đây là cách dễ nhất để biến một bảng tính dễ hỏng, lộn xộn thành một công cụ mạnh mẽ thực sự hoạt động.

Cách hàm BYROW hoạt động

Để thành thạo BYROW, bạn phải thay đổi cách suy nghĩ về các công thức trong Excel. Thông thường, bạn có thể viết một công thức trong một ô và kéo xuống hoặc dựa vào bảng để tự động điền cột. Mặc dù cách này hiệu quả, nhưng nó khiến bảng tính của bạn dễ bị ghi đè thủ công. BYROW thay đổi cách chơi. Nó sử dụng động cơ LAMBDA để thực hiện tính toán trên từng hàng trong một vùng, nhưng toàn bộ logic được chứa trong một ô duy nhất.

Cú pháp BYROW

Hàm BYROW sử dụng một mẫu cụ thể để chỉ cho Excel chính xác những gì cần làm với dữ liệu của bạn:

=BYROW(array,LAMBDA(r,calculation))

• mảng là khối dữ liệu bạn muốn phân tích.
• LAMBDA là một lớp bao bọc thông báo cho Excel biết rằng bạn đang tạo một hướng dẫn tùy chỉnh.
• r là biệt danh cho hàng hiện tại.
• tính toán là phép toán bạn muốn thực hiện trên hàng đó.

BYROW hoạt động tốt nhất khi bạn trỏ nó vào một bảng Excel sử dụng các tham chiếu có cấu trúc. Tuy nhiên, vì đây là một hàm mảng động đổ kết quả xuống từ ô nơi bạn nhập công thức, nó không thể đi bên trong một bảng Excel. Nói cách khác, mặc dù hàm muốn bạn sử dụng các tham chiếu có cấu trúc, hãy chắc chắn rằng bạn nhập công thức này trong một ô thông thường.

Điểm quan trọng về khả năng mở rộng

Sử dụng một bảng Excel đã được định dạng làm dữ liệu nguồn tạo ra một hệ thống bán tự động xử lý sự phát triển khác nhau tùy theo hướng:

• Mở rộng theo chiều dọc: Nếu bạn thêm 100 hàng mới vào bảng, công thức BYROW sẽ phát hiện dữ liệu mới và tự động lan xuống để phù hợp với chiều cao mới. Bạn không bao giờ phải nhấp và kéo tay cầm điền.
• Khả năng mở rộng theo chiều ngang: Nếu bạn sử dụng tham chiếu có cấu trúc bao phủ toàn bộ bảng của mình, Excel sẽ tự động bao gồm các cột mới bạn thêm vào, dù là ở giữa hay bên phải. Tuy nhiên, nếu công thức của bạn nhắm vào một phạm vi con cụ thể của các cột, việc thêm một cột mới ở bên phải sẽ yêu cầu bạn nhanh chóng cập nhật thủ công công thức để mở rộng phạm vi ra cột mới.

Trường hợp sử dụng 1: Phép tính không thể phá vỡ

BYROW rất hữu ích cho việc đảm bảo tính toàn vẹn dữ liệu. Trong một bảng Excel tiêu chuẩn, bất kỳ ai cũng có thể chọn một ô và nhập một con số ngẫu nhiên, làm mất công thức trong hàng đó. Tuy nhiên, nếu ai đó làm gián đoạn tính toán BYROW, Excel sẽ hiển thị lỗi #SPILL!, vì vậy bạn sẽ biết ngay rằng có điều gì đó đã xảy ra sai.

Bối cảnh: Bạn có một bảng điểm (tên là T_StudentScores) và cần tính tổng điểm cho từng sinh viên. Bạn muốn đảm bảo rằng không ai có thể gian lận bằng cách ghi đè lên phép tính của bạn mà không gây ra lỗi hiển thị.

Đây là công thức bạn cần nhập vào ô E2:

=BYROW(
T_StudentScores[[Midterm]:[Final]],
LAMBDA(r,SUM(r))
)

Công thức này yêu cầu Excel xem xét phạm vi ngang từ cột Midterm đến cột Final. Nó định nghĩa mỗi hàng là r, và hàm LAMBDA hướng dẫn Excel SUM các giá trị trong r. Bởi vì đây là một mảng động, kết quả sẽ tràn xuống để khớp với chiều cao của bảng của bạn.

Lợi ích của BYROW trong tình huống này

• so với các hàm cũ: Các công thức tiêu chuẩn như =SUM(T_StudentScores[@[Midterm]:[Final]]) xuất hiện trong mỗi ô, có nghĩa là ai đó có thể dễ dàng ghi đè lên kết quả mà không kích hoạt bất kỳ cảnh báo nào. Với BYROW, toàn bộ cột sẽ kích hoạt lỗi #SPILL!, hoạt động như một báo động bảo mật cho dữ liệu của bạn.
• so với các hàm LAMBDA khác: MAP xử lý từng ô một, vì vậy nó sẽ tính tổng điểm giữa kỳ và cuối kỳ riêng biệt thay vì cộng chúng lại với nhau. SCAN dùng cho tổng tích luỹ, vì vậy nó sẽ tạo ra tổng lớp tích lũy thay vì điểm từng cá nhân. REDUCE nén toàn bộ bảng thành một ô duy nhất, cho bạn tổng cộng chung thay vì phân chia theo từng học sinh.

Trường hợp sử dụng 2: Kiểm tra logic ngang

Bảng là lý tưởng cho dữ liệu theo chiều dọc, nhưng chúng thường gặp khó khăn với logic theo chiều ngang khi bạn muốn kiểm tra các điều kiện trên nhiều cột trong cùng một hàng. Truyền thống, điều này yêu cầu các công thức dài, lồng nhau, và thật là ác mộng để cập nhật nếu bạn thêm nhiều cột hơn.

Tình huống: Bạn có một công cụ theo dõi dự án (tên là T_Launch) nơi một nhiệm vụ chỉ được đánh dấu "Sẵn sàng" khi tất cả ba phòng ban đã đánh dấu vào ô phê duyệt của mình.

Công thức BYROW để điền vào ô F2 như sau:

=BYROW(
T_Launch[[Marketing]:[Product]],
LAMBDA(r,IF(COUNTIF(r,TRUE)=3,"Ready","Pending"))
)

Ở đây, BYROW truyền "lát cắt" theo chiều ngang cho LAMBDA. Sau đó, hàm COUNTIF sẽ kiểm tra hàng r để xem có bao nhiêu giá trị TRUE. Nếu số lượng là 3, dự án được coi là "Sẵn sàng." Ngược lại, nó vẫn là "Đang chờ."

Lợi ích của BYROW trong tình huống này

• so với các hàm cũ: Mặc dù một cột trong bảng với công thức như =AND(T_Launch[@[Marketing]:[Product]]) thì gọn gàng, nhưng nó vẫn là một “cột tính toán” với khả năng có hàng nghìn bản sao ẩn. Nếu bạn vô tình chỉnh sửa công thức ở hàng 500, bạn đã tạo ra sự không nhất quán. BYROW loại bỏ hoàn toàn rủi ro đó bằng cách đặt logic trong một ô duy nhất. Điều này có nghĩa là một giá trị bị ghi đè sẽ gây ra lỗi #SPILL!
• so với các hàm LAMBDA khác: MAP mù hàng — nó có thể kiểm tra nếu một ô riêng lẻ là TRUE, nhưng không thể đếm qua một hàng. SCAN và REDUCE được thiết kế cho tổng tích lũy nơi một hàng phụ thuộc vào hàng trước đó, vì vậy sử dụng chúng để kiểm tra các hàng độc lập là quá mức cần thiết và làm bảng tính của bạn khó kiểm tra hơn.

Trường hợp sử dụng 3: Kiến trúc sổ làm việc gọn nhẹ

Trong một bảng tiêu chuẩn, Excel phải theo dõi hàng nghìn công thức riêng lẻ trong siêu dữ liệu của nó. Ngay cả với một cột duy nhất, BYROW là cách sạch sẽ hơn để xây dựng bảng tính vì nó hợp nhất logic của bạn vào một nguồn sự thật duy nhất. Điều này làm cho bảng tính của bạn dễ kiểm tra hơn và ngăn nó trở thành một hộp đen của các phép tính dư thừa.

Tình huống: Bạn có một bộ dữ liệu các số liệu nhiệt độ từ bốn khu vực cảm biến khác nhau. Bạn cần xác định nhiệt độ cao nhất được ghi nhận ở tất cả các khu vực tại mỗi mốc thời gian.

Trong ô G2, nhập:

=BYROW(
T_SensorData[[Zone_A]:[Zone_D]],
LAMBDA(r,MAX(r))
)

Điều này quét từng hàng ngang r và xác định giá trị tối đa trên bốn khu vực cảm biến cho mốc thời gian cụ thể đó.

Lợi ích của BYROW trong tình huống này

• so với các hàm cũ: Trong khi một cột chuẩn =MAX() có thể hoạt động ở đây, nó sẽ tạo ra một công thức riêng cho từng hàng. BYROW thay thế hàng trăm hoặc hàng nghìn công thức đó bằng một điểm nhập duy nhất, giúp giữ cho cấu trúc nội bộ của sổ làm việc gọn gàng.
• so với các hàm LAMBDA khác: MAP sẽ đánh giá từng ô một cách riêng lẻ, gây tốn tài nguyên không cần thiết khi bạn chỉ cần một kết quả cho mỗi hàng. SCAN dùng cho tổng tích lũy, và REDUCE sẽ cho bạn nhiệt độ cao nhất từng được ghi nhận trong bảng.

Khắc phục các lỗi BYROW thường gặp

Bởi vì hàm BYROW của Excel là một hàm mảng động, nó tuân theo một bộ quy tắc nghiêm ngặt hơn so với công thức thông thường. Nếu kết quả của bạn không hiển thị đúng, hãy sử dụng bảng này để tìm cách khắc phục:

Hiểu được BYROW mang lại cho bạn một cách hoàn toàn khác để xây dựng và quản lý bảng tính của mình. Bởi vì bạn đặt tất cả logic của mình vào một ô "bộ não" duy nhất, bạn đang tạo ra thứ gì đó có khả năng mở rộng theo dữ liệu nguồn, luôn dễ dàng kiểm tra, và khó bị người khác làm hỏng. Sự thay đổi này trở nên khả thi nhờ vào động cơ LAMBDA, đó là lý do lớn tại sao Excel hiện được coi như một ngôn ngữ lập trình. Khi bạn sử dụng các hàm này, bạn đang viết một logic tùy chỉnh biến một tập tin workbook đơn giản thành một công cụ tinh vi.