エクセル関数で、2列の情報を1列に順番に並べたい

質問

以下の元データのようにA～D列にある情報を、
下方のE列、F列にあるように情報を合わせて表示したい場合、
E列、F列にはどのような関数を入れたらよいでしょうか。

COUNTIFやVLOOKUP関数を試してみましたが、
うまく順番に並べることができず途方に暮れています。。。

どなたかお知恵がある方、マクロではなく関数で
教えていただけたらありがたいです。

※A列はB列の情報番号、C列はD列の情報番号です。

↓元データ↓

A　　B　　　　C　 　D
1　みかん　　1　あ
1　みかん　　2　い
1　みかん　　3　う
2　りんご　　 4　え
2　りんご　　 5　お
3　すいか　　6　か
3　すいか　　7　き
3　すいか　　8　く
3　すいか　　9　け

↓このようにしたいです。↓

A　　B　　　　C　　D　　　E　　F
1　みかん　　1　 あ　　　 1　　みかん
1　みかん　　2　 い　　　 1　　あ
1　みかん　　3　 う　　　 2　　い
2　りんご 　　4　 え　　　 3　　う
2　りんご 　　5　 お　　　 2　　りんご
3　すいか　　6　 か　　　 4　　え
3　すいか　　7　 き　　　 5　　お
3　すいか　　8　 く　　　　 3　　すいか
3　すいか　　9　 け　　 　6　　か
　　　　　 　　　　　　　　　　7　　き
　　　　　　 　　　　　　　　　8　　く
　 　　　　　 　　　　　　　　　9　　け

何とぞよろしくお願いします。

回答No.4

kagakusuki · ベストアンサー

まず、処理を簡単にするために、1行目は項目名等を入力するために使用し、実際のデータは2行目（或いは更に下の行）から入力する様にして下さい。
　その上で、E2セルには次の関数を入力して下さい。

=IF(ISNUMBER($E1),IF(COUNTIF($E$1:$E1,$E1)=COUNTIF($A:$A,$E1)+1,IF(COUNTIF($A:$A,">"&$E1),SMALL($A:$A,COUNTIF($A:$A,"<="&$E1)+1),""),$E1),IF(ROWS($2:2)=1,IF(COUNT($A:$A),MIN($A:$A),""),""))

　次に、F2セルに次の関数を入力して下さい。

=IF($E2="","",IF(COUNTIF($E$1:$E1,$E2),INDEX($D:$D,MATCH($E2,$A:$A,0)+COUNTIF($E$1:$E1,$E2)-1),VLOOKUP($E2,$A:$B,2,FALSE)))

　次に、E2～F2の範囲をコピーして、同じ列範囲の3行目以下に貼り付けて下さい。

　これで、御希望の様に並べ替えられたデータがE列～F列に表示されます。

お礼

シンプルでわかりやすい回答をありがとうございました。

みなさまへ＞＞
面倒な質問にもかかわらず、丁寧にわかりやすく
教えてくださって心から感謝しています。

全てのやり方をやってみたうえで、最低な方法を
みつけることができ、おかげさまで、
何とか完成させることができました。

いろいろな発想があることがわかり、
視野が広がりました。楽しいですね。
今後は目的に応じて、教えていただいた
さまざまなやり方を使い分けて活用していきたいです。

今回は手数が少ない方法に感じたので、
ベストアンサーをNO.4にさせていただきました。
自分でゼロから組もうとすると複雑かもしれませんが、
引き続き勉強していきたいと思います。

本当に助かりました。ありがとうございました。

回答No.7

MarcoRossiItaly

No.3 です。補足します。

実際には、私がご質問の処理を行うとしたら、No.6 さんのような感じの方法で行うと思います。つまり、お勧めです。

その方法をかいつまんで言えば、易しい数式、空白行の挿入・削除、ジャンプの機能を、併用するということです。全体の作業の時間としては、データ量が大量だったとしても、数分間で終わると思います。

質問文は数式による表示についてのお尋ねでしたし、正直、上の操作は説明の量が多くて大変なので、つい手抜いてしまいましたが…。すみません。

ということで No.3 は、自分で回答しておいてナンですが、こちらの勝手な判断ではベストアンサーと考えていません。なぜならマクロのコードを書くのにも、ちょっとは時間がかかりますから。難解な数式をセルに記入・構築して一発で表示させようとするよりは、まだマシですが。そういう方法もある、くらいに受け止めていただければと思います。

第 1（数式）、第 2（マクロ）、第 3（ジャンプほかの機能の併用）の方法が出ましたが、第 4 の方法としては、次のような、ピボットテーブルと VLOOKUP、OFFSET 関数の併用があると思います。これも No.6 さんの方法ほどには簡単ではありませんが、ご参考に。

