プロが教えるわが家の防犯対策術!

Excelで、以下のような場合、どのような関数を用いればよいでしょうか。

【1シート目】
《A列》品目
1 りんご
2 ぶどう
3 桃
4 梨

と並んでいて、

【2シート目】
《A列》品目
1 りんご
2 パイナップル
3 ぶどう
4 柿
5 いちご
6 梨
7 桃
・・・
《B列》値段
1 150
2 400
3 350
4 90
5 350
6 100
7 250
・・・

《C列》産地
1 青森
2 沖縄、台湾
3 山梨、岡山
4 和歌山、奈良
5 静岡、愛媛、岡山
6 山梨
7 岡山、山梨

---------------------------------------
と並んでいたとします。
(数字は、例えばB列の2ならB2セルだと思ってくださいm(__)m)

このとき、1シート目に、
A1セル りんご 
B1セル 150
C1セル 青森

A2セル ぶどう
B2セル 350
C2セル 山梨
C3セル 岡山

と並ぶようにしたいです。

つまり、Bのセル、値段の情報は1種類しかないので、
通常通りVlookupで引っ張ってくればいいと思うのですが、
Cのセル、産地は、ものによっては、2シート目でカンマ区切りで横に並んでおり、
それを1シート目に引っ張ってくるときは、縦に並べたい、という状況です。

わかりにくくすみません!
助けてくださると嬉しいです(>_<)
ちなみに、秀丸エディタを使用することができます。

質問者からの補足コメント

  • わかりにくいので、期待する結果の続きを書きます!

    A4セル 桃
    B4セル 250
    C4セル 岡山
    C5セル 山梨


    A6セル 梨
    B6セル 100
    C6セル 山梨

    A列やB列は空白のセルが生じることもあります。

      補足日時:2020/06/21 19:43

A 回答 (8件)

新フォーマット きましました?

    • good
    • 0

こんにちは



横からですが、横並びで良いのならそれなりにできるでしょう。
簡略化のために、いわゆるSPLIT関数のようなものを、ユーザ関数で定義しておけば楽になります。

使い方は、
 splitter(対象文字列, 区切り文字, 序列)
で、文字通り「対象文字列を区切り文字で分割した際の序列番目の文字列を返す」ものです。

これを利用すれば、
>通常通りVlookupで引っ張ってくればいいと思うのですが
ほぼこれで済んでしまいます。


添付図では、(添付の都合上で、別シートではなく)E列以降を抽出用セル範囲としています。
E列に品目が指定されているものとして、F2セルに
 =IF($E2<>"",VLOOKUP($E2,$A:$C,2,0),"")
を入力し、G2セルには
 =IF($E2<>"",splitter(VLOOKUP($E2,$A:$C,3,0),"、",COLUMN(A1)),"")
を入力して、右方に(必要な範囲まで)フィルコピーしています。
その上で、行全体(F2:J2)を下方にフィルコピーしています。


※ ユーザ定義関数を使う前の準備として、以下を標準モジュールにコピペしておきます。

Function splitter(ByRef str As String, ByVal del As String, ByVal n As Long)
Dim s
s = Split(str, del)
splitter = ""
If 0 < n And n - 1 <= UBound(s) Then splitter = s(n - 1)
End Function
「Excelで一致する値を引っ張ってくる方」の回答画像7
    • good
    • 0

お礼 拝見しましたよ。




そうですね、
縦並べは、
諦められた 方が、
寛容でしょうね。


もし、

新仕様告知と 共に、
開けたままに 暫し、
していて くださるなら。


私、

トライして みますよ?
新仕様を、
お教え くださいね。
    • good
    • 0

あの、



以下添付映像の、
左から 右に、
移るように、

行挿入が 伴うのですよね?


失敬しましました、

申し訳ない 知る限り、
別表に 結果表示を、
表させるか、
行挿入を 諦めるか、
ピボットテーブル系関数構文、
等を 併用するか、
VBA以外では。


断言します、

excel仕様上 誰だろうと、
不可能です。

(※注:少し 手間を、
    増やせば、
    離れ業が 使えるかも、
    知れませんがね。)


併せて、

VBAの 使用も、
不可能なのですよね?


ならば、

申し訳ないのですが、

其の 仕様の、
ままでの 自動化は、

貴方の 手元では、
不可能です。


元より、

縦並びに 並び直させる事を、
諦めるか、
別位置での 結果表示を、
許容来るか、
して 頂けるならば。


私なら、

もしや 作れて、
お望みが 叶うかも?

と 思います。


が、

いい難いのですが、
其のままでは 無理な、
相談です。


尚、

VBAでは しばしば、
情報セキュリティー担当よりの、
疑いを 受ける、
事が あります。


何故ならば、

excelの 破壊行為には、
VBAが 用いられる、
ケースも 多いからです。


ですので、

VBA導入前には、
きっちりと 根回しをし。


併せて、

文章による 許可を、
上司と 担当部署の、
両方より 取る事を、
お勧めします。


場合次第では、

首だけでは 済まされません、
故に。
「Excelで一致する値を引っ張ってくる方」の回答画像5
    • good
    • 0
この回答へのお礼

