Tìm số gần nhất trong khoảng để tính hiệu suất và số ngày làm việc (1 người xem)

[thufpts]

Dear các bác !
em có việc này rất mong các bác giúp em, trong 1 file excel em có 2 sheet.
Sheet1 cần lấy dữ liệu từ sheet2 tra về, các bác viết code giúp em trong file đính kèm em gửi.
Em giải thích như sau
Nếu muốn sản xuất 500 ở cột D3 sản phẩm sẽ cần 250 power ở cột E3 tương đương với 20% hiệu suất trung bình tại cột F3 và 1 ngày để sản xuất ở cột G3.
các cột bôi vàng là dữ liệu được lấy từ sheet 2.
Em thường làm thủ công và nói chung là phải mất 2 ngày mới điền được hết dữ liệu mệt mỏi và nản vô cùng.

Lấy cột E3 sheet1 có giá trị là 250 để tìm kiếm bên sheet2 theo đúng mặt hàng bottom(tìm kiếm giá trị là tương đối vì dữ liệu nó chênh lệch nhau)
Nếu E3 sheet1 là 250 mà bên sheet2 ở cột D5 là 255 thì vẫn lấy hiệu suất là 20% tại D3 sheet2 và 1 ngày sau đó trả về sheet1.

Nếu power ở sheet1 và không khớp với power ở sheet2 theo từng mặt hàng thì ưu tiên lấy theo giá trị gần nhất để trả được hiệu suất trung bình và số ngày cần để sản xuất
ví dụ power sheet1 là 250 mà power sheet2 là 255 thì lấy theo 255
từ đó suy ra hiệu suất là 20% và 1 ngày sản xuất

Tóm lại là lấy sum Mhr(power) tại sheet1 để kiểm tra nó nằm sát nhất trong khoảng nào của sheet 2 để trả về hiệu suất và số ngày sản xuất.
Nếu >= 50 đơn vị thì trả về cột bên phải nếu < 50 thì trả về cột bên trái.
là tương tự cho các cột khác sheet 1 là từ tháng 1 đến tháng 12

 

[HieuCD]

Dear các bác !
em có việc này rất mong các bác giúp em, trong 1 file excel em có 2 sheet.
Sheet1 cần lấy dữ liệu từ sheet2 tra về, các bác viết code giúp em trong file đính kèm em gửi.
Em giải thích như sau
Nếu muốn sản xuất 500 ở cột D3 sản phẩm sẽ cần 250 power ở cột E3 tương đương với 20% hiệu suất trung bình tại cột F3 và 1 ngày để sản xuất ở cột G3.
các cột bôi vàng là dữ liệu được lấy từ sheet 2.
Em thường làm thủ công và nói chung là phải mất 2 ngày mới điền được hết dữ liệu mệt mỏi và nản vô cùng.

Lấy cột E3 sheet1 có giá trị là 250 để tìm kiếm bên sheet2 theo đúng mặt hàng bottom(tìm kiếm giá trị là tương đối vì dữ liệu nó chênh lệch nhau)
Nếu E3 sheet1 là 250 mà bên sheet2 ở cột D5 là 255 thì vẫn lấy hiệu suất là 20% tại D3 sheet2 và 1 ngày sau đó trả về sheet1.

Nếu power ở sheet1 và không khớp với power ở sheet2 theo từng mặt hàng thì ưu tiên lấy theo giá trị gần nhất để trả được hiệu suất trung bình và số ngày cần để sản xuất
ví dụ power sheet1 là 250 mà power sheet2 là 255 thì lấy theo 255
từ đó suy ra hiệu suất là 20% và 1 ngày sản xuất

Tóm lại là lấy sum Mhr(power) tại sheet1 để kiểm tra nó nằm sát nhất trong khoảng nào của sheet 2 để trả về hiệu suất và số ngày sản xuất.
Nếu >= 50 đơn vị thì trả về cột bên phải nếu < 50 thì trả về cột bên trái.
là tương tự cho các cột khác sheet 1 là từ tháng 1 đến tháng 12

chạy thử code

Sub Eff()
Dim dic As Object, key, Darr As Variant, Arr As Variant, Sarr() As Variant, Tarr As Variant
Dim i As Long, j As Long, k As Long, jk As Long, Power As Long, Pmin As Long, Pmax As Long, Tong1, Tong2
Set dic = CreateObject("scripting.dictionary")
With Sheets("Sheet1")
  Sarr = .Range("C3:AY" & .Range("A" & Rows.Count).End(xlUp).Row).Value
End With
ReDim Arr(1 To UBound(Sarr), 1 To 2)
For i = 1 To UBound(Sarr)
  key = Sarr(i, 1)
  If Not dic.exists(key) Then
    dic.Add key, k * 2 + 1
    k = k + 1
  End If
Next i

With Sheets("Sheet2")
  Darr = .Range("B1:AH" & .Range("B" & Rows.Count).End(xlUp).Row).Value
  i = Application.Max(.Range("D2").Resize(UBound(Darr) - 1, UBound(Darr, 2) - 2))
  ReDim Tarr(1 To i, 1 To k * 2)
End With

