Lấy giá trị với giá trị gần đúng nhất theo điều kiện cùng ngày (1 người xem)

[tuyetlieu]

Chào các anh chị,

Các anh chị vui lòng giúp đỡ em trong trường hợp sau nhé:

Em muốn lấy giá trị đúng hoặc gần đúng nhất của một cột theo một cột khác với điều kiện các số đó cùng ngày.

Các anh chị xem file đính kèm dùm em nhé.

Em cảm ơn

Tuyết Liễu

 

[SA_DQ]

Ban tham khảo cách mình làm bằng hàm tự tạo

 

[dhn46]

Bạn dùng công thức sau
Kết thúc Ctrl + Shift + Enter

=IF(E2>0,INDEX($I$3:$I$8,MATCH(MIN(IF($H$3:$H$8=B2,ABS((F2-$I$3:$I$8)),100^100)),IF($H$3:$H$8=B2,ABS((F2-$I$3:$I$8)),100^100),0)),"")

 

[tuyetlieu]

Chào bạn HYen17,dhn46,

Mình đã thử công thức của hai bạn nhưng vẫn ko được. Xin hai bạn vui lòng chỉ thêm.

Cám ơn hai bạn,
Tuyết Liễu

 

[Lê Duy Thương]

Chào bạn HYen17,dhn46,

Mình đã thử công thức của hai bạn nhưng vẫn ko được. Xin hai bạn vui lòng chỉ thêm.

Cám ơn hai bạn,
Tuyết Liễu

thử công thức này cho F2 xem nhé

=IF(COUNTIF($B$1:$B2,$B2)>1,"",SUMIFS($C$2:$C$9,$B$2:$B$9,$B2,$A$2:$A$9,$A2))

 

[dhn46]

Chào bạn HYen17,dhn46,

Mình đã thử công thức của hai bạn nhưng vẫn ko được. Xin hai bạn vui lòng chỉ thêm.

Cám ơn hai bạn,
Tuyết Liễu

Không biết bạn có đọc từng từ và làm đúng phần màu đỏ in đậm không? Kết thúc Ctrl + Shift + Enter (3 phím cùng lúc) chứ không phải Enter

Bạn dùng công thức sau
Kết thúc Ctrl + Shift + Enter

=IF(E2>0,INDEX($I$3:$I$8,MATCH(MIN(IF($H$3:$H$8=B2,ABS((F2-$I$3:$I$8)),100^100)),IF($H$3:$H$8=B2,ABS((F2-$I$3:$I$8)),100^100),0)),"")

 

[siwtom]

Không biết bạn có đọc từng từ và làm đúng phần màu đỏ in đậm không? Kết thúc Ctrl + Shift + Enter (3 phím cùng lúc) chứ không phải Enter

Chắc có đọc

=IF(E2>0,INDEX($I$3:$I$8,MATCH(MIN(IF($H$3:$H$8=B2,ABS((F2-$I$3:$I$8)),100^100)),IF($H$3:$H$8=B2,ABS((F2-$I$3:$I$8)),100^100),0)),"")

Trong công thức cho ô F2 thì làm sao bạn có thể tham chiếu tới F2?

Có lẽ bạn gõ nhầm. Phải là E2. Mà sao nhiều ngoặc trong ABS thế?
Ngoài ra tôi không hiểu 100^100. Sao không đơn giản là ""?

=IF($E2;INDEX($I$3:$I$8;MATCH(MIN(IF($H$3:$H$8=$B2;ABS($I$3:$I$8-$E2);""));IF($H$3:$H$8=$B2;ABS($I$3:$I$8-$E2);"");0));"")

Kết thúc bằng Ctrl+Shift+Enter. Kéo xuống dưới

 

[tuyetlieu]

Chào bạn siwtom,

Cám ơn bạn. Mình đã sử dụng được công thức của bạn nhưng không hiểu. Bạn có thể vui lòng giải thích công thức giùm mình được không?

Cám ơn tất cả các thành viên đã giúp đỡ.

Tuyết Liễu

 

[siwtom]

Chào bạn siwtom,

Cám ơn bạn. Mình đã sử dụng được công thức của bạn nhưng không hiểu. Bạn có thể vui lòng giải thích công thức giùm mình được không?

Cám ơn tất cả các thành viên đã giúp đỡ.

Tuyết Liễu

Làm gì thì bạn cũng phải có ý tưởng, thuật toán. Và bạn cứ chia nhỏ bài toán ra những bài toán đơn giản.

1. Muốn tìm giá trị "gần nhất" trong những ngày trùng với B2 thì trước tiên phải có "những ngày trùng với B2" đó. Có được chúng thì mới xét tiếp xem anh nào cách E2 ít nhất. Ta gộp 2 bước này thành 1: Ta tạo 1 mảng tmp có số dòng bằng số dòng cột H. Xét các ngày trong cột H. Những ngày nào không trùng với B2 thì ta không xét, lờ đi, coi chúng như là không khí bằng cách cho ô tương ứng trong tmp bằng rỗng. Với các ngày trùng với B2 thì ta tính cự ly từ giá trị tương ứng ở cột I tới E2. Ta dùng giá trị tuyệt đối ABS vì hiệu giá trị có thể âm mà cự ly không thể âm (bạn không thể nói: cự ly từ Hà Nội tới Vinh là -500 km được).
Dễ thấy là

IF($H$3:$H$8=$B2;ABS($I$3:$I$8-$E2);"") trả về mảng tmp như đã nói ở trên

2. Để tìm giá trị "gần nhất" thì ta phải tìm ra dòng (chỉ số dòng) trong mảng tmp mà có giá tri (giá trị là các cự ly) nhỏ nhất. Vậy thì ta dùng hàm MATCH để tìm ra chỉ số dòng của giá trị nhỏ nhất. Tất nhiên giá trị cần tìm này chính là

MIN(tmp) = MIN(IF($H$3:$H$8=$B2;ABS($I$3:$I$8-$E2);""))

Gọi chỉ số dòng của ô có giá trị nhỏ nhất là k ta có

k = MATCH(MIN(tmp);tmp;0) =
MATCH(MIN(IF($H$3:$H$8=$B2;ABS($I$3:$I$8-$E2);""));IF($H$3:$H$8=$B2;ABS($I$3:$I$8-$E2);"");0)

Có chỉ số dòng của ô giá trị "gần nhất" rồi thì ô đó ta xác định bằng hàm INDEX. Gọi đó là "ô tốt" ta có

"ô tốt" = INDEX($I$3:$I$8;k) = INDEX($I$3:$I$8;MATCH(MIN(IF($H$3:$H$8=$B2;ABS($I$3:$I$8-$E2);""));IF($H$3:$H$8=$B2;ABS($I$3:$I$8-$E2);"");0))

Nhưng ta phải lường được trường hợp khi ô E2 trống, bằng 0

=IF($E2;"ô tốt";"") = 
IF($E2;INDEX($I$3:$I$8;MATCH(MIN(IF($H$3:$H$8=$B2;ABS($I$3:$I$8-$E2);""));IF($H$3:$H$8=$B2;ABS($I$3:$I$8-$E2);"");0));"")

 

[vodoi2x]

Làm gì thì bạn cũng phải có ý tưởng, thuật toán. Và bạn cứ chia nhỏ bài toán ra những bài toán đơn giản.

Quá chịu khó bác siwtom ah,

Đúng là giúp đã khó, bắt giải thích thì càng khó hơn, bác giải thích chu đáo thế này ai cũng muốn nhờ thui , hihihii