(1)元データの 1 行目に項目名（番号1、果物、番号2、平仮名）の行を設置する。
(2)数式を使うなどして、元データの A 列の各値を 10^4 倍しておく。
(3)元データにピボットテーブルを適用し、別シートに表示。
(4)行ラベルのボックス内に「番号1」と「番号2」のフィールドをドラッグ。
　 ピボットテーブルレポートの表内に表示されている数値のセルを右クリック。
　 「フィールドの設定 > レイアウトと印刷タブ」にて「アウトライン形式」と「表形式」を選択できますが、「アウトライン形式」のまま、変更しない。
　 すると、ピボットテーブルレポートの表は既に、ご希望の配置になっています。
　 その表をコピーして、さらに別シートの A1 セルに貼り付け。
(5)元データのシート名が「Sheet1」であれば、3 枚目のシートに次式を記入。

3 枚目のシート
B2　=vlookup(a2,offset(sheet1!a:b,0,2*(a2<10^4)),2,)
C2　=a2/(1+9999*(a2>=10^4))

お礼

何度もご丁寧にありがとうございました。
図表の例示も番号を付けてくださり、
目で追いながら確認がしやすかったです。
見る側へのご配慮に感謝しています。

みなさまへ＞＞
面倒な質問にもかかわらず、丁寧にわかりやすく
教えてくださって心から感謝しています。

全てのやり方をやってみたうえで、最低な方法を
みつけることができ、おかげさまで、
何とか完成させることができました。

いろいろな発想があることがわかり、
視野が広がりました。楽しいですね。
今後は目的に応じて、教えていただいた
さまざまなやり方を使い分けて活用していきたいです。

今回は手数が少ない方法に感じたので、
ベストアンサーをNO.4にさせていただきました。
自分でゼロから組もうとすると複雑かもしれませんが、
引き続き勉強していきたいと思います。

本当に助かりました。ありがとうございました。

回答No.6

noname#204879

面倒を厭わなければ、以下のような方法もあります。
１．セル E1 に次式を入力して、此れを下方へズズーッと
　　ドラッグ＆ペースト
　　　=IF(COUNTIF(A$1:A1,A1)=1,0,"A")
２．列Ｅを選択 (⇒ Fig-1)
３．[編集]⇒[ジャンプ]⇒[セル選択]を実行
４．“数式”に目玉を入れて、“数値”以外のチェックを
　　外して[OK]をクリック (⇒ Fig-2)
５．[挿入]→[行]を実行 (⇒ Fig-3)
６．セル E1 に式 =IF($C1="",A2,C1) を入力して、此れを
　　右隣へドラッグ＆ペースト
７．範囲 E1:F1 を下方へズズーッとドラッグ＆ペーストし
　　た直後に(Fig-4 の状態で)[コピー]→[値の貼り付け]を実行
８．列Ａ～Ｄを選択
９．ステップ３を実行
10．“空白セル”のみに目玉を入れて、[OK]をクリック
11．[編集]→[削除]から“上方向にシフト”目玉を入れて、
　　[OK]をクリック

お礼

ユニークなアイデアをいただきありがとうございました。
作表のみの場合などで活用できそうです。

みなさまへ＞＞
面倒な質問にもかかわらず、丁寧にわかりやすく
教えてくださって心から感謝しています。

全てのやり方をやってみたうえで、最低な方法を
みつけることができ、おかげさまで、
何とか完成させることができました。

いろいろな発想があることがわかり、
視野が広がりました。楽しいですね。
今後は目的に応じて、教えていただいた
さまざまなやり方を使い分けて活用していきたいです。

今回は手数が少ない方法に感じたので、
ベストアンサーをNO.4にさせていただきました。
自分でゼロから組もうとすると複雑かもしれませんが、
引き続き勉強していきたいと思います。

本当に助かりました。ありがとうございました。

回答No.5

aokii

追記です。
その後A列で並べ替えして、EF列にコピーしてください。

お礼

追記の書き込みありがとうございました。

みなさまへ＞＞
面倒な質問にもかかわらず、丁寧にわかりやすく
教えてくださって心から感謝しています。

全てのやり方をやってみたうえで、最低な方法を
みつけることができ、おかげさまで、
何とか完成させることができました。

いろいろな発想があることがわかり、
視野が広がりました。楽しいですね。
今後は目的に応じて、教えていただいた
さまざまなやり方を使い分けて活用していきたいです。

今回は手数が少ない方法に感じたので、
ベストアンサーをNO.4にさせていただきました。
自分でゼロから組もうとすると複雑かもしれませんが、
引き続き勉強していきたいと思います。

本当に助かりました。ありがとうございました。