For i = 5 To UBound(Darr) Step 4
  key = Darr(i, 1)
  If dic.exists(key) Then
    jk = dic.Item(key)
    For j = 3 To UBound(Darr, 2)
      If Darr(i, j) <> "" Then
        If Tarr(Darr(i, j), jk) = "" Then
          Tarr(Darr(i, j), jk) = Darr(i - 2, j)
          Tarr(Darr(i, j), jk + 1) = Darr(1, j)
        End If
      End If
    Next j
  End If
Next i
For j = 3 To UBound(Sarr, 2) Step 4
  If Sarr(1, j) = "" Then Exit Sub 'dòng dau Khong có du lieu, thoat sub
  Tong1 = 0:          Tong2 = 0
  For i = 1 To UBound(Sarr)
    If Sarr(i, 1) <> "" Then
      jk = dic.Item(Sarr(i, 1))
      Power = Sarr(i, j)
      If Tarr(Power, jk) <> "" Then
        Arr(i, 1) = Tarr(Power, jk)
        Arr(i, 2) = Tarr(Power, jk + 1)
      Else
        Pmin = -1000000: Pmax = 1000000
        For k = Power - 1 To 1 Step -1
          If Tarr(k, jk) <> "" Then Pmin = k: Exit For
        Next k
        For k = Power To UBound(Tarr)
          If Tarr(k, jk) <> "" Then Pmax = k: Exit For
        Next k
        If Pmax - Power < Power - Pmin Then k = Pmax Else k = Pmin
        Arr(i, 1) = Tarr(k, jk)
        Arr(i, 2) = Tarr(k, jk + 1)
      End If
      Tong1 = Tong1 + Arr(i, 1)
      Tong2 = Tong2 + Arr(i, 2)
    Else
      Arr(i, 1) = Tong1:  Arr(i, 2) = Tong2
      Tong1 = 0:          Tong2 = 0
    End If
  Next i
  Sheets("Sheet1").Cells(3, 3 + j).Resize(UBound(Arr), 2) = Arr
Next j
End Sub

 

[quocgiacan]

chạy thử code

Sub Eff()
Dim dic As Object, key, Darr As Variant, Arr As Variant, Sarr() As Variant, Tarr As Variant
Dim i As Long, j As Long, k As Long, jk As Long, Power As Long, Pmin As Long, Pmax As Long, Tong1, Tong2
Set dic = CreateObject("scripting.dictionary")
With Sheets("Sheet1")
  Sarr = .Range("C3:AY" & .Range("A" & Rows.Count).End(xlUp).Row).Value
End With
ReDim Arr(1 To UBound(Sarr), 1 To 2)
For i = 1 To UBound(Sarr)
  key = Sarr(i, 1)
  If Not dic.exists(key) Then
    dic.Add key, k * 2 + 1
    k = k + 1
  End If
Next i

With Sheets("Sheet2")
  Darr = .Range("B1:AH" & .Range("B" & Rows.Count).End(xlUp).Row).Value
  i = Application.Max(.Range("D2").Resize(UBound(Darr) - 1, UBound(Darr, 2) - 2))
  ReDim Tarr(1 To i, 1 To k * 2)
End With

For i = 5 To UBound(Darr) Step 4
  key = Darr(i, 1)
  If dic.exists(key) Then
    jk = dic.Item(key)
    For j = 3 To UBound(Darr, 2)
      If Darr(i, j) <> "" Then
        If Tarr(Darr(i, j), jk) = "" Then
          Tarr(Darr(i, j), jk) = Darr(i - 2, j)
          Tarr(Darr(i, j), jk + 1) = Darr(1, j)
        End If
      End If
    Next j
  End If
Next i
For j = 3 To UBound(Sarr, 2) Step 4
  If Sarr(1, j) = "" Then Exit Sub 'dòng dau Khong có du lieu, thoat sub
  Tong1 = 0:          Tong2 = 0
  For i = 1 To UBound(Sarr)
    If Sarr(i, 1) <> "" Then
      jk = dic.Item(Sarr(i, 1))
      Power = Sarr(i, j)
      If Tarr(Power, jk) <> "" Then
        Arr(i, 1) = Tarr(Power, jk)
        Arr(i, 2) = Tarr(Power, jk + 1)
      Else
        Pmin = -1000000: Pmax = 1000000
        For k = Power - 1 To 1 Step -1
          If Tarr(k, jk) <> "" Then Pmin = k: Exit For
        Next k
        For k = Power To UBound(Tarr)
          If Tarr(k, jk) <> "" Then Pmax = k: Exit For
        Next k
        If Pmax - Power < Power - Pmin Then k = Pmax Else k = Pmin
        Arr(i, 1) = Tarr(k, jk)
        Arr(i, 2) = Tarr(k, jk + 1)
      End If
      Tong1 = Tong1 + Arr(i, 1)
      Tong2 = Tong2 + Arr(i, 2)
    Else
      Arr(i, 1) = Tong1:  Arr(i, 2) = Tong2
      Tong1 = 0:          Tong2 = 0
    End If
  Next i
  Sheets("Sheet1").Cells(3, 3 + j).Resize(UBound(Arr), 2) = Arr
