Hàm index tra hai điều kiện?

[KhaNhu1999]

Em chào anh chị diễn đàn ạ, anh chị giúp em công thức này với, em làm công thức mảng tra 2 điều kiện bằng Index, nhưng em không biết cách nào để tra 1 điều kiện là tương đối, 1 điều kiện tra tuyệt đối.


Em muốn nếu sản phẩm A2 ngày 5/8/2022 không có thì nó sẽ lấy ngày trước đó là ngày 1/8/2022, tức là ô G4 kết quả là 30.000.
Công thức mảng của em tại ô G4 ạ:

=INDEX($A$3:$A$6,MATCH(E4&F4,$B$3:$B$6&$C$3:$C$6,0),1)

Anh chị giúp em công thức chỗ này với ạ, em cảm ơn rất nhiều!

 

[qtm1987]

Em chào anh chị diễn đàn ạ, anh chị giúp em công thức này với, em làm công thức mảng tra 2 điều kiện bằng Index, nhưng em không biết cách nào để tra 1 điều kiện là tương đối, 1 điều kiện tra tuyệt đối.
View attachment 280314

Em muốn nếu sản phẩm A2 ngày 5/8/2022 không có thì nó sẽ lấy ngày trước đó là ngày 1/8/2022, tức là ô G4 kết quả là 30.000.
Công thức mảng của em tại ô G4 ạ:

=INDEX($A$3:$A$6,MATCH(E4&F4,$B$3:$B$6&$C$3:$C$6,0),1)

Anh chị giúp em công thức chỗ này với ạ, em cảm ơn rất nhiều!