回答No.3

MarcoRossiItaly

数式をセルに記入することで達成したいとのご希望ですが、こういった複雑な並べ替えをしようとすると難解な数式となるので、実用性が低く、全くお勧めではない方法です。

一応、次式により、できないことはありません。

E1　=a1
E2　=index($A:$D,$G2+($G1=$G2),columns($E2:e2)+2*(1-($G1=$G2)))
G1　=max(index((row(G$1:g1)+A$1:a1+(A$1:a1=0)*max(a:a)<=row(g1))*row(G$1:g1),))

※
E2 セルをコピーして E2:E12 のセル範囲に貼り付け。次いで、E1:E12 をコピーして F1:F12 に貼り付け。

マクロを使えば、苦しんで数式を編み出さなくても、遥かに簡単にできます。次のコードを標準モジュールに貼って実行してください。

Sub ArrangeWithSubtitles()
　　Dim i As Long
　　Range("a1:b1").Copy Destination:=Range("e1")
　　Range("c1:d1").Copy Destination:=Range("e2")
　　For i = 2 To Cells(Rows.Count, "a").End(xlUp).Row
　　　　If Cells(i - 1, "a").Value <> Cells(i, "a").Value Then
　　　　　　With Cells(Rows.Count, "e").End(xlUp)
　　　　　　　　.Offset(1, 0).Value = Cells(i, "a").Value
　　　　　　　　.Offset(1, 1).Value = Cells(i, "b").Value
　　　　　　End With
　　　　End If
　　　　With Cells(Rows.Count, "e").End(xlUp)
　　　　　　.Offset(1, 0).Value = Cells(i, "c").Value
　　　　　　.Offset(1, 1).Value = Cells(i, "d").Value
　　　　End With
　　Next i
End Sub

お礼

ご提案ありがとうございます。
マクロは現在勉強中ですが、なかなか理解が追い付いていません。
ご教示いただいたコードも今度練習でやってみたいと思います。

回答No.2

noname#204879

》 2列の情報を1列に順番に並べたい
とのことだけど、
「２列」とは、「元データ」の何処と何処の列を指しているの？
「１列」とは、「このようにしたい」表の何処の列のこと？

お礼

補足にこたえていただきありがとうございました。
心から感謝いたします。

補足

早速のご回答ありがとうございます。
説明不足ですみませんでした。以下のように考えて書いていました。

>「２列」とは、「元データ」の何処と何処の列を指しているの？
⇒たとえば、A列とB列を▲、C列とD列を■のかたまりとしたとき、
　▲と■のことを指して「２列」と表現してしまいました。
　“２列”を“2情報”と書くべきでした。

>「１列」とは、「このようにしたい」表の何処の列のこと？
⇒同様の考え方で、たとえば、E列とF列を●としたとき、
　●を指して「1列」と書いてしまいました。

つまり、

A列+C列　→E列
B列+D列　→F列

ということが言いたかったのです。

貴重なお時間のお手を煩わせてしまい、申し訳ありませんでした。
引き続きご回答いただけるようでしたら是非お待ちしたいと思います。
なにとぞよろしくお願いいたします。

回答No.1

aokii

AB列のデータの重複行を削除し、以下のように、A列を10倍、C列を10倍+1、のようにしてから、CD列のデータをAB列の下に入れ、A列優先でABデータを並べ替えてはいかがでしょう。
A　　B　　　　C　 　D
10　みかん　11　あ
20　りんご　21　い
30　すいか　31　う
　　　　　　　　41　え
　　　　　　　　51　お
　　　　　　　　61　か
　　　　　　　　71　き
　　　　　　　　81　く
　　　　　　　　91　け

重複行の削除は、
[データ] メニューの [フィルタ] の[フィルタ の詳細設定] で、[重複するレコードは無視する] のチェック ボックスをオンにし、[OK] をクリックします。
フィルタ後のリストが表示され、重複する行が非表示になります。
このデータをコピーして貼り付けます。

お礼

補足にこたえていただきありがとうございました。
心から感謝いたします。

補足

早速ご回答いただきありがとうございました。

教えていただいた通りにやってみましたところ、
以下のようになってしまいました。
何かやり方が違っているのでしょうか・・・。

10 みかん
11 あ
20 りんご
21 い
30 すいか
31 う
41 え
51 お
61 か
71 き
81 く
91 け

↓こんなふうにしたいのです。。。↓

10 みかん
11 あ
12 い
13 う
20 りんご
24 え
25 お
30 すいか
36 か
37 き
38 く
39 け

説明がうまくできず、わかりづらくてすみません。
何度もすみませんが、引き続きご回答いただけると嬉しいです。

よろしくお願いいたします。