Công thức trả về ngày gần nhất thỏa mãn nhiều điều kiện (1 người xem)

[nguyennhungptit]

Em chào mọi người
Em muốn tìm công thức để có thể tự trả về giá trị ngày gần nhất với ngày hiện tại thỏa mãn nhiều điều kiện
Em có bảng số liệu như file đính kèm:
Ở cột stock, e muốn trả về giá trị ở cột "Còn lại" của ngày gần nhất thỏa mãn điều kiện cùng tên nhân viên và cùng nhóm.
Ví dụ: ô D8 trả về giá trị ở ô G4 (thỏa mãn điều kiện cùng tên nhân viên, cùng nhóm và là ngày gần nhất). Tương tự như vậy ô D9 cần trả về giá trị ô G3, ô D10 cần trả về giá trị ô G5
Em cảm ơn mọi người

 

[thaytu]

Cho hỏi có trường hợp 1 nhân viên thuộc vào 2 nhóm không hay là 1 nhóm duy nhất?

 

[maytinhvp01]

Em chào mọi người
Em muốn tìm công thức để có thể tự trả về giá trị ngày gần nhất với ngày hiện tại thỏa mãn nhiều điều kiện
Em có bảng số liệu như file đính kèm:
Ở cột stock, e muốn trả về giá trị ở cột "Còn lại" của ngày gần nhất thỏa mãn điều kiện cùng tên nhân viên và cùng nhóm.
Ví dụ: ô D8 trả về giá trị ở ô G4 (thỏa mãn điều kiện cùng tên nhân viên, cùng nhóm và là ngày gần nhất). Tương tự như vậy ô D9 cần trả về giá trị ô G3, ô D10 cần trả về giá trị ô G5
Em cảm ơn mọi người

Bạn xài thử VBA thử đi!

 

[nguyennhungptit]

Cho hỏi có trường hợp 1 nhân viên thuộc vào 2 nhóm không hay là 1 nhóm duy nhất?

Có ạ. Một nhân viên có thể thuộc vào 2 nhóm.
Em muốn ở côt "Stock" sẽ trả về giá trị ở cột "Còn lại" đúng với nhân viên và nhóm tương ứng ở ngày gần nhất với ngày hiện tại đó

 

[nguyennhungptit]

Bạn xài thử VBA thử đi!

Vì e mới học excel nên VBA e chưa biết dùng ạ
Em mới tập làm quen với các hàm.

 

[ndu96081631]

Vì e mới học excel nên VBA e chưa biết dùng ạ
Em mới tập làm quen với các hàm.

Thử công thức này cho cell D8 xem:

=LOOKUP(2,1/($B$2:$B7=$B8)/($C$2:$C7=$C8),$G$2:$G7)

Kéo fill xuống

 

[leonguyenz]

Vì e mới học excel nên VBA e chưa biết dùng ạ
Em mới tập làm quen với các hàm.

Công thức tại D2:

=IFERROR(LOOKUP(2,1/(($B$1:B1=B2)*($C$1:C1=C2)),$G$1:G1),0)

(file tải lên không được, chắc đường mạng có vấn đề)

 

[nguyennhungptit]

Thử công thức này cho cell D8 xem:

=LOOKUP(2,1/($B$2:$B7=$B8)/($C$2:$C7=$C8),$G$2:$G7)

Kéo fill xuống

dạ, đã được rồi anh ạ
em cảm ơn anh

 

[nguyennhungptit]

Công thức tại D2:

=IFERROR(LOOKUP(2,1/(($B$1:B1=B2)*($C$1:C1=C2)),$G$1:G1),0)

(file tải lên không được, chắc đường mạng có vấn đề)

Em cảm ơn leonguyenz
công thức của leonguyenz và anh ndu96081631 đều ra cùng kết quả ạ
Em cảm ơn mọi người

 

[maytinhvp01]

Thử công thức này cho cell D8 xem:

=LOOKUP(2,1/($B$2:$B7=$B8)/($C$2:$C7=$C8),$G$2:$G7)

