☰

Excel で特定の列が繰り返されないようにする

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
Excel で特定の列が繰り返されないようにする

ERP システムから取得したレポートには、注文の詳細情報が記載されています。注文番号、顧客コード、顧客名、注文日、注文ステータス、注文合計、製品コード、製品名、注文数量、単価、合計価格がリストされます。注文に複数の行がある場合は、ヘッダー情報が複数回リストされます。

生データは次のようになります。 注文レポート未フォーマット

Excel で、各行の詳細行のヘッダー行が繰り返されないようにする方法を探しています。

完了したら、データが次のようになることを望みます。基本的に、各行はそれぞれのヘッダー行の下に繰り返されます。 注文レポートフォーマット1

他に許容される形式としては、以下のものがあります。これはもっとシンプルだと思います。私はピボット テーブルでこれを実現できました。 ここに画像の説明を入力してください

これに VBA が必要かどうかはわかりません。行を強調表示して重複を削除してみましたが、すべての行が一緒に移動します。ご協力いただければ幸いです。

答え1

これは好ましくないことだとは分かっていますが、問題は興味深いものだったので、VBAを書きました。

以下のコードを試してください。提供された例に合わせて定数を設定しましたが、実際のアプリケーションでは変更する必要があるかもしれません。

Function CompressReport()

    'Settings for which columns are the header and details
    Const fHC As Long = 1   'First header column number
    Const lHC As Long = 6   'Last header column number
    Const fDC As Long = 7   'First detail column number
    Const lDC As Long = 11  'Last detail column number

    'Declarations
    Dim rStart&, rStop&, rNew As Long
    Dim r&, c As Long
    Dim ws As Worksheet
    Dim wsNew As Worksheet
    Dim s1$, s2 As String

    'Set the source worksheet to be compressed
    '(Here are a few methods to do this. Pick one.)
    Set ws = Sheet1
    Set ws = Worksheets(1)
    Set ws = Worksheets("Sheet1")

    'Add a new worksheet for our results
    Set wsNew = Worksheets.Add(After:=ws)

    With ws
        'Copy the first row of headers
        .Range(.Cells(1, fHC), .Cells(1, lHC)).Copy wsNew.Cells(1, 1)
        rNew = 2

        'Loop through all the rows
        For rStart = 2 To ws.UsedRange.Rows.Count

            'Copy the header information
            .Range(.Cells(rStart, fHC), .Cells(rStart, lHC)).Copy wsNew.Cells(rNew, 1)

            'Add a thick border (This wasn't in the OP but I recommend it)
            With wsNew.Range(wsNew.Cells(rNew, 1), wsNew.Cells(rNew, lHC - fHC + 1)).Borders(xlEdgeTop)
                .LineStyle = xlContinuous   'You could also try xlDouble
                .Weight = xlThick
            End With

            'Collect the header information into a single unique ID
            s1 = ""
            For c = fHC To lHC
                s1 = s1 & "|" & .Cells(rStart, c).Value
            Next

            'Find the next row with different information
            For rStop = rStart + 1 To .UsedRange.Rows.Count
                s2 = ""
                For c = fHC To lHC
                    s2 = s2 & "|" & .Cells(rStop, c).Value
                Next
                If s2 <> s1 Then Exit For
            Next
            rStop = rStop - 1

            'Copy the detail headers and information
            .Range(.Cells(1, fDC), .Cells(1, lDC)).Copy wsNew.Cells(rNew + 1, 2)
            .Range(.Cells(rStart, fDC), .Cells(rStop, lDC)).Copy wsNew.Cells(rNew + 2, 2)

            'Increase the row we're pasting in the new worksheet
            ' +1 for header data, +1 for detail headers, +n for detail information
            rNew = rNew + 1 + 1 + (rStop - rStart + 1)

            'Increase the row we're copying in the source worksheet
            rStart = rStop  'The FOR loop will iterate it +1

        Next

    End With

    'Formatting (feel free to add to this part)
    With wsNew
        .Columns.AutoFit
    End With

    'Cleanup
    Set wsNew = Nothing
    Set ws = Nothing

End Function

答え2

ここに、あなたの目標を達成するためのちょっとしたコツがあります。これは、どの列のセルにも適用できます。次のようにして始めましょう。

ここに画像の説明を入力してください

