Chuyển công thức excel thành code VBA (1 người xem)

[phankhang]

Chào các anh em trong GPE . Tôi là người mới tầm sư học đạo excel và chưa biết gì về VBA . Diễn đàn này đã giúp tôi rất nhiều trong quá trình tiếp cận . Hiện nay tôi có 1 vấn đề nan giải , rất mong các anh chị giúp cho.

Hiện nay tôi đang lập bảng lương bằng excel cho công nhân nhưng tương đối phức tạp . Dòng công thức như sau
=IF(OR(CCONG!F6="O",CCONG!F6="CO",CCONG!F6="P",CCONG!F6="ts",CCONG!F6="NO",CCONG!F6="U"),0,
IF(OR(LEFT($B6,2)="pl",LEFT($B6,2)="gv",LEFT($B6,2)="NL"),VLOOKUP("K1",XNL,DAY(F$5)+2,0)*VLOOKUP($B6,DGIA,3,0)*CCONG!F6/VLOOKUP($B6,TG_GL,DAY($F$5)+2,0),
IF(OR(LEFT($B6,2)="CB",LEFT($B6,2)="GT",LEFT($B6,2)="lv"),VLOOKUP($B6,SO_KG,DAY($F$5)+2,0)*VLOOKUP($B6,DGIA,3,0)*CCONG!F6/VLOOKUP($B6,TG_GL,DAY($F$5)+2,0),VLOOKUP("KDH",NTP,DAY(F$5)+2,0)*VLOOKUP($B6,DGIA,3,0)*CCONG!F6/VLOOKUP($B6,TG_GL,DAY($F$5)+2,0))))*IF($B6<>"BBDH",1,VLOOKUP($C6,GT_BB,4,0))

Với công thức này khi copy ra hàng ngàn cell thì file excel rất nặng và khi click vào cell thì thanh formula kéo dài xuống tận 1/2 màn hình . Có cách nào chuyển công thức trên thành VBA được không

Tôi rất lơ mơ nên nếu có gì không đúng , mong các bạn chỉ giáo

 

[anhthuan]

Đọc công thức của bạn mình hoa hết cả mắt, chẳng hiểu gì

Nếu bạn muốn chuyển đổi sang VBA thì phải nêu rõ điều kiện và yêu cầu của mình ra. Công thức của bạn tính toán cái gì, thông tin cần lấy là gì?

Để chuyển công thức từ Excel sang VBA có hai cách:
- activesheet.range("a1").formular1c1="=sum(.....)": đặt công thức sum tại ô A1
- application.worksheetfuntion.sum(.....): tạo hàm tính tổng trong VBA

 

[phankhang]

Rất cám ơn bạn đã trả lời . Mình giải thích thêm như sau :
Mục đích của công thức trên là tính lương cho từng người theo từng ngày ( theo từng bộ phận khác nhau ), mỗi bộ phận có định mức lương khác nhau . Giải thích công thức trên như sau :

1. Nếu bảng chấm công là O , co , ... thì lương ngày đó là 0 đồng
2.Nếu bộ phận làm việc là "pl", "gv",... thì tính lương theo nguyên liệu xuất vào sản xuất , tra theo bảng nguyên liệu xuất * tra theo đơn giá nguyên liệu * số giờ làm việc của cá nhân / tổng số giờ làm việc của cả bộ phận
3. Nếu bộ phận làm việc là "CB", "BB",... thì tính lương theo thành phẩm sản xuất ra , tra theo bảng thành phẩm nhập kho* tra theo đơn giá thành phẩm* số giờ làm việc của cá nhân / tổng số giờ làm việc của cả bộ phận

4. Nếu bộ phận làm việc là "BBDH" thì nhân với hệ số ( tra ở bảng riêng ) còn nếu ở bộ phận khác thì hệ số 1

Thật ra đây chỉ là 1 thông số thôi chứ thực tế còn nhiều bảng khác nữa do mỗi ngày làm nhiều loại nguyên liệu khác nhau với đơn giá khác nhau , nhiều loại thành phẩm khác nhau với đơn giá khác nhau . Công nhân có thể trong 1 ngày làm ở 2,3 bộ phận khác nhau với những đơn giá hoàn toàn khác nhau ( do sản xuất rau quả nên không chủ động được nguyên liệu . Khi nguyên liệu này hết thì phải chuyển sang làm nguyên liệu khác mà mỗi loại nguyên liệu thì số người ở mỗi tổ thay đổi do khối lượng công việc khác nhau )

