☰

根據起始字串過濾列，從過濾的單元格中讀取數字並執行算術運算以產生 Excel 中兩行的摘要

tag-icon • tag-icon microsoft-excel macos worksheet-function microsoft-excel-365
根據起始字串過濾列，從過濾的單元格中讀取數字並執行算術運算以產生 Excel 中兩行的摘要

我有一份專案和資源分配報告。我需要幫助生成公式以在報告的右側部分顯示摘要。

以下是我的報告的規格：

  • 第 2 行到第 15 行的 A 列代表每個項目分配的資源（人員）
  • B 列、C 列...代表每月每個項目分配的資源百分比
  • A 列的每個資源合併為 2 個儲存格，因為每個資源每月可以在 1 或 2 個專案中工作
  • 一個月內處理單一項目的資源將合併兩個單元。處理 2 個項目的資源將有 2 行代表該項目
  • 對於每個項目，都有代表分配給該項目的資源百分比的數字

這是我的報告的螢幕截圖：

專案資源分配表截圖

在這裡，在摘要中，我代表每個人在每個項目上花費的總週數。為了簡化計算，我假設每個月剛好相當於 4 週。所以，

  • 如果 A 人 2 月在專案 B 中 100% 工作。那麼對於專案 B，要考慮進行總和求和的數量應為 4
  • 同樣，如果 A 1 月在 2 個專案上投入了 50-50% 的工作量。那麼每個項目要考慮求和的時間應該是 2-2 週

一般來說，總結中要考慮的數字可以在數學上表示為：

4 *[Percentage from column] / 100

這就是我想要實現的目標：

  1. 從單元格讀取字串 (F1 - J1)
  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. 我的摘要的計算是基於兩行數據
  3. 在這裡，我使用人名（“人 A”、“人 B”等）和項目名稱（“項目 A”、“項目 B”等）字串作為佔位符文字。在實際報告中，這些將變為具有隨機字母的實際值。

PS：這與我之前的問題類似，我在單列上產生摘要，其中可以合併列中的兩個儲存格：Excel - 根據起始字串過濾行，從過濾行讀取數字並執行算術運算以產生列摘要。但是，在這裡我對兩行執行計算以獲得摘要。並且合併的行單元格只需考慮一次即可進行計算。

答案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和來完成FILTERXML。存在一個限制，即TEXTJOIN函數產生的字串不能超過32,767字元。如果這是一個問題，那麼 VBA 或 Power Query 解決方案可能會更好。

此外，FILTERXML在 Mac 版本或 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字元。

相關內容