Bagaimana untuk membuat senarai juntai bawah dalam Excel

Senarai juntai bawah ialah alat yang sangat berguna yang boleh membantu menjadikan kerja dengan maklumat lebih selesa. Ia memungkinkan untuk mengandungi beberapa nilai dalam sel sekaligus, yang boleh anda gunakan, seperti mana-mana yang lain. Untuk memilih yang anda perlukan, cuma klik pada ikon anak panah, selepas itu senarai nilai uXNUMXbuXNUMXbis dipaparkan. Selepas memilih yang tertentu, sel diisi secara automatik dengannya, dan formula dikira semula berdasarkannya.

Excel menyediakan banyak kaedah berbeza untuk menjana menu lungsur, dan sebagai tambahan, ia membolehkan anda menyesuaikannya secara fleksibel. Mari analisa kaedah ini dengan lebih terperinci.

Proses penciptaan senarai

Untuk menjana menu pop timbul, klik pada item menu di sepanjang laluan "Data" - "Pengesahan Data". Kotak dialog akan dibuka di mana anda perlu mencari tab "Parameter" dan klik padanya jika ia belum dibuka sebelum ini. Ia mempunyai banyak tetapan, tetapi item "Jenis Data" adalah penting kepada kami. Daripada semua makna, "Senarai" adalah yang betul.

Bilangan kaedah untuk memasukkan maklumat ke dalam senarai pop timbul agak besar.

1. Petunjuk bebas bagi elemen senarai yang dipisahkan oleh koma bertitik dalam medan "Sumber" yang terletak pada tab yang sama pada kotak dialog yang sama.
   

   

2. Petunjuk awal nilai. Medan Sumber mengandungi julat di mana maklumat yang diperlukan tersedia.
   

   

3. Menentukan julat bernama. Kaedah yang mengulangi yang sebelumnya, tetapi ia hanya perlu untuk menamakan julat terlebih dahulu.
   

   

Mana-mana kaedah ini akan menghasilkan hasil yang diingini. Mari lihat kaedah untuk menjana senarai juntai bawah dalam situasi kehidupan sebenar.

Berdasarkan data daripada senarai

Katakan kita mempunyai jadual yang menerangkan jenis buah-buahan yang berbeza.

Untuk membuat senarai dalam menu lungsur berdasarkan set maklumat ini, anda perlu melakukan perkara berikut:

1. Pilih sel yang dikhaskan untuk senarai masa hadapan.
2. Cari tab Data pada reben. Di sana kita klik pada "Sahkan data".
   

   

3. Cari item "Jenis Data" dan tukar nilai kepada "Senarai".
   

   

4. Dalam medan yang menandakan pilihan "Sumber", masukkan julat yang dikehendaki. Sila ambil perhatian bahawa rujukan mutlak mesti dinyatakan supaya apabila menyalin senarai, maklumat tidak beralih.
   

Selain itu, terdapat fungsi untuk menjana senarai sekaligus dalam lebih daripada satu sel. Untuk mencapai ini, anda harus memilih kesemuanya, dan melakukan langkah yang sama seperti yang diterangkan sebelum ini. Sekali lagi, anda perlu memastikan bahawa rujukan mutlak ditulis. Jika alamat tidak mempunyai tanda dolar di sebelah nama lajur dan baris, maka anda perlu menambahnya dengan menekan kekunci F4 sehingga tanda $ berada di sebelah nama lajur dan baris.

Dengan rakaman data manual

Dalam situasi di atas, senarai telah ditulis dengan menyerlahkan julat yang diperlukan. Ini adalah kaedah yang mudah, tetapi kadangkala perlu untuk merekodkan data secara manual. Ini akan membolehkan anda mengelakkan pertindihan maklumat dalam buku kerja.

Katakan kita berhadapan dengan tugas membuat senarai yang mengandungi dua pilihan yang mungkin: ya dan tidak. Untuk menyelesaikan tugas, perlu:

