[[20170221095701]] 『文字の抽出について』(あば) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『文字の抽出について』(あば)

いつもお世話になっております。
文字の抽出で悩んでおります。

sheet1にデータがはいってきます。
A             B       C      D
2017/2/21 1:00  タマネギ   
2017/2/21 2:00  タマネギ  りんご
2017/2/21 3:00         りんご   ソース
2017/2/21 4:00                ソース
  

sheet2に文字を抽出したいのです。
    A           B          C
1 2017/2/21 1:00   2017/2/21 2:00   2017//2/21 3:00
2  タマネギ       りんご        ソース

A2にタマネギと入れたいのですが
sheet1のどの時間にはいってくるのかは解りません。
B列には他の品名は入ってきません。
データが入ってこないセルは空白です。
どなたかお教え下さい。

< 使用 Excel:Excel2013、使用 OS:Windows7 >


 2017/2/21 2:00で、タマネギではなくりんご、2017//2/21 3:00 でりんごではなくソースを表示させるのはどういうルールが
 あるのだろうか?

(ねむねむ) 2017/02/21(火) 10:25


こんにちは

一番右のデータを表示するのでしょうか?

Sheet2のセルB2に

=IFERROR(OFFSET(Sheet1!$A$1,MATCH(A1,Sheet1!$A:$A,0)-1,MATCH("*",INDIRECT("Sheet1!$B$"&MATCH(A1,Sheet1!$A:$A,0)&":$D$"&MATCH(A1,Sheet1!$A:$A,0)),-1)),"")

もっと整理された数式が有ると思います。(数式苦手)

(ウッシ) 2017/02/21(火) 10:45


 要は各行の右端の文字を表示させたいってこと?

 >sheet1のどの時間にはいってくるのかは解りません。

 例表では日時は両方のシートで同じ順番に並んでるけど、実際はそうではないってこと?

 >データが入ってこないセルは空白です。 

 数式も何も入っていない未入力セルという意味なら、

 A2 =IF(A1="","",LOOKUP("ー",INDEX(Sheet1!$B:$D,MATCH(A1,Sheet1!$A:$A,0),0)))

 右コピー

 こういうこと?
(笑) 2017/02/21(火) 10:53

Sub main()
    Dim c As Range, r As Range, x As Range
    Sheets("Sheet2").Cells.Clear
    Set x = Sheets("Sheet2").Range("A1")
    For Each c In Sheets("Sheet1").Rows(1).Cells
        Set r = c.EntireColumn.Cells.SpecialCells(xlCellTypeConstants).Resize(1)
        If r.Column > 1 Then
            x.Resize(2).Value = Application.WorksheetFunction.Transpose(Array(r.Offset(, 1 - r.Column).Value, r.Value))
            Set x = x.Offset(, 1)
        End If
        On Error GoTo err
    Next c
err:
End Sub
(mm) 2017/02/21(火) 11:15

色々と有り難う御座います。
わかりにくかった様ですね。

sheet1のB列にはタマネギ、C列にはりんごの様に列に入る名称は同じものです。
が200行以上ある時間帯のどこにはいってくるのかはわかりません。
名称はまだきまっていないのと、変更になる事もあるので
入ってきた名称を拾いたいのですが。

名称がメインで入ってきた時間の中で1番早い時間を
表示させています。
(あば) 2017/02/21(火) 12:31


 まだよくわからないけど、Sheet1は1行目からデータだとして、

 A1 =IFERROR(INDEX(Sheet1!$A$1:$A$500,MATCH("?*",Sheet1!B1:B500,0)),"")

 表示形式 日時

 A2 =IFERROR(INDEX(Sheet1!B1:B500,MATCH(A1,Sheet1!$A$1:$A$500,0)),"")
 または
 A2 =IFERROR(VLOOKUP("?*",Sheet1!B1:B500,1,FALSE),"")

 右コピー

 こういうこと?
(笑) 2017/02/21(火) 12:47

(笑)様  出来ました。
ありがとうございます!!!
(あば) 2017/02/21(火) 13:13

再度で申し訳ありません。

先日、(笑)様の式のVLOOKUPを使用した式で1件だけで試した所、出来たので安心
していたら、うまくいかなくなりました。

検索範囲B1:B500のなかでB1に文字が入っている場合は結果を返すのですが
B100に文字が入っている場合は空白になりました。
実際のデータは11行目から298行目までです。

どこに手を加えたらいいのか悩んでおります。
ご教示お願い致します。

(あば) 2017/02/24(金) 10:51


 > 実際のデータは11行目から298行目までです。

 だったら ↓ ですね。

 =IFERROR(VLOOKUP("?*",Sheet1!B11:B298,1,FALSE),"")

 VLOOKUPの検索値は半角の "?*" になってますか?
 VLOOKUPの検索の型は FALSE になってますか?

 現象としては、先頭のB11に入力した文字は表示されるけど、12行目以降に入力したら何も表示されない、ということ?
 それともどの行に入力しても何も表示されない、ということ?

 どっちにしても俄に信じがたい。
 再現するデータを提示してください。

 再現するデータとは、
 ・実際にExcelに入力した数式
 ・Sheet1のB列に入力した文字
 最低限この2点。

 数式はExcelからコピーしたものをここに貼り付けてください。
 式を見ながら手入力だと転記ミスがあるので必ずコピーしてください。

 ついでに確認
 1)Sheet1のB11以降に入力するのは「文字」ですよね? 数値じゃないですよね?
    数値だったら無視されるので空白になります。

 2)IFERROR関数を外して ↓ の式にしたらどうなりますか?(Sheet1のB列に何か文字を入力した状態で)
 =VLOOKUP("?*",Sheet1!B11:B298,1,FALSE)

 3)Sheet1のB11以降の文字は手入力?
    数式だったら、その式も提示してください。

 4)Sheet2、A1の日時をどうやって表示させてるのか知りませんが、
    ↓ の式で期待通りの日時が返りますか?
 =IFERROR(INDEX(Sheet1!$A$11:$A$298,MATCH("?*",Sheet1!B11:B298,0)),"")

 とりあえず以上です。
