Bagaimana untuk membuat sampel dalam Excel: 4 Fesyen Bekerja

Apabila bekerja dengan jadual Excel, ia sering diperlukan untuk menjalankan pemilihan pada kriteria tertentu atau dalam beberapa keadaan. Program ini boleh melakukan ini dalam pelbagai cara menggunakan satu siri alat. Mari kita cari cara membuat sampel dalam Excele menggunakan pelbagai pilihan.

Pensampelan

Sampel data terdiri daripada prosedur pemilihan dari jumlah hasil yang memenuhi syarat yang ditetapkan, diikuti dengan output mereka pada helaian senarai berasingan atau dalam julat sumber.

Kaedah 1: Memohon AutoFilt Extended

Cara paling mudah untuk membuat pemilihan adalah penggunaan autofilter yang dilanjutkan. Pertimbangkan bagaimana untuk melakukan ini dengan contoh tertentu.

1. Pilih kawasan pada helaian, antara data yang anda ingin buat sampel. Dalam tab Rumah, klik pada butang "Susun dan Penapis". Ia diletakkan di blok tetapan penyuntingan. Dalam senarai yang dibuka selepas senarai ini, klik pada butang "Penapis".

   

   Terdapat peluang untuk dilakukan dan berbeza. Untuk melakukan ini, selepas memilih kawasan pada helaian, kami berpindah ke tab "Data". Klik pada butang "Penapis", yang diletakkan pada pita dalam kumpulan jenis dan penapis.



2. Selepas tindakan ini dalam tajuk meja, pictograms kelihatan mula menapis dalam bentuk tepi segitiga kecil di pinggir kanan sel. Klik pada ikon ini dalam tajuk lajur itu, mengikut yang kami ingin membuat sampel. Dalam menu yang menjalankan menu, pergi melalui item "Penapis Teks". Seterusnya, pilih kedudukan "Penapis Customizable ...".



3. Tetingkap penapisan pengguna diaktifkan. Di dalamnya, anda boleh menetapkan had di mana pemilihan akan dibuat. Dalam senarai drop-down untuk lajur sel format berangka, yang kami gunakan sebagai contoh, anda boleh memilih salah satu daripada lima jenis keadaan:
   • sama;
   • tidak sama;
   • lebih banyak;
   • lebih atau sama;
   • lebih kecil.

   Mari kita tetapkan keadaan sebagai contoh untuk memilih hanya nilai-nilai yang jumlah pendapatan melebihi 10,000 rubel. Kami menubuhkan suis ke kedudukan "lebih". Dalam bidang yang betul sesuai dengan nilai "10,000". Untuk melakukan tindakan, klik pada butang "OK".



4. Seperti yang kita lihat, selepas penapisan, hanya garis yang kekal, di mana jumlah pendapatan melebihi 10,000 rubel.



5. Tetapi dalam lajur yang sama kita boleh menambah keadaan kedua. Untuk melakukan ini, kembali ke tetingkap penapisan tersuai. Seperti yang dapat kita lihat, di bahagian bawahnya terdapat satu lagi keadaan suis dan bidang yang sama untuk input. Marilah kita memasang sempadan pemilihan atas 15,000 rubel. Untuk melakukan ini, tetapkan suis ke kedudukan "kurang", dan di lapangan di sebelah kanan sesuai dengan nilai "15000".

   Di samping itu, masih terdapat suis keadaan. Dia mempunyai dua peruntukan "dan" dan "atau". Secara lalai, ia dipasang di kedudukan pertama. Ini bermakna bahawa hanya baris yang akan kekal dalam sampel yang memenuhi kedua-dua sekatan. Sekiranya ia ditetapkan kepada kedudukan "atau", maka nilai-nilai yang sesuai untuk mana-mana kedua-dua syarat akan kekal. Dalam kes kami, anda perlu menetapkan suis ke kedudukan "dan", iaitu, meninggalkan tetapan lalai ini. Selepas semua nilai dimasukkan, klik pada butang OK.



6. Sekarang hanya baris yang tinggal di dalam jadual, di mana jumlah pendapatan tidak kurang daripada 10,000 rubel, tetapi tidak melebihi 15,000 rubel.



7. Begitu juga, anda boleh mengkonfigurasi penapis dalam lajur lain. Adalah mungkin untuk mengekalkan penapisan dan pada keadaan sebelumnya yang ditetapkan dalam lajur. Jadi, mari lihat bagaimana pemilihan diambil menggunakan penapis untuk format tarikh. Klik pada ikon Penapisan dalam lajur yang sepadan. Secara konsisten dengan mengklik senarai "penapis mengikut tarikh" dan penapis.



