「絶対参照・複合参照」を使って「条件付き書式」を設定する

「数式」を他のセルにコピーする時に、「絶対参照・複合参照」にしておけば数式が自動で最適化されます

　数式を入力した「セル」をコピーする際には、「自動で参照」されますが、その参照方法にはいくつか種類があります。

数式で使う参照方法には「＄」の付き方によって４種類あります。

1. 相対参照　　　　　　「 B2 」
2. 絶対参照　　　　　　「 $B$2 」
3. 複合参照（列固定）　「 $B2 」
4. 複合参照（行固定）　「 B$2 」

参照方法は「F４」を押すことで変更できます

　参照方法を変える方法は、変更したい「セル番地」にカーソルを置きファンクションキーの「F４」を押すことで変更できます。

参照方法を変更したいセル番地にカーソルを置く　　

　カーソルを置く位置は、「セル番地」に接していればどこでも構いません。

　　　

ファンクションキー「F4」を押すごとに順次切り替わります

　「F4」キーを押すと、「＄」マークの付く位置が変わり参照方法が変更されます。　　

　　　

１．相対参照

　相対参照で指定したセル番地は、セルをコピーすると「行」も「列」も変化します

表の1カ所に入力した数式を、他のセルにコピーする

 　      

 通常は「フィルハンドル」を使ってコピーするだけです

　下に向かってコピーすることで、数式の「行」が自動で変更になります。

　右に向かってコピーすることで、数式の「列」が自動で変更になります。

　この場合は「参照方法」は考慮せずフィルハンドルでコピーします。

　　　

「下方向」にコピーすると「行」が変化します

　下方向に「２」移動すると、行が「３」から「５」に変化します

　　　

２．絶対参照 

 　セル番地の、「行」と「列」それぞれの前に「＄」を付けることによって絶対参照となり、コピーしてもセル番地は変化しません。

絶対参照を使わないと、フィルハンドルでコピーができない例

　数式の中に特定のセルを参照している場合は、コピーすることで「行」が変わると参照先が変わってしまう。

　　　

１行下にコピーすることで、参照先も１行下にずれてしまう

　　　

 絶対参照を使って「参照」するセルを固定する

　参照先のセル番地を「絶対参照」にすることで参照先のセル番地が固定され、数式を下方向にコピーしても参照先が変化しません。

　　　

参照先が絶対参照で固定されました

　数式で参照する「消費税率」のセルを絶対参照にすれば、数式を下方向にコピーしても参照先が変わらず数式が正しく入力されます。

　　　

３．複合参照

　「行」か「列」のどちらか一方に「＄」記号を付けることにより、「行」か「列」のどちらかを固定します。

九九の表を作成します

　縦方向にも、横方向にも変化する必要がある「九九の表」を作成してみます。

縦方向にコピーする時は「行」を固定する

　　　

横方向にコピーする時は「列」を固定する

　　　

（列固定）×（行固定）の複合参照を組み合わせます

　　　

「２行目」×「B列」で掛け算をします

　参照先のセル番地に「＄」記号を付けずに数式を入力します。

　　　

複合参照を適用せずに数式を「縦方向」にコピーします

　セル番地に「＄」記号を付けない相対参照では、数式を下方向にコピーすると参照先の行が変化してしまいます。

　縦方向にコピーすることにより「C2」になるべきものが、「C6」になったてしまうので「行」を「２」に固定するために「C」に「＄」記号を付けます。

　　　

複合参照を使って「C２」を「C$２」として、行だけを「３」行目に固定します

　　　

「横方向」にコピーします

　横方向にコピーすると「B３」になるべきものが「F３」になったので、列を「F」に固定します。

　　　

「F３」を「$F３」として、「F」列に固定します

　　　

九九の表が完成しました

　複合参照を使って、「 ” 列だけを固定したセル”  ×  ”行だけを固定したセル” 」とすることで表が完成しました。

　　　

「条件付き書式」で絶対参照（複合参照）を使う際には注意が必要です

　条件付き書式の設定で数式を使う際に「絶対参照・相対参照」は通常の場合と少し異なる点があります。

条件付き書式で「選択範囲全体」に書式を設定する場合

　数式で条件付き書式を設定をする場合は、書式を設定したい「範囲」の左上のセル番地を相対参照で指定すれば、「範囲全体」に条件付き書式が設定されます。

　※ 条件付き書式の設定は過去記事も参考してください

同じ形式の２つのの表を比較して、条件に合うものに「書式」を設定する場合

　「4月分」と「5月分」の2つの表を比較して、一定割合の変化があるものに書式を設定する。

• ４月分に比べて１０％以上増加したものを「青く」塗りつぶす
• ４月分に比べて１０％以上減少したものを「赤く」塗りつぶす

　　　

条件付き書式を設定する範囲の最初の行（左上）を「数式」の対象にして「相対参照」にする

　書式を設定する範囲の左上のセルを相対参照で指定することで、範囲内の縦方向にも右方向にも最適化されます。

　　　

選択範囲の最初のセル（左上）を「相対参照」で指定する

　　　

相対参照にすることで、選択範囲全体が「条件付き書式」の対象になります

　　　

条件付き書式で「条件に合う「行」」に書式を設定する場合

　数式の条件に特定のフィールド（項目）を指定したい場合は、列固定の複合参照にします。

指定した「条件」に該当する「行全体」に書式を設定します

　「合計」の点数に応じて、「行全体」に書式を設定します。

　　　

「合計」の列の点数を基準に、条件に合う「行」に書式を設定する

　「合計」が条件に合う場合は、「１行単位」で塗りつぶされます。

• 合計が400点以上の「行」全体を青で塗りつぶす
• 合計が350点以上の「行」全体を緑で塗りつぶす
• 合計が300点以上の「行」全体を黄で塗りつぶす
• 合計が300点以下の「行」全体を赤で塗りつぶす

　　　

 

条件付き書式の対象になる「合計」の一番上の行を「数式」の対象にして列固定の「相対参照」にする

条件付き書式の「基準」になる、「合計」の列の最初の行（一番上）を数式の対象にして、「列固定の相対参照」にする。

　　　

選択範囲の最初のセル（左上）を「列固定の相対参照」で指定する

　列固定の複合参照にすることで、行のみが最適化されて選択範囲全体の条件が設定される。

　　　

対象となる条件を持つ「列全体」に書式が設定されました

　条件に合う「セル」だけではなく、「１行単位」で書式が設定されます。

　　　

それ以外の条件も設定します

　複数の条件がある場合は「上から順に」適用されます。

　今回の場合は、条件の「合計」が大きいものを「先に（上に）」設定する必要があります。

　　　

すべての条件が設定されました

　「合計」を基準にして「行」に書式が設定されました。

　　　

　このように「条件付き書式」で、絶対参照（複合参照）を使う場合は注意が必要です。

　使いこなせば「いろいろな表現」が可能です、参考にしてください。

k-ohmori9616.hatenablog.com