☰

開始文字列に基づいて列をフィルタリングし、フィルタリングされたセルから数値を読み取り、算術演算を実行して Excel で 2 行のサマリーを生成します。

tag-icon • tag-icon microsoft-excel macos worksheet-function microsoft-excel-365
開始文字列に基づいて列をフィルタリングし、フィルタリングされたセルから数値を読み取り、算術演算を実行して Excel で 2 行のサマリーを生成します。

プロジェクトとリソース割り当てレポートを作成しています。レポートの右側のセクションに概要を表示するための数式を生成する方法についてサポートが必要です。

私のレポートの仕様は次のとおりです。

  • 列Aの2行目から15行目は、各プロジェクトに割り当てられたリソース（人員）を表します。
  • 列B、列C....は、各プロジェクトに月ごとに割り当てられたリソースの割合を表します。
  • 列Aの各リソースは、各リソースが月に1つまたは2つのプロジェクトで作業できるため、2つのセルに結合されます。
  • 1か月に1つのプロジェクトに取り組んでいるリソースの場合、両方のセルが結合されます。2つのプロジェクトに取り組んでいるリソースの場合、プロジェクトを表す2つの行があります。
  • 各プロジェクトには、そのプロジェクトに割り当てられたリソースの割合を表す数字があります。

私のレポートのスクリーンショットは次のとおりです。

プロジェクト リソース割り当てシートのスクリーンショット

ここでは、各人が各プロジェクトに費やす合計週数を示しています。計算を簡素化するために、各月はちょうど4週間に相当すると仮定します。

  • 2月にAさんがプロジェクトBに100%従事している場合、集計で考慮される数字はプロジェクトBの4になります。
  • 同様に、Aさんが1月に2つのプロジェクトに50-50%取り組んでいる場合、合計に考慮する数字は各プロジェクトにつき2-2週間になります。

一般的に、要約時に考慮される数値は数学的に次のように表すことができます。

4 *[Percentage from column] / 100

私が達成しようとしていることは次のとおりです:

  1. セルから文字列を読み取る (F1 - J1)
  2. 2行（B2、C2...）と（B3、C3...）の人物列を検索して、プロジェクト文字列で始まるセルを取得します。
  3. 上記のセルから数字をフィルタリングします（手順 2 から）。数字を合計し、100 で割り、4 を掛けます（週数を取得するため）。

以下は、同様の質問に対する回答を参考にして作成したものです。ここ:

=IFERROR((IFERROR(SUM(--((TRIM(RIGHT(SUBSTITUTE(FILTER($B2:$C2,ISNUMBER(SEARCH(J$1,$B2:$C2)))," ",REPT(" ",99)),99))))), 0) + IFERROR(SUM(--((TRIM(RIGHT(SUBSTITUTE(FILTER($B3:$C3,ISNUMBER(SEARCH(J$1,$B3:$C3)))," ",REPT(" ",99)),99))))), 0))*4, "")

プロジェクトBの場合は6になります。これを簡略化する方法はありますか？ここでは冗長な操作をたくさん行っていると思います。また、プロジェクトが見つからない場合は0を返します。代わりに空の文字列を使用することをお勧めします。

この計算を実行するより良い方法がある場合は、遠慮なく提案してください。

注記：

  1. 2 つのセルが結合された列の場合、その人物に対して計算は 1 回だけ考慮される必要があります。
  2. 私の要約の計算は2行のデータに基づいています
  3. ここでは、人物名 (「Person A」、「Person B」など) とプロジェクト名 (「Project A」、「Project B」など) の文字列をプレースホルダー テキストとして使用しています。実際のレポートでは、これらはランダムなアルファベットで実際の値になります。

PS: これは、列内の 2 つのセルを結合できる単一の列でサマリーを生成していた前回の質問と似ています。Excel - 開始文字列に基づいて行をフィルタリングし、フィルタリングされた行から数値を読み取り、算術演算を実行して列の概要を生成します。ただし、ここでは集計を取得するために 2 つの行で計算を実行しています。また、結合された行のセルは計算で 1 回だけ考慮する必要があります。

答え1

おそらく、この短い式の提案を試してみてください。

1] ではF2、式は横方向と縦方向にコピーされます。

=SUMPRODUCT(4*TEXT(SUBSTITUTE($B2:$C3,F$1,""),"0%;;;\0"))

2] 前回の質問も同じ式で解くことができます。A19:C23 に記入しました。

ではB19、数式が横方向と縦方向にコピーされます:

=SUMPRODUCT(0+TEXT(SUBSTITUTE(B$2:B$15,$A19,""),"0%;;;\0"))

そして、

編集

0を削除するには

1] セル書式の使用:

すべての数式セルの書式設定 >> 「会計」 >> 「記号」で、「なし」を選択 >> 「小数点位置」: 「1」をクリック

2] IFERROR(1(/1.......),"") を使用して F2 式に追加すると、次のようになります >>

=IFERROR(1/(1/SUMPRODUCT(4*TEXT(SUBSTITUTE($B2:$C3,F$1,""),"0%;;;\0"))),"")

ここに画像の説明を入力してください

答え2

処理する範囲が多次元であるため、ソリューションはより複雑になります。(前回の質問では、単一次元のみでした)

TEXTJOIN処理する方法の 1 つは、と を使用して 1 次元配列に変換することです。関数の結果の文字列は文字数を超えないFILTERXMLという制限があります。これが問題になる場合は、VBA または Power Query ソリューションの方が適している可能性があります。TEXTJOIN32,767

また、FILTERXMLMac バージョンや Excel Online では使用できません。

これらの制限が適用されない場合は、

F2: =IFERROR(4*SUM(FILTERXML("<t><s>" & SUBSTITUTE(FILTERXML("<t><s>" &TEXTJOIN("</s><s>",TRUE,$B2:$C3) & "</s></t>","//s[starts-with(.,'" & F$1 & "')]")," ","</s><s>") & "</s></t>","//s[last()]")),"")

  • 右と下に記入

  • まず、各セルに個別のノードを作成するためにXML（ を使用）を作成します。TEXTJOIN

    • 私たちの引数は、行 1 のプロジェクト名xPathを持つノードのみを返します。start-with
    • 次に、スペースで区切られたノードに基づいて、最後のノードを返す を使用してSUBSTITUTE別のものを作成します。XMLxPath
    • 数学演算を実行します。

ここに画像の説明を入力してください

編集：

もし、するならない関数がある場合はFILTERXML、この式を試してください(O365 Mac または Windows と Excel Online)これは、処理のために 2D 配列を 1D 配列に変換するために別の方法を使用します。

=IFERROR(SUM(4*TRIM(RIGHT(
   SUBSTITUTE(LET(
        seq,IF(SEQUENCE(99)=1,1,(SEQUENCE(99)-1)*99),
        x,TRIM(MID(TEXTJOIN(REPT(" ",99),TRUE,$B2:$C3),seq,99)),
        y, FILTER(x,LEFT(x,LEN(F$1))=F$1),y),
   " ",REPT(" ",99)),99))),"")

この数式には、依然として 32,767 文字の制限があります。この制限に達するかどうかは、主にプロジェクト名に含まれるスペースの数によって決まります。計算式は、おおよそ次のようになります。

  • a = セル内の平均文字数

  • b = セル内のスペースの数

  • c = セルの数

     (a+b*99) * c

たとえば、15文字、5スペース、24セルがある場合は、文字数は約になります12,000。

関連情報