条件付き書式

以下に掲げるrubyスクリプトのサンプルは、Excel操縦用ライブラリ exlap.rb ver 1.12: exlap112.zipに同梱のものを利用するとの前提で書かれています。

Excelの条件付き書式には信頼性に欠ける面があると感じています。正しくスクリプトを書いているはずなのに、意図した結果が得られないことがあります。

私の乏しい経験ではありますが、次の３点に沿ってスクリプトを書くと、比較的思いどおりの結果が得られやすい感じがします。

Rangeに対して一括して条件付き書式を設定するのでなく、セル一つづつに対して設定する。
「セルが」(XlCellValue)でなく、「数式が」(XlExpression)を用いる。
Formula1におけるセル番地の記述は、A1形式でなくRC形式を用いる。

上の３点が妥当なものかどうか確信があるわけではありませんが、うまくいかない時は試してみて下さい。

fc01.rb: 性別欄の色分け。「セルが」を使用
fc01b.rb: 性別欄の色分け。「数式が」を使用
fc01c.rb: 氏名・性別の両欄の色分け
fc02.rb: 4以上・6以下を赤に。Formula2を用いる
fc02b.rb: 4以上・6以下を赤に。「数式が」を使用
fc03.rb: 平均値より小さい数を赤に
fc04.rb: 条件付き書式の「条件」の情報を出力

fc01.rb

#! ruby -Ks
  # 条件付き書式: 氏名と性別からなる表。性別欄の色分け。女を赤、男を青
  # XlCellValue(「セルが」)を使用：「数式が」ではない
require "exlap"
filename = "fc01.xls"
Exlap.new(filename) do |wb|
  ss = wb.fes  # 空のワークシートを選択
  rng = ss.Range("A1:B4")
  rng.Value = [%w(安部 男), %w(加藤 女), %w(佐々木 男), %w(田中 女)]
  ss.range_each(rng) do |cell|  # 領域内のセルを一つづつなぞる
    cell.FormatConditions.Delete  # 既存の条件付き書式を削除
        # ↑ この事例では既存のものがないので必要ないが参考まで
    fc1 = cell.FormatConditions.Add(XlCellValue, XlEqual, "女")
    fc1.Font.ColorIndex = 3  # 赤
    fc2 = cell.FormatConditions.Add(XlCellValue, XlEqual, "男")
    fc2.Font.ColorIndex = 5  # 青
  end
  wb.save
end

download fc01.rb

fc01b.rb

#! ruby -Ks
  # 条件付き書式: 氏名と性別からなる表。性別欄の色分け。女を赤、男を青
  # XlExpression(「数式が」)を使用：「セルが」ではない
require "exlap"
filename = "fc01b.xls"
Exlap.new(filename) do |wb|
  ss = wb.fes  # 空のワークシートを選択
  rng = ss.Range("A1:B4")
  rng.Value = [%w(安部 男), %w(加藤 女), %w(佐々木 男), %w(田中 女)]
  ss.range_each(rng) do |cell|  # 領域内のセルを一つづつなぞる
    fc1 = cell.FormatConditions.Add(XlExpression, nil, '=RC="女"')
    fc1.Font.ColorIndex = 3  # 赤
    fc2 = cell.FormatConditions.Add(XlExpression, nil, '=RC="男"')
    fc2.Font.ColorIndex = 5  # 青
  end
  wb.save
end

download fc01b.rb

fc01c.rb

#! ruby -Ks
  # 条件付き書式: 氏名と性別からなる表。氏名欄と性別欄の両方の色を変える。
require "exlap"
filename = "fc01c.xls"
Exlap.new(filename) do |wb|
  ss = wb.fes  # 空のワークシートを選択
  rng = ss.Range("A1:B4")
  rng.Value = [%w(安部 男), %w(加藤 女), %w(佐々木 男), %w(田中 女)]
  ss.range_each(rng, :YX) do |y,x|  # 領域内のセルをy,x座標数で一つづつなぞる
    cell = ss.cell(y,x)  # 現在注目セル
    sex = Exl::relrc([y,2], [y,x])  # 現在位置から見た性別欄の相対番地
            # ↑ sex => "RC[1]" | "RC"
        # 女性の場合の設定
    formula = "=#{sex}=\"女\""
    fc1 = cell.FormatConditions.Add(XlExpression, nil, formula)
    fc1.Font.ColorIndex = 3  # 赤
        # 男性の場合の設定
    formula = "=#{sex}=\"男\""
    fc2 = cell.FormatConditions.Add(XlExpression, nil, formula)
    fc2.Font.ColorIndex = 5  # 青
  end
  wb.save
end

download fc01c.rb

fc02.rb

#! ruby -Ks
  # 条件付き書式: 3×3領域に 1～9を書き込み、4以上・6以下を赤にする
  # XlCellValue(「セルが」)を使用：Formula2を用いる例
