気持ち悪い表の処理 (INDEX, MATCH,

質問

気持ち悪い表の処理　(INDEX, MATCH, FIND・・・ ??)

添付の様な表があります。
EXCEL2010, Win7
セルの結合やら、セル内の改行やらがあってイラついています。

例えば、この中で欲しいものが「供給No.」にあたる"9-0123"です。
文字列"供給No."を含むセルをまず探して、
セル内の改行(A0?/CHAR(10)?)の後で　且つ2行目のスペースまでの文字列が欲しいです。
（実際には注文番号や処理番号も欲しいので、自動に処理をしたい訳です。）

=INDEX, MATCH, FIND, ISNUMBER関数 など　調べてみたのですが、未熟なためしっくりとくる処理が思い浮かびませんでした。
数式は結合しなくても結構です。セル2,3個使って貰う方が理解できますし。
お時間があるときで構わないのでお力をお貸しください。

しかし、こういうデータ作る奴、全角数字使う奴、スペースで位置調整する奴って、ほんとムカつきませんか！？
この程度が分からない自分にも腹が立ちますが、、、

回答No.4

MarcoRossiItaly · ベストアンサー

例えば質問文の添付図のシート名が「伝票1」であれば、別シートに次式で持ってこれます。

=substitute(asc(right(伝票1!E$4,len(伝票1!E$4)-find("
",伝票1!E$4)))," ",)
あるいは
=substitute(asc(right(伝票1!E$4,len(伝票1!E$4)-find(char(10),伝票1!E$4)))," ",)

※セル内での改行のショートカットキー　Alt+Enter
※上式の中で SUBSTITUTE 関数の第 2 引数の部分では、「"　"」（全角）ではなく「" "」（半角）を記述

ASC は、全角の数字が混ざっていても全部、半角にします。

下の添付図の B2 セルのように、抽出後のデータがハイフンなどを含まない数である場合は、「=0+substitute(…」といった感じで四則演算などを行うと、数値型の値を返します。数値は、書式を設定していなければ、右揃えで表示されます。文字列は C2 のように、左揃えです。

本当は、シート間の参照の方向が逆になるようにファイルを作るのが良い Excel の使い方だと思います。つまり、「伝票」と題された帳票から下の添付図のシートにデータを持ってくるのではなく、反対に、下の添付図の形でデータを蓄積しておいて、帳票のほうにデータを持ってくるという形にします。

例えば帳票の M1 に下の添付図のシート名が入力してある場合なら、次式を帳票の E4 あたりに入力しておけば、添付図の C2 のデータを参照できます。シート名（あるいは日付とか）だけ書き換えればいろんな行から持ってこれるので、帳票シートは 1 つだけ用意すれば済むことになりますね。

=indirect(m1&"!c2")

どちらからどちらを参照するにしても、せめて帳票の「供給No.」と「9-0123」は、1 つのセルに入力せず、セルを分けておくのが望ましいでしょうね。

＞しかし、こういうデータ作る奴、全角数字使う奴、スペースで位置調整する奴って、ほんとムカつきませんか！？

パソコン初心者さんなんだなとは思います。初心者さんのものに限らず、人が作った Excel ファイルを見ると、その人のスキルがだいたい分かります。例えば中級者と上級者が作ったものを比較すると、やはり違います。

お礼

皆様、気持ち悪い事に付き合せてしまい申し訳ございませんでした。
まだ4名様分の内容を検証していないのですが、どれも参考になり恐れ入ります。
御礼申し上げます。
お客のフォームなんでこちらの意向で変更できないんですよぉ…。

mt2008様のVBAの内容は私には分かりづらいですが、参考になりそうなので勉強させていただきます！
ユーザーファンクションなるものが作れるんですね！！目ウロコです。
TRIM+MID+FINDの式もシンプルでいいですね。対象セルが固定なら使えそうです。

MarcoRossiItaly様　ご丁寧にサンプルまで有難うございます！
一番求めていたものに近いかも知れません。
皆様のご返事の理解できる部分をつないで何とか処理しようかと思います。
勉強になりました。有難うございます。

回答No.3

KURUMITO

お示しの表でしたら供給No、注文番号、処理番号などは決められたセルに入力されていることでしょう。
その場合でしたら例えばL3セルに供給No、L4セルに注文番号、L5セルに処理番号とそれぞれ文字列を入力します。
その後にM3セルには次の式を入力することで供給Noが表示されます。

=ASC(SUBSTITUTE(E4,L3,""))

M4セルには次の式を入力します。

=ASC(SUBSTITUTE(G2,L4,""))

M5セルには次の式を入力します。

=ASC(SUBSTITUTE(A5,L5,""))

すべては半角文字で表示するようにしています。

お礼

皆様、気持ち悪い事に付き合せてしまい申し訳ございませんでした。
まだ4名様分の内容を検証していないのですが、どれも参考になり恐れ入ります。
御礼申し上げます。
お客のフォームなんでこちらの意向で変更できないんですよぉ…。

mt2008様のVBAの内容は私には分かりづらいですが、参考になりそうなので勉強させていただきます！
ユーザーファンクションなるものが作れるんですね！！目ウロコです。
TRIM+MID+FINDの式もシンプルでいいですね。対象セルが固定なら使えそうです。

回答No.2

mt2008

確かに気持ちが悪い。

今後の事も考えたら表の作り直しをした方が良いとは思いますが、該当データを取り出すユーザ関数を作ってみました。
下記のコードを標準モジュールに入れた後、気持ち悪い表と同じシートの適当な空きセルに　=fSearch("供給No.")　と、入れてみて下さい。
"供給先"や"処理番号"でも動作します。

Function fSearch(sTarget As String) As String
　　Dim sData, sGetData1, sGetData2, sArea

　　sArea = "A1:K5" '気持ち悪い表の範囲
　　fSearch = "該当なし"
　　'検索対象が存在するか確認
　　If WorksheetFunction.CountIf(Range(sArea), "*" & sTarget & "*") = 0 Then Exit Function

　　'対象セルのデータを取得
　　sData = Range(sArea).Find(What:=sTarget).Text '対象セルを特定
　　sData = StrConv(sData, vbNarrow) '全角を半角に
　　sGetData1 = Split(sData, Chr(10)) '改行で分割
　　If UBound(sGetData1) < 1 Then Exit Function '改行が無ければ終了
　　'2行目を半角スペースで分割した1つ目を取り出す
　　sGetData2 = Split(Trim(sGetData1(1)), " ")
　　fSearch = sGetData2(0)
End Function

お礼

皆様、気持ち悪い事に付き合せてしまい申し訳ございませんでした。
まだ4名様分の内容を検証していないのですが、どれも参考になり恐れ入ります。
御礼申し上げます。
お客のフォームなんでこちらの意向で変更できないんですよぉ…。

mt2008様のVBAの内容は私には分かりづらいですが、参考になりそうなので勉強させていただきます！
ユーザーファンクションなるものが作れるんですね！！目ウロコです。
TRIM+MID+FINDの式もシンプルでいいですね。対象セルが固定なら使えそうです。

回答No.1

keithin

「事実」が不明瞭ですが，Ｅ４から始まる結合セルに
供給No……時間↓
9-0123……13:03
が記入されているということですか。

計算例：一発でやった方が簡単ですが
=TRIM(MID(LEFT(E4,LEN(E4)-5),FIND(CHAR(10),E4&CHAR(10))+1,99))

計算を分けたいなら
注文番号：
=TRIM(MID(G2,FIND(CHAR(10),G2&CHAR(10))+1,99))
から手がかりにして組み立ててみて下さい。

お礼

皆様、気持ち悪い事に付き合せてしまい申し訳ございませんでした。
まだ4名様分の内容を検証していないのですが、どれも参考になり恐れ入ります。
御礼申し上げます。

mt2008様のVBAの内容は私には分かりづらいですが、参考になりそうなので勉強させていただきます！
ユーザーファンクションなるものが作れるんですね！！目ウロコです。
TRIM+MID+FINDの式もシンプルでいいですね。対象セルが固定なら使えそうです。