したがって、次のような要件があります。
シート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 Arrow
、Down 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 分程度で終わると思います。そして、そうでなくても... 読んでみるとどう見えるかはともかく、実際には速いです。
終わり。