require "exlap"
filename = "fc02.xls"
Exlap.new(filename) do |wb|
  ss = wb.fes  # 空のワークシートを選択
  rng = ss.Range("A1:C3")
  rng.Value = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]
  ss.range_each(rng) do |cell|  # 領域内のセルを一つづつなぞる
    fc = cell.FormatConditions.Add(XlCellValue, XlBetween, 4, 6)
        # ↑ 最後の引数 6 が Formula2 の値
    fc.Font.ColorIndex = 3  # 赤
  end
  wb.save
end

download fc02.rb

fc02b.rb

#! ruby -Ks
  # 条件付き書式: 3×3領域に 1～9を書き込み、4以上・6以下を赤にする
  # XlExpression(「数式が」)を使用：Formula2は用いない
require "exlap"
filename = "fc02b.xls"
Exlap.new(filename) do |wb|
  ss = wb.fes  # 空のワークシートを選択
  rng = ss.Range("A1:C3")
  rng.Value = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]
  ss.range_each(rng) do |cell|  # 領域内のセルを一つづつなぞる
    formula = "=AND(RC>=4,RC<=6)"
    fc = cell.FormatConditions.Add(XlExpression, nil, formula)
    fc.Font.ColorIndex = 3  # 赤
  end
  wb.save
end

download fc02b.rb

fc03.rb

#! ruby -Ks
  # 条件付き書式: ３×３領域に乱数をセットして、
  # 各欄の値が平均値未満なら赤色にする。
require "exlap"
filename = "fc03.xls"
Exlap.new(filename) do |wb|
  ss = wb.fes  # 空のワークシートを選択
  rng = ss.Range("A1:C3")
  ss.range_each(rng) do |cell|  # 領域内のセルを一つづつなぞる
    cell.Value = rand(100) + 1  # 1～100の整数(乱数)
  end
  ss.Range("A5").Value = "平均値:"
  avg = ss.Range("B5")
  avg.Formula = "=AVERAGE(#{Exl::relrc(rng,avg)})"
      # ↑ Exl::relrc(rng,avg) => "R[-4]C[-1]:R[-2]C[1]"
  ss.range_each(rng) do |cell|
    formula = "=RC<#{Exl::relrc(avg,cell)}"  # セルの値が平均値未満
    fc = cell.FormatConditions.Add(XlExpression, nil, formula)
    fc.Font.ColorIndex = 3  # 赤
  end
  wb.save
end

download fc03.rb

fc04.rb

#! ruby -Ks
  # 条件付き書式の「条件」の情報を出力。Type, Operator, Formula1, Formula2
require "exlap"
keys = %w(Type Operator Formula1 Formula2)
types = {1=>'XlCellValue', 2=>'XlExpression'}
oprs = {1=>'XlBetween', 2=>'XlNotBetween', 3=>'XlEqual', 4=>'XlNotEqual',
    5=>'XlGreater', 6=>'XlLess', 7=>'XlGreaterEqual', 8=>'XlLessEqual'}
file_list = Dir.glob("./*.xls")
if file_list.size < 1  # xlsファイルがみつからない
  exit
end
puts '条件付き書式の「条件」の情報を出力'
Exlap.new(*file_list) do |wb|
  printf("WorkBook: %s\n", wb.name)
  wb.each do |ss|
    next  if ss.empty?
    printf("WorkSheet: %s\n", ss.Name)
    fc_hs = {}  # FormatConditionsの情報をHash形式でこれに記録
    ss.range_each do |cell|
      next  if cell.FormatConditions.Count < 1  # 条件付き書式がない
      addr = Exl::a1(cell)  # セルの相対番地 A1, B1 など
      ary = []  # 複数の「条件」をこれに記録
      cell.Activate
      cell.FormatConditions.each do |fc|
        fcs = []  # 一つの「条件」をこれに記録
        keys.each do |name|
          scr = "fcs << (fc.#{name}  rescue  nil)"
          eval(scr)
          if fcs.last
            case name
            when 'Type'
              fcs[-1] = types[fcs.last]  if types[fcs.last]
            when 'Operator'
              fcs[-1] = oprs[fcs.last]  if oprs[fcs.last]
            end
          end
        end
        ary << fcs
      end
      w = fc_hs[ary]
      fc_hs[ary] = !w ? addr : "#{w} #{addr}"
    end
    if fc_hs.size > 0
      fc_info = []
      fc_hs.each {|ary, addr|  fc_info << [addr, ary]}
      fc_info.sort.each do |addr, ary|
        puts addr
        ary.each do |fcs|
          str = "\t"
          keys.each_index {|i|  str += "#{keys[i]} #{fcs[i]},  " if fcs[i]}
          puts str.sub(/, +$/, "")
        end
      end
    end
    printf("\n")
  end
end

download fc04.rb

exlapのページへ戻る