Kuasa Kuasa: Berurusan dengan Pelbagai Tajuk Sama - Petua Excel

Isi kandungan

Catatan

Ini adalah salah satu rangkaian artikel yang memperincikan penyelesaian yang dihantar untuk cabaran Podcast 2316.

Dalam masalah pembentukan data asal saya, saya menghadapi masalah pada awal proses. Data yang masuk akan memiliki banyak lajur dengan judul Q1.

Banyak lajur

Dalam penyelesaian saya, saya membuat julat bernama "UglyData" dan mengimportnya ke Power Query. Ini menyebabkan hasil Power Query yang tidak berpuas hati mengubah nama lajur saya menjadi Q1_1.

Lajur dinamakan semula

Kemudian, setelah membatalkan pungutan suara, saya terpaksa mengekstrak dua watak kiri dari tajuk tersebut.

Terdapat tiga penyelesaian berasingan untuk masalah ini:

  • Wyn Hopkins dan Demote Header
  • MF Wong dan hapus centang My Table Has Headers (juga dicadangkan oleh Peter Bartholomew)
  • Jason M dan hapus Header yang Dipromosikan (juga dicadangkan oleh Ondřej Malinský dan Excel MVP John MacDougall)

Inovasi pertama adalah dari Wyn Hopkins di Access Analytic. Daripada julat yang dinamakan, Wyn menukar data ke tabel menggunakan Ctrl + T. Pada ketika ini, kerosakan pada tajuk telah dilakukan, kerana Excel mengubah tajuk menjadi:

Ditukar kepada jadual: Ctrl + T

Setelah Wyn memasukkan data ke Power Query, dia kemudian membuka menu drop-down Use First Row as Headers dan memilih Use Headers sebagai First Row. Saya tidak pernah menyedari bahawa ini ada di sana. Ini membuat langkah yang disebut Table.DemoteHeaders.

Gunakan pengepala sebagai baris pertama

Tetapi, walaupun dengan peningkatan Wyn, dia masih perlu mengekstrak 2 watak pertama dari tajuk tersebut.

Inovasi kedua adalah teknik MF Wong. Semasa dia membuat jadual, dia mencentang Jadual Saya Mempunyai Header!

Jadual saya mempunyai tajuk

Ini memastikan bahawa Excel meninggalkan banyak tajuk Q1 sahaja dan tidak perlu mengeluarkan akhiran tambahan kemudian.

Pelbagai tajuk Q1

Saya faham ada orang di kem "Saya suka meja". Video MF Wong menunjukkan bagaimana dia dapat menambah pekerja baru di sebelah kanan data dan jadualnya berkembang secara automatik. Terdapat banyak alasan yang baik untuk menggunakan jadual.

Tetapi, kerana saya suka Subtotal, Paparan Tersuai, dan Tapis berdasarkan Pilihan, saya cenderung untuk tidak menggunakan Jadual. Oleh itu, saya menghargai penyelesaian daripada Jason M. Dia menyimpan data sebagai rangkaian UglyData yang dinamakan. Sebaik sahaja dia mengimport data ke Power Query, dia menghapus dua langkah berikut:

Langkah dihapuskan

Sekarang, dengan data hanya di Baris 1, tidak ada kerumitan yang mempunyai banyak lajur yang disebut Q1.

Banyak lajur Q1

Berikut adalah kod Wyn Hopkin yang menunjukkan Demotemeaders

let Source = Excel.CurrentWorkbook()((Name="Table1"))(Content), #"Demoted Headers1" = Table.DemoteHeaders(Source), #"Transposed Table1" = Table.Transpose(#"Demoted Headers1"), #"Added Custom" = Table.AddColumn(#"Transposed Table1", "Custom", each if Text.Start((Column1),1) = "Q" then null else (Column1)), #"Filled Down" = Table.FillDown(#"Added Custom",("Custom")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ((Custom) "Dept. Total")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.StartsWith((Column1), "Employee")), #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows1", (PromoteAllScalars=true)), #"Extracted First Characters" = Table.TransformColumns(#"Promoted Headers", (("Category Description", each Text.Start(_, 2), type text))), #"Reordered Columns" = Table.ReorderColumns(#"Extracted First Characters",("Category Description_1", "Category Description", "Administrative", "Holiday", "PTO/LOA/Jury Duty", "Project A", "Project B", "Project C")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Category Description_1", "Category Description"), "Attribute", "Value"), #"Reordered Columns1" = Table.ReorderColumns(#"Unpivoted Other Columns",("Category Description_1", "Attribute", "Category Description", "Value")), #"Pivoted Column" = Table.Pivot(#"Reordered Columns1", List.Distinct(#"Reordered Columns1"(#"Category Description")), "Category Description", "Value", List.Sum), #"Reordered Columns2" = Table.ReorderColumns(#"Pivoted Column",("Attribute", "Category Description_1", "Q1", "Q2", "Q3", "Q4")), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns2",(("Attribute", "Cat Deasc"), ("Category Description_1", "Emp Name"))), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",(("Emp Name", type text), ("Q1", Int64.Type), ("Q2", Int64.Type), ("Q3", Int64.Type), ("Q4", Int64.Type))), #"Inserted Sum" = Table.AddColumn(#"Changed Type", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), Int64.Type) in #"Inserted Sum"

Kembali ke halaman utama untuk cabaran Podcast 2316.

Baca artikel seterusnya dalam siri ini: Kuasa Kuasa: Hapus ini, Hapus itu, atau hapus apa-apa ?.

Artikel menarik...