こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

エクセルでのデータの並べ替えについて

質問があります。下記のように並んでいるデータがあるとします。
  前年   今年
a 10 b 20
d 30 a 40
e 50 c 30
c 40 f 35
g 55
これを、
 前年 今年
a 10 40
b    20
c 40 30
d 30
e 50
f    35
g 55
という様に並べ替える方法を教えてください。(データは多数あります。)

投稿日時 - 2006-04-16 23:36:44

QNo.2096521

暇なときに回答ください

質問者が選んだベストアンサー

元表というシートにA1を基準に質問文のような表がある時
変換表というシートに質問文のような表を作ります。
マクロからconvertTable を呼び出して実行
----------------------------------------------------------------
Public Sub convertTable()
Dim 前年データ, 今年データ, keys, x, c
Dim 前年範囲 As Range, 今年範囲 As Range

Set 前年データ = CreateObject("Scripting.Dictionary")
Set 今年データ = CreateObject("Scripting.Dictionary")

With Sheets("元表")
Set 前年範囲 = .Range(.Range("A2"), .Range("A65535").End(xlUp))
Set 今年範囲 = .Range(.Range("C2"), .Range("C65535").End(xlUp))

For Each x In 前年範囲
前年データ.Add x.Value, x.Offset(0, 1).Value
Next
For Each x In 今年範囲
今年データ.Add x.Value, x.Offset(0, 1).Value
Next
End With

keys = asSet(Application.Union(前年範囲, 今年範囲))
Call ArraySort(keys, True)
With Sheets("変換表")
.Cells.ClearContents
.Range("B1").Value = "前年"
.Range("C1").Value = "今年"
c = 0
For Each x In keys
.Range("A2").Offset(c, 0).Value = x
If 前年データ.Exists(x) Then
.Range("A2").Offset(c, 1).Value = 前年データ.Item(x)
End If
If 今年データ.Exists(x) Then
.Range("A2").Offset(c, 2).Value = 今年データ.Item(x)
End If
c = c + 1
Next
End With
End Sub
Private Function asSet(r As Range) '重複しない数値データの配列にする
Dim NumList
Dim x As Range, i
Set NumList = CreateObject("Scripting.Dictionary")
For Each x In r
If Not NumList.Exists(x.Value) Then '重複チェック
NumList.Add x.Value, 1
Else '重複時の処理
NumList.Item(x.Value) = NumList.Item(x.Value) + 1
End If
Next
asSet = NumList.keys
End Function
Private Sub ArraySort(a, Optional ascending = 0) '規定値は大きいもの順
Dim wk, i As Integer, j As Integer, k As Integer
Dim n
n = UBound(a)
k = n \ 2
Do While (k > 0) 'シェルソート、ソート自体は昇順
For i = 0 To n - k
j = i
Do While (j >= 0)
If a(j) > a(j + k) Then
wk = a(j)
a(j) = a(j + k)
a(j + k) = wk
j = j - k
Else
Exit Do
End If
Loop
Next
k = k \ 2
Loop
If ascending = 0 Then '逆順にする
i = 0: j = n
Do Until (i >= j)
wk = a(i)
a(i) = a(j)
a(j) = wk
i = i + 1: j = j - 1
Loop
End If
End Sub

投稿日時 - 2006-04-17 04:05:18

お礼

返答、お礼が遅くなりすいません。早速教えていただきありがとうございます。
助かりました。
この例題でいうa、b、、、gでなくても並べかえができ、役に立ちました。
もちろん、項目もa、b、、、gのように当方がすべて把握できていないケースが多いので、その場合でも出来たのですばらしいと思います。
やまりこのようにマクロで解決するしかないのですね。

投稿日時 - 2006-05-07 15:36:03

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.5

#4です。
>実際のケースでは項目(この場合a、b、、、g)をすべて把握していなくては出来ず
そんなことは十も承知です。
それであれば、(多分関数でという条件をつけて)質問を改めて質問してください。
関数でやろうとすると、本当はそちらの方が、難しい質問かもしれない。
>「データは多数あります」が、それを暗示するのかもしれませんが
はっきり書いてください。
関数では難しいので、VBAでやって見ました。
A列から1列おきに、文字列があるとする。
例えばH列に、でてくる文字列を、1回だけ抜き出します。
Sub test01()
r = Range("a65536").End(xlUp).Row
c = Range("G1").End(xlToLeft).Column
MsgBox r & "=" & c
Cells(1, "H") = Cells(1, "A")
k = 1
For i = 1 To r
For j = 1 To c - 1 Step 2
If WorksheetFunction.CountIf(Range(Cells(1, "H"), Cells(k, "H")), Cells(i, j)) = 0 Then
k = k + 1
Cells(k, "H") = Cells(i, j)
End If
Next j
Next i
End Sub