(笑) 2017/02/24(金) 13:11

(笑)様

例ではなく現状のできたexcelを書きます。

sheet1名は製品名

          C                  D                 E       F
11    2017/2/22 2:00	                    りんご
12    2017/2/22 2:05	                    りんご
13    2017/2/22 2:10	                    りんご
14    2017/2/22 2:15	                    りんご
15    2017/2/22 2:20	タマネギ	     りんご
16  2017/2/22 2:25                       
17   2017/2/22 2:30                   人参 
18  2017/2/22 2:30                   人参

sheet2                      
セルC10の式 =IFERROR(VLOOKUP("?*",製品名!D11:D298,1,FALSE),"")
セルC14の式 =IFERROR(VLOOKUP("?*",製品名!E11:E298,1,FALSE),"")

式をそのままコピペしました。
C10には何も出ず、C14には りんご と表示されています。

 1)Sheet1のB11以降に入力するのは「文字」ですよね? 数値じゃないですよね?
    数値だったら無視されるので空白になります。
→文字です。

2)IFERROR関数を外して ↓ の式にしたらどうなりますか?(Sheet1のB列に何か文字を入力した状態で)

 =VLOOKUP("?*",Sheet1!B11:B298,1,FALSE)
→結果は全く同じです。

 3)Sheet1のB11以降の文字は手入力?
    数式だったら、その式も提示してください。
→データとして製品名のsheetに書き込まれます。

 4)Sheet2、A1の日時をどうやって表示させてるのか知りませんが、
    ↓ の式で期待通りの日時が返りますか?
 =IFERROR(INDEX(Sheet1!$A$11:$A$298,MATCH("?*",Sheet1!B11:B298,0)),"")
→日時は別のsheetに年、月、日、時、分、秒が各列毎に書き込まれますので
 違う表示の仕方です。日時はわすれて下さい。

宜しくお願い致します。

                                                   
                  

(あば) 2017/02/24(金) 16:52


excelでテスト用を作成してみました。
製品名は同じように手入力して作成した所、出来上がりました。

という事は、書き込まれたデータに問題があるんでしょうね???

(あば) 2017/02/24(金) 17:18


 要するに、11行目から入力されている文字は正しく表示されるけど、
 12行目以降から入力されている場合は何も表示されない、ということでいいですか?

 IFERROR関数を外して、 =VLOOKUP("?*",製品名!D11:D298,1,FALSE)
 としてもエラーも何も表示されないんだったら、
 空白に見えてるところに実際はスペースが入ってるとか?

 Sheet2のどこか空いたセルに =LEN(C10)
 別のセルに         =CODE(C10)

 この2つの式を入力して、それぞれ何が返るか教えてください。

 とりあえず以上です。
(笑) 2017/02/24(金) 17:35

LEN(C10)を入力したら40が返ってきました。

CODE(C10)を入力したら32が返ってきました。

(あば) 2017/02/24(金) 18:42


 ということは製品名シートの何も表示されていないセルには、
 実は半角スペースが40個入っているということでしょうかね、多分。

 それはシステム上どうしようもないんだったら、
 置換でスペースを削除するか、
 Sheet2の数式を変更するか、ですね。

 ■スペースはそのままにして数式を変更するんだったら、

 C10 =IFERROR(VLOOKUP("?*",INDEX(TRIM(製品名!D11:D298),0),1,FALSE),"")

 ただし、これは製品名シートの文字列にスペースが使われていると、
 若干数式の結果と変わってくる可能性があります。

 どういう場合かというと、例えば「りんご□□青森」のように(□はスペース)
 文字と文字の間にスペースが2個以上連続している場合。
 そのVLOOKUPの式では「りんご□青森」とスペースが1個になってしまう。
 元々スペースが1個しか入っていない場合は問題ないと思いますけど。

 ■それではダメな場合は ↓ の式で試してみてください。

 C10 =IFERROR(INDEX(製品名!D11:D298,MATCH(1,INDEX((TRIM(製品名!D11:D298)<>"")*1,0),0)),"")

 とりあえず以上です。
(笑) 2017/02/24(金) 21:09

 > C10 =IFERROR(INDEX(製品名!D11:D298,MATCH(1,INDEX((TRIM(製品名!D11:D298)<>"")*1,0),0)),"")

 ↓ の方がいいです。
 C10 =IFERROR(INDEX(製品名!D11:D298,MATCH("?*",INDEX(TRIM(製品名!D11:D298),0),0)),"")

 PS)
 日時のことは完全に忘れての回答であることを申し添えておきます。
(笑) 2017/02/24(金) 23:28

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.