Cara Menggunakan Formula Array di Google Sheets

Diterbitkan: 2023-06-10

Pada awal tahun 2023, Google memperkenalkan beberapa fungsi baru untuk Spreadsheet, termasuk delapan fungsi untuk bekerja dengan array. Dengan menggunakan fungsi ini, Anda dapat mengubah larik menjadi baris atau kolom, membuat larik baru dari baris atau kolom, atau menambahkan larik saat ini.

Dengan lebih banyak fleksibilitas untuk bekerja dengan array dan melampaui fungsi dasar ARRAYFORMULA, mari kita lihat cara menggunakan fungsi array ini dengan rumus di Google Sheets.

Daftar isi

    Tips : Beberapa fungsi ini mungkin terlihat tidak asing bagi Anda jika Anda juga menggunakan Microsoft Excel.

    Mengubah Array: TOROW dan TOCOL

    Jika Anda memiliki larik dalam kumpulan data yang ingin diubah menjadi satu baris atau kolom, Anda dapat menggunakan fungsi TOROW dan TOCOL.

    Sintaks untuk setiap fungsi adalah sama, TOROW(array, abaikan, pindai) dan TOCOL(array, abaikan, pindai) di mana hanya argumen pertama yang diperlukan untuk keduanya.

    • Larik : Larik yang ingin Anda ubah, diformat sebagai “A1:D4.”
    • Abaikan : Secara default, tidak ada parameter yang diabaikan (0), tetapi Anda dapat menggunakan 1 untuk mengabaikan kosong, 2 untuk mengabaikan kesalahan, atau 3 untuk mengabaikan kosong dan kesalahan.
    • Pindai : Argumen ini menentukan cara membaca nilai dalam larik. Secara default, fungsi memindai menurut baris atau menggunakan nilai False, tetapi Anda dapat menggunakan True untuk memindai menurut kolom jika diinginkan.

    Mari telusuri beberapa contoh menggunakan fungsi TOROW dan TOCOL serta rumusnya.

    Dalam contoh pertama ini, kita akan mengambil larik A1 hingga C3 dan mengubahnya menjadi baris menggunakan argumen default dengan rumus ini:

    =TOROW(A1:C3)

    Seperti yang Anda lihat, array sekarang dalam satu baris. Karena kita menggunakan argumen pemindaian default, fungsi membaca dari kiri ke kanan (A, D, G), ke bawah, lalu dari kiri ke kanan lagi (B, E, H) hingga selesai—dipindai menurut baris.

    Untuk membaca array berdasarkan kolom, bukan baris, kita dapat menggunakan True untuk argumen scan . Kami akan mengosongkan argumen abaikan . Berikut rumusnya:

    =TOROW(A1:C3,,BENAR)

    Sekarang Anda melihat fungsi membaca array dari atas ke bawah (A, B, C), atas ke bawah (D, E, F), dan atas ke bawah (G, H, I).

    Fungsi TOCOL bekerja dengan cara yang sama tetapi mengubah array menjadi kolom. Menggunakan rentang yang sama, A1 hingga C3, inilah rumus yang menggunakan argumen default:

    =TOKOL(A1:C3)

    Sekali lagi, dengan menggunakan default untuk argumen scan , fungsi membaca dari kiri ke kanan dan memberikan hasil seperti itu.

    Untuk membaca array berdasarkan kolom, bukan baris, masukkan True untuk argumen pemindaian seperti ini:

    =TOKOL(A1:C3,,BENAR)

    Sekarang Anda melihat fungsi membaca array dari atas ke bawah.

    Buat Array Baru Dari Baris atau Kolom: CHOOSEROWS dan CHOOSECOLS

    Anda mungkin ingin membuat array baru dari yang sudah ada. Ini memungkinkan Anda membuat rentang sel baru dengan hanya nilai spesifik dari yang lain. Untuk ini, Anda akan menggunakan fungsi CHOOSEROWS dan CHOOSECOLS Google Sheets.

    Sintaks untuk setiap fungsi serupa, CHOOSEROWS (array, row_num, row_num_opt) dan CHOOSECOLS (array, col_num, col_num_opt), di mana dua argumen pertama diperlukan untuk keduanya.

    • Larik : Larik yang ada, dengan format “A1:D4”.
    • Row_num or Col_num : Nomor baris atau kolom pertama yang ingin Anda kembalikan.
    • Row_num_opt atau Col_num_opt : Angka untuk baris atau kolom tambahan yang ingin Anda kembalikan. Google menyarankan Anda menggunakan angka negatif untuk mengembalikan baris dari bawah ke atas atau kolom dari kanan ke kiri.

    Mari kita lihat beberapa contoh menggunakan CHOOSEROWS dan CHOOSECOLS beserta rumusnya.

    Dalam contoh pertama ini, kita akan menggunakan larik A1 hingga B6. Kami ingin mengembalikan nilai di baris 1, 2, dan 6. Berikut rumusnya:

    =PILIH(A1:B6,1,2,6)

    Seperti yang Anda lihat, kami menerima tiga baris tersebut untuk membuat array baru kami.

    Sebagai contoh lain, kita akan menggunakan array yang sama. Kali ini, kami ingin mengembalikan baris 1, 2, dan 6 tetapi dengan 2 dan 6 dalam urutan terbalik. Anda dapat menggunakan angka positif atau negatif untuk mendapatkan hasil yang sama.

    Menggunakan angka negatif, Anda akan menggunakan rumus ini:

    =PILIH(A1:B6,1,-1,-5)

    Untuk menjelaskan, 1 adalah baris pertama yang dikembalikan, -1 adalah baris kedua yang dikembalikan yang merupakan baris pertama yang dimulai dari bawah, dan -5 adalah baris kelima dari bawah.

    Menggunakan angka positif, Anda akan menggunakan rumus ini untuk mendapatkan hasil yang sama:

    =PILIH(A1:B6,1,6,2)

    Fungsi CHOOSECOLS bekerja dengan cara yang sama, kecuali Anda menggunakannya saat Anda ingin membuat larik baru dari kolom, bukan baris.

    Menggunakan larik A1 hingga D6, kita dapat mengembalikan kolom 1 (kolom A) dan 4 (kolom D) dengan rumus ini:

    =CHOOSECOLS(A1:D6,1,4)

    Sekarang kami memiliki array baru kami hanya dengan dua kolom itu.

    Sebagai contoh lain, kita akan menggunakan larik yang sama dimulai dengan kolom 4. Kemudian kita akan menambahkan kolom 1 dan 2 dengan 2 (kolom B) terlebih dahulu. Anda dapat menggunakan angka positif atau negatif:

    =CHOOSECOLS(A1:D6,4,2,1)

    =CHOOSECOLS(A1:D6,4,-3,-4)

    Seperti yang Anda lihat pada tangkapan layar di atas, dengan rumus di dalam sel daripada Bilah Rumus, kami menerima hasil yang sama dengan menggunakan kedua opsi.

    Catatan : Karena Google menyarankan menggunakan angka negatif untuk membalikkan penempatan hasil, ingatlah ini jika Anda tidak menerima hasil yang benar menggunakan angka positif.

    Bungkus untuk Membuat Array Baru: WRAPROWS dan WRAPCOLS

    Jika Anda ingin membuat array baru dari yang sudah ada tetapi membungkus kolom atau baris dengan sejumlah nilai tertentu di masing-masingnya, Anda dapat menggunakan fungsi WRAPROWS dan WRAPCOLS.

    Sintaks untuk setiap fungsi adalah sama, WRAPROWS (rentang, hitung, pad) dan WRAPCOLS (rentang, hitung, pad), di mana dua argumen pertama diperlukan untuk keduanya.

    • Rentang : Rentang sel yang ada yang ingin Anda gunakan untuk larik, diformat sebagai “A1:D4”.
    • Hitung : Jumlah sel untuk setiap baris atau kolom.
    • Pad : Anda dapat menggunakan argumen ini untuk menempatkan teks atau satu nilai dalam sel kosong. Ini menggantikan kesalahan #N/A yang akan Anda terima untuk sel kosong. Sertakan teks atau nilai dalam tanda kutip.

    Mari telusuri beberapa contoh menggunakan fungsi WRAPROWS dan WRAPCOLS beserta rumusnya.

    Dalam contoh pertama ini, kita akan menggunakan rentang sel A1 hingga E1. Kami akan membuat baris pembungkus array baru dengan tiga nilai di setiap baris. Berikut rumusnya:

    =BUNGKUS(A1:E1,3)

    Seperti yang Anda lihat, kami memiliki larik baru dengan hasil yang benar, tiga nilai di setiap baris. Karena kita memiliki sel kosong dalam larik, kesalahan #N/A ditampilkan. Untuk contoh selanjutnya, kita akan menggunakan argumen pad untuk mengganti kesalahan dengan teks “None.” Berikut rumusnya:

    =WRAPROWS(A1:E1,3,”Tidak Ada”)

    Sekarang, kita dapat melihat sebuah kata alih-alih kesalahan Google Sheets.

    Fungsi WRAPCOLS melakukan hal yang sama dengan membuat larik baru dari rentang sel yang ada, tetapi melakukannya dengan membungkus kolom, bukan baris.

    Di sini, kita akan menggunakan larik yang sama, A1 hingga E3, membungkus kolom dengan tiga nilai di setiap kolom:

    =WRAPCOLS(A1:E1,3)

    Seperti contoh WRAPROWS, kami menerima hasil yang benar tetapi juga error karena sel kosong. Dengan rumus ini, Anda dapat menggunakan argumen pad untuk menambahkan kata “Empty”:

    =WRAPCOLS(A1:E1,3,”Kosong”)

    Array baru ini terlihat jauh lebih baik dengan kata daripada kesalahan.

    Gabungkan untuk Membuat Array Baru: HSTACK dan VSTACK

    Dua fungsi terakhir yang akan kita lihat adalah untuk menambahkan array. Dengan HSTACK dan VSTACK, Anda dapat menambahkan dua rentang sel atau lebih secara bersamaan untuk membentuk satu larik, baik secara horizontal maupun vertikal.

    Sintaks untuk setiap fungsi adalah sama, HSTACK (range1, range2,…) dan VSTACK (range1, range2,…), di mana hanya argumen pertama yang diperlukan. Namun, Anda hampir selalu menggunakan argumen kedua, yang menggabungkan rentang lain dengan yang pertama.

    • Rentang1 : Rentang sel pertama yang ingin Anda gunakan untuk larik, diformat sebagai “A1:D4”.
    • Range2,… : Rentang sel kedua yang ingin Anda tambahkan ke sel pertama untuk membuat larik. Anda dapat menggabungkan lebih dari dua rentang sel.

    Mari kita lihat beberapa contoh menggunakan HSTACK dan VSTACK beserta rumusnya.

    Dalam contoh pertama ini, kami akan menggabungkan rentang A1 hingga D2 dengan A3 hingga D4 menggunakan rumus ini:

    =HSTACK(A1:D2,A3:D4)

    Anda dapat melihat rentang data kami digabungkan untuk membentuk satu larik horizontal.

    Sebagai contoh fungsi VSTACK, kami menggabungkan tiga rentang. Menggunakan rumus berikut, kami akan menggunakan rentang A2 hingga C4, A6 hingga C8, dan A10 hingga C12:

    =VSTACK(A2:C4,A6:C8,A10:C12)

    Sekarang, kami memiliki satu larik dengan semua data kami menggunakan rumus dalam satu sel.

    Memanipulasi Array Dengan Mudah

    Meskipun Anda dapat menggunakan ARRAYFORMULA dalam situasi tertentu, seperti fungsi SUM atau fungsi IF, rumus array Google Sheets tambahan ini dapat menghemat waktu Anda. Mereka membantu Anda mengatur lembar Anda persis seperti yang Anda inginkan dan dengan rumus larik tunggal.

    Untuk tutorial lainnya seperti ini, tetapi dengan fungsi non-array, lihat cara menggunakan fungsi COUNTIF atau SUMIF di Google Sheets.