• ベストアンサー

エクセル 条件式書式 数式

仮にG2からG4セルの数値が G8からG14までの数値と同じなら色を付けるという数式 =SUM(IF($G$2:$G$4=G8,1,0))>0  で このデーターが200ほどありまして エクセル の「開発」でのマクロ でどんどんやりたいのですが 書式設定し行の挿入をして 1行横えずらすたびに  違う数値に色がつきます =SUM(IF(G$2:G$4=G8,1,0))>0 =SUM(IF($G2:$G4=G8,1,0))>0 =SUM(IF(G2:G4=G8,1,0))>0  の3パターンどれもだめです どうすればいいでしょうか

質問者が選んだベストアンサー

  • ベストアンサー
  • tom04
  • ベストアンサー率49% (2537/5117)
回答No.7

No.3・4です。 補足をよくよく読んでみると 結局条件付き書式の数式が絶対参照になっている部分を複合参照にすれば良いのではないでしょうか? 数式を =SUM(IF(A$3:A$12=A15,1,0))>0 のように$マークを行だけ固定の「複合参照」にしてみては? これでA列の3~12行目を参照するのではなく、列挿入すると参照先はそのまま移動するはずです。 =COUNTIF(A$3:A$12,A15) という数式でも同じ結果になるとは思いますが・・・ それから列挿入 → A列にデータ貼り付け という操作を200回繰り返す!というコトですが、 もちろんVBAで可能です。 ただ、質問文を見る限りどういったデータをどこから引っ張ってきて貼り付けるのか?が明記されていないので コードの書きようがありません。 とりあえずは「条件付き書式」の対処だけです。m(_ _)m

kimari14
質問者

お礼

出来ました。有り難うございました。助かりました =SUM(IF(A$3:A$12=A15,1,0))>0の式を 次郎の数値1つ1つに設定すればよかったんですねー それを次郎の数値をすべて選択して一番最初の数値に設定していました。それがよくなかったんでしょうか。 なんども付き合っていただいてたすかりました。

kimari14
質問者

補足

なんどもすいませんね >=SUM(IF(A$3:A$12=A15,1,0))>0 のように$マークを行だけ固定の「複合参照」にしてみては?< 一回一回手動というか できます が 困っているのは この手順を エクセルの開発 マクロ? に覚えさせてもうまくいきません。 太郎のA3のセルの数値の色だけ(つまり太郎の頭の数値)を次郎の数値につけ 又、それより前の分はすべて色が消えます なんでなんでしょうか 

その他の回答 (6)

  • kkkkkm
  • ベストアンサー率65% (1579/2414)
回答No.6

条件付き書式を設定しているのは$A$15:$A$25で、その条件が=SUM(IF($A$3:$A$12=A15,1,0))>0なのですよね。示されたデータの例だとA18からA21までが指定された色が付きます。 ちなみに、例示されたデータはA列もB列も同じデータですので同じデータで同じ個所に色がつかないと条件付き書式が有効じゃないことになりますが…。 > 次にA列を次郎のさいごまでコピーしてB列に貼り付けます これで、A列をB列にコピーすればA列とB列のデータは同じですし、条件式の範囲指定が「行列とも絶対参照」になっているわけですから、当然A列のデータを変化させたとしてもB列の条件付き書式の設定がされた$B$15:$B$25の同じ行に色がつくのは当たり前です。ですので、前回の回答で=SUM(IF(A$3:A$12=A15,1,0))>0という「行だけ絶対参照」にする式にすることによって、それぞれの列の3行目から12行目までのデータによって色がつく行が変化しますけどと申し上げたのですが。 > B列に1つ列を挿入 何をしたいのか意味が分かりません。 200回繰り返したいのでしたら For i=1 to 200 したいことはマクロの記録でコードを取得してここに記載 Next > 移動させれば 色をつけた箇所が毎回 A列の太郎の10個の数値の色を返してきます。 コピーとか移動とか語句が一定しませんが、何をどう移動するのでしょう? ちなみに、どのセルを条件付き書式の対象として範囲指定していますか、例示されたデータだとA15からA25の範囲指定になりますが、そうなっていますか?