すごい!試してくださって、ありがとうございます!
VBAってセキュリティ上の問題があるんですね?(*_*)
であれば、うちの職場では使えないと思います。。。
産地を縦に並べるのは諦めます。
他の方法で、うまくデータ整形できないか考えてみます。
ありがとうございます!

お礼日時:2020/06/22 21:53

2シート目データが 重複時は、


最新のもの。


詰まり、

最下のものを 1シート目に、
当てれば いいですよね?


チャレンジしますので、

暫し、
此の 質問は、
開いたままに しておいて、
頂けますか?


といいますのも、

シート作成には 其れなりに、
時間が 掛かる、
ものにも 関わらず。


待ちきれず、

諦めて しまわれる、
方が いて、
困る場合が ありますので。


其れと、

表記の 揺らぎには、
非対応でも 構いませゆよね。
    • good
    • 0

確認です。



産地が2つ以上ある場合は縦方向に並べるのが正解ですか?

上記のような場合、その次の行ではA列が空白になるのが正解で、自動的に修正されることを期待しているのでしょうか?
例えば、当初の質問文によると桃はA3に入力されているのですが、補足ではA4になっています。


Yesの場合、関数ではむずかしいと思います。
    • good
    • 0
この回答へのお礼

>産地が2つ以上ある場合は縦方向に並べるのが正解ですか?
はい、そうなんです。
関数では難しいんですね・・・
他の方法を考えます。ありがとうございます!

お礼日時:2020/06/22 21:50

No.1です。



前回のコードではSheet2のC列の区切りが3個以上ある場合、不具合が生じました。

>これ、コマンドプロンプトに打ち込んで行うんでしょうか・・・?

VBAの操作方法に関しての質問ですかね?

それも含めて再投稿します。
前回のコードは無視してください。

Alt+F11キー → VBE画面が表示されますので、メニュー → 挿入 → 標準モジュール → カーソルが点滅しているところに
↓のコードをコピー&ペースト → Excel画面に戻り(VBE画面を閉じて)ご自身でマクロを実行します。

Alt+F8キー → マクロ → マクロ実行です。

Sub Sample2() '//この行から//
 Dim i As Long, k As Long, cnt As Long
 Dim c As Range, wS As Worksheet
 Dim myAry

  Set wS = Worksheets("Sheet2")
   With Worksheets("Sheet1")
    '//▼B列以降を一旦消去//
    .Range("B:D").ClearContents

    '//▼ココから操作//
    For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
     Set c = wS.Range("A:A").Find(what:=.Cells(i, "A"), LookIn:=xlValues, lookat:=xlWhole)
      If Not c Is Nothing Then '//←念のため//
       myAry = Split(c.Offset(, 2), ",") '//←「カンマ」で区切っている//
        For k = 0 To UBound(myAry)
         cnt = cnt + 1
         If k = 0 Then
          .Cells(cnt, "B") = c
          .Cells(cnt, "C") = c.Offset(, 1)
          .Cells(cnt, "D") = myAry(0)
         Else
          .Cells(cnt, "D") = myAry(k)
         End If
        Next k
      End If
    Next i
   End With
End Sub '//この行まで//

※ No.1の最後に記載しているように
ご自身でマクロを実行してください。m(_ _)m
    • good
    • 0
この回答へのお礼

ありがとうございます。
VBAというのを初めて聞きました(知識が足りず、すみません。)
職場で使えるかどうか?です。

お礼日時:2020/06/22 21:50

こんばんは!



手っ取り早くVBAでの一例です。
Sheet1のA列に「品名」?が羅列してあるとします。
A列はそのままでB~D列に表示するようにしてみました。
尚「カンマ区切り」とありますが、質問文を拝見すると「読点」での区切りになっていますので、
「読点」で区切っています。

標準モジュールにしてください。

Sub Sample1()
 Dim i As Long, k As Long, cnt As Long
 Dim c As Range, wS As Worksheet
 Dim myAry

  Set wS = Worksheets("Sheet2")
   With Worksheets("Sheet1")
    '//▼B列以降を一旦消去//
    .Range("B:D").ClearContents

    '//▼ココから操作//
    For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
     Set c = wS.Range("A:A").Find(what:=.Cells(i, "A"), LookIn:=xlValues, lookat:=xlWhole)
      If Not c Is Nothing Then '//←念のため//
       cnt = cnt + 1
       .Cells(cnt, "B") = c
       .Cells(cnt, "C") = c.Offset(, 1)
        myAry = Split(c.Offset(, 2), "、") '//←カンマではなく「読点」で区切っている★//
         For k = 0 To UBound(myAry)
          cnt = cnt + k
          Cells(cnt, "D") = myAry(k)
         Next k
      End If
    Next i
   End With
End Sub

※ 関数でないので、データ変更があるたびにマクロを実行する必要があります。m(_ _)m
    • good
    • 0
この回答へのお礼

ありがとうございます!
これ、コマンドプロンプトに打ち込んで行うんでしょうか・・・?
Excel上で行う方法はないでしょうかm(__)m
また、読点で投稿してしまっていました!
実際のデータは、カンマ(,)区切りです。

お礼日時:2020/06/21 21:24

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング