Excelテーブルでデータを使用する私の3つのお気に入りの方法

Excelデータ抽出と要約のための3つの実用的な関数

美しくフォーマットされ、フィルター処理され、ソートされたテーブルを備えた大きなワークブックがあるとします。仕事は行われていると思うかもしれませんが、実際には、Excelはこれらのテーブルでさらに多くのことをするのを待っており、これまでに行ったハードワークを最大限に活用できるようにしたいと考えています。

この記事では、Excelテーブルの情報を抽出または要約するために使用できる3つの機能または機能の組み合わせを紹介します。

vlookup and hlookup

vlookupとhlookupは、テーブル内の特定の場所で値を見つけて取得するために使用されます。

vlookupは垂直データテーブルに依存し、テーブルの最初の列（垂直）を調べます。

• hlookupは水平データテーブルに依存しており、テーブルの最初の行（水平）を調べます。
vlookup

ここでは、各グレードにテストスコアと必要な成績のリストがあります（表1と呼びます）。また、学生の成績を持つテーブルもあります（この時点から表2と呼ばれます）。 Excelに表1の情報を使用して、不足している列を表2に記入したいと思います。

表1の最初の列の値を検索して、表2の各生徒のグレードを返すため、vlookupを使用します。 vlookup関数の構文は次のとおりです。

of：

<code>=VLOOKUP(a,b,c,d)</code>

a

は、探す値です（上記の例では、列eの値です）、
• b
は、参照値を含むテーブルです（この場合、それはセルa1からb9、または表1）、
• c
はそのテーブルの列番号です（レベルを返したいので、表1の2番目の列です）、
• d
は、検索値（ "true"）または正確なルックアップ値（ "false"）を近似するかどうかをExcelに伝えるオプションの条件です。空白のままにしておくと、デフォルト値が真です。
• それで、私の場合、私はセルF2にこの式を入力してTOMのグレードを計算し、その後、テーブル内の他のグレードを見つけるためにオートフィルを使用します。

$シンボルを使用して、上記の値の絶対的な参照を作成しました

b

excelは、値を継続的に使用して値を見つけるために継続的に使用したいからです。また、スコア境界テーブルには単一のスコアに割り当てられたランクではなく、範囲が含まれているため、値

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

d

に「true」を使用しました。

hlookup

ここでは、同じレベルの境界情報がありますが、今回は水平に表示されます。これは、フェッチするデータが境界テーブルの2列にあることを意味します。

hlookup関数の構文は、vlookupの構文と似ています：

of：

• aは、見つかる値です（この例では、列cの値）、
• bは、検索値を含むセルへの絶対的な参照です（この場合、それはa1からi2です）
• cはテーブルの行番号です（レベルを返したいので、2行目です）、
• d（オプション）は「true」（近似値）または「false」（正確な値）です。

したがって、セルC5にこの式を入力してTOMのグレードを計算し、Autofillを使用してテーブル内の他のグレードを見つけます。

<code>=VLOOKUP(a,b,c,d)</code>

インデックスアンドマッチ

値を見つけて取得する別の効果的な方法は、特に一緒に使用する場合、インデックスとマッチを使用することです。インデックスは定義された位置の値を探して返しますが、マッチは値のビットを探して返します。一緒に、動的なデータ取得を実装できます。

単一の文法

これらの関数を一緒に見る前に、個別に簡単に表示しましょう。

インデックスの構文は

です

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

ここで、

aはデータを含むセルの範囲であり、bは評価される行番号、cは評価する列番号です。 これに基づいて

<code>=HLOOKUP(a,b,c,d)</code>

は、セルB2からD8を評価し、その範囲の4行目と2番目の列の値を返します。

マッチについては、

に従います

<code>=HLOOKUP(B5,$A:$I,2,TRUE)</code>

ここで、

xは私たちが探している値であり、 yは私たちが探している値の範囲であり、 z（オプション）は一致するタイプ。 これに基づいて

<code>INDEX(a,b,c)</code>

5番の範囲B2からB8がどこにあるかを教えてください。

組み合わせで使用

この例では、特定の月に指定されたプレーヤーが得点する目標の数をExcelに伝えたいと思います。より具体的には、3か月目にプレーヤーCが得点した目標の数を知りたいのですが、いつでもそれらの条件を変更できるようにこの式を作成します。

これを行うには、テーブル内のプレーヤーCの位置を決定し、データの3番目の列の値を教えてください。 Cell G4では、Excelが元のデータから調べて値を返すため、インデックス関数から始めます。次に、そのデータをどこで探すかをExcelに伝えます。

