条件付き書式 2つのシート間比較

質問

エクセルでのアンケート集計をしています。
○と×でA1：D25入力された表で
Sheet1　→前年度　×　　　　Sheet2　→今年度　○　　だったら　赤
Sheet1　→前年度　○　　　　Sheet2　→今年度　×　　だったら　青　

Sheet1　→前年度　○　　　　Sheet2　→今年度　○　　だったら　塗りつぶし無し
Sheet1　→前年度　×　　　　Sheet2　→今年度　×　　だったら　グレー

のように今年度のSheet2にセル塗りつぶしの色の書式をいれたくて悩んでいます。

どなたかご教授願います

回答No.7

kagakusuki · ベストアンサー

>例えば12行目は条件付き書式を反映させない、ときは適用先をA1:D11，A13:D25のように，カンマでつないだだけではだめなんですよね

　いえ、それでOKです。
　それから、1つアドバイスなのですが、Excelの一般機能の場合(VBA等は使わない場合)、どれほど下手な事をしようとも、得られる値が間違っているか、エラーや循環参照となって結果が出ないだけの事で、パソコンが壊れたり、フリーズして処理が永遠に終わらなくなったり、といった困った事態に陥る心配は無いのですから、回答No.6に対する補足コメントに書かれておられる上記の事柄程度の事であれば、他人に聞く前にバックアップ用のコピーファイルを作っておいた上で、駄目元で思い付いた事を試してみるのも一つの方法です。

回答No.6

kagakusuki

> あとできれば
> =AND(A1="○",INDIRECT("'Sheet1'!RC",FALSE)="×")
> のRCってR1C1形式でいうA1のことですか？

　違います。R1C1形式である事は確かですが、R1C1形式における「$A$1」は「R1C1」であり、「RC」ではありません。
　A1形式に慣れてしまった方にはややこしく感じるかも知れませんが、R1C1形式はA1とは参照の指定の仕方が異なります。
　例えばE7セルに次の様な関数が入力されていたとします。

=$B$3+$B4+D$2+I5

　ここでE7セルをコピーしてG8セルに貼り付けたとします。
　するとG8セルに入力されている関数は

=$B$3+$B5+F$2+K6

になります。
　これをR1C1形式で表しますと、R7C5セル(E7セル)に入力されている関数は

=R3C2+R[-3]C2+R2C[-1]+R[-2]C[4]

同じくR8C7(G8セル)に入力されている関数は

=R3C2+R[-3]C2+R2C[-1]+R[-2]C[4]

になります。
　この様に、R1C1形式の場合、数式を別のセルにコピーしても数式が変化しません。
　例えば、R3C2ではRやCの後に直接数字が付けられている形式で記述されていて、Rの後の数字は3、Cの後の数字は2となっていますが、これは「行(RはRowのR)番号が3で列(CはColumnのC)番号が2のセル」、つまりB3セルを参照する事を表し、これを別のセルにコピーしても参照先は「行番号が3で列番号が2のセル」である事は変わりませんから、行番号と列番号の両方が共に絶対参照である事を表しています。
　これに対して、R[-2]C[4]ではRやCの後に数字が[　]付きで記述されていて、Rの後の数字は-2、Cの後の数字は4となっていますが、これは「"数式が入力されているセルに対して"行(RはRowのR)番号が-2(2つ少ない)で列(CはColumnのC)番号が+4(4つ多い)のセル」、つまりR7C5セル(E7セル)に入力されている場合にはR5C9セル(I5セル)を参照し、R8C7セル(G8セル)に入力されている場合にはR6C11セル(K6セル)を参照する事を表し、これを別のセルにコピーしても参照先は「行番号が2つ少なく列番号が4つ多いセル」という相対的な位置関係が同じセルを参照する事になりますから、行番号と列番号の両方が共に相対参照である事を表しています。
　同様に、R[-3]C2の場合は行番号が相対参照で列番号が絶対参照である「行番号が3つ少なく列番号が2の列(B列)のセル」を参照する事を表し、R2C[-1]の場合は行番号が絶対参照で列番号が相対参照である「行番号が2の行で列番号が1つ少ない列のセル」を参照する事を表します。
　そして、RCの場合は行番号と列番号の両方が共に相対参照である事を表していて、「行番号と列番号の両方が共に"数式が入力されているセル"の行番号とや列番号と同じ値となっているセル」を参照する事を表します。
　'Sheet1'!RCの場合は、上記のRCの場合にシート名が付け加わっていますので、「行番号と列番号の両方が共に"数式が入力されているセル"と同じ値となっている"Sheet1上の"セル」を参照する事を表します。
　ですから、'Sheet1'!RCという記述がA1セルに入力されていた場合にはSheet1のA1セルを参照する事を表し、D25セルに入力されていた場合にはSheet1のD25セルを参照する事を表します。

補足

本当に細かく説明していただき、ありがとうございます。まず、アンケート集計をせねばならず、R1C1形式については後ほどマスターしたいと思っております。(理解が遅くて情けないですが。。)で。アンケート集計で、またまた解らない事が！
去年と今年の質問が数問変わっていることが発覚しまして。例えば12行目は条件付き書式を反映させない、ときは適用先をA1:D11，A13:D25のように，カンマでつないだだけではだめなんですよね。。。はー

回答No.5

imogasi

この質問には
・エクセルのバージョンが書いてない
・○と×でA1：D25入力された表ー＞４列のデータのようだが
と・２つのシートにまたがる点
と・Sheet1　→前年度　×　　　　Sheet2　→今年度　○　　だったら　赤
（各シート１列計２列のデータで済むのでは）
は、シートの列的に、どういう風に入力がされているのか、まぎらわしくないか？
具体例をシートー列ー行的に詳しく書くこと。
それというのも、
下記のような件（注意点）があることを知っているか？　　参考にして考えてみるとどうか。
http://www.eurus.dti.ne.jp/~yoneyama/Excel/jyo-syo_betusheet.html

回答No.4

kagakusuki

>補足いたします。新しいルールに数式を入力しますと、条件付き書式は別シートを参照できません。とされてしまいます。2007です。

　Excel2007からは条件付き書式でも別シートを参照可能になっていた筈ですが、現れた警告を無視してそのまま設定を強行すれば設定出来たりはしないのでしょうか?
　もし出来ない場合には、もしかしますとファイルの保存形式が通常の.xlsx形式ではなく、「Excel97-2003ブック」用の.xls形式になっているのではないでしょうか?
　もしそうであったのなら、ファイルの保存形式を.xlsx形式で保存しなおされるか、或は回答No.1の各数式を以下の様に修正されると良いと思います。

=AND(A1="○",Sheet1!A1="×")
　　↓
=AND(A1="○",INDIRECT("'Sheet1'!RC",FALSE)="×")

=AND(A1="×",Sheet1!A1="○")
　　↓
=AND(A1="×",INDIRECT("'Sheet1'!RC",FALSE)="○")

=AND(A1="×",Sheet1!A1="×")
　　↓
=AND(A1="×",INDIRECT("'Sheet1'!RC",FALSE)="×")

補足

できました！ありがとうございます。
あとできれば
=AND(A1="○",INDIRECT("'Sheet1'!RC",FALSE)="×")
のRCってR1C1形式でいうA1のことですか？
A1形式での数式はどうなりますか？

回答No.3

Prome_Lin

「VBA」で組みました。

まず、目的のエクセルファイルを開き、「Alt＋F11」（「Alt」（「オルト」と読みます）キーを押しながら、「F11」を押す）で、「Visual Basic」の画面を出します。

メニューの「挿入」から「標準モジュール」を選ぶと、画面の右側が白くなります。

その白くなった部分に、以下のマクロをコピー＆ペーストして、「F5」（実行）するだけです。

ファイルを閉じる前に「F12」で「名前を付けて保存」を出して、ファイル名が表示されている１行下の右端「∨」をクリックして、「Excel マクロ有効ブック」にして保存してください（「～.xlsm」となります）。

Sub Test()
Set s1 = Worksheets(1)
Set s2 = Worksheets(2)
For i = 1 To 25
For j = 1 To 4
p1 = s1.Cells(i, j).Value
p2 = s2.Cells(i, j).Value
If p1 = "○" And p2 = "×" Then
s2.Cells(i, j).Interior.ColorIndex = 5
End If
If p1 = "×" And p2 = "○" Then
s2.Cells(i, j).Interior.ColorIndex = 3
End If
If p1 = "×" And p2 = "×" Then
s2.Cells(i, j).Interior.ColorIndex = 16
End If
Next j
Next i
End Sub

簡単な説明です。

Set s1 = Worksheets(1)
Set s2 = Worksheets(2)

一番左端のシートを「s1」、左から２番目のシートを「s2」にセットしています。

For i = 1 To 25

２５行処理します。

For j = 1 To 4

４列処理します。

p1 = s1.Cells(i, j).Value
p2 = s2.Cells(i, j).Value

それぞれのセルの値を「p1」と「p2」に代入しています。

If p1 = "○" And p2 = "×" Then
s2.Cells(i, j).Interior.ColorIndex = 5
End If

もし「p1」が「○」で、かつ「p2」が「×」なら、
その２番目のシートのそのセルを「赤」に、

If p1 = "×" And p2 = "○" Then
s2.Cells(i, j).Interior.ColorIndex = 3
End If
If p1 = "×" And p2 = "×" Then
s2.Cells(i, j).Interior.ColorIndex = 16
End If

同じことを「青」と「グレー」でしています。

終わりです。

回答No.2

intin

参考URLに、条件付き書式の設定方法を図解されているページを貼っておきます。

そこの計算式に、
最初に解答されている方が入れている計算式を入れてやれば大丈夫だと思います。

参考URL：

http://www.excelspeedup.com/shi-tohikaku/

回答No.1

kagakusuki

以下の様に操作を行い、条件付き書式を設定して下さい。

Sheet2のA1セルを選択
　　↓
Excelウィンドウの[ホーム]タブをクリック
　　↓
現れた「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に以下の数式を入力

=AND(A1="○",Sheet1!A1="×")

　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた背景色のサンプルの中にある赤色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に以下の数式を入力

=AND(A1="×",Sheet1!A1="○")

　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた背景色のサンプルの中にある青色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に以下の数式を入力

=AND(A1="×",Sheet1!A1="×")

　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた背景色のサンプルの中にある灰色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの「適用先」欄を3箇所とも

=$A$1:$D$25

に変更（カーソルとマウスの左ボタンによる範囲選択が使えます）
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[OK]ボタンをクリック

　以上です。

補足

補足いたします。新しいルールに数式を入力しますと、条件付き書式は別シートを参照できません。とされてしまいます。2007です。