8. Tetingkap Run AutoFilt Custom bermula lagi. Lakukan pemilihan hasil dalam jadual dari 4 hingga 6 Mei 2016 inklusif. Dalam suis pemilihan, seperti yang anda lihat, lebih banyak pilihan daripada untuk format berangka. Pilih kedudukan "selepas atau sama". Di lapangan di sebelah kanan, tetapkan nilai "04.05.2016". Di blok bawah, kami menetapkan suis ke kedudukan "sehingga atau sama". Dalam bidang yang betul, masukkan nilai "06.05.2016". Suis keserasian suis cuti dalam kedudukan lalai - "dan". Untuk memohon penapisan dalam tindakan, tekan butang "OK".



9. Seperti yang anda lihat, senarai kami telah menurun lebih banyak lagi. Sekarang hanya baris yang ditinggalkan di dalamnya, di mana jumlah pendapatan berbeza dari 10,000 hingga 15,000 rubel untuk tempoh dari 04.05 hingga 06.05.2016 termasuk.



10. Kami boleh menetapkan semula penapisan di salah satu lajur. Mari kita lakukan untuk nilai pendapatan. Klik pada ikon AutoFilter dalam lajur yang sepadan. Dalam senarai juntai bawah, klik pada item "Padam Penapis".



11. Seperti yang anda lihat, selepas tindakan ini, sampel oleh jumlah pendapatan akan dilumpuhkan, tetapi hanya akan mengambil pemilihan mengikut tarikh (dari 04/05/2016 hingga 06.05.2016).



12. Jadual ini mempunyai lajur lain - "Nama". Ia mengandungi data dalam format teks. Mari lihat bagaimana untuk membentuk sampel menggunakan penapisan melalui nilai-nilai ini.

    Klik pada ikon penapis dalam nama lajur. Secara konsisten pergi ke nama senarai "Penapis Teks" dan "Penapis Customizable ...".



13. Tetingkap Autofilter pengguna dibuka lagi. Mari buat sampel oleh nama "kentang" dan "daging". Dalam blok pertama, keadaan suis ditetapkan ke kedudukan "sama". Di lapangan di sebelah kanannya sesuai dengan perkataan "kentang". Suis unit yang lebih rendah juga akan meletakkan kedudukan "sama". Di lapangan bertentangan dengannya, saya membuat rekod - "Daging". Dan sekarang kita menjalankan apa yang mereka tidak pernah lakukan: Tetapkan suis keserasian ke kedudukan "atau". Sekarang garis yang mengandungi mana-mana syarat yang ditentukan akan dipaparkan pada skrin. Klik pada butang "OK".



14. Seperti yang anda lihat, terdapat sekatan pada tarikh dalam sampel baru (dari 04.05.2016 hingga 06.05.2016) dan dengan nama (kentang dan daging). Tiada sekatan ke atas jumlah pendapatan.



15. Anda boleh mengeluarkan sepenuhnya penapis dengan kaedah yang sama yang digunakan untuk memasangnya. Dan tidak kira kaedah yang digunakan. Untuk menetapkan semula penapisan, semasa dalam tab "Data", klik pada butang "Penapis", yang terletak di kumpulan "Susun dan Penapis".

    

    Pilihan kedua melibatkan peralihan ke tab "Rumah". Kami melakukan klik pada reben pada butang "Susun dan Penapis" dalam unit penyuntingan. Dalam senarai diaktifkan, klik pada butang "Penapis".

Apabila menggunakan mana-mana dua kaedah di atas, penapisan akan dipadamkan, dan hasil sampel dibersihkan. Iaitu, jadual akan ditunjukkan keseluruhan pelbagai data yang ada.

Pelajaran: Fungsi Penapis Auto dalam Excel

Kaedah 2: Penggunaan formula array

Buat pemilihan juga boleh menggunakan formula kompleks pelbagai. Berbeza dengan versi sebelumnya, kaedah ini menyediakan output hasil dalam jadual yang berasingan.

1. Pada helaian yang sama, kami membuat jadual kosong dengan nama-nama lajur yang sama di header sebagai sumber.



