Tạo dòng "Tổng cộng cuối trang"

Liên hệ QC

handung107

Thành viên gắn bó
Thành viên danh dự
Tham gia
30/5/06
Bài viết
1,630
Được thích
17,436
Nghề nghiệp
Bác sĩ
Tạo ra dòng 'tổng đến hết trang' và 'số mang sang' cho bảng tính nhiều trang

Vọng Phúc​

Khi in bảng tính trong Exel, làm thế nào để khi in ra các trang trong 1 sheet đều có dòng cuối trang của sheet đó ( dòng này là dòng tổng cộng do mình tạo ra không phải footer) ?

Ví dụ như trên sổ quỹ tiền mặt, cuối mỗi trang ta phải có dòng tổng, và sang đầu trang sau phải có số dư mang sang từ trang trước

Dòng tổng cộng này là rất cần thiết với thủ quỹ hay kế toán bằng tay trong việc cộng sổ hay kiểm tra sai sót. Đối với kế toán máy thì có thể bỏ qua

Muốn tạo ra dòng tổng cộng này, và cả dòng mang sang từ trang trước, ta thực hiện một macro đặt page setup và cố định độ cao dòng, mục đích là để có số dòng cố định trên 1 trang giấy in. Sau đó ta thực hiện insert dòng tổng phía dưới trang, insert dòng tiêu đề của trang sau, insert dòng mang sang từ trang trước, và đặt công thức tính toán vào các ô tương ứng

Trong trường hợp ta cần tính toán lại, hoặc thêm bớt dòng dữ liệu, nên ta cũng cần làm một cái macro ngược lại, tức là xóa đi những dòng dữ liệu đã thêm vào
 
phamnhukhang đã viết:
Quả thật bác Ptm rất tận tình hướng dẫn cho các bạn, tuy nhiên tôi có một vài nhận xét về bài này của tác giả. Bác Ptm đã vô tình phức tạp hoá vấn đề mà vẫn phải làm thủ công. Đã thủ công thì ta chọn cách đơn giản và nhanh nhất, sau khi đã làm xong bảng tính, nếu muốn chèn thêm dòng tổng kết vào cuối bảng, ta Print Preview và quay trở lại bảng tính, sẽ xuất hiện các đường kẻ đứt đoạn phận chia trang, vậy thì chỉ việc chèn dòng tổng kết vào cuối là xong (chẳng phải chia 25.., cột phụ hay subtotal gì cho mệt).

Chú kính,

Dẫu sao cháu thấy phải bắt đầu từ cái dễ đến cái khó chứ. Ở đây cháu và các thành viên rất vui khi được tận tình giải thích cặn kẽ như thế thật vô cùng quý báu.

Mỗi phương pháp, giải thuật đều có cái hay của nó.Qua topic này cháu học được 2 phương pháp để giải quyết vấn đề.

Kính
 
Upvote 0
Thực tình các anh chi đã chỉ bảo rất tận tình, tuy nhiên em cảm thấy những cách này là thủ công.
Em nói vậy là vì sheet em gửi các anh chị là sheet thue trong một file lớn, do vậy cần mức độ tự động hóa cao.
Kinh mong các anh, chị chỉ giúp.
 
Upvote 0
ptm0412 đã viết:
Tóm lại là dữ liệu càng nhiều thì số thập phân sau số 25 phải càng nhỏ.
thế thì làm thế này, chắc ăn lun

