📝 整える — データを加工・変換する
TRIM、SUBSTITUTE、TEXT、ASC等を使ったデータクレンジング・変換テクニック
こんなときに:
- •文字列のスペースを消したい
- •全角を半角に統一したい
- •セルの値をくっつけたい
- •表記ゆれを一括で直したい
75件のQ&A
TEXTJOIN関数とCONCAT関数はどのバージョンから使える？
TEXTJOIN関数とCONCAT関数はExcel 2019以降で使用可能です。
TEXTSPLIT関数はどのバージョンで使える？
TEXTSPLIT関数はMicrosoft 365およびExcel 2024で使用可能です。
Power Queryはどのバージョンで使える？
Power QueryはExcel 2016ではアドイン、2019以降では標準搭載です。
Excelで和暦「令和」は使える？「元年」表記の対応は？
令和表示は全バージョン対応ですが、「元年」表記は2021/365のみです。
条件付き書式のアイコンセットはバージョンで違いがある？
アイコンセットは全バージョンで利用可能で大きな違いはありません。
ストック画像をExcelに挿入できるバージョンは？
ストック画像はExcel 2021およびMicrosoft 365で使用可能です。
セルの表示形式で使えるロケール指定（[$-ja-JP]等）に違いはある？
ロケール指定は全バージョンで使用可能ですが、365でより充実しています。
古い.xls形式のファイルは新しいExcelで開ける？
.xlsファイルは全バージョンで開けますが、互換モードになります。
CSVファイルの文字化けを防ぐ方法はバージョンで違う？
全バージョンで文字化けの可能性があります。Power Queryでのエンコーディング指定が確実です。
Excel 2024とは？2021との違いは？
Excel 2024は最新の買い切り版で、TEXTSPLIT等が追加されましたが365専用機能もあります。
セル内のデータをカンマやスペースで分割する方法は？
「データ」→「区切り位置」で分割。365/2024ではTEXTSPLIT関数も利用可能。
URLを貼り付けるとサイト名に自動変換されるのを防ぐ方法は？
Ctrl+Zで元に戻すか、オートコレクト設定でハイパーリンク変換を無効にできます。
文字列（"20250210"等）を日付に変換する方法は？
DATE関数とLEFT/MID/RIGHT、またはDATEVALUE関数で変換できます。
セル内の文字列を操作する基本関数は？（LEFT, RIGHT, MID等）
LEFT/RIGHT/MID等の文字列関数は全バージョンで使用可能です。
複数のセルの文字列を結合する方法は？
「&」演算子が最もシンプル。2019以降はCONCAT/TEXTJOINがより便利。
文字数を数える関数は？LENとLENBの違いは？
LENは文字数、LENBはバイト数を返します。全角/半角の判定にも活用可能。
LEFT、RIGHT、MID関数で文字列を切り出す方法は？
LEFT/RIGHT/MIDで文字列の任意の位置から切り出しが可能です。
FIND関数とSEARCH関数の違いは？
FINDは大文字小文字区別あり、SEARCHは区別なしでワイルドカード対応です。
SUBSTITUTE関数とREPLACE関数の違いは？
SUBSTITUTEは文字列指定、REPLACEは位置指定で置換します。
余分なスペースや改行を削除する方法は？
TRIM関数でスペース、CLEAN関数で制御文字を削除できます。
文字列を数値に変換するVALUE関数、数値を文字列にするTEXT関数の使い方は？
VALUE関数で文字列→数値、TEXT関数で数値→文字列の変換が可能です。
Power Query（パワークエリ）とは？何ができる？
Power Queryはプログラミング不要でデータの取得・変換・読み込みを行う機能です。
Power Queryのマージ（結合）でVLOOKUPの代わりにデータを結合する方法は？
「クエリのマージ」でVLOOKUP的なテーブル結合が可能。6種類の結合方法に対応。
Power Queryエディターでよく使う変換操作は？
列の削除、型変更、フィルタ、分割、ピボット解除等が主な変換操作です。
Power Queryでクロス集計表を縦持ちに変換（ピボット解除）する方法は？
「列のピボット解除」でクロス集計表を縦持ちに変換できます。
Power QueryのM言語とは？詳細エディターの使い方は？
M言語はPower Query内部の関数型言語。詳細エディターで直接編集可能。
FORMULATEXT関数の使い方と数式の可視化方法は?
FORMULATEXT関数はセルの数式を文字列として表示し、数式の可視化や監査に役立ちます。
N関数の使い方と値を数値に変換する方法は?
N関数は値を数値に変換し、TRUE=1、FALSE=0、文字列=0として計算に使えます。
T関数の使い方と文字列の抽出方法は?
T関数は文字列をそのまま返し、数値や論理値は空文字に変換します。
特定の区切り文字の前の部分を取り出す方法は？
LEFT関数とFIND関数を組み合わせて区切り文字の前の部分を取得できます。
2つの区切り文字の間の文字を取り出す方法は？
MID関数とFIND関数を組み合わせて2つの区切り文字の間の文字列を取得できます。
複数の文字を一括で置換する方法は？
SUBSTITUTE関数を複数ネストして異なる文字列を一括置換できます。
TEXT関数でよく使う書式パターンは？
TEXT関数で日付・数値・パーセント・漢数字など多様な書式設定が可能です。
データの正規化（全角半角統一、スペース除去）を一括で行う方法は？
ASC、TRIM、CLEAN、SUBSTITUTE関数を組み合わせてデータ正規化を一括実行できます。
条件に一致するセルの文字列だけを結合する方法は？
TEXTJOIN関数とIF関数を組み合わせて条件一致するセルの文字列だけを結合できます。
特定の文字がセル内に何回出現するか数える方法は？
LEN関数とSUBSTITUTE関数を組み合わせて特定文字の出現回数をカウントできます。
セル内に簡易棒グラフを作る方法は？
REPT関数で文字を繰り返してセル内に簡易的な棒グラフを作成できます。
日付を「令和○年○月○日」形式で表示する方法は？
TEXT関数で日付を「令和○年○月○日」形式の和暦表示にできます。
時間（8:30等）を小数（8.5等）に変換する方法は？
HOUR関数とMINUTE関数で時刻を小数に、TIME関数で小数を時刻に変換できます。
月番号(1〜12)を月名(1月、Januaryなど)に変換する方法は?
SWITCH関数(2019以降)、TEXT関数、CHOOSE関数で月番号を月名に変換できます。TEXT関数が最もシンプルです。
EXACT関数で大文字小文字を区別して文字列を比較する方法は?
EXACT関数は大文字小文字を区別して2つの文字列が完全一致するかを判定します。
REPT関数で文字列を指定回数繰り返す方法は?
REPT関数は文字列を指定回数繰り返します。簡易グラフやゼロ埋めに活用できます。
UPPER/LOWER/PROPER関数で大文字・小文字・先頭大文字に変換する方法は?
UPPER/LOWER/PROPER関数で大文字・小文字・先頭大文字への変換ができます。
CODE関数とCHAR関数の使い方は?(文字コード取得/文字生成)
CODE関数で文字コードを取得、CHAR関数で文字コードから文字を生成できます。
UNICODE/UNICHAR関数(Unicode版CODE/CHAR)の使い方は?
UNICODE/UNICHAR関数はCODE/CHARの多バイト文字版で、日本語や絵文字に対応します(2013以降)。
ASC関数/JIS関数で全角⇔半角を変換する方法は?
ASC関数で全角→半角、JIS関数で半角→全角に変換できます。日本語データの統一に便利です。
NUMBERVALUE関数でロケール指定の文字列→数値変換をする方法は?
NUMBERVALUE関数はロケールを指定して文字列を数値に変換できます(2013以降)。海外形式のデータ変換に便利です。
ADDRESS関数で行番号・列番号からセル参照文字列を生成する方法は?
ADDRESS関数で行番号・列番号からセル参照文字列("A1"など)を生成できます。
セルにどんな数式が入っているか別のセルに表示する方法は？
FORMULATEXT関数で、指定セルの数式を文字列として取得できます。
数式が入っているセルと直接入力されたセルを区別する方法は？
ISFORMULA関数と条件付き書式を組み合わせて、数式セルを視覚的に区別できます。
ISBLANK/ISTEXT/ISNUMBER/ISLOGICAL関数でデータ型を判定する方法は？
IS系関数（ISBLANK、ISTEXT、ISNUMBER等）でセルのデータ型を判定できます。
CONVERT関数で単位変換（kg→lb、cm→in等）する方法は？
CONVERT関数で長さ・重さ・温度など様々な単位間の変換ができます。
ROMAN/ARABIC関数でローマ数字を変換する方法は？
ROMAN関数でアラビア数字→ローマ数字、ARABIC関数でローマ数字→アラビア数字に変換できます。
BAHTTEXT関数とは？（タイバーツの通貨表記）
BAHTTEXT関数は数値をタイ語のバーツ通貨表記に変換する特殊関数です。
WEBSERVICE/FILTERXML/ENCODEURL関数でWeb連携する方法は？
WEBSERVICE関数でWeb APIからデータ取得、FILTERXML関数でXML解析ができます。
検索・置換のショートカットは？
検索は「Ctrl+F」、置換は「Ctrl+H」です。
オートコレクト（自動修正）のカスタマイズ方法は？
ファイル→オプション→文章校正→オートコレクトで、自動修正ルールをカスタマイズできます。
条件付き書式のルール一覧と優先順位の設定は？
ホーム→条件付き書式→ルールの管理で、ルールの一覧表示・優先順位変更・編集ができます。
条件付き書式で数式を使う方法は？
条件付き書式で「数式を使用」を選択し、TRUE/FALSEを返す数式で複雑な条件を指定できます。
セルの表示形式のカスタム書式コード一覧は？
セルの書式設定→ユーザー定義で、#や0、色指定、条件分岐を使った独自書式を作成できます。
セル結合の問題点と結合を使わない代替方法は？
セル結合は並べ替え等で問題が出るため、「選択範囲内で中央」配置が推奨代替方法です。
セル内の折り返し・縮小表示の設定方法は？
ホームタブの「折り返し」またはセル書式の「縮小して全体を表示」で長文を調整できます。
ページレイアウト（余白・用紙・向き）の設定方法は？
ページレイアウトタブで余白・向き・用紙サイズを設定し、印刷範囲や改ページ位置を調整できます。
テーマの色を変更してブック全体の配色を統一する方法は？
ページレイアウト→テーマで配色・フォントを統一し、カスタムテーマも作成可能です。
セルのスタイルで素早く書式を適用する方法は？
ホーム→セルのスタイルで定義済み書式を1クリック適用。独自スタイルの作成も可能です。
スパークライン（ミニグラフ）の挿入と設定方法は？
挿入→スパークラインでセル内ミニグラフを作成。折れ線・縦棒・勝敗の3種類があります。
アイコンセット・データバーの使い方は？
条件付き書式でアイコンセット（矢印・信号等）やデータバー（セル内棒グラフ）を表示できます。
数値が文字列として保存されている場合の一括変換方法は？
空白セルに1を入力してコピー後、対象範囲に「乗算」で貼り付けると数値に一括変換されます。
VBAのRegExp代わりにREGEX関数で正規表現を使う方法は？
REGEX系関数でVBAのRegExpオブジェクト相当の処理が数式で可能
VBAのピボット解除代わりにPower Queryのピボット解除を使う方法は？
Power Queryのピボット解除でVBAの複雑なループが不要に
スプシのSPLIT関数をExcelで使う方法は？
365はTEXTSPLIT、2016/2019/2021は区切り位置またはPower Query
スプシのチェックボックスをExcelに変換する方法は？
Excelは開発タブのフォームコントロールでチェックボックス配置
Excelファイルをスプシで開いたら壊れた場合の対処法は？
xlsx形式で保存し直し、非対応関数やマクロを削除してからアップロード
スプシをExcelに変換したらエラーが出る場合の対処法は？
スプシ専用関数をExcel互換に書き換えてからダウンロード
ExcelのASC/JIS関数（全角半角変換）がスプシで使えない場合は？
スプシはREGEXREPLACEまたはApps Scriptで全角半角変換