☰

数式内の空白の値をどのように処理すればよいですか?

tag-icon • tag-icon microsoft-excel worksheet-function
数式内の空白の値をどのように処理すればよいですか? 
=VLOOKUP(VLOOKUP(A7,InventoryLog,5,FALSE),Price_list,IF(Q2InventoryLog!F7="S",2,3),FALSE)

関数で注文 ID 番号を検索し、それに基づいて商品名と照合するようにします。商品の説明が小さい ("S") 場合は、列 1 に価格を返し、商品が小さくない場合は列 3 に商品を返します。

問題は、時々空白行があって、すべてが台無しになってしまうことです。つまり、空白行が表示されるたびに、1 行後ろになってしまいます。

数式で空白の行をスキップし、次の行の値を使用するようにしたいのですが、IF 関数を調整してみましたが、それでもうまくいきません。

3つの可能性があります:

IF(Q2InventoryLog!F7="L"...), 
IF(Q2InventoryLog!F7="S"...)
IF(Q2InventoryLog!F7=""...)

答え1

私の考えは大きく外れている可能性があります (以前にも同じようなことがありました...) が、あなたの式を読むと、あなたが探しているものが式の中で適切に狙われていないように思えます。私は次のように読みました:

=VLOOKUP(VLOOKUP(A7,InventoryLog,5,FALSE),Price_list,IF(Q2InventoryLog!F7="S",2,3),FALSE)

VLOOKUP(A7,InventoryLog,5,FALSE: A7 には注文 ID 番号があり、ルックアップではそれを使用して、InventoryLog で見つかったレコードの列 5 にある ItemName を検索します。

次に、外部ルックアップはそのアイテム名を使用して Price_list 内のレコードを検索し、Q2InventoryLog 内の行に基づいて、Price_list 内のそのレコードから列 2 または 3 を返します。

論理的に考えると、なぜ F7 が A7 に必要なレコードを持つ行な​​のかという疑問が生じます。明らかに、何の関係もないかもしれません。頭の中に「7」が浮かんでいて、「F」と「7」を入力しただけなのです。しかし、なぜ「F」なのか、そして何を検索するかをどのように便利に知るのでしょうか。おそらく、Q2InventoryLog は InventoryLog を 1 行ずつ照合することになっており、非常にうまく処理します (または、データが使用され、数回更新された後は、これはむしろ無意味になります)。ただし、何らかの理由で空白行が紛れ込むことがあります。

レコードが一致する理由 (F7 が他のセルではなく、このセルにハードコードするセルである理由) については、質問では説明されていません。あなたは何度も何度もその質問をしているようですが、そのたびに詳細に質問しているので、次の繰り返しでその点について説明するかもしれません。そして、誰かがあなたにもっと的確な、より巧妙な助けを与えることができます。あまり広範囲ではありません。しかし、それまでは、2 つのヒントを示します。

  1. Q2InventoryLog データを、内部ルックアップで見つかった商品名、またはすべてを開始する注文 ID 番号のいずれかを含む方法で整理することをお勧めします (前者は「より良い」データ (特に、より広く役立つため) であるため、ある意味ではゴールド スタンダードですが、後者は、ここでの「現場」データには注文関連情報のサイズ情報が含まれると推測されるため、完全に有効なアプローチですが、問題のより一般的なバージョンでは必ずしも機能しません)。ただし、どちらかの場合、2 つ目の内部ルックアップを使用して正確なレコードを見つけ、列 F7 が表すデータを選択して、IF()テストで使用することができます。

  2. 空白行をデータから削除するには、多少の労力が必要です。従来のデータ クレンジングや、Q2InventoryLog データに空白行が挿入される理由の調査をまったく行わないということではありません。おそらく、そうしたければ、またはできるのであれば、そうすれば問題は解決するでしょう (まあ、悪いタイミングで突然問題が発生するのを待っているだけですが)。

私が言いたいのは、非常に単純なことです。FILTER()空白行を単純にフィルター処理するために使用します。 Excel のフィルター処理機能は使用しないでください。行アドレスが保持され、このためにさらに作業が必要になります。FILTER()実際の Q2InventoryLog データ セットを単純な「something <> blank」条件で単純に設定して、問題の原因となる空白行を除外します。 Q2InventoryLog データは、それ以外の場合は 1 対 1 で一致するはずであるように見えるため、これでうまくいきます。 検索では、FILTER()実際のデータではなく、生成されるテーブル/範囲を使用します。

FILTER()この方法で使用される関数の単純化されたバージョンは次のようになります。

(レコードが Q2InventoryLog の C7:F7 で、行の一部のセルが空白ではなく、行全体が空白であるとします。したがって、C12 が空白の場合、行全体に問題があり、行 12 に必要なデータは現在行 13 にあり、行 12 がなくなると右の行に移動するため、行を削除する必要があります。また、データが C3:F27 にあるとします。)

=FILTER(C3:F27,C3:C27<>"")

当然、データに合わせてそれを調整します。

上記で覚えておくべきポイントは、問題には異なる概念的な方法でアプローチして簡単に解決できる場合があるということです。数式に行を 1 つ下に移動するように指示する方法 (最初は 1 行、その後、そのポイントより下のすべての検索で別の空白行が現れるまで、その後はすべて下に移動するように指示し、1 行だけではなく今度は 2 行になり、...) を考えるのではなく、問題の原因となっている行をなくす方法を考えます。そして、それを実行すると、ここでのように、面倒なデータ クレンジングやデータ ソースとの議論だけが唯一の方法として考えられるかもしれませんが、ここでのように、その作業や議論なしで実行できる別の方法があることが判明するかもしれません。

最後に、残念ながらランダムに挿入された空白行が実際に 1 対 1 で対応しておらず、混乱を招いている場合、これでは問題は解決しません (うーん... Q2InventoryLog 構造を使用して追加の内部ルックアップをサポートできることが判明しない限り)。 そのため、もう一度質問してみる必要があります...

もしそうなら、電話会社の技術アプローチである、一度に小さな改善を一つずつ行い、それから何年もそれで利益を上げ、さらに小さな改善を加えて、完全な情報を提供するというやり方を忘れてみてください。そうすれば、すぐにあなたにとってずっと役立つことがわかるでしょう。

答え2

したがって、数式で空白セルを許容するには、ISBLANK() を使用します。

=IF(ISBLANK(A7), "", VLOOKUP(VLOOKUP(A7,InventoryLog,5,FALSE),Price_list,IF(Q2InventoryLog!F7="S",2,3),FALSE))

A7 が空白の場合、空のテキストが返されます。ただし、代わりに A8 アイテムの価格を返したい場合は、"" を A8 の式に置き換えるだけです。

=IF(ISBLANK(A7), VLOOKUP(VLOOKUP(A8,InventoryLog,5,FALSE),Price_list,IF(Q2InventoryLog!F8="S",2,3),FALSE), VLOOKUP(VLOOKUP(A7,InventoryLog,5,FALSE),Price_list,IF(Q2InventoryLog!F7="S",2,3),FALSE))

関連情報