Hàm LOOKUP bị lỗi khi thay thế tham chiếu bằng công thức

[Lê Hồng Minh83]

Chào anh/chị/em diễn đàn
Mình đang bị vướng chút vấn đề về hàm LOOKUP khi thay thế tham chiếu đơn thuần bằng tham chiếu công thức (hàm ADDRESS)
Ví dụ như B6=LOOKUP(2,1/($B$3:B3<>""),$B$3:B3), mình đem thay cụm $B$3:B3 bằng cụm ADDRESS(ROW(B3),2)&":"&ADDRESS(ROW(B3),COLUMN(B3)) thì nó cho kết quả không như mong muốn
Do công thức sẽ copy cho những ô sau và những ô bên dưới nên phải di chuyển vùng tham chiếu bằng hàm
Trong file đính kèm mô tả chi tiết hơn về tình huống.
Cảm ơn!

 

[ndu96081631]

Chào anh/chị/em diễn đàn
Mình đang bị vướng chút vấn đề về hàm LOOKUP khi thay thế tham chiếu đơn thuần bằng tham chiếu công thức (hàm ADDRESS)
Ví dụ như B6=LOOKUP(2,1/($B$3:B3<>""),$B$3:B3), mình đem thay cụm $B$3:B3 bằng cụm ADDRESS(ROW(B3),2)&":"&ADDRESS(ROW(B3),COLUMN(B3)) thì nó cho kết quả không như mong muốn
Do công thức sẽ copy cho những ô sau và những ô bên dưới nên phải di chuyển vùng tham chiếu bằng hàm
Trong file đính kèm mô tả chi tiết hơn về tình huống.
Cảm ơn!

1 thằng là tham chiếu còn thằng là chuỗi, sao thay nhau được
cái công thức ADDRESS... trả về kết quả là chuỗi, muốn trở thành tham chiếu phải được lồng vào trong INDIRECT
Ngoài ra thì: phải thay ROW thành ROWS và COLUMN thành COLUMNS
Đây là 1 ví dụ minh chứng không phải lúc nào cũng dùng ROW được (mà chính xác là phải ROWS). Bởi ROW trả về kết quả 1 array còn ROWS thì trả về kết quả kiểu Long

 

[Lê Hồng Minh83]

1 thằng là tham chiếu còn thằng là chuỗi, sao thay nhau được
cái công thức ADDRESS... trả về kết quả là chuỗi, muốn trở thành tham chiếu phải được lồng vào trong INDIRECT
Ngoài ra thì: phải thay ROW thành ROWS và COLUMN thành COLUMNS
Đây là 1 ví dụ minh chứng không phải lúc nào cũng dùng ROW được (mà chính xác là phải ROWS). Bởi ROW trả về kết quả 1 array còn ROWS thì trả về kết quả kiểu Long

Cảm ơn bác đã trả lời
Nhưng thêm S vào nó cũng hổng ra bác ơi. Dòng 11 có INDERECT luôn mà không được

 

[Phan Thế Hiệp]

Chào anh/chị/em diễn đàn
Mình đang bị vướng chút vấn đề về hàm LOOKUP khi thay thế tham chiếu đơn thuần bằng tham chiếu công thức (hàm ADDRESS)
Ví dụ như B6=LOOKUP(2,1/($B$3:B3<>""),$B$3:B3), mình đem thay cụm $B$3:B3 bằng cụm ADDRESS(ROW(B3),2)&":"&ADDRESS(ROW(B3),COLUMN(B3)) thì nó cho kết quả không như mong muốn
Do công thức sẽ copy cho những ô sau và những ô bên dưới nên phải di chuyển vùng tham chiếu bằng hàm
Trong file đính kèm mô tả chi tiết hơn về tình huống.
Cảm ơn!

Hoặc thử như sau:

B8=LOOKUP(2,1/(INDEX(INDIRECT(ADDRESS(ROW(B3),2)&":"&ADDRESS(ROW(B3),COLUMN(B3))),)<>""),INDIRECT(ADDRESS(ROW(B3),2)&":"&ADDRESS(ROW(B3),COLUMN(B3))))

Chỉ Enter
Thân.

 

[Lê Hồng Minh83]

Hoặc thử như sau:

B8=LOOKUP(2,1/(INDEX(INDIRECT(ADDRESS(ROW(B3),2)&":"&ADDRESS(ROW(B3),COLUMN(B3))),)<>""),INDIRECT(ADDRESS(ROW(B3),2)&":"&ADDRESS(ROW(B3),COLUMN(B3))))

