セルに自動的入力させたい

質問

EXCEL2010を使用しています。

「表1」のような表があるのですが、Ｄ列が「子」である、Ｃ列のオレンジの部分が空欄なので
手動で入力しないといけません。
条件は装置名が同じである「親」と同じ日付が入ります。
なので結果は「表2」のようになります。

これを関数かＶＢＡで自動的に入力されるようにできないでしょうか？
もし関数できたとしてもＣ列には予め数字が入っている行があるので
空欄に飛び飛びで数式を設定するしかないでしょうか？

何か良い方法があればよろしくお願いします。

回答No.1

shintaro-2 · ベストアンサー

自動化は誰かにお願いするとして

step１　親子でソート
step2　装置名でソート
step３　日付列を選択
step４　空白セルにジャンプ（CTRL+G　→　セル選択→空白セル）
step4　日付の下の空白に　＝を入力してカーソル↑を押す。

　そうすると、空白セルがｃ８とすると＝ｃ7と入力されるので　CTRL+リターン
　これで空白セルは全部一つ上のセルと同じ値が入ります。

お礼

shintaro-2さん、ありがとうございました。
うぁ～？理屈はよくわかりませんがうまくいきました。
最初の順番に戻すため通し番号の列を作っておきましたが、この手順のまま並べ替えをしてしまうと
数値が狂ってしまうので日付の列をすべて「値」で貼り付けてから行いました。
中々覚えにくい手順ですが、ほかの時にも使えそうです。

回答No.4

tom04

こんにちは！

やはりVBAになってしまいますけど・・・一例です。
Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面に
↓のコードをコピー＆ペースト　→　Excel画面に戻ってマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

尚、Sheet2を作業用のSheetとして使用していますので、Sheet2はまっさらな状態にしておいてください。

Sub Sample1() 'この行から
Dim i As Long, c As Range, wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1")
Set wS2 = Worksheets("Sheet2")

wS1.Range("A1").AutoFilter field:=4, Criteria1:="親"
wS1.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy wS2.Range("A1")
wS1.AutoFilterMode = False
For i = 2 To wS1.Cells(Rows.Count, "A").End(xlUp).Row
If wS1.Cells(i, "C") = "" Then
Set c = wS2.Range("B:B").Find(what:=wS1.Cells(i, "B"), LookIn:=xlValues, lookat:=xlWhole)
With wS1.Cells(i, "C")
.Value = c.Offset(, 1)
.NumberFormatLocal = c.Offset(, 1).NumberFormatLocal
End With
End If
Next i
wS2.Cells.Clear
End Sub 'この行まで

こんな感じではどうでしょうか？m（_ _）m

お礼

tom04さん、ありがとうございました。
Sheet2に参照用？の表ができ、空欄セルに日付は入りましたが、

実行時エラー91
オブジェクト変数またはwithブロックが設定されていません。

のエラーメッセージが出ます。
ただ、正しい日付は入りましたし、他にもたくさん回答をいただきましたので今回はこのまま閉めさせて
いただきます。

皆さん、たくさんの回答ありがとうございました！
ベストアンサーは悩ましいところでしたが、最初に回答をくださったことと、自動的ではないけど簡単な方法だった
ということでshintaro-2さんとさせていただきます。
ありがとうございました。

回答No.3

MackyNo1

現在のリストにご希望のデータを表示する数式をまとめて入力するなら以下のような手順になります。
C列のデータ範囲を選択し、Ctrl+Gでジャンプダイアログを出し、「セル選択」から「空白セル」にチェックを入れ「OK」し、そのまま画面上部の数式バーを選択し、そこに以下の数式（例示のデータでは一番上の空白セルが５行目なのでB5セルを参照する）を入力し、Ctrlキーを押しながらEnterしてください。

=INDEX($C$3:$C$20,INDEX(MATCH(B5&"親",$B$3:$B$20&$D$3:$D$20,0),))

実際の運用では、最初にこの数式（条件以外はIF関数で空白表示）を入力しておき、D列に「親」や「子」と入力すると自動的にC列の数式に上書きしたデータを参照するようにした方が良いような気もします。

お礼

