thiết lập công thức cho bảng tính mà không cần tham chiếu tới từng cell

[trangthanhlam]

Công ty của mình làm về linh kiện điện tử.mỗi ngày mình phải tính số lượng hàng còn tồn sau khi sử dụng. cộng với số lượng nhận trong ngày, trừ đi kế hoạch sản xuất nhân với tỉ lệ. Tỉ lệ phải cố định theo từng CC (CC1, CC2, CC3)khi kéo công thức từ ô này sang ô khác, không được làm mất dấu tuyệt đối,nếu không thì công thức của mình không đúng.Trường hợp chạy ít model thì mình có thể dùng chuột tham chiếu lên kế hoạchnhưng do sắp tới công ty sản xuất nhiều model, mình sợ dùng tay sê không đảm bảo tính chính xác cao.mà tỉ lệ của Model có thể thay đổi từ 1 qua 2, từ 2 về 1 và từ 1 về 0 …

hix. Quy tắc là làm sao phải mỗi ngày tính ra số hàng tồn sau khi đã sản xuất trong ngày.
huhu.
cứ phải tham chiếu như vậy tới từng Cell thì lâu lắm, có bác nào giúp em với, không thì em điên cái đầu sinh bệnh trầm cảm mà tử thôii... huhu

 

[dat_butmuc]

Công ty của mình làm về linh kiện điện tử.mỗi ngày mình phải tính số lượng hàng còn tồn sau khi sử dụng. cộng với số lượng nhận trong ngày, trừ đi kế hoạch sản xuất nhân với tỉ lệ. Tỉ lệ phải cố định theo từng CC (CC1, CC2, CC3)khi kéo công thức từ ô này sang ô khác, không được làm mất dấu tuyệt đối,nếu không thì công thức của mình không đúng.Trường hợp chạy ít model thì mình có thể dùng chuột tham chiếu lên kế hoạchnhưng do sắp tới công ty sản xuất nhiều model, mình sợ dùng tay sê không đảm bảo tính chính xác cao.mà tỉ lệ của Model có thể thay đổi từ 1 qua 2, từ 2 về 1 và từ 1 về 0 …

Cho một "chút" quy tắc chứ ? Nhìn vào khó hiểu quá !

hix. Quy tắc là làm sao phải mỗi ngày tính ra số hàng tồn sau khi đã sản xuất trong ngày.
huhu.
cứ phải tham chiếu như vậy tới từng Cell thì lâu lắm, có bác nào giúp em với, không thì em điên cái đầu sinh bệnh trầm cảm mà tử thôii... huhu

Chắc bạn không hiểu?
- Bảng tính & công việc của Bạn không có nhiều người hiểu nếu không làm cùng?
- Biểu thức bạn đang tính: =P32+Q30-($M$28*Q7)-($M$30*Q9)-($L$28*Q10)-($K$28*Q12)-($J$28*Q15)-($H$28*Q18)-($H$30*Q20)-($G$28*Q21)-($G$30*Q23)-($F$28*Q24) rất khó hiểu chắc phải có quy tắc chứ, đúng không? Thế thì mô tả quy tắc đó may ra mới làm được
=> Tôi nghĩ không đơn giản chút nào?

 

[trangthanhlam]

bây giờ mình ví dụ về model 31Pở dòng G28 là CC1, dòng G29 là CC2, và dòng G30 là CC3.model 31P ở đây chỉ chạy cho CC1 và tỉ lệ 4, CC2 không chạy và CC3 ti lệ 2.bây giờ mình muốn lập công thức tính cho model 31P: lấy kế hoạch tại ô Q21 nhân với tỉ lệ tại ô G28, và kế hoạch tại ô Q23 nhân với kế hoạch tại ô G30.nhưng nếu như mình không cố định tỉ lệ của dòng G28 và G30, thì công thức của mình không chính xác.vì tỉ lệ chạy cho Mã RGGR000ZTCANL của model 31P CC1 chỉ là 4. và CC3 chỉ là 2.Đó mới là điều khó khăn mình gặp phải khi tham chiếu công thức mà không dùng hàm gì khác.ngoài việc phải tham chiếu đến từng ô 1.rất mệt mà có nhiều khi Model này chạy cho nhiều mã thì không biết mình có tham chiếu lộn qua CC nào không. vì mình làm bằng thủ công, huhu, bạn có cách nào giúp mình không, mình không nghĩ ra cách nào khác được.

 

