Giúp đỡ tìm số liệu của 2 bảng

trong090892 · 28/3/20

Nhờ anh chị giúp dùm em file excel đính kèm em có tô màu kết quả e mong muốn.
Em có bảng 1 từ cột A-C, bảng 2 từ cột E-G. từ bảng 2 em có sẵn dữ liệu cột mã 2 và số lượng, cần tìm cột mã 1 (tìm dữ liệu trên bảng 1) với điều kiện số lượng lớn hơn or bằng.
Cám ơn anh chị nhiều nhiều.

up up mùa vi rut

dazkangel · 31/3/20

Thử:
=INDEX(A$2:A$12,MATCH(1,INDEX((B$2:B$12=E2)*(C$2:C$12>=F2),),0))

leonguyenz · 31/3/20

dazkangel đã viết:
Thử:
=INDEX(A$2:A$12,MATCH(1,INDEX((B$2:B$12=E2)*(C$2:C$12>=F2),),0))

Điều kiện số lượng lớn hơn hoặc bằng, chắc phải dùng AGGREGATE rồi

HieuCD · 31/3/20

dazkangel đã viết:
Thử:
=INDEX(A$2:A$12,MATCH(1,INDEX((B$2:B$12=E2)*(C$2:C$12>=F2),),0))

Thử: C2 =5600

leonguyenz · 31/3/20

Tạm thời dùng công thức loằng ngoằng này trước, nhờ bác nào rút gọn giúp,

Mã:

=INDEX(A$2:A$12,MATCH(1,INDEX((B$2:B$12=E2)*(C$2:C$12>=F2)*($C$2:$C$12=AGGREGATE(15,6,1/($B$2:$B$12=E2)/($C$2:$C$12>=F2)*$C$2:$C$12,1)),0),0))

HieuCD · 31/3/20

leonguyenz đã viết:
Tạm thời dùng công thức loằng ngoằng này trước, nhờ bác nào rút gọn giúp,

Mã:

=INDEX(A$2:A$12,MATCH(1,INDEX((B$2:B$12=E2)*(C$2:C$12>=F2)*($C$2:$C$12=AGGREGATE(15,6,1/($B$2:$B$12=E2)/($C$2:$C$12>=F2)*$C$2:$C$12,1)),0),0))

Thử dữ liệu mới

leonguyenz · 31/3/20

HieuCD đã viết:
Thử dữ liệu mới

Dữ liệu bài #1 em thấy 2 "Mã 1" có số lượng giống nhau, chủ thớt lấy giá trị đầu, nên dùng MATCH, lúc đầu em dùng LOOKUP, thì lấy được kết quả sau cùng.

dazkangel · 31/3/20

leonguyenz đã viết:
Tạm thời dùng công thức loằng ngoằng này trước, nhờ bác nào rút gọn giúp,

Mã:

=INDEX(A$2:A$12,MATCH(1,INDEX((B$2:B$12=E2)*(C$2:C$12>=F2)*($C$2:$C$12=AGGREGATE(15,6,1/($B$2:$B$12=E2)/($C$2:$C$12>=F2)*$C$2:$C$12,1)),0),0))

Có ngắn hơn tí đây, lâu rồi không có bài nào động não nữa anh à :v
=INDEX(A$2:A$12,RIGHT(AGGREGATE(15,6,C$2:C$12*1000/(B$2:B$12=E2)/(C$2:C$12>=F2)+ROW($A$2:$A$12)-ROW($A$1),1),3))

HieuCD · 31/3/20

leonguyenz đã viết:
Dữ liệu bài #1 em thấy 2 "Mã 1" có số lượng giống nhau, chủ thớt lấy giá trị đầu, nên dùng MATCH, lúc đầu em dùng LOOKUP, thì lấy được kết quả sau cùng.
View attachment 234419

2 bảng sort theo số lượng từ thấp đến cao, dùng công thức mới khả thi, nếu không phải dùng VBA

trong090892 · 6/4/20

Thanks a #HieuCD, a #dazkangel, a #leonguyenz,

