hàm sum tự nhảy khi chèn dòng đầu hoặc dòng cuối
anh chị trong diễn đàn cho hỏi có cách nào làm cho hàm sum tự co giãn khi chèn thêm dòng trên cùng hoặc cuối cùng của công thức không ạ. giả sử em mặc định sum từ ô a1 nhưng vị trí dưới thì có thể chèn thêm. em muốn biết thêm để làm cho tiện chứ nếu anh chị bảo chèn ở giữa thì em biết rồi
Sum() dòng cuối, và chèn trên dòng SUM thoải mái:
=SUM(OFFSET($A$1,,,ROW()-MATCH("*",$A:$A,),))
Xem file kèm.
hihi ^o^
Em góp vui!
=SUM(A2:(OFFSET(A25,-1,0)))cách này mình đã thử trước đó nhưng không được nhé bạn, nếu offset thì hàm của offset ko thay đổi, ví dụ như chèn thêm thì cell A25 trong công thức offset vẫn là A25 mà không nhảy lên A26
Không được là sao bạn!
Bạn thử Click chuột vào ô tổng rồi chèn thêm dòng, Bạn nhập số liệu vào xem công thức có thay đổi gì không?
Công thức này đúng với yêu cầu bạn chưa?
=SUM(OFFSET($A$1,,,ROW()-MATCH("*",$A:$A,),))
www.giaiphapexcel.com/diendan/threads/h%C3%A0m-sum-t%E1%BB%B1-nh%E1%BA%A3y-khi-ch%C3%A8n-d%C3%B2ng-%C4%91%E1%BA%A7u-ho%E1%BA%B7c-d%C3%B2ng-cu%E1%BB%91i.134144/post-850569
Bây giờ hiểu rồi.
Công thức cho A100
=SUM(OFFSET($A$1;;;ROW()-1))Với công thức này bạn có thể cộng từ A1. Không bắt buộc A1 phải là tiêu đề. Hoặc A1 để trống
————-
Tất nhiên nếu bạn muốn chọn vd. dòng1-dòng 4 rồi Insert thì lúc đó công thức của tôi sai.
Công thức nên là
=SUM(OFFSET(A1;;;ROW()-ROW(A1)))www.giaiphapexcel.com/diendan/threads/h%C3%A0m-sum-t%E1%BB%B1-nh%E1%BA%A3y-khi-ch%C3%A8n-d%C3%B2ng-%C4%91%E1%BA%A7u-ho%E1%BA%B7c-d%C3%B2ng-cu%E1%BB%91i.134144/post-850576
đúng ý mình rôì nhưng mình không hiểu công thức. hàm offset với hàm match bình thường thì hiểu, nhưng nay thấy đặc biệt quá hihi. giải thích giùm mình với
Công thức này không gì đặc biệt đâu bạn! hihi ^o^.
=SUM( OFFSET($A$1,,,ROW()-MATCH("*",$A:$A,),) ) hay
=SUM( OFFSET($A$1,,,ROW()-ROW(A1),) ) (với ROW(A1) phải là hàng của mốc tiêu đề cột, để khi bạn chèn dưới tiêu đề nó vẫn cộng đúng)
đều vận hành giống nhau:
Hàm OFFSET($A$1,,,ROW()-MATCH("*",$A:$A,),) định vị từ ô A1, lấy chiều dài: với phép tính lấy số dòng (hàm ROW()) của hàng tổng cộng bạn đặt công thức Sum() (Vd: nó đang tại dòng 15), trừ đi vị trí dòng của mốc tiêu đề (Vd: mốc tiêu đề đang tại A2, thì MATCH("*",$A:$A,) sẽ trả về số vị trí dòng là 2), như Vd ta có: 15-2=13, tức đứng từ A1, sẽ kéo thêm 13 dòng nữa, tức Offset sẽ thực hiện cho ra Vùng tham chiếu A1:A14 (A14 là ô liền kề trên của dòng Sum()), để cung cấp cho SUM(….). Cho nên nó cho phép bạn insert dòng phía trên Sum() vì lúc nào cũng được cập nhật theo dòng nó đang đứng (hàm ROW()).
Bạn cũng có thể chèn thêm phía trên dòng tiêu đề bằng các dòng trống (không cần cộng) khác mà không ảnh hưởng công thức cộng của Sum().
hihi ^o^
www.giaiphapexcel.com/diendan/threads/h%C3%A0m-sum-t%E1%BB%B1-nh%E1%BA%A3y-khi-ch%C3%A8n-d%C3%B2ng-%C4%91%E1%BA%A7u-ho%E1%BA%B7c-d%C3%B2ng-cu%E1%BB%91i.134144/post-850602
công thức của em đúng không chị iu?
=SUM(A2:(OFFSET(A25,-1,0)))
Công thức =SUM(A2: (OFFSET(A25,-1,))) đúng, lại đẹp nhất!
👏👏👏
hihi ^o^
giờ hiểu rồi hihi. tại lúc đầu hàm match("*",$A:$A) không hiểu nó mang ý nghĩa gì, nếu thay vào hàm row(A1) thì chắc là hiểu luôn rồi.. tks bạn và mọi người nhé
Em góp vui!
=SUM(A2:(OFFSET(A25,-1,0)))sorry bạn, mình hôm qua đã nghĩ ra công thức này nhưng mình làm sai nên mình cứ nghĩ công thức bạn đưa sai. giờ thử lại thấy đúng. qua mình thay vì làm offset ô A25 lại làm A24, ngớ.ngẩn thật. cảm ơn bạn nhé
Thêm cách, chèn dòng trên cùng được luôn.
=SUM(INDIRECT("R2C:R[-1]C";0))www.giaiphapexcel.com/diendan/threads/h%C3%A0m-sum-t%E1%BB%B1-nh%E1%BA%A3y-khi-ch%C3%A8n-d%C3%B2ng-%C4%91%E1%BA%A7u-ho%E1%BA%B7c-d%C3%B2ng-cu%E1%BB%91i.134144/post-850673
Công thức =SUM(A2: (OFFSET(A25,-1,))) đúng, lại đẹp nhất!
Đẹp xấu tùy thuộc vào tiêu chí. Với Rubens cô nào mũm mĩm thì là đẹp. Hoặc với người này là đẹp nhưng với người khác lại xấu. Thời nay cái đẹp lại khác.
Cũng tùy từng trường hợp mà công thức nào tiện hơn.
Nếu có nhiều bảng trên nhiều sheet có cấu trúc như nhau, tức cột như nhau nhưng số dòng dữ liệu khác nhau tuy bắt đầ từ dòng 1 hay 2 thì công thức với A25 không tiện. Sau khi dán công thức vào từng sheet thì phải sửa 25 thành số khác. Cũng trường hợp này thì 2 công thức
=SUM(OFFSET($A$1,,,ROW()-MATCH("*",$A:$A,),))
=SUM(OFFSET($A$1;;;ROW()-1))
có thể dán vào những dòng khác nhau mà không cần sửa.
www.giaiphapexcel.com/diendan/threads/h%C3%A0m-sum-t%E1%BB%B1-nh%E1%BA%A3y-khi-ch%C3%A8n-d%C3%B2ng-%C4%91%E1%BA%A7u-ho%E1%BA%B7c-d%C3%B2ng-cu%E1%BB%91i.134144/post-850769
Ct này sao mình làm thử từ cột B thì lại không được. Dù đã đổi ct lại là từ A thành B. Do các cột mình cần tổng từ các cột phía sau
Thử dùng hàm này:
=SUM(INDIRECT("D1: D"&ROW()-1))
dùng thử công thức này nha bạn, column nào cũng được khỏi sửa
=SUM(INDIRECT(ADDRESS(1,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN())))
Khóa học Power PI – Ứng dung trong Nhân sự
TỔNG QUAN KHÓA HỌC: POWER BI CHO NGÀNH NHÂN SỰ Khóa học Power BI cho Nhân sự được thiết kế dành riêng cho các...
Xem khóa học