6 Rumus Excel Untuk Membuat Nomor Urut/ Penomoran Otomatis
Rumus Nomor Urut Otomatis - Sebelumnya saya sdh membahas tentang 4 cara membuat nomor urut otomatis pada excel. Artikel tutorial excel kali ini adalah kelanjutan dari artikel tersebut. Sebelum mempelajari lebih lanjut tutorial ini, disarankan anda untuk membaca artikel tersebut.
Jika anda membutuhkan rumus excel untuk membuat nomor urut, tentunya anda punya sedikit masalah dengan pembuatan nomor urut pada excel, pada umumnya karena ingin membuat penomoran excel berdasarkan kriteria-kriteria tertentu.
Jika hanya sekedar membuat nomor urut saja bisa kita lakukan dengan relatif mudah sbgaimana saya jelaskan pada artikel sebelumnya. Baik dengan menulis secara manual, menggunakan fungsi autofill maupun dengan memanfaatkan fitur menu FILL yg sdh disediakan oleh Microsoft exel.
Nah kali ini saya akan jelaskan beberapa teknik penggunaan rumus-rumus excel untuk membuat nomor urut sesuai dengan kondisi atau syarat tertentu.
Barangkali salah satunya adalah yg anda butuhkan, mengingat kebutuhan penomoran dengan rumus excel akan sangat bervariasi sesuai kebutuhan masing-masing. Apabila rumus penomoran yg anda butuhkan tidak ada, jangan sungkan-sungkan untuk menyampaikannya pada kolom komentar. Siapa tahu saya bisa membantu memecahkan permasalahan anda.
**File contoh pembahasan bisa anda download pada bagian akhir pembahasan.
Membuat Nomor Urut Otomatis Berdasarkan Isi Kolom Lain Dengan Fungsi IF Dan Fungsi ROW Excel
Kriteria pertama adalah bahwa nomor urut hanya tampil jika kolom tertentu yg satu baris dengan sel penomoran ada isinya. Untuk kasus yg sprti ini kita membutuhkan fungsi IF Excel.
Perhatikan & slahkan langsung dicoba pada lembar kerja excel anda sprti nampak pada gambar brkut:
Dari gambar diatas kita ingin jika kolom NAMA ada isinya maka kolom NO urut akan otomatis terisi.
Untuk mengatasi hal tersebut tuliskan rumus excel dibawah ini pada sel A2, kemudian tarik atau copy paste ke bawah sampai sel tertentu yg anda inginkan misalnya dlm contoh ini sampai sel A11.
=IF(B2="";"";ROW()-1)Dari rumus diatas nomor urut akan muncul jika kolom nama pada sel B2 & seterusnya kebawah sampai B11 akan terisi jika kolom nama tersebut tidak kosong/empty ("").
Penomoran dengan rumus tersebut menggunakan fungsi ROW yg digunakan untuk mengetahui posisi baris. Karena baris pertama penomoran ada pada posisi baris ke-2 maka saya kurangi 1 (satu) agar nomor pertama tersebut tertulis 1 (satu) sehingga sesuai dengan urutan yg semestinya.
Jika nomor pertama tidak pada baris kedua maka sesuaikan kodisi kolom yg di periksa (B2) & pengurangan fungsi ROW()-1 apda rumus tersebut. misal jika nomor pertama ada pada baris ke-10 maka rumusnya sesuaikan menjadi:
=IF(B10="";"";ROW()-9)Seperti yg terlihat, dengan menggunakan rumus excel diatas masih muncul sedikit masalah. yakni nomor urutnya masih statis menyesuaikan nomor barisnya sehingga jika ada sel kosong pada kolom nama dibagian tengah maka nomor urutnya tidak lagi menjadi urut. Perhatikan lagi gambar diatas untuk lebih jelasnya.
Lah, nomor urut kog tidak urut? @#$%^&
Jika anda menghendaki nomor baris yg dinamis maka gunakan rumus penomoran excel selanjutnya.
Membuat Nomor Urut Otomatis Berdasarkan Isi Kolom Lain Dengan Fungsi IF Dan COUNTA Excel
Kondisi kedua masih sprti pada kriteria pertama, yakni nomor baris hanya dimunculkan jika kolom tertentu ada isinya. namun penomorannya menyesuikan jumlah isi dari kolom yg diperiksa. Sehingga nomor urutnya tidak lompat-lompat jika ada sel pada kolom nama yg kosong.
Untuk jenis penomoran dengan syarat sprti hal tersebut masukkan rumus excel brkut pada sel A2 lalu copy paste atau tarik kebawah sampai sel tertentu yg anda inginkan.
Pada contoh ini rumus ini saya copy paste sampai sel A11.
=IF(B2="";"";COUNTA($B$2:B2))Dari rumus diatas yg perlu anda perhatikan adalah penggunaan tanda $. Pada kasus ini kita menggunakan referensi sel semi absolut.
Dengan menggunakan rumus diatas maka jika sel-sel pada kolom nama ada yg kosong atau anda hapus maka penomorannya akan menyesuaikan secara otomatis.
Jika anda merasa perlu untuk mndlami fungsi COUNTA slahkan baca pembahasan fungsi tersebut pada artikel sebelumnya.
Rumus excel di atas tidak akan berlaku jika kolom B pada tabel tersebut dihasilkan dari sebuah rumus excel lain. Jika kolom B berisi sebuah rumus excel maka gunakan rumus brkut pada kolom A
=IF(B2="";"";COUNTIF($B$2:B2;"?*"))Pada rumus ini kita menggunakan Rumus Countif untuk mendeteksi a&ya 1 karakter atau lebih pada kolom B.
Rumus Membuat Nomor Berulang Maksimal 5 (Lima)
Pada kasus-kasus penomoran excel tertentu terka&g kita membutuhkan nomor urut berulang. Mislanya anda menginginkan nomor urut hanya 1-5 saja & berulang sampai beberapa kali.
Untuk kasus penomoran semacam ini gunakan rumus excel brkut:
=MOD(ROW()-2;5)+1
Pada rumus excel tersebut sesuaikan angka -2 dengan posisi baris pertama penomoran. Jika menghendaki perulangan 10 maka ganti angka 5 menjadi 10.
Misal jika penomoran pertama ada pada sel A10 & anda mengehendaki perulangan 10 maka sesuaikan rumus menjadi sprti ini:
=MOD(ROW()-10;10)+1Mudah bukan?
Untuk membuat nomor berulang 1,1,1,2,2,2,3,3,3, dst. bisa anda pelajari pada laman ini : Cara Membuat Nomor Seri Berulang Dengan Rumus Excel
Rumus Membuat Nomor Urut Berulang Jika Kolom Acuan Berbeda
Kasus penomoran lain yg sering dibutuhkan adalah nomor urut akan berulang dari nomor 1 jika data pada sel acuan berbeda dari data sebelumnya. jika sama maka akan melanjutkan nomor urut yg ada.
Untuk kasus sprti diatas rumus penomoran excel yg digunakan adalah sprti brkut:
=IF(B2=B1;A1+1;1)Tulis rumus excel diatas pada sel A2, lalu kopi paste ke bawah sesuai yg dibutuhkan.
Jika diperhatikan pada baris ke-10 penomoran ternyata berulang juga meskipun data buah mangga sebelumnya sdh ada. Jika anda ingin penomoran untuk data yg sama melanjutkan penomoran yg sdh ada gunakan cara penomoran excel brkutnya.
Rumus Membuat Nomor Urut Berlanjut Untuk Masing-masing Data Acuan
Jika anda menghendaki penomoran berbeda untuk masing-masing data & terus berlanjut jika data tersebut muncul lagi maka anda bisa memanfaatkan fungsi Countif untuk kasus penomoran excel semacam ini.
Untuk data diatas, pada sel A2 tuliskan rumus excel brkut
=COUNTIF($B$2:B2;B2)Selain dengan fungsi Countif, penomoran model bisa juga anda buat dengan fungsi Sumproduct brkut:
=SUMPRODUCT(--($B$2:B2=B2))Perhatikan bahwa pada rumus diatas menggunakan referensi sel semi absolut sprti penomroan pada bagian B yg menggunakan fungsi COUNTA.
Rumus Membuat Nomor Urut Gabungan Dengan Teks
Untuk keperluan tertentu terka&g kita menghendaki nomor urut gabungan. Yakni perpaduan antara teks & nomor urut. Biasanya tujuannya sebenarnya bukan untuk nomor urut meliankan sbgai kode unik tertentu untuk membedakan antar record data.
Cara membuat nomor urut gabungan teks ini sebenarnya sangat mudah yg anda butuhkan hanya operator teks &. Dalam contoh ini saya ingin nomor urut adalah gabungan antara teks "ABCD" & nomor urut. Maka rumus excel yg dipakai adalah.
="ABCD"&ROW()-1
Sekali lagi karena menggunakan fungsi ROW maka ROW()-1 pada rumus tersebut harus anda sesuaikan dengan letak atau posisi pertama penomoran.
Jika menghendaki kondisi yg lain misal sprti kondisi pertama maka sesuaikan saja sprti rumus brkut:
=IF(B2="";"";"ABCD"&ROW()-1)
Jika sprti kondisi B maka sesuaikan sprti brkut.
=IF(B2="";"";"ABCD"&COUNTA($B$2:B2))
Untuk penggabungan teks sendiri sdh ada 2 pembahasan yg bisa Anda pelajari pada halaman brkut:
Nah saya kira 6 rumus excel untuk membuat nomor urut otomatis ini sdh mencukupi.
Untuk pembahasan masing-masing fungsi excel yg saya gunakan untuk menyusun rumus excel pada artikel ini sdh saya bahas detailnya pada artikel-artikel belajar excel sebelumnya.
Untuk file contoh untuk pembahasan kali ini tersedia pada link di bawah ini :
LINK DOWNLOAD TERKUNCI.
Silahkan SHARE untuk membuka kunci!
Semoga bermanfaat.
Jangan lupa Subscribe atau berlangganan via email atau join via sosial media kmi untuk update artikel-artikel belajar excel yg lain. Sumber https://www.kelasexcel.id
Belum ada Komentar untuk "6 Rumus Excel Untuk Membuat Nomor Urut/ Penomoran Otomatis"
Posting Komentar