Pemilihan data makro Excel berdasarkan kondisi. Buat makro otomatis pada pilihan

Pengambilan sampel data

Buat laporan tentang sampel dari Sheet5 di kolom "Kinerja akademik kualitatif, persen." (dari Lembar 8, Tabel 7)

Untuk mengambil data, lakukan hal berikut:

Tentukan jumlah elemen array baru dengan kondisi tertentu dengan memasukkan variabel menggunakan operator InputBox

Deklarasikan dan deklarasikan ulang array baru

Membentuk larik baru. Untuk melakukan ini, Anda perlu menyetel nomor elemen pertama dari larik baru u=1. Kemudian sebuah loop dieksekusi di mana kondisi seleksi ditulis untuk kolom "Kinerja akademik kualitatif, persen". Jika hasil pengujian benar, maka elemen dari array yang diurai menjadi elemen dari array baru.

Tampilkan elemen baru di Lembar 8

Pilihan Sub Laporan()

Lembar ("Sheet8"). Pilih

Dim A () Sebagai Varian

ReDim A (1 Ke n1, 1 Ke m)

VVOD "Sheet5", A, n1, m, 4

C = InputBox("Masukkan kondisi")

Lembar ("Sheet8"). Sel(5,11) = C

Untuk i = 1 Ke n1

Jika A(i,

8) >

d=d+1

Lembar ("Sheet8"). Sel(5,10) = d

Dim B() Sebagai Varian

ReDim B (1 Ke d, 1 Ke m)

Untuk i = 1 Ke n1

Jika A(i,

8) > Lembar ("Sheet8"). Sel (5,11) Lalu

Untuk j = 1 Ke m

B(u, j) = A(i, j)

kamu = kamu + 1

Untuk i = 1 Sampai d

Untuk j = 1 Ke m

Lembar ("Sheet8"). Sel(i + 4, j) = B(i, j)

Gbr.6. Tabel data setelah seleksi

Buat makro otomatis pada pilihan

Aktifkan perekaman makro. Alat>Makro>Mulai Perekaman>OK. Kotak akan muncul di tempat tombol berhenti merekam berada. Di Sheet5 (laporan), pilih tabel tanpa header dan total, salin ke Sheet10 (pemilihan otomatis). Pilih tabel tanpa header dan di item menu, pilih Data>Filter>AutoFilter>pilih kondisi>OK. Kami menandai kolom yang akan kami sortir. Kami menyelesaikan makro.

Sub Makro2Pilih()

" Makro2Seleksi Makro

Lembar ("Lembar5"). Pilih

pilihan. Menyalin

Lembar("Lembar9"). Pilih

Lembar aktif. tempel

Rentang("H5:H17"). Pilih

aplikasi. CutCopyMode=Salah

pilihan. Filter Otomatis

Lembar aktif. Range("$H$5: $H$17"). Bidang AutoFilter: =1, Kriteria1: =">80", _


Operator:=xlDan

Rentang ("G22"). Pilih

Gbr.7. Data tabel setelah pemilihan otomatis


Menentukan nilai maksimum dan minimum

Tentukan nilai maks dan min untuk kolom "Total", "Kemajuan absolut, persen", "Kemajuan kualitatif" (tabel 9, lembar 10)

Untuk menentukan nilai maks dan min, lakukan hal berikut:

Tetapkan variabel referensi yang akan menjadi minimum saat ini (maksimum)

Setiap elemen populasi dibandingkan secara bergiliran dengan minimum saat ini (maksimum), dan jika elemen ini tidak memenuhi kondisi pencarian (dalam kasus minimum lebih besar, dan dalam kasus maksimum kurang), kemudian nilai standar diganti dengan nilai elemen yang dibandingkan

Setelah tampilan penuh dari semua elemen dalam variabel minimum (maksimum) saat ini adalah minimum (maksimum) aktual

Nilai minimum (maksimum) ditampilkan di sel yang sesuai

subminmax()

Dim A () Sebagai Varian

n1 = Lembar("Lembar4"). Sel (5,12)

m = Lembar("Lembar2"). Sel (5,12)

ReDim A (1 Ke n1, 1 Ke m)

VVOD "Sheet5", 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 = 1000000

Untuk i = 1 Ke n1

Jika A (i,j) > maksA Maka

maksA = A(i, j)

Jika A (i, j)< minA Then

minA = A(i, j)

Lembar("Lembar10"). Sel(i + 4 + 2, j) = maksA

Lembar("Lembar10"). Sel(i + 4 + 3, j) = minA


Dengan bantuan alat Excel, Anda dapat memilih data tertentu dari rentang dalam urutan acak, menurut satu atau beberapa syarat. Untuk mengatasi masalah seperti itu, sebagai aturan, rumus array atau makro digunakan. Mari kita lihat contohnya.

Cara membuat pilihan di Excel dengan syarat

Saat menggunakan rumus array, data yang dipilih ditampilkan dalam tabel terpisah. Apa keuntungannya metode ini dibandingkan dengan filter konvensional.

Tabel sumber:

Pertama, mari pelajari cara membuat pilihan menurut satu kriteria numerik. Tugasnya adalah memilih barang dengan harga di atas 200 rubel dari meja. Salah satu solusinya adalah dengan menerapkan penyaringan. Akibatnya, hanya produk yang memenuhi kueri yang akan tetap berada di tabel sumber.