[Ba Tê]

hix. Quy tắc là làm sao phải mỗi ngày tính ra số hàng tồn sau khi đã sản xuất trong ngày.
huhu.
cứ phải tham chiếu như vậy tới từng Cell thì lâu lắm, có bác nào giúp em với, không thì em điên cái đầu sinh bệnh trầm cảm mà tử thôii... huhu

Hiểu lờ mờ mà chưa tìm ra cách hay, làm tạm một đống cột, dòng phụ rồi ẩn nó đi, thấy kết quả giống như trên nhưng không biết có xài được không?

 

[trangthanhlam]

huhu..
cái này là em làm thử 1 mã, chứ thật ra cái bảng tính của em đang dùng nó lên đến con số gần 30MB rùi.

em cũng không hiểu cách bác làm lắm, nhưng kết quả ra đúng thật.
vì bác làm theo hàm, còn em làm thủ công.
giờ bảng tính của em nặng vậy, sao em copy lên đây để bác giải giùm.
thật ra cái khó của em chỉ là khi kéo công thức thì tỉ lệ không được thay đổi.
huhu. Các bác ơi, giúp em đi.

Bác Ba Tê ơi!
Bác có thể gom tất cả vào trong 1 công thức là ô R32 không?
chứ bảng tính của em nhiều lắm.
làm ra nhiều cột phụ dòng phụ thì em không còn "đất đai" nữa rồi.
bác giúp em qua cơn hoạn nạn này với.

 

[ChanhTQ@]

Viết hẵn cho bạn hàm người dùng luôn đây, xin mời

Option Explicit
Function ThongKe(Model As Range, PhTr As Range, Plan As Range)
 Dim Clls As Range, sRng As Range, Rng As Range
 Dim Model_ As String
 
 For Each Clls In Plan
   If Clls.Value > 0 Then
      Set Rng = Model.Cells(Clls.Row - 3, 2)
      Model_ = Right(Rng.Offset(, -1).Value, 3)
      Set sRng = PhTr.Find(Model_, , xlFormulas, xlWhole)
      ThongKe = ThongKe + Clls.Value * sRng.Offset(Right(Rng.Value, 1)).Value
   End If
 Next Clls
End Function

Bạn kiểm tra lại số liệu xem mình chưa hiểu bạn chổ nào đó, mà số liệu còn bị sai lệch;
(Các số liệu từ q4:T26 phải được lấy tính toán mình đã bôi màu mận rồi đó.)
Chú ý: Để fục vụ cho hàm người dùng, mình đã gán 2 vùng tên trong trang tính)

 

[trangthanhlam]

hi Bác ChanhTQ@.
sao em tải file của bác về. Khi F2 để kiểm tra công thức thì thấy bị ra lỗi " #Name? "
Sai tên hàm, nhưng em cũng không hiểu vì sao.
thực tế trong file làm việc của em hiện giờ có tới 600 mã linh kiện như thế.
Nếu như em làm mỗi mã đều phải mất nhiều dòng như bác thì không ổn rồi.
Bác có cách nào khác không? kiểu này em phải chịu thua hay sao?
bao nhiêu ngày tháng nay em đã suy nghĩ không ra.
hu hu cứu em với. còn vài ngày nữa là qua tháng 6. Kế hoạch sãn xuất tăng lên, đòi hỏi chạy nhiều model, mã sẽ càng ngày càng nhiều hơn. Em chịu thôi, chắc không thể sống sót qua mùa đông lạnh giá để hưởng mùa xuân ấm cúng được. hic hic...

 

[yeudoi]

hi Bác ChanhTQ@.
sao em tải file của bác về. Khi F2 để kiểm tra công thức thì thấy bị ra lỗi " #Name? "
Sai tên hàm, nhưng em cũng không hiểu vì sao.
thực tế trong file làm việc của em hiện giờ có tới 600 mã linh kiện như thế.
Nếu như em làm mỗi mã đều phải mất nhiều dòng như bác thì không ổn rồi.
Bác có cách nào khác không? kiểu này em phải chịu thua hay sao?
bao nhiêu ngày tháng nay em đã suy nghĩ không ra.
hu hu cứu em với. còn vài ngày nữa là qua tháng 6. Kế hoạch sãn xuất tăng lên, đòi hỏi chạy nhiều model, mã sẽ càng ngày càng nhiều hơn. Em chịu thôi, chắc không thể sống sót qua mùa đông lạnh giá để hưởng mùa xuân ấm cúng được. hic hic...

