エクセルで質問です。

質問

エクセルで質問です。
シート１とシート２に回数を入力するだけでシート３の様に並び替え及び合計の自動計算してくれる方法はありませんか。
ほとんど関数の使い方を知らないので教えて下さい。

シート１
　　A　B 　C 　　　 D E F G H
1 　NO 項目 円/管理番号 b0006 a0002 空白 空白 空白
2 　1 新規 400円　　 空白 空白 空白 空白 空白
3　 2 撤去 200円 2回 2回 空白 空白 空白
4　 3 変更 300円　　 空白 空白 空白 空白 空白

シート２
　　A　B 　C 　　　 D E F G H
1 　NO 項目 円/管理番号 a0001 b0001 空白 空白 空白
2 　1 新規 400円 空白 1回 空白 空白 空白
3　 2 撤去 200円 2回 2回 空白 空白 空白
4　 3 変更 300円　　 空白 空白 空白 空白 空白

シート３
　　A　B 　C 　　　 D E F G H
1 　NO 項目 円/管理番号 a0001 a0002 b0001 b0006 空白
2 　1 新規 400円　　 空白 空白 1回　 空白 空白
3　 2 撤去 200円 2回 2回 2回 2回 空白
4　 3 変更 300円　　 空白 空白 空白 空白 空白
5　 合計 　400円 400円 800円 400円 空白

回答No.3

KURUMITO · ベストアンサー

回答Ｎｏ2です。補足でご質問の内容についてはかなり難しい問題です。
次のようにシート3に作業列を作って対応することになりますね。
例えばＪ1セルには次の式を入力し下方にオートフィルドラッグします。

=IF(ROW(A1)<=COUNTA(Sheet1!D$1:H$1),INDEX(Sheet1!D$1:H$1,ROW(A1)),IF(ROW(A1)<=COUNTA(Sheet1!D$1:H$1)+COUNTA(Sheet2!D$1:H$1),INDEX(Sheet2!D$1:H$1,ROW(A1)-COUNTA(Sheet1!D$1:H$1)),""))

これでＪ列にはシート1とシート2に入力されている管理番号が表示されます。
次にそれらの番号を並べ替えるために必要な作業列としてＨ1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(J1="","",CODE(J1)+MID(J1,2,5)/10000)

この式では管理番号の前から1文字のみがローマ字で後は数字であるとしています。
Ｈ列に有るデータを使って順序よくシート3のＤ1セルからＨ1セルに管理番号を並べるためにＤ1セルには次の式を入力して右横方向にオートフィルドラッグします。

=IF(COLUMN(A1)>COUNT($K:$K),"",INDEX($J:$J,MATCH(SMALL($K:$K,COLUMN(A1)),$K:$K,0)))

これまで提案した方法を応用することでもっと管理番号が増えた場合でも対応できるようになりますね。

お礼

少し考えてみました。結果=IF(COLUMN(A1)>COUNT($I:$I),"",INDEX($K:$K,MATCH(SMALL($I:$I,COLUMN(A1)),$I:$I,0)))で入力し期待していた結果がでました。これで集計処理がスムーズになります。関数のみを使用しこの様な結果を出せるとは正直出来ないと思っていました。本当に本当に感謝します。ありがとうございました。

補足

何度もご回答頂きありがとうございます。最後の関数=IF(COLUMN(A1)>COUNT($K:$K),"",INDEX($J:$J,MATCH(SMALL($K:$K,COLUMN(A1)),$K:$K,0)))でうまくいきません。Ｄ1セル式を入力して右横方向にオートフィルドラッグしたのですが全て空白となり並べ替えは出来ませんでした。何か私のエクセルの設定に問題あるのでしょうか。

回答No.2

KURUMITO

シート1、シート2およびシート3のセルで円の表示が入るセルについてはそれらのセルの範囲を選択してから右クリックして「セルの書式設定」から「表示形式」のタブで「ユーザー設定」を選び種類の窓には　#"円" と入力しＯＫします。それによってそれらのセルに数値を入力すれば200円などと円が付いて表示されます。同様に回が表示されるセル範囲についても「ユーザー設定」を選び種類の窓には　#"回" と入力してＯＫします。それによってセルに数値を入力すれば2回のように回が付いて表示されます。このようにすることで回や円が付いたセルの値をそのまま計算に使うことができるようになります。
そこでシート3のＤ2セルには次の式を入力してＨ2セルまでオートフィルドラッグ（セルの右下隅に有るフィルハンドルをクリックしてドラッグ操作をします）したのちに下方の4行目までもオートフィルドラッグします。

=IF(D$1="","",IF(COUNTIF(Sheet1!$D$1:$H$1,D$1)>0,INDEX(Sheet1!$D:$H,ROW(D2),MATCH(D$1,Sheet1!$D$1:$H$1,0)),IF(COUNTIF(Sheet2!$D$1:$H$1,D$1)>0,INDEX(Sheet2!$D:$H,ROW(D2),MATCH(D$1,Sheet2!$D$1:$H$1,0)),"")))

その後に合計を表示させるためにＤ4セルには次の式を入力してＨ5セルまでオートフィルドラッグします。

=IF(COUNT(D2:D4)<3,"",$C2*D2+$C3*D3+$C4*D4)

これによってシート1や2にデータが入力されれば即座にシート3のデータも変更されますね。

補足

回答いただきましてありがとうございます。ほぼ理想通りの結果がでました。本当に感謝します。後出来ましたら、管理番号で並び替えa0001　a0002　b0001　b0006の順番に自動で並び替えることは可能でしょうか。マクロ等は使用せず自動でワンクリックもせずに結果を出せれば良いのですが。

回答No.1

aokii

マクロになりますので、マクロなしでやる場合は、シート３にシート１とシート２のデータをコピーして貼り付け、合計してはいかがでしょう。

並べ替えは、手動ですが、行列を入れ替えて並べ替えてから、再度、行列を入れ替えて並べ替えで、できます。

合計は、検索と選択の置換で、円と回という文字をともにブランクに置換してから、
シート３のD5=$C2*D2+$C3*D3+$C4*D4
でいかがでしょう。

お礼

回答ありがとうございます。