10 teratas di tabel pivot 2022

In a previous TechRepublic article, How to highlight the top n values in a Microsoft Excel sheet, I explain two different conditional formatting methods to highlight the top n values in a data set in Microsoft Excel. Either is reasonable when you want to view all of the data. When you want to see only the top n records—filtering out all other records—you’ll need a different strategy.

Disclosure: TechRepublic may earn a commission from some of the products featured on this page. TechRepublic and the author were not compensated for this independent review.

LEARN MORE: Office 365 Consumer pricing and features

Fortunately, Microsoft Excel has a built-in filter for PivotTables that will let you display the top (or bottom) n record. In this article, you’ll create a simple PivotTable and then use the built-in filter to display only the top 10 records in the data source. Then, we’ll discuss some problems with the results and possible solutions. It’s this latter section of the article that might provide the most new material for some of you.

I’m using (desktop) Office 365, but you can use earlier versions of the ribbon format. You can work with your own data or download the demonstration .xlsx file (this article isn’t applicable to the older .xls version). The browser edition will display the PivotTable, but you can’t run code in the browser.

How to build a PivotTable in Excel

This section is a tutorial on building a PivotTable. If you know how to build one, you can skip this section. To build the demonstration PivotTable using the data set in Figure A, do the following:

1. Click anywhere inside the data set and then click the Insert tab. In the Tables group, click PivotTable.
2. In the resulting dialog, click OK; we don’t need to change any default settings.
3. Click inside the PivotTable frame. In the PivotTable field list pane, drag Product Name to the Rows section. Drag Unit Price to the Values section (Figure B). The frame will update accordingly as you add fields.

Figure A

Figure B

By default, the PivotTable sums the unit price values by products, but our list of products is unique, so none of the values change. To format the unit price column, right-click the header cell and choose Number Format from the resulting submenu. (I chose Currency.)

As is, the PivotTable displays all the records. Let’s look at our first method for displaying the top n unit price values.

How to use the built-in option

Applying a filter is easy, and there are lots of options. The downside is they’re not dynamic, but we’ll discuss that in a bit. Now, let’s apply that filter:

1. Click the Row Labels dropdown filter.
2. Choose Value Filters (remember, we’re evaluating the unit price values, not the products), and then choose Top 10 (Figure C). There’s no dropdown for the unit price column because it’s a values column. There are lots of options, so you’ll want to explore those later.
3. The resulting dialog has several options, but the default settings (Figure D) are perfect for our example: display the top 10 items in the Sum of Unit Price column.
4. Click OK to see the results in Figure E.

Figure C

Figure D

Figure E

You could use the resulting PivotTable as is, but there are a few issues you might want to consider first. Let’s take a look next.

Fixing a few small problems

Now, let’s tweak the results a bit. In this case, the totaling row at the bottom isn’t meaningful, so you can turn it off. To do so, click the contextual Design tab. In the Layout group, click the Grand Totals dropdown and choose Off for Rows and Columns.

Excel sorts by the row label—products—so it can group and sum multiple values correctly. In this case, it’s unnecessary and the product sort isn’t particularly meaningful. Most likely, you’ll want to sort by the unit price. To do so, right-click any unit price cell, choose Sort, and then choose Sort Largest to Smallest. The header text isn’t descriptive. To change it, simply click inside the cell and replace the default headers–or not. If you decide to hide the dropdown arrow, you can skip this step.

Most likely, you’ll not want viewers to change the filter. There are a few complex methods for doing so, but the easiest is to remove the filter dropdown. To do so, click anywhere inside the PivotTable and click the contextual PivotTable Analyze tab. Then, click the Options dropdown in the PivotTable group (to the far left), and choose Options. In the resulting dialog, click the Display tab and uncheck the Display field caption and filter drop downs option. Doing so will inhibit both the header text and the dropdown, which isn’t really a problem. Simply hide the row and add the appropriate text or title above the PivotTable. (This option isn’t used in the demonstration file.)

