Tìm vị trí của giá trị cần tìm và tính toán với giá trị đó (1 người xem)

[KingVu]

Chào các bạn !!
Mình có một mảng dữ liệu từ A1:L31. Mình muốn tìm vị trí của giá trị lớn nhất trong chuỗi giá trị đó (325.9) sau đó lấy giá trì vừa tìm được cộng với 2 giá trị liền kề trở về trước (325.9 + 10.6 + 16.5 ). Các bạn giúp mình với nhé.
Cám ơn rất nhiều.

 

[SA_DQ]

Mình làm bằng hàm tự tạo, như sau

Option Explicit
Function SumMax(Rng As Range)
 Dim sRng As Range, WF As Object
 Dim Max_ As Double
 
 Set WF = Application.WorksheetFunction
 Max_ = WF.Max(Rng)
 Set sRng = Rng.Find(Max_, , xlFormulas, xlWhole)
 SumMax = WF.Sum(Range(sRng, sRng.Offset(-2)))
End Function

Tại 1 ô trống nào đó, ta nhập công thức:

=SumMax(A1:L31)

 

[siwtom]

Option Explicit
Function SumMax(Rng As Range)
 Dim sRng As Range, WF As Object
 Dim Max_ As Double
 
 Set WF = Application.WorksheetFunction
 Max_ = WF.Max(Rng)
 Set sRng = Rng.Find(Max_, , xlFormulas, xlWhole)
 SumMax = WF.Sum(Range(sRng, sRng.Offset(-2)))
End Function

Tại 1 ô trống nào đó, ta nhập công thức:

=SumMax(A1:L31)

Nếu tôi không lầm thì ...

Nếu ai đó (đối tác?) nhập code của bạn mà người ta có thiết lập dấu phẩy "," là dấu thập phân thì code sẽ có lỗi.
Bạn cứ thử vào CP đổi dấu rồi thì ô trước đó là 380 thì bây giờ là #VALUE!
---------
@KingVu: Bạn nên "mở rộng, giải thích" cái khái niệm "2 giá trị liền kề trở về trước" của mình.

Vì tôi đoán là "liền kề cùng cột" chứ không phải "liền kề cùng dòng". Nhưng nếu giá trị max đó tìm được ở dòng 2 hoặc 1 thì "2 giá trị liền kề trở về trước" là 2 giá trị nào?

Ngoài ra nếu có > 1 giá trị max (vd. 2 giá trị 352,9) thì quyết định như thế nào?
-------------
Bạn nên nhớ về sau là tất cả những khái niệm bạn tự "bịa" ra thì đều phải định nghĩa rõ ràng. Không nhất thiết người khác sẽ hiểu theo cách của bạn.

 

[nhapmon]

Chào các bạn !!
Mình có một mảng dữ liệu từ A1:L31. Mình muốn tìm vị trí của giá trị lớn nhất trong chuỗi giá trị đó (325.9) sau đó lấy giá trì vừa tìm được cộng với 2 giá trị liền kề trở về trước (325.9 + 10.6 + 16.5 ). Các bạn giúp mình với nhé.
Cám ơn rất nhiều.

tôi thử giải bằng cthức
C33=MAX(A1:L31)
A32=COUNTIF(A4:A31,$C$33)--->kéo qua phải (làm hàng phụ,để xác định cột nào chứa giá trị cần tìm)
c34=SUM(OFFSET($A$4,MATCH(C33,OFFSET($A$4:$A$30,,MATCH(1,A32:L32,0)-1),0)-1,MATCH(1,A32:L32,0)-1,-3))
E34=OFFSET($A$4,MATCH(C33,OFFSET($A$4:$A$30,,MATCH(1,A32:L32,0)-1),0)-2,MATCH(1,A32:L32,0)-1)

