Array Constrain Excel: Batasi Baris dan Kolom

=TAKE(
    FILTER('P&L'!C5:O250,'P&L'!B5:B250="Base Case"),
    12,
    13
)

Formula ini mengembalikan 12 baris pertama yang memenuhi kriteria dan 13 kolom dari tab P&L. Inilah fungsi Excel yang paling mendekati ARRAY_CONSTRAIN di Google Sheets, meskipun sintaks dan penanganan kasus khususnya tidak sepenuhnya sama.

Per Juli 2026, Microsoft menyediakan TAKE untuk Microsoft 365, Excel for the web, Excel for Mac, dan Excel 2024. Workbook yang harus tetap dapat digunakan di Excel 2021 atau instalasi desktop yang lebih lama masih memerlukan alternatif berbasis INDEX.

Apa fungsi Excel yang setara dengan ARRAY_CONSTRAIN?

Google Sheets menggunakan:

=ARRAY_CONSTRAIN(source_range,number_of_rows,number_of_columns)

Pola langsung di Excel adalah:

=TAKE(source_range,number_of_rows,number_of_columns)

Sebagai contoh, model DCF untuk pembiayaan sindikasi perbankan mungkin memuat 60 periode proyeksi dalam 15 skenario. Namun, memo kredit hanya memerlukan 20 kuartal pertama dan 6 kolom output:

=TAKE('DCF Output'!B6:P65,20,6)

TAKE lebih tepat daripada membungkus output dengan IF, mengosongkan sel yang tidak diperlukan, atau mengarahkan chart ke rentang yang terlalu besar. Fungsi ini membatasi spilled array secara langsung. Formula berikutnya akan menerima hasil berukuran 20 baris kali 6 kolom, bukan rentang besar yang hanya dipenuhi string kosong.

Microsoft menjelaskan TAKE sebagai fungsi yang mengembalikan sejumlah baris atau kolom berurutan dari bagian awal atau akhir sebuah array dalam dokumentasi fungsi TAKE.

Perbandingan metode array constrain Excel

TAKE merupakan pilihan terbaik jika kompatibilitas bukan masalah. INDEX lebih aman apabila workbook mungkin dibuka oleh tim treasury, kreditur, atau perusahaan portofolio yang masih menggunakan Excel desktop versi lama.

Rentang manual masih dapat digunakan jika struktur laporan benar-benar tetap. Risikonya muncul ketika istilah "tetap" ternyata berarti seseorang diam-diam menyisipkan data dua bulan tambahan sebelum rapat direksi triwulanan.

Cara membatasi array Excel berdasarkan baris dan kolom

Argumen kedua pada TAKE menentukan jumlah baris. Argumen ketiga, yang bersifat opsional, menentukan jumlah kolom.

=TAKE('Returns Analysis'!B8:Q67,12,8)

Formula tersebut mengambil 12 baris dan 8 kolom dari sudut kiri atas.

Argumen negatif menghitung dari bagian bawah atau kanan:

=TAKE('Cash Flow'!B6:M65,-12,-4)

Formula ini mengembalikan 12 baris terakhir dan 4 kolom terakhir. Pola tersebut berguna untuk laporan likuiditas bergulir ketika output arus kas bertambah setiap bulan.

Dimensi hasilnya tetap eksplisit. Jika analisis imbal hasil mencakup 15 skenario dalam 60 periode, TAKE(...,12,8) akan selalu menghasilkan 96 sel. Output seperti ini lebih mudah diaudit dibandingkan rangkaian pengujian IF(COLUMN()<=8,...) yang disalin ke seluruh tab presentasi.

Ada satu hal yang perlu diperhatikan: 0 bukan berarti tidak mengembalikan baris. Menurut dokumentasi Microsoft, Excel akan menampilkan error #CALC! untuk menunjukkan array kosong. Karena itu, input jumlah baris yang berasal dari tab asumsi perlu diberi nilai minimum jika ada kemungkinan bernilai nol:

=TAKE('Operating Model'!B7:P66,MAX(1,Assumptions!$B$12),8)

Cara membatasi filtered array di Excel

TAKE menjadi lebih berguna ketika ditempatkan di luar FILTER, SORT, atau UNIQUE. Fungsi di bagian dalam membentuk kumpulan data, sedangkan TAKE membatasi jumlah data yang masuk ke tab laporan.

Misalnya, laporan margin kontribusi berisi 186 SKU dan digunakan sebagai sumber laporan triwulanan untuk direksi. Manajemen hanya ingin melihat 10 SKU dengan margin terendah, bukan seluruh tabel operasional:

=TAKE(
    SORTBY(
        FILTER(
            'SKU Economics'!A6:H191,
            'SKU Economics'!H6:H191<Assumptions!$B$18
        ),
        FILTER(
            'SKU Economics'!H6:H191,
            'SKU Economics'!H6:H191<Assumptions!$B$18
        ),
        1
    ),
    10,
    8
)

