Giúp lập công thức 2 dòng cùng điều kiện tính toán giống nhau

Liên hệ QC

chungk54neu

Thành viên mới
Tham gia
3/6/19
Bài viết
45
Được thích
7
File của em có máy 1 chạy các sp1, sp2. sp3, sp4, sp5, khi chạy hết số lượng sp1 sẽ nhảy sang sp2 với tổng thời gian chạy máy 1 ngày của máy 1 là 23h. Trường hợp chung nhau sp2 và sp3 thì thời gian tính cho 2 sp này bằng 23h trừ đi thời gian chạy sp1. Tuy nhiên công thức của em đang tính thời gian chạy sp3 bằng 23h- time chạy sp1-time chạy sp2.
Tính đúng phải là time chạy sp2= 23h-time chạy sp1, Time chạy sp3 = 23h-time chạy sp1 (bỏ qua time chạy sp2 vì sp2 và sp3 chung nhau). anh/chị có phương án giải quyết trường hợp này k giúp em với.
 

File đính kèm

  • Ví dụ 1.xlsx
    38 KB · Đọc: 20
File của em có máy 1 chạy các sp1, sp2. sp3, sp4, sp5, khi chạy hết số lượng sp1 sẽ nhảy sang sp2 với tổng thời gian chạy máy 1 ngày của máy 1 là 23h. Trường hợp chung nhau sp2 và sp3 thì thời gian tính cho 2 sp này bằng 23h trừ đi thời gian chạy sp1. Tuy nhiên công thức của em đang tính thời gian chạy sp3 bằng 23h- time chạy sp1-time chạy sp2.
Tính đúng phải là time chạy sp2= 23h-time chạy sp1, Time chạy sp3 = 23h-time chạy sp1 (bỏ qua time chạy sp2 vì sp2 và sp3 chung nhau). anh/chị có phương án giải quyết trường hợp này k giúp em với.
Công thức nhiều điều kiện rất khó đọc
Nhập tay kết quả mong muốn và giải thích yêu cầu tính, gởi lại file
 
Công thức nhiều điều kiện rất khó đọc
Nhập tay kết quả mong muốn và giải thích yêu cầu tính, gởi lại file
Sr anh cách tính hơi dài dòng, em đã viết lại trong file này, anh xem nếu được thì giải quyết bài toán này giúp em, em cảm ơn
 

File đính kèm

  • Ví dụ 1 cách tính.xlsx
    20.5 KB · Đọc: 13
Không biết đúng yêu cầu không, làm đại.
Insert dòng 4 làm dòng trung gian, mục đích là cho công thức dòng đầu tiên đồng nhất với các dòng sau, không phải làm công thức riêng cho nó.
Tại F4=F5 (Machine 1), format màu chữ giống màu nền để che
Nếu lịch bắt đầu từ cột K (ngày 25/6), dùng 1 cột trống trước nó (cột J) để tính toán cộng dồn cho ngày đầu tiên (cột K) mà không phải làm công thức riêng cho nó
Xây dựng công thức cho K5 và copy hết bảng.
Quy trình như sau:
1) Xác định Số giờ đã sử dụng trong ngày cho đến sản phẩm "không chung nhau" phía trên nó:
SUMPRODUCT(($B$4:$B4="")*($F$4:$F4=$F5)*K$4:K4*$E$4:$E4)
2) Số giờ còn lại trong ngày dành cho sản phẩm này:
(K$2*60-SUMPRODUCT(($B$4:$B4="")*($F$4:$F4=$F5)*K$4:K4*$E$4:$E4))
3) Số sản phẩm sản xuất được:
(K$2*60-SUMPRODUCT(($B$4:$B4="")*($F$4:$F4=$F5)*K$4:K4*$E$4:$E4))/$E5
4) So sánh với số sản phẩm đã SX những ngày trước đó, cái nào nhỏ hơn thì lấy:
Công thức cuối cùng:

Mã:
=MIN($C5-SUM($J5:J5),(K$2*60-SUMPRODUCT(($B$4:$B4="")*($F$4:$F4=$F5)*K$4:K4*$E$4:$E4))/$E5)

Vì không biết các ví dụ đã bao quát hết chưa, nên công thức này chỉ đáp ứng theo ví dụ đó.
Còn các trường hợp khác, nếu có, bạn test thử nếu không đúng, đưa file lên lại nhé.
 

File đính kèm

  • Ví dụ 1 cách tính.xlsx
    25.7 KB · Đọc: 14