1. Klik pada sel untuk senarai.
2. Buka "Data" dan di sana cari bahagian "Pemeriksaan Data" yang biasa kepada kami.
   

   

3. Sekali lagi, pilih jenis "Senarai".
   

   

4. Di sini anda perlu memasukkan “Ya; Tidak” sebagai sumber. Kami melihat bahawa maklumat dimasukkan secara manual menggunakan koma bertitik untuk penghitungan.

Selepas mengklik OK, kami mempunyai hasil berikut.

Seterusnya, program secara automatik akan mencipta menu lungsur dalam sel yang sesuai. Semua maklumat yang telah ditentukan oleh pengguna sebagai item dalam senarai pop timbul. Peraturan untuk membuat senarai dalam beberapa sel adalah serupa dengan yang sebelumnya, dengan satu-satunya pengecualian bahawa anda mesti menentukan maklumat secara manual menggunakan koma bertitik.

Mencipta senarai juntai bawah menggunakan fungsi OFFSET

Sebagai tambahan kepada kaedah klasik, adalah mungkin untuk menggunakan fungsi tersebut PELUPUSANuntuk menjana menu lungsur.

Mari buka helaian.

Untuk menggunakan fungsi untuk senarai juntai bawah, anda perlu melakukan perkara berikut:

1. Pilih sel yang diminati di mana anda ingin meletakkan senarai masa hadapan.
2. Buka tab "Data" dan tetingkap "Pengesahan Data" dalam urutan.
   

   

3. Senarai set". Ini dilakukan dengan cara yang sama seperti contoh sebelumnya. Akhirnya, formula berikut digunakan: =OFFSET(A$2$;0;0;5). Kami memasukkannya di mana sel yang akan digunakan sebagai hujah ditentukan.

Kemudian program akan membuat menu dengan senarai buah-buahan.

Sintaks untuk ini ialah:

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

Kami melihat bahawa fungsi ini mempunyai 5 hujah. Pertama, alamat sel pertama yang akan diimbangi diberikan. Dua argumen seterusnya menentukan bilangan baris dan lajur untuk diimbangi. Bercakap tentang kita, hujah Height ialah 5 kerana ia mewakili ketinggian senarai. 

Senarai jatuh turun dalam Excel dengan penggantian data (+ menggunakan fungsi OFFSET)

Dalam kes yang diberikan PELUPUSAN dibenarkan untuk mencipta menu pop timbul yang terletak dalam julat tetap. Kelemahan kaedah ini ialah selepas menambah item, anda perlu mengedit formula sendiri.

Untuk membuat senarai dinamik dengan sokongan untuk memasukkan maklumat baharu, anda mesti:

1. Pilih sel yang diminati.
2. Kembangkan tab "Data" dan klik pada "Pengesahan Data".
3. Dalam tetingkap yang terbuka, pilih item "Senarai" sekali lagi dan tentukan formula berikut sebagai sumber data: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Klik OK.

Ini mengandungi fungsi COUNTIF, untuk segera menentukan bilangan sel yang diisi (walaupun ia mempunyai bilangan kegunaan yang lebih besar, kami hanya menulisnya di sini untuk tujuan tertentu).

Untuk formula berfungsi dengan normal, adalah perlu untuk mengesan sama ada terdapat sel kosong pada laluan formula. Mereka tidak sepatutnya.

Senarai lungsur dengan data daripada helaian lain atau fail Excel

Kaedah klasik tidak berfungsi jika anda perlu mendapatkan maklumat daripada dokumen lain atau bahkan helaian yang terkandung dalam fail yang sama. Untuk ini, fungsi ini digunakan TIDAK LANGSUNG, yang membolehkan anda memasukkan dalam format yang betul pautan ke sel yang terletak dalam helaian lain atau secara umum – fail. Anda perlu melakukan perkara berikut:

1. Aktifkan sel tempat kita meletakkan senarai.
2. Kita buka tingkap yang kita dah tahu. Di tempat yang sama di mana kami sebelum ini menunjukkan sumber untuk julat lain, formula ditunjukkan dalam format =INDIRECT(“[List1.xlsx]Helaian1!$A$1:$A$9”). Sememangnya, bukannya Senarai1 dan Helaian1, anda boleh memasukkan nama buku dan helaian anda, masing-masing. 

Perhatian! Nama fail diberikan dalam kurungan segi empat sama. Dalam kes ini, Excel tidak akan dapat menggunakan fail yang sedang ditutup sebagai sumber maklumat.

Perlu diingatkan juga bahawa nama fail itu sendiri masuk akal hanya jika dokumen yang diperlukan terletak dalam folder yang sama dengan folder di mana senarai akan dimasukkan. Jika tidak, maka anda mesti menyatakan alamat dokumen ini sepenuhnya.

Mencipta Dropdown Bergantung

Senarai bergantung ialah senarai yang kandungannya dipengaruhi oleh pilihan pengguna dalam senarai lain. Katakan kita mempunyai jadual terbuka di hadapan kita yang mengandungi tiga julat, setiap satunya telah diberi nama.

Anda perlu mengikuti langkah ini untuk menjana senarai yang hasilnya dipengaruhi oleh pilihan yang dipilih dalam senarai lain.

1. Buat senarai pertama dengan nama julat.
   

   

2. Di titik masuk sumber, penunjuk yang diperlukan diserlahkan satu demi satu.
   

   

3. Buat senarai ke-2 bergantung pada jenis tumbuhan yang dipilih oleh orang itu. Sebagai alternatif, jika anda menentukan pokok dalam senarai pertama, maka maklumat dalam senarai kedua ialah "oak, hornbeam, chestnut" dan seterusnya. Ia adalah perlu untuk menulis formula di tempat input sumber data =TIDAK LANGSUNG(E3). E3 – sel yang mengandungi nama julat 1.=INDIRECT(E3). E3 – sel dengan nama senarai 1.

Sekarang semuanya sudah siap.

Bagaimana untuk memilih berbilang nilai daripada senarai juntai bawah?

Kadangkala tidak mungkin untuk memberi keutamaan kepada hanya satu nilai, jadi lebih daripada satu mesti dipilih. Kemudian anda perlu menambah makro pada kod halaman. Menggunakan kombinasi kekunci Alt + F11 membuka Editor Visual Basic. Dan kod itu dimasukkan di sana.

Subsheet_Change Peribadi (Sasaran ByVal Sebagai Julat)

    Pada Error Resume Next

    Jika Tidak Bersilang(Sasaran, Julat(«Е2:Е9»)) Tiada Apa-apa Dan Sasaran.Cells.Count = 1 Kemudian

        Application.EnableEvents = Palsu

        Jika Len (Target.Offset (0, 1)) = 0 Maka

            Sasaran.Offset (0, 1) = Sasaran

        lagi

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

        End If

        Target.ClearContents

        Application.EnableEvents = Benar

    End If

Akhir Sub 

Untuk membolehkan kandungan sel ditunjukkan di bawah, kami memasukkan kod berikut ke dalam editor.

Subsheet_Change Peribadi (Sasaran ByVal Sebagai Julat)

    Pada Error Resume Next

    Jika Tidak Bersilang(Sasaran, Julat(«Н2:К2»)) Tiada Apa-apa Dan Sasaran.Cells.Count = 1 Kemudian

        Application.EnableEvents = Palsu

        Jika Len (Target.Offset (1, 0)) = 0 Maka

            Sasaran.Offset (1, 0) = Sasaran

        lagi

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

        End If

        Target.ClearContents

        Application.EnableEvents = Benar

    End If

Akhir Sub

Dan akhirnya, kod ini digunakan untuk menulis dalam satu sel.

