以前、下記の質問をさせていただきましたが、うまく稼働しないため(私の質問の仕方が悪く…)
http://oshiete.goo.ne.jp/qa/6382052.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
No.5ベストアンサー
- 回答日時:
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))
にしておきたいし、そこまで計算速度にこだわらないと思うので。
お礼が遅くなり申し訳ありません。
画像でわかりやすくしていただきありがとうございました。
まさしく表示したい方法でしたので
こちらをいくらか修正しながら作業したら
ほぼ思い通りの結果を得ることができました。
ただ4の関数だけうまく計算できなかったため
最後に書いてある関数を使用したらきちんと計算できました。
いろいろとご丁寧にありがとうございました。
またご質問させていただく際にはよろしくお願いします。
No.6
- 回答日時:
#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] で確定、配列数式です({}で囲まれる)
右へ下へオートフィル
計算速度無視。質問者の理解無視。単なる自己満足です。
No.4
- 回答日時:
こんばんは!
横からお邪魔します。
一例です。
データが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
お礼が遅くなり申し訳ありません。
画像を貼り付けてくださりありがとうございました。
ただ商品数がとても多いため別シートに在庫表を作ることを考えていなかったため
ほかの方の回答を参考にさせていただくことにしました。
ご回答くださったのに申し訳ありません。
ご丁寧にありがとうございました。
No.3
- 回答日時:
これがもし、在庫の行が無い場合には、仕入の行に集計結果を表示し、在庫の行がある場合には(例え仕入の行があったとしても)必ず最新の在庫の行に表示するのであれば、作業列を必要とせず、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関数が重たいのか計算に少し時間がかかるようでしたので
別の方の回答を参考にさせていただきました。
ご回答くださったのに申し訳ありません。
ご丁寧にありがとうございました。
No.2
- 回答日時:
幾つか不明な点がありますので追加情報を補足願います。
●残高をどの行に表示すべきなのかが不明です。
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円が正しいです。
最後の在庫がない場合は~、という項目の部分は
単純に記載ミスになります。
「仕入と売上のみの場合は仕入-売上」になります。
よろしくお願いいたします。
また何か不備がございましたら回答いただければ補足させていただきます。
どうぞよろしくお願いします。
No.1
- 回答日時:
お示しのデータが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が見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(Microsoft Office) Excelの関数(FILTER関数)について教えてください 2 2023/07/31 16:11
- Visual Basic(VBA) VBAでの共有パスにつきまして 1 2023/03/04 17:24
- メルカリ メルカリShopsを作るとメルカリで販売は楽になりますか メルカリとまったく変わりませんか? 2 2022/10/11 19:22
- Visual Basic(VBA) VBAで、1つのエクセルで、2つのシートからもう1つのシートに条件のある転記コードを教えてください。 1 2023/03/16 18:07
- Excel(エクセル) Excelの関数について 3 2022/11/13 23:47
- スーパー・コンビニ 一斉値上 スーパーの食料品 値上や単価の仕組みは? 4 2022/06/01 16:18
- 確定申告 確定申告の真面目な質問です。 個人事業主で転売屋に毛の生えた事をしています。 販路はメルカリ一択です 1 2023/04/09 22:39
- Visual Basic(VBA) ファイル全てを .xlsm に変更したところ、プログラムが途中で落ちてしまっています 17 2022/12/07 12:03
- Visual Basic(VBA) EXCEL関数LOOKUPとFILTERについての質問です 1 2022/12/21 05:53
- Excel(エクセル) Excel 知恵を下さい。 下記表は、例です。本来の表のデータ量は、1000件以上あります… A列: 9 2023/01/13 12:08
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【関数】スペースがいくつ入っ...
-
西暦や和暦の表示をyyyymmdd表...
-
Excelのセルを飛ばして入力する
-
Excelのオートフィル
-
別シートからの文字を変更
-
Excel 2019 のピボットテーブル...
-
エクセルの行の抽出について質...
-
【マクロ】エクセルにかいてあ...
-
スプレッドシート クエリ関数 1...
-
エクセルでセルに「氏名を入力...
-
MOS365 Excel Expert / Excel R...
-
excelの不要な行の削除ができな...
-
EXACT関数とIF関数の組み合わせ...
-
スプレッドシートの関数VLOOKUP...
-
Excelで全角を半角にしたいので...
-
Excel初心者です。 詳しい方、...
-
エクセルの数式で教えてください。
-
4つのパターンを表示するEXACT...
-
スマートな関数を教えて下さい。
-
【Excel】セル内の時間帯が特定...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報