エクセルで、特定のセル文字によって、足し算する行をピックアップして合計を求める方法

すみませんが、わかる方教えて下さい。
SUMIFをためしましたが、うまく計算できませんでした。
やりたいことは、添付原産国の列から、例えば日本の場合の見積もり合計と実際価格の合計を算出できる計算をしたいです。事前に雛形のフォーマットを用意し、そこに計算式を入れておいて、入力と同時に、合計金額が算出されるようにしたいです。原産国入力欄は、ランダムに日本や、中国など入力されます。
関数、マクロなど不慣れなのでわかる方いらっしゃれば是非教えていただけませんでしょうか？

質問者からの補足コメント

• 回答ありがとうございました。
  仮の条件は、全て正しいです。
  早速、計算式を当てはめて、
  計算しましたが、エラーになりました。
  再度、やり直してみますが、
  E1の計算式の中にE9:E25があります。
  E9から、E25まで見積もり価格意外の
  情報もあり、この中から、見積もり価格
  を識別する計算式が欲しいような気がしましたが、いかがでしょうか？？
  これから頂いた回答をじっくり見て考えてみます

    補足日時：2017/01/20 19:40

• ありがとうございます。
  教えて頂いた計算式を当てはめた
  結果、正しい回答が出ることを確認
  できました。
  できれば、sheet1に合計金額を算出
  することはできませんでしょうか？
  無理言って、申し訳ありません。

    補足日時：2017/01/20 23:54

No.10 ベストアンサー 回答者： yuji3690 回答日時： 2017/01/24 13:01

空白のセルは日本とは一致していないという判断がされるのでFALSEと判定されます。

FALSEは数字でも文字でもありませんが、*1をする事で0に変換されます。

○:△と□:☆を用いた場合、まず○と□で判定します。
次は○の次と□の次で判定します。
○と□の次や○の次と□で判定されることはありません。
両方同じずつ進んでいき、△と☆が同時に出るとその判定で終わりです。
△か☆どちらかが先に出ると(数が合ってなかった場合)少ない数の方は0と判断されるので、多い方もないのと同じです。

最初の判定で、国名の表示されるタイミングで一致するものを見積価格にしているので、同じ数だけ下がった他の国名の時も見積価格と一致するタイミングなのです。

この回答へのお礼

ありがとうございました。
E9から始まっている理由がわかりました。エクセルはすごく利口なんですね。使いこなせたら相当仕事をするがなりますね。
いろいろご教示していただきありがとうございました。

お礼日時：2017/01/24 23:05

No.9 回答者： yuji3690 回答日時： 2017/01/22 12:08

E9:E25だけでなくC7:C23もありますね。

SUMPRODUCTというのは数学の配列のような物なのですが、

SUMPRODUCT((C1=C7:C23)*1　の部分は、まずC7とC1が一致しているかどうか判定し、TRUEかFALSEかを出します。そして*1によりTRUE=1、FALSE=0と変換されます。
この結果が日本の場合は(1←日本,0,0,0,0←アメリカ,0,0,0,1←日本,0,0,0,0←中国,0,0,0,0←アメリカ)、アメリカの場合は(0←日本,0,0,0,1←アメリカ,0,0,0,0←日本,0,0,0,0←中国,0,0,0,1←アメリカ)というふうに保存されています。
分かり易いように←国名としてますが、実際は0と１だけですよ。矢印の無い0は空白のセルなので0です。

そしてSUMPRODUCT内で「,」によって区切られると、次の配列を計算します。
,(E9:E25))　の部分ですね。これはそのまま(E9,E10,E11…E25)となります。

並べるとわかりやすいですが（日本の場合で書きます）
( 1 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 1 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 )
(E9,E10,E11,E12,E13,E14,E15,E16,E17,E18,E19,E20,E21,E22,E23,E24,E25)
これらがそれぞれ対応するものと掛け合わされます。

結果
(E9,0,0,0,0,0,0,0,E17,0,0,0,0,0,0,0,0)
となっています。

これを最後に合計して
(E9+E17)=(1100+1500)=2600
と表示されるのです。

つまり、C7に対応するのがE9、C23に対応するのがE25、と設定している式だということです。

この回答へのお礼

すいませんが、計算式の理屈をもう少し教えて下さい、、。日本0,0,01は、C15が日本なので、1になるのはわかるのですが、0,0,0は
C12C13C14が選択されてるのでしょうか？それと、見積もり価格だけピックアップされて合計されているのは、最初の見積もり価格のE5を選択している事が必要なのでしょうか？
難しい計算だとおもいますが、
すごくシンプルな計算式なので、
しっかりと理解しておきたいです。
お手数おかけ致します

