エクセルで、特定セルとの間隔数値を求める方法はありますでしょうか？？

質問

お世話になります^ ^

みなさんとても親切な方で、大変恐縮ですm(_ _)m

１００００以上のデータがあるのですが、、
あるセルからあるセルまでの間隔の数値を、
自動的に求める方法はありますでしょうか？？

数値はＨセルに入力しています。
例えば、数値１と２と、
他の数値とのセル間隔を数字で求められたらと思っています。

　例）セルＨ１　２
　　　セルＨ２　８
　　　セルＨ３　６
　　　セルＨ４　１５
　　　セルＨ５　１
　　　セルＨ６　１

となる場合、
１と２以外のセル間を数字で求めると、
０→３→０となります。

このように求められる方法はありますでしょうか？

稚拙な文章のため、
意味がわからない！などありましたら、
ご指摘ください。

よろしくお願い致しますm(_ _)m

回答No.15

suekun · ベストアンサー

あっ、Chiquilinさん。。。

ありがとうです。＾＾

tsuna_sandさん。

Chiquilinさんは色んなQ&Aで回答されているお仲間です。
このままエラーが回避できない可能性もありますから、
一度複製を作って、Chiquilinさんの方法も試して見てください。

お礼

ご回答ありがとうございます^ ^

suekunさんとChiquilinさんのおかけで、
ついに完成させることができました！

数日にわたり、
丁寧にご指導していただき、
言葉もないくらい感謝しております！！

良い夏をお過ごしされるとを、
願っております^ ^

今回は本当にありがとうございましたm(_ _)m

回答No.17

noname#176215

とりあえず テスト版ではうまくいったようで良かったですね。

> 仮に９と８がＨセルに出現したら処理を進めるようにする場合、
なら

■I1セル
=IF(OR(H1={8,9}),0,"")

■I2セル
=IF(OR(H2={8,9},AND(H2<>"",H3="")),COUNTIF(H$1:H1,"<>1")-SUM(I$1:I1),"")
I12389セル(最終行)までオートフィルコピー

■J1セル
=IF(I1="",J2,J2+1)
J12390セル(最終行より下の行)までオートフィルコピー

■K1セル
=IF(J$1<ROW(K1),"",INDEX(I:I,MATCH(J$1-ROW(K1),J$1:J$12389,-1)-1))
下方向にオートフィルコピー

でどうでしょう。

お礼

ご回答ありがとうございます^ ^

suekunさんとChiquilinさんのおかけで、
ついに完成させることができました！

数日にわたり、
丁寧にご指導していただき、
言葉もないくらい感謝しております！！

良い夏をお過ごしされるとを、
願っております^ ^

今回は本当にありがとうございましたm(_ _)m

回答No.16

noname#176215

> なぜか最後の２だけ表示されません。。
J9セルに「0」は入っていますか？　J列は最終行より１つ多めにコピー
する必要があります。これをしないと最後の抽出データが表示されなく
なりますのでご注意下さい。

補足

ご回答ありがとうございます^ ^

Ｊ９セルに０を入力しましたら、
完璧にできました！
すごくうれしいです！
ありがとうございます！！

Chiquilinさのが作ってくださった表では、
Ｈセルに１が出現したら処理を進めるようになっていますが、
仮に９と８がＨセルに出現したら処理を進めるようにする場合、
どの数式をどのように変えればよろしいでしょうか？？

回答No.14

suekun

エラーの原因が見つからないのですよね。

エラーを出してるセルを特定しましょう。

一旦、K列の数式はすべて削除して、
I列とJ列を設定した状態に戻しましょう。

ここまででどこかにエラーを出してる箇所があると思います。

では、K列の数式を削除したら、エラーを探す手順です。

1、キーボードの最上段の「F5」を押して下さい。
→ジャンプと言う窓が開きます。

2、左下の「セル選択」というボタンを押します。
→選択オプションが表示されます。

3、数式にチェックを入れて、数式の下位にある「数値」「文字」「論理値」のチェックをはずします。
→チェックがエラー値だけになります。

