Hỏi về công thức tính tổng, khi thay đổi vùng dữ liệu, công thức cũng thay đổi theo?

[nokiano258vn]

Tôi có bảng tính gồm Loại cây; Kích thước cây; ĐVT; Đơn giá; Mật độ cây. Nếu thay 'C:\Documents and Settings\NOKIANO.258\Application Data\Microsoft\AddIns[Tinh cay.xla]DG'! là DG! Thì OK nhưng như vậy thì mỗi lần làm lại phải copy cái DG đấy vào sheet bên cạnh thì bất tiện quá.

Sub nhapcay()
'
' Macro6 Macro
' Macro recorded 10/4/2007 by Nokiano258
'

'

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'C:\Documents and Settings\NOKIANO.258\Application Data\Microsoft\AddIns[Tinh cay.xla]DG'!R4C1:R2000C6,2,0)"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'C:\Documents and Settings\NOKIANO.258\Application Data\Microsoft\AddIns[Tinh cay.xla]DG'!R4C1:R2000C6,3,0)"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'C:\Documents and Settings\NOKIANO.258\Application Data\Microsoft\AddIns[Tinh cay.xla]DG'!R4C1:R2000C6,4,0)"
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'C:\Documents and Settings\NOKIANO.258\Application Data\Microsoft\AddIns[Tinh cay.xla]DG'!R4C1:R2000C6,5,0)"
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'C:\Documents and Settings\NOKIANO.258\Application Data\Microsoft\AddIns[Tinh cay.xla]DG'!R4C1:R2000C6,6,0)"
ActiveCell.Offset(1, -6).Range("A1").Select
End Sub

 

[van2000]

Đúng theo tôi nên dùng cách như anh tuấn là hợp lý nhất. Đặt tên vùng cần tính tổng là song
Dùng name là được thôi
1/ Đặt name DATA = Sheet1!$A$2:$A$65000
2/ Công thức ở A1 : = SUM(DATA)
Thân
Chúc thành công!

 

[anhtuan1066]

Tạo name với tham chiếu từ dòng 2 đến dòng 65000 là đơn giản nhất!
Nhưng có cách nào tạo 1 name động sao cho nó nhận biết dc dòng cuối cùng có dử liệu ko nhỉ? Phòng trường hợp dử liệu ko liên tục ấy mà... Giã định là ko dùng macro nha!
ANH TUẤN

 

[nokiano258vn]

Cái hàm sum(offset) mà anhtuan bảo, tôi đã dùng được và không sợ bị xoá ở giữa vì tôi tính tổng của cột thành tiền, nếu ô nào trống thì có nghĩa là kết quả ở đó bằng 0 và nó vẫn tính bình thường

 

[nokiano258vn]

Tạo name với tham chiếu từ dòng 2 đến dòng 65000. Thì tôi chết, vì như thế nó sẽ cộng cả của cái tổng của người khác vào tổng chung

 

[anhtuan1066]

Nhưng Offset sẽ ko dùng dc trong trường hợp dử liệu bị cách quảng... Bạn thử test trong trường hợp này xem: A2=5... A3=7... A4, A5, A6 rỗng... A7=10... Nếu nó tổng dc = 22 thì OK... hi... hi...
ANH TUẤN

 

[SoiBien]

Tôi có bảng tính gồm Loại cây; Kích thước cây; ĐVT; Đơn giá; Mật độ cây. Nếu thay 'C:\Documents and Settings\NOKIANO.258\Application Data\Microsoft\AddIns[Tinh cay.xla]DG'! là DG! Thì OK nhưng như vậy thì mỗi lần làm lại phải copy cái DG đấy vào sheet bên cạnh thì bất tiện quá.

Nhìn công thức của bạn thì như vậy là bạn để Data trong Xla, vậy bạn làm như thế này thư xem,
Bạn đã save book1 thành addin tên là tinh cay.xla (trong này có đơn giá của cây và nằm ở sheet DG, đúng không ạ?)
Bạn vào menu Tools/Addins
Bấm chọn Browse, browse đến file tinh cay.xla
OK
OK

