[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『リストの中の連続した空白の部分を探したい』（ペペ）
こんにちは。
知り合いからこちらを教えてもらって初めて質問させていただきます。
宜しくお願い致しますm(._.)m
例えば00〜99まで(実際にはもっと多いです)の通し番号が付いたリストがあったとして
そのうちの所々に内容が無い物があるとします。
その内容がまだ無い番号だけを表示させて、さらにそれがいくつ続いているかとか、更にその続いている数が多い順に表示させたりしたいのですが可能でしょうか？
具体的には、、
−−−−−−−−−−
00 あああああ１
01 あああああ２
02
03
04 けけけけけ１
05 けけけけけ２
06
07 たたたたた１
08
09
10
11
12 ままままま１
13 ままままま２
14 ままままま３
・
・
−−−−−−−−−−
↑
こういうリストがあって、新たに「めめめめめ１〜６」までをリストに追加したい時に、どこにそれが入るスペースがあるかを探したいのです。
エクセルは10年以上使っていますので基本的な操作と簡単な関数は分かりますが本当に基本しか分からないレベルです。
初めての投稿なので分かりづらかったり、そもそもこのサイトの使い方等に問題がありましたら申し訳ないです。
宜しくお願い致します。
＜ 使用 Excel：Excel2013、使用 OS：Windows8 ＞
「いくつ続いている」ってのはどういう意味？
例で言うと 「2」、「１」、「４｣ ・・・ ってこと？ （GobGob） 2015/04/07(火) 16:31
ありがとうございます！
そういう意味です！
例えばリスト全体で、
1つ空＝15カ所
2つ空＝8カ所
3つ空＝7カ所
4つ空＝0カ所
5つ空＝2カ所
・
・
とうい場合に、
「1」
05
15
25
27
35
・
・
「2」
9、10
17、18
38、39
・
・
といった感じでそれぞれが何カ所あって、リストの番号が何かが知りたいのです。
宜しくお願い致しますm(._.)m
（ペペ） 2015/04/07(火) 16:42
てか、「めめめめめ１〜６」はどこに入れれるか？でいいんでない？
以下質問。
連続スペースがたとえば ４連続、6連続しかなくて、めめめめめを１〜３ 入れたい場合は どうするの？
おなじく めめめめめ１〜７ 入れたい場合は連続数足りないのでNGでいいの？ （GobGob） 2015/04/07(火) 16:52
ありがとうございますm(._.)m
分かり辛くてすみません。
「めめめめめ1〜6」は空があれば入れて、無ければ諦めるというかNGという感じでOKなんです。
入れたい物の数がバラバラなので、出来れば上記のように、空続きが何で、それぞれどれだけあるのかがリストアップされると助かります。
宜しくお願い致しますm(._.)m
（ペペ） 2015/04/07(火) 17:01
作業列を使う。 連番がA列、データがB列で1行目から1000行目まである場合。
C1セルに =IF(B1<>"","",MATCH(1,INDEX((B1:B$1000<>"")*1,0),0)-1) と入力。
C2セルに =IF(B2<>"","",IF(B1="","",MATCH(1,INDEX((B2:B$1000<>"")*1,0),0)-1)) と入力して下へフィルコピー。
F列に1行目から下へ「1」「2」「3」…と下へ昇順に入力。 G1セルに =COUNTIF(C$1:C$1000,F1) と入力して下へフィルコピー。 この行はF列の個数の空白がいくつあるかを示す。
H1セルに
=IFERROR(INDEX($A$1:$A$1000,SMALL(IF($C$1:$C$1000=$F1,ROW(A$1:A$1000),""),COLUMN(A1))),"")
と入力してShiftキーとCtrlキーを押しながらEnterキーで確定（確定後、式が{}で囲まれればOK）
その後右と下へフィルコピーでどうか。
F列の空白がある開始行の連番を右へ表示していく。 （ねむねむ） 2015/04/07(火) 17:26
ありがとうございます！
何やら難しそうですが具体的に書いてくださっているので、その通りにやってみます！
一両日中に試してみます！
（ペペ） 2015/04/07(火) 17:32
Sub 空欄()
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim xx As Integer
Dim xy As Integer
Dim xi As Integer
Dim yi As Integer
xx = 1
xy = 2
xi = 0
y = Cells(Rows.Count, 1).End(xlUp).Row
For x = 1 To y
If Cells(x, 2) = "" Then
Sheets("Sheet2").Cells(xx, xy) = Cells(x, 1)
xy = xy + 1
xi = 1
For z = x + 1 To y
If Cells(z, 2) <> "" Then
Sheets("Sheet2").Cells(xx, 1) = xi
xx = xx + 1
If yi < xy Then
yi = xy
End If
xy = 2
x = z
Exit For
Else
Sheets("Sheet2").Cells(xx, xy) = Cells(z, 1)
xy = xy + 1
xi = xi + 1
End If
Next z
End If
Next x
y = Cells(Rows.Count, 1).End(xlUp).Row
Cells.Select
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("A1") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet2").Sort
.SetRange Range(Cells(1, 1), Cells(y, yi))
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
（コヨーテひな） 2015/04/08(水) 10:17
A B C D E F G H I J K L M N O 1 00 ああ１ 空き 個数 No⇒ 2 01 ああ２ 1 1 06 3 02 2 1 02 4 03 3 1 15 5 04 けけ１ 4 1 08 6 05 けけ２ 5 0 7 06 6 0 8 07 たた１ 7 0 9 08 8 0 10 09 9 0 11 10 10 0 12 11 13 12 まま１ 14 13 まま２ 15 14 まま３ 16 15 17 16 18 17 19 D列に「スペースの連続数」を入力 E2 =10-COUNTBLANK(F2:O2) F2 =IF($D2="","",IFERROR(INDEX($A:$A,SMALL(INDEX((FREQUENCY(ROW($A$1:INDEX($A:$A,COUNTA($A:$A)+1)),($B$1:INDEX($B:$B,COUNTA($A:$A))<>"")*ROW($A$1:INDEX($A:$A,COUNTA($A:$A))))-1<>$D2)*10^16+ROW($A$1:INDEX($A:$A,COUNTA($A:$A)+1)),),COLUMN(A1))-$D2),""))
F2をO2までコピー。 F2:O2 下へコピー。
※連続スペース数 10個までを前提（範囲で調整） ※E列はA列が数値か文字列かわからないのでCOUNTBLANKで対応。 （GobGob） 2015/04/08(水) 10:37
ありがとうございます！！
昨日のねむねむさんの方法もまだ試せていないのですが、更にお二人から別の方法を提示していただいていることに気づきとても感激しております！
せっかく教えていただいたのでぜひ全て試してみたいのですが、今まで広い海と大きい魚に憧れつつも、浅いところで小魚とかカニとかを捕まえて満足していたところ、突然巨大な漁船を目の前に用意してもらったけど漁どころか船の操作方法すら分からないという状態で焦ってます(;´･ω･)
試すにしても基礎レベルをもっと上げないとダメですよね。
他の方の質問と回答もちょっと見てみたのですが僕にとって質問の意味すら分からないような質問にもみんな回答がされていて、ここは一体何なんだ！？と本当に驚いています！
会ったこともない僕なんかに親切に教えてくださっている御三方や見てくださっている方々に心からお礼申し上げます。
変な例えと長文失礼いたしましたm(._.)m
とにかく最近ずっと無かった感動体験です！
精進します！
（ペペ） 2015/04/08(水) 15:40
ちょっと考え方を変えて・・・。
A B C D E F 1 00 ああ１ データ データ数 2 01 ああ２ めめ 4 08 3 02 るる 1 06 4 03 ああ 4 15 5 04 けけ１ ここ 5 6 05 けけ２ 7 06 8 07 たた１ 9 08 10 09 11 10 12 11 13 12 まま１ 14 13 まま２ 15 14 まま３ 16 15 17 16 18 17 19 18 20
F2 =IF($E2="","",IFERROR(INDEX($A:$A,SMALL(INDEX((FREQUENCY(ROW($A$1:INDEX($A:$A,COUNTA($A:$A)+1)),($B$1:INDEX($B:$B,COUNTA($A:$A))<>"")*ROW($A$1:INDEX($A:$A,COUNTA($A:$A))))-1<>$E2)*10^16+ROW($A$1:INDEX($A:$A,COUNTA($A:$A)+1)),),COUNTIF(E$2:E2,E2))-$E2),"")) 下へコピー。
※各データが当てはまる開始行を表示。 ※キッチリ入るトコだけ検索 （GobGob） 2015/04/08(水) 18:40
Sub test()
Dim srtl As Object
Dim r As Range
Dim n As Long
Dim i As Long
Set srtl = CreateObject("System.Collections.SortedList")
For Each r In Range("a1").CurrentRegion.Columns(2).SpecialCells(xlCellTypeBlanks).Areas
n = r.Count
If Not srtl.contains(n) Then
Set srtl(n) = CreateObject("System.Collections.ArrayList")
End If
If n = 1 Then
srtl(n).Add r.Cells(1, 0).Value
Else
srtl(n).Add r.Cells(1, 0).Value & "〜" & r.Cells(n, 0).Value
End If
Next
Range("e1").CurrentRegion.ClearContents
n = srtl.Count
For i = n - 1 To 0 Step -1
Range("e1").Offset(n - 1 - i).Value = "[" & srtl.GetKey(i) & "]"
Range("f1").Offset(n - 1 - i).Value = srtl.GetByIndex(i).Count & "箇所"
Range("g1").Offset(n - 1 - i).Resize(, srtl.GetByIndex(i).Count).Value = _
srtl.GetByIndex(i).toarray()
Next
End Sub
（マナ） 2015/04/08(水) 22:06 差し替え23:25
＞GobGobさん
更なるご提案をありがとうございます！！
＞マナさん
ありがとうございます！
βさんというすごい方もいらっしゃるんですね！？
お腹いっぱいだなんてとんでもございません。
僕の消化能力が低すぎて消化できないだけでとてもありがたいです。
試させていただきましたのでご報告いたします_(._.)_
結果と詳細を書きます。
◆結果◆
ねむねむさん：途中までできました。(エラー等は出ていません)
コヨーテひなさん：試すことができていません。
GobGobさん(1つ目)：できました！
GobGobさん(2つ目)：できませんでした。
マナさん：試すことができていません。
◆詳細◆
ねむねむさん：G列に空白個数に対する該当数は表示されましたが、
「F列の空白がある開始行の連番を右へ表示していく。」これが表示されませんでした。
H列はちゃんと{}で囲まれましたがセルには何も表示されませんでした。
GobGobさん(1つ目)：望んでいた形にかなり近い状態になりました！
贅沢を言えば、連続した空白の番号の一覧が表示されると良いと思っていましたが、
連続空白の最初の番号がわかるだけで十分ですし、どうしても表示させたければ
たぶん僕でもできる範囲で表示させられると思うので無問題です！
GobGobさん(2つ目)：こちらは僕の目的を更にやりやすくと考えてくださった方法ですよね。
とてもありがたいのですが、ご説明通りにやってみたところ循環参照エラー?が発生してしまいました。
ですが、1つ目の方法だけでも十分に満足しておりますm(._.)m
コヨーテひなさん：
マナさん：
コヨーテひなさんとマナさんのお二方の方法は、せっかく教えていただいたのに僕のマクロの基本が
勉強不足すぎて試せませんでした。
大変申し訳ありません。
ですが、せっかく教えていただきましたし、近いうち必ず試してみるつもりです！
少し時間がかかるかもしれませんがまた報告させていただいます。
実際のデータは例と全く同じレイアウトだったのだろうか？
例とセルが違っていたりする場合は =IFERROR(INDEX($A$1:$A$1000,SMALL(IF($C$1:$C$1000=$F1,ROW(A$1:A$1000),""),COLUMN(A1))),"") 中の INDEX($A$1:$A$1000… 部分の「$A$1:$A$1000」は必ず一行目からの指定にしてくれ。 また、「COLUMN(A1)」部分は変更しないように。
例えば1行目が見出しで2行目からデータのような場合は =IFERROR(INDEX($A$1:$A$1000,SMALL(IF($C$2:$C$1000=$F1,ROW(A$2:A$1000),""),COLUMN(A1))),"") のように変更する。 （ねむねむ） 2015/04/09(木) 11:04
A B C D E F G H
1 ああ1 1 2 2 ああ2 2 2 3 2 3 0 4 4 0 5 かか1 5 0 6 かか2 6 0 7 1 7 0 8 たた1 8 0 9 1 9 0 10 くく1 10 0 11 2 11 0 12 12 0 13 まま1 13 0 14 まま2 14 0 15 まま3 15 0 16 #N/A 16 0 17 17 0 18 18 0 19 19 0 20 20 0 ・ ・ ・
という感じで、見出し等は無いので例と同じだと思うのですがどうでしょうか？
（ペペ） 2015/04/09(木) 11:34
私のチェックミスだった。すまない。
C2セルの式を =IF(B2<>"","",IF(B1="","",IFERROR(MATCH(1,INDEX((B2:B$1000<>"")*1,0),0)-1,INDEX(A$1:A$1000,COUNTA(A$1:A$1000))-A2+1))) としてくれ。 (Shift+Ctrl+Enterは変わらず)
B列が空白でデータの最後が終わる場合を考慮していなかったため16行のようにエラーが表示されてそのために空白行の場所が表示されていなかった。 (データの最後のB列が空白でない場合はエラーにならないためチェック漏れしていた) （ねむねむ） 2015/04/09(木) 11:50
ありがとうございます！
できましたー！！
一瞬やっぱりできないと思ったのですが、もう一度、H1セルの式を入力し直したらちゃんと表示されました！！
目的が同じなので当たり前かもしれませんが、式は違うのに見た目にはGobGobさんと同じようなるのが面白いですね。
気になった点があるのですが、、
”(Shift+Ctrl+Enterは変わらず)”で式を{}で囲むのどういう意味でしょうか？
最初のご説明ではH1セルだけやると書かれていて、先ほどのはC2にもやるようにとのことだったので気になりました。
そして、試しにC2のセルを{}で囲ったのと囲わないの両方で試してみたのですが、結果に影響がないように見えました。
僕のレベルには難しいようでしたらお答えいただかなくて大丈夫です。
とにかくお陰様でできて良かったです！
ありがとうございました！
（ペペ） 2015/04/09(木) 13:19
重ね重ねすまない。
C2セルの式は「Shift+Ctrl+Enter」は必要なかった。 書き込んでいるうちにH1の式を書き換えた気になっていたようだ。
Shift+Ctrl+Enterについては下記を参考にして見てくれ。
http://pc.nikkeibp.co.jp/pc21/special/hr/index.shtml
（ねむねむ） 2015/04/09(木) 13:31
いえいえ、ちゃんと教えていただけるので問題ないです。
参考URLのサイトを少し見てみました。
配列数式なんて初耳でした。
講座を少し見て{}がどういうものなのかは分かりましたが
今回の件での使われ方は僕にはまだ難しいですね。
表計算大会とかハイレベルな世界があるのにも驚きました。
URLの講座は分かりやすかったのでお気に入りに登録して
余裕がある時に勉強します！
色々とありがとうございましたm(._.)m
（ペペ） 2015/04/09(木) 14:49
A B C D E 1 0 ああ１ 空き 個数 2 1 ああ２ 1 2 3 2 2 2 4 3 3 0 5 4 けけ１ 4 1 6 5 けけ２ 5 0 7 6 8 7 たた１ 9 8 10 9 ささ1 11 10 12 11 13 12 まま１ 14 13 まま２ 15 14 まま３ 16 15 17 16 18 17 19 18
E2=SUM((FREQUENCY(ROW(B$1:B$20),(B$1:B$20<>"")*ROW(B$1:B$20))=D2+1)*1)
★下にコピー
※B16:B19の空白が4個もカウントするならば、B2:B20のように範囲を+1します
（maron） 2015/04/09(木) 14:56
ありがとうございます！
こちらはGobGobさんの方法への補足という感じでしょうか？
最後が空白の場合は範囲+1なんですね？
試してみますね！
（ペペ） 2015/04/09(木) 18:11
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.