Nhờ sửa lại công thức tính thành tiền

[TRA KHUC RIVER]

Em có file excel đính kèm, ở cột thành tiền của máy mài Hitachi 100 máy số 08 em dùng công thức này để tính:

=SUMPRODUCT(--(ISERROR(SEARCH(DG!$C$6:$C$10;TTSC!D6))=FALSE);DG!$D$6:$D$10)

tuy nhiên công thức trên chỉ dò tìm từng giá trị, nhưng ở đây máy này bị hỏng 2 bi như vậy khi tính thành tiền bi phải được nhân đôi, thành tiền là 210.000, nhưng ở đây chỉ ra giá trị là 195.000, em không biết phải cài công thức như thế nào cho đúng nhờ mọi người chỉ giúp, em cảm ơn.

 

[ndu96081631]

Cái này nhờ anh em trên GPE làm giúp đấy anh, nhưng em đang bí ở cột E: thành tiền sửa chữa dùng sumproduct nó không tự nhân lên được anh ơi.

Vậy thì thêm name Hệ số nhân nữa (dò tìm trong chuổi, thấy số nào, nhân số nấy)
Tuy nhiên cách làm của bạn như vậy là không hay lắm đâu (chỉ tổ cực thân)
Xem file
-------------------
Ôi trời... Vừa xem lại file... rắc rối hơn tôi tưởng rất nhiều:
Hỏng ruột, 2 bi, than xịn có nghĩa là (Hỏng ruột)*1 +(bi)*2 + (than xịn)*1
Để xem lại cái

 

[salam]

Em có file excel đính kèm, ở cột thành tiền của máy mài Hitachi 100 máy số 08 em dùng công thức này để tính:

=SUMPRODUCT(--(ISERROR(SEARCH(DG!$C$6:$C$10;TTSC!D6))=FALSE);DG!$D$6:$D$10)

tuy nhiên công thức trên chỉ dò tìm từng giá trị, nhưng ở đây máy này bị hỏng 2 bi như vậy khi tính thành tiền bi phải được nhân đôi, thành tiền là 210.000, nhưng ở đây chỉ ra giá trị là 195.000, em không biết phải cài công thức như thế nào cho đúng nhờ mọi người chỉ giúp, em cảm ơn.

Bạn cộng thêm 1 bi nữa:

=IF(F6="";SUMPRODUCT(--(ISERROR(SEARCH(DG!$C$6:$C$10;TTSC!D6))=FALSE);DG!$D$6:$D$10)+IF(ISERROR(FIND(2;D6));0;SUMPRODUCT(--(DG!$C$6:$C$10="bi");DG!$D$6:$D$10));0)

 

[TRA KHUC RIVER]

Cảm ơn anh salam đã giúp đỡ nhưng công thức trên nếu sửa hỏng 2 bi thành hỏng 4 bi hay sửa các giá trị khác như hỏng 3 than xịn chẳng hạn thì nó không ra kết quả chính xác

 

[sealand]

Theo mình đơn giản nhất là bảng giá sửa dòng
bi=15.000 thành 1 bi=15.000
và thêm dòng
2 bi=30.000

sửa công thức

=SUMPRODUCT(--(ISERROR(SEARCH(DG!$C$6:$C$11;TTSC!D6))=FALSE);DG! $D$6:$D$11)

Như vậy là ổn, đối với 3 hay 4 bi thì cứ thêm dòng rồi nới công thức (Nó còn phù hợp trong trường hợp giá bi mua cả bộ thấp hơn mua lẻ vòng ví dụ 2 bi 29.000).

 

[ndu96081631]

Phuuuuuuuu......
Loay hoay từ chiều đến giờ với các công thức, cuối cùng cảm thấy chỉ có VBA là làm vụ này ngon lành nhất

Function Thanhtien(HangMuc As String, BangDG As Range, Optional Cot As Long = 2) As Double
  Dim Temp As Variant, Clls As Range, Item As Variant
  For Each Clls In BangDG.Resize(, 1)
    HangMuc = Replace(HangMuc, Clls, Clls(, Cot))
  Next Clls
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "[^0-9, ]"
    HangMuc = .Replace(HangMuc, "")
  End With
  Temp = Split(HangMuc, ",")
  For Each Item In Temp
    Thanhtien = Thanhtien + Evaluate(Replace(WorksheetFunction.Trim(Item), " ", "*"))
  Next
End Function

