Cara mengatur rentang nilai di Excel

Saat menggunakan Microsoft Excel, sangat sering pengguna tidak mengetahui sebelumnya berapa banyak informasi yang akan ada di tabel sebagai hasilnya. Oleh karena itu, kami tidak mengerti dalam semua situasi kisaran apa yang harus dicakup. Bagaimanapun, satu set sel adalah konsep variabel. Untuk menghilangkan masalah ini, perlu dibuat pembuatan rentang otomatis sehingga hanya didasarkan pada jumlah data yang dimasukkan oleh pengguna.

Secara otomatis mengubah rentang sel di Excel

Keuntungan rentang otomatis di Excel adalah membuatnya lebih mudah untuk menggunakan rumus. Selain itu, mereka memungkinkan untuk secara signifikan menyederhanakan analisis data kompleks yang berisi sejumlah besar rumus, yang mencakup banyak fungsi. Anda dapat memberi nama rentang ini, dan kemudian akan diperbarui secara otomatis tergantung pada data apa yang dikandungnya.

Cara membuat perubahan jangkauan otomatis di Excel

Misalkan Anda adalah seorang investor yang perlu berinvestasi dalam beberapa objek. Akibatnya, kami ingin mendapatkan informasi tentang berapa banyak yang dapat Anda peroleh secara total selama seluruh waktu uang akan bekerja untuk proyek ini. Namun demikian, untuk mendapatkan informasi ini, kita perlu secara teratur memantau berapa banyak total keuntungan yang diperoleh objek ini bagi kita. Buat laporan yang sama seperti pada tangkapan layar ini.

Sekilas, solusinya jelas: Anda hanya perlu menjumlahkan seluruh kolom. Jika entri muncul di dalamnya, jumlahnya akan diperbarui secara independen. Tetapi metode ini memiliki banyak kelemahan:

1. Jika masalah diselesaikan dengan cara ini, tidak mungkin menggunakan sel yang disertakan dalam kolom B untuk tujuan lain.
2. Tabel seperti itu akan menghabiskan banyak RAM, yang membuat dokumen tidak mungkin digunakan pada komputer yang lemah.

Oleh karena itu, perlu untuk memecahkan masalah ini melalui nama dinamis. Untuk membuatnya, Anda harus melakukan urutan tindakan berikut:

1. Buka tab "Rumus", yang terletak di menu utama. Akan ada bagian "Nama yang ditentukan", di mana ada tombol "Tetapkan nama", di mana kita perlu mengklik.
2. Selanjutnya, kotak dialog akan muncul di mana Anda harus mengisi bidang seperti yang ditunjukkan pada tangkapan layar. Penting untuk dicatat bahwa kita perlu menerapkan fungsi = PERGANTIAN bersama dengan fungsi TARIFuntuk membuat rentang pembaruan otomatis. 
3. Setelah itu kita perlu menggunakan fungsi JUMLAH, yang menggunakan rentang dinamis kami sebagai argumen. 

Setelah menyelesaikan langkah-langkah ini, kita dapat melihat bagaimana cakupan sel yang termasuk dalam rentang "pendapatan" diperbarui saat kita menambahkan elemen baru di sana.

Fungsi OFFSET di Excel

Mari kita lihat fungsi-fungsi yang telah kita rekam di kolom “range” tadi. Menggunakan fungsi PEMBUANGAN kita dapat menentukan jumlah range mengingat berapa banyak sel di kolom B yang terisi. Argumen fungsi adalah sebagai berikut:

1. Mulai sel. Dengan argumen ini, pengguna dapat menunjukkan sel mana dalam rentang yang akan dianggap kiri atas. Ini akan melaporkan ke bawah dan ke kanan.
2. Rentang diimbangi oleh baris. Dengan menggunakan rentang ini, kami menetapkan jumlah sel di mana offset harus terjadi dari sel kiri atas rentang. Anda tidak hanya dapat menggunakan nilai positif, tetapi juga nol dan minus. Dalam hal ini, perpindahan mungkin tidak terjadi sama sekali, atau akan dilakukan dalam arah yang berlawanan.
3. Rentang diimbangi oleh kolom. Parameter ini mirip dengan yang sebelumnya, hanya memungkinkan Anda untuk mengatur tingkat pergeseran horizontal rentang. Di sini Anda juga dapat menggunakan nilai nol dan negatif.
4. Jumlah rentang ketinggian. Sebenarnya, judul argumen ini menjelaskan kepada kita apa artinya. Ini adalah jumlah sel yang rentangnya harus ditingkatkan.
5. Nilai rentang lebar. Argumennya mirip dengan yang sebelumnya, hanya menyangkut kolom.

