Contents [show]
Adakah anda mempunyai jadual dengan data dalam Excel yang boleh diubah saiznya, iaitu bilangan baris (lajur) boleh bertambah atau berkurang dalam perjalanan kerja? Jika saiz jadual "terapung", maka anda perlu sentiasa memantau masa ini dan membetulkannya:
- pautan dalam formula laporan yang merujuk kepada jadual kami
- julat awal jadual pangsi yang dibina mengikut jadual kami
- julat awal carta yang dibina mengikut jadual kami
- julat untuk lungsur turun yang menggunakan jadual kami sebagai sumber data
Semua ini secara keseluruhan tidak akan membiarkan anda bosan 😉
Ia akan menjadi lebih mudah dan betul untuk mencipta julat "getah" dinamik, yang akan melaraskan saiz secara automatik kepada bilangan baris dan lajur data sebenar. Untuk melaksanakannya, terdapat beberapa cara.
Kaedah 1. Jadual pintar
Serlahkan julat sel anda dan pilih daripada tab Laman Utama – Format sebagai Jadual (Laman Utama – Format sebagai Jadual):
Jika anda tidak memerlukan reka bentuk berjalur yang ditambahkan pada jadual sebagai kesan sampingan, maka anda boleh mematikannya pada tab yang muncul Pembina (Reka Bentuk). Setiap jadual yang dibuat dengan cara ini menerima nama yang boleh digantikan dengan yang lebih mudah di tempat yang sama pada tab Pembina (Reka Bentuk) dalam bidang Nama jadual (Nama Jadual).
Kini kami boleh menggunakan pautan dinamik ke "jadual pintar" kami:
- Jadual 1 – pautan ke seluruh jadual kecuali baris pengepala (A2:D5)
- Jadual1[#Semua] – pautan ke seluruh jadual (A1:D5)
- Jadual1[Peter] – merujuk kepada lajur julat tanpa pengepala sel pertama (C2:C5)
- Jadual1[#Headers] – pautan ke "pengepala" dengan nama lajur (A1:D1)
Rujukan sedemikian berfungsi dengan baik dalam formula, contohnya:
= SUM (Jadual1[Moscow]) – pengiraan jumlah untuk lajur "Moscow"
or
=VPR(F5;Jadual 1;3;0) – cari dalam jadual untuk bulan dari sel F5 dan keluarkan jumlah St. Petersburg untuknya (apa itu VLOOKUP?)
Pautan sedemikian boleh berjaya digunakan semasa membuat jadual pangsi dengan memilih pada tab Sisipkan – Jadual Pangsi (Sisipkan – Jadual Pangsi) dan memasukkan nama jadual pintar sebagai sumber data:
Jika anda memilih serpihan jadual sedemikian (contohnya, dua lajur pertama) dan mencipta gambar rajah dari sebarang jenis, maka apabila menambah baris baharu, ia akan ditambahkan secara automatik pada rajah.
Apabila membuat senarai juntai bawah, pautan terus ke elemen jadual pintar tidak boleh digunakan, tetapi anda boleh mengatasi had ini dengan mudah menggunakan helah taktikal – gunakan fungsi TIDAK LANGSUNG (LANGSUNG), yang menukar teks menjadi pautan:
Itu. pautan ke jadual pintar dalam bentuk rentetan teks (dalam tanda petikan!) bertukar menjadi pautan penuh dan senarai juntai bawah biasanya melihatnya.
Kaedah 2: Julat bernama dinamik
Jika menukar data anda kepada jadual pintar adalah tidak diingini atas sebab tertentu, maka anda boleh menggunakan kaedah yang sedikit lebih rumit, tetapi lebih halus dan serba boleh – buat julat bernama dinamik dalam Excel yang merujuk kepada jadual kami. Kemudian, seperti dalam kes jadual pintar, anda boleh menggunakan nama julat yang dibuat secara bebas dalam sebarang formula, laporan, carta, dll. Mari kita mulakan dengan contoh mudah:
Petugas: buat julat bernama dinamik yang akan merujuk kepada senarai bandar dan secara automatik meregang dan mengecil dalam saiz apabila menambah bandar baharu atau memadamkannya.
Kami memerlukan dua fungsi Excel terbina dalam yang tersedia dalam mana-mana versi − POICPOZ (PERLAWANAN) untuk menentukan sel terakhir julat, dan INDEKS (INDEX) untuk membuat pautan dinamik.
Mencari sel terakhir menggunakan MATCH
MATCH(nilai_cari, julat, jenis_padanan) – fungsi yang mencari nilai tertentu dalam julat (baris atau lajur) dan mengembalikan nombor ordinal sel tempat ia ditemui. Sebagai contoh, formula MATCH("Mac";A1:A5;0) akan mengembalikan nombor 4 sebagai hasilnya, kerana perkataan "Mac" terletak dalam sel keempat dalam lajur A1:A5. Argumen fungsi terakhir Match_Type = 0 bermakna kami sedang mencari padanan yang tepat. Jika hujah ini tidak dinyatakan, maka fungsi akan bertukar kepada mod carian untuk nilai terkecil yang terdekat - inilah yang boleh berjaya digunakan untuk mencari sel yang diduduki terakhir dalam tatasusunan kami.
Intipati helah itu mudah. MATCH mencari sel dalam julat dari atas ke bawah dan, secara teori, harus berhenti apabila ia menemui nilai terkecil yang terdekat dengan yang diberikan. Jika anda menentukan nilai yang jelas lebih besar daripada mana-mana yang tersedia dalam jadual sebagai nilai yang diingini, maka MATCH akan sampai ke penghujung jadual, mencari apa-apa dan memberikan nombor jujukan sel yang diisi terakhir. Dan kami memerlukannya!
Jika terdapat hanya nombor dalam tatasusunan kami, maka kami boleh menentukan nombor sebagai nilai yang diingini, yang jelas lebih besar daripada mana-mana nombor dalam jadual:
Untuk jaminan, anda boleh menggunakan nombor 9E + 307 (9 kali 10 kepada kuasa 307, iaitu 9 dengan 307 sifar) – nombor maksimum yang boleh digunakan oleh Excel pada dasarnya.
Jika terdapat nilai teks dalam lajur kami, maka sebagai setara dengan nombor terbesar yang mungkin, anda boleh memasukkan pembinaan REPEAT(“i”, 255) – rentetan teks yang terdiri daripada 255 huruf “i” – huruf terakhir bagi abjad. Memandangkan Excel sebenarnya membandingkan kod aksara semasa mencari, sebarang teks dalam jadual kami secara teknikalnya akan menjadi "lebih kecil" daripada baris "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
Hasilkan pautan menggunakan INDEX
Sekarang setelah kita mengetahui kedudukan elemen bukan kosong terakhir dalam jadual, ia kekal membentuk pautan ke keseluruhan julat kami. Untuk ini kami menggunakan fungsi:
INDEX(julat; nombor_baris; bilangan_lajur)
Ia memberikan kandungan sel daripada julat mengikut nombor baris dan lajur, contohnya, fungsi =INDEX(A1:D5;3;4) dalam jadual kami dengan bandar dan bulan dari kaedah sebelumnya akan memberikan 1240 – kandungan daripada baris ke-3 dan lajur ke-4, iaitu sel D3. Jika terdapat hanya satu lajur, maka nombornya boleh ditinggalkan, iaitu formula INDEX(A2:A6;3) akan memberikan "Samara" dalam tangkapan skrin terakhir.
Dan terdapat satu nuansa yang tidak sepenuhnya jelas: jika INDEX bukan sahaja dimasukkan ke dalam sel selepas tanda =, seperti biasa, tetapi digunakan sebagai bahagian akhir rujukan kepada julat selepas titik bertindih, maka ia tidak lagi mengeluarkan kandungan sel, tetapi alamatnya! Oleh itu, formula seperti $A$2:INDEX($A$2:$A$100;3) akan memberikan rujukan kepada julat A2:A4 pada output.
Dan di sinilah fungsi MATCH masuk, yang kami masukkan ke dalam INDEX untuk menentukan penghujung senarai secara dinamik:
=$A$2:INDEX($A$2:$A$100; PERLAWANAN(REP(“Saya”;255);A2:A100))
Buat julat bernama
Ia kekal untuk membungkus semuanya menjadi satu keseluruhan. Buka tab formula (Formula) Dan klik Pengurus Nama (Pengurus Nama). Dalam tetingkap yang terbuka, klik butang Buat (baru), masukkan nama julat dan formula kami dalam medan Pertengahan (Rujukan):
Ia kekal untuk mengklik OK dan julat sedia boleh digunakan dalam sebarang formula, senarai juntai bawah atau carta.
- Menggunakan fungsi VLOOKUP untuk memautkan jadual dan nilai carian
- Cara membuat senarai juntai bawah yang mengisi automatik
- Cara membuat jadual pangsi untuk menganalisis sejumlah besar data