1> Cú pháp
=Thanhtien(Cell cần tính, Bảng đơn giá)
Ví dụ:
=Thanhtien($D6,DG!$C$5:$D$10)
2> Yêu cầu
- Gõ chữ chính xác (gõ sai ra kết quả sai ráng chịu)
- Hệ số nhân nếu có phải cách chữ ít nhất 1 khoảng trắng
- Các hạng mục sửa chửa phải cách nhau bằng dấu phẩy
Xem file
------------------
Nói thêm:
Cách dùng hàm này gần giống với VLOOKUP
Tức:
Tên hàm(Trị dò, Bảng dò, cột dò, kiểu dò)
Nhưng ở đây không có kiểu dò... và nếu ta không gõ Cột dò vào thì mặc định nó xem như Cột dò = 2

 

[ndu96081631]

Sau khi xem lại file, tôi thấy chỉ có cách bố trí lại dử liệu mới mong đơn giản hóa được vấn đề
Tôi đề xuất bố trí kiểu này:

Khi ấy, việc tính thành tiền chỉ làm SUMPRODUCT bình thường!
Bạn thấy sao?

 

[TRA KHUC RIVER]

Bố trí theo cách anh đúng là đơn giản hơn nhiều, thú thật em chỉ thích anh dùng công thức (để còn học hỏi) dùng VBA em không biết.

 

[ndu96081631]

Bố trí theo cách anh đúng là đơn giản hơn nhiều, thú thật em chỉ thích anh dùng công thức (để còn học hỏi) dùng VBA em không biết.

Tại sao phải ngán ngại VBA... vì thuật toán của nó cũng như công thức thôi mà... Chỉ là vấn đề làm sao cho "nó" hiểu được ý ta "nói" ---> Và Excel đã cho ta công cụ Record macro, ở những bước chập chững, nó chuyên dùng để "dịch" những gì ta muốn "nói" sang ngôn ngữ VBA rồi còn gì ---> Từ đây mà suy luận thêm
Bạn cũng nên bắt đầu học VBA đi, vì nó là công cụ tốt và mạnh, có thể giải quyết được những vấn đề khó khăn nhất mà đôi khi công thức không làm được!
Tôi không nói rằng chúng ta phải luôn luôn dùng VBA hay công thức ---> Cứ tùy việc, tùy hoàn cảnh mà chọn lấy 1 giải pháp đơn giản, thích hợp nhất ---> Miển sao nó giải quyết được vấn đề của ta 1 cách nhanh gọn

 

[tigertiger]

Tại sao phải ngán ngại VBA... vì thuật toán của nó cũng như công thức thôi mà... Chỉ là vấn đề làm sao cho "nó" hiểu được ý ta "nói" ---> Và Excel đã cho ta công cụ Record macro, ở những bước chập chững, nó chuyên dùng để "dịch" những gì ta muốn "nói" sang ngôn ngữ VBA rồi còn gì ---> Từ đây mà suy luận thêm
Bạn cũng nên bắt đầu học VBA đi, vì nó là công cụ tốt và mạnh, có thể giải quyết được những vấn đề khó khăn nhất mà đôi khi công thức không làm được!
Tôi không nói rằng chúng ta phải luôn luôn dùng VBA hay công thức ---> Cứ tùy việc, tùy hoàn cảnh mà chọn lấy 1 giải pháp đơn giản, thích hợp nhất ---> Miển sao nó giải quyết được vấn đề của ta 1 cách nhanh gọn

Hiii, ngán là đúng,

nếu đơn giản công thức thuần túy thích hơn,

Bài này dùng cớngthuc là đúng, chỉ góp ý chút, là dùng excel 2007thì còn tiện hơn nữa?

 

[TRA KHUC RIVER]

Sau khi xem lại file, tôi thấy chỉ có cách bố trí lại dử liệu mới mong đơn giản hóa được vấn đề
Tôi đề xuất bố trí kiểu này:

Khi ấy, việc tính thành tiền chỉ làm SUMPRODUCT bình thường!
Bạn thấy sao?

Anh ndu ơi em bố trí dữ liệu lại theo cách của anh nhưng sao nó ra #VALUE, em gởi file đính kèm anh xem lại em với

 

[tigertiger]

Sau khi xem lại file, tôi thấy chỉ có cách bố trí lại dử liệu mới mong đơn giản hóa được vấn đề
Tôi đề xuất bố trí kiểu này:



Khi ấy, việc tính thành tiền chỉ làm SUMPRODUCT bình thường!
Bạn thấy sao?

Đã Sumproduct thì cần gì phải công thức mảng nhỉ ({})

Ndu kiểm tra lại xem

 

[ndu96081631]

Anh ndu ơi em bố trí dữ liệu lại theo cách của anh nhưng sao nó ra #VALUE, em gởi file đính kèm anh xem lại em với

Phải Ctrl + Shift + Enter bạn à!

Đã Sumproduct thì cần gì phải công thức mảng nhỉ ({})
Ndu kiểm tra lại xem