4、「OK」を押してカーソルが飛んだ先のセル番地（例：J12とか）
を教えて下さい。
合わせてそこに書かれてある数式も教えて下さい。
（上部の数式バーに内容が表示されていると思います。）

再び手数をかけますが、なんとか完成に持ち込みましょう。

回答No.13

noname#176215

suekunさんに呼ばれて来ましたが「#VALUE!」エラーが返る理由がよく
分かりません……

今更ですが一度 最初からやってみて下さい。 データ量も多いので極力
軽い計算の方がいいでしょうし。 ファイルを複製して 下記の方法を試
してみて下さい。

_|＿＿H＿＿I＿＿J＿＿K
1|＿＿9＿＿_＿＿3＿＿3
2|＿＿8＿＿_＿＿3＿＿0
3|＿＿6＿＿_＿＿3＿＿2
4|＿＿1＿＿3＿＿3
5|＿＿1＿＿0＿＿2
6|＿＿9＿＿_＿＿1
7|＿＿8＿＿_＿＿1
8|＿＿2＿＿2＿＿1
9|＿＿＿＿＿＿＿0

■I1セル
=IF(H1=1,0,"")

■I2セル
=IF(OR(H2=1,AND(H2<>"",H3="")),COUNTIF(H$1:H1,"<>1")-SUM(I$1:I1),"")
I12389セル(最終行)までオートフィルコピー

■J1セル
=IF(I1="",J2,J2+1)
J12390セル(H列の最終行の1行下)までオートフィルコピー

■K1セル
=IF(J$1<ROW(K1),"",INDEX(I:I,MATCH(J1-ROW(K1),J$1:J$12389,-1)-1))
下方向にオートフィルコピー

でどうでしょうか。エラーの原因が分からないと何とも＾＾；

補足

ご回答ありがとうございます^ ^
おふたりにご教授いただいておりますので、
わたくしも気合を入れてがんばります！

ご指示していただいたとおりに、
実行しましたところ、、

ほぼ完全に、
_|＿＿H＿＿I＿＿J＿＿K
1|＿＿9＿＿_＿＿3＿＿3
2|＿＿8＿＿_＿＿3＿＿0
3|＿＿6＿＿_＿＿3＿＿2
4|＿＿1＿＿3＿＿3
5|＿＿1＿＿0＿＿2
6|＿＿9＿＿_＿＿1
7|＿＿8＿＿_＿＿1
8|＿＿2＿＿2＿＿1
9|＿＿＿＿＿＿＿0
通りになりました！

ほぼといいますのは、
なぜかＩ８セルの最後の数字２が、
Ｋ３セルに表示されません。。

_|＿＿H＿＿I＿＿J＿＿K
1|＿＿9＿＿_＿＿3＿＿3
2|＿＿8＿＿_＿＿3＿＿0
3|＿＿6＿＿_＿＿3＿＿ここに2が入るはずなのですが、、
4|＿＿1＿＿3＿＿3　　何も表示されません。。
5|＿＿1＿＿0＿＿2
6|＿＿9＿＿_＿＿1
7|＿＿8＿＿_＿＿1
8|＿＿2＿＿2＿＿1
9|＿＿＿＿＿＿＿0

エラーも何も無く、
スムーズにできたのですが、、
なぜか最後の２だけ表示されません。。

回答No.12

suekun

なるほどですね。

では、もう一度最初から整理して考えましょう。

　 Ａ　　Ｂ・・・・・・Ｈ　　Ｉ　　Ｊ　　Ｋ
1　　　　　　　　　　　1　　 1　　 0
2　　　　　　　　　　　8　　　　　
3　　　　　　　　　　　5　　 　　　　
4　　　　　　　　　　　7
5　　　　　　　　　　　2　　 5　　 3
6　　　　　　　　　　　1　　 6　　 0

No.5まで出来ているならこういう表になっているはずです。
Ｈ列の数値に対して、その数値が「1」か「2」であれば
その数値が記入されている行番号（上から何行目）がＩ列に
表記される。