Cái file đó là dùng macro tạo công thức người dùng, không phải công thức đã tạo của máy. Khi xử dụng bạn vào tools/macro/ security/low

 

[trangthanhlam]

Bác yêu đời ơi
sao em chạy nó không được
huhu

 

[ChanhTQ@]

(1) Sao em tải file của bác về. Khi F2 để kiểm tra công thức thì thấy bị ra lỗi " #Name? "
Sai tên hàm, nhưng em cũng không hiểu vì sao.
(2) Thực tế trong file làm việc của em hiện giờ có tới 600 mã linh kiện như thế.
Nếu như em làm mỗi mã đều phải mất nhiều dòng như bác thì không ổn rồi.

Bác có cách nào khác không? kiểu này em phải chịu thua hay sao?
bao nhiêu ngày tháng nay em đã suy nghĩ không ra.
Còn vài ngày nữa là qua tháng 6. Kế hoạch sãn xuất tăng lên, đòi hỏi chạy nhiều model, mã sẽ càng ngày càng nhiều hơn. Em chịu thôi

(1) Mình vừa thử mở file mình đã đưa lên diễn đàn & thấy vẫn bình thường mà.
Bạn xem thử trong cửa sổ VBE còn 1 hàm tự tạo hay 1 macro không? Hay bạn sợ virus nên quét đi rồi cũng nên.

(2) File mình làm là file của bạn; & để bạn dễ so sánh kết quả của hàm tự tạo & cách tính tay của bạn nên mình mới ghi hàm xuống dòng dưới vậy thôi.
Nếu bạn so giữa công thức tính của bạn & hàm tự tạo xem cái nào khả dĩ hơn.

Hay thế này đi, Bạn giả lập 600 mã hàng của bạn & gởi lên;
Tất nhiên viết công thức thì thua rồi fải vậy không? Nếu hàm tự tạo mà thua nữa thì chuyển sang macro để tính cho từng cột.
Mà ngộ thật nha, bạn chưa xài được hàm tự tạo (Vì đang lỗi mà), nhưng lại cho rằng hàm sẽ không xài được với dữ liệu 600 dòng! Hãy tìm cách cho hàm tự tạo chạy được cái đi. Lúc đó chúng ta bàn tiếp cũng chưa muộn!

Thân ái!

 

[trangthanhlam]

(1) Mình vừa thử mở file mình đã đưa lên diễn đàn & thấy vẫn bình thường mà.
Bạn xem thử trong cửa sổ VBE còn 1 hàm tự tạo hay 1 macro không? Hay bạn sợ virus nên quét đi rồi cũng nên.

(2) File mình làm là file của bạn; & để bạn dễ so sánh kết quả của hàm tự tạo & cách tính tay của bạn nên mình mới ghi hàm xuống dòng dưới vậy thôi.
Nếu bạn so giữa công thức tính của bạn & hàm tự tạo xem cái nào khả dĩ hơn.

Hay thế này đi, Bạn giả lập 600 mã hàng của bạn & gởi lên;
Tất nhiên viết công thức thì thua rồi fải vậy không? Nếu hàm tự tạo mà thua nữa thì chuyển sang macro để tính cho từng cột.
Mà ngộ thật nha, bạn chưa xài được hàm tự tạo (Vì đang lỗi mà), nhưng lại cho rằng hàm sẽ không xài được với dữ liệu 600 dòng! Hãy tìm cách cho hàm tự tạo chạy được cái đi. Lúc đó chúng ta bàn tiếp cũng chưa muộn!

Thân ái!

Bác ChanhTQ thân!
theo như lời bác, em đã tối giản nhất trong hơn 600 mã của em để đưa lên diễn đàn file này.

Bác coi rồi nghiênc cứu cho em với.

chạy Marco em sợ lỡ bị lỗi file thì sao?

