No.3ベストアンサー
- 回答日時:
VLOOKUPというのは、指定した範囲の左端の列に、検索した値と一致するものがあるかを上から順に調べていき、一致した場合、その行のX列目(指定)のデータを取ってくる関数です。
左端に列を挿入できない場合、別のシートもしくは同一シートの別の箇所に、検索用の列・A列の値・D列の値を用意できるのであれば、使うことができます。(AとDの位置は逆でも可能で、3つの列の間に別の列が含まれていても可能です。)
例えば、E列以降が空いているということですので、E列に判定式、F列=A列、G列=D列 と入れると仮定します。
Sheet2!A1 というのは、Sheet2という名前のシートのセルA1にあるデータを指定します。
$ は列又は行の前に入れる事で、セルをコピーしてもその値を固定させることができます。
IF(○○,X,Y) は、○○という判定式が成り立つのであればX、成り立たないのであればYを表示します。
"" というのは、空白を意味しています。
MAX(A1:B2) というのは、A1~B2という範囲(列全体の場合はA:Bといった応用もできます)で最大の値をとってきます。
VLOOKUP(X,A1:B2,I,FALSE) というのは、XというデータをA1~B2(これもA:Bといった応用が可能)のA1~A2の中で検索し、一致した行のI番目の列にあるデータを取ってきます。ただし、I番目の列が指定した範囲(A~B)の中にある必要があります。また、FALSEというのは、完全一致の検索を意味します。Iで止めて「,FALSE」を省略することもできますが、その場合TRUEとして扱われるため、基本的にはFALSEとする事が多いと思います。
ROW(A1) というのはA1の行番号をとってきます。つまりセルの数字で表されている部分です。ROW()とすると式の入っているセル自体の行番号になります。
<1行目もデータである場合>
E1=IF(Sheet2!A$1>F1,””,IF(Sheet2!B$1<F1,"",IF(G1="","",1)))
日付、データ、等の列の名前を1行目に入れている場合は不要なのですが、
そのセルより上にある数を判定したいので、1行目だとセルの指定ができません。
行番号を自動的に判定して、1行目なら・・・という式にもできますが、できるだけ簡潔にしたいので。
なので1行目だけG1="","",1←この1を直接書いています。
式の意味は、F1が期間の初めよりも前の場合空白、そうではなくて期間の終わりよりも後の場合空白、そうでもない場合でG1が空白ならば空白、それでもないならば1、を表示する。という意味です。
<2行目以降>E2の例で表示するので、入力されたデータがA1000まであるなら、E1000までコピー(セルの右下をドラッグでも可)してください。
E2=IF(Sheet2!A$1>F2,"",IF(Sheet2!B$1<F2,"",IF(G2="","",MAX(E$1:E1)+1)))
これは1行目とほとんど同じですが、E1からE2の1つ上のセル(E2の場合どちらもE1ですが)の中で最大の値に1加えるというものです。
そのセルより上のセルに数字がある場合、その中の最大値に1を加え、無い場合は0に1を加えて1とするので、該当するデータの個数を表しています。
<検索を表示するデータについて>
表示したいのはA列及びB列の3行目以降ということでよろしいでしょうか?
別の場所であるならばそれに合わせて変更してください。
A3=VLOOKUP(ROW()-ROW(A$2),Sheet1!E:G,2,FALSE)
これは、A2から数えて何行目になるか(A3なら1、A4なら2)の値ををさっき作ったSheet1のE列から検索し、該当する行の2番目の列=F列にあるデータを取ってくるという式です。
B3=VLOOKUP(ROW()-ROW(B$2),Sheet1!E:G,3,FALSE)
これは、A3とほぼ同じですが、3番目の列=G列にあるデータを取ってくるという式です。
COLUMN(セル指定) という列番号を取得する関数を使えば、A3もB3も同じ式で表現できますが、そこまでする必要も無いと思いますので。
あとはA3とB3を必要な数だけコピー(セルの右下をドラッグでも可)します。
この時、該当するデータの数よりも下のセル(3つしか該当しないのに4つ目を検索しているセルなど)は#N/Aといったエラーが表示されると思います。
えらーが表示されるのが嫌であるならば、式を以下の様に書き換えます。
A3=IF(ISERROR(VLOOKUP(ROW()-ROW(A$2),Sheet1!E:G,2,FALSE))=TRUE(),"",VLOOKUP(ROW()-ROW(A$2),Sheet1!E:G,2,FALSE))
ISERROR(○○)というのは、○○がエラーであるならTRUE、エラーでないならばFALSEを返す関数です。
TRUE()というのはTRUEを返す関数です。先ほどのTRUEというのは文字列ではないので、一致するか確認するためにはこれが必要です。
つまり、最初にA3に入っていた式がエラーであるならば空白、エラーでないならばその式の値を表示する。という式になっています。
B3も同様で
B3=IF(ISERROR(VLOOKUP(ROW()-ROW(B$2),Sheet1!E:G,3,FALSE))=TRUE(),"",VLOOKUP(ROW()-ROW(B$2),Sheet1!E:G,3,FALSE))
となります。
長くなりましたがお分かりになりましたでしょうか?
No.2
- 回答日時:
個人的には1つのセルで全てを処理しようとすると、後で修正する必要ができた時等に苦労する可能性があるので、可能ならば他のセルも利用しながらにしたいです。
何をしたいのかというと、シート1の左端に列を挿入する事は問題ありませんか?
可能であれば、この新A列に「A列の日付がシート2で指定した期間に含まれているか?という判定を行い、含まれている場合、D列が空白であれば空白、空白でなければ空白でない何個目のデータか」というのを表示させる式を入れます。
日付を表示したいセルには、VLOOKUPでN個目のデータがある日付を表示させる式を入れ、同様にデータを表示させたいセルにはVLOOKUPでN個目のデータを表示させる式を入れます。
この方法を使うつもりでしたら再度式の中身について詳細を書かせていただきますので、ご連絡ください。
No.1
- 回答日時:
こんばんは!
一例です。
↓の画像のような配置とします。
Sheet2の3行目項目名はSheet1の項目名と同じにしておいてください。
画像ではSheet2のA4セルに
=IFERROR(INDEX(Sheet1!$A$1:$D$1000,SMALL(IF((Sheet1!$A$1:$A$1000>=$A$1)*(Sheet1!$A$1:$A$1000<=$B$1)*(Sheet1!$D$1:$D$1000<>""),ROW($A$1:$A$1000)),ROW(A1)),MATCH(A$3,Sheet1!$1:$1,0)),"")
配列数式ですので、Ctrl+Shift+Enterで確定!
A4セルのフィルハンドルで隣りのB4セルまでコピー! → A4セルの表示形式を「日付」にし
A4・B4セルを範囲指定 → B4セルのフィルハンドルで下へコピーすると
画像のような感じになります。
※ 細かいエラー処理はしていません。m(_ _)m
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセル表作成について 5 2023/03/12 13:25
- Excel(エクセル) ユーザー定義について質問です。 2 2023/06/28 13:21
- Excel(エクセル) 【エクセル」 特定のセルで条件抽出した列を、別シートに上から詰めて表示したい。 8 2022/04/08 16:00
- Excel(エクセル) Excel あらかじめ予定表があり、その月毎のセルに、リストの連続データを入れたい 2 2022/04/07 14:20
- Excel(エクセル) スプレットシートでA1~G1にデータが入っていて、 それを1列ずつ空けて表示する関数がわかる方いまし 4 2022/08/25 09:39
- Excel(エクセル) Excel VBA 空白行があるセル範囲に色を付ける 3 2022/06/13 15:58
- Excel(エクセル) 日付以外のデータを抽出したいのですが、 6 2023/06/27 13:32
- Excel(エクセル) エクセルの昇順での数値入力のミスの見つけ方を教えてください。 4 2022/06/26 20:41
- Excel(エクセル) エクセルの関数式を教えてください。 2 2022/11/29 21:09
- その他(データベース) Accessのクエリで1フィールドの抽出条件設定をNullでなく全角半角含む空白のみの文字列でない文 1 2023/04/24 15:20
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル初心者です 関数の入れ...
-
Microsoft1Officeの互換ソフト...
-
Excel ピボットテーブルで日付...
-
エクセル関数を教えてください
-
【マクロ】その時、その時で変...
-
【マクロ】読取専用のファイル...
-
LOOKUP関数を使えばいいのでし...
-
エクセル 白黒印刷で白線を印刷...
-
【関数】先頭だけにある、半角...
-
【関数】適切な文字数の数字を...
-
Excelのチェックボックスの使い...
-
エクセルでの作業計算方法について
-
Excelのpivotについて質問です
-
WPS OFFICEでの縦書きについて
-
時間によってファイル名が変わ...
-
エクセルのセルに同じ大きさの...
-
Aというブックの1というシート...
-
エクセルの順位別一覧表の自動...
-
西暦や和暦の表示をyyyymmdd表...
-
【マクロ】エクセルにかいてあ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報