[[20081023172945]]　『Color関数　色のついた文字で合計を求めるUFClrSu』（SHINOKAZ）　ページの最後に飛ぶ

『Color関数　色のついた文字で合計を求めるUFClrSumfcx』（SHINOKAZ）

 お世話になります、セルに、=UFClrSumfc(A1:A5,3)と入れれば赤色のフォントの数字
 をＡ１からＡ５まで合計してくれるようにはなりましたが、実際に計算させたいのは、
 条件付き書式設定で数式がＸＸで書式の設定にフォントの色やセルの色を指定している
 セルを計算させたいのですが出来ません。なにか良い方法はないでしょうか？

 条件付き書式設定でのセル色やフォント色は見せかけだけのようです。
 よろしくお願いします。

 >条件付き書式設定でのセル色やフォント色は見せかけだけのようです。

 理屈だけの話ですが、、、

 条件付き書式で色がついているなら、「色」で合算の可否を判断させず、
 条件式の真偽で判断させればいいのではないですか？

 (半平太)

 あれー「UFClrSumfc」ってこの学校が紹介しているアドインだったのですね　(^^ゞ
 そこに、「条件付き書式対応版もあります」となっていますが。。

http://www.excel.studio-kazu.jp/DL/UFClr/UFClr.html

 （半平太）

 ご返事ありがとうございます。
 上記「セルの値が」の場合はＯＫですが、「数式が」の場合でないと目的達成出来ません。
 色を変える条件「数式が」にあるセルの文字とあるセルの数値にしている為です。
 きっとマクロ組めば出来るのでしょうが、、。
 （ＳＨＩＮＯＫＡＺ）

 その「数式が」の数式をどのセルに入れているのかを書かれてみてはどうでしょう。
 おそらく数式だけで解決できるはずです。

 （川野鮎太郎）

 数式が　=AND(F5="TMULA",O2=4)　の場合フォント赤、セル黄色　にしているんですが
上記よく意味がわかりません、どこに何を書けばよいのでしょうか？
よろしくお願いします。
（ＳＨＩＮＯＫＡＺ）

 合計したいということですので、少なくとも

 ◆どのセルにその数式が入っているか
 ◆その範囲はどの範囲になるのか、

 という情報が必要です。

 上記情報が出揃えば、数式はおのずと導かれるでしょう。

 (ROUGE)

 衝突。
 ROUGEさんも書かれていますが、

 上記の数式で条件を計算させたいセル範囲はどこでしょうか。
 例えば、F1～F10に　TMULA　が入ってて、O1～O10に　4　が入っているであれば、
 =SUMPRODUCT((A1:A10="TMULA")*(O1:O10=4))　などです。

 （川野鮎太郎）

 ROUGEさん、川野鮎太郎さん　ありがとうございます。
S6のセルの数式　=SUMPRODUCT((F5="TMULA")*(O2=4))でやってみました。
数値の色も赤になりましたがやはり合計は０のままです。
ちなみに、赤文字見に行く範囲　=UFClrSumfc(P3:S8,3)　ですが
P3:S8のセルをコピーして空いてるセルに貼り付けると赤文字が　黒に変わります。
（SHINOKAZ)

 で、私の質問の回答は？
 どのセルにその条件付書式が設定されていますか？
 どの範囲の内容を合計したいのですか？
 (ROUGE)

 条件付書式が設定されているのは、P3からS8で
 合計したいのはやはりP3からS8でです。
 （SHINOKAZ)

 一つ目の質問の回答がありませんが、、、
 P3に　=AND(F5="TMULA",O2=4)　が入っているという理解でよろしいですね？

 =SUMPRODUCT((F5:I10="TMULA")*(O2:R7=4))

 でどうでしょうか。

 (ROUGE)

