HOME > Q&A > Excel （一般機能）

Excel （一般機能）
Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 11 Home : Excel 2019)
複数shiftの集計方法
投稿日時: 26/02/22 21:08:41
投稿者: 桃太郎７

宜しくお願いします。
脳トレ麻雀の個人別点数の集計がしたいです。
shift1 ２月
　A    B    C    D
1 NO    ID    名前    合計
2 1    4    D    557    shift1 ２月のID別集計を求める
3 2    2    B    568    shift1とshift2のID別集計を
4 3    1    A    550    shift3に合算したいです。
5 4    11    K    905    ID登録は30名です。
6 5    12    L    361
7 6    14    N    580
8 7    6    F    705
9 8    8    H    265
shift2 ３月
1 NO    ID    名前    合計
2 1    2    B    540
3 2    1    A    655
4 3    11    K    460
5 4    14    N    585
6 5    19    S    525
7 6    24    X    605
8 7    3    C    500
9 8    6    F    510    
 
年間の実績を個人別に集計したいです。
よろしくお願いいたします。

回答
投稿日時: 26/02/23 12:06:23
投稿者: メジロ

メニュー「データ」の「統合」機能を使ってはどうですか。
２つのシートに同じIDがなくても合計が求まります。

投稿日時: 26/02/24 06:44:27
投稿者: 桃太郎７

メジロ さんの引用:
メニュー「データ」の「統合」機能を使ってはどうですか。
２つのシートに同じIDがなくても合計が求まります。

早速のご回答ありがとうございます。
＞メニュー「データ」の「統合」機能＜
恐れ入りますが、数式ではどの様に記述したらよろしいでしょうか？
詳しくご指導下さい。
お願いします。
回答
投稿日時: 26/02/24 07:05:12
投稿者: んなっと

とりあえず一つのシートにまとめましょう。
  
●Sheet1(2月)
  
　下の範囲を選択して[テーブルとして書式設定]
→お好みの物を選択
→[先頭行をテーブルの見出しとして使用する]　にチェック
→[OK]
  
　　 A　 B　　 C　　 D
 1　NO　ID　名前　合計
 2　 1　 4　　 D　 557
 3　 2　 2　　 B　 568
 4　 3　 1　　 A　 550
 5　 4　11　　 K　 905
 6　 5　12　　 L　 361
 7　 6　14　　 N　 580
 8　 7　 6　　 F　 705
 9　 8　 8　　 H　 265
10　 9　 1　　 A　 300
11　10　11　　 K　 400
12　11　12　　 L　 500
13　12　14　　 N　 600
  
●Sheet2(3月),Sheet3(4月)なども同様に、データ範囲を[テーブルとして書式設定]
  
　　 A　 B　　 C　　 D
 1　NO　ID　名前　合計
 2　 1　 2　　 B　 540
 3　 2　 1　　 A　 655
 4　 3　11　　 K　 460
 5　 4　14　　 N　 585
 6　 5　19　　 S　 525
 7　 6　24　　 X　 605
 8　 7　 3　　 C　 500
 9　 8　 6　　 F　 510
10　 9　11　　 K　 100
11　10　14　　 N　 200
12　11　19　　 S　 300
13　12　24　　 X　 400
  
●[データ]→[データの取得]→[その他のデータソースから]→[空のクエリ]
→[詳細エディター]
→以下を貼り付け
  
let
    fnm = Excel.CurrentWorkbook(),
    flt = Table.SelectRows(fnm, each Text.StartsWith([Name], "テーブル")),
    trs = Table.TransformColumns(flt,{"Name", each Number.FromText(Text.Select(_, {"0".."9"}))}),
    ren = Table.RenameColumns(trs,{{"Name", "シート連番"}}),
    exp = Table.ExpandTableColumn(ren, "Content", Table.ColumnNames(ren[Content]{0})),
    del = Table.RemoveColumns(exp,{"NO"}),
    grp = Table.Group(del, {"シート連番", "ID", "名前"}, {{"総計", each List.Sum([合計]), type number}}),
    srt = Table.Sort(grp,{{"シート連番", Order.Ascending}, {"ID", Order.Ascending}})
in
    srt
  
→エラーがなくなれば　[閉じて読み込む]
  
　　　　　　 A　 B　　 C
 1　シート連番　ID　総計
 2　　　　　 1　 1　 850
 3　　　　　 1　 2　 568
 4　　　　　 1　 4　 557
 5　　　　　 1　 6　 705
 6　　　　　 1　 8　 265
 7　　　　　 1　11　1305
 8　　　　　 1　12　 861
 9　　　　　 1　14　1180
10　　　　　 2　 1　 655
11　　　　　 2　 2　 540
12　　　　　 2　 3　 500
13　　　　　 2　 6　 510
14　　　　　 2　11　 560
15　　　　　 2　14　 785
16　　　　　 2　19　 825
17　　　　　 2　24　1005
  
まずはここまで。無理だったらごめんなさい。
回答
投稿日時: 26/02/24 09:53:20
投稿者: ゆーたん 

	A	B	C	D	E	F	G
1	NO	ID	名前	合計		ID	総合計
2	1	4	D	557		1	550
3	2	2	B	568		2	668
4	3	1	A	550		30	0
5	4	11	K	905			
6	5	12	L	361			
7	6	14	N	580			
8	7	6	F	705			
9	8	8	H	265			
10	9	2	B	100
F列にIDを入力しておいて、G2セルに、
=SUMIF(B:B,F2,D:D)
下フィルコピー。みたいなかんじでどうかしら？(^_^;)
回答
投稿日時: 26/02/24 11:48:18
投稿者: Suzu

shift は sheet だとして。。
 
私も統合機能を使いますかね。。
機能なので、数式はありません。
 
1. sheet3 の A1 選択
2. 「データ」タブ- データツール内「統合」クリック
3. 統合の設定 で
　　集計の種類 「合計」
　　統合元範囲 の 入力部分(参照の左側)にカーソルを置き
　　　sheet 2月 の B～D列を選択 すると、入力部分に '2月'!$B:$D となりますので「追加」
　　　統合元 に、「'2月'!$B:$D 」 が追加されます。
　　その状態から、sheet 3月を選ぶと入力部分が「'3月'!$B:$D」となりますので「追加」
　　　統合元 に、「'3月'!$B:$D 」 が追加されます。
　　　　：
　　　　必要シート分 繰り返し
　　統合の基準「上端行」「左端列」両方にチェックを入れ 「OK」
4. 

	名前	合計
4		557
2		1108
1		1205
11		1365
12		361
14		1165
19		525
24		605
3		500
6		1215
8		265

を得られます。
 
必要に応じて
A1 に 「ID」 を入力、
B2 に 「名前」を得る式・・・ ちょっと面倒・・
 
「ID」毎 でなく、「名前」毎 で集計して良いなら 統合元 の選択の所で
　'2月'!$B:$D ではなく、'2月'!$C:$D の様に、C・D列を対象とすれば
 
	合計
D	557
B	1108
A	1205
K	1365
L	361
N	1165
S	525
X	605
C	500
F	1215
H	265

が得られます。
回答
投稿日時: 26/02/24 17:15:31
投稿者: んなっと

最後に下のようにF列やH列にIDを入力しておきましょう。
2月がシート連番1に、3月がシート連番2に対応するので、例えば3月までの累計は下のI列ようになります。
 
　　　　　　 A　 B　　 C　　 D　E　　　F　　 G　　　H　　 I
 1　シート連番　ID　名前　総計　　 ～2月　　 1　～3月　　 2
 2　　　　　 1　 1　　 A　 850　　　　 1　 850　　　1　1505
 3　　　　　 1　 2　　 B　 568　　　　 2　 568　　　2　1108
 4　　　　　 1　 4　　 D　 557　　　　 3　　 0　　　3　 500
 5　　　　　 1　 6　　 F　 705　　　　 4　 557　　　4　 557
 6　　　　　 1　 8　　 H　 265　　　　 5　　 0　　　5　　 0
 7　　　　　 1　11　　 K　1305　　　　 6　 705　　　6　1215
 8　　　　　 1　12　　 L　 861　　　　 7　　 0　　　7　　 0
 9　　　　　 1　14　　 N　1180　　　　 8　 265　　　8　 265