Chỉ Enter
Thân.

Nó đã ra kết quả mong muốn khi thêm INDEX
Bác cho hỏi sao phải thêm INDEX vào nhỉ

 

[lameco411]

Mình không hiểu mục đích của bạn lắm. Nếu như chỉ cần lấy dữ liệu ở trên xuống dưới thì chỉ cần viết vầy thôi là ra rồi:
B6=IF(B3="",A5,B3)

 

[Lê Hồng Minh83]

Mình không hiểu mục đích của bạn lắm. Nếu như chỉ cần lấy dữ liệu ở trên xuống dưới thì chỉ cần viết vầy thôi là ra rồi:
B6=IF(B3="",A5,B3)

Híc, nó không đơn giản như vậy đâu bạn. Cảm ơn vì đã góp ý!

 

[lameco411]

Híc, nó không đơn giản như vậy đâu bạn. Cảm ơn vì đã góp ý!

Bạn nói rõ yêu cầu của bạn cho mọi người xem có cách nào hay hơn không?

 

[ndu96081631]

Cảm ơn bác đã trả lời
Nhưng thêm S vào nó cũng hổng ra bác ơi. Dòng 11 có INDERECT luôn mà không được

Sao không được chứ. Như đã nói ở trên thì:
- Phải lồng ADDRESS... bên trong INDIRECT và bạn đã làm rồi
- Thêm nữa phải thay ROW thành ROWS và COLUMN thành COLUMNS ---> bạn chưa làm
Cụ thể thì:
- Thay ROW(B3) thành ROWS(A$1:A3)
- Thay COLUMN(B3) thành COLUMNS($A1:B1)

=LOOKUP(2,1/(INDIRECT(ADDRESS(ROWS(A$1:A3),2)&":"&ADDRESS(ROWS(A$1:A3),COLUMNS($A1:B1)))<>""),INDIRECT(ADDRESS(ROWS(A$1:A3),2)&":"&ADDRESS(ROWS(A$1:A3),COLUMNS($A1:B1))))

 

[Phan Thế Hiệp]

Nó đã ra kết quả mong muốn khi thêm INDEX
Bác cho hỏi sao phải thêm INDEX vào nhỉ

Khi tôi xem trong Evaluate Formula, tôi thấy mặc dù đã có Indirect(Address()) và nó cũng nằm trong Mảng "Vectơ chỉ hướng", nhưng nó vẫn chưa chấp nhận kiểu khai báo mảng này, thậm chí nhấn tổ hợp phím CSE.
Tôi đã dùng thử T(...), cũng không được, chỉ sau khi thử Index(...,) thì được, tôi nghĩ chắc nó cần khai báo Mảng ngay khi nó hình thành, và nguyên do như thầy NDU phân tích là bởi hàm ROW().
Thân.

 

[Lê Hồng Minh83]

Sao không được chứ. Như đã nói ở trên thì:
- Phải lồng ADDRESS... bên trong INDIRECT và bạn đã làm rồi
- Thêm nữa phải thay ROW thành ROWS và COLUMN thành COLUMNS ---> bạn chưa làm
Cụ thể thì:
- Thay ROW(B3) thành ROWS(A$1:A3)
- Thay COLUMNS(B3) thành COLUMNS($A1:C1)

=LOOKUP(2,1/(INDIRECT(ADDRESS(ROWS(A$1:A3),2)&":"&ADDRESS(ROWS(A$1:A3),COLUMNS($A1:C1)))<>""),INDIRECT(ADDRESS(ROWS(A$1:A3),2)&":"&ADDRESS(ROWS(A$1:A3),COLUMNS($A1:C1))))

Xin lỗi bác, nãy em thêm S mà quên là 1 mảng chứ không phải 1 ô

 

[Lê Hồng Minh83]

Bạn nói rõ yêu cầu của bạn cho mọi người xem có cách nào hay hơn không?

