Daptar eusi
Pivot tabel anu alus for everyone - aranjeunna ngitung gancang, sarta ngonpigurasi flexibly, sarta desain bisa tatu kana eta elegantly, lamun diperlukeun. Tapi aya ogé sababaraha laleur dina salep, hususna, henteu mampuh nyieun kasimpulan, dimana aréa nilai teu kudu ngandung angka, tapi téks.
Hayu urang nyobian ngalingkungan watesan ieu sareng ngadamel "sababaraha kruk" dina kaayaan anu sami.
Anggap perusahaan urang ngangkut produkna dina peti ka sababaraha kota di Nagara Urang sareng Kazakhstan. Wadah dikirim henteu langkung ti sabulan sakali. Unggal wadahna gaduh nomer alpanumérik. Salaku data awal, aya tabel standar daptar pangiriman, ti mana anjeun kedah ngadamel sababaraha kasimpulan supados jelas ningali jumlah peti anu dikirim ka unggal kota sareng unggal bulan:
Pikeun genah, hayu urang ngadamel tabel sareng data awal "pinter" sateuacanna nganggo paréntah Imah - Format salaku méja (Imah - Format salaku Méja) sareng masihan nami anjeunna Ngirimkeun tab Constructor (Desain). Dina mangsa nu bakal datang, ieu bakal simplify hirup, sabab. bakal tiasa nganggo nami tabel sareng kolom na langsung dina rumus.
Métode 1. Panggampangna - ngagunakeun Power Query
Power Query mangrupikeun alat anu kuat pikeun ngamuat sareng ngarobih data dina Excel. Add-in ieu parantos diwangun kana Excel sacara standar saprak 2016. Upami Anjeun gaduh Excel 2010 atanapi 2013, Anjeun tiasa ngundeur tur masangkeunana misah (gratis pisan).
Sakabeh prosés, pikeun kajelasan, kuring dianalisis step by step dina video ieu:
Upami teu mungkin nganggo Power Query, anjeun tiasa nganggo cara anu sanés - ngalangkungan tabel pangsi atanapi rumus.
Métode 2. Ringkesan bantu
Hayu urang tambahkeun hiji kolom deui kana tabel aslina urang, dimana ngagunakeun rumus basajan urang ngitung jumlah unggal baris dina tabél:
Jelas, -1 diperlukeun, sabab urang boga lulugu hiji-garis dina tabel urang. Upami méja anjeun henteu aya dina awal lambaran, maka anjeun tiasa nganggo rumus anu rada rumit, tapi universal anu ngitung bédana dina nomer baris ayeuna sareng lulugu tabel:
Ayeuna, dina cara standar, urang bakal ngawangun tabel pangsi tina tipe nu dipikahoyong dumasar kana data urang, tapi dina widang nilai urang bakal leupaskeun sawah. Nomer garis tinimbang naon urang hayang wadah:
Kusabab urang teu boga sababaraha peti dina kotana sarua dina bulan anu sarua, kasimpulan urang bakal, kanyataanna, masihan kaluar moal jumlahna, tapi nomer garis tina peti kami butuh.
Salaku tambahan, anjeun tiasa mareuman grand sareng subtotal dina tab Konstruktor - total umum и Subtotal (Desain - Total Total, Subtotal) sarta di tempat anu sarua pindah kasimpulan ka perenah tabel leuwih merenah kalawan tombol Laporan mockup (Laporan Tata Letak).
Ku kituna, urang geus satengahna kana hasil: urang boga tabel dimana, di simpang kota jeung bulan, aya nomer baris dina tabel sumber, dimana kode wadahna urang kudu bohong.
Ayeuna hayu urang nyalin kasimpulan (kana lambaran anu sami atanapi anu sanés) sareng témpél salaku nilai, teras lebetkeun rumus kami kana daérah nilai, anu bakal nimba kode wadahna ku nomer garis anu aya dina kasimpulan:
fungsi IF (lamun), dina hal ieu, pariksa yén sél salajengna dina kasimpulan teu kosong. Upami kosong, teras kaluarkeun string téks kosong "", nyaéta ngantepkeun sél kosong. Upami henteu kosong, teras ekstrak tina kolom wadah tabél sumber Ngirimkeun eusi sél ku nomer baris ngagunakeun fungsi Indéks (INDEKS).
Panginten hiji-hijina titik anu teu jelas pisan di dieu nyaéta kecap ganda wadah dina rumus. Bentuk tulisan anu aneh sapertos kieu:
suplai[[Wadahna]:[Wadahna]]
... ngan diperlukeun pikeun rujukan kolom wadah éta mutlak (kawas rujukan kalawan $ tanda pikeun biasa "non-pinter" tabel) jeung teu dieunakeun kana kolom tatangga nalika nyalin rumus urang ka katuhu.
Dina mangsa nu bakal datang, nalika ngarobah data dina tabel sumber Ngirimkeun, urang kedah émut pikeun ngapdet kasimpulan bantu urang sareng nomer garis ku ngaklik katuhu kana éta sareng milih paréntahna Apdet & Simpen (Refresh).
Métode 3. Rumus
Metoda ieu henteu ngabutuhkeun nyiptakeun méja pangsi perantara sareng ngamutahirkeun manual, tapi nganggo "senjata beurat" Excel - fungsina. SUMMESLIMN (SUMIFS). Gantina milarian nomer baris dina kasimpulan, anjeun tiasa ngitung aranjeunna nganggo rumus ieu:
Kalayan sababaraha bulkiness éksternal, kanyataanna, ieu mangrupikeun kasus panggunaan standar pikeun fungsi penjumlahan selektif SUMMESLIMNA nu sums jumlah baris pikeun kotana jeung bulan dibikeun. Deui, saprak urang teu boga sababaraha peti dina kotana sarua dina bulan anu sarua, fungsi urang bakal, kanyataanna, masihan kaluar teu jumlah, tapi nomer garis sorangan. Lajeng fungsi geus akrab ti métode saméméhna Indéks Anjeun ogé tiasa nimba kode wadahna:
Tangtosna, dina hal ieu, anjeun henteu kedah deui mikir ngeunaan ngamutahirkeun kasimpulan, tapi dina tabel ageung, fungsina. SUMMESLI tiasa noticeably slow. Teras anjeun kedah mareuman ngapdet rumus otomatis, atanapi nganggo metode anu munggaran - tabel pangsi.
Upami penampilan kasimpulan henteu cocog pisan pikeun laporan anjeun, maka anjeun tiasa nimba nomer baris ti dinya kana tabel ahir henteu langsung, sapertos anu urang lakukeun, tapi nganggo fungsina. GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Kumaha ngalakukeun ieu tiasa dipendakan di dieu.
- Kumaha cara ngadamel laporan nganggo tabel pangsi
- Kumaha nyetél itungan dina tabel pangsi
- Cacah selektif sareng SUMIFS, COUNTIFS, jsb.