kimari14
質問者

お礼

出来ました。有り難うございました。 =SUM(IF(A$3:A$12=A15,1,0))>0の式を 次郎の数値1つ1つに設定すればよかったようです。 それを次郎の数値をすべて選択して一番最初の数値に設定していました。それがよくなかったんでしょうか。 なんども付き合っていただいてすいませんでした。

  • kkkkkm
  • ベストアンサー率65% (1579/2414)
回答No.5

2013ですが、元の式を =SUM(IF(G$2:G$4=G8,1,0))>0 にして、条件付きの部分だけをK列に手動でコピーしたら =SUM(IF(K$2:K$4=K8,1,0))>0 に変更されて適用先も =$K$8:$K$14 になりその後列の挿入をしても列の値がその分変更されて(一列挿入したらL列に変更されました)質問には行の挿入とありますが行じゃなく列ですよね。1行横にずらすというのはあり得ませんから、列だと判断しました。 > もとの行のG2ーG4の数値を変えると  > 移動した列のG8ーG14の条件式も影響を受けます ということはなく、コピー先のK列の2行目から4行目のデータで$K$8:$K$14に条件付き書式が反映されました。マクロに何か問題があるのではないでしょうか。もしくは、最初の条件付き書式の範囲がG8からG14以上のところにまで及んでいるということはありませんか。マクロ実行前と実行後の条件付き書式がどのようになっているのか確認されたらいかがでしょう。

kimari14
質問者

補足

すいませんはじめからやります。 まずA列の太郎に毎回違うデーターを貼り付けます この場合仮に10個の数値です。 その下、次郎にも毎回違うデーターを貼り付けます そして太郎の数値と同じものが次郎にあれば次郎の方の数値に色を付ける 書式は=SUM(IF($A$3:$A$12=A15,1,0))>0です 次にA列を次郎のさいごまでコピーしてB列に貼り付けます B列に1つ列を挿入 この作業をくり返して200回ほどしなければらりません。ですのでエクセルの開発のマクロ?で ボタンを押せばこれをくり返えせるとおもったのですが 移動させれば 色をつけた箇所が毎回 A列の太郎の10個の数値の色を返してきます。影響を受けない条件式の書式がしりたいのです。またこれらができるVBAのコードがあればお願いしたいのです、厚かましいことですいません。宜しくお願いします。 A列          B列 太郎         太郎2 1          1 2          2 3          3 4          4 5          5 6          6 7          7 8          8 9          9 10          10 次郎         次郎2 11          11 12          12 13          13 8          8 1          1 3          3 7          7 15          15 17          17 20          20 30          30

  • tom04
  • ベストアンサー率49% (2537/5117)
回答No.4

No.3です。 たびたびごめんなさい。 >このデーターが200ほどありまして すなわち結構の範囲が条件付き書式の対象となる訳ですかね? そうであれば一つ一つループさせる方法ではなく、条件付き書式を設定する方法にしてみました。 画面左下の操作したいSheet見出し上で右クリック → コードの表示 → VBE画面のカーソルが点滅しているところに コピー&ペースト → Excel画面にデータ入力・行挿入・行削除等の操作をしてみてください。 Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'この行から With Cells .FormatConditions.Delete .Interior.ColorIndex = xlNone End With With Range("G8:G14") '←ココで対象セルを選択 .FormatConditions.Add xlExpression, Formula1:="=COUNTIF($G$2:$G$4,G8)" '←数式は条件付き書式の数式 .FormatConditions(1).Interior.ColorIndex = 3 '←「赤」に設定 End With End Sub 'この行まで ※ 今回は条件付き書式を設定するようにしていますので、 条件付き書式のクリアは無視しても大丈夫です。 ※ 実際のデータ範囲はコード内にコメントを入れていますので、 そこで変更してみてください。m(_ _)m

kimari14
質問者

補足

