Geekly Articles each Day

Microsoft Excelで入力されたタスクを解決するためのマニュアル

こんにちは、ハブロジテルさん！

私たちの中には、家の予算の準備と分析から始まり、仕事、勉強などの計算で終わる少量のデータを処理するタスクを処理する必要がある場合があります。 おそらくこれに最も適したツールは、Microsoft Excel（またはおそらく他の類似物ですが、あまり一般的ではありません）です。

この検索で​​は、Habréの類似トピックに関する記事が1つだけでした。 「Google SpreadSheetの数式によるタルマッド」です。 Excelで作業するための基本的な事項についての適切な説明を提供します（ただし、Excel自体については100％ではありません）。

したがって、クエリ/タスクの特定のプールを蓄積して、アイデアはそれらを典型化し、可能なソリューションを提供するようになりました（すべてではありませんが、すぐに結果が得られます）。

ユーザーが直面する最も一般的な問題を解決することです。

ソリューションの説明は次のように構成されています-最初のタスクを含むケースが与えられ、徐々に複雑になっています。各ステップについて説明付きの詳細なソリューションが与えられます。 関数の名前はロシア語で表記されますが、最初の括弧内には英語の元の名前が表記されます（経験上、ユーザーの大半はロシア語版がインストールされているため）。

Case_1：ブール関数と一致関数
「プレートに値のセットがあり、特定の条件/条件のセットが満たされたときに特定の値が表示される必要があります」（c）ユーザー

通常、データは表形式で表示されます。

画像

条件：

論理式を参照し、式で事前に記述したソリューション内の値を提供できるIF式（IF）は、これに役立ちます。 テキスト値は引用符を使用して記述されることに注意してください。

式の構文は次のとおりです。
IF（log_expression、[value_if_true]、[value_if_false]）

ソリューションの式の構文は次のとおりです。
結果をセルD2に出力します。
= IF（C5> 5;「注文不要」、「注文必要」）

出力は結果です：

画像

たとえば、2つ以上の条件が満たされるなど、条件がより複雑になる場合があります。

この場合、「IF」式のみの使用に限定することはできなくなり、その構文に別の式を追加する必要があります。 そして、これは別の論理式「AND」（AND）になります。
式の構文は次のとおりです。
そして（boolean1、[boolean2]、...）

ソリューションの構文は次のとおりです。
結果をセルD2に出力します。
= IF（AND（C2> 5; B2 =“ A”）; 1; 0）

したがって、2つの式の組み合わせを使用して、問題の解決策を見つけ、結果を取得します。

画像

タスクを複雑にしてみましょう-新しい条件：

ソリューションの構文は次のとおりです。
結果をセルD2に出力します。
= IF（OR（AND（C2 = 10; B2 =“ A”）; AND（C2> = 5; B2 =“ B”））; 1; 0）

レコードからわかるように、式「IF」には1つの条件「OR」（OR）と、それに含まれる式「AND」を使用する2つの条件が含まれています。 2番目のレベルの条件の少なくとも1つが値「TRUE」を持つ場合、結果「1」が「結果」列に表示され、そうでない場合は「0」になります。
結果：

画像

それでは、次の状況に移りましょう。
「条件」列の値に応じて、「結果」列に特定の条件を表示し、値と結果の対応を以下に示します。
条件：

IF関数を使用して問題を解決する場合、構文は次のようになります。
結果をセルB2に出力します。
= IF（A2 = 1; "A"; IF（A2 = 2; "B"; IF（A2 = 3; "C"; IF（A2 = 4; "G"; 0））））

結果：

画像

ご覧のとおり、このような式を書くことは非常に便利で面倒なだけでなく、エラーが発生した場合に経験の浅いユーザーが編集するのに時間がかかることもあります。
このアプローチの欠点は、少数の条件に適用できることです。すべての条件を手動で入力し、大きなサイズに「膨らませる」必要があるためです。

代替ソリューション_1：
「選択」式の使用
関数の構文：
SELECT（インデックス番号、値1、[値2]、...）

それを使用する場合、指定された値に応じて条件の結果をすぐに入力します。
条件：

式の構文は次のとおりです。
=選択（A2;“ A”;“ B”;“ C”;“ D”）

結果は、上記の機能の「IF」チェーンを使用したソリューションに似ています。
この式を適用する場合、次の制限があります。
セル「A2」（インデックス番号）には数字のみを表示でき、結果の値は1〜254の値の昇順で表示されます。
つまり、セル「A2」で1から254までの数字が昇順で示されている場合にのみ機能が機能し、この式を使用するときに特定の制限が課せられます。
つまり 数値5を指定するときに値「G」を表示する場合

数式の構文は次のとおりです。
結果をセルB2に出力します。
=選択（A31;“ A”;“ B”;“ C” ;;“ D”）

ご覧のとおり、式の値「4」を空のままにして、結果「G」をシリアル番号「5」に転送する必要があります。

代替ソリューション_2：
そこで、最も人気のあるExcel機能の1つを紹介します。この機能を使用すると、オフィスワーカーが自動的に「経験豊富なExcelユーザー」/ sarcasm /に変わります。
式の構文は次のとおりです。
VLOOKUP（search_value、table、column_number、[interval_view]）

重要： VLOOKUP関数は、最初の一意のレコードのみで一致を検索します。desired_valueがTable引数に複数回存在し、異なる意味を持つ場合、VLOOKUP関数は最初の一致のみを検索し、他のすべての一致の結果は表示されません。 VPR»（VLOOKUP）は、データを操作する別のアプローチ、つまり「ディレクトリ」の形成に関連付けられています。
アプローチの本質は、条件と対応する値が書き込まれているメイン配列とは別に、「Search_value」引数と特定の結果の対応の「ディレクトリ」を作成することです。

画像

次に、テーブルの作業部分に、既に完了したディレクトリへのリンクを含む式が既に書き込まれています。 つまり ディレクトリの列「D」で、列「A」の値が検索され、一致が見つかると、列「E」の値が列「B」に表示されます。
式の構文は次のとおりです。
結果をセルB2に出力します。
= VLOOKUP（A2; $ D $ 2：$ E $ 5; 2; 0）

結果 ：

画像

次に、テーブルが同一ではないときに、あるテーブルから別のテーブルにデータをプルする必要がある状況を想像してください。 以下の例を参照してください。

画像

両方のテーブルのProduct列の行が一致しないことがわかりますが、これはVLOOKUP関数の使用に対する障害ではありません。
結果をセルB2に出力します。
= VLOOKUP（$ A3; $ H $ 3：$ M $ 6; 2; 0）

しかし、解決するとき、新しい問題に直面します-右に書いた式を列 "B"から列 "E"に "ストレッチ"するとき、引数 "列番号"を手動で置き換える必要があります。 これは時間がかかり、感謝のない作業です。したがって、別の機能が役立ちます-「COLUMN」（COLUMN）。
関数の構文：
COLUMN（[リンク]）

次のようなレコードを使用する場合：
= COLUMN（）

次に、関数は現在の列の番号を表示します（数式が記述されているセルに）。
結果は「VPR」関数で使用できる数値であり、次の式を使用して取得します。
結果をセルB2に出力します。
= VLOOKUP（$ A3; $ H $ 3：$ M $ 6; COLUMN（）; 0）

「COLUMN」関数は、現在の列の数を決定します。これは、「Column_Number」引数によって使用され、ディレクトリ内の検索列の数を決定します。
さらに、次の構造を使用できます。
=列（）-1

数字の「1」の代わりに、必要な数字の列の特定のセルを参照したくない場合は、任意の数字を使用して（減算するだけでなく、取得した値に追加して）目的の結果を得ることができます。
結果は次のとおりです。

画像

トピックの開発を続け、条件を複雑にします。製品データが異なる2つのディレクトリがあり、「参照」列に示されている参照の種類に応じて、結果の表に値を表示する必要があるとします
条件：


画像

すぐに思い浮かぶ解決策は次のとおりです。
結果をセルC3に出力します。
= IF（$ B3 = 1; VLR（$ A3; $ G $ 3：$ I $ 6; COLUMN（）-1; 0）; VLR（$ A3; $ K $ 3：$ M $ 6; COLUMN（）-1; 0 ）））

長所 ：ディレクトリの名前は何でもかまいません（テキスト、数字、およびそれらの組み合わせ）、短所-3つ以上のオプションがある場合はあまり適していません。
電話番号が常に番号である場合、次のソリューションを使用することは理にかなっています：
結果をセルC3に出力します。
= VLOOKUP（$ A3; SELECT（$ B3; $ G $ 3：$ I $ 6; $ K $ 3：$ M $ 6）; COLUMN（）-1; 0）

長所 ：数式には最大254個のディレクトリ名を含めることができますが、短所-名前は厳密に数値でなければなりません。
「SELECT」機能を使用した式の結果：

画像

