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]

em biết tyle va model là nhũng cell nào mà.
vậy tiếp theo là gì nữa vậy Bác?

bác ơi!

Bác đâu rồi

tiếp theo em phải làm gì đây?
cái File của Bác chạy là OK rồi,
nhưng sao em đem nó qua file gốc của em thì chạy vẫn chưa ổn là sao.

 

[SA_DQ]

Nếu 100 dòng của trang tính của bạn giống với 100 của trang tính mà bạn đưa lên thì tại ô [AH97] bạn thử nhập công thức =AG95+AH91+AH94-AH92-AH93-Sluong($B89:$AD91,AH5:AH86,$AE5)
& nhấn {ENTER} xem nó có cho kết quả như dòng trên nó không?

(Nếu 2 trang tính không đồng nhất với nhau, thì cú fáp fải chuyển tương ứng để thu được kết quả.

Chú í: $B89:$AD91 là vùng tra tỷ lệ;

AH5:AH86 cột số liệu theo kế hoạch

$AE5 ta có thể thêm hay không cũng OK, nhưng lúc đó fải tồn tại Name này (Model) trong trang tính. Thực ra ta có thể nhập ô nào trong cột AE này cũng xong, nhưng đó là chuyện mai sau.
Cũng xin nhắc lại, trong trang tính còn fải có Name là TyLe

Chúc thành công.

 

[trangthanhlam]

Nếu 100 dòng của trang tính của bạn giống với 100 của trang tính mà bạn đưa lên thì tại ô [AH97] bạn thử nhập công thức =AG95+AH91+AH94-AH92-AH93-Sluong($B89:$AD91,AH5:AH86,$AE5)
& nhấn {ENTER} xem nó có cho kết quả như dòng trên nó không?

(Nếu 2 trang tính không đồng nhất với nhau, thì cú fáp fải chuyển tương ứng để thu được kết quả.

Chú í: $B89:$AD91 là vùng tra tỷ lệ;

AH5:AH86 cột số liệu theo kế hoạch

$AE5 ta có thể thêm hay không cũng OK, nhưng lúc đó fải tồn tại Name này (Model) trong trang tính. Thực ra ta có thể nhập ô nào trong cột AE này cũng xong, nhưng đó là chuyện mai sau.
Cũng xin nhắc lại, trong trang tính còn fải có Name là TyLe

Chúc thành công.

Sao mình làm như bạn nói mà nó ra là #Name?
bị sai tên hàm? Nhưng mình đã đặt Tyle và Model là Name rồi.
Sao hoài không ra ta?
không biết nó bị gì vậy hả Bạn?

 

[SA_DQ]

Một lần nữa & có thể là lần cuối

Bạn Copy hàm tự tạo này vô trang tính của bạn:

Function S_Luong()
 Dim Rng As Range
 
 Set Rng = Range("TyLe"):           S_Luong = Rng.Columns.Count
 Set Rng = Range("Model"):
 S_Luong = S_Luong + Rng.Rows.Count
 
End Function

Và trên ô trống nào đó, bạn thử nhập cú fáp =S_Luong() & {ENTER} xem sao

Nếu nó cho số gì đó thì đúng; Còn thứ khác thì cho biết nha!

 

[trangthanhlam]

làm hoài không ra,
em đã không biết chạy Marco rồi,
mấy bác cứ viết Code MArco không à,
thôi kiểu này em tham chiếu từng ô cho chắc ăn,
em cảm ơn các bác nhé. hic hic

 

[SA_DQ]

Sao, lại định đoạn tình làm vậy?

Bạn thử cách cuối xem sao:

Bạn copy CSDL thật của bạn sang workbook mới;
Xóa hết các trang tính khác ngoại trừ để lại duy nhất 1 trang tính (Đó là trang nào, chắc bạn biết.)

Xóa hết các dòng sau dòng 114 (Theo file bạn đã đưa lên).
(Tại sao fải là 114: Vì để người giúp bạn có thể điền được 2 hàng công thức hàm tự tạo cho bạn)
Nghĩa là dưới RGG1000DTHCNL khoảng mươi dòng gì đó.

Sau khi nhận được trang tính có hàm tự tạo, bạn lại chép những dữ liệu khác vô nối tiếp để workbook trở thành của bạn & có thêm hàm tự tạo.

Mình thấy bạn làm tay rất dễ sai đó, nên khuyên bạn vậy mà!

Kiên trì thêm 1 tẹo xem sao.

 

[trangthanhlam]

Hi SA_DQ
File THUCHANH mà em gửi lên là File chính của em rồi đó.

EM cũng muốn đưa hết dữ liệu lên để các bác thuận tiện hơn trong việc giúp em.

chứ giờ em muốn nhơ mấy bác thì em lại giấu dữ liệu hay sao?

Hic, Các BÁc không ai giúp em thì em tự làm bằng tay tham chiếu thôi.

Mà em cũng biết rủi ro cao lắm, nhưng không còn cách nào khác, đành chấp nhận thương đau thôi.

CHứ file của em là File mà em vẫn làm việc, chỉ có điều còn rất nhiều mã phía sau thôi.

Huhu, Các Bác giúp em với.

 

[ChanhTQ@]

Thử lần nữa xem sao (Theo file đính kèm)

Trong file, mình đã lập công thức tại các dòng 97, 110, & 124;

Mình sẽ hướng dẫn bạn cách mà chúng ta fải trãi qua để có công thức hàm tự tạo tại dòng 136, như sau:

Bạn kích hoạt [H134], hiện nó có công thức:

=AG134+AH130+AH133-AH131-AH132-($V$128*AH27)-($W$128*AH15)- ($X$128*AH5)-($Y$128*AH9)-($AA$128*AH24)-($AB$128*AH21)-($AC$128*AH12)- ($AC$129*AH13)-($S$128*AH33)-($U$128*AH39)-($O$128*AH48)-($N$129*AH52)- ($M$128*AH53)-($L$128*AH61)-($H$128*AH67)-($G$128*AH70)-($D$128*AH79)- ($C$128*AH82)

Sau đó đưa chuột lên thanh công thúc & chép đoạn chuỗi mình mình đã tô đậm của công thức trên ( {CTRL} + C )

Tiếp theo, ta bấm chuột vô [AH136]; Nếu xuất hiện các đường viền đứt đoạn thì bạn bấm {ESC} để biến các đường đó đi; Tiếp bấm {CTRL} +C để dán fần công thức vô ô này. Nếu sau {ENTER} ta thấy trị của nó = với [AG134] thì tiếp

Fần tiếp: Bạn bấm chuột lên vị trí cuối của công thức vừa dán trên thanh công thức; & nhập thêm chuỗi sau: - Sluong(B128:AC131,AH5:AH86,MoDel) & {ENTER} ta sẽ thu được kết quả như tại [AH134]
Chuyện còn lại là sao chép công thức tại ô ta vừa tạo đến các ô khác cùng hàng, cho đến [AV136] (Mình nghĩ cái này bạn thừa sức)

Chúc bạn thành công!

 

[Ba Tê]

làm hoài không ra,
em đã không biết chạy Marco rồi,
mấy bác cứ viết Code MArco không à,
thôi kiểu này em tham chiếu từng ô cho chắc ăn,
em cảm ơn các bác nhé. hic hic

Mình cũng dốt VBA như Bạn.
Chẳng biết dữ liệu của Bạn lớn đến đâu, nếu Bạn đồng ý thay kiểu nhập dữ liệu hàng ngang trong sheet DEADLINE từ dòng 88 trở xuống thành dữ liệu cột dọc như trong Sheet5, ta có thể bỏ hơn 30 cột từ A-->AD trong sheet DEADLINE, công thức tính sẽ đơn giản hơn, Copy dễ dàng xuống các dòng dưới đến khi Excel không chịu nỗi.
Tốn thêm một sheet phụ.
Tôi nhập dữ liệu trong sheet5 được 4 cột, Bạn thử tiếp xem.

 

[trangthanhlam]

Trong file, mình đã lập công thức tại các dòng 97, 110, & 124;

Mình sẽ hướng dẫn bạn cách mà chúng ta fải trãi qua để có công thức hàm tự tạo tại dòng 136, như sau:

Bạn kích hoạt [H134], hiện nó có công thức:

=AG134+AH130+AH133-AH131-AH132-($V$128*AH27)-($W$128*AH15)- ($X$128*AH5)-($Y$128*AH9)-($AA$128*AH24)-($AB$128*AH21)-($AC$128*AH12)- ($AC$129*AH13)-($S$128*AH33)-($U$128*AH39)-($O$128*AH48)-($N$129*AH52)- ($M$128*AH53)-($L$128*AH61)-($H$128*AH67)-($G$128*AH70)-($D$128*AH79)- ($C$128*AH82)

Sau đó đưa chuột lên thanh công thúc & chép đoạn chuỗi mình mình đã tô đậm của công thức trên ( {CTRL} + C )

Tiếp theo, ta bấm chuột vô [AH136]; Nếu xuất hiện các đường viền đứt đoạn thì bạn bấm {ESC} để biến các đường đó đi; Tiếp bấm {CTRL} +C để dán fần công thức vô ô này. Nếu sau {ENTER} ta thấy trị của nó = với [AG134] thì tiếp

Fần tiếp: Bạn bấm chuột lên vị trí cuối của công thức vừa dán trên thanh công thức; & nhập thêm chuỗi sau: - Sluong(B128:AC131,AH5:AH86,MoDel) & {ENTER} ta sẽ thu được kết quả như tại [AH134]
Chuyện còn lại là sao chép công thức tại ô ta vừa tạo đến các ô khác cùng hàng, cho đến [AV136] (Mình nghĩ cái này bạn thừa sức)

Chúc bạn thành công!

Hi Bác ChanhTQ!
Em xin thành thật cảm ơn về những gì Bác đã làm cho em, đã giúp đỡ em,
Hix, em cũng làm như Bác nói, em copy rồi dán, nhưng chỉ còn công đoạn cuối cùng thôi,
em làm ra #Value?

Hix, lại không hiểu là vì sao nữa.

Thôi Bác ạ, em làm phiền Bác nhiều rồi, em không dám phiền Bác nữa đâu. Em chân thành cảm ơn mọi sự giúp đỡ của Bác trong những ngày qua. Hu hu hu

Mình cũng dốt VBA như Bạn.
Chẳng biết dữ liệu của Bạn lớn đến đâu, nếu Bạn đồng ý thay kiểu nhập dữ liệu hàng ngang trong sheet DEADLINE từ dòng 88 trở xuống thành dữ liệu cột dọc như trong Sheet5, ta có thể bỏ hơn 30 cột từ A-->AD trong sheet DEADLINE, công thức tính sẽ đơn giản hơn, Copy dễ dàng xuống các dòng dưới đến khi Excel không chịu nỗi.
Tốn thêm một sheet phụ.
Tôi nhập dữ liệu trong sheet5 được 4 cột, Bạn thử tiếp xem.

**************************************
Hi Bác Ba Tê!

Bác đã hiểu bảng tính của em rồi phải không?
thực ra làm như bác không ổn, vì dữ liệu của em tới 670 mã rồi.
Bây giờ làm như bác thì Excel không đủ cột để làm.
vì Excel chỉ có 256 cột thôi mà.
Dữ liệu của em tới 670 cơ ( chưa kể em chuẩn bị làm Model mới)
EM phải công nhận Excel thì Bác giỏi thật,
Bác lỡ nghĩ rồi, thì Bác nghĩ thêm giùm em cái được không Bác,
Bác ChanhTQ thì giỏi MArco, nhưng phần đó em không hiểu lắm, vì vậy em chỉ có thể trông mong vào bác nữa thôi.

Huhu, mà em phải để Mã cạnh tỉ lệ là vì trong quá trình sử dụng, mã đó có thể thay đổi tỉ lệ từ 1 qua 2 hay từ 2 trở về 0.


Rắc rối lắm, phòng em làm có vài vị cũng Pro Excel lắm, mà nghĩ không ra cách nào cải tiến bảng tính cả.
Thế nên em mới vào giaiphapexcel.com để cầu cứu mà.

Huhu

 

[ChanhTQ@]

Mình tiếc là không giúp bạn được đến nơi đến chốn ấy chứ!

Thôi Bác ạ, em làm phiền Bác nhiều rồi, em không dám phiền Bác nữa đâu. Em chân thành cảm ơn mọi sự giúp đỡ của Bác trong những ngày qua.

Có gì đâu mà fiền, bạn. Mình rất muốn giúp bạn cho đến thành công file này thôi mà.

Còn cái này, bạn xem xem nha:

File của bạn các ô từ AE5:AE96 đã trộn lại theo nhóm.

Nhưng file mình sửa lại đã bỏ trộn rồi.

Hay bạn lập công thức trên file của mình đã đưa lên trước đi đã. Sau đó hãy tính tiếp đến file thực của bạn. . .

Fần bổ sung: Nếu có ai đó giúp bạn công thức thì đã đành; Còn không, như cách làm của bạn hiện nay thì những sai lỗi sẽ rình rập bạn hoài đó nha!
Ví dụ: có lẽ tại AH122 bạn tính sai (?)

 

[trangthanhlam]

Bác coi lại giùm em cái Attach filed ở chỗ Value nha!

Có gì đâu mà fiền, bạn. Mình rất muốn giúp bạn cho đến thành công file này thôi mà.

Còn cái này, bạn xem xem nha:

File của bạn các ô từ AE5:AE96 đã trộn lại theo nhóm.

Nhưng file mình sửa lại đã bỏ trộn rồi.

Hay bạn lập công thức trên file của mình đã đưa lên trước đi đã. Sau đó hãy tính tiếp đến file thực của bạn. . .

Fần bổ sung: Nếu có ai đó giúp bạn công thức thì đã đành; Còn không, như cách làm của bạn hiện nay thì những sai lỗi sẽ rình rập bạn hoài đó nha!
Ví dụ: có lẽ tại AH122 bạn tính sai (?)

không đâu!
tại ô AH122 em không tính sai, vì em sợ nhiều quá sai công thứ, nên em mới xuống dòng tách ra thôi. nên có gì bác coi lại giùm em cái attach filed

 

[Ba Tê]

Không xoay ngang thì xoay dọc

Chủ yếu là các mã dữ liệu phải theo một thứ tự chứ không được ngẫu hứng.
Các mã cột CG từ trên xuống phải giống với các mã dòng 88 từ trái sang phải.
Chưa kiểm tra lại kết quả.
Hy vọng được.
(Hình như Bạn chưa biết cái nút cảm ơn?)

 

[trangthanhlam]

bác Ba Tê ơi!

Sao em tải file về máy, sau đó em nhấp chuột coi công thức thì nó hiện ra Value?

sao kì vậy?

Bác đâu có chạy Marco đâu ?

 

[Ba Tê]

bác Ba Tê ơi!

Sao em tải file về máy, sau đó em nhấp chuột coi công thức thì nó hiện ra Value?

sao kì vậy?

Bác đâu có chạy Marco đâu ?

Đấy là công thức mảng, muốn xem thì xem trên thanh công thức, nếu nhấp chuột vào ô ở chế độ Edit thì phải Ctrl-Shift-Enter.

 

[trangthanhlam]

Đấy là công thức mảng, muốn xem thì xem trên thanh công thức, nếu nhấp chuột vào ô ở chế độ Edit thì phải Ctrl-Shift-Enter.

-----> Nếu vậy,
em chỉ có thể coi trên thanh công thưc thôi sao.
vậy sau này em cần chỉnh sửa công thức thì dữ liệu bị sai hết ạ.

Hí hí... Em làm được rồi.
Mà không còn cách nào khác hả Bác.
Tính em hay vụng về lắm.

Rủi cái bữa sau em quên ấn Ctrl+Shift+Enter thì chết.

Bác thiết kế bảng tính như vậy rất hay và hợp lí rùi.

Nhưng giờ em gặp khó khăn chỉ là chuyện công thức thôi.

Ức ực....

 

[Ba Tê]

-----> Nếu vậy,
em chỉ có thể coi trên thanh công thưc thôi sao.
vậy sau này em cần chỉnh sửa công thức thì dữ liệu bị sai hết ạ.

Hí hí... Em làm được rồi.
Mà không còn cách nào khác hả Bác.
Tính em hay vụng về lắm.

Rủi cái bữa sau em quên ấn Ctrl+Shift+Enter thì chết.

Bác thiết kế bảng tính như vậy rất hay và hợp lí rùi.

Nhưng giờ em gặp khó khăn chỉ là chuyện công thức thôi.

Ức ực....

Cứ nhập công thức vào 1 ô, Ctrl-Shift-Enter. Xong kéo copy sang ngang.
Muốn copy xuống, quét chọn cả hàng ngang dòng chứa công thức, Copy, xuống dòng dưới, Past là xong.
Có thể copy ô đầu tiên trong hàng ngang, xuống dòng dưới (cùng cột) Past, xong lại kéo Copy sang ngang...
Nhấp chuột vào một ô bình thường và xem công thức trên thanh công thức đâu có khó?!?
Sao lại lo lắng thế nhỉ!

 

[trangthanhlam]

híc, Bác không phải em, làm sao Bác hiểu được sự lo lắng của em,
Hu hu, Anyway,Many Thanks for your help.

 

[ChanhTQ@]

Bạn đang sai là không dùng địa chỉ tương đối hay tuyệt đối khi Copy công thức!

không đâu!
tại ô AH122 em không tính sai, vì em sợ nhiều quá sai công thứ, nên em mới xuống dòng tách ra thôi. nên có gì bác coi lại giùm em cái attach filed

Đây là công thức tại ô [AH124] của mình:

=AG121+AH117+AH120-AH118-AH119-Sluong($B$115:$AD$118,AH$5:AH$86)

& tại [AH136] của bạn

=AG134+AH130+AH133-AH131-AH132- Sluong(B128:AC131,AH5:AH86,MoDel)

Nên khi mình copy đến [AM124] nó vẫn là:
=AL121+AM117+AM120-AM118-AM119-Sluong($B$115:$AD$118,AM$5:AM$86)

Còn tại [AM136] của bạn đang là:

=AL134+AM130+AM133-AM131-AM132- Sluong(G128:AH131,AM5:AM86,MoDel)
Vùng để macro tra số liệu đã sai, nên hàm nó bảo sai là đúng rùi!
(Chú í, vì vùng Model là vùng gán tên nên sẽ không dẫn đến sai fạm đâu nghe bạn; Đừng lăng tăng khi ta dùng tên gán trong công thức)
Nhưng vùng tô đỏ đó không nên gán tên; Vì nếu gán tên thì bạn sẽ có 600 hơn cái tên;
Nhưng để thử 1 cái tên cho được cả dòng, hầu ngâm cứu, rút kinh nghiệm là được.
(Dừng tí chê bạn 1 tẹo: Chưa rành cái ni - địa chi thường, tương đối & tuyệt đối thì không xong đâu, bạn à! Tìm các bài cơ bản trên diễn đàn mà bổ khuyết ngay đi, khi có dịp, nha.)

(*) Để thử nghiệm vùng gán tên, ta tiến hành gán 1 cái tên thân iêu cho vùng $B$115:$AD$118 ; sau đó trong công thức tại [AH136] ta thay tên vùng đó vô địa chỉ này.
Xong rồi thì copy vô tư trong hàng.
Chú í là đúng trong hàng thôi nha; Các hàng dưới tương đương thì lại gán tên khác cho vùng tra.

* Chuyện gán này để bạn làm cho biết mùi vị thôi; Chứ không nên áp dụng cho toàn trang tính.

Chúc sớm thành công.

PC: Hãy khoang xài hàm mảng của BaTê Vì đến dòng thức 500 trở lên là thấy liền hà.​

 

[trangthanhlam]

EM Phải làm sao ????

Đây là công thức tại ô [AH124] của mình:

=AG121+AH117+AH120-AH118-AH119-Sluong($B$115:$AD$118,AH$5:AH$86)

& tại [AH136] của bạn

=AG134+AH130+AH133-AH131-AH132- Sluong(B128:AC131,AH5:AH86,MoDel)

Nên khi mình copy đến [AM124] nó vẫn là:
=AL121+AM117+AM120-AM118-AM119-Sluong($B$115:$AD$118,AM$5:AM$86)

Còn tại [AM136] của bạn đang là:

=AL134+AM130+AM133-AM131-AM132- Sluong(G128:AH131,AM5:AM86,MoDel)
Vùng để macro tra số liệu đã sai, nên hàm nó bảo sai là đúng rùi!
(Chú í, vì vùng Model là vùng gán tên nên sẽ không dẫn đến sai fạm đâu nghe bạn; Đừng lăng tăng khi ta dùng tên gán trong công thức)
Nhưng vùng tô đỏ đó không nên gán tên; Vì nếu gán tên thì bạn sẽ có 600 hơn cái tên;
Nhưng để thử 1 cái tên cho được cả dòng, hầu ngâm cứu, rút kinh nghiệm là được.
(Dừng tí chê bạn 1 tẹo: Chưa rành cái ni - địa chi thường, tương đối & tuyệt đối thì không xong đâu, bạn à! Tìm các bài cơ bản trên diễn đàn mà bổ khuyết ngay đi, khi có dịp, nha.)

(*) Để thử nghiệm vùng gán tên, ta tiến hành gán 1 cái tên thân iêu cho vùng $B$115:$AD$118 ; sau đó trong công thức tại [AH136] ta thay tên vùng đó vô địa chỉ này.
Xong rồi thì copy vô tư trong hàng.
Chú í là đúng trong hàng thôi nha; Các hàng dưới tương đương thì lại gán tên khác cho vùng tra.

* Chuyện gán này để bạn làm cho biết mùi vị thôi; Chứ không nên áp dụng cho toàn trang tính.

Chúc sớm thành công.

PC: Hãy khoang xài hàm mảng của BaTê Vì đến dòng thức 500 trở lên là thấy liền hà.​

***********************************************
Vậy 2 Bác bảo em phải làm sao đây?
Trong khi của Bác ChanhTQ thì chạy Marco, còn của Bác Ba Tê thì chỉ đến dòng thứ 500 là biết ( ý nói sẽ không bị cái này thì bị cái kia)
--> đúng là em quá sơ suất khi không để ý đến địa chỉ tuyệt đối và tương đối khi copy công thức xuống hàng dưới,

Nhưng bây giờ đó chỉ là file Bác ChanhTQ đã chạy Marco cho em rồi,
Còn file chính của em thì làm sao em chạy được đây...

Mà em nghe nói là chạy Marco có khi có chương trình diệt Virus tưởng là quân thù, ôi thôi nó tiêu diệt sạch thì có mà chết em thôi,
Huhuhuhu