記号付きの文字列を抽出したい

質問

いつもお世話になります。
WIN7 EXCELL2010 です。

ご指導をいただきたいのは、
添付図で　A2　～　C8 で　#1　#2 #3(この３つだけです)　が付き
尚且つ一つだけ選び出したい。

例えば、「田中　角栄 #1」はA2～C3には３個ありますが
D2のように１個だけ選べればいいです。
もし可能ならばご指導戴ければ幸いです。
宜しくお願いします

回答No.5

tom04 · ベストアンサー

No.1です！
関数での方法をご希望だというコトですので・・・

一例です。
↓の画像のように右側がSheet2でSheet2を作業用のSheetとしています。
作業用の列を5列も設けてしまいました。

Sheet2のA2セルに
=IF(COUNTIF(Sheet1!A2,"*#*"),COLUMN()*1000+ROW(),"")
という数式を入れ、とりあえずC2セルまで列方向にオートフィルでコピー！

Sheet2のD2セルに
=IF(COUNT(A:C)<ROW(A1),"",INDEX(Sheet1!A:C,MOD(SMALL(A:C,ROW(A1)),1000),INT(SMALL(A:C,ROW(A1))/1000)))

Sheet2のE2セルに
=IF(D2="","",IF(COUNTIF(D$2:D2,D2)=1,ROW(),""))

という数式を入れ、Sheet2のA2～E2セルを範囲指定　→　E2セルのフィルハンドルで下へずぃ～～～！っとコピー！

最後にSheet1のD2セルに
=IF(COUNT(Sheet2!E:E)<ROW(A1),"",INDEX(Sheet2!D:D,SMALL(Sheet2!E:E,ROW(A1))))
という数式を入れオートフィルで下へコピーすると
画像のような感じになります。

パッ！っと思いつくと言えばこの程度ですが、
じっくり考えるともっと良い方法があるかもしれません。m(_ _)m

お礼

早速ご回答戴きながらお礼が遅くなり申し訳ありません。
幸せながら沢山の方のご回答を確認させていただいて時間がかかりました。
誠にありがとうございました。
今後の何かに是非役立ててゆきたいと考えています。

どれこれもベストアンサーで迷いましたが、
今回は　VBA と 関数　の両方にご指導いただいた方に独断と偏見で決めさせていただきました。
ご了承ください。
今後共々ご指導をよろしくお願いします。

最後に、
皆様方もいいお年をお迎えください。

回答No.7

kagakusuki

元データの途中に空欄があっても正しく抽出する事が出来る方法です。
　今仮に、元データがSheet1のA列～C列にあり、その1行目は「1」～「3」といった項目名が入力されているものとして、Sheet2のA列に抽出結果を表示するものとします。
　又、Sheet3のA列～D列を作業列として使用するものとします。

　まず、Sheet3のA2セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/COUNTIF(INDEX(Sheet1!$A:$C,1,ROWS($2:2)):INDEX(Sheet1!$A:$C,ROWS(A:A),ROWS($2:2)),"*?#*?")),MATCH("*?",INDEX(Sheet1!$A:$C,1,ROWS($2:2)):INDEX(Sheet1!$A:$C,ROWS(A:A),ROWS($2:2)),-1)-ROW(Sheet1!$A$1),"")

　次に、Sheet3のB2セルに次の関数を入力して下さい。

=IF(ISNUMBER($A2),SUM($A$1:$A1),"")

　次に、Sheet3のA2～B2の範囲をコピーして、Sheet2のA3～B4の範囲に貼り付けて下さい。
　次に、Sheet3のC1セルに次の関数を入力して下さい。

=IF(ROW()>SUM($A:$A),"",INDEX(Sheet1!$A:$C,ROW()-LOOKUP(ROW()-1,$B:$B)+ROW(Sheet1!$A$1),COLUMN(Sheet1!$A$1)+MATCH(ROW()-1,$B:$B)-ROW($B$2)))

　次に、Sheet3のD1セルに次の関数を入力して下さい。

=IF(AND(COUNTIF($C$1:$C1,$C1)=1,COUNTIF($C1,"*?#*?")),ROW(),"")

　次に、Sheet3のC1～D1の範囲をコピーして、同じ列の2行目以下に（元データの数を上回るのに十分な行数となるまで）貼り付けて下さい。

　次に、Sheet2のA2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>COUNT(Sheet3!$D:$D),"",INDEX(Sheet3!$C:$C,SMALL(Sheet3!$D:$D,ROWS($2:2))))

