[[20040308181215]]　『ある条件に合致するものの合計値』（Tear）　ページの最後に飛ぶ

『ある条件に合致するものの合計値』（Tear）

 こんにちは。たびたびお世話になります。
現在、資材業務の集計表を作成しております。
その中で、２つ以上の条件に合致するものの合計値とその件数を知りたいのですが、
上手くいかないので、お力を貸してください！よろしくお願いします！！
現在作成中のシートは、
　F列：勘定科目のコード
　G列：勘定科目の名称（F列のデータより自動入力）
　H列：原価センタのコード
　I列：原価センタの名称（H列のデータより自動入力）
　J列：件名コードのコード
　K列：件名コードの名称（J列のデータより自動入力）
　S列：契約先の業者名
　T列：契約金額　　　　となっております。
また、原価センタか件名コードのどちらか一方しかデータは入力しません。それ以外の列のデータはすべて入力します。
私が知りたいことは、例えば･･････････
　　勘定科目が「消耗品」で、件名コードが「01-2345-67-89」の契約金額合計とその件数。という具合です。
ここで私が特に困っていることは、
　①原価センタor件名コードのどちらで処理を行ったかを、どうやって取得するか。
　②原価センタの場合は、１部署に１つしか名称がないが、件名コードの場合は、各署、随時件名が増えていくということ。
　　　→どういう件名があるのか事前には分からない上に、たくさんある。
という2点です。。。。。
過去ログを拝見していて、SUMPRODUCT関数でできるかなと思い、
ライブラリの＜（e3h）SUMIFとSUMPRODUCT関数＞も参考にしながら、
自分で目で確認できる程度の簡単なサンプルを作成してSUMPRODUCT関数を試してみましたが、
それすらも上手くいきませんでした・・・・・。
条件に合致するものがあるのに、合計値は「0」になってしまったのです　（；_；）
関数のヘルプを見ても良く分かりませんでした。
ですので、恐れ入りますが、SUMPRODUCT関数からご指導いただきたく存じます。
すごく長文になってしまい、質問の意味をご理解いただけるか心配ですが
よろしくお願い致します。

 F列からT列のフィールド名とデータ範囲を選択して、Ctrl+Shift+[F3]で「名前の作成先」を[上端行]にしてOK
 この処理で、フィールド名が範囲名になります。
 条件①　勘定科目="消耗品"　条件②　件名コード="01-2345-67-89"　で契約金額を求める式は。
 契約金額の合計は　=SUMPRODUCT((勘定科目="消耗品")*(件名コード="01-2345-67-89"),契約金額)
 契約金額の件数は　=SUMPRODUCT((勘定科目="消耗品")*(件名コード="01-2345-67-89"))
           または　=SUMPRODUCT((勘定科目="消耗品")*(件名コード="01-2345-67-89")*(契約金額>0))
 (シニア)

　シニアさん、ご回答ありがとうございます！
　今までずっと、SUMPRODUCT関数の仕組みについて考えてました。。。。

　ここでさらに質問です。

　前回、私が書き込みしたのは、知りたいものの例であり、

　実際は、どのような件名コードがあるのか、事前には分かりません。

　・・・「分からない」というのにはさすがに語弊がありますが、

　件名コードは随時追加されていくので、事前に把握することは難しいのです。

　また、たくさんある件名コードの中で、今年度下期中には何も購入しなかったというコードも存在します。

　当該期間中に、何か物品を購入したコード（＝今回作成のシートに記載があるもの）のみ、

　合計金額を取得したいのですが、そういった場合の対処法を教えていただけると幸いです。

　恐れ入りますが、よろしくお願いいたします。　　（Tear）

 契約金額の合計は　=SUMPRODUCT((勘定科目="消耗品")*(件名コード="01-2345-67-89"),契約金額)
     A                  B               C   
1 勘定科目	件名コード	契約金額
2   消耗品	01-2345-67-89	=SUMPRODUCT((INDIRECT(A1)=A2)*(INDIRECT(B1)=B2),INDIRECT(C1))

 前回求めた式をA1,B1,C1にフィールド名、A2はA1のフィールドの条件、B2はB1のフィールドの条件とします。
