☰

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

私が扱っている Excel ファイルは、次のようなものです (プライバシー保護のため変更/簡略化されています)。

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

注文 ＃ タスク
この注文の一環                                                                      として車も洗車されましたか?
1 レビュー はい
1 タイヤ交換 はい
1 洗車 はい
2 レビュー いいえ
2 タイヤ交換 いいえ
2 オイル交換 いいえ

違うタスク同じものを共有できる注文 ＃。

3番目の列（画像では黄色）に「はい」を入力する数式が必要です。タスクの一部である注文 ＃これにはタスク「車を洗う」か、洗わない場合は「いいえ」を返します。

オンラインで解決策を探しましたが、見つかりません。

答え1

以下は 5 つの数式の進行です。各数式は、その前の数式に基づいています。このように進行することで、後の数式が理解しやすくなると思います。

[ 5 の式 1 ]  基礎式

=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"

[ 5 の式 1 ] 基礎式

[ 式 2/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
    • FALSEExcelがゼロに強制する数値0と空のセル。例OR(ISBLANK(A1),A1=0)：(A1<>0)=FALSE
  2. の基本式として使用されますvalue_if_true
  3. value_if_false空の文字列です""。
    このアプローチは"No"、データ内のギャップによる行のオフセットを防ぎながら、戻りをスキップします。

[ 式 2/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. Vスタック配列を垂直に積み重ねることができる
  2. 最初に使用された配列はタイトルでした"Wash on Order?"
  3. 2 番目の配列は前の式です。

[ 式 3/5 ] 見出し行の式

[ 式 4/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. 列Aaと列Bはb次のように返されます。落とす不要な列をディスパッチします。
  4. a式では、と の代わりに とがb使用されるようになりました。A2:A7B2:B7

[ 式 4/5 ] LET関数が追加されました

[ 式 5/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. フィルター注文 ID を含む最後の行arrの行番号と同じ行の配列を返します。<=
  2. 最後の行は、マックス行番号の配列に、その行にゼロ以外の値も含まれるようにします。アスタリスク*は「and」演算子です。MAX(ROW(rng)*rng<>0)
  3. この結果の配列には、見出し (行 1)、順序、およびギャップが含まれます。ギャップは、意図的または偶発的なギャップによって結果が元の範囲からオフセットされないようにするために含まれています。
  4. の落とす関数は見出し行を削除するために使用されます。または、フィルター状態ROW(rng)<>1

[ 式 5/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残りの行にコピーします。

何か見落としがある場合（サンプル データ セットが非常に限られているため、見落としている可能性があります）、お知らせください。修正するように努めます。この解決策で問題が解決した場合は、回答としてマークしてください。

関連情報