Soalan ini muncul sekali setiap dekad: Bolehkah anda melakukan Median di meja pangsi. Secara tradisinya, jawapannya adalah Tidak. Saya masih ingat pada tahun 2000 ketika saya mengupah Excel MVP Juan Pablo Gonzalez untuk menulis makro hebat yang membuat laporan yang kelihatan seperti jadual pangsi tetapi mempunyai orang Median.
Oleh itu, ketika Alex dalam seminar Houston Power Excel saya bertanya tentang membuat median, saya dengan pantas mengatakan "Tidak". Tetapi kemudian … saya tertanya-tanya adakah DAX mempunyai fungsi median. Carian Google yang pantas dan Ya! Terdapat fungsi DAX untuk Median.
Apa yang diperlukan untuk menggunakan DAX dalam jadual pangsi? Anda memerlukan Excel versi Windows yang menjalankan Excel 2013 atau yang lebih baru.
Berikut adalah set data saya yang sangat mudah yang akan saya gunakan untuk menguji bagaimana median pivot table dikira. Anda dapat melihat median untuk Chicago harus 5000 dan median untuk Cleveland harus 17000. Dalam kes Chicago, terdapat lima nilai, jadi Median akan menjadi nilai tertinggi ke-3. Tetapi untuk keseluruhan set data, terdapat 12 nilai. Ini bermaksud median berada di antara 11000 dan 13000. Excel rata-rata dua nilai untuk mengembalikan 12000.
Untuk memulakan, pilih satu sel dalam data anda dan tekan Ctrl + T untuk memformat data sebagai jadual.
Kemudian, pilih Insert, Pivot Table. Dalam dialog Insert PivotTable, pilih kotak untuk Tambah Data Ini ke Model Data.
Di Medan Pivot Table, pilih Wilayah dan Daerah. Tetapi jangan pilih Jualan. Sebaliknya, klik kanan pada tajuk Jadual dan pilih Ukuran Baru. "Ukur" adalah nama mewah untuk bidang yang dikira. Langkah lebih kuat daripada medan yang dikira dalam jadual pangsi biasa.
Dalam dialog Define Measure, isikan empat entri yang ditunjukkan di bawah:
- Ukur Nama: Median Jualan
- Formula
=MEDIAN((Sales)) - Format nombor: Nombor
- Tempat Perpuluhan: 0
Setelah membuat ukuran, ia ditambahkan ke senarai medan, tetapi anda harus memilih entri untuk menambahkannya ke kawasan Nilai pada jadual pangsi. Seperti yang anda lihat di bawah, jadual pangsi mengira median dengan betul.
Tonton Video
Transkrip Video
Belajar Excel dari Podcast, Episode 2197: Median dalam Jadual Pangsi.
Saya mesti memberitahu anda, saya sangat teruja dengan perkara ini. ketika saya di Houston melakukan seminar Power Excel di sana, dan Alex mengangkat tangannya, berkata, "Hei, adakah cara untuk melakukan median di meja pangsi?" Tidak, anda tidak boleh melakukan median di meja pangsi. Saya masih ingat 25 tahun yang lalu, rakan baik saya, Juan Pablo Gonzales, sebenarnya membawa makro untuk melakukan yang sama dengan median, tidak menggunakan jadual pangsi - itu tidak mungkin.
Tetapi saya mempunyai percikan api. Saya berkata, "Tunggu sebentar," dan saya membuka penyemak imbas, dan saya mencari "DAX median", dan, tentu saja, terdapat fungsi MEDIAN di DAX, yang bermaksud bahawa kita dapat menyelesaikan masalah ini.
Baiklah, jadi, untuk menggunakan DAX, anda mesti berada di Excel 2013 atau Excel 2016, atau di Excel 2010, dan mempunyai tambahan Power Pivot; anda tidak perlu mempunyai tab Power Pivot, kita hanya perlu mempunyai Model Data. Baiklah? Oleh itu, saya akan memilih data ini, saya akan membuatnya menjadi jadual dengan Ctrl + T, dan mereka memberikan nama yang tidak membayangkan "Jadual 4". Dalam kes anda, mungkin "Jadual 1". Dan kami akan memasukkan Jadual Pangsi, menambahkan data ini ke Model Data, klik OK, dan kami akan memilih Wilayah di sebelah kiri, tetapi bukan Penjualan. Sebagai gantinya, saya ingin membuat pengukuran yang dikira baru. Oleh itu, saya datang ke sini ke meja dan saya klik kanan dan berkata, Tambah Ukur. Dan ukuran ini akan disebut "Median of Sales", dan formula akan menjadi: = MEDIAN. Tekan tab di sana dan pilih Jualan,menutup kurungan. Saya boleh memilih ini sebagai nombor dengan tempat perpuluhan sifar, menggunakan pemisah seribu dan klik OK. Dan, mari kita lihat, medan baru kita ditambahkan di sini, kita mesti menandakannya untuk menambahkannya, dan mengatakan median untuk Midwest adalah 12,000.
Sekarang mari kita periksa. Jadi, di sini, seluruh Midwest, median keseluruhan set data antara 11,000 dan 13,000. Oleh itu, rata-rata 11 dan 13, itulah yang dilakukan oleh median dalam Excel biasa. Sekarang mari kita tambahkan daerah dan ia mengatakan bahawa median Chicago sebanyak 5,000, median Cleveland adalah 17,000; Jumlah pertengahan dan keseluruhan 12,000 Semua itu betul. Betapa hebatnya itu? Akhirnya, median dalam jadual pangsi, berkat medan atau ukuran yang dikira, seperti yang disebut, dan Model Data.
Sekarang, banyak petua kegemaran saya - petua untuk seminar Power Excel langsung saya - dalam buku ini LIVe, 54 Petua Terhebat Sepanjang Masa. Klik "Saya" di sudut kanan atas untuk membaca lebih lanjut mengenai buku ini.
Baiklah. Penutup: Bolehkah anda melakukan median di meja pangsi? Oh tidak, ya, ya anda boleh, terima kasih kepada Model Data. Anda boleh membuat median; Ctrl + T untuk menjadikan data anda menjadi jadual; Masukkan Jadual Pangsi; dan tandakan kotak itu, Tambahkan data ini ke Model Data; klik kanan nama jadual dan pilih ukuran baru, dan ukurannya adalah = MEDIAN ((Jualan)). Ia mengagumkan.
Terima kasih kepada Alex kerana hadir dalam seminar saya dan mengemukakan soalan itu, terima kasih kepada anda kerana berhenti. Sampai jumpa lagi untuk siaran net lain dari.
Muat turun Fail Excel
Untuk memuat turun fail excel: pivot-table-median.xlsx