Cái này mình đang làm bôi màu tự động cho 1 vùng dữ liệu khi có cùng 1 điều kiện ví dụ #216, khi trong vùng xuất phát từ ô $B$3 chạy đi, nó sẽ tìm thấy các điều kiện tiếp theo để đổ màu hợp lý cho trước. Và đương nhiên là bạn không thể chỉ làm cho 1 dòng, mà phải là nhiều dòng bên dưới với các dòng điều kiện khác nhau, và nếu mà dùng tham chiếu đơn thuần thì bạn không thể áp dụng cho phía dưới vì dòng đã bị cố định. Nên mình muốn sử dụng tham chiếu bằng công thức để có thể áp dụng cho rất nhiều dòng phía dưới mà không phải thay đổi công thức...
Bài toán đã có 2 lời giải rồi, Cảm ơn bạn đã quan tâm

 

[Lê Hồng Minh83]

Khi tôi xem trong Evaluate Formula, tôi thấy mặc dù đã có Indirect(Address()) và nó cũng nằm trong Mảng "Vectơ chỉ hướng", nhưng nó vẫn chưa chấp nhận kiểu khai báo mảng này, thậm chí nhấn tổ hợp phím CSE.
Tôi đã dùng thử T(...), cũng không được, chỉ sau khi thử Index(...,) thì được, tôi nghĩ chắc nó cần khai báo Mảng ngay khi nó hình thành, và nguyên do như thầy NDU phân tích là bởi hàm ROW().
Thân.

Cảm ơn bác

 

[vanthe_bk6]

Có cách không dùng công thức mà nhanh. Bạn chọn B3:M3, dùng chức năng Go to special, chon Blanks, công thức tại B4 là =b3, Ctrl+Enter là xong.

 

[lameco411]

Có phải như thế này không bạn?

 

[Lê Hồng Minh83]

Có cách không dùng công thức mà nhanh. Bạn chọn B3:M3, dùng chức năng Go to special, chon Blanks, công thức tại B4 là =b3, Ctrl+Enter là xong.

Cảm ơn bạn đã góp ý. Nhưng như mình đã nói ở bài #12, cái mình hỏi nó chỉ là 1 phần rất nhỏ trong 1 mớ công thức hỗn độn khác và mình cũng không cần thiết phải điền vào mấy cái ô trống đó nội dung còn thiếu...

 

[Lê Hồng Minh83]

Có phải như thế này không bạn?
View attachment 196095

Không phải đâu bạn
nó như vầy nè: Tô màu tự động cho mã hàng theo danh sách cho trước, mã nào đi với màu đó, màu khúc sau sẽ giống màu khúc trước

 

[Phan Thế Hiệp]

Nó đã ra kết quả mong muốn khi thêm INDEX
Bác cho hỏi sao phải thêm INDEX vào nhỉ

Từ câu hỏi của bạn tôi lại rút ra 1 kinh nghiệm:

• Mảng "Vectơ chỉ hướng" khi mang vào để so sánh (dùng với các toán tử = < >) thì cần phải khai báo Mảng khi có dùng Indirect(Address(ROW())..) trong nội tại hàm. Cho nên: 1/(INDEX(INDIRECT(ADDRESS(ROW(B3),2)&":"&ADDRESS(ROW(B3),COLUMN(B3))),) <>"" )
• Riêng "Mảng chứa giá trị kết quả" không tính toán gì thêm nữa, thì không cần khai báo nữa, nên chỉ: INDIRECT(ADDRESS(ROW(B3),2)&":"&ADDRESS(ROW(B3),COLUMN(B3))) mà không cần dùng thêm Index(....,)

Cảm ơn bạn cho một trường hợp đáng lưu ý.
Thân.

 

[Lê Hồng Minh83]

Từ câu hỏi của bạn tôi lại rút ra 1 kinh nghiệm:

• Mảng "Vectơ chỉ hướng" khi mang vào để so sánh (dùng với các toán tử = < >) thì cần phải khai báo Mảng khi có dùng Indirect(Address(ROW())..) trong nội tại hàm. Cho nên: 1/(INDEX(INDIRECT(ADDRESS(ROW(B3),2)&":"&ADDRESS(ROW(B3),COLUMN(B3))),) <>"" )
• Riêng "Mảng chứa giá trị kết quả" không tính toán gì thêm nữa, thì không cần khai báo nữa, nên chỉ: INDIRECT(ADDRESS(ROW(B3),2)&":"&ADDRESS(ROW(B3),COLUMN(B3))) mà không cần dùng thêm Index(....,)

Cảm ơn bạn cho một trường hợp đáng lưu ý.
Thân.

Cảm ơn bác đã chia sẻ. Vì nhìn chúng thấy giống nhau nhưng tính chất lại hoàn toàn khác nhau