投稿日時 - 2006-05-07 18:23:15

お礼

ありがとうございます。
私の質問の仕方がわるかったですね。
関数、マクロともあまり理解がないものですから。
今回教えていただいたおかげで助かりました。
ただ、エラーをださないようにする方法もいまいちわからないものですから、実際に使いこなす自信はありません。
マクロのほうはほとんど理解つかいこなす自信がありませんから、関数での方法がわかり助かりました。

投稿日時 - 2006-05-07 21:49:34

ANo.4

例データ
a10b20
d30a40
e50c30
c40f35
g55
G列にa-gをれる。
G列  H列   I列
a1040
b#N/A20
c4030
d30#N/A
e50#N/A
f#N/A35
g55#N/A
H列H1に
=INDEX($A$1:$B$5,MATCH(G1,$A$1:$A$5,0),2)
I列I1に
=INDEX($C$1:$D$5,MATCH(G1,$C$1:$C$5,0),2)
と入れて下方向に式を複写する。
結果は上記の通り。
#N/Aが出てしまっているが、MATCH関数がエラーの場合、空白にするように式を変えれば空白にできます。(略)

投稿日時 - 2006-04-18 00:05:57

お礼

返答、お礼がおそくなりすいません。
教えていただきありがとうございます。
この例題の場合はできましたが、実際のケースでは項目(この場合a、b、、、g)をすべて把握していなくては出来ず、この方法では解決できませんでした。

投稿日時 - 2006-05-07 15:47:05

ANo.3

以下のように、AB列に「前年」、CD列に「今年」
1行目にタイトル、2行目からデータがあり、
A B C D
1]  前年   今年
2]a 10 b 20
3]d 30 a 40
4]e 50 c 30
5]c 40 f 35
6]g 55

A11セルより集計とする場合、「VLOOKUP関数」を使い、
A   B   C
11] \  前年  今年
12] a  10  40
13] b      20

B12セル「=IF(ISERROR(VLOOKUP($A11,$A$2:$B$6,2,FALSE)),"",VLOOKUP($A11,$A$2:$B$6,2,FALSE))」
C12セル「=IF(ISERROR(VLOOKUP($A11,$C$2:$D$6,2,FALSE)),"",VLOOKUP($A11,$C$2:$D$6,2,FALSE))」
上記をフィルコピー

投稿日時 - 2006-04-17 11:16:08

お礼

返答、お礼が遅くなりすいません。
早速返答いただきありがとうございます。
教えていただいた方法で試してみました。
例題のケースではできました。
ただ、この場合、項目(この場合a、b、、、g)をすべて知っていなくてはならず、データ量が膨大な場合は、ちょっと時間がかかるとおもいました。
恐れ入りますが、この方法では解決!とまではいきませんでした。ありがとうございました。

投稿日時 - 2006-05-07 15:44:03

ANo.1

B1セルに「前年」、D1セルに「今年」
A2セル以下にa、d、e・・・の記号、B2セル以下に数値、C2セル以下に記号、D2セル以下に数値が入っているものとします。
C2からD列の最後までを範囲指定して、A列の下に移動します。
移動した数値をC列にずらします。
A2からデータの最後までを選択して、A列を最優先にして昇順で並べ替えます。
記号前年 今年
a10
a40
b20
c40
c30
d30
e50
f35
g55
こんなふうになります。
タイトルの「今年」をC1セルに移動させます。
A1セルにタイトルを入れます。ここでは「記号」としておきます。

表内のセルをどれか一つ選択した状態で、「データ」-「ピボットテーブル~」を選びます。
ピボットテーブルツールバーから
「記号」を「行のフィールド~」にドラッグします。
「前年」を「データアイテム~」にドラッグします。
「今年」も「データアイテム~」にドラッグします。
「データの個数:前年」となっていたら、右クリックして「フィールドの設定」で「集計の方法」を「合計」にします。
これで出来ると思います。

投稿日時 - 2006-04-17 00:55:05

お礼

お礼、返答が遅くなりすいません。早速教えていただきありがとうございます。私の質問が舌足らずだったのですが、最終的には
 前年 今年  差額
a 10 40 30
b    20 20
という形にして分析したいのです。教えていただいた方法では、そこまですることが出来ず解決することができませんでした。

投稿日時 - 2006-05-07 14:55:54

あなたにオススメの質問