MATCH関数とINDEX関数の組み合わせ方についての質問
- シートから別ファイルの該当欄に金額データを表示する方法について質問です。
- 元データの条件セルが縦2行に配置されているため、関数の組み合わせ方に困っています。
- 地域は番号順に並び、商品名はアルファベットと数字が混ざり、ソートされていない状態です。
- ベストアンサー
MATCH関数とINDEX関数の組み合わせ方
あるシートから別ファイルのシートへ、"金額の値"をコピーしたいのですが、 条件セルが元データでは縦2行(地域と商品名）、コピー先では（列）に配置されているため、 どうやって関数を組んだら良いのかわかりません。 関数ではなく、マクロにしないと無理でしょうか？ （マクロでもいいのですが、コードがよくわからないので…） 又、地域はコードで番号順に並んでいますが、商品名はアルファベットと数字が混ざっており、 ソートされていない並び（変更不可）になっています。 Sheet1(元データ） A B C 1 地域 商品名 金額 2 (01関東) (AS01) (200) 3 (01関東) (333B) (250) このSheet1の金額データを別ファイル：Sheet2の該当欄に表示させたい。 Sheet2(コピー先） A B C D 1 地域＼ 商品名 (AS0100) (9999B) (AF205) 2 (01関東) （200） （300） （250） 3 (02中部） (50) (250) (290) どなたか、お知恵を拝借できれば幸いです。
- nekonote19
- お礼率60% (26/43)
- オフィス系ソフト
- 回答数3
- ありがとう数2
- みんなの回答 （3）
- 専門家の回答
質問者が選んだベストアンサー
- ベストアンサー
Sheet2!B2: =IF(SUMPRODUCT((Sheet1!$A$2:$A$10=$A2)*(Sheet1!$B$2:$B$10=B$1)),INDEX(Sheet1!$C$2:$C$9,SUMPRODUCT((Sheet1!$A$2:$A$10=$A2)*(Sheet1!$B$2:$B$10=B$1),ROW(A$2:A$10)-1)),"")
その他の回答 (2)
[ANo.1この回答への補足]に対するコメント、 「なぜ必要なのか」に対する答えは、それがないと所望の値が得られないからです。 ただし、ROW(A$2:A$10)-1 の代わりに ROW($2:$10)-1 でもＯＫです。 INDEX(配列,行番号,列番号)の「行番号」に相当する部分を算出するために必要、と言えばお分かりでしょうか？
お礼
理解しました！ ご丁寧にありがとうございます。
- grumpy_the_dwarf
- ベストアンサー率48% (1628/3337)
Sheet1の金額の前に1列足して、「地域&商品名」を作れば、 Sheet2!C3: =vlookup($A3&C$1, Sheet1!C:C, 2, FALSE) でおしまい。
お礼
ご回答ありがとうございます。 確かにこの方法が一番シンプルなのですが、 Sheet1にあたるファイルは書式を変更してはいけない ということと、商品欄の並び（これも変更不可）が 必ずしも昇順ではなかったので VLookupは使えないかと思うのです。 関数だけでやりくりするには、限界があるのかもしれません。
関連するQ&A
- エクセル関数２
シート(1) A B C D 作業列 コード 顧客名 金額 3 300001 A 10,000 3 300002 B 15,000 3 300003 C 20,000 3 300004 D 25,000 3 300005 E 30,000 4 400001 F 35,000 4 400002 G 40,000 4 400003 H 45,000 4 400004 I 50,000 4 400005 J 55,000 シート(2) A B C D E F 「1」 「2」 「3」 「4」 「5」 「6」 10,000 35,000 15,000 40,000 20,000 45,000 25,000 50,000 30,000 55,000 ※ シート(1)にコード、顧客名、金額を入力すると、シート(2)にあるように該当するコードの先頭行の列に表示するようにしたいと思っています。 シート１で作業列を作りその作業列を基にシート２でVLOOKUP関数を使い‥シート２の10,000のセルの場合、 VLOOKUP(C$2,sheet1!$A$3,$D$12,4,false)として、下にコピーすると同じ金額になってしまいます。 セルが空白にならず、シート１の金額が上から順番にシート２に表記されるようにするにはどうしたらよいでしょうか。マクロはできませんので、関数で詳しく教えて下さい。宜しくお願いします。
- 締切済み
- オフィス系ソフト
- MATCH関数？INDEX関数？
いつもお世話になってます。 2のエクセルデータ、ＡとＢがあります。 Ａのファイルが基本ファイルで、Ｂファイルが必要項目をＡより転記してみんなで閲覧するファイルです。 Ａのファイルが下記のように入力されているとします…。 日付 Ｎｏ. ロッド番号1 ロッド番号2 得意先 金額1 金額2 8/22 5-11 123 ○×商事 \100 8/23 5-12 555 △△株式会社 \200 8/24 5-13 124 有限会社◆ \500 \200 8/24 5-13 556 ○×商事 \300 8/25 5-14 557 有限会社◆ \800 ロッド番号1、ロッド番号2とそれぞれ別の商品があり、番号があり金額が入力されています。 Ｎｏ.は受付番号でロッド番号1、2をそれぞれ購入の場合は同じ受付番号を使います。 Bのファイルにはロッド番号1のシート、ロッド番号2のシートがありそれぞれＡより、必要項目が転記されています。 これまではロッド番号をもとにＶＬＯＯＫＵＰ関数でＢへ転記していましたが、Ｎｏ.が同じ場合にうまくいきませんでした。 おそらくMATCH関数、INDEX関数を使うのだと思いますが、どのように組んだらよいのかわかりません。 Aの項目内容は変えられません。 関数にお詳しい方、ご教授いただきたいです。
- ベストアンサー
- オフィス系ソフト
- ExcelでIndexとMatch関数が使いこなせない
すみません、カテゴリを間違えてまして、再度投稿いたします。 有給管理表をExcelで作成しています。 シート１に社員のデータ表を入力してあり、 シート２には有給が発生する社員へ有給の残日数や 付与日に関するお知らせの案内状をＢ５サイズで作っています。 シート１のデータを拾って、シート２の案内状へ反映させる際、 付与日や残日数などはVlookupで参照できるのですが、 社員番号は退職した社員がいるため番号が飛んでいて うまくできません。 IndexやMatch関数を使えばなんとかなりそうだと思い やってみましたが、VALUE!が表示されてしまいます。 どなたかうまくいく関数を教えて頂けますでしょうか？ シート１のデータ例 Ａ列 Ｂ列 Ｃ列 Ｄ列 Ｅ列 社員番号 氏名 入社日 付与日 付与日数 100001 くま 2007/4/1 2007/10/1 10 100003 たぬき 2007/5/1 2007/11/1 10 100005 うさぎ 2007/5/10 2007/5/10 10 100010 きつね 2007/6/1 2007/12/1 10 シート２（例）ページ１ Ｂ列 Ｃ列 B1 社員番号 100001←ここに式を入れたい B2 社員氏名 くま さん（vlookupでC1を拾ってくる） B4 あなたの有給付与日は 2007/10/1 です。付与日数は 10 日です・・・・・・。 シート２ ページ２ B30 社員番号 100003 （←ページ１の次の該当者を拾いたい） B31 社員氏名 たぬき さん B33 あなたの有給付与日は 2007/11/1 です。 付与日数は 10 日です・・・・・・。 このような感じです。社員番号が飛んでいても下の行を拾っていくような関数はありますでしょうか？（Index、Match関数以外でも結構です）
- ベストアンサー
- オフィス系ソフト
- excel index関数？changeイベント？？
作者不明のため、教えていただきたいのですがお願いします。 シートが４つほどあり、index関数でデータを５つめのシートで集計しています。 集計シート例： Ａ Ｂ Ｃ Ｄ Ｅ Ｆ Ｇ Ｈ ・・・ １ 【ここにドロップダウンのボックス？があります▼】 ２ あ い う え ・・・ ３●● １ １ ２ ２ ・・・ ４△△ ２ ２ ３ ２ ・・・ ５□□ １ １ １ １ ・・・ ・ ・ ●●シート例： Ａ Ｂ Ｃ Ｄ Ｅ Ｆ Ｇ Ｈ ・・・ １ あ い う え ・・・ ２ＡＡ １ １ １ １ ・・・ ３ＢＢ ０ １ １ １ ・・・ ４ＣＣ １ ２ ２ ２ ・・・ あいうえ項目が各シートにあり、集計シートでドロップダウンリスト（リストの下のセルＢ１には数字の割り当てがあります）からＡＡを選ぶと各シートからデータを引っ張ってきて（index関数がＢ１の数字を読み取ります）、各シートのデータが集計されます。 ＢＢやＣＣと選んでも簡単に集計してくれるので重宝していたのですが、 これを応用しようと新規ブックで似たものを作り、 index関数（例：=IF((INDEX(●●!$B$2:$H$10,$B$1,1))="","*",INDEX(●●!$B$2:$H$10,$B$1,1))） ※列番号も右セル（あ→い→う・・・）にいくごとに増えていきます。 もコピーしましたが「#REF!」となって集計してくれません。 元のブックを開いてドロップリストを右クリック→マクロの登録をみるとマクロ「ドロップ1_change」という登録が見えますが、マクロを調べても表示は Sub ドロップ1_Change() End Sub しかでてきません。 なにも入っていないのになぜ動くのでしょうか？ また他のブックではなぜ「#REF!」となるのでしょうか。 質問もどうしていいかわからないためわかりにくいかと思いますが、教えてください。この説明で不足している場合はご連絡下さい<(_ _;)>
- 締切済み
- オフィス系ソフト
- INDIRECT、MATCH関数等の組み合わせ方
入力フォームとして作成したシート（あああ）のデータを、シート（一覧表）に、計算式で自動的に並べる方法（参照する方法）を教えてください。（画像を添付しました。） 入力フォームは、実際は150枚ぐらいになります。一覧表も実際は数枚あります。 画像の「あああ」が入力フォーム、「一覧表」が一覧表です。 あああ、AAA、111、アアアなどは、すべて項目名です。 DATA1からDATA32、DATA100-103が、実際にデータが入る部分です。 例えばですが、入力フォーム（あああ）のセルC6に入力したDATA1が、一覧表のセルC3に入るように、C3に計算式を入れたいです。 １．シート名を計算式にいれるには、INDIRECT関数が使えるかと思います。 ２．複数の条件（A列、B列、1行、2行）でセルを特定する関数がわかりません。 ３．１．と２．を組み合わせると、同様な関数になるのか、わかりません。 DATAについては、ゼロはゼロ、無記入は無記入のまま一覧表に表示したいです。 よろしくお願いします。
- 締切済み
- Excel（エクセル）
- INDEX,MATCH使用方法
Sheet1 A B C D E 1 得意先コード入力 納品先候補： 納品先コード 納品先名 2 1001 1 あああ 3 2 いいい 4 3 ううう 5 4 えええ 6 5 おおお 7 1 かかか Sheet2 A B C 1 得意先コード 納品先コード 納品先名 2 1001 1 あああ 3 1001 2 いいい 4 1001 3 ううう 5 1001 4 えええ 6 1001 5 おおお 7 1002 1 かかか 8 1002 2 ききき 9 1002 3 くくく 10 1002 4 けけけ お世話になります。 Sheet1で得意先コードをA2に入力すれば、Sheet2から納品先候補を検索して表示するようにしたいのですが、現状ではSheet1のD2以下に下記のような関数を入れています。 D2：=INDEX(Sheet2!$B$2:$B$10,MATCH(Sheet1!$A$2,Sheet2!$A$2:$A$10,0)) D3：=INDEX(Sheet2!$B$2:$B$10,(MATCH(Sheet1!$A$2,Sheet2!$A$2:$A$10,0)+1)) 以下同様、E列も同じです。 これでも表示はされるのですが、ドラッグコピーが出来ない(+1,2…)ので手入力になるのとエラーや0を非表示にする為にさらにISERRORを組み込む事を考えますとかなりしんどい事になりそうですので、もっと良いやり方はないでしょうか。 さらに、入力した得意先コードの分のみの納品先候補表示が出来ればありがたいです。 宜しくお願い致します。
- ベストアンサー
- Excel（エクセル）
- Excel2000で、データの比較とコピー
こんばんは！ シート１に お店 商品コード 商品名 金額 01 05000 みかん 500 01 05010 メロン 1000 02 01000 いちじく 300 とあり、 シート２に お店 商品コード 商品名 金額 01 05000 みかん 500 01 05010 メロン 1300 02 01000 いちじく 300 となっている場合に、 商品コードが同じものの行を比較して、金額の変更があったら、シート３に 01 05010 メロン 1300 と行ごとコピーをしたいのですが、どのようにすれば良いでしょうか？ 何か良い関数や、マクロなどありましたらご伝授願います。
- ベストアンサー
- オフィス系ソフト
- ファイル間のシートコピー（Match関数とArray）
初心者の質問で申し訳ありませんが、困っているので教えてください。 AというファイルとBというファイルがあります。 Bがマクロ実行ファイルで、Bでは複数のマクロがあり、そのマクロにAのデータが必要です。 Aのファイルには13シートありますが、そのうちの5つのシートの情報を Bファイルのシートにコピーしたいというのが状況です。 （Bファイルには同名のシートが既にあり、Aファイルから毎回同じシート名のデータをコピーしたい） *ActiveWorkbookがAファイルになっているという状態で、下記のように 書きましたが、"r=..."のところでアプリケーション定義、またはオブジェクト定義のエラーになります。 Dim sh As Worksheet Dim r As Long Dim myTarget As Variant With ActiveWorkbook For Each sh In Sheets r = WorksheetFunction.Match( _ sh.Name, Array("A", "B", "C", "D", "E"), 0) If r > 0 Then sh.Cells.Copy Workbooks("B").Worksheet(sh.Name).Paste End If Next End With よろしくお願いいたします。
- ベストアンサー
- Visual Basic
- エクセルで関数を使いこのようなこと可能でしょうか？
こんにちは。皆様のお力を貸してください。 エクセルで商品一覧(シートA)を作りました。販売管理(シートB)と連動させ、納品日欄(F列)に日付が入ったら、列Bに入っている商品の仕入金額をG列に入れたいのです。商品一覧シートは、A列に商品コード、B列に商品名、C列に商品金額、D列に表示させたい仕入金額が入ってます。 関数で簡単に出来そうですが、組み方がいまいちわかりません。 ご存知の方いましたら、ご教授願います。
- ベストアンサー
- その他MS Office製品
- VLOOKUPとその他関数の組み合わせ方
こんにちわ。 VLOOKUPとifを組み合わせた関数？について教えて頂けますでしょうか。 例えば、２枚の以下のようなシートがあったとします。 (1) 商品名 金額 A 1000円 B 2000円 C 3000円 D 4000円 E 5000円 (2) 商品名 特価 A 100円 B 200円 D 400円 特価があるものに関しては、(2)のシートの金額を抽出。特価がないものに関しては、(2)のシートにA、B、C、Dなどの商品名がないですが、(1)のシートより商品名と金額を抽出してくるような式を教えて頂きたいです。 このような感じで↓ 商品名 金額 A 100円 B 200円 C 3000円 D 400円 E 5000円 どうぞよろしくお願いいたします。
- ベストアンサー
- その他MS Office製品
補足
ご回答ありがとうございます。この方法で上手くいきました！ ただ、最後の ROW(A$2:A$10)-1 の記述は なぜ必要なのかよくわかりませんでした。 後々のためにも、教えて頂ければうれしいです。