Bagaimana untuk menetapkan julat nilai dalam Excel

Apabila menggunakan Microsoft Excel, selalunya pengguna tidak mengetahui terlebih dahulu berapa banyak maklumat yang akan ada dalam jadual akibatnya. Oleh itu, kami tidak faham dalam semua situasi apakah julat yang perlu dilindungi. Lagipun, satu set sel adalah konsep berubah-ubah. Untuk menyingkirkan masalah ini, adalah perlu untuk membuat penjanaan julat automatik supaya ia hanya berdasarkan jumlah data yang dimasukkan oleh pengguna.

Menukar julat sel secara automatik dalam Excel

Kelebihan julat automatik dalam Excel ialah ia menjadikannya lebih mudah untuk menggunakan formula. Di samping itu, mereka memungkinkan untuk memudahkan analisis data kompleks dengan ketara yang mengandungi sejumlah besar formula, yang merangkumi banyak fungsi. Anda boleh memberi nama julat ini, dan kemudian ia akan dikemas kini secara automatik bergantung pada data yang terkandung di dalamnya.

Bagaimana untuk membuat perubahan julat automatik dalam Excel

Katakan anda seorang pelabur yang perlu melabur dalam beberapa objek. Akibatnya, kami ingin mendapatkan maklumat tentang jumlah keseluruhan yang anda boleh perolehi sepanjang masa wang itu akan berfungsi untuk projek ini. Namun begitu, untuk mendapatkan maklumat ini, kami perlu sentiasa memantau jumlah keuntungan yang dibawa oleh objek ini kepada kami. Buat laporan yang sama seperti dalam tangkapan skrin ini.

Pada pandangan pertama, penyelesaiannya adalah jelas: anda hanya perlu menjumlahkan keseluruhan lajur. Jika entri muncul di dalamnya, jumlah akan dikemas kini secara bebas. Tetapi kaedah ini mempunyai banyak kelemahan:

1. Jika masalah diselesaikan dengan cara ini, sel yang disertakan dalam lajur B tidak akan dapat digunakan untuk tujuan lain.
2. Jadual sedemikian akan menggunakan banyak RAM, yang akan menjadikannya mustahil untuk menggunakan dokumen pada komputer yang lemah.

Oleh itu, adalah perlu untuk menyelesaikan masalah ini melalui nama dinamik. Untuk menciptanya, anda mesti melakukan urutan tindakan berikut:

1. Pergi ke tab "Formula", yang terletak di menu utama. Akan ada bahagian "Nama yang ditentukan", di mana terdapat butang "Tetapkan nama", yang perlu kita klik.
2. Seterusnya, kotak dialog akan muncul di mana anda perlu mengisi medan seperti yang ditunjukkan dalam tangkapan skrin. Adalah penting untuk ambil perhatian bahawa kita perlu menggunakan fungsi tersebut = PERPINDAHAN bersama-sama dengan fungsi CHECKuntuk mencipta julat pengemaskinian automatik. 
3. Selepas itu kita perlu menggunakan fungsi tersebut SUM, yang menggunakan julat dinamik kami sebagai hujah. 

Selepas melengkapkan langkah-langkah ini, kita dapat melihat cara liputan sel kepunyaan julat "pendapatan" dikemas kini semasa kami menambah elemen baharu di sana.

Fungsi OFFSET dalam Excel

Mari lihat fungsi yang kami rakamkan dalam medan “julat” tadi. Menggunakan fungsi PELUPUSAN kita boleh menentukan jumlah julat yang diberikan berapa banyak sel dalam lajur B diisi. Argumen fungsi adalah seperti berikut:

1. Mulakan sel. Dengan hujah ini, pengguna boleh menunjukkan sel mana dalam julat yang akan dianggap kiri atas. Ia akan melaporkan ke bawah dan ke kanan.
2. Julat diimbangi oleh baris. Dengan menggunakan julat ini, kami menetapkan bilangan sel yang mengimbangi harus berlaku dari sel kiri atas julat. Anda boleh menggunakan bukan sahaja nilai positif, tetapi sifar dan tolak. Dalam kes ini, anjakan mungkin tidak berlaku sama sekali, atau ia akan dijalankan dalam arah yang bertentangan.
3. Julat diimbangi oleh lajur. Parameter ini serupa dengan yang sebelumnya, hanya ia membolehkan anda menetapkan tahap anjakan mendatar julat. Di sini anda juga boleh menggunakan kedua-dua nilai sifar dan negatif.
4. Jumlah julat ketinggian. Malah, tajuk hujah ini menjelaskan kepada kita apa maksudnya. Ini ialah bilangan sel yang mana julatnya harus meningkat.
5. Nilai julat dalam lebar. Hujah adalah serupa dengan yang sebelumnya, hanya ia berkaitan dengan lajur.

