[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『ひとつの条件から、複数のデータを検索して表示』（どんぐり）
とにかく楽に検索したくて・・日々精進！先日からいろいろお知恵を頂き、表を作り変え,ずいぶん楽に仕事できるようになりました。ありがたやー
ところで、複数条件で検索する値は解決。ではその逆は？
商品コード 販売商品名 実際の商品
１２３ 野菜 だいこん
１２３ 野菜 白菜
１２３ 野菜 にんじん
４５６ 果物 りんご
１２３ 野菜 じゃがいも
４５６ 果物 バナナ
７８９ 菓子 チョコレート
４５６ 果物 栗
７８９ 菓子 クッキー
･･･といった表があり、別シートでＡ列に商品コードを打ち込むと、Ｂ列に販売商品名とＣ列に該当の実際の商品が全て値として出されるとうれしいです。
別シート
Ａ列 Ｂ列 Ｃ列
１２３ 野菜 だいこん
白菜
にんじん
じゃがいも
７８９ 菓子 チョコレート
クッキー
４５６ 果物 りんご
バナナ
クッキー
実際には、商品コードはコード順に並んでいます。商品名は３００くらい、実際の商品は荷姿が違うものもあるので、２０００位あるかと思われます。
実際の商品にＩＤをつけたり、accessにも手を出しましたが、使い勝手が悪いので、何とかよろしくお願いします。
オートフィルターで出来ませんか？ (INA)
又、最初の表は新規商品があれば行の挿入が、度々あります。
なかなかレスがつきまへんなぁ。 ほんなら、関数やおまへんけど関数まがいのマクロでやってみまっか？ 使えるかどうかまぁいっぺん試してみておくんなはれや。 （おいぼれ 弥太郎）
sheet1にデータ、sheet2のＡ列にコードをタイプっちゅう案配になっとります。
'sheet2 のコードに
'---------------
'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 1 Then
Exit Sub
Else
picup
End If
End Sub
'標準モジュールに
'-----------------------
Sub picup()
Application.OnKey "~", "macro_a"
End Sub
'-----------------------
Sub macro_a()
Dim i As Integer, n As Integer
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")
adrs = ActiveCell.Row
i = 1
n = 0
Do
i = i + 1
If ws1.Cells(i, 1) = ws2.Cells(adrs, 1) Then
If ws2.Cells(adrs + n, 1) <> "" Then
ws2.Cells(adrs + n, 2) = ws1.Cells(i, 2)
End If
ws2.Cells(adrs + n, 3) = ws1.Cells(i, 3)
n = n + 1
End If
Loop While ws1.Cells(i, 1) <> ""
End Sub
衝突しました。ん、弥太郎さんでしたか。 なかなか出てこられないので、場つなぎのレスをしてた矢先に・・・ 暇なら私のも見てみてください。そのままコピペしま〜す。 悲しくて、寂しいけど、来年が楽しみな(sin)
マクロの達人の方々は別件でお忙しそうなので、作業列を作成する方法で我慢してください。
しかも、見た目も決してきれいとは言えませんが・・・
まず、DBのシート（仮にSheet1)のA:Cにデータがあるとして、C列に列挿入し、C2に
=B2&"-"&COUNTIF($B$2:B2,B2)
で、必要行までコピー。
表示させるシートのB2に
=IF($A2="",IF(ISERROR(VLOOKUP(INDIRECT("A"&MAX(IF($A$1:A1<>"",ROW($A$1:A1),1))),Sheet1!$A$1:$D$12,2,FALSE)),"",VLOOKUP(INDIRECT("A"&MAX(IF($A$1:A1<>"",ROW($A$1:A1),1))),Sheet1!$A$1:$D$12,2,FALSE)),VLOOKUP($A2,Sheet1!$A$1:$D$12,2,FALSE))
とし、Ctrl+Shift+Enterで配列数式にします。
B2セルで【書式】-【条件付き書式】で、『数式が』-『=COUNTIF($B$1:$b1,B2)>0』フォントを白でOK。
C2セルに
=IF($A2<>"",VLOOKUP($A2,Sheet1!$A$1:$D$12,4,FALSE),IF($B2="","",VLOOKUP(B2&"-"&COUNTIF($B$1:B1,B2)+1,Sheet1!$C$1:$D$12,2,FALSE)))
とし、Ctrl+Shift+Enterで配列数式にします。
B2：C2セルをコピーして必要と思われる行まで貼り付けてください。
A2に123と入力するとC7以降は、#N/A が返されますので、A7に次の商品コードを入力してください。
商品DBは、【挿入】-【名前】でA1:D10→商品DB、C1:D10→商品DBS 等と定義付けして応用して下さい。
また、『#N/Aが嫌』っという場合は、ISNA関数でも使ってください。
多分お望みの形に近いものが出来ると思います。
どんぐりさんちょっとごめん。 sinさん、ほないに慰めてもろたら、涙がでてくるわ。阪神負けとったらクククっちゅ て笑いよるんも居るしなぁ。うぇ〜ん。 （しょぼくれ 弥太郎） >暇なら私のも見てみてください これはイヤヤでっ。あの小数点問題以来、カッコ恐怖症になってまんねん。 ざっと目ぇ通すだけでケンショウ無し。
>阪神負けとったらクククっちゅて笑いよるんも居るしなぁ。 自嘲の悲しい笑いと取ってました。ククク・・・ 上記の私の式において、同じ商品コードをダブりで入力すると、その行のC列のみ表示される事に気づきました。 対応方法は、入力規則で行う予定です。必要があれば、言ってください。 私も『ざっと目ぇ通すだけでケンショウ無し。』（＝マクロはよく分からん。）ですが、 弥太郎さんのでOKが出るでしょうから無用か？。 最後まで応援するといつも負けてします (sin)
ところで関係ないことですが、この書き込みボードで文字が大きいのと小さいのがあるのは何故？
（どんぐり）
各行の頭に半角のスペースを入れますと文字が小さくなります。
(EUREKA)
#N/Aが返ってくるのは、B列ですか？C列ですか？ ・B列ならばA列に入力した値が元の商品データに存在しないという事になります。 半角・全角などの確認をしてみてください。 ・C列の場合は、？？？です。同じ行のB列にも#N/Aが出ていれば、上記の理由です。 念のためB列の書式設定を解除して、そこに何と表示されるか確認してみてください。 解決の糸口になるかも知れません。 ・他には、式が配列数式になっているかどうか確認してみてください。 全体を補足して言いますと、 表示するシートも1行目に商品データと同じ見出しが入っているのをイメージしてます。 A列へのコード入力は、2行目から行い、C列に最初にでる#N/Aの行のA列に次の商品コードを入力します。 解決しない場合は、具体的にどうやっているかを書き込んでみてください。 (sin)
早速にありがとうございます。出てくるのはC列なんですよぉ。表のイメージとしては良いと思うんですが、又配列数式であるか確認してみました。具体的には別件の急ぎの仕事が入ってしまったので、後ほどよろしくお願いいたします。わがまま言ってすみません。
一つ気に掛かったので、確認してください。 商品DBのC列に挿入して入れた式は、すべての行にコピーされていますか？ >まず、DBのシート（仮にSheet1)のA:Cにデータがあるとして、C列に列挿入し、C2に >=B2&"-"&COUNTIF($B$2:B2,B2) >で、必要行までコピー。 ↑ この部分です。 (sin)
sinさん、衝突しました。
どんぐりさん、忙しそうでんなぁ、結構、結構。 コードのコピーの仕方がお分かりになりまへんようなんで書いときますわ。
Alt+F11でVBEを開く 左のプロジェクトウィンドウのsheet2を選択 F7でコードウィンドウを開ける sheet2の分をコピぺ 「挿入」→「標準モジュール」を選択 標準モジュール分をコピぺ Alt+Qでエクセルに戻る
コレで準備ＯＫですわ。 後はシート１にデータを適当に書き込み sheet2を表示してＡ列に商品コードを打ち込んでおくんなはれ。 お望み通りの結果がＢ、Ｃ列に表示されるはずですわ、えぇ。 （おいぼれ 弥太郎）
追記しようとしてたら、またまた、衝突しました。 ひょっとして、同じような生活習慣なのかな？もしくは、存在を知らない双子？（おいぼれてはいないが！） 追記しま〜す。 商品DBシートのC列を非表示にしていますか？ 行挿入して、A・B・D列のみデータ入力したとか？ 追加する場合は、「データのある行コピー」→「コピーした行の挿入」にするとC列の式もいい感じにコピーされます。 その分、データ書き換えのリスクはともないますが・・・ コピー用のデータ未入力行を設けておくと便利かも (sin) うっ、腰が痛い・・・
皆様ご親切にありがとうございます。sinさん、お世話を掛けました。シート名を変えたり、 シートを挿入したり･･コリャいかん！と別のブックに慌ててコピペしている間に 式やら何やら ”>=B2&"-"&COUNTIF($B$2:B2,B2)”部分に値だけ貼り付いていたりで おかしげなことになっていました。で、落ち着いてやったらちゃーんとできましたよ！ 未熟者の私がいけませんでした。配列数式気に入ったんで又使ってみたいです。
･･がかなり複雑な式なので又お手数かけるかも・・・ 腰痛には整体いかがですか？お大事に・・・ ところで弥太郎さん手取り足取りありがとうございます。それでこの後には、 マクロの実行とか必要ないですか？(ホント素人なんです お世話かけます。） 適当にマクロ実行すると次のようなメッセージが出ます。 コードのところで、"コンパイルエラー プロージャの外では無効です” で、 ”Target”に色がつています。 秋も深まり、気分は落ち込む一方・・でもここで教えてもらったことが成功すると、 暗い世間が一気に明るくなる気分 この未熟者and欲張り者をどうぞよろしく 申し遅れました。EUREKさん 文字の件ありがとうございました。 （どんぐり）
よかった、よかった。ボロを出さずに済んだ。・・かな？ 弥太郎さん、出番でっせ！ 気張ってやぁ！ (sin)
どんぐりさん、ちゃいまんがな。 関数まがいのマクロってはじめに申し上げましたやろ。 マクロの実行なんてせんでもよろしいんやで、えぇ。 関数と同じよう（sheet2のＡ列）に入力してEnterキー（テンキー、↓キーには対応 してない）を叩けば、Ｂ、Ｃ列に即座に希望通りのデータが転送されまんねん。 なにもマクロやゆうて構える必要はおまへんねんで、えぇ。 も一回初めから読み直して挑戦してみておくんなはれ。 sinさんの関数がお気に入りでしたら、それはそれでよろしおまんねんけど、更に複雑 な作業になって関数では対応でけへん事も出てきまっしゃろから、その時のに慌てん でもええ様にこんなやり方もあるんやいう事を知っといた方がよろしおまっしゃろ。 sinさんばんざ〜い！（おいぼ..もうおいぼれ止めた 弥太郎）
弥太郎さん大変失礼いたしました。関数もマクロも難しいけど、日本語はもっと難しい！！ （ってかちゃんと読んでなかったんですね。） もう一度家に帰ってからやり直しします。”更に複雑な作業になって関数では対応でけへん事も” ・・・そうなんです。そのつもりで、できるだけ若いうちに（！？）早いうちに勉強したく思います。 明日結果報告いたします。（どんぐり）
弥太郎さーん！おはようございます！！出来た 出来た 出来ましたぁ!!! まず、間違っていたのは･･･Ａ列を入力してもなにも出ないので、”=picup,=macro_a" とかマクロの実行をする前に入れていました。でも出てくるわけがありません。で、マクロの実行を試して 昨日ご注意をいただきました。 で、懲りずにやっぱり何も出てこんので、 ="picup"とか=macro_a"とか入れてみました。 "関数とおなじように”･･という言葉があったもので･･･するってぇと、値は出るんですが、Ａ列にコードを打ち込むたびに Ｂ列にこの数式を入れなくちゃいけない･･･ コピーも出来なさそうだし･･これもおかしい･･･ そこで又書き込みを読み直し表を作り直し･･"あったぁ!これだぁ!!”→"テンキーには 対応してない” でした。商品コードをテンキーで打ち込んでたもんで・・・ 何をやっているんだ、と言われそうですが私にとって文明開化ですよー早く私の頭も 文明開花しなくちゃ！ sinさんの関数の違いはsheet1で行挿入した場合に出てます。どちらもコードを打ち直す必要があるようなんですが （まだ完璧には運用していないので）弥太郎さんのほうが sheet2で空欄で挿入されてくるので分かりやすいかも･･･悲願だったんですよ。sheet1 に行挿入されるとリンクページでも行挿入されるというの。 皆様いろいろこの未熟者相手にありがとうございました。呆れ顔が目に浮かぶようですが・・ 又面倒見てやってください。日本語の勉強もしておきます。今日は世間も気分も快晴!!
どんぐりさん、でけてよろしおましたなぁ。
ところで、テンキーの方が都合よろしいんでっか？
せやったら"~"の所"{Enter}"に換えて下さい。
これでテンキーを叩いたらＯＫですわ。
ほな...（弥太郎）
最後まで面倒見てもらってありがとうございます。テンキーが主なのでとても助かりました。 あの部分を変えることでテンキーになっちゃうんですね。やはり暗号（？）解読に努めなければ・・・ また燃えてきた!! ほんとありがとうございました。（どんぐり）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.