Next j
End Sub

Thấy Code của bạn hiền hiệu quả "Ba trong một" nút nhấn, ngưỡng mộ và cũng tò mò , nên muốn góp vui bằng giải pháp công thức (và cũng nếu chủ thớt có nhã hứng tham khảo).

F3=IF(COUNTIF($A3,$A2&" Total"),SUMIF(A$2:$A2,$A2,F$2:F2),IFERROR(OFFSET(Data!$C$1,MATCH($C3&F$2,INDEX(Data!$B$2:$B$45&Data!$C$2:$C$45,),0),G3)*(E3>0),0))
G3=IF(COUNTIF($A3,$A2&" Total"),SUMIF($A$2:$A2,$A2,G$2:G2),MOD(AGGREGATE(15,6,ABS(E3-OFFSET(Data!$D$1:$AH$1,MATCH($C3&E$2,Data!$B$2:$B$45&Data!$C$2:$C$45,0),))*10^4+COLUMN($A:$AE),1),10^4)*(E3>0))

Enter fill xuống. Xong copy dán qua các cột tương ứng.

Chúc bạn hiền ngày mới thiệt vui và năng động.

 

[Lê Duy Thương]

Thấy Code của bạn hiền hiệu quả "Ba trong một" nút nhấn, ngưỡng mộ và cũng tò mò , nên muốn góp vui bằng giải pháp công thức (và cũng nếu chủ thớt có nhã hứng tham khảo).

F3=IF(COUNTIF($A3,$A2&" Total"),SUMIF(A$2:$A2,$A2,F$2:F2),IFERROR(OFFSET(Data!$C$1,MATCH($C3&F$2,INDEX(Data!$B$2:$B$45&Data!$C$2:$C$45,),0),G3)*(E3>0),0))
G3=IF(COUNTIF($A3,$A2&" Total"),SUMIF($A$2:$A2,$A2,G$2:G2),MOD(AGGREGATE(15,6,ABS(E3-OFFSET(Data!$D$1:$AH$1,MATCH($C3&E$2,Data!$B$2:$B$45&Data!$C$2:$C$45,0),))*10^4+COLUMN($A:$AE),1),10^4)*(E3>0))

Enter fill xuống. Xong copy dán qua các cột tương ứng.

Chúc bạn hiền ngày mới thiệt vui và năng động.

Xem bằng điện thoại nên không biết kết quả nhưng vẫn ủng hộ cho anh quocgiacan 100 likes vì lòng nhiệt tình. Thương dạo này bận quá nên không thường xuyên online được. Nhưng offline chắc được.

 

[thufpts]

chạy thử code

Sub Eff()
Dim dic As Object, key, Darr As Variant, Arr As Variant, Sarr() As Variant, Tarr As Variant
Dim i As Long, j As Long, k As Long, jk As Long, Power As Long, Pmin As Long, Pmax As Long, Tong1, Tong2
Set dic = CreateObject("scripting.dictionary")
With Sheets("Sheet1")
  Sarr = .Range("C3:AY" & .Range("A" & Rows.Count).End(xlUp).Row).Value
End With
ReDim Arr(1 To UBound(Sarr), 1 To 2)
For i = 1 To UBound(Sarr)
  key = Sarr(i, 1)
  If Not dic.exists(key) Then
    dic.Add key, k * 2 + 1
    k = k + 1
  End If
Next i

With Sheets("Sheet2")
  Darr = .Range("B1:AH" & .Range("B" & Rows.Count).End(xlUp).Row).Value
  i = Application.Max(.Range("D2").Resize(UBound(Darr) - 1, UBound(Darr, 2) - 2))
  ReDim Tarr(1 To i, 1 To k * 2)
End With

For i = 5 To UBound(Darr) Step 4
  key = Darr(i, 1)
  If dic.exists(key) Then
    jk = dic.Item(key)
    For j = 3 To UBound(Darr, 2)
      If Darr(i, j) <> "" Then
        If Tarr(Darr(i, j), jk) = "" Then
          Tarr(Darr(i, j), jk) = Darr(i - 2, j)
          Tarr(Darr(i, j), jk + 1) = Darr(1, j)
        End If
      End If
    Next j
  End If
Next i
For j = 3 To UBound(Sarr, 2) Step 4
  If Sarr(1, j) = "" Then Exit Sub 'dòng dau Khong có du lieu, thoat sub
  Tong1 = 0:          Tong2 = 0
  For i = 1 To UBound(Sarr)
    If Sarr(i, 1) <> "" Then
      jk = dic.Item(Sarr(i, 1))
      Power = Sarr(i, j)
      If Tarr(Power, jk) <> "" Then
        Arr(i, 1) = Tarr(Power, jk)
        Arr(i, 2) = Tarr(Power, jk + 1)
      Else
        Pmin = -1000000: Pmax = 1000000
        For k = Power - 1 To 1 Step -1
          If Tarr(k, jk) <> "" Then Pmin = k: Exit For
        Next k
        For k = Power To UBound(Tarr)
          If Tarr(k, jk) <> "" Then Pmax = k: Exit For
        Next k
        If Pmax - Power < Power - Pmin Then k = Pmax Else k = Pmin
        Arr(i, 1) = Tarr(k, jk)
        Arr(i, 2) = Tarr(k, jk + 1)
      End If
      Tong1 = Tong1 + Arr(i, 1)
      Tong2 = Tong2 + Arr(i, 2)
    Else
      Arr(i, 1) = Tong1:  Arr(i, 2) = Tong2
      Tong1 = 0:          Tong2 = 0
    End If
  Next i
  Sheets("Sheet1").Cells(3, 3 + j).Resize(UBound(Arr), 2) = Arr
