Excel 並び替え

質問

横に並んでいる情報を並び替えたいです。

説明が難しいので、図に示します。

Ｅｘｃｅｌの機能を使ってできるものでしょうか。

マクロを組まないと無理ですか？

回答No.5

MackyNo1 · ベストアンサー

ご質問の意味がやっと分かりました。
1つの行で2列のデータごとに名前を基準に昇順に並べ替えたいということですね。

その場合は以下のような配列数式を使えば表示することができます。

まず以下の式を入力し右方向に１つオートフィルします。

=INDEX($A1:$P1,MAX(INDEX((COUNTIF($A1:$P1,"<="&$A1:$P1)*MOD(COLUMN($A1:$P1),2)=INT((COLUMN(A1)+1)/2))*COLUMN($A1:$P1),)))

右のセルの数式の最後に「+1」を追加して以下のように変更します。

=INDEX($A1:$P1,MAX(INDEX((COUNTIF($A1:$P1,"<="&$A1:$P1)*MOD(COLUMN($A1:$P1),2)=INT((COLUMN(B1)+1)/2))*COLUMN($A1:$P1),))+1)

この2つのセルを右方向に全部で16列分オートフィルします。
この16列を下方向にデータ数分だけオートフィルします。

ただし、一度に1000行もこの数式をオートフィルコピーするとシートの動きが重くなるので、たとえば100行ずつオートフィルするなどして（必要に応じて数式を値に変更して）作業してください。

お礼

つたない再補足で真意を読み取っていただき感謝いたします。
＞1つの行で2列のデータごとに名前を基準に昇順に並べ替えたい
　⇒その通りです。ピッタリな説明です。。。

すごいです、一瞬で並び替えが完了しました！
記載いただいた関数を読み解こうと思ったのですが…難しいですね。
もっと勉強いたします。

これでマクロを使用しないで並び替えを完了することができます。
計算式、このまま使わせていただこうと思います。
ありがとうございました！！

INDEX関数【INDEX（範囲,行番号,列番号,領域番号)】【INDEX(配列,行番号,列番号)】
・セル範囲形式・・・指定した行と列が交差する位置にあるセルの値を抽出または参照を返す
・配列形式・・・行番号と列番号で指定したテーブルまたは配列の値を返す

MOD関数【MOD(数値,除数)】
・余りを求める

COLUMN関数【COLUMN(範囲)】
・引数として指定された範囲の列番号を返す

INT関数【INT(数値)】
・小数点以下を切り捨てる

回答No.4

MackyNo1

補足説明をしていただきましたが、まだご希望の操作がわかりません。

＞それぞれのデータ数がすべて同じで３つずつなのでしょうか？
　⇒データー数は列数は１６列、行数は１０００行ほどになります

私の聞いているのは品名が重複するデータ（たとえばオレンジ）の数がすべて同じで、いくつずつあるのかということです。

＞データの抽出順はどのような基準があるのでしょうか？
　⇒あるデータベースからｃｓｖにてダウンロードしてきた結果となります
　　抽出準はそのデータベースに入っている順番であり、順番は順不同で頭から入れられます
　　「オレンジが最初」という基準はない。「ジュースが最初」の時もある。「項目が含まれない（オレンジが含まれない）」ケースもあり。

私が聞いているのは最終的に並べ替えるときどのような基準でならべかえたいのか（順番にこだわるのか）ということです。

＞それぞれのデータ数
　⇒１行の項目は８項目＋８値段となります。（それが１０００行）

このレイアウトは了解しましたが、最終的にどのような形で並べ替えたいのかということです。
すなわち、2列×8組＊1000行のデータに並べ替えたいということでしょうか？
この時、おそらく1000件も同じ商品があると思えないので、同じ列には同じ商品名を表示するという条件は」いらないということですね。