　次に、Sheet2のA2セルをコピーして、Sheet2のA3以下に貼り付けて下さい。

　以上です。

お礼

早速ご回答戴きながらお礼が遅くなり申し訳ありません。
幸せながら沢山の方のご回答を確認させていただいて時間がかかりました。
誠にありがとうございました。
今後の何かに是非役立ててゆきたいと考えています。

どれこれもベストアンサーで迷いましたが、
今回は　VBA と 関数　の両方にご指導いただいた方に独断と偏見で決めさせていただきました。
ご了承ください。
今後共々ご指導をよろしくお願いします。

最後に、
皆様方もいいお年をお迎えください。

回答No.6

MarcoRossiItaly

＞ただ相手がマクロを嫌っていますのでできれば関数では無理なんでしょうか。
　ご無理を言いますがなんとかご協力いただけないでしょうか。
　だめならVBAで進めます。

条件の後出しにならないよう、ご注意くださいね。とりあえず質問文の要求には No.1さんのご回答で答えられていそうなので、ベストアンサーは辞退します。

数式 1 本だけで鮮やかに最終的な一覧を得るのは困難っぽいですが、いくらかの作業が伴ってもよいなら、添付図のとおりできました。

(1)次のとおり入力します。

D2　'=a2
D3　'=b2
D4　'=c2

(2) D2:D4 のセル範囲を選択し、D19 までドラッグし、オートフィル。

※長い距離をドラッグするのがたいへんという場合は、D19 まで
　フィルされている状態であれば、例えば D17:D40 の範囲を
　選んだ状態で「ホーム＞フィル＞連続データの作成」にて
　「オートフィル」を指定して OK すると、D20:D40 がフィルされます。

(3) D 列をコピーして E 列に右クリックなどから「形式を選択して貼り付け」を、「値」を指定して実行。そうすると E 列に、「文字列化した数式が」貼り付いています。(4) E 列全体を選択した状態で「データ＞区切り位置＞完了」とクリック。これで添付図のとおり E 列の数式が人名を表示するようになります。(5) E 列をコピーして F 列に右クリックなどから「形式を選択して貼り付け」を、「値」を指定して実行。これで F 列に、「数式でない文字列が」入力されています。(6)次のとおり入力。

F1　★
H1　★
H2　*#*

(7)「データ＞フィルタ＞詳細設定」から表示される「フィルタ オプション」ダイアログにて、「抽出先」を「指定した範囲」に指定の上、「リスト範囲」に「$F$1:$F$19」を、「検索条件範囲」に「$H$1:$H$2」を、「抽出範囲」に「$H$4」を入力し、「重複するレコードは無視する」をチェックして OK 。

こうしてできた H5:H11 のデータの並べ替えとかは、データ タブの並べ替えのボタンとかで適当にどうぞ。

お礼

早速ご回答戴きながらお礼が遅くなり申し訳ありません。
幸せながら沢山の方のご回答を確認させていただいて時間がかかりました。
誠にありがとうございました。
今後の何かに是非役立ててゆきたいと考えています。

どれこれもベストアンサーで迷いましたが、
今回は　VBA と 関数　の両方にご指導いただいた方に独断と偏見で決めさせていただきました。
ご了承ください。
今後共々ご指導をよろしくお願いします。

最後に、
皆様方もいいお年をお迎えください。

回答No.4

KURUMITO

作業列を作って対応するのが分かり易く計算にも負担がかかりません。
お示しの表がA,B,C列でデータが2行目から下方に入力されているとします。
作業列のE2セルには次の式を入力して下方にドラッグコピーします。

=IF(ROW(A1)<=COUNTA(A$2:A$1000),INDEX(A$2:A$1000,ROW(A1)),IF(ROW(A1)<=COUNTA(A$2:A$1000)+COUNTA(B$2:B$1000),INDEX(B$2:B$1000,ROW(A1)-COUNTA(A$2:A$1000)),IF(ROW(A1)<=COUNTA(A$2:A$1000)+COUNTA(B$2:B$1000)+COUNTA(C$2:C$1000),INDEX(C$2:C$1000,ROW(A1)-COUNTA(A$2:A$1000)-COUNTA(B$2:B$1000)),"")))

F2セルには次の式を入力して下方にドラッグコピーします。