Không biết đúng yêu cầu không, làm đại.
Insert dòng 4 làm dòng trung gian, mục đích là cho công thức dòng đầu tiên đồng nhất với các dòng sau, không phải làm công thức riêng cho nó.
Tại F4=F5 (Machine 1), format màu chữ giống màu nền để che
Nếu lịch bắt đầu từ cột K (ngày 25/6), dùng 1 cột trống trước nó (cột J) để tính toán cộng dồn cho ngày đầu tiên (cột K) mà không phải làm công thức riêng cho nó
Xây dựng công thức cho K5 và copy hết bảng.
Quy trình như sau:
1) Xác định Số giờ đã sử dụng trong ngày cho đến sản phẩm "không chung nhau" phía trên nó:
SUMPRODUCT(($B$4:$B4="")*($F$4:$F4=$F5)*K$4:K4*$E$4:$E4)
2) Số giờ còn lại trong ngày dành cho sản phẩm này:
(K$2*60-SUMPRODUCT(($B$4:$B4="")*($F$4:$F4=$F5)*K$4:K4*$E$4:$E4))
3) Số sản phẩm sản xuất được:
(K$2*60-SUMPRODUCT(($B$4:$B4="")*($F$4:$F4=$F5)*K$4:K4*$E$4:$E4))/$E5
4) So sánh với số sản phẩm đã SX những ngày trước đó, cái nào nhỏ hơn thì lấy:
Công thức cuối cùng:

Mã:
=MIN($C5-SUM($J5:J5),(K$2*60-SUMPRODUCT(($B$4:$B4="")*($F$4:$F4=$F5)*K$4:K4*$E$4:$E4))/$E5)

Vì không biết các ví dụ đã bao quát hết chưa, nên công thức này chỉ đáp ứng theo ví dụ đó.
Còn các trường hợp khác, nếu có, bạn test thử nếu không đúng, đưa file lên lại nhé.
cảm ơn anh rất nhiều, công thức anh gần đúng rồi, còn 1 trường hợp nhỏ là ở dòng A10 khi SP6 không chung với Sp5 và Sp4 thì ngày sản xuất Sp 6 này là ngày kết thúc của 2 sp chung nhau là Sp5 vs Sp4 (file em đính kèm). anh xem giúp em trường hợp đó với ạ
 

File đính kèm

  • Ví dụ 1 cách tính.xlsx
    25.7 KB · Đọc: 3
Ngày kết thúc của 2 SP 4 và 5 có liên quan gì đâu, nó có thể kết thúc vào n ngày sau đó, phụ thuộc vào SL đặt hàng.
Tuy nhiên, dây chuyền của bạn sản xuất liên tục mà, vào ngày 25 máy 1 đã hết công suất nên nó SX vào ngày 26, sao phải chờ đến ngày 29 (vì SP 5 kết thúc vào 28?)
 
Sr anh cách tính hơi dài dòng, em đã viết lại trong file này, anh xem nếu được thì giải quyết bài toán này giúp em, em cảm ơn
Mã:
I4 =$D$1+(MATCH(0,L4:AB4,0)-1)-1
Copy xuống
Mã:
L4 =MIN($C4-SUM($K4:K4),23*60/$E4)
Copy ngang qua phải
Mã:
L5 =IF(L$3<$H5,"X",MIN($C5-SUM($K5:K5),(23*60-SUMPRODUCT($E$4:$E4*L$4:L4*($B$4:$B4="")*($F$4:$F4=$F5)))/$E5))
Copy cho các ô còn lại
 

File đính kèm

  • Ví dụ 1 cách tính.xlsx
    27.1 KB · Đọc: 8
Ngày kết thúc của 2 SP 4 và 5 có liên quan gì đâu, nó có thể kết thúc vào n ngày sau đó, phụ thuộc vào SL đặt hàng.
Tuy nhiên, dây chuyền của bạn sản xuất liên tục mà, vào ngày 25 máy 1 đã hết công suất nên nó SX vào ngày 26, sao phải chờ đến ngày 29 (vì SP 5 kết thúc vào 28?)
Vì chạy cùng machine 1 và SP4 SP5 chạy cùng nhau nên khi sản xuất hết SP5 ngày 2/7 thì thời gian còn lại của ngày 2/7 mới bắt đầu sản xuất SP6. (em gửi kèm file ạ)z2255861860911_4173661caef3014d4fcb2e1e33ef4979.jpg
Bài đã được tự động gộp:

Mã:
I4 =$D$1+(MATCH(0,L4:AB4,0)-1)-1
Copy xuống
Mã:
L4 =MIN($C4-SUM($K4:K4),23*60/$E4)
Copy ngang qua phải
Mã:
L5 =IF(L$3<$H5,"X",MIN($C5-SUM($K5:K5),(23*60-SUMPRODUCT($E$4:$E4*L$4:L4*($B$4:$B4="")*($F$4:$F4=$F5)))/$E5))
Copy cho các ô còn lại
Em cảm ơn anh nhiều
Bài đã được tự động gộp:

Mã:
I4 =$D$1+(MATCH(0,L4:AB4,0)-1)-1
Copy xuống
Mã:
L4 =MIN($C4-SUM($K4:K4),23*60/$E4)
Copy ngang qua phải
Mã:
L5 =IF(L$3<$H5,"X",MIN($C5-SUM($K5:K5),(23*60-SUMPRODUCT($E$4:$E4*L$4:L4*($B$4:$B4="")*($F$4:$F4=$F5)))/$E5))
Copy cho các ô còn lại
file của anh nếu em xóa "chung nhau 1" ở ô B6 thì nó hiện lỗi ở các dòng phía dưới
 

File đính kèm

  • Ví dụ 1 cách tính.xlsx
    24.9 KB · Đọc: 7
Vì chạy cùng machine 1 và SP4 SP5 chạy cùng nhau nên khi sản xuất hết SP5 ngày 2/7 thì thời gian còn lại của ngày 2/7 mới bắt đầu sản xuất SP6. (em gửi kèm file ạ)View attachment 252230
Bài đã được tự động gộp:


Em cảm ơn anh nhiều
Bài đã được tự động gộp:


file của anh nếu em xóa "chung nhau 1" ở ô B6 thì nó hiện lỗi ở các dòng phía dưới
Giản lượt yêu cầu
Mã:
L5 =MIN($C5-SUM($K5:K5),(23*60-SUMPRODUCT(L$4:L4*$E$4:$E4*($B$4:$B4="")*($F$4:$F4=$F5)))/$E5)
Copy cho các ô còn lại
Rối hơn
Less:
=IF(L$3<$H5,"X",MIN($C5-SUM($K5:K5),(23*60-SUMPRODUCT(IF(L$4:L4="X",0,L$4:L4)*$E$4:$E4*($B$4:$B4="")*($F$4:$F4=$F5)))/$E5))
Nhấn ctrl+shift+enter, Copy cho các ô còn lại
 
c
Giản lượt yêu cầu
Mã:
L5 =MIN($C5-SUM($K5:K5),(23*60-SUMPRODUCT(L$4:L4*$E$4:$E4*($B$4:$B4="")*($F$4:$F4=$F5)))/$E5)
Copy cho các ô còn lại
Rối hơn
Less:
=IF(L$3<$H5,"X",MIN($C5-SUM($K5:K5),(23*60-SUMPRODUCT(IF(L$4:L4="X",0,L$4:L4)*$E$4:$E4*($B$4:$B4="")*($F$4:$F4=$F5)))/$E5))
Nhấn ctrl+shift+enter, Copy cho các ô còn lại
Cảm ơn anh nhé.
 
Giản lượt yêu cầu
Mã:
L5 =MIN($C5-SUM($K5:K5),(23*60-SUMPRODUCT(L$4:L4*$E$4:$E4*($B$4:$B4="")*($F$4:$F4=$F5)))/$E5)
Copy cho các ô còn lại
Rối hơn
Less:
=IF(L$3<$H5,"X",MIN($C5-SUM($K5:K5),(23*60-SUMPRODUCT(IF(L$4:L4="X",0,L$4:L4)*$E$4:$E4*($B$4:$B4="")*($F$4:$F4=$F5)))/$E5))
Nhấn ctrl+shift+enter, Copy cho các ô còn lại
vd3.png
Anh ơi lại xuất hiện trường hợp này đối với công thức của anh, dòng nằm liền kề dưới sản phẩm chung nhau sẽ nhảy bị sai ạ, sản lượng của ô này sẽ tính là 23h (em đã bôi đỏ ô bị sai ạ), em đã note bôi đỏ, mong anh sửa lại công thức giúp em với ạ
 

File đính kèm

  • Ví dụ 1 22.1.2021.xlsx
    27.5 KB · Đọc: 9
