[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『関数のコピー？』（すずりん）
こんばんわ！ みなさま、いつもお世話になっております。
vlookup関数、sum関数などが入った作業日報シートを作りました。 そこで質問です！ 改行するたびに、その関数が、下の行にコピーされる・・・ なんていうことは可能でしょうか？ よろしくお願いいたします。
ご希望とはちょこっと違いますが Ctrl+Dで、直上のセルのコピーができますが、それではだめでしょうか？ ちなみに、Ctrl+Rは、左隣のセルをコピーします＾＾ （庸）
A列に入力すると、B列に数式が入ってほしいのは
こんな感じでしょうか（あきお＝パンヤに入れない）
Private Sub Worksheet_Change(ByVal Target As Range)
If Cells(Target.Row, 1) <> "" And _
Cells(Target.Row, 2) = "" Then
'入力セル行 ~B列
Cells(Target.Row, 2).FormulaR1C1 = "=RC[-1]*2"
'入力セルのB列の数式セット
End If
End Sub
（庸）さま、もう一人のお方（お名前私が間違えて消してしまったのでしょうか?）
早速の返信ありがとうございます。
えっと、なぜこのような質問をしたのかといいますと・・・
一日に、100行ほどC～O列に、入力しています。
(そのうち G・H・I・J・O列 式が入っています。）
オートフィルコピーで未入力のセルに数式コピーしたりしているのですが、
\x{fffd}@入力途中で数式の入ったセルがなくなるということが微妙に煩わしい
\x{fffd}Aデータや数式の入ったセルが多いと、エクセルの動きが鈍くなる
なので！できれば、改行すると、上の行の式が自動的にコピーされて
入力が手を止めることなく続けられる・・・
ようなことがしたいのです。
ですので、（庸）さまのアドバイス、ありがたいのですが、今回のこの件には
あてはまりません。ごめんなさい。
そしてもう一人の方の、マクロ（？）の式ですが・・・
マクロなど使ったことがないの正直よく意味が分かりません。
この式をどのように使えばよいのか教えていただけせんか？
ちなみに、とりあえずG列に入っている式は・・・
=IF(F2461="","",VLOOKUP($F2461,$R$7:$T$36,2,0))です。
2462行以降のセルへ対応させたいのです。
説明わかりづらかったらごめんなさい。
いえ、私が消しちゃったかもです,m(__)m （あきお）
パッとおもったのは、
=IF(F2461="","",VLOOKUP($F2461,$R$7:$T$36,2,0))です。
~~~~~~~~~~ここんとこ
ここんとこ、は別シートのほうがよいと思います（そのままでもよいですけど）
んで、試しに、新規のブックでお試しいただけたらありがたいのですが、
Sheet2
A B
1 100 あああ
2 200 かかか
3 300 さささ
と用意して、
Sheet1 に以下のマクロを追加します # マクロ作成は、以下を参考になさってみてください。 # Worksheet_SelectionChange の代わりに Worksheet_Change です 「 (4)Worksheet_SelectionChangeの簡単な作成方法」 https://www.excel.studio-kazu.jp/mag2/backnumber/mm20040727.txt
Private Sub Worksheet_Change(ByVal Target As Range)
'入力セルの、F列(6)が未入力 かつ 1行下の数式の入るG列(7)が未入力のとき
If Cells(Target.Row, 6) <> "" Then
'入力セルの、1行下のG列(7)に数式をセット
Cells(Target.Row + 1, 7).FormulaR1C1 = _
"=IF(R[-1]C[-1]="""","""",IF(ISNA(VLOOKUP(R[-1]C6,Sheet2!R1C1:R30C3,2,0)),"""",VLOOKUP(R[-1]C6,Sheet2!R1C1:R30C3,2,0)))"
'入力セルの1行下のG列に数式セット
'もしも、もっと数式があんまり長くなったら、& で、文字列連結（蛇足）
'とりあえず、R1C1にして、マクロの記録で、数式がどんなふうに記録されるか確かめてみるのも手です
End If
End Sub
■ Sheet1のF1に 100 と入力すると、"あああ"と表示される・・といいな。
Sheet1
F G
1 100
2 あああ
3
【もうちょっと追加】 私は、R1C1参照形式にしてるので、確認だけそれで試してもらえるとよいのですが。 7(G列)に数式を入力してみて、[F2]を押下すると、 シートの上の fx （数式バー？名前知らない）のところにカーソルが移動すると思いますが、 そこで、 =IF(R[-1]C[-1]="","",IF(ISNA(VLOOKUP(R[-1]C6,Sheet2!R1C1:R30C3,2,0)),"",VLOOKUP(R[-1]C6,Sheet2!R1C1:R30C3,2,0))) となっているはずです。 時間がかかるときは、数式から値にマクロ内でやってしまいます（値のコピペ） さらに蛇足ですが、Sheet2!R1C1 の R1C1 のところ（4文字のどこでもよい）にカーソルをあてて [F4]を押下すると、参照の形式が絶対参照から相対参照にコロコロ変わります。(A1形式でも同じです）
あ、読み返してみて。数式は同じ行に入れればよいのですか！だとしたら、 Cells(Target.Row, 7).FormulaR1C1 = _ "=IF(RC6="""","""",IF(ISNA(VLOOKUP(RC6,Sheet2!R1C1:R30C3,2,0)),"""",VLOOKUP(RC6,Sheet2!R1C1:R30C3,2,0)))" ですね～
(あきお)さま 大変お返事が遅くなってしまい、申し訳ありません。 マクロ初心者なので、試行錯誤しておりました。 で、G列へのコピーですが、うまくいきました！ す、す、すごいですぅ！感激してしまいました。 ほんと、エクセルって何でもできちゃうんですね。
そこで、H・I・J・O列にも挑戦しようと悪戦苦闘しているのですが・・・。 なんだかうまくいきません。 アドバイスいただけませんでしょうか？
ちなみに作業日報表と各列の数式は以下です。 (vlookupの参照先は"sheet2"に変更しました。）
B C D E F G H I J K L M N O 整理No 年 月 日 作業者コード 作業者名 在班名 作業班 作業工番 正常 異常 合計
2546 18 01 15 22 山田太郎 平田班 小室班 応 NC05-304 TK 2.50 0.25 2.75 2547 18 01 15 23 田中 博 平田班 平田班 NT05-204 EW 1.80 1.80
H列=IF(F2454="","",VLOOKUP($F2454,Sheet2!$A$1:$C$30,3,0)) I列=IF(F2454="","",IF(OR(K2454="出張",K2454="講習",K2454="検診"),"",IF(OR($F2454=$R$21,$F2454=$R$24,$F2454=$R$27,$F2454=$R$28),"機",IF($K2454="",VLOOKUP($F2454,Sheet2!$A$1:$C$30,3,0),VLOOKUP(LEFT($K2454,2),Sheet2!$E$1:$F$10,2,0))))) J列=IF(OR(K2454="出張",K2454="講習",K2454="検診"),"",IF(H2454=I2454,"","応")) O列=SUM(M2454:N2454)
なんだかだらだら長い数式なのですが、こんな感じでやっております。 ･･･どうぞよろしくおねがいいたします。 (すずりん）
おまたせ～ ^^)/~ ちょっと時間ができたので整理してみました（あきお） くれぐれも、オリジナルのブックは、バックアップしてお試しくださいまし。 （オリジナルのブックをコピーしてから、試すのがセオリーです） それから、過去データの数式がセルに残っていると、参照がうまくいかなかったり、遅いので、 シート全体をコピーして、値にしといたほうがよいですね（おそらく対応済みでしょうけども） # 過去は終わったものと割り切り、内容の変更は無いもの、として・・
1. まず、解りやすくするため、VLOOKUP() などで参照したい表に、名前をつけます
# メンテを考えて、表別にシートを分けてみました（お好みで
1-1. 作業者表（Sheet2!$A$1:$C$30）
A B C
作業者コード 作業者名 在班名
22 山田太郎 平田班
23 田中 博 平田班
1-2. 作業班表（Sheet3!$A$1:$B$10）
A B
作業 作業班名
NC 小室班
NT 平田班
1-3. 機の表（Sheet4!$A$1:$A$3）
A
機
100
200
1-4. 状況表（Sheet5!$A$1:$A$3）
A
状況
出張
講習
検診
2. やりたいことの整理（要件定義）
・入力項目で関係するのは、
F列[作業者コード]、K列[作業工番]、M列[正常]、N列[異常]
・表示項目で関係するのは、
G列[作業者名] ...「作業者表」から[作業者名] をセット @1
H列[在班名] ...「作業者表」から[在班名] をセット @2
I列[作業班名] ...これがやっかい（笑）
次のいずれかのとき、クリア
F列[作業者コード] 未入力 @3
K列[作業工番] が「状況表」に存在するとき @4
次のとき、"機" の文字 をセット
K列[作業工番] が、「機の表」に存在するとき @5
次のとき、H列[在班名] をセット
K列[作業工番] が、未入力のとき @6
次のとき、[作業班名] をセット
K列[作業工番] の先頭2文字が、「作業班表」に存在するとき @7
J列[(可否?)] ...次のいずれかのとき、クリア
F列[作業者コード] 未入力 @3
K列[作業工番]が「状況表」に存在するとき @8
H列[在班名] = I列[作業班名] のとき @9
上記以外のとき、"応" の文字 をセット @A
O列[合計] ...次のとき、再計算
M列[正常] または N列「異常」を入力したとき
3. 実際にやってみると
・F列[作業者コード] を入力したときの動きとしては、
G列[作業者名] ... [作業者名] のセット @1 #「作業者表」から
H列[在班名] ... [在班名] のセット @2 # 同上
I列[作業班名] ... K列[作業工番] が未入力ならば、H列[在班名] をセット @6
・F列[作業者コード]の値をクリアしたときの動きとしては、
G列[作業者名]、H列[在班名]、I列[作業班名]、J列[(可否?)] をクリア @3
・K列[作業工番]を入力したときの動きとしては、
I列[作業班名] ... F列[作業者コード] の判定
未入力のとき --> クリア @3
【追加】 「機の表」に存在するとき --> "機"の文字をセット @5
K列[作業工番] の判定
「状況表」に存在するとき --> クリア @4
【間違い】 「機の表」に存在するとき --> "機"の文字をセット @5
未入力のとき --> H列[在班名] をセット @6
「作業班表」に存在するとき --> [作業班名] をセット @7
# K列[作業工番]の先頭2文字
J列[(可否?)] ... F列[作業者コード] の判定
未入力のとき --> クリア @3
I列[作業班名] の判定
H列[在班名] と等しいとき --> クリア @9
H列[在班名] と等しくないとき --> "応" の文字 をセット @A
4. あとで、マクロにしてみます
＜表の名前の付け方 参考URL＞ スプーンおばさんのパソコン教室 エクセル応用 02 http://homepage3.nifty.com/pcsalon/eo/eo02.htm エクセルの学校（e1Ow）リストボックス https://www.excel.studio-kazu.jp/lib/e1Ow/e1Ow.html
＜相対参照・絶対参照の考え方 参考URL＞ エクセルの学校 エクセル・マイスター(8) https://www.excel.studio-kazu.jp/mag2/backnumber/mm20040720.txt
（あきお）さま またまた感激しておりますぅ・・・。 ここまで丁寧な対応をして頂いて･･･涙でそうです（マジで！） 整理していただいた内容も、ばっちりです。 ぜひぜひ、よろしくお願いいたします。 ・・・・・・・・・ （あきお）さま！実は、本当はもうひとつ条件を入れたくて、頭を悩ませていた事がありました。 やっかいだと思われている、『作業班名』のところです。 K列[作業工番］に、『朝礼休憩』『打合せ』『運搬』『会議』『作業指導』（その他もろもろたくさんあります。） などの在場内容を入れたいのです。 （『出張』『講習』『検診』は現場を離れた時間なので、I列［作業班名］は空白なわけです。） その場合、I列［作業班名］には、"在班名"が入るようにしたいのですが･･･。 そこは（K列）今のところ空白で、まとめて時間を入れている次第です。 ・・・やっかいですよね？？ いえいえ、もし何かいいお知恵があれば･･･のような感じですのでやっかいであれば、この内容は無視してください。 なんか、虫の良いお話ばかりしてすみません。 それでは（あきお）さま、お返事楽しみにしております。 （すずりん）
すみません、見直してみて、 "機"の文字をセットするとき @5 は、 F列[作業者コード] で参照する方が正しかったです（あきお） # あと、誤字 I列［作業班名］は、I列［作業班］でしたf(^^;
Const 列_作業者コード As Integer = 6 'F列
Const 列_作業者名 As Integer = 7 'G列
Const 列_在班名 As Integer = 8 'H列
Const 列_作業班 As Integer = 9 'I列
Const 列_可否 As Integer = 10 'J列
Const 列_作業工番 As Integer = 11 'K列
Const 列_正常 As Integer = 13 'M列
Const 列_異常 As Integer = 14 'N列
Const 列_合計 As Integer = 15 'O列
Public 行_入力 As Long
Public 列_入力 As Integer
Private Sub Worksheet_Change(ByVal Target As Range)
行_入力 = Target.Row
列_入力 = Target.Column
Select Case 列_入力
Case 列_作業者コード
Call F_作業者コード(Target)
Call K_作業工番(Target)
Case 列_作業工番
Call K_作業工番(Target)
Case 列_正常, 列_異常
Call 合計
Case Else
Exit Sub
End Select
'TEST Call コピペ
End Sub
Sub F_作業者コード(ByVal 入力セル As Range)
If 入力セル.Value <> "" Then
Cells(行_入力, 列_作業者名).FormulaR1C1 = _
"=IF(RC6="""","""",VLOOKUP(RC6,作業者表,2,0))" '@1
Cells(行_入力, 列_在班名).FormulaR1C1 = _
"=IF(RC6="""","""",VLOOKUP(RC6,作業者表,3,0))" '@2
Else '@3
Cells(行_入力, 列_作業者名).Value = ""
Cells(行_入力, 列_在班名).Value = ""
' Cells(行_入力, 列_作業班).Value = ""
Cells(行_入力, 列_可否).Value = ""
End If
End Sub
Sub K_作業工番(ByVal 入力セル As Range)
If Cells(行_入力, 列_作業者コード).Value = "" Then
Cells(行_入力, 列_作業班).Value = "" '@3
Cells(行_入力, 列_可否).Value = ""
Exit Sub
End If
作業班名の数式 = "=IF(RC6="""","""","
作業班名の数式 = 作業班名の数式 & "IF(ISNA(MATCH(RC11,状況表)),"
作業班名の数式 = 作業班名の数式 & "IF(ISNA(MATCH(RC11,在場内容))," 'New
作業班名の数式 = 作業班名の数式 & "IF(ISNA(MATCH(RC6,機の表)),"
作業班名の数式 = 作業班名の数式 & "IF(RC11="""",RC8," '@6
作業班名の数式 = 作業班名の数式 & "VLOOKUP(LEFT(RC11,2),作業班表,2,0))," '@7
作業班名の数式 = 作業班名の数式 & """機"")," '@5
作業班名の数式 = 作業班名の数式 & "RC8),""""))" '@4
Cells(行_入力, 列_作業班).FormulaR1C1 = 作業班名の数式
Call 可否
End Sub
Sub 可否()
可否の数式 = "=IF(RC8=RC9,""""," '@9
可否の数式 = 可否の数式 & """応"")" '@A
Cells(行_入力, 列_可否).FormulaR1C1 = 可否の数式
End Sub
Sub 合計()
If Cells(行_入力, 列_正常).Value = "" And _
Cells(行_入力, 列_異常).Value = "" Then
Cells(行_入力, 列_合計).Value = ""
Else
Cells(行_入力, 列_合計).FormulaR1C1 = "=SUM(RC13:RC14)"
End If
End Sub
Sub コピペ()
With Cells(行_入力, 列_作業班)
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
End Sub
たぶん、うまくいくハズ・・なので、キレイじゃないけど、どぞお試しください。
# うすうす感じていたのは、入力し終わった最終行の数式を
# 次に入力する行に単純にコピーして、、という方法が単純でいいかも。。
# って、表題の「関数のコピー」の方法ですね（笑）でも、やりかけちゃったので
# 上記、マクロを作ってみました。
また、何かありましたら、お気軽にどうぞ ^^
忘れてました、在場内容 という名前の表を用意する必要があります A 朝礼休憩 打合せ 運搬 会議 作業指導 など （Sheet6!$A$1:$A$?）みたいにです～（あきお）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.