Microsoft Excel を使用する場合、多くの場合、ユーザーは結果として表に含まれる情報がどれだけになるかを事前に知りません。 したがって、すべての状況でどの範囲をカバーする必要があるかを理解しているわけではありません。 結局、セルのセットは変数の概念です。 この問題を解決するには、範囲生成を自動化し、ユーザーが入力したデータ量のみに基づくようにする必要があります。
Excel でセル範囲を自動的に変更する
Excel の自動範囲の利点は、数式の使用がはるかに簡単になることです。 さらに、多くの関数を含む多数の式を含む複雑なデータの分析を大幅に簡素化することができます。 この範囲に名前を付けると、含まれるデータに応じて自動的に更新されます。
Excelで自動範囲変更を行う方法
あなたが何らかの対象に投資する必要がある投資家であるとします。 その結果、このプロジェクトのためにお金が働いている間、あなたが合計でいくら稼ぐことができるかについての情報を取得したいと考えています。 それでも、この情報を取得するには、このオブジェクトがもたらす総利益を定期的に監視する必要があります。 このスクリーンショットと同じレポートを作成します。
一見すると、解決策は明らかです。必要なのは、列全体を合計することだけです。 エントリが表示される場合、金額は個別に更新されます。 しかし、この方法には多くの欠点があります。
- このように問題が解決された場合、列 B に含まれるセルを他の目的に使用することはできなくなります。
- このようなテーブルは大量の RAM を消費するため、弱いコンピューターではドキュメントを使用できなくなります。
したがって、動的な名前を使用してこの問題を解決する必要があります。 それらを作成するには、次の一連のアクションを実行する必要があります。
- メインメニューにある「数式」タブに移動します。 クリックする必要がある「名前を割り当てる」ボタンがある「定義された名前」セクションがあります。
- 次に、スクリーンショットに示すように、フィールドに入力する必要があるダイアログ ボックスが表示されます。 関数を適用する必要があることに注意することが重要です = 変位 機能と一緒に チェック自動更新範囲を作成します。
- その後、関数を使用する必要があります 和、引数としてダイナミック レンジを使用します。
これらの手順を完了すると、「収入」の範囲に属するセルのカバレッジが、そこに新しい要素を追加するときにどのように更新されるかを確認できます。
Excel の OFFSET 関数
以前に「範囲」フィールドに記録した関数を見てみましょう。 機能の使用 廃棄 列 B のセルの数が指定されている場合、範囲の量を決定できます。 関数の引数は次のとおりです。
- セルを開始します。 この引数を使用すると、ユーザーは範囲内のどのセルを左上と見なすかを指定できます。 右下にレポートします。
- 行単位の範囲オフセット。 この範囲を使用して、範囲の左上のセルからのオフセットが発生するセルの数を設定します。 正の値だけでなく、ゼロとマイナスも使用できます。 この場合、変位はまったく発生しないか、逆方向に実行されます。
- 列ごとの範囲オフセット。 このパラメータは前のものと似ていますが、範囲の水平シフトの度合いを設定できるだけです。 ここでは、ゼロと負の値の両方を使用することもできます。
- 高さの範囲の量。 実際、この議論のタイトルは、それが何を意味するのかを明確にしています。 これは、範囲が増加するセルの数です。
- 幅の範囲の値。 引数は前のものと似ていますが、列に関係するだけです。
必要がなければ、最後の XNUMX つの引数を指定する必要はありません。 この場合、範囲値は XNUMX つのセルのみになります。 たとえば、式を指定すると =オフセット(A1;0;0)、この式は最初の引数のセルと同じセルを参照します。 垂直方向のオフセットが 2 単位に設定されている場合、この場合、セルはセル A3 を参照します。 それでは、関数の意味を詳しく説明しましょう チェック.
ExcelのCOUNT関数
関数の使用 チェック 列 B に合計で入力したセルの数を決定します。 つまり、XNUMX つの関数を使用して、範囲内のセルの数を決定し、受信した情報に基づいて範囲のサイズを決定します。 したがって、最終的な式は次のようになります。 =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)
この式の原理を正しく理解する方法を見てみましょう。 最初の引数は、ダイナミック レンジの開始点を指します。 この場合、これはセル B2 です。 その他のパラメータの座標はゼロです。 これは、左上のセルに対するオフセットが必要ないことを示唆しています。 記入しているのは範囲の垂直方向のサイズだけで、関数を チェック、データを含むセルの数を決定します。 入力した XNUMX 番目のパラメーターは単位です。 したがって、範囲の合計幅が XNUMX 列であることを示します。
したがって、関数を使用して チェック ユーザーは、いくつかの値を含むセルのみをロードすることで、メモリを可能な限り効率的に使用できます。 したがって、スプレッドシートが動作するコンピューターのパフォーマンスの低下に関連する作業に追加のエラーが発生することはありません。
したがって、列の数に応じて範囲のサイズを決定するには、同様の一連のアクションを実行する必要があります。この場合のみ、XNUMX 番目のパラメーターで単位を指定し、XNUMX 番目のパラメーターで式を指定する必要があります。 チェック.
Excel の数式の助けを借りて、数学的な計算を自動化できるだけではないことがわかります。 これは大海の一滴にすぎませんが、実際には、頭に浮かぶほぼすべての操作を自動化できます。
Excel の動的グラフ
そのため、最後のステップで、ダイナミック レンジを作成することができました。ダイナミック レンジのサイズは、含まれる塗りつぶされたセルの数に完全に依存します。 これで、このデータに基づいて、ユーザーが変更を加えたり列や行を追加したりするとすぐに自動的に変化する動的グラフを作成できます。 この場合の一連のアクションは次のとおりです。
- 範囲を選択した後、「グループ化されたヒストグラム」タイプのチャートを挿入します。 この項目は、「チャート – ヒストグラム」セクションの「挿入」セクションにあります。
- ヒストグラムのランダムな列をマウスで左クリックすると、関数 =SERIES() が関数行に表示されます。 スクリーンショットでは、詳細な式を確認できます。
- その後、数式にいくつかの変更を加える必要があります。 「Sheet1!」の後の範囲を置き換える必要があります。 範囲の名前に。 これにより、次の関数が生成されます。 =ROW(Sheet1!$B$1;;Sheet1!収入;1)
- グラフが自動的に更新されるかどうかを確認するために、レポートに新しいレコードを追加する必要があります。
ダイアグラムを見てみましょう。
その方法をまとめましょう。 前の手順では、動的範囲を作成しました。そのサイズは、含まれる要素の数によって異なります。 これを行うために、関数の組み合わせを使用しました チェック и 廃棄. この範囲に名前を付け、この名前への参照をヒストグラムの範囲として使用しました。 最初の段階でどの特定の範囲をデータ ソースとして選択するかは、それほど重要ではありません。 主なことは、後で範囲の名前に置き換えることです。 このようにして、多くの RAM を節約できます。
名前付き範囲とその用途
名前付き範囲を正しく作成し、それらを使用して Excel ユーザー用に設定されたタスクを実行する方法について詳しく説明しましょう。
デフォルトでは、時間を節約するために通常のセル アドレスを使用します。 これは、範囲を XNUMX 回以上書き込む必要がある場合に便利です。 常に使用する必要がある場合、または適応性が必要な場合は、名前付き範囲を使用する必要があります。 式の作成がはるかに簡単になり、ユーザーが多数の関数を含む複雑な式を分析することはそれほど難しくありません。 ダイナミック レンジの作成に必要な手順をいくつか説明しましょう。
すべては、セルに名前を付けることから始まります。 これを行うには、単にそれを選択し、必要な名前をその名前のフィールドに書き込みます。 覚えやすいことが重要です。 命名時に考慮すべきいくつかの制限があります。
- 最大長は 255 文字です。 これは、あなたの心が望む名前を割り当てるのに十分です。
- 名前にスペースを含めることはできません。 したがって、複数の単語が含まれている場合は、アンダースコア文字を使用してそれらを区切ることができます。
後でこのファイルの他のシートでこの値を表示したり、さらに計算を実行するために適用したりする必要がある場合、最初のシートに切り替える必要はありません。 この範囲セルの名前を書き留めることができます。
次のステップは、名前付き範囲を作成することです。 手順は基本的に同じです。 最初に範囲を選択してから、その名前を指定する必要があります。 その後、この名前は Excel の他のすべてのデータ操作で使用できます。 たとえば、名前付き範囲は、値の合計を定義するためによく使用されます。
さらに、[名前の設定] ツールを使用して [数式] タブを使用して名前付き範囲を作成することもできます。 選択すると、範囲の名前を選択する必要があるウィンドウが表示され、範囲を手動で拡張する領域を指定します。 この範囲が動作する場所 (XNUMX つのシート内またはブック全体) を指定することもできます。
名前範囲がすでに作成されている場合、それを使用するために、名前マネージャーと呼ばれる特別なサービスがあります。 新しい名前を編集または追加するだけでなく、不要になった名前を削除することもできます。
数式で名前付き範囲を使用している場合、それを削除した後、数式は正しい値で自動的に上書きされないことに注意してください。 そのため、エラーが発生する場合があります。 したがって、名前付き範囲を削除する前に、それがどの数式でも使用されていないことを確認する必要があります。
名前付き範囲を作成する別の方法は、テーブルから取得することです。 これを行うには、「選択範囲から作成」という特別なツールがあります。 私たちが理解しているように、それを使用するには、まず編集する範囲を選択してから、見出しがある場所を設定する必要があります。 その結果、このデータに基づいて、Excel がすべてのデータを自動的に処理し、タイトルが自動的に割り当てられます。
タイトルに複数の単語が含まれている場合、Excel はそれらをアンダースコアで自動的に区切ります。
したがって、動的な名前付き範囲を作成する方法と、それらを使用して大量のデータを処理する作業を自動化する方法を理解しました。 ご覧のとおり、いくつかの機能と機能に組み込まれたプログラム ツールを使用するだけで十分です。 一見、初心者にはそう見えるかもしれませんが、複雑なことは何もありません。