Công thức dò tìm giá trị (5 người xem)

[bee111]

Xin chào tất cả các anh chi trên diên đàn, em có bài tập dò tìm gia trị lớn nhất từ 2 cột dữ liệu, kết quả trả về tại 1 ô là gia trị ngày và tháng tương ứng với gia trị đó. cấu trúc dữ liệu có file em gửi kèm theo, nhờ các anh chị giúp em.

 

[bebo021999]

Xin chào tất cả các anh chi trên diên đàn, em có bài tập dò tìm gia trị lớn nhất từ 2 cột dữ liệu, kết quả trả về tại 1 ô là gia trị ngày và tháng tương ứng với gia trị đó. cấu trúc dữ liệu có file em gửi kèm theo, nhờ các anh chị giúp em.

Nếu chỉ dò trong tháng 7 và 8 thì đơn giản, tuy nhiên mình làm công thức áp dụng cho dò tìm cả năm luôn, đỡ mất công hỏi nữa.

="Gia tri lon nhat la "&MAX($C$3:$N$33)&" vao ngay "&MAX(IF($C$3:$N$33=MAX($C$3:$N$33),ROW($C$3:$N$33),""))-2&" thang "&MAX(IF($C$3:$N$33=MAX($C$3:$N$33),COLUMN($C$3:$N$33),""))-2

Kết thúc bằng Ctrl-Shift-Enter

Điều kiện là ngày, tháng phải liên tục.

 

[bee111]

cảm ơn bác BEBO021999, ham cua bác chạy rất tốt, bác có thể giải thich công thức đó cho em hiểu được không a?

 

[bebo021999]

cảm ơn bác BEBO021999, ham cua bác chạy rất tốt, bác có thể giải thich công thức đó cho em hiểu được không a?

Giá trị lớn nhất trong vùng:MAX($C$3:$N$33) = 800
IF($C$3:$N$33=MAX($C$3:$N$33),ROW($C$3:$N$33),"") -> Tìm giá trị lớn nhất (800) trong vùng, trả về thứ tự dòng (=20)
Trừ đi 2 sẽ ra giá trị ngày (=18)
Tương tự cho giá trị tháng.

 

[bee111]

hàm dò tìm giá trị

em cảm ơn bác đã tận tình chỉ bảo, em lại phiền bác tí nữa, vẫn bài toán trên thay vì tìm giá trị lớn nhất của tất cả 12 tháng mà chỉ tìm giá trị lớn nhất của 2 tháng là tháng 3 và tháng 5 (2 cột tháng này không liền nhau, và trong bảng có dữ liệu của nhiều tháng). Bác làm giúp em với

 

[bebo021999]

em cảm ơn bác đã tận tình chỉ bảo, em lại phiền bác tí nữa, vẫn bài toán trên thay vì tìm giá trị lớn nhất của tất cả 12 tháng mà chỉ tìm giá trị lớn nhất của 2 tháng là tháng 3 và tháng 5 (2 cột tháng này không liền nhau, và trong bảng có dữ liệu của nhiều tháng). Bác làm giúp em với

Nếu dữ liệu sắp xếp đúng như vậy (từ tháng 6-12):
Ctrl-F3, đặt Name: Ngay

=IF(ISERROR(MATCH(MAX('CB-13'!$G$6:$G$36,'CB-13'!$J$6:$J$36),'CB-13'!$J$6:$J$36,0)),MATCH(MAX('CB-13'!$G$6:$G$36,'CB-13'!$J$6:$J$36),'CB-13'!$G$6:$G$36,0)&" / "&7,MATCH(MAX('CB-13'!$G$6:$G$36,'CB-13'!$J$6:$J$36),'CB-13'!$J$6:$J$36,0)&" / "&8)

Công thức tính :

="Gia tri tham so lon nhat la "&MAX($G$6:$G$36,$J$6:$J$36)&" vao ngay "&TRIM(LEFT(Ngay,2))&" thang "&TRIM(RIGHT(Ngay,2))

 

