Đơn giản công thức tính số dòng cuối cùng

[digita]

Chào các bạn,

Tôi có 1 công thức sau đây đếm số dòng trong bảng dữ liệu bắt đầu từ ô A26 xuống. Ô X2 chứa giá trị cái hàng (hay dòng) cuối của bảng:

=COUNTA(INDIRECT("$A$26:$A"&X2))

Vì số hảng trong bảng này thay đổi thường xuyên và tôi không muốn phải gõ con số trong ô X2 mổi khi tính. Tôi viết ra UDF dưới đây để XL tự động trả về giá trị hàng cuối trong bảng:

Function LR(cel As Range)
If cel.End(xlDown).Row > Cells.Rows.Count Then
LR = cel.Row
Else
LR = cel.End(xlDown).Row
End If
End Function

Giờ thì công thức được thay đổi thành:

=COUNTA(INDIRECT("$A$26:$A"&lr($A$25)))

Tuy nhiên, UDF trên cho biết giá trị hàng cuối rồi. Tôi nghĩ dùng hàm indirect thì hơi thừa. Mà không có nó trong đây thì không được.

Trong GPE này có nhiều người giỏi về công thức. Vậy nhờ các bạn có cách nào rút ngắn cái công thức xe lửa trên giùm.

----------------------------
Bác digita ơi, nhớ cẩn thận cái tiêu đề nhé. OverAC

 

[ptm0412]

nếu đã có giá trị hàng cuối thì lấy số đó trừ 25. Và giá trị dòng cuối không cần phải viết UDF mới có.

 

[ThuNghi]

Chưa hiểu bạn cần gì nhưng mà chỉ cần Counta($A$26:$A$50000) là OK rồi

 

[cadafi]

Chào các bạn,

Tôi có 1 công thức sau đây đếm số dòng trong bảng dữ liệu bắt đầu từ ô A26 xuống. Ô X2 chứa giá trị cái hàng (hay dòng) cuối của bảng:



Vì số hảng trong bảng này thay đổi thường xuyên và tôi không muốn phải gõ con số trong ô X2 mổi khi tính. Tôi viết ra UDF dưới đây để XL tự động trả về giá trị hàng cuối trong bảng:



Giờ thì công thức được thay đổi thành:



Tuy nhiên, UDF trên cho biết giá trị hàng cuối rồi. Tôi nghĩ dùng hàm indirect thì hơi thừa. Mà không có nó trong đây thì không được.

Trong GPE này có nhiều người giỏi về công thức. Vậy nhờ các bạn có cách nào rút ngắn cái công thức xe lửa trên giùm.

Chào bạn,
Sao mình không dùng Counta(A:A) nhỉ, đâu cần UDF và cột phụ đâu bạn!

 

[Po_Pikachu]

Bạn dùng thằng này thì có toàn bộ địa chỉ của cột đó luôn. Với cel là ô đầu tiên của cột đó.

Function LR(cel As Range) a = cel.Address b = cel.End(xlDown).Address LR = (a & ":" & b) End Function

Hoặc lấy riêng địa chỉ cuối cùng thôi

Function LR(cel As Range) a = cel.End(xlDown).Address LR = a End Function

Với cel là địa chỉ ô đầu tiên bạn muốn lấy! Bạn có thể sữa lại: a = cel.address thành a = cel.End(xlUp).address để có thể chọn ô ở bất cứ đâu trong cột đó. Không biết có đúng ý bạn không.

 

[digita]

Cảm ơn các bạn đã góp ý.

Cái này có chút rắc rối tôi quên nói rõ. Trong sheet tôi hiện có 2 phần. Phần đầu có khoảng hơn 40 ngàn dòng dữ liệu liên tục tử hàng 26 xuống. Phần thứ 2 dưới phần 1 và vài hàng trống chia đôi 2 bảng. Nếu dùng công thức

=counta(A:A)

