勘定科目の集計がしたい

質問

営業の使う精算書を作成しています。
精算書には日付、科目、用途、金額などの項目があり、
空欄に記入できるようになっています。
科目と用途は別シートの科目一覧をリスト参照して選択できるようにしています。
18の科目の中から、精算書に記入された科目だけを集計して、
精算書と同じシートに表示したいです。（ソートは不要）

科目一覧表にsumif関数やdsum関数で
各項目を表示することはできるのですが
「精算書に記入されている科目だけを集計」というところがクリアできません。

初心者のため、おわかりになる方がいらっしゃいましたらご教授いただけないでしょうか。
よろしくお願いいたします。

回答No.7

layy

＞例：全18科目18行の表示ではなく
＞　　清算書に交通費と通信費しか入力されていなければ2行の表示

科目一覧を元に作業セルを準備、
そこで、何種類？、それは何？を求めるのを先、としてできるか？。
重複しているかどうかを求めてから合計、「交通費5000円」、は設定しやすい。
合計を先に求めているから、重複はどうしよう？となった。

交通費　出現１回目　→　合計表示対象セル
交通費　出現２回目　→　合計表示対象セルでない
通信費　出現１回目　→　合計表示対象セル
交通費　出現３回目　→　合計表示対象セルでない
・・・・
・・・・
Ａ１＝Ａ１からＡ１まででＡ１と同じものだったら＋１、よって１
Ａ２＝Ａ１からＡ２まででＡ２と同じものだったら＋１、よって２
Ａ３＝Ａ１からＡ３まででＡ３と同じものだったら＋１、よって１
Ａ４＝Ａ１からＡ４まででＡ４と同じものだったら＋１、よって３
出現カウンタは求めることができそう。

-----------------------------------------------------------------
科目一覧表は下記のようなレイアウトです。
交通費　￥5,000（sumif/dsumを利用）
交際費　￥0
通信費　￥1,000
....（18行）

例：全18科目18行の表示ではなく
　　清算書に交通費と通信費しか入力されていなければ2行の表示
-----------------------------------------------------------------
当初の質問としてはこれではないか？。

「何種類？、それは何？を求める」
このあたりを観点にすると何か見つかるのでは？。

回答No.6

MackyNo1

>C列に60行までランダムな順で入力される科目を集計して表にしたいのですが、

ご希望の操作を簡単に説明すると、C列に（私への補足説明ではB列のように見えますが）入力された科目データのみ重複を避けて一覧表を作成したい（それ以外はDUMIF関数などで計算できる）ということでしょうか？

その場合は(１行目が項目名で2行目からデータが入力されている場合）、以下のような数式でC列に入力されている科目のみ表示することが可能です。

=INDEX(C:C,SMALL(INDEX((MATCH($C$2:$C$60&"",$C$2:$C$60&"",)<>ROW($C$2:$C$60)-1)*1000+ROW($C$2:$C$60),),ROW(A1)))&""

回答No.5

layy

＞科目一覧表にsumif関数やdsum関数で

SUMIFの使い方は知っているみたいなので、
それでは違うのだろう。
「科目だけを」の「だけ」に困っていると思いますが？？。

逆か？。
精算書に記載された科目だけ、科目一覧の金額欄にセットする。
精算書に記載されていない科目は、科目一覧の金額欄に表示させない。

本人が応用できれば良い話なので、
具体例提示じゃなくても、
・・・のシートの・・・を・・・したい、とか
聞きたいところの補足をお願いします。

お礼

たびたびご検討いただきありがとうございます！

すでに表示された行との重複をどうすべきか迷っております。
matchなどを用いてdsumの入れ子やsumproductなども使えないか検討しましたが考え付くことができておりません。
マクロを用いたほうがよろしいでしょうか。
どうぞよろしくお願いいたします。

補足

補足させていただきます。

精算書に記載された科目だけ、科目一覧の科目名・金額欄に1行目からセット（ソートは不要です）
精算書に記載されていない科目は、科目一覧の科目名・金額欄に表示させない。

こちらで説明になればよいのですが・・
別シートにある科目一覧は、現在は文字列昇順に整列されておりません。

回答No.4

MackyNo1

>「精算書に記入されている科目だけを集計」というところがクリアできません。

具体的にどのような集計結果にしたのでしょうか？

元データのレイアウトと集計データのレイアウトを具体的に例示してください。

お礼

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

求めている表示形式は
清算書に入力された科目（18科目のうちからランダム・60行まで）
の科目名・合計金額を科目一覧表に表示したいです。
例：全18科目18行の表示ではなく
　　清算書に交通費と通信費しか入力されていなければ2行の表示

よろしくお願いいたします。

補足

清算書は下記のようなレイアウトです。
日付　科目　用途　区間1　区間2　金額　備考
2/17　交通費　電車代　東京　新宿　190　
※科目は別シートにある科目名一覧からリスト選択
※用途はindirect(科目)で別シートにある科目別用途名一覧からリスト選択
　（交通費の用途名は電車代・バス代・飛行機代・・など）
科目一覧表は下記のようなレイアウトです。
交通費　￥5,000（sumif/dsumを利用）
交際費　￥0
通信費　￥1,000
....（18行）
ここまではできております。

回答No.3

layy

精算書にはどの科目が記載されるか特定でない。
精算書のC5セルには交通費だったり事務費だったりする。

科目一覧の交通費や事務費欄横には、この科目というのが決まるのでSUMIF数式で求めることができている。

ならば

精算書に記載した科目が科目一覧のどの位置にあるか検知しその横の金額を取得、そんな感じでどうでしょうか。

VLOOKUPのような検索に使う関数を探す。

具体的に
別シートのどのセルにあるか知りたい、と例を挙げると回答は出そう。

関数の見つけ方わかれば出来るところまでやってみる。

お礼

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

C列に60行までランダムな順で入力される科目を集計して
表にしたいのですが、
18科目から入力されたものの科目名とその合計金額の表示
こちらの方法を模索しております。

説明不足で申し訳ございません。
どうぞよろしくお願い申し上げます。

補足

説明不足で大変恐縮です。
layyさんのおっしゃるとおり
・どの科目が記載されるか特定でない
・18科目の一覧表にsumif/dsum(dcount)で合計額を表示
こちらまではできております。

回答No.2

sarugetu

sumifを使用して、
交通費　￥5,000
交際費　￥0
通信費　￥1,000
のように18科目を計算した後、
合計を出すのでは駄目なのでしょうか？
完成イメージをもう少し教えていただければ検討させていただきます。

お礼

回答ありがとうございます。
18科目を記載していただいたように表示するところまではできたのですが、
０円のものは非表示にしたいとリクエストをもらい考えあぐねております。

何かいいお考えはありますでしょうか。

回答No.1

KURUMITO

例えばA1セルに日付、B1セルに科目、C1セルに用途、D1セルに金額の文字があり2行目から下方にそれぞれのデータが入力されているとします。そこで集計したい科目名をG1セルに入力するとしてH1セルにその合計金額を表示させるとしたらH1セルには次の式を入力します。

=SUMIF(B:B,G$1,D:D)

お礼

ご回答と関数式をありがとうございました。

レイアウトのイメージはそのような並びです。
sumif, dsum, dcount関数を使ってGに予め18科目の場所を用意しておき、
ランダムに入力された科目の合計結果が表示、入力されない科目は0または空欄表示。

そこまではできたのですが、
例えば入力された科目だけをGに上から表示(入力されない科目は非表示)させることは関数でできるでしょうか。

別シートに科目名一覧(並びは昇順ではない)があり、科目の入力はこのリストを参照しています。