☰

リンクされたシートの順序を失うことなく、間に空の行がある場合に数式をコピーするにはどうすればよいですか?

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2013 microsoft-excel-2016
リンクされたシートの順序を失うことなく、間に空の行がある場合に数式をコピーするにはどうすればよいですか?

2 つのシートがある Excel ファイルがあります。

  • まとめ
  • 生データ

私のサマリーシートには、次のデータ行ごとに数行のギャップがあるようなデータがあります。

サンプルデータのスクリーンショット

ここで問題となるのは、1000 行を超えるシートがあり、その中の何かを変更したい場合、RawData シートの参照が継続されず、Summary シートの参照としてセル番号が取得されるため、各セル参照を手動で編集する必要があることです。

添付のサンプル画像では、数式をコピーして貼り付けると、行の間隔に応じてセル番号が 2、5、8、11 などになりますが、現在のシートの行番号に関係なく、B2、B3、B4、B5 の順序のように手動数式列に示されているように数式をコピーする必要があります。いろいろ試しましたが、まったく解決策が見つかりません。何か助けてもらえませんか?

混乱を避けるために、RawData タブのスクリーンショットも含め、シートからより現実的な例を作成します。

生データシート:- 生データシート

これは、RawDataシートからデータをリンクしているリンクシートです。 製品概要シート

ご覧のとおり、数式をコピーして貼り付けると、テスト製品 1、テスト製品 6、テスト製品 11 が表示されますが、これはテスト製品 1、2、3 である必要があります。

サンプルのExcelファイルのリンクはこちらですhttps://we.tl/t-3F3mJra7pe

答え1

まずこの問題の背後にある理由を説明しましょう。

  • の上概要シートセルでは、F3数式は=Rawdata!A2値を返しますTest Product 1。
  • コピーするとすぐに概要シートB3:F7のデータは B8Excelで読み取ります6 行との間は、 Cell ofからF3:F8返されます 。Test Product 7B8生データシート。

それは練習全体を台無しにしていました。

ソリューション：

  • 必要なのはヘルパー列で生データシート。

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

  • B3セルを書き換えるまとめシート付き シンプルな製品 1。
  • この数式をセルに入力してF3くださいまとめシート。

=VLOOKUP(B3,RawData!A$2:E$12,2,FALSE)

わかりますよね。

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

  • 今、コピー範囲B3:F7とペーストセル上B8。

わかりますよね。

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

  • B8セルを書き換えるシンプルプロダクト2、これで正しいシーケンスが得られます。

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

  • 繰り返しますコピーペースト修正してシンプルな製品のシーケンスを実行すると、望ましい結果が得られます。

必要に応じて、数式内のセル参照を調整できます。

答え2

このようなシナリオでは、よりシンプルかつ堅牢なソリューションは OFFSET() 関数を使用することだと思います。Offset は、単一のセルまたはセル範囲への参照を返します。シナリオを考慮すると、最初の数式のコピー & 貼り付けが RawData Product1 を参照し、2 番目のコピー & 貼り付けが Product2 を参照する場合、これらのコピー & 貼り付けでスキップされる行数に関係なく、この OFFSET を以下のように実装します。

A B               C      D        E       F
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B1);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B6);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B11);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B16);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large

数式「=OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B1);0)」をコピーすると、「Summary!$B$1:B1」への参照が、望ましくない同じ動作で自動的に変更されることに注意してください。ただし、その参照が COUNTA() 数式内で使用されると、トリックが実行され、何千もの参照を自分で変更する必要がなくなります。これを行うと、COUNTA() は列の開始以降に「Simple Product」が何回出現したかをカウントし、この内部オフセットを使用して、順序位置でそれぞれの製品を参照します。これはすべて、補助列を作成する必要なしに行われます。結果は次のようになります。

A B               C      D        E       F
  Simple Product  FALSE  TRUE             TestProduct1
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             TestProduct2
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             TestProduct3
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             TestProduct4
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large

関連情報