そのＩ列の数値を見て、数値（行番号）が表示されていれば
間にある空白のセル数を数えてＪ列に表記する。

この事でＩ列には上から該当データーがあるセルには昇順に行番号が
表示されていて、Ｊ列にはその間のセルの数が表示されていますよね。

このデーターが並ぶ正しい行数を教えて頂けますか？
1行目から12567行目までとかですね。

そしてこの範囲に設定した関数が返した答え以外が表示されてないか
さっと見て下さい。

お手数かけますが宜しくお願いします。

補足

いつもご回答ありがとうございます！！

おっしゃるとおり、
ご提示いただいた表のような結果になっています。

＞Ｈ列の数値に対して、その数値が「1」か「2」であれば
　その数値が記入されている行番号（上から何行目）がＩ列に
　表記される。

＞そのＩ列の数値を見て、数値（行番号）が表示されていれば
　間にある空白のセル数を数えてＪ列に表記する。

＞この事でＩ列には上から該当データーがあるセルには昇順に行番号が
表示されていて、Ｊ列にはその間のセルの数が表示されていますよね。

これらもおっしゃるとおりです。

このデーターが並ぶ正しい行数は、
１行目から12389行目までです。

そしてこの範囲に設定した関数が返した答え以外は、
表示されていませんでした。。

本当にいつもお世話になり、
感謝感謝であります^ ^

回答No.11

suekun

日曜にバレーの試合があったのでちょっと疲れて
返事が遅くなりました。

さて、#VALUE　というエラーがよくわかりませんね。
SMALLで指定している範囲の数値が数値として認知してないなら
別のエラーが出るはずですからね。

一応範囲を強制的に数値に置き換えてみましょうか？

=SMALL(VALUE($I$1:$I$10000),ROW(J1))

この式をK1に入れてみてください。

補足

ご回答ありがとうございます^ ^

遅れたなんてとんでもないです！
ご回答していただけるだけで、
とても感謝しておりますm(_ _)m

=SMALL(VALUE($I$1:$I$10000),ROW(J1))を
k1に入力してみましたが、、
また#VALUEと表示されてしまいました。。

ためしに
=SMALL(VALUE($I$1:$I$10000),ROW(J1))
をk1セルに入力し、下方にコピーしましたところ、
ほとんどのセルに、#VALUEと表示されます。
ただ前回と違うのは、Ｊ列に数字表示があるセルがありますよね。
そのＪ列の右下のＫのセルに、
#NUM!と表示されていました。。

お時間のある時で結構ですので、
お力になっていただければ幸いです。

回答No.10

suekun

あっ、ごめんなさい。K列でしたね。

No.5まで出来ているなら、次はK列に行詰めですから
J列ではなく、K列に入力して頂く事になります。

それでもエラーが表示されたなら、このエラーは
引数や演算子に使われている種類のアンマッチを示しますので
一旦数式を二つに分けてどこにエラーがあるか見てみます。

K列に入れる数式を
=SMALL($I$1:$I$10000,ROW(J1))
として下にコピーしてください。

問題が無ければ、I列の数値が行詰めされて表示されるはずです。

この操作が出来ていれば、次はL列に
=INDEX($J$1:$J$10000,K1)
と入力してみてください。

この二段階の操作をまとめて書いたのが先ほどの数式です。

PS:ちなみにエクセルのバージョンは何でしょう？　2003？

補足

いつもご回答ありがとうございます^ ^

そして返事が遅れてしまい、
大変申し訳ありません。。
ちょっと出かけていました。

ご教授いただきましたとおり、
K列に入れる数式を
=SMALL($I$1:$I$10000,ROW(J1))
として下にコピーしたのですが、、

#VALUE
と数式をコピーしたＫ列のセルすべてに
表示されてしまいました。。

エクセルのバージョンは、
おっしゃるとおり2003であります。

回答No.9

suekun

こんにちは。

No.5までは表示されたのですよね。
さて、数式の解説をしながら検証してみますか。

この数式はエラー処理をしているので、IFの後にISERRORを入れて
あります。
ISERRORの中に書かれた関数がエラーだったら空白を返します。
その部分が、
ISERROR(INDEX($J$1:$J$10000,SMALL($I$1:$I$10000,ROW(J1))))
です。

