• ベストアンサー
  • すぐに回答を!

Excelで複数条件を満たし残高を計算する関数

   A列     B列  C列     D列     E列  F列    日付    No.商品名  仕入/販売  金額 残高 1  2010/12/1  101  りんご   仕入  ¥1,000    ¥- 2  2010/12/2  101  りんご   販売  ¥1,000    ¥- 3  2010/12/3  122  みかん   仕入  ¥1,200    ¥400 4  2010/12/4  122  みかん   販売  ¥800     ¥- 5  2010/12/5  135  バナナ   仕入  ¥1,300    ¥800 6  2010/12/6  135  バナナ   仕入  ¥300     ¥- 7  2010/12/7  135  バナナ   販売  ¥800     ¥- 8  2010/12/8  148  ぶどう   仕入  ¥2,000    ¥- 9  2010/12/9  148  ぶどう   販売  ¥1,500    ¥- 10 2010/12/10  148  ぶどう   販売   ¥500 ¥- 上記のようなEXCELの表があります。 B列No.(第一条件)、C列商品名(第二条件)が一致するものを 仕入から販売を引いてF列のような数字になるように残高を出したいです。 どんな関数でも構いません。 関数でできる方法があれば教えてください。 実際のデータ量が2000行くらいあるので手作業でするのが大変で…。 よろしくお願いしますm(_ _)m OS:WindowsXP Office:2007

共感・応援の気持ちを伝えよう!

  • 回答数6
  • 閲覧数236
  • ありがとう数5

質問者が選んだベストアンサー

  • ベストアンサー
  • 回答No.1
  • MackyNo1
  • ベストアンサー率53% (1521/2850)

B列とC列の値は対応しているように見えますが、もし違うなら以下のような数式になります. =IF(COUNTIF($B$2:B2,B2)=1,SUMPRODUCT(($B$2:$B$2000=B2)*($C$2:$C$20=C2)*($D$2:$D$2000="仕入")*$E$2:$E$2000)-SUMPRODUCT(($B$2:$B$2000=B2)*($C$2:$C$2000=C2)*($D$2:$D$2000="販売")*$E$2:$E$2000),"") B列とC列の値が対応しているなら以下のような数式で表示できます。 =IF(COUNTIF($B$2:B2,B2)=1,SUMPRODUCT(($B$2:$B$2000=B2)*($D$2:$D$2000="仕入")*$E$2:$E$2000)-SUMPRODUCT(($B$2:$B$2000=B2)*($D$2:$D$2000="販売")*$E$2:$E$2000),"")

共感・感謝の気持ちを伝えよう!

質問者からのお礼

素早い回答ありがとうございました。 実際の表にこちらの関数を入れ込んだところだいぶ近い結果を得ることができました。 ただ実際の表がもうちょっと複雑なので修正しているところなのですが うまく正しい結果が出ません(泣) 私の質問の仕方がいけなかったのかもしれないので 再度実際の表にもっと近い状態にして質問させていただきます。 このたびはありがとうございました。