bạn có thể tham khảo thêm một chủ đề rất gần với chủ đề của bạn, ở topic này
http://www.giaiphapexcel.com/forum/showthread.php?83589-Hàm-Tìm-kiếm-nhiều-vùng
đang được thảo luận bởi các bậc đại đại cao thủ

 

[KingVu]

Nếu tôi không lầm thì ...

Nếu ai đó (đối tác?) nhập code của bạn mà người ta có thiết lập dấu phẩy "," là dấu thập phân thì code sẽ có lỗi.
Bạn cứ thử vào CP đổi dấu rồi thì ô trước đó là 380 thì bây giờ là #VALUE!
---------
@KingVu: Bạn nên "mở rộng, giải thích" cái khái niệm "2 giá trị liền kề trở về trước" của mình.

Vì tôi đoán là "liền kề cùng cột" chứ không phải "liền kề cùng dòng". Nhưng nếu giá trị max đó tìm được ở dòng 2 hoặc 1 thì "2 giá trị liền kề trở về trước" là 2 giá trị nào?

Ngoài ra nếu có > 1 giá trị max (vd. 2 giá trị 352,9) thì quyết định như thế nào?
-------------
Bạn nên nhớ về sau là tất cả những khái niệm bạn tự "bịa" ra thì đều phải định nghĩa rõ ràng. Không nhất thiết người khác sẽ hiểu theo cách của bạn.

Chào bạn. Rất mong bạn thông cảm vì sự trình bày không rõ ràng. Mình đã bổ sung vào trong bảng excel giá trị cột là tháng và giá trị hàng là ngày. Do đó khi dịch giá trị liền kề thì tức là dịch "liền kề cùng cột về quá khứ". Tuy nhiên nếu giá trị max đó tìm được ở dòng 2 hoặc 1 thì chắc phải dịch sang giá trị cuối cùng của cột liền kề bên trái. Ngoài ra nếu có 2 giá trị max thì mình lấy giá max cuối cùng ( tức là giá trị cuối cùng trong chuỗi số).
Cám ơn bạn đã có những nhận xét đúng.

 

[concogia]

Chào bạn. Rất mong bạn thông cảm vì sự trình bày không rõ ràng. Mình đã bổ sung vào trong bảng excel giá trị cột là tháng và giá trị hàng là ngày. Do đó khi dịch giá trị liền kề thì tức là dịch "liền kề cùng cột về quá khứ". Tuy nhiên nếu giá trị max đó tìm được ở dòng 2 hoặc 1 thì chắc phải dịch sang giá trị cuối cùng của cột liền kề bên trái. Ngoài ra nếu có 2 giá trị max thì mình lấy giá max cuối cùng ( tức là giá trị cuối cùng trong chuỗi số).
Cám ơn bạn đã có những nhận xét đúng.

Nếu giá trị Max nằm ở cell [B2] hoặc cell [B3] thì sao vậy bạn ?

 

[siwtom]

Tuy nhiên nếu giá trị max đó tìm được ở dòng 2 hoặc 1 thì chắc phải dịch sang giá trị cuối cùng của cột liền kề bên trái.

"chắc phải"? Thế là thế nào? Bạn không biết mình muốn gì? Định làm gì, thế nào là do bạn quyết định chứ đâu phải là do người khác đề nghị? Người khác chỉ có thể "chiều" bạn mà thôi.

Mà bạn vẫn không nói nếu không có "cột liền kề bên trái" - khi max rơi vào B2 hoặc B3 - thì xử lý thế nào.

Cứ kiểu này cò cưa mãi thì đến Tết. Do tôi đã trót tham gia nên tôi tự xét 2 trường hợp. Nếu hợp với ý bạn thì tốt, vì tôi kết thúc ở đây.
---------------
Tôi xét 2 trường hợp. Có cả công thức và code. Bạn để ý là với code của tôi thì bạn cứ vô tư, thiết lập dấu thập phân là dấu phẩy hay dấu chấm đều chơi tuốt.

