☰

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

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

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

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

  • Column AからRow 2はRow 15各プロジェクトに割り当てられたリソース（人員）を表します

  • Column B、Column C....は各プロジェクトに月ごとに割り当てられたリソースの割合を表します

  • 各リソースは、column A1 か月あたり 1 つまたは 2 つのプロジェクトで作業できるため、2 つのセルに結合します。

  • 1か月に1つのプロジェクトに取り組んでいるリソースの場合、両方のセルが結合されます。2つのプロジェクトに取り組んでいるリソースの場合、プロジェクトを表す2つの行があります。

  • 各プロジェクトには、そのプロジェクトに割り当てられたリソースの割合を表す数字があります。

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

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

下部のレポートには、プロジェクトごとに割り当てられたリソースの数を表す概要セクションがあります。現在、これらの数は手動で追加されています。これらの概要の数式を生成するのに助けが必要です。私が達成しようとしていることは次のとおりです。

  1. セルから文字列を読み取る (A20 - A24)
  2. 月列（B2 - B15）を検索して、プロジェクト文字列で始まるセルを取得します。
  3. 上記のセルから数字をフィルタリングします（手順2から）
  4. 数字を合計して割ると100リソース数が得られます

これまでのところ、渡された行から数値を読み取る次の数式を生成できます。

=TEXTJOIN("",TRUE,IFERROR((MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)*1),""))

テキストを含む50セルから数値を取得します。B2"Project A 50%"

答え1

O365 をお持ちの場合は、次の機能を使用できますFILTER。

  • A20:Annの内容でリストをフィルタリングする

  • 各文字列の最後のスペースで区切られた値（パーセンテージ）のみを返します。

  • 結果を合計する

  • IFERRORプロジェクトが存在しない場合に使用します

      =IFERROR(SUM(--((TRIM(RIGHT(SUBSTITUTE(FILTER(B$2:B$15,ISNUMBER(SEARCH($A20,B$2:B$15)))," ",REPT(" ",99)),99))))),"")

または(ゼロを返すか、""存在しないエントリを返すかによって異なります)

    =IFERROR(SUM(--((TRIM(RIGHT(SUBSTITUTE(FILTER(B$2:B$15,ISNUMBER(SEARCH($A20,B$2:B$15)))," ",REPT(" ",99)),99))))),"")

編集：コメントで、OP は、プロジェクトは似たような名前が付けられるが、先頭に文字が付くため、別々に扱う必要があると述べています。これには、異なるフィルター基準が必要です。

=IFERROR(SUM(--((TRIM(RIGHT(SUBSTITUTE(FILTER(B$2:B$15,LEFT(B$2:B$15,LEN($A20))=$A20)," ",REPT(" ",99)),99))))),"")

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

答え2

2 行目から 15 行目までのデータの表現は、1 つのセルに 2 つの情報を結合しているため、乱雑になっています。これにより、基本的にすべての数値セル関数が使用できなくなります。プロジェクト ID とリソース量を 2 つの列に分割します。

列 B にプロジェクト識別子、列 C にリソースがある場合は、セル B20 を "=sumif(b$2:b$15;"A";c$2:c$15)" に設定するだけです。プロジェクト B の場合は、その数式を "B" に変更します。

数式を変更せずに入力を高速化するには、ABCD .... を含む非表示の列を作成し、上記の数式の固定文字列が非表示の値 ABCD .... を指すようにします。

答え3

この答えはあまり気に入らないかもしれませんが、心を開いて

おそらく、このテーブルを分析する必要があるのは今回が最後ではないでしょう。今再構築しておけば、後で作業が楽になります。

  1. 結合したセルは、その時は良いアイデアのように思えますが、後で悲惨な結果を招くだけです。
  2. 変数を「プロジェクトA 50%」という1つのセルに結合すると、それらの変数を分離して理解する必要があるため、分析が難しくなります。
  3. クロス集計されたデータを入力すると、少しは簡単になるかもしれませんが、コストがかかります。プロジェクト A に割り当てられた四半期ごとのリソースの前年比を尋ねるレポートを作成する必要がある場合はどうでしょうか。また、翌年になったらどうなるでしょうか。新しいテーブルを作成しますか。その後、どのように比較しますか。

ここで鈍感になりたいわけではありませんが、Excel やその他のシステムで 25 年間データに取り組んできた経験から、後で何が必要になるかについて考えるよう人々に勧めたいと思います。

次のようなものが最適です:

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

これは、Ctrl + T またはリボンの [ホーム] タブの [テーブルとして書式設定] を使用してテーブルとして書式設定されていることに注意してください。

この構造にはいくつかの利点があります。

  1. 新しい行に入力するだけで、データの入力が非常に簡単になりました
  2. ピボットテーブルを使用すると、あらゆる次元（人、月、プロジェクト）にわたって分析を行うことができます。単純な質問に複雑な数式を使用する必要はありません。
  3. チャート作成は今やとても簡単
  4. 各行に列として新しい情報を追加できます。別のテーブルから XLOOKUP として、その人の直属の上司やチーム名を行に追加したり、その人のその月のパフォーマンスや休暇に関するメモを追加したりすることもできます。
  5. これを別のスプレッドシートにコピーしたり、csvとして誰かに送信したり、分析のためにデータベースやデータフレームのテーブルにロードしたりする必要がある場合、それを行う前に結合を解除して空のセルを埋めるのに時間を費やす必要はありません。

編集：

この修正されたデータ構造を使用して質問に答えるには、ピボット テーブルを作成するだけです。

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

関連情報