ボーナス：引数 "search_value"の2つ以上の理由でVLOOKUP。
条件：

両方の表を以下にリストします。

画像

表形式のフォームからわかるように、各アイテムには名前（一意ではない）だけでなく、特定のクラスを参照し、独自のパッケージオプションがあります。
名前とクラスおよびパッケージの組み合わせを使用して、新しい特性を作成できます。そのために、データテーブルに追加列「追加属性」を作成し、次の式を使用して入力します。
= H3＆ "_"＆I3＆ "_"＆J3

「＆」記号を使用して、3つの属性を1つに結合します（単語間の区切りは任意である場合もあれば、まったくない場合もありますが、主なことは検索に同様のルールを使用することです）
式の類似体はCONCATENATE関数である場合があり、その場合は次のようになります。
=クリック（H3; "_"; I3; "_"; J3）

データを含むテーブルの各レコードに対して追加の機能が作成された後、次のようにこの機能の検索関数を記述します。
結果をセルD3に出力します。
= IFエラー（VLOOKUP（A2＆ "_"＆B2＆ "_"＆C2; $ G $ 2：$ K $ 6; 5; 0）; 0）

「VLOOKUP」関数では、3つの属性の同じ束を引数「search_value」（name_class_packing）として使用しますが、すでにテーブルに入力して入力し、引数に直接入力します（オプションとして、追加の列で引数の値を選択できます）表に記入しますが、このアクションは冗長です）。
目的の値が見つからない場合はIFERROR関数を使用する必要があり、VLOOKUP関数は値 "＃N / A"を表示します（詳細については後述）。
下の写真の結果：

画像

この手法はより多くの機能に使用できます。唯一の条件は、取得された組み合わせの一意性です。それが尊重されない場合、結果は不正確になります。

Case_3配列内の値を検索するか、VLOOKUPが役に立たない場合

セルの配列に必要な値が含まれているかどうかを理解する必要がある状況を考えます。
チャレンジ：

視覚的には、すべてが次のようになります。

画像

ご覧のとおり、ここでは「VPR」機能は無力です。 完全に一致するものではなく、必要な値がセルに存在するものを探しています。
この問題を解決するには、いくつかの機能を組み合わせて使用​​する必要があります。
IF
エラーの場合
「LINE」
見つける

すべての順序で、「IF」はすでに以前に分解されているので、関数「IFERROR」に進みます。
IFエラー（値、error_value）


重要：情報の配列やリファレンスブックを操作する場合、この式はほとんど常に必要です。 目的の値がディレクトリにないことがよくあります。この場合、関数はエラーを返します。 セルにエラーが表示され、セルが計算などに関与している場合、エラーも発生します。 さらに、数式がエラーを返したセルには、統計処理を容易にするさまざまな値を割り当てることができます。 また、エラーが発生した場合、他の機能を実行できます。これは、配列を操作するときに非常に便利であり、分岐条件を考慮して式を作成できます。

LINE
低（テキスト）


重要：LINE関数は、文字以外の文字を置き換えません。
式の役割：FIND関数は大文字と小文字を区別して検索するため、テキスト全体を1つのケースにまとめる必要があります。そうしないと、teaはteaと等しくなりません。 これは、レジスタの値が値の検索および選択の条件ではない場合に関係します。そうでない場合、式「LINE」を使用できないため、検索がより正確になります。

次に、FIND関数の構文について詳しく説明します。
FIND（searched_text、viewed_text、[start_position]）


ソリューション式の構文は次のとおりです。
結果をセルB2に出力します。
= IF（IFエラー（FIND（LINE（A2）; LINE（E2）; 1）; 0）= 0; "fail"; "bingo！"）

アクションの式のロジックを分析してみましょう。
  1. LINE（A2）-A2のセルの引数「Searched_text」を小文字のテキストに変換します
  2. 「FIND」関数は、「LINE（E2）」関数によって小文字テキストに変換される配列「Viewed_text」内の変換された引数「Searched_text」の検索を開始します。
  3. 関数が一致する場合、つまり 一致する単語/値の最初の文字のシリアル番号を返します。「IF」式のTRUE条件がトリガーされます。 結果の値はゼロに等しくありません。 その結果、「ビンゴ！」という値が「結果」列に表示されます。
  4. ただし、関数が一致を検出しない場合、つまり 一致する単語/値の最初の文字のシリアル番号は示されず、値の代わりにエラーが返されます。「IF ERROR」式で設定された条件がトリガーされ、「0」に等しい値が返されます。 結果の値は「0」です。 その結果、「結果」列に値「失敗」が表示されます。


画像

上記の図からわかるように、「LINE」および「FIND」機能のおかげで、文字の大文字小文字やセル内の位置に関係なく、目的の値が見つかりますが、5行目に注意する必要があります。
検索条件は「111」に設定されていますが、値「1111111 cookies」が検索配列に示されていますが、式は結果「Bingo！」を返します。 これは、値「111」が一連の値「1111111」に含まれるため、結果として一致するためです。 そうでない場合、この条件は機能しません。

Case_4いくつかの条件に従って配列内の値を検索する場合、またはVLOOKがまだ役に立たない場合

いくつかの条件、つまり値「Name」と「Month」について、2次元配列「参照」の「結果の表」から値を見つける必要がある状況を想像してください。
タスクの表形式は次の形式になります。

画像

条件：

この問題を解決するには、機能「INDEX」と「SEARCH」の組み合わせが適しています

INDEX関数の構文
INDEX（配列、行番号、[列番号]）


つまり、関数は、引数Arrayで指定された配列から、Row_Number引数とColumn_Number引数で指定された座標の交点にある値を返します。

MATCHの構文
SEARCH（search_value、lookup_array、[match_type]）


SEARCH関数は、セル範囲内の指定された要素を検索し、範囲内のその要素の相対位置を返します。
INDEX関数とSEARCH関数の組み合わせを使用することの本質は、「座標軸」に沿った名前で値の座標を検索することです。
軸Yは列「名前」になり、軸Xは行「月」になります。

式の一部：
検索（$ A4; $ I $ 4：$ I $ 7; 0）
Y軸に沿って数値を返します。この場合、1になります。 値「A」は目的の範囲内にあり、この範囲内の相対位置は「1」です。
式の一部：
検索（B $ 3; $ J $ 3：$ L $ 3; 0）
値＃N / Aを返します。 値「1」は表示範囲内にありません。

したがって、INDEX関数がArray引数を検索するために使用するポイント（1;＃N / A）の座標を取得しました。
セルB4の完全に記述された関数は次のようになります。
= INDEX（$ J $ 4：$ L $ 7; SEARCH（$ A4; $ I $ 4：$ I $ 7; 0）; SEARCH（B $ 3; $ J $ 3：$ L $ 3; 0））

実際、必要な値の座標がわかっている場合、関数は次のようになります。
= INDEX（$ J $ 4：$ L $ 7; 1;＃N / A））

引数「Column_Number」の値は「＃N / A」であるため、セル「B4」の結果は対応します。
結果からわかるように、結果を含むテーブル内のすべての値がディレクトリと一致するわけではなく、結果として、テーブル内の値の一部が「＃N / A」の形式で表示されることがわかります。これにより、データの使用が複雑になります。
結果：

画像

このマイナスの影響を中和するために、先ほど読んだ「エラー」関数を使用し、返される値をエラー「0」に置き換えると、式は次のようになります。
結果をセルB4に出力します。
= IFエラー（INDEX（$ J $ 4：$ L $ 7; SEARCH（$ A4; $ I $ 4：$ I $ 7; 0）; SEARCH（B $ 3; $ J $ 3：$ L $ 3; 0））; 0）

結果のデモンストレーション：

画像

図からわかるように、「＃N / A」の値は、結果の表の値を使用した後続の計算で干渉しなくなりました。

Case_5数値範囲内の値を検索

特定の範囲内の数値に特定の属性を与える必要があると想像してください。
条件：
製品のコストに応じて、特定のカテゴリを割り当てる必要があります。
値が範囲内にある場合


画像

LOOKUP関数は、行、列、または配列から値を返します。 この関数には、ベクター形式と配列形式の2つの構文形式があります。
VIEW（検索値;閲覧ベクトル; [結果ベクトル]）



結果をセルB3に出力します。
=ビュー（E3; $ A $ 3：$ A $ 7; $ B $ 3：$ B $ 7）


画像

引数「Viewed_Vector」および「Result_Vector」は配列の形式で記述できます。この場合、Excelワークシートの別のテーブルに表示する必要はありません。
この場合、関数は次のようになります。
結果をセルB3に出力します。
= VIEW（E3; {0; 1001; 1501; 2001; 2501}; {"A"; "B"; "C"; "G"; "D"}）

Case_6属性による数値の合計

