Semua yang anda perlu tahu mengenai tumpahan dalam excel

Pautan cepat

• Definisi yang harus anda ketahui terlebih dahulu

contoh tumpahan

Merujuk julat tumpah

Betulkan #spill biasa! Kesilapan

Kebanyakan formula dalam Excel mengembalikan hasil sel tunggal. Walau bagaimanapun, jika formula Excel mengembalikan satu set nilai, hasilnya akan

tumpahan ke sel -sel jiran. Definisi yang anda harus tahu terlebih dahulu

sebelum saya menunjukkan contoh -contoh tumpahan dalam tindakan, berikut adalah definisi beberapa istilah Excel yang berkaitan dalam panduan ini:

• Tumpahan: Apabila formula Excel mengembalikan satu set nilai (juga dikenali sebagai array).
• Array: Satu set data dalam pelbagai sel. Sebagai contoh, jika sel A1 hingga A20 mengandungi data, A1: A20 adalah julat, dan data dalam julat itu adalah array.
• Fungsi: Formula yang telah ditentukan sebelumnya yang melakukan pengiraan apabila digunakan dalam formula. Formula: gabungan fungsi, rujukan, nilai, pengendali, dan nama sel yang, apabila digunakan bersama -sama mengikut tanda sama (=), mengembalikan hasil
• formula array dinamik: formula yang mampu mengembalikan array.
• Formula Array Tumpahan: Formula yang kini mengembalikan array yang tumpah. Contoh -contoh tumpahan
• pertama, mari kita lihat contoh yang mudah ditumpahkan dalam Excel. Spreadsheet Excel ini mengandungi enam pasukan kuiz dan skor mereka selama berminggu -minggu satu hingga lima. Formula Array, menaipnya ke dalam sel H2 mengembalikan array tumpah yang meliputi H2 hingga H7. Perhatikan bagaimana, apabila saya memilih sel H2, Excel meletakkan sempadan sementara di sekitar sel -sel tersebut untuk mengingatkan saya bahawa ia adalah array yang tumpah. H3 hingga H7, ini akan mengganggu dan memecahkan array yang tumpah yang baru dibuat oleh Excel, menghasilkan #spill! ralat. Saya akan bercakap lebih lanjut mengenai #spill! kesilapan tidak lama lagi. sekarang, bagaimanapun, saya ingin menggunakan lebih banyak jenis hasil ini dengan menggunakan fungsi offset. Dalam kes ini, saya mahu Excel memberitahu saya apa yang setiap pasukan menjaringkan dalam minggu tertentu, bergantung pada nombor yang saya taip ke dalam sel i1. I2, saya akan menaip

  

  kerana saya mahu Excel bermula pada sel A2, tinggal di baris yang sama, bergerak melintasi bilangan lajur yang saya taipkan dalam sel i1, dan kembali Hasilnya adalah 6 baris ke bawah dan 1 lajur di seluruh. Oleh kerana Excel mengembalikan array iaitu 6 baris tinggi, walaupun saya hanya menaip formula ke dalam sel i2, hasilnya tumpahan dari sel i1 hingga i7. Jadual pengambilan data saya, saya juga ingin menambah nama pasukan ke lajur H. Untuk melakukan ini, saya akan menaip

  =B2:B7

  . Saya juga memohon beberapa pemformatan untuk meningkatkan kebolehbacaan spreadsheet. , tatasusunan tumpah akan menyesuaikan dengan sewajarnya. Matlamat saya adalah untuk memaparkan satu set keputusan pasukan tertentu sepanjang tempoh lima minggu, dengan pasukan yang dipersoalkan bergantung kepada nilai dalam sel i1. Pertama, saya akan menaip formula array dinamik

  

  ke dalam sel H3 untuk menghasilkan pelbagai bilangan minggu dalam sel H3 hingga L3.

  

  Sekarang, saya akan membuat senarai drop-down dalam sel i1 yang mengandungi nama pasukan yang disenaraikan dalam sel A2 hingga A7. Untuk melakukan ini, saya akan mengklik data & gt; Pengesahan Data, Pilih "Senarai" dalam medan "Benarkan", dan pilih sel A2 hingga A7 untuk menentukan sumber senarai, sebelum mengklik "OK." , Saya boleh menggunakan fungsi XLOOKUP untuk mengembalikan skor untuk pasukan saya yang dipilih sepanjang lima minggu. Dalam sel H3, saya akan menaip:

  

  Kembalikan array yang sepadan dari lajur B ke F. Oleh kerana hasilnya adalah lima lajur lebar, ia dipaparkan sebagai array tumpah di seluruh sel H3 hingga L3. Dalam kes ini, saya memilih Team Quizpicable saya dari senarai drop-down saya di Cell I1. dan Randarray. Menggunakan contoh yang sama seperti di atas, saya ingin memaparkan skor total untuk pasukan yang diberikan berdasarkan array tumpah dalam sel H3 hingga L3.

  jadi, menaip

  =B2:B7

  ke dalam sel L4 memberitahu Excel untuk jumlah array dalam julat tumpah bermula pada H3.

  Betulkan #spill biasa! Kesilapan

  Situasi tertentu akan menyebabkan Excel mengembalikan #Spill! ralat. Inilah yang perlu diperhatikan jika ini berlaku dalam spreadsheet anda, dan juga apa yang boleh anda lakukan untuk menyelesaikan masalah:

  penyebab #Spill! ERROR array tumpah melintasi sel yang digabungkan. Pilih sel yang digabungkan, dan klik "Gabungan dan Pusat" dalam kumpulan penjajaran tab rumah untuk menyahaktifkan sel. Sesuatu menyekat sel -sel di mana array tumpah cuba pergi. Formula array dinamik digunakan dalam jadual Excel yang diformat. Formula array dinamik telah menyebabkan Excel kehabisan ingatan.

  cara untuk memperbaiki ralat
  julat dalam formula array dinamik melangkaui tepi lembaran kerja. .
  Gerakkan formula array dinamik ke lokasi di luar jadual yang diformat. Sebagai alternatif, tukar jadual Excel yang diformat anda ke julat yang tidak diformat dengan memilih salah satu sel dalam jadual, dan mengklik "Tukar ke Range" dalam kumpulan Alat tab Reka Bentuk Jadual.
  julat tumpah tidak diketahui, kerana formula array dinamik mengandungi fungsi yang tidak menentu. . Malangnya, satu -satunya cara untuk mengatasi masalah ini adalah untuk mengelakkan menggunakan formula array dinamik yang membuat tatasusunan panjang berubah -ubah. , anda boleh menggunakan fungsi trimrange untuk memberitahu Excel untuk menolak baris kosong, pada dasarnya memangkas data untuk memasukkan hanya sel -sel yang diperlukan.

Atas ialah kandungan terperinci Semua yang anda perlu tahu mengenai tumpahan dalam excel. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!