Excel：MID関数で(xxxx/yy/zz)の場合

質問

http://okwave.jp/qa3857919.html
にて似たような質問をしました。
今回は数値ではなく日付の場合です。

Sheet1のB列に「xxxx/yy/zz」というように年月日が入力されています。
常にこの年月日の最新年月日の一ヶ月前の年月日をSheet2の指定セルに分割して表示させることは出来ないでしょうか？
例えば以下の場合、

Sheet1のB2に2008/1/31、B3に2008/2/29、B3に2008/3/31と入力されていた場合にSheet2のA18に「0」、B18に「8」、D18に「0」、E18に「2」、G18に「2」、H18に「9」と表示させたいのです。
Sheet1のB列には毎月年月日が追加されていきます。
Sheet1のB4に2008/4/30と追加されれば、Sheet2のA18に「0」、B18に「8」、D18に「0」、E18に「3」、G18に「3」、H18に「1」と表示されるようになります。

解りづらいかもしれませんが、よろしく御教示お願い致します。

回答No.2

noname#79209 · ベストアンサー

最新の日付は
=MAX(Sheet1!B:B)
で算出できますので、その1ヶ月前なら
=EDATE(MAX(Sheet1!B:B),-1)
です。
これを「yymmdd」の形で表すようにするには、
=TEXT(EDATE(MAX(Sheet1!B:B),-1),"yymmdd")
です。
そこで、
Sheet2のA18に
=MID(TEXT(EDATE(MAX(Sheet1!B:B),-1),"yymmdd"),1,1)
Sheet2のB18に
=MID(TEXT(EDATE(MAX(Sheet1!B:B),-1),"yymmdd"),2,1)
Sheet2のD8に
=MID(TEXT(EDATE(MAX(Sheet1!B:B),-1),"yymmdd"),3,1)
Sheet2のE18に
=MID(TEXT(EDATE(MAX(Sheet1!B:B),-1),"yymmdd"),4,1)
Sheet2のAG18に
=MID(TEXT(EDATE(MAX(Sheet1!B:B),-1),"yymmdd"),5,1)
Sheet2のH18に
=MID(TEXT(EDATE(MAX(Sheet1!B:B),-1),"yymmdd"),6,1)
で可能ですが、
「TEXT(EDATE(MAX(Sheet1!B:B),-1),"yymmdd")」の部分は、
別のセルに記述しておき、（仮にSheet2のセルA1と仮定すると）
Sheet2のA18に
=MID(Sheet2!$A$1,1,1)
などと書くことができるようにした方がよいでしょう。

お礼

ありがとうございます。
最初、この数式を使ってもその通りにならなかったので、補足質問させていただこう、と思っていた矢先に#5様が、

>使う時は [ツール]→[アドイン]→[分析ツール]にチェックして[OK]
>をしておく必要があります。

という回答があったので、これで一気に解決できました。
本当に助かります。

回答No.6

noname#79209

#2です。

もし、前月末日が得たいなら、
=MID(TEXT(EOMONTH(MAX(Sheet1!B:B),-1),"yymmdd"),1,1)
などと、EOMONTH関数が使えます。
ただし、この関数もEDATE関数と同様「分析ツール」アドインに
入っている関数です。

回答No.5

noname#176215

質問者の質問の仕方によって回答が決まります。

「望んだ回答でなかったから」といって 売り言葉に買い言葉では困りま
す。回答者には色んな人がいますし 色んなアプローチの仕方があります。
知らない人に道を尋ねるのに 相手の返事があなたに対して批判的だった
からといって あなたが相手を批判する理由にはなりません。

ましてや 質問の仕方が悪いという指摘をもらって嫌がる理由などありま
しょうか。ありがたく意見を頂戴しておけばいいではありませんか。

他の方法は既に出ていますから 例で出ている場合にのみ対応する方法を
提示します。みている限り月末を表示したいだけのようですから

=MID(TEXT(MAX(Sheet1!$B:$B)-DAY(MAX(Sheet1!$B:$B)),"yymmdd"),COLUMN(A18),1)

***

下の方が回答されている EDATE関数は アドイン関数です。
使う時は [ツール]→[アドイン]→[分析ツール]にチェックして[OK]
をしておく必要があります。

お礼

ありがとうございます。
そうですね、今後はうっとうしい回答者は無視するように心がけます。
そして#1の質疑は削除依頼をします。

回答No.4

noname#79209

#2です。
> あと、質問では年と月と日の間に一列づつ間が開いているようなので、
> #2さんが言われるように、COLUMN関数で横コピーはしんどいかも．．
じつは「#2さん」は「#1さん」のつもりだったんですが、
「COLUMN関数で横コピー」してもコピー後にいらないセルのデータは
上書きしてしまえば済むことですね。
むしろ横コピーの方が制作効率は上がりますね。

#1さん、失礼なことを言ってしまいました。ゴメンナサイ。

