[[20160528024205]] 『無い数字を表示』(葵) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『無い数字を表示』(葵)

A5〜A3000までに毎日ランダムに数字が記入されていきます。
(空の行もあります)
A列の最小値から最大値(例では1から8)の間で、無い数字をB5から若番順に
表示させることは可能でしょうか。

	A	B
  5	2	4
  6	1	5
  7	 	6
  8	8	7
  9		
  10		
   11		
   12	3	

< 使用 Excel:unknown、使用 OS:unknown >


 こんばんわ。

 EXCELのバージョンは何ですか?

(sy) 2016/05/28(土) 04:41


 ついでにお聞きします。

 ・A列に入力する可能性のある最大値はだいたいどのくらいですか?
  最小値は「1」ですか?
 ・B列は最大で何行ぐらいになりますか?

 とりあえず以上2点。
(笑) 2016/05/28(土) 06:59

 A列に入力する値の最大値が 1000以内、A列の行数も 1000行まで。
 素人ですからだらだらと長くなりました。

 B1 : =IF(SMALL(IF(ISERROR(MATCH(ROW($1:$1000),A$1:A$1000,0)),ROW($1:$1000)),ROW(A1))>MAX(A:A),"",SMALL(IF(ISERROR(MATCH(ROW($1:$1000),A$1:A$1000,0)),ROW($1:$1000)),ROW(A1)))

 これを Ctrl/Shift/Enter で入力し、下にフィルコピー。

(β) 2016/05/28(土) 09:20


皆さまありがとうございます。
出来ればマクロで作成することは可能でしょうか

syさん

Excel2010です。

笑さん

・A列に入力する最大値は2800ほどです。
・B列は最大で100行ぐらいだと思います。


(葵) 2016/05/28(土) 20:14


 seiyaさんに教わった処理コードを借用して

 Sub Sample()
    Dim v As Variant
    Dim mx As Long

    Application.ScreenUpdating = False

    Columns("B").ClearContents

    With Range("A1", Range("A" & Rows.Count).End(xlUp))
        mx = WorksheetFunction.Max(.Cells)
        v = Filter(Evaluate("TRANSPOSE(IF(ISERROR(MATCH(ROW($1:$" & mx & ")," & .Address & ",0)),ROW($1:$" & mx & "),CHAR(2)))"), Chr(2), False)
    End With

    Range("B1").Resize(UBound(v)).Value = WorksheetFunction.Transpose(v)
    Range("B1", Range("B" & Rows.Count).End(xlUp)).Sort Key1:=Columns("B"), Order1:=xlAscending, Header:=xlNo

 End Sub

(β) 2016/05/28(土) 20:56


 自前のごりごりループ処理です。
 (アップ後、例外データ(整数以外のデータ)がないという前提のコードに変えました。21:16)

 Sub Sample2()
    Dim al As Object
    Dim i As Long
    Dim c As Range

    Application.ScreenUpdating = False

    Set al = CreateObject("System.Collections.ArrayList")
    Columns("B").ClearContents

    With Range("A1", Range("A" & Rows.Count).End(xlUp))
        For i = 1 To WorksheetFunction.Max(.Cells)
            al.Add i
        Next

        For Each c In .Cells
            If Not IsEmpty(c) Then al.Remove CLng(c.Value)
        Next
    End With

    Range("B1").Resize(al.Count).Value = WorksheetFunction.Transpose(al.toarray)

 End Sub

(β) 2016/05/28(土) 21:12


 表示させる行数が100行くらいなら、関数でも再計算は一瞬で終わると思います。
 2500行全て表示させるとかになったら、関数では再計算に数秒かかりますね。 

 マクロで行いたいのなら、マクロ記録を使えばご自身で作成も難しくありません。

 以下マクロ記録の手順です。

 と書いてたら、すでにβさんから提示されてましたので止めときます。
 流石βさん、速いです。

 因みに関数の方法も提示しておきます。

 B1セル =IFERROR(AGGREGATE(15,6,(1/((COUNTIF(A:A,ROW(INDEX(A:A,MIN(A:A)):INDEX(A:A,MAX(A:A))))=0)))*ROW(INDEX(A:A,MIN(A:A)):INDEX(A:A,MAX(A:A))),ROW(A1)),"")
 下にオートフィル

 追記です。(21:44)
 >A列の最小値から最大値
 との事ですので、上記式はA列の値で、最小値〜最大値までを集計対象にしていますが、
 必ず1からなら下の式で少し短くなります。
 と言っても1から始まるコードは、βさんから提示があるので関数の出番はないかと思いますが。

 B1セル =IFERROR(AGGREGATE(15,6,(1/((COUNTIF(A:A,ROW(A$1:INDEX(A:A,MAX(A:A))))=0)))*ROW(A$1:INDEX(A:A,MAX(A:A))),ROW(A1)),"")

