14/10/2022
homesmartjp
1281 Views

Structured Excel Tech: Divide cells containing multiple data into cells and one data: TECH TIPS

This article is limited to members.You can see everything by registering (free).

Serialized table of contents

Target: Excel 2016/2019/365

セルに複数のデータが含まれていると集計などが行えない1つのセルに複数のデータが含まれた表は、集計や並べ替えなどが行えず、再利用などが難しい。そこで、1つのセルに複数のデータが含まれた表を、1セル1データに変換しよう。

　In the table created in "Microsoft Excel", one cell contains multiple data.Although such a table may be easy to see, the value in the cell becomes a string, and it is not possible to calculate or sort, and there is an extra man -hours when utilizing it.

　Unification rules of the statistical table that each ministry published by the Ministry of Internal Affairs and Communications Statistical Bureau at the general window of government statistics (e-Stat) "Formulation of unified rules for the data that can be read in the statistical table"In the second page "Check item 1-2 1 cell 1 data", "If multiple data is entered in one cell, the sort of calculations, ascending and descending order, copying and paste and graphs.Many manual work and program creation are required for processing and editing, and it cannot be used as data immediately. "

　Of course, the new table should be created with such a policy.However, if you add it to such a table and update it, or if a table of such Excel has been sent from the other party, it is necessary to recreate it in one cell and one data.It is troublesome to do this manually, and the value may change depending on the operation mistake.

　So, in this Tech Tips, I will introduce how to use a single cell to include a table containing multiple data into one cell and one data.The procedure is explained using the example listed in "Formulation of unified rules for the notation of data that can be read in the statistical table".

構造化Excelテク：複数のデータが含まれたセルを1セル1データに分割する：Tech TIPS

■関連記事

When one cell contains two data

　If one cell contains multiple data, the data should be separated by ", (tent)", "(commas)", "() () ()".Using these symbols, multiple data contained in one cell can be separated.

　"Example 1" in "Check items 1-2 1 cell 1 data" in "formulating unified rules for the notation of data that can be read in the statistical table", the "Example 1" is a bit complicated, so the simple steps are simplified.Let's explain how to cut the data from "Example 2".

［区切り位置指定ウィザード］を使ってデータを分割する

　If the numerical value is also included on the right side of the column containing multiple data, insert the column as the number of data included.For example, if one cell contains two data, insert one column in which the cut data is separated.

　When the column is inserted, select a cell containing multiple data, and click the Data Tools group in the Data tab.

　Since [Division Designated Wizard] opens, select "Comma, tab, etc. ..." in "Please select the file format of the data" (in the case of a fixed length data, depending on the space ... "Select).

　On the next screen, specify a separated character.In this case, you may select "Space" here and check "Continuous separated characters ...", but select "Other" and specify "(" (") in the input field.(You can save one time to delete extra character strings later.) Since you can check the status separated by "Data preview", it is good to select a separated character that is easy to process afterwards by trial and error.

　On the next screen, select the column data format and display destination (output destination) of the column, and click the [Complete] button.The "total cost" shown in "()" was separated into the next column.

［区切り位置指定ウィザード］を使ってデータを分割する（1）複数のデータが入ったセルを選択し、［データ］タブの［区切り位置］をクリックする。▼［区切り位置指定ウィザード］を使ってデータを分割する（2）［区切り位置指定ウィザード］が起動するので、「コンマやタブなどの区切り文字によって……」を選択し、［次へ］ボタンをクリックする。▼［区切り位置指定ウィザード］を使ってデータを分割する（3）区切り文字を選択する。この例の場合は、「その他」だけにチェックを入れ、区切り文字として「(」を指定する。すぐに「データのプレビュー」に反映されるので、思った通りにデータが分離しているか確認する。▼［区切り位置指定ウィザード］を使ってデータを分割する（4）通常は、「G/標準」を選んでおけばよい。データ形式が適切でない列があった場合は、その列を選択し、「列のデータ形式」を変更すればよい（日付が入ったセルがある場合は、「日付」を選択するなど）。▼［区切り位置指定ウィザード］を使ってデータを分割する（5）これでデータが分離できる。

余分な文字を削除して体裁を整える

　However, since ")" remains in the separated data, it is not a numerical value.So, select the cut data column, click [Search and Select] in the [Home] tab, and select [Replacement] (you may need to press [Ctrl] + [H] key).When the [Search and Replacement] dialog opens, enter ")" in "Search string", and click the [Replace] button without entering anything in the "substitution string".Now, ")" is deleted, and the cut data is only numeric value.After that, you can prepare the appearance of the item name in the table.

余分な文字を削除して体裁を整える（1）分離したデータには、余分な「)」が残っているので、置換を使ってこれを削除する。余計な文字が入っているセルを選択し、［ホーム］タブの［検索と選択］−［置換］を選択する。［検索と置換］ダイアログが表示されるので、「検索する文字列」に「)」を入力し、「置換後の文字列」には何も入力せずに、［すべて置換］ボタンをクリックする。▼余分な文字を削除して体裁を整える（2）「)」が削除され、数値データになる。▼余分な文字を削除して体裁を整える（3）項目名を追加し、フォントやけい線などを調整し、表の体裁を整える。

When one cell contains multiple data

　Example 1 in "Check item 1-2 1 cell 1 data" in "Formulation of unified rules of how to not write the data that can be read in the statistical table" is packed with three or more data in one cell.Because it is, the procedure is a little complicated.However, basically, as in the case of "Example 2", the data may be separated using [Division Position specified wizard].