多くの場合、ユーザーは数式でセルを固定する必要に直面します。 たとえば、数式をコピーしたいが、リンクが元の場所からコピーされたのと同じ数のセルを上下に移動しない場合に発生します。
この場合、Excel でセル参照を修正できます。 そして、これは一度にいくつかの方法で行うことができます。 この目標を達成する方法を詳しく見てみましょう。
Excel リンクとは
シートはセルで構成されています。 それぞれに特定の情報が含まれています。 他のセルは、計算でそれを使用できます。 しかし、彼らはどこからデータを取得するかをどのように理解するのでしょうか? リンクを作成するのに役立ちます。
各リンクは、XNUMX つの文字と XNUMX つの数字を含むセルを指定します。 文字は列を表し、数字は行を表します。
リンクには、絶対、相対、および混合の XNUMX つのタイプがあります。 XNUMX つ目はデフォルトで設定されています。 絶対参照は、列と列の両方の固定アドレスを持つものです。 したがって、混合は、別の列または行のいずれかが固定されているものです。
1つの方法
列アドレスと行アドレスの両方を保存するには、次の手順に従います。
- 数式を含むセルをクリックします。
- 必要なセルの数式バーをクリックします。
- F4を押します。
その結果、セル参照は絶対参照に変更されます。 特徴的なドル記号で認識できます。 たとえば、セル B2 をクリックしてから F4 をクリックすると、リンクは $B$2 のようになります。
セルごとの住所の部分の前にあるドル記号は何を意味しますか?
- 文字の前に配置されている場合は、数式がどこに移動されても、列参照が同じままであることを示します。
- 数字の前にドル記号がある場合は、文字列が固定されていることを示します。
2つの方法
この方法は前の方法とほとんど同じですが、F4 を 2 回押すだけです。 たとえば、セル B2 がある場合、その後は B$XNUMX になります。 簡単に言えば、このようにしてラインを修正することができました。 この場合、列の文字が変わります。
たとえば、一番下のセルに上から XNUMX 番目のセルの内容を表示する必要があるテーブルで非常に便利です。 このような数式を何度も実行する代わりに、行を修正して列を変更するだけで十分です。
3つの方法
これは前の方法とまったく同じですが、F4 キーを XNUMX 回押す必要があるだけです。 次に、列への参照のみが絶対になり、行は固定されたままになります。
4つの方法
セルへの絶対参照があるとしますが、ここでは相対にする必要がありました。 これを行うには、F4 キーを何度も押して、リンクに $ 記号が表示されないようにします。 そうすると相対になり、式を移動したりコピーしたりすると、列アドレスと行アドレスの両方が変化します。
広い範囲のセルの固定
上記の方法は、実行するのにまったく問題がないことがわかります。 しかし、タスクは具体的です。 そして、たとえば、一度に数十の数式があり、その中のリンクを絶対的なものに変える必要がある場合はどうすればよいでしょうか。
残念ながら、標準的な Excel の方法ではこの目標を達成できません。 これを行うには、VBA-Excel という特別なアドオンを使用する必要があります。 これには、Excel で一般的なタスクをより高速に実行できるようにする多くの追加機能が含まれています。
25 を超えるユーザー定義関数と XNUMX の異なるマクロが含まれており、定期的に更新されます。 ほぼすべての側面で作業を改善できます。
- 細胞。
- マクロ
- さまざまなタイプの関数。
- リンクと配列。
特に、このアドインを使用すると、多数の数式のリンクを一度に修正できます。 これを行うには、次の手順を実行する必要があります。
- 範囲を選択します。
- インストール後に表示される VBA-Excel タブを開きます。
- 「数式のロック」オプションがある「関数」メニューを開きます。
6 - 次に、必要なパラメータを指定する必要があるダイアログ ボックスが表示されます。 このアドオンを使用すると、列と列を個別に固定したり、一緒に固定したり、パッケージで既存の固定を削除したりできます。 対応するラジオ ボタンを使用して必要なパラメータを選択したら、[OK] をクリックしてアクションを確認する必要があります。
例
より明確にするために例を挙げてみましょう。 商品の原価、その合計数量、および売上高を表す情報があるとします。 そして、数量とコストに基づいて、損失を差し引くことなく獲得できた金額を自動的に決定するテーブルを作成するというタスクに直面しています。
この例では、式 = を入力する必要があります。B2*C2。 ご覧のとおり、非常に簡単です。 彼女の例を使用して、セルまたはその個々の列または行のアドレスを修正する方法を説明するのは非常に簡単です。
もちろん、この例では、オートフィル マーカーを使用して数式を下にドラッグすることもできますが、この場合、セルは自動的に変更されます。 したがって、セル D3 には別の数式があり、数値はそれぞれ 3 に置き換えられます。さらに、スキーム (D4) によると、数式は = B4 * C4、D5 の形式になります。ただし、 5番など。
それが必要な場合（ほとんどの場合それが判明します）、問題はありません。 ただし、ドラッグしても変化しないように XNUMX つのセルで数式を修正する必要がある場合、これはやや難しくなります。
ドル収入を決定する必要があるとします。 B7セルに入れましょう。 少し懐かしくなって、35 ドルあたり XNUMX ルーブルのコストを示しましょう。 したがって、ドルでの収入を決定するには、ルーブルでの金額をドルの為替レートで割る必要があります。
この例では、次のようになります。
以前のバージョンと同様に、式を処方しようとすると、失敗します。 同様に、数式は適切なものに変更されます。 この例では、次のようになります。 =E3*B8. ここから見ることができます。 数式の最初の部分が E3 に変わったことを確認し、このタスクを設定しますが、数式の 8 番目の部分を B4 に変更する必要はありません。 したがって、参照を絶対参照に変える必要があります。 これは、FXNUMX キーを押さなくても、ドル記号を入力するだけで実行できます。
XNUMX 番目のセルへの参照を絶対参照に変更した後、変更から保護されました。 これで、オートフィル ハンドルを使用して安全にドラッグできます。 式の位置に関係なく、すべての固定データは同じままであり、コミットされていないデータは柔軟に変更されます。 すべてのセルで、この行に記述されているルーブル単位の収益が同じドル為替レートで除算されます。
式自体は次のようになります。
=D2/$B$7
注意！ XNUMX つのドル記号を示しました。 このようにして、列と行の両方を修正する必要があることをプログラムに示します。
マクロでのセル参照
マクロは、アクションを自動化できるサブルーチンです。 Excel の標準機能とは異なり、マクロを使用すると、特定のセルをすぐに設定し、わずか数行のコードで特定のアクションを実行できます。 たとえば、アドオンをインストールする方法がない場合 (たとえば、個人のコンピュータではなく会社のコンピュータが使用されている場合) に、情報のバッチ処理に役立ちます。
まず、マクロの重要な概念は、他のオブジェクトを含むことができるオブジェクトであることを理解する必要があります。 Workbooks オブジェクトは、電子ブック (つまり、ドキュメント) を担当します。 これには、開いているドキュメントのすべてのシートのコレクションである Sheets オブジェクトが含まれます。
したがって、セルは Cells オブジェクトです。 特定のシートのすべてのセルが含まれています。
各オブジェクトは、括弧で囲まれた引数で修飾されます。 セルの場合、この順序で参照されます。 行番号が最初にリストされ、その後に列番号または文字が続きます (両方の形式が受け入れられます)。
たとえば、セル C5 への参照を含むコード行は次のようになります。
ワークブック(“Book2.xlsm”).シート(“リスト2”).セル(5, 3)
ワークブック(“Book2.xlsm”).シート(“リスト2”).セル(5, “C”)
オブジェクトを使用してセルにアクセスすることもできます 几帳面。 一般に、範囲への参照を与えることを目的としています (ちなみに、その要素は絶対または相対にすることもできます) が、Excel ドキュメントと同じ形式で単純にセル名を与えることができます。
この場合、行は次のようになります。
ワークブック(“Book2.xlsm”).シート(“リスト2”).範囲(“C5”)
このオプションの方が便利に思えるかもしれませんが、最初の XNUMX つのオプションの利点は、括弧内の変数を使用して、絶対リンクではなく相対リンクのようなリンクを指定できることです。計算。
したがって、マクロはプログラムで効果的に使用できます。 実際、ここでのセルまたは範囲へのすべての参照は絶対的であるため、それらで修正することもできます。 確かに、それほど便利ではありません。 マクロの使用は、アルゴリズムに多数のステップがある複雑なプログラムを作成する場合に役立ちます。 一般に、絶対参照または相対参照を使用する標準的な方法は、はるかに便利です。
結論
セル参照とは何か、どのように機能するのか、何のためにあるのかを理解しました。 絶対参照と相対参照の違いを理解し、あるタイプを別のタイプに変換するために何をする必要があるかを理解しました (簡単な言葉で言えば、アドレスを修正するか固定を解除します)。 多数の値を使用してすぐに実行できる方法を見つけました。 適切な状況でこの機能を柔軟に使用できるようになりました。