Tabel pivot apik kanggo kabeh wong - padha ngetung kanthi cepet, lan dikonfigurasi kanthi fleksibel, lan desain bisa dicithak kanthi elegan, yen dibutuhake. Nanging ana uga sawetara fly ing ointment, utamané, kasekengan kanggo nggawe ringkesan, ngendi area Nilai ngirim ora ngemot nomer, nanging teks.
Ayo nyoba ngatasi watesan iki lan nggawe "saperangan kruk" ing kahanan sing padha.
Upaminipun perusahaan kita ngangkut produk ing kontaner menyang sawetara kutha ing Negara Kita lan Kazakhstan. Kontainer dikirim ora luwih saka sapisan sasi. Saben wadhah duwe nomer alfanumerik. Minangka data awal, ana tabel pangiriman dhaptar standar, saka ngendi sampeyan kudu nggawe sawetara ringkesan supaya bisa ndeleng kanthi jelas jumlah kontaner sing dikirim menyang saben kutha lan saben wulan:
Kanggo penak, ayo nggawe tabel kanthi data awal "pinter" sadurunge nggunakake printah Ngarep - Format minangka tabel (Ngarep - Format minangka Tabel) lan menehi jeneng Pangiriman tab Constructor (Desain). Ing mangsa ngarep, iki bakal nyederhanakake urip, amarga. bakal bisa nggunakake jeneng tabel lan kolom langsung ing rumus.
Cara 1. Paling gampang - nggunakake Power Query
Power Query minangka alat super kuat kanggo mbukak lan ngowahi data ing Excel. Add-in iki wis dibangun ing Excel minangka standar wiwit 2016. Yen sampeyan duwe Excel 2010 utawa 2013, sampeyan bisa ngundhuh lan nginstal kanthi kapisah (gratis).
Kabeh proses, kanggo kajelasan, aku nganalisa langkah demi langkah ing video ing ngisor iki:
Yen ora bisa nggunakake Power Query, sampeyan bisa nggunakake cara liya - liwat tabel pivot utawa rumus.
Cara 2. Ringkesan tambahan
Ayo nambah siji kolom maneh menyang tabel asli, ing ngendi nggunakake rumus prasaja kita ngetung nomer saben baris ing tabel:
Temenan, -1 dibutuhake, amarga kita duwe header siji-baris ing tabel kita. Yen meja sampeyan ora ana ing wiwitan lembar, sampeyan bisa nggunakake rumus universal sing rada rumit, nanging universal sing ngitung prabédan ing nomer baris saiki lan header tabel:
Saiki, kanthi cara standar, kita bakal nggawe tabel pivot saka jinis sing dikarepake adhedhasar data kita, nanging ing lapangan nilai kita bakal nyelehake lapangan Nomer garis tinimbang sing dikarepake Sing:
Amarga kita ora duwe sawetara kontaner ing kutha sing padha ing sasi sing padha, ringkesan kita bakal, nyatane, ora menehi jumlah, nanging nomer baris saka kontaner sing kita butuhake.
Kajaba iku, sampeyan bisa mateni grand lan subtotal ing tab Konstruktor - Total total и Subtotal (Desain - Total Total, Subtotal) lan ing panggonan sing padha ngalih ringkesan kanggo tata letak meja luwih trep karo tombol Laporan mockup (Laporan Tata letak).
Mangkono, kita wis setengah dalan kanggo asil: kita duwe tabel ing ngendi, ing persimpangan kutha lan sasi, ana nomer baris ing tabel sumber, ing ngendi kode wadhah sing kita butuhake.
Saiki ayo nyalin ringkesan (menyang sheet sing padha utawa liyane) lan tempel minangka nilai, banjur ketik rumus kita menyang area nilai, sing bakal ngekstrak kode wadhah kanthi nomer baris sing ditemokake ing ringkesan:
fungsi IF (Yen), ing kasus iki, priksa manawa sel sabanjure ing ringkesan ora kosong. Yen kosong, banjur metu string teks kosong "", yaiku ninggalake sel kosong. Yen ora kosong, banjur extract saka kolom wadhah tabel sumber Pangiriman isi sel miturut nomer baris nggunakake fungsi INDEKS (INDEX).
Mbok menawa titik mung sing ora ketok banget yaiku tembung dobel wadhah ing rumus. Bentuk tulisan sing aneh:
Persediaan[[Wadhah]:[Wadhah]]
... mung perlu kanggo referensi kolom wadhah ana Absolute (kaya referensi karo $ pratandha kanggo biasa "non-pinter" tabel) lan ora slip menyang kolom tetanggan nalika nyalin rumus kita ing sisih tengen.
Ing mangsa ngarep, nalika ngganti data ing tabel sumber Pangiriman, kita kudu ngelingi nganyari ringkesan tambahan karo nomer baris kanthi ngeklik tengen lan milih printah Nganyari & Simpen (Refresh).
Cara 3. Rumus
Cara iki ora mbutuhake nggawe tabel pivot penengah lan nganyari manual, nanging nggunakake "senjata abot" Excel - fungsi SUMMESLIMN (SUMIFS). Tinimbang nggoleki nomer baris ing ringkesan, sampeyan bisa ngetung kanthi nggunakake rumus iki:
Kanthi sawetara bulkiness eksternal, nyatane, iki minangka kasus panggunaan standar kanggo fungsi penjumlahan selektif SUMMESLIMNA sing jumlah nomer baris kanggo kutha tartamtu lan sasi. Maneh, amarga kita ora duwe sawetara kontaner ing kutha sing padha ing sasi sing padha, fungsi kita bakal, nyatane, ora menehi jumlah, nanging nomer baris dhewe. Lan banjur fungsi wis menowo saka cara sadurunge INDEKS Sampeyan uga bisa ngekstrak kode wadah:
Mesthine, ing kasus iki, sampeyan ora perlu mikir babagan nganyari ringkesan, nanging ing tabel gedhe, fungsi kasebut. SUMMESLI bisa noticeably alon. Banjur sampeyan kudu mateni nganyari rumus kanthi otomatis, utawa gunakake cara pisanan - tabel pivot.
Yen tampilan ringkesan ora cocog banget kanggo laporan sampeyan, mula sampeyan bisa ngekstrak nomer baris kasebut menyang tabel pungkasan ora langsung, kaya sing ditindakake, nanging nggunakake fungsi kasebut. GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Cara nindakake iki bisa ditemokake ing kene.
- Cara nggawe laporan nggunakake tabel pivot
- Cara nyiyapake petungan ing tabel pivot
- Ngitung selektif nganggo SUMIFS, COUNTIFS, lsp.