Anda tidak perlu menentukan dua argumen terakhir jika tidak perlu. Dalam hal ini, nilai rentang hanya akan menjadi satu sel. Misalnya, jika Anda menentukan rumus =OFFSET(A1;0;0), rumus ini akan merujuk ke sel yang sama dengan yang ada di argumen pertama. Jika offset vertikal diatur ke 2 unit, maka dalam hal ini sel akan merujuk ke sel A3. Sekarang mari kita jelaskan secara rinci apa arti fungsinya TARIF.

Fungsi COUNT di Excel

Menggunakan fungsi TARIF kita tentukan berapa jumlah sel pada kolom B yang telah kita isi secara total. Artinya, dengan menggunakan dua fungsi, kami menentukan berapa banyak sel dalam rentang yang diisi, dan berdasarkan informasi yang diterima, kami menentukan ukuran rentang. Oleh karena itu, rumus akhirnya akan menjadi sebagai berikut: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Mari kita lihat bagaimana memahami prinsip rumus ini dengan benar. Argumen pertama menunjukkan di mana rentang dinamis kita dimulai. Dalam kasus kami, ini adalah sel B2. Parameter lebih lanjut memiliki koordinat nol. Ini menunjukkan bahwa kita tidak memerlukan offset relatif terhadap sel kiri atas. Yang kami isi hanyalah ukuran vertikal rentang, yang kami gunakan fungsinya sebagai TARIF, yang menentukan jumlah sel yang berisi beberapa data. Parameter keempat yang kita isi adalah unit. Jadi kami menunjukkan bahwa lebar total rentang harus satu kolom.

Jadi, dengan menggunakan fungsi TARIF pengguna dapat menggunakan memori seefisien mungkin dengan hanya memuat sel-sel yang berisi beberapa nilai. Dengan demikian, tidak akan ada kesalahan tambahan dalam pekerjaan yang terkait dengan buruknya kinerja komputer tempat spreadsheet akan bekerja.

Dengan demikian, untuk menentukan ukuran rentang tergantung pada jumlah kolom, Anda perlu melakukan urutan tindakan yang serupa, hanya dalam hal ini Anda perlu menentukan unit di parameter ketiga, dan rumus di parameter keempat TARIF.

Kami melihat bahwa dengan bantuan rumus Excel, Anda tidak hanya dapat mengotomatiskan perhitungan matematis. Ini hanya setetes di lautan, tetapi sebenarnya mereka memungkinkan Anda untuk mengotomatisasi hampir semua operasi yang muncul di pikiran.

Bagan Dinamis di Excel

Jadi, pada langkah terakhir, kami dapat membuat rentang dinamis, yang ukurannya sepenuhnya bergantung pada berapa banyak sel terisi yang dikandungnya. Sekarang, berdasarkan data ini, Anda dapat membuat bagan dinamis yang akan berubah secara otomatis segera setelah pengguna membuat perubahan atau menambahkan kolom atau baris tambahan. Urutan tindakan dalam hal ini adalah sebagai berikut:

1. Kami memilih rentang kami, setelah itu kami memasukkan bagan tipe "Histogram dengan pengelompokan". Anda dapat menemukan item ini di bagian “Sisipkan” di bagian “Bagan – Histogram”.
2. Kami membuat klik kiri mouse pada kolom acak dari histogram, setelah itu fungsi =SERIES() akan ditampilkan di baris fungsi. Di tangkapan layar Anda dapat melihat rumus terperinci. 
3. Setelah itu, beberapa perubahan perlu dilakukan pada formula. Anda perlu mengganti rentang setelah "Sheet1!" dengan nama rentang. Ini akan menghasilkan fungsi berikut: =ROW(Lembar1!$B$1;;Lembar1!penghasilan;1)
4. Sekarang tinggal menambahkan catatan baru ke laporan untuk memeriksa apakah grafik diperbarui secara otomatis atau tidak.

Mari kita lihat diagram kita.