今回の操作の目的が、すべてのデータを2列単位で並べ替えて、そのデータを再度2列×8組＊1000行のデータに配置替えしたものを印刷したいというなら、実戦的には以下のような操作手順が簡単です(2，3分で作業できます）。

C１:D1セルを選択して、Ctrl+Shift+↓キーでC,D列のデータ範囲を選択し、Ctrl+Cでコピーし、Ctrl+←、↓のあとに、Ctrlを離して↓キーだけでA列の最下行の1つ下のセルを選択し、Ctrl+Vで貼り付けます。
再度Ctrl+↑で1行目のセルを選択し、同様にE、F列のコピーをしてA列の最下行に貼り付け作業を7回行います。
このデータをA列を基準に並べ替え、上記の操作でA,B列データ範囲を選択し、Ctrl＋Cでコピーしwordの新規文書にCｔｒｌ＋Vで貼り付けし（適宜フォントサイズなどをを調整し）、ページレイアウトタブで４段を指定してください。

関数でエクセル上で表示したいなら、以下の式を入力して右方向に１つオートフィルし、その数式の「INDEX($A:$A」の部分を「INDEX($B:$B」に修正し、この2つのセルを下方向および右方向に16列分オートフィルコピーしてください。

=IF(COUNTA($A:$A)/8<ROW(1:1),"",INDEX($A:$A,ROW(1:1)+INT((COLUMN(A:A)-1)/2)*COUNTA($A:$A)/8))

もちろん16列の元データを関数で2列のデータに変形することもできますが、実際のデータ数が1000行に決まっている場合や、決まっていなくても各列のデータ数が同じような条件がある場合はより簡単な数式で表示できますので、どうしても関数で2列にしたいのであれば、このあたりの条件も提示されたほうが良いと思います。

＃上記の操作がご希望の操作と違う場合は、詳細を補足説明してください。

お礼

的外れな補足で申し訳ありません。
以下再度補足いたします。

＞私の聞いているのは品名が重複するデータ（たとえばオレンジ）の数がすべて同じで、いくつずつあるのかということです。
　⇒　１行中では重複することはありません。
　　　全体で見ると含む行もあれば含まない行もあるため、いくつあるかは不明です。

＞私が聞いているのは最終的に並べ替えるときどのような基準でならべかえたいのか（順番にこだわるのか）ということです。
　⇒　１～１０のように昇順です
　　　順番にはこだわります。

＞このレイアウトは了解しましたが、最終的にどのような形で並べ替えたいのかということです。
すなわち、2列×8組＊1000行のデータに並べ替えたいということでしょうか？
この時、おそらく1000件も同じ商品があると思えないので、同じ列には同じ商品名を表示するという条件は」いらないということですね。
　⇒　１行ずつ昇順に数字をキーに並べ替えを行いたく思います。
　　　商品は８種類となります。
　　　そのため、ご指摘の通り、１行でみれば商品は重なりません。

＞C１:D1セルを選択して・・・
　⇒　実行したところ、行の内容が変わってしまいました。
　　　１行は単独扱い、維持したい、という説明が抜けており申し訳ありませんでした。

　　　※１行目のオレンジと２行目のオレンジが入れ替わってはダメなんです
　　　　項目は８項目なのですが、それに付随する値段はまちまちなのです。
　　　　オレンジジュースは１００円の時もあれば２００円の時もある。

※画像を添付したかったのですが、やり方が分からず文字だけの説明となってしまったことをお詫び申し上げます。

回答No.3

tom04

こんにちは！
VBAでの一例です。

元データはSheet1にあり、Sheet2に表示するとします。
前提条件として
（1）Sheet1のデータは1行目からある。
（2）必ず2列で1セットになっている。
（3）1.ジュース　のように数値の後は必ず「.」（ピリオド）となっている。

尚、Sheet3を作業用のSheetとして使用していますので、
Sheet3は全く使用していない状態にしておいてください。

Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻り、マクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, lastRow As Long, lastCol As Long, wS2 As Worksheet, wS3 As Worksheet
Set wS2 = Worksheets("Sheet2")
Set wS3 = Worksheets("Sheet3")
Application.ScreenUpdating = False
wS2.Cells.ClearContents
wS3.Range("A1") = "ダミー"
With Worksheets("Sheet1")
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
lastCol = .Cells(i, Columns.Count).End(xlToLeft).Column
.Cells(i, "A").Resize(, lastCol).Copy
wS3.Range("B2").PasteSpecial Paste:=xlPasteAll, Transpose:=True
lastRow = wS3.Cells(Rows.Count, "B").End(xlUp).Row
With Range(wS3.Cells(2, "A"), wS3.Cells(lastRow, "A"))
.Formula = "=IF(COUNT(B2),A1,LEFT(B2,FIND(""."",B2)-1))"
.Value = .Value
End With
wS3.Range("A1").CurrentRegion.Sort key1:=wS3.Range("A1"), order1:=xlAscending, Header:=xlYes
Range(wS3.Cells(2, "B"), wS3.Cells(lastRow, "B")).Copy
wS2.Cells(i, "A").PasteSpecial Paste:=xlPasteAll, Transpose:=True
wS3.Range("B:B").ClearContents
Next i
End With
wS3.Cells.Clear
With wS2
.Columns.AutoFit
.Activate
.Range("A1").Select
End With
End Sub 'この行まで

※　関数でないので、Sheet1のデータ変更があるたびにマクロを実行する必要があります。m(_ _)m

お礼

コード、ありがとうございます。
関数にこだわるのはダメですね。

さっそく実行してみました。
まさしく望み通りの形になりました！

ＶＢＡはかじるくらいしかわからなく、一つ一つゆっくり動かしていくと
きれいに無駄なく動いていました。
感動です。。。

特に以下のコード。
私にはまだ理解が付きません。
でもこれが一番のポイントだと思われます。
きれいですね。。。

With Range(wS3.Cells(2, "A"), wS3.Cells(lastRow, "A"))
.Formula = "=IF(COUNT(B2),A1,LEFT(B2,FIND(""."",B2)-1))"
.Value = .Value
End With

「マクロを使わないで加工したい」との当初の目的からはずれましたが、
とても勉強になりました。

いただいたコードを再度確認します。
ありがとうございました。

回答No.2

nishi6

添付図のようなカタチを想定します。３行×４列（「２」列でセット）と考えます。この「２」と「３」が算式内に何度も出てきます。拡張される場合は注意してください。

また、行（Row）や列（Column）を使っているため、算式の単純化を目的にSheet2、Sheet3をA1セルから使っています。行・列が「1」から始まるため考えやすくなります。

●まず、Sheet1の３行×４列をSheet2で、12行×１列（２列でセット）にします。
Sheet2のセルA1,
=INDEX(Sheet1!$A$1:$H$3,MOD(ROW()-1,3)+1,INT((ROW()-1)/3)*2+COLUMN())
　３行×４列分コピーします。
　※2つの「3」がSheet1の行数です。
　※「2」は2列で1セットの「2」です。
　※「1」は数値を0から計算するための定数です。

　次に、１列に並んだデータをコピーして横に値貼り付けします。添付図ではD・E列に貼り付けました。貼り付けたデータを商品名で昇順に並べ替えます。（先頭行は表題にしません）

●Sheet2のD・E列を再度３行×４列（２列でセット）に作り直します。
Sheet3のセルA1,
=INDEX(Sheet2!$D$1:$E$12,MOD(ROW()-1,3)+1+INT((COLUMN()-1)/2)*3,MOD(COLUMN()-1,2)+1)
　３行×４列分コピーします。
　※ここでも、2つの「3」がSheet1の行数です。　
　※「2」は2列で1セットの「2」です。

　でき上がりです。

表の３行×４列すべてが埋まっていない場合は、明らかに最後になるダミーデータで埋めてください。未入力を文字列、数値属性で判定すると算式が冗長になるか、２つに分ける必要になるのがその理由です。最後にダミーデータを消去してください。

お礼

回答が遅くなり、申し訳ありません。
私の説明が抜けておりました。
関数の詳細まで記載いただいたのに本当に申し訳りません。

以下実行結果となります。
ありがとうございました。

＞●まず、Sheet1の３行・・・
⇒　実行したところ、行の内容が変わってしまいました。
　　　１行は単独扱い、維持したい、という説明が抜けており申し訳ありませんでした。

　　　※１行目のオレンジと２行目のオレンジが入れ替わってはダメなんです
　　　　項目は８項目なのですが、それに付随する値段はまちまちなのです。
　　　　オレンジジュースは１００円の時もあれば２００円の時もある。

※画像を添付したかったのですが、やり方が分からず文字だけの説明となってしまったことをお詫び申し上げます。

補足

おはようございます。

コメントありがとうございます。
まだ全部を飲み込めていませんが、本日いっぱいをかけてｔｒｙしてみます。
結果はお礼に記載予定です。

もうしばらくお時間を下さい。
わかりやすく図まで添付いただきありがとうございまいす！！

回答No.1

MackyNo1

例示されているデータがあまりにも特殊な例のように思うのですが、それぞれのデータ数がすべて同じで３つずつなのでしょうか？
また、データの抽出順はどのような基準があるのでしょうか？

一般的に関数で処理して対応するなら、関数で2列のデータにしてから、名前の列で並べ替えして同じデータを一塊にして、これを関数で横のデータに再配分するような手順になります。

実際のデータのレイアウトやそれぞれのデータ数などの詳細な条件を提示していただければ、具体的な操作法を示すことができると思います。

お礼

深夜も関わらず、回答ありがとうございます。
以下詳細となります。

２セル（前後）ひと塊で行毎に昇順で並び替えができないものか、と頭を悩ませております。
（1.ジュースと￥１００はセット、2.オレンジジュースと￥３００はセット、3.ファンタと￥４００はセット）

難しいでしょうか。

＞それぞれのデータ数がすべて同じで３つずつなのでしょうか？
　⇒データー数は列数は１６列、行数は１０００行ほどになります

＞データの抽出順はどのような基準があるのでしょうか？
　⇒あるデータベースからｃｓｖにてダウンロードしてきた結果となります
　　抽出準はそのデータベースに入っている順番であり、順番は順不同で頭から入れられます
　　「オレンジが最初」という基準はない。「ジュースが最初」の時もある。「項目が含まれない（オレンジが含まれない）」ケースもあり。

＞実際のデータのレイアウト
　⇒類似データ（画像どおり）しか開示ができない状態です。申し訳ありません。

＞それぞれのデータ数
　⇒１行の項目は８項目＋８値段となります。（それが１０００行）