[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『サイズの抽出』（AKI）
Mac OS XでExcel2004を使用しています。 今、下記のようにＡ列にサイズを投入しています。
A サイズ 1 H2400 2 D350×H1200 3 W900×D450×H1800 4 W1200×D20×H600 5 W500×H450
これを横の各列にW、D、Hのサイズを抽出したいんです。
つまり、下記のようにしたいと思っています。
A サイズ W D H 1 H2400 2400 2 D350×H1200 350 1200 3 W900×D450×H1800 900 450 1800 4 W1200×D20×H600 1200 20 600 5 W500×H450 500 450
どのようにしたらうまくできるのでしょうか。
どうかご教授お願いします。
以下のような表だとして、 [A] [B] [C] [D] [1] サイズ W D H [2] H2400 2400 [3] D350×H1200 350 1200 [4] W900×D450×H1800 900 450 1800 [5] W1200×D20×H600 1200 20 600 [6] W500×H450 500 450 B2=IF(COUNT(FIND(B$1,$A2)),LOOKUP(9^9,--MID($A2,FIND(B$1,$A2)+1,COLUMN($1:$1))),"") 必要分、右と下にフィルコピー。 (ROUGE)
少なくとも提示した条件ではうまくいっています。 上手くいかなかったときの表レイアウトと数式を教えてください。 (ROUGE)
あ、できました。 全く同じ状態でやってみたんですけどさっきは出来なかったんです。 原因はよくわかりません。。。。（ごめんなさい。）
すぐに回答して頂いたのに本当に申し訳ありません。 ROUGEさん。ありがとうございました。
COLUMN($1:$1) ← こんなに配列作る必要ないと思うんですが。
=IF(COUNT(FIND(B$1,$A2)),-LOOKUP(1,-MID($A2,FIND(B$1,$A2)+1,ROW($1:$10))),"")
（R）
衝突！
折角ですので、数式の理解を深めてください。
B2=IF(COUNT(FIND(B$1,$A2)),LOOKUP(9^9,--MID($A2,FIND(B$1,$A2)+1,COLUMN($1:$1))),"")
~~~~~~~~~~~~~~~~~~~1 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~2 ~~3
まず、IF関数の部分は上記のように3つに分けられます。
１が論理式で、成立していれば２の数式を計算、成立していなければ３にある""（空欄）を返すことになります。
１の論理式について
FIND(B$1,$A2)というのは、A2にB1の文字があればその位置番号を返します。
ない場合にはエラーとなります。
COUNT(FIND(B$1,$A2))とすることにより、位置番号がある、即ちA2にB1の文字がある場合には「1」を、
エラー、即ちA2にB1の文字がない場合には「0」を返します。
（COUNT関数は数値を数える関数なので）
IF関数の論理式で0は成立していない（False）、0以外は成立（True）とみなされるので、
A2にB1の文字がある場合には２の数式を計算、ない場合には""（空欄）が返ります。
上記の例のB2の数式を例にしましたが、この場合はA2にB1の文字がないので""（空欄）が返っています。
次に２の部分に着目します。
B2の例では分かりにくいので、B4の例で説明します。
LOOKUP(9^9,--MID($A2,FIND(B$1,$A4)+1,COLUMN($1:$1)))
~~1 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~2
上記数式は、１と２に分けられます。
LOOKUP関数は配列を扱うことができる関数です。
２の部分で文字を切り出して数値化し配列におり、それを9^9という検索値で検索しています。
9^9というのは９の９乗という数値です。
LOOKUP関数では、検索値が見つからない場合、一番最後に出てきた数値を返す特長があります。
LOOKUP関数の２の部分に着目します。
--MID($A4,FIND(B$1,$A4)+1,COLUMN($1:$1))
~~1 ~~2 ~~~~~~~~~~~~~~3 ~~~~~~~~~~~~4
MID関数は文字を切り出す関数です。
２の部分に切り出す対象の文字列を指定します（今回の場合はサイズが書かれたセルの文字列）。
３の部分で切り出しを開始する文字の場所（文字数）を指定します。
４の部分で、３で指定した箇所から切り出す文字数を指定します。
３は、A4の文字列の中でB1の文字がある場所の次の場所を指定しています。
B4の例では、Wの場所、即ち１文字目の次である２文字目になります。
４は、COLUMN(1:1)とすることで配列にしています。
COLUMN(1:1)というのは、{1,2,3,4,・・・・,256} （Excel2003以前の場合）というものです。
以上より、--MID($A4,FIND(B$1,$A4)+1,COLUMN($1:$1)) は、
--{"9","90","900","900×","900×D","900×D4","900×D45","900×D450","900×D450×",
"900×D450×H","900×D450×H1","900×D450×H18","900×D450×H180","900×D450×H1800",
"900×D450×H1800"・・・,"900×D450×H1800"}
となっていることが分かると思います。
最後に１の部分で--と付けているのは、切り出した文字列を数値化する処理です。
数値化できるものであれば数値に、そうでないものはエラーとなりますので、
{9,90,900,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,・・・,#VALUE!}
となります。
これをLOOKUP関数に入れ込むと、、、一番最後にある数値を取り出しますので、「900」という結果が得られます。
分かりにくい部分があったらご質問ください。
(ROUGE)
> COLUMN(1:1)というのは、{1,2,3,4,・・・・,256}（Excel2003以前の場合）
> 分かりにくい部分があったらご質問ください。
質問者でなく恐縮ですが 何のために256個も配列を作るのか、その理由を教えてください。
（R）
別に理由なんてありませんが、そこまで拘る理由があったら教えてください。 (ROUGE)
せいぜい10個程度で事足りるのに無駄なことをしてると思っただけです。 特に理由はないということですね。了解です。
（R）
いろいろな回答ありがとうございます。 お二人ともすごいですね。 私ももっと精進せねば… (AKI)
こんにちは。 すでに解決済ですが、精進用として（^^）作業列を使った別案を挙げてみます。ご参考まで。 （W,D,Hの文字位置と、それ以降の×の位置を求めて、その間の数字を抜き出しています） （コタ）
※「_」セルは未入力または"" [R/C] [A] [B] [C] [D] [E] [F] [G] [1] サイズ W D H W D H [2] H2400 _ _ 2400 0 0 1 [3] D350×H1200 _ 350 1200 0 1 6 [4] W900×D450×H1800 900 450 1800 1 6 11 [5] W1200×D20×H600 1200 20 600 1 7 11 [6] W500×H450 500 _ 450 1 0 6 [7] W500 500 _ _ 1 0 0 [8] D350 _ 350 _ 0 1 0 [9] W900×D450 900 450 _ 1 6 0 [10] _ _ _ _ 0 0 0
B2 =IF(E2=0,"",MID($A2,E2+1,FIND("×",$A2&"×",E2)-E2-1)*1) →B2:D10フィルコピー
E2 =IF(ISERROR(FIND(E$1,$A2)),0,FIND(E$1,$A2)) →E2:G10フィルコピー
>せいぜい10個程度で事足りるのに無駄なことをしてる 無駄か無駄じゃないかは質問者さんが判断することで、 256必要か10個で十分かは回答者には予測はできても判断できない内容です。 表が応用されて後々10以上になる可能性もあります。 256の場合：COLUMN(1:1) 10の場合：COLUMN(1:10) 数に応じて修正することができることを質問者さんに伝わればいいのでは？ なぜ無駄と言い切れるか、私には理解不能です。 (とおりすがり)
> 256必要か10個で十分かは回答者には予測はできても判断できない内容です。
Excelの有効桁数が 15桁というのはご存知ですか？
それに検査値が 9^9 になってますから、COLUMN($1:$1) にしていても 最大8桁ないし9桁にしか対応できません。
> 256の場合：COLUMN(1:1) 2003以前の場合という意味ですか？
> 10の場合：COLUMN(1:10) これで 10 になりますか？
（R）
数式の内容を詳しく見ていないので、適当に例を書いたまでなので言いかえます。
>せいぜい10個程度で事足りるのに無駄なことをしてる 無駄か無駄じゃないかは質問者さんが判断することで、 256必要か10個で十分かは回答者には予測はできても判断できない内容です。 表が応用されて後々10以上になる可能性もあります。 違いや応用方法が質問者さんに伝えればいいのでは？ なぜ無駄と言い切れるか、私には理解不能です。
(とおりすがり)
↑ は上のレスを見た上でのコメントですか？
あくまで質問内容と提示された数式に対して感想を述べただけです。 そこを離れて一般論的なことに話をすりかえられても 答えようがないですね。
で > 10の場合：COLUMN(1:10) これで 10 になるんですか？
（R）
↑ は上のレスを見た上でのコメントですか？
>適当に例を書いたまで といいましたが… 技術的なことを言っているのではありません！
ストレートに書きます！ Rさんが、無駄か無駄ではないかを判断する必要なし！
AKIさんにご迷惑なのでこれにて…
(とおりすぎました)
> なぜ無駄と言い切れるか、私には理解不能です。
10桁で事足りそうなものに、256桁用意することが無駄と思わない人がいるとは思えません。 ROUGEさんも256桁の必要性は述べていません。
また、倍の20桁を用意した程度なら、誰も目くじらは立てないでしょう。 しかし、それ以上になれば、なんでぇ？ ってことになってもおかしくありません。
ROUGEさんは『特に理由なんてありません』と回答していますが、 私は何らかの理由はあったと思います。 私が書いた式だとしたら、こんな風に私の頭の中で考えたと思います。
9^9がカバーできれば十分だが、何桁まで切り出すかカッチリ書くのも面倒である。 10進法に慣れた人間としては、１、５、１０、２０、・・１００ なんかの区切りでスマートにやりたい。 バージョンはXL2007ではないので、１を使った関数なら COLUMN($1:$1) で256桁確保できる。 256桁は、無駄はあるが絶対個数としては大したことではない。 許容範囲の無駄なんだ。堅いこと云わないで、これで行っちゃおーッと。
(半平太) 2009/11/11 13:19
LOOKUP関数の検査値が 9^9 なので無駄と言えば無駄なのでしょうね 質問の例示からするとせいぜい5桁程度と思いますが・・・
>Excelの有効桁数が 15桁というのはご存知ですか？ 数値としてですので、文字として抽出は可能ではないですか?
LOOKUP(10^250,--MID($A$2,FIND(B$1,$A$2)+1,COLUMN($1:$1)),MID($A$2,FIND(B$1,$A$2)+1,COLUMN($1:$1))) この場合どのように考えますか
> 数値としてですので、文字として抽出は可能ではないですか? > どのように考えますか
↑ どなたのコメントで、誰に向けて質問しているのかわかりませんが くり返します、あくまで最初に提示された数式について述べただけです。
別の数式を持ち出してきて、文字列なら可能だと言われても そうですね、としか答えようがありません。
（R）
>提示された数式に対して >最初に提示された数式について 最初に書けばいいのに… 後から言い出すなんて卑怯だね！！
提示の式限定で考えると256も必要ないので10で十分だと思います。 その場合、下記のようになります。今後、表が応用されて10で足りなくなったら別ですが… =IF(COUNT(FIND(B$1,$A2)),-LOOKUP(1,-MID($A2,FIND(B$1,$A2)+1,ROW($1:$10))),"") とか、断りを入れればよかったんじゃないの？
それを勝手に表はこれ以上応用されないと決め付けて、勝手に無駄って判断するのがおかしい！ 最初に定義をうたっておけば、文句はなかった！！！
(とおりすぎたけど、またよってみた)
(とおりすがり) ＝(とおりすぎました)＝(とおりすぎたけど、またよってみた)
同じ人なんでしょうか？
（R）
念のために書いておきますが > LOOKUP関数では、検索値が見つからない場合、一番最後に出てきた数値を返す特長があります。
この説明はちょっと違うと思います。 検査値が最大値よりも大きければ、ですね。
（R）
なんか外出して帰ってみたらえらいことになっていて驚きました。 理由はないとは言いましたが、感覚的には半平太さんが掲載されたことだったと思います。 単に無精なだけですーー； LOOKUP関数の説明については、確かに欠落していました。 まあ、もともと説明するのも不得手なもので、たまに頑張って書いてもそんなことになるんですよね＾＾； でも、読んでいただいた方がいたということで、書いた甲斐がありました。 (ROUGE)
より簡潔な数式、より矛盾のない数式を設定するには、多くの関数に関する知識と経験が必要と思います。質問者抜きにして論争を しかけることは、それなりに質問者にエクセルの奥の深さを知ってもらうというメリットはあるでしょうが少々見苦しさを覚えます。 (XYZ)
次のものも解説お願いしたいのですが・・・
A1に 東京都杉並区阿佐ヶ谷100
と入力されているとします。
ここで、地番は半角、それ以外は全角で入力されているとします。
そこで、東京都杉並区阿佐ヶ谷
だけを、つまり地番以外を取り出したいとき、下記の式で取り出せると教わったのですが、私には
解析できません。
解説お願いできませんか？
=LEFT(A1,MATCH(0,INDEX(0/(MID(A1,ROW($1:$50),1)+1),),0)-1)
（アヒル＆ビーバー） 2015/08/25(火) 15:49
この式は半角全角関係なく数値以前の場所までを抜き出す式になっている。
A:MID(A1,ROW($1:$50),1)+1 A1セルの先頭から1文字目、2文字目…と50文字目まで一文字ずつ取り出した配列としてその要素に1をたす。 これでその文字が数字以外の場合は「#VALUE!」エラーに、数字の場合はその数字に1を足した数値になる。 (1を足すのは数字の数値化と、0の場合に1とするため）
B:0/A 0を上記の配列で割ることでエラー部分がエラー、数値部分が「0」の配列となる。 (0/0でもエラーとなるがAで1を足すことで0を1とすることで回避している）
C:MATCH(0,INDEX(B,),0) INDEX関数はMATCH関数でBの結果を配列として扱うために使用。 MATC関数の検索の型に「0」を指定しているので配列内で最初に「0」が出てくる位置を検索する。 つまりA1セルで最初に数字が出てくる位置。
D:LEDT(A1,C-1) Cの結果は初めて数字が出てくる位置のためそれから1を引くことで数字の手前までを取り出す。
このような説明でいいだろうか？
ところで、なぜ新規質問にせず直接関係のない質問へのレスにしているのだろうか？
（ねむねむ） 2015/08/25(火) 16:16
とりあえず、お礼まで。
新規質問にすればよかったですね。。
たまたま、配列の関係でこのスレ見つけて質問しちゃいました。
よくわからないとこあったら、また質問します。
ありがとうございました。
（アヒル＆ビーバー） 2015/08/25(火) 16:50
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.