Helen đã sử dụng một macro để tạo ra một mẫu số đơn giản. Mẫu được chứa trong một cột duy nhất, trông như thế này:

1,1,0,2,2,0,3,3,0,4,4,0,...

Helen đã tự hỏi liệu có cách nào để tạo ra cùng một mẫu bằng cách sử dụng công thức thay vì macro.

Trên thực tế, có một số công thức khác nhau mà bạn có thể sử dụng để đạt được mẫu mong muốn. Một cách là bắt đầu với chuỗi hạt giống của bạn (1, 1, 0) trong các ô A1 đến A3. Sau đó, nhập công thức = A1 + 1 vào ô A4, công thức = A4 vào ô A5 và công thức = A3 vào ô A6. Bây giờ bạn có thể chọn các ô trong A4: A6 và sử dụng điều khiển điền để kéo và điền vào bất kỳ hàng nào bạn cần.

Một cách tiếp cận công thức khác là vẫn đặt chuỗi hạt giống của bạn (1, 1, 0) trong các ô từ A1 đến A3, rồi nhập công thức sau vào ô A4:

=IF(A1<>0,A1+1,0)

Bạn có thể sao chép công thức này xuống bao nhiêu ô nếu cần để lặp lại mẫu mong muốn.

Nếu bạn không muốn sử dụng chuỗi hạt giống (ví dụ: chuỗi sẽ luôn bắt đầu bằng 1, 1, 0), thì có thể sử dụng công thức thẳng bắt đầu bằng ô A1. Một trong các công thức sau sẽ cho kết quả tương tự:

=IF(MOD(ROW(),3)=0,0,INT(ROW()/3)+1)

=(INT(ROW()/3)+1)*(MOD(ROW(),3)<>0)

Các công thức (và nhiều biến thể của các công thức này) kiểm tra hàng mà công thức được định vị, sau đó tìm xem nó nằm ở hàng đầu tiên, thứ hai hay thứ ba của mỗi tập hợp. Dựa trên vị trí này, công thức tính xem nó nên hiển thị “số thiết lập” (1, 2, 3, v.v.) hay giá trị 0.

Nếu mẫu của bạn không bắt đầu ở hàng đầu tiên của trang tính, bạn cần điều chỉnh công thức để tính phần bù từ hàng đầu tiên. Ví dụ: nếu mẫu bắt đầu ở hàng thứ hai (bạn có thể có tiêu đề ở hàng đầu tiên), thì công thức có thể được điều chỉnh theo cách này:

=IF(MOD(ROW()-1,3)=0,0,INT((ROW()-1)/3)+1)

=(INT((ROW()-1)/3)+1)*(MOD(ROW()-1,3)<>0)

Chỉ cần đặt công thức vào hàng thứ hai và sao chép nó xuống, theo yêu cầu. Để điều chỉnh độ lệch cho bất kỳ hàng nào khác, chỉ cần thay đổi giá trị -1 (hai trong số chúng trong mỗi công thức) thành số hàng bạn đã đẩy công thức xuống từ hàng đầu tiên.

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

Mẹo này (11988) áp dụng cho Microsoft Excel 2007, 2010, 2013 và 2016.

Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: