サンプル データを含む添付画像を参照して、複数の行の日付、人物、メモを 1 行に結合し、結合した値のセットごとに改行を入れたいと思います。各メモは名前と ID が空白の行を占めるため、値を含むメイン行の下のすべての行を結合する必要があります。データと必要な出力については、添付画像を参照してください。
サンプルデータ:
+-------+--------------+------------------+---------+-----------------------+
| Name | ID | Date | Person | Notes |
+-------+--------------+------------------+---------+-----------------------+
| Danny | UID11224512 | 11/5/2019 | Joe | <p>Note for Danny</p> |
| | | 11/5/2019 | John | <p>Note 2</p> |
| Joe | UID11224956 | 11/5/2019 18:54 | Someone | <p>Note 1</p> |
| | | 11/5/2019 18:54 | Someone | <p>Note 2</p> |
| Jane | UID11224959 | 11/5/2019 18:54 | Danny | <p>Note 1 </p> |
| | | 11/5/2019 17:33 | Shawn | <p>Note 3</p> |
| | | 11/6/2019 18:54 | Jane | <p>Note 2</p> |
| Tom | UID115466652 | | | |
| Eric | UID168998955 | | | |
| Paula | UID166559885 | | | |
| Frank | UID112249569 | 11/5/2019 18:54 | Someone | <p>Note 1</p> |
| | | 11/6/2019 18:54 | Someone | <p>Note 2</p> |
| | | 11/7/2019 18:54 | Someone | <p>Note 3</p> |
| | | 11/8/2019 18:54 | Someone | <p>Note 4</p> |
| | | 11/9/2019 18:54 | Someone | <p>Note 5</p> |
| | | 11/10/2019 18:54 | Someone | <p>Note 6</p> |
| Paul | UID1665588 | | | |
+-------+--------------+------------------+---------+-----------------------+
望ましい出力:
+-------+--------------+-----------------------------------+
| Name | ID | Notes |
+-------+--------------+-----------------------------------+
| Danny | UID11224512 | "11/5/2019 (Joe) - Note for Danny |
| | | 11/5/2019 (John) - Note 2" |
| Joe | UID11224956 | "11/5/2019 (Someone) - Note 1 |
| | | 11/5/2019 (Someone) - Note 2" |
| Jane | UID11224959 | "11/5/2019 (Danny) - Note 1 |
| | | 11/5/2019 (Shawn) - Note 2 |
| | | 11/5/2019 (Jane) - Note 3" |
| Tom | UID115466652 | |
| Eric | UID168998955 | |
| Paula | UID166559885 | |
| Frank | UID112249569 | "11/5/2019 (Someone) - Note 1 |
| | | 11/6/2019 (Someone) - Note 2 |
| | | 11/7/2019 (Someone) - Note 3 |
| | | 11/8/2019 (Someone) - Note 4 |
| | | 11/9/2019 (Someone) - Note 5 |
| | | 11/10/2019 (Someone) - Note 6" |
| Paul | UID1665588 | |
+-------+--------------+-----------------+---------+-----------------------------------+
答え1
これを行う 1 つの方法は、Windows Excel 2010 以降および Office 365 で利用可能な Power Query を使用することです。
- データテーブル内のセルを選択します
Data => Get&Transform => from Table/Range
- Power Query UI の場合:
Home => Advanced Editor
- 2行目のテーブル名をメモします。
- 以下のMCodeをウィンドウに貼り付けます
- 2行目のテーブル名を実際のテーブル名に変更します。
- コードとコメントを読み、
Applied Steps
ウィンドウを調べて何が起こっているかを理解します。
簡単なアルゴリズム
Fill down
名前とIDの列の空白Group
名前とIDの列- 抽出する初め日付、人物、メモを別々の列に表示します
- 最後の列のすべての日付/人物/メモ(改行で区切られている)を結合します
Mコード
以下のコードでは、2行目のテーブル名を実際のテーブル名に変更してください。
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"ID", type text}, {"Date", type datetime}, {"Person", type text}, {"Notes", type text}}),
//Fill down to fill in the blanks for Name and ID columns
#"Filled Down" = Table.FillDown(#"Changed Type",{"Name", "ID"}),
//Group on Name and ID -- should be the same so this is a double check.
#"Grouped Rows" = Table.Group(#"Filled Down", {"Name", "ID"}, {
//For each Name/ID
//Extract first Date as a date string if it has no time component, otherwise leave it as a datetime
{"Date", each if DateTime.Time([Date]{0}) = #time(0,0,0)
then DateTime.ToText([Date]{0},"MM/dd/yy")
else [Date]{0}},
//Extract first Person
{"Person", each [Person]{0}},
//extract first Note
{"Notes.1", each [Notes]{0}},
//Extract and combine, for each note, the Date, person, and note
// Then split the combination for each entry by linefeed.
{"Notes.2", (t)=> Text.Combine(
List.Generate(() => [Count=1,
dt =DateTime.ToText(t[Date]{0},"MM/dd/yyyy") & " (" &
t[Person]{0} &
") - " & t[Notes]{0}],
each [Count] <= Table.RowCount(t),
each [Count = [Count] + 1, dt = DateTime.ToText(t[Date]{[Count]},"MM/dd/yyyy") &
" (" & t[Person]{[Count]} &
") - " & t[Notes]{[Count]}],
each [dt]),"#(lf)")
}
})
in
#"Grouped Rows"
Notes.2
Excelでは、列を にWrap Text
、Date
列を にフォーマットする必要がありますmm/dd/yyyy hh:mm
(日付列では、時間のない日付はテキスト文字列として返されるため、同じ形式にはなりません(これも質問に示されているとおりです)。)
答え2
コメント列全体に対して、[ホーム] リボンから [テキストの折り返し] を必ず設定してください。次の数式を使用して、テキスト値を改行と結合します。
="comment1" & CHAR(10) & "Comment2"
2 つの別々のテーブルを結合する場合は、Excel の Power Query を調べる価値があるかもしれません。