Công thức tăng Excel mỗi x hàng

ID sản phẩm, số sê-ri và các số tham chiếu khác yêu cầu một mã định danh duy nhất để tránh nhầm lẫn khi tham chiếu một sản phẩm hoặc điểm dữ liệu cụ thể. Mặc dù bạn có thể nhập các số duy nhất cho mỗi mục theo cách thủ công nhưng làm như vậy rất tẻ nhạt và tốn nhiều thời gian đối với các danh sách dài. Thay vào đó, hãy liên tục tăng một số trong Microsoft Excel để tạo các số nhận dạng duy nhất này

Phương pháp công thức

1. Cách rõ ràng nhất để tăng một số trong Excel là thêm một giá trị cho nó. Bắt đầu với bất kỳ giá trị nào trong ô A1 và nhập "=A1+1" vào ô A2 để tăng giá trị bắt đầu lên một. Sao chép công thức trong A2 xuống phần còn lại của cột để liên tục tăng số trước. Điều này tạo ra một danh sách dài các số nhận dạng duy nhất. Bạn có thể sử dụng bất kỳ số nào để tăng giá trị, chẳng hạn như thay đổi công thức thành "=A1+567," để tạo một mẫu ít rõ ràng hơn

Tính năng gia tăng

1. Microsoft Excel vốn đã cung cấp một hệ thống đánh số để tự động tạo một dãy số tăng dần. Nhập bất kỳ giá trị bắt đầu nào vào ô A1. Nhập giá trị tiếp theo vào ô A2 để thiết lập mẫu. Chọn hai ô đó và kéo thanh điều khiển điền dưới cùng xuống cột để tạo một chuỗi số gia tăng. Ví dụ: nhập 12 và 24 vào ô A1 và A2 sẽ tạo chuỗi 12, 24, 36, 48, 60 khi sao chép xuống ô A5

Sắp xếp các số tăng dần

1. Nếu bạn cần sắp xếp dữ liệu, hãy chọn tính năng gia tăng của Excel. Phương pháp công thức hoạt động tuyệt vời. Tuy nhiên, nếu bạn thay đổi thứ tự của các ô, công thức cũng thay đổi theo. Ngược lại, tính năng gia tăng của Excel tránh các công thức và nhập giá trị gia tăng thực tế vào mỗi ô. Những con số này không thay đổi, ngay cả khi bạn sắp xếp lại danh sách

Dán giá trị

1. Nếu bạn không muốn thay đổi các giá trị gia tăng trong công thức, hãy thay thế các công thức bằng các giá trị mà chúng tạo ra. Chọn các ô bạn muốn tạo hằng số và sao chép chúng. Nhấp chuột phải vào các ô đã chọn và nhấn "V" trên bàn phím của bạn để thay thế các công thức bằng các giá trị thực, không đổi

   Trong ví dụ này, mục tiêu là sao chép mọi giá trị thứ n từ cột B, trong đó n là một biến có thể thay đổi khi cần

   Trong Excel, rất khó để tạo các công thức bỏ qua các hàng theo một mẫu nhất định, vì các tham chiếu trong công thức sẽ tự động thay đổi khi công thức được sao chép theo gia số 1 bước giữa các ô. Tuy nhiên, với một chút công việc, bạn có thể xây dựng các tham chiếu công thức tuân theo các mẫu cụ thể. Trong ví dụ này, chúng tôi đang sử dụng hàm OFFSET, được thiết kế để tạo tham chiếu đến các ô hoặc phạm vi ô khác, dựa trên điểm bắt đầu hoặc gốc tọa độ đã cho.

   bắt đầu từ n

   Trong ví dụ được hiển thị, công thức trong D5, được sao chép xuống, là

   =OFFSET($B$5,ROW(D1)*3-1,0)
   

   sao chép mọi giá trị thứ ba từ phạm vi B5. B59, bắt đầu từ B7, là ô thứ ba trong phạm vi. Điểm bắt đầu bên trong hàm OFFSET là đối số tham chiếu, được cung cấp dưới dạng tham chiếu tuyệt đối

   =OFFSET($B$5
   

   Tham chiếu đến B5 bị khóa để nó không thay đổi khi công thức được sao chép xuống. Đối số tiếp theo là các hàng, cho biết độ lệch hàng mong muốn từ tham chiếu bắt đầu. Thay vì một số được mã hóa cứng thông thường, các hàng được cung cấp dưới dạng một biểu thức tính toán phần bù được yêu cầu

   ROW(D1)*3)-1 // calculate rows offset
   

   Đây là nơi n được cung cấp dưới dạng 3, để sao chép mọi giá trị thứ ba. Ở đây, hàm ROW được sử dụng để lấy số hàng cho ô D1. Chúng tôi bắt đầu với D1, vì chúng tôi muốn bắt đầu với 1 cho giá trị đầu tiên. Khi công thức được sao chép xuống cột, giá trị do ROW trả về sẽ tăng thêm 1 vì tham chiếu đến D1 là tương đối. Kết quả này từ ROW được nhân với n, đây là yếu tố tạo ra mẫu "mọi thứ n", trong trường hợp này là "mọi thứ 3". Khi công thức được sao chép xuống, biểu thức được đánh giá như thế này

   ROW(D1)*3-1 // returns 2
   ROW(D2)*3-1 // returns 5
   ROW(D3)*3-1 // returns 8
   

   Những con số này có thể trông kỳ lạ đối với bạn trong ngữ cảnh của "mọi giá trị thứ 3" nhưng hãy nhớ rằng đây là một phần bù, bắt đầu bằng ô B5. Lý do chúng tôi trừ 1 là vì hàm OFFSET không bao gồm ô tham chiếu khi đối số rows được áp dụng. Nói cách khác, bù một hàng từ A1 trả về A2

   =OFFSET(A1,1,0) // returns A2
   

   Trừ 1 có tính đến hành vi này

   Cuối cùng, đối số cột được cung cấp bằng không (0), vì chúng tôi không muốn bất kỳ phần bù cột nào; . Khi công thức được sao chép xuống, nó sẽ trả về các tham chiếu bắt buộc

   =OFFSET($B$5,ROW(D1)*3-1,0) // returns B7
   =OFFSET($B$5,ROW(D2)*3-1,0) // returns B10
   =OFFSET($B$5,ROW(D3)*3-1,0) // returns B13
   

   Số n có thể được thay đổi khi cần thiết. Ví dụ: nếu n được thay đổi thành 5, công thức sẽ nhận mọi giá trị thứ 5

   bắt đầu từ 1

   Để bắt đầu sao chép ở hàng đầu tiên trong một phạm vi nhất định, sau đó làm theo mẫu thứ n sau đó, bạn có thể điều chỉnh công thức như sau

   =OFFSET($B$5,(ROW(A1)-1)*n,0)
   

   Trong phiên bản này, chúng tôi trừ 1 trực tiếp từ kết quả từ hàm ROW. Điều này "bỏ không" trường hợp đầu tiên của các hàng, để OFFSET trả về một tham chiếu đến ô hiện tại. Công thức trong ô F5 sử dụng phương pháp này