[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『vlookupやhlookupの反対は?』（悩む子）
LOOKUPなどは、指定範囲からキーワードで指定したものを抜き出してくれますが、逆に
その元にあった場所に戻すような、関数ってあるのでしょうか。または戻す方法があったら、教えて頂けないでしょうか。つまり、表があり各項目データは、VLOOKUPで抜き出すことはできましたが、抜き出した情報を加工して、その元の場所に戻したいのですが?
良い方法があったら、教えていただきたい。データベースでなく、EXCELでの方法です。
お願いします。マクロは得意じゃないんですけど・・・
ご希望の手順を関数が実在するとすれば、確実に循環参照になると思います。 例えばA1に元の値、B1に悩む子さんがご希望の関数が入っていた場合、B1の関数によってA1が書き換えられますが、再びB1の計算が行われ、 永遠にA1の値が確定しないですよね？
対応策としては、表の変化させたい部分に、変化の規則に応じた関数を入力しておくことです。 もし規則性が全く無いのであれば、関数どころか、例えマクロでも再現は難しいと思います。
（ショウ）
10月1日 売上げ A B C AA AB AC AD AE AF AG -------------------------------- ------------------------------------------- 1 品名 数 単価 10/1 りんご 10 10 なし 25 20 -------------------------------- ------------------------------------------- 2 りんご 10 10 10/2 ぶどう 5 20 かき 3 15 -------------------------------- ------------------------------------------- 3 なし 25 20
* * * * * * 表 * * * * * * * * <---->* * * * * データ保存領域 * * * * * *
上記のように、売上げ表を作り、いつでも日付により保存領域から、呼び出すのに
VLOOKUP関数で、呼び出せるようにしてありますが、呼び出した表の、例えば、10月1日の数に誤りがあったとして、この表上で修正を加え、元の10/1のデータ保存領域の10/1の下の場所に戻し、再利用したいというのが目的ですが、どうしてもできないのですが良い方法はないでしょうか。現在は呼び出すときに別な場所に一旦、VLOOKUPで、呼び出して、それをコピーし、値を貼り付けで表に移す、記録マクロ(記録マクロしか分かってません)を使っています。以上ですが、お願いします。
「データ保存領域」はもちろん全ての履歴が残されているものと思いますが、「表」についてはどうでしょうか？ 例えば、上記の「表」では10月1日についてのみ書かれていますが、その下に10月2日、3日と続いているのでしょうか？ （ショウ）
まず、数字が変わったかどうかをエクセルに確認してもらうために 表のＤ列に「数量訂正」列をもうける。
１．データ保存領域の書式を変えない
D2に数字が入っている場合
日付をAA列の一番最後に（AA3)
A2をAB列、D2-B2をAB列、C2をAC列 それぞれ一番下（３行目）にコピー。
D3にも数字が入っている場合
日付をAA列の一番最後に(上でAA3に入れたのでその下のAA4）
A3をAB列、D3-B3をAB列、C3をAC列 それぞれ一番下（４行目）にコピー。
この「一行毎の確認→コピー」作業をVBAでやらせればデータ保存領域に
データを追加する事が出来ると思います。
但し、一日に１０件変更があったら、１０行増えます。
２．データ保存領域の書式が変わってもいいなら
AA AB AC AD AE AF AG AH AI
---------------------------------------------------------
1 りんご なし ぶどう かき
---------------------------------------------------------
2 10/1 10 10 25 20
---------------------------------------------------------
3 10/2 5 20 3 15
---------------------------------------------------------
Ｄ列の「一行毎の確認→コピー」は必要ですが
Ａ列の名前を確認し「りんご」ならABとAC、「なし」ならADとAE にコピー
としておけば、一日の変更で一行しか増えません。
上の二つは「確認」がマクロの自動登録では出来ない って所が問題ですかね。 あとは、一行毎に確認させるとなると、行数が多い場合時間がかかる事があります。
３．そもそも 表自体が変わってもいい
となると、関数とマクロで出来そうです。
（作業領域が必要になりますし、品名数にもよりますが）
さて、以上はデータ保存領域にデータを追加する方法でしたが それを集計しようとした時、キーになる日付が二つあった場合 VLOOKUPでは どちらか一方しか出てきません。 数字を「引っ張ってくる」のではなく「集計」が必要になります。
つらつらと書いてみましたが、何かいい方法はきっと見つかると思います。 色々情報収集をしてみて下さい。
(HANA)
横から失礼します。
保存領域(列AA～)から、表に呼び出すときに、VLOOKUP関数を使っているとのことですよね。 どのような式か分かりませんが、修正後、保存領域を変更させるなら、(ショウ)さんが仰るように、 循環参照になります。今現在のところ、保存領域から表への呼び出しはうまく行っているわけですから、 別に下記のようなマクロを組んでみてはどうでしょうか？どこかのセルに、変更する日付 (例えばセルD1)を入力後、下記のマクロを実行したらうまく行きそうですが。
①セルD1と一致する行を、保存領域から探す
②A～C列のデータ（関数ではなく値のみ）を、保存領域に貼り付け
(例えば、最下行になりますが、range("ab65536").end(xlup).offset(1).value=range("a1").value みたいに)
③A～C列およびセルD1のデータをすべて削除
これなら循環は避けれそうですが。。。
(HANA)さんの方法も考察すべきでしょうね。 (ken)
表（現在は確認のみで直接変更修正できない．ここから入力したい）
↑ ***（ここで情報交換したいができない）****
（貼付け） データ保存領域（実際のデータ入力と修正はこの場所）
↑ ↓
↑←←←←←（コピー）←呼び出し領域（日付に準じて、ＶＬＯＯＫＵＰで呼出）
（表に関数入れると壊される可能性があったので）
kenさんの書き込みから思いついたことがあるのですが （もしかしたら同じ事を繰り返して言っているだけかもしれませんが） 一つ確認させて頂きたいことがあります。
表の日付と一致する日付を、保存領域(AA列)から探し出す
と言う事が 悩む子さんにとってマクロで出来る事でしょうか？ それとも、そこが問題ですか？
「出来る」と言うのであれば、話はとても簡単な気がします。 表では品名が縦に並んでおり、日付も別の所にありますが それを一度 保存領域と同じ形に作り直して、 “表の日付と一致する日付を、保存領域(AA列)から探し出し” 上から貼り付けてしまえば良いわけですよね。
横に並べるのは、IF関数とセルの参照を組み合わせるだけで =IF(A2="","",A2) =IF(A2="","",B2) =IF(A2="","",C2) =IF(A3="","",A3) =IF(A3="","",B3) =IF(A3="","",C3) と、３個セット（品名・数量・単価）の関数を一つずつ ひたすら右へ右へと入れていきます。 できあがったら、保存領域にある日付の場所にコピーして貼り付けです。
たとえば、「出来ない」と言う場合でも
1. 保存領域にオートフィルタをかける。
2. 「表」になっている日付の行を「保存領域」から呼び出し 削除。
3. オートフィルタを解除。
4. 横に並べた新しいデータの最初に売上日を付けた物を一番下に貼り付け。
5. 日付順(AA列）で並べ替え。
6. （行を削除した際、「表」部分も消える可能性があるので
確認と言う名目で（笑））
もう一度同じ日付で「表」にするマクロを実行。
って感じのは如何でしょうか？ ２番目の工程がマクロの記録で出来るかどうか 分からないのですが・・・・。
(HANA)
と、署名しておいて もう一つ再度確認しておきたい事があります。 「表」の部分は関数ではなく、数値や文字として入っているんですよね。
今行われているマクロと言うのは
「表」や「保存領域」とは全く別の場所で
=VLOOKUP(日付,保存領域,2,FALSE) =VLOOKUP(日付,保存領域,3,FALSE) =VLOOKUP(日付,保存領域,4,FALSE)
=VLOOKUP(日付,保存領域,5,FALSE) =VLOOKUP(日付,保存領域,6,FALSE) =VLOOKUP(日付,保存領域,7,FALSE)
上の様な関数で、一日一行で並んでいる保存領域のデータを品目毎に縦に並べかえ
その値のみを「表」に貼り付けている。
と言うことで宜しいのでしょうか？ つまり、「表」部分に関数を入れても循環しないが マクロを実行するたびに消えてしまう・・・・。
今度こそ(HANA)
①“表の日付と一致する日付を、保存領域(AA列)から探し出し”
上から貼り付けてしまえば良いわけですよね。***私もそうしたい。
②表」の部分は関数ではなく、数値や文字として入っているんですよね。
今行われているマクロと言うのは
「表」や「保存領域」とは全く別の場所で
=VLOOKUP(日付,保存領域,2,FALSE) =VLOOKUP(日付,保存領域,3,FALSE) =VLOOKUP(日付,保存領域,4,FALSE)
=VLOOKUP(日付,保存領域,5,FALSE) =VLOOKUP(日付,保存領域,6,FALSE) =VLOOKUP(日付,保存領域,7,FALSE)
上の様な関数で、一日一行で並んでいる保存領域のデータを品目毎に縦に並べかえ
その値のみを「表」に貼り付けている。***全くそのとおりです。
③表の日付と一致する日付を、保存領域(AA列)から探し出す
と言う事が 悩む子さんにとってマクロで出来る事でしょうか？ それとも、そこが問題ですか？***全く問題ですね。
以上の状況です。何度も何度も、皆様には大変申し訳ありません。(悩む子)
(悩む子) さんが記録しているマクロ、およびVLOOKUP関数を、ここに提示してみてはどうでしょうか。 そうすれば、レスも付くように思いますが。
(ken)
レスでなくて申し訳ないのですが、↑とあわせてどこまで許容出来るかも教えてください。 １．関数のみで何とかすませたい ２．得意ではないがマクロの記録くらいまでならＯＫ ３．VBAだろうと何だろうと、とにかく結果が出るようにしたい ４．その他 辺りで・・・・・。
(HANA)
皆さん、色々ありがとうございます。実際私が、出来る程度を下記に記入したら、程度
が、わかっていただけるということなので、設問に沿って作ってみました。
AA AB AC
8 =VLOOKUP($A$1,$AA$2:$AF$7,2) =VLOOKUP($A$1,$AA$2:$AF$7,3) =VLOOKUP(.....4)
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
9 =VLOOKUP($A$1,$AA$2:$AF$7,5) =VLOOKUP($A$1,$AA$2:$AF$7,6) =VLOOKUP(.....7)
Sub Macro1()
'
' Macro1 Macro
Range("AB8:AD9").Select
Selection.Copy
Range("A3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A3").Select
End Sub
ということで、いたって単純なことしかしていません。日付の変更は、スクロール
バーで変化させ、これに一緒にモクロ登録して、使っています。HANAさんの質問に対
しては、出来れば関数のみで終わらせれるものだったら、それが一番ベターですけど
簡単なマクロで解決できるのであれば、それにも挑戦します。最後はやはり結果をだ
したいのでとことん勉強させていただきます。 (悩む子) 以上よろしくお願いし
ます。 ところでレスって何????
なんとなくは分かりましたが、こんな感じでしょうか？
勝手に、シートを変更しましたが・・・
A B C ････ AA AB AC AD AE AF AG ･･･ AP
1 日付 日付 品名 数 単価 品名 数 単価 単価
2 品名 数 単価 10/1 りんご 10 10 なし 25 20
3 10/2 ぶどう 5 20 かき 3 15
このような表として、下記マクロを作りましたので、御参考までに。ただし、セルB1に、検索したい 日付を入力して下さい。 また、1日に入力できる最大数を5つまでにしてますので、増えるなら変更しなくてはなりません。
まず、当該シートのマクロに、下記をコピペ。
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Row = 1 And Target.Cells.Column = 2 And Cells(1, 2) <> "" Then
Call 前処理
End If
End Sub
次に、標準モジュールに、下記をコピペ。
Function 前処理()
Dim 行, a, b, c As Long
Range("A3:C7").Formula = ""
For 行 = 3 To 7 a = 行 - 1 b = (行 - 2) * 3 + 24 c = (行 - 2) * 3 - 1
Cells(行, 1).FormulaR1C1 = _
"=IF(R[-" & a & "]C[1]="""","""",IF(VLOOKUP(R[-" & a & "]C[1],C[26]:C[" & b & "]," & c & ",0)="""","""",VLOOKUP(R[-" & a & "]C[1],C[26]:C[" & b & "]," & c & ",0)))"
Cells(行, 2).FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],C[" & b - 1 & "]:C[" & b & "],2,0))"
Cells(行, 3).FormulaR1C1 = _
"=IF(RC[-2]="""","""",VLOOKUP(RC[-2],C[" & b - 2 & "]:C[" & b & "],3,0))"
Next 行
End Function
Sub 決定()
Dim 日付 As Variant Dim a As Long
日付 = Range("B1").Value
For a = 2 To Range("AA65536").End(xlUp).Row
If Range("AA" & a).Value = 日付 Then
Cells(a, 28).Value = Cells(3, 1).Value
Cells(a, 29).Value = Cells(3, 2).Value
Cells(a, 30).Value = Cells(3, 3).Value
Cells(a, 31).Value = Cells(4, 1).Value
Cells(a, 32).Value = Cells(4, 2).Value
Cells(a, 33).Value = Cells(4, 3).Value
Cells(a, 34).Value = Cells(5, 1).Value
Cells(a, 35).Value = Cells(5, 2).Value
Cells(a, 36).Value = Cells(5, 3).Value
Cells(a, 37).Value = Cells(6, 1).Value
Cells(a, 38).Value = Cells(6, 2).Value
Cells(a, 39).Value = Cells(6, 3).Value
Cells(a, 40).Value = Cells(7, 1).Value
Cells(a, 41).Value = Cells(7, 2).Value
Cells(a, 42).Value = Cells(7, 3).Value
Exit For
End If
Next a
End Sub
これで、ご確認を。
(ken)
＞ところでレスって何???? たぶん、response (返答) の略だと思うんですが。ちなみに、ken は、今、日本以外の国に住んでますが、 英語圏では、レスという表現はありません。基本的には、reply (返信) を使いますね。PC をパソコンと いうみたいに、和製英語だと思うんですが。どなたかの連絡でも待ちましょうか。
(ken)
ウィキペディアの「レス」の項目です（庸）
はっっ、レスって一般用語ではなかったのですね。 申し訳ないです。
関数とマクロの記録で難関箇所を試してみた所 （ある特定の日付の行を消す って箇所です） 上手く行ったみたいなので、もしかしたら 関数とマクロの記録のみで全部出来るかもしれません。
ただし、手順が煩雑なのと（まぁ一回記録したら終わりですが） シートが概算５枚必要で、表とデータ領域が違うシートになってしまいます。 あと、手順が煩雑な分 何かあったときのメンテナンスが大変かもしれません。
それでも問題なければ組み立てて手順を書こうと思いますが如何でしょうか？
(HANA)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.