Solusi lain adalah dengan menggunakan rumus array. Baris yang sesuai dengan kueri akan ditempatkan dalam laporan tabel terpisah.

Pertama, kami membuat tabel kosong di sebelah yang asli: kami menduplikasi judul, 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, ubah argumen pertama fungsi INDEX.

Hasilnya, kami mendapat 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 adalah memilih dari tabel sumber barang yang mulai dijual pada 20/09/2015. Artinya, kriteria pemilihannya adalah tanggal. Untuk kenyamanan, kami akan memasukkan tanggal yang diinginkan di sel terpisah, I2.

Untuk mengatasi masalah tersebut, rumus array yang serupa digunakan. 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 begitu banyak di Excel.



Memilih 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. Kami menggabungkan kondisi dengan tanda "*". Rumus array terlihat seperti ini: ( }.!}

Ini untuk kolom pertama tabel laporan. Untuk yang kedua dan ketiga, kami mengubah argumen pertama dari fungsi INDEX. Hasil:

Untuk membuat pilihan berdasarkan beberapa tanggal atau kriteria numerik, kami menggunakan rumus larik yang serupa.

Pilihan acak di Excel

Saat pengguna bekerja dengan sejumlah besar data, pengambilan sampel acak mungkin diperlukan untuk analisis selanjutnya. Anda dapat menetapkan nomor acak untuk setiap baris, lalu mengurutkan pilihan.

Kumpulan data awal:

Pertama, masukkan dua kolom kosong di sebelah kiri. Di sel A2, masukkan rumus RAND (). Mari kalikan dengan seluruh kolom:

Sekarang kita salin kolom dengan nomor acak dan tempelkan ke kolom B. Hal ini diperlukan agar angka tersebut tidak berubah saat data baru dimasukkan ke dalam dokumen.

Untuk memasukkan nilai, dan bukan rumusnya, klik kanan pada kolom B dan pilih alat " Sisipan khusus". Di jendela yang terbuka, beri tanda centang di depan item "Nilai":

Sekarang Anda dapat mengurutkan data di kolom B dalam urutan menaik atau menurun. Urutan penyajian nilai asli juga akan berubah. Kami memilih sejumlah baris dari atas atau bawah - kami mendapatkan sampel acak.


Lain-lain (39)
Bug dan gangguan Excel (3)

Bagaimana cara mendapatkan daftar nilai unik (tidak berulang)?

Bayangkan daftar besar berbagai nama, nama lengkap, nomor personel, dll. Dan perlu untuk meninggalkan daftar semua nama yang sama dari daftar ini, tetapi agar tidak terulang - mis. hapus semua entri duplikat dari daftar ini. Seperti yang disebut sebaliknya: buat daftar elemen unik, daftar yang tidak berulang, tanpa duplikat. Ada beberapa cara untuk melakukannya: menggunakan alat Excel bawaan, rumus bawaan, dan terakhir menggunakan kode. Dasar visual untuk Aplikasi (VBA) dan tabel pivot. Pada artikel ini, kita akan melihat setiap opsi.

Menggunakan kemampuan bawaan Excel 2007 dan yang lebih baru
Di Excel 2007 dan 2010, ini mudah dilakukan - ada perintah khusus yang disebut -. Itu terletak di tab Data ayat Bekerja dengan data (Alat data)

Cara menggunakan perintah ini. Sorot sebuah kolom (atau beberapa) dengan data di mana Anda ingin menghapus entri duplikat. Buka tab Data -Hapus Duplikat.

Jika Anda memilih satu kolom, tetapi di sebelahnya ada lebih banyak kolom dengan data (atau setidaknya satu kolom), maka Excel akan menawarkan Anda pilihan: 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 rentang, data hanya akan berubah dalam satu kolom, dan data di kolom yang berdekatan tidak akan berubah.

Sebuah jendela akan muncul dengan opsi untuk menghapus duplikat.

Centang kotak di sebelah kolom yang ingin Anda hapus duplikatnya dan klik OK. Jika rentang yang dipilih juga berisi header data, lebih baik atur benderanya Data saya berisi header agar tidak sengaja menghapus data di tabel (jika mereka tiba-tiba cocok dengan nilai di header).

Metode 1: Filter lanjutan
Dalam kasus Excel 2003, semuanya menjadi lebih rumit. Tidak ada alat seperti Hapus Duplikat. Tapi ada alat yang luar biasa seperti Filter lanjutan. Pada tahun 2003 alat ini dapat ditemukan di Data -Saring -Filter lanjutan. Keindahan dari metode ini adalah dengan bantuannya Anda tidak dapat merusak data asli, tetapi membuat daftar dalam rentang yang berbeda. Di Excel 2007-2010 juga ada, tapi sedikit tersembunyi. Terletak di tab Data, grup Sortir & Filter - Canggih
Cara menggunakannya: jalankan alat yang ditentukan - kotak dialog akan muncul:

  • Perlakuan: Memilih Salin hasilnya ke lokasi lain (Salin ke lokasi lain).
  • Rentang sumber (Rentang daftar) : Memilih rentang dengan data (dalam kasus kami ini adalah A1:A51).
  • Rentang kriteria : dalam hal ini, biarkan kosong.
  • Letakkan hasilnya dalam rentang (Salin ke): tentukan sel pertama untuk keluaran data - apa saja yang kosong (dalam gambar - E2).
  • Beri tanda centang Hanya catatan unik (Hanya catatan unik).
  • Klik OKE.

