Pertanyaan ini muncul sekali setiap dekade: Bisakah Anda melakukan Median di tabel pivot. Biasanya, jawabannya adalah Tidak. Saya ingat di tahun 2000 ketika saya menyewa MVP Excel Juan Pablo Gonzalez untuk menulis makro mengagumkan yang membuat laporan yang tampak seperti tabel pivot tetapi memiliki Median.
Jadi ketika Alex dalam seminar Houston Power Excel saya bertanya tentang pembuatan median, saya dengan cepat menjawab "Tidak". Tapi kemudian… Saya bertanya-tanya apakah DAX memiliki fungsi median. Pencarian Google cepat dan Ya! Ada fungsi DAX untuk Median.
Apa yang diperlukan untuk menggunakan DAX dalam tabel pivot? Anda memerlukan Excel versi Windows yang menjalankan Excel 2013 atau yang lebih baru.
Berikut adalah kumpulan data saya yang sangat sederhana yang akan saya gunakan untuk menguji bagaimana median tabel pivot dihitung. Anda dapat melihat median untuk Chicago harus 5000 dan median untuk Cleveland harus 17000. Dalam kasus Chicago, ada lima nilai, jadi Median akan menjadi nilai tertinggi ke-3. Namun untuk keseluruhan kumpulan data, ada 12 nilai. Itu berarti mediannya berada di antara 11000 dan 13000. Excel rata-rata dari kedua nilai tersebut untuk menghasilkan 12000.
Untuk memulai, pilih satu sel dalam data Anda dan tekan Ctrl + T untuk memformat data sebagai tabel.
Kemudian, pilih Sisipkan, Tabel Pivot. Dalam dialog Sisipkan PivotTable, pilih kotak untuk Tambahkan Data Ini ke Model Data.
Di Bidang Tabel Pivot, pilih Wilayah dan Distrik. Tapi jangan pilih Sales. Sebagai gantinya, klik kanan pada judul Tabel dan pilih Pengukuran Baru. "Measure" adalah nama keren untuk bidang kalkulasi. Pengukuran lebih efektif daripada bidang kalkulasi dalam tabel pivot biasa.
Dalam dialog Define Measure, isi empat entri yang ditunjukkan di bawah ini:
- Mengukur Nama: Median Penjualan
- Rumus
=MEDIAN((Sales)) - Format angka: Angka
- Tempat Desimal: 0
Setelah membuat ukuran, itu ditambahkan ke daftar bidang, tetapi Anda harus memilih entri untuk menambahkannya ke area Nilai dari tabel pivot. Seperti yang Anda lihat di bawah, tabel pivot menghitung median dengan benar.
Menonton video
Transkrip Video
Pelajari Excel dari Podcast, Episode 2197: Median dalam Tabel Pivot.
Saya harus memberi tahu Anda, saya sangat bersemangat tentang ini. ketika saya di Houston melakukan seminar Power Excel di sana, dan Alex mengangkat tangannya, berkata, "Hei, adakah cara untuk membuat median dalam tabel pivot?" Tidak, Anda tidak dapat membuat median di tabel pivot. Saya ingat 25 tahun yang lalu, teman baik saya, Juan Pablo Gonzales, sebenarnya membawa makro untuk melakukan padanan median, tidak menggunakan tabel pivot-- itu tidak mungkin.
Tapi aku punya percikan. Saya berkata, "Tunggu sebentar," dan saya membuka browser, dan mencari "median DAX", dan, tentu saja, ada fungsi MEDIAN di DAX, yang berarti kami dapat menyelesaikan masalah ini.
Baiklah, jadi, untuk menggunakan DAX, Anda harus berada di Excel 2013 atau Excel 2016, atau di Excel 2010, dan memiliki add-in Power Pivot; Anda tidak harus memiliki tab Power Pivot, kita hanya perlu memiliki Model Data. Baik? Jadi saya akan memilih data ini, saya akan membuatnya menjadi tabel dengan Ctrl + T, dan mereka memberinya nama yang tidak imajinatif dari "Tabel 4". Dalam kasus Anda, itu mungkin "Tabel 1". Dan kami akan memasukkan Tabel Pivot, menambahkan data ini ke Model Data, klik OK, dan kami akan memilih Regional di sisi kiri, tetapi bukan Penjualan. Sebagai gantinya, saya ingin membuat pengukuran terhitung baru. Jadi saya datang ke sini ke meja dan saya klik kanan dan berkata, Tambahkan Ukuran. Dan ukuran ini akan disebut "Median Penjualan", dan rumusnya adalah: = MEDIAN. Tekan tab di sana dan pilih Penjualan,tanda kurung tutup. Saya dapat memilih ini sebagai angka dengan nol tempat desimal, menggunakan pemisah seribu dan klik OK. Dan, mari kita lihat, bidang baru kita ditambahkan di sini, kita harus memberi tanda centang untuk menambahkannya, dan dikatakan median untuk Midwest adalah 12.000.
Sekarang mari kita periksa. Jadi, di sini, semua Midwest, median dari seluruh kumpulan data antara 11.000 dan 13.000. Jadi rata-rata 11 dan 13, yang persis seperti yang akan dilakukan median di Excel biasa. Sekarang mari kita tambahkan distrik dan dikatakan median 5.000 Chicago, median Cleveland adalah 17.000; Midwest total dan grand total 12.000. Semua itu benar. Seberapa hebat itu? Terakhir, median dalam tabel pivot, berkat bidang terhitung, atau ukuran, sebagaimana namanya, dan Model Data.
Sekarang, banyak tip favorit saya - tip untuk seminar Power Excel saya secara langsung - dalam buku ini LIVe, The 54 Greatest Tip of All Time. Klik "Saya" di pojok kanan atas untuk membaca lebih lanjut tentang buku itu.
Baik. Kesimpulan: Bisakah Anda membuat median di tabel pivot? Oh tidak, ya, Anda bisa, berkat Model Data. Anda dapat membuat median; Ctrl + T untuk membuat data Anda menjadi tabel; Sisipkan Tabel Pivot; dan centang kotak itu, Tambahkan data ini ke Model Data; klik kanan nama tabel dan pilih ukuran baru, dan ukuran akan = MEDIAN ((Penjualan)). Itu mengagumkan.
Terima kasih kepada Alex karena telah hadir di seminar saya dan mengajukan pertanyaan itu, terima kasih telah mampir. Sampai jumpa di lain waktu untuk netcast lain dari.
Unduh File Excel
Untuk mendownload file excel: pivot-table-median.xlsx