Anda tidak perlu menentukan dua hujah terakhir jika anda tidak perlu. Dalam kes ini, nilai julat hanya akan menjadi satu sel. Sebagai contoh, jika anda menentukan formula =OFFSET(A1;0;0), formula ini akan merujuk kepada sel yang sama seperti dalam hujah pertama. Jika offset menegak ditetapkan kepada 2 unit, maka dalam kes ini sel akan merujuk kepada sel A3. Sekarang mari kita terangkan secara terperinci apa maksud fungsi tersebut CHECK.

COUNT fungsi dalam Excel

Menggunakan fungsi CHECK kita tentukan bilangan sel dalam lajur B yang telah kita isi secara keseluruhan. Iaitu, menggunakan dua fungsi, kami menentukan bilangan sel dalam julat yang diisi, dan berdasarkan maklumat yang diterima, menentukan saiz julat. Oleh itu, formula akhir adalah seperti berikut: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Mari kita lihat cara memahami prinsip formula ini dengan betul. Hujah pertama menunjukkan di mana julat dinamik kami bermula. Dalam kes kami, ini adalah sel B2. Parameter lanjut mempunyai koordinat sifar. Ini menunjukkan bahawa kita tidak memerlukan offset berbanding sel kiri atas. Apa yang kami isi ialah saiz menegak julat, yang kami gunakan fungsi sebagai CHECK, yang menentukan bilangan sel yang mengandungi beberapa data. Parameter keempat yang kami isi ialah unit. Oleh itu, kami menunjukkan bahawa jumlah lebar julat hendaklah satu lajur.

Oleh itu, menggunakan fungsi CHECK pengguna boleh menggunakan memori secekap mungkin dengan memuatkan hanya sel-sel yang mengandungi beberapa nilai. Sehubungan itu, tidak akan ada ralat tambahan dalam kerja yang berkaitan dengan prestasi buruk komputer yang akan digunakan hamparan.

Sehubungan itu, untuk menentukan saiz julat bergantung pada bilangan lajur, anda perlu melakukan urutan tindakan yang serupa, hanya dalam kes ini anda perlu menentukan unit dalam parameter ketiga, dan formula dalam yang keempat CHECK.

Kami melihat bahawa dengan bantuan formula Excel, anda bukan sahaja boleh mengautomasikan pengiraan matematik. Ini hanyalah setitik di lautan, tetapi sebenarnya ia membenarkan anda mengautomasikan hampir semua operasi yang terlintas di fikiran.

Carta Dinamik dalam Excel

Jadi, dalam langkah terakhir, kami dapat mencipta julat dinamik, yang saiznya bergantung sepenuhnya pada bilangan sel yang terisi yang terkandung di dalamnya. Kini, berdasarkan data ini, anda boleh membuat carta dinamik yang akan berubah secara automatik sebaik sahaja pengguna membuat sebarang perubahan atau menambah lajur atau baris tambahan. Urutan tindakan dalam kes ini adalah seperti berikut:

1. Kami memilih julat kami, selepas itu kami memasukkan carta jenis "Histogram dengan kumpulan". Anda boleh mencari item ini dalam bahagian "Sisipkan" dalam bahagian "Carta - Histogram".
2. Kami membuat klik tetikus kiri pada lajur rawak histogram, selepas itu fungsi =SERIES() akan ditunjukkan dalam baris fungsi. Dalam tangkapan skrin anda boleh melihat formula terperinci. 
3. Selepas itu, beberapa perubahan perlu dibuat pada formula. Anda perlu menggantikan julat selepas "Helaian1!" kepada nama julat. Ini akan menghasilkan fungsi berikut: =ROW(Helaian1!$B$1;;Lembaran1!pendapatan;1)
4. Kini ia kekal untuk menambah rekod baharu pada laporan untuk menyemak sama ada carta dikemas kini secara automatik atau tidak.

Mari lihat rajah kami.

