Cara nggawe dhaptar gulung mudhun ing Excel

Dhaptar gulung mudhun minangka alat sing migunani banget sing bisa mbantu nggarap informasi luwih nyaman. Iku ndadekake iku bisa kanggo ngemot sawetara nilai ing sel bebarengan, karo sing bisa digunakake, kaya karo liyane. Kanggo milih sing sampeyan butuhake, mung klik ing lambang panah, banjur dhaptar nilai uXNUMXbuXNUMXbis ditampilake. Sawise milih sing spesifik, sel kasebut diisi kanthi otomatis, lan rumus diitung maneh adhedhasar.

Excel nyedhiyakake macem-macem cara kanggo nggawe menu gulung mudhun, lan uga ngidini sampeyan ngatur kanthi fleksibel. Ayo analisa metode kasebut kanthi luwih rinci.

Proses nggawe dhaptar

Kanggo nggawe menu pop-up, klik ing item menu ing sadawane path "Data" - "Validasi Data". Kothak dialog bakal mbukak ing ngendi sampeyan kudu nemokake tab "Parameter" lan klik yen durung dibukak sadurunge. Wis akeh setelan, nanging item "Jinis Data" penting kanggo kita. Saka kabeh makna, "Daftar" iku sing bener.

Jumlah cara kanggo ngetik informasi menyang dhaptar pop-up cukup akeh.

1. Indikasi bebas saka unsur dhaptar sing dipisahake karo titik koma ing kolom "Sumber" sing ana ing tab sing padha ing kothak dialog sing padha.
   

   

2. Indikasi awal saka nilai. Kolom Sumber ngemot kisaran ing ngendi informasi sing dibutuhake kasedhiya.
   

   

3. Nemtokake sawetara sing dijenengi. Cara sing mbaleni sing sadurunge, nanging mung preliminarily jeneng sawetara.
   

   

Sembarang cara kasebut bakal ngasilake asil sing dikarepake. Ayo goleki cara kanggo nggawe dhaptar gulung mudhun ing kahanan nyata.

Adhedhasar data saka dhaptar

Ayo kita duwe tabel sing nggambarake macem-macem jinis woh-wohan.

Kanggo nggawe dhaptar ing menu gulung mudhun adhedhasar set informasi iki, sampeyan kudu nindakake ing ngisor iki:

1. Pilih sel sing dilindhungi undhang-undhang kanggo dhaptar mangsa ngarep.
2. Temokake tab Data ing pita. Ing kana kita klik "Verifikasi data".
   

   

3. Temokake item "Jinis Data" lan ganti nilai kasebut menyang "Dhaptar".
   

   

4. Ing lapangan sing nuduhake pilihan "Sumber", ketik sawetara sing dikarepake. Wigati dimangerteni manawa referensi mutlak kudu ditemtokake supaya nalika nyalin dhaptar, informasi kasebut ora owah.
   

Kajaba iku, ana fungsi kanggo ngasilake dhaptar bebarengan ing luwih saka siji sel. Kanggo entuk iki, sampeyan kudu milih kabeh, lan nindakake langkah sing padha kaya sing wis diterangake sadurunge. Maneh, sampeyan kudu nggawe manawa referensi mutlak ditulis. Yen alamat ora duwe tandha dolar ing jejere jeneng kolom lan baris, sampeyan kudu nambahake kanthi mencet tombol F4 nganti tandha $ ana ing jejere jeneng kolom lan baris.

Kanthi ngrekam data manual

Ing kahanan ing ndhuwur, dhaptar kasebut ditulis kanthi nyorot kisaran sing dibutuhake. Iki minangka cara sing trep, nanging kadhangkala perlu kanggo ngrekam data kanthi manual. Iki bakal ngidini supaya ora duplikasi informasi ing buku kerja.

Upaminipun kita ngadhepi tugas nggawe dhaptar sing ngemot rong pilihan: ya lan ora. Kanggo ngrampungake tugas, perlu:

1. Klik ing sel kanggo dhaptar.
2. Bukak "Data" lan ing kana temokake bagean "Priksa Data" sing kita kenal.
   

   

3. Maneh, pilih jinis "Dhaptar".
   

   

4. Ing kene sampeyan kudu ngetik "Ya; Ora" minangka sumber. Kita weruh manawa informasi dilebokake kanthi manual nggunakake titik koma kanggo enumerasi.

Sawise ngeklik OK, kita duwe asil ing ngisor iki.

Sabanjure, program bakal kanthi otomatis nggawe menu gulung mudhun ing sel sing cocog. Kabeh informasi sing wis ditemtokake pangguna minangka item ing dhaptar pop-up. Aturan kanggo nggawe dhaptar ing sawetara sel padha karo sing sadurunge, kajaba mung sampeyan kudu nemtokake informasi kanthi manual nggunakake titik koma.

Nggawe dhaptar gulung mudhun nggunakake fungsi OFFSET

Saliyane cara klasik, bisa uga nggunakake fungsi kasebut Pembuangankanggo ngasilake menu gulung mudhun.

Ayo mbukak lembaran.

Kanggo nggunakake fungsi kanggo dhaptar gulung mudhun, sampeyan kudu nindakake ing ngisor iki:

1. Pilih sel kapentingan ing ngendi sampeyan pengin nyelehake dhaptar mangsa ngarep.
2. Bukak tab "Data" lan jendhela "Validasi Data" kanthi urutan.
   

   

3. Setel "Dhaptar". Iki ditindakake kanthi cara sing padha karo conto sadurunge. Pungkasan, rumus ing ngisor iki digunakake: =OFFSET(A$2$;0;0;5). Kita ngetik ing ngendi sel sing bakal digunakake minangka argumen ditemtokake.

Banjur program bakal nggawe menu kanthi dhaptar woh-wohan.

Sintaks kanggo iki yaiku:

=OFFSET(referensi,line_offset,column_offset,[dhuwur],[lebar])

Kita weruh yen fungsi iki duwe 5 argumen. Kaping pisanan, alamat sel pisanan sing bakal diimbangi diwenehi. Rong argumen sabanjure nemtokake jumlah baris lan kolom sing kudu diimbangi. Ngomong babagan kita, argumen Dhuwur yaiku 5 amarga nggambarake dhuwure dhaptar. 

Dhaptar gulung mudhun ing Excel kanthi substitusi data (+ nggunakake fungsi OFFSET)

Ing kasus tartamtu Pembuangan diijini nggawe menu pop-up dumunung ing sawetara tetep. Kerugian metode iki yaiku sawise nambahake item kasebut, sampeyan kudu ngowahi rumus dhewe.

Kanggo nggawe dhaptar dinamis kanthi dhukungan kanggo ngetik informasi anyar, sampeyan kudu:

1. Pilih sel sing dikarepake.
2. Bukak tab "Data" lan klik "Validasi Data".
3. Ing jendhela sing mbukak, pilih item "Dhaptar" maneh lan nemtokake rumus ing ngisor iki minangka sumber data: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Klik OK.

Iki ngandhut fungsi COUNTIF, kanggo langsung nemtokake jumlah sel sing diisi (sanajan jumlah panggunaan sing luwih akeh, kita mung nulis ing kene kanggo tujuan tartamtu).

Supaya rumus bisa mlaku kanthi normal, perlu dilacak manawa ana sel kosong ing jalur rumus kasebut. Mesthine ora.

Dhaptar gulung mudhun karo data saka sheet utawa file Excel liyane

Cara klasik ora bisa digunakake yen sampeyan butuh informasi saka dokumen liyane utawa malah lembar sing ana ing file sing padha. Kanggo iki, fungsi digunakake LANGSUNG, sing ngidini sampeyan ngetik link menyang sel kanthi format sing bener ing sheet liyane utawa ing umum - file. Sampeyan kudu nindakake ing ngisor iki:

1. Aktifake sel ing ngendi kita nyelehake dhaptar kasebut.
2. We mbukak jendhela kita wis ngerti. Ing panggonan sing padha sadurunge kita nuduhake sumber kanggo kisaran liyane, rumus dituduhake ing format =INDIRECT(“[List1.xlsx]Sheet1!$A$1:$A$9”). Mesthi wae, tinimbang List1 lan Sheet1, sampeyan bisa nglebokake jeneng buku lan lembar. 

manungsa waé! Jeneng berkas diwenehi ing kurung kothak. Ing kasus iki, Excel ora bakal bisa nggunakake file sing lagi ditutup minangka sumber informasi.

Sampeyan uga kudu dicathet yen jeneng file dhewe mung bisa dimangerteni yen dokumen sing dibutuhake dumunung ing folder sing padha karo sing bakal dilebokake dhaptar. Yen ora, sampeyan kudu nemtokake alamat dokumen iki kanthi lengkap.

Nggawe Dropdown gumantung

Dhaptar sing gumantung yaiku dhaptar sing isine dipengaruhi dening pilihan pangguna ing dhaptar liyane. Upaminipun kita duwe meja mbukak ing ngarep kita sing ngemot telung kisaran, saben kang wis diwenehi jeneng.

Sampeyan kudu ngetutake langkah-langkah iki kanggo ngasilake dhaptar sing asile kena pengaruh pilihan sing dipilih ing dhaptar liyane.

1. Nggawe dhaptar 1st karo jeneng sawetara.
   

   

2. Ing titik entri sumber, indikator sing dibutuhake disorot siji-siji.
   

   

3. Gawe dhaptar nomer 2 gumantung saka jinis tanduran sing dipilih wong. Utawa, yen sampeyan nemtokake wit ing dhaptar pisanan, informasi ing dhaptar kapindho bakal dadi "oak, hornbeam, chestnut" lan liya-liyane. Perlu ditulis rumus ing panggonan input sumber data = LANGSUNG(E3). E3 – sel sing ngemot jeneng kisaran 1. = INDIRECT(E3). E3 - sel kanthi jeneng dhaptar 1.

Saiki kabeh wis siyap.

Kepiye cara milih sawetara nilai saka dhaptar gulung mudhun?

Kadhangkala ora bisa menehi pilihan mung siji nilai, mula luwih saka siji kudu dipilih. Banjur sampeyan kudu nambah makro menyang kode kaca. Nggunakake kombinasi tombol Alt + F11 mbukak Editor Visual Basic. Lan kode dilebokake ing kono.

Private Sub Worksheet_Change (ByVal Target As Range)

    On kesalahan Resume Sabanjure

    Yen Ora Intersect(Target, Range(«Е2:Е9»)) Ora Ana Lan Target.Cells.Count = 1 Banjur

        Application.EnableEvents = Palsu

        Yen Len (Target.Offset (0, 1)) = 0 Banjur

            Target.Offset (0, 1) = Target

        liya

            Target.End (xlToRight) .Offset (0, 1) = Target

        mungkasi Yen

        Target.ClearContents

        Application.EnableEvents = Bener

    mungkasi Yen

Akhir Sub 

Supaya isi sel bisa ditampilake ing ngisor iki, kita masang kode ing ngisor iki menyang editor.

Private Sub Worksheet_Change (ByVal Target As Range)

    On kesalahan Resume Sabanjure

    Yen Ora Intersect(Target, Range(«Н2:К2»)) Ora Ana Lan Target.Cells.Count = 1 Banjur

        Application.EnableEvents = Palsu

        Yen Len (Target.Offset (1, 0)) = 0 Banjur

            Target.Offset (1, 0) = Target

        liya

            Target.End (xlDown) .Offset (1, 0) = Target

        mungkasi Yen

        Target.ClearContents

        Application.EnableEvents = Bener

    mungkasi Yen

Akhir Sub

Lan pungkasane, kode iki digunakake kanggo nulis ing siji sel.