0. Đặt 2 name
pos =MAX(($B$2:$M$32=MAX($B$2:$M$32))*((COLUMN($B$2:$M$32)-1)*100+ROW($B$2:$M$32)-1))

maxcell =INDEX($B$2:$M$32;MOD(pos;100);INT(pos/100);1)

1. Trường hợp 1. Nếu nhiều max thì lấy ở cột cuối cùng. Nếu cột cuối cùng chứa nhiều max thì lấy ở dòng cuối cùng. Nếu max ở dòng 2 hoặc 3 của bảng tính thì lấy được bao nhiêu thì lấy còn "phần còn lại" lấy ở cột trước tính từ dòng cuối cùng. Nếu không có cột trước, tức max ở cột B thì thôi

công thức cho D35

=IF(MOD(pos;100)>=3;SUM(OFFSET(maxcell;-2;;3));SUM(OFFSET(maxcell;1-MOD(pos;100);;MOD(pos;100))[COLOR=#ff0000];IF(INT(pos/100)>1;OFFSET(maxcell;29;-1;3-MOD(pos;100));0)[/COLOR]))

Nếu dùng hàm thì công thức cho D35 =MaxAndSum(B2:M32)

Function MaxAndSum(Rng As Range)
Dim cell_ As Range, maxValue As Double, ds As String, us As Boolean
    With Application
        ds = .DecimalSeparator
        us = .UseSystemSeparators
        .DecimalSeparator = "."
        .UseSystemSeparators = False
    End With
    
    maxValue = WorksheetFunction.Max(Rng)
    Set cell_ = Rng.Find(maxValue, , xlFormulas, xlWhole, xlByColumns, xlPrevious)
    If cell_.Row >= 4 Then
       MaxAndSum = WorksheetFunction.Sum(cell_.Offset(-2).Resize(3))
    Else
       MaxAndSum = WorksheetFunction.Sum(cell_.Offset(2 - cell_.Row).Resize(cell_.Row - 1))
       [COLOR=#0000ff]If cell_.Column > 2 Then MaxAndSum = MaxAndSum + WorksheetFunction.Sum(cell_.Offset(29, -1).Resize(4 - cell_.Row))[/COLOR]
    End If
    
    With Application
        .DecimalSeparator = ds
        .UseSystemSeparators = us
    End With
End Function

2. Trường hợp 2. Nếu nhiều max thì lấy ở cột cuối cùng. Nếu cột cuối cùng chứa nhiều max thì lấy ở dòng cuối cùng. Nếu max ở dòng 2 hoặc 3 của bảng tính thì lấy được bao nhiêu thì lấy

công thức cho D35

=IF(MOD(pos;100)>=3;SUM(OFFSET(maxcell;-2;;3));SUM(OFFSET(maxcell;1-MOD(pos;100);;MOD(pos;100))))

tức công thức ở điểm 1 bỏ đi chỗ đỏ đỏ

Nếu dùng hàm thì công thức cho D35 =MaxAndSum(B2:M32)

Function MaxAndSum(Rng As Range)
Dim cell_ As Range, maxValue As Double, ds As String, us As Boolean
    With Application
        ds = .DecimalSeparator
        us = .UseSystemSeparators
        .DecimalSeparator = "."
        .UseSystemSeparators = False
    End With
    
    maxValue = WorksheetFunction.Max(Rng)
    Set cell_ = Rng.Find(maxValue, , xlFormulas, xlWhole, xlByColumns, xlPrevious)
    If cell_.Row >= 4 Then
       MaxAndSum = WorksheetFunction.Sum(cell_.Offset(-2).Resize(3))
    Else
       MaxAndSum = WorksheetFunction.Sum(cell_.Offset(2 - cell_.Row).Resize(cell_.Row - 1))
    End If
    
    With Application
        .DecimalSeparator = ds
        .UseSystemSeparators = us
    End With
End Function

tức công thức ở điểm 1 bỏ đi chỗ xanh xanh