Jika Anda tidak terbiasa dengan Pivottable, inilah perilaku penting yang perlu Anda ingat: mereka tidak memperbarui secara otomatis. Jika Anda mengubah nilai dalam data sumber yang akan berdampak pada 10 teratas yang dapat diputus, pivottable tidak akan menampilkan perubahan itu sampai Anda menyegarkannya. Tetapi ada bantuan, jika bukan solusi yang sempurna.

Solusi yang tidak terlalu dinamis

Untuk memperbarui yang dapat diputuskan, klik kanan sel apa pun dan pilih Refresh. Pemirsa tidak akan tahu untuk melakukan ini, sehingga layar dapat berada di belakang pembaruan. Solusi paling sederhana adalah memaksa Excel untuk memperbarui Pivottable ketika seseorang membuka buku kerja. Anda dapat melakukannya, sebagai berikut:

1. Klik di mana saja di dalam Pivottable dan kemudian klik tab Analisis Pivottable kontekstual.
2. Di grup Pivottable (ke paling kiri), klik dropdown opsi (di bawah kotak nama Pivottable), dan pilih opsi dari daftar dropdown.
3. Dalam dialog yang dihasilkan, klik tab Data.
4. Periksa data refresh saat membuka opsi file (Gambar F).Figure F).
5. Klik OK.

Gambar f

Memilih opsi ini memastikan bahwa pemirsa melihat informasi terkini, tetapi mereka harus membuka file, jadi itu bukan perbaikan yang sempurna untuk masalah tersebut. (Opsi ini tidak diatur dalam file demonstrasi sehingga Anda dapat mengalami perilaku sendiri.) Mungkin solusi yang lebih baik adalah prosedur peristiwa sederhana dalam daftar A.Listing A.

Daftar a

Sub private sub worksheet_deactivate ()

ThisWorkbook.RefreshAll

Akhir sub

Tambahkan prosedur ini ke modul lembar yang terpasang pada sumber data. Saat Anda memperbarui data dan kemudian meninggalkan lembar itu, metode Nonaktifkan memicu metode refresh. Solusi mudah ini hadir dengan beberapa keterbatasan. Pertama, Pivottable dan sumber data harus ada di lembar yang berbeda. Selain itu, metode refreshall menyegarkan semuanya: semua pivottable, kueri, dan sebagainya. Jika Anda ingin membatasi refresh ke yang dapat dipotong, gunakan metode refresh berikut sebagai gantinya:

sheetname.pivottables ("PivottableName"). Pivotcache.refresh

Pastikan untuk memperbarui SheetName dan PivottableName sesuai saat mendaftar ke pekerjaan Anda sendiri. File demonstrasi berisi kode, tetapi dikomentari. Saat menerapkan kode untuk pekerjaan Anda sendiri, jangan salin dari halaman web ini karena Visual Basic Editor (VBE) tidak akan dapat mengkompilasi karakter yang tidak terlihat. Masukkan kode sendiri atau salin kode ke Word (atau editor teks lain) dan kemudian salin kode dari sana ke VBE. Selain itu, jika Anda menambahkan kode ke buku kerja Anda, pastikan untuk menyimpan file sebagai buku kerja yang diaktifkan makro (.xlsm).

Kirimkan saya pertanyaan Microsoft Office Anda

Saya menjawab pertanyaan pembaca kapan saya bisa, tetapi tidak ada jaminan. Jangan mengirim file kecuali diminta; Permintaan awal untuk bantuan yang datang dengan file terlampir akan dihapus belum dibaca. Anda dapat mengirim tangkapan layar data Anda untuk membantu memperjelas pertanyaan Anda. Saat menghubungi saya, jadilah spesifik mungkin. Misalnya, "Mohon memecahkan masalah buku kerja saya dan perbaiki apa yang salah" mungkin tidak akan mendapat tanggapan, tetapi "dapatkah Anda memberi tahu saya mengapa formula ini tidak mengembalikan hasil yang diharapkan?" mungkin. Harap sebutkan aplikasi dan versi yang Anda gunakan. Saya tidak diganti oleh TechRepublic untuk waktu atau keahlian saya ketika membantu pembaca, saya juga tidak meminta biaya dari pembaca yang saya bantu. Anda dapat menghubungi saya di.