【Excelの集計について質問です。】

【Excelの集計について質問です。】

・質問内容
[表①]から[表④]に集計を簡単に行いたいです。

・現在の方法
[表①]からピボットテーブルにて[表②]と[表③]を作ったあと、
ピボットテーブルウイザードで[表②]と[表③]を一つにして[表④]を
作成しています。

・条件
1.[表①]の形式を崩さないで下さい。(例)[表⑤]などはNG。
2.使用Excelは、Office2010または2013
3.使用可能なソフトExcel,POWER QUERY,Power BI

恐れ入りますが、ご教授をお願い致します。

No.7 回答者： bonaron 回答日時： 2022/12/05 06:14

VBA でも良ければ。

Sub Sample()
Dim cn As Object
Dim rs As Object
Dim strSQL As String

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

'ADO接続
cn.Provider = "Microsoft.ACE.OLEDB.12.0"
cn.Properties("Extended Properties") = "Excel 12.0"
cn.Open ThisWorkbook.FullName
'
'SQL文の作成
strSQL = "SELECT T.名前, Count(*) AS [人数] "
strSQL = strSQL & "FROM ("
strSQL = strSQL & "SELECT 名前1 AS [名前] FROM [元データ$] "
strSQL = strSQL & "UNION ALL "
strSQL = strSQL & "SELECT 名前2 FROM [元データ$] "
strSQL = strSQL & ") AS T "
strSQL = strSQL & "GROUP BY 名前 "

'SQL文の実行
rs.Open strSQL, cn

'取得した Recordse をシートに貼り付け
With Worksheets("結果")
.Range("A1:B1").Value = Array("名前", "人数")
.Range("A2").CopyFromRecordset rs
.Range("A:B").Sort key1:=Range("A1"), Header:=xlYes, SortMethod:=xlPinYin
End With

'後始末
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

No.6 回答者： goomania 回答日時： 2022/12/04 23:45

直接の回答ではなく、大変失礼とは存じますが、ご質問者の掲示された表の内容で不明な点があります。

まず表④の数値の見出しに「人数」と表示されているところから推測すると、作成目的は各苗字が幾つの都道府県で登場するかを数えるということではなく、各苗字について同じ苗字の人が何人いるかという集計表という理解でよいでしょうか?

だとすると、表①は名前①、名前➁は1セルが1名と考えられるので、

出身都道府県__名前①__名前➁
北海道_______佐藤____高橋
東京都_______鈴木____佐藤
京都府_______田中____山本
大阪府_______田中____山本

のように作成されていますが、

出身都道府県__名前①__名前➁
北海道_______佐藤____佐藤
東京都_______鈴木____鈴木
・・・・・・・・・・・・・・・

のような表になることもあるということでよいのでしょうか?

また、表①では名前①、名前➁の2列が表示されていますが、出身都道府県によっては3列以上になることもあるということでよろしいでしょうか?

この場合、行数、列数ともに不定となり、関数での処理は難しいと思われますので、power queryを利用することになると思いますが、既に回答されている皆さんの方法は、名前①、名前➁の2列のみの場合の対応方法だと思われます。

この辺りを補足していただかないと正しい回答は得られないと思います。

No.5 回答者： fujillin 回答日時： 2022/12/04 00:46

こんばんは

ピボットテーブルはあまり使ったことがないので、関数での回答でも宜しければ・・

実際には、No4様の回答のように作業列を用いる方が、関数式も長いものにはならないですみますし、無駄な計算を行わないので現実的な方法だと思います。
とは言え、ここでご質問をなさる方は一発で算出できる方法を求めていることが多いようにも思いますので、以下はその様な方法の一例です。

＞Office2010または2013
とのことですので、UNIQUE関数なども使えないでしょうから、ローテクになってしまいますが・・

添付図では、E2セルに
=IFERROR(INDEX($A:$C,INT(AGGREGATE(15,6,(ROW($B$2:$C$50)*10+COLUMN($B$2:$C$50))/(COUNTIF(OFFSET($B$1,,,ROW($B$2:$C$50)),$B$2:$C$50)+COUNTIF(OFFSET($C$1,,,ROW($B$1:$C$49)+COLUMN($A$1:$B$49)-1),$B$2:$C$50)=1),ROW(A1))/10),MOD(AGGREGATE(15,6,(ROW($B$2:$C$50)*10+COLUMN($B$2:$C$50))/(COUNTIF(OFFSET($B$1,,,ROW($B$2:$C$50)),$B$2:$C$50)+COUNTIF(OFFSET($C$1,,,ROW($B$1:$C$49)+COLUMN($A$1:$B$49)-1),$B$2:$C$50)=1),ROW(A1)),10)),"")

F2セルに
=IF(E2="","",COUNTIF($B$2:$C$50,E2))

の式を入力し、E2:F2を下方に適当なところまでフィルコピーしてあります。

※ 名前の列挙順がご提示のものとは異なりますが、元の表中の出現順になります。

No.4 回答者： mike_g 回答日時： 2022/12/03 22:39

[No.3]の単なる補足、

範囲 G:H のリストを、例えば、ここの添付図の範囲 K:L のリスト(数値の昇順かつ出身地の北→南の順に)のように並べ変えるのも朝飯前にできますよ。

No.3 回答者： mike_g 回答日時： 2022/12/03 20:52

[No.2お礼]へのコメント、

》 マスターの表である[表①]を崩す事なく、
》 直接[表①]から[表④]へ導く事
は、浅学な私には無理難題ですが、貴方が不要な[表⑤]からなら、
[表④]と同じ情報がイトも簡単に得られるけど、それでは駄目よダメダメですか？
添付図参照(Excel 2019)
￣￣￣￣￣￣￣￣￣￣￣↓↓↓↓↓↓↓↓↓↓↓↓↓

No.2 回答者： mike_g 回答日時： 2022/12/03 18:37

[No.1]への補足、

》 (例)[表⑤]などはNG。
えｯ？！
そのコメント、見落としてました。
失礼しヤしたｯ！m(_._)m

でも、貴方が求める集計になってもいない、
余計な[表⑤]を載せた理由は何だったンですか？

この回答へのお礼

質問へのご回答ありがとうございます。

誤解を招く記載で申し訳ありません。

[表⑤]を記載した理由は、マスターの表である[表①]から[表⑤]へ
作成してからピボットテーブルで[表④]を導く事は容易に出来ますが、
マスターの表である[表①]を崩す事なく、直接[表①]から[表④]へ
導く事が出来ればと思っております。

お礼日時：2022/12/03 19:17

No.1 回答者： mike_g 回答日時： 2022/12/03 18:29

添付図参照(Excel 2019)

式 =OFFSET(A$2,(ROW(A1)-1)/2,MOD(ROW(A1)-1,2)*(COLUMN(A1)-1))
を入力したセル E2 を右隣り＆下方にズズーッとオートフィル