Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


8999 / 13644 ツリー ←次へ | 前へ→

【29706】検索表示 sim 05/10/12(水) 9:52 質問[未読]
【29712】Re:検索表示 Jaka 05/10/12(水) 13:26 発言[未読]
【29713】Re:検索表示 sim 05/10/12(水) 14:06 発言[未読]
【29743】Re:検索表示 ichinose 05/10/12(水) 22:01 発言[未読]
【29757】Re:検索表示 sim 05/10/13(木) 8:49 お礼[未読]
【29809】Re:検索表示 ichinose 05/10/13(木) 23:51 発言[未読]
【29824】Re:検索表示 sim 05/10/14(金) 12:04 お礼[未読]
【29758】Re:検索表示 Jaka 05/10/13(木) 9:15 お礼[未読]
【29815】Re:検索表示 ichinose 05/10/14(金) 8:10 発言[未読]
【29898】Re:検索表示 gako 05/10/15(土) 21:19 発言[未読]

【29706】検索表示
質問  sim  - 05/10/12(水) 9:52 -

引用なし
パスワード
   お世話になります。VLOOKUP関数で検索値を2つにした場合に検索がうまくできないのですがどのように式を書けばよいのでしょうか?初歩的質問ですみませんがよろしくお願いいたします。

【29712】Re:検索表示
発言  Jaka  - 05/10/12(水) 13:26 -

引用なし
パスワード
   すみません、意味がわかりません。
誰が読んでも意味の通る質問内容にしてください。
どのような表で、どのような検索値で、どのような結果がほしいのか具体的に書いてください。

【29713】Re:検索表示
発言  sim  - 05/10/12(水) 14:06 -

引用なし
パスワード
   ▼Jaka さん:
>すみません、説明不足でした。
   A    B     C  ・・・・・・・・・・AA   AB     AC
  −−−−−−−−−−−−−−−−−−−  −−−−−−−−−−−−−−−−− 1| 地区| 個人 |  氏名     |  | 地区| 個人 |  氏名 |
  −−−−−−−−−−−−−−−−−−−  −−−−−−−−−−−−−−−−−
 2| 001 | 0101 | VLOOKUP関数 |  | 001 | 0101 | ああああ|
  −−−−−−−−−−−−−−−−−−−  −−−−−−−−−−−−−−−−− 3| 001 | 0102 | VLOOKUP関数 |  | 001 | 0102 | いいいい|
  −−−−−−−−−−−−−−−−−−−  −−−−−−−−−−−−−−−−−
 4| 001 | 0101 | VLOOKUP関数 |  | 001 | 0103 | うううう|
  −−−−−−−−−−−−−−−−−−−  −−−−−−−−−−−−−−−−−
 5| 001 | 0103 | VLOOKUP関数 |         ↑
  −−−−−−−−−−−−−−−−−−−        基礎データ
 6| 001 | 0102 | VLOOKUP関数 |       
    ↑    ↑     ↑      
   入力1  入力2  ああああ
             いいいい
             うううう
 入力1、入力2に番号を入れると氏名が自動で入るようにしたいのですが、VLOOKUP関 数は検索値が2つの場合も使えるのでしょうか?
  ちなみに式は
  =VLOOKUP(A2:B2,AA2:AC4,3,FALSE)

 ですが #VALUE! となります。すみませんがよろしくお願いいたします。

【29743】Re:検索表示
発言  ichinose  - 05/10/12(水) 22:01 -

引用なし
パスワード
   sim さん、Jaka さん、こんばんは。