ながなが書いて更新したら衝突で消えてしまいました、とほほ。
O３からO8まで装置の機種、P3からS3までは人のクラスが記載されています。
テスト等やってP4からS8に点数が入ります。
ただ計算したいのは本人が選んだ2機種でなお且つ本人のクラスが合った所ですので
その部分を色を変えどこかのセルに自動計算結果を出したいのです。3箇所のセルの合計
です。そこで試しにS6のセルの数式　=SUMPRODUCT((F5="TMULA")*(O2=4))でやってみましたがNGでした。
追記ですがO2にはクラス１から４が表示しています。

 ＞S6のセルの数式　=SUMPRODUCT((F5="TMULA")*(O2=4))でやってみました。
 と言う記述が何度か出てきますが
 川野鮎太郎さんと、ROUGEさんが書いて居られる式を
 よく見て下さい。

 お二方の式は
=SUMPRODUCT((F5:I10="TMULA")*(O2:R7=4))
             ~~~~~~           ~~~~~
 範囲が設定されていますが、お気づきでしょうか？

 それから、セル番地が曖昧ですね。

 ＞O３からO8まで装置の機種
 ＞P3からS3までは人のクラス
 ＞P4からS8に点数
 ＞O2にはクラス１から４

 ＞数式が　=AND(F5="TMULA",O2=4)
 この条件付書式がどこのセルに設定されている物か分かりませんが
 もしもS6に設定されているなら
 S7セルの条件付書式の数式は「=AND(F6="TMULA",O3=4)」
 ですよね。
 O3は「クラス」ではなく「装置の機種」が入っているのですよね。
 これで希望する所に色が塗られているのですか？

 言葉だけで説明するのは難しいと思うので
 O2:F8の範囲をコピーして
 こちらへ貼り付けてみてはどうでしょう。

 また、P3セルに設定してある条件付書式の数式を
 コピーしてこちらに貼り付けて下さい。

 (HANA)

 SUMPRODUCTについて一度↓を訪問しては？
http://www.excel.studio-kazu.jp/lib/e3h/e3h.html

お世話になります。
http://www.excel.studio-kazu.jp/lib/e3h/e3h.html
はじっくり見させていただくとしまして、

O2:F8の範囲は、

 　
   O       P       Q        R         S
　
2  4	M3	M2	M1	J3  ←クラスでJ3が4、M1が3、M2が2,M3が1です
3  A	1	1	1	1　　　O2に本人が入れたクラスが自動で表示。
4  B	1	1	1	1
5  C	1	1	1	1
6  D	1	1	1	1
7  E	1	1	1	1
8  F	1	1	1	1　＊今クラス４で機種はＥとＦ選択しています
　　　　　　　　　　　　　　　　　　　　　のでＳ７とＳ８が赤になってます。

　↑機種

P3セルに設定してある条件付書式の数式は

Ｐ３条件１の数式　=AND(F5="Ａ",O2=1)　　←Ｆ５に1機種目を表示
Ｐ３条件２の数式　=AND(F7="Ｂ",O2=1)　　←Ｆ７に2機種目を表示

　
以上です、よろしくお願いします。

（ＳＨＩＮＯＫＡＺ）

 正直、何がなんだか分からなくなりました。
 条件に設定されているF列には何があるんですか？
 条件１と条件２では参照しているアドレスがF5、F7と異なりますが、なぜですか？
 以前提示された　O2　の条件は　4　でしたが、今回　1　になっていますが、どちらが本当ですか？
 正確な情報がないと、正確な回答は得られませんよ？
 (ROUGE)

 あっっ、OよりFの方が左側ですね。。。

 訂正します
 ＞O2:F8の範囲
 ではなく
 ●F2:S8の範囲
 の情報が必要なのです。

 ですが・・・なんとなく
 条件付書式の設定の数式を
 各セルで変えて居られるように思います。
 ので条件付書式の設定に付いても確認ですが
 　◆「P4には、又新たに条件付書式の数式を作成し
 　◆　その式が入っていたりしませんか？」

 ＞今クラス４で機種はＥとＦ選択しています
 この情報がF列に入っていそうに思いますが
 F列にはどの様にして入力（或いは表示）されているのですかね？

 そのご説明から式を組み立てた方が良いように思います。

 (HANA)

