Hatena::ブログ(Diary)

@jitteの日記 RSSフィード

2006-09-10

[] セル範囲の共通部分を求める

セル範囲式を空白でつなげるだけ。=SUM(B1:D10 A5:E8)のように書ける。=SUM(B5:D8)と同じ結果となる。

2006-08-08

[] TEXT()関数

何度も調べては忘れるExcelのワークシート関数メモ。

TEXT(値, 表示形式)

=TEXT(0.03,"0.0%")と書くと"3.0%"と表示してくれる。その他の表示形式は下記あたりを参照。

標準の書式だけで表現できない形式、例えば入力した数値(通貨)の末尾に必ず「円」を付加したいとか、正数は黒字/負数は赤字で表記したい、などといったような独自の書式を表現したいこともあるだろう。このような場合は、ユーザー定義の書式を用いると便利である。

@IT:Windows TIPS -- Tips:Excelでユーザー・カスタムの書式設定を定義する

2006-08-04

jitte2006-08-04

[][][] 機種依存文字をチェックする

微妙に需要があるみたいだったので機種依存文字があるセルをチェックするツールを作ってみた。

  • 空白、Latin文字、全角英数記号、JIS第一水準、JIS第二水準はOKとする
  • 機種依存文字がみつかったセルの背景色を変える
  • 左端に絞り込み用のフラグを書き込み、オートフィルタをかける

使い方:exeにしてデスクトップ等に置き、csvやExcelファイルをドロップすればOK。


checkjis.rb

#! /usr/bin/ruby -Ks

require 'excel_lib'
require 'rubygems'
require 'win32/clipboard'

$KCODE = 's'

class Excel
  def initialize(visible = true)
    @raw_object = connector('Excel.Application')
    @raw_object.visible = visible
    if block_given?
      yield self
    end
  end

  def connector(app_name)
    begin
      raw_object = WIN32OLE.connect(app_name)
      @connected = true
    rescue
      raw_object = WIN32OLE.new(app_name)
    end
    WIN32OLE.my_const_load(raw_object, self.class)
    raw_object
  end

  class Worksheet
    def used_range
      Range.new(@raw_object.usedrange)
    end
  end

  class Range
    def copy_value
      self.copy
      value = Win32::Clipboard.data
      Win32::Clipboard.empty # 内容を消しておく
      value.split("\r\n").map do |s| s.split("\t") end
    end

    def paste_value(aa)
      Win32::Clipboard.set_data((aa.map do |a| a.join("\t") end).join("\r\n"))
      self.worksheet.paste('Destination' => @raw_object)
      Win32::Clipboard.empty # 内容を消しておく
    end
  end
end

class Excel
  class CheckJIS
    JIS_1_2 = %r(\A[
      \x20-\x7E  # latin
      \xA1-\xDF  # kana
       -╂      # non kanji
      亜-腕      # jis1
      弌-滌漾-熙 # jis2
    ]*\Z)xo

    def initialize(file)
      return unless file && File.exist?(file)
      @xls = Excel.new
      @wkb = @xls.open_book(file)
      check()
      autofilter()
    end

    def check
      @sht = @wkb.sheets(1)
      a = @sht.used_range.copy_value
      b = a.map { |r| r.map { |e| jis_1_2?(e) } }
      @sht.range('A:A').insert
      b.each_with_index do |r, y|
        next if r.all?
        @sht.cells(y + 1, 1).value = 1
        r.each_with_index do |e, x|
          @sht.cells(y + 1, x + 2).interior.colorIndex = 6 unless e
        end
      end
    end

    def autofilter
      @sht.range('A1').autofilter('Field' => 1, 'Criteria1' => '1')
    end
    
    def jis_1_2?(s)
      JIS_1_2.match(s) ? true : false
    end
  end
end

if __FILE__ == $0
  Excel::CheckJIS.new(ARGV[0])
end
  • 例によってexcel_lib使用。自作便利関数をコピペ。
  • win32/clipboardはgemになっていたのでgem install win32-clipboard
  • rubyscript2exeだと正規表現がエラーになるのでExerbを使った

rakefile.rb

debug = ''
script = 'checkjis.rb'
argv = 'sample.xls'

task :default => [:run]

task :run do
  ruby %(#{debug} #{script} #{argv})
end

task :debug do
  debug = '-d -r debug'
end

task :exe do
  ruby %(-rexerb/mkexy #{script} #{argv})
  sh %(exerb -c gui checkjis.exy)
end

exeを作るときはrake exeで。

2006-05-12

[][][] ExcelでCSVをそのまま開く

ExcelでCSVファイルを開くと、0で始まる数値や日付形式と解釈可能なデータが自動変換されてしまい、うっかり保存すると元の情報が失われてしまうことがあります。例えば

sample.csv

001,1/2/3,03-14,01234567890

Excelで開いて保存すると

1,2001/2/3,3月14日,1234567890

となってしまいます。

これに対し、自動変換させずに開く方法としては、テキストインポートウィザードを使う方法が知られていますが、意外と手順が面倒です。

拡張子が.txtの場合はファイルを開くだけで自動的にウィザードが起動するのですが、.csvの場合はいったん.txtにリネームするか、「データ→外部データの取り込み→テキストファイルのインポート」を使って取り込む必要があります*1。前者の拡張子を変更する方法は単純に考えて面倒なだけでなく、エクスプローラの設定で「登録されている拡張子は表示しない」のチェックをはずしておく必要があります。一方、後者のデータインポートの方法だとフィールドにインポート属性が付いてしまうので、ウィザードの途中でプロパティを変更して属性を付かないようにする手順が増えてしまいます。

VBAでマクロを書けばよさそうなものですが、Excelに記録させてみると以下のようなコードであることがわかります(わかりやすいように主要な部分以外は省略)。

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\rawcsv\sample.csv", Destination:=Range("A1"))
        .AdjustColumnWidth = True
        .TextFileTabDelimiter = True
        .TextFileCommaDelimiter = True
        .TextFileColumnDataTypes = Array(2, 2, 2, 2)
        .Refresh
        .Delete
    End With

ここで問題なのがArray()の部分です。読み込むCSVのカラム数に合わせてそれぞれの形式を指定する(2=文字列のようです)のですが、カラム数は読み込んでからでないとわかりません。1回読み込んでからカラム数を調べて再読み込みするのかな、と思っていたのですが、実は、Excelの最大カラム数=256に合わせて配列を指定すればよいことがわかりました。

VBAだと配列の初期値を一括指定できないので、Forループで代入してやる必要があります。これ、Rubyに慣れた体には大変な苦痛です。なにせArray.new(256, 2)と書けばいいところがDim v(256) As Long, i As Long: For i = 0 To 255: v(i) = 2: Next iこれですから。

そこでRubyの登場ですよ。わあ、強引。

冗談は抜きにしても、VBAで書く場合、マクロはCSV側には保存できませんから、マクロつきのファイルを別に用意するか、アドインにしてやる必要があります。ところが、セキュリティ警告の出方がバージョンによって違うので、他の人に使ってもらおうと思うとドキュメントを書くのも一苦労です。また、アドインの場合、メニューなりツールバーにボタンをつけるなりしてやらないと使いづらいのですが、アドインを使わなくなったときに後始末をするとかなんとかで、とにかく面倒です。面倒なの!

ということで(前置き長すぎ)、VBAを以下のようなコードに書き直します。

rawcsv.rb

require 'excel_lib'

file = ARGV[0]
exit unless file && File.exist?(file)

app = Excel.new
app.displayAlerts = true
begin
  wks = app.workbooks.add.sheets(1)
  qt = wks.queryTables.add(
    'Connection' => "TEXT;#{file}",
    'Destination' => wks.range('A1'))
  qt.adjustColumnWidth = true
  qt.textFileTabDelimiter = true
  qt.textFileCommaDelimiter = true
  qt.textFileColumnDataTypes = Array.new(256, 2)
  qt.refresh
  qt.delete
rescue
  app.quit
end

Excelの制御には以前も紹介した「RubyでExcel」を使っています。site_libあたりにインストールしてください。これをrubyscript2exeで実行形式にします。

rubyscript2exe --rubyscript2exe-rubyw rawcsv.rb c:\sample.csv

できあがったrawcsv.exeか、ショートカットをデスクトップにおいて、そのアイコンに目的のCSVをドラッグ&ドロップするとそのままで開いてくれます。

追記:

よく見たらexcel_libの機能はほとんど使っていませんね。begin以前を以下のようにすれば標準のwin32oleだけで動作するようです。

require 'win32ole'

file = ARGV[0]
exit unless file && File.exist?(file)

app = WIN32OLE.new('Excel.Application')
app.visible = true

追記:Rubyって何?という人向けには、以下に普通のやりかたが書いてあります。

*1:Excel2000の場合。Excel2003では、メニュー名称が違います

2006-04-30

[][][] RubyでExcel三昧

その後半年は、ひたすらこれを使い倒しました。

ちょうど仕事でピボットテーブルを量産しないといけなかったので、そのためのラッパークラスをごりごり書いたりしてました。ピボットテーブルは使い込むと便利なのですが、なかなか期待通りの動作をしなかったりでコーディングは結構大変でした。おかげで人に見せられる完成度にはなってないのですが…。

ところで、Excelに書き込みをする際、100x10000セルの書き込み、なんてのをセルごとにやると非常に遅くなるんです。当たり前ですけど。WIN32OLE関連でいろいろ検索してみましたが、なかなか高速化方法は見つかりませんでした。そこで、クリップボードを経由して一括張り付ける方法にしてみたところ、非常に高速化できました。ここでは、このコードを紹介しておきます。

require 'excel_lib'
require 'win32/clipboard'

class Excel
  class Range < Excel_Wrapper
    def copy_value
      self.copy
      value = Win32::Clipboard.data
      Win32::Clipboard.empty # 内容を消しておく
      value.split("\r\n").map do |s| s.split("\t") end
    end

    def paste_value(aa)
      Win32::Clipboard.set_data((aa.map do |a| a.join("\t") end).join("\r\n"))
      self.worksheet.paste('Destination' => @raw_object)
      Win32::Clipboard.empty # 内容を消しておく
    end
  end
end

win32/clipboardを使い、配列の配列を引数として渡すと一括読み出し、一括書き込みができます。セル内容に改行が含まれる場合は各要素を""で囲めばOKです。

「RubyでExcel」の作者の方には、本当に感謝しています。^^;