Next j
End Sub

Bác HieuCD ơi em gặp phải vấn đề mong bác chỉnh sửa giúp em vì trong lúc tính toán em chưa thể hình dung được các vấn đề phát sinh.
1. dòng total nó không được fix cố định mà nó sẽ thay đổi vị trí ví dụ lúc nó ở dòng 14 lúc nó ở dòng 24 hoặc bất cứ dòng nào vì vậy nếu fix cố định thì sẽ bị sai mất kế quả. bác có thể để thuật toán cho nó không cố định mà vẫn tính được tổng không. Chỉ tính tổng cho cột Sum Qty, Power và cột
Sah produce Accumulate còn cột Eff Average thì không tính tổng mà sẽ bằng SUM(cột E * Cột F)/SUM(Cột E) theo Cột A (buyer)
2. Nếu em thiết lập style có power nằm ngoài khoảng bên style và power bên sheet2 thì nó sẽ báo lỗi.
Ví dụ khi em nhập style có power bất kỳ là 900 bên sheet1 nhưng bên sheet2 style có power tương ứng mà nhỏ hơn 900 nó sẽ báo lỗi ở dòng code
If Tarr(Power, jk) <> "" Then.
trong trường hợp này em thấy rằng nó chỉ tính sát nhất trong trường hợp nhỏ hơn còn lớn hơn cụ thể ở đây sheet1 chỉ cần lớn hơn sheet2 một đơn vị là báo lỗi. bác có thể fix lỗi này giúp em với.
+) Nếu sheet1 style có power lớn hơn style và power tương ứng bên sheet2 thì lấy giá trị cao nhất của style có power tương ứng bên sheet2, nếu bôi đỏ được nó nữa thì càng tốt khi em nhìn vào em có thể biết được nó đang nằm ngoài khoảng.
3. em vừa cập nhật thêm dữ liệu sau đó nhấn button thì nó chạy không ra kết quả cũng không báo lỗi. em gửi lại file bác xem giúp
em cám ơn

 

[thufpts]

Thấy Code của bạn hiền hiệu quả "Ba trong một" nút nhấn, ngưỡng mộ và cũng tò mò , nên muốn góp vui bằng giải pháp công thức (và cũng nếu chủ thớt có nhã hứng tham khảo).

F3=IF(COUNTIF($A3,$A2&" Total"),SUMIF(A$2:$A2,$A2,F$2:F2),IFERROR(OFFSET(Data!$C$1,MATCH($C3&F$2,INDEX(Data!$B$2:$B$45&Data!$C$2:$C$45,),0),G3)*(E3>0),0))
G3=IF(COUNTIF($A3,$A2&" Total"),SUMIF($A$2:$A2,$A2,G$2:G2),MOD(AGGREGATE(15,6,ABS(E3-OFFSET(Data!$D$1:$AH$1,MATCH($C3&E$2,Data!$B$2:$B$45&Data!$C$2:$C$45,0),))*10^4+COLUMN($A:$AE),1),10^4)*(E3>0))

Enter fill xuống. Xong copy dán qua các cột tương ứng.

Chúc bạn hiền ngày mới thiệt vui và năng động.

Hi Bác. Công thức của bác chạy rất ok trong trường hợp sheet data có dữ liệu không đổi còn khi em thay đổi lại số thì nó không ra kết quả em lay hoay mất gần 2 tuần nay sửa mà càng sửa càng sai mong bác sửa giúp trong file đính kèm em gửi

 

[quocgiacan]

Hi Bác. Công thức của bác chạy rất ok trong trường hợp sheet data có dữ liệu không đổi còn khi em thay đổi lại số thì nó không ra kết quả em lay hoay mất gần 2 tuần nay sửa mà càng sửa càng sai mong bác sửa giúp trong file đính kèm em gửi

1. Do dữ liệu mẫu của bạn đưa lúc đầu không có số lẻ, nay số thực tế có số lẻ thì phải khác chút.
2. Lưu ý: mã "Zipper jacket " có 2 khoảng trắng ở sau cùng--> nên nó báo lỗi vì không so khớp

Bạn xem lại file.

Chúc bạn ngày vui.
p/s: mấy nay không biết bạn hiền tôi đi đâu mất tiêu rồi! nếu không hỗ trợ VBA cho bạn nhẹ file. Công thức của tôi gửi chỉ có tính tham khảo, nếu dữ liệu quá nhiều (>1000 dòng) sẽ làm chậm tốc độ xử lý.

 

[thufpts]