これで、フィールド名や条件を変更すれば、その条件に対応する契約金額がC2の式で求められます。
確かめて下さい。(シニア)

　お忙しいところ上記のようなご回答をいただいた上に、

こんなことを申し上げるのも大変恐縮なのですが、

フィールドの条件等、シニアさんのご回答がいまいち理解できません・・・。ゴメンナサイ。

私の力量不足であることは確実ですが、

プラスαの説明をお願いできませんでしょうか。

また、SUMPRODUCT関数のように一括で計算する方法以外でも結構ですので、

いくつかのステップを踏まえるけれども初心者にも理解しやすいというような

方法をご存知でしたら教えていただけると幸いです。

なんせこちらの過去ログを拝見していてSUMPRODUCT関数というものを

初めて知ったという程度の知識ですので・・・・。

重ね重ねお手数をお掛けしますが、よろしくお願いいたします。　　（Tear）

 最初の回答の契約金額の合計は
 =SUMPRODUCT((勘定科目="消耗品")*(件名コード="01-2345-67-89"),契約金額)
 の式で確認されたのですか？それとも納得する解が得られなかったのですか？
 二回目の回答は式を変更しないで、条件のフィールド名と内容を変更して結果を得る式です。
 データベースには各列の先頭にフィールド名があります。
例　勘定科目　がフィールド名です。その内容の１つに消耗品があります。
従って、勘定項目の内消耗品が条件になります。これを式化すれば(勘定科目="消耗品")
 　 件名コード がフィールド名です。その内容の１つに01-2345-67-89があります。
従って、件名コードの内01-2345-67-89が条件になります。これを式化すれば(件名コード="01-2345-67-89")
この２つの条件を満たす契約金額を求めるのですから、
=SUM(IF(勘定科目="消耗品",IF(件名コード="01-2345-67-89",契約金額,0))) と入力し、Ctrl+Shift+Enterで確定すれば、行列数式で求められます。
これと同じ機能の式がSUMPRODUCT関数でEnterで確定できます。
まづ初回の回答を確認してから再度質問して下さい。(シニア)

 シニアさん、返信待ちのところすみません、失礼します。
 思いますに、フィールド名　という「言葉」になじみが薄いのでは無いでしょうか。
 実は私も人様に説明できるほどには理解していません、SUMPRODUCTも同様に。

 実際にTearさんが使用している列が T列迄あり、W:Z列もふさがってると思うので
 シニアさんの書いてくれた
      A                B                    C
 1 勘定科目        件名コード             契約金額
 2 消耗品          01-2345-67-89

 をAA列からと仮定しました。
      AA              AB                    AC
 1 勘定科目        件名コード             契約金額
 2 消耗品          01-2345-67-89

 AA2 に　消耗品     AB2 に　01-2345-67-89
 と入力し
 AC2 に　=SUMPRODUCT(($G$2:$G$100=AA2)*($J$2:$J$100=AB2)*($T$2:$T$100))
 として下にコピー。範囲はとりあえず 100迄としてますが適宜変更です。

 勘定科目や件名コードに追加追加があった時点で、AA3,AB3 以下に新規入力する。

 これで、
 >件名コードは随時追加されていくので、事前に把握することは難しいのです。
 にも、対応出来るのではないでしょうか。

 この方法や数式はシニアさんが書いてくれたものと全く同じ事です。

 AA:AC 列はかなり離れてるので、
 [合計金額を取得] のシートを新しく作っても、いいかもしれませんね。

 シニアさん、出過ぎた事をしましたがどうぞご容赦を。　　　（jun53）

 jun53さん詳しく補説して頂き有難う御座います。
Tearさんは熱心にSUMPRODUCT関数を活用しようと取り組んで見える様子ですが、
こちらの説明の仕方にギャップがあったのか？突っ込んだ問い合わせが遅れていますが
これだけの具体的な表作成とその処理を実務として取り組んで見えるのですから、便利機能の一例をあげれば
 各列の表題を範囲名にする操作（Ctrl+Shift+*+[F3]［上端行」OK)