thì lại đếm luôn cái bảng thứ 2 nên không được. Tôi chỉ muốn biết số lượng dữ liệu phần 1 thôi trong phần báo cáo. Nếu UDF cho ra số 48123 thì tôi muốn cái công thức trở thành là:

=counta($A$26:$A$48123)

Tôi muốn bỏ cái hàm indirect bằng cách đơn giản hóa công thức chưa nghĩ ra. Chắc phải dùng chiêu VBA quá.

 

[ndu96081631]

Lở tạo UDF rồi sao không tạo luôn hàm đếm theo ý bạn, cần gì vừa UDF lại vừa công thức?
Còn nếu chỉ muốn thuần công thức thì bạn tham khảo tại đây:
http://www.giaiphapexcel.com/forum/showthread.php?t=7669
Có công thức chuyên dùng lấy dòng cuối cùng có dử liệu đấy
Còn theo như bạn nói: từ dòng 26 trở đi đựoc chia thành 2 bảng tính, giữa chúng có vài dòng rổng... Dựa vào mấy dòng rổng này có thể xác định cell cuối cùng của bảng 1 rồi!
Ví dụ như:

=MATCH(TRUE,A26:A60000="",0)+24

Đó chính là dòng cuối cùng có dử liệu của bảng 1
(chú ý: đây là công thức mãng)

 

[Po_Pikachu]

Vậy công thức như vậy thôi!
=COUNTA(INDIRECT(lr(A26)))
Và đáp số là 48098 ô đúng không!

 

[digita]

Á, hay quá bạn ndu96081631 ơi. Đúng rồi đây là công thức tôi đang tìm. Tôi muốn bạn góp ý thêm. Nếu trong trường hợp bảng tính có hơn 65336 hàng và phải xài XL 2007 thì cần sửa cái công thức trên như thế nào vừa tích hợp cho XL 2007 và các phiên bản trước đó.

Cám ơn bạn nhiếu nhé.

 

[digita]

Gửi bạn Po_Pikachu,

Tròi ơi, cái này sao tôi nghĩ không ra cà! Cái này đễ dành sài cho XL 2007 nếu không có cách nào khác.

Cám ơn bạn nhá.

 

[ndu96081631]

Á, hay quá bạn ndu96081631 ơi. Đúng rồi đây là công thức tôi đang tìm. Tôi muốn bạn góp ý thêm. Nếu trong trường hợp bảng tính có hơn 65336 hàng và phải xài XL 2007 thì cần sửa cái công thức trên như thế nào vừa tích hợp cho XL 2007 và các phiên bản trước đó.

Cám ơn bạn nhiếu nhé.

1> Giã sử rằng dòng đầu tiên là dòng số 26
2> Giã sử rằng bạn muốn công thức này đúng với cả Excel2007 và các version trứoc
Vậy thì dùng cái này là ăn chắc:

=MATCH(TRUE,INDIRECT("A26:A"&MATCH(REPT("z",255),$A:$A))="",0)+24

Nếu dử liệu của bạn là Number thì:
thay thế:
MATCH(REPT("z",255),$A:$A)
bằng:
MATCH(9.99999999999999E+307,$A:$A)
Vậy công thức cuối cùng bạn cần là:

=COUNTA(INDIRECT("A26:A"&MATCH(TRUE,INDIRECT("A26:A"&MATCH(REPT("z",255),$A:$A))="",0)+24))

Có thể đặt name để rút gọn công thức này

 

[digita]

Coi như đã giải quyết đếm dữ liệu xong. Chỉnh đổi đôi chút, dùng cách anh bạn ndu96081631 thì là:

{=MATCH(TRUE,A26:A60000="",0)-1}

Còn của bạn Po_Pikachu là:

=lr(A26)-25

Bây giờ lại đẻ ra cái vụ đếm có điều kiện countif thì kết hợp hai cách trên thì như thế nào đây? Dựa theo cái UDF thì tôi có công thức sau:

=COUNTIF(INDIRECT("$A$26:$A"&lr($A$25)),$N6)

Làm sao bỏ luôn hảm indirect hả các bạn?

 

