Cần giúp đỡ về tự động so sánh số liệu trong excel (6 người xem)

[ghzktc]

Em có một file.
ở sheet 1 là dữ liệu để nội suy
ở sheet 2 em nhập công thức nội suy
Vậy có hàm nào mà em chỉ cần nhập số vào ô giá trị cần nội suy thì excel sẽ tự động so sánh và nhập số nhỏ hơn gần nhất so với giá trị cần nội suy vào ô giá trị đầu, số lớn hơn gần nhất vào ô giá trị sau không ạ
Nếu làm được như vậy thì ở ô tham số tương ứng em chỉ cần dùng hàm Index là có thể tự động suy ra kết quả nội suy

 

[dat_butmuc]

Em có một file.
ở sheet 1 là dữ liệu để nội suy
ở sheet 2 em nhập công thức nội suy
Vậy có hàm nào mà em chỉ cần nhập số vào ô giá trị cần nội suy thì excel sẽ tự động so sánh và nhập số nhỏ hơn gần nhất so với giá trị cần nội suy vào ô giá trị đầu, số lớn hơn gần nhất vào ô giá trị sau không ạ
Nếu làm được như vậy thì ở ô tham số tương ứng em chỉ cần dùng hàm Index là có thể tự động suy ra kết quả nội suy

Thực sự không phải ai cũng làm công việc như bạn nên hiểu được vấn đề bạn nói, vì thế để mọi người hiểu & có thể hỗ trợ cho bạn thì:
- Bạn nêu rõ chi tiết xem lấy giá trị của Cột/Ô nào của Sheet 1 cho các giá trị đầu/giá trị cuối/giá trị nội suy,...
- Bạn cho biết một số ví dụ thật chi tiết và kết quả mong muốn & mô tả quy luật của nó ....
=> Càng chi tiết thì càng dễ dàng tiếp cận hơn & nhiều người có thể giúp bạn được !

 

[ghzktc]

Mình ví dụ tra A của ϕtc = 5,5, vì trong bảng dữ liệu không có giá trị 5,5 nên phải tiến hành nội suy từ giá trị trước và sau của 5,5 có trong bảng tính là 4 và 6.
Vậy nên ý mình là có hàm nào để nếu chỉ cần nhập 5,5 vào ô giá trị nội suy thì 4 và 6 sẽ được tự động so sánh và nhập vào 2 ô giá trị đầu và giá trị cuối hay không, mình có làm ví dụ vào file, bạn xem thử nhé, công thức thì mình đã nhập sẵn vào ô màu vàng, vì để có kết quả phải nhập 5 dữ liệu, nên nếu có thể dùng hàm để đơn giản thì chỉ cần nhập 1 ô giá trị cần nội suy mà thôi.

 

[MinhCong]

Mình ví dụ tra A của ϕtc = 5,5, vì trong bảng dữ liệu không có giá trị 5,5 nên phải tiến hành nội suy từ giá trị trước và sau của 5,5 có trong bảng tính là 4 và 6.
Vậy nên ý mình là có hàm nào để nếu chỉ cần nhập 5,5 vào ô giá trị nội suy thì 4 và 6 sẽ được tự động so sánh và nhập vào 2 ô giá trị đầu và giá trị cuối hay không, mình có làm ví dụ vào file, bạn xem thử nhé, công thức thì mình đã nhập sẵn vào ô màu vàng, vì để có kết quả phải nhập 5 dữ liệu, nên nếu có thể dùng hàm để đơn giản thì chỉ cần nhập 1 ô giá trị cần nội suy mà thôi.

Không biết như vầy có đúng ý Bạn không. Xem file nhé! Tuy nhiên Tham số tương ứng sao phải lấy cột B mà không lấy ở cột C và D?

 

[ghzktc]

à, đúng ý mình rồi, tham số tương ứng lấy ở cột B là vì mình đang ví dụ về tra giá trị A của 5,5
bạn có thể giải thích cho mình xíu về cú pháp hàm bạn dùng không
=IF(C3<=0,0,INDEX(Sheet1!A:A,MATCH(C3,Sheet1!A:A,1)))
=IF(C3>=45,A3,IF(AND(C3>=44,C3<45),A3+1,A3+2))
Sao hàm dùng cho giá trị đầu lại khác mà giá trị cuối lại khác vậy bạn
Vậy nếu mình muốn đổi qua tra ở bảng M1, M2, M3 thì dùng hàm này có được không

 

[SA_DQ]

Xài thử hàm tự tạo này nha

Mình ví dụ tra A của ϕtc = 5,5, vì trong bảng dữ liệu không có giá trị 5,5 nên phải tiến hành nội suy từ giá trị trước và sau của 5,5 có trong bảng tính là 4 và 6.

Option Explicit
Function CucTri(Rng As Range, Value_ As Double, Optional Min_ As Boolean = True)
 Dim WF As Object, GiaTri As Double, Cls As Range
 
 Set WF = Application.WorksheetFunction
 If Not Min_ Then GiaTri = WF.Min(Rng) - 1
 For Each Cls In Rng
   If Min_ Then
      If Cls.Value > Value_ Then
         CucTri = Cls.Value:     Exit Function
      End If
   Else
      If Cls.Value >= Value_ Then
         CucTri = GiaTri:     Exit Function
      Else
         GiaTri = Cls.Value
      End If
   End If
 Next Cls
End Function

 

[ghzktc]

cái của MinhCong đúng ý mình hơn vì mình muốn dùng hàm trong trường hợp này, nhưng cái của SA_DQ cũng rất hay, cảm ơn 2 bạn nhiều nhé

 