お礼日時：2017/01/24 06:52

No.8 回答者： yuji3690 回答日時： 2017/01/21 17:21

ごめんなさい。

あの式で表示できるわけないですね…
今見ると何であれでいけると思ったのか。。。

E1=SUMPRODUCT((C1=C7:C23)*1,E9:E25)
でどうでしょう？
元々国名に対応するセルは見積価格のセルだけなので、文字だった場合に･･･とか考える必要なかったです。

この回答へのお礼

ありがとうございます。
頂いた計算式を見様見真似で、
実際価格へも式を入れたら正しい値となりました。計算式の理解はこれから行いたいと思いますが、
最初に一つだけ教えていただけませんでしょうか？
頂いだ計算式で、E9:E25とありますが、どういう理論で、見積もり価格の価格のみ、ピックアップすることになるのでしょうか？

お礼日時：2017/01/21 23:32

No.7 回答者： ママチャリ 回答日時： 2017/01/21 12:08

後からの口出しで申し訳ないのですが、そもそもこのシートの構成は感心しません。

各項目は横に展開すべきです。もしレイアウトの変更ができないようであれば、せめて作業列だけでも横に展開すべきです。
添付の画像を例にすると各作業列に次の式を入力し、下へ引っ張ってください。
これにより、集計する式が簡単になると思います。

【F2】=IF($B2="","",$E2)
【G2】=IF($B2="","",$E3)
【H2】=IF($B2="","",$E4)
【I2】=IF($B2="","",$E5)

この回答へのお礼

連絡ありがとうございます。
確かにそうですね、元の表の工夫も考える必要があると思いますので、考えてみます。
今回は、質問がしたいことが伝わるように、参考例として表を作ったので、そもそも表の作りがおかしく見えてる部分もあったと思います。煩わしくしてすみません。
それとアドバイスありがとうございました

お礼日時：2017/01/21 13:12

No.6 回答者： tom04 回答日時： 2017/01/21 11:10

No.2・5です。

＞A〜F列に結果を表示したいのですが・・・

表の配置によって当然数式が変わってきますが、
↓の画像のような配置の場合です。
やはり作業用の列を設けます。
作業列F10セルに
=IF(D10="","",IF(C10="",F9,C10))
という数式を入れ下へずぃ～～～！っとフィル＆コピー！

そしてE2セルに
=SUMIFS(E$10:E$1000,D$10:D$1000,D2,F$10:F$1000,OFFSET(D2,(MOD(ROW(A1),2)=0)*-1,-1))

という数式を入れフィルハンドルで下へコピーしています。

