簡単な関数でさまざまなサイズの他のシートから配列を評価する方法はありますか?

Nov 25 2020

1枚のデータの状況の変化に基づいてカスタム関数と配列を作成する関数があります。メインシートで評価しようとすると#valueが返されますが、シートで評価すると配列の元になり、正しい出力が返されます。3D配列のサイズが変更されている場合、これを回避する方法はありますか?

複雑なsumproduct関数を使用しているため、配列が返されます。sumproduct関数は、evaluate式内にネストされた唯一の関数です。

私のEvaluateVBAは現在次のようになっています。

Function Makeformula(Ref As String)
Application.Volatile
Makeformula = Evaluate(Ref)
End Function

こんにちは、ベン、

概要:以下の(1a)と(2a)は、(1)と(2)の出力をコピーして新しいセルに貼り付け、テキストを関数として実行すると(等号を追加して)、シート1で正しい結果を取得します。 。(1)提示されたいずれかのソリューションで機能し、(2)そのままでは機能しません。(2)は、sumproductがsumproductの2つの値を取得するときの配列です。

私はあなたが与えた両方の答えを無駄に試しました。以下にいくつかのコードを添付して表示し、Long式を太字にして見やすくします。

以前と同様に、私のsumproductが最終的に(1)に見られるテキスト値を生成するが、(2)は生成しない場合に、回答は機能します。唯一の違いは、(1)は多次元配列ではないのに対し、(2)は多次元配列であるということです。また、(1)と(2)を個別にコピーして、テキスト値として空白のセルに貼り付け、先頭に等号を付けると、正しい値が取得されることにも注意してください。ただし、(2)は、Sumproduct数式の生成後に新しいセルに挿入された純粋なテキストとして評価できる場合でも、指定したMakeformula関数では正しく評価されません。(2)#Valueにつながります!そのままエラー。

以下は、MakeFormula関数を使用してセルに入力する方法です。関数の2番目の引数に「MonthEndInput Frontend」を使用しました。これは、(1a)と(2a)に示すように、sumproductが独立して実行された場合に最終的に参照されるものだからです。最初のソリューションの2番目の引数を「MonthEndBackend」に変更しても機能しません。

(1)= MakeFormula2(( " SUMPRODUCT(("&VLOOKUP(VLOOKUP( 'Inc Stmnt''20 vs''19'!$C11,'Month End Input Backend'!$B$4:$D$78,3,FALSE),'Month End Input Backend'!$H$4:$K$27,3,FALSE)&"="&CHAR(34)&U$7&CHAR(34)& ")("&LEFT(myvlookup($C11,'Month End Input Backend'!$B$4:$E$78,2,INDIRECT(VLOOKUP(VLOOKUP('Inc Stmnt ''20 vs ''19'!$C11、「月末入力バックエンド」!$B$4:$D$78,3、FALSE)、「月末入力バックエンド」!$H$4:$K$27,4、FALSE)))、LEN(myvlookup($C11,'Month End Input Backend'!$B$4:$E$78,2,INDIRECT(VLOOKUP(VLOOKUP('Inc Stmnt ''20 vs ''19'!$C11、「月末入力バックエンド」!$B$4:$D$78,3、FALSE)、「月末入力バックエンド」!$H$4:$K$27,4、FALSE))))-1)& ")("&VLOOKUP(VLOOKUP( 'Inc Stmnt''20 vs''19'!$C11,'Month End Input Backend'!$B$4:$D$78,3,FALSE),'Month End Input Backend'!$H$4:$K $ 27,2、FALSE)& "))")、 "月末入力フロントエンド")

^これは機能します

(2)= MakeFormula2(( " = SUMPRODUCT(("&VLOOKUP(VLOOKUP( 'Inc Stmnt''20 vs''19'!$ C35、 'Month End Input Backend'!$B$4:$D$78,3、FALSE)、「月末入力バックエンド」!$H$4:$K$27,3、FALSE)& "="&CHAR(34)&U $ 7&CHAR(34)& ")("&LEFT(myvlookup($ C35、 'Month End Input Backend'!$B$4:$E$78,2、INDIRECT(VLOOKUP(VLOOKUP( 'Inc Stmnt''20 vs''19'!$C35,'Month End Input Backend'!$B$4:$D$78,3,FALSE),'Month End Input Backend'!$H$4:$K$27,4,FALSE))),LEN(myvlookup($C35、「月末入力バックエンド」!$B$4:$E$78,2、INDIRECT(VLOOKUP(VLOOKUP( 'Inc Stmnt''20 vs''19'!$C35,'Month End Input Backend'!$B$4:$D$78,3,FALSE),'Month End Input Backend'!$H$4:$K $ 27,4、FALSE))))-1)& ")("&VLOOKUP(VLOOKUP( 'Inc Stmnt''20 vs''19'!$ C35、 'Month End Input Backend'!$B$4:$D$78,3、FALSE)、「月末入力バックエンド」!$H$4:$K$27,2、FALSE)& "))")、 "月末入力フロントエンド")

^^これは違います。

以下は、テキスト値としてコピーして新しいセルに貼り付けた場合に、両方の場合に値を正しくプルできる数式の合計積部分の結果です。


(1a) SUMPRODUCT(('Month End Input Frontend'!$C$12:$Q$12="Jan 2020")*(('Month End Input Frontend'!B13:B89="                                Sales of Product Income"))*('Month End Input Frontend'!$C$13:$Q$89))

(2a) SUMPRODUCT(('Month End Input Frontend'!$C$12:$Q$12="Jan 2020")*(('Month End Input Frontend'!B13:B89="                        Billing Fee Expense")+('Month End Input Frontend'!B13:B89="                                Medical Billing and Collection"))*('Month End Input Frontend'!$C$13:$Q$89))

回答

BigBen Nov 25 2020 at 22:34

Evaluate(またはApplication.Evaluate)は、アクティブシートのコンテキストで動作します。を使用したいWorksheet.EvaluateのでWorksheet、問題の参照を関数に渡す必要があります。オプションは次のとおりです。

  • シート名をString:として渡します。
Public Function MakeFormula(ByVal Ref As String, ByVal SheetName As String) As Variant
    Application.Volatile
    Makeformula = ThisWorkbook.Worksheets(SheetName).Evaluate(Ref)
End Function
  • Range参照を渡し、を使用しますRange.Parent。あなたの式は次のようになります=MakeFormula("yourlongformula", 'Sheet 2'!A1)
Public Function MakeFormula(ByVal Ref As String, ByVal Cell As Range) As Variant
    Application.Volatile
    Makeformula = Cell.Parent.Evaluate(Ref)
End Function