①表全体を選択するショートカットは（表内選択して、Ctrl+Shift+*　）
②各列の表題を範囲名にするショートカットは①に続いてCtrl+Shift+[F3]で［上端行」選択OK
①②の操作で、データ追加され範囲拡張されても式は更新されます。
 jun53さんの協力も得られますので、Tearさん再度訪問して下さい。お待ちしています
 （シニア）

 今回質問させていただいたこととは別の作業も並行して行っていたため、
こちらでの確認が遅れており、その結果レスも遅れてしましました。。。。。
しかし、今日ひさしぶりにこちらに訪れて、私は今ものすごく感激しております。
おふたりともありがとうございます！！(>_<)
実は別の方法でやろうかと、諦めかけていたところでしたので（質問しといてそれはどうなの）
おふたりの書き込みにより、前向きな気持ちになって参りました。
 さて、前置きが長くなりましたが、一番初めにシニアさんが教えてくださった数式で、
私が考えているものが求められることを、確認しました。
また、SUMPRODUCT関数の基本的な仕組みも理解しました（のつもりです・・・）。
 私が何に悩んでいたかというと、
シニアさんが教えてくださった、「各列の標題を範囲名にする操作」についてでした。
しかし、前回のシニアさんのご回答により、データを追記した場合に対応していると
やっと理解しました・・・・・。理解力乏しくてすいません・・・・・・(;_;)
そのことに関して１点確認させていただきたいのですが、
データを追記した場合、勝手に範囲が修正されるわけではなく、
追記するごとに、上記の①②の操作を行わなければなりませんよね？
 それと、私の説明不足でしたが、件名コードというものは、
仕事（？）として成立した際に社内で新規に設定されるものであって、
必ずしもその件名コードで物品を購入するとは限らないのです。
私が現在作成している集計表は、物品を購入した件名コードのみを取り扱っておりますので、
jun53さんがおっしゃるような、
 >勘定科目や件名コードに追加追加があった時点で、AA3,AB3 以下に新規入力する。
ということは、単純にはいかないかな、と考えております。
また、件名コードと同じく、勘定科目もかなり多量に設定されておりますが、
その中で使用している勘定科目は限られてます。
ですので、集計結果を出す前の段階で、どの勘定科目・件名コードが使用されているのかを
拾っておいて、それを教えていただいたSUMPRODUCT関数に反映させるのがいいかな、
というのが、私の中でまとまってきた考えなのですが・・・・。
しかし、どうやったらそれを拾えるかが、実は分からないので、
お手数ですが、そちらも教えてください・・・・(;_;)
長文になってしまいましたが、よろしくお願いいたします。　　（Tear）

 SUMPRODUCT関数についての概要を把握されましたので、効率的な作業が可能になると思います。
 お尋ねの例えば勘定科目のデータが多量にあって、その中で使用している勘定科目は限られてます。
 この重複しないデータをどのように抽出するか知りたいとのことですが、
 データメニューのフィルタ・フィルターのオプションの設定で行います。
①勘定科目の列番号を選択してコピーします。空きシートのA1に貼り付けます。
②表題の「勘定科目」をコピーして、C1,E1に貼り付けます。
③データメニューのフィルタ・フィルターのオプションの設定で
   抽出先を「指定した範囲」を選択します。
   リスト範囲はA列のデータ範囲を選択します。
   検索条件範囲は　$C$1:$C$2
   抽出範囲は　　　$E$1
   □重複するレコードは無視する　にチェックしてOK
④抽出されたデータ範囲を選択して、「名前ボックス」に「リスト1」と範囲名を付けます。
jun53さんの紹介の範囲でAA2を選択します。
      AA              AB                    AC
 1 勘定科目        件名コード             契約金額
 2 消耗品          01-2345-67-89
⑤データメニュー・入力規則で入力値の種類を［リスト］にし、元の値を［=リスト1］とします。
 AA2の▼でリスト選択すれば、条件を変更した契約金額を確認することが出来ます。