Private Sub Worksheet_Change (ByVal Target As Range)

    On kesalahan Resume Sabanjure

    Yen Ora Intersect(Target, Range(«C2:C5»)) Ora Ana Lan Target.Cells.Count = 1 Banjur

        Application.EnableEvents = Palsu

        newVal = Target

        Aplikasi. Undo

        oldval = Target

        Yen Len (oldval) <> 0 Lan oldval <> newVal Banjur

            Target = Target & «,» & newVal

        liya

            Target = newVal

        mungkasi Yen

        Yen Len (newVal) = 0 Banjur Target.ClearContents

        Application.EnableEvents = Bener

    mungkasi Yen

Akhir Sub

Range bisa diowahi.

Kepiye carane nggawe dhaptar gulung mudhun kanthi telusuran?

Ing kasus iki, sampeyan kudu nggunakake jinis dhaptar sing beda. Tab "Developer" mbukak, sawise sampeyan kudu ngeklik utawa nutul (yen layar didemek) ing unsur "Insert" - "ActiveX". Wis kothak combo. Sampeyan bakal dijaluk nggambar dhaptar iki, lan banjur ditambahake menyang dokumen kasebut.

Luwih, dikonfigurasi liwat properti, ing ngendi sawetara ditemtokake ing pilihan ListFillRange. Sel ing ngendi nilai sing ditemtokake pangguna ditampilake dikonfigurasi nggunakake opsi LinkedCell. Sabanjure, sampeyan mung kudu nulis karakter pisanan, amarga program bakal menehi saran kanthi otomatis nilai sing bisa ditindakake.

Dhaptar gulung mudhun kanthi substitusi data otomatis

Ana uga fungsi sing data diganti kanthi otomatis sawise ditambahake menyang sawetara. Iku gampang kanggo nindakake iki:

1. Nggawe sakumpulan sel kanggo dhaptar mangsa ngarep. Ing kasus kita, iki minangka set warna. Kita milih.
   

   

2. Sabanjure, kudu diformat minangka tabel. Sampeyan kudu ngeklik tombol kanthi jeneng sing padha lan pilih gaya tabel.
   

   

   

Sabanjure, sampeyan kudu konfirmasi kisaran iki kanthi mencet tombol "OK".

Kita milih tabel asil lan menehi jeneng liwat kolom input sing ana ing ndhuwur kolom A.

Mekaten, ana tabel, lan bisa digunakake minangka basis kanggo dhaptar gulung mudhun, sing sampeyan butuhake:

1. Pilih sel ngendi dhaftar dumunung.
2. Bukak dialog Validasi Data.
   

   

3. Kita nyetel jinis data menyang "Dhaptar", lan minangka nilai kita menehi jeneng tabel liwat tandha =.
   

   

   

Kabeh, sel wis siyap, lan jeneng warna ditampilake ing kono, kaya sing dibutuhake. Saiki sampeyan bisa nambah posisi anyar mung kanthi nulis ing sel sing dumunung rada ngisor sanalika sawise sing pungkasan.

Iki kauntungan saka Tabel, sing sawetara otomatis mundhak nalika data anyar ditambahake. Dadi, iki minangka cara sing paling trep kanggo nambah dhaptar.

Kepiye carane nyalin dhaptar gulung mudhun?

Kanggo nyalin, cukup nggunakake kombinasi tombol Ctrl + C lan Ctrl + V. Dadi dhaptar gulung mudhun bakal disalin bebarengan karo format. Kanggo mbusak format, sampeyan kudu nggunakake tempel khusus (ing menu konteks, pilihan iki katon sawise nyalin dhaptar), ing ngendi pilihan "kahanan ing nilai" disetel.

Pilih kabeh sel sing ngemot dhaptar gulung mudhun

Kanggo ngrampungake tugas iki, sampeyan kudu nggunakake fungsi "Pilih klompok sel" ing grup "Golek lan Pilih".

Sawise iku, kothak dialog bakal mbukak, ing ngendi sampeyan kudu milih item "Kabeh" lan "Iki padha" ing menu "Validasi Data". Item pisanan milih kabeh dhaptar, lan sing nomer loro mung milih sing padha karo dhaptar tartamtu.