これぞ晴天のヘキレキ

エクセルのセル関数をズレずに固定させる方法を教えて!

      2017/02/07

エクセルのセル関数をズレずに固定させる方法を教えて!

エクセルを使っていると関数式での疑問が色々出て来ます。

その中でもセルがズレてしまって分からなくなった!という人の声が多数ある事が分かりました。

セルを固定させる方法をその道のベテランの方達に詳しく解説してもらいましょう!

スポンサーリンク


  こんな記事もよく読まれています

ウインナーをレンジ調理で時間短縮!しかし注意点も!!

ウインナーを朝食やお弁当に使われている方も多いと思います。 ...

ウィンドウズの起動時のパスワードについて教えて

ウィンドウズを起動する時にパスワード入力を要求されますが、毎...

インフルエンザに感染しても発症しないって!?


インフルエンザに感染しても、発症しない人はいます...

インフルエンザに感染したかも?発症させない方法ある?

家族がインフルエンザを発症したら自分も感染し、発症するのでは...

インフルエンザにかかっても、発熱が37度という場合


インフルエンザにかかると、身体がかなりしんどいも...

インフルエンザなのに出勤するのは迷惑ですか?


満員電車で隣の人がゲホゲホ咳をしているのを見ると...

インフルエンザで高熱!40度出たらどうする?

風邪で高熱が出る事はありますが、40度の熱が出ると慌ててしま...

インフルエンザb型と検査で判明!いつからうつる?

今年も流行したインフルエンザ

イン...

インテリアの雑誌に投稿をしたいけれど

インテリアが大好きな人は、自分のお気に入りのものを雑誌に投稿...

インターネットと電話とtvをもっと快適に!契約の疑問とコツ


初心者にも分かる!インターネットや電話とtvを契...

インスタのコメントを見られるのが嫌!そんな時の対処法


お洒落な写真が投稿出来る大人気アプリのインスタグ...

no image
エアコンのコンセントを抜くと節約できるって本当?


毎月の電気代を節約したい!

エアコンのコンセントが遠い場合どうする?

エアコンを付ける時にはコンセントが必要です。
...

え!?大学の体育が必修ってどういう事ですか・・?


あれ?うちの大学って体育が必修なんだ・・入学して...

no image
ウナギ釣りのシーズンと仕掛けについて


ウナギといえば、土用の丑の日!
<...

うちの赤ちゃん指が長い!これって普通?大丈夫?


初めての赤ちゃんはとっても可愛くしょうがない! ...

no image
ウサギを猫から守ろう!散歩で気を付ける事とは?

犬、猫に続きペットとして飼いたい動物で人気なのがウサギちゃん...

no image
ウォークマンの曲でcdを作成したい!


ウォークマンの曲をPCと共有する時は、同社のソニ...

ウォーキングしてるのに体重が落ちないのはなぜ?


ダイエットを決意してウォーキングを始めてみたもの...

no image
イープラスでチケットが取れない!いい席を取るポイントは?


好きなアーティストのライブに行きたいけどチケット...

スポンサーリンク


Q.関数式内におけるセルの固定をする方法は?

エクセルの関数式内におけるセルの固定についての質問です。

=15000/IF(AND(A45=*,AF1=*),0.3,(A45-*)^2+(AF1-*)^2)

という関数で、*の部分以外を固定させたいのですがどうしたろよいのでしょうか?

【追記】

=15000/IF(AND(A45=B2,AF1=C1),0.09,(A45-B2)^2+(AF1-C1)^2)

C2にこの式を入力し、C2やD2に移動すると上記の*の部分が

=15000/IF(AND(A46=B3,AF2=C2),0.09,(A46-B3)^2+(AF2-C2)^2)

というように動いてしまうんです。

処理するデータの数がとても多いので、できれば*部分以外の数値を動かないようにしたいのですが、どのような関数を入力したらよいのでしょうか?

説明が分かりにくいかと思いますが、よろしくお願いいたします。

ちなみに、使っているのは2007バージョンのソフトです。

引用元-エクセルの関数式内におけるセルの固定についての質問です。=15000/IF… – Yahoo!知恵袋

「絶対参照」にしてセル関数を固定する

関数をコピーするとズレるのは「相対参照」だから

エクセルで、セルに入力された関数を、他のセルにコピーや移動すると、参照先のセル番地ズレます。

これが正常です。
(下図では、C5に入力した計算式をC6とC7にコピーしましたが、参照セルがズレます。)

この自動でズレることを、ちょっと難しい言葉で「相対参照」と言います。

計算式の入っているセルから見て参照先を”相対的”に移動させる機能です。

エクセルが自動でズレを発生させて、人手による入力を軽減してくれているのです。
便利ですね~w。

この相対参照がエクセルの初期設定(デフォルト)です。

でも「消費税」や「なんらかの係数」を一か所に設定し、後から一括変更したい時などでは、参照先を固定してずらしたくない場合もありますね?

こんなときの解決方法が「絶対参照」です。