すいませんはじめからやります。 まずA列の太郎に毎回違うデーターを貼り付けます この場合仮に10個の数値です。 その下、次郎にも毎回違うデーターを貼り付けます そして太郎の数値と同じものが次郎にあれば次郎の方の数値に色を付ける 書式は=SUM(IF($A$3:$A$12=A15,1,0))>0です 次にA列を次郎のさいごまでコピーしてB列に貼り付けます B列に1つ列を挿入 この作業をくり返して200回ほどしなければらりません。ですのでエクセルの開発のマクロ?で ボタンを押せばこれをくり返えせるとおもったのですが 移動させれば 色をつけた箇所が毎回 A列の太郎の10個の数値の色を返してきます。影響を受けない条件式の書式がしりたいのです。またこれらができるVBAのコードがあればお願いしたいのです、厚かましいことですいません。宜しくお願いします。 A列          B列 太郎         太郎2 1          1 2          2 3          3 4          4 5          5 6          6 7          7 8          8 9          9 10          10 次郎         次郎2 11          11 12          12 13          13 8          8 1          1 3          3 7          7 15          15 17          17 20          20 30          30

  • tom04
  • ベストアンサー率49% (2537/5117)
回答No.3

こんばんは! 質問の内容は行挿入(列挿入)しても条件付き書式の設定範囲は常にG8~G14の範囲で固定したい! というコトでしょうか? そうであれば、行挿入(列挿入)した場合は挿入されたセルも適応され、条件付き書式の範囲も 挿入された行数(列数)分だけ広がってしまいますね。 そこで条件付き書式ではなく、VBAでの一例です。 条件付き書式の設定はセルの塗りつぶしを「赤」にするという前提でのコードです。 ↓のコードを操作したいシートモジュールにコピー&ペーストしてデータ変更・行挿入等を行ってみてください。 Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'この行から Dim c As Range, r As Range, myRng As Range Set myRng = Range("G2:G4") Cells.Interior.ColorIndex = xlNone For Each c In Range("G8:G14") Set r = myRng.Find(what:=c, LookIn:=xlValues, lookat:=xlWhole) If Not r Is Nothing Then c.Interior.ColorIndex = 3 '←「赤」としています End If Next c End Sub 'この行まで ※ 対象セルに条件付き書式の設定があると反応しませんので、 条件付き書式のルールはクリアしておいてください。 ※ 本題から離れますがお示しの条件付き書式の数式は =COUNTIF($G$2:$G$4,G8) というコトだと判断しています。m(_ _)m

  • kkkkkm
  • ベストアンサー率65% (1579/2414)
回答No.2

=SUM(IF($G$2:$G$4=$G8,1,0))>0 ではだめでしょうか。

kimari14
質問者

補足

仮にG2からG4セルの数値が G8からG14までの数値と同じならG8からG14のほうに色を付ける =SUM(IF($G$2:$G$4=G8,1,0))>0 で色はつきますが この列をコピーして移動させて 次のデーター作業で もとの行のG2ーG4の数値を変えると  移動した列のG8ーG14の条件式も影響を受けます そうならないように です 言葉足らずですいません

  • asuncion
  • ベストアンサー率33% (2126/6286)
回答No.1

>仮にG2からG4セルの数値が >G8からG14までの数値と同じなら色を付けるという数式 >=SUM(IF($G$2:$G$4=G8,1,0))>0  で G9~G14はどこへ行っていますか?

kimari14
質問者

補足

仮にG2からG4セルの数値が G8からG14までの数値と同じならG8からG14のほうに色を付ける =SUM(IF($G$2:$G$4=G8,1,0))>0 で色はつきますが この列をコピーして移動させて 次のデーター作業で もとの行のG2ーG4の数値を変えると  移動した列のG8ーG14の条件式も影響を受けます そうならないように です 言葉足らずですいません