1. Do dữ liệu mẫu của bạn đưa lúc đầu không có số lẻ, nay số thực tế có số lẻ thì phải khác chút.
2. Lưu ý: mã "Zipper jacket " có 2 khoảng trắng ở sau cùng--> nên nó báo lỗi vì không so khớp

Bạn xem lại file.

Chúc bạn ngày vui.
p/s: mấy nay không biết bạn hiền tôi đi đâu mất tiêu rồi! nếu không hỗ trợ VBA cho bạn nhẹ file. Công thức của tôi gửi chỉ có tính tham khảo, nếu dữ liệu quá nhiều (>1000 dòng) sẽ làm chậm tốc độ xử lý.

Ghê thật bác sửa nhanh vậy. có lẽ nào não của em vẫn chưa tiến hóa hết gần 2 tuần mà cũng không biết nó sai ở đâu.
Bác cho em hỏi chút vì em quên mất. toàn bộ cột Eff Average bác đổi sum thành tính Average được không.

 

[quocgiacan]

Ghê thật bác sửa nhanh vậy. có lẽ nào não của em vẫn chưa tiến hóa hết gần 2 tuần mà cũng không biết nó sai ở đâu.
Bác cho em hỏi chút vì em quên mất. toàn bộ cột Eff Average bác đổi sum thành tính Average được không.

1. Giải pháp: dùng ROUND(,0) để triệt tiêu số lẻ cũng được, hay dùng 10^3 thay cho 10^4 tại vị trí nó đứng cuối cùng cũng là 1 cách. 'Cha Mẹ' sinh thành chắc chắn sẽ biết 'rõ' về tính khí của 'con cái' nhiều hơn, không có gì lạ và hay hết!
2. Chưa hiểu ý bạn muốn gì? kết quả chỗ nào? bạn phải nói rõ hơn.

Chúc bạn ngày vui.

 

[thufpts]

1. Giải pháp: dùng ROUND(,0) để triệt tiêu số lẻ cũng được, hay dùng 10^3 thay cho 10^4 tại vị trí nó đứng cuối cùng cũng là 1 cách. 'Cha Mẹ' sinh thành chắc chắn sẽ biết 'rõ' về tính khí của 'con cái' nhiều hơn, không có gì lạ và hay hết!
2. Chưa hiểu ý bạn muốn gì? kết quả chỗ nào? bạn phải nói rõ hơn.

Chúc bạn ngày vui.

À đây bác ạ bác xem cái ảnh bên dưới. cái 350% là tổng bây giờ thay vì tính tổng bác tính giá trị trung bình được không, nghĩa là cộng vào và chia cho 11 được trung bình
là 32 %
45% 32% 29% 32% 32% 29% 32% 29% 29% 34% 32%

 

[quocgiacan]

À đây bác ạ bác xem cái ảnh bên dưới. cái 350% là tổng bây giờ thay vì tính tổng bác tính giá trị trung bình được không, nghĩa là cộng vào và chia cho 11 được trung bình
là 32 %
45% 32% 29% 32% 32% 29% 32% 29% 29% 34% 32%

View attachment 182050

Thay vì đoạn hàm: SUMIF($A$2:A2,$A2,N$2:N2) bạn thay bằng AVERAGEIF($A$2:A2,$A2,N$2:N2) rồi copy xuống các dòng dưới là xong liền. (tức chỉ bỏ chữ SUM thay bằng AVERAGE)

Chúc bạn ngày vui.

 

[thufpts]

Cám ơn bác rất nhiều !

 

[thufpts]

Thay vì đoạn hàm: SUMIF($A$2:A2,$A2,N$2:N2) bạn thay bằng AVERAGEIF($A$2:A2,$A2,N$2:N2) rồi copy xuống các dòng dưới là xong liền. (tức chỉ bỏ chữ SUM thay bằng AVERAGE)

Chúc bạn ngày vui.

Bác ơi sr bác nhưng nó có vấn đề rồi bác ạ em đang kéo công thức để xem kết quả thì nó sảy lỗi như sau.
Từ cột E13 đến G13 nếu Power là 25 thì hiệu suất trung bình lẽ ra nó phải là 25% và Sah produce Accumulate là 1, nhưng đằng này nó lại là 0% và Sah produce Accumulate là 31.
Nếu như power theo type bất kỳ bên sheet GPE mà nhỏ hơn khoảng đã cho trước bên sheet data thì mặc định trả về giá trị đầu tiên và Sah produce Accumulate là 1.

em gửi lại file đính kèm bác xem giúp em với.

 

[quocgiacan]

Bác ơi sr bác nhưng nó có vấn đề rồi bác ạ em đang kéo công thức để xem kết quả thì nó sảy lỗi như sau.
Từ cột E13 đến G13 nếu Power là 25 thì hiệu suất trung bình lẽ ra nó phải là 25% và Sah produce Accumulate là 1, nhưng đằng này nó lại là 0% và Sah produce Accumulate là 31.
Nếu như power theo type bất kỳ bên sheet GPE mà nhỏ hơn khoảng đã cho trước bên sheet data thì mặc định trả về giá trị đầu tiên và Sah produce Accumulate là 1.
em gửi lại file đính kèm bác xem giúp em với.

