仕組みと動作原理
TOCOL関数はExcel業務を効率化する代表的な関数です。Transforms a 2D table or array into a single vertical column. 複雑な集計や参照処理を簡素化し、データ分析の精度と生産性を飛躍的に高めます。
構文.
標準Excel表記法=TOCOL(array, [ignore], [scan_by_column])引数一覧.
この関数で指定できるすべての引数について詳しく解説します。
The 2D array or range to flatten.
指定例:A2:B30 = Keep all (default), 1 = Ignore blanks, 2 = Ignore errors, 3 = Ignore blanks and errors.
初期値: 0具体的な計算例
ステップ別の実務シナリオShift Schedule - Flatten Roster Grid
Reshape morning and evening shift staff into a single roster column.
| Morning Shift | Evening Shift | Combined Roster |
|---|---|---|
| Alice | Bob | — |
| Charlie | Dave | — |
=TOCOL(A2:B3, 1)Evaluates TOCOL over the sample table cells, placing the verified output in cell C2.
数式ジェネレーター
ウィザードを開く引数を入力するだけで、シートにそのまま貼り付けて使える数式を作成できます。
=TOCOL()出力される値の特性
Returns "Alice" based on sample evaluation criteria and active cell references.
実務上のベストプラクティス
- Use ignore = 1 to consolidate sparse data grids into clean lists without blanks.
よくあるミスと注意点
- Only available in Excel 365 and Excel for Web; older Excel versions return #NAME?.
各プラットフォーム対応表
実際のExcelおよびGoogleスプレッドシートの計算エンジン仕様に基づいた検証結果です。
In Excel 2021 or 2019, use INDEX combined with SEQUENCE, or use Power Query (Unpivot Columns) to flatten grids into a column.
Introduced in Excel 365 (late 2022). Not available in standalone Excel 2021, Excel 2019, or older.
Fully supported in Google Sheets (added late 2022). Uses identical arguments to Excel.
よくある質問と回答
技術解説Can I use TOCOL across different worksheets in Excel?↓
Yes. You can reference cells located on other sheets by including the sheet name followed by an exclamation mark, for example: =TOCOL(Sheet2!A2:A10). If the sheet name contains spaces, enclose it in single quotes: ='Q1 Report'!A2:A10.
What is the most common reason for errors in TOCOL?↓
Only available in Excel 365 and Excel for Web; older Excel versions return #NAME?.
Does TOCOL work identically in Google Sheets?↓
Yes, TOCOL is supported in both Microsoft Excel and Google Sheets with standard evaluation behavior.