8
8
特定の値を別のセルに貼り付けるエクセル式の書き方は？
そこで基本的には、条件をチェックする数式を書き、その条件が満たされた場合、特定の行のテキストを別のセルに貼り付けたいと考えています。ここで注意しなければならないのは、値を貼り付けたいセルに数式が存在しないようにすることです。ですから、例えば、B5に値を貼り付けたい場合、数式はセルB5には存在してほしくありません…。
そこで基本的には、条件をチェックする数式を書き、その条件が満たされた場合、特定の行のテキストを別のセルに貼り付けたいと考えています。ここで注意しなければならないのは、値を貼り付けたいセルに数式が存在しないようにすることです。ですから、例えば、B5に値を貼り付けたい場合、数式はセルB5には存在してほしくありません…。
以下のアプローチは、VBAで定義されたワークシート関数が別のセルの値を設定できるようにするために、 こちら および こちら で説明されている回避策を利用しています。
カスタム関数は、ターゲット・セルのアドレスとそのセルが設定される値をグローバル変数に格納します。そして、ワークシートが再計算したときにトリガされるマクロがグローバル変数を読み込んで、ターゲット・セルを指定した値に設定します。
=SetCellValue(target_cell, value)
ここで、target_cell はワークシート内のセル (例: “A1” ) への文字列参照、またはそのような参照に対して評価される式です。これには、B14の値が “A1 "である=B14のような式も含まれます。この関数は、任意の有効な式で使用することができます。
SetCellValue は、値がターゲット・セルに正常に書き込まれた場合は 1 を返し、そうでない場合は 0 を返します。ターゲットセルの以前の内容はすべて上書きされます。
3つのコードが必要です。
SetCellValue自体を定義するコード IsCellAddressが有効なセルアドレスであることを確認するためのユーティリティ関数 target_cell。このコードは、ワークブックに挿入された標準モジュールに貼り付ける必要があります。このモジュールは、Visual Basic エディタのメニューから挿入することができます。
Option Explicit
Public triggerIt As Boolean
Public theTarget As String
Public theValue As Variant
Function SetCellValue(aCellAddress As String, aValue As Variant) As Long
If (IsCellAddress(aCellAddress)) And _
(Replace(Application.Caller.Address, "$", "") <> _
Replace(UCase(aCellAddress), "$", "")) Then
triggerIt = True
theTarget = aCellAddress
theValue = aValue
SetCellValue = 1
Else
triggerIt = False
SetCellValue = 0
End If
End Function
このコードは、Visual Basic を使用するワークシート固有のコードに含める必要があります。最も簡単な方法は、Developer ビューのワークシートのタブを右クリックして SetCellValue を選択し、表示されるエディタペインにコードを貼り付けることです。
Private Sub Worksheet_Calculate()
If Not triggerIt Then
Exit Sub
End If
triggerIt = False
On Error GoTo CleanUp
Application.EnableEvents = False
Range(theTarget).Value = theValue
CleanUp:
Application.EnableEvents = True
Application.Calculate
End Sub
このコードは、Homeのコードと同じモジュールに貼り付けることができます。
Function IsCellAddress(aValue As Variant) As Boolean
IsCellAddress = False
Dim rng As Range ' Input is valid cell reference if it can be
On Error GoTo GetOut ' assigned to range variable
Set rng = Range(aValue)
On Error GoTo 0
Dim colonPos As Long 'convert single cell "range" address to
colonPos = InStr(aValue, ":") 'single cell reference ("A1:A1" -> "A1")
If (colonPos <> 0) Then
If (Left(aValue, colonPos - 1) = _
Right(aValue, Len(aValue) - colonPos)) Then
aValue = Left(aValue, colonPos - 1)
End If
End If
If (rng.Rows.Count = 1) And _
(rng.Columns.Count = 1) And _
(InStr(aValue, "!") = 0) And _
(InStr(aValue, ":") = 0) Then
IsCellAddress = True
End If 'must be single cell address in this worksheet
Exit Function
GetOut:
End Function
セルB5に値「緑」が含まれている場合に、セルC5に「テキストA'」を表示させたいとします。
**数式アプローチを使用できますが、数式は他のセルの値を変更できないため、数式はセルC5に入力する必要があります。
=IF(ISNUMBER(FIND("green",B5)),"Text A","")
セル C5 は、B5 に “green” という単語が含まれている場合にのみ “Text A” と表示されるようになりました。
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B5")) Is Nothing Then
If InStr(1, Target, "green", vbTextCompare) Then
Target.Offset(0, 1) = "Text A"
End If
End If
End Sub
セルC5は、B5に「緑」という単語が含まれている場合にのみ「テキストA」を表示するようになりました。特定の状況で助けを得るためには、要件を定義する必要があります。
C5に数式を持たせたくない場合は、VBAのアプローチを使うこともできます。セルB5が変更されるたびに実行されるワークシート変更イベントを、値を手動で編集するか、何かを貼り付けて実行することができます。
0x1&
このようなマクロの例は、
0x1&
出力の条件や配置はもちろん一例であり、要件に合わせて調整する必要があります。
式とマクロアプローチの違いは、
注：上記はあくまでも一例です。数値を評価するのかテキストを評価するのか、評価は大文字と小文字を区別するのか、評価ルールは何か、結果をどこに配置するのか、などの要件を定義する必要があります。
条件が満たされているかどうかをチェックするには、「別のセルの特定の行のテキスト」を更新するために、いくつかのセルにIF式を書きます。結果セルには、以下のように、その値を生成した数式ではなく、数式の値のみが格納されます。
作業セル（例：J5） =IF(A1="yes","Specific line of text","")
結果セル（例：B5） =J5
条件が満たされている場合（A1=“yes"）、B5には "特定の行のテキスト "が含まれます。それ以外の場合は空白のままです。
注意: 変数値を持つセルには、通常、値を更新し続けるための何らかの式が含まれています。
値を生成している本当の数式を誰にも知られたくない場合、数式は他のセルに移動して値を生成し、その値は結果セルにコピーされます。
値を生成した数式を非表示にしたり、B5が参照しているセルを非表示にする（値の更新が必要かどうかをチェックし続ける）には、以下を参照してください。 https://support.office.com/en-us/article/display-or-hide-formulas-f7f5ab4e-bf24-4efc-8fc9-0c1b77a5356f ](https://support.office.com/en-us/article/display-or-hide-formulas-f7f5ab4e-bf24-4efc-8fc9-0c1b77a5356f)
同じ効果を得ることができる他の関数には、CHOOSE, HLOOKUP, LOOKUP, VLOOKUPがあります。
=value(cell)
同じ問題を抱えていましたが、それは動作します。