他の項目についても重複データを除いたリストを作って活用してみて下さい。
 データ追記する度に、（Ctrl+Shift+*+[F3]［上端行」OK)の操作が面倒であれば、
データ範囲の最終行内で行挿入すれば、範囲は拡張されます。
 （シニア）

　シニアさん、すみません、私の頭ではいまいち理解できてませんので、

追加で質問させていただきます・・・。

現段階で、手順③までが上手くできてないので、④以降は試してもいません。

　①→　勘定科目は、F列に勘定科目コード・G列に勘定科目の名称となっているため、

　　その2列をコピーし、空きシートA1に貼り付けました。

　②→　[表題]がどれのことかよくわからなかったので、とりあえず、F1・G1セルを結合して

　　入力してある、「勘定科目」という言葉を、空きシートのC1セルとE1セルに貼り付けました。

　③→　データ・フィルタ・フィルタオプションの設定で、

　　抽出先：「指定した範囲」

　　リスト範囲：データが入っているA1:B28

　
　　検索条件範囲：$C$1:$c$2

　　抽出範囲：$E$1

　　「重複するレコードは無視する」にチェックを入れて　OK。

　→→→①～③を実行した結果、E2～E10に勘定科目のコードが表示されましたが、

　　勘定科目の名称は表示されておらず、E2～E10以外は空白のままでした。

　（私が試した分では、9種類の勘定科目が使われているということですよね・・・。）

　フィルタのオプション設定を使用するのは今回がはじめてで、

　いろいろ試してはみましたが、なんともいきませんでした・・・・・・・。

　シニアさん側で試された結果とは、この時点で既に異なるのでは？とは思うのですが。

　大変申し訳ありませんが、詳しくご説明願えませんでしょうか。

　ちなみに④の「名前ボックス」がどこから開けるのかも分かりません・・・。

　本当に申し訳ないのですが、よろしくお願い致します m(_ _)m　　（Tear）

 >→→→①～③を実行した結果、E2～E10に勘定科目のコードが表示されましたが
E2～E10の9種類が抽出されたのですから、成功しているのではないですか
A1にコピーしたのがA列の表題（フィールド名）です。A1をC1とE1にコピーします。
この結果は「勘定科目コード」が9種類抽出されたのです。
次はB1をC1とE1にコピーして、
　③→　データ・フィルタ・フィルタオプションの設定で、
        抽出先：「指定した範囲」
        リスト範囲：データが入っているB1:B28
      検索条件範囲：$C$1:$C$2
          抽出範囲：$E$1
     「重複するレコードは無視する」にチェックを入れて　OK。
 E2～E10に勘定科目の名称が表示されます。
「名前ボックス」は列番号Aの上にあります。マウスを近づけると「名前ボックス」と表示されます。
同様の操作はリストにしたい範囲選択して、挿入メニュー・名前・定義で行います。
 手順通りに出来ましたら次の質問をして下さい。（シニア）

　自分の知識不足は、自分で補う努力が必要ですよね。

フィルタオプションの設定について、いろいろ調べてみた結果、

私が「？？？」だったところがだいぶ分かりました。

自分で調べもしずに、頼ってばかりで申し訳ありませんでした・・・m(_ _)m

ちなみに、私の中でどの個所が一番のネックだったかと言うと、

「検索条件範囲：$C$1:$C$2」の$C$2のところです。

どうして空白のセルを検索条件範囲に含むのかほんとにナゾでしたが、

空白セルを指定＝全件を検索条件の対象とする、という意味だったのですね・・・。

　私のイメージでは、上記①～③の手順を行うと、

重複データを除いた勘定科目がコード・名称ともに

C1セルから下方向に表示されると考えていたため、

　>シニアさん側で試された結果とは、この時点で既に異なるのでは？とは思うのですが。

という書き込みをしてしまいました。。。。。

　自分なりにフィルタオプションについて考えましたが、1点分からないことがあります。

現在作成中のシートは、

　F列：勘定科目のコード

　G列：勘定科目の名称（F列のデータより自動入力）

