Inti dari kueri pemilihan adalah memilih baris dari tabel sumber yang memenuhi kriteria tertentu (mirip dengan menggunakan standar). Mari kita pilih nilai dari tabel sumber menggunakan. Tidak seperti menggunakan ( CTRL + SHIFT + L atau Data / Urutkan dan Filter / Filter) baris yang dipilih akan ditempatkan di tabel terpisah.
Dalam artikel ini, kami akan mempertimbangkan kueri yang paling umum, misalnya: pemilihan baris tabel yang nilainya dari kolom numerik berada dalam rentang tertentu (interval); pilihan baris yang tanggalnya periode tertentu; tugas dengan 2 kriteria teks dan lainnya. Mari kita mulai dengan pertanyaan sederhana.
1. Satu kriteria numerik (Pilih Produk yang harganya lebih tinggi dari harga minimum)
contoh file, lembar Salah satu kriterianya adalah angka ).
Penting untuk menampilkan dalam tabel terpisah hanya catatan (baris) dari tabel Sumber, yang memiliki harga lebih tinggi dari 25.
Anda dapat dengan mudah menyelesaikan tugas ini dan selanjutnya dengan bantuan. Untuk melakukan ini, pilih tajuk tabel Sumber dan tekan CTRL + SHIFT + L... Menggunakan daftar drop-down di tajuk Harga pilih Filter Angka..., lalu atur kondisi pemfilteran yang diperlukan dan klik OK.
Rekaman yang cocok dengan kriteria pemilihan akan ditampilkan.
Pendekatan lain adalah dengan menggunakan. Berbeda dengan baris yang dipilih, mereka akan ditempatkan di tabel terpisah - jenis, yang, misalnya, dapat diformat dengan gaya yang berbeda dari tabel Sumber atau membuat modifikasi lainnya.
Tempatkan kriteria (harga minimum) di sel E6 , tabel untuk data yang difilter - dalam rentang D10: E19 .
Sekarang mari kita pilih rentangnya D11: D19 (kolom Produk) dan masukkan:
INDEKS (A11: A19;
KECIL (JIKA ($ E $ 6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-LINE ($ B $10))
Sebagai gantinya MEMASUKKAN tekan pintasan keyboard CTRL + SHIFT + ENTER(rumus array akan menjadi).
E11: E19 (kolom Harga) di mana kita akan memasukkan yang serupa:
INDEKS (B11: B19;
KECIL (JIKA ($ E $ 6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-LINE ($ B $10))
Akibatnya, kita akan mendapatkan tabel baru yang hanya berisi produk dengan harga tidak kurang dari yang ditentukan di sel E6 .
Untuk menunjukkan dinamisme Permintaan Sampel yang diterima, kami memperkenalkan ke E6 nilai 55. Hanya 2 record yang akan masuk ke tabel baru.
Jika Anda menambahkan produk baru dengan Harga 80 ke tabel Sumber, catatan baru akan ditambahkan secara otomatis ke tabel baru.
Catatan... Anda juga dapat menggunakan dan untuk menampilkan data yang difilter. Pilihan alat tertentu tergantung pada tugas yang dihadapi pengguna.
Jika Anda tidak nyaman menggunakan rumus array, yang mengembalikan beberapa nilai, Anda dapat menggunakan pendekatan berbeda, yang dibahas dalam bagian di bawah ini: 5.a, 7, 10, dan 11. Dalam kasus ini, digunakan.
2. Dua kriteria numerik (Pilih Produk yang harganya termasuk dalam kisaran tersebut)
Biarkan ada tabel Awal dengan daftar Produk dan Harga (lihat. contoh file, lembarRentang Angka).
Tempatkan kriteria (batas harga bawah dan atas) dalam kisaran E5: E6 .
itu. jika Harga Barang berada dalam interval yang ditentukan, maka catatan tersebut akan muncul di tabel data Filter yang baru.
Tidak seperti tugas sebelumnya, kami akan membuat dua: Produk dan Harga (Anda dapat melakukannya tanpa mereka, tetapi mereka nyaman saat menulis formula). Rumus yang sesuai akan terlihat di Manajer Nama ( Rumus / Nama yang Ditetapkan / Manajer Nama) sebagai berikut (lihat gambar di bawah).
Sekarang mari kita pilih rentangnya D11: D19 dan di dalamnya kami memperkenalkan:
INDEKS (Barang;
PALING SEDIKIT(
JIKA (($ E $5<=Цены)*($E$6>= Harga); LINE (Harga); "");
Sebagai gantinya MEMASUKKAN tekan pintasan keyboard CTRL + SHIFT + ENTER.
Kami akan melakukan manipulasi yang sama dengan jangkauan E11: E19 di mana kami akan memperkenalkan yang serupa:
INDEKS (Harga;
PALING SEDIKIT(
JIKA (($ E $5<=Цены)*($E$6>= Harga); LINE (Harga); "");
LINE (Harga) -LINE ($ B $ 10)) - LINE ($ B $ 10))
Akibatnya, kita akan mendapatkan tabel baru yang hanya berisi produk yang harganya jatuh dalam interval yang ditentukan dalam sel E5 dan E6 .
Untuk menunjukkan dinamisme Laporan yang diterima (Permintaan seleksi), kami memasukkan E6 nilai 65. Satu record lagi dari tabel Source akan ditambahkan ke tabel baru yang memenuhi kriteria baru.
Jika Anda menambahkan produk baru dengan Harga dalam kisaran 25 hingga 65 ke tabel Sumber, maka catatan baru akan ditambahkan ke tabel baru.
File sampel juga berisi rumus larik yang menangani kesalahan saat kolom Harga berisi nilai kesalahan, seperti # DIV / 0! (lihat lembar Pemrosesan kesalahan).
Tugas-tugas berikut diselesaikan dengan cara yang sama, jadi kami tidak akan mempertimbangkannya secara rinci.
3. Tanggal satu kriteria (Pilih Produk yang Tanggal Pengirimannya cocok dengan yang ditentukan)
contoh file, lembarSatu kriteria - Tanggal).
Untuk memilih baris, rumus array digunakan, mirip dengan Tugas1 (bukan kriteria<= используется =):
=INDEX (A12: A20; KECIL (JIKA ($ E $ 6 = B12: B20; LINE (B12: B20); ""); LINE (B12: B20) -LINE ($ B $ 11)) - LINE ($ B $11))
INDEX (B12: B20; KECIL (JIKA ($ E $ 6 = B12: B20; LINE (B12: B20); ""); LINE (B12: B20) -LINE ($ B $ 11)) - LINE ($ B $11))
4. Dua kriteria Tanggal (Pilih Produk yang Tanggal Pengirimannya termasuk dalam kisaran tersebut)
Misalkan ada tabel Awal dengan daftar Barang dan Tanggal Pengiriman (lihat. contoh file, lembarRentang tanggal).
Perhatikan bahwa kolom Tanggal TIDAK DIUrut.
Solusi1: Anda dapat menggunakan untuk memfilter baris.
Masuk ke sel D12 rumus larik:
INDEX (A $12: A $20;
BESAR (($E $6<=$B$12:$B$20)*($E$7>= $B$12:$B$20)* (LINE ($B$12:$B$20) -LINE ($B$1));
$ J $ 12-LINE (A12) + LINE ($ B $ 11) +1))
Catatan: Setelah memasukkan rumus, alih-alih tombol ENTER, tekan kombinasi tombol CTRL + SHIFT + ENTER. Pintasan keyboard ini digunakan untuk memasukkan rumus array.
Salin rumus array ke jumlah sel yang diinginkan. Rumus hanya akan mengembalikan nilai Produk yang dikirimkan dalam rentang tanggal yang ditentukan. Sel lainnya akan berisi #NUM! Kesalahan. Kesalahan dalam file contoh (Lembar 4 Rentang Tanggal) .
Rumus serupa harus dimasukkan untuk tanggal di kolom E.
Di dalam sel J12 menghitung jumlah baris tabel sumber yang memenuhi kriteria:
COUNTIFS (B12: B20; "> =" & $E $6; B12: B20; "<="&$E$7)
Baris tabel sumber yang memenuhi kriteria adalah.
Solusi2: Untuk memilih baris, Anda dapat menggunakan rumus array yang mirip dengan Task2 (yaitu):
=INDEKS (A12: A20; KECIL (JIKA (($ E $ 6<=B12:B20)*($E$7>= B12: B20); LINE (B12: B20); ""); LINE (B12: B20) -LINE ($ B $ 11)) - LINE ($ B $ 11))
INDEKS (B12: B20; KECIL (JIKA (($ E $ 6<=B12:B20)*($E$7>= B12: B20); LINE (B12: B20); ""); LINE (B12: B20) -LINE ($ B $ 11)) - LINE ($ B $ 11))
Untuk memasukkan rumus pertama, pilih rentang sel G12: G20 ... Setelah memasukkan rumus, alih-alih tombol ENTER, tekan kombinasi tombol CTRL + SHIFT + ENTER.
Solusi3: Jika kolom Tanggal DIUrut, maka Anda tidak perlu menggunakan rumus array.
Pertama, Anda perlu menghitung posisi pertama dan terakhir dari baris yang memenuhi kriteria. Kemudian cetak garis.
Contoh ini sekali lagi dengan jelas menunjukkan betapa mudahnya menulis rumus.
5. Tanggal satu kriteria (Pilih Barang yang Tanggal Pengirimannya tidak lebih awal / tidak lebih dari yang ditentukan)
Misalkan ada tabel Awal dengan daftar Barang dan Tanggal Pengiriman (lihat. contoh file, lembar Satu kriteria - Tanggal (paling lambat) ).
Untuk memilih baris yang tanggalnya tidak lebih awal (termasuk tanggal itu sendiri), gunakan rumus array:
=INDEKS (A12: A20; KECIL (JIKA ($ E $ 7<=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))
Juga, file contoh berisi rumus untuk kondisi: Tidak lebih awal (tidak termasuk); Paling lambat (termasuk); Paling lambat (tidak termasuk).
7. One Text Criterion (Pilih Produk dengan tipe tertentu)
Biarkan ada tabel Awal dengan daftar Produk dan Harga (lihat. contoh file, lembarSatu kriteria - Teks).
8. Dua Kriteria Teks (Pilih Barang dari jenis tertentu yang dikirim pada bulan tertentu)
Biarkan ada tabel Awal dengan daftar Produk dan Harga (lihat. contoh file, lembar 2 kriteria - teks (I) ).
INDEX ($ A $ 11: $ A $ 19;
KECIL (JIKA (($ F $ 6 = $ A $ 11: $ A $ 19) * ($ F $ 7 = $ B $ 11: $ B $ 19); GARIS ($ A $ 11: $ A $ 19) -LINE ($ A $10); 30); LINE (TIDAK LANGSUNG ("A1: A" & BARIS ($ A $ 11: $ A $ 19)))))
Ekspresi ($ F $ 6 = $ A $ 11: $ A $ 19) * ($ F $ 7 = $ B $ 11: $ B $ 19) menetapkan kedua kondisi (Item dan Bulan).
Ekspresi GARIS (TIDAK LANGSUNG ("A1: A" & BARIS ($ A $ 11: $ A $ 19))) bentuk (1: 2: 3: 4: 5: 6: 7: 8: 9), mis. nomor baris dalam tabel.
9. Dua Kriteria Teks (Pilih Produk Jenis Tertentu)
Biarkan ada tabel Awal dengan daftar Produk dan Harga (lihat. contoh file, lembar2 kriteria - teks (ATAU)).
Tidak seperti Soal 7, pilih baris dengan barang dari 2 jenis ().
Rumus array digunakan untuk memilih baris:
INDEX (A $ 11: A $ 19;
LARGE ((($E $6 = $A$11:$A$19) + ($E$7 = $A$11:$A$19))* (ROW ($A$11:$A$19 ) -LINE ($ A $ 10)); COUNTIF ($ A $ 11: $ A $ 19; $ E $ 6) + COUNTIF ($ A $ 11: $ A $ 19; $ E $ 7) -ROWS ($ A $ 11: A11) +1))
Kondisi ($E$6 = $A$11:$A$19) + ($E$7 = $A$11:$A$19) menjamin bahwa hanya jenis barang tertentu yang akan dipilih dari sel kuning (Produk2 dan Produk3). Tanda + (penambahan) digunakan untuk tugas (setidaknya 1 kriteria harus dipenuhi).
Ekspresi di atas akan mengembalikan array (0: 0: 0: 0: 1: 1: 1: 0: 0). Mengalikannya dengan ekspresi LINE ($ A $ 11: $ A $ 19) -LINE ($ A $ 10), yaitu pada (1: 2: 3: 4: 5: 6: 7: 8: 9), kami mendapatkan susunan posisi (nomor baris tabel) yang memenuhi kriteria. Dalam kasus kami, itu akan menjadi array (0: 0: 0: 0: 5: 6: 7: 0: 0).
Sebagai contoh, kami menyajikan solusi untuk masalah berikut: Pilih Produk, yang harganya terletak pada kisaran tertentu dan diulang beberapa kali atau lebih.
Mari kita ambil tabel pengiriman barang sebagai yang pertama.
Misalkan kita tertarik pada berapa banyak dan kiriman barang apa yang dipasok dengan harga 1000 rubel. hingga 2000r. (kriteria 1). Apalagi harus ada minimal 3 pihak dengan harga yang sama (kriteria 2).
Solusinya adalah rumus array:
KECIL (ROW ($A$14:$A$27)* ($C$14:$C$27> = $B$7)* ($C$14:$C$27<=$C$7)*($D$14:$D$27>= $B $10); F14 + ($G$8- $G$9))
Rumus ini mengembalikan nomor baris yang memenuhi kedua kriteria.
Rumus = SUMPRODUCT (($ C $ 14: $ C $ 27> = $ B $ 7) * ($ C $ 14: $ C $ 27<=$C$7)*($D$14:$D$27>= $B$ 10)) menghitung jumlah baris yang memenuhi kriteria.
11. Kami menggunakan nilai kriteria (Apa saja) atau (Semua)
DI contoh file pada lembar "11. Kriteria Setiap atau (Semua)" varian kriteria ini telah diterapkan.
Rumus dalam hal ini harus berisi fungsi IF(). Jika nilai (Semua) dipilih, maka rumus digunakan untuk menampilkan nilai tanpa memperhitungkan kriteria ini. Jika ada nilai lain yang dipilih, maka kriteria bekerja dengan cara biasa.
JIKA ($C$8 = "(Semua)";
KECIL ((LINE ($ B $ 13: $ B $ 26) -LINE ($ B $ 12)) * ($ D $ 13: $ D $ 26> = $ D $ 8); F13 + ($ G $ 6 - $G$7));
KECIL ((LINE ($ B $ 13: $ B $ 26) -LINE ($ B $ 12)) * ($ D $ 13: $ D $ 26> = $ D $ 8) * ($ C $ 13: $ C$26 = $C$8) ; F13 + ($G$6- $G$7)))
Sisa formula serupa dengan yang dibahas di atas.
Mengambil data
Buat laporan tentang sampel dari Lembar 5 di kolom "Kinerja kualitatif, persen". (dari Lembar 8, tabel 7)
Untuk mengambil sampel data, Anda harus melakukan langkah-langkah berikut:
Tentukan jumlah elemen array baru sesuai dengan kondisi yang diberikan dengan memasukkan variabel menggunakan operator InputBoxBox
Deklarasikan dan deklarasikan ulang array baru
Bentuk array baru. Untuk melakukan ini, Anda perlu mengatur jumlah elemen pertama dari array baru u = 1. Kemudian siklus dijalankan di mana kondisi pemilihan ditulis untuk kolom "Kinerja kualitatif, persen." Jika hasil pengecekan benar, maka elemen array yang dianalisis menjadi elemen array baru.
Tampilkan elemen baru pada Lembar 8
Sub LaporanContoh ()
Lembar ("Lembar8"). Pilih
Dim A () Sebagai Varian
Redim A (1 Sampai n1, 1 Sampai m)
VVOD "Lembar5", A, n1, m, 4
C = InputBox ("Masukkan kondisi")
Lembar ("Lembar8"). Sel (5.11) = C
Untuk i = 1 Sampai n1
Jika A (saya,
8) >
d = d + 1
Lembar ("Lembar8"). Sel (5,10) = d
Dim B () Sebagai Varian
Redim B (1 Sampai d, 1 Sampai m)
Untuk i = 1 Sampai n1
Jika A (saya,
8)> Sheets ("Sheet8"). Sel (5.11) Kemudian
Untuk j = 1 Ke m
B (u, j) = A (i, j)
u = u + 1
Untuk i = 1 Sampai d
Untuk j = 1 Ke m
Lembar ("Lembar8"). Sel (i + 4, j) = B (i, j)
Gambar 6. Data tabel setelah diambil
Membuat makro otomatis pada pilihan
Kami mengaktifkan perekaman makro. Alat> Makro> Mulai Merekam> OK. Sebuah kotak akan muncul di mana tombol berhenti merekam berada. Pada Sheet5 (laporan), pilih tabel tanpa header dan total, salin ke Sheet10 (pemilihan otomatis). Pilih tabel tanpa header dan pada item menu, pilih Data> Filter> AutoFilter> pilih kondisi> OK. Kami menandai kolom yang akan kami sortir. Kami menyelesaikan pekerjaan makro.
Sub Macro2Contoh ()
"Macro2Setup Makro
Lembar ("Lembar5"). Pilih
Pilihan. Salinan
Spreadsheet ("Lembar9"). Pilih
Lembar aktif. Tempel
Rentang ("H5: H17"). Pilih
Aplikasi. CutCopyMode = Salah
Pilihan. Filter Otomatis
Lembar aktif. Rentang ("$H$5:$H$17"). Bidang FilterOtomatis: = 1, Kriteria1: = "> 80", _
Operator: = xlAnd
Rentang ("G22"). Pilih
Gambar 7. Data tabel setelah pemilihan otomatis
Menentukan nilai maksimum dan minimum
Tentukan nilai maks dan min untuk kolom "Total", "Kinerja absolut, persen.", "Kinerja kualitatif" (tabel 9, lembar 10)
Untuk menentukan nilai maks dan min, Anda perlu melakukan hal berikut:
Tetapkan variabel referensi, yang akan menjadi minimum saat ini (maksimum)
Setiap elemen populasi dibandingkan satu per satu dengan minimum saat ini (maksimum), dan jika elemen ini tidak memenuhi kondisi pencarian (dalam kasus minimum, lebih besar, dan dalam kasus maksimum, kurang) , maka nilai referensi diganti dengan nilai elemen yang dibandingkan
Setelah pemindaian lengkap semua elemen dalam variabel minimum saat ini (maksimum), minimum aktual (maksimum) ditemukan
Nilai minimum (maksimum) ditampilkan di sel yang sesuai
Subminmaks ()
Dim A () Sebagai Varian
n1 = Lembar ("Lembar4"). Sel (5.12)
m = Lembar ("Lembar2"). Sel (5.12)
Redim A (1 Sampai n1, 1 Sampai m)
VVOD "Lembar5", A, n1, m, 4
VIVOD "Sheet10", A, n1, m, 4
VVOD "Sheet10", A, n1, m, 4
Untuk j = 3 Ke m
maksA = 0,00001
minA = 1.000.000
Untuk i = 1 Sampai n1
Jika A (i, j) > maxA Maka
maksA = A (i, j)
Jika A (i, j)< minA Then
minA = A (i, j)
Spreadsheet ("Sheet10"). Sel (i + 4 + 2, j) = maxA
Spreadsheet ("Sheet10"). Sel (i + 4 + 3, j) = minA
Makro adalah urutan tindakan yang telah direkam dan disimpan untuk referensi di masa mendatang. Makro yang disimpan dapat diputar ulang dengan perintah khusus. Dengan kata lain, Anda bisa merekam tindakan Anda di makro, menyimpannya, lalu mengizinkan pengguna lain untuk memutar ulang tindakan yang disimpan di makro dengan penekanan tombol sederhana. Ini sangat berguna saat mendistribusikan laporan PivotTable.
Katakanlah Anda ingin memberi pelanggan Anda kemampuan untuk mengelompokkan laporan PivotTable menurut bulan, kuartal, dan tahun. Secara teknis, proses pengelompokan dapat dilakukan oleh pengguna mana pun, tetapi beberapa klien Anda mungkin tidak perlu memahami hal ini. Dalam kasus seperti itu, Anda dapat merekam satu makro untuk pengelompokan menurut bulan, yang lain menurut kuartal, dan yang ketiga menurut tahun. Kemudian buat tiga tombol - satu untuk setiap makro. Kemudian pelanggan non-PivotTable Anda hanya perlu mengklik tombol untuk mengelompokkan laporan PivotTable dengan benar.
Manfaat utama menggunakan makro dalam laporan PivotTable adalah memungkinkan pelanggan untuk mengeksekusi dengan cepat di tabel ringkasan operasi yang biasanya tidak dapat mereka lakukan. Ini secara signifikan meningkatkan efisiensi analisis data yang diberikan.
Unduh catatan dalam format atau, unduh dengan contoh (di dalam berkas Excel dengan makro; kebijakan penyedia tidak mengizinkan secara langsung mengunggah file dengan format ini ke situs).
Perekaman makro
Perhatikan tabel pivot yang ditunjukkan pada Gambar. 1. Anda dapat menyegarkan tabel pivot ini dengan mengklik kanan di dalamnya dan memilih Menyegarkan... Jika Anda merekam tindakan sebagai makro saat memperbarui tabel pivot, maka Anda atau pengguna lain dapat memutar ulang tindakan ini dan memperbarui tabel pivot sebagai hasil menjalankan makro.
Ara. 1. Merekam tindakan selama pembaruan tabel pivot ini akan memungkinkan Anda untuk memperbarui data di masa mendatang sebagai hasil dari menjalankan makro
Langkah pertama dalam merekam makro adalah memanggil kotak dialog Perekaman makro... Pergi ke tab Pengembang pita dan klik tombol Perekaman makro... (Jika Anda tidak dapat menemukan tab di pita Pengembang, pilih tab Mengajukan, dan klik tombol Parameter... Pada kotak dialog yang muncul Opsi Excel Pilih Kategori Menyesuaikan Pita dan dalam daftar di sebelah kanan, centang kotak Pengembang... Akibatnya, sebuah tab akan muncul di pita Pengembang.) Cara alternatif mulai merekam makro - klik tombol (Gbr. 2).
Di kotak dialog Perekaman makro masukkan informasi berikut tentang makro (Gambar 3):
Namamakro... Nama harus menjelaskan tindakan yang dilakukan oleh makro. Nama harus dimulai dengan huruf atau garis bawah; tidak boleh mengandung spasi dan karakter tidak valid lainnya; tidak boleh sama dengan nama Excel bawaan atau nama objek lain di buku kerja.
Kombinasikunci... Anda dapat memasukkan huruf apa saja di bidang ini. Ini akan menjadi bagian dari pintasan keyboard yang akan digunakan untuk memainkan makro. Kombinasi tombol adalah opsional. Secara default, hanya Ctrl yang ditawarkan sebagai awal dari kombinasi. Jika Anda ingin kombinasi menyertakan Shift juga, ketik huruf di jendela sambil menahan tombol Shift
Menyimpandi... Di sinilah makro disimpan. Jika Anda akan mendistribusikan laporan PivotTable ke pengguna lain, pilih opsi IniBook... Excel juga memungkinkan Anda untuk menyimpan makro ke Buku baru atau di Buku makro pribadi.
Deskripsi... Deskripsi makro yang dibuat dimasukkan dalam bidang ini.
Ara. 3. Pengaturan jendela Perekaman makro
Karena makro memperbarui tabel pivot, pilih namanya Pembaruan Data... Anda juga dapat menetapkan makro ke kombinasi Tombol Ctrl+ Shift + Q. Ingatlah bahwa setelah membuat makro, Anda akan menggunakan pintasan keyboard ini untuk menjalankannya. Pilih opsi sebagai lokasi penyimpanan makro Buku ini dan klik baik.
Setelah mengklik dialog Perekaman makro pada tombol baik perekaman makro dimulai. Pada titik ini, semua tindakan yang Anda lakukan di Excel akan dicatat.
Klik kanan di area PivotTable dan pilih Menyegarkan(seperti pada Gambar 1, tetapi dalam mode perekaman makro). Setelah memperbarui tabel pivot, Anda dapat menghentikan proses perekaman makro menggunakan tombol Berhenti merekam tab Pengembang... Atau klik lagi pada tombol yang ditunjukkan pada gambar. 2.
Jadi Anda baru saja merekam makro pertama Anda. Sekarang Anda dapat menjalankan makro menggunakan pintasan keyboard Ctrl + Shift + Q.
Peringatan keamanan makro. Perlu dicatat bahwa jika makro direkam oleh pengguna, mereka akan dieksekusi tanpa batasan dari bagian subsistem keamanan. Namun, saat mendistribusikan buku kerja berisi makro, Anda harus memberikan kesempatan kepada pengguna lain untuk memastikan bahwa tidak ada risiko dalam membuka file yang berfungsi, dan eksekusi makro tidak akan menyebabkan infeksi virus pada sistem. Secara khusus, Anda akan segera melihat bahwa file sampel yang digunakan dalam bab ini tidak akan berfungsi sepenuhnya kecuali Anda secara khusus mengizinkan Excel untuk menjalankan makro di dalamnya.
Cara termudah untuk menjaga keamanan makro Anda adalah dengan membuat Lokasi Tepercaya — folder tempat hanya buku kerja bebas virus "Tepercaya" yang akan ditempatkan. Lokasi tepercaya memungkinkan Anda dan klien Anda untuk menjalankan makro di buku kerja tanpa batasan keamanan apa pun (perilaku ini tetap ada selama buku kerja berada di folder tepercaya).
Untuk menyiapkan lokasi tepercaya, ikuti langkah-langkah berikut.
Pilih tab pita Pengembang dan klik tombol Keamanan makro... Sebuah kotak dialog akan muncul di layar. Pusat Kepercayaan.
Klik pada tombol Tambahkan lokasi baru.
Klik pada tombol Gambaran untuk menentukan folder untuk file kerja yang Anda percayai.
Setelah Anda menentukan lokasi tepercaya, makro arbitrer akan berjalan secara default untuk semua buku kerja di dalamnya.
Model keamanan telah ditingkatkan di Excel 2013. File buku kerja yang sebelumnya "tepercaya" sekarang diingat; setelah dibuka buku kerja excel dan klik tombol Sertakan konten Excel mengingat keadaan ini. Akibatnya, buku ini termasuk dalam kategori buku tepercaya, dan pertanyaan yang tidak perlu tidak ditanyakan pada pembukaan berikutnya.
Membuat antarmuka pengguna menggunakan kontrol formulir
Menjalankan makro menggunakan kombinasi tombol Ctrl + Shift + Q akan membantu ketika hanya ada satu makro dalam laporan PivotTable. (Selain itu, pengguna perlu mengetahui kombinasi ini.) Namun, misalkan Anda ingin memberi klien Anda beberapa makro yang melakukan tindakan berbeda. Dalam hal ini, Anda perlu menyediakan pelanggan dengan pemahaman dan secara sederhana menjalankan setiap makro tanpa harus mengingat kombinasi tombol. Solusi sempurna sederhana antarmuka pengguna sebagai kumpulan kontrol seperti tombol, bilah gulir, dan alat lain yang memungkinkan Anda menjalankan makro dengan klik mouse.
Excel menawarkan seperangkat alat yang dirancang untuk membuat antarmuka pengguna langsung di spreadsheet. Alat-alat ini disebut kontrol formulir. Ide dasarnya adalah memungkinkan untuk memasukkan kontrol formulir lembar kerja dan menetapkan makro yang direkam sebelumnya. Setelah ditetapkan ke kontrol, makro akan berjalan dengan mengklik kontrol itu.
Kontrol formulir dapat ditemukan di grup Kontrol formulir tab pita Pengembang... Untuk membuka palet kontrol, klik di grup ini pada tombol Tempel(gbr. 4).
Ara. 4. Kontrol formulir Tombol
Harap dicatat: selain kontrol bentuk, palet juga mengandung Kontrol ActiveX... Meskipun mereka serupa, secara terprogram mereka adalah objek yang sama sekali berbeda. Kontrol formulir dengan disabilitas mereka dan pengaturan sederhana dirancang khusus untuk penempatan pada lembar kerja. Dalam waktu yang bersamaan Kontrol ActiveX digunakan terutama dalam bentuk kustom. Buat aturan untuk hanya menempatkan kontrol formulir di lembar kerja Anda.
Anda harus memilih kontrol yang paling sesuai dengan tugas yang ada. Dalam contoh ini, pelanggan harus dapat memperbarui PivotTable dengan mengklik tombol. Klik pada kontrol Tombol, pindahkan penunjuk tetikus ke posisi di lembar kerja yang Anda inginkan untuk meletakkan tombol, dan klik.
Setelah Anda menempatkan tombol di tabel, kotak dialog akan terbuka Tetapkan makro obyek(gbr. 5). Pilih makro yang diperlukan (dalam kasus kami - Pembaruan Data direkam sebelumnya) dan klik baik.
Ara. 5. Pilih makro yang akan ditetapkan ke tombol dan klik tombol baik... DI kasus ini makro harus diterapkan Pembaruan Data
Setelah menempatkan semua kontrol yang diperlukan dalam laporan PivotTable, Anda bisa memformat tabel untuk membuat antarmuka dasar. dalam gambar. 6 memperlihatkan laporan PivotTable setelah pemformatan.
Memodifikasi Makro yang Direkam
Sebagai hasil dari perekaman makro program excel membuat modul yang menyimpan tindakan yang Anda lakukan. Semua tindakan yang direkam diwakili oleh baris kode VBA yang membentuk makro. Anda dapat menambahkan berbagai jenis laporan tabel pivot ke Kegunaan dengan menyesuaikan kode VBA untuk mendapatkan hasil yang Anda inginkan. Untuk mempermudah memahami cara kerja semua ini, mari buat makro baru yang menampilkan lima catatan pelanggan pertama. Pergi ke tab Pengembang dan klik tombol Perekaman makro... Kotak dialog yang ditunjukkan pada Gambar. 7. Beri nama makro yang dibuat Pertamapelanggan dan tentukan lokasi penyimpanan Buku ini... Klik baik untuk mulai merekam makro.
Setelah Anda mulai merekam, klik panah di sebelah kotak Nama Pelanggan, Pilih Filter menurut nilai dan pilihan 10 pertama(Gbr.8a). Pada kotak dialog yang muncul, konfigurasikan pengaturan seperti yang ditunjukkan pada Gambar. 8b. Pengaturan ini memberitahu Anda untuk menampilkan data dari lima pelanggan teratas dalam hal penjualan. Klik baik.
Ara. 8. Pilih filter (a) dan sesuaikan parameter (b) untuk menampilkan lima pelanggan teratas berdasarkan penjualan
Setelah Anda berhasil mencatat semua langkah yang diperlukan untuk mengambil 5 prospek penjualan teratas, buka tab Pengembang dan klik tombol Berhenti merekam.
Anda sekarang memiliki makro yang akan memfilter tabel pivot untuk mengambil 5 pelanggan penjualan teratas. Hal ini diperlukan untuk membuat makro bereaksi terhadap keadaan bilah gulir, mis. menggunakan bilah gulir, Anda harus dapat menunjukkan kepada makro jumlah pelanggan yang datanya akan ditampilkan dalam laporan tabel pivot. Jadi, dengan menggunakan bilah gulir, pengguna akan dapat mengambil lima besar, delapan teratas, atau tiga puluh dua klien terbaik sesuai keinginan mereka.
Untuk menambahkan bilah gulir ke spreadsheet Anda, buka tab Pengembang, klik tombol Tempel, pilih kontrol pada palet Scroll bar dan letakkan di lembar kerja. Klik kanan pada kontrol Scroll bar Format objek... Sebuah kotak dialog akan terbuka Format kontrol(gbr. 9). Di dalamnya, buat perubahan berikut pada pengaturan: Nilai minimum tetapkan nilai 1, parameter Nilai maksimum - nilai 200, dan di lapangan komunikasi sel masukkan nilai $M $2 untuk menampilkan nilai scrollbar di sel M2. Klik pada tombol baik untuk menerapkan pengaturan yang ditentukan sebelumnya.
Sekarang Anda harus mencocokkan makro yang baru direkam recorded Pertamapelanggan dengan kontrol Scroll bar pada lembar kerja. Klik kanan pada kontrol Scroll bar dan masuk menu konteks pilih tim Tetapkan makro untuk membuka kotak dialog Tugas Makro. Tetapkan makro yang direkam ke bilah gulir Pelanggan FirstN... Makro akan berjalan setiap kali bilah gulir diklik. Uji bilah gulir yang dibuat. Setelah mengklik strip, makro akan berjalan Pelanggan FirstN dan nomor di sel M2 akan berubah untuk menunjukkan status bilah gulir. Nomor di sel M2 penting karena digunakan untuk mengikat makro ke bilah gulir.
Satu-satunya yang tersisa untuk dilakukan adalah membuat makro memproses angka di sel M2 dengan mengaitkannya dengan bilah gulir. Untuk melakukan ini, Anda harus pergi ke kode VBA makro. Untuk melakukan ini, buka tab Pengembang dan klik tombol makro... Sebuah kotak dialog akan terbuka Makro(gbr. 10). Di dalamnya, Anda dapat menjalankan, menghapus, dan mengedit makro yang dipilih. Untuk menampilkan kode VBA makro di layar, pilih makro dan klik tombol Sunting.
Ara. 10. Untuk mengakses kode makro VBA Pertamapelanggan, pilih makro dan klik tombol Sunting
Jendela editor akan muncul di layar Dasar visual dengan kode VBA makro (gbr. 11). Tujuan Anda adalah mengganti nomor 5 hard-coded, yang disetel saat Anda merekam makro, dengan nilai di sel M2 yang terikat pada bilah gulir. Awalnya, makro tercatat untuk menampilkan lima pelanggan teratas dengan pendapatan tertinggi.
Hapus nomor 5 dari kode dan masukkan ekspresi berikut sebagai gantinya:
ActiveSheet.Range ("M2") .Nilai
Tambahkan dua baris ke awal makro untuk menghapus filter:
Rentang ("A4") .Pilih
ActiveSheet.PivotTables ("PivotTable1") .PivotFields ("Nama Pelanggan") .ClearAllFilters
Kode makro sekarang akan terlihat seperti yang ditunjukkan pada Gambar. 12.
Tutup Editor Visual Basic dan kembali ke laporan PivotTable. Uji bilah gulir dengan menyeret penggeser ke 11. Makro harus menjalankan dan memfilter 11 catatan tentang klien terbaik oleh penjualan.
Sinkronkan dua tabel pivot dengan satu tarik-turun
Laporan yang ditunjukkan pada Gambar. 13 berisi dua tabel pivot. Masing-masing memiliki bidang halaman yang memungkinkan Anda memilih area penjualan. Masalahnya adalah setiap kali Anda memilih pasar di bidang halaman satu tabel pivot, Anda harus memilih pasar yang sama di bidang halaman tabel pivot lain. Menyinkronkan filter antara dua tabel selama fase analisis data bukanlah masalah besar, tetapi ada kemungkinan Anda atau pelanggan Anda masih lupa melakukannya.
Ara. 13. Dua tabel pivot berisi bidang halaman yang menyaring data berdasarkan pasar. Untuk menganalisis data dari satu pasar, Anda perlu menyinkronkan kedua tabel pivot
Salah satu cara untuk menjaga agar tabel pivot ini tetap sinkron adalah dengan menggunakan daftar dropdown. Idenya adalah untuk merekam makro yang memilih pasar yang diinginkan dari lapangan Pasar penjualan di kedua tabel. Kemudian Anda perlu membuat daftar drop-down dan mengisinya dengan nama-nama pasar penjualan dari dua tabel pivot. Terakhir, makro yang direkam perlu dimodifikasi untuk memfilter kedua PivotTable menggunakan nilai dari daftar turun bawah. Untuk mengatasi masalah ini, Anda perlu melakukan langkah-langkah berikut.
1. Buat makro baru dan beri nama Pasar Sinkron... Saat perekaman dimulai, pilih di bidang Pasar untuk keduanya tabel pivot penjualan California dan berhenti merekam makro.
2. Tampilkan palet kontrol formulir dan tambahkan daftar drop-down ke lembar kerja.
3. Buat daftar hard-code dari semua pasar PivotTable. Perhatikan bahwa item pertama dalam daftar adalah (Semua). Anda harus mengaktifkan elemen ini jika Anda ingin dapat memilih semua pasar dari daftar drop-down.
4. Pada titik ini, laporan PivotTable akan terlihat seperti yang ditunjukkan pada Gambar. empat belas.
Ara. 14. Anda memiliki semua alat yang Anda butuhkan: makro yang mengubah bidang Pasar penjualan baik tabel pivot, daftar dropdown dan daftar semua pasar penjualan yang terdapat dalam tabel pivot
5. Klik kanan pada daftar drop-down dan pilih perintah Format objek untuk menyesuaikan kontrol.
6. Pertama, atur rentang nilai awal yang digunakan untuk mengisi daftar drop-down, seperti yang ditunjukkan pada Gambar 6-7. 15. Dalam hal ini, kita berbicara tentang daftar pasar penjualan yang Anda buat pada langkah 3. Kemudian tentukan sel yang menampilkan nomor seri elemen yang dipilih (dalam contoh ini, ini adalah sel H1). Parameter Jumlah baris daftar menentukan berapa banyak baris yang akan ditampilkan dalam daftar drop-down pada saat yang bersamaan. Klik pada tombol baik.
Ara. 15. Pengaturan daftar drop-down harus mengarah ke daftar pasar penjualan sebagai rentang nilai awal, dan menentukan sel H1 sebagai titik jangkar
7. Sekarang Anda memiliki kesempatan untuk memilih pasar penjualan dari daftar drop-down, serta menentukan nomor seri terkait di sel H1 (Gbr. 16). Timbul pertanyaan: mengapa nilai indeks yang digunakan bukan nama pasar yang sebenarnya? Karena dropdown mengembalikan bukan nama, tetapi angka. Misalnya, jika Anda memilih California dari daftar drop-down, nilai 5 muncul di sel H1, yang berarti California adalah item kelima dalam daftar.
Ara. 16. Daftar drop-down sekarang diisi dengan nama-nama pasar, dan nomor seri pasar yang dipilih ditampilkan di sel H1
8. Untuk menggunakan nomor urut alih-alih nama pasar, Anda harus meneruskannya menggunakan fungsi INDEX.
9. Masukkan fungsi INDEX, yang mengubah nomor seri dari sel H1 menjadi nilai yang berarti.
10. Fungsi INDEX membutuhkan dua argumen. Argumen pertama mewakili rentang nilai dalam daftar. Dalam kebanyakan kasus, Anda akan menggunakan rentang yang sama yang mengisi menu tarik-turun. Argumen kedua adalah nomor urut. Jika nomor seri dimasukkan ke dalam sel (misalnya, di sel H1, seperti pada Gambar 17), maka Anda cukup merujuk ke sel ini.
Ara. 17. Fungsi INDEX di sel I1 mengubah nomor urut yang disimpan di sel H1 menjadi nilai. Anda akan menggunakan nilai di sel I1 untuk mengubah makro
11. Edit makro Pasar Sinkron menggunakan nilai di sel I1 alih-alih nilai hard-coded. Pergi ke tab Pengembang dan klik tombol makro... Sebuah kotak dialog akan muncul di layar, seperti yang ditunjukkan pada Gambar. 18. Pilih makro di dalamnya Pasar Sinkron dan klik tombol Ubah.
Ara. 18. Untuk mengakses kode VBA makro, pilih makro Pasar Sinkron dan klik Sunting
12. Saat merekam makro, Anda memilih area penjualan California dari bidang di kedua tabel pivot. Pasar penjualan... Seperti yang dapat dilihat dari Gambar. 19, pasar California sekarang di-hardcode dalam kode makro VBA.
13. Ganti California dengan Activesheet.Range ("I1") .Value, yang mengacu pada nilai di sel I1. Pada titik ini, kode makro akan terlihat seperti yang ditunjukkan pada Gambar. 20. Setelah memodifikasi makro, tutup Editor Visual Basic dan kembali ke spreadsheet.
Ara. 20. Ganti "California" dengan ActiveSheet.Range ("I1") .Value dan tutup Editor Visual Basic
14. Tetap hanya memastikan eksekusi makro saat memilih area penjualan di daftar drop-down. Klik kanan pada dropdown dan pilih opsi Tetapkan makro... Pilih makro Pasar Sinkron dan klik tombol baik.
15. Sembunyikan baris dan kolom dengan bidang halaman di tabel pivot dan daftar pasar dan formula indeks yang Anda buat.
dalam gambar. 21 menunjukkan hasil akhir. Anda sekarang memiliki antarmuka pengguna yang memungkinkan pelanggan untuk memilih area penjualan mereka di kedua tabel pivot menggunakan satu daftar drop-down.
Saat Anda memilih item baru dari daftar drop-down, kolom secara otomatis diubah ukurannya untuk mengakomodasi semua data yang ditampilkan di dalamnya. Perilaku program semacam ini cukup membosankan saat memformat templat lembar kerja. Anda dapat mencegahnya dengan mengklik kanan pada PivotTable dan memilih Opsi tabel pivot... Kotak dialog dengan nama yang sama akan muncul di layar, di mana Anda perlu mengosongkan kotak centang Secara otomatis mengubah ukuran kolom pada pembaruan.
Catatan berdasarkan buku Jelen, Alexander. ... Bab 12.
Menggunakan alat Excel, Anda dapat memilih data tertentu dari rentang dalam urutan acak, satu kondisi atau beberapa. Untuk mengatasi masalah seperti itu, sebagai aturan, rumus array atau makro digunakan. Mari kita lihat beberapa contoh.
Cara membuat seleksi di Excel dengan kondisi
Saat menggunakan rumus array, data yang dipilih ditampilkan dalam tabel terpisah. Apa keuntungannya? metode ini dibandingkan dengan filter konvensional.
Tabel sumber:
Pertama, mari kita pelajari cara membuat pilihan berdasarkan kriteria numerik tunggal. Tugasnya adalah memilih produk dengan harga di atas 200 rubel dari tabel. Salah satu solusinya adalah dengan menerapkan filtering. Akibatnya, hanya produk yang memenuhi permintaan yang akan tetap berada di tabel asli.
Solusi lain adalah dengan menggunakan rumus array. Baris yang sesuai dengan permintaan akan masuk ke dalam laporan tabel terpisah.
Pertama, kami membuat tabel kosong di sebelah yang asli: header duplikat, jumlah baris dan kolom. Tabel baru menempati rentang E1: G10. Sekarang pilih E2: E10 (kolom "Tanggal") dan masukkan rumus berikut: ( }.
Untuk mendapatkan rumus array, tekan kombinasi tombol Ctrl + Shift + Enter. Di kolom berikutnya - "Produk" - kami memasukkan rumus array yang serupa: ( ). Hanya argumen pertama dari fungsi INDEX yang berubah.
Di kolom "Harga", masukkan rumus larik yang sama, yang mengubah argumen pertama dari fungsi INDEX.
Akibatnya, kami mendapatkan laporan barang dengan harga lebih dari 200 rubel.
Pilihan seperti itu bersifat dinamis: ketika kueri berubah atau produk baru muncul di tabel sumber, laporan akan berubah secara otomatis.
Tugas nomor 2 - pilih dari tabel asli barang yang mulai dijual pada 20/09/2015. Artinya, kriteria pemilihan adalah tanggal. Untuk kenyamanan, kami akan memasukkan tanggal yang diinginkan di sel terpisah, I2.
Rumus array serupa digunakan untuk menyelesaikan masalah. Hanya bukan kriteria).
Rumus serupa dimasukkan di kolom lain (lihat prinsip di atas).
Sekarang kita menggunakan kriteria teks. Alih-alih tanggal di sel I2, masukkan teks "Produk 1". Mari kita ubah sedikit rumus array :( }.
Fungsi seleksi yang bagus di Excel.
Seleksi dengan beberapa kondisi di Excel
Pertama, mari kita ambil dua kriteria numerik:
Tugasnya adalah memilih barang yang harganya kurang dari 400 dan lebih dari 200 rubel. Mari kita gabungkan kondisi dengan tanda "*". Rumus array terlihat seperti ini: ( }.!}
Ini untuk kolom pertama dari tabel laporan. Untuk yang kedua dan ketiga - kami mengubah argumen pertama dari fungsi INDEX. Hasil:
Untuk membuat seleksi berdasarkan beberapa tanggal atau kriteria numerik, kami menggunakan rumus array yang serupa.
Pengambilan sampel acak di Excel
Saat pengguna bekerja dengan jumlah besar data mungkin memerlukan pengambilan sampel acak untuk analisis selanjutnya. Anda dapat menetapkan nomor acak untuk setiap baris dan kemudian menerapkan pengurutan ke pilihan.
kumpulan data asli:
Pertama, mari kita masukkan dua kolom kosong di sebelah kiri. Di sel A2, tulis rumus RAND(). Mari kita kalikan dengan seluruh kolom:
Sekarang kita salin kolom dengan angka acak dan tempel ke kolom B. Ini diperlukan agar angka-angka ini tidak berubah ketika data baru dimasukkan ke dalam dokumen.
Untuk menyisipkan nilai, bukan rumus, klik kanan pada kolom B dan pilih alat Tempel Spesial. Di jendela yang terbuka, beri tanda centang di depan item "Nilai":
Anda sekarang dapat mengurutkan data di kolom B dalam urutan menaik atau menurun. Urutan di mana nilai asli disajikan juga akan berubah. Kami memilih sejumlah garis dari atas atau bawah - kami akan mendapatkan sampel acak.
Lain-lain (39)
Bug dan gangguan Excel (3)
Bagaimana cara mendapatkan daftar nilai unik (non-duplikat)?
Bayangkan daftar besar nama yang berbeda, nama lengkap, nomor personel, dll. Dan perlu untuk meninggalkan daftar semua nama yang sama dari daftar ini, tetapi agar mereka tidak mengulangi diri mereka sendiri - mis. hapus semua entri duplikat dari daftar ini. Seperti yang disebut: buat daftar elemen unik, daftar yang tidak berulang, tidak ada duplikat. Ada beberapa cara untuk melakukan ini: alat Excel bawaan, rumus bawaan dan, akhirnya, menggunakan kode Visual Basic untuk Aplikasi (VBA) dan tabel pivot. Artikel ini akan membahas setiap opsi.
Menggunakan fitur bawaan di Excel 2007 dan di atasnya
Di Excel 2007 dan 2010 semudah mengupas buah pir - ada - tim khusus, yang disebut begitu -. Itu terletak di tab Data ayat Alat data
Cara menggunakan perintah ini. Sorot kolom (atau beberapa) dengan data di mana catatan duplikat perlu dihapus. Pergi ke tab Data -Hapus Duplikat.
Jika Anda memilih satu kolom, tetapi di sebelahnya akan ada lebih banyak kolom dengan data (atau setidaknya satu kolom), maka Excel akan menawarkan untuk memilih: perluas rentang pilihan dengan kolom ini atau biarkan pilihan apa adanya dan hapus data saja dalam rentang yang dipilih. Penting untuk diingat bahwa jika Anda tidak memperluas jangkauan, maka data akan diubah hanya dalam satu kolom, dan data di kolom yang berdekatan akan tetap tidak berubah.
Sebuah jendela dengan opsi untuk menghapus duplikat akan muncul 
Centang kotak di depan kolom tersebut, duplikat yang ingin Anda hapus dan klik OK. Jika tajuk data juga terletak di kisaran yang dipilih, maka lebih baik untuk mengatur bendera Data saya berisi header untuk menghindari penghapusan data dalam tabel secara tidak sengaja (jika mereka tiba-tiba benar-benar bertepatan dengan nilai dalam judul).
Metode 1: Filter lanjutan
Dalam kasus Excel 2003, segalanya lebih rumit. Tidak ada alat seperti Hapus duplikat... Tetapi di sisi lain, ada alat yang luar biasa seperti Filter tingkat lanjut... Pada tahun 2003 alat ini dapat ditemukan di Data -Saring -Filter tingkat lanjut... Keindahan metode ini adalah Anda dapat menggunakannya untuk membuat daftar dalam rentang yang berbeda daripada merusak data aslinya. Di Excel 2007-2010, itu juga ada, tetapi sedikit tersembunyi. Terletak di tab Data, Grup Sortir & Filter - Maju
Cara menggunakannya: jalankan alat yang ditentukan - kotak dialog muncul: 
- Pengobatan: Kami memilih Salin hasilnya ke lokasi lain.
- Kisaran daftar: Memilih rentang dengan data (dalam kasus kami ini adalah A1: A51).
- Rentang kriteria: dalam hal ini, biarkan kosong.
- Salin ke rentang: kami menunjukkan sel pertama untuk menampilkan data - kosong apa pun (dalam gambar - E2).
- Centang kotak Hanya catatan unik.
- Kami menekan baik.
catatan: jika Anda ingin menempatkan hasilnya pada lembar lain, maka Anda tidak dapat menentukan lembar lain. Anda akan dapat menentukan sel pada lembar lain, tetapi ... Sayangnya dan ah ... Excel akan memberikan pesan bahwa itu tidak dapat menyalin data ke lembar lain. Tapi ini juga bisa dielakkan, dan cukup sederhana. Anda hanya perlu lari Filter tingkat lanjut dari lembar di mana kita ingin menempatkan hasilnya. Dan sebagai data awal, kami memilih data dari lembar apa pun - ini diperbolehkan.
Anda juga tidak dapat mentransfer hasilnya ke sel lain, tetapi memfilter data di tempat. Data tidak akan terpengaruh dengan cara apa pun - ini akan menjadi pemfilteran data biasa.
Untuk melakukan ini, Anda hanya perlu memilih di bagian Pengolahan Filter daftar, di tempat.
Metode 2: Rumus
Metode ini lebih sulit dipahami oleh pengguna yang tidak berpengalaman, tetapi membuat daftar nilai unik tanpa mengubah data asli. Nah, ini juga lebih dinamis: jika Anda mengubah data di tabel sumber, hasilnya juga akan berubah. Ini terkadang berguna. Saya akan mencoba menjelaskan dengan jari saya apa dan apa: misalnya, Anda memiliki daftar dengan data di kolom A (A1: A51, di mana A1 adalah heading). Kami akan menampilkan daftar di kolom C, mulai dari sel C2. Rumus dalam C2 akan menjadi sebagai berikut:
(= INDEX ($ A $ 2: $ A $ 51; KECIL (JIKA (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; ROW ($ A $ 1: $ A $ 50)); 1)) )
(= INDEX ($ A $ 2: $ A $ 51; KECIL (JIKA (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; ROW ($ A $ 1: $ A $ 50)); 1)) )
Analisis terperinci tentang pengoperasian formula ini diberikan dalam artikel:
Perlu dicatat bahwa rumus ini adalah rumus array. Ini bisa dikatakan kawat gigi, di mana rumus ini terlampir. Dan formula seperti itu dimasukkan ke dalam sel dengan pintasan keyboard - Ctrl+Bergeser+Memasukkan... Setelah kita memasukkan rumus ini di C2, kita harus menyalin dan menempelkannya dalam beberapa baris sehingga semua elemen unik ditampilkan secara akurat. Segera setelah formula di sel bawah kembali #JUMLAH!- ini berarti semua elemen ditampilkan dan tidak ada gunanya meregangkan rumus di bawah ini. Untuk menghindari kesalahan dan membuat rumus lebih universal (tanpa meregangkan setiap kali hingga kesalahan muncul), Anda dapat menggunakan pemeriksaan sederhana:
untuk Excel 2007 dan yang lebih baru:
(= IFERROR (INDEX ($ A $ 2: $ A $ 51; KECIL (JIKA (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; STRING ($ A $ 1: $) A $50)); 1 )); ""))
(= IFERROR (INDEX ($ A $ 2: $ A $ 51; KECIL (JIKA (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; ROW ($ A $ 1: $) A $50)); 1 )); ""))
untuk Excel 2003:
(= JIKA (ISH (KECIL (JIKA (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; ROW ($ A $ 1: $ A $ 50)); 1)); ""; INDEX( $A$2:$A$51; KECIL (JIKA(COUNTIF($C$1:C1; $A$2:$A$51) = 0; ROW ($A$1:$A $50)); 1))))
(= JIKA (ISERR (KECIL (JIKA (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; ROW ($ A $ 1: $ A $ 50)); 1)); ""; INDEX( $A$2:$A$51; KECIL (JIKA(COUNTIF($C$1:C1; $A$2:$A$51) = 0; ROW ($A$1:$A $50)); 1))))
Kemudian alih-alih kesalahan #JUMLAH! (#NUM!) Anda akan memiliki sel kosong(tidak sepenuhnya kosong, tentu saja - dengan rumus :-)).
Sedikit lebih detail tentang perbedaan dan nuansa rumus ESLIOSHIBKA dan IF (EOSH bisa dibaca di artikel ini: Cara menampilkan 0 di sel dengan rumus bukan error
Metode 3: Kode VBA
Pendekatan ini akan membutuhkan resolusi makro dan pengetahuan dasar untuk bekerja dengannya. Jika Anda tidak yakin dengan pengetahuan Anda, saya sarankan membaca artikel ini terlebih dahulu:
- Apa itu makro dan di mana saya bisa menemukannya? video tutorial terlampir pada artikel
- Apa itu modul? Modul apa saja yang ada? akan diminta untuk mencari tahu di mana memasukkan kode di bawah ini
Kedua kode di bawah ini harus ditempatkan di modul standar... Makro harus diizinkan.
Biarkan data awal dalam urutan yang sama - daftar dengan data terletak di kolom "A" (A1: A51, di mana A1 adalah judulnya)... Hanya kita yang akan menampilkan daftar bukan di kolom C, tapi di kolom E, mulai dari sel E2:
Sub Extract_Unique () Dim vItem, avArr, li As Long ReDim avArr (1 To Rows.Count, 1 To 1) Dengan Koleksi Baru Pada Kesalahan Lanjutkan Berikutnya Untuk Setiap vItem Dalam Rentang ("A2", Sel (Rows.Count, 1) .End (xlUp)). Nilai "(! LANG: Sel (Rows.Count, 1) .End (xlUp) - mendefinisikan sel yang diisi terakhir di kolom A. Tambahkan vItem, CStr (vItem) If Err = 0 Kemudian li = li + 1: avArr (li, 1) = vItem Lain: Err.Clear End If Next End With If li Then .Resize (li) .Value = avArr End Sub
Dengan kode ini, Anda dapat mengekstrak nilai unik tidak hanya dari satu kolom, tetapi dari berbagai kolom dan baris. Jika alih-alih garis
Jarak ("A2", Cells(Rows.Count, 1).End(xlUp)).Value !}
tentukan Selection.Value, maka hasil kode akan menjadi daftar elemen unik dari rentang yang dipilih pada lembar aktif. Hanya dengan begitu akan lebih baik untuk mengubah sel keluaran nilai - alih-alih
menempatkan satu di mana tidak ada data.
Anda juga dapat menentukan rentang tertentu:
Rentang ("C2", Sel (Rows.Count, 3). End (xlUp)).Nilai
Kode universal untuk memilih nilai unik
Kode di bawah ini dapat diterapkan ke rentang apa pun. Cukup untuk menjalankannya, tentukan rentang dengan nilai untuk memilih hanya yang tidak berulang (lebih dari satu kolom diperbolehkan) dan sel untuk menampilkan hasilnya. Sel-sel yang ditentukan akan dipindai, yang hanya nilai unik(sel kosong dilewati) dan daftar yang dihasilkan akan ditulis mulai dari sel yang ditentukan.
| Sub Extract_Unique () Dim x, avArr, li As Long Dim avVals Dim rVals As Range, rResultCell As Range On Error Resume Next "minta alamat sel untuk memilih nilai unik Atur rVals = Application.InputBox ( "Tentukan rentang sel untuk mengambil sampel nilai unik", "Permintaan Data", "A2: A51", Ketik: = 8) Jika rVals Tidak Ada Maka "jika tombol Batal diklik Keluar Sub Akhir Jika "Jika hanya satu sel yang ditentukan, tidak ada gunanya memilih Jika rVals.Count = 1 Maka MsgBox "Untuk memfilter nilai unik, Anda perlu menentukan lebih dari satu sel", vbInformation, "www.site" Keluar Sub Akhir Jika "potong baris dan kolom kosong di luar jangkauan kerja Atur rVals = Intersect (rVals, rVals.Parent.UsedRange) "jika hanya sel kosong di luar rentang kerja yang ditentukan Jika rVals Bukan Apa-apa Maka MsgBox "Data tidak cukup untuk memilih nilai", vbInformation, "www.site" Keluar Sub Akhir Jika avVals = rVals.Value "meminta sel untuk menampilkan hasilnya Atur rResultCell = Application.InputBox ( "Tentukan sel untuk memasukkan nilai unik yang dipilih", "Permintaan Data", "E2", Ketik: = 8) Jika rResultCell Bukan Apa-apa Kemudian "jika tombol Batal diklik Keluar Sub Akhir Jika "tentukan dimensi maksimum yang mungkin dari array untuk hasilnya ReDim avArr (1 To Rows.Count, 1 To 1) "menggunakan objek Koleksi "pilih hanya catatan unik, "karena Koleksi tidak dapat berisi nilai duplikat Dengan Koleksi Baru Pada Kesalahan Lanjutkan Berikutnya Untuk Setiap x Dalam avVals Jika Len (CStr (x)) Kemudian "lewati sel kosong.Tambahkan x, CStr (x) "jika elemen yang ditambahkan sudah ada di Koleksi, kesalahan akan terjadi "jika tidak ada kesalahan, nilai ini belum dimasukkan, "tambahkan ke array yang dihasilkan Jika Err = 0 Maka li = li + 1 avArr (li, 1) = x Lain-lain "pastikan untuk menghapus objek Kesalahan Err.Hapus Akhir Jika Berakhir Jika Berikutnya Berakhir Dengan "tulis hasilnya ke lembar, mulai dari sel yang ditentukan If li Kemudian rResultCell.Cells (1, 1) .Resize (li) .Value = avArr End Sub |
Sub Extract_Unique() Dim x, avArr, li As Long Dim avVals Dim rVals As Range, rResultCell As Range On Error Resume Next "minta alamat sel untuk memilih nilai unik Set rVals = Application.InputBox (" Tentukan kisaran sel untuk memilih nilai unik " , "Permintaan data", "A2: A51", Ketik: = 8) If rVals Is Nothing Kemudian "jika tombol Batal diklik Keluar Sub End If" jika hanya satu sel yang ditentukan , tidak ada gunanya memilih If rVals.Count = 1 Then MsgBox " Untuk memilih nilai unik, Anda perlu menentukan lebih dari satu sel ", vbInformation," www.site "Exit Sub End If" kami memotong baris dan kolom kosong di luar rentang kerja Set rVals = Intersect (rVals, rVals.Parent.UsedRange) "jika hanya sel kosong yang ditentukan di luar rentang kerja If rVals Is Nothing Then MsgBox "Data tidak cukup untuk memilih nilai", vbInformation, "www..Value " (! LANG: meminta sel untuk menampilkan hasil Set rResultCell = Application.InputBox ("Укажите ячейку для вставки отобранных уникальных значений", "Запрос данных", "E2", Type:=8) If rResultCell Is Nothing Then "если нажата кнопка Отмена Exit Sub End If "определяем максимально возможную размерность массива для результата ReDim avArr(1 To Rows.Count, 1 To 1) "при помощи объекта Коллекции(Collection) "отбираем только уникальные записи, "т.к. Коллекции не могут содержать повторяющиеся значения With New Collection On Error Resume Next For Each x In avVals If Len(CStr(x)) Then "пропускаем пустые ячейки.Add x, CStr(x) "если добавляемый элемент уже есть в Коллекции - возникнет ошибка "если же ошибки нет - такое значение еще не внесено, "добавляем в результирующий массив If Err = 0 Then li = li + 1 avArr(li, 1) = x Else "обязательно очищаем объект Ошибки Err.Clear End If End If Next End With "записываем результат на лист, начиная с указанной ячейки If li Then rResultCell.Cells(1, 1).Resize(li).Value = avArr End Sub!}
Metode 4: tabel pivot
Beberapa cara non-standar mengambil nilai-nilai unik.
- Pilih satu atau lebih kolom dalam tabel, buka tab Memasukkan-Kelompok Meja -Tabel pivot
- Di kotak dialog Buat PivotTable periksa kebenaran pemilihan rentang data (atau sumber baru data)
- menunjukkan lokasi PivotTable:
- Lembar Kerja Baru
- Lembar kerja yang ada
- konfirmasi pembuatan dengan menekan tombol baik
Karena tabel pivot, saat memproses data yang ditempatkan di area baris atau kolom, pilih hanya nilai unik darinya untuk analisis lebih lanjut, maka sama sekali tidak ada yang diperlukan dari kami kecuali membuat tabel pivot dan menempatkan data kolom yang diinginkan di area baris atau kolom.
Menggunakan file yang dilampirkan pada artikel sebagai contoh, saya:
Apa ketidaknyamanan bekerja dengan tabel pivot dalam kasus ini: ketika data sumber berubah, tabel pivot harus diperbarui secara manual: Pilih sel mana pun dari tabel pivot -Tombol kanan mouse - Menyegarkan atau tab Data -Segarkan semua -Menyegarkan... Dan jika sumber data diisi ulang secara dinamis dan bahkan lebih buruk, perlu untuk menentukan ulang rentang sumber data. Dan satu lagi kelemahannya - data di dalam tabel pivot tidak dapat diubah. Karena itu, jika perlu bekerja dengan daftar yang dihasilkan di masa mendatang, maka setelah membuat daftar yang diinginkan menggunakan ringkasan, itu harus disalin dan ditempelkan pada lembar yang diinginkan.
Untuk lebih memahami semua langkah dan mempelajari cara menggunakan tabel pivot, saya sangat menyarankan Anda membaca artikel Informasi umum tentang tabel pivot - tutorial video terlampir, di mana saya dengan jelas menunjukkan kesederhanaan dan kenyamanan bekerja dengan dasar fitur tabel pivot.
Dalam contoh terlampir, selain teknik yang dijelaskan, variasi yang sedikit lebih kompleks dari mengekstraksi elemen unik dengan rumus dan kode ditulis, yaitu: ekstraksi elemen unik berdasarkan kriteria... Apa yang kita bicarakan: jika dalam satu kolom nama belakang, dan di kedua (B) ada beberapa data (dalam file ini adalah bulan) dan Anda ingin mengekstrak nilai unik kolom B hanya untuk nama belakang yang dipilih. Contoh ekstraksi unik seperti itu ada di lembar Ekstraksi berdasarkan kriteria.
Unduh contoh:
(108.0 KiB, 14.152 unduhan)
Apakah artikel itu membantu? Bagikan tautan dengan teman-teman Anda! Pelajaran video