Bác có cách nào nghiên cứu ra công thức không hả bác?
Em đang chờ tin tức của Bác từng ngày từng giờ.

huhu

 

[ChanhTQ@]

Option Explicit
Function SanLuong(TyLe As Range, PhTr As Range, PLan As Range, Model As Range)
 Dim Clls As Range, Rng As Range, sRng As Range
 Dim MoDal As String
 
 For Each Clls In PLan
   If Clls.Value > 0 Then
      MoDal = Right(Cells(Clls.Row, Model.Column).Value, 3)
      For Each Rng In TyLe
         If Rng.Value = MoDal Then
            Set sRng = Rng:         Exit For
         End If
      Next Rng
      SanLuong = SanLuong + Clls.Value * _
         PhTr.Cells(Right(Cells(Clls.Row, Model.Column + 1).Value, 1), sRng.Column - 1).Value
   End If
 Next Clls
End Function

Trong file, tại [Al95] bạn đang có công thức =AH95+AI91+AI94-AI92-AI93-($V$89*AI27)-($P$89*AI42)

Thực tế số liệu tại đây chỉ có thể là AG95-($P$89*AI42) Hay AH95-($P$89*AI42)

Bạn kkhẳng định xem sao nha: Rằng như thế nào là đúng.

Đến lúc đó chúng ta tiếp ở bên dưới.

Bổ sung sau khi gộp bài (Cho thêm rõ nghĩa)

Đây là VER. 2.1 của hàm tự tạo:

Option Explicit
Function SLuong(PhTr As Range, Plan As Range, Optional Model As Range)
 Dim TyLe As Range, Clls As Range, Rng As Range, sRng As Range
 Dim MoDal As String:                     Dim Cot As Byte
 If Model Is Nothing Then Set Model = Range("Model")
 Cot = Model.Column
 Set TyLe = Range("TyLe")
 
 For Each Clls In Plan
   If Clls.Value > 0 Then
      MoDal = Right(Cells(Clls.Row, Cot).Value, 3)
      For Each Rng In TyLe
         If Rng.Value = MoDal Then
            Set sRng = Rng:         Exit For
         End If
      Next Rng
      SLuong = SLuong + Clls.Value * _
         PhTr.Cells(Right(Cells(Clls.Row, Cot + 1).Value, 1), sRng.Column - 1).Value
   End If
 Next Clls
End Function

 

[trangthanhlam]

Option Explicit
Function SLuong(PhTr As Range, Plan As Range, Optional Model As Range)
Dim TyLe As Range, Clls As Range, Rng As Range, sRng As Range
Dim MoDal As String: Dim Cot As Byte
If Model Is Nothing Then Set Model = Range("Model")
Cot = Model.Column
Set TyLe = Range("TyLe")

For Each Clls In Plan
If Clls.Value > 0 Then
MoDal = Right(Cells(Clls.Row, Cot).Value, 3)
For Each Rng In TyLe
If Rng.Value = MoDal Then
Set sRng = Rng: Exit For
End If
Next Rng
SLuong = SLuong + Clls.Value * _
PhTr.Cells(Right(Cells(Clls.Row, Cot + 1).Value, 1), sRng.Column - 1).Value
End If
Next Clls
End Function

Hi ChanhTQ!
Have A good day,

Bạn vào lại File của bạn gửi giùm mình, có lẽ bạn không hiểu ý mình, vì nếu theo như bên công thức tại ô AH124 --> AM124 thì khi kéo công thức đi thì nó bị chạy phần tỉ lệ của Model.
linh kiện ở đây quan trọng nhất là phần tỉ lệ, vì nếu tỉ lệ 1 thì không sao, nhưng tỉ lệ 2 hay 4 hay 20 thì hàng hóa sẽ cần rất nhiều, hàng ngày mình phải gửi bảng kế hoạch qua nước ngoài.
nên không thể sai sót được, có thì cũng chỉ trong sự sai sót ít mà thôi.
số Stock của ngày hôm trước là Stock đầu của ngày hôm sau, sau khi đã trừ đi phần kế hoạch sản xuất nhân tỉ lệ trong ngày, và số lượng hàng nhận trong ngày. Trong đó tỉ lệ chạy cho Model nào phải được cố định cho đến cuối dòng cuối cùng của công thức khi tham chiếu.
mình muốn sử dụng hàm excel thôi, vì Marco máy mình chạy không được, hay báo lỗi mà mình cũng không hiểu nó là lỗi gì cả.
Bạn có thể nghĩ ra cách làm bằng hàm Excel không? Giúp mình với, hu hu

 

[ChanhTQ@]

(1) Bạn có lẽ bạn không hiểu ý mình, vì nếu theo như bên công thức tại ô AH124 --> AM124 thì khi kéo công thức đi thì nó bị chạy phần tỉ lệ của Model. linh kiện ở đây quan trọng nhất là phần tỉ lệ, vì nếu tỉ lệ 1 thì không sao, nhưng tỉ lệ 2 hay 4 hay 20 thì hàng hóa sẽ cần rất nhiều, hàng ngày mình phải gửi bảng kế hoạch qua nước ngoài. nên không thể sai sót được, có thì cũng chỉ trong sự sai sót ít mà thôi.
số Stock của ngày hôm trước là Stock đầu của ngày hôm sau, sau khi đã trừ đi phần kế hoạch sản xuất nhân tỉ lệ trong ngày, và số lượng hàng nhận trong ngày. Trong đó tỉ lệ chạy cho Model nào phải được cố định cho đến cuối dòng cuối cùng của công thức khi tham chiếu.
(2) mình muốn sử dụng hàm excel thôi, (3) vì Marco máy mình chạy không được, hay báo lỗi mà mình cũng không hiểu nó là lỗi gì cả.
Bạn có thể nghĩ ra cách làm bằng hàm Excel không? Giúp mình với, hu hu

(1) Chuyện này chẳng qua là chưa hiểu hết ý của nhau thôi; Sáng nay mình đã có suy nghĩ giống bạn rồi.

(2) Mình chỉ hay viết hàm tự tạo thôi; Công thức Ẽxcel có khi mình còn thua cả bạn nữa ấy chứ lị! Hãy kêu gọi những nhà siêu công thức 1 cách trực tiếp xem sao (Bằng cách nhắn tin trên diễn đàn cho họ í, như NDU, CaDaFi, BNTT, Dom. . ., & rất nhiều người khác nữa!)

(3) Bạn fải tìm nguyên nhân đó là lỗi gì: Bằng 1 cách là có thể đưa câu lỗi ấy lên diễn đàn để hỏi.
Fân tích cho bạn thấy: Công thức không thể hay & tốt hơn Macro trong mọi trường hợp; Ví dụ đọc số thành chữ (tiếng Việt) là 1 điển hình.
Chẳng lẽ, những người cần xài hàm tự tạo đọc số thành chữ trong Excel đều bó tay như bạn sao.

Chuyện này mình cũng nói trước: Rằng có ai đó làm cho bạn công thức thì với dữ liệu 600 dòng & hơn 40 cột đó máy cũng lặc lè mà thôi.

Chờ í bạn để tiếp, chứ tiếp mà bạn không thèm xài thì fí, nhỉ?!

Thân ái!

 

[trangthanhlam]

hi! Bác ChanhTQ vui tính nhỉ?
thì quả thật là chạy Marco thì nhanh.
Nhưng sao mình không chạy được, không biết là nó có bị làm sao không, hay là mình không biết cách chạy

huhu, bạn chỉ cho mình cách đi, mình cũng làm theo như mọi người chỉ mà.

hổng hiểu sao nó hổng chạy mà cứ báo lỗi.

Trong file Excel của bạn gửi thì mình thấy Marco chạy công thức Sanluong(TyLe,$C91:$AE93,AI5:AI86,$AF5)


--> Có cách nào làm cho nó chạy bằng Excel không? thực ra mình cũng muốn chạy Marco như bạn nói, nhưng không được, huhu
chỉ cho mình với, sao nó cứ báo lỗi.

 

[ChanhTQ@]

Bạn hãy nhờ 1 ai đã & đang tham gia topic này chạy thử macro coi ra răng!

Ver 2.1 đó nha! Cái cũ mình sẽ thu hồi để đỡ tốn tài nguyên. (Ngay sau khi đăng bài này )

Đừng có mà mơ rằng mình sẽ làm công thức excel cho bạn!