Không có gì phải lo!

Do công thức chọn 31 cột: Data!$D$1:$AH$1, tương ứng với 31 ngày!? nhưng cột thứ 31 thì số liệu trống không nên khi đem số của Power= 25, trừ số của cột 31 = 0, nó ra 25 nên đó là số chênh lệch gần nhất nó phải lấy kết quả số cột 31 là 'chính xác'. Công thức đúng, nhưng người viết 'chỉ thị' cho nó sai .

Bạn bổ sung thêm chút xíu xiu:

G3=IF(COUNTIF($A3,$A2&" Total"),AVERAGEIF($A$2:$A2,$A2,G$2:G2),MOD(AGGREGATE(15,6,ABS(E3-1/(1/OFFSET(Data!$D$1:$AH$1,MATCH($C3&E$2,Data!$B$2:$B$45&Data!$C$2:$C$45,0),)))*10^4+COLUMN($A:$AE),1),10^3)*(E3>0))

Enter rồi fill xuống.

Chúc bạn ngày vui.
p/s: làm cái gì cũng được, nặng nhẹ cũng được, nhưng tha cho tôi cái vụ 'sờ rờ' ("sr") nha! Cảm ơn

 

[thufpts]

Không có gì phải lo!

Do công thức chọn 31 cột: Data!$D$1:$AH$1, tương ứng với 31 ngày!? nhưng cột thứ 31 thì số liệu trống không nên khi đem số của Power= 25, trừ số của cột 31 = 0, nó ra 25 nên đó là số chênh lệch gần nhất nó phải lấy kết quả số cột 31 là 'chính xác'. Công thức đúng, nhưng người viết 'chỉ thị' cho nó sai .

Bạn bổ sung thêm chút xíu xiu:

G3=IF(COUNTIF($A3,$A2&" Total"),AVERAGEIF($A$2:$A2,$A2,G$2:G2),MOD(AGGREGATE(15,6,ABS(E3-1/(1/OFFSET(Data!$D$1:$AH$1,MATCH($C3&E$2,Data!$B$2:$B$45&Data!$C$2:$C$45,0),)))*10^4+COLUMN($A:$AE),1),10^3)*(E3>0))

Enter rồi fill xuống.

Chúc bạn ngày vui.
p/s: làm cái gì cũng được, nặng nhẹ cũng được, nhưng tha cho tôi cái vụ 'sờ rờ' ("sr") nha! Cảm ơn

em không sờ rờ nữa kaka. em copy xong nó bị lỗi ở Top/Bra cứ chỗ nào có Top/Bra là nó báo cái này #NUM!

 

[quocgiacan]

em không sờ rờ nữa kaka. em copy xong nó bị lỗi ở Top/Bra cứ chỗ nào có Top/Bra là nó báo cái này #NUM!

Tôi cố tình để nó báo lỗi, vì Data/Top/Bra đâu có dữ liệu nào đâu!?
Vậy bạn muốn chỗ báo lỗi hiện kết quả gì?

 

[thufpts]

Tôi cố tình để nó báo lỗi, vì Data/Top/Bra đâu có dữ liệu nào đâu!?
Vậy bạn muốn chỗ báo lỗi hiện kết quả gì?

thế cũng được bác ạ. để em kéo thêm dữ liệu vào xem nó có bị làm sao không rồi em lại nhờ bác giúp.

 

[thufpts]

Không có gì phải lo!

Do công thức chọn 31 cột: Data!$D$1:$AH$1, tương ứng với 31 ngày!? nhưng cột thứ 31 thì số liệu trống không nên khi đem số của Power= 25, trừ số của cột 31 = 0, nó ra 25 nên đó là số chênh lệch gần nhất nó phải lấy kết quả số cột 31 là 'chính xác'. Công thức đúng, nhưng người viết 'chỉ thị' cho nó sai .

Bạn bổ sung thêm chút xíu xiu:

G3=IF(COUNTIF($A3,$A2&" Total"),AVERAGEIF($A$2:$A2,$A2,G$2:G2),MOD(AGGREGATE(15,6,ABS(E3-1/(1/OFFSET(Data!$D$1:$AH$1,MATCH($C3&E$2,Data!$B$2:$B$45&Data!$C$2:$C$45,0),)))*10^4+COLUMN($A:$AE),1),10^3)*(E3>0))

Enter rồi fill xuống.

Chúc bạn ngày vui.
p/s: làm cái gì cũng được, nặng nhẹ cũng được, nhưng tha cho tôi cái vụ 'sờ rờ' ("sr") nha! Cảm ơn

Không ổn bác ạ nó không chạy đúng rồi, khi em kéo công thức cho tất cả các tháng mới thấy được nhiều vấn đề.
Eff Average không trả về sát với khoảng cần lấy, ví dụ từ I37 đến K37 lẽ ra power là 162 thì nó phải tra về 25% và date là 1 hoặc 32% và date là 2 nhưng nó lại trả về 0% và date là 5002, nó bị lệch nhiều lắm bác xem giúp em với

 

[quocgiacan]