※　数式の説明をすると・・・
基本は「SUMIFS関数」です
=SUMIFS（合計対象範囲,条件範囲1,条件1,条件範囲2,条件2)
という並びになりますので、
合計対象範囲　→　E10～E1000
条件範囲1　　→　D10～D1000
条件1　　　　→　D2
条件範囲2　　→　F10～F1000(作業用の列）
条件2　　　　→　ここでOFFSET関数を使っています。
OFFSET関数の基準（参照セル）は最初の行がD2セル　→　その下の行がD3セル・・・

D列セルを基準とします。
列に関しては「基準セル」から「-1」としていますので、常にC列を参照するようにしています。

行に関して少し手をかけています。
数式が入っているセルの行番号　ROW(A1)　としているので「1」となり次の行が「2」となる
(MOD(ROW(A1),2)=0)
で、式を入れている行番号が偶数の場合は「TRUE」（1）、奇数行の場合は「FALSE」（0）となり
それに「-1」を掛けているので行は基準セルからみると
偶数の場合は「-1」すなわち1行上、奇数の場合は「0」で同じ行！
結局、数式を入れている最初の行から奇数行はその行のC列を、偶数行はその一つ上の行のC列を条件2としている。といったコトになります。

SUMIFS関数の
E2・E3セルの両セルともC2セルが条件2
E4・E5セルの両セルともC4セルが条件2

といった感じで下へ数式が続きます。m(_ _)m

この回答へのお礼

すいません、何度もありがとうございます。
計算式はなかなか理解まで行き着いてませんが、教えて頂いた計算式で、求める表ができそうです。
実際の表は、もう少し複雑なので、休み明けに完成させていきます。このあと、少しの応用がきけるよう計算式の理解に努めます。
行き詰まったときは、相談にのっていただければありがたいです

お礼日時：2017/01/21 13:19

No.5 回答者： tom04 回答日時： 2017/01/21 00:30

No.2です。

＞できれば、sheet1に合計金額を算出
＞することはできませんでしょうか？

どちらの回答者に対する補足なのでしょうか？
とりあえず当方だとして・・・

↓の画像のような配置だとします。。
列全体を参照するようにしていますので、A～F列内に結果を表示させたい場合は
条件範囲の行を指定する必要があります。

今回もF列を作業用の列としています。
画像ではJ1セルに
=SUMIFS(E:E,D:D,I1,F:F,OFFSET(I1,(MOD(ROW(A1),2)=0)*-1,-1))
という数式を入れフィルハンドルで下へコピーしています。m(_ _)m

この回答へのお礼

ありがとうございました。
推測通り、A〜F列に結果を表示したいのですが、その場合計算式の
どこを変えればよいか、具体的に
教えていただけるとありがたいです。計算式の内容が難しく理解できないので、、(*_*)

お礼日時：2017/01/21 10:09

No.4 回答者： yuji3690 回答日時： 2017/01/21 00:01

sheet1に合計金額とはどういう意味でしょう？

このシートはsheet1ですか？
それとも別のシートに表示したいということですか？
合計金額とは何の合計金額ですか？

この回答へのお礼

説明不足ですみませんでした。
tom04さんの回答に対してのお願いでした。結果表示をsheet2ではなく、sheetへ表示したかったのです。

お礼日時：2017/01/21 10:33

No.3 回答者： yuji3690 回答日時： 2017/01/20 23:43

失礼しました。

E1=SUMPRODUCT((COUNTIF(C1,C7:C23)=1),(E9:E25＞0)*1,E9:E25)
でどうでしょうか？

この回答へのお礼

ありがとうございます。
計算式を入れてみましたが、
エラーにはなりませんが、0でした。再度確認してみたいとおもっていますが、何か計算式の修正が必要であれば再度教えていただければ、ありがたいです。
それと、E列は4桁と連絡しましたが、5桁、7桁も混在しておりました。すみません。

お礼日時：2017/01/21 13:07

No.2 回答者： tom04 回答日時： 2017/01/20 22:41

こんばんは！

元データはSheet1にあり、Sheet2に表示するとします。
配置は↓の画像のようになっているという前提です。
（セル結合、もしくは最初の行だけデータが入り空白セルが存在している）

Sheet1に作業用の列を設けた方が簡単だと思います。
作業列F2セルに
=IF(D2="","",IF(B2="",F1,C2))
という数式を入れフィルハンドルでこれ以上データは増えない！というくらいまで下へコピー！

そしてSheet2のC1セルに
=SUMIFS(Sheet1!E:E,Sheet1!D:D,B1,Sheet1!F:F,INDIRECT("A"&INT(ROW(A2)/2)*2-1))
という数式を入れフィルハンドルで下へコピー！

これで画像のような感じになります。m(_ _)m

この回答へのお礼

こんばんは、週末の夜に回答いただきありがとうございます。

お礼日時：2017/01/20 23:55

No.1 回答者： yuji3690 回答日時： 2017/01/20 14:25

説明のために、画像の例でタイトル行（商品、原産国、項目、内容　という文字のある行）を6行目、番号が書かれているのがA列。

として、各情報は部品番号～実際価格の４行で固定である（3行や５行といったものはない）と仮定します。

E1（日本の見積価格合計が計算されているセル）の計算式は
=SUMPRODUCT((COUNTIF(C1,C7:C23)=1)*(E9:E25))
となります。
これは、C7～C23（原産国の入力されている範囲）においてC1（この場合日本）と一致した場合（COUNTIF()=1という部分がそれを意味します）に、対応するE9～E25（見積もり価格の表示されている範囲）のデータを取ってきて、それを合算する。　というものです。

E2（日本の実際価格の合計が計算されているセル）であれば、
=SUMPRODUCT((COUNTIF(C1,C7:C23)=1)*(E10:E26))
見積もり価格の範囲（E9:E25）が実際価格の範囲（E10:E26）に変わっています。

アメリカ（E3,E4）の場合は、それぞれE1,E2の式のC1と入力されている所をC3に変更すれば完了です。

範囲を事前に未入力の部分にまで拡大させておけば、入力した時点で結果は反映されますが、
あまり範囲を大きく取り過ぎ（シートの下の端までとか）の場合は、重くなってしまいますので、あまり広げ過ぎないようにしましょう。

この回答へのお礼

早々の回答ありがとうございました

お礼日時：2017/01/20 19:43