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

質問

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

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

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

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

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

回答No.3

noname#70958 · ベストアンサー

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

●データが【同じ行の左から右方向に】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：最新の日付と３番目に新しい日付の差
　　=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は数値に設定してください。

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

以上ご参考まで。

お礼

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

回答No.4

imogasi

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

お礼

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

回答No.2

mu2011

一例です。
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キーを同時押下して下さい。

因みに貸出日をＢ列、返却日をＣ列とすると数式も簡単になりますのでご検討下さい。
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)),"")

お礼

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

回答No.1

noname#63240

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

お礼

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