2. Allocate semua sel kosong dari lajur pertama jadual baru. Pasang kursor dalam rentetan formula. Hanya di sini akan dimasukkan oleh formula yang menghasilkan sampel pada kriteria yang ditentukan. Kami akan memilih baris, jumlah pendapatan yang melebihi 15,000 rubel. Dalam contoh tertentu kami, formula yang diperkenalkan akan kelihatan seperti berikut:

   = Indeks (A2: A29; terkecil (jika (15000

   Sememangnya, dalam setiap kes tertentu, alamat sel dan julat akan menjadi milik anda. Mengenai contoh ini, anda boleh membandingkan formula dengan koordinat mengenai ilustrasi dan menyesuaikannya untuk keperluan anda.



3. Oleh kerana ini adalah formula tatasusunan, untuk menerapkannya dalam tindakan, anda perlu menekan butang Enter, tetapi Ctrl + Shift + masukkan kombinasi kekunci. Kita melakukannya.



4. Setelah menyerlahkan lajur kedua dengan tarikh dan memasang kursor dalam rentetan formula, kami memperkenalkan ungkapan berikut:

   = Indeks (B2: B29; terkecil (jika (15000

   Klik Ctrl + Shift + masukkan kombinasi kekunci.



5. Begitu juga, dalam lajur dengan pendapatan, masukkan formula kandungan berikut:

   = Indeks (C2: C29; terkecil (jika (15000

   Sekali lagi, taipkan Ctrl + Shift + masukkan kombinasi kekunci.

   Dalam ketiga-tiga kes, hanya nilai pertama koordinat yang berubah, dan seluruh formula sepenuhnya sama.



6. Seperti yang kita lihat, meja dipenuhi dengan data, tetapi penampilannya tidak sepenuhnya menarik, lebih-lebih lagi, nilai-nilai tarikh diisi dengan salah. Ia adalah perlu untuk membetulkan kekurangan ini. Ketidakhadiran tarikh ini berkaitan dengan fakta bahawa format sel-sel lajur yang sama adalah perkara biasa, dan kita perlu menetapkan format tarikh. Kami menyerlahkan seluruh lajur, termasuk sel-sel dengan kesilapan, dan klik pada menonjolkan butang tetikus kanan. Dalam senarai yang muncul, pergi melalui "format sel ...".



7. Dalam tetingkap pemformatan yang dibuka, buka tab "Nombor". Dalam "Format Numerik" menyampaikan nilai "tarikh". Di sebelah kanan tetingkap, anda boleh memilih jenis paparan tarikh yang dikehendaki. Selepas tetapan dipamerkan, klik pada butang "OK".



8. Sekarang tarikh dipaparkan dengan betul. Tetapi, seperti yang kita lihat, seluruh bahagian bawah meja dipenuhi dengan sel-sel yang mengandungi nilai yang salah "# nombor!". Pada dasarnya, ini adalah sel-sel, data dari sampel yang mereka tidak mempunyai cukup. Ia akan menjadi lebih menarik jika mereka dipaparkan sama sekali kosong. Untuk tujuan ini, kami menggunakan pemformatan bersyarat. Kami menyerlahkan semua sel meja, kecuali untuk pengepala. Semasa dalam tab Rumah, klik pada butang "Pemformatan Bersyarat", yang berada dalam blok alat "gaya". Dalam senarai yang muncul, pilih item "Buat Peraturan ...".



9. Dalam tetingkap yang dibuka, pilih jenis peraturan "Format Hanya sel yang mengandungi". Dalam bidang pertama, di bawah tulisan "Format hanya sel yang mana keadaan berikut," pilih kedudukan "Ralat". Seterusnya, klik pada butang "Format ...".



10. Dalam tetingkap pemformatan berjalan, pergi ke tab "Font" dan pilih Putih dalam medan yang sesuai. Selepas tindakan ini, klik pada butang "OK".



11. Pada butang dengan nama yang sama, klik selepas kembali ke tetingkap Mewujudkan Tingkap.

Sekarang kita mempunyai sampel yang siap pada had yang ditentukan dalam jadual yang dihiasi dengan sempurna.

Pelajaran: Pemformatan bersyarat dalam Excel

Kaedah 3: Pensampelan pada beberapa keadaan menggunakan formula

Sama seperti apabila menggunakan penapis, menggunakan formula yang anda boleh pilih pada beberapa keadaan. Sebagai contoh, ambil jadual sumber yang sama, serta jadual kosong, di mana keputusan akan dikeluarkan, dengan pemformatan berangka dan bersyarat yang telah dilakukan. Kami akan mewujudkan had pertama sempadan yang lebih rendah pemilihan oleh pendapatan dalam 15,000 rubel, dan keadaan kedua sempadan atas 20,000 rubel.

1. Masukkan dalam lajur yang berasingan, keadaan sempadan untuk pensampelan.



2. Seperti dalam kaedah sebelumnya, secara bergantian memperuntukkan lajur kosong dari jadual baru dan masukkan tiga formula yang sepadan di dalamnya. Dalam lajur pertama kami memperkenalkan ungkapan berikut:

   = Indeks (A2: A29; terkecil (jika (($ D $ 2 = C2: C29); garis (C2: C29); ""); rentetan (C2: C29) -strkok ($ C $ 1)) - Baris ($ C $ 1))

   Dalam lajur berikutnya, sesuai dengan formula yang sama, hanya dengan menukar koordinat dengan serta-merta selepas nama pengendali, indeks ke lajur yang sepadan yang kita perlukan, dengan analogi dengan kaedah sebelumnya.

   Setiap kali selepas masuk, jangan lupa untuk mendapatkan Ctrl + Shift + masukkan kombinasi kekunci.



3. Kelebihan kaedah ini sebelum yang sebelumnya ialah jika kita mahu mengubah sempadan sampel, tidak perlu untuk mengubah formula pepejal itu sendiri, yang dengan sendirinya agak bermasalah. Ia cukup dalam lajur keadaan pada helaian untuk mengubah nombor sempadan pada yang diperlukan oleh pengguna. Hasil pemilihan akan segera berubah secara automatik.

Kaedah 4: Pensampelan Rawak

Dalam pengasingan, dengan bantuan formula khas, ia juga mungkin untuk memohon pemilihan rawak. Ia diperlukan dalam beberapa kes apabila bekerja dengan sejumlah besar data apabila anda perlu membentangkan gambar yang sama tanpa analisis komprehensif semua data array.

1. Ke kiri meja melangkau satu lajur. Dalam sel lajur seterusnya, yang terletak bertentangan dengan sel pertama dengan data jadual, masukkan formula:

   = Pelekat ()

   Ciri ini memaparkan nombor rawak. Untuk mengaktifkannya, klik pada butang Enter.



2. Untuk membuat satu lajur nombor rawak, tetapkan kursor ke sudut kanan bawah sel, yang sudah mengandungi formula. Penanda pengisian muncul. Saya meregangkannya dengan butang kiri tetikus selari dengan jadual dengan data sehingga akhirnya.



3. Sekarang kita mempunyai pelbagai sel yang dipenuhi dengan nombor rawak. Tetapi, dia mengandungi formula Calc. Kita perlu bekerjasama dengan nilai yang bersih. Untuk melakukan ini, salin ke lajur kosong di sebelah kanan. Pilih julat sel dengan nombor rawak. Terletak di tab "Rumah", klik pada ikon "Salin" pada reben.



4. Kami menyerlahkan lajur kosong dan klik klik kanan, memanggil menu konteks. Dalam bar alat "Parameter Masukkan", pilih klausa "Nilai", digambarkan sebagai Piktogram dengan nombor.



5. Selepas itu, semasa di tab "Rumah", klik pada ikon yang sudah biasa dari ikon "Susun dan Penapis" yang sudah biasa. Dalam senarai drop-down, hentikan pemilihan pada item "Penyusun Adat".



6. Tetingkap tetapan penyortiran diaktifkan. Pastikan anda memasang tanda yang bertentangan dengan parameter "Data saya mengandungi tajuk utama" jika topi tersedia, tetapi tiada klik. Dalam bidang "Sort by", tentukan nama lajur di mana nilai yang disalin nombor rawak terkandung. Dalam medan "Susun", biarkan tetapan lalai. Dalam bidang "Pesanan", anda boleh memilih parameter sebagai "menaik" dan "menurun". Untuk sampel rawak, nilai ini tidak ada. Selepas tetapan dihasilkan, klik pada butang "OK".



7. Selepas itu, semua nilai jadual dibina dalam urutan menaik atau pengurangan nombor rawak. Anda boleh mengambil apa-apa bilangan baris pertama dari jadual (5, 10, 12, 15, dan lain-lain) dan mereka boleh dianggap sebagai hasil daripada sampel rawak.

Pelajaran: Menyusun dan menapis data ke Excel

Seperti yang anda lihat, sampel dalam jadual Excel boleh dihasilkan, kedua-duanya menggunakan autofilter dan menggunakan formula khas. Dalam kes pertama, hasilnya akan dipaparkan di meja sumber, dan di tempat kedua - di kawasan yang berasingan. Adalah mungkin untuk menghasilkan pemilihan, kedua-dua satu keadaan dan beberapa. Di samping itu, anda boleh membuat sampel rawak menggunakan fungsi pelekat.