エクセル 関数 固定。 エクセルで数値を固定する方法|わかりやすい動画解説付き

計算式で参照するセルを固定してコピーする方法|Excel(エクセル)の使い方

エクセル 関数 固定

Contents• セルの参照は絶対参照で固定できる セルの参照を絶対参照にすると数式を他のセルに コピーしても参照が絶対にズレなくなり、正しく計算ができるようになります。 また、 絶対参照は行だけを固定する、または列だけを固定するという応用もできます。 (後述致します) VLOOKUPなどのエクセル関数では、 セルの参照をコントロールすることが必要不可欠になりますので、この機会にぜひエクセルの参照を理解しましょう。 スポンサーリンク エクセルの2つのセル参照「相対参照」と「絶対参照」とは? セルの参照には「相対参照」と「絶対参照」があります。 これを「 相対参照」と呼びます。 先ほどとは違う表で説明します。 E2セルに7月から9月の合計をSUM関数で求めました。 数式をE5セルまでコピーすると、それぞれの店舗の合計金額がE列に表示されました。 E列に入力されている数式を確認してみます。 相対参照のおかげで1つ1つのセルにSUM関数を入力する必要はなく、E2セルの数式をコピーするだけで計算ができました。 相対参照はこのような場面では非常に便利。 しかし、冒頭にお話ししたような表の場合、 相対参照では正しく計算ができませんでした。 絶対参照では、数式を他のセルにコピーしても 参照が固定されてズレることがありません。 だから 数式をコピーしても参照がズレないと覚えてくれればOKです。 相対参照と絶対参照を分かりやすく比較してみます。 A1セルに入力した文字をB列に表示しています。 相対参照の場合、B1セルを他のセルにコピーすると当然参照がズレます。 次に絶対参照で同じことをやってみます。 B1セルを他のセルにコピーしてみました。 どのセルにコピーしても、参照先はA1セルになっています。 これが絶対参照。 セルの参照を完全に固定することが可能ですね。 スポンサーリンク セルの参照を固定した数式をコピーする方法 本題に戻ります。 冒頭で例にした表で、 絶対参照を使ってみます。 C2セルを選択して、数式を絶対参照に変更します。 セルの参照を固定したいB6の部分を選択して、 キーボードのF4キーを1度押します。 これだけで、 セルの参照が相対参照から絶対参照へ変更されます。 相対参照を絶対参照に変更したいときはキーボードのF4キーを押す B2セルをB5セルまでコピーします。 相対参照では数式をコピーしてもエラー表示がされていましたが、今回はB6セルが 絶対参照になっているためコピーした数式で問題なく計算できています。 C2セルからC5セルに入力された数式を確認してみます。 割り算の分子となるB2セルはコピーしたセルに応じてB3、B4…と 変化をしていますが、分母となるB6セルはどのセルも同じで 固定されているのが分かると思います。 このように、 相対参照と絶対参照を使い分けることで、数式や関数を使ったときに正確に求めたい結果を計算することができます。 スポンサーリンク セル参照で列のみ・行のみを固定したいときは複合参照 実際の作業の中では、完全にセルを固定したいわけではなく、 列だけ固定したい、行のみを固定したいという場面もありますよね。 列のみ・行のみのセルの参照を固定したいケース レンタル料金と日数の掛け算によってレンタル料金が計算される一覧表を例にします。 数式を相対参照のまま他のセルにコピーしてみます。 どう見ても 正しい計算はできていませんよね。 では、D3セルの参照を絶対参照にして固定してみましょう。 すべてのセルに同じ料金が表示されてしまいました。 セル参照を完全に固定してるから当然ですよね… このように、相対参照でダメだからと言って絶対参照にしても、それだけは上手くいかないこともあります。 この問題で重要なのは、 列または行のみを固定する方法です。 複合参照により、 列のみ、または行のみのセルの参照を固定することができます。 では、複合参照を使ってレンタル料金の一覧表を完成させてみましょう。 すべての箇所に正しく計算結果が表示できました。 参考に、各セルに入力された計算式を載せておきますね。 相対参照で変化しているところと、絶対参照で固定されているセル参照がありますね。 スポンサーリンク まとめ エクセル関数を使いこなすためには、 相対参照、 絶対参照、そして 複合参照の使い分けが必要不可欠です。 最初はどうすれば良いのか判断を迷うこともあると思いますが、ゆっくりと時間をかけて覚えていけば良いです。 大切なのはキーボードのF4キー。 エクセルのセルの参照で困ったときには、この記事を参考に考えてみてくださいね。 スポンサーリンク.

