Hỏi cách trả về giá trị cột đầu tiên khi biết trước 1 số trong bảng

[hrfantasy]

Kính gửi các anh/chị
Em muốn tham chiếu giá trị của một số cho trước có trong bảng để từ đó lập công thức trả về giá trị cột đầu tiên hoặc hàng dâu tiên, em có dùng vlookup nhưng vẫn ko ra, mọi người giúp em với ạ> Thanks cả nhà nhiều...

 

[bebo021999]

Bậc:

=SUMPRODUCT(($B$16:$H$21=A3)*ROW($B$16:$H$21))-15

Mức:

=SUMPRODUCT(($B$16:$H$21=A3)*COLUMN($B$16:$H$21))-1

Lưu ý: Số 15 tương ứng với vị trí dòng của bậc 1 trong bảng tham chiếu trừ đi 1 (16-1); Số 1 tương ứng số cột của bậc 1 trừ đi 1. Nếu bảng trong file thực tế của bạn khác với file này thì căn cứ theo quy luật này mà sửa số cho phù hợp

 

[hrfantasy]

Cám ơn bạn bebo021999 đã giúp đỡ mình, mình thấy những câu trả lời của bạn rất hay và cụ thể, hi vọng một ngày nào đó mình sẽ có khả năng excel gần bằng bạn, hj

 

[ndu96081631]

Bậc:

=SUMPRODUCT(($B$16:$H$21=A3)*ROW($B$16:$H$21))-15

Mức:

=SUMPRODUCT(($B$16:$H$21=A3)*COLUMN($B$16:$H$21))-1

Lưu ý: Số 15 tương ứng với vị trí dòng của bậc 1 trong bảng tham chiếu trừ đi 1 (16-1); Số 1 tương ứng số cột của bậc 1 trừ đi 1. Nếu bảng trong file thực tế của bạn khác với file này thì căn cứ theo quy luật này mà sửa số cho phù hợp

Vầy hay hơn:
Mức:

=SUMPRODUCT(($B$16:$H$21=A3)*$A$16:$A$21)

Bậc:

=SUMPRODUCT(($B$16:$H$21=A3)*$B$15:$H$15)

Khỏi có -15, -1 gì cả ---> Bậc bao nhiêu ra bao nhiêu, mức bao nhiêu ra bấy nhiêu

 

[hrfantasy]

cũng với vấn đề này, mình muốn tìm ngược lại, từ bậc cũ, mức cũ tìm ra bậc mới, mức mới chút liệu có công thức nào làm được ko nhỉ? (Chi tiết như file mình đính kèm)

 

[nghiaphuc]

cũng với vấn đề này, mình muốn tìm ngược lại, từ bậc cũ, mức cũ tìm ra bậc mới, mức mới chút liệu có công thức nào làm được ko nhỉ? (Chi tiết như file mình đính kèm)

Mức mới: Tạm thời là thế này, trong khi chờ công thức khác hay hơn:

D3=INDEX(OFFSET($C$15:$I$15,A3+(MATCH(B3,OFFSET($C$15:$I$15,A3,0),1)=7),0),MATCH(B3,OFFSET($C$15:$I$15,A3+(MATCH(B3,OFFSET($C$15:$I$15,A3,0),1)=7),0),1)+1)

Bậc mới thì công thức giống như trên (dựa vào mức mới để tìm).

 

[ndu96081631]

cũng với vấn đề này, mình muốn tìm ngược lại, từ bậc cũ, mức cũ tìm ra bậc mới, mức mới chút liệu có công thức nào làm được ko nhỉ? (Chi tiết như file mình đính kèm)

Bạn cho vài ví dụ cụ thể luôn đi cho dễ hình dung:
- Với mức cũ=2560000, bậc cũ=1 thì mức mới và bậc mới = bao nhiêu?
- Với mức cũ=3669000, bậc cũ=3 thì mức mới và bậc mới = bao nhiêu?
vân vân

 

[hrfantasy]

Ví dụ:
Với mức cũ 2560000, bậc cũ là 1 thì mức mới trả về 2660000, bậc mới vẫn là 1
Với mức cũ 3669000, bậc cũ là 3 thì mức mới trả về 3685000, bậc mới là 2 (đảm bảo cả 2 điều kiện là hiệu số mức mới - mức cũ là nhỏ nhất và chênh lệch bậc cũ và bậc mới không quá 2 bậc) Chỉ nghĩ thôi đã thấy phức tạp rồi...hic

 

[ndu96081631]

