この記事ではよく使用される関数と、数式を最大限に活用する上で役立つその他のリソースをご紹介します。
関数を組み合わせてデータを分析し、さらに使いやすくする方法は無数にあります。
- 包括的な関数リストを確認します。
- 数式テンプレートをダウンロードします。
- Smartsheet オンライン コミュニティで具体的なユース ケースを質問します。
シート間の数式
これから紹介する数式はさまざまな関数を組み合わせており、シートをまたがるデータ収集でうまく機能します。詳細については「数式: 別のシートのデータを参照する」をご覧ください。
引用符で囲まれた条件を数式に入力する代わりに、[列の名前]@row など、その値が配置されたセルへの直接参照に置き換えることができます。数式でセル参照または列参照を作成する方法をご紹介します。
一致する値を使用して 1 つのセルを探す
別の列の一致する値に基づいて、ある列から値を取り出し、行を特定します。VLOOKUP の代わりにこれを使用してシートのパフォーマンスを向上させ、シート間のセル参照を減らします。
たとえば、以下のように INDEX と MATCH を使用します。
=INDEX({返す値のある列}, MATCH("一致する値", {一致させる値のある列}, 0))
「一致する値」は両方のシートにわたって一意で、同じタイプの列に格納されていなければなりません。たとえば、その値でシートをフィルターすると、1 行のみが表示されるはずです。「一致する値」が複数の行にある場合、数式は最初に見つかった一致を返します。この場合に代替手段として使用できる、以下の 2 つのソリューションをご覧ください。
複数の条件を使用して 1 つのセルを探す
別の列に含まれる、複数の一致する値に基づいて、ある列から値を取り出します。
たとえば、以下のように INDEX と COLLECT を使用します。
=INDEX(COLLECT({返す値のある列}, {一致させる値のある列 1}, "値 1", {一致させる値のある列 2}, "値 2"), 1)
一致するすべてのコンテンツを 1 つのセルに集める
条件に一致するすべてのセルを 1 つのセルに取り出します。INDEX の使用と似ていますが、ソース シートに一致するデータを持つ行が複数ある場合、これを使用すると、一致する可能性のある値をすべて表示できます。
たとえば、以下のように JOIN と COLLECT を使用します。
=JOIN(COLLECT({返す値のある列}, {条件列 1}, "条件 1", {条件列 2}, "条件 2"), ", ")
複数条件の平均
別の列の複数条件に基づいて、ある列の平均を出します。実質的に複数の AVERAGEIF のように機能し、複数のフィルター条件を追加して、どの行を平均の計算に使うかを決められます。
たとえば、以下のように AVG と COLLECT を使用します。
=AVG(COLLECT({平均する列}, {条件列 1}, "条件 1", {条件列 2}, "条件 2"))
条件に合致する、最新のデータまたは最も大きい数字を探す
別の行の複数条件に基づいて、ある列から最新のデータまたは最も大きい数字を取り出します。
たとえば、以下のように MAX と COLLECT を使用します。
=MAX(COLLECT({最も遅い日付または最大の数字を探す列}, {条件列 1}, "条件 1", {条件列 2}, "条件 2"))
最も早い日付または最小の数字を探す場合は、MAX ではなく MIN を使用できます。
シート間の参照を最適化する
シート内で数式が参照できるセルの総数は 2,500 万に達することがあります。この制限を超えると、数式で参照されるセルの数を減らすように求められます。
シート内の数式によって参照されるセルの数を最適化するには、次のヒントが役立ちます。
- VLOOKUP の代わりに INDEX(MATCH) を使用します。VLOOKUP は表の範囲全体を参照しますが、INDEX(MATCH) は必要な列のみを参照するため、関係するセルの数が減ります。
- 複数のシートに数式を分散する: 単一のマスター シートを使用する代わりに、異なるリクエスト タイプごとに個別のシートを作成してデータを分散します。これにより、数式をより効率的に分布できるようになります。
- 必要なセルのみを参照する: 数式では計算に必要な特定のセルのみ参照する必要があります。これにより、参照されるセルの合計数を減らすことができます。
これらの手順は、ソフトウェアの機能を最大限に活用し、パフォーマンスを向上させるのに役立ちます。
さらにサポートが必要な場合
他のサポート リソースをお探しの場合は、数式ハンドブック テンプレートをご活用ください。リアル タイムで操作を練習できるすべての関数の用語集や、よく使われる数式と高度な数式の例を含む、100 個以上の数式を記載しています。
Smartsheet の他のお客様がこの関数をどのように使用しているかの例を検索したり、Smartsheet オンライン コミュニティで特定の使用例について質問したりすることができます。