Zurri AmianG

 

Rumus Perulangan Pada Excel - Beberapa hari yang lalu saya mendapatkan sebuah kasus excel yang untuk pemecahannya membutuhkan sebuah rumus excel yang dapat digunakan untuk menyusun atau membuat nomor seri berulang atau angka urut yang berulang.

Angka seri yang saya maksud disini adalah seperti berikut : 1,2,3,1,2,3,1,2,3,dst. dan 1,1,1,2,2,2,3,3,3,dst. untuk setiap sel excel yang berbeda

Untuk model yang pertama, yakni angka seri berulang 1,2,3,1,2,3,1,2,3, dst. sebenarnya sudah pernah saya bahas sedikit pada salah satu bagian tutorial tentang 6 rumus excel untuk membuat nomor urut otomatis. Silahkan menuju ke halaman tersebut jika anda kesasar ke blog ini untuk mendapatkan rumus tersebut.

Sedangkan untuk model yang kedua yakni : 1,1,1,2,2,2,3,3,3,dst. sebenarnya bisa kita buat dengan Gabungan Fungsi IF dan Fungsi COUNTIF seperti berikut:

=IF(COUNTIF($A$1:A1;A1)=3;A1+1;A1)

Rumus excel diatas ditulis pada sel A2 dan dengan anggapan bahwa di sel A1 kita tulis manual angka 1.

Hanya saja rumus tersebut tidak dapat diterapkan untuk menjadi bagian dari fungsi/rumus excel yang lain, sedangkan kebutuhan saya adalah menggunakan angka berulang tersebut pada sebuah fungsi excel untuk menyusun sebuah rumus excel lainnya.

Lalu bagaimana solusinya?

Pembahasan kali ini akan dibagi dalam dua bagian. Bagian pertama membahas rumus excel untuk menyusun seri berulang model pertama (1,2,3,1,2,3,1,2,3,dst.), sedangkan pembahasan kedua untuk model angka berulang kedua (1,1,1,2,2,2,3,3,3,dst.).

Nomor Urut Berulang Model 1: 1,2,3,1,2,3,1,2,3,dst.

Untuk membuat nomor seri model pertama ini rumus yang bisa digunakan adalah:

=MOD(ROW(A1)-1;3)+1

Kemudian tarik menggunakan autofill atau copy paste ke sel-sel lain dibawahnya. Hasilnya seperti contoh gambar berikut:

Jika yang anda butuhkan nomor seri berulang tersebut tersusun ke samping kanan maka ganti fungsi ROW pada rumus excel tersebut dengan Fungsi COLUMN sehingga rumus excelnya akan seperti berikut:

=MOD(COLUMN(A1)-1;3)+1

Hasilnya akan nampak seperti gambar berikut:

Untuk merubah maksimal angka urut yang dihasilkan ganti angka 3 pada kedua rumus excel diatas dengan angka maksimal yang anda kehendaki.

Fungsi MOD sendiri digunakan untuk mendapatkan sisa dari pembagian sebuah angka. Lebih detailnya bisa anda pelajari pada halaman berikut:

Fungsi MOD Excel: Cara Menghitung Sisa Pembagian di excel

Nomor Urut Berulang Model 2 : 1,1,1,2,2,2,3,3,3,dst.

Untuk angka urut berulang dengan model kedua ini rumus excel yang bisa digunakan adalah:

=INT((ROW(A1)-1)/3)+1

Selanjutnya tinggal ditarik ke bawah. Hasilnya seperti gambar berikut:

Untuk penggunaan seri berulang ke arah kolom (samping kanan) rumus excel yang digunakan :

=INT((COLUMN(A1)-1)/3)+1

Hasil dari rumus excel diatas adalah sebagai beirkut:

Cara kerja kedua rumus diatas sebenarnya sangat simpel, yakni memanfaatkan fungsi ROW dan COLUMN yang biasa digunakan untuk mengetahui nomor baris atau nomor kolom pada lembar excel.

Kemudian melakukan pembulatan ke atas untuk hasil pembagian nomor baris atau kolom dengan jumlah pengulangan untuk masing-masing seri angka. Sehingga Fungsi INT pada kedua rumus tersebut bisa juga anda gantikan dengan fungsi-fungsi sejenis lainnya. Misalnya Fungsi ROUND, ROUNDUP, TRUNC dan lain sebaginya.

Pembahasan tentang cara pembulatan angka pada microsoft excel bisa anda pelajari pada bagian halaman berikut:

Untuk menambah jumlah perulangan untuk masing-masing angka anda tinggal merubah angka 3 pada rumus excel di atas dengan jumlah perulangan yang anda kehendaki.

Saya cukupkan dulu untuk pembahasan tutorial Cara membuat nomor ataua angka berulang kalai ini. Untuk pemanfaatannya saya kembalikan kepada anda masing-masing.

Semoga bermanfaat dan jangan lupa untuk di share agar teman yang lainnya juga ikut mendapatkan manfaatnya.