rồi, bây giờ dữ liệu trong bảng DG được load tự động mỗi khi bạn mở Excel

phần còn lại thì bạn sửa các công thức có liên quan đến bảng DG trong file tinh cay.xla thành [tinh cay.xla]DG!$D$1:$F$2000,

Không biết đúng ý bạn không, nếu chưa được thì bạn cứ xoá bớt data trong file tinh cay.xla đi, nén lại rồi post lên. Bạn phài post cả file có công thức dùng để tham chiếu đến file tinh cay.xla. đại khái là các file liên quan.

Thân.

 

[nokiano258vn]

Nhưng Offset sẽ ko dùng dc trong trường hợp dử liệu bị cách quảng... Bạn thử test trong trường hợp này xem: A2=5... A3=7... A4, A5, A6 rỗng... A7=10... Nếu nó tổng dc = 22 thì OK... hi... hi...
ANH TUẤN

Thực ra tôi không đặt như vậy, vì các số trong cột của tôi luôn có. Vì là cột thành tiền nên phải có ít nhất là khối lượng nhân đơn giá, nếu một trong hai ô khối lượng hoặc đơn giá là 0 thì đương nhiên bên cột thành tiền là 0 không ảnh hưởng gì đến kết quả cả phải không.

 

[anhtuan1066]

Uh... tất nhiên tôi hiểu cách bạn làm... nhưng ở đây là tôi muốn hỏi mọi người về cách làm Offset, sao cho nó nhận biết dc cell cuối cùng... may mắn là cột của bạn luôn có dử liệu, (số 0 cũng xem như là có dử liệu)... Trong 1 trường hợp đặt biệt nào đó khác với trường hợp của bạn, cell = rổng nằm cắt ngang qua vùng dử liệu thì coi như.. thua... Ít nhất là cho đến hiện tại tôi vẫn ko tìm dc cách hoàn hảo nếu ko dùng VBA
ANH TUẤN

 

[SoiBien]

tôi muốn hỏi mọi người về cách làm Offset, sao cho nó nhận biết dc cell cuối cùng

Bác Tuấn xem cái em làm nhé, nếu được thì bác cải tiến hộ em.

 

[TranThanhPhong]

Uh... tất nhiên tôi hiểu cách bạn làm... nhưng ở đây là tôi muốn hỏi mọi người về cách làm Offset, sao cho nó nhận biết dc cell cuối cùng... may mắn là cột của bạn luôn có dử liệu, (số 0 cũng xem như là có dử liệu)... Trong 1 trường hợp đặt biệt nào đó khác với trường hợp của bạn, cell = rổng nằm cắt ngang qua vùng dử liệu thì coi như.. thua... Ít nhất là cho đến hiện tại tôi vẫn ko tìm dc cách hoàn hảo nếu ko dùng VBA
ANH TUẤN

Anh Tuấn đọc tài liệu hàm mảng chưa?

Anh vào đọc mục u và v mục 3 các ví dụ

http://www.giaiphapexcel.com/forum/showpost.php?p=30743&postcount=56

TP.

 

[anhtuan1066]

Cám ơn Phong... về mảng tôi vẫn còn yếu lắm.. đễ ngâm cứu thêm cái...
Với Soibien: Nè... cái tôi muốn nói là ko dùng macro cơ mà... thế mới cay... A1, A2 và A6 có dử liệu... A3, A4, A5 rỗng... thế thì làm sao điều khiển offset từ A1 cho nó mở rộng đúng 6 dòng nhỉ...
KO DÙNG VBA... KHÓ LÀ Ở CHỔ ĐÓ ĐÓ...
ANH TUẤN

 

[SoiBien]

Cám ơn Phong... về mảng tôi vẫn còn yếu lắm.. đễ ngâm cứu thêm cái...
Với Soibien: Nè... cái tôi muốn nói là ko dùng macro cơ mà... thế mới cay... A1, A2 và A6 có dử liệu... A3, A4, A5 rỗng... thế thì làm sao điều khiển offset từ A1 cho nó mở rộng đúng 6 dòng nhỉ...
KO DÙNG VBA... KHÓ LÀ Ở CHỔ ĐÓ ĐÓ...
ANH TUẤN