Bạn sửa 0 trong hàm match thành 1:
=INDEX($A$3:$A$6,MATCH(E4&F4,$B$3:$B$6&$C$3:$C$6,1),1

 

[Nhattanktnn]

Bạn sửa 0 trong hàm match thành 1:
=INDEX($A$3:$A$6,MATCH(E4&F4,$B$3:$B$6&$C$3:$C$6,1),1

Chỉ vậy là chỉ bậy rồi đó

Em muốn nếu sản phẩm A2 ngày 5/8/2022 không có thì nó sẽ lấy ngày trước đó là ngày 1/8/2022, tức là ô G4 kết quả là 30.000.

Dùng công thức này tại G3:

=LOOKUP(2,1/($B$3:$B$6=E3)/($C$3:$C$6<=F3),$A$3:$A$6)

Điều kiện là ngày cập nhật phải thứ tự từ bé tới lớn (cho mỗi mã sản phẩm)

 

[KhaNhu1999]

Bạn sửa 0 trong hàm match thành 1:
=INDEX($A$3:$A$6,MATCH(E4&F4,$B$3:$B$6&$C$3:$C$6,1),1

Mình đã thử, nhưng kết quả không chính xác với dữ liệu thật bạn ạ!

Bài đã được tự động gộp: 25/8/22

Chỉ vậy là chỉ bậy rồi đó

Dùng công thức này tại G3:

=LOOKUP(2,1/($B$3:$B$6=E3)/($C$3:$C$6<=F3),$A$3:$A$6)

Điều kiện là ngày cập nhật phải thứ tự từ bé tới lớn (cho mỗi mã sản phẩm)

Cám ơn anh, chính xác luôn ạ, cám ơn anh nhiều!

 

[KhaNhu1999]

Em làm được rồi ạ, cám ơn anh rất nhiều!

 

[qtm1987]

Chỉ vậy là chỉ bậy rồi đó

Bạn giải thích thêm giúp công thức này sai chỗ nào, với dữ liệu trong file ví dụ trên thì mình không thấy sai và trong công việc hàng ngày mình thường dùng hàm match thay đổi 0 thành 1 hoặc -1 để dò bảng giá bán cập nhật theo ngày như vậy.

Mình đã thử, nhưng kết quả không chính xác với dữ liệu thật bạn ạ!

Nếu có thể bạn gửi dữ liệu mẫu nhiều hơn thử, mình sẽ xem lại.

 

[huhumalu]

Hàm index tra hai điều kiện.

 

[qtm1987]

Hàm index tra hai điều kiện.

Mình hiểu ý nghĩa hàm Match, với yêu cầu của chủ thớt dữ liệu trong bảng "Giá cập nhật" chắc chắn sẽ luôn cập nhật giá mới thì mảng chứa giá trị ngày sẽ luôn tăng dần nên mình chỉ thớt đổi 0 thành 1 để :
"Hàm MATCH tìm kiếm giá trị lớn nhất mà giá trị đó nhỏ hơn hoặc bằng giá trị tìm kiếm. Giá trị trong đối số mảng tìm kiếm phải được sắp theo thứ tự tăng dần, ví dụ: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE."
Cái mình thắc mắc là mình chỉ "bậy" chỗ nào để mình rút kinh nghiệm.

 

[Nhattanktnn]

Mình hiểu ý nghĩa hàm Match, với yêu cầu của chủ thớt dữ liệu trong bảng "Giá cập nhật" chắc chắn sẽ luôn cập nhật giá mới thì mảng chứa giá trị ngày sẽ luôn tăng dần nên mình chỉ thớt đổi 0 thành 1 để :
"Hàm MATCH tìm kiếm giá trị lớn nhất mà giá trị đó nhỏ hơn hoặc bằng giá trị tìm kiếm. Giá trị trong đối số mảng tìm kiếm phải được sắp theo thứ tự tăng dần, ví dụ: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE."
Cái mình thắc mắc là mình chỉ "bậy" chỗ nào để mình rút kinh nghiệm.

Tra chỉ số dùng match với tham số 1, hay -1 áp dụng cho khoảng giá trị là không sai. Nhưng cái sai ở đây là cách làm của bạn sai. Bạn xem hình dưới:

Khi dùng match như thế này:

MATCH(E4&F4,$B$3:$B$6&$C$3:$C$6,1)

Thì hàm match sẽ nối chuỗi sản phẩm và ngày tháng thành dạng text là mã sản phẩm và giá trị số của ngày tháng đó (chỗ nối chuỗi trong hình)
Vì sao bạn làm đúng trong trường hợp này:
Vì sản phẩm được sắp xếp tăng dần. Bạn biết hàm Match sẽ lấy giá trị nhỏ hơn hoặc bằng tính từ dưới lên. Trong trường hợp này A244774 là giá trị cuối cùng <= A244778. Vì thế trả về kết quả đúng.
Giả sử sản phẩm không được sắp xếp. A2 nằm trên, A1 nằm dưới. Khi đó dòng A1 cuối cùng luôn bé hơn A244778. Kết quả sẽ trả về dòng A1 cuối cùng

 

[qtm1987]

Tra chỉ số dùng match với tham số 1, hay -1 áp dụng cho khoảng giá trị là không sai. Nhưng cái sai ở đây là cách làm của bạn sai. Bạn xem hình dưới:
View attachment 280337
Khi dùng match như thế này:

Thì hàm match sẽ nối chuỗi sản phẩm và ngày tháng thành dạng text là mã sản phẩm và giá trị số của ngày tháng đó (chỗ nối chuỗi trong hình)
Vì sao bạn làm đúng trong trường hợp này:
Vì sản phẩm được sắp xếp tăng dần. Bạn biết hàm Match sẽ lấy giá trị nhỏ hơn hoặc bằng tính từ dưới lên. Trong trường hợp này A244774 là giá trị cuối cùng <= A244778. Vì thế trả về kết quả đúng.
Giả sử sản phẩm không được sắp xếp. A2 nằm trên, A1 nằm dưới. Khi đó dòng A1 cuối cùng luôn bé hơn A244778. Kết quả sẽ trả về dòng A1 cuối cùng
View attachment 280338

Cám ơn bạn đã giải thích, theo mình thì chủ yếu là phụ thuộc vào dữ liệu của bạn thớt thôi, nếu bạn ấy để lộn xộn không sắp xếp ngày tăng dần thì hàm Lookup như bạn chỉ cũng tìm sai đúng không.

 

[VetMini]

Cám ơn bạn đã giải thích, theo mình thì chủ yếu là phụ thuộc vào dữ liệu của bạn thớt thôi, nếu bạn ấy để lộn xộn không sắp xếp ngày tăng dần thì hàm Lookup như bạn chỉ cũng tìm sai đúng không.

Hàm Lookup vốn dĩ được liệt vào danh sách "hàm nguy hiểm". Độ nguy hiểm đứng trên Aggregate một bậc.
Tính chất "nguy hiểm" được đánh giá dựa trên mức độ cần hiểu biết về căn bản hoạt dộng của hàm và tình trạng dữ liệu.

"nguy hiểm" có nghĩa là nếu không nắm vững công thức, dữ liệu thay đổi có thể làm hàm cho ra kết quả sai.

Khi viết giùm công thức cho người khác, và công thức có hàm nguy hiểm thì:
- Nên cố gắng làm thật đơn giản.
- Nên báo trước cho người dùng những trường hợp dữ liệu có thể cho kết quả sai.

 

[kelacloi]

Em chào anh chị diễn đàn ạ, anh chị giúp em công thức này với, em làm công thức mảng tra 2 điều kiện bằng Index, nhưng em không biết cách nào để tra 1 điều kiện là tương đối, 1 điều kiện tra tuyệt đối.
View attachment 280314

Em muốn nếu sản phẩm A2 ngày 5/8/2022 không có thì nó sẽ lấy ngày trước đó là ngày 1/8/2022, tức là ô G4 kết quả là 30.000.
Công thức mảng của em tại ô G4 ạ:

=INDEX($A$3:$A$6,MATCH(E4&F4,$B$3:$B$6&$C$3:$C$6,0),1)

Anh chị giúp em công thức chỗ này với ạ, em cảm ơn rất nhiều!

Thử sumproduct xem sao.

=SUMPRODUCT($A$3:$A$6*($B$3:$B$6=E3)*($C$3:$C$6=F3))

 

[Binbo2020]

Thử sumproduct xem sao.

=SUMPRODUCT($A$3:$A$6*($B$3:$B$6=E3)*($C$3:$C$6=F3))

Sai rồi lấy giá ngày thay đổi gần nhất mà có phải chỉ mỗi đúng ngày đâu

 

[kelacloi]

Sai rồi lấy giá ngày thay đổi gần nhất mà có phải chỉ mỗi đúng ngày đâu

Nếu vầy thì sửa lại thế này thử xem.
(Tôi tiện tay lấy cả cột, có thể sửa lại vùng cho phù hợp).

=SUMPRODUCT($A$3:$A$6*($B$3:$B$6=E3)*($C$3:$C$6=MAXIFS(C:C,B:B,E3,C:C,""&"<="&F3)))

 

[87kilua]

Sửa gì công thức, sửa cả bảng luôn

 

[Phan Thế Hiệp]

Cám ơn bạn đã giải thích, theo mình thì chủ yếu là phụ thuộc vào dữ liệu của bạn thớt thôi, nếu bạn ấy để lộn xộn không sắp xếp ngày tăng dần thì hàm Lookup như bạn chỉ cũng tìm sai đúng không.

1/ Muốn dùng Index(match()) thì dùng như vầy:

=INDEX($A$3:$A$10,MATCH(F3,INDEX($C$3:$C$10/($B$3:$B$10=E3),)))

Enter, fill xuống.

2/ Dùng Lookup() cũng có thể dùng như vầy:

=LOOKUP(F3,$C$3:$C$10/($B$3:$B$10=E3),$A$3:$A$10)

Enter, fill xuống.

Dù cái nào cũng dựa vào dữ liệu đã được sắp xếp trước từ nhỏ đến lớn.

Thân

 

[KhaNhu1999]

Dạ, em mới học Excel, thật sự em chưa hiểu tường tận, nhưng đọc giải thích của anh Nhattanktnn em cũng đã hiểu được cách mà Excel hoạt động.
Em cám ơn cài bài viết của anh chị làm em thấm thêm một chút về Excel, hihi