いつもお世話になります。
WIN7 EXCELL2010 です。
例えば添付図でB14に日付を入力するとJ列に月が替わるところで#VAUE!が表示されます。
ただしD列の個数を入力するとD列は正しく表示されます。
この#VALUE!を表示しない方法はないでしょうか。
3の行の各列には、
A =IF(B3="","",TEXT(B3,"mm"))
B C D は手入力
E =IF(OR(B3="",C3=""),"",TEXT(B3,"yymm")&C3)
F =IF(C3="","",VLOOKUP(C3,商品管理!$A$2:$B$211,2,FALSE)) & " "
G =IF(C3="","",VLOOKUP(C3,商品管理!$A$2:$C$211,3,FALSE)) & ""
H =IF(G3="","",D3*G3)
I =IF(ISBLANK(D3),"",SUM($H$2:$H3))
J =IF(MONTH(B3)=MONTH(B4),"",SUMPRODUCT((MONTH(OFFSET($B$2,0,0,COUNT(B:B)))=MONTH(B3))*OFFSET($H$2,0,0,COUNT(B:B))))
以上ですがご指導を仰ぎたいのですがよろしくお願いします。
No.4ベストアンサー
- 回答日時:
H列の値が関数で""となっている場合は、そのセルの値は0などの数値と扱う事が出来なくなりますから、文字列に対して掛け算をした場合と同様の状態となってしまい、#VAUE!が表示されます。
但し、Excelでは
"0"
の様な数値を文字列データとしたものと同じ文字列に対してならば、四則演算は可能になります。
これを利用して、次の様にしますとエラーとはならずに済ます事が出来ます。
=IF(MONTH(B3)=MONTH(B4),"",SUMPRODUCT((MONTH(OFFSET($B$2,0,0,COUNT(B:B)))=MONTH(B3))*(0&OFFSET($H$2,0,0,COUNT(B:B)))))
但し、このままですと、例えばB3セルが1月の日付で、B4セルが空欄の場合、関数に拠らない純粋な空欄は数値の0、即ち1900年1月0日を表すシリアル値になりますから、J3セルには何も表示されない事になります。
又、COUNT(B:B)で行数を求めたのでは、B列の途中に空欄のセルか、文字が入力されているセルがある場合には、日付が入力されている最下段の行を正しく求める事は出来ません。
これらの弊害を排除するには、次の様にする方法があります。
=IF(TEXT($B3,"yyyy/m")=TEXT($B4,"yyyy/m"),"",SUMPRODUCT((TEXT($B$2:INDEX($B:$B,MATCH(9E+99,$B:$B)),"yyyy/m")=TEXT($B3,"yyyy/m"))*(0&$H$2:INDEX($H:$H,MATCH(9E+99,$B:$B)))))
MATCH関数の照合の型を省略しますと、「『検査範囲内において、検査値よりも少しでも小さな数値が入力されているセルの中で、最後尾のセル』が検査範囲内で何番目のセルになるのか」を示す数が返されます。
ですから、検査値に「『検査範囲内に含まれている最大値』よりも大きな数」を指定しますと、「『検査範囲内に含まれている最大値』よりも大きな数」よりも少しでも小さな数が入力されている最下段の行、即ち、数値が入力されている最下段の行が、検査範囲内で数えて上から何番目の行にあたるのかを示す数が返される事になりますから、これをCOUNT(B:B)の代わりに使う訳です。
しかしながら、SUMPRODUCT関数は「繰り返し計算」を行う関数であるため、指定する行範囲に含まれる行数(或いは列範囲に含まれる列数)が多くなりますと、計算処理が重くなり、計算結果が出るまでに時間が掛かるという短所があります。
このため、SUMPRODUCT関数を使わずに済む場合には、SUMPRODUCT関数の使用は避けた方が無難です。
御質問の件に関しましても、SUMPRODUCT関数を使わずに、次の様な関数としますと、計算が重くならずに済みますので、こちらの方を使用される事を御勧め致します。(「B3セルの日付の翌月の1日よりも前の日付」の合計額から、「B3セルと同月の1日よりも前の日付」の合計額を差し引いた額を求める関数です)
=IF(TEXT($B3,"yyyy/m")=TEXT($B4,"yyyy/m"),"",SUMIF($B:$B,"<"&DATE(YEAR($B3),MONTH($B3)+1,1),$H:$H)-SUMIF($B:$B,"<"&DATE(YEAR($B3),MONTH($B3),1),$H:$H))
いつもお世話になります。
お示しいただいた数式で試しましたが
下記の数式を採用させていただきました。
=IF(TEXT($B3,"yyyy/m")=TEXT($B4,"yyyy/m"),"",SUMIF($B:$B,"<"&DATE(YEAR($B3),MONTH($B3)+1,1),$H:$H)-SUMIF($B:$B,"<"&DATE(YEAR($B3),MONTH($B3),1),$H:$H))
こういう時は当たり前的に SUMPRODUCTを良く使うのですがこの数式では思いつきませんでした。
詳細にご説明していただき本当にありございました。
今後ともご指導よろしくお願いします。
No.3
- 回答日時:
ああ、そうですね。
No.1さんのおっしゃるとおり H 列で「""」を返しているから、このままじゃエラーですね。次式にすると、H 列にゼロを表示する代わりに、J 列のエラーは解消されるかもしれません。ベストアンサーは辞退します。H列 =IF(G3="",,D3*G3)
早速ご回答いただきありがとうございます。
私のミスで画像を添付していなくて下記のアドレスで再質問させていただき御迷惑をおかけしました。
お許しください。
http://oshiete.goo.ne.jp/qa/7852709.html
No.2
- 回答日時:
数式だけ見ててもよく分かりませんが、なんか月が変わるタイミングでだけ、その月の H 列にある何かの合計を数えてるのですね。
よく分かりませんが、SUMPRODUCT 関数で「#VALUE!」というエラー値が出る原因としてよくあるのは、「SUMPRODUCT(配列*配列)」という書式と「SUMPRODUCT(配列,配列)」の違いによって発生するものです。前者では文字列が配列の一部に含まれているとエラーになりますが、後者では文字列のある行を無視するのでエラーになりません。
この考えでご相談の数式を修正するなら、式中の「*」を「*1,」に直してみてください。ただし、あえてエラーを表示したい場合は、修正しないというのもアリかと。
今回の原因がこのせいだったとしたら、問題の文字列が存在しているのは、H 列でしょうね。
早速ご回答いただきありがとうございます。
私のミスで画像を添付していなくて下記のアドレスで再質問させていただき御迷惑をおかけしました。
お許しください。
http://oshiete.goo.ne.jp/qa/7852709.html
No.1
- 回答日時:
#VALUE!エラーが表示されるのはH列に空白「文字列があるためです。
空白文字列を無視して(0とみなして)計算するにはJ列のSUMPRODUCT式の部分を以下のように変更する必要があります。
SUMPRODUCT((MONTH(OFFSET($B$2,0,0,COUNT(B:B)))=MONTH(B3))*1,OFFSET($H$2,0,0,COUNT(B:B)))
早速ご回答いただきありがとうございます。
ご指導いただいた数式で試してみました。
バッチリでしたが勝って言いますと空白部分かないのが非常に残念です。
私のミスで画像を添付していなくて下記のアドレスで再質問させていただき御迷惑をおかけしました。
お許しください。
http://oshiete.goo.ne.jp/qa/7852709.html
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセル VBA For Next 繰り返しの書き方を教えてください 6 2022/09/01 14:11
- Visual Basic(VBA) C3とC4のセルに、Visual basicで実行した時入力した値をC3に表示させ、その後に、C3に 1 2023/07/14 09:43
- Excel(エクセル) セルの値 11 2022/06/29 11:00
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Visual Basic(VBA) VBAコードが作動せず、どこに問題があるのか教えて下さい。 3 2023/06/13 13:20
- PHP PHPで画像の渡しが上手く行きません。 1 2023/02/02 09:39
- Excel(エクセル) Formulaプロパティーを使ってセルに数式を組んだのですが簡潔にしたい。 3 2022/08/21 20:51
- Visual Basic(VBA) VBA Userformで一部別シートに転記がしたいのですが 2 2023/05/24 13:08
- Visual Basic(VBA) ファイル全てを .xlsm に変更したところ、プログラムが途中で落ちてしまっています 17 2022/12/07 12:03
- Excel(エクセル) ExcelにおけるVLOOKUPでの祝日表示について 5 2022/09/18 22:23
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
Excel 日付を比較したら、同じ...
-
英数字のみ全角から半角に変換
-
会社PCのメールが更新されない
-
Outlookを立ち上げたらGoogleロ...
-
outlookのメールが固まってしま...
-
Excelで空白以外の値がある列の...
-
ウィンドウィズ メモ帳で日付だ...
-
Excelに貼ったリンクについて E...
-
Microsoft Formsの「個人情報や...
-
Excelで時間計算(負)
-
microsoft office
-
【Excel VBA】PDFを作成して,...
-
エクセルでXLOOKUP関数...
-
マイクロソフト 一時使用コード...
-
Outlookでの時間指定送信機能に...
-
【スプレドシート】目標達成の...
-
Googleのスプレッドシートでシ...
-
Microsoft Officeを2台目のPCに...
-
MicrosoftOfficeについて質問で...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
【スプレッドシート】指定の日...
-
英数字のみ全角から半角に変換
-
会社PCのメールが更新されない
-
マイクロソフト 一時使用コード...
-
Office 2021 Professional Plus...
-
エクセルで例えば、関数を使っ...
-
Microsoft Formsの「個人情報や...
-
1つのPCに「Excel 2010」「Exc...
-
エクセルで例えば、A1に㈱ベ...
-
理由を教えてください。
-
エクセルでXLOOKUP関数...
-
マイクロソフト オフィスについて
-
VLOOKUP関数について
-
teams設定教えて下さい。 ①ビデ...
-
Googleのスプレッドシートでシ...
-
【Excel VBA】PDFを作成して,...
-
Microsoft365で写真をアルバム...
-
Outlook で宛先が複数の場合の人数
-
Excel テーブル内の空白行の削除
おすすめ情報