抽出したエクセルデータの並べ直しが上手くできません。

Question

以下のようなレイアウトで、エクセルデータが出力されるのですが、抽出後の並びが、左詰で出力されてしまうため、このままの状態では統計処理等に使用することができず、手作業（通番追加後、フィルタリング機能、ドラッグ＆マウス移動を使って）にて加工後データに並べ替えている状況です。
実際のデータでは、項目数（果物）は50項目程度、件数（支店数）は5000件程度と分量が膨大であるため、重すぎない処理で、綺麗に解決できる手段がございませんでしょうか。
エクセル関数やVBAなどに詳しい方へ、何卒ご教授お願いいたします。

＜抽出後データ＞
＊＊＊ A ＊＊ B ＊＊ C ＊＊ D ＊＊E ＊ ＊   F ＊＊G ＊＊ H ＊＊  I  ＊＊  J  ＊＊ K ＊
1  青森支店  りんご  500コ  バナナ  600コ  ブドウ   700コ  イチゴ   900コ  
2  秋田支店  りんご  500コ  イチゴ  250コ
3  山形支店  バナナ  900コ  イチゴ  300コ
4  宮城支店  バナナ  200コ  キウイ  500コ  イチゴ   900コ  
・
・
・
＜加工後データ＞
＊＊＊ A ＊＊ B ＊＊ C ＊＊ D ＊＊E ＊ ＊   F ＊＊G ＊＊ H ＊＊  I  ＊＊  J  ＊＊ K ＊
1  青森支店  りんご  500コ  バナナ  600コ  ブドウ   700コ   ＊＊＊  ＊＊＊ イチゴ   900コ 
2  秋田支店  りんご  500コ  ＊＊＊  ＊＊＊  ＊＊＊   ＊＊＊   ＊＊＊  ＊＊＊ イチゴ   250コ
3  山形支店  ＊＊＊  ＊＊＊  バナナ  900コ  ＊＊＊   ＊＊＊  ＊＊＊  ＊＊＊  イチゴ   300コ
4  宮城支店  ＊＊＊  ＊＊＊  バナナ  200コ  ＊＊＊   ＊＊＊  キウイ  500コ  イチゴ   900コ  
・
・
・
※上記果物の品目の（横方向の）順番は、抽出元のシステムで決まっている順番です。

tatsu99 · Accepted Answer

以下のマクロを標準モジュールへ登録してください。クロス表を作成します。
Sheet1が抽出後データのシート名、
Sheet2がクロス表のシート名です。
--------------------------------------------------------
Option Explicit
Public Sub クロス表作成()
    Dim sh1 As Worksheet    'sheet1　抽出後データ
    Dim sh2 As Worksheet    'sheet2　クロス表
    Dim maxrow As Long     'sheet1 最大行
    Dim maxcol As Long     'sheet1 各行の最大列
    Dim dicT As Object      '連想配列
    Dim row1 As Long
    Dim row2 As Long
    Dim col1 As Long
    Dim col2 As Long
    Dim colNo As Long
    Dim key As String
    Set dicT = CreateObject("Scripting.Dictionary") ' 連想配列の定義
    Set sh1 = Worksheets("sheet1")
    Set sh2 = Worksheets("sheet2")
    sh2.Cells.ClearContents
    maxrow = sh1.Cells(Rows.Count, 1).End(xlUp).Row 'sheet1 最終行を求める
    colNo = 2
    row2 = 2
    For row1 = 1 To maxrow
        sh2.Cells(row2, "A").Value = sh1.Cells(row1, "A").Value     '支店名設定
        maxcol = sh1.Cells(row1, Columns.Count).End(xlToLeft).Column   '該当行の最終列を求める
        If maxcol < 3 Or maxcol Mod 2 <> 1 Then
            MsgBox (row1 & "行の最大列が不正")
            Exit Sub
        End If
        For col1 = 2 To maxcol - 1 Step 2
            key = sh1.Cells(row1, col1).Value
            If key = "" Then
                MsgBox (row1 & "行 " & col1 & "列の果物名が空白")
                Exit Sub
            End If
            If dicT.exists(key) = False Then
                dicT(key) = colNo
                sh2.Cells(1, colNo).Value = key '見出しへ果物名設定
                colNo = colNo + 1
            End If
            col2 = dicT(key)
            sh2.Cells(row2, col2).Value = sh1.Cells(row1, col1 + 1).Value   '個数設定
        Next
        row2 = row2 + 1
    Next
    MsgBox ("完了")
End Sub

mike_g · Answer