Không biết giải thích như vậy đã rõ chưa . Cám ơn bạn nhiều

Mình gửi kèm file rút gọn lên . Rất mong chỉ giáo

 

[tigertiger]

Đúng là hoa cả mắt,

Để chuyển công thức từ Excel sang VBA có hai cách:
- activesheet.range("a1").formular1c1="=sum(.....)": đặt công thức sum tại ô A1
- application.worksheetfuntion.sum(.....): tạo hàm tính tổng trong VBA

- cách này thì đặt nguyên c/thức giống như đặt tay ->cũng hay

- nhưng cách hay là lập hàm VBA (UDF)để thay công thức lun

- vấn đề? bạn nên upload file lên có vd và giải thích công thức thì mọi ng mới giúp được

 

[tigertiger]

vì vội, tigertiger mới chỉ

Dùng cách đặt tên động cho bạn -> c thức ngắn hơn và dễ kiểm soát hơn, và phát hiện thành phần này -> bạn nên đặt thừa số chung ra ngoài

[FONT=&quot]*VLOOKUP($B6,DGIA,3,0)*CCONG!F6/VLOOKUP($B6,TG_GL,DAY($F$5)+2,0)[/FONT]

nhưng chắc còn nh tồn tại

tigertiger quay lại chủ đề sớm

 

[SA_DQ]

Bạn tham khảo thêm hàm tự tạo sau:

Option Explicit

[B]Function TienCong(CgOm As Range, MaTo As Range, Ngay As Range)[/B]
'[COLOR="Blue"]=IF(OR(CCONG!F6="O";CCONG!F6="CO";CCONG!F6="P";CCONG!F6="ts";CCONG!F6="NO";CCONG!F6="U");0; _
IF(OR(LEFT($B6;2)="pl";LEFT($B6;2)="gv";LEFT($B6;2)="NL");VLOOKUP("K1";XNL;DAY(F$5)+2;0) _
    *VLOOKUP($B6;DGIA;3;0)*CCONG!F6/VLOOKUP($B6;TG_GL;DAY($F$5)+2;0); _
IF(OR(LEFT($B6;2)="CB";LEFT($B6;2)="GT";LEFT($B6;2)="lv");VLOOKUP($B6;SO_KG;DAY($F$5)+2;0) _
    *VLOOKUP($B6;DGIA;3;0)*CCONG!F6/VLOOKUP($B6;TG_GL;DAY($F$5)+2;0); _
    VLOOKUP("KDH";NTP;DAY(F$5)+2;0)*VLOOKUP($B6;DGIA;3;0)*CCONG!F6/VLOOKUP($B6;TG_GL;DAY($F$5)+2;0)))) _
    *IF($B6<>"BBDH";1; VLOOKUP($C6;GT_BB;4;0)) [/COLOR] 
 Dim lMTo As String
 Dim DonGia As Double, TGGL As Double, Dat As Long
 
 CgOm = UCase$(CgOm):           lMTo = UCase$(Left(MaTo, 2))
 Dat = Day(Ngay) + 2
 
 DonGia = Application.VLookup(MaTo, DGIA, 3, 0)
 TGGL = Application.VLookup(MaTo, TG_GL, Dat, 0)
 
 If CgOm = "O" Or CgOm = "CO" Or CgOm = "P" Or CgOm = "TS" Or CgOm = "NO" Or CgOm = "U" Then
    TienCong = 0: Exit Function
 ElseIf lMTo = "PL" Or lMTo = "GV" Or lMTo = "NL" Then
    TienCong = Application.VLookup("K1", XNL, Dat, 0) * DonGia * CgOm / TGGL [COLOR="red"]' <= K1?[/COLOR]
 ElseIf lMTo = "CB" Or lMTo = "GT" Or lMTo = "LV" Then
    TienCong = Application.VLookup(MaTo, SO_KG, Dat, 0) * DonGia * CgOm / TGGL
 Else
    TienCong = Application.VLookup("KDH", NTP, Dat, 0) * DonGia * CgOm / TGGL
 End If
 If MaTo = "BBDH" Then TienCong = TienCong * Application.VLookup(C6, GT_BB, 4, 0) [COLOR="Red"]'<= C6?![/COLOR] 
[B]End Function [/B]

Hàm này chưa hoàn toàn kết thúc; chỉ để tham khảo thôi!

 

[yeudoi]

Có có cách khác là dùng VBA. Khi chèn công thức xong là chuyển qua giá trị luôn việc này mình nghĩ cũng đơn giản bởi lương thì không có nhiều dòng.

 

[rockfusion]

Tôi xin có một chút ý kiến như vâỳ
để nắm bát nhanh cho những ngươì mơí tập viết VB trong excell, nêú có vấn đè gì chưa hiêủ bạn cứ record những thao tác cuả bạn trên bảng tính kể cả lập công thức, sau đó stop record rôì vaò code để xem và rút kinh nghiệm, hôì đó lúc chưa rành VB tôi cũng hay làm cách naỳ, khá hữu hiêụ đâý các bạn ạ.

 

[Thien]

Tôi xin có một chút ý kiến như vâỳ
để nắm bát nhanh cho những ngươì mơí tập viết VB trong excell, nêú có vấn đè gì chưa hiêủ bạn cứ record những thao tác cuả bạn trên bảng tính kể cả lập công thức, sau đó stop record rôì vaò code để xem và rút kinh nghiệm, hôì đó lúc chưa rành VB tôi cũng hay làm cách naỳ, khá hữu hiêụ đâý các bạn ạ.

Cách này chỉ ứng dụng trong những việc nhỏ thui. còn những việc lớn như trích xuất dữ liệu, lấy dữ liệu từ nhiều sheet thành 1 sheet ... thì không biết VBA cũng thua thui.
Mặc khác những đoạn code do ghi lại từ record sẽ không chạy trơn tru & nhanh bằng thêm mắm muối gạo tiền vào, đặc biệt các đoạn bẫy lỗi trong code.....


Vài dòng gửi bạn.

Thân chào.

 

[ThuNghi]

Chào các anh em trong GPE . Tôi là người mới tầm sư học đạo excel và chưa biết gì về VBA . Diễn đàn này đã giúp tôi rất nhiều trong quá trình tiếp cận . Hiện nay tôi có 1 vấn đề nan giải , rất mong các anh chị giúp cho.

Hiện nay tôi đang lập bảng lương bằng excel cho công nhân nhưng tương đối phức tạp . Dòng công thức như sau
=IF(OR(CCONG!F6="O",CCONG!F6="CO",CCONG!F6="P",CCONG!F6="ts",CCONG!F6="NO",CCONG!F6="U"),0,
IF(OR(LEFT($B6,2)="pl",LEFT($B6,2)="gv",LEFT($B6,2)="NL"),VLOOKUP("K1",XNL,DAY(F$5)+2,0)*VLOOKUP($B6,DGIA,3,0)*CCONG!F6/VLOOKUP($B6,TG_GL,DAY($F$5)+2,0),
IF(OR(LEFT($B6,2)="CB",LEFT($B6,2)="GT",LEFT($B6,2)="lv"),VLOOKUP($B6,SO_KG,DAY($F$5)+2,0)*VLOOKUP($B6,DGIA,3,0)*CCONG!F6/VLOOKUP($B6,TG_GL,DAY($F$5)+2,0),VLOOKUP("KDH",NTP,DAY(F$5)+2,0)*VLOOKUP($B6,DGIA,3,0)*CCONG!F6/VLOOKUP($B6,TG_GL,DAY($F$5)+2,0))))*IF($B6<>"BBDH",1,VLOOKUP($C6,GT_BB,4,0))

Tôi vận dụng code của Bác SA hoàn chỉnh thêm cho bạn nhưng hưa test và không bẫy lỗi. Bạn cứ dùng sai đến đâu sửa đến đó.

Option Explicit
Dim lMTo As String
Dim DonGia As Double, TGGL As Double, iDate As Long
Dim DGIA As Range, TG_GL As Range, XNL As Range, SO_KG As Range, NTP As Range, GT_BB As Range
Public Function TienCong(MaNV As Range, MaTo As Range, Ngay As Date, CgOm As Range)
Set DGIA = Range("DGIA")
Set TG_GL = Range("TG_GL")
Set XNL = Range("XNL")
Set SO_KG = Range("SO_KG")
Set NTP = Range("NTP")
Set GT_BB = Range("GT_BB")
' CgOm = UCase$(CgOm)
 lMTo = UCase$(Left(MaTo, 2))
 iDate = Day(Ngay) + 2

DonGia = Application.VLookup(MaTo, DGIA, 3, 0)
TGGL = Application.VLookup(MaTo, TG_GL, iDate, 0)
 
