Tập này cho thấy ba cách để thực hiện tổng số chạy.
Tổng đang chạy, đối với danh sách các giá trị số, là tổng các giá trị từ hàng đầu tiên đến hàng của tổng đang chạy. Cách sử dụng phổ biến của tổng số đang chạy là trong sổ đăng ký sổ séc hoặc bảng kế toán. Có nhiều cách để tạo tổng hai cách đang chạy được mô tả bên dưới.
Kỹ thuật đơn giản nhất là, trên mỗi hàng, thêm tổng số đang chạy từ hàng trên với giá trị trong hàng. Vì vậy, công thức đầu tiên trong hàng 2 là:
=SUM(D1,C2)
Lý do chúng ta sử dụng hàm SUM là vì ở hàng đầu tiên, chúng ta đang xem tiêu đề ở hàng trên. Nếu chúng ta sử dụng công thức đơn giản hơn, trực quan hơn =D1+C2thì lỗi sẽ được tạo ra vì giá trị tiêu đề là văn bản so với số. Điều kỳ diệu là hàm SUM bỏ qua các giá trị văn bản, những giá trị này được thêm vào dưới dạng giá trị 0. Khi công thức được sao chép xuống tất cả các hàng có tổng số đang chạy là mong muốn, các tham chiếu ô sẽ được điều chỉnh cho phù hợp:
Kỹ thuật khác cũng sử dụng hàm SUM nhưng mỗi công thức tính tổng tất cả các giá trị từ hàng đầu tiên đến hàng hiển thị tổng số đang chạy. Trong trường hợp này, chúng tôi sử dụng ký hiệu đô la ($) để làm cho ô đầu tiên trong tham chiếu là tham chiếu tuyệt đối, có nghĩa là nó không được điều chỉnh khi được sao chép:
Cả hai kỹ thuật đều không bị ảnh hưởng bởi việc sắp xếp và xóa hàng, nhưng khi chèn hàng, công thức phải được sao chép vào các hàng mới.
Excel 2007 đã giới thiệu Bảng, đây là sự tái triển khai của Danh sách trong Excel 2003. Bảng đã giới thiệu một số tính năng rất hữu ích cho bảng dữ liệu như định dạng, sắp xếp và lọc. Với sự ra đời của Bảng, chúng tôi cũng đã được cung cấp một cách mới để tham chiếu các phần của Bảng. Kiểu tham chiếu mới này được gọi là tham chiếu có cấu trúc.
Để chuyển đổi ví dụ trên thành Bảng, chúng tôi chọn dữ liệu chúng tôi muốn đưa vào Bảng và nhấn Ctrl + T. Sau khi hiển thị lời nhắc yêu cầu chúng tôi xác nhận phạm vi của Bảng và có hay không có tiêu đề hiện có, Excel sẽ chuyển đổi dữ liệu thành một Bảng được định dạng:
Lưu ý rằng các công thức chúng ta đã nhập trước đó vẫn giữ nguyên.
Một trong những tính năng hữu ích mà Tables cung cấp là định dạng tự động và duy trì công thức khi các hàng được thêm, bớt, sắp xếp và lọc. Chúng tôi sẽ tập trung vào việc duy trì công thức nói riêng và có thể có vấn đề. Để giữ cho các Bảng hoạt động trong khi chúng được thao tác, Excel sử dụng các cột được tính toán là các cột có công thức như cột D trong ví dụ trên. Khi các hàng mới được chèn được thêm vào dưới cùng, Excel sẽ tự động điền các hàng mới bằng công thức “mặc định” cho cột đó. Vấn đề với ví dụ trên là Excel bị nhầm lẫn với các công thức chuẩn và không phải lúc nào cũng xử lý chúng một cách chính xác. Điều này được thể hiện rõ ràng khi các hàng mới được thêm vào cuối Bảng (bằng cách chọn ô dưới cùng bên phải trong Bảng và nhấn TAB):
Sự thiếu hụt này được giải quyết bằng cách sử dụng tham chiếu có cấu trúc mới hơn. Tham chiếu có cấu trúc loại bỏ nhu cầu tham chiếu các ô cụ thể bằng cách sử dụng kiểu tham chiếu A1 hoặc R1C1 và thay vào đó sử dụng tên cột và các từ khóa khác để xác định và tham chiếu các phần của Bảng. Ví dụ: để tạo cùng một công thức tổng đang chạy được sử dụng ở trên nhưng sử dụng tham chiếu có cấu trúc, chúng ta có:
=SUM(INDEX((Sales),1):(@Sales))
Trong ví dụ này, chúng tôi có tham chiếu đến tên cột, “Bán hàng”, cùng với dấu tại (@) để tham chiếu đến hàng trong cột chứa công thức, còn được gọi là hàng hiện tại.
Để triển khai ví dụ đầu tiên ở trên, trong đó chúng tôi đã thêm tổng giá trị đang chạy ở hàng trước vào số tiền bán hàng trong hàng hiện tại, bạn có thể sử dụng hàm OFFSET:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Nếu số tiền được sử dụng để tính tổng số tiền đang chạy nằm trong hai cột, ví dụ một cột cho “Khoản nợ” và một cột cho “Tín dụng”, thì công thức là:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Ở đây, chúng tôi đang sử dụng hàm INDEX để định vị các ô Ghi có và Ghi nợ của hàng đầu tiên, đồng thời tính tổng toàn bộ cột lên đến và bao gồm các giá trị của hàng hiện tại. Tổng số đang chạy là tổng của tất cả các khoản tín dụng lên đến và bao gồm hàng hiện tại trừ đi tổng của tất cả các khoản ghi nợ cho đến và bao gồm cả hàng hiện tại.
Để biết thêm thông tin về các tham chiếu có cấu trúc nói riêng và các Bảng nói chung, chúng tôi giới thiệu cuốn sách Bảng Excel: Hướng dẫn Hoàn chỉnh về Tạo, Sử dụng và Tự động hóa Danh sách và Bảng của Zack Barresse và Kevin Jones.
Khi tôi yêu cầu độc giả bình chọn cho mẹo yêu thích của họ, các bảng đã được phổ biến. Cảm ơn Peter Albert, Snorre Eikeland, Nancy Federice, Colin Michael, James E. Moede, Keyur Patel và Paul Peton đã đề xuất tính năng này. Peter Albert đã viết Mẹo thưởng Tài liệu tham khảo có thể đọc được. Zack Barresse đã viết mẹo tiền thưởng Running Totals. Bốn độc giả đã đề xuất sử dụng OFFSET để tạo phạm vi mở rộng cho biểu đồ động: Charley Baak, Don Knowles, Francis Logan và Cecelia Rieb. Các bảng hiện làm điều tương tự trong hầu hết các trường hợp.
Xem video
- Tập này chỉ ra ba cách để thực hiện tổng số chạy
- Phương thức đầu tiên có công thức ở Hàng 2 khác với tất cả các hàng khác
- Phương pháp đầu tiên là = Trái ở hàng 2 và = Trái + Lên ở hàng 3 đến N
- Nếu bạn cố gắng sử dụng cùng một công thức, bạn sẽ gặp lỗi # Giá trị với = Tổng + Số
- Phương pháp 2 sử dụng
=SUM(Up,Left)hoặc=SUM(Previous Total,This Row Amount) - SUM bỏ qua Văn bản để bạn không gặp lỗi VALUE
- Phương pháp 3 sử dụng phạm vi mở rộng:
=SUM(B$2:B2) - Mở rộng phạm vi rất tuyệt nhưng chúng chậm
- Đọc sách trắng của Charles Williams về Tốc độ Công thức Excel
- Phương pháp thứ ba là một vấn đề khi bạn sử dụng Ctrl + T và thêm các hàng mới
- Excel không thể tìm ra cách viết công thức
- Các giải pháp thay thế yêu cầu một số kiến thức về tham chiếu có cấu trúc trong Bảng
- Giải pháp 1 là chậm
=SUM(INDEX((Qty),1):(@Qty)) - Cách giải quyết 2 là dễ thay đổi
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) đề cập đến Qty trên hàng này
- (Qty) đề cập đến tất cả các giá trị Qty
Bản ghi video
Học Excel cho Podcast, Tập 2004 - Tổng số đang chạy
Tôi sẽ podcasting toàn bộ cuốn sách này. Nhấp vào Tôi ở góc trên cùng bên phải để đăng ký.
Chào mừng bạn quay trở lại mạng truyền hình tế bào thần bí. Tôi là Bill Jelen. Bây giờ chủ đề này trong cuốn sách, tôi đã được đóng góp bởi người bạn của tôi Zach Parise. Nói về bảng Excel, Zach là chuyên gia hàng đầu thế giới về bảng Excel. Anh ấy đã viết một cuốn sách về bảng Excel, nhưng trước tiên hãy nói về việc chạy các tổng không có trong bảng.
Vì vậy, khi tôi nghĩ về việc chạy tổng số, có ba cách khác nhau để thực hiện tổng số đang chạy và cách mà tôi luôn bắt đầu là ở hàng đầu tiên bạn vừa nói, hãy mang lại giá trị. Vì vậy, bằng bất cứ điều gì bên trái của tôi. Được rồi, định dạng này ở đây chỉ là = B2. Đây là tất cả văn bản công thức ở đây ở góc bên phải, vì vậy bạn đang nhìn thấy những gì chúng tôi đang sử dụng và sau đó từ đó trở đi, đó là một công thức nhỏ đơn giản bằng giá trị trước đó, cộng với giá trị hiện tại bên phải và sao chép nó xuống , nhưng bạn biết đấy, chúng tôi gặp vấn đề này rằng nó yêu cầu hai công thức khác nhau và bạn biết trong một tình huống hoàn hảo, bạn có cùng một công thức hoàn toàn giống nhau và lý do chúng tôi phải có một công thức khác ở hàng đầu tiên là rằng khi bạn thử và thêm bằng 7 cộng với tổng từ thì đó là lỗi giá trị,nhưng công nhân tuyệt vời ở đây, là không chỉ sử dụng trái cộng lên, mà sử dụng = (SUM) của giá trị trước đó cộng với số lượng trong hàng này, và thấy một số đủ xa để bỏ qua văn bản. Đúng để cho phép cùng một công thức. tất cả các con đường xuống.
Được rồi, đó là khi tôi bắt đầu sử dụng Excel, tôi đang sử dụng nó và sau đó tôi phát hiện ra phạm vi mở rộng, phạm vi mở rộng cho biết chúng ta sẽ thực hiện L $ 2: L2 và điều xảy ra là điều này luôn bắt đầu ở hàng 2, nhưng sau đó nó sẽ đi xuống hàng hiện tại. Vì vậy, khi bạn xem cách này hoạt động như thế nào khi nó được sao chép, chúng tôi luôn bắt đầu hàng 2, nhưng chúng tôi đi xuống hàng hiện tại và đây trở thành phương pháp yêu thích của tôi. Tôi giống như, ồ, cái này phức tạp hơn nhiều và khi chúng ta vào Tùy chọn Excel, hãy chuyển đến Tab Công thức và chọn R1C1 trong Kiểu tham chiếu. Được rồi, R1C1, tất cả các công thức này hoàn toàn giống nhau. Tôi không biết liệu bạn có hiểu về R1C1 hay không, chỉ cần biết rằng chúng ta có các công thức R1C1 giống hệt nhau.
Hãy quay trở lại. Vì vậy, phương pháp này ở đây là phương pháp mà tôi thích, cho đến khi Charles Williams, một MBP Excel từ Anh, người có một bài báo đáng kinh ngạc về tốc độ công thức, tốc độ công thức Excel, hoàn toàn gỡ bỏ phương pháp này. Phương pháp này, giả sử bạn có 10.000 hàng, mỗi công thức đơn lẻ đang xem xét hai tham chiếu. Vì vậy, bạn đang xem 20.000 tham chiếu, nhưng cái này, cái này nhìn hai, cái này nhìn ba, cái này nhìn bốn, cái này xem năm và cái cuối cùng đang xem 10.000 tham chiếu, và nó chậm hơn khủng khiếp và vì vậy tôi đã ngừng sử dụng phương pháp này.
Sau đó, tôi tiếp tục đọc Zack trong cuốn sách của Kevin Jones về bảng Excel và tôi phát hiện ra một vấn đề khác với phương pháp này. Vì vậy, một trong những tính năng hữu ích mà bảng cung cấp, là 'định dạng tự động và duy trì công thức các hàng được thêm, xóa, sắp xếp và lọc'. Được rồi, đó là trích dẫn từ cuốn sách của anh ấy. Và để thêm một hàng vào bảng, bạn chỉ cần đi đến ô cuối cùng trên bảng và nhấn tab. Vì vậy, mọi thứ đang hoạt động ở đây. Chúng tôi giảm xuống 70, điều đó thật tuyệt vời và sau đó là A104 và tôi sẽ đưa vào 100 ở đây. Được rồi, vì vậy 70 sẽ thay đổi thành 170 và nó đã làm, nhưng 70 này không nên thay đổi chút nào. Được rồi, 68 + 2 không phải là 170. Tôi sẽ làm lại. A 104 và đặt một trăm khác vào ô cuối cùng là đúng. Hai điều này không đúng. Được rồi, vì vậy chúng tôi có một số tình huống kỳ lạ mà nếu bạn 'đang sử dụng công thức này và bạn chuyển đổi sang bảng, bạn bắt đầu thêm hàng, tổng số đang chạy sẽ không hoạt động. Thực hư thế nào?
Được rồi, vì vậy Zack đưa ra hai cách giải quyết và cả hai đều yêu cầu một chút kiến thức về cách hoạt động của các tham chiếu cấu trúc. Chúng tôi sẽ có một cột mới ở đây và nếu tôi muốn tính số lượng, số lượng bằng nhau, đúng vậy, vì vậy = (@ Qty) cho biết số lượng trong hàng này. Thật tuyệt, có một loại tham chiếu khác mà chúng ta sử dụng Qty mà không có @. Kiểm tra điều này. Vì vậy, = SUM (INDEX ((Qty), 1: (@ Qty)) có nghĩa là tất cả các đại lượng và chúng ta sẽ nói rằng chúng ta muốn SUM từ số lượng đầu tiên, vì vậy (INDEX ((Qty), 1 cho biết giá trị đầu tiên ở đây, xuống đến số lượng hàng hiện tại và điều này đang sử dụng một phiên bản chỉ mục thực sự đặc biệt, khi chỉ mục được theo sau bởi dấu hai chấm, nó thực sự thay đổi thành tham chiếu ô. Được rồi, cách giải quyết này rất tiếc vi phạm quy tắc Charles Williams của, chúng tôi 'sẽ phải xem xét từng tham chiếu đơn lẻ, và vì vậy khi bạn nhận được 10.000 hàng, điều này sẽ diễn ra thực sự, rất chậm.
Zach có một giải pháp khác không vi phạm vấn đề Charles Williams, nhưng đó là sử dụng OFFSET đáng sợ. OFFSET là một hàm dễ bay hơi vì vậy mỗi khi bạn tính toán điều gì đó, OFFSET sẽ tính toán lại và mọi thứ từ OFFSET sẽ được tính toán lại. Đó chỉ là một cách tuyệt vời để hoàn thiện, hoàn toàn chỉnh sửa các công thức của bạn và điều này đang làm, nó đang nói, chúng tôi đang lấy tổng số từ hàng này, tăng lên một hàng, trên không cột và vì vậy những gì đang làm là: lấy tổng số từ hàng trước và sau đó chúng tôi thêm vào đó số lượng từ hàng này. Được rồi, vì vậy, bây giờ tất cả đều đang xem xét hai tham chiếu mỗi lần, nhưng tiếc là OFFSET đang giới thiệu các hàm dễ thay đổi.
Chà, bạn đã có nó, nhiều hơn những gì bạn muốn biết về Running Totals. Tôi đoán ý kiến cuối cùng của tôi ở đây là sử dụng phương pháp này, bởi vì nó chỉ có hai. Tất cả cùng một công thức và tham chiếu bảng có cấu trúc của bạn sẽ hoạt động.
Để biết về khám phá này và 39 mẹo thực sự hay khác, hãy xem cuốn sách XL này, 40 mẹo Excel hay nhất mọi thời đại.
Tóm tắt cho tập này, chúng tôi đã nói về ba cách để thực hiện tổng số chạy. Phương thức đầu tiên có một công thức, hàng 2, khác với tất cả các hàng khác. Nó bằng nhau bên trái ở hàng 2 và sau đó bằng bên trái cộng lên ở hàng 3 đến N, nhưng nếu bạn thử và chỉ sử dụng cùng một công thức đó, bên trái bằng nhau cộng lên, xuống dưới, bạn sẽ gặp lỗi #Value như thế nào . So = SUM (Lên, Trái), là tổng số trước đó, cộng với lộ trình này, hoạt động tốt, không có Lỗi giá trị và sau đó là phạm vi mở rộng mà tôi yêu thích. Chúng thật tuyệt, nhưng cho đến khi tôi đọc sách trắng của Charles Williams về biểu mẫu tốc độ của Excel. Sau đó, tôi bắt đầu ghét những tài liệu tham khảo mở rộng này. Nó cũng có vấn đề khi bạn sử dụng CTRL T và thêm các hàng mới. Excel không thể tìm ra cách mở rộng công thức đó, cách thêm hàng mới. Tôi thích mẹo này, hãy chuyển đến ô cuối cùng trong bảng và nhấn Tab,điều đó sẽ thêm một hàng mới và sau đó chúng ta đã nói về một số tham chiếu có cấu trúc, nơi chúng ta đang sử dụng số lượng trong hàng này và sau đó là tất cả các đại lượng. = SUM (OFFSET ((@ Tổng), - 1,00, (@ Qty)).
Được rồi, tôi muốn cảm ơn Zach vì đã đóng góp mẹo đó. Tôi muốn cảm ơn bạn đã ghé qua. Chúng tôi sẽ gặp bạn lần sau cho một netcast khác từ.
Tải tập tin
Tải xuống tệp mẫu tại đây: Podcast2004.xlsx
