« Windows 8.1が無料アップデートで提供 | トップページ | Google I/Oで新Googleマップ発表 »

2013年5月16日 (木)

Excelで時刻にあわせたセルの選択をするための関数

な・・・何をいってるのかわからねえだろうが、ありのままを話すぜ・・・

私の部署では、ネットワークライセンスで使用数の限られたあるソフトを使っており、それを予約表で管理してます。

ところが、最近そのソフトの使用頻度が上がり、時間区切りで使用制限をかけております。

それを管理する予約表はこんな感じ。Excelでかかれています。

Excelindex1

A~Eさんがいて、トータル本数が3本の場合、何時に誰が使うかをセルに1という数字を入れて予約させております(実際にはもっと複雑)。

で、現時点で誰がこのソフトを使っているかを調べるマクロというのが既に存在してます。

ところが、現時刻の予約と今の使用者があっているか?を整合するのが大変。

上のように数が少なければいいんですが、実際には100人近くいるので、合意した予約どおりに使っているのかどうかがまったくチェックできないという状況でした。

そこで、今の時刻から欲しい行を自動的に選んでくれるように工夫してみました。

例えば、今の時刻が10時だったら、上の表の時刻”10”のところのA~Eさんの数字を拾ってくるようにすればいいわけです。

これを行うために使ったのは”INDEX”という関数。

=INDEX(A1:Z1 , 3)

と記入すると、セル A1からZ1までのうち3番目のセルの中身を抽出してくれます。

上の例で行くと、セル C3からM3 に書かれた数字がAさんの予約の有無を表してます。

今が10時だったら、セルC3~M3の間の3番目のセルが欲しい情報ということになります。つまり現在の時刻から7引いた数が自動的に取り出せればいいわけです。

では、どうやって現在の時刻10時の”10”という数字を取り出すのか?

Excel上で現時刻を抜き出す関数は”Now()”というのがあります。

=Now()”とExcelのセルに書き込むと、”2013/5/10 17:09”のように、現在の時刻を表した日時が出てきます。

これの”セルの書式設定”を”数値”に変更すると”41404.713611・・・”という数字に変わります。

これは1900年1月1日からの日数を示しています。

ここから時間を抜き出す方法ですが、  (=Now()を書き込んだセル) - INT( =Now()を書き込んだセル ) という計算をさせて、小数点以下の数字だけを抜き出します。

これに24をかけてから小数点以下を切り捨て(INT関数を使う)と、時刻を表す数字に変わります。

下では17時の例ですが、”17”という数字が現れてます。

Excelindex2

あとはINDEX関数の○番目を抜き出すという数字のところにこの時刻の数字を入れてやれば、現時刻での予約表の数字を抜き出してくれます。

こうして別マクロで抜き出した使用状況と、予約の状況を照合することが簡単にできるようになりました。

ちょっとわかりにくい話でしたが、Excelの関数だけでもかなりのことが可能だということがよくわかります。

Microsoft Office Excel 2013 通常版  [パッケージ]

« Windows 8.1が無料アップデートで提供 | トップページ | Google I/Oで新Googleマップ発表 »

パソコン系」カテゴリの記事

コメント

表の上端の行が「左から右に時刻が増加」しているのなら
=HLOOKUP(時刻, $表の範囲,表の上から何行目のセルを返すか, 0または1)
とかありますよ。

つまり
 =HLOOKUP(HOUR(NOW()), $C$3:$M$9, 2, 1) でAさんの値が返ります。

 エラー表示が嫌なら
 =IF(ISERR( 上の式 ), "", 上の式)
 とやればエラーならヌル文字になります。

 他のセルからこのセルの結果がエラーかどうかを確認するには
 セル内がテキストかどうかを確認すればよく
 =IF( ISTEXT( 参照先セル ), "エラーでした", "正常です" )

 ところで分単位のCSVデータから毎正時のデータを拾い出すとき
 =VLOOKUP(DATE(年, 月, 日) + TIME(時, 0, 0 ), $表の範囲, 何番目の列, 0か1 )
