Excelでデータを扱う際、一部のタスクが不必要に面倒に感じることがあります。例えば、氏名の列を姓と名の列に分割したり、複数のセルのテキストを特定のコンマで結合したりする必要があるかもしれません。これらは複雑な分析上の課題ではなく、日常的に発生する基本的なデータ処理タスクです。
幸いなことに、Excelにはこのような状況に特化した組み込み関数が用意されています。しかし、これらの関数はExcelの標準機能の一部ではないため、見落とされがちです。 ほとんどの人が学ぶ標準的なExcelツールセット私も含め、ここで紹介する関数は高度な計算ではありませんが、繰り返しのデータ処理を行っている場合は、これらの関数を使うことで時間を節約できるかもしれません。
クイックリンク
5. テキスト分割
くっついたテキストを分離します
誰かの姓名、さらにはミドルネームのイニシャルまでが1つのセルに詰め込まれたスプレッドシートを受け取ったことがあるなら、そのデータを分割する苦労をご存知でしょう。TextSplitはまさにこの問題に対処します。1つのセルからテキストを取得し、指定した区切り文字に基づいて複数の列に分割します。
サンプルの営業スプレッドシートを使ってみましょう。営業担当者の名前が「Sarah Chen」、「Mike Johnson」、「Lisa Park」とすべて同じ列に並んでいます。各名前を手動で別々の列に入力する代わりに、TextSplit を使えば自動的に処理できます。
式は次のとおりです。
=TEXTSPLIT(テキスト, 列区切り, [行区切り], [空を無視], [一致モード], [埋め込み])
各教師の業務は次のとおりです。
- テキスト： 分割するテキストが含まれているセル。
- 列区切り文字: データを区切る文字 (スペース、カンマ、セミコロンなど)。
- row_delimiter（オプション）: 行と列の両方に分割するときに使用します。
- ignore_empty（オプション）: TRUE は空の値を無視し、FALSE は空の値を保持します (デフォルトは FALSE)。
- match_mode（オプション）: 大文字と小文字の区別を制御します (大文字と小文字を区別する場合は 0、大文字と小文字を区別しない場合は 1)。
- pad_with（オプション）: 結果の長さが等しくない場合、空のセルに何を入力しますか?
たとえば、営業担当者の名前の場合、次の数式を使用して名前を別々の列に分割します。
=TEXTSPLIT(A2, " ")
この関数は、データに基づいて必要な数の列を自動的に作成します。この基本的なアプローチはほとんどの場合機能しますが、追加のパラメータを使用して、よりきめ細かな制御を行うことができます。 ExcelのTEXTSPLIT関数.
4. テキスト結合
複数のセルを1つのセルに結合する
TEXTJOINはTEXTSPLITの逆の動作をします。複数のセルからテキストを取得し、選択した区切り記号を使用して1つのセルに結合します。これは、完全な住所、製品の説明、メールリストなど、連続した値を作成する必要があるときに便利です。
式は次のようになります。
=TEXTJOIN(区切り文字, 空文字を無視, テキスト1, [テキスト2], ...)
各パラメータが制御する内容は次のとおりです。
- デリミタ： 埋め込まれた値を区切る文字またはテキスト (カンマ、スペース、ダッシュなど)。
- 空を無視: 空白セルを無視する場合は TRUE、結果に含める場合は FALSE です。
- text1、text2など: 結合するセルまたは範囲 (個々のセルまたは範囲全体を指定できます)。
売上スプレッドシートを見ると、名と地域に別々の列があるが、それらを 1 つの列に結合する必要がある場合は、TEXTJOIN を使用します。 無視_空 TRUE の場合、空白のセルは自動的にスキップされます。
=TEXTJOIN(" - ", TRUE, B2, D2)さまざまなテキスト統合方法を選択する際には、 CONCAT関数とTEXTJOIN関数の違い 特定のデータ統合ニーズに適したツールを選択するのに役立ちます。
3. チョーセコールズ
データの特定の列を指定します。
CHOOSECOLS 関数を使用すると、コピー＆ペーストや参照の作成をすることなく、範囲から特定の列を抽出できます。データセットが大きく、分析に必要なのは2列目、5列目、8列目のみの場合、この関数は必要な列のみを抽出し、残りは破棄します。
売上データに基づいて、受注日、製品カテゴリ、その他の詳細は無視し、営業担当者と営業担当者名のみを抽出したい場合があります。CHOOSECOLS関数は、手動で列を選択してコピーする代わりに、ソースデータが変更されると自動的に更新される動的な参照を作成します。
この関数は次の式に従います。
=CHOOSECOLS(配列, 列番号1, [列番号2], ...)
各パラメータの動作は次のとおりです。
- アレイ： ソース データが含まれている範囲またはテーブル (A1:F100 のようなセル範囲またはテーブル参照)。
- 列番号1: 抽出する最初の列の番号 (最初の列の場合は 1、2 番目の列の場合は XNUMX など)。
- col_num2など: 含める追加の列番号 (オプション – 必要な数だけ指定できます)。
たとえば、列 2 から営業担当者の名前を抽出し、列 9 からステータスを抽出したい場合は、次のようにします。
=CHOOSECOLS(A1:I23, 2, 9)
この関数は両方の列をストリーム配列として返し、データに合わせて自動的にサイズを調整します。そのため、CHOOSECOLSは 時間を大幅に節約できるExcel関数大規模なデータ セットを操作するときに、複数の VLOOKUP 数式を使用したり、列を手動でコピーしたりする必要がなくなります。
Excel には CHOOSEROWS 関数もあり、同様に動作しますが、行番号を使用した同じ数式構造を使用して、列ではなく特定の行を選択します。
2. テイクアンドドロップ
データの一部を抽出する
TAKE と DROP はペアで動作し、データ範囲の特定の部分を抽出します。TAKE はデータセットの先頭または末尾から特定の数の行または列を抽出し、DROP は先頭または末尾から行または列を削除して、残りの部分を残します。
これらの関数は、データサンプリングのための精密なツールとして機能します。簡単な分析のために最初の10行のデータだけが必要な場合でも、計算の妨げになるヘッダー行を削除したい場合でも、これらの関数はタスクをスムーズに処理します。
TAKE は次の式を使用します:
=TAKE(配列, 行, [列])
DROP も同様のパターンに従います。
=DROP(配列, 行, [列])
両方の関数のパラメータの動作は次のとおりです。
- アレイ： 抽出または変更するソース データの範囲。
- 行： 取得/削除する行数 (正の数は上から、負の数は下から)。
- 列（オプション）:
取得または削除する列の数 (左から正、右から負)。
売上データの最初の 5 行を取得するには、次の数式を使用します。
=TAKE(A1:C100, 5)
最初の 20 行を削除してクリーンなデータで作業するには、次の操作を試してください。
=DROP(A1:C23, 20)
行と列の演算を組み合わせることができます。例えば、次の数式は最初の10行と最初の3列を返します。
=TAKE(A1:F23, 10, 3)
これらの機能は、特に自動的に適応する動的なデータのサブセットが必要な場合に非常に役立ちます。 ExcelでTAKE関数とDROP関数を使う方法 これにより、変化するデータ セットのサイズに適応する柔軟なレポートを作成できるようになります。
1. 集計
複雑なデータを処理する強力な計算
AGGREGATEは、19種類の統計関数の機能をXNUMXつの柔軟な数式に統合します。その特徴は、エラー、非表示の行、フィルタリングされたデータを無視できることです。これは、SUMやAVERAGEなどの標準関数では確実に実行できない機能です。
データに#N/Aエラーが含まれている場合や、特定の地域のみを表示するようにフィルタリングした場合でも、AGGREGATEを使えば、これらの問題に悩まされることなく、合計、平均、その他の統計を計算できます。可視性やデータ品質が頻繁に変化する動的なデータセットを扱う際に、AGGREGATEは便利です。
文の構造にはいくつかの要素が含まれます。
=AGGREGATE(関数番号, オプション, 配列, [k])
各基準は計算のさまざまな側面を制御します。
- 関数番号: 使用する関数を指定する 1 ～ 19 の数字 (1 = AVERAGE、4 = MAX、9 = SUM、12 = MEDIAN など)。
- オプション： 計算中に無視する内容を制御します (0 = なし、1 = 非表示の行、2 = エラー値、3 = 非表示の行とエラー、5 = エラー値のみ、6 = 非表示の行とエラー値)。
- アレイ： 計算するセルの範囲。
- k (オプション):
- LARGE、SMALL、PERCENTILE などの特定の関数でのみ使用されます。
エラーを無視して表示される売上金額を要約するには、次を使用します。
=集計(9, 6, D2:D23)
数字 9 は SUM を指定し、数字 6 は関数に非表示の行とエラー値の両方を無視するように指示します。
計算を実行するこの強力な機能こそが、AGGREGATE が含まれている理由です。 すべてのオフィスワーカーが知っておくべきExcel関数のリスト—より単純な機能では効果的に管理できない現実世界のデータの混沌を処理できます。
使う価値のある組み込みツール
Excelの最も重要な関数は、多くの場合、最初に学ぶものではありません。しかし、これらの関数は、乱雑なテキストデータの処理、大規模なデータセットからの特定の部分の抽出、不完全なデータに対する計算など、実際のスプレッドシート作業で発生する微妙な問題に対処します。ここで紹介した関数はどれも、高度なExcelスキルを必要としません。ただし、TEXTSPLIT、CHOOSECOLS、TAKE、DROPはMicrosoft 365とWeb版Excelでのみ使用できます。
次回、データのクリーンアップや列のコピーを何度も繰り返すことになったら、これらの関数の存在を思い出してください。Excelにはこれらの関数が既に組み込まれており、面倒な作業も自動的に処理してくれるので、データが実際に何を伝えているのかに集中できます。