関連するQ&A

  • Excel複数条件を満たし残高を計算する関数(続)

    以前、下記の質問をさせていただきましたが、うまく稼働しないため(私の質問の仕方が悪く…) http://okwave.jp/qa/q6382052.html 再度実際の表に近い状態で質問させていただきます。 よろしくお願いします。 A列  B列   C列   D列   E列    F列   G列   日付  No.   商品名  金額  数量   区分   残高 1  12/1  101  りんご   7000   10   売上   0 2  11/2  101  りんご   7000   10   在庫   0 3  10/3  101  りんご   15000   15   在庫  15000 4  12/4  205  いちご   8000   10   売上   0 5  10/4  205  いちご   8000   10    在庫   0 6  11/5  205  いちご   8000   15    在庫   8000 7  10/5  306 バナナ   9000   10   在庫   2000 ←在庫のうち一部販売している 8  11/5  306  バナナ   7000    8   売上   0 9  12/3  411  みかん   2000   10   仕入   500 10 11/2  411  みかん   1000     5   在庫   0 11 12/5  411   みかん  1500    8   売上   0 12 12/3  523   ぶどう   2000   10   仕入   0 13 11/2  523   ぶどう   2000    10   売上   0 14 12/5  523  マスカット  1500  8    仕入   0 15 12/1  523  マスカット 1500    8    売上   0 上記のような表があります。 B列No.(第一条件)、C列商品名(第二条件)が一致して 在庫、仕入がある場合は在庫+仕入-売上、 在庫のみの場合は在庫-売上、 在庫がない場合は在庫-売上 になるようにG列のような数字になるように残高を出したいです。 ちなみに「いちご」のように在庫が複数ある場合がありますが その場合は、一番最新の日付の在庫として残高を出したいです。 またNo.は同じですが、「ぶどう」「マスカット」のように商品名が同一ではないものもあります。 それぞれの商品によって条件が違うので改行でわかりやすく表示しています。 1つの関数で処理することは可能でしょうか? 1つの関数で処理するのが困難であれば、いくつかの関数に分けて処理する方法でも構いません。 またどんな関数でも構いません。 関数でできる方法があれば教えてください。 実際のデータ量が2000行くらいあるので手作業でするのが大変で…。 よろしくお願いしますm(_ _)m

  • Excelで複数条件を満たし残高を計算する関数

    画像のようなEXCELの表があります。 B列No.(第一条件)、C列商品名(第二条件)が一致するものを 仕入から販売を引いてF列のような数字になるように残高を出したいです。 どんな関数でも構いません。 関数でできる方法があれば教えてください。 実際のデータ量が2000行くらいあるので手作業でするのが大変で…。 よろしくお願いしますm(_ _)m OS:WindowsXP Office:2007

  • 行と列の条件に一致したら印を付けたい

    シート1に名前と商品のリストがあります 名前 商品 佐藤 りんご 佐藤 みかん 佐藤 ぶどう 加藤 みかん 山本 バナナ 山本 みかん 小林 りんご 小林 ぶどう 小林 みかん 小林 バナナ シート2の行に名前、列に商品名の表があり、シート1のリストを元に 行と列の条件に一致した箇所に印をつけたいのですが、 何か適当な関数はありますでしょうか? りんご みかん ぶどう バナナ 佐藤 加藤 山本 小林

その他の回答 (5)

  • 回答No.6
  • CoalTar
  • ベストアンサー率40% (705/1742)

#2です 計算負荷が気になるようでしたら、もう一工夫です =IF(D2="販売",0, IF(SUMPRODUCT(($B$2:$B2=B2)*($C$2:$C2=C2))<>1,0, SUMPRODUCT((B2:$B$2000=B2)*(C2:$C$2000=C2)*(D2:$D$2000={"仕入","販売"})*{1,-1}*E2:$E$2000))) 下へオートフィル、「販売」の数にもよりますが、負荷は半分以下になると思います。 2000行で試験し、当方では2秒足らずの計算が1秒足らずになりました。 OFFSET関数はSUMPRODUCT関数より凶悪です。 計算に関係のないセルに入力したり、削除するだけで10秒近く待たされます。 kagakusukiさん ぜひ、試験してみてください。 検索キーワードは 「揮発性関数」 INDIRECT関数もその部類入ります。

共感・感謝の気持ちを伝えよう!

  • 回答No.5

 回答番号:ANo.4です。  もし、B列とC列の値は対応していて、且つB列の値が同じ値となっている複数の行の中には、仕入が必ず一回しか現れないと決まっている場合には、次の方法が使用可能になります。  F2セルに次の数式を入力してから、F2セルをコピーして、F列の他のセルに貼り付けて下さい。 =IF(AND($D1="仕入",SUMIF($B:$B,$B1,$E:$E)<$E1*2),$E1*2-SUMIF($B:$B,$B1,$E:$E),"-")

共感・感謝の気持ちを伝えよう!

  • 回答No.4

