Contents [show]
Apakah tatasusunan dinamik
Pada September 2018, Microsoft mengeluarkan kemas kini yang menambahkan alat baharu sepenuhnya pada Microsoft Excel: Tatasusunan Dinamik dan 7 fungsi baharu untuk bekerja dengannya. Perkara-perkara ini, tanpa keterlaluan, secara radikal mengubah semua teknik biasa bekerja dengan formula dan fungsi dan kebimbangan, secara literal, setiap pengguna.
Pertimbangkan contoh mudah untuk menerangkan intipati.
Katakan kita mempunyai jadual ringkas dengan data tentang bulan-bandar. Apakah yang akan berlaku jika kita memilih mana-mana sel kosong di sebelah kanan helaian dan memasukkan ke dalamnya formula yang memautkan bukan kepada satu sel, tetapi serta-merta kepada julat?
Dalam semua versi Excel sebelumnya, selepas mengklik pada Masukkan kita akan mendapat kandungan hanya satu sel pertama B2. Bagaimana lagi?
Baiklah, atau mungkin untuk membungkus julat ini dalam beberapa jenis fungsi pengagregatan seperti =SUM(B2:C4) dan mendapatkan jumlah besar untuknya.
Jika kami memerlukan operasi yang lebih kompleks daripada jumlah primitif, seperti mengekstrak nilai unik atau Top 3, maka kami perlu memasukkan formula kami sebagai formula tatasusunan menggunakan pintasan papan kekunci Ctrl+Beralih+Masukkan.
Sekarang semuanya berbeza.
Sekarang selepas memasukkan formula sedemikian, kita boleh klik pada Masukkan – dan dapatkan hasilnya dengan segera semua nilai uXNUMXbuXNUMXbto yang kami rujuk:
Ini bukan sihir, tetapi tatasusunan dinamik baharu yang dimiliki Microsoft Excel sekarang. Selamat datang ke dunia baru 🙂
Ciri bekerja dengan tatasusunan dinamik
Secara teknikal, keseluruhan tatasusunan dinamik kami disimpan dalam sel pertama G4, mengisi bilangan sel yang diperlukan ke kanan dan ke bawah dengan datanya. Jika anda memilih mana-mana sel lain dalam tatasusunan, maka pautan dalam bar formula akan menjadi tidak aktif, menunjukkan bahawa kita berada dalam salah satu sel "anak":
Percubaan untuk memadam satu atau lebih sel "kanak-kanak" tidak akan membawa kepada apa-apa - Excel akan serta-merta mengira semula dan mengisinya.
Pada masa yang sama, kita boleh merujuk sel "kanak-kanak" ini dengan selamat dalam formula lain:
Jika anda menyalin sel pertama tatasusunan (contohnya, dari G4 ke F8), maka keseluruhan tatasusunan (rujukannya) akan bergerak ke arah yang sama seperti dalam formula biasa:
Jika kita perlu memindahkan tatasusunan, maka ia akan mencukupi untuk bergerak (dengan tetikus atau gabungan Ctrl+X, Ctrl+V), sekali lagi, hanya sel utama pertama G4 – selepas itu, ia akan dipindahkan ke tempat baharu dan keseluruhan tatasusunan kami akan dikembangkan semula.
Jika anda perlu merujuk di tempat lain pada helaian kepada tatasusunan dinamik yang dicipta, maka anda boleh menggunakan aksara khas # (“paun”) selepas alamat sel utamanya:
Sebagai contoh, kini anda boleh membuat senarai lungsur turun dengan mudah dalam sel yang merujuk kepada tatasusunan dinamik yang dibuat:
Ralat tatasusunan dinamik
Tetapi apakah yang berlaku jika tidak ada ruang yang mencukupi untuk mengembangkan tatasusunan, atau jika terdapat sel yang telah diduduki oleh data lain dalam laluannya? Temui jenis ralat asas baharu dalam Excel - #PINDAHKAN! (#TUHAN!):
Seperti biasa, jika kita mengklik pada ikon dengan berlian kuning dan tanda seru, kita akan mendapat penjelasan yang lebih terperinci tentang punca masalah dan kita boleh mencari sel yang mengganggu dengan cepat:
Ralat serupa akan berlaku jika tatasusunan keluar dari helaian atau mencecah sel yang digabungkan. Jika anda mengalih keluar halangan, maka segala-galanya akan segera diperbetulkan dengan cepat.
Tatasusunan dinamik dan jadual pintar
Jika tatasusunan dinamik menghala ke jadual "pintar" yang dibuat oleh pintasan papan kekunci Ctrl+T atau dengan Laman Utama – Format sebagai jadual (Laman Utama — Format sebagai Jadual), maka ia juga akan mewarisi kualiti utamanya – auto-saiz.
Apabila menambah data baharu ke bahagian bawah atau ke kanan, jadual pintar dan julat dinamik juga akan meregangkan secara automatik:
Walau bagaimanapun, terdapat satu had: kami tidak boleh menggunakan rujukan julat dinamik dalam forum di dalam jadual pintar:
Tatasusunan dinamik dan ciri Excel yang lain
Okay, awak cakap. Semua ini menarik dan lucu. Tidak perlu, seperti sebelum ini, untuk meregangkan formula secara manual dengan merujuk kepada sel pertama julat asal ke bawah dan ke kanan dan semua itu. Dan itu sahaja?
Tidak cukup.
Tatasusunan dinamik bukan sekadar alat lain dalam Excel. Kini mereka tertanam dalam hati (atau otak) Microsoft Excel - enjin pengiraannya. Ini bermakna formula dan fungsi Excel lain yang biasa kepada kami kini turut menyokong kerja dengan tatasusunan dinamik. Mari kita lihat beberapa contoh untuk memberi anda gambaran tentang kedalaman perubahan yang telah berlaku.
Transpose
Untuk menukar julat (tukar baris dan lajur) Microsoft Excel sentiasa mempunyai fungsi terbina dalam TRANSP (PINDAHKAN). Walau bagaimanapun, untuk menggunakannya, anda mesti terlebih dahulu memilih julat untuk keputusan dengan betul (contohnya, jika input adalah julat 5×3, maka anda mesti telah memilih 3×5), kemudian masukkan fungsi dan tekan butang gabungan Ctrl+Beralih+Masukkan, kerana ia hanya boleh berfungsi dalam mod formula tatasusunan.
Sekarang anda hanya boleh memilih satu sel, masukkan formula yang sama ke dalamnya dan klik pada normal Masukkan – tatasusunan dinamik akan melakukan segala-galanya dengan sendirinya:
Jadual pendaraban
Ini adalah contoh yang saya pernah berikan apabila saya diminta untuk menggambarkan faedah formula tatasusunan dalam Excel. Sekarang, untuk mengira keseluruhan jadual Pythagoras, cukup untuk berdiri di sel pertama B2, masukkan formula yang mendarab dua tatasusunan (set nombor menegak dan mendatar 1..10) dan hanya klik pada Masukkan:
Pelekatan dan penukaran kes
Tatasusunan bukan sahaja boleh didarab, tetapi juga dilekatkan bersama dengan operator standard & (ampersand). Katakan kita perlu mengekstrak nama pertama dan akhir daripada dua lajur dan membetulkan kes lompat dalam data asal. Kami melakukan ini dengan satu formula pendek yang membentuk keseluruhan tatasusunan, dan kemudian kami menggunakan fungsi itu padanya PROPNACH (PROPER)untuk mengemas daftar:
Kesimpulan Top 3
Katakan kita mempunyai sekumpulan nombor yang daripadanya kita ingin memperoleh tiga keputusan teratas, menyusunnya dalam tertib menurun. Sekarang ini dilakukan dengan satu formula dan, sekali lagi, tanpa sebarang Ctrl+Beralih+Masukkan seperti dahulu:
Jika anda mahu keputusan diletakkan bukan dalam lajur, tetapi dalam satu baris, maka sudah cukup untuk menggantikan titik bertindih (pemisah baris) dalam formula ini dengan koma bertitik (pemisah unsur dalam satu baris). Dalam versi bahasa Inggeris Excel, pemisah ini ialah koma bertitik dan koma, masing-masing.
VLOOKUP mengekstrak berbilang lajur sekaligus
Fungsi VPR (TAMPILAN) kini anda boleh menarik nilai bukan dari satu, tetapi dari beberapa lajur sekaligus – hanya tentukan nombornya (dalam sebarang susunan yang dikehendaki) sebagai tatasusunan dalam hujah ketiga fungsi:
Fungsi OFFSET mengembalikan tatasusunan dinamik
Salah satu fungsi yang paling menarik dan berguna (selepas VLOOKUP) untuk analisis data ialah fungsi PELUPUSAN (OFFSET), yang mana saya menumpukan pada satu masa keseluruhan bab dalam buku saya dan artikel di sini. Kesukaran untuk memahami dan menguasai fungsi ini selalunya ialah ia mengembalikan tatasusunan (julat) data sebagai hasilnya, tetapi kami tidak dapat melihatnya, kerana Excel masih tidak tahu cara bekerja dengan tatasusunan di luar kotak.
Sekarang masalah ini adalah pada masa lalu. Lihat bagaimana sekarang, menggunakan formula tunggal dan tatasusunan dinamik yang dikembalikan oleh OFFSET, anda boleh mengekstrak semua baris untuk produk tertentu daripada mana-mana jadual yang diisih:
Mari kita lihat hujah beliau:
- A1 – sel permulaan (titik rujukan)
- ПОИСКПОЗ(F2;A2:A30;0) – pengiraan peralihan dari sel permulaan ke bawah – kepada kubis pertama ditemui.
- 0 – anjakan "tetingkap" ke kanan berbanding dengan sel permulaan
- СЧЁТЕСЛИ(A2:A30;F2) – pengiraan ketinggian "tetingkap" yang dikembalikan - bilangan baris di mana terdapat kubis.
- 4 — saiz "tetingkap" secara mendatar, iaitu output 4 lajur
Fungsi Baharu untuk Tatasusunan Dinamik
Selain menyokong mekanisme tatasusunan dinamik dalam fungsi lama, beberapa fungsi yang benar-benar baharu telah ditambahkan pada Microsoft Excel, dipertajamkan khusus untuk bekerja dengan tatasusunan dinamik. Khususnya, ini adalah:
- GRED (SORT) – mengisih julat input dan menghasilkan tatasusunan dinamik pada output
- SORTPO (DISUSUN MENGIKUT) – boleh mengisih satu julat mengikut nilai daripada yang lain
- FILTER (PENAPIS) – mendapatkan semula baris daripada julat sumber yang memenuhi syarat yang ditetapkan
- UNIK (UNIK) – mengekstrak nilai unik daripada julat atau mengalih keluar pendua
- SLMASSIVE (RANDARRAY) – menjana tatasusunan nombor rawak bagi saiz tertentu
- SELEPAS BERSALIN (URUTAN) — membentuk tatasusunan daripada jujukan nombor dengan langkah tertentu
Lebih lanjut mengenai mereka - sedikit kemudian. Mereka bernilai artikel yang berasingan (dan bukan satu) untuk kajian yang bernas 🙂
kesimpulan
Jika anda telah membaca semua yang ditulis di atas, maka saya rasa anda sudah menyedari skala perubahan yang telah berlaku. Begitu banyak perkara dalam Excel kini boleh dilakukan dengan lebih mudah, lebih mudah dan lebih logik. Saya harus mengakui bahawa saya agak terkejut dengan berapa banyak artikel yang kini perlu diperbetulkan di sini, di laman web ini dan dalam buku saya, tetapi saya bersedia untuk melakukan ini dengan hati yang ringan.
Merumuskan hasil, tambahnya tatasusunan dinamik, anda boleh menulis yang berikut:
- Anda boleh melupakan gabungan itu Ctrl+Beralih+Masukkan. Excel kini tidak melihat perbezaan antara "formula biasa" dan "formula tatasusunan" dan memperlakukannya dengan cara yang sama.
- Mengenai fungsi SUMPRODUCT (SUMPRODUCT), yang sebelum ini digunakan untuk memasukkan formula tatasusunan tanpa Ctrl+Beralih+Masukkan anda juga boleh lupa – kini sudah cukup mudah SUM и Masukkan.
- Jadual pintar dan fungsi biasa (SUM, IF, VLOOKUP, SUMIFS, dll.) kini turut menyokong tatasusunan dinamik sepenuhnya atau sebahagiannya.
- Terdapat keserasian ke belakang: jika anda membuka buku kerja dengan tatasusunan dinamik dalam versi lama Excel, ia akan bertukar menjadi formula tatasusunan (dalam pendakap kerinting) dan terus berfungsi dalam "gaya lama".
Terjumpa beberapa nombor tolak:
- Anda tidak boleh memadamkan baris, lajur atau sel individu daripada tatasusunan dinamik, iaitu ia hidup sebagai satu entiti.
- Anda tidak boleh mengisih tatasusunan dinamik dengan cara biasa Data – Isih (Data — Isih). Kini terdapat fungsi khas untuk ini. GRED (SORT).
- Julat dinamik tidak boleh ditukar menjadi jadual pintar (tetapi anda boleh membuat julat dinamik berdasarkan jadual pintar).
Sudah tentu, ini bukan penamat, dan saya pasti Microsoft akan terus menambah baik mekanisme ini pada masa hadapan.
Di mana saya boleh memuat turun?
Dan akhirnya, soalan utama 🙂
Microsoft mula-mula mengumumkan dan menunjukkan pratonton tatasusunan dinamik dalam Excel pada September 2018 pada persidangan Menyalakan. Dalam beberapa bulan akan datang, terdapat ujian menyeluruh dan penggunaan ciri baharu, mula-mula kucing pekerja Microsoft sendiri, dan kemudian pada penguji sukarelawan dari kalangan Office Insiders. Tahun ini, kemas kini yang menambah tatasusunan dinamik mula dilancarkan secara beransur-ansur kepada pelanggan Office 365 biasa. Sebagai contoh, saya hanya menerimanya pada bulan Ogos dengan langganan Office 365 Pro Plus (Sasaran Bulanan) saya.
Jika Excel anda belum mempunyai tatasusunan dinamik, tetapi anda benar-benar mahu bekerja dengannya, maka terdapat pilihan berikut:
- Jika anda mempunyai langganan Office 365, anda hanya boleh menunggu sehingga kemas kini ini sampai kepada anda. Seberapa cepat perkara ini berlaku bergantung pada kekerapan kemas kini dihantar ke Pejabat anda (setahun sekali, enam bulan sekali, sebulan sekali). Jika anda mempunyai PC korporat, anda boleh meminta pentadbir anda menyediakan kemas kini untuk dimuat turun dengan lebih kerap.
- Anda boleh menyertai barisan sukarelawan ujian Office Insiders tersebut – maka anda akan menjadi orang pertama yang menerima semua ciri dan fungsi baharu (tetapi terdapat peluang untuk meningkatkan kereta dalam Excel, sudah tentu).
- Jika anda tidak mempunyai langganan, tetapi versi Excel kendiri berkotak, maka anda perlu menunggu sehingga keluaran versi Office dan Excel seterusnya pada tahun 2022, sekurang-kurangnya. Pengguna versi sedemikian hanya menerima kemas kini keselamatan dan pembetulan pepijat, dan semua "barangan" baharu kini hanya diberikan kepada pelanggan Office 365. Sedih tapi benar 🙂
Walau apa pun, apabila tatasusunan dinamik muncul dalam Excel anda – selepas artikel ini, anda akan bersedia untuk itu 🙂
- Apakah formula tatasusunan dan cara menggunakannya dalam Excel
- Penjumlahan tetingkap (julat) menggunakan fungsi OFFSET
- 3 Cara untuk Mengubah Jadual dalam Excel