If Not IsNumeric(CgOm) Then
    TienCong = 0: Exit Function
ElseIf lMTo = "PL" Or lMTo = "GV" Or lMTo = "NL" Then
    TienCong = Application.VLookup("K1", XNL, iDate, 0) * DonGia * CgOm / TGGL ' <= K1?
ElseIf lMTo = "CB" Or lMTo = "GT" Or lMTo = "LV" Then
    TienCong = Application.VLookup(MaTo, SO_KG, iDate, 0) * DonGia * CgOm / TGGL
Else
    TienCong = Application.VLookup("KDH", NTP, iDate, 0) * DonGia * CgOm / TGGL
End If
If MaTo = "BBDH" Then TienCong = TienCong * Application.VLookup(MaNV, GT_BB, 4, 0) '<= C6?!
Set DGIA = Nothing
Set TG_GL = Nothing
Set XNL = Nothing
Set SO_KG = Nothing
Set NTP = Nothing
Set GT_BB = Nothing

End Function

Tại sh DHBC, từ F6 bạn nhập ct: Tiencong(MaNV,MaTo,Ngay,CgOm) cụ thể là

F6=tiencong($C6,$B6,F$5,CCONG!F6)

Bạn tổ chức nhiều bảng quá, lương gì phức tạp thế.

 

[phankhang]

Xin cám ơn tất cả các bạn . Số là tôi không phải là dân chuyên về tin học mà rặt ròng là dân hóa thực phẩm . Tuy nhiên do hệ thống bảng lương công nhân của mình quá phức tạp . Đại loại như sau :

- Mỗi ngày sản xuất nhiều sản phẩm khác nhau với đơn giá khác nhau
- Mỗi loại nguyên liệu cũng có đơn giá khác nhau
- Mỗi công nhân một ngày có thể tham gia nhiều loại sản phẩm , nhiều loại nguyên liệu
- Đơn giá từng công đoạn cũng khác nhau , có công đoạn ăn lương riêng từng nhóm người , có công đoạn ăn lương cá nhân , có loại lương theo thời gian . Mỗi người 1 ngày có khả năng làm 1 vài giờ ở chế độ lương này và 1 vài giờ khác ở chế độ lương khác
- Nhóm ăn lương tập thể cũng không có số người cố định mà có thể thay đổi tùy theo mặt hàng nào , nguyên liệu nào ,...
- Và còn một số rắc rối khác nữa

Tôi thường để ý thấy bộ phận làm lương công nhân thường làm bằng excel và gần như không ứng dụng được gì các công thức cả mà mỗi ô lương phải cộng bằng tay ( đại loại sử dụng excel giống như thay máy caculator). Tôi có nhã ý muốn giúp các em làm lương đỡ vất vả hơn và quan trọng là không được làm sai , số liệu phải báo cáo hàng ngày để lãnh đạo có thể ra quyết định điều chỉnh kịp thời . Nhưng rõ ràng là khi bắt tay vô mới thấy cái khó vì với số vốn ít ỏi tự học của mình việc hoàn thành đồ án này còn quá xa vời .

Thú thật tôi cũng đã nhờ một vài công ty phần mềm đến khảo sát nhưng hình như họ cũng "ngán " hay sao đó mà lặn mất tiêu . Thôi thì đành vừa làm vừa học vậy . Biết đến đâu làm đến đó . Các y kiến của các bạn đều giúp tôi rất nhiều trong việc dần dần tiếp cận

Nhân đây nếu các bạn nào thấy có thể chấp nhận thử thách với những yêu cầu trên , mình sẵn sàng hợp tác kể cả với tư cách cá nhân ( không phải pháp nhân cũng được) để hoàn thành đồ án trên . Chi phí thì thỏa thuận . Vui lòng liên hệ theo private messages . Cám ơn tất cả các bạn

 

[SA_DQ]

Nhân đây nếu các bạn nào thấy có thể chấp nhận thử thách với những yêu cầu trên , mình sẵn sàng hợp tác kể cả với tư cách cá nhân ( không phải pháp nhân cũng được) để hoàn thành đồ án trên . Chi phí thì thỏa thuận . Vui lòng liên hệ theo private messages . Cám ơn tất cả các bạn

LVDuyệt (Và Bình) xem xét & đứng ra nhận lời cũng như tổ chức thực hiện cho GPE;
Chắc sẽ có những người góp phần . . . cho GPE! Mình tin như vậy!

 