Không ổn bác ạ nó không chạy đúng rồi, khi em kéo công thức cho tất cả các tháng mới thấy được nhiều vấn đề.
Eff Average không trả về sát với khoảng cần lấy, ví dụ từ I37 đến K37 lẽ ra power là 162 thì nó phải tra về 25% và date là 1 hoặc 32% và date là 2 nhưng nó lại trả về 0% và date là 5002, nó bị lệch nhiều lắm bác xem giúp em với

Bạn chép công thức, rồi bổ sung thêm một số nữa, rồi không báo là số 'Power' có số lẻ quá nhiều lần nên công thức *10^4 không ăn thua, làm kết quả không chính xác. Do vậy, bắt buộc phải sử dụng ROUND để khống chế.

Bạn xem lại file

Chúc bạn ngày vui

 

[thufpts]

Bạn chép công thức, rồi bổ sung thêm một số nữa, rồi không báo là số 'Power' có số lẻ quá nhiều lần nên công thức *10^4 không ăn thua, làm kết quả không chính xác. Do vậy, bắt buộc phải sử dụng ROUND để khống chế.

Bạn xem lại file

Chúc bạn ngày vui

Sau một hồi sờ mò em không phát hiện được thêm lỗi nào nữa nó chạy ổn rồi. cám ơn bác rất nhiều.

 

[thufpts]

Bạn chép công thức, rồi bổ sung thêm một số nữa, rồi không báo là số 'Power' có số lẻ quá nhiều lần nên công thức *10^4 không ăn thua, làm kết quả không chính xác. Do vậy, bắt buộc phải sử dụng ROUND để khống chế.

Bạn xem lại file
Chúc bạn ngày vui

Bác ơi em xin cầu cứu bác vụ này cũng đau đầu lắm. trong công thức của bác có thể sửa giúp em Total thay vì để AVERAGEIF thì tính như anh bên dưới được không
Total của từng buyer dòng màu xanh F5= Sum(E2*F2 + E3*F3 + E4*F4)/Sum(E2:E4) tương tự cho F15
còn dòng màu đen Grand Total F16 = SUM(E5*F5+E15*F15)/SUM(E5+E15)
Mong bác giúp em

 

[quocgiacan]

Bác ơi em xin cầu cứu bác vụ này cũng đau đầu lắm. trong công thức của bác có thể sửa giúp em Total thay vì để AVERAGEIF thì tính như anh bên dưới được không
Total của từng buyer dòng màu xanh F5= Sum(E2*F2 + E3*F3 + E4*F4)/Sum(E2:E4) tương tự cho F15
còn dòng màu đen Grand Total F16 = SUM(E5*F5+E15*F15)/SUM(E5+E15)
Mong bác giúp em

View attachment 182329

Thử thay AVERAGEIF() bằng --> IFERROR(SUMPRODUCT(F$1:F1,E$1:E1,N($A$1:$A1=$A1))/SUMIF($A$1:$A1,$A1,E$1:E1),0)

F2=IF(COUNTIF($A2,$A1&" Total"),IFERROR(SUMPRODUCT(F$1:F1,E$1:E1,N($A$1:$A1=$A1))/SUMIF($A$1:$A1,$A1,E$1:E1),0),IFERROR(OFFSET(calculate!$C$1,MATCH($C2&F$1,INDEX(calculate!$B$2:$B$45&calculate!$C$2:$C$45,),0),G2)*(E2>0),0))

Enter, rồi fill xuống. Copy qua các cột tương ứng.

Riêng "Grand Total" bạn nên tính riêng vì là dòng cuối cùng, không nên thêm vào công thức thành: 3 trong 1 sẽ làm phức tạp thêm công thức.

E1036=SUMIF($A$2:$A$1035,"*Total",$E$2:$E$1035)
F1036=IFERROR(SUMPRODUCT(F$2:$F$1035,E$2:$E$1035,ISNUMBER(FIND("Total",$A$2:$A$1035))*1)/$E$1036,0)

Chúc bạn ngày vui.

 

[thufpts]

Thử thay AVERAGEIF() bằng --> IFERROR(SUMPRODUCT(F$1:F1,E$1:E1,N($A$1:$A1=$A1))/SUMIF($A$1:$A1,$A1,E$1:E1),0)

F2=IF(COUNTIF($A2,$A1&" Total"),IFERROR(SUMPRODUCT(F$1:F1,E$1:E1,N($A$1:$A1=$A1))/SUMIF($A$1:$A1,$A1,E$1:E1),0),IFERROR(OFFSET(calculate!$C$1,MATCH($C2&F$1,INDEX(calculate!$B$2:$B$45&calculate!$C$2:$C$45,),0),G2)*(E2>0),0))

Enter, rồi fill xuống. Copy qua các cột tương ứng.

Riêng "Grand Total" bạn nên tính riêng vì là dòng cuối cùng, không nên thêm vào công thức thành: 3 trong 1 sẽ làm phức tạp thêm công thức.

E1036=SUMIF($A$2:$A$1035,"*Total",$E$2:$E$1035)
F1036=IFERROR(SUMPRODUCT(F$2:$F$1035,E$2:$E$1035,ISNUMBER(FIND("Total",$A$2:$A$1035))*1)/$E$1036,0)

