Kumaha cara ngadamel daptar turun di Excel

Daptar turun-handap mangrupikeun alat anu luar biasa anu tiasa ngabantosan damel sareng inpormasi langkung nyaman. Éta ngamungkinkeun pikeun ngandung sababaraha nilai dina sél sakaligus, anu anjeun tiasa damel, sapertos anu sanés. Pikeun milih anu anjeun peryogikeun, klik dina ikon panah, saatos éta daptar nilai uXNUMXbuXNUMXbis ditampilkeun. Saatos milih anu khusus, sél sacara otomatis ngeusi éta, sareng rumusna diitung deui dumasar kana éta.

Excel nyayogikeun seueur metode anu béda pikeun ngahasilkeun ménu turun-handap, sareng salian ti éta, éta ngamungkinkeun anjeun sacara fleksibel ngaluyukeunana. Hayu urang nganalisis métode ieu dina leuwih jéntré.

Prosés nyieun daptar

Pikeun ngahasilkeun menu pop-up, klik dina item menu sapanjang jalur "Data" - "Validasi Data". Kotak dialog bakal dibuka dimana anjeun kedah milarian tab "Parameter" teras klik upami éta henteu acan dibuka sateuacanna. Éta ngagaduhan seueur setélan, tapi item "Tipe Data" penting pikeun urang. Tina sagala hartos, "Daptar" mangrupikeun anu leres.

Jumlah metode dimana inpormasi diasupkeun kana daptar pop-up cukup ageung.

1. Indikasi bebas tina daptar elemen dipisahkeun ku titik koma dina widang "Sumber" ayana dina tab sarua kotak dialog sarua.
   

   

2. Indikasi awal tina nilai. Widang Sumber ngandung rentang dimana inpormasi anu diperyogikeun sayogi.
   

   

3. Nangtukeun rentang ngaranna. Metoda nu repeats saméméhna, tapi ngan perlu preliminarily ngaran rentang.
   

   

Salah sahiji metodeu ieu bakal ngahasilkeun hasil anu dipikahoyong. Hayu urang tingali metodeu pikeun ngahasilkeun daptar turun-handap dina kaayaan kahirupan nyata.

Dumasar data tina daptar

Anggap urang gaduh tabel anu ngajelaskeun jinis buah anu béda.

Pikeun nyieun daptar dina menu turun-handap dumasar kana set inpormasi ieu, anjeun kedah ngalakukeun ieu:

1. Pilih sél nu ditangtayungan pikeun daptar nu bakal datang.
2. Manggihan tab Data dina pita. Di dinya urang klik "Verifikasi data".
   

   

3. Manggihan item "Tipe Data" jeung pindah nilai ka "Daptar".
   

   

4. Dina widang anu nunjukkeun pilihan "Sumber", lebetkeun rentang anu dipikahoyong. Punten dicatet yén rujukan mutlak kedah disebatkeun supados nalika nyalin daptar, inpormasina henteu bergeser.
   

Salaku tambahan, aya fungsi pikeun ngahasilkeun daptar sakaligus dina langkung ti hiji sél. Pikeun ngahontal ieu, anjeun kedah milih sadayana, sareng ngalaksanakeun léngkah anu sami sapertos anu dijelaskeun sateuacana. Sakali deui, anjeun kedah mastikeun yén rujukan mutlak ditulis. Upami alamatna henteu gaduh tanda dolar di gigireun kolom sareng nami baris, anjeun kedah nambihanana ku mencét tombol F4 dugi ka tanda $ aya di gigireun nami kolom sareng baris.

Kalayan rékaman data manual

Dina kaayaan di luhur, daptar ieu ditulis ku panyorot rentang diperlukeun. Ieu métode merenah, tapi kadang perlu pikeun ngarekam data sacara manual. Ieu bakal ngamungkinkeun pikeun nyegah duplikasi inpormasi dina buku kerja.

Anggap urang nyanghareupan tugas nyieun daptar ngandung dua pilihan mungkin: enya jeung henteu. Pikeun ngalengkepan tugas, perlu:

1. Pencét sél pikeun daptar.
2. Buka "Data" teras milarian bagian "Data Cék" anu wawuh ka urang.
   

   

3. Sakali deui, pilih jinis "Daptar".
   

   

4. Di dieu anjeun kudu ngasupkeun "Leres; Henteu" salaku sumberna. Kami ningali yén inpormasi diasupkeun sacara manual nganggo tanda titik koma pikeun enumerasi.

Saatos ngaklik OK, urang gaduh hasil di handap ieu.

Salajengna, program bakal otomatis nyieun menu turun-handap dina sél luyu. Sadaya inpormasi anu parantos ditangtukeun ku pangguna salaku item dina daptar pop-up. Aturan pikeun nyieun daptar dina sababaraha sél sami sareng anu sateuacana, ngan ukur anjeun kedah netepkeun inpormasi sacara manual nganggo titik koma.

Nyieun daptar turun-handap ngagunakeun pungsi OFFSET

Salian métode klasik, kasebut nyaéta dimungkinkeun pikeun ngagunakeun fungsi Buangpikeun ngahasilkeun menu dropdown.

Hayu urang buka lambaranana.

Pikeun nganggo fungsi pikeun daptar turun-handap, anjeun kedah ngalakukeun ieu:

1. Pilih sél anu dipikaresep dimana anjeun badé nempatkeun daptar hareup.
2. Buka tab "Data" sareng jandela "Validasi Data" dina urutan.
   

   

3. Setel "Daptar". Hal ieu dilakukeun dina cara anu sami sareng conto sateuacana. Tungtungna, rumus ieu dianggo: =OFFSET(A$2$;0;0;5). Urang lebetkeun dimana sél anu bakal dianggo salaku argumen anu dieusian.

Teras program bakal nyiptakeun ménu kalayan daptar buah.

Sintaksis pikeun ieu nyaéta:

=OFFSET(rujukan,line_offset,column_offset, [jangkungna], [lebar])

Kami ningali yén fungsi ieu ngagaduhan 5 argumen. Kahiji, alamat sél munggaran pikeun offset dibikeun. Dua argumen salajengna nangtukeun sabaraha baris jeung kolom pikeun offset. Diomongkeun ku urang, argumen Jangkungna nyaéta 5 sabab ngagambarkeun jangkungna daptar. 

Daptar dropdown di Excel sareng substitusi data (+ nganggo fungsi OFFSET)

Dina kasus dibikeun Buang diwenangkeun nyieun menu pop-up lokasina dina rentang tetep. Karugian tina metode ieu nyaéta saatos nambihan barang, anjeun kedah ngédit rumusna nyalira.

Pikeun nyieun daptar dinamis kalayan dukungan pikeun ngasupkeun inpormasi anyar, anjeun kedah:

1. Pilih sél anu dipikaresep.
2. Dilegakeun tab "Data" teras klik dina "Validasi Data".
3. Dina jandéla anu muka, pilih deui item "Daptar" sareng sebutkeun rumus ieu salaku sumber data: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Klik OK.

Ieu ngandung hiji fungsi COUNTIF, pikeun langsung nangtukeun sabaraha sél nu dieusian (sanajan mibanda jumlah loba nu leuwih gede pamakéan, urang ngan nulis dieu pikeun tujuan husus).

Supados rumus tiasa dianggo sacara normal, anjeun kedah ngalacak naha aya sél kosong dina jalur rumus. Éta henteu kedah.

Daptar turun sareng data tina lambaran atanapi file Excel anu sanés

Metodeu klasik henteu tiasa dianggo upami anjeun peryogi inpormasi tina dokumen anu sanés atanapi malah lambaran anu aya dina file anu sami. Pikeun ieu, fungsina dianggo TEU LANGSUNG, nu ngidinan Anjeun pikeun nuliskeun dina format bener link ka sél lokasina di lambar sejen atawa sacara umum - file. Anjeun kedah ngalakukeun ieu:

1. Aktipkeun sél dimana urang nempatkeun daptar.
2. Kami muka jandela anu kami parantos terang. Di tempat anu sami dimana urang saacanna nunjukkeun sumber pikeun rentang anu sanés, rumus dituduhkeun dina formatna =TIDAK LANGSUNG(“[List1.xlsx]Lambar1!$A$1:$A$9”). Alami, tinimbang List1 jeung Sheet1, anjeun tiasa nyelapkeun nami buku sareng lambar anjeun masing-masing. 

Perhatosan! Ngaran koropak dirumuskeun dina kurung kuadrat. Dina hal ieu, Excel moal tiasa nganggo file anu ayeuna ditutup salaku sumber inpormasi.

Ogé kudu dicatet yén ngaran file sorangan asup akal ngan lamun dokumén diperlukeun ayana dina polder sarua salaku hiji tempat daptar bakal diselapkeun. Upami henteu, maka anjeun kedah netepkeun alamat dokumen ieu sacara lengkep.

Nyieun Dependant Dropdowns

Daptar gumantung nyaéta daptar anu eusina kapangaruhan ku pilihan pangguna dina daptar anu sanés. Anggap urang boga méja muka di hareup urang nu ngandung tilu rentang, nu masing-masing geus dibéré ngaran.

Anjeun kedah nuturkeun léngkah-léngkah ieu pikeun ngahasilkeun daptar anu hasilna kapangaruhan ku pilihan anu dipilih dina daptar anu sanés.

1. Jieun daptar 1st kalayan ngaran rentang.
   

   

2. Dina titik éntri sumber, indikator anu diperyogikeun disorot hiji-hiji.
   

   

3. Jieun daptar ka-2 gumantung kana jinis pepelakan anu dipilih ku jalma. Alternatipna, upami anjeun netepkeun tangkal dina daptar kahiji, maka inpormasi dina daptar kadua bakal "ek, hornbeam, chestnut" sareng salajengna. Perlu dituliskeun rumus dina tempat asupan sumber data = TEU LANGSUNG(E3). E3 – sél nu ngandung ngaran rentang 1. = TEU LANGSUNG(E3). E3 - sél kalayan nami daptar 1.

Ayeuna sagalana geus siap.

Kumaha carana milih sababaraha nilai tina daptar turun?

Kadang-kadang teu mungkin mun masihan leuwih sering dipake tinimbang ngan hiji nilai, jadi leuwih ti hiji kudu dipilih. Teras anjeun kedah nambihan makro kana kode halaman. Ngagunakeun kombinasi konci Alt + F11 muka Editor Visual Basic. Sareng kodeu diselapkeun di dinya.

Swasta Sub Worksheet_Change (ByVal Target Salaku Rentang)

    Dina Kasalahan ngahanca Salajengna

    Lamun Teu Intersect(Target, Rentang(«Е2:Е9»)) Euweuh Jeung Target.Cells.Count = 1 Lajeng

        Application.EnableEvents = Palsu

        Lamun Len (Target.Offset (0, 1)) = 0 Lajeng

            Target.Offset (0, 1) = Target

        lamun henteu

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

        mungkas Mun

        Target.ClearContents

        Application.EnableEvents = Leres

    mungkas Mun

tungtung Sub 

Supados eusi sél bisa dipidangkeun di handap, urang selapkeun kodeu handap kana redaktur.

Swasta Sub Worksheet_Change (ByVal Target Salaku Rentang)

    Dina Kasalahan ngahanca Salajengna

    Lamun Teu Intersect(Target, Rentang(«Н2:К2»)) Euweuh Jeung Target.Cells.Count = 1 Lajeng

        Application.EnableEvents = Palsu

        Lamun Len (Target.Offset (1, 0)) = 0 Lajeng

            Target.Offset (1, 0) = Target

        lamun henteu

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

        mungkas Mun

        Target.ClearContents

        Application.EnableEvents = Leres

    mungkas Mun

tungtung Sub

Sarta pamustunganana, kode ieu dipaké pikeun nulis dina hiji sél.