[MinhCong]

à, đúng ý mình rồi, tham số tương ứng lấy ở cột B là vì mình đang ví dụ về tra giá trị A của 5,5
bạn có thể giải thích cho mình xíu về cú pháp hàm bạn dùng không
=IF(C3<=0,0,INDEX(Sheet1!A:A,MATCH(C3,Sheet1!A:A,1)))
=IF(C3>=45,A3,IF(AND(C3>=44,C3<45),A3+1,A3+2))
Sao hàm dùng cho giá trị đầu lại khác mà giá trị cuối lại khác vậy bạn
Vậy nếu mình muốn đổi qua tra ở bảng M1, M2, M3 thì dùng hàm này có được không

1/Giá trị đầu:

=IF(C3<=0,0,INDEX(Sheet1!A:A,MATCH(C3,Sheet1!A:A,1)))

Trong bảng tra của Bạn thì giá trị của cột A nhỏ nhất là 0 và lớn nhất là 45. => Giá trị đầu sẽ có kết quả là từ 0÷45
Hàm ở trên => Nếu C3<=0 thì => giá trị đầu sẽ =0. Ngược lại C3>0 thì nó sẽ lấy giá trị của hàm INDEX.
Hàm INDEX thì khỏi phải giải thích?
Hàm MATCH(C3,Sheet1!A:A,1). Để ý số 1 nhé. Nghiên cứu thêm cấu trúc hàm MATCH.

Hàm MATCH

Chức năng: Hàm Match là hàm dò tìm, sẽ trả về vị trí của giá trị dò tìm trong vùng dò tìm (một dòng hoặc một cột).

Cú pháp: MATCH(lookup_value,lookup_array,match_type)

- Lookup_value: là giá trị dùng để dò tìm, lookup_value có thể là một số, một chuỗi, một giá trị logic hay một tham chiếu.

- Lookup_array: là vùng dò tìm, có thể là một cột hoặc một dòng, hoặc một mảng các giá trị.

- Match_type: là tùy chọn để xác định kiểu dò tìm. Có 3 tùy chọn là -1,0,1:

• match_type = 0: Hàm sẽ dò tìm chính xác giá trị lookup_value trong lookup_array, nếu không tìm thấy hàm sẽ báo lỗi #N/A.
• match_type = 1(hoặc để trống): Hàm sẽ dò tìm giá trị lớn nhất trong lookup_array mà có giá trị <= giá trị của lookup_value. Trường hợp này, các phấn tử trong lookup_array phải được sắp xếp từ nhỏ đến lớn (..-2,-1,0,1,2…A,B,C..Z…False, True).
• match_type = -1: Hàm sẽ dò tìm giá trị nhỏ nhất trong lookup_array mà có giá trị >= giá trị của lookup_value. Trường hợp này, các phấn tử trong lookup_array phải được sắp xếp từ lớn đến nhỏ ( True, False, Z…A,..2,1,0,-1,-2..).

2/Giá trị cuối:

=IF(C3>=45,A3,IF(AND(C3>=44,C3<45),A3+1,A3+2))

=> Hàm này nên sửa lại như sau:

=IF(C3>=45,A3,IF(C3>=44,A3+1,A3+2))

Bảo điều kiện AND(....C3<45) đi => Bị thừa.
Tương tự giá trị đầu. Giá trị cuối nhận giá trị tối đa là 45.
- Nếu C3>=45 => Giá trị đầu luôn luôn A3=45. Và giá trị cuối =A3=45.
- Nếu 44<=C3<45 thì Giá trị đầu A3=44 => Giá trị cuối =A3+1=44+1=45 (Cộng thêm 1 vì giữa giá trị nội suy từ 44 đến 45 chỉ cách nhau 1 đơn vị).
- Còn lại: Nếu C3<44 (tức ngoài 2 trường hợp trên) thì Giá trị cuối sẽ =Giá trị đầu +2 (Vì các giá trị nội suy giữa các số từ 0÷44 cách nhau 2 đơn vị).

3/Tra giá trị đối với M
a/Giá trị đầu:

=IF(C3<=1,1,INDEX(Sheet1!G:G,MATCH(C3,Sheet1!G:G,1)))

b/Giá trị cuối:

=IF(C3>=45,A3,A3+1)

c/Giá trị nôi suy sửa lại các cột dò tìm hàm INDEX và MATCH

 

[ghzktc]

2/giá trị cuối:

=if(c3>=45,a3,if(and(c3>=44,c3<45),a3+1,a3+2))

=> hàm này nên sửa lại như sau:

=if(c3>=45,a3,if(c3>=44,a3+1,a3+2))

bảo điều kiện and(....c3<45) đi => bị thừa.
Tương tự giá trị đầu. Giá trị cuối nhận giá trị tối đa là 45.
- nếu c3>=45 => giá trị đầu luôn luôn a3=45. Và giá trị cuối =a3=45.
- nếu 44<=c3<45 thì giá trị đầu a3=44 => giá trị cuối =a3+1=44+1=45 (cộng thêm 1 vì giữa giá trị nội suy từ 44 đến 45 chỉ cách nhau 1 đơn vị).
- còn lại: Nếu c3<44 (tức ngoài 2 trường hợp trên) thì giá trị cuối sẽ =giá trị đầu +2 (vì các giá trị nội suy giữa các số từ 0÷44 cách nhau 2 đơn vị).

sửa hàm lại không được, báo sai ở số 44 là sao bạn ?

 

[changtraicaoca4989]

fw hay thật =)).fw hay thật =)).fw hay thật =)).fw hay thật =)).