Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Professional : Excel 2010)
表示形式に関して
投稿日時: 17/10/21 13:47:02
投稿者: 鶴の舞

 いつも助かっています。
 今回、手入力で 2000件 入力とenterを繰り返したので、
 改善策をお聞き致し度、投稿致しました。質問は短いのですが、前提条件を少し詳しく下記ました。
 長くて恐縮です。
 
 関数で処理した後の表示形式について、質問です。
駐車場の駐車時間を計測して 時間ごとにグループ分けをするデータを作っています。
(1)こちらがテーブル
項番    滞留時間
1    15分以内
2    30分以内
3    1時間以内
4    2時間以内
5    2時間超  
(2)こちらがデータです
 日付    入車    出車 滞留時間1    滞留時間2  区分1 手入力
6月12日    8:30    16:35    8:05    2時間超    5    5 
6月12日    8:30    8:53    0:23    30分以内   2    2
6月12日    8:30    9:01    0:31    1時間以内   3    3
 
=IF(U3<= "0:15","1",IF(AND(U3>"0:15",U3<="0:30"),"2",IF(AND(U3>"0:30",U3<="1:00"),"3",IF(AND(U3>"1:00",U3<="2:00"),"4","5"))))
[質問]
 区分1を 滞留時間1 から IF関数で テーブルの1〜5に分ける
 次に VLOOKUP関数で、区分1数字から 滞留時間を抽出 表の滞留時間2に入力する
 
 ロジックは難しくないのですが、VLOOKUP関数で、区分1を認識せずにエラーとなりました。
 そこで、この区分1をコピーして 形式を選択して貼り付けで、手入力のところに
 値のみ 貼り付けにしましたが、これも認識せずエラー。
 最後に 手入力のところに 手打ちで、5,2,3を 入力すると 認識して表に表示されるように
 なりました。VLOOKUP関数で認識させるため、手入力欄全件手打ちしました。
 → 表示形式は ’標準’になっています。
  なぜ、IF関数で産出した 区分1 の数字を認識しないのか。
  なぜ、手入力したら 認識できたのか・・・ご教授お願い致します。
 
  

回答
投稿日時: 17/10/21 14:29:17
投稿者: WinArrow
投稿者のウェブサイトに移動

>なぜ、IF関数で産出した 区分1 の数字を認識しないのか。
 
IF関数の戻り値は、文字列です。
一方テーブルの方は、数値なので、合致しないことになります。
 
IF関数でセットしている
"1"

1
のように「"」を外してみてください。

回答
投稿日時: 17/10/21 18:53:41
投稿者: Mike

  A  B   C    D     E    F   G  H   I   J    K
1 日付 入車 出車 滞留時間1 滞留時間2 区分1 手入力  滞留時間1 項番 滞留時間2
2 6/12 8:30 16:35   8:05 2時間超    5        0:00  1 15分以内
3 6/12 8:30 8:53   0:23 30分以内   2        0:16  2 30分以内
4 6/12 8:30 9:01   0:31 1時間以内   3        0:31  3 1時間以
5                             1:01  4 2時間以内
6                             2:01  5 2時間超
式 =VLOOKUP($D2,$I$2:$K$6,4-COLUMN(A1))
を入力したセル E2 を右隣に、および、下方にオートフィル
 
【お断り】実はご質問文の内容が良く理解できていませんが、提示された表を見ただけの勝手読みに基づく“怪答”です。参考になれば仕合せ。的外しになっていたらご免なさい。

回答
投稿日時: 17/10/22 07:06:46
投稿者: sy

鶴の舞 さんの引用:
=IF(U3<= "0:15","1",IF(AND(U3>"0:15",U3<="0:30"),"2",IF(AND(U3>"0:30",U3<="1:00"),"3",IF(AND(U3>"1:00",U3<="2:00"),"4","5"))))
  なぜ、IF関数で産出した 区分1 の数字を認識しないのか。

式そのものが合ってるかの検証はしてませんが1点だけ。
 
U3<= "0:15"
これは0:15に見える文字と比較してますね。
文字は書いてある内容に関係なく無限大として扱われるので、無条件にTRUEになります。
U3>"0:30"
此方は0:30の方が無限大なので、必ずFALSEになります。
 
U3<= "0:15"*1
と*1してシリアル値に変換してやる必要があります。

回答
投稿日時: 17/10/22 12:54:52
投稿者: LMK

遠回りはやめた方がいいです。
 
以下のような表を用意し、その範囲に例えば「区分」と名前を付けておきます。
(10^-7(=百分の一秒弱)を加えているのは演算誤差対策を兼ねています。)
(表示形式は適宜「時分」表示に)
 

0
="0:15"+10^-7
="0:30"+10^-7
="1:00"+10^-7
="2:00"+10^-7

 
以下の式で、その滞留時間がどの区分に当たるかを取得できます。
 
=MATCH(U3,区分)

投稿日時: 17/10/22 13:48:42
投稿者: 鶴の舞

 回答頂いた内容を確認するのが遅くなり、失礼致しました。
 
 WinArrow さん
  試したところうまく行きました。
  表示形式が 同じ標準でも 文字と数字があることを初めて知りました。
  そういえば セルで、手入力は 右側に寄ります。
  VLOOKUP で”” の時は左側に寄っていました。 有難うございました。
  解決できてうれしいです。
 
Mike さん
 いつも有難うございます。column関数は使ったことがありませんでした。
 オーソドックスなやり方しか試してないので、こちらもう少し深堀して
 できるか試してみます。 勉強になります。
 
sy さん
 時間を関数で識別する場合は、*1 を必ず入れるということですね。
 一行いれてみました。処理に影響はないが、正しい認識をするため必要と解釈しました。
LMKさん
 MATCH関数も使ったことはありませんが、IFだけでなく、知識の幅を広げる意味で
 試してみます。
 
 まずは御礼まで。当面の問題は解決致しましたが、アドバイス頂いたことは
 すべて試してみます。 有難うございました。
 

回答
投稿日時: 17/10/22 15:03:26
投稿者: Mike

鶴の舞 さんの引用:
column関数は使ったことがありませんでした。
オーソドックスなやり方しか試してないので、…
COLUMN、COLUMNS、ROW、ROWS関数は多用してますが、私も「オーソドックスなやり方」しかできません。

回答
投稿日時: 17/10/22 23:31:43
投稿者: LMK

以下念のため。
 
そもそもこの場合、IF関数のネストはまったくオーソドックスな方法ではないんですよね。
何のための表なのかってことです。
 
あとは、こういう計算につきものの「演算誤差」で誤判定にならないよう、気を使った方がいいですよ。

投稿日時: 17/10/23 08:46:09
投稿者: 鶴の舞

LMKさん
 アドバイス有難うございます。
 判定区分はIF文だけではないよ・・ もっといいやり方もあるよ・・ 承知致しました。
 今回より演算誤差も織り込んで データを作ります。