Catatan: jika Anda ingin meletakkan hasilnya di lembar lain, maka menentukan lembar lain saja tidak akan berfungsi. Anda akan dapat menentukan sel di lembar lain, tapi... Sayangnya dan ah... Excel akan menampilkan pesan bahwa data tidak dapat disalin ke lembar lain. Tapi ini bisa dilewati, dan cukup sederhana. Anda hanya perlu lari Filter lanjutan dari lembar tempat kita ingin meletakkan hasilnya. Dan sebagai data awal, kami memilih data dari lembar mana pun - ini diperbolehkan.

Anda juga tidak dapat mentransfer hasilnya ke sel lain, tetapi memfilter data di tempat. Data tidak akan mengalami hal ini dengan cara apa pun - ini akan menjadi pemfilteran data normal.

Untuk melakukan ini, cukup pilih di bagian Pemrosesan Saring daftar, di tempat.

Metode 2: Rumus
Metode ini lebih sulit dipahami oleh pengguna yang tidak berpengalaman, tetapi metode ini membuat daftar nilai unik tanpa mengubah data aslinya. Nah, ini lebih dinamis: jika Anda mengubah data di tabel sumber, hasilnya juga akan berubah. Terkadang ini berguna. Saya akan mencoba menjelaskan dengan jari saya apa itu: katakanlah daftar Anda dengan data terletak di kolom A (A1: A51, di mana A1 adalah judulnya). Kami akan menampilkan daftar di kolom C, mulai dari sel C2. Rumus dalam C2 adalah:
(=INDEX($A$2:$A$51 ;SMALL(IF(COUNTIF($C$1:C1 ; $A$2:$A$51)=0;ROW($A$1:$A$50));1)) )
(=INDEX($A$2:$A$51 ;SMALL(IF(COUNTIF($C$1:C1 ; $A$2:$A$51)=0;ROW($A$1:$A$50));1)) )
Analisis terperinci tentang pekerjaan formula ini diberikan dalam artikel:
Perhatikan bahwa rumus ini adalah rumus array. Ini dapat ditunjukkan dengan tanda kurung kurawal di mana rumus ini diapit. Dan rumus berikut dimasukkan ke dalam sel dengan pintasan keyboard - ctrl+Menggeser+Memasuki. Setelah kita memasukkan rumus ini di C2, kita harus menyalin dan menempelkannya di beberapa baris agar semua elemen unik ditampilkan secara akurat. Setelah rumus di sel bawah kembali #NOMOR!- ini berarti semua elemen ditampilkan dan tidak ada gunanya memperluas rumus di bawah ini. Untuk menghindari kesalahan dan membuat rumus lebih universal (tanpa meregangkan setiap kali hingga terjadi kesalahan), Anda dapat menggunakan pemeriksaan sederhana:
untuk Excel 2007 dan lebih tinggi:
(=IFERROR(INDEX($A$2:$A$51 ,SMALL(IF(COUNT($C$1:C1 , $A$2:$A$51)=0,ROW($A$1:$A$50)),1 ));""))
(=IFERROR(INDEX($A$2:$A$51 ;SMALL(IF(COUNTIF($C$1:C1 ; $A$2:$A$51)=0;ROW($A$1:$A$50));1 ));""))
untuk Excel 2003:
(=IF(ISN(SMALL(IF(COUNTIF($C$1:C1 , $A$2:$A$51)=0,ROW($A$1:$A$50)),1)),"";INDEX( $A$2:$A$51 ;SMALL(IF(COUNT($C$1:C1 ; $A$2:$A$51)=0;ROW($A$1:$A$50));1))))
(=IF(ISERR(SMALL(IF(COUNTIF($C$1:C1 ; $A$2:$A$51)=0;ROW($A$1:$A$50));1));"";INDEX( $A$2:$A$51 ;SMALL(IF(COUNTIF($C$1:C1 ; $A$2:$A$51)=0;ROW($A$1:$A$50));1))))
Kemudian alih-alih kesalahan #NOMOR! (#NUM!) Anda akan memiliki sel kosong (tidak sepenuhnya kosong, tentu saja - dengan rumus :-)).

Sedikit lebih banyak tentang perbedaan dan nuansa rumus IFERROR dan IF(EOSH) dapat ditemukan di artikel ini: Cara menampilkan 0 alih-alih kesalahan dalam sel dengan rumus

Metode 3: Kode VBA
Pendekatan ini akan membutuhkan izin makro dan pengetahuan dasar tentang 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? Apa modulnya? diperlukan untuk memahami di mana harus memasukkan kode di bawah ini

Kedua kode berikut harus ditempatkan di modul standar. Makro harus diaktifkan.

