[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『VLOOKUP関数の結果を別の列に縦書きに詰めて表示したい。』（sarai）
A B C D 1 りんご みかん 2 売切 みかん 梨 3 バナナ スイカ 4 売切 梨 5 売切 スイカ
こんばんは。
マクロでよければ、
シートモジュール（シートタブ右クリック→コードの表示→出てきた白いところ）に
コピペして、
A列に"売切"(""は必要なし）と入力してみてください。
Private Sub Worksheet_Change(ByVal Target As Range)
Const myVal As String = "売切"
With Target
If .Count > 1 Then Exit Sub
If IsEmpty(.Value) Then Exit Sub
If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then
Application.EnableEvents = False
If .Value = myVal Then
If Range("D1") = "" Then
Range("D1").Value = .Offset(, 1).Value
Else
Range("D65536").End(xlUp).Offset(1).Value = .Offset(, 1).Value
End If
End If
Application.EnableEvents = True
End If
End With
End Sub
(SHIOJII）
衝突〜☆
こんばんは。
関数でよければ、
こんな感じでいかがでしょう？（笑
D1 =IF(COUNTIF($A$1:$A$5,"売切")<ROW(A1),"",
INDEX($B$1:$B$5,SMALL(IF($A$1:$A$5="売切",ROW($A$1:$A$5),""),ROW(A1))))
↑配列数式
上記式入力（または、コピペ）後、Ctrl + Shift + Enter で確定。
｛ 数式 ｝になればOK
以下コピー
※範囲は実際のものに変更してください。
（キリキ）（〃⌒o⌒)b
キリキさん またまた衝突ですね(笑
関数の時には、どんなふうに考えていくのでしょう？
全く関数は思いつきもしません？？？
もっとも関数を知らないというのが一番なんですが・・・
ちょっとこのスレを借りてご指導を お願いできませんか？
(SHIOJII)
私も配列で挑戦してみました。
=IF(ISERROR(INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10="売切",ROW($A$1:$A$10)),ROW(A1)),1)),"",INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10="売切",ROW($A$1:$A$10)),ROW(A1)),1))
として、Ctrl + Shift + Enter として確定して下さい。 如何でしょうか？ (SS)
あらっ！ よ〜く見ると(SS)のと、キリキさんのってそっくりでしたね。 (SS)
衝突〜☆
>よ〜く見ると(SS)のと、キリキさんのってそっくりでしたね。
SSさんも、INDEX関数好き？（笑
>ちょっとこのスレを借りてご指導を お願いできませんか？
ご指導だなんて。。。
σ(^o^;)は、関数もマクロも使っていくことで「慣れ」てくる気がします。
もっとも、マクロに関してはもっと「慣れ」なくてはと思っているのですが。。。
今回の場合「軸」となる関数を INDEX関数で考えました。
（とは言え、σ(^o^;)がINDEX関数が好きなだけですけどね）
他にも、INDIRECT・OFFSET関数などが使えるかもしれませんね。
INDEX関数は、ヘルプで見ると
INDEX(範囲,行番号,列番号,領域番号) となっています。
上記で言うと、最終的に返したいものは B列 になるので
範囲は「$B$1:$B$5」
行番号は配列数式で考えました。
SMALL(IF($A$1:$A$5="売切",ROW($A$1:$A$5),""),ROW(A1))
ここの部分ですね。
コレを、もっと分解してみると、、、
↓IF({0;"売切";0;"売切";"売切"}="売切",ROW($A$1:$A$5),"")
↓IF({FALSE;TRUE;FALSE;TRUE;TRUE},ROW($A$1:$A$5),"")
↓{"";2;"";4;5}
↓SMALL({"";2;"";4;5},ROW(A1))
↓SMALL({"";2;"";4;5},1)
↓2
このような流れで、$A$1:$A$5 の中に"売切" と同じものがどの行にあるか。
そしてその一番最小（上から順番）のものが何行目か。
を出しています。
列番号は、1列しかないので省略
領域番号、関係ないので省略
上記で出たものを INDEX関数に当てはめてみると、、、
↓INDEX($B$1:$B$5,{2})
↓INDEX({"りんご";"みかん";"バナナ";"梨";"スイカ"},{2}))
2番目の「みかん」が返る。
となって行きます。
はじめの、下記部分は
=IF(COUNTIF($A$1:$A$5,"売切")<ROW(A1),"",
COUNTIF関数で "売切" がいくつあるか。
そして、その値より ROW関数が大きくなったら
IF関数で、「""」空欄を返しています。
このような感じでしょうか？
σ(^o^;)は説明が苦手なので伝わるかどうか？？？
順番としては「軸」となる関数を決めた後、
その回答を出したい場合の、行番号・列番号等にするための関数を考え
（コレは、作業列などを用いて別セルで考えるとわかりやすいかも）
その関数に当てはめて行く感じです。
そして、最後にエラー処理をどのように考えるか。
こんな感じで考えております＾＾；
長々とすいませんでした。。。
（キリキ）（〃⌒o⌒)b
すみませんね。関数初心者に・・・ なるほどマクロと同じですね。習うより慣れろ！！ 私は、マクロ1年生ですが、課題を見ると、これが使えそうだと ようやく考えられるようになりました。 同じですね。いろんなスレッドとヘルプから 少しずつ自分のものにするしかないようですね。 キリキさん。ありがとうございました。 sarai さん。スレッドをお借りして申し訳ありませんでした。 (SHIOJII)
はいはい。
寝る前に最後の書き込み＾＾
D2 =IF(COUNTIF($A$1:$A$10,"売切")<ROW(A1),"",
INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10="売切",ROW($A$1:$A$10),""),ROW(A1))))
※範囲を広げましたけど、上記数式と同じです。
試しに、最初の数式を D2 にコピペしてみてください。
「みかん」が出るでしょ？
でも範囲がずれちゃうから、その後範囲を広げればオッケー。
（キリキ）（〃⌒o⌒)b
>１行挿入したところ、 ということですが・・・
使用する式により、変更点が違います。 それはROW関数を使用しているためです。
D1 =IF(COUNTIF($A$1:$A$5,"売切")<ROW(A1),"",
INDEX($B$1:$B$5,SMALL(IF($A$1:$A$5="売切",ROW($A$1:$A$5),""),ROW(A1))))
を使用しているとして１行挿入すると
D2=IF(COUNTIF($A$2:$A$6,"売切")<ROW(A2),"",
INDEX($B$2:$B$6,SMALL(IF($A$2:$A$6="売切",ROW($A$2:$A$6),""),ROW(A2))))
となります
D2=IF(COUNTIF($A$2:$A$6,"売切")<ROW(A1),"",
INDEX($B:$B,SMALL(IF($A$2:$A$6="売切",ROW($A$2:$A$6),""),ROW(A1))))
のように変更します
変更方法は、いろんな方法がありますので 一つの案として提示しました。
ROW(A1) は 1,2,3,というように１から始まる連続した数値を出すためのものです。
by しげちゃん
>「売切」の他に「廃棄」「処分」等が入る場合は、どのようにしたらよいでしょうか？ 同じ場所（列）に色々な言葉が入るって事でしょうか？
>扱い的には、「売切」と同じＤ列に順番に入れたいのですが。よろしくご指導お願いします。 皆さんの計算式の『売切』の部分を他の言葉に変えれば、変えた文字のものを返してくれますよ〜♪
>ところでどうして文字が大きくなってしまうのでしょうか。 ←ここに半角スペースを入れると、以降の文章を勝手に整形してくれます＾＾
スペースを入れないとこんな感じ＾＾
（キリキ）(〃⌒o⌒)b
キリキさん、ありがとうございました。 日曜日なのに仕事中です。返信が遅くなり申し訳ありません。 まず、スペースを入れてみました。 本題ですが、「売切」の他に他の言葉を入れた場合ですが、言葉が混在するんです。 別の言葉ではできたのですが、２種目の言葉を入力するとエラーになってしまいます。 例えば、Ａ２：Ａ２０に何か文字が入っていると○列に順番に表示せよ。というような ことができないでしょうか。説明が下手で申し訳ありませんが、よろしくお願いします。＜初心者sarai＞
マクロでよければ A列に何か文字を入れると、隣のB列の値をD列に書き出します。 １行目には、項目があるものとします。
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count > 1 Then Exit Sub
If .Row = 1 Then Exit Sub
If IsEmpty(.Value) Then Exit Sub
If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then
Application.EnableEvents = False
Range("D65536").End(xlUp).Offset(1).Value = .Offset(, 1).Value
End If
Application.EnableEvents = True
End With
End Sub
(SHIOJII)
shiojiiさんありがとうございました。 マクロは初めてでわからないのですが、 挑戦してみます。(難しそうですが・・・） 最初に質問があります。 値を「隣のＢ列」でなく例えばＤ40に変える場合はどこを変更すればよいのでしょうか？ 意味が通じにくいことお詫びします。初心者ということでお許しください。 ＜初心者sarai＞
Ａ列に何か入力すると、入力した隣のＢ列の値を、順にＤ列に書き出すのではないのですね。 もしそうならば、もう少し詳しい仕様を教えて下さい。
値を「隣のＢ列」でなく例えばＤ40に変える場合はどこを変更すればよいのでしょうか？ と、言われても・・・そのまま解釈すると、Ａ列に何か文字を入力すると、D40の値を
D列に書き出したい。と言うことになりますが・・・
たぶん、A列に何か入力すると、入力したB列の値をD40から順に入力したいのですが・・ということかと思いますが、違っていたら無駄レスになりますので、詳しい仕様を教えて下さい。
コメントを付けておきます。
'シートのセルの値が変わったら、以下のマクロを実行
Private Sub Worksheet_Change(ByVal Target As Range)
'値の変わったセルが
With Target
'もし複数のセルなら、マクロを中止しなさい
If .Count > 1 Then Exit Sub
'もし一行目なら項目なので、マクロを中止しなさい
If .Row = 1 Then Exit Sub
'もし何も入力がなかったら、マクロを中止しなさい
If IsEmpty(.Value) Then Exit Sub
'もし、A列のセルの値がかわったのであれば
If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then
'イベントを中止しなさい。
Application.EnableEvents = False
’D列の値の入っている最終行の次に値の変わったA列の右隣（つまりB列）の
’値を記入しなさい。
Range("D65536").End(xlUp).Offset(1).Value = .Offset(, 1).Value
End If
’イベント開始
Application.EnableEvents = True
End With
End Sub
（SHIOJII）
B1:B10に重複がない場合のものです。 何度か書いているのですが、まったく、人気がありません。 口座が延引していますので、試しにまた、書いてみます。
C1にIF(COUNTIF(A1:A10,"売切")<ROW(A1),"",VLOOKUP("売切",A1:B10,2,))、
C2に=IF(COUNTIF(A$1:A$10,"売切")<ROW(A2),"",
VLOOKUP("売切",INDEX(A$1:A$10,MATCH(C1,B$1:B$10,)+1,):B$10,2,))
として、C10までフィルドラッグ。というものです。 (LOOKUP)
以前この式を見たときは、 ):B$10 ~~~~~ この部分に感動した記憶があります。 ただ、自分で使いこなせるようになっていません・・・orz
今日、あらためて拝見してまた何かつかんだ気がします＾＾ 勉強させて頂きました。m(_ _)m
>Ａ列に何か入力すると、入力した隣のＢ列の値を、順にＤ列に書き出すのではないのですね。
SHIOJIIさんが仰ってますが、A列 に何かが入力で D列 に抜き出すのでしたら
LOOKUPさんの数式を、下記の様に変更すれば出来そうですね＾＾
C1 =IF(COUNTIF(A1:A20,"*")<ROW(A1),"",VLOOKUP("*",A1:B20,2,))
C2 =IF(COUNTIF(A$1:A$20,"*")<ROW(A2),"",VLOOKUP("*",INDEX(A$1:A$20,MATCH(C1,B$1:B$20,)+1,):B$20,2,))
以下コピー
（キリキ）（〃⌒o⌒)b
最初に、（SHIOJII）さん、説明がまとまらなくてすみませんでした。 「売切」だけでなく他の言葉が入っていても表示するという意味なのですが、 また、D40とは、D列のいずれの場所でも表示可能にしたいという意味だったのです。 順にＤ列に書き出すのもかわりません。説明が悪くてすみません。 A B C D 1 りんご 2 売切 みかん 3 廃棄 バナナ 4 梨 5 売切 スイカ
みかん
バナナ
スイカ
マクロの説明ありがとうございます。早速勉強してみます。
(LOOKUP)さんありがとうございます。
B1:B10には重複はありません。
『何度か書いているのですが、まったく、人気がありません。
口座が延引していますので、試しにまた、書いてみます。』という文章は、
すみませんちょっとよくわからないのですが、アドバイスありがとうございます。
早速試してみたいと思います。
（キリキ）さん、再度ありがとうございます。"*"私もこれかなと考えたのですが、
できなかったんです。試してみたいと思います。
・・・・皆さんにいろいろ教えていただき、ありがたく思っています。 実は、今日、田植えをしました。ちょっと疲れています。＜sarai＞
> 『何度か書いているのですが、まったく、人気がありません。 >口座が延引していますので、試しにまた、書いてみます。』という文章は、 >すみませんちょっとよくわからないのですが、
そうでしょう。これは、いわゆる独り言です。気にしないで下さい。 (LOOKUP)
saraiです。（キリキ）さんのアドバイスを使用して何度かいろいろ試してみたのですがうまくいきません。 B列を増やしたところ、下のようになったのですが原因がわかりません。
A B C D E
1 状態 品名
2 りんご 状態２
3 売切 みかん みかん
4 廃棄 バナナ バナナ
5 梨 もも
6 スイカ もも ←ももが複数表示されてしまう。
7 売切 もも もも
8 品切 ブドウ ブドウ
9 きゅうり ねぎ
10 なす ねぎ
11 売切 ねぎ
12 品切 じゃがいも ←ねぎの次のじゃがいも・だいこんが表示されない。
13 しいたけ
14 ほうれんそう
15 売切 だいこん
Ｅ３=IF(COUNTIF(A1:A20,"*")<ROW(A1),"",VLOOKUP("*",A2:B20,2,))
Ｅ４=IF(COUNTIF(A$2:A$20,"*")<ROW(A2),"",VLOOKUP("*",INDEX(A$2:A$20,MATCH(B2,B$1:B$20,)+1,):B$20,2,))
よろしくお願いします。＜初心者sarai＞
上記表ですと、こうなるんじゃないかな？
E3 =IF(COUNTIF(A1:A20,"*")<ROW(A1),"",VLOOKUP("*",A2:B20,2,))
E4 =IF(COUNTIF(A$2:A$20,"*")<ROW(A2),"",VLOOKUP("*",INDEX(A$2:A$20,MATCH(E3,B$1:B$20,),):B$20,2,))
（キリキ）（〃⌒o⌒)b
（キリキ）さん ありがとうございました。 できました。大変助かりました。 ありがとうございました。また、質問があると思いますが、 そのときもよろしくお願いします。＜初心者sarai＞
（キリキ）さん、先ほどできたと思っていたのですが、試した表が前の表だったため、 改めて、上記の表に入れてみましたができませんでした。Ｂ列が増えていることが、 原因でしょうか？
状態２ 0 #N/A 再度ご指導お願いします。＜初心者sarai＞
エラーになった数式を入力したセル番地がわからないと何とも言えませんが、、、
数式をもう一度、整理してみましょうか？
E3 =IF(COUNTIF(A1:A20,"*")<ROW(A1),"",VLOOKUP("*",A2:B20,2,))
IF関数で、真・偽を確認
COUNTIF(A1:A20,"*") → 範囲 A1:A20 の間に、ワイルドカードの "*"（任意の文字、1文字以上）がいくつあるか。
<ROW(A1) → 上記で出したものが、
ROW(A1) → 「1」より小さければ、真。ROW(A1) → 「1」より等しい、若しくは大きければ、偽。
真の場合
"" → 空欄を返しなさい
偽の場合
VLOOKUP("*",A2:B20,2,) → 範囲 A2:B20 の左側に、ワイルドカードの "*"（任意の文字、1文字以上）が入っている、2番目（B列）を返しなさい。
E4 =IF(COUNTIF(A$2:A$20,"*")<ROW(A2),"",VLOOKUP("*",INDEX(A$2:A$20,MATCH(E3,B$1:B$20,),):B$20,2,))
IF関数で、真・偽を確認
COUNTIF関数は、上記と同じ考え方
真の場合
"" → 空欄を返しなさい
偽の場合
VLOOKUP("*",INDEX(A$2:A$20,MATCH(E3,B$1:B$20,),):B$20,2,)
まず、MATCH(E3,B$1:B$20,)
最初に求めた一つ上の回答（ココでは E3）が B列の何番目にあるか。
それを、INDEX(A$2:A$20,MATCH(E3,B$1:B$20,),)
INDEX関数で、A列の何処になるのかを当てはめ、
:B$20 までの範囲を形成。
上記表で言うと、
$A$4:$B$20 という範囲になります。
その範囲の中で、ワイルドカードの "*"（任意の文字、1文字以上）が入っている、2番目（B列）を返しなさい。
という感じになっています。
もう一度、ご自身の表と照らし合わせて考えてみてください〜♪
（キリキ）(〃⌒o⌒)b
（キリキ）さん、今度はできました。 下記のとおり修正したところ、思っていたとおりにできました。 丁寧な説明を付けていただきありがとうございました。
E3=IF(COUNTIF(A2:A20,"*")<ROW(A1),"",VLOOKUP("*",A2:C20,3,))
E4=IF(COUNTIF(A$2:A$20,"*")<ROW(A2),"",VLOOKUP("*",INDEX(A$2:A$20,MATCH(E3,C$1:C$20,),):C$20,3,))
＜初心者sarai＞
良かったですね＾＾ 自分で解読・作成すると、関数も楽しいでしょ〜？ （キリキ）（〃⌒o⌒)b
関数も楽しいですね。といいたいところですが、 関数の中に関数が入っていると、わかりづらいですね。 正直、よくわからないんです。 他の質問ボードを見ていてもほとんどわからないし・・・・ 少しずつ勉強したいと思います。ありがとうございました。 ＜初心者sarai＞
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.