エクセルについて。関数は何を使用すれば？

質問

下記の表があります。
作業者鈴木1の伝票内容を横にうちこんで合計をだしてあります。
（Ｋ列に１と入力すると対象の金額が出るようにIFERRORとVLOOKUPでやってます）

月末に、１か月分の
鈴木→小型○○台・××円　　中型○○台・××円・・・・
佐藤→同じく
と、項目ごとに合計台数と金額を集計したいのです。

今までは、列ごとに項目を分けて縦に計算していたのですが
項目が１５項目あるため（+イレギュラーのフリー入力枠が２枠）
関数は組みやすいのですが、横にすごく長くなってしまうのです。
（１回の作業で発生する項目は多くても３つです）

それを解消するため、作業した項目のみの表示にしたかったのですが。
うまくいかずに悩んでおります。

DSUMを試しましたが、何かが違うと思い　そこで行き詰まりました。。。

全てネットで調べてはマネをして・・・の独学ですので
最初のIFERRORのくだりもこれで一番良いものなのかは自信ありません。

説明もうまくできておらず、理解して頂けるか不安ですが
お知恵を貸してください。
よろしくお願い致します。

回答No.5

kagakusuki

回答No.2です。

>$Eとありますが、Ｅ列はどこを参照なんでしょうか？

　失礼致しました。
　実は、御質問の解決方法を探るためにExcel上で作表した際、上の方の表に「小型」、「中型」、「大型」の各型の金額を自動表示させるためにE列～G列に各番号と金額の対応表を作っていたのですが、対応表は自分で勝手に作ったものであった事を忘れて、回答の関数に組み込んでしまっておりました。
　尚、