そして、私たちは余分なものを見ないようにしたいのですマイクなど。セルをクリックしますA2条件付き書式を適用して、セルの値が上のセルと同じ場合は、フォントの色がセルの背景色と同じになるようにします。

ここに画像の説明を入力してください

次にセルをコピーしますA2そして、PasteSpecialFormats を列の下に貼り付けます。これにより、繰り返しの値が「非表示」になります。

ここに画像の説明を入力してください

実際のデータはそのまま残り、表示のみが変更されます。

答え3

置いた

                        マークダウン内のCSVデータ。「編集」をクリックしてアクセスします。

Sheet1 に入力すると、Sheet2 は次のようになります。

2 つのヘルパー列を使用しますが、もちろん、必要に応じて右に移動したり、非表示にしたりすることができます。

  • A1(Sheet2 で) を に設定し=Sheet1!A1、右にドラッグして、複数の行にまたがって重複する列を覆います。あなたの例では、これは Column になります F。(私の例では Column です C。)
  • Y2を に設定し2、Z2を に設定します1。列の値は、  YSheet1の行これ行はデータの取得元です。列 Zは1、これがヘッダー行 (Sheet1 の左側の列、つまりキー フィールドからデータを取得) の場合、 2これがサブヘッダー行の場合、3これがサブデータ行 (Sheet1 の右側の列からデータを取得) の場合、および0これが空白行 (最後のデータ行の下) の場合です。
  • A2に設定します=IF($Z2=1, INDEX(Sheet1!A:A, $Y2), "")。該当する場合は、右にドラッグして、キー データのみに使用される列を覆います。あなたの例では、列 から始まる非キー データがあるため、これは適用されません B。(私の例では、列 までです B。) これにより、ヘルパー列の定義が実装されます。Zが の場合は1、Sheet1 からキー データが取得され、それ以外の場合は空白になります。

  • C2私の例では、

    =CHOOSE($Z2+1, "", INDEX(Sheet1!C:C, $Y2), Sheet1!D$1, INDEX(Sheet1!D:D, $Y2))

    あなたの例では、B2次のように設定する必要があります

    =CHOOSE($Z2+1, "", INDEX(Sheet1!B:B, $Y2), Sheet1!G$1, INDEX(Sheet1!G:G, $Y2))

    BSheet2 列が取得する可能性がある2 つの Sheet1 列を反映します 。

    • 列 B（「顧客コード」）、または
    • 列 G（「製品コード」）

    これもヘルパー列の指示どおりに動作します。値に、、、、をマップし、、、、、を追加します。  1最初の 引数Zを使用して次の引数にインデックスを付けます。01231234CHOOSE

    • Zが空白の場合0、
    • Zの場合1、キーデータを取得します。
    • Zの場合は2、Sheet1の行から見出しを取得し1、
    • Zの場合3、非キーデータを取得します。

  • Y3に設定=IF($Z2<3, $Y2, $Y2+1)しZ3、

    =IF($Z2=0, 0, IF($Z2<3, $Z2+1, IF(INDEX(Sheet1!A:A,$Y2+1)="", 0,
                    IF(INDEX(Sheet1!A:A,$Y2)=INDEX(Sheet1!A:A,$Y2+1), 3, 1))))

    (すべて 1 行に)。これらは、Z前の行の値が1または2(または 0) の場合、このY値を前の行の値と同じに設定することを意味します。これは、データベース テーブルのすべての行 (Sheet1 の列の一意の値のすべてのセット) がA、FSheet2 に少なくとも 3 つの行をもたらすためです。それ以外の場合は、Y値を増分して、Sheet1 の次の行に対応します。

    前のZ値が であれば0、これで終了なので、 をゼロで埋めます。前のZ値が1または で2あれば、次の値に進みます。それ以外の場合は、Sheet1 のキー データを確認します。空白の場合は、データの末尾にあると想定し、Zに 設定します0。前の行と同じ場合は、 を使用して3作業を続けます。それ以外の場合は、新しい一意の値のセットに入るので、 でサイクルを再開します1。

  • すべてのデータが表示されるまで下にドラッグします。

A4一意の値が個別に一意でない場合 (たとえば、 =であるA5がB4≠ である場合B5)、列のテストを拡張して Z 、必要な数の列をテストします ( と組み合わせますAND(…))。

=$Z2=2当然ですが、サブ見出しを適切にフォーマットするために、という数式を使用した条件付き書式を使用しました。

関連情報