MackyNo1さん、ありがとうございました。
うまくいきました。
>実際の運用では、最初にこの数式（条件以外はIF関数で空白表示）を入力しておき、
この表は「子」が空欄状態で関連会社からもらうものなので予め数式を入れることはできません。

「空白セル」にだけ数式を入れる方法はこれからも使えそうなテクニックですのでぜひ覚えておきたいと思います。

回答No.2

kagakusuki

手入力で値を入力したり、入力されている値を消去したりする場合があるC列に対して、値を自動入力するには、ワークシート関数では無理で、VBAを使う事になります。（ワークシート関数では、C列の値を書き換えたり、消去したりする際に、関数を消してしまう恐れがあります）

　質問者様の添付画像の例では、B装置の納入日が入力されているセルが、装置名が「B装置」となっている行の中で最も下の行となっている事から考えますと、空欄に入れるべき日付は、必ずしも直上の行の日付であるとは限らない事になります。
　又、複数種類の装置が同時期に納入される可能性も無いとは言えませんから、空欄に入れるべき日付は、日付が入力されている行の中で最も近い行に入力されている日付であるとは限らないと思われます。
　そうなりますと、同じ装置名の中で日付が入力されている所から、日付を引っ張って来るしか方法は無く、そうしますと、もしも、同じ装置名で納入日が異なるものがあった場合には、空欄にどの日付を入れるべきかを判断する術は無いという事になります。
　従って、表中の全ての行に亘って、同じ装置名が入力されている全ての行において、納入日は全て同じ日となっている（同じ装置名で納入日が異なるものは存在しない）ものとして考えなければ、自動化は不可能という事になりますので、万が一、同じ装置名で納入日が異なるものがあった場合には、その装置名で日付が入力されている行の中で、最も上にある行に入力されている日付を、その装置名の中で納入日が空欄となっている箇所にに入力すべき日付として、一律で採用する事に致しますが、それで宜しいでしょうか？

　もし、それで宜しければ、以下の様なVBAのマクロとなります。(表の行数が多い場合においても動作速度を速くするために、１行ごとの繰り返し処理は行わずに、Y列とZ列に一時的にワークシート関数を入力してから、Z列の値をC列にコピーする方法としております)

Sub Macro()

Dim LR As Long
LR = Application.WorksheetFunction.Match("*?", Range("B:B"), -1)
Range("Y3:Y" & LR).FormulaR1C1 = "=IF(AND(RC2<>"""",ISNUMBER(1/RC3/DAY(RC3)),LEFT(CELL(""format"",RC3),1)=""D""),RC2,"""")"
Range("Z3:Z" & LR).FormulaR1C1 = "=IF(RC2="""","""",IF(COUNTIF(C[-1],RC2),IF(RC3="""",INDEX(C3,MATCH(RC2,C[-1],0)),RC3),""該当無し""))"
Range("C3:C" & LR).Value = Range("Z3:Z" & LR).Value
Range("Y3:Z" & LR).ClearContents

End Sub

　尚、もしも、C列において空欄となっているセルの書式が、日付を表示する様になってない場合には、C列の日付を表示させるセルの書式の表示形式を、手動で[日付]にして下さい。
　何故なら、C列における日付が入力されているセルの書式の表示形式は、必ずしも

m"月"d"日"

であるとは限りませんから、マクロの中で表示形式を指定しておく事は出来ませんし、さりとて、納入日が入力されているセルの書式を、そのまま他のセルにもコピーしてしまったのでは、セルの地の色やフォントの設定、罫線の設定等までコピーしてしまう事になりますので、例えば１行毎にセルの地の色を交互に変えて見やすくしていた場合や、罫線の一部を太枠にしていた場合などに不都合が生じる恐れがありますから、書式の方は手動で設定して頂いた方が話が簡単になるためです。

お礼

kagakusukiさん、ありがとうございました。
うまくできました。（日付の書式設定は必要でした）
>一律で採用する事に致しますが、それで宜しいでしょうか？
はい、かまいません。同じ装置名で納入日が異なるものはありません。

ただこの表はサンプルとして簡単にしたものなので、実際の表で使う場合はアレンジが必要ですね。
頑張ってみます。