IF(COUNTIF($E:$E,INDEX($K:$K,ROW())),

の部分は、無効な番号が入力されていた場合にはSUMPRODUCT関数による処理を行わずに済ませる事で、処理速度を僅かでも速くするために設けた部分であって、この部分が無くとも正常に動作致します。
　ですから、L15セル及びM15セルに入力する関数を、次の様に変更して下さい。

[L15セルの関数]

×　　=IF(COUNTIF($E:$E,INDEX($K:$K,ROW())),IF(ISERROR(1/(MATCH(CHAR(1),$I$14:INDEX($I:$I,ROW()),-1)>1)),"",SUMPRODUCT(COUNTIFS($I$2:$I$14,INDEX($I:$I,MATCH(CHAR(1),$I$14:INDEX($I:$I,ROW()),-1)+ROW($I$14)-1),OFFSET($K$2:$K$14,,{0,2,4}),INDEX($K:$K,ROW()))*1)),"")

　　↓

○　　=IF(ISERROR(1/(MATCH(CHAR(1),$I$14:INDEX($I:$I,ROW()),-1)>1)),"",SUMPRODUCT(COUNTIFS($I$2:$I$14,INDEX($I:$I,MATCH(CHAR(1),$I$14:INDEX($I:$I,ROW()),-1)+ROW($I$14)-1),OFFSET($K$2:$K$14,,{0,2,4}),INDEX($K:$K,ROW()))*1))

[ML15セルの関数]

×　　=IF(COUNTIF($E:$E,INDEX($K:$K,ROW())),IF(ISERROR(1/(MATCH(CHAR(1),$I$14:INDEX($I:$I,ROW()),-1)>1)),"",SUMPRODUCT(SUMIFS(OFFSET($L$2:$L$14,,{0,2,4}),$I$2:$I$14,INDEX($I:$I,MATCH(CHAR(1),$I$14:INDEX($I:$I,ROW()),-1)+ROW($I$14)-1),OFFSET($K$2:$K$14,,{0,2,4}),INDEX($K:$K,ROW()))*1)),"")

　　↓

○　　=IF(ISERROR(1/(MATCH(CHAR(1),$I$14:INDEX($I:$I,ROW()),-1)>1)),"",SUMPRODUCT(SUMIFS(OFFSET($L$2:$L$14,,{0,2,4}),$I$2:$I$14,INDEX($I:$I,MATCH(CHAR(1),$I$14:INDEX($I:$I,ROW()),-1)+ROW($I$14)-1),OFFSET($K$2:$K$14,,{0,2,4}),INDEX($K:$K,ROW()))*1))

　後、余談ですが、見落としや誤認等が不可な事は金銭計算に限らず当たり前の話であり、どんな関数であろうとピボットテーブルであろうと、入力ミスがあったのでは正しい結果が得られ無くになる事に変わりはありません。
　勿論、SUMPRODUCT関数だとて、入力ミスがあれば正しい結果など得られませんので御注意下さい。

補足

ご丁寧にありがとうございます。
やっと時間ができたので、取りかかってみます。

回答No.4

Nouble

間違えていたら済みません

例えば、
鈴木さん、小型の
台数と売り上げ
此の集計が欲しい

と、いう事ですね？

作表次第で変わりますね
指定項目が3つですが
簡単な表では
縦横方向1つづつしか
指定できないでしょう

項を集約する
とか
別表を項毎に用意する
とか
に、なります
よね？

で、ですが
此って、例えば

鈴木さんと佐藤さん
等、皆さんが
同時に見えないと駄目

大型中型小型が各々
同時に見えないと駄目

ですか？

ピボットテーブル化すれば

例えば、

見出し(？)項目で佐藤さんに絞れば
一覧が見える
とか、

同様に
見出し(？)項目で大型に絞れば
全員の台数と売り上げか見える

とか、
見出し(？)項目で台数に絞れば
全主全員の一覧が見える
とか、

凄く軽々
と、できます
よ？

関数の方がいいですか？

関数ならSUMPRODUCTでしょう
ね
金銭計算で
見落とし、誤認、
此等は
もっての他

なので
SUMIF系やCOUNTIF系は
使用ご法度
ですから

まあ仕方なく
と、いうか…
ね

知らない方も多いようです

が
此等の関数の不確かさを
一度でも現認したら
他の方に勧める
等、できなくなります

さて、
鈴木さん小型売り上げ
此が知りたい時
的な事ですよね？

仮に
誰のが知りたいか
此がセルR5に入っている
何型が知りたいか
此がセルR6に入っている
見出しが3行目に書かれている
と、します

=SUMPRODUCT(($I=$R$5)*INDIRECT(CODE(64+MATCH(TRUE(),INDEX($3=$R$6,,),0))))

此で行ける
かと、

かなり大きくデータ範囲を取った
ので

逆に言えば
絞り込みと、
加えて、エラー処理を省いて
短くした
ので

少々重めで指定値によりエラーが出ます

が
此の式は
何千セルと書き込まれるもの
ではなく

其の適応数を考慮すれば
問題ない
かと、思います

尚、
半角大文字アルファベットA
此のコードが65、
此の前提で、書いています

ASCII又はCHR
と、言った関数で
調べれば、確認できます

確認をお願いします

=chr("A")
ですね

そんなには長くない
と、思うのですが
ピボットテーブル
が、お勧めかも

補足

調べてみます！
毎月末に誰がなにを何台でいくらか？という表を出力していますので
ピポットテーブルでも行けると思われます。
いろいろ考察してみます。
ありがとうございます！

回答No.3

msMike

「関数は組みやすい」けど、「横にすごく長くなってしまう」だけが悩みのタネなんですね？
「１回の作業で発生する項目は多くても３つ」だけど、全項目数が多いのなら止むを得ないのでは？
項目数より人数が少ないのなら、「作業者」を横に、「項目名」を縦に並べたら？

補足

車両に対して誰がなにをしたか？なので、そうは行かないのです。。。

回答No.2

kagakusuki

SUMPRODUCT関数で、行と列がともに複数のセル範囲を参照させてそのまま計算させた場合、参照範囲に含まれているセルの個数と同じ回数だけ「繰り返し計算」が行われるため、あまり広い範囲を指定しますと、計算処理の際のパソコンの負荷が大きくなります。
　ご質問の件では、台数はCOUNTIFS関数を使ってK列、M列、O列ごとの台数を求めて合計することで求める事が出来ますし、金額はSUMIFS関数を使ってL列、N列、P列ごとの金額を求めて合計することで求める事が出来ます。
　尤も、K列、M列、O列ごとに合わせたCOUNTIFS関数や、L列、N列、P列ごとに合わせたSUMIFS関数は、参照先がそれぞれ2列ずつずれているだけで、3つとも同じような関数であり、それら3つの関数を全て記述したのでは関数が長くなってしまいます。
　そこで以下の方法では、SUMPRODUCT関数と組み合わせる事で関数の長さを短くしています。
　尚、以下の方法では、SUMPRODUCT関数を使っていると言いましても、繰り返し計算の回数はたったの3回だけとなるようにしておりますので、処理が重くなる恐れはありません。

　まず前提条件ですが、今仮に、I15セル(或いはI15:I17が結合されたセル)に「鈴木」、
I18セル(或いはI18:I20が結合されたセル)に「佐藤」等のように作業者名が入力されていて、
K15セルとK18セルには「1」(「小型」の番号)、
K16セルとK19セルには「2」(「中型」の番号)、
K17セルとK20セルには「3」(「大型」の番号)
が入力されているものとします。
　そして、L15セルには鈴木の小型の台数を、M15セルには鈴木の小型の金額を、それぞれ自動的に表示させるものとします。
　同様に、L16セルには鈴木の中型の台数を、M16セルには鈴木の中型の金額を、
L17セルには鈴木の大型の台数を、M17セルには鈴木の大型の金額を、
L18セルには佐藤の小型の台数を、M18セルには佐藤の小型の金額を、
L19セルには佐藤の中型の台数を、M19セルには佐藤の中型の金額を、
L20セルには佐藤の大型の台数を、M20セルには佐藤の大型の金額を、
それぞれ自動的に表示させるものとします。

　その場合、まずL15セルに次の関数を入力して下さい。

=IF(COUNTIF($E:$E,INDEX($K:$K,ROW())),IF(ISERROR(1/(MATCH(CHAR(1),$I$14:INDEX($I:$I,ROW()),-1)>1)),"",SUMPRODUCT(COUNTIFS($I$2:$I$14,INDEX($I:$I,MATCH(CHAR(1),$I$14:INDEX($I:$I,ROW()),-1)+ROW($I$14)-1),OFFSET($K$2:$K$14,,{0,2,4}),INDEX($K:$K,ROW()))*1)),"")

　次に、M15セルに次の関数を入力して下さい。

=IF(COUNTIF($E:$E,INDEX($K:$K,ROW())),IF(ISERROR(1/(MATCH(CHAR(1),$I$14:INDEX($I:$I,ROW()),-1)>1)),"",SUMPRODUCT(SUMIFS(OFFSET($L$2:$L$14,,{0,2,4}),$I$2:$I$14,INDEX($I:$I,MATCH(CHAR(1),$I$14:INDEX($I:$I,ROW()),-1)+ROW($I$14)-1),OFFSET($K$2:$K$14,,{0,2,4}),INDEX($K:$K,ROW()))*1)),"")

　次に、L15～M15のセル範囲をコピーして、同じ列の16行目以下に貼り付けて下さい。

　以上です。

補足

回答ありがとうございます。
表までわざわざ作って頂いて。
しかし、$Eとありますが、Ｅ列はどこを参照なんでしょうか？

回答No.1

mshr1962

作業者およびK,M,O…列の数値で隣の列の金額を集計ということでいいのでしょうか？

番号と金額の欄はK列からAN列でいいのでしょうか？集計範囲が3行から99行として

鈴木→小型○○台
=SUMPRODUCT(($I$3:$I$99="鈴木")*($K$3:$AM$99=1))
鈴木→小型××円
=SUMPRODUCT(($I$3:$I$99="鈴木")*($K$3:$AM$99=1)*($L$3:$AN$99))
鈴木→中型○○台
=SUMPRODUCT(($I$3:$I$99="鈴木")*($K$3:$AM$99=2))
鈴木→中型××円
=SUMPRODUCT(($I$3:$I$99="鈴木")*($K$3:$AM$99=2)*($L$3:$AN$99))

作業者（鈴木,佐藤）と項目番号（1,2,3・・・）を、セル参照にすれば楽に集計できると思います。

お礼

いろんな関数があるのですね。
やってみます！
ありがとうございます。