>   A    B     C  ・・・・・・・・・・AA   AB     AC
>  −−−−−−−−−−−−−−−−−−−  −−−−−−−−−−−−−−−−− 1| 地区| 個人 |  氏名     |  | 地区| 個人 |  氏名 |
>  −−−−−−−−−−−−−−−−−−−  −−−−−−−−−−−−−−−−−
> 2| 001 | 0101 | VLOOKUP関数 |  | 001 | 0101 | ああああ|
>  −−−−−−−−−−−−−−−−−−−  −−−−−−−−−−−−−−−−− 3| 001 | 0102 | VLOOKUP関数 |  | 001 | 0102 | いいいい|
>  −−−−−−−−−−−−−−−−−−−  −−−−−−−−−−−−−−−−−
> 4| 001 | 0101 | VLOOKUP関数 |  | 001 | 0103 | うううう|
>  −−−−−−−−−−−−−−−−−−−  −−−−−−−−−−−−−−−−−
> 5| 001 | 0103 | VLOOKUP関数 |         ↑
>  −−−−−−−−−−−−−−−−−−−        基礎データ
> 6| 001 | 0102 | VLOOKUP関数 |       
>    ↑    ↑     ↑      
>   入力1  入力2  ああああ
>             いいいい
>             うううう
> 入力1、入力2に番号を入れると氏名が自動で入るようにしたいのですが、VLOOKUP関 数は検索値が2つの場合も使えるのでしょうか?
>  ちなみに式は
>  =VLOOKUP(A2:B2,AA2:AC4,3,FALSE)
>
> ですが #VALUE! となります。すみませんがよろしくお願いいたします。

これは、Vlookup関数単独では無理だと思います。
VBAで処理した方が後々の変更の事も考えると簡単そうなのですが・・・。
敢えて数式で考えましょうか!!

マスターデータが セルAA1から
    AA   AB   AC
1   地区   個人   氏名
2   001   0101   ああああ
3   001   0102   いいいい
4   001   0103   うううう
5   002   0101   えええええ
6   002   0102   おおお
7   002   0103   かかか

というように入力されているとします。

尚、AA列の書式はユーザー定義で「000」、
  AB列の書式はユーザー定義で「0000」
に設定されているため「001」等と表示されるのであって、
セルには、数値が入力されているものとします。

又、このマスターデータの配置の条件として
地区(コード)は、同じ番号は必ず、連続しているものとします。
つまり、上記のように地区001ものが連続して入力され、
すべての001のデータが入力後、次の002のデータを入力する。

地区
001
001
002
001

こういうパターンはNGです。

又、実際にデータを入力する
A列、B列の書式もそれぞれAA列、AB列と同じにします。

以上の条件の時、

セルC2には、

「=OFFSET($AC$2,MATCH(A2,$AA$2:$AA$7,0)+MATCH(B2,OFFSET($AA$2,MATCH(A2,$AA$2:$AA$7,0)-1,1,COUNTIF($AA$2:$AA$7,A2),1),0)-2,0,1,1)」

という数式を入力してみてください。
後は、フィル操作という手順です。

ご覧のとおり、私も明日になれば、どういうロジックだったか
忘れてしまいそうです。

VBAをご存知なら、VBAで行う事も検討してみてください。

【29757】Re:検索表示
お礼  sim  - 05/10/13(木) 8:49 -

引用なし
パスワード
   ▼ichinose さん:
できました\(^^)/ありがとうございました。

>VBAをご存知なら、VBAで行う事も検討してみてください。

ちなみにVBAではどのように書くのでしょうか?AccessのVBAは何度か

書いたことがあるのですが・…よろしかったら教えて下さい。

【29758】Re:検索表示
お礼  Jaka  - 05/10/13(木) 9:15 -

引用なし
パスワード
   ichinose さん こんにちは。
フォローありがとうございます。

最初は、DGETが使えるんじゃないかと思っていたんですが使えず、配列関数?かとか、一晩考えたんですが、何も思いつきませんでした。
もっと勉強しよっと!(言うのはやすしで、言うだけですが....。)

【29809】Re:検索表示
発言  ichinose  - 05/10/13(木) 23:51 -

引用なし
パスワード
   ▼sim さん:
>▼ichinose さん:
>できました\(^^)/ありがとうございました。
>
>>VBAをご存知なら、VBAで行う事も検討してみてください。
>
>ちなみにVBAではどのように書くのでしょうか?AccessのVBAは何度か
>
>書いたことがあるのですが・…よろしかったら教えて下さい。
VBAを使用すれば、方法はいくつもありますよ!!

そのひとつとして、

シートのレイアウト等は前回の投稿と同じとします。

当該シートのシートモジュールに

'===========================================================
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim crng As Range
  Dim f_value As Variant
  Dim f_rng As Range
  Dim add1 As String
  Dim add2 As String
  Dim ans As Variant
  Application.EnableEvents = False
  For Each crng In Target
    With crng
     If .Row >= 2 And (.Column = 1 Or .Column = 2) Then
      If datachk(.Row) = True Then
        f_value = Cells(.Row, 1).Value * 10000 + Cells(.Row, 2).Value
        Set f_rng = Range("aa2", Cells(Rows.Count, 27).End(xlUp))
        add1 = f_rng.Address
        add2 = f_rng.Offset(0, 1).Address
        '↓ 一例として match(a2*10000+b2,($AA$2:$AA$8)*10000+$AB$2:$AB$8,0)
        '        こんな配列数式の実行
        ans = Evaluate("match(" & f_value & ",(" & add1 & ")*10000+" & add2 & ",0)")
        If IsError(ans) Then
         MsgBox "not found"
        Else
         Cells(.Row, 3).Value = f_rng.Cells(ans, 3).Value
         End If
        End If
       
       End If
     End With
    Next
  Application.EnableEvents = True
End Sub
'===================================================================
Function datachk(rw As Long) As Boolean
'A列とB列のデータのチェック
'true---- 正常データ
'false---不正データ 又は、データが未完成
  datachk = False
  If Cells(rw, 1).Value = "" Or Cells(rw, 2).Value = "" Then
    Exit Function
    End If
  If IsNumeric(Cells(rw, 1).Value) And IsNumeric(Cells(rw, 2).Value) Then
    datachk = True
  Else
    MsgBox "データが不正です。数値を指定してください"
    End If
End Function


これでA列B列にデータを入力してみてください。
不正データはエラーメッセージを表示するようにしました。
確認してみてください。

【29815】Re:検索表示
発言  ichinose  - 05/10/14(金) 8:10 -

引用なし
パスワード
   ▼Jaka さん:
おはようございます。


>
>最初は、DGETが使えるんじゃないかと思っていたんですが使えず、配列関数?かとか、一晩考えたんですが、何も思いつきませんでした。

でも、数式とSQLについては、いつも思うことなんですが、一行で
出来てしまうのは 悩んで作った直後は、「やったあ!!」なんて思いますが、

出来た数式の複雑さに実際には 

使えないなあ

という結果になることもしばしばです。

ここでは、それを見切る練習だと思って
これからも実際には 使えない(私が使わない)投稿をするかもしれません。
でも、ここは「VBA研究所」だから いいよね!!

【29824】Re:検索表示
お礼  sim  - 05/10/14(金) 12:04 -

引用なし
パスワード
   ▼ichinose さん:
できました、今後の参考にします。ありがとうございました\(^^)/

【29898】Re:検索表示
発言  gako  - 05/10/15(土) 21:19 -

引用なし
パスワード
   単純な方法ではありますが
地区1と2の二つの表を仕上げ
→Indirectを使った関数でやれば、できると思います。

=VLOOKUP(B2,INDIRECT(B3),2,TRUE)

このB2に地区分けをするとできます
B3に個人のコード(個人コード右の列に個人名)


個人コード 氏名
101     AAA
102     BBB
 :     :
(地区001)


個人コード 氏名
101     AAA
102     BBB
 :     :
(地区002)

地区分けは名前の定義で(範囲指定して)区分させると
良いでしょう。

8999 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free