こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

Excel 一列おきのデータ参照と計算

Excelの計算式について質問です。

同じ行の左から右方向に,貸した日付と帰ってきた日付を順番に入力します。(データは,B1貸し日 B2返却日 B3貸し日 B4返却日・・・・ の順に増えていきます)

A1に一番最新の貸し日付を,A2に一番最新の返却日付を,表示させたいと思います。つまり,単に一番右のデータ(最新日付)を表示させるのではなく,奇数列と偶数列のそれぞれの最新データを表示させたいのです。

そしてA3には,返却状態でない場合は(最新貸し日付-一つ前の貸し日付=日数)を表示させ,返却状態であれば(最新返却日付-一つ前の返却日付=日数)を表示させたいと思います。(つまりサイクル日数を出したいのですが,物が当方にあるかないかで計算に用いる日付が変わります)

この場合,A1 A2 A3 にはどのような計算式が良いでしょうか。もしよろしければお教えください。よろしくお願いします。

投稿日時 - 2008-07-17 10:45:49

QNo.4183032

困ってます

質問者が選んだベストアンサー

「順番に入力」してあるのであれば、
【最新の日付】すなわち【最大の数値】となりますから…

●データが【同じ行の左から右方向に】B1,C1,D1,…と入っている場合

 A1:最新の貸出日(偶数列にある日付のうち最大のもの)
  =MAX(INDEX(B1:IV1*MOD(COLUMN(B1:IV1)+1,2),))

 A2:最新の返却日(奇数列にある日付のうち最大のもの)
  =MAX(INDEX(B1:IV1*MOD(COLUMN(B1:IV1),2),))

 A3:最新の日付と3番目に新しい日付の差
  =LARGE(B1:IV1,1)-LARGE(B1:IV1,3)

●データが【B1貸し日 B2返却日 B3貸し日 B4返却日…】と同じ列の上から下方向に入っている場合、

 A1: =MAX(INDEX(B1:B99*MOD(ROW(B1:B99),2),))
 A2: =MAX(INDEX(B1:B99*MOD(ROW(B1:B99)+1,2),))
 A3: =LARGE(B1:B99,1)-LARGE(B1:B99,3)

数式の入力後、
セルの書式設定>表示形式で、A1,A2は日付,A3は数値に設定してください。

また、データが1つ以下の場合は「エラー」値や0が返ります。
もし気になるようでしたら、条件付書式を使うか、
 =IF(COUNT(B1:IV1)<2,"",【数式】)
のようにIFをかぶせてエラー値を見えないようにしてください。

以上ご参考まで。

投稿日時 - 2008-07-17 13:54:18

お礼

とても丁寧に教えてくださりありがとうございます。教えていただいた式を参考に作りたい物を作ることができました。ありがとうございました。

投稿日時 - 2008-07-17 23:40:28

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.4

関数だと#2のご回答のような複雑な式になる。
簡単のためユーザー関数を作ってみる。
標準モジュールに
Function nw(a)
Application.Volatile
nw = Range("iv" & a.Row).End(xlToLeft)
End Function
と定義すると
(ENDキー+←の操作に当たる)
例データ
A2:E2で
A列  B列   C列  D列    E列
weasdwe
A1に
=nw(A2)
と入れればよい。
結果
we
1つ左は
Function nwb(a)
Application.Volatile
nwb = Range("iv" & a.Row).End(xlToLeft).Offset(0, -1)
End Function
式は
=nwb(A2)
結果
d
--
貸し出しー返却のペアがくづれると使えないが。奇数列かなどのチェックは加えることは可能だが。
ーーー
エクセルをこういう仕事に使うことは危うさを感じる。
表形式ではダメで、データベース形式(貸し出しや返却を表の位置で表すのを使うのをやめる)を採用すべき。

投稿日時 - 2008-07-17 14:30:36

お礼

そうですよね。知り合いにも少し聞いたら,Excelではなくてデータベースソフトですればいいよ,と教わりました。(その人はExcelはよくわからない人でした)しかしデータベースソフトはなかなか難しそうで・・・データ量が増えたりするとそちらの方がいいんでしょうけど。
ユーザー関数というのは考えつきませんでした。ありがとうございました。

投稿日時 - 2008-07-17 23:44:16

ANo.2

一例です。
A1に=IF(COUNT(B:B),INDEX(B:B,LARGE((MOD(ROW($B$1:$B$100),2)=1)*($B$1:$B$100<>"")*ROW(($B$1:$B$100)),1)),"")
A2に=IF(COUNT(B:B)>1,INDEX(B:B,LARGE((MOD(ROW($B$1:$B$100),2)=0)*($B$1:$B$100<>"")*ROW(($B$1:$B$100)),1)),"")
A3に=IF(COUNT(B:B)>2,IF(A1>A2,INDEX(B:B,LARGE((MOD(ROW($B$1:$B$100),2)=1)*($B$1:$B$100<>"")*ROW(($B$1:$B$100)),1))-INDEX(B:B,LARGE((MOD(ROW($B$1:$B$100),2)=1)*($B$1:$B$100<>"")*ROW(($B$1:$B$100)),2)),INDEX(B:B,LARGE((MOD(ROW($B$1:$B$100),2)=0)*($B$1:$B$100<>"")*ROW(($B$1:$B$100)),1))-INDEX(B:B,LARGE((MOD(ROW($B$1:$B$100),2)=0)*($B$1:$B$100<>"")*ROW(($B$1:$B$100)),2))),"")
尚、数式は全て配列数式の為、入力完了時にshift+ctrl+enterキーを同時押下して下さい。

因みに貸出日をB列、返却日をC列とすると数式も簡単になりますのでご検討下さい。
A1は=IF(COUNT(B:B),LARGE(B:B,1),"")
A2は=IF(COUNT(C:C),LARGE(C:C,1),"")
A3は=IF(COUNT(B:B)>1,IF(A1>A2,LARGE(B:B,1)-LARGE(B:B,2),LARGE(C:C,1)-LARGE(C:C,2)),"")

投稿日時 - 2008-07-17 11:47:02

お礼

私がうっかりしておりまして,サンプルが間違えておりました。
しかし,縦方向にデータを入力する方法もよさそうですね。特に貸出日と返却日の列を分ける方法など,とても参考になります。いろいろ検討してわかりやすい表を作りたいと思います。ありがとうございました。

投稿日時 - 2008-07-17 14:13:54

ANo.1

>同じ行の左から右方向に,貸した日付と帰ってきた日付を順番に入力します。(データは,B1貸し日 B2返却日 B3貸し日 B4返却日・・・・ の順に増えていきます)
列方向に入力と書いてますが、サンプルでは行方向になってます。
どちらが正しいのですか?

投稿日時 - 2008-07-17 10:54:09

お礼

ご指摘の通りです。サンプルが間違えております。横方向にデータが入ります。申し訳ありません。

投稿日時 - 2008-07-17 14:09:15

あなたにオススメの質問