関数をコピーしても参照セルのズレを起こさない「絶対参照」

関数をコピーしてもセルの参照先をズレなくする(固定する)方法は、関数の引数に指定しているセル番地に「$」を付けることでズレなくなります。

これが「絶対参照」です。上で説明したように相対参照がデフォルトなので、これを「$」を付けることで強制的に固定させちゃいます。

このように個数(A5,A6,A7)と価格(B5、B6、B7)相対参照、消費税(A2)は絶対参照にできます。

一つのセル内の計算式で相対参照と絶対参照を混ぜて使うことができます。

引用元-EXCELの関数をコピーするとズレる?(絶対参照&相対参照)~みんなのエクセル表計算

セル関数を固定をさせた後の操作方法

今回は相対参照と絶対参照についての説明です。はじめての方にはちょっとわかりづらいかもしれませんが、慣れれば大丈夫です。^^

まずは概要を説明します。

セル参照には、「相対参照」「絶対参照」「複合参照」という3種類があります。

これらは、数式を他のセルにコピーしたときに、セル参照のセル番地を変更するかどうかを指定するものです。

相対参照:数式をコピーしたとき、コピー先でそこのセル範囲に合わせて行番号と列番号が変化する参照の仕方。

絶対参照:行・列ともに固定させる参照方法。

複合参照:行と列の一方を変化、他方を固定にする複合型の参照方法。

絶対行参照:複合参照の中でも、行だけを固定する方法。

絶対列参照:複合参照の中でも、列だけを固定する方法。

行番号、列番号を固定させるには、セル番地の行番号、列番号の前に「$」(ドル記号)を付けます。

「$」は、手入力することも出来ますが、セル番地を入力状態にして「F4」キーを押すと簡単に入力出来ます。

「F4」キーを押すごとに、

$A$1(絶対参照) → A$1(絶対行参照) → $A1(絶対列参照) → A1(相対参照)

の順で変化します。

引用元-Excel豆知識6-1:エクセル:絶対参照・相対参照

セルの各関数の役割

【各関数の役割】

セルB2(結合B3):COUNTA関数

この関数はA社の対応内容が書かれているセルが何個あるか数えています。

ただし範囲はZ列までの中でとなっていますのでこれを超えて書き込まれても増えることはないので、実際にはどんな表を作るかによって個別に設定した方がよいでしょう。

セルC2:MAX関数
この関数はA社の日付が書かれているセルで最も大きい(最新の)日付を探して表示しています。

ただしCOUNTA関数同様に範囲はZ列までの中でとなっていますのでこれを超えて書き込まれても増えることはないので、実際にはどんな表を作るかによってCOUNTA関数に合わせて設定した方がよいでしょう。

セルC3:OFFSET関数
この関数は使い方がいくつかありますが、今回は「セルの位置を指定すると、その位置(アドレス)にあるセルの内容を表示する」という仕組みを使っています。

そしてこの表では「最新の対応内容」のセルを表示して(させて)います。
ポイントとしては、最新の対応内容が入力されているセルの位置は不確定です。

対応回数が増えれば、その分セルの位置的には右に移動するからです。簡単に言うとセルの位置が固定できず決め打ちできません。

そこで今回はCOUNTA関数の入っているセルB2の(関数の結果)値を使っています。イメージとしては「もしCOUNTAのセルの対応回数がNなら、セルD3から右にN個分のセルの内容を表示しなさい」と言った内容になります。

と、することで、いわゆる「汎用的な作り」になっています。

引用元-Excelで関数だけを使って進捗管理表

エクセルのセル参照は便利です!

エクセルの「セル参照」とは、例えば A1 として「セル番地」A1を直接「数式」で使用し、そのA1の「値」または「文字列」を取り出すことです。

次に、入門書で良く使われる例を示しました。

1.B1へ =A2 と式を入力すると、 「セル番地」 A2 の「値」または「文字列」をB1に表示できます。

2.C1へ =SUM(B1:B20) と数式を入力すると、範囲B1からB20の「値」の合計を計算し、C1に表示できます。

3.C1に =A1+B1 を式として入力した場合、A1とB1の和をC1に表示させます。
このようにエクセルは、「セル番地」を「数式」「関数」の中で使用し、「セル参照」できます。

優れている「セル参照」の効果を試したことがありますか?
なぜ、この「セル参照」が便利なのでしょうか?

理由は、エクセルが、入力した値・文字列を「セル参照」により「数式」「関数」で活用出来るように設計されているためです。

したがって、100% EXCELの能力を活用するため、表計算の原点「セル参照」を見直す必要があるようです。

「数式」や「関数」の基になる数学の定理は、応用範囲が広く、少数の定理の活用方法を覚えると、様々の場面で利用出来るようになります。

数学は、数式を使用します。

数式には、変数が活躍します。

この「変数」に相当するものが「セル番地」です。

引用元-エクセル 参照 は便利です!

twitterの反応


https://twitter.com/blx_jet/status/619359892277608448

 - 未分類

ページ
上部へ