確認させてください。 <抽出後データ>の並び順は左列→右列にバナナ/イチゴ/キウイでなくて、バナナ/キウイ/イチゴになっています。 <加工後データ>の並び順は左列→右列にりんご/バナナ/ブドウ/イチゴ/キウイでなくて、りんご/バナナ/ブドウ/キウイ/イチゴになっています。その理由(規則)は？規則なんてモンはなくて、そのときの貴方あるいは「システム外注先」の気分次第とか？そういうことであれば、今からでもキッチリ規則を設けても構わないとか、外注先にお伺いをせねばならないとか？

tatsu99 · Answer

並べ替えた結果ですが、添付の画像を参照ください。
質問１．データは１行目から始まっている。（通常１行目は見出し行になるが、今回は見出し行はない）・・・これでOKですか。
質問２．同じ果物は同じ列に並べる。・・・これが並べ直しの条件であってますか。
質問３．キウイは、あなたの提示例ではH列ですが、H列にすることはできません。J列になりますがそれでも良いですか。
もし、キウイをどうしてもH列にしたいのであれば、予め、果物の名前と、何処に割り当てるかの、対応表が、必要です。
対応表がない場合は、以下のような手順になります。
①1行目は、果物が出現した順に列を割り当てる。
②２行目は、既出の果物は前と同じ列に割り当て、新しく出現した果物は、最後の列に割り当てる。
③３行以降は２行目と同じように繰り返す。
質問４．果物の個数ですが200コのようになっていますが、データに"コ"の文字がついているのですか。
そうすると、文字列の扱いになるので、果物の総数を求める等の計算ができなくなりますが、それでよいのでしょうか。
それとも、セルの内容は数値で格納され、表示上（書式設定で）"コ"をつけているのでしょうか。

銀鱗 · Answer

1 　　　　　りんご　バナナ　ブドウ　キウイ　イチゴ
2 青森支店　500　　 600　　700　　　　　　 900
3 秋田支店　500　　　　　　　　　　　　　　 250
4 山形支店 　　　　　900　　　　　　　　　　300
5 宮城支店　　　　 　200　　　　　　500　　900

…とすべきだよなあ。

めぐみん_ · Answer

基本的に目で見ていって途中に出てくる項目でそれまでの順位を全体に動かさなければならないと言う事ですよね？
質問文で言えば『キウイ』が該当してますが。
抽出元が決めている順番をあなたの方で守らなければならない物なのですか？（キウイとイチゴが逆では問題なのでしょうか？）

と言った疑問はあります。

blue_rumble · Answer

補足をいただき、ありがとうございます。
カンマなしの仕様なのですね。
では、そもそものお話になりますが、すべて左詰めの状態の中、何を条件に
「ここを空けなければ（右にずらさなければ）いけない」かの判断をなさっているのですか？
明確な基準、条件があるならVBAでなら対応できるはずです。

blue_rumble · Answer

csvファイルなら、項目間にカンマなどの区切り文字がセットされます。
例「リンゴ,バナナ,メロン」
仮に何もない項目が存在しても、カンマはセットされるので「リンゴ,,メロン」の様になります。
※但し、出力時点で、空白の場合は、区切り文字もセットしないという仕様であれば、以下の方法は使えません。

さて、エクセルへの取り込みですが、上部メニューの「データ」→「外部データの取り込み」→「テキストファイル」にて、
対象のCSVファイルを選択します。
次に「インポート」をクリックすると、インポートウィザードが表示され、その画面で、区切り文字の指定や、取り込み後のエクセル側のセルの書式などを指定できます。

文字で説明してもわかりにくいと思いますので、「エクセル　CSV　インポート」などで検索してみてください。

banzaiA · Answer

＞以下のようなレイアウトで、エクセルデータが出力されるのですが、
＞加工後データに並べ替えている

エクセルデータに出力されるということですが、（.txtファイルに出力可能なら）その出力を（加工後）並べ替えるフォーマットで出力すればいいでしょう。

抽出したエクセルデータの並べ直しが上手くできません。

以下のマクロを標準モジュールへ登録してください。

確認させてください。

並べ替えた結果ですが、添付の画像を参照ください。

1 りんご バナナ ブドウ キウイ イチゴ

基本的に目で見ていって途中に出てくる項目でそれまでの順位を全体に動かさなければならないと言う事ですよね？

補足をいただき、ありがとうございます。

csvファイルなら、項目間にカンマなどの区切り文字がセットされます。

＞以下のようなレイアウトで、エクセルデータが出力されるのですが、

似たような質問が見つかりました

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

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

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

1 　　　　　りんご　バナナ　ブドウ　キウイ　イチゴ