Trong công thức có hàm TRANSPOSE, vẫn phải Ctrl + Shift + Enter như thường ---> Enter nó cóc chạy!
Ẹc.. Ẹc..
Muốn khỏi Ctrl + Shift + Enter chỉ có nước đặt công thức thành name, hoặc ít nhất cũng phải đặt đoạn TRANSPOSE thành 1 name

 

[ndu96081631]

Ah... thêm 1 chọn lựa nữa cho bạn (1 cách bố trí khác)
Nếu bạn bố trí như hình dưới đây thì công thức càng đơn giản và không cần sheet DG luôn

Tham khảo xem!

 

[tigertiger]

Ah... thêm 1 chọn lựa nữa cho bạn (1 cách bố trí khác)
Nếu bạn bố trí như hình dưới đây thì công thức càng đơn giản và không cần sheet DG luôn


Tham khảo xem!

hoặc bố trí số liệu nằm ngang như thế ở sheet đơn giá --> cũng không cần transpose làm gì cho rắc rối (lại phải Ctrl+Shift+ Enter khổ thân)


Còn vẫn bố trí theo thế và dùng transpose --> dùng công thức mảng thì dùng
{=SUM(TRANSPOSE(D7:H7),DG!$D$6:$D$10 )}

 

[ndu96081631]

hoặc bố trí số liệu nằm ngang như thế ở sheet đơn giá --> cũng không cần transpose làm gì cho rắc rối (lại phải Ctrl+Shift+ Enter khổ thân)

Vâng! Tôi cũng định đề nghị thế, nhưng đang nghĩ đến 1 trường hợp khác:
- Nếu có từ 50 loại danh mục hư hỏng trở lên thì hơi căng ---> Không biết bố trí kiểu nào là hợp lý ở cả 2 sheet (dử liệu nhiều mà xếp ngang thì khó xem lắm)
- Tigertiger thử gợi ý 1 cách bố trí nào khác trong trường hợp này với

 

[ndu96081631]

Tôi làm code ở bài #8 mà xem lại vẫn thấy chưa vừa ý...
Ví dụ:
Hỏng ruột, 3 bi, than xịn mà vô tình viết thành Hỏng ruột, 3bi, than xịn là nó tịt
Cải tiến lại... ai yêu thích VBA hãy tham khảo nha ---> Cái mới này "tuyệt chiêu" đây:

Function Thanhtien(HangMuc As String, BangDG As Range, Optional Cot As Long = 2) As Double
  Dim Clls As Range, Temp As String
  Temp = UCase$(HangMuc)
  For Each Clls In BangDG.Resize(, 1)
    Temp = Replace(Temp, UCase$(Clls), "*" & Clls(, Cot))
  Next Clls
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "[^0-9,*]"
    Temp = .Replace(Temp, "")
  End With
  Temp = Mid(Temp, 1 - (Left(Temp, 1) = "*"), Len(Temp))
  Thanhtien = Evaluate(Replace(Replace(Temp, ",*", "+"), ",", "+"))
End Function

- Gọn vì giảm bớt 1 vòng lập
- Tính toán chính xác, gõ kiểu gì cũng tính được (đương nhiên là đúng chính tả)
- Chỉ có yêu cầu duy nhất: Các hạng mục cách nhau bởi 1 dấu phẩy
(trò này gần giống đổi chử thành số)
Xem file

 

[TRA KHUC RIVER]

Em gởi file excel đính kèm, ở sheet TRA NCC em muốn tính lượng OXY, GAS, ARGON tổng số lượng nhập, và trả vỏ là bao nhiêu? ĐVT của gas có khác nhau khi em nhận thì nhận bình 1bình = 12kg, khi xuất em xuất lẻ có lúc xuất 3, 6, 9 kg vậy làm sao qui đổi nó sang bình hả anh?

 

[ndu96081631]

Em gởi file excel đính kèm, ở sheet TRA NCC em muốn tính lượng OXY, GAS, ARGON tổng số lượng nhập, và trả vỏ là bao nhiêu? ĐVT của gas có khác nhau khi em nhận thì nhận bình 1bình = 12kg, khi xuất em xuất lẻ có lúc xuất 3, 6, 9 kg vậy làm sao qui đổi nó sang bình hả anh?

Chưa hiểu lắm!
- Bên sheet nhập, rõ ràng tôi tính nhẩm ra 60 chai OXY... nhưng bên sheet TRA NCC bạn lại dùng SUMIF và tính ra được 10 chai?
- Trả vỏ cũng có vụ trả nửa chai à?

 

[TRA KHUC RIVER]

Em dùng sumif nó k ra kết quả đúng, trả nữa chai thì sai rồi, ý em muốn hỏi làm thế nào để qui đổi kg ra bình, 1bình =12kg