vlookupで出した値を区切り位置で区切る方法

お世話になってます。エクセル初心者で以下の件で悩んでいます。

vlookupで他のシートから日付を出してきた。（例) 2014. 1.1
これを年・月・日と区切りたいが、区切り位置をしようとすると、「選択したデータ」として表れるのが「2014.1.1」ではなく、「=VLOOKUP(A2, .........)」となってしまい、日付の区切りができない。

選択したデータが関数としてではなく、その結果出てきた数値として参照できるようにする方法があればご教示ください。。。

No.2 ベストアンサー 回答者： eden3616 回答日時： 2014/04/26 10:41

VLOOKUPで日付を持ってきてるということは変動する可能性があるということを前提に書かせていただきます

excelの区切り機能ではあくまでそのセルに入力されている値（計算式）を対象として処理をしますので
数式などで参照してきた値を区切ることはできません。

関数でそれぞれ年、月、日を取り分ける必要があります。

以下の方法で出来ます（2通りの方法でやってみました）
A1に日付「2014.1.2」が入っているとします
（1.1でしたら月と日の見分けが出来ないため変えています）

■文字列として扱い取り分ける場合
(1)「年」を取り出すには
=LEFT(A1,SEARCH(".",A1)-1)
(2)「月」を取り出すには
=MID(A1,SEARCH(".",A1)+1,SEARCH(".",A1,SEARCH(".",A1)+1)-SEARCH(".",A1)-1)
(3)「日」を取り出すには
=RIGHT(A1,LEN(A1)-SEARCH(".",A1,SEARCH(".",A1)+1))

解説＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
○使用している関数は以下のものになります。
LEFT・・・指定した文字の左から何文字か取り出す関数
MID・・・指定した文字の何文字目から何文字目までを取り出す関数
RIGHT・・・指定した文字の右から何文字か取り出す関数
LEN・・・指定した文字の文字数を返す関数
SEARCH・・・指定した文字の中から指定した指定した検索文字のある場所を返す関数

○方法
SEARCHでドット「.」の位置を調べてLEFT、MID、RIGHTと組み合わせて文字を取り出しています
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
■日付として扱い取り分ける場合
(1)「年」を取り出すには
=YEAR(DATEVALUE(SUBSTITUTE(A1,".","/")))
(2)「月」を取り出すには
=MONTH(DATEVALUE(SUBSTITUTE(A1,".","/")))
(3)「日」を取り出すには
=DAY(DATEVALUE(SUBSTITUTE(A1,".","/")))

解説＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
○使用している関数は以下のものになります。
YEAR・・・指定したシリアル値から年を取得して返します
MONTH・・・指定したシリアル値から月を取得して返します
DAY・・・指定したシリアル値から日を取得して返します
DATEVALUE・・・日付としてシリアル値（1900/1/1を1としたときの日数）に変換します
SUBSTITUTE・・・指定した文字から指定した文字を検索して指定した文字へ置換します

○方法
値が2014/1/2とかでしたらエクセルは日付として認識できるのですが、
区切り文字がドット「.」でしたらそのままでは文字列としか認識してくれません。
SUBSTITUTEで「.」を「/」に置換してからDATEVALUEで日付に変換し
YEAR、MONTH、DAYを取り出しています
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣

前者の方法を覚えると文字の取出しが自由にできるようになるとおもいます。
が、この場合シンプルでわかりやすいのは後者でしょうかね。


～～～～～～～～～～～～～～～～～～～～～～～～～

そもそも

VLOOKUPで取り出した日付がそれ以降変動させる必要がない場合、
関数で取り出した値「2014.1.2」をそのまま文字列として変換し、
excelの区切り機能で分けることが可能です。

(1)A1をコピー
(2)A1を右クリック「形式を指定して貼付」を選択
(3)「値」を選んで「OK」をクリック

でA1のVLOOKUPの数式が無くなり日付の「2014.1.2」がA1に入りますので
excelの区切り機能で区切り文字を「.」としてください。