Giả sử số thứ tự (STT) ở A7 thì ô B7 tính thế này: =INT(A7/(25+0.1/A7)
thế là ko lo số lớn nữa


P/S sửa:

Sorry nhậm A7 thành B7 hic, (đã sửa chỗ màu đỏ)

buổi trưa post sau nhìn thấy NHẦM mà cứ bấm vào SỬA bài viết thì GPE đứt không sửa được (tức thật)
 
Lần chỉnh sửa cuối:
Upvote 0
phamnhukhang đã viết:
Quả thật bác Ptm rất tận tình hướng dẫn cho các bạn, tuy nhiên tôi có một vài nhận xét về bài này của tác giả. Bác Ptm đã vô tình phức tạp hoá vấn đề mà vẫn phải làm thủ công. Đã thủ công thì ta chọn cách đơn giản và nhanh nhất, sau khi đã làm xong bảng tính, nếu muốn chèn thêm dòng tổng kết vào cuối bảng, ta Print Preview và quay trở lại bảng tính, sẽ xuất hiện các đường kẻ đứt đoạn phận chia trang, vậy thì chỉ việc chèn dòng tổng kết vào cuối là xong (chẳng phải chia 25.., cột phụ hay subtotal gì cho mệt).
Nếu 5, 7 trang làm như vậy không thành vấn đề, nhưng nếu 50 trang thì làm vậy chậm hơn cách của tớ, tớ làm hàng loạt, fill côngthức tớ doubled-click là xong. Sửa sub total tớ có cách ép các dòng nằm kế nhau, cũng làm hàng loạt luôn.
Còn cách của bạn khi thêm bớt dòng cũng phải làm lại mà!

To TigerTiger:
Giả sử số thứ tự (STT) ở A7 thì ô B7 tính thế này: =INT(A7/(25+0.1/B7)
thế là ko lo số lớn nữa
Công thức của bạn bị Circular Reference.
 
Upvote 0
Từ hôm thấy câu hỏi này, tôi nhớ đã đọc bài này ở đâu đó, có điều không nhớ tên bài để tìm...

Hôm nay đã tìm ra, các bạn xem lại thử nha: http://www.giaiphapexcel.com/forum/showthread.php?t=820

Bài viết này có đề cập đến chuyện tạo dòng tổng cộng cuối trang, đồng thời có làm cái "mang sang" cho trang sau luôn.
 
Chỉnh sửa lần cuối bởi điều hành viên:
Upvote 0
Oh... files viết bằng code của bác Đào Việt Cường, nhưng qua Test thử chỉ làm việc tốt cho lần đầu tiên, nếu chèn thêm dòng và chạy lại code thì dòng tổng kết trang không tự xóa đi mà chèn sang trang sau. Vẫn bất cập quá, một ngày nào đó bác Bill sẽ hiểu nỗi khổ này cho phép link công thức trong H&P.
 
Upvote 0
Với lại cũng phải dựa vào điều kiện là các dòng dữ liệu phải cùng độ cao (row Height).
Ngoài ra, cả 2 dòng cộng đó đều là cộng dồn, trong khi dân trong ngành thường có yêu cầu là:
- cuối trang 2 dòng: dòng 1: Cộng trang này : chỉ cộng trong trang
- Dòng 2: Cộng dồn đến ngày ... :Cộng dồn từ đầu báo cáo
- Đầu trang sau: Mang sang: Cộng dồn từ đầu báo cáo.

Còn không thì chỉ cộng từng trang như yêu cầu của Thanhan, khỏi mang sang. Code này nếu cải tiến thêm tí nữa thì tuyêt.
 
Upvote 0
ptm0412 đã viết:
To TigerTiger:
Trích:
Giả sử số thứ tự (STT) ở A7 thì ô B7 tính thế này: =INT(A7/(25+0.1/B7)
thế là ko lo số lớn nữa
Công thức của bạn bị Circular Reference.
CHÍNH XÁC LÀ (tigertiger đã sửa ở post#18):

thế thì làm thế này, chắc ăn lun

Giả sử số thứ tự (STT) ở A7 thì ô B7 tính thế này: =INT(A7/(25+0.1/A7)
thế là ko lo số lớn nữa

P/S sửa:
Sorry nhầmm A7 thành B7 hic, (đã sửa chỗ màu đỏ)

buổi trưa post sau nhìn thấy NHẦM mà cứ bấm vào SỬA bài viết thì GPE đứt không sửa được (tức thật)
 
Lần chỉnh sửa cuối:
Upvote 0
ptm0412 đã viết:
Với lại cũng phải dựa vào điều kiện là các dòng dữ liệu phải cùng độ cao (row Height).
Ngoài ra, cả 2 dòng cộng đó đều là cộng dồn, trong khi dân trong ngành thường có yêu cầu là:
- cuối trang 2 dòng: dòng 1: Cộng trang này : chỉ cộng trong trang
- Dòng 2: Cộng dồn đến ngày ... :Cộng dồn từ đầu báo cáo
- Đầu trang sau: Mang sang: Cộng dồn từ đầu báo cáo.
phamnhukhang đã viết:
Bác ra tay đi...em thấy cái vụ này ứng dụng rất thực tế, rất cần cho những người hay làm báo cáo.
TigerTiger đã phát triển lại CODE của Đào Việt Cường (gần như đổi hoàn toàn)

Giờ cho phép cộng chuyển trang, cộng dồn, rồi tổng quát hóa các trang cho phép ký lun (định nghĩa qua name ở sheet TEMP) - trang cuối có footer khác hẳn
Tigertiger đã thử với chiều cao dòng bất kỳ
Dĩ nhiên là các công thức cộng chuyển đúng với file ví dụ -> file khác thì cần sửa lại tt cột trong code cho hợp lý

PHP:
Option Explicit

Public Sub AddFooterX()
Application.ScreenUpdating = False
Dim myPage As HPageBreak, iP As Integer, iRb As Integer, iRe As Integer, nRbt As Integer, iReE As Integer
Dim nP As Integer

SOKT.Range("A13").Select
SOKT.Cells.RemoveSubtotal

iRb = TEMP.Range("DongBatDau").Value
iReE = TEMP.Range("DongCuoiCung").Value + 1
nRbt = TEMP.Range("CONGHETTRANG").Rows.Count

nP = SOKT.HPageBreaks.Count
SOKT.Range("A" & iReE).Value = 1
SOKT.Range("A" & iReE + 1 & ":A" & (iReE + nP * nRbt)).Formula = "=R[-1]C+1"

nP = SOKT.HPageBreaks.Count
SOKT.Range("A" & iReE & ":A" & (iReE + nP * nRbt)).ClearContents

iP = 0
With SOKT

For iP = 1 To nP + 1
    If iP <= nP Then
        iRe = SOKT.HPageBreaks(iP).Location.Row - nRbt + 1
        TEMP.Range("CONGHETTRANG").Copy
    Else
        iRe = nP * nRbt + TEMP.Range("DongCuoiCung").Value + 1
        TEMP.Range("CongTrangCuoi").Copy
    End If
    Rows(iRe).Insert xlShiftDown, True
    .Range("F" & iRe).Formula = "=SUBTOTAL(9,F" & iRb & ":F" & (iRe - 1) & ")"
    .Range("G" & iRe).Formula = "=SUBTOTAL(9,G" & iRb & ":G" & (iRe - 1) & ")"
    
    .Range("F" & iRe + 1).Formula = "=SUBTOTAL(9,F" & TEMP.Range("DongBatDau").Value & ":F" & (iRe - 1) & ")"
    .Range("G" & iRe + 1).Formula = "=SUBTOTAL(9,G" & TEMP.Range("DongBatDau").Value & ":G" & (iRe - 1) & ")"
    If iP <= nP Then
        .Range("F" & (iRe + nRbt - 1)).Formula = .Range("F" & (iRe + 1)).Formula
        .Range("G" & (iRe + nRbt - 1)).Formula = .Range("G" & (iRe + 1)).Formula
    Else
        .Range("F" & iRe + 2).Formula = "=F" & TEMP.Range("DongBatDau").Value - 1 & "+F" & (iRe + 1) _
                                            & "-G" & TEMP.Range("DongBatDau").Value - 1 & "-G" & (iRe + 1)
        If .Range("F" & iRe + 2).Value < 0 Then
            .Range("G" & iRe + 2).Formula = "=-F" & TEMP.Range("DongBatDau").Value - 1 & "-F" & (iRe + 1) _
                                            & "+G" & TEMP.Range("DongBatDau").Value - 1 & "+G" & (iRe + 1)
            .Range("F" & iRe + 2).ClearContents
        End If
    
    End If
    iRb = iRe + nRbt
Next iP
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
mong nhận được góp ý

xem file gửi kèm - cách nhập thông số trong sheet TEMP
 

File đính kèm

  • EndPageSum_tigertiger.zip
    19.4 KB · Đọc: 706
Upvote 0
Cảm ơn bác vì dòng này.
SOKT.HPageBreaks(iP).Location.Row
Góp ý :
- Việc chèn dòng nên chèn từ dưới chèn lên, như vậy vừa chính xác, code lại ngắn hơn.
- Việc xác định dòng đầu tiên của 1 trang (SOKT.HPageBreaks(iP).Location.Row) thì nên dựa vào chế độ ActiveWindow.SelectedSheets.PrintPreview sẽ chính xác hơn (Kinh nghiệm kiểm chứng+Nhiều người nói vậy)


Thân!
 
Upvote 0
bạn ơi,mình làm thị báo lỗi runtime 424 là gì vậy? Mà cho mình hỏi,bạn send 2 đoạn code thì mình nên sử dụng đoạn code nào.Bài #2 or bài #5
 
Upvote 0
Upvote 0
Cảm ơn Tigertiger rất nhiều về file này. Tuy nhiên mình có câu hỏi nhờ bạn giải đáp. Nếu format của mình ít cột hay nhiều cột hơn thi mình điều chỉnh như thế nào?

Tks again
Tuyenbw
 
Upvote 0
Mình mới tham gia vào diển đàn Giải phap excel, mình thấy rất hữu ích trong công việc của mình. Minh đang áp dụng "endpagesum" vào bảng tính , nhung mình muốn thêm cột hoặc thêm dòng cho phù hợp với cái bảng tinh của minh, các bạn có thể chỉ dẩn cho nminh, cảm on.
 
Upvote 0
Phần này hay quá, nhưng mình muốn lọc ra từng tháng thì làm sao?
Ví dụ khi chọn in phát sinh tháng 1 - thì chỉ có số liệu tháng 1 thôi ....

Hoặc chèn thêm dòng số dư cuối kỳ và đặt công thức Tồn + Thu - Chi
rồi mới ngắt trang, cộng từng trang, xuất ra từng tháng .
Mong các bạn chỉ giúp .xin cảm ơn nhiều .
 
Lần chỉnh sửa cuối:
Upvote 0
Phần này hay quá, nhưng mình muốn lọc ra từng tháng thì làm sao?
Ví dụ khi chọn in phát sinh tháng 1 - thì chỉ có số liệu tháng 1 thôi ....

Hoặc chèn thêm dòng số dư cuối kỳ và đặt công thức Tồn + Thu - Chi
rồi mới ngắt trang, cộng từng trang, xuất ra từng tháng .
Mong các bạn chỉ giúp .xin cảm ơn nhiều .
Coi như dữ liệu ở Sh Data.
Dùng Advance Fliter chỉ lấy theo tháng cần lấy vào Sh In. (bao gồm chuyển số dư).
Dùng lại cách trên theo sh In tạo để ngắt trang.
In.
Nếu đã VBA insert ngắt trang thì thêm Tồn vô tư.
 
Upvote 0
Xuất số liệu ra báo cáo từng tháng

Mình có tập hợp số liệu sổ kho 3 tháng theo file đính kèm.
Mong các bạn giúp mình dùng cách nào để khi chọn tháng 1 thì chỉ in toàn bộ phát sinh (Số đầu kỳ - số nhập - số xuất - số tồn ) trong tháng 1 ra báo cáo và in .
Khi chọn tháng 2 thì cũng ra kết quả tháng 2 và in .
Tháng 3 cũng như vậy .
Nếu chọn được ngắt trang thì quá tốt .
 
Upvote 0
xin nhờ chỉ giúp

Application.Goto Reference:="DONGCUOI"
Application.Goto Reference:="TONGCONG"
....
xem code thấy, sao tìm mãi vùng DONGCUOI",TONGCONG"... cũng không thấy
xin các anh chỉ giúp cho
 

File đính kèm

  • wwww.xls
    26.5 KB · Đọc: 130
Upvote 0
Application.Goto Reference:="DONGCUOI"
Application.Goto Reference:="TONGCONG"
....
xem code thấy, sao tìm mãi vùng DONGCUOI",TONGCONG"... cũng không thấy
xin các anh chỉ giúp cho

Bạn vào Insert \ name define

Bạn sẽ thấy các name trên -> Bạn sẽ hiểu đó là name VÙNG đặt (ở bên Sheet kia)
 
Upvote 0
thêm dòng tổng

file chèn dòng tính tổng cuối trang như EndPageSum_tigertiger thì hay quá
nhưng tính tổng theo từng loại mặt hàng thì làm sao? (vd: lấy cột MaHang làm chuẩn, sau khi sắp xếp thì chèn dòng tổng cuối mỗi loại mặt hàng). em đang cần cái này lắm,
 
Upvote 0
Web KT
Back
Top Bottom