次の

【エクセル】セルの参照を固定(列のみ・行のみ)する方法とは?|ガラクタブログ

エクセル 関数 固定

図のA6からD13番地は、ちょっとした一覧表をに変換したものです。 実は、テーブル内のセルを使って数式を組むと、その数式は、普段とは違う「構造化参照」という形式の数式になります。 「構造化参照」と聞くと、超難しそうに感じますが、構える必要はありません! ちなみに、このページは、構造化参照を細かくではなく、 ざっくり捉えるためのページとなっています。 なぜなら、数式を手入力するときも、マウスで設定するときも、構造化参照の表記はExcelさんが手助けしてくれますし、数式を入力した後も、「こういう意味の数式なんだな」と、ざっくり分かればそれで事足りるからです。 それではその「構造化参照」という形式の数式がどんなものなのか、見ていくことにしましょう! まずはテーブル名を確認! 「構造化参照」うんぬんの前に、まずはA6からD13番地のテーブルに設定してあるを確認してみます。 A6からD13番地のテーブル内のセルならどれでもいいので、どれか1つのセルをすると、 リボンに[テーブルツール]の[デザイン]タブが表示されます。 このタブの[プロパティ]グループにある[テーブル名]で、このテーブルのテーブル名を確認できます。 このテーブルのテーブル名は「テーブル1」であることが分かりました。 ではここで、後の操作のために、この「テーブル1」にを追加します。 「テーブル1」内のセルならどれでもいいので、どれか1つのセルがされていることを確認し、 [テーブルツール]の[デザイン]タブ、[テーブルスタイルのオプション]グルーブの[集計行]にチェックを付けると、 「テーブル1」に集計行を追加することができました! したので、Excelさんは気を利かせて、「実績」の合計を表示してくれています。 スポンサーリンク 構造化参照は、数式の意味が分かりやすい! それでは、この「テーブル1」のデータを使って、様々な数式を設定してみることにしましょう! まず、この「テーブル1」に入力されているスタッフの人数を求めてみます。 人数は、氏名が入力されているセルの個数を数えればいいので、「氏名」列のデータ部分である、A7からA13番地のセルの個数を数えればいいことになります。 そして、「氏名」列に入力されているのは、数字ではなく文字。 そこで、数字が入力されているセルの個数を数えるではなく、文字も含めた、空白以外のセルの個数を数えるを使います。 それでは早速、を設定していきましょう。 スタッフの人数を表示させたいC1番地をし、[関数の挿入]ボタンをクリックします。 関数を指定するためのダイアログボックスが表示されるので、[関数の分類]で「すべて表示」を選択すると、すべての関数が[関数名]欄にABC順で表示されます。 今回は COUNTA関数を使いたいので、一覧から「COUNTA」をクリックで選択し、[OK]ボタンを押します。 ちなみに、この一覧から「COUNTA」を選択する際、「COUNTA」の頭文字である「C」のところまで、を使うと便利です。 前のダイアログボックスで「COUNTA」を選択して[OK]ボタンをクリックすると、このようなダイアログボックスが表示されます。 表示されたダイアログボックスの[値1]欄に、どの範囲のセルの個数を数えてほしいのかを指定します。 今回数えてほしいのは、「テーブル1」の「氏名」列のデータ部分であるA7からA13番地。 普通にこの範囲をして指定してもいいのですが、テーブルには、があるので、それを使って指定していくことにしましょう。 ダイアログボックスの[値1]欄に文字カーソルがあることを確認し、「テーブル1」の「氏名」列にある 見出しの上端にマウスポインタを合わせ、マウスポインタが図のような 黒い矢印に変わったらクリックすると、 「氏名」 列のデータ部分のみを選択できます。 データ部分のみなので、見出しは選択されていません。 のおかげで、範囲選択したかった場所を手っ取り早く選択することができました。 そして、ダイアログボックスの[値1]欄には、普段表示されるはずの「A7:A13」といったセル範囲が表示されるのではなく、ちょっと変わった表示になっています。 これが構造化参照。 角括弧で囲んだ見出しの名前は、「その見出しがある列」を意味するので、[氏名]は「氏名の列」という意味になり、図の意味は「テーブル1の氏名の列」になります。 でご紹介するような表現がくっつかず、ただ[氏名]のようにシンプルに記載してあるものは、「列」の中でも「列のデータ部分」という風にとらえていいので、 今回の例では、A7からA13番地のことになります。 必要な指定が終わったので、ダイアログボックス右下の[OK]ボタンをクリックすると、 COUNTA関数を設定することができました! COUNTA関数を設定したC1番地をして数式バーを見ると、たった今設定した数式の内容を確認することができます。 なんだか見慣れない表現の数式になっていますが、テーブル内のセルを使って数式を組み立てたので、テーブルの構造で表現された数式になっているんです! もし数式で指定した範囲が、テーブルではない普通のセルだったら、このような数式になるはず。 でも、数式で指定した範囲が、テーブル内のセルだったので、テーブルの構造で表現された数式となり、どんな内容の数式なのかがパッと見で分かりやすくなっています! スポンサーリンク 集計行のデータを引っ張るとこう表示される お次はC2番地に、C14番地に集計されている実績の合計額が表示されるようにしてみます。 まずは合計額を表示させたいC2番地をし、C14番地とまったく同じものを表示させたいだけなので、「=(イコール)」を入力後、 C14番地をクリックします。 数式バーの[入力]ボタンか、キーボードの[Enter]キーで数式を確定すると、 C2番地に、C14番地とまったく同じものが表示されるように設定できました! ここで、C2番地をすると、数式バーで先ほどこのセルに入力した数式の内容を確認できます。 C14番地と同じものが表示されるようにしてあるわけですから、いつもなら図のような数式になるはず。 ところが、指定したC14番地は、テーブルの、しかも集計行にあるセルだったので、数式がテーブルの構造で表現されたものになっています! 前の章で確認していただいたとおり、角括弧で囲んだ見出しの名前は、「その見出しがある列」を意味するので、[実績]は「実績の列」ということになります。 そして、その前に「 (シャープ)」が付いた表現がありますが、これは更に場所を限定する表現。 [ 集計]で、「集計行」を意味しますので、上の図の意味は、「テーブル1の「実績」の列にある集計と同じものを表示してね」という意味になります。 ちなみに[ 集計]は、「集計行」を意味しますが、[ 見出し]だと「見出し行」を意味します。 スポンサーリンク 「@」の意味 今度はB7番地に、A7番地のフリガナが表示されるようにしてみます。 フリガナを表示させるのはですから、B7番地にPHONETIC関数を設定していきます。 フリガナを表示させたいB7番地をし、[関数の挿入]ボタンをクリックします。 関数を指定するためのダイアログボックスが表示されるので、[関数の分類]で「すべて表示」を選択すると、すべての関数が[関数名]欄にABC順で表示されます。 今回は PHONETIC関数を使いたいので、一覧から「PHONETIC」をクリックで選択し、[OK]ボタンを押します。 ちなみに、この一覧から「PHONETIC」を選択する際、「PHONETIC」の頭文字である「P」のところまで、を使うと便利です。 前のダイアログボックスで「PHONETIC」を選択して[OK]ボタンをクリックすると、このようなダイアログボックスが表示されます。 [参照]欄には、どのセルのフリガナを表示させたいのかを設定します。 今回はA7番地のフリガナを表示させたいわけですから、[参照]欄に文字カーソルがある状態で、 A7番地をクリックすると、指定したA7番地は、テーブル内のセルだったので、「A7」というセル番地ではなく、テーブルの構造で表現されます。 「@」は、「この行の」という意味。 「この数式が入力されているのと同じ行にある」ということを意味します。 というわけで、[@氏名]は、「この数式が入力されているのと同じ行にある「氏名」」、つまり「この行の「氏名」欄」という意味になります。 ダイアログボックス右下の[OK]ボタンをクリックすると、 数式が入力されると同時に、残りのセルにも自動的に同様の数式が設定されました! 普段なら、1つのセルに数式を入力した後、残りのセルにはする必要がありますが、 テーブル内に数式を設定した場合には、1つのセルに数式を入力するだけで、残りのセルにも自動的に数式を補充してくれます。 ここで、最初に数式を設定したB7番地をした状態で数式バーを見て、B7番地に設定した数式を確認してみましょう。 そして、同じ列にある、自動的に数式が補充された他のセルも同じようにして、そのセルに設定された数式を、数式バーで確認してみます。 そうです、この列のセルにはまったく同じ数式が入っているんです! もし、テーブルになっていない表で今回の数式を設定したなら、B7番地には図のような「A7番地のフリガナを表示してね」という数式が、 B11番地には図のような「A11番地のフリガナを表示してね」という数式が入ります。 でも、構造化参照の数式は、そもそもの数式の意味が「この数式が入力されているのと同じ行にある「氏名」のフリガナを表示してね」という意味になるので、同じ数式でもうまく動くわけです。 スポンサーリンク 構成要素の名前で数式が組まれているということは 今度は、これまたテーブル内にあるD7番地に、星野さんの実績が、全体の実績の中で何位なのかを表示させてみます。 順位を表示させるのはですから、順位を表示させたいD7番地をし、[関数の挿入]ボタンをクリックします。 RANK関数を選択して[OK]ボタンをクリックすると、 このようなダイアログボックスが表示されます。 の設定は、「この数字は、この中で、大きい方から数えて(何位ですか)」という設定になりますから、 今回の設定では、「星野さんの実績は、全体の実績の中で、大きい方から数えて(何位ですか)」となるように設定していきます。 まずは「星野さんの実績は、全体の実績の中で、大きい方から数えて何位ですか」の、「星野さんの実績は」の部分から。 星野さんの実績はC7番地ですから、[数値]欄に文字カーソルがある状態で、 C7番地をクリックすると、[@実績]と表示されました。 「@」は、「この数式が入力されているのと同じ行にある」という意味ですから、[@実績]は、「この数式が入力されているのと同じ行にある「実績」」ということになり、この行は星野さんに関する行ですから、必然的に星野さんの実績のことを意味することになります。 1つ目の設定が終わったので、次の欄に文字カーソルを移します。 [Tab]キーを押すと、 文字カーソルが移りました。 ここでは「星野さんの実績は、全体の実績の中で、大きい方から数えて何位ですか」の、「全体の実績の中で」の部分を設定します。 全体の実績はC7からC13番地にある実績データすべてのことなので、普通にこの範囲をして指定してもいいのですが、テーブルには、があるので、それを使って指定していくことにしましょう。 「実績」欄の 見出しの上端にマウスポインタを合わせ、マウスポインタが図のような 黒い矢印に変わったらクリックすると、 「実績」 列のデータ部分のみを選択できます。 データ部分のみなので、見出しは選択されていません。 のおかげで、範囲選択したかった場所を手っ取り早く選択することができました。 そしてダイアログボックスには、[実績]と表示されました。 [実績]は「実績の列」のこと。 [実績]のようにシンプルに記載してあるものは、「列」の中でも「列のデータがある部分」という風にとらえていいので、見出しを除いたC7からC13番地にある実績のデータすべてのことになるわけです。 今回の順位は実績が大きいものから1位、2位・・・としていきたいので、最後のし、ダイアログボックス右下の[OK]ボタンをクリックします。 数式が入力されると同時に、残りのセルにも自動的に同様の数式が設定されました! 普段なら、1つのセルに数式を入力した後、残りのセルにはする必要がありますが、 テーブル内に数式を設定した場合には、1つのセルに数式を入力するだけで、残りのセルにも自動的に数式を補充してくれます。 ここで、最初に数式を設定したD7番地をした状態で、数式バーで数式を確認してみます。 そして、同じ列にある、自動的に数式が補充された他のセルも、同じようにして、数式バーで数式を確認してみます。 やはり、この列にはまったく同じ数式が入っていることが分かります! 構造化参照で設定されたこの数式は、そもそもの意味が「この数式が入力されているのと同じ行にある「実績」が、全体の実績の中で、大きい方から数えて何位ですか」という意味になるので、「順位」欄のどのセルでも同じ数式でうまく動くわけです。 さてここで、表の構造の名前を使った数式になっているということの利点をもう1つ見ておきます。 もし、この順位付けした表がテーブルではなかった場合、 D7番地に入る数式 C7番地の数字が、 C7からC13番地の中で、大きい方から数えて何位ですか D8番地に入る数式 C8番地の数字が、 C7からC13番地の中で、大きい方から数えて何位ですか D9番地に入る数式 C9番地の数字が、 C7からC13番地の中で、大きい方から数えて何位ですか となり、どのセルに入る数式もC7からC13番地にある全体の実績の中で順位付けされるよう、。 ですが、今回組んだ数式では全体の範囲を指定する箇所に[実績]と指定してあります。 [実績]は必然的にC7からC13番地のことを指すので、このように 構造化参照で指定されている場合は、絶対参照を設定する必要がありません。 これも構造化参照の利点の1つです。 スポンサーリンク データを追加しても さて、ここでデータを追加してみましょう。 テーブルの一番右下にある直角マークにマウスポインタを合わせ、 マウスポインタが両矢印の形になったら下にドラッグして、 二人分のデータが入れられる行を作ります。 追加した行の順位欄にエラーが表示されているので、どうなっているのか確認してみましょう。 エラーが出ているセルをして数式バーを見ると、行を追加しただけなのに、 既に数式が補充されていることが分かります。 テーブルでは、こういうこともExcelさんが気を利かせて自動でやってくれるわけです。 そして、自動で数式が補充されたけれど、その数式で順位を付けようにも、まだ実績が入力されていないので、エラーが出てしまっているというわけです。 また、追加した行の「フリガナ」のセルをして数式バーを見ても、やはり、行を追加しただけなのに、 既に数式が補充されていることが分かります。 行を追加した時点で既に数式が補充されているわけですから、適当なデータを入力すると、フリガナも順位もちゃんと表示されます! 更に、もし、この順位付けした表がテーブルではなかった場合、「C7番地の数字が、 C7からC13番地の中で、大きい方から数えて何位ですか」といった数式になるため、 行を追加したなら、「C7番地の数字が、 C7からC15番地の中で、大きい方から数えて何位ですか」という風に数式を修正してあげなければなりません。 ですが、今回組んだ数式では全体の範囲を指定する箇所に[実績]と指定してあるため、 [実績]欄が広くなろうが狭くなろうが[実績]の範囲を常に認識してくれるので数式を修正する必要がありません。 スポンサーリンク テーブルになる前の数式はそのまま 今回ご紹介した構造化参照は、既にテーブルの状態となっているセルを数式で使う場合に現れます。 ですので、する前に数式を設定し、 その後でテーブルに変換しても、数式は以前のまま、構造化参照になるわけではありません。 構造化参照 まとめ ここまでで、「構造化参照」という形式の数式がどんなものなのかをご紹介しました。 数式の意味が分かりやすい• 1つのセルに数式を入力するだけで、残りのセルにも自動的に数式が補充される• 絶対参照を設定する必要が無い• データを追加しても、それを含めて改めてテーブル範囲として認識してくれるため、データを追加する度に数式を手直しする必要が無い 今回は、構造化参照をざっくり捉えるためのご紹介をしましたが、もっと深く知りたい!という方は、Excelを作っているMicrosoft社の「」をご覧ください。 は、かなり使いでのある機能なので、それに合わせて構造化参照の数式を目にする機会も多くなると思います。 そんな時に、今回ご紹介した特徴が頭の隅っこにあると、テーブルがどんどん身近な機能になっていきます!.

