EXCEL関数で重複するデータが存在するシートから,
複数条件で検索して抽出する方法を教えてください。
<データシート>
A B C D
1行目 品番 色 m数 倉庫NO,
2行目 KR-71XXXCC BE 46.0 6中
3行目 KR-71XXXCC BE 46.0 6下
4行目 KR-71XXXCC NAVY45.4 7下
5行目 KR-71XXXCC NAVY50.0 7下
6行目 KR-71XXXCC NAVY20.7 6中
7行目 KR-71XXXCC NAVY48.0 6中
8行目 KR-71XXXCC NAVY43.5 6中
9行目 KR-71XXXCC NAVY41.4 6下
10行目 ZPM-3355A3 BK 4.0 8中
11行目 SG-3006B WHITE11.06下
12行目 SG-3006B WHITE10.06下
13行目 NJ-4363 BLACK13.58中
<検索結果シート>
A B C D E
1行目 6中 8中
2行目 KR-71XXXCC BE 46.0 6中 ZPM-3355A3 BK 4.0
3行目 KR-71XXXCC NAVY20.7 6中 NJ-4363 BLACK 13.5
4行目 KR-71XXXCC NAVY48.0 6中
5行目 KR-71XXXCC NAVY43.5 6中
6行目
7行目 7下
4行目 KR-71XXXCC NAVY45.4 7下 ・・・・・・・
5行目 KR-71XXXCC NAVY50.0 7下
A2に入っている式は
=INDEX(データシート!A:A,SMALL(INDEX(SUBSTITUTE((データシート!$E$1:$E$300=$A$1)*1,0,10^5)
*ROW(データシート!$E$1:$E$300),),ROW(V1)))
です。過去質問を参考に作成したのですが,6行目,E列以降に検索結果を表示させようとすると,
0(ゼロ)表示になります。
上記のように結果を表示させる為にはどのような関数を使えばよろしいでしょうか?
よろしくお願いします。
A 回答 (5件)
- 最新から表示
- 回答順に表示
No.5
- 回答日時:
No.3です。
>2行目以降が何も表示されません。
というコトですが、もしかして配列数式になっていないのではないでしょうか?
① 前回書いたようにこの画面から数式をドラッグ&コピーし、Sheet2のA2セルを選択
② 数式バー内に貼り付け
①・②が完了したら
以下のいずれかの操作でもよいので、一つ操作してみてください。
・ 数式バー内が編集状態になっているか確認!
・ もう一度数式バー内をクリック!
・ A2セルが選択されている状態でF2キーを押下し、編集可能にする。
・ A2セルをダブルクリック!
以上の4つのどれでも構いませんA2セルを編集状態にし、
Ctrl+Shiftキーを押しながら Enterキーを押下してみてください。
数式バー内を確認し、数式の前後に{ }マークがついていれば配列数式になりますので、
その状態でフィル&コピーしてみてください。m(_ _)m
No.4
- 回答日時:
貴方の希望にはドンピシャではないので、“私ならこうする”別解ということで。
(添付図参照→拡大すればかろうじて判読できる?)Sheet1
_1.セル E2 に式 =ROW() を入力して、此れを下方に
_____ズズーッとドラッグ&ペースト
_2.セル G2 から下へ、庫番を表示させたい順に入力
_3.範囲 A1:E1000 を選択
_4.[数式]→[定義された名前]→[選択範囲から作成]で
____“上端行”のにみチェックを残して[OK]をパシーッ
Sheet2
__5.セル A1 から右へ、庫番、品番、色、m数、TMP を入力
__6.次の各セルにそれぞれの右側の式を入力
___A2: =OFFSET(Sheet1!G$2,(ROW(A1)-1)/7,)
___E2: {=IFERROR(SMALL(IFERROR(INDIRECT(E$1)/(INDIRECT(A$1)=A2),""),IF(MOD(ROW(Sheet1!A1),7)=0,7,MOD(ROW(Sheet1!A1),7))),"")}
_____(この式は配列数式であることに注意)
___B2: =IFERROR(INDEX(INDIRECT(C$1),$E2-1),"")
___セル B2 を右列にドラッグ&ペースト
__7.範囲 A2:E2 を下方にズズーッとドラッグ&ペースト
No.3
- 回答日時:
こんばんは!
「別Sheet」のレイアウトを変更すればもっと簡単になると思いますが、
極力お望みの配置でやってみました。
↓の画像のように元データがSheet1にあり、Sheet2に表示するとします。
Sheet2のA2セルに
=IFERROR(INDEX(Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$D$1:$D$1000=$A$1,ROW($A$1:$A$1000)),ROW(A1))),"")
配列数式になりますので、Ctrl+Shift+Enterで確定!
この画面からコピー&ペーストする場合は
上記数式をドラッグ&コピー → Sheet2のA2セルを選択 → 数式バー内に貼り付け → そのまま(編集可能なまま)
Ctrl+Shiftキーを押しながらEnterキーで確定!
数式の前後に{ }マークが入り配列数式になります。
このA2セルのフィルハンドルでD2セルまでコピー → そのまま下へ9行目までコピー!
これでA1セルに表示されているデータが表示されます。
次にA2セルを選択 → 四辺のどこでも良いので上下左右の小さな矢印になるところにマウスポインタを移動させ
Ctrlキーを押しながらA11セルまでドラッグ!
数式が
=IFERROR(INDEX(Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$D$1:$D$1000=$A$1,ROW($A$1:$A$1000)),ROW(A10))),"")
となるはずですので、数式バー内で再編集し
=IFERROR(INDEX(Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$D$1:$D$1000=$A$10,ROW($A$1:$A$1000)),ROW(A1))),"")
とします(この画面からコピー&ペーストしても構いません)
微妙に数式が違っているので注意してください(A1とA10を再編集)
A11セルも配列数式ですので、同様にCtrl+Shift+Enterで確定し、列・行方向にフィル&コピー!
最後にC列の小数点以下の表示形式を調整して完了です。
※ さらに下に別データが続く場合、同様にCtrlキーを押しながらドラッグし
数式をそのままコピーしますが、
参照先の変更(Sheet1!$D$1:$D$1000=$A$10 の部分のA10セル番地の変更)
と最後は必ず ROW(A1) にする必要があります。
まずはこの程度で・・・m(_ _)m
ありがとうございます!
早速A2セルに数式をコピーして,
そのままD2セルまでコピー,
9行目までドラッグしたのですが
2行目以降が何も表示されません。。
No.2
- 回答日時:
数式を見るとデータシートはE列まであるようですが、
質問には書かれていません。
区切り位置が図りかねます。
それとも、A1セルに「1行目」、A2セルに「2行目」って入っているのでしょうか?
また、結果シートのA8セルに「4行目」、A9セルに「5行目と」入っているのでしょうか?
>EXCEL関数で重複するデータが存在するシートから,
>複数条件で検索して抽出する方法を教えてください。
具体的に何をしたいのでしょうか?
また、数式でなければいけないのでしょうか?
なんとな~く、数式から読み取れますが、セルの位置が分からないし、
上記説明と食い違っているように感じられる。
抽出するデータはすべて5件以内なのでしょうか?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) VBAで、1つのエクセルで、2つのシートからもう1つのシートに条件のある転記コードを教えてください。 1 2023/03/16 18:07
- Visual Basic(VBA) 列と行の名前(重複あり)が交差するセルに、データを入力したい 3 2022/06/12 11:17
- Excel(エクセル) VLOOKUP が機能しない、その原因は何 ? 8 2022/10/19 12:06
- Excel(エクセル) 【困っています】VBA 追加処理の記述を教えてください。 1 2022/08/25 22:54
- Visual Basic(VBA) 列と行の名前(重複あり)が交差するセルに、データを入力したい 2 2022/06/25 22:42
- Excel(エクセル) Excelの列から検索して該当する行を別シートに転記するVBA 2 2022/12/20 09:35
- Visual Basic(VBA) 集計シートA列のコードと一致する右に並んだシート名(コード)の3行目から10行目をコピーして貼り付け 4 2022/08/18 15:24
- Visual Basic(VBA) 【ご教示ください】VBAの記述方法がわかりません。 2 2022/08/12 21:28
- Visual Basic(VBA) 【困っています2】VBA 追加処理の記述を教えてください。 2 2022/08/26 11:42
- Visual Basic(VBA) VBAで、シート間の転記するコードをFOR~NEXTで教えてください。 9 2023/04/30 20:04
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【関数】スペースがいくつ入っ...
-
西暦や和暦の表示をyyyymmdd表...
-
【Microsoft Office Excel Comp...
-
Excelはなんで先頭の0を消すん...
-
Excelのセルを飛ばして入力する
-
別シートからの文字を変更
-
エクセルの行の抽出について質...
-
Excelのオートフィル
-
Excel 2019 のピボットテーブル...
-
スプレッドシート クエリ関数 1...
-
excelの不要な行の削除ができな...
-
Excel初心者です。 詳しい方、...
-
【Excel】セル内の時間帯が特定...
-
Excel初心者です。 詳しい方、...
-
EXACT関数とIF関数の組み合わせ...
-
Excelのグラフ軸について
-
スマートな関数を教えて下さい。
-
Excelで全角を半角にしたいので...
-
【マクロ】エクセルにかいてあ...
-
Excel:一部のフォントでセルの...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報
質問内容が分かりづらくて申し訳ございません。データシートを書き直しました。データ件数は全部で200件あります。
A B C D E
1行目 品番 色 m数 倉庫NO,
2行目 X-1 BE 46.0 6中
3行目 X-1 BE 46.0 6中
4行目 X-1 NV 32.0 6中
5行目 X-1 NV 50.0 7下
6行目 X-1 NV 20.7 6中
7行目 X-1 NV 48.0 6中
やりたいこと:シートを分けて場所ごとのリストを作りたいです。データシートの内容が更新されたら,場所別シートのデータも変わるように同期したいです。2行目,3行目のように同じ倉庫NO,で同品番/M数が2つ以上あります。
何度もすいません。投稿すると文字の位置がずれてしまうので
区切りが分かりにくくなるのですね。
データシートと場所別シートの画像を添付します。
場所別シートの画像です。
データシートに新しい品番が加わったり,M数が変わったら場所別シートも自動的に
更新されるようにしたいです。