- 締切済み
検索値が1つに対して複数ヒットする関数
月次請求書をエクセルで作成します。(Sheet1) 受注情報は別シート(Sheet2)に入力しています。 Sheet2の入力内容 A1 受注日 B1 顧客番号 C1 顧客名 D1 商品名 月締めですから、X顧客は1点のみ購入、Y顧客は3点購入など、バラバラです。 顧客番号をキーにして、VLOOKUPのように請求書に反映させる関数はありますか? *フィルター以外で Sheet1のレイアウト A1 顧客番号 *複数あっても表示は1つ B1 顧客名 *複数あっても表示は1つ C1~ 受注日 *複数ある場合は複数行 D1~ 商品名 *複数ある場合は複数行
- linktyo
- お礼率40% (4/10)
- SE・インフラ・Webエンジニア
- 回答数2
- ありがとう数7
- みんなの回答 (2)
- 専門家の回答
みんなの回答
- kagakusuki
- ベストアンサー率51% (2610/5101)
まず、適当な使用していない列(ここでは仮にSheet3のA列とします)を作業列として使用する事にして、その列の1行目のセル(Sheet3!A1セル)に次の関数を入力して下さい。 =IF(INDEX(Sheet2!$A:$A,ROW())="","",INDEX(Sheet2!$A:$A,ROW())&"■"&COUNTIF(Sheet2!$A$1:INDEX(Sheet2!$A:$A,ROW()),INDEX(Sheet2!$A:$A,ROW()))) 次に、Sheet3のA1セルをコピーして、Sheet3のA2以下に貼り付けて下さい。 次に、Sheet1のB1セルに次の関数を入力して下さい。 =IF(AND($A$1<>"",COUNTIF(Sheet2!$B:$B,$B$1)),VLOOKUP($B$1,Sheet2!$B:$C,2,FALSE),"") 次に、Sheet1のC1セルに次の関数を入力して下さい。 =IF(OR($A$1="",ROWS($1:1)>COUNTIF(Sheet2!$B:$B,$B$1)),"",IF(INDEX(Sheet2!$A:$A,MATCH($A$1&"■"&ROWS($1:1),Sheet3!$A:$A,0))="","",INDEX(Sheet2!$A:$A,MATCH($A$1&"■"&ROWS($1:1),Sheet3!$A:$A,0)))) 次に、Sheet1のD1セルに次の関数を入力して下さい。 =IF(OR($A$1="",ROWS($1:1)>COUNTIF(Sheet2!$B:$B,$B$1)),"",IF(INDEX(Sheet2!$D:$D,MATCH($A$1&"■"&ROWS($1:1),Sheet3!$A:$A,0))="","",INDEX(Sheet2!$D:$D,MATCH($A$1&"■"&ROWS($1:1),Sheet3!$A:$A,0)))) 次に、Sheet1のC1~D1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。 以上で準備は完了で、後はSheet1のA1セルに顧客番号を入力しますと、その顧客番号のデータのみが抽出されて表示されます。 尚、この方法では、Sheet1のコピーシートを作成しますと、そのコピーシートにおいても別の顧客番号のデータを表示させる事が出来ますから、顧客番号ごとに請求書のシートを作成しておくという事も出来ます。
- Cupper-2
- ベストアンサー率29% (1342/4565)
やり方は人それぞれと思いますので、自分ならこうするというやり方で…。 Sheet1のA1、B1セルは普通に顧客番号を入力したセルや、 その値を使いVLOOKUP関数を使うなどして表示させましょう。 A1=顧客番号 B1=VLOOKUP(顧客番号,A:B,2,-1) 問題は複数ある場合です。 ここから自己流になります。 対象になる行にマークして、そのマークのある行のデータを読み取ればいいということです。 Sheet2のE列にCOUNT関数を使い、指定した顧客番号で範囲(Sheet2のA列)に対してその数を数えます。 E1=COUNTIF(A$1:A1,顧客番号) これを入力した範囲の最後の行までコピーします。 するとデータがヒットする都度、上から数値が加算されていきます。 あとはINDEX関数で1から最大カウント数までの数値で値を引っ張ってきたら出来上がり。 こんな感じかな。 C1=INDEX(C:C,MATCH(ROW(A1),E:E,0)) D1=INDEX(D:D,MATCH(ROW(A1),E:E,0)) MATCH関数は一番初めに見つけたセルを返してくるので「加算された」行を選択することになります。 それぞれ適当な行までコピーしてください。 しかし、これだけでは最大カウント数よりも大きい数字を拾ってこようとするとエラーになりますので、 最大カウント数よりも大きい数字を拾うことの無いようエラートラップをIF関数で作るとよいでしょう。 C1=IF(MAX(E:E)>ROW(A1),"",INDEX(C:C,MATCH(ROW(A1),E:E,0))) みたいにね。 なお、検索に使う顧客番号を入力するセルが示されていませんでしたので、 そのセルに範囲名「顧客番号」を設定したとしています。、 また、記述が面倒なのでシート間の参照に Sheet2!A1 のような表現をせず 単に A1 などとしていますので適当に読み替えてください。 あくまでも自己流ですので、もっとスマートなやり方があると思います。 自身でも考えてみてください。
関連するQ&A
- VBAで検索して、行をコピー&追加したい
Excel2010で以下のことをしたいのですが、VBAがあまりできないのでやれません。 どうか助けてください。 ・sheet1のA列に検索用の番号(例として商品番号)が入力されています。 ・sheet2はデータベースで、A列に商品番号B列に商品名、C列に国名、D列に価格・・~その後J列まで情報が入っています。(行数は1万行) ・sheet1に入っている商品番号でデータベースから行をピックアップし、該当の行をsheet1のB列以降にコピーしたいのです。 (シート3を新しく作っても構いません。やりやすい方で) ・ただし、同じ商品番号で複数の行がヒットしますので、複数の行がヒットしたら行を追加しながら、行をコピーしたいです。 どのように書いたら良いか参考になるURLだけでもご教授ください。 よろしくお願いします。
- ベストアンサー
- Excel(エクセル)
- エクセルで複数シートを検索して抽出するには
いつも参考にさせていただいています。 過去の質問を見てもわからなかったので回答をお願いします。 顧客管理台帳を作成していますが、他のファイルの複数のシートを検索してデータの抽出をしたいのです。 顧客管理台帳というファイルのシートに顧客一覧があります。 A番号 B顧客名 C住所 D対応状況 進捗表というファイルにランクA、B、Cのシートがあります。 A番号 B顧客名 C住所 D内容 E進捗状況 AAAA ○○×× ・・・ 保留 PPPP ××△△ ・・・ 終了 シートごとに顧客名の重複はありません。 顧客名はランダムに並んでいます。 顧客管理台帳の顧客名を進捗表の各シートから検索して 対応状況に進捗状況を反映させたいのです。 進捗表の一覧を作るとVLOOKUP関数でできると思うのですが シート毎の管理をし、日々更新しているので 現状のままでできる方法はないでしょうか。 よろしくお願いいたします。
- ベストアンサー
- オフィス系ソフト
- 関数で出来ますか?。
エクセルのシートでA列に固定顧客番号が入力されていきます。 B列に顧客氏名が入力されます。 A列に不特定多数の番号が入力され、たとえば500行目に1234と入力したならその500行目のB列に過去に入力された1234の顧客名が表示されるようにしたいのですが。 A列は日々入力され2000行くらいまで使用します。 もしできるようでしたなら何処にどのような関数を入れればよいか教えてください。 宜しくお願いいたします。
- ベストアンサー
- オフィス系ソフト
- シートごとに参照先セルを変更するには
エクセルで請求書を作成している初心者です。 請求元データシートには請求先顧客名、商品名、数量、金額が一覧で入力してあります。 そのシートを元データとして、各請求先別シートが70シートあります(70件の請求書を作成)。 元データのA2~A71に顧客名、B2~B71に商品名、C2~C71に数量、D2~D71に金額が入力されており、A社請求シートには下データA2、B2、C2、D2を参照し、B社請求シートにはA3、B3、C3、D3と参照のセルを変えていきたいのですが、シートをコピーすると数式もコピーされてしまい、参照元を手入力で変更していて気が遠くなります。 検索してもよくわからなかったのですが、このシートにはこの行を参照、というように自動で変える数式か方法はありませんでしょうか? 初歩の質問でしたら申し訳ありません・・・。
- ベストアンサー
- Excel(エクセル)
- エクセル 関数 データを並び替えに合わせる
元シートC列にID番号があり、EC列まで受注情報や顧客情報が入っています。 C列のIDは重複、空白が混ざっています。 シートはロックをかけており、マクロのみで行の追加が行えます。 同じ顧客が複数受注することがあるので、マクロで行追加をする際には、選択行の真下に選択行を丸々コピペし、受注内容だけを削除するようにしています。 行の削除は管理者のみが行えます。 並び替えはIDや会社名など数列、マクロで行えるようになっています。 しかし顧客情報を変更する場合は複数行忘れずに変える必要等の不便があるので、別シートに各IDのデータを集めた顧客シートを作成し、そこから読み込むようにしました。 ユーザーフォームで元シートから情報を読み込み、編集は顧客情報の部分は顧客シートへ、受注内容は元シートへ記述します。 現時点では元シートへの直入力もできる状態です。 顧客シートはA列にIDを表示し、 =IF(COUNTIF(元!C$5:C6,元!C6)>1,"",元!C6) をドラッグして、元シートにIDが追加されても重複しないIDを表示し、その隣へ顧客情報を並べています。 ところが、元シートをIDやその他の列で並び替えた時、顧客シートで読み込んでいるIDも並び替わってしまい、その横の顧客情報と一致しなくなってしまいます。 1. 元シートを並び替えた時、顧客シートの情報はIDとマッチさせたままにする 2. 元シートに新規追加した際、IDが空白の場合は、仮IDとして自動で【仮1】などを順に振っていく。後で編集可能 顧客シートは現在A列が元シートから引っ張ってきたIDですが、ID列以降横に顧客情報が並べられれば、A列の前に作業列を数行挿入してもOKです。 新規顧客は初めからIDが無いため、ID無しでその他の顧客情報を記録するところから始まります。 しかし顧客情報はIDを基に管理したいので、ID発行までは仮IDを自動で振りたいと思っています。 どうぞ宜しくお願いいたします。
- 締切済み
- Excel(エクセル)
- エクセルVBAで質問です。
エクセルVBAで質問です。 エクセルで商品在庫管理をしていています。 A列(顧客名)B(商品名)C(受注日)D(次回受注日)E(必要在庫数) 中村 あ 7/1 8/1 10 田中 い 6/20 7/30 20 佐藤 う 7/20 8/20 15 中村 あ 8/1 10/1 20 渡辺 う 8/14 9/15 30 中村 か 7/30 8/30 30 A列(顧客名)とB列(商品名)が同じで、D列(次回受注日)が複数存在する場合、 受注日の古い行だけ色を付けることは可能でしょうか?
- ベストアンサー
- Visual Basic
- エクセルで検索して別シートに抽出したい
エクセル2003で顧客リストを作成しています。 そのリストの中から特定の文字列を検索し(あいまい検索)、その項目が含まれる行ごと 別シートに抽出をしたいです。 ほかの方のご質問も参考に試してみたのですが、上手くいきません。 1行目に各項目名 A日付 B担当1 C担当2 D管理番号 E顧客名 2行目からデータです。 ほぼ担当1か顧客名で検索し、検索結果は複数になることがほとんどです。 どなたかよろしくお願いいたします。
- ベストアンサー
- その他MS Office製品
- 《Excelマクロ》請求書のデータを一覧表に抽出したい
こんにちは。いつも参考にさせてもらってます。 下記のような請求書に入力したデータなんですが、 A B … E F G H 5 顧客codeあ|顧客名あ ・ 11 商品codeA|商品名A 単価A|仕入値A|数量|金額 ・ 商品codeB|商品名B 単価B|仕入値B|数量|金額 ・ 商品codeC|商品名C 単価C|仕入値C|数量|金額 50 ------------------------------------------------------------ 入力を済ませたら直ちに別のシート(仮に「一覧表」)に下記のように 抽出したいのです。 A B C D E … FV 1 商品codeA|商品codeB|商品codeC … 2 商品名A |商品名B |商品名C … 3 顧客codeあ|顧客名あ 単価A | 単価B | 単価C … 4 顧客codeあ|顧客名あ 仕入値A |仕入値B |仕入値C … ・ 顧客の数に応じて増減 ※1~2行目の商品code・商品名についてはあらかじめシート内に記載 ※1つの顧客に、単価と仕入値、2行の表記が出てきます ------------------------------------------------------------- 1つの顧客について、請求書に入力を済ませたらボタン操作で 一覧表に抽出し、また新たな顧客を請求書に入力、抽出・・・の繰り返し 作業を行いたいと思っています。 なおかつ、最終的にはどの顧客にもまったく売れていない商品(単価・仕入 共に0)の列を省いて体裁を整えたいと考えています。(横に長すぎるので) 似たようなマクロは見かけるのですが、知識が乏しいので応用がきかず うまく生かすことができません・・・ マクロは自動記録しかやったことがないので、完全に行き詰っています。 皆さんのお知恵を貸してください。よろしくお願いします。
- 締切済み
- オフィス系ソフト
- エクセルで 複数のデータを検索して複数表示させたいのですが・・・。
エクセルで在庫の場所がチェックできる下記のような物を作りたいと考えています。 在庫リスト シート1 A B C 1行目 商品名 色 場所 2行目 あいう 赤 A1 3行目 かきく 黄 A2 4行目 さしす 青 A3 5行目 あいう 黄 A4 6行目 かきく 青 A5 ・ ・ ・ ・ 在庫リスト シート2 A B C 1行目 商品名 色 場所 2行目 あいう 青 B1 3行目 かきく 緑 B2 4行目 さしす 桃 B3 5行目 あいう 茶 B4 6行目 かきく 桃 B5 ・ ・ ・ ・ 上のような表を作成し、別のシート(シート3)で 検索名 あいう と打ち込むと 赤 A1 黄 A4 青 B1 茶 B4 という具合に同じ商品を検索し、色と場所を全て表示させるようなものをエクセルで作成できたらと思っています。可能でしょうか?また、可能であればどのような関数を使って作成したらよいのかを教えていただきたいです。vlookupが使えるかと思ったのですが、それだと一番上のデータ(この例でいけば 赤 A1)のみの表示になってしまい、使えませんでした。 あと、欲を言えば、 商品名を全て打ち込みをしなくても、一部のみ(この例でいけば 「あいう」 ではなくて 「あい」のみ)を打ち込むだけでその字を含む商品名を検索し、表示してくれるような表記の仕方があるともっとありがたいです。在庫リストは在庫の場所ごとに複数のシートに分かれています。 よろしくお願いします。
- 締切済み
- オフィス系ソフト
- エクセルで、条件にあう複数の行を別のシートに抽出する
《どなたか、お教え下さい》 エクセルで、条件にあう複数の行を別のシートに抽出してまとめる。 「sheet1」の下記の基本データを「sheet2」に「得意先」ごと「日付」の早い順にまとめたいのでが。関数を使ってできないものか、どなたかお教え下さい。「マクロ」や「Access」は使用したことがないので、関数を使ってやりたいと思いますが、できるものでしょうか。よろしくお願いします。 sheet1 番号 得意先 日付 商品名 商品詳細 金額 備考 1 A 2 B 2 B 4 D 5 E 6 F 2 B 3 C 5 E A B 番号 得意先 1 A 2 B 得意先名は、番号を入力することで表示されるように 3 C 関数「VLOOKUP」を使って表示させるようにしていま 4 D す。 5 E 6 F
- ベストアンサー
- オフィス系ソフト