=IF(AND(COUNTIF(E2,"*#1")+COUNTIF(E2,"*#2")+COUNTIF(E2,"*#3")>0,COUNTIF(E$2:E2,E2)=1),MAX(F$1:F1)+1,"")

ここでデータがすべて半角の#1 #2 #3などと入力されているものとしています。全角でしたら全角に直して入力してください。

お求めのデータはD列に表示させるとしてD2セルには次の式を入力して下方にドラッグコピーします。

=IF(ROW(A1)>MAX(F:F),"",INDEX(E:E,MATCH(ROW(A1),F:F,0)))

E列やF列が目障りでしたらそれらの列を選択して右クリックし「非表示」を選択すればよいでしょう。

回答No.3

WindFaller

マクロを使わないのでしたら、別のシートで、A列,B列,C列のデータを全部まとめ、1列にしてしまい、

隣の列に、以下の数式を置き、データのある分にオートフィルコピーをしてしまいます。項目行を1行目に設けましたので、データは、2行目からになります。

B2：～
=AND(OR(COUNTIF(A2,"*#1"),COUNTIF(A2,"*#2")),COUNTIF($A$2:A2,A2)=1)

そうすると、TRUE, FALSEが出てきますから、それをコピーして、その範囲そのままに、値貼り付けで、TRUE, FALSEを定数化してしまいます。(数式のまま並べ替えは出来ないはずです。）

その後は、A1にセルポインターを置き、数式の列(2列目）に対して、並び替えで、降順にすれば、おっしゃっている「選び出し」は取り出せます。

そのままの表では、マクロ以外には、不可能ではないかと思われます。1列なら、配列数式でも可能だと思いますが、3列あるので、補助列が必要になるのではないかと思います。それなら、こちらの方が簡単です。

お礼

早速ご回答戴きながらお礼が遅くなり申し訳ありません。
幸せながら沢山の方のご回答を確認させていただいて時間がかかりました。
誠にありがとうございました。
今後の何かに是非役立ててゆきたいと考えています。

どれこれもベストアンサーで迷いましたが、
今回は　VBA と 関数　の両方にご指導いただいた方に独断と偏見で決めさせていただきました。
ご了承ください。
今後共々ご指導をよろしくお願いします。

最後に、
皆様方もいいお年をお迎えください。

回答No.2

noname#204879

「志田　菜穂 #3」と「田中　角栄 #3」はなぜ「選び出し」しないの？

お礼

早速ご回答戴きながらお礼が遅くなり申し訳ありません。
幸せながら沢山の方のご回答を確認させていただいて時間がかかりました。
誠にありがとうございました。
今後の何かに是非役立ててゆきたいと考えています。

どれこれもベストアンサーで迷いましたが、
今回は　VBA と 関数　の両方にご指導いただいた方に独断と偏見で決めさせていただきました。
ご了承ください。
今後共々ご指導をよろしくお願いします。

最後に、
皆様方もいいお年をお迎えください。

補足

早速ご検討いただきありがとうございます。
「田中　角栄 #3」は作図をする時の私のミスです。
必ずどれもこれも「#1 #2 #3」の全部があるとは限りません。
#1のみの時もあります。

回答No.1

tom04

こんにちは！
一例です。

手っ取り早く、VBAでやってみました。
Sheet1のデータをSheet2のA2以降に表示するようにしています。

Alt+F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, j As Long, cnt As Long
Dim wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1") '←「Sheet1」は実際のSheet名に！
Set wS2 = Worksheets("Sheet2")

wS2.Cells.ClearContents
cnt = 1
For j = 1 To 3 'A列～C列まで
For i = 2 To wS1.Cells(Rows.Count, j).End(xlUp).Row '2行目～最終行まで
If InStr(StrConv(wS1.Cells(i, j), vbNarrow), "#") > 0 _
And WorksheetFunction.CountIf(wS2.Columns(1), wS1.Cells(i, j)) = 0 Then
cnt = cnt + 1
wS2.Cells(cnt, 1) = wS1.Cells(i, j)
End If
Next i
Next j
End Sub 'この行まで

※　お示しの画像では最後に「田中　角栄#3」が表示されると思いますが・・・m(_ _)m

補足

早速ご回答いただきありがとうございます。
すぐにVBAを試させていただきました。
バッチリでした。

ただ相手がマクロを嫌っていますのでできれば関数では無理なんでしょうか。
ご無理を言いますがなんとかご協力いただけないでしょうか。
だめならVBAで進めます。