Konten
Apakah Anda memiliki tabel dengan data di Excel yang dapat diubah ukurannya, yaitu jumlah baris (kolom) yang dapat bertambah atau berkurang selama bekerja? Jika ukuran tabel "mengambang", maka Anda harus terus memantau momen ini dan memperbaikinya:
- tautan dalam rumus laporan yang merujuk ke tabel kami
- rentang awal tabel pivot yang dibangun sesuai dengan tabel kami
- rentang awal grafik yang dibuat sesuai dengan tabel kami
- rentang untuk dropdown yang menggunakan tabel kami sebagai sumber data
Semua ini secara total tidak akan membuat Anda bosan
Akan jauh lebih nyaman dan benar untuk membuat rentang "karet" dinamis, yang secara otomatis akan menyesuaikan ukurannya dengan jumlah baris dan kolom data yang sebenarnya. Untuk mengimplementasikannya, ada beberapa cara.
Metode 1. Meja pintar
Sorot rentang sel Anda dan pilih dari tab Beranda – Format sebagai Tabel (Home – Format sebagai Tabel):
Jika Anda tidak membutuhkan desain bergaris yang ditambahkan ke tabel sebagai efek samping, maka Anda dapat mematikannya pada tab yang muncul Konstruktor (Desain). Setiap tabel yang dibuat dengan cara ini menerima nama yang dapat diganti dengan yang lebih nyaman di tempat yang sama pada tab Konstruktor (Desain) di lapangan Nama tabel (Nama Tabel).
Sekarang kita dapat menggunakan tautan dinamis ke "tabel pintar" kita:
- Tabel 1 – link ke seluruh tabel kecuali baris header (A2:D5)
- Tabel1[#Semua] – link ke seluruh tabel (A1:D5)
- Tabel1[Petrus] – referensi ke kolom rentang tanpa header sel pertama (C2:C5)
- Tabel1[#Header] – link ke “header” dengan nama kolom (A1:D1)
Referensi seperti itu berfungsi dengan baik dalam rumus, misalnya:
= SUM (Tabel1[Moskow]) – perhitungan jumlah untuk kolom “Moskow”
or
=VPR(F5;Tabel 1;3;0) – cari di tabel untuk bulan dari sel F5 dan keluarkan jumlah St. Petersburg untuk itu (apa itu VLOOKUP?)
Tautan tersebut dapat berhasil digunakan saat membuat tabel pivot dengan memilih pada tab Sisipkan – Tabel Pivot (Sisipkan – Tabel Pivot) dan memasukkan nama tabel pintar sebagai sumber data:
Jika Anda memilih fragmen dari tabel seperti itu (misalnya, dua kolom pertama) dan membuat diagram jenis apa pun, maka saat menambahkan baris baru, mereka akan secara otomatis ditambahkan ke diagram.
Saat membuat daftar drop-down, tautan langsung ke elemen tabel cerdas tidak dapat digunakan, tetapi Anda dapat dengan mudah mengatasi batasan ini menggunakan trik taktis – gunakan fungsi TIDAK LANGSUNG (TIDAK LANGSUNG), yang mengubah teks menjadi tautan:
Itu. tautan ke tabel cerdas dalam bentuk string teks (dalam tanda kutip!) berubah menjadi tautan lengkap, dan daftar drop-down biasanya melihatnya.
Metode 2: Rentang bernama dinamis
Jika mengubah data Anda menjadi tabel cerdas tidak diinginkan karena alasan tertentu, maka Anda dapat menggunakan metode yang sedikit lebih rumit, tetapi jauh lebih halus dan serbaguna – buat rentang nama dinamis di Excel yang merujuk ke tabel kami. Kemudian, seperti dalam kasus tabel cerdas, Anda dapat dengan bebas menggunakan nama rentang yang dibuat dalam rumus, laporan, bagan, dll. Mari kita mulai dengan contoh sederhana:
tugas: membuat rentang nama dinamis yang akan merujuk ke daftar kota dan secara otomatis meregangkan dan menyusut ukurannya saat menambahkan kota baru atau menghapusnya.
Kami akan membutuhkan dua fungsi Excel bawaan yang tersedia di versi apa pun POICPOZ (COCOK) untuk menentukan sel terakhir dari rentang, dan INDEKS (INDEKS) untuk membuat tautan dinamis.
Menemukan sel terakhir menggunakan MATCH
MATCH(nilai_pencarian, rentang, jenis_kecocokan) – fungsi yang mencari nilai tertentu dalam rentang (baris atau kolom) dan mengembalikan nomor urut sel tempat ditemukannya. Misalnya, rumus MATCH(“Maret”;A1:A5;0) akan menghasilkan angka 4 sebagai hasilnya, karena kata “Maret” terletak di sel keempat di kolom A1:A5. Argumen fungsi terakhir Match_Type = 0 berarti kita mencari kecocokan yang sama persis. Jika argumen ini tidak ditentukan, maka fungsi akan beralih ke mode pencarian untuk nilai terkecil terdekat – inilah yang dapat berhasil digunakan untuk menemukan sel terakhir yang ditempati dalam array kita.
Inti dari triknya sederhana. MATCH mencari sel dalam rentang dari atas ke bawah dan, secara teori, harus berhenti ketika menemukan nilai terkecil terdekat dengan yang diberikan. Jika Anda menentukan nilai yang jelas lebih besar daripada yang tersedia di tabel sebagai nilai yang diinginkan, maka MATCH akan mencapai bagian paling akhir tabel, tidak menemukan apa pun dan memberikan nomor urut sel yang diisi terakhir. Dan kita membutuhkannya!
Jika hanya ada angka dalam array kita, maka kita dapat menentukan angka sebagai nilai yang diinginkan, yang jelas lebih besar daripada yang ada di tabel:
Sebagai jaminan, Anda dapat menggunakan angka 9E + 307 (9 kali 10 pangkat 307, yaitu 9 dengan 307 nol) – angka maksimum yang dapat digunakan Excel pada prinsipnya.
Jika ada nilai teks di kolom kami, maka sebagai ekuivalen dengan angka terbesar yang mungkin, Anda dapat memasukkan konstruksi REPEAT(“i”, 255) – string teks yang terdiri dari 255 huruf “i” – huruf terakhir dari alfabet. Karena Excel benar-benar membandingkan kode karakter saat mencari, teks apa pun di tabel kami secara teknis akan "lebih kecil" daripada baris "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy" yang panjang:
Buat tautan menggunakan INDEX
Sekarang setelah kita mengetahui posisi elemen terakhir yang tidak kosong dalam tabel, itu tetap membentuk tautan ke seluruh rentang kita. Untuk ini kami menggunakan fungsi:
INDEX(rentang; jumlah_baris; jumlah_kolom)
Ini memberikan isi sel dari rentang dengan nomor baris dan kolom, yaitu misalnya, fungsi =INDEX(A1:D5;3;4) di tabel kami dengan kota dan bulan dari metode sebelumnya akan memberikan 1240 – konten dari baris ke-3 dan kolom ke-4, yaitu sel D3. Jika hanya ada satu kolom, maka nomornya dapat dihilangkan, yaitu rumus INDEX(A2:A6;3) akan memberikan “Samara” di screenshot terakhir.
Dan ada satu nuansa yang tidak sepenuhnya jelas: jika INDEX tidak hanya dimasukkan ke dalam sel setelah tanda =, seperti biasa, tetapi digunakan sebagai bagian akhir dari referensi ke rentang setelah titik dua, maka ia tidak lagi memberikan isi sel, tapi alamatnya! Jadi, rumus seperti $A$2:INDEX($A$2:$A$100;3) akan memberikan referensi ke rentang A2:A4 pada output.
Dan di sinilah fungsi MATCH masuk, yang kita masukkan ke dalam INDEX untuk menentukan akhir daftar secara dinamis:
=$A$2:INDEKS($A$2:$A$100; PERTANDINGAN(REP("Saya";255);A2:A100))
Buat rentang bernama
Tetap mengemas semuanya menjadi satu kesatuan. Buka tab rumus (Rumus) Dan klik Manajer nama (Manajer Nama). Di jendela yang terbuka, klik tombol membuat (baru), masukkan nama rentang dan rumus kami di bidang Jarak (Referensi):
Tetap mengklik OK dan rentang siap dapat digunakan dalam formula, daftar drop-down, atau bagan apa pun.
- Menggunakan fungsi VLOOKUP untuk menautkan tabel dan mencari nilai
- Cara membuat daftar dropdown yang terisi otomatis
- Cara membuat tabel pivot untuk menganalisis sejumlah besar data