Kéo fill xuống

Anh ơi bài này tối qua em đã viết code chạy theo thủ tục " code hơi lộn xộn"
Hôm nay em viết bằng Function anh kiểm tra hộ em có được không a?
Hàm tên la: = stock(chọn vùng "tên nhân viên", chọn vùng "tên nhóm", chọn vùng "còn lại")
em vừa viết xong nên chưa kịp thử chạy bằng hàm của anh! tối nay em thử hàm của anh để học tập.

 

[ndu96081631]

Anh ơi bài này tối qua em đã viết code chạy theo thủ tục " code hơi lộn xộn"
Hôm nay em viết bằng Function anh kiểm tra hộ em có được không a?
Hàm tên la: = stock(chọn vùng "tên nhân viên", chọn vùng "tên nhóm", chọn vùng "còn lại")
em vừa viết xong nên chưa kịp thử chạy bằng hàm của anh! tối nay em thử hàm của anh để học tập.

Hàm LOOKUP của bác Bill rất đa dạng, tôi nghĩ chúng ta khó có thể nào viết được 1 hàm có khả năng tương tự thế
Tuy nhiên nếu có gắng viết thì tôi sẽ viết khác bạn:
1> Hàm hổ trợ

Function ArrayTo1DArray(ByVal SourceArray)
  Dim aTmp, Item, arr()
  Dim n As Long
  aTmp = SourceArray
  If Not IsArray(aTmp) Then aTmp = Array(aTmp)
  For Each Item In aTmp
    n = n + 1
    ReDim Preserve arr(1 To n)
    arr(n) = Item
  Next
  ArrayTo1DArray = arr
End Function

2> Hàm chính:

Function LookUpArray(ByVal CriteriaArray, ByVal ReturnArray, ByVal ReturnType As Boolean)
  'ReturnType = FALSE ---> Lay ket qua dau tien thoa d/k
  'ReturnType = TRUE ---> Lay ket qua cuoi cùng thoa d/k
  Dim aCrit, aRet, tmp, bChk As Boolean
  Dim i As Long
  On Error Resume Next
  LookUpArray = vbNullString
  aCrit = ArrayTo1DArray(CriteriaArray)
  aRet = ArrayTo1DArray(ReturnArray)
  For i = LBound(aCrit) To UBound(aCrit)
    bChk = CBool(aCrit(i))
    If bChk Then
      tmp = aRet(i)
      If ReturnType = False Then Exit For
    End If
  Next
  If TypeName(tmp) <> "Error" Then LookUpArray = tmp
End Function

3> Áp dụng:
Trên bảng tính, tạo cell D3 ta gõ công thức:

=LookUpArray([COLOR=#ff0000]($B$2:$B2=B3)*($C$2:$C2=C3)[/COLOR],$G$2:$G2,TRUE)

Bấm Ctrl + Shift + Enter để kết thúc
Lưu ý: Biến CriteriaArray là viết chung chung, không chỉ rõ điều kiện tìm là gì cả mà cho người dùng tự tùy biến ---> Vì thực chất ta làm gì biết trước người dùng sẽ muốn điều kiện tìm là gì đâu mà viết cho cụ thể. Như công thức ghi ở trên thì CriteriaArray chính là chổ màu đỏ

 

[maytinhvp01]

Hàm LOOKUP của bác Bill rất đa dạng, tôi nghĩ chúng ta khó có thể nào viết được 1 hàm có khả năng tương tự thế
Tuy nhiên nếu có gắng viết thì tôi sẽ viết khác bạn:
1> Hàm hổ trợ

Function ArrayTo1DArray(ByVal [COLOR=#ff0000]SourceArray[/COLOR])
  Dim aTmp, Item, arr()
  Dim n As Long
  aTmp = SourceArray
  If Not IsArray(aTmp) Then aTmp = Array(aTmp)
  For Each Item In aTmp
    n = n + 1
    ReDim Preserve arr(1 To n)
    arr(n) = Item
  Next
  ArrayTo1DArray = arr
End Function

Anh ơi cho em hỏi lá biến "SourceArray" lúc khai báo không khống chế nó là kiểu dữ liệu nào "mặc định là variants , vậy nó có thể nhận kiểu dữ liệu chuỗi "string" không ạ?. Nếu có thể nhận thì biến đưa vào "SourceArray" là chuỗi thì lúc tạo mảng aTmp " aTmp = Array(aTmp) thì em chưa hiểu anh giải thích cho em với ạ?

 

[ndu96081631]

1> Hàm hổ trợ

Function ArrayTo1DArray(ByVal [COLOR=#ff0000]SourceArray[/COLOR])
  Dim aTmp, Item, arr()
  Dim n As Long
  aTmp = SourceArray
  If Not IsArray(aTmp) Then aTmp = Array(aTmp)
  For Each Item In aTmp
    n = n + 1
    ReDim Preserve arr(1 To n)
    arr(n) = Item
  Next
  ArrayTo1DArray = arr
End Function

Anh ơi cho em hỏi lá biến "SourceArray" lúc khai báo không khống chế nó là kiểu dữ liệu nào "mặc định là variants , vậy nó có thể nhận kiểu dữ liệu chuỗi "string" không ạ?. Nếu có thể nhận thì biến đưa vào "SourceArray" là chuỗi thì lúc tạo mảng aTmp " aTmp = Array(aTmp) thì em chưa hiểu anh giải thích cho em với ạ?

Đúng là thế! Tôi dự trù SourceArray là bất cứ kiểu dữ liệu gì mà:
- Có thể là Range do bạn quét chọn
- Có thể là mảng từ 1 biểu thức nào đó trả về (như biểu thức ($B$2:$B2=B3)*($C$2:$C2=C3) cũng là 1 mảng)
Trường hợp SourceArray là dạng String hay gì gì đó (đại khái là 1 phần tử đơn, không phải mảng) thì nó cũng sẽ được đưa vào mảng nhở dòng code
If Not IsArray(aTmp) Then aTmp = Array(aTmp)
Tức không phải Array thì ta cho nó vào 1 Array (mảng 1 phần tử)
Tóm lại: Bạn đoán... đúng cả rồi
Ẹc... Ẹc...

 

[maytinhvp01]

Đúng là thế! Tôi dự trù SourceArray là bất cứ kiểu dữ liệu gì mà:
- Có thể là Range do bạn quét chọn
- Có thể là mảng từ 1 biểu thức nào đó trả về (như biểu thức ($B$2:$B2=B3)*($C$2:$C2=C3) cũng là 1 mảng)
Trường hợp SourceArray là dạng String hay gì gì đó (đại khái là 1 phần tử đơn, không phải mảng) thì nó cũng sẽ được đưa vào mảng nhở dòng code
If Not IsArray(aTmp) Then aTmp = Array(aTmp)
Tức không phải Array thì ta cho nó vào 1 Array (mảng 1 phần tử)
Tóm lại: Bạn đoán... đúng cả rồi
Ẹc... Ẹc...

Sub test()
Dim str As String
Dim atpm
str = "hello"
atpm = str
If Not IsArray(atpm) Then atpm = Array(atpm)
MsgBox atpm(0)
End Sub

Vâng ạ. Giờ em đã hiểu đúng là nó trả về mảng một phần tử và giá trị của phần phần tử mảng duy nhất đó chính là biến đưa vào.
Em buồn ngủ rùi chưa sem tiếp được đoạn code nữa của anh.
Thank anh nhé." Qua bài này em biết thêm được hàm kiểm tra có phải là mảng không isarray.."

 

[nguyennhungptit]

Thử công thức này cho cell D8 xem:

=LOOKUP(2,1/($B$2:$B7=$B8)/($C$2:$C7=$C8),$G$2:$G7)

Kéo fill xuống

Em chào anh
Hôm qua e đã làm được bảng tính theo ý mình khi áp dụng công thức của anh
Nhưng thực sự e chỉ biết áp dụng, hôm nay e có tìm các tài liệu để hiêu công thức mà không tìm được ạ
Anh có thể giải thích giúp e về công thức được không ạ
Theo e biết cú pháp hàm lookup là =LOOKUP(lookup_value,lookup_vector,result_vector) hoặc =LOOKUP(lookup_value,array)
Em không hiểu là trong công thức của anh tại sao dung lookup_value là 1, và lookup_vector dạng là 1/....
em tìm các tài liệu e có và có tìm trên mạng mà không tìm ra được
Có vào mục hàm tìm kiếm của giaiphapexcel nhưng chỉ giải thích về công thức (hoặc do em không biết cách tìm)
Mong anh giải đáp giúp e với ạ
Em cảm ơn anh

 

[ndu96081631]

Em chào anh
Hôm qua e đã làm được bảng tính theo ý mình khi áp dụng công thức của anh
Nhưng thực sự e chỉ biết áp dụng, hôm nay e có tìm các tài liệu để hiêu công thức mà không tìm được ạ
Anh có thể giải thích giúp e về công thức được không ạ
Theo e biết cú pháp hàm lookup là =LOOKUP(lookup_value,lookup_vector,result_vector) hoặc =LOOKUP(lookup_value,array)
Em không hiểu là trong công thức của anh tại sao dung lookup_value là 1, và lookup_vector dạng là 1/....
em tìm các tài liệu e có và có tìm trên mạng mà không tìm ra được
Có vào mục hàm tìm kiếm của giaiphapexcel nhưng chỉ giải thích về công thức (hoặc do em không biết cách tìm)
Mong anh giải đáp giúp e với ạ
Em cảm ơn anh

Giải thích hoài cái vụ này rồi
Bạn xem tạm ở đây nhé:
http://www.giaiphapexcel.com/forum/...ìm-kiếm-giá-trị-với-2-ô-điều-kiện-Lookupvalue

 

[lavie1998]

Thử công thức này cho cell D8 xem:

=LOOKUP(2,1/($B$2:$B7=$B8)/($C$2:$C7=$C8),$G$2:$G7)

Kéo fill xuống

Em chào anh
Theo công thức của anh ở trên, kết quả sẽ cho ngày GẦN NHẤT thỏa mãn điều kiện
Cho em hỏi nếu em muốn tìm ngày SỚM NHẤT thỏa mãn điều kiện thì công thức như thế nào ạ
Em cảm ơn anh

 

[leonguyenz]

Em chào anh
Theo công thức của anh ở trên, kết quả sẽ cho ngày GẦN NHẤT thỏa mãn điều kiện
Cho em hỏi nếu em muốn tìm ngày SỚM NHẤT thỏa mãn điều kiện thì công thức như thế nào ạ
Em cảm ơn anh

Công thức tìm ngày sớm nhất thỏa mãn điều kiện:

D8 =INDEX($A$2:$A$7,MATCH(1,INDEX(($B$2:$B$7=B8)*($C$2:$C$7=C8),0),0))

Nếu tìm còn lại thì thế $G$2:$G$7 cho $A$2:$A$7.

 

[shuyinol]

Mình gà nên không hiểu công thức này lắm, mong các bạn giúp giải thích. Theo như yêu cầu thì ngày tháng cũng là một điều kiện mà công thức ở đây lại không xét thành ra khi một ai đó có thay đổi ngày tháng đi nữa thì công thức cũng không cập nhật được cho đúng với điều kiện ngày gần nhất.
Mình cũng có làm thử một bản để mọi người góp ý.

 

[lavie1998]

Công thức tìm ngày sớm nhất thỏa mãn điều kiện:

D8 =INDEX($A$2:$A$7,MATCH(1,INDEX(($B$2:$B$7=B8)*($C$2:$C$7=C8),0),0))

Nếu tìm còn lại thì thế $G$2:$G$7 cho $A$2:$A$7.

Em cám ơn anh đã giúp đỡ ạ!