を0時~23時(あるいは01時~24時)の24行×列数分用意して使lっています。

こんにちは、enuteaさん。

こちらの方が一行でいけそうですね、ありがとうございます。会社で早速試して見ます。

ただこの仕組みにより部署内の仲がちょっと悪くなってしまいました。見える化もほどほどにした方が良いですね。本来こんなマクロ・関数に頼る前に、ライセンスを増やすのが一番なんですけどね。

 そもそもは、
"池"の水位が1cm変動すると
水が何立方メートル貯まるか?(あと何立方メートル貯める空間があるか?)
なんてことをマルチプランとかロータス1-2-3でやってた頃
職場の先輩がVLOOKUP()で簡単にやっちゃったから
それ以来なんですよ。

 "池"に流れ込む水の量は、そのままでは捉まえ難いのですが
"池"から流れ出す水の量は分るし、水位の変化も分るので
"池"に流れ込む水の量を逆算し、あと何分(何時間)後に"池"が満タン(空っぽ)になる
って算出してたんですよ。

で、エクセルの日時分秒は「連番」ですので連番を検索値に使うと
その時刻の各項目の測定値が取り出せる
結構重宝してる関数です。
あっ、そうそう、検索対象は、時刻のように増加する並びの必要があります。

Excelには、他に[V]も[H]も付かない[LOOKUP]関数ってのがありますね。
大昔(まだWindows用Excelが発売される前のMac用Excelを使ってた頃 (^^;A)、解説本を読んで、「Lotus1-2-3との互換用に入ってる関数で、MSでは推奨しておらず、VLOOKUPかHLOOKUPを使うように」と書かれていたのを覚えてます。
LOOKUP関数は少なくとも2003ではまだ残っていますが、VLOOKUPやHLOOKUPほど用法が簡単では無いようで、今でもHELPを読んでも使い方が分かりませんw

以上、無用な雑学&横槍で失礼しました。

こんにちは、enuteaさん。

そんな使われ方もあるんですね、Excel。関数も奥が深いですね。

私は会社でOffice系のソフトを使う場合は、ExcelとPowerPoint、たまにAccessしか使いません。Wordなにそれおいしいの?状態です。すっかりExcelがWord代わりになっております。

マクロの自由度の高さもExcelがピカ一ですよね。PowerPointのマクロがあれほど使えないとは思わなかった・・・というか、PowerPointらしくないことをやらせようとしたのが悪いんですが。

こんにちは、緋呂々さん。

なるほどLotus123時代の関数ですか。MS-DOSの頃はLotus123でしたが、当時はそこまでは使いこなしていなかったので。

最近のOfficeも安くなりましたよね。Excel 2002を持ってますが、今思えば一番高い時期に買ってしまった気がします。その後アップデート版が買えて安く入手できるようになるからとわざわざ新規版を買ったものの、結局その後のバージョンを買っておりません。

123もマルチプランもマクロは、キーボード操作を記述したものでしたから、実行中のマクロ自身がマクロを書き換えたり
マクロの正体は、マクロ用の「名前」を設定したセルに「キー」の文字を並べた【文字列】にしか過ぎませんでした。
だからマクロである文字列を関数の組合せで数式を作っておくと
与えたデータでマクロがダイナミックに切り替わるなんてこともやってました。
Excelのマクロの正体はVBAですからプログラミングの基本を押さえる必要があるのがちょっとだけ敷居が高くなるかなと思います。
だけどExcelからオブジェクト呼び出しでインターネットエクスプローラを呼び出し、webのデータを取り込めたりするのは便利です。

コメントを書く

(ウェブ上には掲載しません)

トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/510034/57347801

この記事へのトラックバック一覧です: Excelで時刻にあわせたセルの選択をするための関数:

« Windows 8.1が無料アップデートで提供 | トップページ | Google I/Oで新Googleマップ発表 »

当ブログ内検索

  • カスタム検索

スポンサード リンク

ブログ村

無料ブログはココログ