Mari kita ringkaskan bagaimana kita melakukannya. Dalam langkah sebelumnya, kami mencipta julat dinamik, saiznya bergantung pada bilangan elemen yang terkandung di dalamnya. Untuk melakukan ini, kami menggunakan gabungan fungsi CHECK и PELUPUSAN. Kami membuat julat ini dinamakan, dan kemudian kami menggunakan rujukan kepada nama ini sebagai julat histogram kami. Julat khusus yang manakah untuk dipilih sebagai sumber data pada peringkat pertama tidak begitu penting. Perkara utama ialah menggantikannya dengan nama julat kemudian. Dengan cara ini anda boleh menjimatkan banyak RAM.

Julat yang dinamakan dan kegunaannya

Sekarang mari kita bercakap dengan lebih terperinci tentang cara membuat julat bernama dengan betul dan menggunakannya untuk melaksanakan tugas yang ditetapkan untuk pengguna Excel.

Secara lalai, kami menggunakan alamat sel biasa untuk menjimatkan masa. Ini berguna apabila anda perlu menulis julat satu kali atau lebih. Jika ia perlu digunakan sepanjang masa atau ia perlu disesuaikan, maka julat yang dinamakan harus digunakan. Mereka menjadikan penciptaan formula lebih mudah, dan ia tidak akan begitu sukar bagi pengguna untuk menganalisis formula kompleks yang merangkumi sejumlah besar fungsi. Mari kita terangkan beberapa langkah yang terlibat dalam mencipta julat dinamik.

Semuanya bermula dengan menamakan sel. Untuk melakukan ini, hanya pilihnya, dan kemudian tulis nama yang kita perlukan dalam medan namanya. Adalah penting bahawa ia mudah diingati. Terdapat beberapa sekatan yang perlu dipertimbangkan semasa menamakan:

1. Panjang maksimum ialah 255 aksara. Ini sudah cukup untuk memberikan nama yang diingini oleh hati anda.
2. Nama tidak boleh mengandungi ruang. Oleh itu, jika ia mengandungi beberapa perkataan, maka boleh dipisahkan menggunakan aksara garis bawah.

Jika kemudian pada helaian lain fail ini kita perlu memaparkan nilai ini atau menggunakannya untuk melakukan pengiraan selanjutnya, maka tidak perlu beralih ke helaian pertama. Anda hanya boleh menulis nama sel julat ini.

Langkah seterusnya ialah membuat julat bernama. Prosedurnya pada asasnya sama. Mula-mula anda perlu memilih julat, dan kemudian nyatakan namanya. Selepas itu, nama ini boleh digunakan dalam semua operasi data lain dalam Excel. Sebagai contoh, julat bernama sering digunakan untuk menentukan jumlah nilai.

Selain itu, adalah mungkin untuk membuat julat bernama menggunakan tab Rumus menggunakan alat Set Name. Selepas kami memilihnya, tetingkap akan muncul di mana kami perlu memilih nama untuk julat kami, serta menentukan kawasan yang akan dilanjutkan secara manual. Anda juga boleh menentukan tempat julat ini akan beroperasi: dalam satu helaian atau sepanjang buku.

Jika julat nama telah dibuat, maka untuk menggunakannya, terdapat perkhidmatan khas yang dipanggil pengurus nama. Ia membenarkan bukan sahaja mengedit atau menambah nama baharu, tetapi juga memadamkannya jika tidak diperlukan lagi.

Perlu diingat bahawa apabila menggunakan julat bernama dalam formula, maka selepas memadamkannya, formula tidak akan ditimpa secara automatik dengan nilai yang betul. Oleh itu, ralat mungkin berlaku. Oleh itu, sebelum memadam julat bernama, anda perlu memastikan bahawa ia tidak digunakan dalam mana-mana formula.

Satu lagi cara untuk mencipta julat bernama ialah mendapatkannya daripada jadual. Untuk melakukan ini, terdapat alat khas yang dipanggil "Buat daripada Pilihan". Seperti yang kami faham, untuk menggunakannya, anda mesti terlebih dahulu memilih julat yang akan kami edit, dan kemudian tetapkan tempat di mana kami mempunyai tajuk. Akibatnya, berdasarkan data ini, Excel akan memproses semua data secara automatik dan tajuk akan diberikan secara automatik.

Jika tajuk mengandungi beberapa perkataan, Excel akan memisahkannya secara automatik dengan garis bawah.

Oleh itu, kami mengetahui cara membuat julat bernama dinamik dan cara ia membolehkan anda mengautomasikan kerja dengan jumlah data yang besar. Seperti yang anda lihat, cukup untuk menggunakan beberapa fungsi dan alat program yang dibina ke dalam fungsi tersebut. Tiada apa-apa yang rumit sama sekali, walaupun ia mungkin kelihatan begitu kepada pemula pada pandangan pertama.