Swasta Sub Worksheet_Change (ByVal Target Salaku Rentang)

    Dina Kasalahan ngahanca Salajengna

    Lamun Teu Intersect(Target, Rentang(«C2:C5»)) Nyaeta Euweuh Jeung Target.Cells.Count = 1 Saterusna

        Application.EnableEvents = Palsu

        newVal = Target

        Aplikasi.Batalkeun

        oldval = Target

        Lamun Len (oldval) <> 0 Jeung oldval <> newVal Lajeng

            Target = Target & «,» & newVal

        lamun henteu

            Sasaran = newVal

        mungkas Mun

        Lamun Len (newVal) = 0 Lajeng Target.ClearContents

        Application.EnableEvents = Leres

    mungkas Mun

tungtung Sub

Rentang tiasa diédit.

Kumaha cara ngadamel daptar turun kalayan milarian?

Dina hal ieu, mimitina anjeun kedah nganggo jinis daptar anu béda. Tab "Pamekar" dibuka, saatos éta anjeun kedah klik atanapi ketok (upami layar toél) dina unsur "Selapkeun" - "ActiveX". Éta ngagaduhan kotak kombo. Anjeun bakal dipenta pikeun ngagambar daptar ieu, saatos éta bakal ditambah kana dokumén.

Salajengna, eta geus ngonpigurasi ngaliwatan sipat, dimana rentang dieusian dina pilihan ListFillRange. Sél dimana nilai anu ditetepkeun ku pangguna dipintonkeun dikonpigurasi nganggo pilihan LinkedCell. Salajengna, Anjeun ngan perlu nulis handap karakter munggaran, sabab program bakal otomatis nyarankeun nilai mungkin.

Daptar dropdown sareng substitusi data otomatis

Aya ogé fungsi nu data diganti otomatis sanggeus aranjeunna ditambahkeun kana rentang. Ieu gampang pikeun ngalakukeun ieu:

1. Jieun sakumpulan sél pikeun daptar hareup. Dina kasus urang, ieu mangrupikeun sakumpulan warna. Urang pilih eta.
   

   

2. Salajengna, éta kedah diformat janten méja. Anjeun kedah klik tombol nami anu sami sareng pilih gaya méja.
   

   

   

Salajengna, anjeun kedah mastikeun rentang ieu ku mencét tombol "OK".

Kami milih tabel anu hasilna sareng masihan nami ngalangkungan kolom input anu aya di luhur kolom A.

Éta waé, aya méja, sareng éta tiasa dianggo salaku dasar daptar turun-handap, anu anjeun peryogikeun:

1. Pilih sél dimana daptar ayana.
2. Buka dialog Validasi Data.
   

   

3. Kami nyetél jinis data kana "Daptar", sareng salaku nilai kami masihan nami tabel ngalangkungan tanda =.
   

   

   

Sagalana, sél geus siap, sarta ngaran kelir ditémbongkeun di jerona, sakumaha urang mimitina diperlukeun. Ayeuna anjeun tiasa nambihan posisi énggal ku cara nyeratna dina sél anu lokasina rada handap langsung saatos anu terakhir.

Ieu kaunggulan tabel, éta rentang otomatis naek lamun data anyar ditambahkeun. Sasuai, ieu cara nu pangmerenahna pikeun nambahkeun daptar.

Kumaha nyalin daptar turun?

Pikeun nyalin, cukup nganggo kombinasi konci Ctrl + C sareng Ctrl + V. Janten daptar turun-handap bakal disalin sareng pormatna. Pikeun ngahapus pormat, anjeun kedah nganggo témpél khusus (dina ménu kontéks, pilihan ieu muncul saatos nyalin daptar), dimana pilihan "kondisi nilai" disetel.

Pilih sadaya sél anu ngandung daptar turun

Pikeun ngalaksanakeun tugas ieu, anjeun kedah nganggo fungsi "Pilih grup sél" dina grup "Panggihan sareng Pilih".

Saatos éta, kotak dialog bakal dibuka, dimana anjeun kedah milih item "Sadaya" sareng "Ieu sami" dina menu "Validasi Data". Item kahiji milih sadaya daptar, sareng anu kadua milih ngan ukur anu mirip sareng anu tangtu.