Konten
Apa itu array dinamis?
Pada bulan September 2018, Microsoft merilis pembaruan yang menambahkan alat yang sama sekali baru ke Microsoft Excel: Dynamic Arrays dan 7 fungsi baru untuk bekerja dengannya. Hal-hal ini, tanpa berlebihan, secara radikal mengubah semua teknik biasa bekerja dengan formula dan fungsi dan perhatian, secara harfiah, setiap pengguna.
Pertimbangkan contoh sederhana untuk menjelaskan esensi.
Misalkan kita memiliki tabel sederhana dengan data kota-bulan. Apa yang akan terjadi jika kita memilih sel kosong di sebelah kanan lembar dan memasukkan formula yang menghubungkan bukan ke satu sel, tetapi langsung ke rentang?
Di semua versi Excel sebelumnya, setelah mengklik Enter kita akan mendapatkan konten hanya dari satu sel pertama B2. Bagaimana lagi?
Yah, atau mungkin saja untuk membungkus rentang ini dalam beberapa jenis fungsi agregasi seperti =SUM(B2:C4) dan dapatkan total keseluruhan untuk itu.
Jika kita membutuhkan operasi yang lebih kompleks daripada penjumlahan primitif, seperti mengekstrak nilai unik atau 3 Teratas, maka kita harus memasukkan rumus kita sebagai rumus larik menggunakan pintasan keyboard Ctrl+perubahan+Enter.
Sekarang semuanya berbeda.
Sekarang setelah memasukkan formula seperti itu, kita cukup mengklik Enter – dan dapatkan hasilnya segera semua nilai uXNUMXbuXNUMXbto yang kami rujuk:
Ini bukan keajaiban, tetapi array dinamis baru yang dimiliki Microsoft Excel sekarang. Selamat datang di dunia baru
Fitur bekerja dengan array dinamis
Secara teknis, seluruh array dinamis kami disimpan di sel pertama G4, mengisi jumlah sel yang diperlukan ke kanan dan bawah dengan datanya. Jika Anda memilih sel lain dalam larik, maka tautan di bilah rumus tidak akan aktif, menunjukkan bahwa kita berada di salah satu sel "anak":
Upaya untuk menghapus satu atau lebih sel "anak" tidak akan menghasilkan apa-apa – Excel akan segera menghitung ulang dan mengisinya.
Pada saat yang sama, kita dapat dengan aman merujuk ke sel "anak" ini dalam rumus lain:
Jika Anda menyalin sel pertama larik (misalnya, dari G4 ke F8), maka seluruh larik (referensinya) akan bergerak ke arah yang sama seperti dalam rumus biasa:
Jika kita perlu memindahkan array, maka itu akan cukup untuk bergerak (dengan mouse atau kombinasi dari Ctrl+X, Ctrl+V), sekali lagi, hanya sel utama pertama G4 – setelah itu, itu akan dipindahkan ke tempat baru dan seluruh array kami akan diperluas lagi.
Jika Anda perlu merujuk ke tempat lain pada lembar ke array dinamis yang dibuat, maka Anda dapat menggunakan karakter khusus # ("pound") setelah alamat sel utamanya:
Misalnya, sekarang Anda dapat dengan mudah membuat daftar tarik-turun di sel yang merujuk ke larik dinamis yang dibuat:
Kesalahan array dinamis
Tapi apa yang terjadi jika tidak ada cukup ruang untuk memperluas array, atau jika ada sel yang sudah ditempati oleh data lain di jalurnya? Temui jenis kesalahan baru yang mendasar di Excel – #TRANSFER! (#TUMPAHAN!):
Seperti biasa, jika kita mengklik ikon dengan berlian kuning dan tanda seru, kita akan mendapatkan penjelasan lebih rinci tentang sumber masalahnya dan kita dapat dengan cepat menemukan sel yang mengganggu:
Kesalahan serupa akan terjadi jika larik keluar dari lembar atau mengenai sel yang digabungkan. Jika Anda menghapus rintangan, maka semuanya akan segera diperbaiki dengan cepat.
Array dinamis dan tabel pintar
Jika array dinamis menunjuk ke tabel "pintar" yang dibuat oleh pintasan keyboard Ctrl+T atau dengan Beranda – Format sebagai tabel (Beranda — Format sebagai Tabel), maka itu juga akan mewarisi kualitas utamanya – ukuran otomatis.
Saat menambahkan data baru ke bawah atau ke kanan, tabel pintar dan rentang dinamis juga akan secara otomatis meregang:
Namun, ada satu batasan: kami tidak dapat menggunakan referensi rentang dinamis di forum di dalam tabel pintar:
Array dinamis dan fitur Excel lainnya
Oke, katamu. Semua ini menarik dan lucu. Tidak perlu, seperti sebelumnya, meregangkan rumus secara manual dengan referensi ke sel pertama dari rentang asli ke bawah dan ke kanan dan semua itu. Dan itu saja?
Tidak cukup.
Array dinamis bukan hanya alat lain di Excel. Sekarang mereka tertanam di jantung (atau otak) Microsoft Excel – mesin kalkulasinya. Ini berarti bahwa rumus dan fungsi Excel lain yang kita kenal sekarang juga mendukung bekerja dengan array dinamis. Mari kita lihat beberapa contoh untuk memberi Anda gambaran tentang kedalaman perubahan yang telah terjadi.
Mengubah urutan
Untuk mentranspos rentang (menukar baris dan kolom) Microsoft Excel selalu memiliki fungsi bawaan TRANSP (MENGUBAH URUTAN). Namun, untuk menggunakannya, Anda harus terlebih dahulu memilih rentang hasil dengan benar (misalnya, jika inputnya adalah rentang 5×3, maka Anda harus memilih 3×5), lalu masuk ke fungsi dan tekan tombol kombinasi Ctrl+perubahan+Enter, karena itu hanya bisa bekerja dalam mode rumus array.
Sekarang Anda cukup memilih satu sel, masukkan rumus yang sama ke dalamnya dan klik normal Enter – array dinamis akan melakukan semuanya dengan sendirinya:
Tabel perkalian
Ini adalah contoh yang saya berikan ketika saya diminta untuk memvisualisasikan manfaat rumus array di Excel. Sekarang, untuk menghitung seluruh tabel Pythagoras, cukup dengan berdiri di sel pertama B2, masukkan di sana rumus yang mengalikan dua array (set angka vertikal dan horizontal 1..10) dan cukup klik Enter:
Perekatan dan konversi kasing
Array tidak hanya dapat dikalikan, tetapi juga direkatkan dengan operator standar & (ampersand). Misalkan kita perlu mengekstrak nama depan dan belakang dari dua kolom dan memperbaiki kasus loncatan pada data asli. Kami melakukan ini dengan satu rumus pendek yang membentuk seluruh array, dan kemudian kami menerapkan fungsi ke dalamnya PROPNAKH (SESUAI)untuk merapikan register:
Kesimpulan Top 3
Misalkan kita memiliki sekelompok angka dari mana kita ingin mendapatkan tiga hasil teratas, mengaturnya dalam urutan menurun. Sekarang ini dilakukan dengan satu formula dan, sekali lagi, tanpa apapun Ctrl+perubahan+Enter seperti sebelumnya:
Jika Anda ingin hasilnya tidak ditempatkan dalam satu kolom, tetapi dalam satu baris, maka cukup mengganti titik dua (pemisah garis) dalam rumus ini dengan titik koma (pemisah elemen dalam satu baris). Dalam versi bahasa Inggris Excel, pemisah ini adalah titik koma dan koma, masing-masing.
VLOOKUP mengekstrak beberapa kolom sekaligus
Fungsi VPR (VLOOKUP) sekarang Anda dapat menarik nilai bukan dari satu, tetapi dari beberapa kolom sekaligus – cukup tentukan nomornya (dalam urutan yang diinginkan) sebagai array dalam argumen ketiga fungsi:
Fungsi OFFSET mengembalikan array dinamis
Salah satu fungsi yang paling menarik dan berguna (setelah VLOOKUP) untuk analisis data adalah fungsi PEMBUANGAN (MENGIMBANGI), yang saya dedikasikan pada satu waktu seluruh bab dalam buku saya dan sebuah artikel di sini. Kesulitan dalam memahami dan menguasai fungsi ini selalu mengembalikan array (rentang) data sebagai hasilnya, tetapi kami tidak dapat melihatnya, karena Excel masih tidak tahu cara bekerja dengan array di luar kotak.
Sekarang masalah ini sudah berlalu. Lihat bagaimana sekarang, dengan menggunakan satu rumus dan larik dinamis yang dikembalikan oleh OFFSET, Anda dapat mengekstrak semua baris untuk produk tertentu dari tabel yang diurutkan:
Mari kita lihat argumennya:
- A1 – sel awal (titik referensi)
- (F2;A2:A30;0) – perhitungan pergeseran dari sel awal ke bawah – ke kubis yang pertama kali ditemukan.
- 0 – pergeseran "jendela" ke kanan relatif terhadap sel awal
- (A2:A30;F2) – perhitungan ketinggian "jendela" yang dikembalikan - jumlah baris di mana ada kubis.
- 4 — ukuran "jendela" secara horizontal, yaitu menghasilkan 4 kolom
Fungsi Baru untuk Array Dinamis
Selain mendukung mekanisme larik dinamis pada fungsi lama, beberapa fungsi yang sama sekali baru telah ditambahkan ke Microsoft Excel, yang dipertajam khusus untuk bekerja dengan larik dinamis. Secara khusus, ini adalah:
- GRADE (MENYORTIR) – mengurutkan rentang input dan menghasilkan larik dinamis pada output
- SORTPO (SORTIR DENGAN) – dapat mengurutkan satu rentang berdasarkan nilai dari yang lain
- FILTER (SARING) – mengambil baris dari rentang sumber yang memenuhi kondisi yang ditentukan
- UNIK (UNIK) – mengekstrak nilai unik dari rentang atau menghapus duplikat
- SLMASIF (RANDARRAY) – menghasilkan array angka acak dengan ukuran tertentu
- TEMBUNI (URUTAN) — membentuk array dari urutan angka dengan langkah yang diberikan
Lebih banyak tentang mereka - nanti. Mereka layak menjadi artikel terpisah (dan bukan satu) untuk studi yang bijaksana
Kesimpulan
Jika Anda telah membaca semua yang tertulis di atas, maka saya pikir Anda sudah menyadari skala perubahan yang telah terjadi. Begitu banyak hal di Excel sekarang dapat dilakukan dengan lebih mudah, lebih mudah, dan lebih logis. Saya harus mengakui bahwa saya sedikit terkejut dengan berapa banyak artikel yang sekarang harus dikoreksi di sini, di situs ini dan di buku-buku saya, tetapi saya siap melakukan ini dengan hati yang ringan.
Menyimpulkan hasilnya, plus array dinamis, Anda dapat menulis yang berikut:
- Anda bisa melupakan kombinasinya Ctrl+perubahan+Enter. Excel sekarang tidak melihat perbedaan antara "rumus biasa" dan "rumus array" dan memperlakukannya dengan cara yang sama.
- Tentang fungsi SUMPRODUK (SUMPRODUK), yang sebelumnya digunakan untuk memasukkan rumus array tanpa Ctrl+perubahan+Enter Anda juga bisa melupakan – sekarang cukup mudah JUMLAH и Enter.
- Tabel cerdas dan fungsi yang sudah dikenal (SUM, JIKA, VLOOKUP, SUMIFS, dll.) sekarang juga mendukung sepenuhnya atau sebagian array dinamis.
- Ada kompatibilitas mundur: jika Anda membuka buku kerja dengan array dinamis di versi Excel lama, mereka akan berubah menjadi rumus array (dalam kurung kurawal) dan terus bekerja dalam "gaya lama".
Menemukan beberapa nomor minus:
- Anda tidak dapat menghapus baris, kolom, atau sel individual dari larik dinamis, yaitu ia hidup sebagai satu kesatuan.
- Anda tidak dapat mengurutkan array dinamis dengan cara biasa Data – Penyortiran (Data — Urutkan). Sekarang ada fungsi khusus untuk ini. GRADE (MENYORTIR).
- Rentang dinamis tidak dapat diubah menjadi tabel cerdas (tetapi Anda dapat membuat rentang dinamis berdasarkan tabel cerdas).
Tentu saja, ini bukan akhir, dan saya yakin Microsoft akan terus meningkatkan mekanisme ini di masa mendatang.
Di mana saya bisa mengunduh?
Dan akhirnya, pertanyaan utama
Microsoft pertama kali mengumumkan dan menunjukkan pratinjau array dinamis di Excel pada September 2018 di sebuah konferensi Terbakar. Dalam beberapa bulan ke depan, ada pengujian menyeluruh dan menjalankan fitur-fitur baru, pertama pada kucing karyawan Microsoft sendiri, dan kemudian pada penguji sukarela dari lingkaran Office Insiders. Tahun ini, pembaruan yang menambahkan array dinamis mulai diluncurkan secara bertahap ke pelanggan Office 365 reguler. Misalnya, saya baru menerimanya pada bulan Agustus dengan langganan Office 365 Pro Plus (Bertarget Bulanan).
Jika Excel Anda belum memiliki array dinamis, tetapi Anda benar-benar ingin bekerja dengannya, maka ada opsi berikut:
- Jika Anda memiliki langganan Office 365, Anda cukup menunggu hingga pembaruan ini sampai kepada Anda. Seberapa cepat hal ini terjadi bergantung pada seberapa sering pembaruan dikirimkan ke Office Anda (setahun sekali, setiap enam bulan sekali, sebulan sekali). Jika Anda memiliki PC perusahaan, Anda dapat meminta administrator Anda untuk mengatur pembaruan agar lebih sering diunduh.
- Anda dapat bergabung dengan barisan sukarelawan pengujian Office Insiders – maka Anda akan menjadi yang pertama menerima semua fitur dan fungsi baru (tetapi tentu saja ada kemungkinan peningkatan buggy di Excel).
- Jika Anda tidak memiliki langganan, tetapi versi Excel yang berdiri sendiri dalam kotak, maka Anda harus menunggu hingga rilis versi Office dan Excel berikutnya pada tahun 2022, setidaknya. Pengguna versi tersebut hanya menerima pembaruan keamanan dan perbaikan bug, dan semua "barang" baru sekarang hanya tersedia untuk pelanggan Office 365. Sedih tapi nyata
Bagaimanapun, ketika array dinamis muncul di Excel Anda – setelah artikel ini, Anda akan siap untuk itu
- Apa itu rumus array dan bagaimana menggunakannya di Excel
- Penjumlahan jendela (rentang) menggunakan fungsi OFFSET
- 3 Cara Transpose Tabel di Excel