Công thức Excel: LAMBDA nối thêm phạm vi theo chiều ngang
Công thức Excel: LAMBDA nối thêm phạm vi theo chiều ngang

Tóm lược

Excel không cung cấp hàm để nối các phạm vi theo chiều ngang, nhưng bạn có thể sử dụng Hàm LAMBDA để tạo một hàm tùy chỉnh để kết hợp hai phạm vi, một cạnh nhau. Trong ví dụ dưới đây, công thức trong ô C5 là:

=AppendRangeHorizontal(B5:C12,E5:F10,"")

Công thức này kết hợp hai phạm vi được cung cấp (B5: C12 và E5: F10), bằng cách thêm phạm vi thứ hai vào bên phải của phạm vi đầu tiên. Kết quả được trả về dưới dạng mảng của các giá trị tràn thành H5: K12. Đối số thứ ba cung cấp giá trị mặc định để sử dụng khi công thức gặp lỗi khi kết hợp phạm vi, ví dụ: khi phạm vi có số cột hoặc hàng khác nhau.

Giải trình

Excel không cung cấp hàm công thức để nối hoặc kết hợp các phạm vi, theo chiều ngang hoặc chiều dọc. Bạn có thể sử dụng Power Query cho tác vụ này và điều này có ý nghĩa đối với các phép biến đổi dữ liệu phải được tự động hóa và lặp lại liên tục. Tuy nhiên, bạn cũng có thể sử dụng Hàm LAMBDA để tạo một hàm tùy chỉnh để kết hợp các phạm vi. Điều này có ý nghĩa khi bạn kiểm soát tốt dữ liệu và cần một giải pháp cập nhật tự động mà không cần bước làm mới.

Trong ví dụ dưới đây, công thức trong ô C5 là:

=AppendRangeHorizontal(B5:C12,E5:F10,"")

Đây là một hàm tùy chỉnh được tạo bằng LAMBDA, dựa trên một số hàm Excel, bao gồm MỤC LỤC, SỰ NỐI TIẾP, IFERROR, ROWS, CỘT, và TỐI ĐA. Nắm giữ toàn bộ mọi thứ cùng nhau là LAMBDAĐỂ CHO:

=LAMBDA(range1,range2,default,
  LET(
  rows1,ROWS(range1),
  rows2,ROWS(range2),
  cols1,COLUMNS(range1),
  cols2,COLUMNS(range2),
  rowindex,SEQUENCE(MAX(rows1,rows2)),
  colindex,SEQUENCE(1,cols1+cols2),
  result,
  IF(
    colindex<=cols1,
    INDEX(range1,rowindex,colindex),
    INDEX(range2,rowindex,colindex-cols1)
  ),
  IFERROR(result,default)
  )
)

Công thức này dựa trên một công thức giải thích ở đây, được điều chỉnh để làm việc với các cột hoặc hàng thay thế. Đó là một ví dụ điển hình về cách thức hoạt động của LAMBDA và Hàm LET làm việc tốt với nhau. Bên trong hàm LET, sáu dòng mã đầu tiên chỉ cần gán giá trị cho các biến. Sau khi các giá trị được gán, các biến này điều khiển đầu ra của hàm.

Logic cốt lõi của công thức, mã xây dựng mảng kết hợp, là ở đây:

result,
  IF(
    colindex<=cols1,
    INDEX(range1,rowindex,colindex),
    INDEX(range2,rowindex,colindex-cols1)
  ),
  IFERROR(result,default)
  )

Mã này có thể khó đọc, đặc biệt nếu bạn chưa quen với các chức năng LAMBDA và công thức mảng động nói chung. Với các dấu ngắt dòng được thêm vào để dễ đọc, thật hấp dẫn để đọc nó giống như một vòng lặp, với colindex như một bộ đếm tăng dần, nhưng colindex không phải là một giá trị, mà là một mảng gồm 4 giá trị, được tạo bằng Hàm SEQUENCE sớm hơn:

colindex,SEQUENCE(1,cols1+cols2) // returns {1,2,3,4)

Tương tự, rowindex là một mảng 8 số:

rowindex,SEQUENCE(rows1+rows2) // returns {1;2;3;4;5;6;7;8} 

Các Hàm IF kiểm tra các giá trị trong colindex tất cả trong một. Nếu colindex nhỏ hơn hoặc bằng tổng số cột trong range1 (2), MỤC LỤC tìm nạp các cột từ range1. Nếu colindex lớn hơn 2, INDEX tìm nạp các cột từ range2. Trong cả hai trường hợp, rowindex được sử dụng để truy xuất các hàng.

Mảng mà IF và INDEX tạo được gán cho biến kết quả, được trả về dưới dạng giá trị cuối cùng bởi công thức thông qua Hàm IFERROR. Điều này được thực hiện như một cách để bắt lỗi xảy ra khi các phạm vi số lượng hàng hoặc cột khác nhau được kết hợp. Trong trường hợp này, INDEX sẽ xuất hiện lỗi khi nó cố gắng lấy giá trị từ một hàng hoặc cột không tồn tại và giá trị mặc định sẽ được xuất ra thay vì lỗi.

https://exceljet.net/formula/lambda-append-range-horizontal