[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『VLOOKUP関数でヒットするはずの2番目以降も取得』（山Q）
過去ログにあるのかも知れませんが、よろしくお願い致します。 単純モデルで質問します。A列に得意先名、B列に問い合わせ内容が記入された シート(シート名：電話受付)があるとします。レコード数は約2,000程度です。 検索用シートを別途用意するとして、得意先名を入力すると｢電話受付｣シートから、 その得意先からの過去の問い合わせ内容をすべて取得したいのです。 VLOOKUP関数そのものをストレートに使うと、最初にヒットしたものは取得できますが、
Q1)2番目以降をヒットさせるにはどのように関数を組み立てればよいのでしょうか? Q2)VLOOKUP関数では無理なのでしょうか? ｢電話受付｣シートに参照用の列を加えても構いません。Win2000 EXCEL2000
｢電話受付｣シート ｢検索｣シート
A列 B列 A列 B列
1行目 あいうえお イロハニホ...... 1行目 あいうえお イロハニホ......
2行目 かきくけこ ハニホヘト...... 2行目 abcdefgh.......
3行目 さしすせそ ニホヘトチ...... 3行目 チリヌルヲ......
4行目 あいうえお abcdefgh...... ｢検索｣シートには、
5行目 あいうえお チリヌルヲ...... ヒット取得用に充分な
6行目 かきくけこ OPQRSTUV...... 行を用意します。
. . .
. . .
検索シートのB1に =IF(COUNTIF(電話受付!$A$1:$A$20,$A$1)<ROW(),"",INDEX(電話受付!$B$1:$B$20,SMALL(IF(電話受付!$A$1:$A$20=$A$1,ROW($A$1:$A$20)),ROW()))) こしてCtrl+Shift+Enterで配列数式にします。数式が｛｝で囲まれればOKです。 後はフィルダウンしてコピーします。 （ケン）
(ケン)様 早速のご教示に感謝致します。 自分なりに解釈できるように関数記述を行改えしてみます。
こうなりますか。
=IF(COUNTIF(電話受付!$A$1:$A$20,$A$1)<ROW(),"",
INDEX(
電話受付!$B$1:$B$20,SMALL(IF(電話受付!$A$1:$A$20=$A$1,ROW($A$1:$A$20)),
ROW())))
これに配列数式への移行を加える。
む、む、ムズカシイ～!
早速、試行してみます。
テキストファイルに保存しなっくっちゃ。
ありがとうございます。
(サンQ)
う～、うまく動かないです。(山Q) 得意先名の記述が各自ばらばらなため、 ワイルドカードを使うのですが、それが原因でしょうね。
株式会社あいうえお あいうえお株式会社 ㈱あいうえお あいうえお㈱ 株あいうえお あいうえお株 株）あいうえお KKあいうえお
と、人によってマチマチなため *あいうえお* で検索しようと思っています。 フォームで検索してもよいのですが、 一覧表のようにしたいのと、 実は、｢電話受付｣シートが月ごとにあるのです。 つまり「電話受付１月｣「電話受付2月」・・・・です。
結果として以下のような一覧表としたいです。
A列 B列 C列 D列 E列 ・・ 1 得意先名 電話受付1月 電話受付2月 2 あいうえお イロハニホ・・・ イロイロアレコレ・・ 3 abcdefgh・・・ a-demonai ko-demonai・・ 4 チリヌルヲ・・・ tamanihakaodase・・・
=IF(COUNTIF(電話受付!$A$1:$A$20,"*"&$A$1&"*")<ROW(),"",INDEX(電話受付!$B$1:$B$20,SMALL(IF(NOT(ISERROR(FIND($A$1,電話受付!$A$1:$A$20))),ROW($A$1:$A$20)),ROW()))) こんな感じでどうでしょうか？ （ケン）
(ケン)様 この数式を配列数式に移行させるわけですね。 職場では確認できませんので、帰宅して早速トライしてみます。 最初の質問が単純過ぎました。申し訳ございません。
（サンQベリーマッチ）
アナタハ、マクロガオキライヤ オマヘンカ？ コンナホウホウモ ゴザイマス
新しいブックで遊んでみてくだはい。 これはSheet1を検索シートに任命しとります。 ですから、その他のSheet名を電話受付１月、電話受付２月、電話受付・・・ とか適当に変更しとってくだはい。（一応全角で統一しときまひょか）
そのそれぞれのシート（受付シート）にこれも適当なデータを放り込んでくだはい。 Sheet1にはA列から得意先名、電話受付１月、電話受付２月、電話受付・・・ と右方向に並べマス。
Sheet1のタブを右クリックしてコードの表示を選択 下のコードをコピペして下はい。
後はSheet1のA列にあいうえお、なり、かきくけこなり入力すると、
ほら、どうでっか？ 満足でけるデータが揃いまっしゃろ？
ほんなら（弥太郎）
'-----------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Worksheet, ws1 As Worksheet
Dim n As Integer, f As Integer, j As Integer
Application.ScreenUpdating = False
Set ws1 = Worksheets("sheet1")
If Target.Count > 1 Then End
If Target.Column <> 1 Then End
Application.EnableEvents = False
For n = 2 To ws1.Cells(1, 256).End(xlToLeft).Column
For Each c In Worksheets
If c.Name = ws1.Cells(1, n) Then
With Sheets(c.Name)
For j = 1 To .Range("a65536").End(xlUp).Row
If .Cells(j, 1) Like "*[" & Target & "]*" Then
ws1.Cells(Target.Row + f, n) = .Cells(j, 2)
f = f + 1
End If
Next j
End With
End If
Next c
f = 0
Next n
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
横からおじゃまします。作業列を使う方法です。 # マクロも提供されたことですし、こんなやり方もあるということで(汗) 前提条件で、集計シートのB1,C1にはシート名『電話受付1月』『電話受付2月』が入力され ているとします。 『電話受付1月』シートのC列を作業列とします。なお、検索値はSheet2!$A$2です。 C2セルに=IF(ISERROR(FIND(Sheet2!$A$2,A2)),"",COUNTIF($A$2:A2,"*"&Sheet2! $A$2&"*")) として「あいうえお」を含む行に連番をつけます。 A B C 1 得意先名 内容 作業列 2 (株)あいうえお aaa 1 3 おあｋ bbb 4 あいうえお（株） ccc 2 5 （有）あいうえお ddd 3 6 いうお eee 7 あいうえお fff 4 8 かきく ggg 集計用のシートSheet2が↓のようにB1,C1にシート名が入力されているのなら B2セルに=IF(ISNA(MATCH(ROW(B1),INDIRECT(B$1&"!C2:C8"),0)),"",INDEX(INDIRECT (B$1&"!A2:B8"),MATCH(ROW(B1),INDIRECT(B$1&"!C2:C8"),0),2)) と入力して、右方向、下方向へ必要と思われるだけコピーします。 A B C 1 得意先名 電話受付1月 電話受付2月 2 あいうえお (Hatch)
ワォ～! なんて素晴らしい学校でしょう。 弥太郎様、しばらく待ってオクンナマシ。 先ずは、ケン様のご教示を吸収してみますサカイ。 ワクワクもんですょ。ありがとうございます。 Hatchさんもありがとうございます。順次トライしてみます。 皆さん、必ずご報告致します。
(ベリーサンQ マッチ)
面白そうなので、私も参加させてください。SEARCH関数でやってみました。 当然、Ctrl+Shift+Enterで確定です。 v(=∩_∩=)v （SoulMan） =IF(SUM(ISNUMBER(SEARCH($A$1,電話受付!$A$1:$A$20,1))*1)<ROW(A1),"",INDEX(電話受付!$A$1:$F$20,SMALL(IF(ISNUMBER(SEARCH($A$1,電話受付!$A$1:$A$20,1)),ROW($A$1:$A$20)),ROW(A1)),COLUMN(A1)))
大、大、大歓迎です、(SoulMan)さん。 SoulTrain, SoulTrain～てな感じで、 色んなアイデアが連なってきちゃって。
この、「検索のヒット」に該当する項目を、いくつものシートから 全部拾い上げて一覧表にまとめ上げるという課題をクリアー出来れば、 業務でも結構使えると思っていました。ず～と出来なかった。
*あいう* でも、*いうえ* でも、*うえお* ででも、 *あいうえお* に関する項目を漏れなくリストアップしてくれるし、 社名を変えれば、*かきくけこ* に関して全部抜き出してくれる!
うれしい限りです。ヤッタルデ～!
(山Q)
ちょっと、マクロも入れてアップしてみました。 http://ryusendo.no-ip.com/cgi-bin/upload/src/up0078.xls v(=∩_∩=)v （SoulMan）
おはようございます。 この手のお題は、どうしても「おまけ」を付けたくなるσ(^◇^;)
=IF(SUM(ISNUMBER(SEARCH($A$2,電話受付!$A$2:$A$20,1))*1)<ROW(A1),"", INDIRECT(ADDRESS(SMALL(IF(ISNUMBER(SEARCH($A$2,電話受付!$A$2:$A$20,1)),ROW($A$2:$A$20)),ROW(A1)),COLUMN(B1),4,1,"電話受付")))
=IF(SUM(ISNUMBER(SEARCH($A$2,電話受付!$A$2:$A$20,1))*1)<ROW(A1),"", OFFSET(電話受付!$A$2,SMALL(IF(ISNUMBER(SEARCH($A$2,電話受付!$A$2:$A$20,1)),ROW($A$1:$A$19)),ROW(A1))-1,COLUMN(A1)))
http://ryusendo.no-ip.com/cgi-bin/upload/src/up0079.xls
v(=∩_∩=)v （SoulMan）
(SoulMan)様 いつから始められたのですか? あっという間にレスが挿入されていましたね。 驚きです。アップしたのは、数式のシミュレーションテーブルですね。 解決へのアプローチは色々ありますよって事ですヵ。 参考にさせていただきます。 (参Qs)
>いつから始められたのですか? b(･ｏ･)dおw(･0･)wはぁで(･＜＞･)まよｃ(^･^)っちゅ っておきてからでぇ～～～～す。v(=∩_∩=)v （SoulMan）
(山Q)です。 長く時間を費やしてしまい、ご報告が遅れました。オワビ～です。 (ケン）さんと（弥太郎）さんのご教示分にかじりついて、 悪戦苦闘(これは私のエクセルスキルが低いことに起因します)の末、 以下の結果が出ました。
(ケン)さんのご教示では、 検索ヒットの1番目が表示されませんでした。 そして、2番目以降は完璧にヒットしているのですが、1行下の内容を呼び寄せています。 つまり、ヒットした行の1行下を表示するのです。 自分なりにアレンジを加えてみます。
(弥太郎)さんのご教示分。 並んで連なっている全てのシートの内容を、 見事に!全部!「検索シート」に呼び寄せてくれました。 つまり、分かれていたシート内容をすべて統合してくれちゃったみたいです。 コードをアレンジしたのが原因かもしれません。 "sheet1"を"検索シート"に替えてみただけなんですが。
ワタクシメは、VBAにはまったくの素人です。 コードを組み立てられる能力には、感服いたしております。
そんな訳で、(Hatch)さんと(SoulMan)さんの分は、まだ、 手付かずの状態です。ゴメンナサイ。またご報告致します。 (ソリー、サンQ)
>検索ヒットの1番目が表示されませんでした。 >そして、2番目以降は完璧にヒットしているのですが、1行下の内容を呼び寄せています。 =IF(COUNTIF(電話受付!$A$2:$A$20,"*"&$A$1&"*")<ROW(A1),"",INDEX(電話受付!$B$2:$B$20,SMALL(IF(NOT(ISERROR(FIND($A$1,電話受付!$A$2:$A$20))),ROW($A$1:$A$19)),ROW(A1)))) 少し訂正。 （ケン）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.