[bee111]

cảm ơn bác Bebo021999, ham của bác chạy rất tốt, nhưng bác có cách nào ngắn gọn hơn được không a?

 

[bee111]

Xin chào bác Bebo021999, em lại phiền bác lần nữa, cung như bài toán lần trước nhưng so sánh thêm 1 cột nữa và giá trị trả vè của tháng là chữ số La mã như ở dòng tiêu đề ấy. Bác giúp em với nhé, xin cảm ơn bác trước

 

[concogia]

Xin chào bác Bebo021999, em lại phiền bác lần nữa, cung như bài toán lần trước nhưng so sánh thêm 1 cột nữa và giá trị trả vè của tháng là chữ số La mã như ở dòng tiêu đề ấy. Bác giúp em với nhé, xin cảm ơn bác trước

Tối nay buồn quá, làm tạm cái hàm "xi-ma-chao" này để thay buồn bằng....nhức cái đầu chơi. Híc

="Gía trị tham số lớn nhất là "&MAX($G$6:$G$36,$J$6:$J$36,$M$6:$M$36)&" vào ngày "&MATCH("*-"&MAX($G$6:$G$36,$J$6:$J$36,$M$6:$M$36)&"-*",("-"&$G$6:$G$36&"-"&$J$6:$J$36&"-"&$M$6:$M$36&"-"),0)&" tháng "&IF(MAX($G$6:$G$36)=MAX($G$6:$G$36,$J$6:$J$36,$M$6:$M$36),$E$2,IF(MAX($J$6:$J$36)=MAX($G$6:$G$36,$J$6:$J$36,$M$6:$M$36),$H$2,$K$2))

Óp-la gì xa quá, hổng đi nhậu được cũng buồn
Thân

 

[mhung12005]

Tối nay buồn quá, làm tạm cái hàm "xi-ma-chao" này để thay buồn bằng....nhức cái đầu chơi. Híc

Óp-la gì xa quá, hổng đi nhậu được cũng buồn
Thân

Bác concogia cho em hỏi chút: Em không hiểu 2 ký tự "*-" và "-*" có ý nghĩa như thế nào. Bác có thể giải thích cho em được không ? Cảm ơn bác.

 

[concogia]

Bác concogia cho em hỏi chút: Em không hiểu 2 ký tự "*-" và "-*" có ý nghĩa như thế nào. Bác có thể giải thích cho em được không ? Cảm ơn bác.

Giá trị MAX có thể nằm ở 1 cột bất kỳ trong 3 cột, mình dùng hàm MATCH để xác định giá trị MAX nằm ở hàng nào
Giá trị dò *-giá trị MAX-* trong đó * là ký tự đại diện (bạn xem thêm trong phần Help của hàm MATCH), dấu "-" ở 2 đầu để tìm đúng giá trị MAX trong vùng dò ( vì không biết giá trị MAX có bao nhiêu ký tự nên khi ráp 3 cột G, J và M để tạo vùng dò mình chèn thêm dấu "-" ở 2 đầu để phân biệt )
Đây chỉ là một cách làm của mình thôi, mình vừa "quất" xong lon thứ 5, hơi "tưng tưng", có thể giải thích không rõ lắm, mong bạn thông cảm. Híc
Thân

 

[bee111]

ham do tim gia tri