Mari kita rangkum bagaimana kita melakukannya. Pada langkah sebelumnya, kami membuat rentang dinamis, yang ukurannya tergantung pada berapa banyak elemen yang dikandungnya. Untuk melakukan ini, kami menggunakan kombinasi fungsi TARIF и PEMBUANGAN. Kami membuat rentang ini dinamai, dan kemudian kami menggunakan referensi ke nama ini sebagai rentang histogram kami. Rentang spesifik mana yang dipilih sebagai sumber data pada tahap pertama tidak begitu penting. Yang utama adalah menggantinya dengan nama range nantinya. Dengan cara ini Anda dapat menghemat banyak RAM.

Rentang bernama dan kegunaannya

Sekarang mari kita bicara lebih detail tentang cara membuat rentang bernama dengan benar dan menggunakannya untuk melakukan tugas yang ditetapkan untuk pengguna Excel.

Secara default, kami menggunakan alamat sel biasa untuk menghemat waktu. Ini berguna ketika Anda perlu menulis rentang satu kali atau lebih. Jika perlu digunakan sepanjang waktu atau perlu adaptif, maka rentang bernama harus digunakan. Mereka membuat pembuatan rumus menjadi lebih mudah, dan tidak akan terlalu sulit bagi pengguna untuk menganalisis rumus kompleks yang menyertakan sejumlah besar fungsi. Mari kita jelaskan beberapa langkah yang terlibat dalam membuat rentang dinamis.

Semuanya dimulai dengan memberi nama sel. Untuk melakukan ini, cukup pilih, lalu tulis nama yang kita butuhkan di bidang namanya. Yang penting mudah diingat. Ada beberapa batasan yang perlu dipertimbangkan saat memberi nama:

1. Panjang maksimum adalah 255 karakter. Ini cukup untuk menetapkan nama yang diinginkan hati Anda.
2. Nama tidak boleh mengandung spasi. Oleh karena itu, jika mengandung beberapa kata, maka dimungkinkan untuk memisahkannya menggunakan karakter garis bawah.

Jika nanti pada lembar lain dari file ini kita perlu menampilkan nilai ini atau menerapkannya untuk melakukan perhitungan lebih lanjut, maka tidak perlu beralih ke lembar pertama. Anda cukup menuliskan nama sel rentang ini.

Langkah selanjutnya adalah membuat rentang bernama. Prosedurnya pada dasarnya sama. Pertama, Anda perlu memilih rentang, lalu menentukan namanya. Setelah itu, nama ini dapat digunakan di semua operasi data lainnya di Excel. Misalnya, rentang bernama sering digunakan untuk menentukan jumlah nilai.

Selain itu, dimungkinkan untuk membuat rentang bernama menggunakan tab Rumus menggunakan alat Setel Nama. Setelah kami memilihnya, sebuah jendela akan muncul di mana kami perlu memilih nama untuk jangkauan kami, serta menentukan area yang akan diperluas secara manual. Anda juga dapat menentukan di mana rentang ini akan beroperasi: dalam satu lembar atau di seluruh buku.

Jika rentang nama telah dibuat, maka untuk menggunakannya, ada layanan khusus yang disebut pengelola nama. Ini memungkinkan tidak hanya mengedit atau menambahkan nama baru, tetapi juga menghapusnya jika tidak lagi diperlukan.

Harus diingat bahwa ketika menggunakan rentang bernama dalam rumus, maka setelah menghapusnya, rumus tidak akan secara otomatis ditimpa dengan nilai yang benar. Oleh karena itu, kesalahan dapat terjadi. Oleh karena itu, sebelum menghapus rentang bernama, Anda perlu memastikan bahwa rentang tersebut tidak digunakan dalam rumus apa pun.

Cara lain untuk membuat rentang bernama adalah dengan mendapatkannya dari tabel. Untuk melakukan ini, ada alat khusus yang disebut "Buat dari Seleksi". Seperti yang kami pahami, untuk menggunakannya, Anda harus terlebih dahulu memilih rentang yang akan kami edit, dan kemudian mengatur tempat di mana kami memiliki judul. Akibatnya, berdasarkan data ini, Excel akan secara otomatis memproses semua data, dan judul akan ditetapkan secara otomatis.

Jika judul berisi beberapa kata, Excel akan secara otomatis memisahkannya dengan garis bawah.

Jadi, kami menemukan cara membuat rentang bernama dinamis dan bagaimana rentang tersebut memungkinkan Anda untuk mengotomatisasi pekerjaan dengan data dalam jumlah besar. Seperti yang Anda lihat, cukup menggunakan beberapa fungsi dan alat program yang ada di dalam fungsi tersebut. Tidak ada yang rumit sama sekali, meskipun mungkin tampak begitu bagi pemula pada pandangan pertama.