(sy) 2016/05/28(土) 21:34


 syさんがおっしゃったマクロ記録利用、どうやるんだろうと、ちょっと考えて
 こんな方法でも行けるなと。(syさんが示唆されたものは、もっとスマートなものかもしれませんが)

 1.A列をB列にコピペ
 2.A列,B列の1行目に行挿入して、任意の(同じ)タイトルを記入
 3.B列を昇順並び替え
 4.作業シートの A1 に 同じタイトル。(以下、作業シートに対して)
 5.A2に 1
 6.A2を選択して 編集グループのフィル、連続データの作成。
   範囲を列にして停止値に 元のシートA列の最大値をセットしてOK
 7.データタブ フィルターグループの詳細設定
 8.リスト範囲(L)にA列、検索条件範囲(C)に元シートのB1:B●を指定してOK
 9.抽出された行(タイトル行以外)を選択して行削除
 10.フィルター クリア
 11.この A列を元シートのB1にコピペ(上書き)
 12.元シートの1行目を削除

 これをマクロ記録すれば基本コードができますので、あとは、固定になっている数や領域を変数化。

(β) 2016/05/28(土) 22:18


 マクロ記録の手順ですか。

 こんな感じです。

 1、最小値をC1セルに表示 =MIN(A:A)
 2、最大値をC2セルに表示 =MAX(A:A)
 3、B1セルに関数を入力 =IF(OR(C$1>ROW(),C$2<ROW(),COUNTIF(A:A,ROW())>0),"",ROW())
 4、B1セルをコピーして、B1〜B2800を選択して貼付
 5、そのままコピーして値のみ貼付
 6、そのまま昇順でソート
 7、C列の関数を削除

 それと1つ目のコードは、例えば最大値を1000にしたら998までしか表示してくれないですね。
 2つ目のコードは999まで表示してくれます。

 実際の関数の参照先が、手順と違うようにしてたので、修正忘れてましたので、今修正します。(22:59)

(sy) 2016/05/28(土) 22:34


 >>それと1つ目のコードは、例えば最大値を1000にしたら998までしか表示してくれないですね。

 あっ! 確認しました。そうですね。(汗)
 理解せず、つけやきばで人様のコードを拝借しているので使い方が間違っているところがありましたね。

 Filter で取得した配列は LBoundが0 ですから

 Range("B1").Resize(UBound(v)+1).Value = WorksheetFunction.Transpose(v)

 でしたね。

 失礼しました。

(β) 2016/05/28(土) 23:00


syさん、βさん ありがとうございました。

マクロで上手くいきました(•‾̑⌣‾̑•)

ちなみにB列に表示させるのとは別に、ポップアップで表示させるのもマクロ記録で出来るのでしょうか。


(葵) 2016/05/29(日) 01:33


 ポップアップ表示の処理コードはマクロ記録では無理ですね。

 Sample2 でいいますと

 MsgBox "結果は以下の通りです" & vbLf & Join(al.toarray, vbLf)

(β) 2016/05/29(日) 07:30


 おはようございます。

 ポップアップは100行もあると現実的ではないですね。
 フォームならスクロールバーがあるから出来ない事は無いですが、、、
 セルに表示が一番良いと思います。

(sy) 2016/05/29(日) 07:50


 syさんと同様に思います。
 最大値が2800だとすれば、A列にいくつ数が出てくるかはわかりませんが、膨大な表示数になりますし
 ポップアップは、表示が終われば消えるわけで、あとから何が抜けていたのかなと、そう思っても
 メモしておかない限り覚えきれないでしょうから。

 少なくともセルへの書き込みと併用ですね。

 あぁ、それと、ちょっと勘違いしていたところがあって、Sample のほうの 最後の並び替えは不要です。

 で、Sample でのポップアップは

 MsgBox "結果は以下の通りです" & vbLf & Join(v, vbLf)

 になります。

(β) 2016/05/29(日) 08:20


syさん、βさん

たくさん教えて頂きありがとうございます。

これからはマクロ記録でチャレンジしていきます。


(葵) 2016/05/29(日) 13:16


何度もスミマセン。

最小値〜最大値で「番号なし」がない場合には実行時エラー '13'が
表示されるのですが、エラーを表示しない方法はどうすればよいでしょうか?


(葵) 2016/05/29(日) 14:41


 そうなることは、わかっていて(でも、そういうことは、きっとないんだろうと)手抜きしてたんですけど
 対処はもちろんできます。

 コード、それぞれで、少しずつ対処が違ってきます。

 お使いになるのは Sample ですか、Sample2 ですか?
 それとも、マクロ記録のための操作案がsyさんとβからでていますが、そのいずれかで
 自分で作り上げたコードですか?
 最後のケースなら、そのコードをアップしてください。

(β) 2016/05/29(日) 14:45


 とりあえず Sample と Sample2 それぞれの該当部分をアップしておきますね。

 Sample なら

    If UBound(v) < 0 Then
        MsgBox "抜けはありません"
    Else
        Range("B1").Resize(UBound(v) + 1).Value = WorksheetFunction.Transpose(v)
        MsgBox "結果は以下の通りです" & vbLf & Join(v, vbLf)
    End If

 Sample2 なら

    If al.Count = 0 Then
        MsgBox "抜けはありません"
    Else
        Range("B1").Resize(al.Count).Value = WorksheetFunction.Transpose(al.toarray)
        MsgBox "結果は以下の通りです" & vbLf & Join(al.toarray, vbLf)
    End If

(β) 2016/05/29(日) 18:14


βさん

Sample2で使わせて頂きます。

ありがとうございました。


(葵) 2016/05/30(月) 00:27


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.