Excel複数条件を満たし残高を計算する関数
- Excel複数条件を満たし残高を計算する関数を作成することは可能です。
- 質問文章の表にある条件を考慮して、在庫、仕入、売上の値から残高を計算する関数を作成します。
- また、商品ごとに異なる条件を考慮する必要があるため、複数の関数を組み合わせて処理することも可能です。
- ベストアンサー
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
- momosuke111
- お礼率57% (8/14)
- オフィス系ソフト
- 回答数6
- ありがとう数3
- みんなの回答 (6)
- 専門家の回答
質問者が選んだベストアンサー
1. 日付を降順で並べ替える (優先順位が4番目になる) 2. データ 並べ替え 最優先 No. 昇順 2番目 商品名 昇順 3番目 区分 [オプション]ふりがなを使わない 3. 文字の連結 G2セルに =B2&C2 4. 検索範囲の行番号 H2セルに =IF(G1=G2,"",MATCH(G2,G$2:G$2000)+1) 5. 残高 I2セルに =IF($H2="","",SUM( SUMIF($F2:INDEX($F:$F,$H2),{"在庫","仕入"},D2:INDEX(D:D,$H2)), -SUMIF($F2:INDEX($F:$F,$H2),"売上",D2:INDEX(D:D,$H2)))) 6. 残数 I2セルを右へオートフィル 7. 数式のコピー G2:J2セルを選択して フィルハンドルをダブルクリック 気分的には 4.の検索範囲の行番号は =IF(G1=G2,"",ROW()+COUNTIF(G3:G$2500,G2)) にしておきたいし、そこまで計算速度にこだわらないと思うので。
その他の回答 (5)
- 某HN クロメート(Chromate)(@CoalTar)
- ベストアンサー率40% (705/1742)
#5です 在庫と仕入が同じ日で最新だと以下の式でダブルが、どちらが優先だろうか? =IF($F2="売上","", IF(MAX(($B2=$B$2:$B$2000)*($C2=$C$2:$C$2000)* ($F$2:$F$2000={"在庫","仕入"})*$A$2:$A$2000)<>$A2,"", SUMPRODUCT(($B2=$B$2:$B$2000)*($C2=$C$2:$C$2000)* ($F$2:$F$2000={"在庫","仕入","売上"})*D$2:D$2000*{1,1,-1}))) [Ctrl]+[Shift] +[Enter] で確定、配列数式です({}で囲まれる) 右へ下へオートフィル 計算速度無視。質問者の理解無視。単なる自己満足です。
- tom04
- ベストアンサー率49% (2537/5117)
こんばんは! 横からお邪魔します。 一例です。 データが2000行位あるということなので、極力配列数式は使わないように↓の画像のような感じで 作業列を3列設けています。 (だたし、どうしても配列数式を一つ使ってしまいました) Sheet1の作業列1のI2セルに =C2&F2 作業列2のJ2セルに =IF(COUNTIF($C$2:C2,C2)=1,ROW(),"") K2セルに =IF(A2="","",IF(A2=MAX(IF($C$2:$C$2000=C2,$A$2:$A$2000)),1,"")) このK列だけが配列数式になってしまいますので、この画面からK2セルにコピー&ペーストした後数式バー内で一度クリックしてください。 編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定! 数式の前後に{ }マークが入り配列数式になります。 このI2~K2セルを範囲指定し、K2セルのフィルハンドルで下へずぃ~~~!っとコピー! そして、Sheet2にも表を作っています。 Sheet2のB1セルに =IF(COUNT(Sheet1!$J:$J)<COLUMN(Sheet1!A1),"",INDEX(Sheet1!$C:$C,SMALL(Sheet1!$J:$J,COLUMN(Sheet1!A1)))) という数式を入れ、列方向(右方向)にオートフィルでコピー! これでSheet1の商品名がすべて表示されます。 B2セルには =IF(B$1="","",SUMIF(Sheet1!$I:$I,B$1&$A6,Sheet1!$D:$D)) として「売上」のB4セルまでコピー B5セルは単に =IF(B1="","",B2+B3-B4) 同様に「数量」の表も作っておきます。 B6セルを =IF(B$1="","",SUMIF(Sheet1!$I:$I,B$1&$A2,Sheet1!$E:$E)) としてB8セルまでコピー B9セルは =IF(B1="","",B6+B7-B8) B2~B9セルを範囲指定し、B9セルのフィルハンドルで列(右)方向へオートフィルでコピーします。 これでやっと準備が整いました。 最後にSheet1のG2セルに =IF(K2="","",INDEX(Sheet2!$5:$5,,MATCH(C2,Sheet2!$1:$1,0))) H2セルに =IF(K2="","",INDEX(Sheet2!$9:$9,,MATCH(C2,Sheet2!$1:$1,0))) という数式を入れ、G2・H2セルを範囲指定し、H2セルのフィルハンドルで下へコピーすると 画像のような感じになります。 尚、Sheet2のデータができていないうちはSheet1の作業列に数式を入れてもエラーになるか何も表示されないとおもいますが、 それは無視して淡々と数式を入れてみてください。 以上、長々と書きましたが 外していたらごめんなさいね。m(__)m
お礼
お礼が遅くなり申し訳ありません。 画像を貼り付けてくださりありがとうございました。 ただ商品数がとても多いため別シートに在庫表を作ることを考えていなかったため ほかの方の回答を参考にさせていただくことにしました。 ご回答くださったのに申し訳ありません。 ご丁寧にありがとうございました。
- kagakusuki
- ベストアンサー率51% (2610/5101)
これがもし、在庫の行が無い場合には、仕入の行に集計結果を表示し、在庫の行がある場合には(例え仕入の行があったとしても)必ず最新の在庫の行に表示するのであれば、作業列を必要とせず、1つの関数で処理する事が可能なのですが、仕入と在庫の両方がある場合には、仕入の行に集計結果を表示するという条件では、作業列を使用する方法しか見つける事は出来ませんでした。 今仮に、表が存在しているシートをSheet1であるとし、Sheet2のA列を作業列として使用するものとします。 まず、Sheet2のA1セルに次の数式を入力してから、そのセルをコピーして、A2以下に貼り付けて下さい。 =IF(COUNTIF(Sheet1!$A1:$F1,"")>0,"",IF(SUMPRODUCT((OFFSET(Sheet1!$B$1,MATCH(Sheet1!$B1,Sheet1!$B:$B,0)-1,,MATCH(9^9,Sheet1!$B:$B)-MATCH(Sheet1!$B1,Sheet1!$B:$B,0)+1)=Sheet1!$B1)*(OFFSET(Sheet1!$C$1,MATCH(Sheet1!$B1,Sheet1!$B:$B,0)-1,,MATCH(9^9,Sheet1!$B:$B)-MATCH(Sheet1!$B1,Sheet1!$B:$B,0)+1)=Sheet1!$C1)*(OFFSET(Sheet1!$F$1,MATCH(Sheet1!$B1,Sheet1!$B:$B,0)-1,,MATCH(9^9,Sheet1!$B:$B)-MATCH(Sheet1!$B1,Sheet1!$B:$B,0)+1)="在庫")*(OFFSET(Sheet1!$A$1,MATCH(Sheet1!$B1,Sheet1!$B:$B,0)-1,,MATCH(9^9,Sheet1!$B:$B)-MATCH(Sheet1!$B1,Sheet1!$B:$B,0)+1)>=Sheet1!$A1))=1,Sheet1!$B1&Sheet1!$C1,"")) 次に、Sheet1のG1セルに次の数式を入力してから、そのセルをコピーして、Sheet1のG列とH列のその他のセルに貼り付けて下さい。 =IF(COUNTIF($A1:$F1,"")>0,"",IF(OR($F1="仕入",AND(SUMPRODUCT(($B$2:$B$16=$B1)*($C$2:$C$16=$C1)*($F$2:$F$16="仕入"))=0,$T1<>"")),SUMPRODUCT((OFFSET($B$1,MATCH($B1,$B:$B,0)-1,,MATCH(9^9,$B:$B)-MATCH($B1,$B:$B,0)+1)=$B1)*(OFFSET($C$1,MATCH($B1,$B:$B,0)-1,,MATCH(9^9,$B:$B)-MATCH($B1,$B:$B,0)+1)=$C1)*OFFSET(D$1,MATCH($B1,$B:$B,0)-1,,MATCH(9^9,$B:$B)-MATCH($B1,$B:$B,0)+1)*(LOOKUP(OFFSET($F$1,MATCH($B1,$B:$B,0)-1,,MATCH(9^9,$B:$B)-MATCH($B1,$B:$B,0)+1),{"","仕入","在庫","売上";0,1,0,-1})))+IF(COUNTIF(Sheet2!$A:$A,$B1&$C1)=0,0,INDEX(D:D,MATCH($B1&$C1,Sheet2!$A:$A,0))),"-")) 以上です。
お礼
お礼が遅くなり申し訳ありません。 こちらの関数で検証させていただきましたが OFFSET関数が重たいのか計算に少し時間がかかるようでしたので 別の方の回答を参考にさせていただきました。 ご回答くださったのに申し訳ありません。 ご丁寧にありがとうございました。
- kagakusuki
- ベストアンサー率51% (2610/5101)
幾つか不明な点がありますので追加情報を補足願います。 ●残高をどの行に表示すべきなのかが不明です。 B列とC列が同じデータの中で、F列に「仕入」がある場合には、必ずF列に「仕入」と入力されている行に、残高を表示させればよいのでしょうか? F列に「仕入」が無い場合、B列が「101」でC列が「りんご」のデータでは、F列が「在庫」となっている中で最も古い日付の行に、残高が表示されているのに対して、B列が「205」でC列が「いちご」のデータでは、F列が「在庫」となっている中で最も新しい日付の行に、残高が表示されていますが、残高を表示させる行はどのような規則に従って決まるのでしょうか? ●在庫+仕入-売上の計算が合っていない様ですが、どの様な計算になっているのでしょうか? 例えば、B列が「101」でC列が「りんご」のデータでは、10/3に数量が15で金額と残高が15000であったものが、途中に売上や仕入れが無かったにも関わらず、11/2にはいきなり数量が10で金額が7000、残高が0になっています。 しかも、10/3から11/2の間に数量は3分の2になっただけであるのに、金額は15分の7になっていますから、同じ商品でも単価が異なる事になります。 同じ事が205のいちごでも起きています。 411のみかんでは、11/2に在庫の数量が5で金額が1000あったところへ、12/3に数量が10で金額が2000の仕入れがあったのですから、合計で数量が15の金額が3000ある筈ですが、12/5に数量が3の金額が1500を売り上げただけで、残高が500になっています。 ● >在庫がない場合は在庫-売上 とありますが、在庫が無いのにも関わらず、売る事が出来るのでしょうか? それとも、この部分は、「仕入と売上のみの場合は仕入-売上」の間違いなのでしょうか?
補足
いくつか記載が間違っていたようで申し訳ありません。 一つずつ補足させていただきます。 残高の表示はG列に表示させようと思っています。 「りんご」の残高の表示するところを最新の日付に入力するつもりでしたが 誤って一番古い日付に入力してしまいましたので 「りんご」の残高は11/2の残高欄に15000となります。 残高の計算方法ですがその都度の残高を出したいのではなく 月末等にその月の最終的な残高を出したいので 最後の在庫の行に最終的な残高を入力できたらと思っています。 今気づいてしまったのですが(汗)残高の数量も出したいです。 なのでG列またはH列にそれぞれの残高と在庫の数量が出せればうれしいです。 最終的に在庫(または仕入)と売上が相殺された分の行は削除したいので できたら0円表示などができればフィルターで削除するのが便利かと思っています。 単価に関しては無視してくださって結構です。 あいまいな表を作ってしまって申し訳ありません。 「みかん」に関しては単純に残高の計算ミスをしているので 仕入行に1500円が正しいです。 最後の在庫がない場合は~、という項目の部分は 単純に記載ミスになります。 「仕入と売上のみの場合は仕入-売上」になります。 よろしくお願いいたします。 また何か不備がございましたら回答いただければ補足させていただきます。 どうぞよろしくお願いします。
- KURUMITO
- ベストアンサー率42% (1835/4283)
お示しのデータが2行目から下方にあるして、J2セルには次の式を入力して下方にオートフィルドラッグします。 =B2&C2&F2 お求めのG列ではG2セルに次の式を入力して下方にオートフィルドラッグします。 =IF(B2="","",IF(OR(F2="売上",COUNTIF(J$2:J2,J2)<COUNTIF(J:J,J2)),0,IF(AND(COUNTIF(J:J,B2&C2&"仕入")>0,F2="仕入"),SUMIF(J:J,B2&C2&"在庫",D:D)+SUMIF(J:J,B2&C2&"仕入",D:D)-SUMIF(J:J,B2&C2&"売上",D:D),IF(AND(COUNTIF(J:J,B2&C2&"仕入")=0,F2="在庫"),SUMIF(J:J,B2&C2&"在庫",D:D)-SUMIF(J:J,B2&C2&"売上",D:D),0))))
関連するQ&A
- 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
- ベストアンサー
- オフィス系ソフト
- Excelで複数条件を満たし残高を計算する関数
画像のようなEXCELの表があります。 B列No.(第一条件)、C列商品名(第二条件)が一致するものを 仕入から販売を引いてF列のような数字になるように残高を出したいです。 どんな関数でも構いません。 関数でできる方法があれば教えてください。 実際のデータ量が2000行くらいあるので手作業でするのが大変で…。 よろしくお願いしますm(_ _)m OS:WindowsXP Office:2007
- ベストアンサー
- オフィス系ソフト
- Excel2010 複数条件の計算方法
複数の条件に該当する項目の合計を計算したいのですが、関数の作り方が分かりません・・・ 例えば、A列に文字があってB列に数値が入力されている表があったとして、A列にある項目の中から2つ以上に該当する数値の合計を計算する場合、どのような関数にすれば良いのでしょうか? 例えば、A列に果物の名前(りんご、みかん、ぶどう、メロン・・・)があり、B列にその値段が入っていて、「りんご」と「みかん」と「メロン」の値段の合計を算出するような場合、どのような関数になりますか? 条件がひとつであれば、SUMIF関数を使って計算できるかと思いますが、2つ以上の場合、どのようにすれば良いか分からないのです。 Officeのバージョンは2010です。 ネットで検索してもイマイチ分かりませんでした。どなたか簡単に教えて頂けますでしょうか?
- ベストアンサー
- その他MS Office製品
- Excelの使い方で質問
例えば、 A列1~10には「No.」として1~10の数字が順番に入ってます。 B列1~10には「りんご」「みかん」「いちご」「ぶどう」という文字がいくつかずつ入っています。 そういうデータで「りんご」は「No.」いくつといくつにあるか、「みかん」は「No.」いくつといくつにあるか、「いちご」は…、「ぶどう」は…、といったように集計したいと思っています。 りんご 1,5,6 みかん 9 いちご 2,3,9 ぶどう 4,7,8,10 といった感じにできれば良いのですが、どうすればよろしいのでしょうか? よろしくご教授ください。
- ベストアンサー
- Excel(エクセル)
- EXCEL2007 条件付き書式
変数AとBがあります。AorBと一致するセルのみを活かして一致しないセルを書式と文字列をクリアするにはどのようにすればいいでしょうか??? EXCEL2007 条件付き書式→数式を用いて・・・関数ORをつかってみましたがうまくできません。 ちなみにABの変数にはすでに背景色が設定されており、そのまま活かして残したいです。 例えると、データは以下のように表になっています。 {りんご みかん} りんご ばなな すもも すいか みかん りんご・・・・・・・ {いちご れもん} ばなな りんご すもも すいか いちご れもん・・・・・・・ 処理後↓ {りんご みかん} りんご みかん りんご・・・・・・・ {いちご れもん} いちご れもん・・・・・・・ 上記のような感じで処理できればと思います。 VBAの知識も無く、処理できず困っています。宜しくお願いします!
- 締切済み
- オフィス系ソフト
- Excel データの個数を複数条件付きでカウントしたい
OS:XP Ver.:Excel2003 Excelの関数で質問です。 いろいろ調べたのですがどうしてもわかりません。 お知恵をご拝借下さい。 A B C D 1月 2月 3月 りんご 250 0 300 みかん 150 80 0 りんご 88 150 200 いちご 0 300 85 ぶどう 0 350 89 (確認画面にするとずれてしまいますが、A列には品名、B~D列には月が入るようになっています。) 上記のような元データがあり、(実際はもっとたくさん) 1月 2月 3月 りんご みかん いちご ぶどう (こちらもずれてしまいますが、それぞれの月の品名ごとの集計を入れたいのです。) のような表を完成させたいと思います。 (1)表に入れるのは、合計数量ではなく【データの個数】です。 1月のりんごは2、みかんは1、という感じです。 ただし、0はカウントしたくないので、1月のいちごとぶどうは0を 返してほしいのです。 countifやsumproductなど考え付くあたり試してみましたが、うまくできません。 どういう数式(関数)を入れればよいのでしょうか? (2)2月以降のデータ範囲を指定する場合はどうすればいいのでしょうか? 1月ならA2:B6とするのかもしれませんが、2月の場合は1月列(B列)が不要ですよね?
- 締切済み
- その他MS Office製品
- Excel2003 複数の条件を満たすデータを抽出する関数を教えて下さ
Excel2003 複数の条件を満たすデータを抽出する関数を教えて下さい! 下のデータで、A列でB、B列で赤を選んだ人は「みかん」という「みかん」を抽出する関数を教えて頂けませんでしょうか? データシートとは別に集計シートを作成しています。 お忙しい中恐れ入りますが、ご教示下さいますようお願い申し上げます。。。 ●データ A B C 1 A 赤 みかん 2 B 赤 りんご 3 C 白 いちご 4 B 青 いちご 5 D 赤 みかん 6 A 青 みかん 7 C 黄 りんご 8 E 赤 バナナ
- 締切済み
- オフィス系ソフト
- Excel2003の条件付き書式について
お世話になります。 条件付書式で下記のようなことが可能か、可能であれば設定方法をご教授お願いします。 【シート1】(単価リスト)※単価の昇順で並べ替えています。 A B C D E 1 レベル 単価 カテゴリー 商品名 在庫 2 1 5 AAA りんご ● 3 2 7 BBB みかん 4 3 8 CCC いちご 5 2 8 DDD か き 【シート2】(各商品に必要な材料表)※材料名の昇順で並べ替えされています。 A B C 1 カテゴリー 商品名 材料 2 BBB みかん オレンジ 3 DDD か き オレンジ 4 CCC いちご 赤 3 AAA りんご 赤 4 BBB みかん 黄 5 AAA りんご 黒 6 AAA りんご 白 上記のような同じBOOK内の2枚のシートで商品名をキーとして 【シート1】のE列「在庫」に「●」が入っている商品の場合 【シート2】の商品名セルが塗りつぶされるようにしたいのです。 【シート2】には、1つの商品に対して必要な材料すべてが行を変えて記入されているため、同じ商品名が複数あります。 たとえばりんごの場合 【シート1】のE列「在庫」に「●」が入っているので【シート2】の商品名「りんご」 のセル(B3、B5、B6)はすべて塗りつぶされる。 (他の商品は「在庫」に「●」が入っていないので【シート2】の商品名セルは塗りつぶしなしのまま) 条件付き書式の「数式で」で検索する関数と「●」が入っている場合は FLASEとなるようIF関数の組み合わせを条件1と条件2に指定して試してみたのですが 上手くいきませんでした。 説明が足りなかったり、分かりにくければ補足させていただきますので宜しくお願いします。
- ベストアンサー
- オフィス系ソフト
- 【Excel】文字データの検索&一致不一致の判断
Excelで以下のような列があります。 A 1 りんご 2 みかん 3 ぶどう 4 いちご (以下略) ここで、A列に対してすでにA列に存在しているデータが入力された場合、B列に×を返し、存在していないデータなら○を返すような式を入れたいです。 A B 1 りんご ○ 2 みかん ○ 3 ぶどう ○ 4 いちご ○ ・・・ 19 ばなな ○ 20 りんご × どのような関数を入れれば良いか、教えていただきたいです。 よろしくお願いします。
- ベストアンサー
- Excel(エクセル)
- エクセルで複数の条件からデータを抽出する方法
次のような表において、A列の入荷日、B列の製品名から、別表に月別に製品別毎の入荷数及び合計金額を求める関数を教えて下さい。 A B C(個数) D(単価) 2013/2/15 みかん 100 50 2013/2/30 みかん 200 50 2013/3/2 りんご 50 70 2013/3/4 ぶどう 150 40 2013/4/3 いちご 100 50 よろしくお願いします
- ベストアンサー
- その他(ITシステム運用・管理)
お礼
お礼が遅くなり申し訳ありません。 画像でわかりやすくしていただきありがとうございました。 まさしく表示したい方法でしたので こちらをいくらか修正しながら作業したら ほぼ思い通りの結果を得ることができました。 ただ4の関数だけうまく計算できなかったため 最後に書いてある関数を使用したらきちんと計算できました。 いろいろとご丁寧にありがとうございました。 またご質問させていただく際にはよろしくお願いします。