☰

tag-icon • tag-icon microsoft-excel worksheet-function

我正在處理與此類似的 Excel 文件（出於隱私原因進行了更改/簡化）：

在此輸入影像描述

命令 ＃ 任務
作為此訂單的一部分                                                                      洗車了嗎？
1 審查 是的
1 更換輪胎 是的
1 洗車 是的
2 審查 不
2 更換輪胎 不
2 換油 不

不同的任務可以分享相同的命令 ＃。

我需要一個公式來填充第三列（圖像中的黃色），如果任務是一個的一部分命令 ＃其中包括任務“洗車”，如果不是，則返回“否”。

我在網上搜尋了解決方案但沒有成功。

答案1

下面是 5 個公式的級數。每個公式都基於其前面的公式。希望這樣的進展能讓後面的內容更容易理解。

[ 公式 1 / 5 ]  基礎公式

=IFERROR(IF(
   MATCH(A2:A7,FILTER(A2:A7,B2:B7="Wash car"),0),
   "yes"),"no")
  1. 如果功能用途匹配就像它一樣logical_test。
    • 匹配依序使用每個訂單作為search_key
    • 此range參數使用以下方式填充篩選函數傳回任務為「洗車」的訂單列表
  2. 這如果函數的value_if_true參數是"Yes"
  3. value_if_false被省略，因為logical_test使用的只能返回TRUE或#N/A
  4. 錯誤將錯誤替換為"No"

[ 公式 1 / 5 ] 基礎公式

[公式 2 of 5 ]  忽略空白行

=IF(A2:A7<>0, IFERROR(IF(
   MATCH(A2:A7,FILTER(A2:A7,B2:B7="Wash car"),0),
   "Yes"),"No"),"")
  1. 若要忽略空白行，如果函數被加value<>0為它的logical_test。它將返回：
    • TRUE對於包含非零數字的任何單元格以及任何文字字串，甚至包含數字的單元格。例如。("0"<>0)=TRUE
    • FALSE對於數字0以及 Excel 強制為零的空白儲存格。例如。如果OR(ISBLANK(A1),A1=0)那麼(A1<>0)=FALSE
  2. 這基本公式被用作value_if_true
  3. value_if_false是一個空字串""。
    此方法會跳過返回，"No"同時防止資料間隙中的行偏移。

[公式 2 of 5 ] 忽略空白行

[ 公式 3 / 5 ]  標題行公式

將公式移至標題行可能會防止其被覆蓋。

=VSTACK("Wash on Order?",
  IF(A2:A7<>0, IFERROR(IF(
     MATCH(A2:A7,FILTER(A2:A7,B2:B7="Wash car"),0),
     "Yes"),"No"),""))
  1. 堆疊允許垂直堆疊數組
  2. 第一個使用的陣列是標題"Wash on Order?"
  3. 第二個陣列是前面的公式。

[ 公式 3 / 5 ] 標題行公式

[ 公式 4 of 5 ]  新增了 LET 函數

儘管讓單獨使用不會對前面的公式添加很多內容，在這一步中應用它是為了澄清轉換。隨著公式越來越複雜，讓變得更加清晰。

=LET(rng,A2:B7,
   a,DROP(rng,,-1), b,DROP(rng,,1),
   VSTACK("Wash on Order?",
     IF(a<>0, IFERROR(IF(
       MATCH(a, FILTER(a, b="Wash car"), 0),
       "Yes"), "No"), "")))
  1. 這讓函數允許將值和公式儲存在變數中以供重用。這可以減少程式碼重複和公式長度，還可以使複雜的公式更易於理解和管理。
  2. 源範圍儲存在rng.
  3. A 列a和 B 列b回傳使用降低調度不必要的列。
  4. a現在在公式中b使用 和 代替和A2:A7。B2:B7

[ 公式 4 of 5 ] 新增了 LET 函數

[ 公式 5 of 5 ]  自動調整範圍大小

允許資料增長而無需修改公式。接受完整列，然後根據填充的資料根據需要調整數組大小。的價值讓正在全面展示。

=LET(rng,A:B,  
  arr,DROP(FILTER(rng, ROW(rng)<=
    MAX((rng<>0)*(ROW(rng)))),1),
  a,DROP(arr,,-1), b,DROP(arr,,1),
  VSTACK("Wash on Order?",
    IF(a<>0, IFERROR(IF(MATCH(a,
      FILTER(a, b="Wash car"), 0),
      "yes"),"no"),"")))
  1. 篩選傳回一個arr行數組，其行號是<=包含訂單 ID 的最後一行的行號。
  2. 最後一行是透過應用程式計算的最大限度到行號數組，其行也包含非零值。星號*是“and”運算符：MAX(ROW(rng)*rng<>0)
  3. 產生的陣列包括標題（第 1 行）、訂單以及任何間隙。包含間隙是為了確保有意或無意的間隙不會導致結果偏離原始範圍。
  4. 這降低函數用於刪除標題行。或者，這可以透過添加篩選狀態ROW(rng)<>1

[ 公式 5 of 5 ] 自動調整範圍大小

答案2

以下解決方案似乎滿足範例資料中規定的目標。

  1. 在空白列中C，將A每行的B列與=concat(A2,B2)（在儲存格中C2）連接起來。

  2. 在空白列中D，從 開始新增下列公式D2。這是一個數組公式，因此您需要CTRL+SHIFT+ENTER同時按下才能使其發揮作用。

    =IF(OR($A$1:$A$6=A1)*(OR($C$1:$C$6=CONCAT(A1,"Wash car"))),"Yes","No")

  3. 複製D2到其餘行。

如果我忽略了某些事情（我覺得我可能正在做，因為我的樣本資料集非常有限），請告訴我，我會盡力糾正它。如果此解決方案最終解決了您的問題，請將其標記為答案。

相關內容