View attachment 253395
Anh ơi lại xuất hiện trường hợp này đối với công thức của anh, dòng nằm liền kề dưới sản phẩm chung nhau sẽ nhảy bị sai ạ, sản lượng của ô này sẽ tính là 23h (em đã bôi đỏ ô bị sai ạ), em đã note bôi đỏ, mong anh sửa lại công thức giúp em với ạ
Không tìm được cách dùng công thức Excel. Bạn @excel_lv1.5 , @dazkangel ới ơi! nhín chút thời gian xử đẹp bài nầy nha /-*+/
Dùng Macro dể hơn :)
 
Lần chỉnh sửa cuối:
View attachment 253395
Anh ơi lại xuất hiện trường hợp này đối với công thức của anh, dòng nằm liền kề dưới sản phẩm chung nhau sẽ nhảy bị sai ạ, sản lượng của ô này sẽ tính là 23h (em đã bôi đỏ ô bị sai ạ), em đã note bôi đỏ, mong anh sửa lại công thức giúp em với ạ
Dùng Hàm tự tạo
Mã:
Function PhanBoSoLuong(ByVal maxPhut#, ByVal Ngay, ByVal SoLuong, _
            Chung As Range, DinhMuc As Range, May As Range, Rng As Range) As Variant
  Dim sRow&, sCol&, i&, j&
  Dim tMay$, tDinhMuc#, tSoLuong As Variant
  Dim tmpChung$, tmpPhut#
 
  sRow = Rng.Rows.Count: sCol = Rng.Columns.Count
  tMay = May(sRow, 1).Offset(1).Value
  tDinhMuc = DinhMuc(sRow, 1).Offset(1).Value
  If Rng(1, sCol) < Ngay Then PhanBoSoLuong = "X": Exit Function
  For j = 2 To sCol - 1
     tSoLuong = Rng(sRow, j).Offset(1).Value
    If IsNumeric(tSoLuong) Then SoLuong = SoLuong - tSoLuong
  Next j
  For i = 2 To sRow
    If IsNumeric(Rng(i, sCol)) Then
      If May(i, 1) = tMay Then
        If Chung(i, 1) = Empty Then
          maxPhut = maxPhut - Rng(i, sCol) * DinhMuc(i, 1)
        Else
          If tmpChung <> Chung(i, 1) Then
            tmpChung = Chung(i, 1)
            tmpPhut = 0
          End If
          If tmpPhut < Rng(i, sCol) * DinhMuc(i, 1) Then
            tmpPhut = Rng(i, sscol) * DinhMuc(i, 1)
          End If
          If tmpChung <> Chung(i, 1).Offset(1) Then
            maxPhut = maxPhut - tmpPhut
          End If
        End If
      End If
    End If
  Next i
  tSoLuong = maxPhut / tDinhMuc
  If SoLuong > tSoLuong Then
    PhanBoSoLuong = Round(tSoLuong, 6)
  Else
    PhanBoSoLuong = Round(SoLuong, 6)
  End If
End Function
Công thức ô L4 =PhanBoSoLuong( 23 * 60, $H4,$C4, $B$3:$B3, $E$3:$E3,$F$3:$F3, $K$3:L3)
Copy cho các ô còn lại
 

File đính kèm

  • Ví dụ 1 22.1.2021.xlsb
    28.7 KB · Đọc: 5
Dùng Hàm tự tạo
Mã:
Function PhanBoSoLuong(ByVal maxPhut#, ByVal Ngay, ByVal SoLuong, _
            Chung As Range, DinhMuc As Range, May As Range, Rng As Range) As Variant
  Dim sRow&, sCol&, i&, j&
  Dim tMay$, tDinhMuc#, tSoLuong As Variant
  Dim tmpChung$, tmpPhut#

  sRow = Rng.Rows.Count: sCol = Rng.Columns.Count
  tMay = May(sRow, 1).Offset(1).Value
  tDinhMuc = DinhMuc(sRow, 1).Offset(1).Value
  If Rng(1, sCol) < Ngay Then PhanBoSoLuong = "X": Exit Function
  For j = 2 To sCol - 1
     tSoLuong = Rng(sRow, j).Offset(1).Value
    If IsNumeric(tSoLuong) Then SoLuong = SoLuong - tSoLuong
  Next j
  For i = 2 To sRow
    If IsNumeric(Rng(i, sCol)) Then
      If May(i, 1) = tMay Then
        If Chung(i, 1) = Empty Then
          maxPhut = maxPhut - Rng(i, sCol) * DinhMuc(i, 1)
        Else
          If tmpChung <> Chung(i, 1) Then
            tmpChung = Chung(i, 1)
            tmpPhut = 0
          End If
          If tmpPhut < Rng(i, sCol) * DinhMuc(i, 1) Then
            tmpPhut = Rng(i, sscol) * DinhMuc(i, 1)
          End If
          If tmpChung <> Chung(i, 1).Offset(1) Then
            maxPhut = maxPhut - tmpPhut
          End If
        End If
      End If
    End If
  Next i
  tSoLuong = maxPhut / tDinhMuc
  If SoLuong > tSoLuong Then
    PhanBoSoLuong = Round(tSoLuong, 6)
  Else
    PhanBoSoLuong = Round(SoLuong, 6)
  End If
