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

Question

以下の元データのように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　　け

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

kagakusuki · Accepted Answer

まず、処理を簡単にするために、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列に表示されます。

MarcoRossiItaly · Answer

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))

mike_g · Answer

面倒を厭わなければ、以下のような方法もあります。
１．セル 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]をクリック

aokii · Answer

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

MarcoRossiItaly · Answer

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

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

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

mike_g · Answer

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

aokii · Answer

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] をクリックします。
フィルタ後のリストが表示され、重複する行が非表示になります。
このデータをコピーして貼り付けます。

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

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

No.3 です。

面倒を厭わなければ、以下のような方法もあります。

追記です。

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

》 2列の情報を1列に順番に並べたい

この回答への補足

AB列のデータの重複行を削除し、以下のように、A列を10倍、C列を10倍+1、のようにしてから、CD列のデータをAB列の下に入れ、A列優先でABデータを並べ替えてはいかがでしょう。

この回答への補足

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

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