エクセルを使ってデータを振り分けたいのですが。。

質問

EXCEL2003のことで質問です。

現在、下記のようにならんでいるデータがあるのですが、
これをID毎に振り分け直したいのですが、
IDが文字数がバラバラのため、どのようにすればいいのか
上手い方法が見つかりません。

[現在]
ID/区分 　　個数
――――――――――――――
00000：区分AA 　　10
00000：区分B 　　20
12222：区分AA 　　11
12222：区分B 　　22
12222：区分D 　　44
222222：区分AA 　　5
222222：区分CCC 　　8

[最終]
ID/区分 合計　区分AA　区分B　区分CCC　区分D
――――――――――――――――――――――――――――
00000 　　30　　10　　　20
12222 　　77　　11　　　22　　　　　　　44
222222 　　13　　 5　　　8

最終的にはIF文とVLOOKUPの組み合わせになるのかな？と思うのですが、
左の文字列、右の区分名の文字数が全てバラバラの為、
予めLEFTなどで抜き出すことが出来ません。

このような場合は、どのようにすれば良いのでしょうか？
マクロはわからないので、エクセル関数での方法を教えていただけませんか？
よろしくお願いします。

回答No.6

mu2011

一例です。
■まず、元シート(SHEET1)のＡ列（ID/区分）を２列に変更します。
（１）Ｂ列選択→挿入→列
（２）Ａ列を選択→データ→区切り位置→次へ→その他をチェックし、：を入力→次へ→Ａ列のデータ形式を文字列をチェック→完了
（３）B1に任意文字を入力
■SHEET2にＩＤ列を抽出します。
（４）SHEET2のA1を選択→データ→フィルタ→フィルタオプションの設定→指定した範囲にチェック、リスト範囲欄にsheet1!$A:$A→抽出範囲欄にA1→重複するレコードは無視するをチェック→ＯＫ
■SHEET2に区分の見出し行を抽出します。
（５）SHEET2のB1を選択→データ→フィルタ→フィルタオプションの設定→指定した範囲にチェック、リスト範囲欄にsheet1!$B:$B→抽出範囲欄にB1→重複するレコードは無視するをチェック→ＯＫ
（６）B2以下の範囲をコピー→C1を選択し、形式を選択して貼り付け→行列を入れ替えるをチェック→ＯＫ
■SHEET2のＢ列を選択→delete→B1に合計を入力
■SHEET2のC2に以下の数式を設定し、縦横にコピー
=IF(SUMPRODUCT((Sheet1!$A$1:$A$8=$A2)*(Sheet1!$B$1:$B$8=C$1)),INDEX(Sheet1!$C:$C,SUMPRODUCT((Sheet1!$A$1:$A$8=$A2)*(Sheet1!$B$1:$B$8=C$1)*ROW(Sheet1!$A$1:$A$8))),"")
数式内のセル範囲は調整して下さい。
■SHEET2のB2に=SUM(C2:H2)を設定、下方向にコピー

回答No.5

imogasi

見飽きたようなパターンの質問です。
ーー
ＶＢＡでやるのがお勧め。
ーー
＜明細なしの合計＞
ＩＤごとの計数合計なら割合簡単。
ピボットテーブルもあります。
ーー
＜明細を残したまま＞
でやるのは関数では相当難しい。
ーー
ピボットＴでやってみる
例データ（Ａ、Ｂ列）
Ａ列　　Ｂ列　　Ｃ列
ID 計数 ＮＵＭ
a 2 1
a 3 2
a 1 3
b 2 1
b 4 2
c 2 1
c 3 2
c 1 3
d 2 1
C列は　=COUNTIF($A$2:A2,A2)
ーー
ピボットのレイアウトで
行が「ＩＤ｝、列が「ＮＵＭ」、データに「計数」とする
結果
ID 1 2 3 総計
a 2 3 1 6
b 2 4 6
c 2 3 1 6
d 2 2
総計 8 10 2 20
となる。
データが変わったときのピボットＴの更新方法など勉強のこと。
これで関数に近くなる。

回答No.4

KenKen_SP

区切り位置＋ピボットテーブルでできると思いますよ。

◆手順

1.　[個数] の列をC列に移動
2.　[ID/区分] の列を選択し、[データ]-[区切り位置] を使って、”：”
　　で２列にデータ分割する。

　　イメージ
　　A列　　　　B列　　　　C列
　　ID　　　　 区分　　　 個数
　　00000　　　区分AA　　 10
　　00000　　　区分BA　　 20
　　12222　　　区分AA　　 11
　　・・・

3.　2で作成された表全体を選択し、[データ]-[ピボットテーブルと・・]
4.　ピボットテーブル/ピボットグラフ ウィザードを進めます

　　1)　ウィザード1/3　[次へ] クリック
　　2)　ウィザード2/3　[次へ] クリック
　　3)　ウィザード3/3　[レイアウト] クリック
　　4)　画面右側にあるフィールドボタンを下図のようにドロップして
　　　　配置し、[OK] クリック

　　　　　 　│列　　[区分]
　　───┼────────
　　行　　 │データ
　　　[ID]　│　　　[個数]

　　5)　ウィザード3/3　結果の表示さきを適当に設定し、[完了] クリック

で、ご希望に近いものができますから、あとは別シートで体裁を整えたり。

回答No.3

surounin

取り合えずと言う感じのできですが、
データの区切り文字に着目してください。
ＩＤと区分の区切りは「：」ですよね？
ＩＤを取り出すには
Ａ１にデータが入力されているとすると
Ｂ１に=FIND("：",A1,1)
と入力すると6が帰ってきますね？
そこで、Ｃ１に=LEFT(A1,B1-1)
と入力するとＩＤを取り出せます。
次に数値の区切りは半角スペースなので、
同様にＤ１に=FIND(" ",A1,B1)
と入力すると11が帰ってきますので
E1に=MID(A1,B1+1,D1-B1)
と入力すると、区分を取り出すことができます。
最後に数値ですが、 =value(trim(RIGHT(A1,LEN(A1)-D1-1)))
で取り出せます。
へぼなので長くてすみません

回答No.2

Bronco7

補足：

ID　と　区分　と　個数　でセルを分けたシートを作ってください。

ID列　区分列　個数列
１　　A　　　　1
１　　B 2
２　　A 1
２　　C 3
３ D 4

ウィザードに従ってデータ範囲を表題（ID列等）から全て囲めばOKです。

回答No.1

Bronco7

ピボットテーブルで簡単に集計できますよ

データ　⇒　ピボットテーブル　で、あとはウィザードに従って
表に要素を置くだけ。

出来上がった表をまた、コピー＆ペーストで加工するもよしです。

難しい関数や数式なんか使う必要はありませんね。