特定の基準に従って数値を合計するには、3つの異なる関数を使用できます。
SUMIF（SUMIF）-1つの特性のみを要約します
SUMIFS（SUMIFS）-機能セットを要約します
SUMPRODUCT（SUMPRODUCT）-機能セットを要約します
数式「SUM」が配列に上げられたときに、「SUM」（SUM）と配列の数式の関数を使用するオプションもあります。
（{= SUM（（）*（））}
しかし、そのようなアプローチはかなり不便であり、式「SUMMPROIZV」によって機能が完全に重複しています
次に、構文「SUMPRODUCT」の詳細：
SUMPRODUCT（アレイ1、[アレイ2]、[アレイ3]、...）


条件：


画像

データを含むテーブルからわかるように、コストを計算するには、価格に数量を掛ける必要があり、選択条件を適用した結果の値を結果とともにテーブルに転送する必要があります。
ただし、SUMMPROIZ数式を使用すると、そのような計算を数式内で実行できます。
結果をセルB4に出力します。
= SUMPRODUCT（（$ A4 = $ H $ 3：$ H $ 11）*（$ K $ 3：$ K $ 11> = B $ 3）*（$ K $ 3：$ K $ 11 <C $ 3）;（$ M $ 3：$ M $ 11）*（$ L $ 3：$ L $ 11））

部分的に式を分析しましょう：
（$ A4 = $ H $ 3：$ H $ 11）
-結果の表の「名前」列のデータを使用して、表の「名前」列に選択条件を設定します
（$ K $ 3：$ K $ 11> = B $ 3）*（$ K $ 3：$ K $ 11 <C $ 3）
-時間枠に従って条件を設定します。日付は現在の月の最初の日以上ですが、次の月の最初の日よりも小さくなります。 同様に-結果を含むテーブル内の条件、配列-データを含むテーブル内。
（$ M $ 3：$ M $ 11）*（$ L $ 3：$ L $ 11）
-テーブルの「数量」列と「価格」列にデータを掛けます。
この関数の間違いない利点は、記録条件の自由な順序です。それらは任意の順序で書き込むことができ、これは結果に影響しません。
結果：

画像

ここで、条件を複雑にし、「cookies」という名前による選択が「small」および「large」クラスに対してのみ行われ、「pastry」という名前による選択がクラス「jam」を除くすべてを追加するという要件を追加します。

結果をセルB4に出力します：
= SUMPRODUCT（（$ A4 = $ H $ 3：$ H $ 11）*（$ J $ 3：$ J $ 11> = B $ 3）*（$ J $ 3：$ J $ 11 <C $ 3）*（（$ I $ 3：$ I $ 11 = "小"）+（$ I $ 3：$ I $ 11 = "大"））;（$ L $ 3：$ L $ 11 * $ K $ 3：$ K $ 11））

Cookie選択式に新しい条件が追加されました。
（（$ I $ 3：$ I $ 11 = "小さい"）+（$ I $ 3：$ I $ 11 = "大きい"））
-ご覧のとおり、1つの列の2つ以上の条件は、「+」記号を使用して別のグループで選択し、条件を追加のかっこで囲みます。
ロール選択式に新しい条件も追加されました。
= SUMPRODUCT（（$ A5 = $ H $ 3：$ H $ 11）*（$ J $ 3：$ J $ 11> = B $ 3）*（$ J $ 3：$ J $ 11 <C $ 3）*（$ I $ 3：$ I $ 11 <> "ジャムあり"）;;（$ L $ 3：$ L $ 11）*（$ K $ 3：$ K $ 11））

これ：
（$ I $ 3：$ I <>「ジャムあり」）
-実際、この式では、Cookieを選択するときと同様に選択条件を書き込むことができましたが、その後、式に3つの条件をリストする必要があります。この場合、値を使用する「ジャムあり」とは異なり、例外を書く方が簡単です「<>」。
一般に、機能/クラスのグループが事前にわかっている場合、すべての条件を関数に書き込んで膨らませるよりも、これらをグループに結合してディレクトリを作成する方が適切です。
結果：

画像

さて、ここで簡単なマニュアルの最後に来ますが、実際はもっと大きくなる可能性がありますが、目標は最も一般的な状況への解決策を提供することであり、特定の解決策を説明することではありませんでした（しかし、より興味深いケース）。
このマニュアルが、Excelを使用した問題の解決に役立つことを願っています。これは、私の仕事が無駄にならなかったことを意味するからです。

お時間をいただきありがとうございます！

Source: https://habr.com/ru/post/J238553/

More articles:


All Articles