ROUGEさん
分かりにくくてすいません。
F列のF5とF7には本人が選んだ機種が表示してます。
F5は基本的に1機種目でF7は2機種目です、ただ例えばF5にＡ、もしくはF7にA
を入れてもＡの機種の数値が赤になるようにしている為です。

Ｐ３は=AND(F5="Ａ",O2=1)と=AND(F7="Ａ",O2=1)で
Ｑ３は=AND(F5="Ａ",O2=2)と=AND(F7="Ａ",O2=2)で
Ｒ３は=AND(F5="Ａ",O2=3)と=AND(F7="Ａ",O2=3)で
Ｓ３は=AND(F5="Ａ",O2=4)と=AND(F7="Ａ",O2=4)です。
Ｐ４は=AND(F5="Ｂ",O2=1)と=AND(F7="Ｂ",O2=1)と続いていきます。

HANAさん
F2:S8の範囲は色んな情報が入っていますので実ファイル見ていただくしか
ありません。
F5、F7の装置の表示は機種ボタンでマクロ動かして機種を表示させています。

これで良いでしょうか？

（ＳＨＩＮＯＫＡＺ）

 だとすれば、

 P3の条件付書式の数式は、

 =AND($F$5=$O3,$O$2=COLUMN(A$1))

 のようになるのではありませんか？

 で、いったい何の合計を求めたいのでしょうか？

 (ROUGE)

 おっと、ROUGEさんと衝突。
 取り敢えず載せておきます。

 やはり、条件付書式の設定方法を
 個々で変えて居られたのですね・・・。

 因みに
 ＞3箇所のセルの合計です。
 って事ですがF9にもう一台有ったりするのですか？

 現在の情報だけだと
=SUMPRODUCT(((O3:O8=F5)+(O3:O8=F7))*(COLUMN(A1:D1)=O2),P3:S8)
 の様にすれば求まりそうに思います。

 なお、条件付書式の条件式は
=AND(OR($F$5=$O3,$F$7=$O3),$O$2=COLUMN(A1))
 一つで良いかもしれません。

 A3:S8セルを選択し、数式に上記式を設定します。

 (HANA)