Jika Assumptions!$B$18 berisi ambang margin kontribusi 38,5%, formula tersebut akan menyaring SKU yang berada di bawah ambang, mengurutkannya dari margin terburuk ke terbaik, lalu mengembalikan maksimal 10 baris dalam 8 kolom.

Urutan fungsi sangat menentukan. Jika TAKE diterapkan sebelum SORTBY, Excel akan mengambil 10 baris pertama dari sumber, lalu hanya mengurutkan baris tersebut. Hasilnya menjawab pertanyaan yang berbeda, tetapi kesalahan ini mudah lolos dari proses review karena tampilannya tetap terlihat rapi.

Pola yang sama dapat digunakan untuk memfilter tanggal dari tab lain:

=TAKE(
    FILTER(
        'P&L'!B:C,
        ('P&L'!B:B>=Assumptions!$B$3)*
        ('P&L'!B:B<=Assumptions!$B$4)
    ),
    12,
    2
)

Jika yang dibutuhkan adalah nilai agregat, bukan array yang ditampilkan, tetap gunakan formula lintas tab yang umum:

=SUMIFS('P&L'!C:C,'P&L'!B:B,">="&Assumptions!$B$3)

Membatasi data yang ditampilkan tidak mengubah logika agregasi. Pembatasan ini hanya mengendalikan penyajian dan ketergantungan spill pada formula berikutnya, bukan populasi data sumber.

Cara melakukan array constrain Excel tanpa TAKE

Untuk alternatif yang tetap mendukung dynamic array, gabungkan INDEX dan SEQUENCE:

=INDEX(
    'DCF Output'!B6:P65,
    SEQUENCE(MIN(20,ROWS('DCF Output'!B6:P65))),
    SEQUENCE(,MIN(6,COLUMNS('DCF Output'!B6:P65)))
)

Kedua pengujian MIN mencegah dimensi yang diminta melebihi ukuran sumber. Ini penting ketika output analisis sensitivitas terkadang hanya berisi 4 skenario terisi, bukan 6 skenario yang direncanakan.

Untuk perilaku array versi lama, gunakan INDEX sebagai penentu sudut kanan bawah sebuah rentang:

='DCF Output'!B6:INDEX('DCF Output'!B6:P65,20,6)

Formula tersebut membuat referensi dari B6 hingga sel pada baris ke-20 dan kolom ke-6 dalam rentang sumber. Bentuknya ringkas dan cocok digunakan di dalam fungsi yang menerima referensi:

=SUM('DCF Output'!B6:INDEX('DCF Output'!B6:P65,20,6))

Pendekatan ini tidak selalu berperilaku sama dengan spilled array modern. Uji formula menggunakan versi Excel yang benar-benar dipakai oleh penerima workbook, bukan hanya versi yang terpasang di laptop pembuat model.

Kapan DROP perlu digunakan bersama TAKE?

TAKE memilih bagian tepi sebuah array, sedangkan DROP membuang bagian tepinya. Jika digabungkan, keduanya dapat mengambil jendela data dari bagian tengah.

Sebagai contoh, forecast 5 tahun mungkin berisi 12 bulan data historis yang diikuti 60 bulan proyeksi. Untuk mengambil tahun kedua periode forecast, buang 24 kolom pertama dan ambil 12 kolom berikutnya:

=TAKE(DROP('Monthly Model'!C10:BZ45,,24),,12)

Dokumentasi fungsi DROP dari Microsoft menjelaskan bahwa nilai negatif akan mengecualikan baris atau kolom dari bagian akhir array. Karena itu, DROP juga berguna untuk membuang kolom terminal value di bagian belakang sebelum membatasi tampilan DCF.

Laporan triwulanan untuk direksi dapat menggunakan pola yang sama guna mengambil 8 kuartal dari model bulanan:

=TAKE(
    DROP('Quarterly Summary'!B5:AZ30,,Assumptions!$B$20),
    26,
    8
)

Dalam contoh ini, Assumptions!$B$20 menentukan kolom awal. Output tetap berukuran 26 baris kali 8 kuartal meskipun periode pelaporannya terus bergulir.

Mengapa array yang dibatasi penting dalam model keuangan?

Menurut spesifikasi dan batas Excel, sebuah worksheet mendukung 1.048.576 baris dan 16.384 kolom. Kapasitas tersebut bukan alasan untuk menjalankan dynamic array berbasis seluruh kolom di setiap tab.

Formula satu kolom penuh dapat memeriksa lebih dari 1 juta sel, padahal laporan sebenarnya mungkin hanya memuat 4.800 transaksi. Jika proses yang sama diulang dalam 20 skenario, workbook akan melakukan pekerjaan jauh lebih besar hanya karena pembuat model tidak menentukan rentang secara jelas.

Membatasi array juga mengurangi risiko spill collision. Panduan dynamic array Microsoft menjelaskan bahwa rentang spill yang terhalang akan menghasilkan #SPILL!. Area tetap berukuran 12 baris untuk dashboard jauh lebih mudah disediakan dibandingkan filter tanpa batas yang bertambah dari 9 menjadi 43 baris setelah data diperbarui.