No.4 回答者： bunjii 回答日時： 2014/04/26 11:25

>vlookupで他のシートから日付を出してきた。

（例) 2014. 1.1
例示の2014.1.1は文字列でしょうか？、それともシリアル値（数値）の表示形式を日付形式にしたものでしょうか？
何方かによって処理の方法が異なります。

>選択したデータが関数としてではなく、その結果出てきた数値として参照できるようにする方法があればご教示ください。。。
処理の途中はどのような方法でも、最終目的に合えば良いと思います。

VLOOKUP関数の返り値（2014.1.1）が文字列としたとき以下のような数式で対応できます。
年の値（数値）=YEAR(VALUE(SUBSTITUTE(VLOOKUP($A1,$E$1:$F$3,2),".","/")))
月の値（数値）=MONTH(VALUE(SUBSTITUTE(VLOOKUP($A1,$E$1:$F$3,2),".","/")))
日の値（数値）=DAY(VALUE(SUBSTITUTE(VLOOKUP($A1,$E$1:$F$3,2),".","/")))
この数式を使ってExcel 2013で検証した結果の画像を添付します。
文字列の切り出し以外に年・月・日の値を切り出す方法があります。
今回の処理では数値として取り出しましたがそれを文字列に変換するにはTEXT関数を使えば良いでしょう。
今回の数式はVLOOKUP関数の戻り値をSUBSTITUTE関数に渡し、VALUE関数で数値に置き換えた上で、YEAR関数で年の数値に、MONTH関数で月の数値に、DAY関数で日の数値に変換しています。

この回答へのお礼

なるほど、戻り値をSUBSTITUTE関数、VALUE関数、そしてYEAR/MONTH/DAY関数で変換することができるのですね。とても勉強になりました。誠にありがとうございました！

お礼日時：2014/04/26 12:14

No.3 回答者： WindFaller 回答日時： 2014/04/26 11:12

ご質問内容の情報が抜けているので、きちんとした回答ではないのですが、

>「2014.1.1」ではなく、「=VLOOKUP(A2, .........)」

これは、「=VLOOKUP(A2, .........)」の数式のセルが、[書式]--[文字列]になっているのが原因ですから、まず、それを[標準]などに修正しないことには、どんな数式でも解決しないはずです。

その後は、例えば、こんなふうになります。
=YEAR(SUBSTITUTE(VLOOKUP(A2,D1:E10,2),".","/")*1)

これで、数値データになります。

この回答へのお礼

なるほど、そもそも書式の修正が必要なのですね。修正して再度トライしてみます。ありがとうございます！

お礼日時：2014/04/26 12:15

No.1 回答者： tom04 回答日時： 2014/04/26 10:34

こんにちは！

VLOOKUP関数で表示されているのは質問通りの　2014.1.1 となっているのでしょうか？
そうであれば参照先は「文字列」というコトだと思います。
ただ、数式によって表示されているデータを「区切り位置」で区切れませんので、実データにしてやります。

仮にB列に結果が表示されているとします。
B列すべて（もしくは区切りたいデータ）を範囲指定　→　右クリック　→　コピー　→　そのまま右クリック　→　「形式を選択して貼り付け」　→　「値」を選択しOK
後は普通に「.」で区切れば大丈夫だと思います。

※　質問文をみるとまずそういうコトはないと思いますが、
「値」にして、5桁数値の場合はシリアル値ですので、区切り位置で区切るコトはできません。
その場合B2セルに5桁数値が表示されているとすると
C2セルに
=YEAR(B2)
D2セルに
=MONTH(B2)
E2セルに
=DAY(B2)
のように各セルに数式を入れたやる必要があります。m（_ _）m

この回答へのお礼

セルの形式を「値」にする方法がわからなかったのですが、tom04さんの説明でよくわかりました。本当にありがとうございました！

お礼日時：2014/04/26 12:16