Excelの質問です。今、以下のようなデータがA列とB列に、1000行ぐらいずらっと並んでいます。
sp1|春の季語[あしび(馬酔木)]
sp2|春の季語[かげろふ(陽炎)]
sp3|春の季語[きじ(雉子)]
sp4|春の季語[ざんせつ(残雪)]
sp5|春の季語[ふぢのはな(藤の花)]
sp6|春の季語[やまぶき(山吹)]
・
・
・
su1|夏の季語[あぢさい(紫陽花)]
su2|夏の季語[かたつむり(蝸牛)]
su3|夏の季語[くものみね(雲の峰)]
su4|夏の季語[ころもがへ(衣替)]
su5|夏の季語[さみだれ(五月雨)]
su6|夏の季語[ばんりょく(万緑)]
su7|夏の季語[ひまはり(向日葵)]
su8|夏の季語[ほたる(蛍)]
su9|夏の季語[ほととぎす(時鳥)]
・
・
・
au1|秋の季語[あかとんぼ(赤蜻蛉)]
au2|秋の季語[あまのがわ(天の川)]
au3|秋の季語[いわしぐも(鰯雲)]
au4|秋の季語[かまきり(蟷螂)]
au5|秋の季語[きり(霧)]
au6|秋の季語[すすき(薄)]
au7|秋の季語[まんじゅしゃげ(曼珠沙華)]
au8|秋の季語[めいげつ(明月)]
・
・
・
wi1|冬の季語[おちば(落ち葉)]
wi2|冬の季語[かも(鴨)]
wi3|冬の季語[かれの(枯れ野)]
wi4|冬の季語[こがらし(木枯し)]
wi5|冬の季語[ゆきのこる(雪残る)]
・
・
・
シート名は<季語・枕詞>、ブック名は『俳句.xls』です。
A列には「sp1、sp2・・・su1、su2・・・au1、au2、・・・wi1、wi2・・・」といったIDのようなデータ、B列には「春の季語・・・」「夏の季語・・・」「秋の季語・・・」「冬の季語・・・」といったデータが入っています。
皆さまのお助け↓
http://okwave.jp/qa5096109.html
により、このデータの中から「春の季語」「夏の季語」「秋の季語」「冬の季語」といった、“種類のデータ”のみを抽出する方法は、理解することはできました。
その節はありがとうございました(>_<)
ただ、実は隣の列も、抽出しなければならない事態が生じまして(ToT)
皆様に教えていただいた方法により、別シート<季語・枕詞の種類>のA列に、「春の季語」「夏の季語」「秋の季語」「冬の季語」・・・といった“種類のデータ”は、並べることができました。
ここから、<季語・枕詞>シートのB列(季語のデータ)と、<季語・枕詞の種類>シートのA列(種類のデータ)を比較し、データが一致(完全一致ではなく部分一致でも構いません)すれば、<季語・枕詞の種類>シートのB列に、<季語・枕詞>シートのA列(ID)を、転記することはできないでしょうか?
例えば種類のデータとして<季語・枕詞の種類>シートのA列にある
「夏の季語」
に、部分一致する<季語・枕詞>シートのB列のデータは、
su1|夏の季語[あぢさい(紫陽花)]
su2|夏の季語[かたつむり(蝸牛)]
su3|夏の季語[くものみね(雲の峰)]
・
・
・
と、膨大にあります。
その膨大なデータ中の、一つだけでもいいのです。例えば、検索を開始して、最初に部分一致したデータが「su3|夏の季語[くものみね(雲の峰)]」であったならば、<季語・枕詞の種類>シートのA列とB列には、
夏の季語|su3
というように、転記したいのです。
“su”という情報が欲しいだけなので、三文字目の番号は、特に何でも構わないのです。
VLOOKUP関数を使おうと思ったのですが、部分一致検索で、VLOOKUPを使おうとしてもうまくいきませんでして・・・(ToT)
どうすれば、<季語・枕詞の種類>シートのB列に、<季語・枕詞>シートのA列(ID)を、転記できるでしょうか?
皆様のお力をお借しいただければ幸いです<m(__)m>
No.1
- 回答日時:
質問文が長すぎて、読む気もしない。
何とか簡略に。例の数を減らし、1例でよいから、どういう思考プロセスでデータがどうだから、こうしたいと言うふうに説明を短くしてほしい。
VLOOUP関数(のTRUE型に限って)は第1引数の語句が、第3引数の検索データを探す関数であるのは知ってますね。
また部分一致検索はと出来ません。
その場合第1引数の内容によって検索表を切り替えたい場合は有るでしょう。その場合は第1引数でIF関数で判断して表が2,3程度に分かれるなら、VLOOKUP関数式の中に組み込めるでしょう。
===
この質問は何なんだ?と読んでみると、A列の「AuXX」(Auがついている行)を別シートに、A,B列とも抜き出したいのか。
それならエクセルの関数の、抜き出し問題で、毎日のように質問がある、見飽きた問題だ。
そうなら質問の書き方がガラっと変わるべき。前半など要らない。
Googleで「imogasi方式」で照会すれば、何百と同類の質問と、imogasi方式でない他の方の解法も出てくる。
=====
●抜き出し問題なのかどうか補足してください。
ーー
それなら例は
a 千葉市
b 横浜市
a 甲府市
c 藤沢市
からaの分を抜き出し
a 千葉市
a 甲府市
を(別シートに)出したいと書くとおなじだよ。
自分の例を抽象化して、パターン化せよ。
すいません、もっと短くするように気をつけます<m(__)m>
やっぱVLOOKUは使えないみたいですね・・・。
確かに、ただの抜き出し問題かもしれませんね・・・(^_^;)
<季語・枕詞の種類>シートのA列は、
春の季語
夏の季語
秋の季語
・
・
・
と並んでいますが、隣のB列は、空白の状態です。
<季語・枕詞>シートから、データを抜き出したいのですが、<季語・枕詞>のB列には、「夏の季語[かたつむり(蝸牛)]」など、種類だけでなく、余計な部分([かたつむり(蝸牛)]等)がくっついているんですよね・・・。
でも、imogasiさんの助言で思いつきました、削除すればいいんですよね。“[*]”を“空白”に置換して、<季語・枕詞>シートのB列を、種類だけの文字列にして、IDのある<季語・枕詞>シートのA列も、後ろの数字の部分を削除すれば、ちょっと時間がかかりそうですが、VLOOKUP関数でもうまくいきそうです(^_^;)
No.2
- 回答日時:
リンク先はよくわからなかったが、たたき台で
D1セルに 検索文字(夏の季語)
E1セルに
=IF($D1="","",LEFT(INDEX(A:A,MATCH("*"&$D1&"*",$B:$B,0)),2))
C1:D1セルを別シートへ切りとって貼りつけ
すごいやり方ですね!
INDEX関数は詳しく知らなかったので、調べたところ、
INDEX(配列,行番号,列番号,領域番号)
http://excel.onushi.com/function/f-index.htm
ということみたいで(^_^;)
CoalTarさんが回答していただいた関数は、列番号と領域番号が省略されているみたいですね・・・A列のデータの中から、B列が"*"&$D1&"*"にマッチする行があれば、A列の行のデータを、Left関数で左から2文字切り取って、Eに表示させるのですね~ありがとうございます(>_<)
No.3
- 回答日時:
季語・枕詞の種類のシートのB1セルに次の式を入力して下方にオートフィルドラッグすればよいでしょう。
=IF(A1="","",IF(A1="春の季語","sp",IF(A1="夏の季語","su",IF(A1="秋の季語","au",IF(A1="冬の季語","wi","")))))
これまたすごい関数ですね、IF関数がすごい絡み合ってます・・・こんなの初めてみました(^_^;)
もし、A1が空白であれば、空白をそのまま返して、空白ではなく「春の季語」という文字列があれば、“sp”を返して、ない場合は・・・というように続いていくみたいですね。
ただ、私の質問の仕方が悪かったのですが、種類が100や200もありそうな状況ですと、関数を入力する手間が、ものすごく大変なような気がします・・・
No.4
- 回答日時:
#1です補足して貰っても、質問の内容(何がやりたいか)のことは十分書かず、自分の場合のことばかり(こんなの不要)書いている。
ーー
A列「夏の季語」で、C列「かたつむり」を含む!行を抜き出したいと
勝手に解釈して、
例データ
Sheet1
A列 B列(空白 C列 E列
第2行目からデータとする
夏の季語かたつむり(蝸牛)の姿1
秋の季語すすき
夏の季語とんぼのはね
冬の季語雁の群れ
秋の季語かたつむりの色
夏の季語歩むかたつむり2
E列は =IF(AND(A2="夏の季語",COUNTIF(C2,"*かたつむり*")>=1),MAX($E$1:E1)+1,"")
と入れて下方向に式複写。
条件に合った行に連番を振った。
Sheet2で
A2の式 =INDEX(Sheet1!$A$1:$D$100,MATCH(ROW()-1,Sheet1!$E$1:$E$100,0),1)
B2の式 =INDEX(Sheet1!$A$1:$D$100,MATCH(ROW()-1,Sheet1!$E$1:$E$100,0),3)
最後の引数はSheet1での、データの有る列の番号を意味する。
以上「imogasi方式」。
う~ん、うまくいかないですね(>_<)
imogasiさんのおっしゃる通りに、A列に種類名のデータ、B列は空白、C列に区切り文字“[”で区切った、種類名より後ろの文字列のデータ、D列は空白、E列に、
=IF(AND(A2="夏の季語",COUNTIF(C2,"*かたつむり*")>=1),MAX($E$1:E1)+1,"")
を入力したのですが、循環参照の警告が出てきてしまいまして・・・
No.5
- 回答日時:
回答No3です。
春、夏、秋、冬だけが表示されていたので、簡単な式で済むような気がしました。たくさんの種類があるのでしたら季語・枕詞の種類のシートでB1セルには次の式を入力し、下方にオートフィルドラッグします。
=IF(OR(A1="",COUNTIF(季語・枕詞!B:B,A1&"*")=0),"",LEFT(INDIRECT("季語・枕詞!A"&MATCH(A1&"*",季語・枕詞!B:B,0)),2))
この式ではIDの頭の2文字が表示されるようになっています。
3文字のケースがあるのでしたらそれを考慮した式にすることが必要です。
うまくいきました(^_^;)
ありがとうございます<m(__)m>
A1セルが空白、もしくは<季語・枕詞>シートに“A1&*”の文字列が一つもない(=0)場合、空白を返して、偽の場合は、LEFT関数で左から2文字切り取るみたいですね・・・でも、INDIRECT関数の使い方が、よくわかりませんでして・・・「"季語・枕詞!A"」とは、どういう意味なのでしょうか?
よくわからないので、
LEFT(INDIRECT(MATCH(A1&"*",季語・枕詞!B:B,0)),2))
と、削除してみたら、エラーになってしまいました(ToT)
普通に<季語・枕詞>シートのA列全部を選択しようとしたら、「季語・枕詞!A:A」となり、これまたエラーになってしまいました・・・「"季語・枕詞!A"」における、一つだけポツンとある“A”は、一体何なのでしょうか?
すいません、お暇な時で構いませんので、再度ご回答いただれば幸いです(>_<)
No.7ベストアンサー
- 回答日時:
回答5です。
=IF(OR(A1="",COUNTIF(季語・枕詞!B:B,A1&"*")=0),"",LEFT(INDIRECT("季語・枕詞!A"&MATCH(A1&"*",季語・枕詞!B:B,0)),2))
LEFT関数はLEFT(文字列、文字数)のように使われますね。
上式ではINDIRECT関数を使ってその文字列を季語・枕詞のシートから取り出しています。そこでAとあるのはA列を意味しており、MATCH関数でA1セルに書かれた文字を先頭に含む文字を季語・枕詞のシートのB列で検索し、その行を取り出しています。
表示したINDIRECT関数の全体では季語・枕詞シートのA列でのその行を求め、そこにある文字列を取り出していることになります。
INDIRECT関数を使うことでA列の例えば3行、4行を取り出す場合には単に=A3や=A4とすればよいのですが3や4が定まっていない場合、例えばB1セルに入力された数値の行でA列でのデータを取り出したい場合には普通の書き方ではできません。その場合には、例えば=INDIRECT("A"&B1)のような式を使うことで解決することができます。この場合に""を省略することはできません。
なんとなくわかりました!
"季語・枕詞!A"と、MATCH(A1&"*",季語・枕詞!B:B,0)は、“&”でペアになっており、「MATCH(A1&"*",季語・枕詞!B:B,0)を満たすA列のデータを返す。」という意味ですね(^_^;)
ありがとうございます<m(__)m>
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
ExcelでASCを使って全角を半角...
-
今まで文字化けなく開けていたc...
-
Microsoft 365Excelの見開きペ...
-
Excel関数について教えてくださ...
-
マクロの処理が遅くなった
-
スプレッドシートで指定された...
-
エクセルの質問です。 F列からL...
-
Excel関数について教えてくださ...
-
エクセルのセル内に分数などの...
-
作成した数式を値として表示し...
-
ワークシートに出現したこの画...
-
条件付き書式設定で罫線を引き...
-
EXCELの散布図で日付が1900年に...
-
タイムスタンプとテキストから...
-
シートの情報を別のシートへま...
-
Excelでの文字色
-
【マクロ】VLOOKUPにて参照元に...
-
エクセルの文字が途中から消える
-
エクセルの数式バーのフォント...
-
エクセルでファイルの最終更新...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
Excelで数値を時間数に変換する...
-
エクセルの数式バーのフォント...
-
エクセルで数字の組み合わせを...
-
エクセルを使用して、円周率を...
-
Excelで特定の文字列が含まれて...
-
Excel 対象のセルに入力が無い...
-
任意の値が存在する行に名前を...
-
エクセルでファイルの最終更新...
-
index関数の説明をお願いします。
-
条件付き書式でやりたいのですが
-
重複しない値を取り出したい
-
【ExcelVBA】UTF-8(BOM無)でC...
-
【マクロ】マクロが割当てされ...
-
エクセル IF計算式?でしょうか?
-
エクセルで曜日を入れたい
-
表中の指定した条件の文字列を...
-
【Excel】版が同じ事を示す番号...
-
EXCELの散布図で日付が1900年に...
-
Excelについて。Excelに縦1列に...
おすすめ情報