[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『複数の条件を指定して文字列を抽出する』（カルロ）
WindowsXP,Excel2002 こんにちわ。いつもお世話になりますB
< マスター > [A] [B] [C] [1] 氏名 タイトル 備考 [2] みちこ みかん すき [3] あかね みかん きらい [4] まりこ りんご すき [5] はつみ なし きらい
< 表 > [A] [B] [C] [D] [1] 氏名 みかん りんご なし [2] みちこ すき [3] あかね きらい [4] まりこ すき [5] はつみ きらい
このように"マスター"シートを参照して"表"シートのB2:D5までに"マスター"シートの 備考欄の文字が抽出できるようにしたいのです。 どのような関数を利用したらよいのでしょうか？ 教えて下さい。よろしくお願いします。
表シートのB2セルに =IF(SUMPRODUCT((マスター!$A$2:$A$5=$A2)*(マスター!$B$2:$B$5=B$1)),INDEX(マスター!$C$2:$C$5,SUMPRODUCT((マスター!$A$2:$A$5=$A2)*(マスター!$B$2:$B$5=B$1)*ROW($1:$4))),"") と入力して右および下へコピーで、どうでしょうか？ (独覚)
独覚さん回答有難うございました。 すごい！完璧です。有難うございました。 １つ教えて下さい。 "*ROW($1:$4)"この意味を教えていただけないでしょうか？ すみません。もう１つ教えて下さい。
[A] [B] [C] [D] [1] <氏名で抽出> [2] みちこ AAA BBB CCC [3] みかん なし りんご [4] [5] [6] [7] <マスター> [8] 由紀子 CCC AAA DDD [9] りんご なし みかん [10] みちこ AAA BBB CCC [11] みかん なし りんご [12] ようこ DDD FFF KKK [13] ばなな ぶどう なし
このよう表があるとき、 A2に名前を入力すればB2:D2、B3:D3に関数を入力していて<マスター>より 該当データを抽出する。 というようなことはできますか？教えて下さい（カルロ）
こちらについて。 "*ROW($1:$4)"この意味を教えていただけないでしょうか？
(マスター!$A$2:$A$5=$A2)*(マスター!$B$2:$B$5=B$1)
これだけですと結果は「{1;0;0;0}」となります。
(数式バー上で上記部分だけ選択してF9を押してみてください）
つまり該当する行が1で他の行は0の配列です。
SUMPRODUCT関数ではこれが合計されますのでどの行が当てはまる場合でも結果は「1」となり、何行目が当てはまるのか判りません。
そこで
(マスター!$A$2:$A$5=$A2)*(マスター!$B$2:$B$5=B$1)*ROW($1:$4)
とした場合、「{1;0;0;0}*{1;2;3;4}」となり、結果として該当する行数を返すことになります。
(数式バーで式の一部分を選択してからF9を押すとその部分の結果がどうなるか表示されるのでいろいろと試してみてください）
(独覚)
追加のご質問に関しては B2セルに =IF(COUNTIF($A$8:$A$15,$A$2),INDEX(B8:B15,MATCH($A$2,$A$8:$A$15,0)),"") としてみるのはどうでしょう。
(HANA)
"*ROW($1:$4)"この意味を教えていただけないでしょうか？ 独学さん回答有難うございました。 数式バーで式の一部分を選択してからF9を押してみました。おっしゃるとおりです。 いろいろな値に変化しました。配列関数は難しいですね。こんな難しい関数をわかる なんてすごい！ わたしも理解できるよう努力します。
'=IF(COUNTIF($A$8:$A$15,$A$2),INDEX(B8:B15,MATCH($A$2,$A$8:$A$15,0)),"") HANAさん回答有難うございます。バッチシです。すごい。 頭いいな！
すみません。もう１つ教えて頂けますか？ 実は、表シートにマスターと表が２つある場合について教えて下さい。
< 表 > [A] [B] [C] [D] [F] [G] [H] [I] [J] [1] 氏名 タイトル 備考 氏名 みかん なし りんご [2] みちこ みかん すき あかね きらい [3] あかね みかん きらい はつみ すき きらい きらい [4] まりこ りんご すき まりこ すき [5] はつみ なし きらい [6] はつみ みかん すき [7] はつみ りんご きらい 上記 表シートのA1:C7（マスター）を参照してG列、H1:J1項目を参照してH2:J7まで関数を入力 して備考(C列)データを抽出することのできる関数を教えて下さい。 はじめに独学さんより教えていただいた関数を入力したのですが、抽出された回答がずれてしまいうまくいきません。 何度もすみません。よろしくお願いします（カルロ）
例えばD1に「番号」 D2に =ROW(A1) として下にフィルドラッグ
この状態で、H2:J2セルに 該当のセルの情報が何番目かを 表示させる事は出来ますか？
SUMPRODUCT関数については、こちらをご参考に。 https://www.excel.studio-kazu.jp/lib/e3h/e3h.html 使用例はこちら。 [[20081219110246]]『行・列の二つの条件を満たす値を返す』（ねこまま）
(HANA)
HANAさん回答有り難うございます。 表に、 =IF(SUMPRODUCT((マスター!$A$2:$A$5=$A2)*(マスター!$B$2:$B$5=B$1)),INDEX(マスター! $C$2:$C$5,SUMPRODUCT((マスター!$A$2:$A$5=$A2)*(マスター!$B$2:$B$5=B$1)*ROW($1:$4))),"") この関数を入力したらできました。 たびたび申し訳ありません。 今まで、エクセルの学校の優秀な先生方にご教授いただましたおかげで仕事面でも大変役に立ち、 データ処理スピードもアップし正確に処理できるようになりました。 私自身も大変 勉強になりました。本当に有り難うございます。 大変 感謝しております。今後ともよろしくお願いします(カルロ)
こんにちわ。いつもお世話になります。 今 上記、SUMPRODUCT関数を利用して計算式を入力していたのですが、 1行目から表をつくってSUMPRODUCT関数を入力したら正常な回答がでますが、 行挿入(1行〜5行目まで)すると回答がおかしくなってきます。 SUMPRODUCT＋INDEX＋ROW関数を利用する場合は、行を空けないよう1行目から表を作った方が よいのでしょうか？ 現在 利用しているデータは、電算機より抽出したデータです。 もし、わかりましたら教えて下さい（カルロ）
< 表 > [A] [B] [C] [D] [F] [G] [H] [I] [J] [1] 氏名 タイトル 備考 氏名 みかん なし りんご [2] みちこ みかん すき あかね きらい [3] あかね みかん きらい はつみ すき きらい きらい
ROW関数がどの様な働きをしているのかが分かれば その問題は解決できると思います。
そして、それが分かるようにと 私は上の質問をしたのですけどね。
数式を分けて考えてみてください。 INDEX関数は INDEX(配列,行番号,列番号) の様に書きますよね。 配列の部分が 「マスター!$C$2:$C$5」 行番号の部分が 「SUMPRODUCT((マスター!$A$2:$A$5=$A2)*(マスター!$B$2:$B$5=B$1)*ROW($1:$4)))」 ですので、このSUMPRODUCT関数の戻り値が 配列の先頭から数えて期待する番号になっている必要が有ります。
SUMPRODUCT関数部分のみをセルに入力し 行を空けないよう1行目から表を作った場合の戻り値と数式 行を空けて作った場合の戻り値のずれとその時の数式を それぞれ見比べてみるのはどうでしょう。
(HANA)
HANAさん回答有難うございます。
>配列の先頭から数えて期待する番号になっている必要が有ります。 >行を空けないよう1行目から表を作った場合の戻り値と数式 >行を空けて作った場合の戻り値のずれとその時の数式を
関数の上でＦ９キーを押して確認しました。 ROW($1:$4)関数の戻り値を確認してわかりました。 ヒントを下さり有難うございます。 少しづつですが理解できそうな気がします。 今後とも よろしくお願いします（カルロ）
こんにちわ。いつもお世話になります。 上記 関数について追加で教えて下さい。 =IF(SUMPRODUCT((マスター!$A$2:$A$5=$A2)*(マスター!$B$2:$B$5=B$1)),INDEX(マスター! $C$2:$C$5,SUMPRODUCT((マスター!$A$2:$A$5=$A2)*(マスター!$B$2:$B$5=B$1)*ROW($1:$4))),"") この関数を入力したら文字を抽出します。もし文字ではなく数字を抽出するとします。 この場合、数字は抽出できますが、文字型になってしまいます。 その抽出した文字型数字は、SUM関数を使って合計できません。 例えば、H2〜J2まで文字型数字を抽出したとします。 それを数値型に変える方法を教えて下さい。 よろしくお願いします（カルロ）
＞この場合、数字は抽出できますが、文字型になってしまいます。 この書き方だと、 「数式で参照したから数値が文字列に成ってしまった」 ととれますが、ご提示の式からはそうなるとは思えません。
１．元の数値がそもそも文字（数字）である。 ２．実際に使用している式が、ご提示の物と違う。 ３．それでもやっぱり数字に変わる。
確認して見られてはどうでしょう。
もしかしたら SUMPRODUCT((マスター!$A$2:$A$5=$A2)*(マスター!$B$2:$B$5=B$1),$C$2:$C$5) SUMPRODUCT((マスター!$A$2:$A$5=$A2)*(マスター!$B$2:$B$5=B$1)*$C$2:$C$5) のどちらかでいけるか？
ちなみに、下側の式では正しく計算出来るが、上側で「0」と成る場合は １の状況であると思います。
(HANA)
HANAさん回答有難うございます。 >１．元の数値がそもそも文字（数字）である。 文字型の数字でした。 SUMPRODUCT((マスター!$A$2:$A$5=$A2)*(マスター!$B$2:$B$5=B$1)*$C$2:$C$5) こちらの計算式で文字型数字を数値型数字に変換できました。
下記 計算式では、どこを変更すればよいのでしょうか？ 文字型数字もあれば文字型もあります。 =IF(SUMPRODUCT((マスター!$A$2:$A$5=$A2)*(マスター!$B$2:$B$5=B$1)),INDEX(マスター! $C$2:$C$5,SUMPRODUCT((マスター!$A$2:$A$5=$A2)*(マスター!$B$2:$B$5=B$1)*ROW($1:$4))),"") 教えて下さい。よろしくお願いします（カルロ）
シートの状況（データの状況）を もっと正確に教えて下さい。
これまでのC列の様にではなく どの様に成っているのですか？ 正確なサンプルデータを載せて下さい。
C2:C5の範囲に数字と文字と両方入っているなら 「*$C$2:$C$5」の式ではエラーになると思います。
また ＞電算機より抽出したデータ をどの様にエクセルにしているのか分かりませんが 最初から値は値で取り込めるよう検討するのも 一つの方法と言えるかもしれません。
(HANA)
HANAさん回答有難うございます。 説明がわかりにくく失礼しました。 詳しくは、下記のようになります。 Ｂ列に記号が入っていてその記号の種類によって備考欄の言葉が違います。 Ｈ２に関数を入力します。 A1,A2ならば文字、A3〜A4ならば文字型数字が入っています。 Ｊ７にＪ２：Ｊ６までの合計を算出します。
< 表 > [A] [B] [C] [G] [H] [I] [J] [K] [1] 氏名 タイトル 備考 氏名 A1 A2 A3 A4 [2] みちこ A1 すき あかね きらい [3] あかね A2 きらい みちこ すき [4] りえ A3 '2 ゆきえ すき '5 '10 [5] ゆきえ A3 '5 りえ '2 [5] ゆきえ A1 すき [6] ゆきえ A4 '10 [7] 合計 7 10
＞最初から値は値で取り込めるよう検討するのも この方法で考えてみます。お手数をお掛けしました（カルロ）
列によって文字の列と数字の列が有るようですので J,K列の式は値に直す様な式に変更しておいても 良いかもしれません。 =IF(SUMPRODUCT((マスター!$A$2:$A$5=$A2)*(マスター!$B$2:$B$5=D$1)),INDEX(マスター! $C$2:$C$5,SUMPRODUCT((マスター!$A$2:$A$5=$A2)*(マスター!$B$2:$B$5=D$1)*ROW($1:$4)))*1,"") INDEX関数で返された数字に *1 をして数値に変更。 ~~
(HANA)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.