>SUMPRODUCT関数などを使いますとパソコンの動きが重くなります。 と仰る方がいますので、SUMPRODUCT関数を使用した割には計算負荷が多少軽くなる数式を紹介させて頂きます。  データが入力されている最も下の行番号と、仕入れの商品Noが最初に現れる行番号に応じて、計算処理を行う範囲が自動調整されますので、表が2000行以上ある場合にも対応します。 ¥- の表記部分は0として F2セルに次の数式を入力してから、F2セルをコピーして、F列の他のセルに貼り付けて下さい。 =IF(COUNTIF($A2:$E2,"")>0,"",IF($D2="仕入",SUMPRODUCT((OFFSET($B$1,MATCH($B2,$B:$B,0)-1,,MATCH(9^9,$B:$B)-MATCH($B2,$B:$B,0)+1)=$B2)*(OFFSET($C$1,MATCH($B2,$B:$B,0)-1,,MATCH(9^9,$B:$B)-MATCH($B2,$B:$B,0)+1)=$C2)*OFFSET($E$1,MATCH($B2,$B:$B,0)-1,,MATCH(9^9,$B:$B)-MATCH($B2,$B:$B,0)+1)*(1-2*(OFFSET($D$1,MATCH($B2,$B:$B,0)-1,,MATCH(9^9,$B:$B)-MATCH($B2,$B:$B,0)+1)<>"仕入"))),"-"))

共感・感謝の気持ちを伝えよう!

質問者からのお礼

とても早い回答ありがとうございました。 実際の表に合わせて細かく関数を修正しながら入力してみたのですが うまく結果が出ませんでした。 私の質問の仕方が悪かったので再度実際の表に近い状態にして質問させていただこうと思います。回答いただいたのに申し訳ありません。。。 ありがとうございました。

  • 回答No.3
  • KURUMITO
  • ベストアンサー率42% (1835/4283)

SUMPRODUCT関数などを使いますとパソコンの動きが重くなります。 作業列などを使って対応するのがよいでしょう。 例えばデータは2行目から下方にあるとしてH2セルには次の式を入力して下方にオートフィルドラッグします。 =B2&C2&D2 答えのF列にはF2セルに次の式を入力して下方にオートフィルドラッグします。 =IF(OR(B2="",D2="販売",COUNTIF(H$2:H2,H2)>1,SUMIF(H:H,B2&C2&"仕入",E:E)-SUMIF(H:H,B2&C2&"販売",E:E)=0),"",SUMIF(H:H,B2&C2&"仕入",E:E)-SUMIF(H:H,B2&C2&"販売",E:E))

共感・感謝の気持ちを伝えよう!

質問者からのお礼

とても早い回答ありがとうございました。 実際の表に合わせて細かく関数を修正しながら入力してみたのですが うまく結果が出ませんでした。 私の質問の仕方が悪かったので再度実際の表に近い状態にして質問させていただこうと思います。回答いただいたのに申し訳ありません。。。 ありがとうございました。

  • 回答No.2
  • CoalTar
  • ベストアンサー率40% (705/1742)

¥- の表記部分は0として F2セルに =IF(SUMPRODUCT(($B$2:$B2=B2)*($C$2:$C2=C2))<>1,0, SUMPRODUCT((B2:$B$2000=B2)*(C2:$C$2000=C2)*(D2:$D$2000={"仕入","販売"})*{1,-1}*E2:$E$2000))

共感・感謝の気持ちを伝えよう!

質問者からのお礼

とても早い回答ありがとうございました。 実際の表が仕入/販売以外にもあったりして 細かく関数を修正しながら入力してみたのですが うまく結果が出ませんでした。 私の質問の仕方が悪かったので再度実際の表に近い状態にして質問させていただこうと思います。 ありがとうございました。