Manfaat lain yang sering terlewat adalah tata kelola model. Jika ringkasan valuasi dirancang untuk menampilkan 8 perusahaan pembanding dan median kelipatan EBITDA sebesar 14,2x, membatasi tampilan menjadi 8 baris akan memaksa tim menetapkan aturan peringkat secara eksplisit. Daftar tanpa batas dapat terus berubah sementara tampilannya tetap memberikan kesan bahwa seluruh proses sudah terkendali.

Kapan array Excel tidak perlu dibatasi?

Jangan menggunakan TAKE untuk menyembunyikan logika sumber yang belum lengkap. Jika analisis sensitivitas runway tidak mencakup kelompok karyawan baru karena rentang sumber berhenti 6 baris terlalu awal, membatasi output yang sudah dirapikan hanya akan menutupi masalah.

Jangan pula membatasi jadwal audit yang mengutamakan kelengkapan data. Saldo utang, eliminasi antarperusahaan, dan pemeriksaan laporan keuangan terintegrasi harus mencakup seluruh populasi yang dikendalikan. Spill range yang lebih rapi tidak sebanding dengan risiko neraca yang mengabaikan liabilitas sewa senilai Rp 4,2 miliar.

Berhati-hatilah saat membuat output top-N. TAKE(FILTER(...),10) hanya mengembalikan 10 data pertama yang memenuhi kriteria sesuai urutan sumber. Jika keputusan memerlukan eksposur terbesar, margin terendah, atau jatuh tempo paling awal, urutkan datanya secara eksplisit sebelum mengambil 10 baris pertama.

Untuk pola spill terkait di Google Sheets, lihat referensi ARRAYFORMULA untuk model keuangan. Prinsip desainnya berlaku di berbagai platform: bentuk hasil perhitungan harus dikendalikan secara sengaja, meskipun fungsi yang digunakan berbeda.

FAQ tentang array constrain Excel

Apakah Excel memiliki fungsi ARRAY_CONSTRAIN?

Excel tidak memiliki fungsi bernama ARRAY_CONSTRAIN. Padanan terdekatnya adalah TAKE, dengan sintaks TAKE(array, rows, [columns]). Fungsi tersebut tersedia di Microsoft 365 dan Excel 2024.

Bagaimana cara membatasi array menjadi 10 baris di Excel?

Gunakan =TAKE(array,10). Jika hasil harus dipilih berdasarkan peringkat tertentu, urutkan array terlebih dahulu, misalnya =TAKE(SORT(array),10).

Bagaimana cara membatasi jumlah baris dan kolom sekaligus?

Gunakan jumlah baris sebagai argumen kedua dan jumlah kolom sebagai argumen ketiga. Formula =TAKE(A2:H100,12,5) mengembalikan 12 baris pertama dan 5 kolom pertama.

Bagaimana cara mengambil baris terakhir dari sebuah array?

Gunakan angka negatif pada argumen baris. Sebagai contoh, =TAKE(A2:F100,-12) mengambil 12 baris terakhir dari array.

Apa alternatif TAKE untuk Excel versi lama?

Gunakan INDEX untuk menentukan batas akhir rentang, misalnya =A2:INDEX(A2:F100,20,6). Untuk Excel yang mendukung dynamic array tetapi tidak mendukung TAKE, Anda dapat menggabungkan INDEX dengan SEQUENCE.

Mengapa formula TAKE menghasilkan #CALC!?

TAKE dapat menghasilkan #CALC! jika jumlah baris atau kolom yang diminta adalah nol karena Excel tidak mendukung array kosong. Gunakan batas minimum seperti MAX(1,jumlah_baris) jika input dapat bernilai nol.

Apakah TAKE mempercepat workbook Excel?

TAKE dapat mengurangi ukuran output spill dan jumlah sel yang diproses oleh formula lanjutan. Namun, fungsi ini tidak otomatis mempercepat perhitungan sumber. Untuk hasil terbaik, hindari referensi seluruh kolom jika data aktual hanya menggunakan beberapa ribu baris.

Ringkasan: cara tepat melakukan array constrain Excel

Gunakan TAKE jika workbook dijalankan di Microsoft 365 atau Excel 2024. Gabungkan dengan DROP untuk membuat jendela data bergulir. Tempatkan TAKE di luar FILTER atau SORTBY jika hasil akhir yang sudah disaring dan diurutkan perlu dibatasi berdasarkan jumlah baris atau kolom.

Gunakan INDEX jika kompatibilitas dengan Excel 2021 atau versi desktop yang lebih lama masih diperlukan. Apa pun metodenya, tentukan alasan pelaporan yang jelas untuk setiap pembatasan, misalnya 12 bulan, 8 kuartal, 10 SKU, atau 6 skenario. Alasan seperti "supaya spill-nya muat" bukan kebijakan pemodelan yang dapat dipertanggungjawabkan.

Coba ModelMonkey gratis selama 14 hari untuk mengerjakan tugas AI langsung di Google Sheets maupun Excel.