Tim có một trang tính trong đó ô B1 chứa công thức = SUM (A1: A7).

Anh ta muốn sao chép công thức này xuống và có phạm vi tăng lên 7 hàng, để ô B2 sẽ chứa công thức = SUM (A8: A14), ô B3 sẽ chứa = SUM (A15: A21), v.v. Vấn đề là khi anh ta sao chép nó xuống, mỗi “cuối” của phạm vi chỉ được tăng thêm 1 trong đó nó sẽ được tăng thêm 7 để đáp ứng nhu cầu của anh ta. Anh ấy tự hỏi làm thế nào anh ấy có thể làm cho Excel thực hiện tăng dần thích hợp.

Bạn không thể làm cho Excel thực hiện tăng dần thích hợp bằng cách sử dụng sao chép và dán; nó sẽ không làm điều đó. Lý do rất đơn giản – có những lúc khi tăng 1 có ý nghĩa theo quan điểm công thức. Vì Excel không thể đọc được suy nghĩ của bạn (ít nhất là cho đến phiên bản tiếp theo:>)), nó đưa ra giả định rằng nó chỉ tăng thêm 1.

Giải pháp là thay đổi công thức của bạn. Sử dụng một vài hàm trang tính, bạn có thể yêu cầu Excel xây dựng phạm vi mong muốn cho phép tính tổng. Hãy xem xét ví dụ sau về một công thức sẽ cung cấp tổng mong muốn:

=SUM(INDIRECT("A" & (ROW()-1)7+1 & ":A" & (ROW()-1)7+7))

Nếu bạn đặt công thức này vào ô B1, công thức sẽ hoạt động vì nó sẽ xem xét số hàng (do hàm ROW trả về) của hàng chứa công thức. Vì nó ở hàng 1 nên công thức được đánh giá theo cách này bằng Excel:

=SUM(INDIRECT("A" & (ROW()-1)7+1 & ":A" & (ROW()-1)7+7))

=SUM(INDIRECT("A" & (1-1)7+1 & ":A" & (1-1)7+7))

=SUM(INDIRECT("A" & 07+1 & ":A" & 07+7))

=SUM(INDIRECT("A" & 0+1 & ":A" & 0+7))

=SUM(INDIRECT("A" & 1 & ":A" & 7))

=SUM(INDIRECT("A1:A7"))

=SUM(A1:A7)

Kết quả bạn đạt được trong B1 là tổng số bạn mong muốn. (Hàm INDIRECT sử dụng giá trị trong chuỗi như thể đó là một phạm vi thực, đó là giá trị bạn muốn.) Khi bạn sao chép công thức xuống cột, khi số hàng tăng lên, công thức sẽ cung cấp các gia số thích hợp là 7 ở cả hai đầu của phạm vi.

Có những biến thể khác về kỹ thuật này mà bạn có thể sử dụng. Sự khác biệt duy nhất là các biến thể sử dụng các hàm trang tính khác nhau để hoàn thành cùng một nhiệm vụ. Ví dụ: biến thể sau vẫn sử dụng hàm ROW, nhưng cuối cùng dựa vào hàm OFFSET để tính phạm vi mong muốn:

=SUM(OFFSET(A1,((ROW()-1)6),0):OFFSET(A7,((ROW()-1)6),0))

Một cách tiếp cận ngắn hơn sử dụng OFFSET như sau:

=SUM(OFFSET($A$1,ROW()*7-7,0,7,1))

Bất kể cách tiếp cận nào, bạn có thể nói rằng ý tưởng là đưa ra một công thức sử dụng hàng mà công thức xuất hiện để tạo phạm vi mà bạn thực sự muốn. Mỗi ví dụ cho đến nay đều giả định rằng bạn đang bắt đầu ở ô B1. Nếu bạn muốn bắt đầu trong ô B2, thì bạn sẽ cần sửa đổi các công thức để tính cho bất kỳ hàng nào bạn đang bắt đầu. Để cung cấp cho bạn ý tưởng về cách hoạt động của điều này, nếu bạn bắt đầu từ ô B2, thay vào đó, ba công thức được trình bày trong mẹo này sẽ được sửa đổi theo những cách sau:

=SUM(INDIRECT("A" & (ROW()-2)7+2 & ":A" & (ROW()-2)7+8))

=SUM(OFFSET(A2,((ROW()-2)6),0):OFFSET(A8,((ROW()-2)6),0))

=SUM(OFFSET($A$2,(ROW()-1)*7-7,0,7,1))

Bắt đầu ở một vị trí khác và bạn sẽ cần phải sửa đổi thêm đối với công thức bạn chọn sử dụng.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (8385) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: