Paragraf
Apa sampeyan duwe tabel kanthi data ing Excel sing bisa diowahi ukurane, yaiku jumlah larik (kolom) bisa nambah utawa nyuda sajrone karya? Yen ukuran meja "ngambang", sampeyan kudu terus-terusan ngawasi wektu iki lan mbenerake:
- pranala ing rumus laporan sing nuduhake tabel kita
- kisaran wiwitan tabel poros sing dibangun miturut tabel kita
- kisaran awal denah sing dibangun miturut tabel kita
- kisaran kanggo dropdown sing nggunakake tabel kita minangka sumber data
Kabeh iki total ora bakal bosen 😉
Bakal luwih trep lan bener kanggo nggawe kisaran "karet" dinamis, sing bakal nyetel ukuran kanthi otomatis menyang jumlah baris lan kolom data sing nyata. Kanggo ngleksanakake iki, ana sawetara cara.
Cara 1. Tabel pinter
Sorot sawetara sel lan pilih saka tab Ngarep - Format minangka Tabel (Ngarep - Format minangka Tabel):
Yen sampeyan ora mbutuhake desain belang sing ditambahake ing meja minangka efek samping, sampeyan bisa mateni ing tab sing katon. Konstruktor (Desain). Saben tabel sing digawe kanthi cara iki nampa jeneng sing bisa diganti karo sing luwih trep ing papan sing padha ing tab kasebut Konstruktor (Desain) ing lapangan Jeneng meja (Jeneng Tabel).
Saiki kita bisa nggunakake pranala dinamis menyang "tabel cerdas" kita:
- Tabel 1 – pranala menyang kabeh tabel kajaba baris header (A2:D5)
- Tabel 1[#Kabeh] - link menyang kabeh tabel (A1:D5)
- Tabel 1[Petrus] – referensi kanggo kolom-jarak tanpa header sel pisanan (C2:C5)
- Tabel 1[#Headers] - link menyang "header" kanthi jeneng kolom (A1:D1)
Referensi kasebut bisa digunakake kanthi apik ing rumus, contone:
= SUM (Tabel 1[Moscow]) - pitungan jumlah kanggo kolom "Moscow"
or
=VPR(F5;Tabel 1; 3; 0) - telusuri ing tabel kanggo sasi saka sel F5 lan ngetokake jumlah St. Petersburg (apa VLOOKUP?)
Tautan kasebut bisa digunakake kanthi sukses nalika nggawe tabel pangsi kanthi milih ing tab Sisipake - Tabel Pivot (Sisipake - Tabel Pivot) lan ketik jeneng tabel pinter minangka sumber data:
Yen sampeyan milih fragmen saka tabel kasebut (contone, rong kolom pisanan) lan nggawe diagram saka jinis apa wae, banjur nalika nambah garis anyar, dheweke bakal ditambahake kanthi otomatis menyang diagram.
Nalika nggawe dhaptar gulung mudhun, pranala langsung menyang unsur tabel pinter ora bisa digunakake, nanging sampeyan bisa kanthi gampang ngatasi watesan iki kanthi nggunakake taktik taktik - gunakake fungsi kasebut. LANGSUNG (Ora adil), sing ngowahi teks dadi link:
Sing. link menyang tabel pinter ing wangun senar teks (ing tandha petik!) dadi link lengkap, lan dhaftar gulung-mudhun biasane perceives iku.
Cara 2: Rentang jeneng dinamis
Yen ngowahi data dadi tabel pinter ora dikarepake amarga sawetara alasan, mula sampeyan bisa nggunakake cara sing rada rumit, nanging luwih subtle lan serbaguna - nggawe kisaran jeneng dinamis ing Excel sing nuduhake tabel kita. Banjur, kaya ing tabel cerdas, sampeyan bisa kanthi bebas nggunakake jeneng kisaran sing digawe ing rumus, laporan, grafik, lan liya-liyane. Ayo miwiti kanthi conto sing gampang:
Task: nggawe kisaran jeneng dinamis sing bakal nuduhake dhaptar kutha lan kanthi otomatis mbentang lan nyilikake ukuran nalika nambah kutha anyar utawa mbusak.
Kita butuh rong fungsi Excel sing kasedhiya ing versi apa wae − POICPOZ (PERTANDINGAN) kanggo nemtokake sel pungkasan saka sawetara, lan INDEKS (INDEX) kanggo nggawe link dinamis.
Nemokake sel pungkasan nggunakake MATCH
MATCH(lookup_value, range, match_type) – fungsi sing nggoleki nilai sing diwenehake ing sawetara (baris utawa kolom) lan ngasilake nomer ordinal sel sing ditemokake. Contone, rumus MATCH ("Maret"; A1: A5; 0) bakal ngasilake angka 4 minangka asil, amarga tembung "Maret" dumunung ing sel kaping papat ing kolom A1: A5. Argumen fungsi pungkasan Match_Type = 0 tegese kita nggoleki sing cocog. Yen argumen iki ora ditemtokake, fungsi kasebut bakal pindhah menyang mode telusuran kanggo nilai paling cilik sing paling cedhak - iki persis sing bisa digunakake kanggo nemokake sel sing dikuwasani pungkasan ing array kita.
Inti trik iku prasaja. MATCH nggoleki sel ing kisaran saka ndhuwur nganti ngisor lan, miturut teori, kudu mandheg nalika nemokake nilai sing paling cedhak karo sing diwenehake. Yen sampeyan nemtokake nilai sing temenan luwih saka sembarang kasedhiya ing tabel minangka nilai sing dikarepake, bakal MATCH tekan mburi meja, golek apa-apa lan menehi nomer urutan sel kapenuhan pungkasan. Lan kita kudu!
Yen mung ana nomer ing array, kita bisa nemtokake nomer minangka nilai sing dikarepake, sing luwih gedhe tinimbang sing ana ing tabel:
Kanggo njamin, sampeyan bisa nggunakake nomer 9E + 307 (9 kaping 10 kanggo daya 307, IE 9 karo 307 nul) - nomer maksimum sing Excel bisa karo ing asas.
Yen ana nilai teks ing kolom kita, banjur minangka setara karo nomer paling gedhe, sampeyan bisa nglebokake konstruksi REPEAT("i", 255) - string teks sing dumadi saka 255 huruf "i" - huruf pungkasan saka aksara. Amarga Excel bener mbandhingake kode karakter nalika nggoleki, teks apa wae ing tabel kita kanthi teknis bakal dadi "cilik" tinimbang baris "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
Nggawe link nggunakake INDEX
Saiki kita ngerti posisi unsur non-kosong pungkasan ing meja, iku tetep kanggo mbentuk link kanggo kabeh kita. Kanggo iki kita nggunakake fungsi:
INDEX(rentang; row_num; kolom_nume)
Iki menehi isi sel saka kisaran miturut nomer baris lan kolom, contone, fungsi = INDEX(A1:D5;3;4) ing tabel kita karo kutha lan sasi saka cara sadurunge bakal menehi 1240 - isi saka baris 3 lan kolom 4, yaiku sel D3. Yen mung ana siji kolom, nomer kasebut bisa diilangi, yaiku rumus INDEX(A2:A6;3) bakal menehi "Samara" ing gambar pungkasan.
Lan ana siji nuansa sing ora jelas: yen INDEX ora mung dilebokake ing sel sawise tandha =, kaya biasane, nanging digunakake minangka bagean pungkasan referensi kanggo kisaran sawise titik loro, mula ora ana maneh. isi sel, nanging alamate! Dadi, rumus kaya $A$2:INDEX($A$2:$A$100;3) bakal menehi referensi kanggo kisaran A2:A4 ing output.
Lan ing kene ana fungsi MATCH, sing dilebokake ing INDEX kanggo nemtokake pungkasan dhaptar kanthi dinamis:
=$A$2:INDEX($A$2:$A$100; MATCH(REP(“I”;255);A2:A100))
Nggawe kisaran jenenge
Iku tetep kanggo ngemas kabeh dadi siji. Bukak tab rumus (Formula) Lan klik ing Jeneng Manajer (Nama Manager). Ing jendhela sing mbukak, klik tombol kasebut nggawe (anyar), ketik jeneng kisaran lan rumus ing lapangan Range (Referensi):
Iku tetep kanggo klik ing OK lan sawetara siap bisa digunakake ing sembarang rumus, dhaptar gulung-mudhun utawa denah.
- Nggunakake fungsi VLOOKUP kanggo ngubungake tabel lan nilai goleki
- Cara nggawe dhaptar gulung mudhun kanthi otomatis
- Cara nggawe tabel poros kanggo nganalisa data sing akeh