Formula / Google Sheet · June 9, 2022 0

Mencari Nilai Deret Terbanyak di Google Sheets dan Excel (Max Consecutive)

Kasus ini muncul dari pertanyaan yang ada di group. Pertanyannya adalah bagaimana rumus Google Sheets dan Excel digunakan untuk menghitung Deret terbanyak yang sama berturut-turut.

Disini saya sengaja buat di Excel dan di Google sheet sebagai perbandingan rumus yang bisa digunakan.

Untuk contoh kasusnya bisa dilihat digambar dibawah ini:

Rumus Max Consecutive Google Sheet

Cara yang pertama saya buat untuk versi Google Sheet. Alur logika yang saya gunakan adalah mengecek apakah kondisinya TRUE atau FALSE setelah itu digabungkan dengan JOIN.

=ARRAYFORMULA(JOIN("",B3:P3="H"))

rumus diatas akan menghasilkan TRUEFALSETRUETRUEFALSETRUETRUE….. dimana jika isinya H akan TRUE dan jika bukan Akan FALSE. Jika terlalu panjang bisa tambahkan rumus LEFT agar hasilnya menjadi TFTTFFTFT….

Selanjutnya hasil tersebut saya potong atau pisahkan dengan SPLIT dengan pembatas FALSE atau F sehingga menyisakan TRUE saja.

=ARRAYFORMULA(SPLIT(JOIN("",LEFT(B3:P3="H")),"F"))

Langkah terkahir tinggal tambahkan LEN untuk menghitung banyaknya T dan MAX untuk mengambil Jumlah T terbanyak. Sehingga formula akhirnya menjadi seperti ini.

=ARRAYFORMULA(MAX(LEN(SPLIT(JOIN("",LEFT(B3:P3="H")),"F"))))
Rumus Max Consecutive

Cara ke 2 : Dengan Formula FREQUENCY

Untuk cara kedua ini menggunakan rumus FREQUENCY untuk logikanya kurang lebih sama hal pertama yg dilakukan adalah melakukan pengecekan Range dengan IF kemudian Mengganti hasil TRUE dengan Deret Angka hasil dari rumus COLUMN() jika datanya horizontal, jika datanya vertical gunakan ROW().

=ARRAYFORMULA(MAX(FREQUENCY(IF(B3:P3="H",COLUMN(B3:P3)),IF(B3:P3<>"H",COLUMN(B3:P3)))))

Rumus Max Consecutive Excel

Karena ada perbedaan rumus, saya buat dua di versi Excelnya. Untuk yang pertama untuk versi Excel yang sudah support dengan rumus JOIN dan TEXTSPLIT (Excel 365). Rumusnya sama seperti pada Google Sheet hanya ada perbedaan sedikit saja.

=MAX(LEN(TEXTSPLIT(TEXTJOIN("",1,LEFT(B3:P3="H")),"F")))

Untuk cara kedua juga sama menggunakan rumus FREQUENCY hanya saja jika menggunakan Excel dibawah 365 harus menekan CTRL + SHIFT + ENTER (CSE) saat mengeksekusi formula.

Jika deret yang mau dihitung vertical (Kebawah) ganti fungsi COLUMN dengan ROW.

Itulah beberapa formula yang bisa digunakan dalam mencari deret terbesar di google sheet dan Excel. Untuk lebih jelasnya bisa langsung lihat rumus di Share Google sheet dibawah ini.