色々とありがとうございます。
ご回答いただいている内容なんですがもう一度確認させてください。
あるセルに、=UFClrSumfc(A1:A5,3)と入れればマクロでUFClrSumfcの定義
がされていればA1:A5の中の赤色の数値を合計してくれます。
ただ、条件付き書式設定での方法で数式を使い数値の色を変えても赤色を認識してくれません、数値を選んでフォントの色を赤に上書きすればＯＫです。
そこで例えば数式=AND(F5="Ａ",O2=4)　を=SUMPRODUCT(((O3:O8=F5)+(O3:O8=F7))*(COLUMN(A1:D1)=O2),P3:S8)にするか（＊これって条件付書式の条件式のことじゃないんでしょうか？）=AND(OR($F$5=$O3,$F$7=$O3),$O$2=COLUMN(A1))にすれば
赤色を、=UFClrSumfc(A1:A5,3)で認識出来るという事でよいのでしょうか？
（ＳＨＩＮＯＫＡＺ）

 カラー関数は今回不要です。
 条件付書式で数式で条件を設定しているということは、その数式を基に計算ができます。
 あとは、計算式をどのように組むかにかかってきますが、規則性を持たせた条件付書式であれば、
 一括して処理が可能になりますので、そのような提案を致しました。
 まず、その辺りの理解はよろしいでしょうか？

 HANAさん提示の数式　=SUMPRODUCT(((O3:O8=F5)+(O3:O8=F7))*(COLUMN(A1:D1)=O2),P3:S8)

 をそのまま入力してみてください。
 （もしかしたら、最後の　,P3:S8　は不要かもしれませんが）

 戻り値はご希望通りではありませんか？

 (ROUGE)

 あらら、またROUGEさんと衝突です☆
 取り敢えず、載せておきます。（笑）

 ん？どの範囲の合計を出したいのですか？

 「=UFClrSumfc(A1:A5,3)」と言うことは、A1:A5の範囲の合計ですか？
 SUMPRODUCT関数を使用した物は、最後が「P3:S8」に成っていますよね。
 この範囲の合計を出します。

 UFClrSumfcに変わる関数の提案が　SUMPRODUCT関数を使用した物です。

 その後の =AND(OR　で始まっている物が
 現在各セルに設定されている条件付書式の数式に変わる数式の提案です。

 それぞれのセルに一つずつ条件付書式の式を設定しなくても
 P3セルに上記数式で設定をして
 その書式をコピーしてもらうだけで
 同じ結果が得られるのではないかと思います。

 ・・・ただし、ご説明と同じ動きしかしませんが。

 これらの式は　UFClrSumfcが使えるように成るための設定ではありません。
 UFClrSumfcは
 　条件付き書式設定での方法で数式を使い数値の色を変えても
 　赤色を認識してくれません
 ので今回は使用できないのです。

 (HANA)

色々ありがとうございました。
回答いただいた内容もう一度じっくり拝見し、他も調べやってみます。
結果は来週お知らせいたします。
長い間、お付き合いいただきありがとうございました。（もう頭が動きません）
（ＳＨＩＮＯＫＡＺ）

  >理屈だけの話ですが、、、
  >条件付き書式で色がついているなら、「色」で合算の可否を判断させず、
  >条件式の真偽で判断させればいいのではないですか？

  と言ってはみたものの、理屈を具現化するのは、かなりしんどい(トホホ)
 （ ConversionFormulaを2回も使用する羽目になってしまった。他にうまい手があってもよさそうな気がします ）

 ※1 [ 大前提 ]  条件付き書式の「条件」は全て『数式が→』で設定するものとします。
                 従って、『セルの値が』で設定できるものも「=自セル=○○」と設定する。

 ※2 [動作確認]  XL2002でＯＫ。XL2007はＮＧです。(条件付き書式の機能が大幅に変った事による）
    　　　　　　 複雑な条件式にEvaluate関数が適応できるかは未検証。

 ※3 [数式の例]  =UDFcnd(A1:A5,3)

 標準モジュールに以下のコードをコピペ

 Function UDFcnd(Rng As Range, clrIdx As Long)　’条件付き書式のフォント色で合計(XL2007は不可)
 Dim Cel As Range
 Dim Fmcdn As FormatCondition
 Dim Rslt, Test
 Dim strFmla As String       '修正後条件式
 Dim rr As Long, cc As Long  'アクティブセルとのオフセット差

     Application.Volatile

     For Each Cel In Rng
         If IsNumeric(Cel.Value) Then
             If Cel.FormatConditions.Count Then
                 rr = ActiveCell.Row - Cel.Row
                 cc = ActiveCell.Column - Cel.Column

                 For Each Fmcdn In Cel.FormatConditions
                    strFmla = Application.ConvertFormula(Fmcdn.Formula1, _
                                         xlA1, xlR1C1, , Cel.Offset(rr, cc))
                    strFmla = Application.ConvertFormula(strFmla, xlR1C1, xlA1, , Cel)

                    Test = Evaluate(strFmla) '条件式を評価

                    If Not IsError(Test) Then
                         If Test Then
                             If Fmcdn.Font.ColorIndex = clrIdx Then
                                 Rslt = Rslt + Cel.Value
                             End If
                             Exit For
                         End If
                    End If
                 Next
              End If
          End If

     Next
     UDFcnd = Rslt
 End Function

  (半平太)

 (ROUGE)さん、 (HANA)さん、そして (半平太)さん、教えていただいた
やり方でどちらもうまくいきました。それから（川野鮎太郎）さん。

本当にありがとうございました。
（ＳＨＩＮＯＫＡＺ）

コメント返信：

[ 一覧(最新更新順) ]

[[20081023172945]] 『Color関数 色のついた文字で合計を求めるUFClrSu』（SHINOKAZ） ページの最後に飛ぶ

[[20081023172945]]　『Color関数　色のついた文字で合計を求めるUFClrSu』（SHINOKAZ）　ページの最後に飛ぶ