Mà sao bạn không chụp lại cái màn hình báo lỗi đưa lên đây để mọi người cùng chiêm ngưỡng nhỉ?
Làm gì mà cứ dấu làm của riêng mãi vậy?!

 

[trangthanhlam]

Trời.
Bác nói cứ n hư em giấu làm của riêng vậy.
thực ra em không học về Marco nên em không biết mà.
chỉ đọc trên diễn đàn là làm Alt+F11, copy code dán vào sau đó Alt+Q, tiếp tục Alt+F8 để chạy.

híc híc, thế thôi mà. Mà bác không làm cho em bằng công thức Excel hả? Sao vậy? thế chả phải Bác đang giấu cho riêng mình à!
Thôi mà, giúp em đi
Em đang làm thêm 5 Model mới.
Kiểu này chắc em chết đứng quá
Mà em chạy Marco như vậy có đúng không hả Bác, sai chỗ nào, chỉnh sửa chỗ nào, Bác chỉ em cái coi.
Thanks

Sao Bác nói nặng lời với em thế?
Em có biết gì đâu, Em đã học Marco bao giờ đâu.
EM đọc trên diễn đàn thấy có bác chỉ là Alt+F11 copy Code rồi Alt+Q, sau đó Alt+F8.

EM làm gì mà giấu làm của riêng đâu. ( BIết gì đâu mà giấu)
Híc híc, bác coi giúp em với, Mà sao Bác biết mà không chỉ cho em bằng Công thức Excel mà lại bảo em nằm mơ.

Huhu giờ em phải làm sao?

Bác tài giỏi thế, mà không giúp em được hay sao?
Em đang phải làm thêm model mới đây, 5 model nữa. Add công thức chắc lại mờ mắt, chuẩn bị đeo kiếng nặng độ hơn quá.

Huhu
mà Bác rành Marco thì chỉ cho em coi, em làm vậy đúng không?
chết thôi, giúp em với

 

[ChanhTQ@]

(1) Mà bác không làm cho em bằng công thức Excel hả? Sao vậy? thế chả phải Bác đang giấu cho riêng mình à!
Thôi mà, giúp em đi
Em đang làm thêm 5 Model mới.
Kiểu này chắc em chết đứng quá
(2) Bác nói cứ như em giấu làm của riêng vậy.
thực ra em không học về Marco nên em không biết mà.
chỉ đọc trên diễn đàn là làm Alt+F11, copy code dán vào sau đó Alt+Q, tiếp tục Alt+F8 để chạy. híc híc, thế thôi mà. Mà em chạy Marco như vậy có đúng không hả Bác, sai chỗ nào, chỉnh sửa chỗ nào, Bác chỉ em cái coi.
Thanks

Trả lời (1): Xem lại bài bên trên.
Trả lời (2) :
Giờ thế này đi: Bạn vô menu Tool & để chuột vô hàng có chữ Macro ->;
Khi đó ngay bên fải sẽ hiện 1 (CS) cửa sổ con;
Trong CS này bạn chọn tiếp hàng có chữ Security. . . Để hiện CS này (Thường có 2 ngăn);
Ta vô ngăn Security Level. Sẽ thấy trong ngăn này 4 nút (mà người ta hay gọi là nút radio)
Bạn hãy cho biết nút nào trong chúng đang có dấu (.)
Để hàm người dùng hay macro chạy suông sẻ, ta fải chuyển chọn 1 trong 2 hàng cuối.

Hỏi thêm: tại [AH97] bạn có thấy trị số không vậy?

 

[trangthanhlam]

có mà.
em vẫn thấy giá trị, có nghĩa là Marco đang chạy
nhưng sao em copy code đó, qua bên file chưa chạy Marco thì không chạy được
hix... buồn như con chuồn chuồn...

Trả lời bác:

Hiện tại bên máy em đang ở chế độ Low( Not Recommended)
có phải chuyển nữa không bác.

 

[ChanhTQ@]

có mà.
em vẫn thấy giá trị, có nghĩa là Marco đang chạy
nhưng sao em copy code đó, qua bên file chưa chạy Marco thì không chạy được
hix... buồn như con chuồn chuồn...

Hàm cần 2 name có tên là "TyLe" & "Model"; Để biết 2 tên này là thế nào, bạn fải vố menu Insert -> Name ->