Daptar eusi
Dupi anjeun gaduh tabel kalawan data dina Excel nu bisa dirobah ukuranana, nyaéta jumlah baris (kolom) bisa nambahan atawa ngurangan dina kursus gawé? Upami ukuran méja "ngambang", maka anjeun kedah terus-terusan ngawas waktos ieu sareng ngabenerkeunana:
- Tumbu dina rumus laporan anu ngarujuk kana tabel kami
- rentang awal tabel pangsi anu diwangun nurutkeun tabel kami
- rentang awal grafik diwangun nurutkeun tabel kami
- rentang pikeun dropdowns anu ngagunakeun tabel kami salaku sumber data
Sadaya ieu sacara total moal ngantep anjeun bosen 😉
Éta bakal langkung saé sareng leres pikeun nyiptakeun rentang "karét" dinamis, anu sacara otomatis bakal nyaluyukeun ukuran kana jumlah baris sareng kolom data anu saleresna. Pikeun ngalaksanakeun ieu, aya sababaraha cara.
Métode 1. méja pinter
Nyorot rentang sél Anjeun tur pilih tina tab Imah - Format salaku Méja (Imah - Format salaku Méja):
Upami anjeun henteu peryogi desain belang anu ditambah kana méja salaku éfék samping, anjeun tiasa mareuman éta dina tab anu muncul. Konstruktor (Desain). Unggal tabel dijieun ku cara kieu narima ngaran nu bisa diganti ku hiji leuwih merenah dina tempat anu sarua dina tab Konstruktor (Desain) di sawah Ngaran méja (Ngaran Méja).
Ayeuna urang tiasa nganggo tautan dinamis kana "méja pinter" kami:
- table 1 – numbu ka sakabéh méja iwal ti baris lulugu (A2:D5)
- Tabél1[#Sadaya] – link ka sakabéh tabel (A1:D5)
- Tabél 1[Peter] - rujukan ka kolom rentang tanpa header sél munggaran (C2:C5)
- Tabél1[#Headers] - numbu ka "header" kalayan ngaran kolom (A1:D1)
Rujukan sapertos dianggo saé dina rumus, contona:
= Jumlah (Tabél 1[Moscow]) - itungan jumlah pikeun kolom "Moscow"
or
=VPR(F5;table 1; 3; 0) - milarian dina tabel pikeun bulan tina sél F5 sareng ngaluarkeun jumlah St. Petersburg pikeun éta (naon VLOOKUP?)
Tumbu misalna bisa hasil dipaké nalika nyieun tabel pangsi ku milih dina tab Selapkeun – Pivot Table (Selapkeun – Pivot Table) sareng asupkeun nami tabel pinter salaku sumber data:
Upami anjeun milih sempalan tabel sapertos kitu (contona, dua kolom kahiji) sareng ngadamel diagram tina jinis naon waé, teras nalika nambihan garis énggal, aranjeunna bakal otomatis ditambah kana diagram.
Nalika nyieun daptar turun-handap, Tumbu langsung ka elemen tabel pinter teu bisa dipaké, tapi anjeun bisa kalayan gampang meunang sabudeureun watesan ieu ngagunakeun trik taktis - ngagunakeun fungsi. TEU LANGSUNG (Henteu adil), nu ngarobah téks kana tumbu:
Jelema. Tumbu ka tabel pinter dina wangun string téks (dina tanda petik!) Ngarobah kana link full-fledged, sarta daptar turun-handap ilaharna perceives eta.
Métode 2: rentang ngaranna dinamis
Upami ngarobah data anjeun kana méja pinter henteu pikaresepeun pikeun sababaraha alesan, maka anjeun tiasa nganggo metode anu rada pajeulit, tapi langkung halus sareng serbaguna - jieun rentang anu namina dinamis dina Excel anu ngarujuk kana méja kami. Teras, sapertos dina kasus tabel pinter, anjeun tiasa nganggo nami rentang anu diciptakeun dina sagala rumus, laporan, grafik, jsb. Hayu urang mimitian ku conto saderhana:
Tugas: nyieun rentang ngaranna dinamis nu bakal nujul kana daptar kota tur otomatis manteng tur ngaleutikan ukuranana lamun nambahkeun kota anyar atawa mupus aranjeunna.
Urang peryogi dua fungsi Excel anu aya dina versi naon waé − POICPOZ (PERTANDINGAN) pikeun nangtukeun sél panungtungan rentang, jeung Indéks (INDEKS) pikeun nyieun tumbu dinamis.
Milarian sél terakhir nganggo MATCH
MATCH(lookup_value, range, match_type) - fungsi anu milarian nilai anu dipasihkeun dina rentang (baris atanapi kolom) sareng mulangkeun nomer ordinal sél dimana éta kapanggih. Salaku conto, rumus MATCH ("Maret"; A1: A5; 0) bakal mulangkeun angka 4 salaku hasilna, sabab kecap "Maret" aya dina sél kaopat dina kolom A1: A5. Argumen fungsi panungtungan Match_Type = 0 hartina urang keur néangan hiji patandingan pasti. Lamun argumen ieu teu dieusian, fungsi bakal pindah ka modeu pilarian pikeun nilai pangleutikna pangcaketna - ieu téh kahayang bisa hasil dipaké pikeun manggihan sél panungtungan nempatan dina Asép Sunandar Sunarya kami.
Intina trik basajan. MATCH maluruh sél dina rentang ti luhur ka handap sarta, dina tiori, kudu eureun lamun manggihan nilai pangleutikna pangcaketna ka nu dibikeun. Lamun nangtukeun hiji nilai anu écés leuwih gede ti sagala sadia dina tabél salaku nilai nu dipikahoyong, lajeng cocog bakal ngahontal pisan tungtung tabél, manggihan nanaon sarta masihan nomer runtuyan sél dieusian panungtungan. Sareng urang peryogina!
Lamun ngan aya angka dina Asép Sunandar Sunarya urang, mangka urang bisa nangtukeun hiji angka salaku nilai nu dipikahoyong, nu écés leuwih badag batan salah sahiji nu leuwih dina tabél:
Pikeun garansi, anjeun tiasa nganggo nomer 9E + 307 (9 kali 10 kana kakuatan 307, nyaéta 9 sareng 307 nol) - jumlah maksimal anu tiasa dianggo ku Excel sacara prinsip.
Upami aya nilai téks dina kolom kami, maka salaku sarimbag tina jumlah panglegana, anjeun tiasa ngalebetkeun konstruksi REPEAT ("i", 255) - string téks anu diwangun ku 255 hurup "i" - hurup terakhir tina alfabét. Kusabab Excel saleresna ngabandingkeun kodeu karakter nalika milarian, téks naon waé dina méja kami sacara teknis bakal "langkung leutik" tibatan garis "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy":
Ngahasilkeun tautan nganggo INDEX
Ayeuna urang terang posisi unsur non-kosong panungtungan dina tabél, éta tetep ngabentuk tumbu ka sakabéh rentang urang. Pikeun ieu kami nganggo fungsi:
INDEX(rentang; row_num; column_num)
Éta masihan eusi sél tina rentang ku nomer baris sareng kolom, contona, fungsi = INDEKS (A1: D5; 3; 4) dina tabel kami sareng kota sareng bulan ti metodeu sateuacana bakal masihan 1240 - eusi. ti baris ka-3 jeung kolom ka-4, nyaéta sél D3. Upami ngan aya hiji kolom, jumlahna tiasa dileungitkeun, nyaéta rumus INDEX(A2:A6;3) bakal masihan "Samara" dina layar anu terakhir.
Tur aya hiji nuansa teu sagemblengna atra: lamun INDEX teu ngan diasupkeun kana sél sanggeus tanda =, sakumaha biasa, tapi dipaké salaku bagian ahir rujukan pikeun rentang sanggeus titik titik, mangka euweuh masihan kaluar. eusi sél, tapi alamatna! Ku kituna, rumus kawas $A$2:INDEX ($A$2:$A$100;3) bakal mere rujukan kana rentang A2:A4 dina kaluaran.
Sareng ieu dimana fungsi MATCH asup, anu urang selapkeun di jero INDEX pikeun sacara dinamis nangtukeun tungtung daptar:
=$A$2:INDEX($A$2:$A$100; MATCH(REP("Kuring";255);A2:A100))
Jieun rentang ngaranna
Eta tetep pak eta sadayana kana hiji sakabéh. Buka tab rumus (Rumus) Tur klik Manajer Ngaran (Ngaran Manajer). Dina jandela nu muka, klik tombol nyiptakeun (anyar), asupkeun ngaran rentang urang jeung rumus dina widang jajaran (Rujukan):
Tetep klik dina OK jeung rentang siap bisa dipaké dina sagala rumus, daptar turun-handap atawa grafik.
- Ngagunakeun fungsi VLOOKUP pikeun ngaitkeun tabel sarta nilai lookup
- Kumaha cara nyieun daptar turun-turun anu ngeusian otomatis
- Kumaha cara nyieun tabel pangsi pikeun nganalisis jumlah data anu ageung