☰

複数の行を 1 つの列に統合する方法

tag-icon • tag-icon microsoft-excel worksheet-function vba transpose
複数の行を 1 つの列に統合する方法

これが私の状況です。38,000 件の顧客アカウントのリストを取得し、その情報を新しい Web サイトにインポートできる特定のテンプレートに並べ替える必要があります。ただし、データの表示方法により、それが困難になっています...

1 人の顧客に保存されている住所はそれぞれ別の行です。したがって、請求先住所または配送先住所が異なる場合は、2 行になります。場合によっては 3 行になります。これらの行を 1 行の下の列 (住所 1、住所 2 など) に移動する必要があります。したがって、CustomerA に 3 つの住所が保存されている場合は、これらの 3 行を 1 行の 3 列に移動する必要があります。これでおわかりいただけたでしょうか。

数式、ピボット テーブル、または VBA (この分野では Jon Snow と同じくらいの知識です) のどれが最適な方法なのか、私にはわかりません。ピボット テーブルを試しましたが、うまくいきませんでした。インデックス/一致が最適な解決策かもしれないと考えていました。各アドレス列の数式を複製するだけで、目的を達成できます。ただし、複数の行に対応する方法がわかりません。

以下は、私が受け取ったデータの例です。行 20 は、行を移動する必要があるテンプレートです。列 g_user id は顧客ごとに一意ですが、1 人の顧客のアドレスの行が複数ある場合、その値はスプレッドシートで重複します。customer_no も同様です。

http://www.filedropper.com/excelhelp

またはこの画像を見る

さらに詳しい情報が必要な場合はお知らせください。さらに詳しく説明いたします。

編集 - 投稿したスプレッドシートから、行 2 と 3 を取得し、住所情報を行 1 の新しい列に移動する必要があります。住所は 2 列、市区町村は 1 列、州は 1 列、郵便番号は 1 列、国は 1 列なので、これら 2 行から合計 12 列のデータが作成されます。

答え1

  1. 結合されたコンテンツを配置する空白セルを選択し、数式バーに数式 =CONCATENATE(TRANSPOSE(B2:B19)) を入力します。次に、数式の TRANSPOSE(B2:B19) 部分を選択して、F9 キーを押します。以下のスクリーンショットに示すように、数式が変更されていることがわかります。

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

  1. 数式バーの数式から中括弧を削除し、Enter キーを押します。

答え2

このようなタスクには VBA を使用するのがよいでしょう。サンプル テーブルに示されているように、一意のユーザー ID があると仮定すると、次のような操作を実行できます。

Sub Addresses_To_Columns()

    Dim lastRow As Long
    Dim addressCount As Integer: addressCount = 0

    lastRow = Range("A" & Rows.Count).End(xlUp).Row

    For i = lastRow To 3 Step -1
        If Cells(i, 1).Value = Cells(i - 1, 1).Value Then
            addressCount = addressCount + 1
            Range(Cells(i - 1, 10), Cells(i - 1, (addressCount * 6) + 10)) = Range(Cells(i, 4), Cells(i, (addressCount * 6) + 4)).Value
            Rows(i).Delete
        Else
            addressCount = 0
        End If
    Next i

End Sub

これにより、次のようになります。 ここに画像の説明を入力してください

次のようにします: ここに画像の説明を入力してください

このコードは、特定の形式に合わせてカスタマイズする必要があります。また、これは概念実証として急いで行われたため、一般的なベスト プラクティスがいくつか欠けている可能性があることに注意してください。このコードには重複行を削除する削除コマンドが含まれているため、このコードを試す前にデータをバックアップしてください。

答え3

皆様のご協力とご提案に感謝いたします。しかし、私は別の方法でこの問題を解決しました。

私は単に「-1」、「-2」、「-3」を「一意の」顧客 ID に連結しました。次に、追加する必要のある新しい列ごとに一意の -1、-2、-3 値に対して VLOOKUP を実行しました。

この試練から得られた評判のために、この時間と労力すべてが価値あるものであったことを祈ります。

答え4

あなたの質問を正しく理解していれば、非常に簡単な解決策です。当然ですが、最初にスプレッドシートをバックアップしてください。

1) 必要な数の新しい列を正しい場所に追加します。新しい列を N とします。

2) 顧客 ID は列 A にあり、同じ顧客の新しい住所の行は、その行の列 A に同じ番号があると想定しています。最後に、各顧客の最初の行には、住所以外の最も完全な詳細 (名前、電話番号など) があると想定しています (そうでない場合は、最初に顧客番号で並べ替え、次に名前で並べ替える並べ替えを使用します)。

3) セル N2 に次の数式を入力します。

=if(and(a3=a2,a1<>a2),h3,"")

ここで、H は住所の列です。次の行に同じ顧客番号 (a3=a2) があり、この行が新しい顧客 (つまり、前の行とは異なる顧客番号 (a1<>a2)) である場合、次の行から住所が取得され、その顧客のメイン行に配置されます。その住所の他のすべての部分についても繰り返して、すべての情報が移動されるようにします。つまり、同じ式ですが、N2 ではなく O2 で、h3 ではなく i3 を参照するなどです。

4) 3 番目のアドレスに対しても同様の数式を実行します。つまり、列 X が 3 番目のアドレスの新しい列であると仮定して、X2 に次のように入力します。

=if(and(a4=a2,a1<>a2),h4,"")

基本的には同じですが、代わりに 2 行下から情報を取得します。

5) この時点で、すべての住所が各顧客の「メイン」行に表示されているはずです。次に、すべての住所をコピーし、形式を選択して貼り付け - 値を使用して同じ場所に貼り付ける必要があります。これにより、数式が削除され、入力したのと同じように住所がデータとして表示されます。

6) 次に、顧客名で並べ替えて、名前が空白になっている行 (余分な住所であるため) をすべて 1 か所に集めます。次に、必要なデータがすべて正しい行にあることを確認してから、それらの行をすべて削除します。これで完了です。

関連情報