お世話になります^ ^
みなさんとても親切な方で、大変恐縮ですm(_ _)m
10000以上のデータがあるのですが、、
あるセルからあるセルまでの間隔の数値を、
自動的に求める方法はありますでしょうか??
数値はHセルに入力しています。
例えば、数値1と2と、
他の数値とのセル間隔を数字で求められたらと思っています。
例)セルH1 2
セルH2 8
セルH3 6
セルH4 15
セルH5 1
セルH6 1
となる場合、
1と2以外のセル間を数字で求めると、
0→3→0となります。
このように求められる方法はありますでしょうか?
稚拙な文章のため、
意味がわからない!などありましたら、
ご指摘ください。
よろしくお願い致しますm(_ _)m
No.15ベストアンサー
- 回答日時:
あっ、Chiquilinさん。
。。ありがとうです。^^
tsuna_sandさん。
Chiquilinさんは色んなQ&Aで回答されているお仲間です。
このままエラーが回避できない可能性もありますから、
一度複製を作って、Chiquilinさんの方法も試して見てください。
ご回答ありがとうございます^ ^
suekunさんとChiquilinさんのおかけで、
ついに完成させることができました!
数日にわたり、
丁寧にご指導していただき、
言葉もないくらい感謝しております!!
良い夏をお過ごしされるとを、
願っております^ ^
今回は本当にありがとうございましたm(_ _)m
No.17
- 回答日時:
とりあえず テスト版ではうまくいったようで良かったですね。
> 仮に9と8がHセルに出現したら処理を進めるようにする場合、
なら
■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
- 回答日時:
> なぜか最後の2だけ表示されません。
。J9セルに「0」は入っていますか? J列は最終行より1つ多めにコピー
する必要があります。これをしないと最後の抽出データが表示されなく
なりますのでご注意下さい。
この回答への補足
ご回答ありがとうございます^ ^
J9セルに0を入力しましたら、
完璧にできました!
すごくうれしいです!
ありがとうございます!!
Chiquilinさのが作ってくださった表では、
Hセルに1が出現したら処理を進めるようになっていますが、
仮に9と8がHセルに出現したら処理を進めるようにする場合、
どの数式をどのように変えればよろしいでしょうか??
No.14
- 回答日時:
エラーの原因が見つからないのですよね。
エラーを出してるセルを特定しましょう。
一旦、K列の数式はすべて削除して、
I列とJ列を設定した状態に戻しましょう。
ここまででどこかにエラーを出してる箇所があると思います。
では、K列の数式を削除したら、エラーを探す手順です。
1、キーボードの最上段の「F5」を押して下さい。
→ジャンプと言う窓が開きます。
2、左下の「セル選択」というボタンを押します。
→選択オプションが表示されます。
3、数式にチェックを入れて、数式の下位にある「数値」「文字」「論理値」のチェックをはずします。
→チェックがエラー値だけになります。
4、「OK」を押してカーソルが飛んだ先のセル番地(例:J12とか)
を教えて下さい。
合わせてそこに書かれてある数式も教えて下さい。
(上部の数式バーに内容が表示されていると思います。)
再び手数をかけますが、なんとか完成に持ち込みましょう。
No.13
- 回答日時:
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
通りになりました!
ほぼといいますのは、
なぜかI8セルの最後の数字2が、
K3セルに表示されません。。
_|__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
エラーも何も無く、
スムーズにできたのですが、、
なぜか最後の2だけ表示されません。。
No.12
- 回答日時:
なるほどですね。
では、もう一度最初から整理して考えましょう。
A B・・・・・・H I J K
1 1 1 0
2 8
3 5
4 7
5 2 5 3
6 1 6 0
No.5まで出来ているならこういう表になっているはずです。
H列の数値に対して、その数値が「1」か「2」であれば
その数値が記入されている行番号(上から何行目)がI列に
表記される。
そのI列の数値を見て、数値(行番号)が表示されていれば
間にある空白のセル数を数えてJ列に表記する。
この事でI列には上から該当データーがあるセルには昇順に行番号が
表示されていて、J列にはその間のセルの数が表示されていますよね。
このデーターが並ぶ正しい行数を教えて頂けますか?
1行目から12567行目までとかですね。
そしてこの範囲に設定した関数が返した答え以外が表示されてないか
さっと見て下さい。
お手数かけますが宜しくお願いします。
この回答への補足
いつもご回答ありがとうございます!!
おっしゃるとおり、
ご提示いただいた表のような結果になっています。
>H列の数値に対して、その数値が「1」か「2」であれば
その数値が記入されている行番号(上から何行目)がI列に
表記される。
>そのI列の数値を見て、数値(行番号)が表示されていれば
間にある空白のセル数を数えてJ列に表記する。
>この事でI列には上から該当データーがあるセルには昇順に行番号が
表示されていて、J列にはその間のセルの数が表示されていますよね。
これらもおっしゃるとおりです。
このデーターが並ぶ正しい行数は、
1行目から12389行目までです。
そしてこの範囲に設定した関数が返した答え以外は、
表示されていませんでした。。
本当にいつもお世話になり、
感謝感謝であります^ ^
No.11
- 回答日時:
日曜にバレーの試合があったのでちょっと疲れて
返事が遅くなりました。
さて、#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と表示されます。
ただ前回と違うのは、J列に数字表示があるセルがありますよね。
そのJ列の右下のKのセルに、
#NUM!と表示されていました。。
お時間のある時で結構ですので、
お力になっていただければ幸いです。
No.10
- 回答日時:
あっ、ごめんなさい。
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
と数式をコピーしたK列のセルすべてに
表示されてしまいました。。
エクセルのバージョンは、
おっしゃるとおり2003であります。
No.9
- 回答日時:
こんにちは。
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セルに入力して下にコピーして見ましたところ、
入力した数式にエラーが発生しましたという物が出て、
修正を適応しますか?という問にはいと回答しました。
すると、数式を計算できませんという警告が出てきました。
その後J列にはすべて数字の0が表示されていました。
ためしにK1セルに入力して下にコピーして見ましたところ、、
同じような警告文が出ました。
その後K列には#VALUEと表示されました。
度々お世話になりますm(_ _)m
No.8
- 回答日時:
>詰めて表示
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)
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) 先頭と末尾を指定して連続した数字を入力 1 2022/09/14 13:12
- Excel(エクセル) 条件に合った数値の合計を表示させたい関数と条件指定の方法 3 2023/05/13 16:07
- Excel(エクセル) エクセルの質問です 5 2022/12/19 10:34
- Excel(エクセル) エクセルの関数式を教えてください。 2 2022/11/29 21:09
- Excel(エクセル) エクセルの数式を等間隔にオートフィルできるやり方を教えていただきたいです。 実際の作業↓ A3セルに 7 2023/06/05 19:04
- Excel(エクセル) 独自の条件を用いた計算式、もしくは別の方法で打ち出す事は可能でしょうか? 6 2022/05/18 10:38
- Excel(エクセル) 特定の条件で計算式を組みたいのですが、教えて頂けますでしょうか? 6 2022/10/28 19:02
- Excel(エクセル) エクセル VBA セルの結合 2 2022/09/07 11:48
- Excel(エクセル) 関数 9 2022/05/27 09:47
- Excel(エクセル) エクセルVBAでセルに表示されているとおりの数値を取得したい(時間の計算結果) 1 2022/03/30 17:52
このQ&Aを見た人はこんなQ&Aも見ています
-
プロが教える店舗&オフィスのセキュリティ対策術
中・小規模の店舗やオフィスのセキュリティセキュリティ対策について、プロにどう対策すべきか 何を注意すべきかを教えていただきました!
-
Excelで特定の数字の間にあるセルの数を数える方法
その他(Microsoft Office)
-
下記のようにA列にランダムで出現する数字に対して、B列でその出現間隔を
Excel(エクセル)
-
エクセルで数字と数字の間隔(空白の数)を計算する方法を教えてください。
Excel(エクセル)
-
-
4
Excel 特定の文字列間のセルの個数を数える方法
Excel(エクセル)
-
5
座標(x,y)間(=2点)の距離をエクセルで求めるには?
Excel(エクセル)
-
6
[エクセル関数]等間隔に値を取得する方法について教えてください
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル vbaについて教えてく...
-
Excel 複数列 A列B列C列一致 D...
-
【マクロ】名前を保存する際に...
-
エクセルであるセルに数字を入...
-
エクセルファイルがファイルの...
-
Excelについて教えてください
-
エクセル マクロ チェックボックス
-
【Excel】数字を3倍にし、なお...
-
エクセルVBA、別ブックへ転記す...
-
Excel分析ツールでのポアソン回...
-
Excelでセルの値が同じか...
-
エクセルで年休を管理する方法...
-
エクセル共有したが、アクセス...
-
エクセルのデーターが2か月前の...
-
【マクロ】顧客番号にて一致さ...
-
(マクロ)データをAブックからB...
-
エクセルを使っていて2024/5/15...
-
エクセル②
-
UNIQUE関数が使えないバージョ...
-
【マクロ】その時、その時で変...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報