やじゅ@アプリケーション・ラボ わんくま支局

目次

Blog 利用状況

ニュース

プロフィール

MSMVP

Excelのミリ秒表示が後0埋めになるについて

これは、Excel Advent Calendar 2014の12/4日の記事。

お仕事にて、Windows Server 2008R2 + Oracle11g の環境にシステム移行をしたのですが、既存アプリケーションは32bitであるため、Oracle client 64bit版と32bit版を共存して使用しております。
ある既存アプリケーションは、遅くても2秒以内で処理を終わらせるようになっているのですが、2秒超えることが何度か発生するようになりました。しかも平均の処理速度が以前より少し遅くなっている。
原因を調べるために、急遽、計測用にログ出力を仕組みました。その際にミリ秒を後でExcelの書式で3桁0埋めすればいいかと安易に0埋めしないままにしました。

出力された計測用のログを調べていくと、Oracleのコネクション接続だけで2秒を超えていたのです。※古いアプリ故にコネクションプーリングは未使用なんです。
2014/12/04 8:45:36.541,Oracle Begin
2014/12/04 8:45:48.601,Oracle End

ちゃんと調査するためにExcelで計測用のログを取り込んで、コネクション接続の差分を取ろうとしたわけです。
Excelでミリ秒を3桁で表示するには、ユーザー書式にて「hh:mm:ss.000」とすればいい。差分は=Oracle End - Oracle Beginで計算させる。
これで全行計算させてみると、#####と表示されて計算出来ないところが出てきました。
何故?  その行の時刻を見てみるとOracle End より Oracle Begin の方が値が大きくなっておりました。
2014/12/04 8:50:01.890,Oracle Begin
2014/12/04 8:50:01.541,Oracle End
元データを見てみると、本来のOracle Beginの時刻は2014/12/04 8:50:01.89とミリ秒は2桁だったのです。
ユーザー書式にて「hh:mm:ss.000」とすると、ミリ秒は前0埋めではなく後0埋めなのです。それっておかしくないですか?
他にもミリ秒が1桁の場合は、4→400となってしまっているのです。

元データのミリ秒を前0埋めにする方法が直ぐに思いつかず、かといって手作業で修正するには時間がかかりすぎる。
その時は、同僚に頑張ってもらってLog Parser側で書式を修正する関数を作成してもらいました。

後日、自分の方でExcel上でもミリ秒を前0埋めするセル関数を作りました。
Excelで時間1:2:3.5を書式hh:mm:ss.000にて、01:02:03.005とするには
=TEXT(LEFT(A2,FIND(".",A1)-1),"hh:mm:ss")&"."&TEXT(MID(A1,FIND(".",A1)+1,3),"000")とする。

また、差分の平均を出そうとしても書式が時刻形式だと計算してくれません。
Excelでミリ秒の差分の合計や平均を出したい場合、ミリ秒のセル値に86400(24*60*60)を掛けた別セル(例 ”=C1*86400")を用意して、その列で計算させます。


ちなみにサクラエディタで正規表現を使ってミリ秒に前0を付ける方法
2014/12/04 8:45:36.541,
2014/12/04 8:45:44.89,

2014/12/04 8:45:36.541
2014/12/04 8:45:44.089,
とミリ秒が2桁のところのみ先頭に0を追加したい場合
検索 \.(\d)(\d),
置換 \.0$1$2,
検索で()を付けるとグループ化される。今回は2つグループ化した。
置換でグループ化された変数の$1と$2を使う。今回の場合、$1に8、$2に9が入る。
[サクラエディタ]正規表現で検索した結果の一部を置換する方法
http://oss-lab.net/?p=906


Oracleのコネクション接続だけで2秒を超えるのが何度か発生していた問題ですが、下記の質問内容にてOracle11g Client 32bit→Oracle10g Client 32bitにしたら速くなったとのことで、試すと本当に速くなりました(正常に戻ったが正解か)。一体なんなんでしょうかね。
oracle11g 11.2.0.3(64bit) +OracleClient 11.2.0.3(32bit)のDB接続が遅い

投稿日時 : 2014年12月4日 2:19

コメントを追加

# I constantly spent my half an hour to read this website's articles daily along with a mug of coffee. 2019/05/05 2:49 I constantly spent my half an hour to read this we

I constantly spent my half an hour to read this website's articles daily along with a mug of coffee.

# We are a group of volunteers and starting a new scheme in our community. Your website offered us with valuable information to work on. You've done an impressive job and our entire community will be thankful to you. 2019/05/08 17:19 We are a group of volunteers and starting a new sc

We are a group of volunteers and starting a new scheme in our community.
Your website offered us with valuable information to work on. You've done an impressive job
and our entire community will be thankful
to you.

# great issues altogether, you just gained a new reader. What might you recommend about your submit that you made a few days ago? Any positive? 2019/05/10 3:49 great issues altogether, you just gained a new rea

great issues altogether, you just gained a new reader.
What might you recommend about your submit that you made a few days ago?

Any positive?

# I think this is one of the most important information for me. And i'm glad reading your article. But should remark on few general things, The web site style is perfect, the articles is really excellent : D. Good job, cheers 2019/05/28 14:42 I think this is one of the most important informat

I think this is one of the most important information for me.
And i'm glad reading your article. But should remark
on few general things, The web site style is perfect, the articles is really excellent : D.

Good job, cheers

# wonderful issues altogether, you just gained a logo new reader. What might you suggest in regards to your submit that you made a few days ago? Any positive? 2019/06/05 10:01 wonderful issues altogether, you just gained a log

wonderful issues altogether, you just gained a logo new reader.
What might you suggest in regards to your submit that you made a few days ago?
Any positive?

# Hi, the whole thing is going perfectly here and ofcourse every one is sharing information, that's actually good, keep up writing. 2019/06/05 11:11 Hi, the whole thing is going perfectly here and of

Hi, the whole thing is going perfectly here and ofcourse every one is sharing
information, that's actually good, keep up writing.

タイトル  
名前  
URL
コメント