Subsheet_Change Peribadi (Sasaran ByVal Sebagai Julat)

    Pada Error Resume Next

    Jika Tidak Bersilang(Sasaran, Julat(«C2:C5»)) Tiada Apa-apa Dan Sasaran.Cells.Count = 1 Kemudian

        Application.EnableEvents = Palsu

        newVal = Sasaran

        Permohonan.Buat asal

        oldval = Sasaran

        Jika Len (oldval) <> 0 Dan oldval <> newVal Then

            Sasaran = Sasaran & «,» & newVal

        lagi

            Sasaran = newVal

        End If

        Jika Len (newVal) = 0 Kemudian Target.ClearContents

        Application.EnableEvents = Benar

    End If

Akhir Sub

Julat boleh diedit.

Bagaimana untuk membuat senarai juntai bawah dengan carian?

Dalam kes ini, anda mesti menggunakan jenis senarai yang berbeza pada mulanya. Tab "Pembangun" dibuka, selepas itu anda perlu mengklik atau mengetik (jika skrin disentuh) pada elemen "Sisipkan" - "ActiveX". Ia mempunyai kotak kombo. Anda akan digesa untuk melukis senarai ini, selepas itu ia akan ditambahkan pada dokumen.

Selanjutnya, ia dikonfigurasikan melalui sifat, di mana julat ditentukan dalam pilihan ListFillRange. Sel di mana nilai yang ditentukan pengguna dipaparkan dikonfigurasikan menggunakan pilihan LinkedCell. Seterusnya, anda hanya perlu menulis aksara pertama, kerana program secara automatik akan mencadangkan nilai yang mungkin.

Senarai jatuh turun dengan penggantian data automatik

Terdapat juga fungsi bahawa data digantikan secara automatik selepas ia ditambahkan pada julat. Ia mudah untuk melakukan ini:

1. Buat satu set sel untuk senarai masa hadapan. Dalam kes kami, ini adalah satu set warna. Kami memilihnya.
   

   

2. Seterusnya, ia perlu diformatkan sebagai jadual. Anda perlu mengklik butang dengan nama yang sama dan pilih gaya jadual.
   

   

   

Seterusnya, anda perlu mengesahkan julat ini dengan menekan butang "OK".

Kami memilih jadual yang terhasil dan memberikannya nama melalui medan input yang terletak di atas lajur A.

Itu sahaja, terdapat jadual, dan ia boleh digunakan sebagai asas untuk senarai juntai bawah, yang anda perlukan:

1. Pilih sel di mana senarai itu terletak.
2. Buka dialog Pengesahan Data.
   

   

3. Kami menetapkan jenis data kepada "Senarai", dan sebagai nilai kami memberikan nama jadual melalui tanda =.
   

   

   

Segala-galanya, sel sudah sedia, dan nama warna ditunjukkan di dalamnya, seperti yang kita perlukan pada asalnya. Kini anda boleh menambah kedudukan baharu hanya dengan menulisnya dalam sel yang terletak lebih rendah sedikit selepas yang terakhir.

Ini adalah kelebihan jadual, bahawa julat secara automatik meningkat apabila data baharu ditambah. Oleh itu, ini adalah cara yang paling mudah untuk menambah senarai.

Bagaimana untuk menyalin senarai juntai bawah?

Untuk menyalin, cukup menggunakan kombinasi kekunci Ctrl + C dan Ctrl + V. Jadi senarai juntai bawah akan disalin bersama dengan pemformatan. Untuk mengalih keluar pemformatan, anda perlu menggunakan tampal khas (dalam menu konteks, pilihan ini muncul selepas menyalin senarai), di mana pilihan "syarat pada nilai" ditetapkan.

Pilih semua sel yang mengandungi senarai juntai bawah

Untuk menyelesaikan tugas ini, anda mesti menggunakan fungsi "Pilih kumpulan sel" dalam kumpulan "Cari dan Pilih".

Selepas itu, kotak dialog akan dibuka, di mana anda harus memilih item "Semua" dan "Ini sama" dalam menu "Pengesahan Data". Item pertama memilih semua senarai, dan yang kedua hanya memilih senarai yang serupa dengan senarai tertentu.