em cảm [n bac concogia , hàm của bác rất hay, bác giúp em 2 bài toán trên mà bác Bebo đã làm cho em nhung em muốn sua lại tên tháng bằng chũ La Mã nhu hàm của bác làm vua rùi , bác giải sầu giúp em nhé

 

[mhung12005]

Giá trị MAX có thể nằm ở 1 cột bất kỳ trong 3 cột, mình dùng hàm MATCH để xác định giá trị MAX nằm ở hàng nào
Giá trị dò *-giá trị MAX-* trong đó * là ký tự đại diện (bạn xem thêm trong phần Help của hàm MATCH), dấu "-" ở 2 đầu để tìm đúng giá trị MAX trong vùng dò ( vì không biết giá trị MAX có bao nhiêu ký tự nên khi ráp 3 cột G, J và M để tạo vùng dò mình chèn thêm dấu "-" ở 2 đầu để phân biệt )
Đây chỉ là một cách làm của mình thôi, mình vừa "quất" xong lon thứ 5, hơi "tưng tưng", có thể giải thích không rõ lắm, mong bạn thông cảm. Híc
Thân

Bác giải thích như vậy là ok rồi. "tưng tưng" rồi thêm 1 lon nữa là chẳng càn chút sầu nào nữa. Chúc bác luôn vui. Cảm ơn bác nhiều.

 

[concogia]

em cảm [n bac concogia , hàm của bác rất hay, bác giúp em 2 bài toán trên mà bác Bebo đã làm cho em nhung em muốn sua lại tên tháng bằng chũ La Mã nhu hàm của bác làm vua rùi , bác giải sầu giúp em nhé

Côn thức Gt1:

="Gia tri lon nhat la "&MAX($I$3:$J$33)&" vao ngay "&MAX(IF($C$3:$N$33=MAX($C$3:$N$33),ROW($C$3:$N$33),""))-2&" thang "&IF(MAX($I$3:$I$33)=MAX($I$3:$J$33),$I$1,$J$1)

Công thức Gt2:

="Gia tri tham so lon nhat la "&MAX($G$6:$G$36,$J$6:$J$36)&" vao ngay "&MATCH("*-"&MAX($G$6:$G$36,$J$6:$J$36)&"-*",("-"&$G$6:$G$36&"-"&$J$6:$J$36&"-"),0)&" thang "&IF(MAX(G6:G36)=MAX($G$6:$G$36,$J$6:$J$36),$E$2,$H$2)

Thân

 

[vodoi2x]

Côn thức Gt1:

Công thức Gt2:

Thân

Công thức thế là chuẩn rồi NHƯNG:

Bác cò già ah, nên thay ký tự "-" trong các công thức của bạn bằng ký tự khác đặc biệt hơn ví như "#" hay "@" ...vv vì nếu không công thức sẽ SAI khi có số âm trong các cột số liệu đang xét

Không rõ ý nhỏ vậy có chính xác không ạ?

 

[concogia]

Công thức thế là chuẩn rồi NHƯNG:

Bác cò già ah, nên thay ký tự "-" trong các công thức của bạn bằng ký tự khác đặc biệt hơn ví như "#" hay "@" ...vv vì nếu không công thức sẽ SAI khi có số âm trong các cột số liệu đang xét

Không rõ ý nhỏ vậy có chính xác không ạ?

Mình cũng chẳng để ý nữa, nhưng hình như số âm vẫn đúng hay sao í ( 2 dấu "-" mình thêm vào mà, dấu "trừ" nếu có thì vẫn còn nguyên đó chứ có chạy đi đâu đâu, lúc gom 3 cột nó thành dạng Text rồi mà ), bạn test giúp mình xem sao
Mà cũng chẳng sao, nếu chủ topic này nói có khi toàn số âm không thì ta xem kết quả có chạy...bậy hông, nếu sai ta lại....sửa. Híc
Thân

 

[vodoi2x]

Mình cũng chẳng để ý nữa, nhưng hình như số âm vẫn đúng hay sao í ( 2 dấu "-" mình thêm vào mà, dấu "trừ" nếu có thì vẫn còn nguyên đó chứ có chạy đi đâu đâu, lúc gom 3 cột nó thành dạng Text rồi mà ), bạn test giúp mình xem sao
Mà cũng chẳng sao, nếu chủ topic này nói có khi toàn số âm không thì ta xem kết quả có chạy...bậy hông, nếu sai ta lại....sửa. Híc
Thân

SAI bác ah, bác cứ nhập thử
-500 vào G13 xem ,==> ngày max sẽ sai (khi đó là ngày 8 thay vì ĐÚNG là 19)

Nên cứ có 1 số âm mà trị tuyệt đối trùng giá trị Max và nằm hàng trên thì Kết quả ngày sẽ sai (chứ không cần phải tất cả âm ah)

 

[concogia]

SAI bác ah, bác cứ nhập thử
-500 vào G13 xem ,==> ngày max sẽ sai (khi đó là ngày 8 thay vì ĐÚNG là 19)

Nên cứ có 1 số âm mà trị tuyệt đối trùng giá trị Max và nằm hàng trên thì Kết quả ngày sẽ sai (chứ không cần phải tất cả âm ah)

Ừ nhỉ, nếu có trường hợp này xảy ra. Vậy, muốn chắc thì đừng "chơi" dấu "-" nữa.
Nhưng nhìn bảng của bạn bee111 thì khó có khả năng xảy ra trường hợp hi hữu đó
Híc

 

[bee111]

xin chào các bác, cảm ơn các bác đã than gia topic cua em, tát cả các số liệu trong bảng đều là số dương bác ạ

 

[bebo021999]

Cám ơn bác cò già trong lúc bebo offline đã giải quyết giúp bài toán 3 cột.

Tuy nhiên công thức của bác chưa ổn lắm do:

- Vướng số âm. Dù chủ topic không yêu cầu.
- Theo quy luật của chủ topic, điều kiện lúc đầu là tìm trong 2 tháng, sau nâng lên 3 tháng. Giả sử ngày mai chủ topic yêu cầu 12 cột của 12 tháng thì "tèo"

Bebo mạn phép làm công thức luôn cho 12 tháng, âm dương đều được. Giải thích luôn:

Lợi dụng quy luật xét các cột có tiêu đề là "R":

* Giá trị lớn nhất:

MAX(IF($B$4:$AK$4="R",$B$6:$AK$36,0))

* Ngày:

MAX(IF([COLOR=#ff0000]IF($B$4:$AK$4="R",$B$6:$AK$36,0)=MAX(IF($B$4:$AK$4="R",$B$6:$AK$36,0))[/COLOR],[COLOR=#000080]$A$6:$A$36[/COLOR],""))

Trong đó màu đỏ xác định vị trí có giá trị MAX. Màu xanh là lấy giá trị ngày tương ứng

* Tháng:

INDEX($A$2:$AK$2,,MAX(IF([COLOR=#ff0000]IF($B$4:$AK$4="R",$B$6:$AK$36,0)=MAX(IF($B$4:$AK$4="R",$B$6:$AK$36,0))[/COLOR],[COLOR=#000080]COLUMN($B$2:$AK$2)-2[/COLOR],"")))

Trong đó màu đỏ xác định vị trí có giá trị MAX. Màu xanh là lấy số thứ tự cột chứa MAX tương ứng trừ (-) đi 2 ra cột tương ứng có chứa tháng.

Công thức đầy đủ:

="gia tri tham so lon nhat la "&MAX(IF($B$4:$AK$4="R",$B$6:$AK$36,0))&" vao ngay "&MAX(IF(IF($B$4:$AK$4="R",$B$6:$AK$36,0)=MAX(IF($B$4:$AK$4="R",$B$6:$AK$36,0)),$A$6:$A$36,""))&" thang "&INDEX($A$2:$AK$2,,MAX(IF(IF($B$4:$AK$4="R",$B$6:$AK$36,0)=MAX(IF($B$4:$AK$4="R",$B$6:$AK$36,0)),COLUMN($B$2:$AK$2)-2,"")))

Kết thúc bẳng Ctrl-Shift-Enter

Các sư phụ (nhất là vodoi2X) chỉ giáo thêm, vì có cách ngắn và đơn giản hơn cách này.