Công thức Excel: Phạm vi nối LAMBDA
Công thức Excel: Phạm vi nối LAMBDA

Công thức chung
=AppendRange(range1,range2,default)
Tóm lược

Excel không cung cấp một hàm để nối các phạm vi, 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 các phạm vi. Trong ví dụ dưới đây, công thức trong ô C5 là:

=AppendRange(E5:F9,H5:I10,"null")

Công thức này kết hợp hai phạm vi được cung cấp (E5: F9 và H5: I10), bằng cách thêm phạm vi thứ hai vào phạm vi đầu tiên và trả về mảng của các giá trị tràn thành B5: C15. Đố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 khác nhau.

Giải trình

Mặc dù 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 (bạn có thể nối các phạm vi với Power Query), bạn 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. Trong ví dụ dưới đây, công thức trong ô C5 là:

=AppendRange(E5:F9,H5:I10,"null")

Đâ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(rows1+rows2),
  colindex,SEQUENCE(1,MAX(cols1,cols2)),
  result,
  IF(
    rowindex<=rows1,
    INDEX(range1,rowindex,colindex),
    INDEX(range2,rowindex-rows1,colindex)
  ),
  IFERROR(result,default)
  )
)

Công thức này dựa trên một công thức đơn giản giải thích ở đây. Đó là một ví dụ điển hình về cách hàm LAMBDA và hàm LET hoạt động tốt cùng 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(
    rowindex<=rows1,
    INDEX(range1,rowindex,colindex),
    INDEX(range2,rowindex-rows1,colindex)
  ),
  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 rowindex như một bộ đếm tăng dần, nhưng trên thực tế, rowindex không phải là một giá trị, mà là một mảng gồm 11 giá trị, được tạo bằng Hàm SEQUENCE sớm hơn:

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

Các Hàm IF kiểm tra các giá trị trong rowindex tất cả trong một. Nếu rowindex nhỏ hơn hoặc bằng số hàng trong range1 (5), MỤC LỤC lấy các hàng từ range1. Nếu rowindex lớn hơn 5, INDEX tìm nạp các hàng từ range2. Nếu chúng ta mở rộng giá trị của các biến, mã trông giống như sau:

=IF({1;2;3;4;5;6;7;8;9;10;11}<=5,
INDEX(E5:F9,{1;2;3;4;5;6;7;8;9;10;11},{1,2}),
INDEX(H5:I10,{1;2;3;4;5;6;7;8;9;10;11}-5,{1,2}))

Mã này có thể được kiểm tra trực tiếp trên trang tính và nó sẽ trả về kết quả giống như công thức.

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 cột khác nhau được kết hợp. Ví dụ: nếu bạn kết hợp phạm vi hai cột với phạm vi một cột, INDEX sẽ tạo ra lỗi khi cố gắng lấy các giá trị từ cột 2, vì cột 2 không tồn tại. Trong trường hợp này, giá trị mặc định sẽ được xuất ra thay vì lỗi.

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