[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『日付を基準に別シートの任意の列データを抽出』（うりぼー）
エクセルマイスターの方々へ
いつもお世話になております。いつものごとく是非ご教授頂きたく思います。
下記のように同じファイルに2枚のシート1、2があります。
シート1
A列 B列 C列 D列 E列 F列 G列 1行目 2008/10/14 9,388.97 9,924.28 9,050.06 9,310.99 8,161,990,400 -0.82% 2行目 2008/10/15 9,301.91 9,301.91 8,516.50 8,577.91 6,542,330,000 -7.87% 3行目 2008/10/16 8,577.04 9,073.64 8,176.17 8,979.26 7,984,500,000 4.68% 4行目 2008/10/17 8,975.35 9,304.38 8,640.83 8,852.22 6,581,780,000 -1.41%
シート2
A列 B列 1行目 2008/10/14 2行目 2008/10/15 3行目 2008/10/16 4行目 2008/10/17
シート2のB列には、シート1のG列を入力したく思います。 そこで、例えばシート2のB1には=offset(vlookup(A1,シート1!A1:A4,,6,,)) だと考えたのですが、 「入力した数式は正しくありません」となってしまいます。 日付の書式が悪さをしているのでしょうか。 それとも、式自体の問題なのでしょうか？
以上、宜しくお願い申し上げます。
A列が一致するG列の値を返したいなら =vlookup(A1,シート1!A$1:G$4,7,FALSE) では？
OFFSET関数を使いたいなら MATCH関数と組み合わせるのが良いと思います。 =OFFSET(シート1!A$1,MATCH(A1,シート1!A$1:A$4,0)-1,6)
(HANA)
HANA様
早速のご回答ありがとうございます。 私の説明不足がございましたのでお詫び致します。
今回の例の場合は、シート1とシート2の日付が全く同じなのですが、 シート1にはあって、シート2には無い場合、またその逆もあります。 ですので、まずはシート2の日付がシート1にあるかどうかを確認という式を組み込みたいのです。 そして、シート1に同じ日付があれば、 そこから右に6つ目のデータをシート2のB列に返したく思います。
たびたびお手数ですが宜しくお願い致します。
（うりぼー）
＞シート2の日付がシート1にあるかどうか 調べる方法は色々有ると思います。
上に書いたVLOOKUP関数は検索の型にFALSEを指定しています。 A1セルの値を 範囲の中から「完全一致」で探し 見つからない場合はエラー値 #N/A が返されます。
ですから、ISNA関数で戻り値がエラー値か確認すれば 存在するかしないかは 分かると思います。 ISNA(VLOOKUPの式)がTRUEの時に「存在しない」と言えます。
他に良く使われるのは、COUNTIF関数を使用した物です。 A1セルの値と一致するものをA列の範囲の中から数え 戻り値が0より大の時に「存在する」と言えます。
お好みの論理式を作成し、IF関数で分岐させて下さい。
＞そこから右に6つ目のデータをシート2のB列に返したく思います。 OFFSET関数をご使用に成りたいのなら MATCH関数の戻り値がエラー値かどうかで 判定するのが良いかもしれませんね。 MATCH関数も、照合の型に「0」を指定した場合 検査値が見つからない場合は、エラー値 #N/A が返されます。
私が作るなら COUNTIF関数で検査し、存在する場合は VLOOKUP関数で値を返す式にすると思います。 =IF(COUNTIF関数>0,VLOOKUP関数,"")
或いは、日付は重複しないと思いますので SUMIF関数の戻り値が0で無い場合に、その戻り値を返す とか・・・。 =IF(SUMIF関数=0,"",SUMIF関数)
(HANA)
>私が作るなら >COUNTIF関数で検査し、存在する場合は >VLOOKUP関数で値を返す式にすると思います。 >=IF(COUNTIF関数>0,VLOOKUP関数,"") 私のレベルでいろいろ考え試してみましたが、私の求めるものはできませんでした。 そこで、式を分割してシート2のB1に以下の式を入力してみました。
=VLOOKUP(A1,Sheet1!A1:A4,1,)
→ちゃんと日付が返ってきました。
=COUNTIF(A1,Sheet1!A1:A4)
→1と表示されました。
上記2つの式をifで結びつけて、シート1の6行目をどのように、 シート2に出力させるかが分かりませんでした。 6行目の”6”という数字は、どの式のどの部分で指定すると良いのでしょうか？
全くお手上げですので、大変お手数ですがもう少しお付き合いしていただけないでしょうか。
(うりぼー)
VLOOKUP関数は、余り使われないですかね？ まずは、この関数について ご理解を深めて頂くのが宜しいかと思います。
↓ライブラリより、VLOOKUP関数 http://www.excel.studio-kazu.jp/lib/e1tw/e1tw.html
VLOOKUP関数は VLOOKUP(検索値,範囲,列番号,検索の型) の様に書き 指定された範囲の左端の列で特定の値を検索し、 範囲内の対応するセルの値を返します。
B1セルに =VLOOKUP(A1,シート1!A$1:G$4,7,FALSE) と入れた場合、 指定された範囲の左端の列 ＝ A1:A4 の範囲から 特定の値 ＝ A1 セルの値 を検索し 対応するセルの値 ＝ 同じ行の【7】番目の列 の値を返します。
この時、検索の型に FALSE を指定しているので A1:A4 の範囲から A1 セルの値 を【完全一致】で検索します。
この式のみをセルに入れると 日付が無い場合は エラー値が 有る場合は 求める値が返されていますよね？
ですから、COUNTIF関数で条件判断をし COUNTIF(A1,Sheet1!A1:A4) > 0 の時 （A1セルの値が存在し、エラーが表示されない時） VLOOKUP関数の結果を表示。 それ以外は「""」 と言う式にして下さい。 =IF(COUNTIF(A1,Sheet1!A$1:A$4)>0,VLOOKUP(A1,Sheet1!A$1:G$4,7,FALSE),"")
(HANA)
VLOOKUPが列を指定して返せるということを分かっていませんでした。 勉強不足でした、どうもありがとうございました。
今回の例だと、2008/10/14は2枚のシートで1行目に、2008/10/15は2行目という風になっていますが、 そのようになっていない場合、つまりどちらかのシートにデータ行数の過不足がある場合には、 うまく数字を返すことができませんでした。 例）シート1の1行目に、2008/10/13を挿入すると、シート2に数字が返ってこなくなります。 ちなみにエクセルのバージョンは2003です。
このように2枚のシート行数に過不足が発生する場合は、他の関数を使うことになるのでしょうか？
(うりぼー)
確認していただきたいことがあります。
＞シート1の1行目に、2008/10/13を挿入すると、シート2に数字が返ってこなくなります。 この時の数式の参照セル範囲を確認してください。 1行目が範囲から外れていませんか？ 外れているのなら、挿入した行の値が返されないのは 当然だと思われますよね。
小さなサンプルで試してみましょう。 [A] [B] [C] [D] [E] [1] あ AA い II [2] い II え EE [3] う UU か #N/A [4] え EE [5] お OO A1:B5が範囲、D列が検索値 E1セルに =VLOOKUP(D1,$A$1:$B$5,2,FALSE) として、D4セルまでフィルドラッグしてあります。
E1,E2セルに関しては D列の値がA列の範囲に存在するので 対応するE列の値が返されます。 E3セルはA列の範囲にありませんので エラー値が返されます。
これはうりぼーさんの言っておられる 「2枚のシート行数に過不足が発生する場合」 の簡単なサンプルとして使用できるものですか？
VLOOKUP関数は「検索」をして最初に一致した行の 指定列番目にある値を返します。 ですから、検索値と検索される値が 同じ行にある必要はありません。
ここで、1行目に行を挿入すると ↓の状態になりますが [A] [B] [C] [D] [E] [1] [2] あ AA い II [3] い II え EE [4] う UU か #N/A [5] え EE [6] お OO E2セルの数式を見ると =VLOOKUP(D2,$A$2:$B$6,2,FALSE) になっています。
この状態では、たとえA1:B1にデータを追加しても このセルは検索の範囲に含まれませんので A1セルの値を検索しても、B2セルを返せません。
1行目にデータを挿入する事があるのなら 列で参照させておくのが良いかもしれませんね。 =VLOOKUP(D2,A:B,2,FALSE) のように・・・。
VLOOKUP関数は上記のような関数ですから ＞どちらかのシートにデータ行数の過不足がある場合には、 ＞うまく数字を返すことができません というのが、どういう状況なのかよくわかりません。
実際にどのようなデータなのか。 また、どの式をどこに入れておられるのか ご説明いただけませんかね。
「作成なさった数式が違うのではないか？」 という気がします。
(HANA)
>VLOOKUP関数は上記のような関数ですから >>どちらかのシートにデータ行数の過不足がある場合には、 >>うまく数字を返すことができません >というのが、どういう状況なのかよくわかりません。 シート2に1行挿入すると、シート2のE列の結果が真っ白になります。 vlookupとcountifをシートF列、G列に独立して作り、 行の挿入を行ったところ、今までTRUEと出力されていたcountif列(G列)は真っ白になりました。 vlookup列(F列はそのままでした。) 私の求める結果は、vlookupだけを使うと良いことも分かりました。 本当にありがとうございました。 vlookupで分かっていなかったことを理解することができました。
countifは、なぜ行の挿入で出力結果が消えてしまうのかは、これからいろいろ試してみようと思います。
>実際にどのようなデータなのか。 >また、どの式をどこに入れておられるのか >ご説明いただけませんかね。 日本とアメリカの市場データですので、 各々の休日により、一方では市場が開いていますが、 一方では開いてない状況が発生します。 上記にも記載しましたが、HANAさんのご説明のおかげで vlookupを用いることにより解決できました。
まだ、全て解決ではありませんが、 いつも無知な私に辛抱強くご説明頂き大変感謝しています。
(うりぼー)
＞vlookupだけを使うと良いことも分かりました。 「Sheet2にのみ存在する日付」は無いのですか？ でしたら、エラー処理は無くても良い様に思いますが・・・。
＞countifは、なぜ行の挿入で出力結果が消えてしまうのか 私も気になります。 しかも「TRUEと出力されていたcountif列(G列)は真っ白」 と言う表現も・・・。 （FALSEになった なら分からないでも無いのですが・・・。 IF関数の中に入れてあったりするのですかね？）
原因が分かりましたら教えて下さい。
(HANA)
＞＞countifは、なぜ行の挿入で出力結果が消えてしまうのか ＞私も気になります。 ＞しかも「TRUEと出力されていたcountif列(G列)は真っ白」 ＞と言う表現も・・・。 ＞（FALSEになった なら分からないでも無いのですが・・・。 ＞ IF関数の中に入れてあったりするのですかね？）
countifでのエラー処理がうまくいきました。 間違っていました原因は、countifの中の範囲と検索条件の選択を逆にしていました。 いままで使っていた関数の多くは、まず検索条件を選択し、次に範囲の選択ということもあり その変で、自分のなかでのすり込み効果により自分のなかで間違いをおこしてしまっていました。 大変お手数をお掛けして申しわけありませんでした。
(うりぼー)
「countifの中の範囲と検索条件の選択を逆にしていました。」 はっっ、申し訳ないです。 私が逆に書いてますね。。。
以後気を付けます。 ご報告有り難う御座いました。
(HANA)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.