[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『INDIRECT関数＋MIDorFIND関数を使って、別シートから特定セル内の特定文字を抽出』（はな）
お世話になります。
掲題通りなのですが、同ブック・別シートの時系列表から
特定数値を抽出したいと考えています。
参照先の別シートの記載状況は下記のような内容です。
ここから数値のみ、100 と 110% と 50 と 110% を
隣に設けた別シートに抽出したいと考えています。
使用する関数にこだわりはありませんが、自分で調べた結果を掲題致しました。エラー続きでうまくいかず、こちらご相談させて頂きました。
A B
1 売上:100円（前年比110%）
営業利益:50円（前年比110%） 2 売上:100円（前年比110%） 営業利益:50円（前年比110%） 3 売上:100円（前年比110%） 営業利益:50円（前年比110%）
抽出後
A B C D E
1 100 110 50 110
2 100 110 50 110
3 100 110 50 110
ご回答よろしくお願い致します。
＜ 使用 Excel：Office365、使用 OS：Windows10 ＞
売上と営業利益が 2行のように見えますが セル内で改行とかしているのですか？
（渡辺ひかる） 2019/11/27(水) 16:02
A1 =CLEAN(TRIM(MID(SUBSTITUTE(SUBSTITUTE(Sheet1!$B2,"円（前年比",REPT(" ",20)),"%）",REPT(" ",20)),(COLUMN(A2)-1)*20+1,20)))*1
（GobGob） 2019/11/27(水) 16:17
注釈忘れ。
A列に 売上: 営業利益:
B列に
○○円（前年比○○%） ○○円（前年比○○%）
が前提。
（GobGob） 2019/11/27(水) 16:19
私はどちらもA列が1、2、3だと判断して。
Sheet2の
A1セル:=IF(Sheet1!A1="","",Sheet1!A1)
B1セル:=REPLACE(LEFT(Sheet1!B1,FIND("円",Sheet1!B1)-1),1,FIND(":",Sheet1!B1),"")*1
C1セル:=REPLACE(LEFT(Sheet1!B1,FIND("%",Sheet1!B1)-1),1,FIND("比",Sheet1!B1),"")*1
（ねむねむ） 2019/11/27(水) 16:23
D1セル:=REPLACE(LEFT(Sheet1!B1,FIND("円",Sheet1!B1,FIND("円",Sheet1!B1)+1)-1),1,FIND(":",Sheet1!B1,FIND(":",Sheet1!B1)+1),"")*1
E1セル:=REPLACE(LEFT(Sheet1!B1,FIND("%",Sheet1!B1,FIND("%",Sheet1!B1)+1)-1),1,FIND("比",Sheet1!B1,FIND("比",Sheet1!B1)+1),"")*1
（ねむねむ） 2019/11/27(水) 16:23
＞3行使用して、各々改行して記載しています。
了解しました。 すでにレスがついているようですので、私はここで失礼します。
（渡辺ひかる） 2019/11/27(水) 16:56
例示がずれてしまい、失礼致しました。
A列：1、2、3
B列：売上
C列：前年比
D列：営業利益
E列：前年比110
上記となります。。
お手数をおかけします。
本日AMに施行させて頂きます。
（はな） 2019/11/28(木) 09:32
上記は抽出後のセル配置です。
抽出前のデータ（セル配置）は
・A列が１，２，３、・・・付与した管理番号
・B列が売上と営業利益が改行して2段に記載されています。
※下記が一つのセルに記載（B1、B2、B3、・・・）
売上:100円（前年比110%）
営業利益:50円（前年比110%）
解りづらくて申し訳ありません。。
（はな） 2019/11/28(木) 09:54
A1 =IFERROR(LEFT(Sheet1!A1,FIND(" ",Sheet1!A1)-1),"")
B1 =IF($A1="","",CLEAN(TRIM(MID(SUBSTITUTE(SUBSTITUTE(Sheet1!$B1,"円（前年比",REPT(" ",20)),"%）",REPT(" ",20)),(COLUMN(A1)-1)*20+1,20)))*1)
下へコピー。 （GobGob） 2019/11/28(木) 09:57
GobGob様
有難うございます！
カーソルを持っていくと、確かに数式は入っているのですが
抽出先のセルが、すべて空白で表示されます・・（何も表示されないです）。。
大変恐縮ですが
初心者が間違えやすい箇所がありましたらご教示頂けますと助かります。
お手数をおかけしますが、どうぞよろしくお願いいたします。
ねむねむ様
有難うございます！
出来ました！
他の抽出元データがありアレンジがうまくいかずエラー続きです。。
下図のように表示することは可能でしょうか。。
A(列) B(列)
1 A： 100件（10%）
B 100件（10%）
2 A： 200件（20%）
B 200件（20%）
C 20件(2回実施)
抽出後
A B C D E F G
1 100 10 100 10
2 200 20 200 20 20 2
大変お手数をおかけして申し訳ありませんが
どうぞよろしくお願い致します。。
（はな） 2019/11/29(金) 11:17
これは最初のデータ形式と今回のデータ形式の両方に対応する必要があるのか？ それとも今回のデータ形式のみに対応すればいいのだろうか？ （ねむねむ） 2019/11/29(金) 11:24
今回のデータ形式のみに対応できれば大丈夫です。。
有難うございます！！
（はな） 2019/11/29(金) 14:10
>C 20件(2回実施) ここだけ()が半角で他の個所は全角だがこれはカッコは全角・半角統一されていないのだろうか？
（ねむねむ） 2019/11/29(金) 14:44
半角で統一するよう伝え、現データは修正致します。。
（入力者に確認したところ「半角」とのことです。実際は統一されていません。。）
有難うございます！！！
（はな） 2019/11/29(金) 15:24
「全角」で統一に変更になりました！！すみません・・。
他も「全角」に訂正いたします。。
よろしくお願い致します！！
（はな） 2019/11/29(金) 15:27
これ、元シートの方のC列、D列、E列を作業列として使うことはできるだろうか？ 一応式は立てたのだがかなり長くややこしい式になったものでもし 作業列を使えるようであればもっと簡単にできる。
（ねむねむ） 2019/11/29(金) 15:38
元シートは使えないんです。。
（元シートは他のデータもミックスしていて、別の作業でも使うので。。）
抽出先シート、あるいは新たに別のシートを使うことは可能です。。。
（はな） 2019/11/29(金) 15:48
抽出先シートのI列からK列を作業列として使う。 I1セルに =CLEAN(MID(SUBSTITUTE(Sheet1!$B1,CHAR(10),REPT(CHAR(9),100)),COLUMN(A1)*100-99,100)) と入力して右（K列まで）及び下へフィルコピー。 （ねむねむ） 2019/11/29(金) 16:01
そして
B1セル:=IFERROR(REPLACE(LEFT(I1,FIND("件",I1)-1),1,2,"")*1,"")
C1セル:=IFERROR(REPLACE(LEFT(I1,FIND("%",I1)-1),1,FIND("（",I1),"")*1,"")
D1セル:=IFERROR(REPLACE(LEFT(J1,FIND("件",J1)-1),1,2,"")*1,"")
E1セル:=IFERROR(REPLACE(LEFT(J1,FIND("%",J1)-1),1,FIND("（",J1),"")*1,"")
（ねむねむ） 2019/11/29(金) 16:04
F1セル:=IFERROR(REPLACE(LEFT(K1,FIND("件",K1)-1),1,2,"")*1,"")
G1セル:=IFERROR(REPLACE(LEFT(K1,FIND("回",K1)-1),1,FIND("（",K1),"")*1,"")
と入力ではどうだろうか？
（ねむねむ） 2019/11/29(金) 16:05
すまない。
I1セルの式を
=SUBSTITUTE(CLEAN(MID(SUBSTITUTE(Sheet1!$B1,CHAR(10),REPT(CHAR(9),100)),COLUMN(A1)*100-99,100)),"回","%")
に、G1セルの式を
=IFERROR(REPLACE(LEFT(K1,FIND("%",K1)-1),1,FIND("（",K1),"")*1,"")
に修正してくれ。
（ねむねむ） 2019/11/29(金) 16:11
有難うございます！
まるで魔法です！！
ただ・・
B・E・F・G列が、何も表示されません。。
実際の表への置き換えが問題なのだと思うのですが、
1種類目・2種類目の"件"と,
3種類目の"件""%"が表示されないので
初心者が理解できていない置き換え箇所があるのかもしれないと思いレスさせて頂きました。
度々恐縮ですが
ご教示頂けますでしょうか。。
よろしくお願いいたします。
（はな） 2019/12/01(日) 00:33
お世話になっています。
G列は表示できました！
()➡（）への置換もれでした。失礼しました！
BDF列の「件数」が未だ表示できずに試行中です。。
（はな） 2019/12/01(日) 20:08
: 100
件数が表示されます！
「: 」を消すにはどうすればいいでしょうか。。
試行を続けてみます！
（はな） 2019/12/01(日) 22:26
※数値は正常表示されますが、スペース含めて左側を消したい状態です。。
（はな） 2019/12/01(日) 22:40
もしかしてA、B、Cの個所は一文字ではなく何文字か連続しているのだろうか？ 最初の式の時は名称と件数の間に必ず:があったのでそれを目印にしていたが今回の式では :が無いパターンもあったので頭二文字を消すようにしていた。 （ねむねむ） 2019/12/02(月) 09:17
であればススペースを目印にしたいと思うが名称と件数の間のスペースは全角だろうか半角だろうか、 それとも統一されていないのだろうか？ （ねむねむ） 2019/12/02(月) 09:19
現状は、Aのみ 件数の前に大文字「：」がついています
（BとCはす、全角スペースが1個入っています。）。
スペースの大きさは
すべて「全角」1個で統一されています！
（はな） 2019/12/02(月) 09:50
まず、I1セルの式を =SUBSTITUTE(SUBSTITUTE(CLEAN(MID(SUBSTITUTE(Sheet1!$B1,CHAR(10),REPT(CHAR(9),100)),COLUMN(A1)*100-99,100)),"回","%"),"："," ") に変更してくれ。
（ねむねむ） 2019/12/02(月) 10:05
そのうえで
B1セル:=IFERROR(REPLACE(LEFT(I1,FIND("件",I1)-1),1,FIND(" ",I1),"")*1,"")
D1セル:=IFERROR(REPLACE(LEFT(J1,FIND("件",J1)-1),1,FIND(" ",J1),"")*1,"")
F1セル:=IFERROR(REPLACE(LEFT(K1,FIND("件",K1)-1),1,FIND(" ",K1),"")*1,"")
でどうだろうか？
（ねむねむ） 2019/12/02(月) 10:06
できました！！！！！
有難うございますm(__)m！！！
頂いた数式、よく勉強させて頂きます！
感謝をこめて。。
（はな） 2019/12/02(月) 10:19
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.