Kami akan membiarkan data awal dalam urutan yang sama - daftar dengan data terletak di kolom "A" (A1:A51 dengan A1 adalah judulnya). Hanya kami yang akan menampilkan daftar bukan di kolom C, tetapi 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) With New Collection On Error Lanjutkan Berikutnya Untuk Setiap vItem In Range("A2", Cells(Rows.Count, 1) .End(xlUp)).Value "Cells(Rows.Count, 1).End(xlUp) - menentukan sel yang diisi terakhir di kolom A. Tambahkan vItem, CStr(vItem) Jika Err = 0 Lalu li = li + 1: avArr(li, 1) = vItem Else: Err.Clear End If Next End With If li Then .Resize(li).Value = avArr End Sub

Dengan menggunakan kode ini, Anda dapat mengekstrak yang unik tidak hanya dari satu kolom, tetapi juga dari berbagai kolom dan baris. Jika bukan garis
Jangkauan("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 menyenangkan untuk mengubah nilai sel keluaran - sebagai gantinya letakkan yang tidak ada datanya.
Anda juga dapat menentukan rentang tertentu:

Range("C2", Cells(Rows.Count, 3).End(xlUp)).Nilai

Kode generik untuk memilih nilai unik
Kode di bawah ini dapat diterapkan pada rentang apa pun. Cukup menjalankannya, tentukan rentang dengan nilai untuk memilih hanya yang tidak berulang (lebih dari satu kolom diperbolehkan) dan sel untuk menampilkan hasilnya. Sel yang ditentukan akan dipindai, hanya nilai unik yang akan dipilih darinya (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", "Kueri Data" , "A2:A51" , Ketik :=8) Jika rVals Tidak Ada Maka "jika tombol Batal ditekan Keluar Sub Akhir Jika "jika hanya satu sel yang ditentukan, tidak ada gunanya memilih Jika rVals.Count = 1 Maka MsgBox "Diperlukan lebih dari satu sel untuk memilih nilai unik", vbInformation, "www.site" Keluar Sub Akhir Jika "potong baris dan kolom kosong di luar rentang kerja Tetapkan rVals = Perpotongan(rVals, rVals.Parent.UsedRange) "jika hanya sel kosong di luar rentang kerja yang ditentukan Jika rVals Tidak Ada Maka MsgBox "Data tidak cukup untuk memilih nilai", vbInformation, "www.site" Keluar dari Sub Akhir Jika avVals = rVals.Value "meminta sel untuk menampilkan hasilnya Atur rResultCell = Application.InputBox( "Tentukan sel untuk menyisipkan nilai unik yang dipilih", "Data Kueri", "E2", Ketik :=8) Jika rResultCell Tidak Ada Maka "jika tombol Batal ditekan Keluar Sub Akhir Jika "tentukan dimensi array maksimum yang mungkin untuk hasilnya ReDim avArr(1 Ke Baris.Count, 1 Ke 1) "menggunakan objek Koleksi "pilih hanya catatan unik, "karena Koleksi tidak boleh berisi nilai duplikat Dengan Koleksi Baru Pada Kesalahan Lanjutkan Berikutnya Untuk Setiap x Dalam avVals If Len(CStr(x)) Then "lewati sel kosong.Tambahkan x, CStr(x) "jika elemen yang ditambahkan sudah ada di Koleksi, kesalahan akan terjadi "jika tidak ada kesalahan - nilai seperti itu belum dimasukkan, "tambahkan ke array yang dihasilkan Jika Err = 0 Maka li = li + 1 avArr(li, 1) = x Else "pastikan untuk menghapus objek Kesalahan Err.Hapus Akhiri Jika Akhiri Jika Selanjutnya Akhiri Dengan "tulis hasilnya ke lembar, mulai dari sel yang ditentukan Jika 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 , "Query data", "A2:A51", Type:=8) If rVals is Nothing Then "if tombol Batal ditekan Keluar Sub Akhir Jika "jika hanya satu sel yang ditentukan, tidak masuk akal untuk memilih Jika rVals.Count = 1 Kemudian MsgBox " Untuk memilih nilai unik, Anda perlu menentukan lebih dari satu sel", vbInformation, " www.site" Exit Sub End If "memotong baris dan kolom kosong di luar rentang kerja Tetapkan rVals = Intersect(rVals, rVals.Parent.UsedRange) "jika hanya sel kosong yang ditentukan di luar rentang Jika rVals Is Nothing Then MsgBox "Insufficient data untuk memilih nilai", vbInformation, "www..Value "meminta sel untuk menampilkan hasil Atur 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
Cara yang agak tidak standar untuk mengekstraksi nilai unik.

  • Pilih satu atau beberapa kolom dalam tabel, buka tab Menyisipkan-kelompok Meja -Tabel pivot
  • Di kotak dialog Membuat PivotTable (Buat PivotTable) periksa kebenaran pemilihan rentang data (atau instal sumber data baru)
  • tentukan lokasi PivotTable:
    • Ke lembar baru (Lembar Kerja Baru)
    • Ke Lembar Kerja yang Ada
  • konfirmasikan pembuatan dengan menekan tombol OKE

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 kita, kecuali untuk membuat tabel pivot dan menempatkan data dari kolom yang diinginkan di area baris atau kolom.
Menggunakan contoh file yang dilampirkan pada artikel, saya:


Apa ketidaknyamanan bekerja dengan tabel pivot dalam hal ini: jika data sumber berubah, tabel pivot harus diperbarui secara manual: Pilih sel mana saja dari tabel pivot -Tombol kanan tikus - Menyegarkan atau tab Data -Segarkan semua -Menyegarkan. Dan jika data awal diisi ulang secara dinamis dan bahkan lebih buruk - perlu untuk menentukan kembali kisaran data awal. Dan satu kekurangan lagi - data di dalam tabel pivot tidak dapat diubah. Oleh 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 ke lembar yang diinginkan.

Untuk lebih memahami semua tindakan dan mempelajari cara menangani tabel pivot, saya sangat menyarankan Anda membaca artikel Ikhtisar Tabel Pivot - tutorial video terlampir, di mana saya dengan jelas menunjukkan kesederhanaan dan kenyamanan bekerja dengan fitur utama tabel pivot.

Dalam contoh terlampir, selain teknik yang dijelaskan, variasi ekstraksi elemen unik yang sedikit lebih kompleks dengan rumus dan kode dicatat, yaitu: mengekstraksi elemen unik dengan kriteria. Tentang apa: jika dalam satu kolom nama belakang, dan di kolom kedua (B) ada beberapa data (dalam file ini adalah bulan) dan diperlukan untuk mengekstrak nilai unik kolom B hanya untuk yang dipilih nama belakang. Contoh ekstraksi unik tersebut terletak di lembaran Ekstrak berdasarkan kriteria.

Contoh unduhan:

(108,0 KiB, 14.152 unduhan)

Apakah artikel itu membantu? Bagikan tautan dengan teman-teman Anda! Pelajaran video

Jika Anda harus bekerja dengan meja besar Anda pasti akan menemukan di dalamnya jumlah duplikat yang tersebar di seluruh kolom. Pada saat yang sama, Anda mungkin perlu memilih data dari tabel dengan nilai numerik terkecil pertama yang memiliki duplikatnya sendiri. Kami membutuhkan pemilihan data otomatis berdasarkan kondisi. Di Excel, Anda bisa berhasil menggunakan rumus array untuk tujuan ini.

Cara membuat pilihan di Excel dengan syarat

Untuk menentukan nilai yang sesuai untuk bilangan terkecil pertama, diperlukan pemilihan dari tabel berdasarkan kondisi. Katakanlah kita ingin mengetahui produk termurah pertama di pasaran dari daftar harga yang diberikan:

Pengambilan sampel otomatis diterapkan dengan rumus yang akan memiliki struktur berikut:

INDEX(rentang_data_untuk_pemilihan, MIN(IF(rentang=MIN(rentang),ROW(rentang)-ROW(tajuk_kolom),"")))

Di tempat "data_range_for_selection" Anda harus menentukan rentang nilai A6:A18 untuk pemilihan dari tabel (misalnya, teks), dari mana fungsi INDEX akan memilih satu nilai yang dihasilkan. Argumen "rentang" berarti area sel dengan nilai numerik dari mana angka terkecil pertama harus dipilih. Dalam argumen "column_header" untuk fungsi ROW kedua, Anda harus menentukan referensi sel dengan header kolom yang berisi rentang nilai numerik.

Secara alami, rumus ini harus dilakukan dalam sebuah array. Oleh karena itu, untuk mengonfirmasi masuknya, Anda harus menekan bukan hanya tombol Enter, tetapi juga seluruh kombinasi tombol CTRL + SHIFT + Enter. Jika semuanya dilakukan dengan benar, kurung kurawal akan muncul di bilah rumus.

Perhatikan gambar di bawah ini, di mana rumus dalam array ini dimasukkan ke dalam sel B3:

Memilih nilai yang sesuai dengan angka terkecil pertama:


Dengan rumus ini, kami dapat memilih nilai minimum relatif terhadap angka. Selanjutnya, kami akan menganalisis prinsip rumus dan menganalisis seluruh urutan semua perhitungan langkah demi langkah.



Cara kerja pemilihan berdasarkan kondisi

Fungsi INDEX memainkan peran kunci di sini. Tugas nominalnya adalah memilih dari tabel sumber (ditentukan dalam argumen pertama - A6:A18) nilai yang sesuai dengan angka tertentu. INDEX bekerja sesuai dengan kriteria yang ditentukan dalam argumen kedua (nomor baris di dalam tabel) dan ketiga (nomor kolom dalam tabel). Karena tabel sumber kami A6:A18 hanya memiliki 1 kolom, kami tidak menentukan argumen ketiga dalam fungsi INDEX.

Beberapa fungsi kalkulasi digunakan untuk menghitung nomor baris tabel yang berlawanan dengan angka terkecil dalam rentang yang berdekatan B6:B18 dan menggunakannya sebagai nilai untuk argumen kedua.

Fungsi IF memungkinkan Anda memilih nilai dari daftar berdasarkan kondisi. Argumen pertamanya menentukan di mana setiap sel dalam rentang B6:B18 diperiksa untuk nilai numerik terkecil: IFB6:B18=MINB6:B18. Dengan cara ini, sebuah array dibuat dalam memori program dari nilai logika BENAR dan SALAH. Dalam kasus kita, 3 elemen larik akan berisi BENAR, karena nilai minimum 8 berisi 2 duplikat lagi di kolom B6:B18.

Langkah selanjutnya adalah menentukan baris mana dalam rentang yang berisi setiap nilai minimum. Kami membutuhkan ini karena definisi nilai terkecil pertama. Diimplementasikan tugas yang diberikan menggunakan fungsi ROW, ini mengisi elemen array di memori program dengan nomor baris lembar. Tapi pertama-tama, dari semua angka ini, angkanya dikurangi dengan baris pertama tabel - B5, yaitu angka 5. Ini dilakukan karena fungsi INDEX bekerja dengan angka di dalam tabel, dan bukan dengan angka lembar kerja Excel. Pada saat yang sama, fungsi ROW hanya dapat mengembalikan nomor baris dari lembar tersebut. Agar tidak mendapatkan offset, perlu dilakukan pembandingan urutan nomor baris lembar dan tabel dengan cara mengurangkan selisihnya. Misalnya, jika tabel berada di baris ke-5 lembar, maka setiap baris tabel akan lebih kecil 5 dari baris lembar yang sesuai.

Setelah semua nilai minimum dipilih dan semua nomor baris tabel cocok, fungsi MIN akan memilih nomor baris terkecil. Baris yang sama akan berisi angka terkecil pertama yang muncul di kolom B6:B18. Berdasarkan nomor baris ini, fungsi INDEX akan memilih nilai yang sesuai dari tabel A6:A18. Hasilnya, rumus mengembalikan nilai ini di sel B3 sebagai hasil perhitungan.

Cara memilih nilai dengan angka terbesar di Excel

Setelah memahami prinsip rumusnya, kini Anda dapat dengan mudah memodifikasinya dan menyesuaikannya dengan kondisi lain. Misalnya, rumus dapat dimodifikasi untuk memilih nilai maksimum pertama di Excel:


Jika Anda perlu mengubah ketentuan rumus agar di Excel Anda dapat memilih maksimum pertama, tetapi kurang dari 70:

!}

Cara memilih nilai minimum pertama selain nol di Excel:


Seperti yang dapat Anda lihat dengan mudah, rumus ini hanya berbeda dalam fungsi MIN dan MAX serta argumennya.

Sekarang tidak ada yang membatasi Anda. Setelah Anda memahami prinsip pengoperasian rumus dalam array, Anda dapat dengan mudah memodifikasinya untuk banyak kondisi dan dengan cepat menyelesaikan banyak masalah komputasi.

Saat bekerja dengan tabel Excel, seringkali Anda harus memilihnya sesuai dengan kriteria atau beberapa kondisi tertentu. Anda dapat melakukannya di program cara yang berbeda menggunakan sejumlah alat. Mari cari tahu cara membuat sampel di Excel menggunakan berbagai opsi.

Sampling data terdiri dari prosedur pemilihan dari susunan umum hasil yang memenuhi kondisi yang diberikan, dengan tampilan selanjutnya pada lembar dalam daftar terpisah atau dalam rentang asli.

Metode 1: Terapkan AutoFilter Tingkat Lanjut

Paling dengan cara sederhana untuk membuat pilihan adalah menerapkan filter otomatis lanjutan. Mari kita lihat bagaimana melakukannya contoh spesifik.

  1. Kami memilih area pada lembar, di antara data yang ingin Anda pilih. Di tab "Rumah" klik tombolnya "Sortir dan Filter". Itu terletak di blok pengaturan. "Mengedit". Dalam daftar yang terbuka setelah ini, klik tombolnya "Saring".

    Ada kesempatan untuk melakukannya secara berbeda. Untuk melakukan ini, setelah memilih area pada lembar, pindah ke tab "Data". Mengklik tombol "Saring", yang terletak di pita di grup "Sortir dan Filter".

  2. Setelah tindakan ini, ikon untuk memulai pemfilteran muncul di tajuk tabel dalam bentuk segitiga kecil terbalik di tepi kanan sel. Kami mengklik ikon ini di tajuk kolom yang ingin kami pilih. Di menu yang terbuka, buka item tersebut "Filter Teks". Selanjutnya, pilih posisi "Filter Khusus...".

  3. Jendela pemfilteran khusus diaktifkan. Di dalamnya, Anda dapat mengatur batasan pemilihan yang akan dilakukan. Dalam daftar drop-down untuk kolom yang berisi sel format angka yang kami gunakan sebagai contoh, Anda dapat memilih salah satu dari lima jenis ketentuan:
    • sama dengan;
    • tidak sama;
    • lagi;
    • lebih atau sama;
    • lebih sedikit.

    Mari, sebagai contoh, tetapkan kondisi untuk memilih hanya nilai yang jumlah pendapatannya melebihi 10.000 rubel. Atur sakelar ke posisi "Lagi". Masukkan nilai di bidang kanan "10.000". Untuk melakukan tindakan, klik tombol. OKE.

  4. Seperti yang Anda lihat, setelah memfilter, hanya baris yang jumlah pendapatannya melebihi 10.000 rubel yang tersisa.

  5. Tapi di kolom yang sama, kita bisa menambahkan syarat kedua. Untuk melakukan ini, kami kembali ke jendela pemfilteran khusus lagi. Seperti yang Anda lihat, di bagian bawahnya ada sakelar kondisi lain dan bidang input yang sesuai dengannya. Sekarang mari kita atur batas atas pemilihan menjadi 15.000 rubel. Untuk melakukan ini, atur sakelar ke posisi "Lebih sedikit", dan di bidang di sebelah kanan, masukkan nilainya "15000".

    Selain itu, ada juga saklar kondisi. Ini memiliki dua posisi "DAN" Dan "ATAU". Secara default, ini diatur ke posisi pertama. Ini berarti bahwa hanya baris yang memenuhi kedua batasan yang akan tetap ada di sampel. Jika ditempatkan pada posisinya "ATAU", maka akan ada nilai yang sesuai dengan salah satu dari kedua kondisi tersebut. Dalam kasus kami, Anda perlu mengatur sakelar ke posisi "DAN", yaitu pergi pengaturan ini bawaan. Setelah semua nilai dimasukkan, klik tombol OKE.

  6. Sekarang di tabel hanya ada garis di mana jumlah pendapatan tidak kurang dari 10.000 rubel, tetapi tidak melebihi 15.000 rubel.

  7. Demikian pula, Anda dapat mengatur filter di kolom lain. Pada saat yang sama, Anda juga dapat menyimpan pemfilteran berdasarkan kondisi sebelumnya yang ditentukan di kolom. Jadi, mari kita lihat bagaimana pemilihan dibuat menggunakan filter untuk sel dalam format tanggal. Klik ikon filter di kolom yang sesuai. Secara berurutan klik pada item dalam daftar "Filter menurut tanggal" Dan "Filter khusus".

  8. Jendela Custom AutoFilter diluncurkan lagi. Mari kita lakukan pemilihan hasil dalam tabel dari 4 Mei hingga 6 Mei 2016 inklusif. Dalam sakelar pemilihan kondisi, seperti yang bisa kita lihat, ada lebih banyak opsi daripada format angka. Pilih posisi "Setelah atau Sama". Tetapkan nilai di bidang di sebelah kanan "05.04.2016". Di blok bawah, atur sakelar ke posisi "Sebelum atau Sama". Masukkan nilai di bidang kanan "06.05.2016". Biarkan sakelar kompatibilitas kondisi di posisi default - "DAN". Untuk menerapkan pemfilteran dalam tindakan, klik tombol OKE.

  9. Seperti yang Anda lihat, daftar kami semakin berkurang. Sekarang hanya garis yang tersisa di dalamnya, di mana jumlah pendapatan bervariasi dari 10.000 hingga 15.000 rubel untuk periode dari 04.05 hingga 06.05.2016 inklusif.

  10. Kami dapat mengatur ulang pemfilteran di salah satu kolom. Mari kita lakukan ini untuk nilai pendapatan. Klik ikon filter otomatis di kolom yang sesuai. Di daftar tarik-turun, klik item tersebut "Hapus Filter".

  11. Seperti yang Anda lihat, setelah tindakan ini, pemilihan berdasarkan jumlah pendapatan akan dinonaktifkan, dan hanya pemilihan berdasarkan tanggal yang tersisa (dari 05/04/2016 hingga 05/06/2016).

  12. Tabel ini memiliki satu kolom lagi - "Nama". Ini berisi data di format teks. Mari kita lihat cara membentuk pilihan dengan memfilter nilai-nilai ini.

    Klik ikon filter di nama kolom. Telusuri nama-nama daftar secara berurutan "Filter Teks" Dan "Filter Khusus...".

  13. Jendela Custom AutoFilter terbuka lagi. Mari kita lihat nama-namanya "Kentang" Dan "Daging". Di blok pertama, setel sakelar kondisi ke posisi "Sama". Di bidang di sebelah kanannya, masukkan kata "Kentang". Sakelar blok bawah juga disetel ke posisi "Sama". Di lapangan di seberangnya, kami membuat entri - "Daging". Dan kemudian kami melakukan sesuatu yang tidak kami lakukan sebelumnya: kami menyetel sakelar kompatibilitas kondisi ke posisi "ATAU". Sekarang baris yang berisi salah satu kondisi yang ditentukan akan ditampilkan di layar. Mengklik tombol OKE.

  14. Seperti yang Anda lihat, dalam sampel baru ada batasan berdasarkan tanggal (dari 05/04/2016 hingga 05/06/2016) dan berdasarkan nama (kentang dan daging). Tidak ada batasan jumlah pendapatan.

  15. Anda dapat sepenuhnya menghapus filter dengan cara yang sama seperti yang digunakan untuk memasangnya. Tidak masalah metode mana yang digunakan. Untuk mengatur ulang pemfilteran, berada di tab "Data" klik tombolnya "Saring", yang ditempatkan di grup "Sortir dan Filter".

    Opsi kedua melibatkan pergi ke tab "Rumah". Di sana kami mengklik tombol di pita "Sortir dan Filter" di blok "Mengedit". Dalam daftar yang diaktifkan, klik tombol "Saring".

Saat menggunakan salah satu dari dua metode di atas, pemfilteran akan dihapus dan hasil pemilihan akan dihapus. Artinya, tabel tersebut akan menampilkan seluruh array data yang dimilikinya.

Metode 2: Terapkan rumus array

Anda juga dapat membuat pilihan menggunakan rumus kompleks Himpunan. Berbeda dengan versi sebelumnya, metode ini menyediakan output dari hasil dalam tabel terpisah.

  1. Di lembar yang sama, kami membuat tabel kosong dengan nama kolom yang sama di header seperti di sumbernya.

  2. Pilih semua sel kosong di kolom pertama tabel baru. Tempatkan kursor di bilah rumus. Di sinilah formula yang memilih sesuai dengan kriteria yang ditentukan akan dimasukkan. Kami akan memilih jalur yang jumlah pendapatannya melebihi 15.000 rubel. Dalam contoh khusus kami, rumus yang dimasukkan akan terlihat seperti ini:

    INDEKS(A2:A29,RENDAH(JIKA(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

    Secara alami, dalam setiap kasus, alamat sel dan rentang akan berbeda. Dalam contoh ini, Anda dapat membandingkan rumus dengan koordinat pada ilustrasi dan menyesuaikannya dengan kebutuhan Anda.

  3. Karena ini adalah rumus array, untuk menerapkannya dalam tindakan, Anda tidak perlu mengklik tombolnya Memasuki, dan pintasan keyboard Ctrl+Shift+Enter. Ayo lakukan.

  4. Memilih kolom kedua dengan tanggal dan menempatkan kursor di bilah rumus, masukkan ekspresi berikut:

    INDEKS(B2:B29,KECIL(JIKA(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

    Tekan pintasan keyboard Ctrl+Shift+Enter.

  5. Demikian pula, di kolom pendapatan, masukkan rumus berikut:

    INDEKS(C2:C29,KECIL(JIKA(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

    Ketik pintasan keyboard lagi Ctrl+Shift+Enter.

    Dalam ketiga kasus tersebut, hanya nilai pertama dari koordinat yang berubah, tetapi jika tidak, rumusnya benar-benar identik.

  6. Seperti yang Anda lihat, tabel diisi dengan data, tetapi tampilannya tidak sepenuhnya menarik, apalagi pengisian nilai tanggalnya salah. Kekurangan tersebut perlu diperbaiki. Ketidaktepatan tanggal disebabkan oleh fakta bahwa format sel pada kolom yang sesuai adalah umum, dan kita perlu mengatur format tanggal. Pilih seluruh kolom, termasuk sel dengan kesalahan, dan klik kanan pada pilihan. Dalam daftar yang muncul, buka item tersebut "Format Sel...".

  7. Di jendela pemformatan yang terbuka, buka tab "Nomor". Di blok "Format Angka" menonjolkan nilai "Tanggal". Di bagian kanan jendela, Anda dapat memilih jenis tampilan tanggal yang diinginkan. Setelah pengaturan diatur, klik tombol OKE.

  8. Sekarang tanggal ditampilkan dengan benar. Namun, seperti yang Anda lihat, seluruh bagian bawah tabel diisi dengan sel yang berisi nilai yang salah "#NOMOR!". Faktanya, ini adalah sel yang tidak memiliki cukup data dari sampel. Akan lebih menarik jika ditampilkan benar-benar kosong. Untuk tujuan ini, kami akan menggunakan pemformatan bersyarat. Pilih semua sel tabel kecuali untuk header. Berada di tab "Rumah" klik tombolnya "Pemformatan Bersyarat", yang terletak di kotak peralatan "Gaya". Di daftar yang muncul, pilih item "Buat aturan ...".

  9. Di jendela yang terbuka, pilih jenis aturan "Format hanya sel yang berisi". Di bidang pertama di bawah prasasti "Format hanya sel yang memenuhi kondisi berikut" memilih posisi "Kesalahan". Selanjutnya, klik tombolnya "Format…".

  10. Di jendela pemformatan yang terbuka, buka tab "Font" dan di bidang yang sesuai pilih warna putih. Setelah langkah-langkah ini, klik tombol OKE.

  11. Klik tombol dengan nama yang persis sama setelah kembali ke jendela untuk membuat kondisi.

Sekarang kami memiliki sampel siap pakai untuk kendala yang ditentukan dalam tabel terpisah yang diformat dengan benar.

Metode 3: Memilih Beberapa Kondisi Menggunakan Rumus

Sama seperti menggunakan filter, menggunakan formula, Anda dapat memilih berdasarkan beberapa kondisi. Misalnya, mari kita ambil tabel sumber yang sama, serta tabel kosong tempat hasilnya akan ditampilkan, dengan pemformatan numerik dan bersyarat sudah selesai. Mari kita tetapkan batas bawah pemilihan berdasarkan pendapatan menjadi 15.000 rubel sebagai kendala pertama, dan batas atas menjadi 20.000 rubel sebagai syarat kedua.


Metode 4: pengambilan sampel secara acak

Di Excel menggunakan formula khusus RAND pemilihan acak juga dapat digunakan. Ini diperlukan dalam beberapa kasus saat bekerja dengan sejumlah besar data, saat Anda perlu menyajikan gambaran besar tanpa analisis komprehensif dari semua data dalam larik.


Seperti yang Anda lihat, pilihan dalam tabel Excel dapat dibuat menggunakan filter otomatis dan menggunakan rumus khusus. Dalam kasus pertama, hasilnya akan ditampilkan di tabel asli, dan yang kedua - di area terpisah. Dimungkinkan untuk membuat pilihan, baik dengan satu syarat, dan dengan beberapa syarat. Selain itu, Anda dapat melakukan pengambilan sampel acak menggunakan fungsi tersebut RAND.



Memuat...
Atas