Tabel dinamis di google sheets

Setiap kali, saya lupa beberapa langkah, dan harus memikirkan bagaimana melakukannya setiap kali (karena saya jarang melakukannya)

Karena saya menghabiskan waktu terlalu lama memikirkan bagaimana melakukan ini, saya pikir saya akan membaginya dengan diri saya sendiri dan juga dengan semua orang

Daftar isi

Apa itu array dinamis di Google Sheets?

Istilah "array dinamis" dapat berarti beberapa hal

Dalam hal ini, dengan "array dinamis" yang saya maksud adalah sekumpulan perhitungan atau fungsi yang secara otomatis diperluas berdasarkan daftar utama hal-hal yang akan dioperasikan

Baru-baru ini misalnya, saya sedang membuat buku kerja dari halaman web yang saya analisis (halaman web yang saya miliki dan kelola). Saya ingin menambahkan halaman web ke daftar master dan membuat analisisnya direplikasi sendiri

Anda membuat array dinamis saat Anda

• Miliki daftar hal-hal yang Anda ingin membuat sesuatu yang lain secara otomatis bertambah besar
• Miliki tabel yang ingin Anda isi secara otomatis

Sebagai contoh. Anda melakukan P&L pada sepuluh bisnis, dan menambahkan satu lagi. Semua data Anda berasal dari satu sumber. Jadi, ketika Anda menambahkan bisnis kesebelas Anda ke dalam daftar sepuluh, Anda ingin penghitungan P&L Anda secara otomatis diperluas untuk bisnis baru tanpa harus repot-repot menyalin dan menempel di mana-mana.

Setiap kali saya menambahkan sesuatu ke dalam daftar, saya tidak ingin mengingat semua tempat yang harus saya tambahkan baris dan salin-tempel. Saya lebih suka memiliki array dinamis

Mur dan baut. Menggunakan Concatenate, Split, dan Transpose

Singkatnya, Anda membuat larik dinamis di Google Spreadsheet menggunakan penggabungan, split, transpose, semuanya dalam konteks arrayformula

Inilah mengapa Anda menggunakan ketiganya

• concatenate. Gunakan ini untuk menghubungkan rangkaian sel menjadi satu string, dipisahkan dengan koma misalnya
• split_. Sekarang Anda memisahkan string ini
• transpose_. Untuk mengubah bentuk menjadi sesuatu yang lebih nyaman

arrayformula berulang melalui rentang

Contoh 1. Daftar Dinamis dengan sub-daftar

Saya memiliki daftar situs web dan saya ingin memiliki sub-daftar untuk masing-masing situs

Dan saya ingin semuanya dibangun secara dinamis

Katakanlah saya memiliki daftar awal ini. Buah, misalnya

Dan saya ingin membangun daftar kedua ini

Saya akan menggunakan struktur ini untuk membuat array dinamis saya

=arrayformula(transpose(split(concatenate(range_of_items&splitter),",")))

Dalam hal ini splitter saya akan menjadi string ini.

concatenate("  "&range_of_split_values&",")

Ada koma awal, untuk memisahkan pemisah dari daftar item. Lalu ada spasi, lalu koma setelah masing-masing

Rumus melakukan ini

1. Beroperasi dalam rumus array, ini beroperasi pada item satu per satu. Buah, Sayuran, dll
2. Dibutuhkan setiap item dan menambahkan string splitter ke dalamnya, sehingga masing-masing menjadi e. g.

   concatenate("  "&range_of_split_values&",")

   _2
3. Kemudian menggunakan split untuk membaginya menjadi empat kolom terpisah

Setelah itu menjadi sekumpulan kolom, ia menggunakan transpose untuk membagi semuanya menjadi rentang vertikal

Tahap selanjutnya adalah membuat splitter secara dinamis

Ini adalah concatenate lain di dalam arrayformula

concatenate("  "&range_of_split_values&",")

Sekali lagi, ini menempatkan dua spasi di depan masing-masing (untuk estetika), dan menempelkan koma setelah akhir

Formula terakhir saya terlihat seperti ini

=arrayformula(transpose(split(concatenate(range_of_items&","&concatenate("  "&range_of_split_values&",")),",")))

Sekarang, jika saya menambahkan elemen lain ke dalam daftar pertama atau kedua, seluruh array berubah. Lihat misalnya di bawah, saya menambahkan "Kentang" ke dalam daftar kedua

Contoh 2. Buat tabel yang dibuat secara dinamis

Alasan kedua Anda mungkin ingin menggunakan larik dinamis adalah untuk membuat tabel yang dibuat secara dinamis

Ini berfungsi sedikit seperti tabel pivot tetapi berfungsi sepenuhnya melalui rumus

Dua keuntungan dari tabel yang dibuat secara dinamis adalah

1. Lebih mudah melakukan perhitungan pada hasil akhir. Anda tidak perlu melakukan rumus tabel pivot, yang bisa sedikit membingungkan
2. Anda tidak perlu "memperbarui" tabel pivot

Anda mungkin memiliki berbagai alasan untuk melakukannya sendiri — saya akan menyerahkannya kepada Anda

Membuat tabel yang dibuat secara dinamis mirip dengan di atas, tetapi Anda menggunakan rumus untuk split dan concatenate sedikit berbeda, dan Anda tidak harus menggunakan transpose

Berikut rumus umum untuk membuat tabel dinamis di Google Spreadsheet dengan 

=arrayformula(transpose(split(concatenate(range_of_items&","&concatenate("  "&range_of_split_values&",")),",")))

=arrayformula(transpose(split(concatenate(range_of_items&","&concatenate("  "&range_of_split_values&",")),",")))

=arrayformula(vertical_range&"'s "&split(concatenate(horizontal_range&","),","))

Seperti di atas, jika Anda menambahkan item ke salah satu larik, tabel yang dibuat secara dinamis akan diperluas untuk mengakomodasi

Bagaimana cara membuat tabel dinamis di Google Sheets?

Bisakah Anda membuat tabel frekuensi di Google Sheets?

Bagaimana cara memperluas tabel secara otomatis di Google Sheets?