パターンをコピーして貼り付ける適切な Excel 数式または VBA コードは何ですか?

パターンをコピーして貼り付ける適切な Excel 数式または VBA コードは何ですか?

したがって、次のような要件があります。

シート1

xxxx
YYYY
ZZZZ

シート2

1994 xxx
1995 xxx 
1996 xxx
1994 YYY
1995 YYY 
1996 YYY
1994 ZZZ
1995 ZZZ 
1996 ZZZ

基本的に私がやりたいのは、シート 1 から会社名をコピーし、各年に対して貼り付け、約 600 社についてこれを繰り返すことです。

これを実現するための数式やVBAコードはありますか?どんな助けでもいただければ幸いです

答え1

企業のリストを選択し、[データ] > [データの取得と変換] > [テーブル/範囲から] を使用します。

これにより、Power Query エディターが開きます。次のような画面が表示されます。

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

ホーム>閉じる&読み込む

ここで、開始年から終了年までの間の年を動的にリスト化してみましょう。これは、後で開始年と終了年を変更する場合に便利です。

ワークブックの別々のセルに開始年と終了年を入力します。

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

開始年を選択し、名前ボックスに start_year などの名前を入力してセルに名前を付けます。

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

セルに名前を付けたら、セルを選択し、「データ」>「データの取得と変換」>「テーブル/範囲から」を使用します。

Power Query エディターが開き、列ヘッダーが Column1 の単一のセルが表示されたら、そのセルを右クリックして [ドリルダウン] を選択します。

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

左側のクエリ ペインを展開すると、次の画面が表示されます。

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

[ホーム] > [閉じて読み込む] を使用し、[接続の作成のみ] を選択します。

これを年末まで繰り返すと、次のようになります。

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

もう一度、[閉じてロード] をクリックし、接続の作成のみを実行します。

次に、Excel で [データ] > [クエリと接続] をクリックします。次のように表示されます。

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

私の場合は、50 の会社名を含むダミー データを作成したため、「50 行がロードされました」と表示されます。お客様の場合は異なる可能性があります。

Table1 クエリを右クリックし、[編集] を選択します。これにより、Power Query エディターが再度開きます。

次に、「ホーム」>「詳細エディター」に移動します。次のようになります。

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

年のリストを作成するためのコードを追加します。

次の行を 2 行目に追加します。

years = List.Numbers(start_year,end_year),

このような:

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

次に、「完了」をクリックします。何も変更されていないように見えますが、心配しないでください。

[列の追加] > [カスタム列] を使用して、次のように構成します。

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

[OK] をクリックすると、次のように表示されます。

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

年列の上部にある二重矢印をクリックし、「新しい行に展開」をクリックします。

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

会社と年の組み合わせごとに行があることがわかります。

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

これをワークブックに戻すには、[ホーム] > [閉じて読み込む] を使用します。

企業のリストや開始年および/または終了年が変更された場合は、ソース データを変更してこのクエリを更新するだけです。

答え2

それはとても簡単なことです。方法はたくさんありますが、以下は今説明した内容に当てはまる最も簡単な方法です。

会社名がいくつあるか合計します。(会社Ctrl-Down Arrow名の列を押して、最後の行が何行かを確認します。そのうちの 1 つを含む最初の行を使用して、存在する数を確認します。この回答では、587 であるとします。)

もう一方のシートで、列を選択して最初の年を入力し、それを下にコピーして合計 587 個のセルを埋めます。A1 から始めて A587 で終わるとします。セル A588 に数式 を入力します=A1+1。それを下にコピーして、別の 587 個のセルを埋めます。セルをコピーし、それと残りの 587 個のセル (A588:A1174) を強調表示して貼り付けます。これで、587 個のセルすべてが強調表示され、数式が入力されました。そのブロックをコピーして を押しCtrl-Down ArrowDown Arrowもう一度 を押して次の空のセル (A1175) に到達します。

もう一度貼り付けて、次の空のセルに移動します。リストに含めたいすべての年が 587 個のセル ブロックになるまで続けます。エントリの列全体を強調表示します (おそらく 30 年なので、セル A1:A17610 を強調表示します)。すべてをコピーして、Paste|Special|Valuesすべてを永続的なテキストに変更します。

587 の会社名をコピーして、次の列 B1:B587 に貼り付けます。次に、次の空のセル B588 に移動し、数式 を入力して=B1、セルをコピーします。列 A に移動して、一番最後の入力済みセルに移動する、または を使用してF5 GoToセル B17610 に移動します (年が入力されている最後のセルは A17610 で、いずれにしても列 B に移動する必要があります)。方法に関係なく、B17610 に移動します。キーを押してShift-Up Arrow、すべての空のセル (それ自体と、移動先の A588 まで) をカバーします。A588 に貼り付けても問題ありません。数式は同じです)。

ハイライトしたら、貼り付けます。次に、列 A と同じように列 B のセルをすべてハイライトし、コピーして、Paste|Special|Valuesそれらもすべて永続的なテキストにします。

1 時間とかなり面倒な作業のように見えます。実際、多少手こずっても、最大 2 分程度で終わると思います。そして、そうでなくても... 読んでみるとどう見えるかはともかく、実際には速いです。

終わり。

関連情報