[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『文字削除』（カナ）
以前、こちらで質問し、大変勉強になりました。目からウロコの連続でした。 [[20120501223747]]『文字の選択』 また 新しく質問させていただきます。
Ａ列に、名前１ というデータがあります。 ｜ ｜ これを、Ｂ列に このように表示したいのです。 ↓ ↓ A B
1 名前 1 名前 2 2 原 辰子 妹の 珠子 原 辰子 3 （株）松本 監査役 徳井 （株）松本 4 篠塚 利子 篠塚 利子 5 中畑（協） 取次店 品川 中畑（協） 6 淡口 憲子 恵子 望海 淡口 憲子 7 （有）河埜 （有）河埜 8 山倉 和子 山倉 和子 9 王貞（株） 代表貞雄 王貞（株） 10 山倉 和子 山倉 和子 11 小林 繁美 兄は 茂 小林 繁美 12 ホワイト・ジョーン ホワイト・ジョーン
↓ ａ）個人の名前で、左から２番目の空白（全角）から右に文字があるのもは全て消す。（上の表の、2･6･11行目） ｂ）会社名（中カッコがついているもの）で、 最初の空白（全角）から右に文字があるものは全て消す。（上の表の、3・5・9行目） ｃ）TRIM関数で前後の空白を消し、ａ）ｂ）以外のものは、そのまま表示する（そのままのものは、上の表の、4・7・8・10・12行目）
Ｂ列に、どのような関数を作ればいいのでしょうか。 作業列を４列も作って挑戦しました。個人名は2番目の、会社名は１番目の空白を『／』に置き換え、 『／』を含んだ それより右の文字は消す・・・というような関数を作りましたが、 恐ろしく長くなり、これでいいはずはないと、低迷状態です。とっても難しいとは思いますが、よろしくお願いします！ [エクセルのバージョン] Excel2007 [OSのバージョン] WindowsXP
例題でしか試してませんけど
B2 =TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",50)),100-50*COUNTIF(A2,"*（*")))
下コピー ついでにもうひとつ
B2 =LEFT(A2,FIND("♪",SUBSTITUTE(A2," ","♪",2-COUNTIF(A2,"*（*"))&"♪")-1)
（JPN）
ゑ！！ ザワザワ…… JPNさん、ありがとうございます。 シンプルな短い式なのに、完璧にできて、非常に驚いております。 私が作った関数は、数式バーに3行でした。 空白リピート50回…100ひく50を、中カッコのあるものに掛ける…… うーん 関数って難しい。
おっと、ふたつも考えてくださって もう一度ありがとうございます。 空白を♪に置き換えるという方法、例題の 原 辰子も、妹の珠子も、♪がついて るんるんです。 関数というのは、いくら本で勉強しても あまり実践では使えないです。組み合わせを楽しむ発想力が重要なのですね。 お陰様で、目につまっていたウロコが すっかり 出ました。 （カナ）
=TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",100),2),100))
=LEFT(A2,FIND(" ",A2&" ",FIND(" ",A2&" ")-COUNTIF(A2,"*（*")+1)-1)
※条件c忘れ。8:26訂正
(GobGob)
=LEFT(A1,FIND("★",SUBSTITUTE(SUBSTITUTE(A1,"）"," ")," ","★",2)&"★")-1)
GobGobさん、3つも考えてくださって、ありがとうございます！ 真ん中の式は、ばっちり チョッキリ全条件にあてはまり、完璧にできました。ありがとうございます。 2つ目のFIND関数は、ひとつ目のFIND関数での開始位置になるのですね。おおー 驚異的。 目をみはる、関数の組み合わせ方……発想力に、感嘆しております。 しかし、上の3つの式のなかで、1番目と3番目の式は、条件ｂ）が うまく表示できないような…… 私の 勘違いであれば 申し訳ございません！ （カナ）
A列に、データがあります。 ｜ ｜ B列に、下の※の条件で、種類分け番号を表示したいです。 ↓ ↓ A列 B列
[1] 状況 種類分け番号 [2] 原 監督 10 [3] 岡崎コーチ 10 [4] 杉内俊子は福岡から来て 移籍 422 [5] 宮本武子 選手養成 38 [6] コーチ 江藤 10 [7] 背番号を変更した小野淳子 555へ 422 [8] 土本恭子 教育人 38 [9] 大立恭平を支配下選手登録 抹消 422 [10] 成瀬功 投手統括 38 [11] コーチ 村田 10 [12] コーチャー阿波野 25 [13] 横浜から移籍してきた真田 哲子 422 [14] 川相指揮官 10 [15] 河本コーチ 10 [16] 投手管理人 川口 25 （まだ下にデータがあります） A列に、下の文字が含まれていたら、決められた数字を表示します。 ※監督 → 10 （上の表で、2行目） ※指揮官 → 10 （14行目） ※コーチ → 10 （3・6・11・15行目） ※管理人 → 25 （16行目） ※コーチャー → 25 （12行目） といった条件が、30以上あって、それ以外のものの条件は、下の※の2つになります。 ※上の条件以外で、 ・全角スペースがあり、 ・全角スペースを含む 左からの文字数が10文字以内のもの → 38 （上の表で、5・8・10行目） ※どの条件にもあてはまらないもの → 422 （4・7・9・13行目）
ファイルが.xlsとなっています。（97-2003ブック）現在、数式バーで2行ほどになる IF関数を使って表示していますが、 7つしか設定できないため、オートフィルターで『〇〇を含むもの』を出しては、番号を入力しています。 何かいい案があれば…… ご指導よろしくお願いします。 （カナ）
> A列に、下の文字が含まれていたら、決められた数字を表示します。
この参照リストをどこかに作ってください。
（例）F列とG列にリスト F G 1 監督 10 2 指揮官 10 3 コーチ 10 4 管理人 25 5 コーチャー 25
【重要その1】 A12セルの「コーチャー阿波野」は「コーチ」も「コーチャー」も両方含みます このようにキーワード同士で重なる部分がある場合、文字数の多い方 つまり「コーチャー」を「コーチ」よりも必ず下にしてください 「コーチャー」が3行目で「コーチ」が4行目とか5行目では駄目だということです
B2 =IF(A2="","",IF(COUNT(INDEX(0/FIND($F$1:$F$5,A2),0)),LOOKUP(1,0/FIND($F$1:$F$5,A2),$G$1:$G$5),IF(AND(COUNT(FIND(" ",A2)),FIND(" ",A2&" ")<=10),38,422)))
下コピー
【重要その2】 $F$1:$F$5 とか $G$1:$G$5 が参照リストの範囲です この範囲は必ずデータが入力されているきっちりとした範囲を指定してください 5行目までしかデータがないのに今後増えるかもしれないからと$F$1:$F$10とかにしないでください
【重要その3】 A列の1つのセルに複数のキーワードを含む場合（例：監督と管理人） 参照リストのより下の方にあるキーワードの数値が返ります 監督と管理人の両方含む場合 25 になります ところで > 全角スペースを含む 左からの文字数が10文字以内のもの この10文字以内というのは左から全角スペースまでの文字数ということですか？ セルの文字数ということじゃないですよね？
（JPN）
JPNさんルールに沿って条件リスト範囲を100行まで。
=IF(A2="","",IF(COUNT(INDEX(0/(FIND(F$1:F$100,A2)*(F$1:F$100<>"")),0)),LOOKUP(1,0/(FIND(F$1:F$100,A2)*(F$1:F$100<>"")),G$1:G$100),IF(COUNT(0/(FIND(" ",A2)<11)),38,422)))
※追記 8:51
＞GobGobさん、3つも考えてくださって、ありがとうございます！
当方、真ん中の1つしか回答してませんよ。 上下2つは ななしさん 回答っす。
(GobGob)
別案も考えてみました。
=IF(A2="","",IF(SUMPRODUCT((SUBSTITUTE(A2,F$1:F$100,"")<>A2)*1),LOOKUP(1,0/(SUBSTITUTE(A2,F$1:F$100,"")<>A2),G$1:G$100),IF(COUNT(0/(FIND(" ",A2)<11)),38,422)))
(GobGob)
JPNさん、GobGobさん、早速の解答を、ありがとうございます。仕事で徹夜してしまい、お礼が遅くなり、すみません。 関数の組み合わせの可能性は無限大なんですネ。 今年に入って、一番 驚いでいます。
重要な注意点を詳しく書いてくださって、勉強になります。また、【重要その1】での注意点は、試しに「コーチャー」を「コーチ」よりも上にしてみても、 きちんと表示されました。この場合が 偶然そうなっただけなのでしょうネ。使うときは、注意点を守ります！ （>>>> 全角スペースを含む 左からの文字数が10文字以内のもの >この10文字以内というのは左から全角スペースまでの文字数ということですか？ その通りです。説明が中途半端で、ご心配をおかけしてすみません。 ）
私には、この驚異的組み合わせの関数が、IFの参照先しか解らなくて、いろいろ調査してみました。 ・FINDの前の『/』は、割るという意味でしょうか？調べてみましたが、どっこにも載っていません。 どうしても そこを理解したいので、お手すきの時間に 教えて頂けたら嬉しいです！
GobGobさん、2つも考えてくださって、ありがとうございます。ん！？ すみませんでした……上の方で解答してくださってたコメントを、 全部GobGobさんだと勘違いしてしまいました。大変失礼しました！ JPNさんルールに沿った進化式、*(F$1:F$100<>"")とすると、範囲が広がるということ、大きな習得になりました。 2番目の式も、今後の為に、研究調査したいと思います。わからない時は、また 質問に来たいです！ よろしくお願いします。
あまり好ましくない、しつこい質問者で 申し訳ありません。 （カナ）
> 試しに「コーチャー」を「コーチ」よりも上にしてみても、きちんと表示されました。
G列の数値も入れ替えましたか？ 入れ替えてるなら、A12の「コーチャー阿波野」は25になるべきところが10になるはずなんですけど？ > FINDの前の『/』は、割るという意味でしょうか？
そうですよ。 その説明の前に前提として知っておかないといけないことがあります。
まずは ↓ をお読みください。 一番下のデータを取得する－LOOKUP関数 http://www.relief.jp/itnote/archives/003450.php
読んでくれましたか？ 要するにLOOKUPの検査値を範囲内の最大値よりも大きな数値にすると、その範囲の最後の数値を返すということですね。
では0/FINDが何をしているかを簡単に説明します。
例としてA2セルが「あああ監督いいい管理人」だとします（安直な例ですみません）
F G H I 1 監督 10 4 0 2 指揮官 10 #VALUE! #VALUE! 3 コーチ 10 #VALUE! #VALUE! 4 管理人 25 9 0 5 コーチャー 25 #VALUE! #VALUE!
H1 =FIND(F1,$A$2) I1 =0/FIND(F1,$A$2) 下コピー
監督と管理人両方含みますから、H1とH4にはその位置が表示され それ以外の文字列はA2セルにはないので#VALUE!エラーになります。
ここで先ほどのLOOKUPの特性を使って =LOOKUP(100,H1:H5) とするとH列の最後の数値 9 が返ります。 FINDが返す数値が100未満であることを前提にして検査値を100にしました。
次にI列のように 0/FIND にするとエラーはそのままですが、数値はすべて 0 になります。 つまりFINDがどんな結果を返しても0か#VALUE!のどちらかになるということ。 そうすると範囲内の数値は0以外ないので、LOOKUPの検査値は0よりも大きい値 1 にしておけばどんな場合でもOKになります。
=LOOKUP(1,I1:I5) で最後の数値 0 が返ります（位置的に言うとI4セルの0）
=LOOKUP(1,I1:I5,G1:G5) でI4と位置的に対応するG4の 25 が返ります。
これをI列を使わずにやると =LOOKUP(1,0/FIND(F1:F5,A2),G1:G5)
【まとめ】 0/FINDにしているのは数値を0にするため。 絶対にそうしないといけないというわけではありません。 LOOKUPの検査値が最大値よりも大きなものにさえなっていれば 0/ はなくてもいいです。
FINDが0を返すことはありませんから、0/0 になることはありませんが 0を返す数式（またはTRUEかFALSEを返す論理式）の場合は 0/ にすることで 0 の場合（論理式ならFALSEの場合）わざとエラーにする（数値でなくす）というまた別の重要な意味をもつことになります。 > IF(COUNT(INDEX(0/FIND($F$1:$F$5,A2),0)),･･･
これは0にする必要ないんで IF(COUNT(INDEX(FIND($F$1:$F$5,A2),0)),･･･ でよかったですね。 ついでに【重要その1】の補足 > キーワード同士で重なる部分がある場合 「一塁手」と「三塁手」というキーワードがリストにあるとします。 「塁手」の部分がかぶってますが、これは全く問題ありません。
注意しないといけないのはコーチ → コーチャーのように 1つのキーワードが丸々別のキーワードの一部になっている場合です。
（JPN）
＞試しに「コーチャー」を「コーチ」よりも上にしてみても、 ＞きちんと表示されました。この場合が 偶然そうなっただけなのでしょうネ。
偶然かと。 JPNさん回答にあるとおりLOOKUPの処理的にはおかしい結果となってるはず。
てなことで、F列 順不同案。
=IF(A2="","",
IF(SUMPRODUCT((SUBSTITUTE(A2,F$1:F$100,"")<>A2)*1),
INDEX(G$1:G$100,MOD(MIN(INDEX(LEN(SUBSTITUTE(A2,F$1:F$100,""))*10^7+ROW($1:$100),0)),10^7)),
IF(COUNT(0/(FIND(" ",A2)<11)),38,422)))
※A列セルにF列条件文字が複数あった場合、文字数が多い方を優先。
いろいろ考えてこの数式にしたけどG列が最大桁数の決まった正数のみなら 数式もうチョイカンタンになるよ。 (この回答はG列文字列でも可能）
(GobGob)
JPNさん、GobGobさん、他のサイトでも類をみない 丁寧なご説明、ありがとうございます！ こちらのエクセルの学校の先生方は、心暖かい、温情の強い方々ばかりで、万謝しております。 とても詳しくて熱心なお答え… ご厚意に深謝です グスン
紹介していただいたURLで、ちょっとばかし お勉強をして参ります。理解できたところで JPNさんのご指導を もう一度見直しに戻ります。 その時に、お礼のコメントを…… あっ！！ …… ガ――――――ン …… お礼のコメントの前に、お詫びのコメントを……
＞G列の数値も入れ替えましたか？ ＞入れ替えてるなら、A12の「コーチャー阿波野」は25になるべきところが10になるはずなんですけど？ わー ぎゃー おっしゃる通りです。 勘違いも甚だしくて すみません…… G列ではなくて、A列の事だと思ってました。 きちんと書いて下さっているのに、私の勝手な勘違いで たわけを尽くし、大変失礼いたしました。
どゎー！ GobGobさん、昨日から おとといから、びっくり仰天が続いております。目を見張る関数の式… きっとまた、しつこい質問者は、質問に伺うと思います。その時に お時間がございましたら、ご指導をよろしくおねがいします！
先生方への感謝の気持ちは…… =REPT("ありがとうございます","無限大")
（カナ）
JPNさん、GobGobさん、先日は、非常に詳しい関数のご指導、ありがとうございました！
JPNさんに教えてもらったURLで、すごい裏ワザが出来ることや、他のページの関数も勉強させていただきました。 関数のサンプルなどもあったりして、JPNさんの完璧な説明と合わせて読むと、最初に教えてもらった式の意味がとってもよくわかりました。 COUNT(INDEX(FIND($G$1:$G$5,A6),0)) ……… A列に参照リストの言葉がいくつあるのか数える式 LOOKUP(1,0/FIND($G$1:$G$5,A6),$H$1:$H$5) …… リスト表の数字を表示させる式 （0/で、「コーチャー」のような被りがある時は一番下の数字を表示するようにしている）
JPNさんの説明がなければ 最後まで理解不能でした。真から理解て喜んでおります。 IF関数しか使えてなかった私には、多くの関数と組み合わせが驚異の世界で、大変勉強になりました。 最大級に、ありがとうございました！
今は、GobGobさんの SUMPRODUCTがある式を分解して勉強しております。また 自力でわからない所は、質問させてくださいネ！ （カナ）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.