回答No.3

noname#79209

#2です。
私が示したのは、Sheet1に次々と追加されていく日付が、
常に新しい日付である場合です。
古い日付が追加されていくような場合はMAX関数は使えません。

> 「１セルに１文字」って何か意味があるのですか？
┌─┬─┬─┬─┬─┬─┬─┬─┬─┐
┃０ ┃８ ┃年 ┃０ ┃２ ┃月 ┃２ ┃９ ┃日 ┃
└─┴─┴─┴─┴─┴─┴─┴─┴─┘
といったようなことをやりたい方が結構おられるようで。
エクセルをワープロとして使っていると、こういう要求がでますね。

あと、質問では年と月と日の間に一列づつ間が開いているようなので、
#2さんが言われるように、COLUMN関数で横コピーはしんどいかも．．．

回答No.1

zap35

この質問は３つの内容をまとめて聞いています。とても欲張りですね。それぞれが１つの質問でもよいくらいの内容です。

１）Ｂ列の最後の値（日付）を取得する
２）取得した日付の１カ月前の日付を求める
３）その日付をＡ１８セルから順次表示する

１）COUNTA関数を用います。
　=COUNTA(B:B)
でB列の「何か入力されたセルの数」を求められます。B1セルにも何か（空白でもよい）を入力してあれば、この値は最終行を返します。

すると
　=OFFSET($B$1,COUNTA(B:B)-1,0)
が最下行の日付を取得する式になります

２）実はこれが一番難しいです。
　2008/3/31の１カ月前→2008/2/29
　2008/3/30の１カ月前→2008/2/29
　2008/3/29の１カ月前→2008/2/29
　2008/2/28の１カ月前→2008/2/28
でよいですか？
これは
　=DATE(YEAR(OFFSET($B$1,COUNTA(B:B)-1,0)),MONTH(OFFSET($B$1,COUNTA(B:B)-1,0))-1,IF(DAY(OFFSET($B$1,COUNTA(B:B)-1,0))>DAY(DATE(YEAR(OFFSET($B$1,COUNTA(B:B)-1,0)),MONTH(OFFSET($B$1,COUNTA(B:B)-1,0)),0)),DAY(DATE(YEAR(OFFSET($B$1,COUNTA(B:B)-1,0)
で求められます。

３）やっと最後の部分です。どこかのセルに
　=TEXT(DATE(YEAR(OFFSET($B$1,COUNTA(B:B)-1,0)),MONTH(OFFSET($B$1,COUNTA(B:B)-1,0))-1,IF(DAY(OFFSET($B$1,COUNTA(B:B)-1,0))>DAY(DATE(YEAR(OFFSET($B$1,COUNTA(B:B)-1,0)),MONTH(OFFSET($B$1,COUNTA(B:B)-1,0)),0)),DAY(DATE(YEAR(OFFSET($B$1,COUNTA(B:B)-1,0)),MONTH(OFFSET($B$1,COUNTA(B:B)-1,0)),0)),DAY(OFFSET($B$1,COUNTA(B:B)-1,0)))),"yymmdd")
と入力してください

仮にこのセルをSheet1のZ1とします
Sheet2のA18には
　=MID(Sheet1!$Z$1,COLUMN(A1),1)
を入力して右方向にコピーしてください

並の質問の３倍疲れました(^^;　これが「急がない」質問ですからやってられません。きっと回答に対するレスポンスも悪いんでしょうね。
最後に個人的な感想ですが「１セルに１文字」って何か意味があるのですか？　エクセル的にはあまり意味のないことだと思うのですが…

お礼

かなりお手数をおかけしたようで恐縮ですが、文頭・文末に批判というか愚痴というか・・、これは要らないんじゃないですか？
それだったら初めからご回答いただかなくてもよいと思います。
私も不愉快ですし、また第三者の方々も見て気分のよいものではないと思いますよ。

>この質問は３つの内容をまとめて聞いています。

Excelに精通している方にはそういう解釈になるのでしょうが、私のように精通していないものにとっては内容がいくつになるかなんて知ったことではありません。

>これが「急がない」質問ですからやってられません。

やってられなければやらないでください。

>きっと回答に対するレスポンスも悪いんでしょうね。

過去の私のExcel関連の質問見ましたか？
http://okwave.jp/qa3857919.html​
これも「急がない」質問ですが、回答に対するレスポンス、遅いですか？

>最後に個人的な感想ですが「１セルに１文字」って何か意味があるのですか？

ここは質問に対して回答するコミュニティです。感想は要りません。
私もここでこんなことを言っているくらいですから、人のことを言えた立場ではありませんが・・・。
Excelをどのように活用しようと人の勝手ではないですか？
こう言う使い方はしてはいけない、って規則があるなら別ですが、そんな話、聞いたことがありません。
それとも私がExcelに精通していないから、知らないだけでこう言う使い方は規則に反するのでしょうか？