次の

財務関数(PMT,PV,FV,RATE,NPER関数)の使い方:Excel関数

エクセル 関数 固定

スポンサーリンク 図のような表のB4番地には、「手数料」を計算するための数式が入力されています。 コピー元であるB4番地をすると、そのセルが太い枠で囲まれ、その右下にポッチが付いています。 このポッチにマウスポインタを合わせると、マウスポインタが 黒い十字の形になるので、下に向かってドラッグします。 の操作です。 数式をコピーできました!と言いたいところですが、明らかにおかしな状態になっています! なぜ「おかしな状況」なのか、じっちゃんの名にかけて、原因を解明していくことにしましょう。 スポンサーリンク コピーで数式がおかしくなる原因 原因を解明したい時には、おかしなことになっている箇所の、現状を把握することがとっても大切! そこで、コピー先であるB5番地をした状態で数式バーを見ると、このセルには、 = A5 * B2 という数式が入っていることが分かります。 更にその下のコピー先も見てみましょう。 B6番地をした状態で数式バーを見ると、このセルには、 = A6 * B3 という数式が入っていることが分かります。 現在の「手数料」欄に入っている数式をまとめると、図の左側「現在の数式」のような状態になっています。 なので、このコピー元のセルと、見ただけで明らかにおかしいコピー先の数式を、すべて消していくことにしましょう。 B4からB7番地をした状態で、[Delete]キーを押すと、 ! それでは気分を改めて。 コピー元となる数式を入力するところから始めていきましょう! 数式を入力したいB4番地をし、 数式を入れる合図の「=」(イコール)を入力します。 そしてこの、「購入額」にあたるA4番地は、 図の「正しい数式」のようにA4、A5、A6、A7、A8と、数式をコピーした時にズレて構わないセルなので、特に何もせず、いつも通り先に進みます。 この、「手数料率」にあたるB1番地は、 数式をコピーした時にズレては困るセルなので、ここでズレないようにする技をほどこします! キーボードの [F4]キーをポンッと押すと、数式内の「B1」の部分に ドルマークが付きました! これで、数式をコピーしてもズレないようにできたわけなんですが、これを良く見ると、「B1」の「B」の前と、「1」の前に、それぞれドルマークが1つずつ付いていることが分かります。 (この時、ドルマークが「B」の前と、「1」の前に1つずつではなく、片方だけにしか付いていない、またはどちらにも付いていないという方は、[F4]キーを押すときに緊張してしまってキーを長押ししてしまい、[F4]キーを何回も押してしまったのと同じ状態になっています。 そうなってしまっても、そのまま先に読み進めれば対処できますので、慌てることなく、そのまま読み進めてください。 ) ここでもう一度 [F4]キーをポンッと押すと、今度は「B」の前にあったドルマークが消え、「1」の前にだけ付きました。 更にもう一度 [F4]キーを押すと、今度は「B」の前にだけドルマークが付き、「1」の前のドルマークが消えました。 更にもう一度 [F4]キーを押すと、「B」の前からも、「1」の前からもドルマークが消え、一番最初と同じ状態に戻りました。 このように、 [F4]キーを押すたびに、ドルマークの付く位置が変わっていくのですが、数式をコピーした時にズレないようにする通常パターンは、「B」の前にも、「1」の前にも、 両方にドルマークが付いている状態なので、 もう一度 [F4]キーを押して、「B」の前にも、「1」の前にも、両方にドルマークが付いている状態にします。 数式はこれでいいので、最後に[Enter]キーを押すか、数式バーの[入力]ボタンをクリックして、数式を確定します。 早速、してみましょう! 数式を入力したB4番地をすると、そのセルが太い枠で囲まれ、その太い枠の右下にポッチが表示されます。 「B1」はズレないように設定してあったので、コピー先でもドルマークが付いているわけですね。 私の経験では、Excelで数式をコピーする際、今回のように縦方向にのみオートフィルするか、もしくは横方向にのみオートフィルするか、といった、どちらかの方向にのみオートフィルするケースが大半を占めます。 特に初心者の方は、「両方にドルマークが付いていればズレない」という風に、単純に覚えていただいてOKです。 ところが、だんだんExcelを使い慣れてくると、1つの数式を、縦方向にも、 横方向にもコピーしたいことが出てきます。 そういった、縦方向にも横方向にも同じ数式をコピーしたい時に出てくるのが、片方にだけドルマークを付けるパターンです。 これは「複合参照」というものなのですが、Excel初心者の方が「複合参照」に手を付けるのは早いと、私は思っています。 (あくまでも私本人の個人的な見解です。 ) というわけで、まずは今回ご紹介した、 [F4]キーを一回ポンッと押して、両方にドルマークが付いている状態にすることで、数式をコピーしてもズレないようにできる「 絶対参照」を徹底的に覚えてください。 「絶対参照」という名前は覚えなくていいので、「 [F4]キーでドルマークを付け、ズレないようにする」ということは、確実に覚えていきましょう!.

次の