インデックス構文の次の部分は行番号です。これは、セルG2で宣言するプレーヤーによって異なります。たとえば、プレーヤーAを見つけたい場合は、最初の行になります。これを行うには、ExcelがCell G2のプレーヤーをプレーヤー列（A2：A8）の対応するセルに一致させ、どの行があるかを把握するため、一致関数を開始します。また、Excelに正確な検索を返したいので、最後の0を追加しました。

<code>=VLOOKUP(a,b,c,d)</code>

Excelインデックスの関数ライン番号を伝えたので、列番号でそれを行う必要があります。私の例では、列番号は、Cell G3で入力した月数を表します。

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

Enterキーを押したとき、ExcelはプレーヤーCが3か月目に5つのゴールを決めたと正しく言った。

ルックアップテーブルの価値を変更して、任意の月の任意のプレーヤーの合計を見つけることができます。

countifおよびsumif

名前からわかるように、これらの2つの関数は、設定した条件に基づいて値と合計値をカウントします。あなたの状態に含まれていないものは、あなたが指定したスコープ内であっても、追加またはカウントされません。

countif

Countifは、特定の条件を含むセルをカウントします。構文は

です

<code>=HLOOKUP(a,b,c,d)</code>

ここで、aはあなたが数えたい範囲であり、bはカウントの条件です。

同様に、複数の条件を含めたい場合は、countifsを使用します。

<code>=HLOOKUP(B5,$A:$I,2,TRUE)</code>

ここで、

aおよび bは最初の範囲条件のペアリングであり、cdは2番目の範囲条件のペアリングです（あなたは最大127ペアを持つことができます）。 いずれかの条件がテキストまたは論理的または数学的なシンボルである場合、二重引用符で囲む必要があります。

下の給与表では、40,000ポンドを超える人の数と1,000ポンド以上のボーナスを個別に受け取る人の数を計算したいと思います。

40,000ポンド以上の給与を持っている従業員の数を計算するには、この式をセルD8に入力する必要があります。

ここで、

c2：c6

<code>INDEX(a,b,c)</code>

は給与の範囲です。

1,000ポンド以上のボーナスを受け取ったサービススタッフの数を計算するには、2つの条件があるため、Countifsを使用します。 b2：b6、 "services"

パーツは最初の範囲条件付きペアリング、

d2：d6、 "＆gt; 1000"

が2番目のものです。

<code>INDEX(B2:D8,4,2)</code>

上記のテーブルにカンマで数千桁を分離したとしても、カンマはここに異なる機能を持っているため、これらのコンマをフォーミュラに含めません。

sumif

SUMIFは、設定した条件に応じて細胞を合計します。 Countifと同様に機能しますが、括弧内により多くのパラメーターが含まれています。構文は

です

ここで、

a

は、要求する前に評価したい細胞の範囲です。

b

は、その評価の条件（これは値またはセルの参照である可能性があります）、

<code>MATCH(x,y,z)</code>

c

（オプション）は、aとは異なる場合に追加するセルです。

今回は、3つのことを計算する必要があります。40,000ポンド以上の合計給与、サービス部門の合計給与、35,000ポンドを超える従業員の合計ボーナスです。

最初に、£40,000を超える合計給与を計算するには、セルD8に次の式を入力する必要があります。

ここで、

<code>=VLOOKUP(a,b,c,d)</code>

c2：c6

は、テーブルの給与を引用します。 次に、サービス部門の総給与を知りたいです。したがって、セルC9では、と入力します

b2：b6

quotes department列、

"services"

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

excelにサービス部門の従業員を探しているExcel、

c2：c6excelに伝えるこれらの従業員の賃金について合計されています。 私の最後の仕事は、35,000ポンド以上を獲得した従業員が受け取ったボーナスを見つけることでした。セルC10では、と入力します ここで、

c2：c6

excelに賃金を評価するように指示します。条件パーソナルボーナス合計。

<code>=HLOOKUP(a,b,c,d)</code>

Excelには、同じ手順を実行するSumifs関数もありますが、複数の条件については機能します。 sumifの構文とは大きく異なります： ここで、a

は追加される細胞の範囲です。

bは、評価される最初の範囲です。 >、

d

および

e

<code>=HLOOKUP(B5,$A:$I,2,TRUE)</code>

の条件は、次の範囲条件のペアリングです（最大127ペアを持つことができます）。

上の表を使用して、45,000ポンド以上を稼ぐ従業員のボーナスを合計したいとします。これが私が入力する式です： 上記の関数を習得した後、データを再配置せずにFind値列の左右に値を見つけることにより、vlookupの欠点の一部を解くように設計されたXlookup関数を使用してください。

以上がExcelテーブルでデータを使用する私の3つのお気に入りの方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。