本来なら、
INDEX($J$1:$J$10000,SMALL($I$1:$I$10000,ROW(J1))))
だけで機能するのですが、行詰めした以降のセルにはエラーが
表示されるのでエラー回避してます。

セル数を数えた数値が0・2・0・3・5・4と6個しかなければ
上から6行目以降はすべてエラー表示されますからね。

では先にSMALLです。
この関数は、指定された範囲の中で小さい順に●番目の
数字を返します。
●番目と言うのは、任意に指定できます。
Iの列には、検索に当たった行数が表示されていますから、
上から下に数字が段々大きくなります。
この数値の中でJ列に小さい方から順番に拾って行こうというのが
狙いです。

そこで●番目の設定ですが、「ROW(J1)」と指定しています。
参照の形が相対参照というコピーする事で参照先が変化する
形をとってますので、下にコピーして行けば、J2　J3と変化します。
ROWは行番号を返す関数ですから、列番号に関係なく
J1=1　J2=2　J30＝30という数字を返します。
つまり、提示した基本の数式では、1番目に小さい数字を拾うと
言う事になります。

そしてその上の「INDEX」は範囲の中で行数で指定された場所の
値を拾います。
つまり、SMALLで小さい方からと拾った数値はそのまま行番号なので
その数値を範囲の中で当てはめれば、J列のセル数を数えた数値が
返されるはずです。

何も起こらないって事はエラー回避が働いている可能性がありますから
一度、
＝INDEX(J:J,SMALL(J:J,ROW(J1))))
この関数をだけをJ1セルに入力して下にコピーして見て下さい。

その結果を教えて下さい。

補足

いつもご回答ありがとうございます^ ^

ご教授いただきました処理を行った結果を、
報告いたします。

J1セルに入力して下にコピーして見ましたところ、
入力した数式にエラーが発生しましたという物が出て、
修正を適応しますか？という問にはいと回答しました。

すると、数式を計算できませんという警告が出てきました。
その後Ｊ列にはすべて数字の０が表示されていました。

ためしにK1セルに入力して下にコピーして見ましたところ、、
同じような警告文が出ました。
その後Ｋ列には#VALUEと表示されました。

度々お世話になりますm(_ _)m

回答No.8

cafe_au_lait

＞詰めて表示

J2=SMALL($I:$I,ROW())-SMALL($I:$I,ROW()-1)-1

これを下にコピーでOKです。
エラーを回避するなら、

J2=IF(COUNT($I:$I)<ROW(),"",SMALL($I:$I,ROW())-SMALL($I:$I,ROW()-1)-1)

回答No.7

suekun

ごめんなさい。バレーの練習に行ってました。＾＾；

K列での行詰めですね。

K1セルに
=IF(ISERROR(INDEX($J$1:$J$10000,SMALL($I$1:$I$10000,ROW(J1)))),"",INDEX($J$1:$J$10000,SMALL($I$1:$I$10000,ROW(J1))))
を記入して下方にコピーです。

ただし、範囲の終わりが不明なので、範囲参照の中に「$I$1:$I$10000」と「$J$1:$J$10000」が二つづつありますので
正しい行数に直してください。
最終行の行数に「10000」を書き換えてくれればいいです。＾＾

補足

ご回答ありがとうございます^ ^

先日はお世話になりましたm(_ _)m

バレーですか！
いいですね～
お疲れ様です！

ご教授していただいたとおりにやってみたのですが、、
何も起こりません。。

まず、Ｋ１セルに
IF(ISERROR(INDEX($J$1:$J$10000,SMALL($I$1:$I$10000,ROW(J1)))),"",INDEX($J$1:$J$10000,SMALL($I$1:$I$10000,ROW(J1))))
をコピーしてエンターを押しました。

そしてＫ１セルをコピーして、
ためしにＫ２からＫ3000までペーストしましたが、
何も起こりませんでした。。

何か手順が間違っていましたでしょうか？