Chúc bạn ngày vui.

Em đã thay vào nhưng tất cả dòng màu xanh mà em đã trình bày lần trước nó đều là 0%. em đang thử nghiệm ở cột F
vì file này nó bắt buộc phải để ở dạng table cho nên nếu Grand Total mà viết riêng một công thức thì tất cả các dòng còn lại nó sẽ theo công thức của Grand Total chứ nó không
độc lập được bác ạ. hôm qua em có ngồi nghiên cứu công thức của bác để sửa xem có được không nhưng vốn dĩ em chưa hiểu lắm nên không sửa được.
em gửi lại bác hỗ trợ em với.

 

[quocgiacan]

Em đã thay vào nhưng tất cả dòng màu xanh mà em đã trình bày lần trước nó đều là 0%. em đang thử nghiệm ở cột F
vì file này nó bắt buộc phải để ở dạng table cho nên nếu Grand Total mà viết riêng một công thức thì tất cả các dòng còn lại nó sẽ theo công thức của Grand Total chứ nó không
độc lập được bác ạ. hôm qua em có ngồi nghiên cứu công thức của bác để sửa xem có được không nhưng vốn dĩ em chưa hiểu lắm nên không sửa được.
em gửi lại bác hỗ trợ em với.

• Do các cột: Q'ty, MHR.. bạn phải định dạng 'Số' (Number) cho các cột dữ liệu là số, bạn để dữ liệu dạng text nên nó coi như số 0 nên tính toán kết quả = 0.
• Về dòng cuối cùng "Grand Total" đâu nhất thiết nó nằm trong 'Table', bạn làm dòng riêng ra là được thôi.

Bạn xem file đính kèm.

Chúc bạn ngày vui.

 

[thufpts]

• Do các cột: Q'ty, MHR.. bạn phải định dạng 'Số' (Number) cho các cột dữ liệu là số, bạn để dữ liệu dạng text nên nó coi như số 0 nên tính toán kết quả = 0.
• Về dòng cuối cùng "Grand Total" đâu nhất thiết nó nằm trong 'Table', bạn làm dòng riêng ra là được thôi.

Bạn xem file đính kèm.

Chúc bạn ngày vui.

Grand Total thì ok rồi bác ạ nhưng còn cái định dạng text và number
Nó không dừng lại ở mức đấy bác ạ chính vì nó ở dạng table dữ liệu sẽ luôn được cập nhật liên tục từ hệ thống bằng cách chạy query vì vậy mà định dạng nó không giữ được như ban đầu nữa. vừa rồi em định chuyển đổi công thức của bác bằng cách dùng hàm value nhưng chẳng biết đặt value vào chỗ nào nữa. bác xem giúp em với.

 

[quocgiacan]

Grand Total thì ok rồi bác ạ nhưng còn cái định dạng text và number
Nó không dừng lại ở mức đấy bác ạ chính vì nó ở dạng table dữ liệu sẽ luôn được cập nhật liên tục từ hệ thống bằng cách chạy query vì vậy mà định dạng nó không giữ được như ban đầu nữa. vừa rồi em định chuyển đổi công thức của bác bằng cách dùng hàm value nhưng chẳng biết đặt value vào chỗ nào nữa. bác xem giúp em với.

Bạn xem file đính kèm.

Lưu ý: công thức cột F và Grand Total là công thức mảng, kết thúc lệnh phải nhấn Ctrl+Shift+Enter

Chúc bạn ngày vui

 

[thufpts]

Bạn xem file đính kèm.

Lưu ý: công thức cột F và Grand Total là công thức mảng, kết thúc lệnh phải nhấn Ctrl+Shift+Enter

Chúc bạn ngày vui

Nó chạy ổn rồi bác. em cám ơn rất nhiều em sẽ học được rất nhiều từ công thức của bác

 

[Kieutri]

Thấy Code của bạn hiền hiệu quả "Ba trong một" nút nhấn, ngưỡng mộ và cũng tò mò , nên muốn góp vui bằng giải pháp công thức (và cũng nếu chủ thớt có nhã hứng tham khảo).

F3=IF(COUNTIF($A3,$A2&" Total"),SUMIF(A$2:$A2,$A2,F$2:F2),IFERROR(OFFSET(Data!$C$1,MATCH($C3&F$2,INDEX(Data!$B$2:$B$45&Data!$C$2:$C$45,),0),G3)*(E3>0),0))
G3=IF(COUNTIF($A3,$A2&" Total"),SUMIF($A$2:$A2,$A2,G$2:G2),MOD(AGGREGATE(15,6,ABS(E3-OFFSET(Data!$D$1:$AH$1,MATCH($C3&E$2,Data!$B$2:$B$45&Data!$C$2:$C$45,0),))*10^4+COLUMN($A:$AE),1),10^4)*(E3>0))

Enter fill xuống. Xong copy dán qua các cột tương ứng.

Chúc bạn hiền ngày mới thiệt vui và năng động.

Hay quá anh ơi. Công thức tuyệt vời. Cảm ơn anh