エクセル関数をドラッグした際に連続データにならない

質問

こんにちは

エクセル関数をドラッグした際に
関数内で、データが連続してコピーされない場合の対処方法を教えてください。

以下の様な関数を作成しました。
=SUMPRODUCT(($E$2:$E$450="66")*ISNUMBER(FIND("りんご",$BS$2:$BS$450)))

この数式を下にドラッグした際、"66"を
"65"
"64"
"63"
といった具合に、降順で表示されるようにしたいです。
そのままだと全て"66"になってしまいます。

【試してみた方法】
以前どこかで、「=」 と 「"66」以降を削除し、
下にドラッグすると
66が連続データとしてコピーされ、
後は置換で数式を修正する。というのをみたことがあるのですが
サイトがみつからず、記憶があいまいなため
質問させていただきました。
上記の方法以外でも何か手がありましたら教えてください。
よろしくお願いいたします。

回答No.4

xls88 · ベストアンサー

1、数式先頭の、= を # に書き換えます
#SUMPRODUCT(($E$2:$E$450="66")*ISNUMBER(FIND("りんご",$BS$2:$BS$450)))
2、文字列化した式を
#SUMPRODUCT(($E$2:$E$450="66")
と
*ISNUMBER(FIND("りんご",$BS$2:$BS$450)))
2つに分け、例えばA1とB1に分割入力します。
3、A2にも分割した文字列式を入力します。最後の数字は、降順になるように、65とします。
#SUMPRODUCT(($E$2:$E$450="66")
#SUMPRODUCT(($E$2:$E$450="65")
4、A1とA2を選択し、フィルハンドル（枠右下角の■）を下方にドラッグ
#SUMPRODUCT(($E$2:$E$450="66")
#SUMPRODUCT(($E$2:$E$450="65")
#SUMPRODUCT(($E$2:$E$450="64")
#SUMPRODUCT(($E$2:$E$450="63")
#SUMPRODUCT(($E$2:$E$450="62")
#SUMPRODUCT(($E$2:$E$450="61")
#SUMPRODUCT(($E$2:$E$450="60")
・
・
・
5、B1を下方にコピー
*ISNUMBER(FIND("りんご",$BS$2:$BS$450)))
*ISNUMBER(FIND("りんご",$BS$2:$BS$450)))
*ISNUMBER(FIND("りんご",$BS$2:$BS$450)))
*ISNUMBER(FIND("りんご",$BS$2:$BS$450)))
*ISNUMBER(FIND("りんご",$BS$2:$BS$450)))
*ISNUMBER(FIND("りんご",$BS$2:$BS$450)))
*ISNUMBER(FIND("りんご",$BS$2:$BS$450)))
・
・
・
6、C1に =A1&B1 とセルデータ結合式を入力し、下方にコピー
#SUMPRODUCT(($E$2:$E$450="66")*ISNUMBER(FIND("りんご",$BS$2:$BS$450)))
#SUMPRODUCT(($E$2:$E$450="65")*ISNUMBER(FIND("りんご",$BS$2:$BS$450)))
#SUMPRODUCT(($E$2:$E$450="64")*ISNUMBER(FIND("りんご",$BS$2:$BS$450)))
#SUMPRODUCT(($E$2:$E$450="63")*ISNUMBER(FIND("りんご",$BS$2:$BS$450)))
#SUMPRODUCT(($E$2:$E$450="62")*ISNUMBER(FIND("りんご",$BS$2:$BS$450)))
#SUMPRODUCT(($E$2:$E$450="61")*ISNUMBER(FIND("りんご",$BS$2:$BS$450)))
#SUMPRODUCT(($E$2:$E$450="60")*ISNUMBER(FIND("りんご",$BS$2:$BS$450)))
・
・
・
7、C列の数式セル範囲を選択し、コピー
8、その場で「形式を選択して貼り付け」の「値」で貼り付け
9、セル範囲選択状態のまま、編集メニューの「置換」で
#
を
=
に「すべて置換」

お礼

お礼がおそくなり、申し訳ございません。
探していた方法はまさにこれでした！
助かりました！

回答No.5

xls88

試しにマクロを組んでみました。

Sub test1()
　　Dim r As Range
　　Dim i As Long
　　i = 66
　　For Each r In Range("A1:A66")
　　　　r.Formula = "=SUMPRODUCT(($E$2:$E$450=""" & i & """)*ISNUMBER(FIND(""りんご"",$BS$2:$BS$450)))"
　　　　i = i - 1
　　Next
End Sub

回答No.3

suekun

またしても引数不足　＾＾；

全角
JIS(TEXT(66-ROW(A1),"@"))

=SUMPRODUCT(($E$2:$E$450=JIS(TEXT(66-ROW(A1),"@")))*ISNUMBER(FIND("りんご",$BS$2:$BS$450)))

お礼

お礼が遅くなり、申し訳ございません！
何度もご説明ありがとうございました。

回答No.2

suekun

ごめんなさい、走り読みだったので、数字が減るのですね。

66-ROW(A1）　これだけは数字なので、全角文字にするなら
JIS(TEXT(66-ROW(A1)))

=SUMPRODUCT(($E$2:$E$450=JIS(TEXT(66-ROW(A1))))*ISNUMBER(FIND("りんご",$BS$2:$BS$450)))

回答No.1

suekun

66が数値でない場合は、テキストに変更する仕組みが必要になります。

例えば、TEXT関数で括ってネストする等ですね。

その事を視野に入れれば、上下方向への変動で有れば
ROWを利用する事が可能だと思います。
ROW(A1)+65　等ですね。