10　　　　　 2　 1　　 A　 655　　　　 9　　 0　　　9　　 0
11　　　　　 2　 2　　 B　 540　　　　10　　 0　　 10　　 0
12　　　　　 2　 3　　 C　 500　　　　11　1305　　 11　1865
13　　　　　 2　 6　　 F　 510　　　　12　 861　　 12　 861
14　　　　　 2　11　　 K　 560　　　　13　　 0　　 13　　 0
15　　　　　 2　14　　 N　 785　　　　　　　　　　　　　　　
16　　　　　 2　19　　 S　 825　　　　　　　　　　　　　　　
17　　　　　 2　24　　 X　1005　　　　　　　　　　　　　　　
 
G2
=SUMIFS(クエリ1[総計],クエリ1[シート連番],"<="&G$1,クエリ1[ID],F2)
下方向・↓
I2
=SUMIFS(クエリ1[総計],クエリ1[シート連番],"<="&I$1,クエリ1[ID],H2)
下方向・↓

回答
投稿日時: 26/02/26 09:35:51
投稿者: んなっと

シート名がSheet1,Sheet2,...と規則的な場合、INDIRECT関数を使って現在のシートまでの累計を得ることもできます。しかしサイトのセキュリティー対策の不具合で、G1の式を一部置き換えないと書き込み出来ません。リプレイスはREPLACEに変更してください。
 
●Sheet1
 
　　 A　 B　　 C　　 D　E　 F　　　 G
 1　NO　ID　名前　合計　　 ID　Sheet0
 2　 1　 4　　 D　 557　　　1　　 850
 3　 2　 2　　 B　 568　　　2　　 568
 4　 3　 1　　 A　 550　　　3　　　 0
 5　 4　11　　 K　 905　　　4　　 557
 6　 5　12　　 L　 361　　　5　　　 0
 7　 6　14　　 N　 580　　　6　　 705
 8　 7　 6　　 F　 705　　　7　　　 0
 9　 8　 8　　 H　 265　　　8　　 265
10　 9　 1　　 A　 300　　　9　　　 0
11　10　11　　 K　 400　　 10　　　 0
12　11　12　　 L　 500　　 11　　1305
13　12　14　　 N　 600　　 12　　 861
14　　　　　　　　　　　　 13　　　 0
 
G1
="Sheet"&リプレイス(CELL("filename",F2),1,FIND("]",CELL("filename",F2))+LEN("Sheet"),)-1
G2
=SUMIF(B:B,F2,D:D)+IFERROR(INDIRECT(G$1&"!RC",FALSE),)
 
●Sheet2　上のF:G列をそのままコピー貼り付け
 
　　 A　 B　　 C　　 D　E　 F　　　 G
 1　NO　ID　名前　合計　　 ID　Sheet1
 2　 1　 2　　 B　 540　　　1　　1505
 3　 2　 1　　 A　 655　　　2　　1108
 4　 3　11　　 K　 460　　　3　　 500
 5　 4　14　　 N　 585　　　4　　 557
 6　 5　19　　 S　 525　　　5　　　 0
 7　 6　24　　 X　 605　　　6　　1215
 8　 7　 3　　 C　 500　　　7　　　 0
 9　 8　 6　　 F　 510　　　8　　 265
10　 9　11　　 K　 100　　　9　　　 0
11　10　14　　 N　 200　　 10　　　 0
12　11　19　　 S　 300　　 11　　1865
13　12　24　　 X　 400　　 12　　 861
14　　　　　　　　　　　　 13　　　 0 3
投稿日時: 26/02/26 10:23:07
投稿者: 桃太郎７

んなっとさんを始め皆さんからのご回答ありがとうございました。
私には、初めての内容ですが何とか挑戦をします。
皆さんありがとうございました。