お世話になります。
どうしてもわかりません…
エクセル名人の皆様、どうかお知恵をお貸しくださいませ。
Sheet1に下記のようなデータが約5000件あります。
A B C D … BP …
1 No. ID. 品目 月日…状況 …
2 1 a33 A 3/4 0
3 2 a87 B 5/11 1
このうち、BP列が1のもの約900件のみがSheet2に抽出されています。
(このためのマクロについて前回質問させていただきました)
Sheet2:
A B C D E F G …
1 No. ID 品目 月日
2 2 a87 B 5/11
3 7 b3 K 2/5
4 18 c44 F 12/1
5 11 r93 J 7/28
ちなみにA列のNo.は1,2,3,…と続く通し番号で、だぶりはありません。
Sheet1のデータの項目数は大変多いのですが、Sheet2にはその項目すべては必要でないため、
Sheet2には上記のとおりAからD列までの必須情報のみ抽出し、
E列以降の情報については、必要な列の情報のみ、
A列の通しNo.で紐づけしてVLOOKUP関数で取得したいと考えています。
しかし、Sheet1の表には空欄があり、これを空欄のまま表示させるために
IF関数を使いたいのですが、
Sheet1の参照元のセルの番地をどう書けばいいでしょうか?
とてもわかりにくくて申し訳ありません…
たとえば、Sheet2のA列が18であれば(上記Sheet2のA4)、
元データはSheet1の19行目にあることになります。
A列の数値+1、が行番号ということです。
そのK列を参照する場合、
=IF(Sheet1!K19="","",VLOOKUP(A4,Sheet1!A:K,COLUMN(K4),FALSE)
となるかと思うのですが、
このSheet1!K19 というセル番地を、自分で計算して手入力するというのはとても大変なので、
関数などで表現する方法はないでしょうか。
たとえば、Sheet1!K(A4+1) とか書いてみましたが、もちろんこれでよいわけもなく…
不勉強で恐縮ですが、どうしてもわかりません。
どうかご教授ください。
よろしくおねがいいたします。
No.7
- 回答日時:
No.6です!
たびたびごめんなさい。
投稿後に思ったのですが・・・
Sheet1の1行目は項目が入っているのですよね?
そうであればSheet2の1行目は列番号でなく
表示したい「項目名」を入力した方が良いと思います。
その場合の数式は
E2セルを
=IF(OR(E$1="",$A2=""),"",INDEX(OFFSET(Sheet1!$A:$A,,MATCH(E$1,Sheet1!$1:$1,0)-1,,1),MATCH($A2,Sheet1!$A:$A,0)))
として、列・行方向にオートフィルでコピーしてみてください。
(前回の数式は列方向のオートフィルは考慮していませんでした)
※ 空白の「0」の表示の件についてはNo.1さんが回答してくださっているので
その方法を利用させてもらう手もあると思います。
何度も失礼しました。m(_ _)m
No.6
- 回答日時:
こんにちは!
Sheet2のE1セルは単に表示したい列番号を入力するだけとします。
今回の質問の場合は K と入力
E2セルに
=IF(OR(A2="",E$1=""),"",INDEX(INDIRECT("Sheet1!"&E$1&":"&E$1),MATCH(A2,Sheet1!A:A,0)))
という数式を入れオートフィルで下へコピー!
※ Sheet1の表示したいセルが空白の場合は「0」が表示されてしまいますので、
Excelのオプション → 詳細設定 → 「次のシートで作業するときの・・・」 → 「ゼロ値」のチェックを外しておきます。
尚、Sheet2で他の項目でどうしても「0」を表示しなければならない場合は
IF関数で上記数式が空白の場合の条件を追加してやります。m(_ _)m
前回に引き続き、貴重なお時間を割いてご回答くださったこと、
心から感謝いたします。また、わざわざオートフィルのことまで
ご考慮いただき追加のご回答をいただきましたことも、
重ねて御礼申し上げます。
こちらで質問させていただくごとに自分の不勉強が身に沁みます。
またお世話になることがあるかもしれませんが、どうぞよろしくお願いいたします。
No.2
- 回答日時:
他にも方法はあると思いますが、例えば、
=IF(INDIRECT"Sheet1!K"&(A4+1))="","",VLOOKUP(A4,Sheet1!A:K,COLUMN(K4),FALSE)
でどうでしょうか。もしくは、
>たとえば、Sheet2のA列が18であれば(上記Sheet2のA4)、
>元データはSheet1の19行目にあることになります。
>A列の数値+1、が行番号ということです。
なのですから、VLOOKUPで廻さなくても
=IF(INDIRECT"Sheet1!K"&(A4+1))="","",INDIRECT"Sheet1!K"&(A4+1)))
とか。
この他にもOFFSET関数を使ってもできそうです。
No.1ベストアンサー
- 回答日時:
基本のご質問:
シート1のK列の18+1=19行目は
=INDEX(Sheet1!K:K,A4+1)
で直接参照します。わざわざVLOOKUPする必要「も」ありません。
次のご質問:
>これを空欄のまま表示させるために
K列の内容が「文字列」だった場合は
=INDEX(Sheet1!K:K,A4+1)&""
としておきます。
K列の内容が「数値」で、これを「数値として」計算結果を出しとかなきゃならない場合は
=IF(INDEX(Sheet1!K:K,A4+1)="","",INDEX(Sheet1!K:K,A4+1))
としておきます。
K列の内容が数値でも、結果は「文字列として表示するだけでOK」な場合は、先の&""の方式を利用できます。
前回に引き続きご回答いただきありがとうございます!
しかも、とてもスマートな関数に変身させてくださって、
大変勉強になりました。
心からの感謝をこめて、重ね重ね御礼申し上げます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) Sheet「状況」から、分類の年齢別カウント数をSheet「D表」へ転記する下記マクロを作っています 7 2022/12/14 17:57
- Excel(エクセル) SUMIFSと日付変換 10 2023/04/16 15:38
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Excel(エクセル) 【Excel質問】別シートにある複数の同型の表から、同じ行項目にある数字を集計する 4 2023/02/16 00:14
- Visual Basic(VBA) VBA 別sheetからの転記なのですが 2 2023/05/22 15:55
- Visual Basic(VBA) VBAでvlookup関数から、別シート参照するやり方・・・ 2 2022/11/14 18:49
- Excel(エクセル) Excelにて、行の最後のセルの値をコピーして別sheetに張りつけるVBAコードをご教授願います 3 2022/11/20 14:35
- Excel(エクセル) フォルダ内のエクセルファイルを開かずにデータ採取する関数式 2 2022/12/22 22:15
- その他(プログラミング・Web制作) pythonでクラスで複数のメソッドを利用する方法 2 2022/04/15 04:17
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
Excel 日付を比較したら、同じ...
-
Outlookを立ち上げたらGoogleロ...
-
outlookのメールが固まってしま...
-
会社PCのメールが更新されない
-
【スプレッドシート】指定の日...
-
ウィンドウィズ メモ帳で日付だ...
-
英数字のみ全角から半角に変換
-
Excelで空白以外の値がある列の...
-
Microsoft Formsの「個人情報や...
-
microsoft office
-
MicrosoftOfficeについて質問で...
-
マイクロソフト 一時使用コード...
-
outlookで宛先が異なるメールを...
-
Outlookでの時間指定送信機能に...
-
【スプレドシート】目標達成の...
-
自分の専門分野の仕事。初見で...
-
Microsoft Officeを2台目のPCに...
-
【Excel VBA】PDFを作成して,...
-
エクセルでXLOOKUP関数...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
【スプレッドシート】指定の日...
-
英数字のみ全角から半角に変換
-
会社PCのメールが更新されない
-
マイクロソフト 一時使用コード...
-
Office 2021 Professional Plus...
-
エクセルで例えば、関数を使っ...
-
Microsoft Formsの「個人情報や...
-
1つのPCに「Excel 2010」「Exc...
-
エクセルで例えば、A1に㈱ベ...
-
理由を教えてください。
-
エクセルでXLOOKUP関数...
-
マイクロソフト オフィスについて
-
VLOOKUP関数について
-
teams設定教えて下さい。 ①ビデ...
-
Googleのスプレッドシートでシ...
-
【Excel VBA】PDFを作成して,...
-
Microsoft365で写真をアルバム...
-
Outlook で宛先が複数の場合の人数
-
Excel テーブル内の空白行の削除
おすすめ情報