関連するQ&A

  • 数式・条件付書式の設定方法を教えてください。

    初心者です、宜しくお願いいたします。 1.F6:H9に設定した内、F6・G6・H6に設定した数式及び条件付書式です。(現在、まだ40行分しか設定出来ていません)   ●F6 獲得点数     ・点数をマニュアル入力する。     ・条件付書式 =SUM($F$6:$F$9)=100000  → 書式(パターン) ブルー   ●G6 順位     ・数式  =IF(F6="","",RANK(F6,$F$6:$F$9)+COUNTIF($F$6:$F6,F6)-1)     ・条件付書式  =SUM($G$6:$G$9)=10  → 書式(パターン) ブルー   ●H6 加算後点数     ・数式  =IF(OR(C6="",E6="",F6="",G6=""),"",(IF(G6=1,F6+12000,IF(G6=2,F6+4000,IF(G6=3,F6-4000,IF(G6=4,F6-12000))))))     ・条件付書式  =SUM($H$6:$H$9)=100000  → 書式(パターン) ブルー 2.実際の処理   ●F列(F6:F9)     ・F6:F9に点数を入力する。     ・その合計が100、000点になれば、F6:F9をブルーで塗りつぶす。 --------------   ●G列(G6:G9)     ・入力したF6:F9の点数で順位を決定し順位をG列に表示する     ・同点の場合は若いセル数を上位にする。     ・その合計が10になれば、G6:G9をブルーで塗りつぶす。 --------------   ●H列(H6:H9)     ・F列・G列の結果を受けて数式のような点数を表示する     ・その合計が100、000点になれば、H6:H9をブルーで塗りつぶす。 --------------     ・4行毎に塗り潰しの色を変えたい。     ・6行目:9行目はブルーで塗りつぶす。     ・10行目:13行目は黄色で塗りつぶす。     ・14行目:17行目はブルーで塗りつぶす。 ーーーーーーーーーーーーーーーーーー 3.ご指導いただきたき事 上記のようにしたいのですが、約1000行ほどになりますので、250回ほど設定しなければなりません。 ぜんぜん判りませんが、何か良い設定方法をご指導いただきたくお願い致します。   ●現在設定を始めました、数式及び条件付書式には、拘りません。   ●黄色い部分もブルーにし、ブルー一色でも結構です。   ●VBA記述でも可能なのでしょうか。    VBA記述はぜんぜん出来ません、申し訳ございませんが、可能であれば宜しくお願いいたします。

  • Excelの条件付き書式で、数式と数値を区別する方法

    Excelの条件付き書式の使い方を教えてください。 ちょっとややこしめの表があって、数式や関数がちりばめられています。 この表は、毎月、数値を変えて使いたいのですが、 数値が入っているセルと、数式が入っているセルが混在していて、 どこに値を入力すればいいのか、いまいちつかみにくいのです。 数値のセルだけ色をつけ、入力するところのみを際立たせたいのですが、 条件付き書式でできないでしょうか? よろしくお願いします。

  • エクセルの条件付き書式が消えてしまいます。

    エクセルの条件付き書式が消えてしまいます。 エクセルのC列に条件付き書式を設定ました。次の数式のとき、セルに色がつくというものです。 =COUNTIF(C:C,C1)>1 しかし、行を削除したり、また付け加えたりしているうちに、条件付き書式が無効になるのに気づきました。おそらく新しく追加する行にはこれは無効なのでしょうか。。(?) 私はC列ならすべて、この条件に当てはめたかったのですが、追加したり削除したりしていると、無効になるセル(行)が発生するようです。 これを防ぐことはできないでしょうか?条件付き書式ではムリで、マクロなどにするしかないのでしょうか?

  • エクセルの「条件付き書式」で困っています

    エクセルの「条件付き書式」なんですが 表の見栄えを良くするために、一行ごとに色を付けようと 「書式」-->「条件付き書式」-->設定ダイヤログに 条件(1)・・「数式が」-----「=MOD(ROW(),2)=0」を設定しました     書式-->パターン・グリーンを指定 思い通り選択範囲の表に、一行ごとの縞模様が出来たのですが 追加で 条件(2)・・「セルの値が」-----「次の値より大きい」-----「100」     書式-->フォント太字・赤を指定 と指定したところセルに色の付いた行(偶数行)は100以上の数値が入力されても フォントが太字・赤になりません このような条件は「条件付き書式」ではなく、表示形式で設定するのでしょうか? 私は「条件付き書式」で一括に設定したいのですが 詳しい方がいらしたら教えて下さい WIN2000・エクセル2000  です。

  • エクセル 「数式を使用して書式設定」式の書き方

    「条件付き書式」の設定について教えて下さい。 「数式を使用して書式設定するセルを決定」にて、セルの塗りつぶしをしたいです。 「次の数式を満たす場合に値を書式設定」で、下のような範囲指定をしたいです。 セルに格納されているのは数値で、例えば「70」以上でセルの色を黄色に、「80」以上でセルの色をオレンジ色に、「90」以上でセルの色を赤色に、という風に設定したいです。 その時の、条件式の書き方を教えて下さい。 宜しくお願いします。

  • 条件付き書式(エクセル2003)について

    セル C1 の値(文字列)がXならばセルA1 の背景色を黄色にしたいんですが、「条件付き書式」からどうすればいいのでしょうか?A1を選択して条件付き書式、数式が、まではわかるのですがここからどうしましょうか? =C1="X" で、パターンから色を黄色にすればいいんでしょうか???

  • エクセル IF関数が入った条件付き書式について

    エクセルにおいて、セルA1、B1、C1があるとします。A1、B1には数値が何も入力されていない状態で、C1に計算式「=SUM(A1)/B1」が入っているとします。このままでは、C1は、「#DIV/0!」と表示されます。 この表示を消すために、C1にIF、ISERROR関数を使い、「=IF(ISERROR(SUM(A1)/B1),"",SUM(A1)/B1)」の計算式を入力します。 このままでもいいのですが、C1の値が「10以上」になった時に、C1のセルに色を付けたいのです。 書式→条件付き書式→セルの値が→次の値以上→10 にすると、セルに何も値が入っていない(空白)状態で色だけが付いてしまいます。 色々調べると、IF関数の""の空白が文字列のため、数値より大きいとみなされている事が原因っていうことは、分かりましたが、色々試してみても出来ません。何かいい方法はございますでしょうか? ご教授お願いします。

  • エクセル2000の条件付き書式について

    エクセル2000において、条件付き書式を利用すると、そのセルの文字色とかパターン(塗りつぶしの色)などをその条件にそって、変えることができるのはわかったのですが、行単位で、条件付き書式を利用できないでしょうか? つまり、A1のセルがマイナスになった場合は、1の行はすべて赤に表示するということなのですが…

  • EXCEL2003 条件付書式が狂ってくる

    条件付書式は、見ためにどのセルに入っているのかわからないというのと、複数の条件付書式が入ると、なぜかその前に決めていた条件付書式が消えてしまっているということがあって、なかなか不便に思っています。(やりかたが悪いのかもと思うのですが) それで、VBAの entireculumn というのを見つけたのですが、 これで対応できるでしょうか? やりたい事は、 1 2*--------------------------- 3*--------------------------- 4 5 6*--------------------------- 7*--------------------------- 上記のように、「*」をつけた行にだけ、行全体に色をつけておく、 というのを条件付書式で、 「数式が」「=$A1="*"」 としたもので、その設定をしているシートに、横にずっと、いろんな関数を入れていってます。 ですが、関数を入れたり、そのプロセスで列挿入などすると、 色がずれてしまうのです。 その回避策として、VBAで最初に色の設定をしておけないでしょうか? 「*がある行だけに色をつける。」 よろしくお願いします。

  • エクセル2010 条件付き書式について

    エクセル2010での数式を使った条件付き書式について教えてください。 画像のようなデータで、条件付き書式を使ってレコード(行)に塗りつぶしを設定したいと思ってます。 条件は合計欄(G列)の最大値のレコードに黄色の塗りつぶしと最小値のレコードに赤の塗りつぶしのようにしたいと思っています。 1.B4~H11を範囲選択 2.条件付き書式 3.数式を使って書式設定 4.=max(G4:G11) というように設定してみたりしてるのですが、違うようで、思うようになりません。 絶対参照を行につけたり列につけたりしてもうまくいきません。 わかる方がいたらぜひ教えてください。 よろしくお願いします。

専門家に質問してみよう