有沒有辦法縮短excel公式？

我正在尋找一種方法來縮短我的公式。我是 excel 的新手，不知道如何縮短公式，我做了幾次嘗試，但總是出錯。我敢肯定，對于更有經驗的人來說，這將是一個微不足道的問題。

我希望有人能幫助我，我很感激任何回應。

=SUM(IF((ISNUMBER(SEARCH("Bench",$P$9:$U$11))) (ISNUMBER(SEARCH("Press",$P$9:$U$11)));V9:V11*$W$9:$W$11,0)) SUM(IF((ISNUMBER(SEARCH("Bench",$P$19:$U$21))) (ISNUMBER(SEARCH("Press ";$P$19:$U$21)));V19:V21*$W$19:$W$21;0)) SUM(IF((ISNUMBER(SEARCH("Bench",$P$29:$U$31))) (ISNUMBER(SEARCH("Press",$P$29:$U$31)));V29:V31*$W$29:$W$31,0)) SUM(IF( (ISNUMBER(SEARCH("Bench",$P$39:$U$41))) (ISNUMBER(SEARCH("Press",$P$39:$U$41)));V39:V41*$W$39:$ W$41;0))

uj5u.com熱心網友回復：

使用 SUMPRODUCT：

=SUMPRODUCT(
    (ISNUMBER(MATCH(MODE(ROW($P$9:$U$41),10);{9;0;1};0)))*
    ((ISNUMBER(SEARCH("Bench";$P$9:$U$41))) 
    (ISNUMBER(SEARCH("Press";$P$9:$U$41))));
    $V$9:$V$41;
    $W$9:$W$41)

這允許輸入整個范圍，它只會查看以 9,0 或 1 結尾的那些行。

uj5u.com熱心網友回復：

我不認為這里有任何“簡單”的儲蓄。我所做的是以一種幫助我更好地可視化邏輯的方式“分解”一個極其復雜的公式。在這種情況下：

=SUM( IF( 
    (ISNUMBER( SEARCH( "Bench" , $P$9:$U$11 ) ) )  
    (ISNUMBER( SEARCH( "Press" , $P$9:$U$11 ) ) ) ;
    V9:V11 * $W$9:$W$11 , 
    0 ) )

 SUM( IF(
    (ISNUMBER( SEARCH( "Bench" , $P$19:$U$21 ) ) )  
    (ISNUMBER( SEARCH( "Press" ; $P$19:$U$21 ) ) ) ;
    V19:V21 * $W$19:$W$21 ;
    0 ) )

 SUM( IF(
    (ISNUMBER( SEARCH( "Bench" , $P$29:$U$31 ) ) )  
    (ISNUMBER( SEARCH( "Press" , $P$29:$U$31 ) ) ) ;
    V29:V31 * $W$29:$W$31 ,
    0 ) )

 SUM( IF(
    (ISNUMBER( SEARCH( "Bench" , $P$39:$U$41 ) ) )  
    (ISNUMBER( SEARCH( "Press" , $P$39:$U$41 ) ) ) ;
    V39:V41 * $W$39:$W$41;
    0 ) )

除了以一種讓我看到模式的方式格式化它之外，我沒有改變任何東西……而且我想研究模式，而不是細節。

我能看到的是這個公式在 6r x 3c 的 4 個范圍內搜索“Bench”或“Press”的任何實體。如果至少有一個實體，則將 2 個 3 元陣列相乘并對結果求和，否則回傳該范圍的零。

你有 O365，所以你可以在你的名稱管理器中創建一個命名的 LAMBDA() 函式。例如，您可以在名稱管理器中創建自定義 Excel 函式，例如：

=snorlaxFunction( "Bench", "Press", $P$39:$U$41 )

這會將 4 個術語中的每一個簡化為：

 SUM( IF( snorlaxFunction( "Bench", "Press", $P$39:$U$41 ), 
    V39:V41 * $W$39:$W$41; 0 ) )

您可以更進一步，將整個 SUM(IF()) 封裝為一個函式：

=snorlaxFunction( "Bench", "Press", $P$39:$U$41, V39:V41, $W$39:$W$41 )

在這種情況下，您的整個公式變為：

= snorlaxFunction( "Bench", "Press", $P$9:$U$11 , V9:V11 , $W$9:$W$11  )  
  snorlaxFunction( "Bench", "Press", $P$19:$U$21, V19:V21, $W$19:$W$21 )  
  snorlaxFunction( "Bench", "Press", $P$29:$U$31, V29:V31, $W$29:$W$31 )  
  snorlaxFunction( "Bench", "Press", $P$39:$U$41, V39:V41, $W$39:$W$41 )

老實說，如果是我……我看不出有什么簡單的方法可以縮短邏輯，而且我沒有重復的術語適合 LET() 函式。所以我會按原樣保留公式，但alt enters會像上面那樣以大綱格式顯示公式。除非我要在電子表格中多次重復使用該公式，否則我不會為此創建命名的 LAMBDA()。

筆記

comma我注意到的一件事是您的符號在分隔文本和分隔文本之間漂移很多semicolon，正如您在上面看到的那樣。我會堅持您配置的區域設定并保持 100% 一致，僅使用逗號或分號。

uj5u.com熱心網友回復：

使用LET和堆疊范圍可以簡化您的公式：

=LET(pu,VSTACK($P$9:$U$11,$P$19:$U$21,$P$29:$U$31,$P$39:$U$41),
     v, VSTACK(V9:V11,V19:V21,V29:V31,V39:V41),
     w, VSTACK($W$9:$W$11,$W$19:$W$21,$W$29:$W$31,$W$39:$W$41),

SUM(IF(
       (ISNUMBER(SEARCH("Bench",pu))) 
       (ISNUMBER(SEARCH("Press",pu))),
       v*w,
       0)))

正如 Scott Craner 所建議的那樣，您可以從涵蓋所有列/行的范圍開始，然后過濾出要使用的范圍。這樣，如果資料可能增長，您只需調整第一個LET條件即可調整公式：

=LET(rng,P9:W41,
     pw,FILTER(rng,ISNUMBER(XMATCH(MOD(ROW(rng),10),{9;0;1}))),
     pu,TAKE(pw,,COLUMNS(pw)-2),
     v,  CHOOSECOLS(pw,COLUMNS(pw)-1),
     w, TAKE(pw,,-1),
SUM(IF(
       (ISNUMBER(SEARCH("bench",pu)))  
       (ISNUMBER(SEARCH("press",pu))),
       v*w,
       0)))

標籤：擅长excel公式办公室365

上一篇：計算篩選作業表中的可見行數

下一篇：排序列-ExcelJavascript