[phankhang]

Nếu các bạn nhận làm mình sẽ gửi yêu cầu chi tiết

 

[phankhang]

To Thunghi :

Cám ơn thunghi , cho mình hỏi thêm : Mình thấy bạn sử dụng công thức tại ô F6 có đoạn
=CCONG!F6>100

để thay thế cho cái đoạn
=OR(CCONG!F6="O",CCONG!F6="CO",CCONG!F6="P",CCONG!F6="ts",CCONG!F6="NO",CCONG!F6="U")

của mình kết quả rất đúng mà hay . Bạn có thể vui lòng giải thích hộ xem tại sao bạn lại sử dụng như vậy không . Mình chưa hiểu lắm , đúng là các bạn đã dạy cho mình rất nhiều

 

[ThuNghi]

To Thunghi :
Cám ơn thunghi , cho mình hỏi thêm : Mình thấy bạn sử dụng công thức tại ô F6 có đoạn
=CCONG!F6>100
để thay thế cho cái đoạn
=OR(CCONG!F6="O",CCONG!F6="CO",CCONG!F6="P",CCONG!F6="ts",CCONG!F6="NO",CCONG!F6="U")

Nếu F6 là text, chữ luôn luôn lớn hơn số, có thể thay =CCONG!F6>1000000000
Bạn thử cho đại F6="c", dùng thử =F6>1000000000 nó sẽ báo là true.
ie nếu F6 là "O", "CO"... thì luôn > số nào đó.
Nôm na dễ hiểu.

 

[kelvin]

LVDuyệt (Và Bình) xem xét & đứng ra nhận lời cũng như tổ chức thực hiện cho GPE;
Chắc sẽ có những người góp phần . . . cho GPE! Mình tin như vậy!

Mong Duyệt và Bình chủ trì nhận thực hiện, mình tin sẽ có nhiều anh em khác tham gia, như hôm trước có trao đổi với Bình đó

 

[levanduyet]

Em xin cám ơn anh SA_DQ và anh Kenvil.

To: Phankhang,
Bạn có thể liên hệ với tôi theo e-mail: levanduyet@yahoo.com
Nhưng cho tôi hỏi, nếu trong trường hợp các ý tưởng chúng tôi thực hiện cho bạn nếu đưa lên GPE, thì bạn có đồng ý không?

Lê Văn Duyệt

 

[le tin]

Đường dẫn trong VBA

Chào các bạn
Nhờ các bạn giúp mình đường dẫn ... để ghi code trong trường hợp này

Application.WorksheetFunction.VLookup([c1], … , 2, 0)
CÔNG THỨC:
=VLOOKUP(C1;'D:\ChuongTrinh\[DL2009.xls]Mua'!$A$1:$B$8;2;0)

Cảm ơn nhiều

 

[ThuNghi]

Chào các bạn
Nhờ các bạn giúp mình đường dẫn ... để ghi code trong trường hợp này

Application.WorksheetFunction.VLookup([c1], … , 2, 0)
CÔNG THỨC:
=VLOOKUP(C1;'D:\ChuongTrinh\[DL2009.xls]Mua'!$A$1:$B$8;2;0)

Cảm ơn nhiều

Anh dùng thử code sau:

Sub DoTim()
Dim WF As WorksheetFunction
Set WF = WorksheetFunction
Dim myRng As Range
Set myRng = Sheet1.Range(Cells(1, 1), Cells(4, 2))
If WF.CountIf(myRng.Resize(, 1), Cells(1, 4)) > 0 Then
  Cells(1, 5) = WF.VLookup(Cells(1, 4), myRng, 2, 0)
  MsgBox Cells(1, 5)
Else
  MsgBox "Khong tim thay"
End If
End Sub

Trong đó MyRng là 'D:\ChuongTrinh\[DL2009.xls]Mua'!$A$1:$B$8
Có 2 cách, dùng .Formular cũng được.

 

[le tin]

Cảm ơn ThuNghi , ý trên là trong công thức vùng chọn là 'D:\ChuongTrinh\[DL2009.xls]Mua'!$A$1:$B$8 thì trong VBA phải ghi vào chỗ dấu (... ) như thế nào .
Trong đó MyRng là 'D:\ChuongTrinh\[DL2009.xls]Mua'!$A$1:$B$8 cụ thể ghi thế nào vậy bạn