[anhtuan1066]

Ko dùng INDIRECT thì dùng OFFSET, cũng vậy thôi!
E rằng phải dùng 1 trong 2 cái, ko bỏ dc! Trừ phi bạn làm nó bằng UDF luôn, khỏi lộn xộn giữa công thức và VBA

 

[digita]

Cám ơn bạn ndu96081631 rất nhiều. Cái công thức dùng cho mọi version trong XL rất hay tuy rằng cũng phải tạm xài cái hàm indirect. Rồi anh Tuấn nói thêm là nếu không thích indirect thì dùng offset nhưng phải xài 1 trong 2. Có rất nhiều cách giải quyết mà giá trị cái hàng cuối cùng là biến số thì hảm indirect đôi khi là cách duy nhất trong việc đếm. Áp dụng các cách trên vô trong hàm countif thì hơi căng. Thôi thì tôi cứ tạm giữ nguyên cái công thức xe lửa vậy.

Một lần nữa cám ơn tất cả mọi người đã quan tâm với câu hỏi này.

 

[Po_Pikachu]

Cám ơn bạn ndu96081631 rất nhiều. Cái công thức dùng cho mọi version trong XL rất hay tuy rằng cũng phải tạm xài cái hàm indirect. Rồi anh Tuấn nói thêm là nếu không thích indirect thì dùng offset nhưng phải xài 1 trong 2. Có rất nhiều cách giải quyết mà giá trị cái hàng cuối cùng là biến số thì hảm indirect đôi khi là cách duy nhất trong việc đếm. Áp dụng các cách trên vô trong hàm countif thì hơi căng. Thôi thì tôi cứ tạm giữ nguyên cái công thức xe lửa vậy. Một lần nữa cám ơn tất cả mọi người đã quan tâm với câu hỏi này.

Gì mà vội vậy, phải cho thời gian suy nghĩ chứ. Tại bạn không nêu rõ từ đầu là muốn đếm có điều kiện nên công việc mới chậm thế đó. Còn đây là công thức đếm có điều kiện luôn nè!

Function countiflr(cel As Range, cri As Range) a = Replace(cel.Address, "$", "") b = Replace(cel.End(xlDown).Address, "$", "") c = Replace(cri.Address, "$", "") countiflr = Application.WorksheetFunction.CountIf(ActiveSheet.Range(a & ":" & b), ActiveSheet.Range(c)) End Function

Cái cel thì phải là ô đầu tiên của cột cần lọc, cri là điều kiện lọc của nó. Sử dụng cho mọi phiên bảng Excel luôn.

 

[digita]

Cám ơn bạn Po_Pikachu rất nhiều. Vì công việc gấp rút cần cách giải quyết nhanh nên mong bạn thông cảm. UDF rất hay thay thế cái công thức dài dòng trên. Giờ thì không cần xài cái hàm indirect và UDF LR cùng 1 lúc làm chậm tốc độ tính toán.

Có vài mẹo nhỏ (tôi nghĩ bạn cũng biết) xin chia sẽ nơi đây để làm cái UDF này gọn nhẹ và chạy nhanh hơn. Lấy cái địa chỉ ô mà không có cái dấu "$" bằng range(x).address(0,0) thì khỏi cần dùng hàm VBA replace. Giảm thiểu gọi nhiều object, thí dụ activesheet.range(x) thì rút lại thành range(x).

Càm ơn bạn.

 

[ndu96081631]

Có vài mẹo nhỏ (tôi nghĩ bạn cũng biết) xin chia sẽ nơi đây để làm cái UDF này gọn nhẹ và chạy nhanh hơn. Lấy cái địa chỉ ô mà không có cái dấu "$" bằng range(x).address(0,0) thì khỏi cần dùng hàm VBA replace. Giảm thiểu gọi nhiều object, thí dụ activesheet.range(x) thì rút lại thành range(x).

Càm ơn bạn.

Cái này chuyên dùng để Get Column Letter... Nhanh gọn và chính xác đấy