関連するQ&A

  • エクセルで複雑条件での抽出

    特定の商品名を抽出したいと考えています。 商品   等級 -------------- りんご  1級 りんご  2級 みかん  2級 みかん  3級 みかん  4級 もも   1級 もも   3級 ぶどう  2級 ぶどう  4級 上記のような表があります。 抽出したいのは、「2級」の商品だけですが、同じ商品で「1級」があると抽出したくないです。 つまりこの表だと、「みかん」「ぶどう」の商品名を抽出したいです。 りんごは1級があるので、抽出したくありません。 このような場合は、フィルタでできるのでしょうか? もしくは関数などでできるならそれでも良いです。 色々考えてみましたが、うまくいきません。 どうぞお知恵をお貸しください。 よろしくお願い致します。

  • Excelでの文字列とりだしについて

    Excel(version2013)について質問です。 ある文字列を検索範囲内で検索し、みつかった場合、 その文字列を返すようにしたいです、どのような関数を使えば宜しいでしょうか。 例えば下記A列に検索したいリスト(りんご、ばなな、ぶどう)があり、 検索したい範囲がD1~E5とします。 その場合、B列に関数を使った式を入れ、 りんご、ばなな、空文字、りんご、ぶどうと並ぶようにしたいです。 どのような関数を使えば宜しいでしょうか。       A         B      C      D        E 1  りんご   (式)        りんご  みかん 2  ばなな  (式)        ばなな すいか 3  ぶどう   (式)        すいか みかん 4              (式)        りんご  すいか      5              (式)        ぶどう  みかん                                      

  • エクセルの関数について教えてください

    例えば、A列に「りんご」「みかん」「ぶどう」「梨」「洋梨」とあり(空白のセルもあります)、このA列に「りんご」もしくは「梨を含む」場合に、B列に「○」を表示させたいのですが、関数でできますか? 「りんご」だけとか「梨を含む」というように単一の条件で指定する方法はわかるのですが…。 職場の管理表で使いたいのですが、どうしても解決できないので、お力を貸してください。 どうかよろしくお願いします。 ちなみに以下のような結果を求めています。    A   B 1 みかん 2 りんご ○ 3 梨   ○ 4 ぶどう 5 洋梨  ○ 6 ぶどう 7 りんご ○

  • excelの関数で数式を教えてください。

    下記のような2つのexcelシートがある場合で、条件にあった場合の数値だけを合計したい場合、どのような関数を使えば良いのでしょうか?教えてください。 具体的にはAさんのカロリー数値の合計(答えが4.5) (シート1)    A    B    C   D   &#65293;&#65293;&#65293;&#65293;&#65293;&#65293;&#65293;&#65293;&#65293;&#65293;&#65293;&#65293;&#65293; 1|Aさん みかん りんご バナナ 2|Bさん ぶどう なし (シート2)   A    B   &#65293;&#65293;&#65293;&#65293;&#65293;&#65293;&#65293; 1|みかん 1.5 2|りんご  2.3 3|バナナ 0.7 4|ぶどう  3.1 5|なし   1.2 宜しくお願いします。

  • DATEIF関数の算出結果の合計平均を出す方法の件

    品名No.毎の平均期間の算出を希望致します。 収穫日(D列)と購入日(C列)から期間を算出し(算出結果:E列)、 算出結果(E列)を活かし、品名No.毎の期間平均の算出を希望しております。 結果が下記のようになります。 000101⇒平均期間(0年01ヶ月) 000103⇒平均期間(0年01ヶ月) 000104⇒平均期間(2年10ヶ月) E列に入っている関数は下記になります。 =(DATEDIF(D4,C4+1,"Y")+(DATEDIF(D4,C4+1,"YM")=11))&"年"&MOD(DATEDIF(D4,C4+1,"YM")+   (DATEDIF(D4,C4+1,"MD")>0),12)&"ヶ月"  A列   B列   C列      D列      E列   品名NO 商品名 購入日   収穫日    期間 000101 みかん 2005/10/30 2005/10/01 0年1ヶ月 000101 みかん 2005/12/20 2005/12/01 0年1ヶ月 000101 みかん 2005/12/19 2005/12/01 0年1ヶ月 000101 みかん 2006/01/11 2006/01/01 0年1ヶ月 000101 みかん 2006/01/11 2006/01/01 0年1ヶ月 000101 みかん 2006/11/02 2006/11/01 0年1ヶ月 000101 みかん 2006/11/02 2006/11/01 0年1ヶ月 000101 みかん 2006/11/06 2006/11/01 0年1ヶ月 000103 ばなな 2007/10/25 2007/10/01 0年1ヶ月 000103 ばなな 2007/10/25 2007/10/01 0年1ヶ月 000103 ばなな 2007/10/25 2007/10/01 0年1ヶ月 000103 ばなな 2008/08/22 2008/07/01 0年2ヶ月 000103 ばなな 2010/11/18 2010/11/01 0年1ヶ月 000103 ばなな 2010/12/22 2010/12/01 0年1ヶ月 000103 ばなな 2004/12/27 2004/12/01 0年1ヶ月 000103 ばなな 2005/01/12 2005/01/01 0年1ヶ月 000103 ばなな 2010/12/01 2010/11/01 0年2ヶ月 000104 りんご 2003/10/08 2001/07/01 2年4ヶ月 000104 りんご 2004/06/01 1998/04/01 6年3ヶ月 000104 りんご 2005/09/01 2000/04/01 5年6ヶ月 000104 りんご 2005/12/14 2005/12/01 0年1ヶ月 000104 りんご 2005/12/19 2005/12/01 0年1ヶ月 フィルタをかけて抽出していけば良いのですが、数が大量すぎて手作業では 膨大な時間がかかってしまいます。 本当に分からなくご質問した次第です。 よろしくお願い致します。

  • あるセルの文字列が条件で、あるセルに色を設定するには?(2)

    例えば、下のような表で      A      B      C       D      1   商品名   数量     名前    2   りんご     2     さとう 3   みかん     1     さとう     3 4   ぶどう     1     すずき 5   ばなな     2     すずき     3 6   みかん     3     たなか 7   りんご     2     たなか 8   ぶどう     2     たなか     4 「みかん」という商品名の時だけ、C列の重複している名前の一番下のセルに、黄色を設定したい場合は、どうしたらよいのでしょうか? つまり、セルC3とC8に色をかけたいのです。不可能でしょうか(-_-;)ちなみにD列は、重複している名前の一番下の行に合計が出る関数が入っています。一番下が難しいなら、みかんの商品があるうちの重複している名前(さとう・たなか)すべてに色がついてもかまいません。 どなたかいい方法があれば教えてください。よろしくお願いします(>_<)

  • Excelの関数を使用したデータ抽出

    Excelの関数を使用したデータ抽出 Excelの関数を使用して以下のようなデータ抽出をすることは可能でしょうか? Aに以下の基本データがあります。 --------------- 型番 商品 価格 受注 出荷 仕入 No AAA りんご 100 1/1 1/1 80 001 AAA りんご 120 1/3 1/3 80 002 BBB みかん 200 1/1 1/1 90 003 BBB みかん 210 1/5 1/5 90 004 BBB みかん 220 1/5 1/5 90 005 --------------- Bに型番のみのデータがあります。 --------------- 型番 商品 価格 受注 出荷 仕入 No AAA BBB BBB CCC AAA AAA AAA CCC BBB BBB BBB --------------- Aから抽出したデータをBに反映させたいです。 以下のような形です。 --------------- 型番 商品 価格 受注 出荷 仕入 No AAA りんご 100 1/1 1/1 80 001 BBB みかん 200 1/1 1/1 90 003 BBB みかん 210 1/5 1/5 90 004 CCC ぶどう AAA りんご 120 1/3 1/3 80 002 AAA りんご AAA りんご CCC ぶどう BBB みかん 220 1/5 1/5 90 005 BBB みかん BBB みかん --------------- 宜しくお願い致します。

  • エクセル関数で複数条件を 探す

    表1で、A1 りんご B1 あまい       A2 ぶどう B2 まずい という表があります。   表2で、A1 りんご少しあまい       A2 ぶどうとてもまずい という表があります。  表1の「りんご」と「あまい」のふたつの条件を満たすものを表2のA列で探すという作業をしているのですが、関数を使って簡単に探す方法はありますか?  ちなみに今は 表1のA1とB1のふたつの条件を「りんご*あまい」としてCtrl+Fで 表2のA列で検索して 一致したら ○を 横につけていくという地道な作業をしています。4000件近くありますので何かいい方法ありましたらよろしくお願いいたします。

  • エクセル関数について

    例えば次の表があって   A   B   C D    E    F   G 1店名 商品名 金額  店名 商品名 合計 2東京 りんご 120 東京 りんご 3大阪 みかん 130 大阪 みかん 4東京 りんご 120 京都 ばなな 5京都 ばなな 150 Gに店名と商品名が合致する合計を出したいので、教えてください。G2のセルには240というようにです。

  • エクセル 同名の場合はB列にC列の数値を入れたい!

     A列  B列   C列     D列 りんご        50    みかん みかん        30    ぶどう いちご        20    ばなな ぶどう        40    りんご ばなな        60     いちご ↑上記のような表があります。 もしA列とD列が同名であればC列の金額をB列に表示したいのですがよい関数はありますでしょうか?? おわかりの方教えてくださーい!