Trời, em có dùng VBA đâu. Dynamic Name mà, em định nghĩa 2 cái, Data là dynamic name (offset....), còn lastrow cũng là name mà. Bác coi kỹ cái file em gửi đó.

 

[ThuNghi]

LastRow={=MAX((INDIRECT("$A$1:$A65535")<>"")*(ROW(INDIRECT("$A$1:$A$65535"))))}
Quá hay luôn, đang thắc mắc, nếu refer to thì không cần {}. Hay quá ha.

 

[anhtuan1066]

Ah... đễ xem lại.. tại tôi thấy trong file có macro nên tường... Remove macro và kiểm tra lại cái nhé!
......................................
Xem rồi.... Ai dà... công thức này hơi bị độc à nha! Cám ơn nhiều nhiều
Đề nghị BQT nên đưa bài này vào những bài toán hay đi
ANH TUẤN

 

[anhphuong]

Trời, em có dùng VBA đâu. Dynamic Name mà, em định nghĩa 2 cái, Data là dynamic name (offset....), còn lastrow cũng là name mà. Bác coi kỹ cái file em gửi đó.

Tuyệt cú mèo ah nha! Hay! Cám ơn SoiBien. Nhớ giúp giùm vụ bữa trước nha, rất cần đấy. Thanhk

 

[anhtuan1066]

Nói thật nha... thấy thì nó hay... nhưng tôi vẩn ngờ ngợ... chưa thật sự hiểu lắm cái name này... Sói biển có thể giãi thích sơ qua chút ko?
Tuy nhiên... ko uổng công làm quen đại cao thủ.. vừa giõi lại vừa khiêm tốn... ha.. ha..
Nhằn riêng: Tối nào cảm thấy cô đơn thì Alo 1 tiếng qua Biên Hòa uống cafe nhé! Tôi luôn sẳn sàng...
ANH TUẤN

 

[ThuNghi]

{=MAX((INDIRECT("$A$1:$A65535")<>"")*(ROW( INDIRECT("$A$1:$A$65535"))))}

Nó là công thức mảng, tìm Row <> "" max

 

[Pansy_flower]

Hì, giờ em ngồi đọc lại bài này từ đầu đến cuối đến công thức của anh Soibien mới thấy nó giống bài này.

Hì, vậy anh TuanGiang và anh Soibien, tư tưởng lớn gặp nhau!.

 

[SoiBien]

anhtuan1066][/B]có thể giãi thích sơ qua chút ko?

Offset thì không phải giải thích phải không ạ

còn lastrow thì giải thích tạm thế này nha anh:
VD mình có 3 ô a1 = 3, a2 = (trống, blank), a3 = "C"
vậy thì khi dùng công thức mảng

Với Indirect(a1:a3<>"")

• thì mình có 1 mảng với 3 giá trị sau : {true,false,true} hoặc hiểu là {1,0,1}

với Row(indirect(a1:a3))

• thì mình có 1 mảng trả về chứa dòng của từng cell trong mảng trả về bởi Indirect, với vd trên mình sẽ có mảng {1,2,3}

đem 2 mảng {1,0,1}, {1,2,3} nhân với nhau, mình sẽ có mảng {1,0,3}

vậy cuối cùng hàm Max sẽ trả về phần tử lớn nhất trong mảng là 3.


Không biết giải thích vậy có rõ không nữa, nếu ai có cách nào dễ hiểu hơn thì post lên nha. Tks.

Nhưng công thức mà em làm không nhập chung thành 1 được, mà em phải tách ra làm 2, tính lastrow riêng, rồi gán lastrow vào trong công thức offset thì được, nhập chung thì không được, chẳng hiểu sao nữa ???? Bạn nào biết vui lòng giải thích cho mình với

1 điều nữa là không dùng tới địa chỉ A65536 được ??????????

(bởi vậy em mới nhờ anh cải tiến giùm đó ạ!!!)