と、なっているのですが、フィールド名は、F1・G1を結合して「勘定科目」としています。

フィールド名が結合された状態で①～③の手順を行うと、

元F列のコードは抽出されましたが、元G列の名称は抽出できませんでした。

フィールド名というのは結合セルだとやはり問題でしょうか？？

　また、④以降も試してみたのですが、▼でリスト選択しても、

「リスト1」の1件しか表示されませんでした。。。

私が説明下手なゆえ、質問の内容をご理解いただけるか心配ですが、

ご教授のほど、よろしくお願い致します。　　（Tear）

 次の方法でもう一度実行してみて下さい。
 勘定科目のコードと勘定科目の名称を表示させるには、A1とB1のフィールド名を結合するのではなく、
 A1をC1とE1にコピーして、B1をF1にコピーします。
　③→　データ・フィルタ・フィルタオプションの設定で、
        抽出先：「指定した範囲」
        リスト範囲：データが入っているA1:B28
      検索条件範囲：$C$1:$C$2
          抽出範囲：$E$1:$F$1
     「重複するレコードは無視する」にチェックを入れて　OK。
 E2～F10に勘定科目コードと名称が表示されます。
 ④F2：F10を選択して「名前ボックス」に　リスト1　と入力して範囲名をリスト1とします。
      AA              AB                    AC
 1 勘定科目        件名コード             契約金額
 2 消耗品          01-2345-67-89
 ⑤AA2でデータメニュー・入力規則で入力値の種類を［リスト］にし、元の値を［=リスト1］とします。
AA2の▼でリスト表示させるとF2:F10の勘定科目の名称が表示されます。（シニア）

  お久しぶりの書き込みになってしまいました・・・。申し訳ありません。

 >A1とB1のフィールド名を結合するのではなく、･･･

  元々、一覧でデータを入力したシートでのフィールド名が結合セルに入力されていたため、
 空きシートにコピペをした際にも、同様の形式となっておりました。
 しかし、フィルタオプションを使用するために、空きシートに貼り付けたほうのフィールド名を、
 A列・B列それぞれに分けて記入したところ、上手くいきました！

  また、その後のリスト表示についても、上手く表示させることができました。
 ありがとうございます　(^o^)

  これまで、いろいろとご丁寧に教えていただきましたが、
 自分がどういう表を作成しようとしているのか、
 ここへ来て、よく分からなくなっておりますので、
 一度、仕様を明確にしたいと考えています　(*_*)
 （どういう表にするかは上司からお任せされてるのですが、
 どういう表が求められてるのか、いまいち把握できていないのです・・・・・。
 「教えてください」とお願いした立場であるのに、ごめんなさい。。。。）
 仕様を明確にした後、またお力を貸していただくことがあるかもしれませんが、
 その時はよろしくお願いいたします。
　　　　　　　　　　　　　　　　　　　　　　　　（Tear）

 データ処理する基本の表が、各列をフィールドと言い、各行をレコードと言います。
表の上端行の名前をフィールド名といいます。
１レコードに１つのデータを入力していきます。
決して、列結合や行結合をしないことです。
データ処理はメニューデータにあるすべての処理がこの約束の表になっています。
データベース処理の基本が理解できたので、上司の任された仕事を仕上げて下さい。
 （シニア）

 シニアさんがまとめの書き込みをしてくださらなかったら
 理解があいまいのままになるところでした(*_*)
 ありがとうございます！！

  ◇1レコードに1つのデータを入力
  ◇列や行の結合をしない
 となると、現在私が作成しているものはデータベースとは言えないんですね（＞＿＜。。。 
 フィールド名は結合してあるし、データもブランクのところが多々あるし。
 まだまだ勉強することがたくさんあります！シニアさんありがとうございました！！　（Tear）

コメント返信：

[ 一覧(最新更新順) ]

[[20040308181215]] 『ある条件に合致するものの合計値』（Tear） ページの最後に飛ぶ

[[20040308181215]]　『ある条件に合致するものの合計値』（Tear）　ページの最後に飛ぶ