End Function
Công thức ô L4 =PhanBoSoLuong( 23 * 60, $H4,$C4, $B$3:$B3, $E$3:$E3,$F$3:$F3, $K$3:L3)
Copy cho các ô còn lại
Em cảm ơn anh rất nhiều ạ.
Bài đã được tự động gộp:

Dùng Hàm tự tạo
Mã:
Function PhanBoSoLuong(ByVal maxPhut#, ByVal Ngay, ByVal SoLuong, _
            Chung As Range, DinhMuc As Range, May As Range, Rng As Range) As Variant
  Dim sRow&, sCol&, i&, j&
  Dim tMay$, tDinhMuc#, tSoLuong As Variant
  Dim tmpChung$, tmpPhut#

  sRow = Rng.Rows.Count: sCol = Rng.Columns.Count
  tMay = May(sRow, 1).Offset(1).Value
  tDinhMuc = DinhMuc(sRow, 1).Offset(1).Value
  If Rng(1, sCol) < Ngay Then PhanBoSoLuong = "X": Exit Function
  For j = 2 To sCol - 1
     tSoLuong = Rng(sRow, j).Offset(1).Value
    If IsNumeric(tSoLuong) Then SoLuong = SoLuong - tSoLuong
  Next j
  For i = 2 To sRow
    If IsNumeric(Rng(i, sCol)) Then
      If May(i, 1) = tMay Then
        If Chung(i, 1) = Empty Then
          maxPhut = maxPhut - Rng(i, sCol) * DinhMuc(i, 1)
        Else
          If tmpChung <> Chung(i, 1) Then
            tmpChung = Chung(i, 1)
            tmpPhut = 0
          End If
          If tmpPhut < Rng(i, sCol) * DinhMuc(i, 1) Then
            tmpPhut = Rng(i, sscol) * DinhMuc(i, 1)
          End If
          If tmpChung <> Chung(i, 1).Offset(1) Then
            maxPhut = maxPhut - tmpPhut
          End If
        End If
      End If
    End If
  Next i
  tSoLuong = maxPhut / tDinhMuc
  If SoLuong > tSoLuong Then
    PhanBoSoLuong = Round(tSoLuong, 6)
  Else
    PhanBoSoLuong = Round(SoLuong, 6)
  End If
End Function
Công thức ô L4 =PhanBoSoLuong( 23 * 60, $H4,$C4, $B$3:$B3, $E$3:$E3,$F$3:$F3, $K$3:L3)
Copy cho các ô còn lại
anh ơi ô M6 khi chạy kết quả bằng 776 mới đúng ạ do ô này bị sai nên dẫn đến ô T6 bị sai và ô T7 cũng sai theo ạ, vì SP 1 sản xuất hết 3.67h, SP3 sản xuất hết 10.32h -> do SP1 vs SP3 chung nhau nên mình sẽ lấy thời gian sản xuất lớn nhất trong 2 sp này -> thời gian còn lại sx sản phẩm 2 là 23- 10.32 = 12.68h => Nên cột M6 sẽ bằng 12.68*60/0.98=776. anh giúp em với ạ
 
Lần chỉnh sửa cuối:
Em cảm ơn anh rất nhiều ạ.
Bài đã được tự động gộp:


anh ơi ô M6 khi chạy kết quả bằng 776 mới đúng ạ do ô này bị sai nên dẫn đến ô T6 bị sai và ô T7 cũng sai theo ạ, vì SP 1 sản xuất hết 3.67h, SP3 sản xuất hết 10.32h -> do SP1 vs SP3 chung nhau nên mình sẽ lấy thời gian sản xuất lớn nhất trong 2 sp này -> thời gian còn lại sx sản phẩm 2 là 23- 10.32 = 12.68h => Nên cột M6 sẽ bằng 12.68*60/0.98=776. anh giúp em với ạ
Kết quả đúng mờ, bạn xem cột J và cột K trong file
 

File đính kèm

  • Ví dụ 1 22.1.2021.xlsb
    30.3 KB · Đọc: 12
Web KT
Back
Top Bottom