エクセルに関する質問

質問

エクセルに関する質問
約1050～1100個ある数字データがA1から順に1050～1100個並んでいるのですが
このデータの数を真ん中(1050の場合は525、1100の場合は550)を基準として

端端を同じだけとり(1050の場合は1～25と1025から1050を省いて)1000個のデータにするのに
現在その数をいちいち計算して、データの端端をとって1000個のデータにしているのですが
頻繁になってくるとこの作業が正直煩わしくなってしまいます。
そこで約1050～1100のデータをどこかのセルに貼り付けるだけで関数などによって
パパッとそのデータを真ん中基準で1000個にしてくれるみたいな方法などないでしょうか？
虫のいい話で申し訳ありません。
何卒詳しい方おられましたら、よろしくお願いします。

回答No.5

merlionXX

#4です。
先ほどのコードで少し修正したほうがいい点がありました。
差し換えます。

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Or Target.Columns.Count > 1 Then Exit Sub
If Target.Count < 1000 Then Exit Sub
Dim x As Long, y As Long, a As Long
Columns("B").ClearContents
x = Cells(Rows.Count, "A").End(xlUp).Row
y = x - 1000
If y < 0 Then Exit Sub
a = Application.WorksheetFunction.Round(y / 2, 0)
Range(Cells(1, "B"), Cells(1000, "B")).Value = _
Range(Cells(a + 1, "A"), Cells(a + 1001, "A")).Value
End Sub

なお、このコードですが、1000を超えた部分が２で割り切れないと、先頭の削除分は四捨五入されます。たとえば1047個の場合、23.5個というデータはないので先頭の1～24と、後ろの23データが除かれます。

お礼

活用させていただきます。
大変役にたちました。
本当にありがとうございました。

回答No.4

merlionXX

> パパッとそのデータを真ん中基準で1000個にしてくれるみたいな方法などないでしょうか？
> 虫のいい話で申し訳ありません。

そういう虫のいい話を実現できるのがエクセルの素晴らしいところです。

A1から下に1000個以上のデータが貼り付けられた場合、自動的にB1～B1000に真ん中の1000個のデータを自動的に表示させるようにしてみました。
以下の手順をお試しください。

１．データを貼り付けるシートのタブを右クリックし、「コードの表示」を選択。
２．出てきたVisualBasicEditor（白い部分）に下記のコードをコピペ。

'*****ここから下をコピペ*****

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Count < 1000 Then Exit Sub
If Application.WorksheetFunction.CountA(Target) = 0 Then Exit Sub
Dim x As Long
Columns("B").ClearContents
x = Cells(Rows.Count, "A").End(xlUp).Row
y = x - 1000
a = Application.WorksheetFunction.Round(y / 2, 0)
Range(Cells(1, "B"), Cells(1000, "B")).Value = _
Range(Cells(a + 1, "A"), Cells(a + 1001, "A")).Value
End Sub

'*****ここより上までをコピペ*****

３．AltキーとF11キーを同時に押してワークシートへもどります。

A1以下にデータ（1000個以上必要）を貼り付けてみてください。

回答No.3

noname#153814

Ｎｏ１のものです。
=RANK(B1,$B$1:$B$1100,1）と最後に「１」を入れてください。
こうすることで、昇順に並べ替えられます。

お礼

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

回答No.2

redowl

B1に、下式を入力。　さらに、B2以下にコピペ（または、ドラッグダウン）
=IF(OR(RANK(A1,$A:$A,1)<=(COUNTA($A:$A)-1000)/2,RANK(A1,$A:$A,0)<=(COUNTA($A:$A)-1000)/2),"",A1)

RANK(A1,$A:$A,1)<=(COUNTA($A:$A)-1000)/2
RANK関数で　A列データの順位を小さい数値から割り付け
これを、　全体のデータ数から１０００マイナスし２で割る事で、両端のカット数を算出
このカット数と順位を比較する事で、カット値の場合は空欄、採用値ならその数を表示
また、
RANK(A1,$A:$A,0) とする事で、大きい数値から順位割り付け
以上の二つの条件で判断させれば可能かなと。
なお、データが奇数個の場合と、同値データが複数個有った場合については、
１０００個丁度にならない場合が発生します。

お礼

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

回答No.1

noname#153814

雑文と思って読んでください。
Ａ１：Ａ１００にデータが入っている。（重複データが無いものとします）

列挿入で、Ａ１：Ａ１１００のデータをＢ１：Ｂ１１００に移動します。（ＶＬＯＯＫＵＰ関数を使うためです）

Ａ１に=RANK(B1,$B$1:$B$1100)と入力してＡ１１００までドラッグします。

Ｃ１に=MEDIAN(A1:A1100)として、中心を出します。

Ｆ１（どこでも良いが）に１～１０００までの数字を入れます。

その数字の「５００」の隣（Ｇ５００）に、＝Ｃ１とします。

Ｇ４９９に＝Ｇ５００－１とします。これをＧ１までドラッグします。

Ｇ５０１に＝Ｇ５００＋１として、Ｇ１０００までドラッグします。

Ｈ１に=VLOOKUP(G1,$A$1:$B$1100,2,FALSE)として、Ｈ１０００までドラッグすれば回答が得られますが、悠長ですよね。

お礼

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