E có áp dụng công thức thì nếu giá trị gần vs giá trị yêu cầu thì ra đúng, Em có chạy trong file đính kèm. E lấy dữ liệu nếu nó >= thì lấy giá trị đầu tiên (J0005,SL: 4000 ->95681260) nếu (J0005, SL: 10 or 1... đều lấy kết quả đủ điều kiện -> 95681260), nếu (J0005, SL: 5500, lớn hơn giá trị đầu và thứ 2,.... ->85898330, dữ liệu e cần là sl nếu thoả dk >= thì lấy dòng gần nhất từ trên xuống.

HieuCD · 6/4/20

trong090892 đã viết:
Cảm ơn a #HieuCD, a #dazkangel, a #leonguyenz,

E có áp dụng công thức thì nếu giá trị gần vs giá trị yêu cầu thì ra đúng, Em có chạy trong file đính kèm. E lấy dữ liệu nếu nó >= thì lấy giá trị đầu tiên (J0005,SL: 4000 ->95681260) nếu (J0005, SL: 10 or 1... đều lấy kết quả đủ điều kiện -> 95681260), nếu (J0005, SL: 5500, lớn hơn giá trị đầu và thứ 2,.... ->85898330, dữ liệu e cần là sl nếu thoả dk >= thì lấy dòng gần nhất từ trên xuống.

Nếu dữ liệu J5 như trong file kết quả thế nào

trong090892 · 6/4/20

HieuCD đã viết:
Nếu dữ liệu J5 như trong file kết quả thế nào
View attachment 234863

Hi anh, J0005, SL: 1000 -> 95681260
J0005, SL: 6500 -> 95681260

Phuocam · 6/4/20

trong090892 đã viết:
Hi anh, J0005, SL: 1000 -> 95681260
J0005, SL: 6500 -> 95681260

Bạn đã thử công thức trong bài số #2 chưa?

HieuCD · 6/4/20

trong090892 đã viết:
Hi anh, J0005, SL: 1000 -> 95681260
J0005, SL: 6500 -> 95681260

View attachment 234871

Bài nầy dùng công thức Excel rất và rất phức tạp, phải dùng hàm tự tạo

Mã:

Function FiFo_OutOne(ByVal sRng As Range, ByVal ResRng As Range)
  Dim sArr(), Ma$, SL#, MaRes$, tmp
  Dim sR&, sR2&, i&,  r&

  sArr = sRng.Value2
  sR = UBound(sArr)
  sR2 = ResRng.Rows.Count
  MaRes = ResRng(sR2, 1).Value2
  For r = 1 To sR2
    Ma = ResRng(r, 1).Value2
    If Ma = MaRes Then
      SL = ResRng(r, 2).Value2
      For i = 1 To sR
        If sArr(i, 2) = Ma Then
          If sArr(i, 3) >= SL Then
            sArr(i, 3) = sArr(i, 3) - SL
            If r = sR2 Then tmp = sArr(i, 1)
            Exit For
          End If
        End If
      Next i
    End If
  Next r
  If tmp = Empty Then FiFo_OutOne = "" Else FiFo_OutOne = tmp
End Function

G2 =FiFo_outOne($A$2:$C$12,$E$2:F2)
Copy xuống
Nếu dữ liệu "Đẹp" kết quả ngon lành, còn "Xấu quá" có thể Function bỏ chạy

Hau151978 · 6/4/20

Góp vui công thức, nếu bạn sử dụng excel 365 thì có thể dùng công thức sau tại G2

Mã:

=IFERROR(INDEX(FILTER($A$2:$A$12,($B$2:$B$12=E2)*($C$2:$C$12>=F2)),1),"")

HieuCD · 7/4/20

Hau151978 đã viết:
Góp vui công thức, nếu bạn sử dụng excel 365 thì có thể dùng công thức sau tại G2

Mã:

=IFERROR(INDEX(FILTER($A$2:$A$12,($B$2:$B$12=E2)*($C$2:$C$12>=F2)),1),"")

Xuất kho theo phương pháp FIFO, hàng tồn kho đã xuất những lần trước phải trừ số lượng tồn để tính xuất các lần sau, bài toán khó hơn bình thường là mỗi yêu cầu chỉ được xuất 1 lần, dùng công thức không khả thi, viết code giải quyết tất cả khả năng có thể xảy ra cũng không đơn giản

trong090892 · 7/4/20

Hiện tại e sử dụng công thức này (thoả được điều kiện lấy số >= ra kết quả như mong đợi)

dazkangel đã viết:
[

dazkangel đã viết:

Thử:
=INDEX(A$2:A$12,MATCH(1,INDEX((B$2:B$12=E2)*(C$2:C$12>=F2),),0))

Nhấp chuột vào đây để mở rộng...

/QUOTE]

Bài đã được tự động gộp: 7/4/20

HieuCD đã viết:
Xuất kho theo phương pháp FIFO, hàng tồn kho đã xuất những lần trước phải trừ số lượng tồn để tính xuất các lần sau, bài toán khó hơn bình thường là mỗi yêu cầu chỉ được xuất 1 lần, dùng công thức không khả thi, viết code giải quyết tất cả khả năng có thể xảy ra cũng không đơn giản

Điều kiện làm FIFO thì bắt buộc phải chạy VBA hả anh?

HieuCD · 7/4/20

trong090892 đã viết:
Hiện tại e sử dụng công thức này (thoả được điều kiện lấy số >= ra kết quả như mong đợi)

Bài đã được tự động gộp: 7/4/20

Điều kiện làm FIFO thì bắt buộc phải chạy VBA hả anh?

Mình nghỉ bài nầy không dùng công thức excel được, Function tự tạo của mình có một số tình huống không ra được kết quả, phải dùng Sub xét hết các khả năng mới tính được kết quả

excel_lv1.5 · 7/4/20

HieuCD đã viết:
Bài nầy dùng công thức Excel rất và rất phức tạp, phải dùng hàm tự tạo

Mã:

Function FiFo_OutOne(ByVal sRng As Range, ByVal ResRng As Range) Dim sArr(), Ma$, SL#, MaRes$, tmp Dim sR&, sR2&, i&, r& sArr = sRng.Value2 sR = UBound(sArr) sR2 = ResRng.Rows.Count MaRes = ResRng(sR2, 1).Value2 For r = 1 To sR2 Ma = ResRng(r, 1).Value2 If Ma = MaRes Then SL = ResRng(r, 2).Value2 For i = 1 To sR If sArr(i, 2) = Ma Then If sArr(i, 3) >= SL Then sArr(i, 3) = sArr(i, 3) - SL If r = sR2 Then tmp = sArr(i, 1) Exit For End If End If Next i End If Next r If tmp = Empty Then FiFo_OutOne = "" Else FiFo_OutOne = tmp End Function

G2 =FiFo_outOne($A$2:$C$12,$E$2:F2)
Copy xuống
Nếu dữ liệu "Đẹp" kết quả ngon lành, còn "Xấu quá" có thể Function bỏ chạy

Công thức có lẽ vẫn làm được, nếu chỉ đưa đơn hàng cuối, mượn file của bạn

Mã:

=INDEX($A$2:$A$12,MATCH(SUMIF($E$1:E2,E2,$F$1:F2),SUMIF(OFFSET($B$1,,,ROW($1:$12),),E2,OFFSET($C$1,,,ROW($1:$12),))+0.1))

Ctrl+Shift+Enter

HieuCD · 7/4/20

excel_lv1.5 đã viết:
Công thức có lẽ vẫn làm được, nếu chỉ đưa đơn hàng cuối, mượn file của bạn

Mã:

=INDEX($A$2:$A$12,MATCH(SUMIF($E$1:E2,E2,$F$1:F2),SUMIF(OFFSET($B$1,,,ROW($1:$12),),E2,OFFSET($C$1,,,ROW($1:$12),))+0.1))

Ctrl+Shift+Enter

Dữ liệu nhập theo thời gian, liên tục có đơn hàng cuối mới
Ví dụ trong file, Function của mình và công thức của bạn cùng chịu thua

Giúp đỡ tìm số liệu của 2 bảng

Thành viên mới

File đính kèm

<New Horizons>

Thành viên gạo cội

Chuyên gia GPE

Thành viên gạo cội

Chuyên gia GPE

File đính kèm

Thành viên gạo cội

<New Horizons>

Chuyên gia GPE

Thành viên mới

File đính kèm

Chuyên gia GPE

File đính kèm

Thành viên mới

Thành viên mới

Chuyên gia GPE

File đính kèm

Thành viên tích cực

Chuyên gia GPE

Thành viên mới

Chuyên gia GPE

Thành viên tích cực

Chuyên gia GPE

File đính kèm

Facebook

Group