Ví dụ:
Với mức cũ 2560000, bậc cũ là 1 thì mức mới trả về 2660000, bậc mới vẫn là 1
Với mức cũ 3669000, bậc cũ là 3 thì mức mới trả về 3685000, bậc mới là 2 (đảm bảo cả 2 điều kiện là hiệu số mức mới - mức cũ là nhỏ nhất và chênh lệch bậc cũ và bậc mới không quá 2 bậc) Chỉ nghĩ thôi đã thấy phức tạp rồi...hic

Bạn chú ý chổ màu đỏ nha
Tra trong bảng chẳng thấy chổ nào giống vậy cả
Nếu mức mới là 3685000 thì tra vào ta được bậc =6 chứ không phải 2

 

[hrfantasy]

3685000 tra vào là bậc 2 mức 6 mà, bậc là theo chiều dọc đó ạ (từ 1-6), còn mức theo chieu ngang (từ 1-7), trong cái file đính kèm của em quên chưa ghi đảm bảo điều kiện (mức mới > mức cũ và trị tuyệt đối chênh lẹch bậc mới - bậc cũ là nhỏ nhất ạ

 

[ndu96081631]

3685000 tra vào là bậc 2 mức 6 mà, bậc là theo chiều dọc đó ạ (từ 1-6), còn mức theo chieu ngang (từ 1-7), trong cái file đính kèm của em quên chưa ghi đảm bảo điều kiện (mức mới > mức cũ và trị tuyệt đối chênh lẹch bậc mới - bậc cũ là nhỏ nhất ạ

Uh, tôi nhầm! Nhìn rối óc quá
Thử thế này:
Mức:

=MIN(IF(($C$16:$I$21>B3)*($B$16:$B$21>=A3-1)*($B$16:$B$21<=A3+1),$C$16:$I$21,""))

Bậc:

=SUMPRODUCT(($C$16:$I$21=D3)*$B$16:$B$21)

Riêng công thức tính mức, gõ xong phải bấm tổ hợp phím Ctrl + Shift + Enter nhé
----------------
Bạn kiểm tra lại xem, có sai sót gì ta bàn tiếp

 

[hrfantasy]

Cảm ơn anh ndu96081631, theo điều kiện trên thì em đã làm ok rồi
Đúng là em còn kém mấy hàm mảng này quá, hic!
Em mày mò mãi ko ra, vấn đề là em đang định dựa vào bậc cũ, mức cũ để xếp vào bậc mới mức mới, đảm bảo: mức mới phải cao hơn mức cũ, trường hợp mức cao nhất trong bậc cũ vẫn chưa cao hơn mức cũ thì phải xếp lên bậc trên nhưng vẫn đảm bảo mức mới lớn hơn mức cũ. (mức mới> mức cũ, Bậc mới lớn hơn hoặc bằng bậc cũ, và hiệu mức mới - mức cũ là nhỏ nhất có thể). Đau đầu quá đi, em dựa vào công thức của anh mà tìm mãi không ra được.hic
Ví dụ: tại cột c5, giá trị trả về là 3863000 chứ ko phải là 3714000 (vì bậc cũ là 4 nên ko thể xếp xuống 3), òa òa

 

[ndu96081631]

Cảm ơn anh ndu96081631, theo điều kiện trên thì em đã làm ok rồi
Đúng là em còn kém mấy hàm mảng này quá, hic!
Em mày mò mãi ko ra, vấn đề là em đang định dựa vào bậc cũ, mức cũ để xếp vào bậc mới mức mới, đảm bảo: mức mới phải cao hơn mức cũ, trường hợp mức cao nhất trong bậc cũ vẫn chưa cao hơn mức cũ thì phải xếp lên bậc trên nhưng vẫn đảm bảo mức mới lớn hơn mức cũ. (mức mới> mức cũ, Bậc mới lớn hơn hoặc bằng bậc cũ, và hiệu mức mới - mức cũ là nhỏ nhất có thể). Đau đầu quá đi, em dựa vào công thức của anh mà tìm mãi không ra được.hic
Ví dụ: tại cột c5, giá trị trả về là 3863000 chứ ko phải là 3714000 (vì bậc cũ là 4 nên ko thể xếp xuống 3), òa òa

Tại bạn mô tả không rõ ràng chi
Sửa công thức D3 thành vầy xem:

=MIN(IF(($C$16:$I$21>B3)*($B$16:$B$21>=A3)*($B$16:$B$21<=A3+2),$C$16:$I$21,""))

Vẫn phải Ctrl + Shift + Enter nhé