文字列を分割/結合する技:Excel関数の技
屈指の人気関数を今こそマスター VLOOKUP（ブイ・ルックアップ）は、表を縦方向に検索し、 特定のデータに対応する値を取り出すExcel関数です。 よくある例としては、以下のように 顧客コードから氏名や性別を取り出すという使い方があります。 セルB3〜B6にVLOOKUP関数が入力されており、顧客コード「T002」に対応する氏名・性別・居住地・年齢を表（顧客リスト）から取り出しています。 VLOOKUP関数をマスターすれば、 対応する値を目視で探したり、手動で何度も検索したりする手間が省けるため、業務効率は格段にアップします。 そのため、 Excel関数の中でも屈指の人気を誇るのですが、引数の指定が複雑という困った面もあります。 【 エクセル時短】第6回では、「ちゃんと使える自信がない」「久しぶりに使おうとしたら忘れてしまった」という人のために、VLOOKUP関数の使い方をおさらいします。 引数は最初の3つがポイント まず、VLOOKUP関数の構文を確認します。 それぞれ以下のような意味があります。 ［ 検索値］... どのデータで• ［ 範囲］... どこを検索して• ［ 列番号］... どの列にある値を取り出すか 4つめの引数［ 検索の型］は、とりあえず置いておきます。 先ほどの顧客リストを例にすると、［ 検索値］には、探したい顧客コードが入力されているセルB1を指定します。 そして［ 範囲］には、顧客リスト全体（セルA10〜F109）を指定します。 顧客リストの見出し行を含める必要はありません。 ここで注意したいのが、［ 検索値］の検索対象は［ 範囲］の 左端の列（左から1列目）である点。 左から2列目や3列目を検索することはできません。 仮に［ 検索値］として「氏名」を指定したいときは、［ 範囲］の左端の列が「氏名」となるように、あらかじめ表を整えておく必要があります。 続いて［ 列番号］には、［ 範囲］で指定した表のうち、 取り出したい値がある列が左から何列目にあるかを数字で入力します。 仮に「2」と指定すれば、表の左から2列目にある「氏名」が取り出されます。 VLOOKUP関数の入力から、第1・第2・第3引数の指定までの操作は次の通りです。 2第1引数［検索値］を指定する 「 」を入力し、第1引数の［ 検索値］となるセル（ここではセルB1）を指定します。 指定したいセルをクリックしてもOKです。 3第2引数［範囲］を指定する 「,」を入力し、第2引数の［ 範囲］となるセル（ここではセルA10～F109）を指定します。 指定したいセル範囲をドラッグして選択してもOKです。 4第3引数［列番号］を指定する 「,」を入力し、第3引数の［ 列番号］を指定します。 ここでは表の左から2列目にある「氏名」を求めたいので「2」と入力します。 これで第1・第2・第3引数までを指定できました。 4つめの引数は「FALSE」と覚える 最後の［ 検索の型］は、 検索したデータが見つからなかったとき、どのように処理するかを決める引数です。 「TRUE」または「FALSE」で指定し、省略した場合は「TRUE」と同じ扱いになります。 ［ 検索値］に一致するデータが見つからないとき、「TRUE」では［ 検索値］を超えない最大値を該当のデータとします。 「FALSE」ではエラーになります。 通常、VLOOKUP関数では 完全に一致するデータを探すことが多いので、少々乱暴ではありますが［ 検索の型］は 「FALSE」にすると覚えてください。 「TRUE」にするケースとしては、成績や予算などの表で一定の基準値を超えない最大値を探したいときなどが考えられます。 第4引数を指定して、VLOOKUP関数の数式を完成させましょう。 5第4引数［検索の型］を指定する 「,」を入力し、第4引数の［ 検索の型］として「FALSE」を指定します。 これで［ 検索値］と完全に一致する値のみが検索されるようになります。 6数式の入力を完了する 「 」を入力して［Enter］キーを押し、VLOOKUP関数の数式の入力を完了します。 結果、セルB3に顧客コード「T002」に対応する氏名が表示されました。 セルB3〜B6の数式をそのまま表示しました。 セルB4～B6のセルは、［ 検索値］ ［ 範囲］［ 検索の型］についてはセルB3と同じですが、［ 列番号］が異なっています。 これにより、それぞれの列に対応する値が取り出されるわけです。 VLOOKUP関数を使ううえで欠かせない、4つの引数をおさらいしました。 関数の使い方を覚えるには、自分で使ってみるのがいちばん。 数式を実際に入力し、VLOOKUP関数の働きを確かめてみましょう。 close 閉じる.
次の
別々のセルに入力した姓と名をつなげて表示したい: [aotenブログ]木村幸子の知って得するマイクロソフトオフィスの話
エクセルによる名寄せ• 名寄せとは 「名寄せ」とは、リスト内にある同じ人や同じ企業を一つにまとめて、重複を削除することをいい、データプレパレーションでよく行われる作業です。 リスト内にある同じ人や同じ企業ができる原因として以下のようなものがあります。 ・別のソフトやシステムでリストを作成している ・引っ越しや異動の際、元のデータを変更せずに、新規に登録する など 名寄せの手順 データ収集 名寄せ対象となるデータをエクセルで読み込めるように、エクセル形式やcsv形式で収集します。 名寄せ方針を決定 どのデータが一致する際に同じとするかの方針を決めます。 例えば、「氏名が一致した場合に、同一とする」とした場合、同姓同名の人が同じ人としてまとめられますので、名寄せ作業は不完全となります。 そこで、「氏名に加え、住所、電話番号、生年月日のすべてが一致する場合同一とする」とした場合、引っ越しなどで住所や電話番号が変わっている場合や、片方の生年月日が記載されていない場合は、同じ人と判定されませんので、同じ人がリスト内に残ることとあります。 名寄せ対象となるリストの内容と今後の活用方法により、その基準を決める必要があります。 また、名寄せ時片側だけにしかないデータを残すか、残さないかも合わせて決めておく必要があります。 名寄せ作業 データクレンジング エクセルでの名寄せ作業は、名寄せ対象となる列のセルが完全に一致していることが前提となります。 リストの作成方法や作成時期により、同じデータであってもリスト内での内容が違って、エクセルでは異なるものとして認識します。 このような違いは、統一する必要があり、これをデータクレンジングといいます。 クレンジング対象例：• 表記文字：全角・半角、ひらがな・カタカナ・アルファベット• 氏名：スペース区切りかどうか、ふりがながカタカナかひらがなか、など• 企業名：株式会社・（株）、企業名と株式会社の間にスペースがあるか、社名変更など• 住所：町村の場合、郡が記載されているか、丁目番地表記か、ハイフン区切りか• 集合住宅：マンション、アパート名を記載しているか、101号室を101と記載しているか101号室と記載しているか• 郵便番号、電話番号：ハイフン区切りか、ハイフンなしか• 使用列数：1列で保存しているか、複数列で保存しているか（住所、郵便番号、電話番号などは） データ統合 列数そろえ 住所、郵便番号、電話番号などは、1列で保存しているか、複数列で保存しているかは、そのデータソースにより異なります。 結合対象のリストで違いがある場合には、同じ列構造にそろえる必要があります。 列並びそろえ 名寄せを行うために、1シート縦に結合する必要がありますので、作業をしやすいように、列の並びをそろえておく方が、効率的、確実に作業を行えます。 重複削除 エクセルの機能で「重複の削除」を行います。 また、昨日よる重複ができない場合は、並び替え、フィルタ機能なども活用し、ひとつずつ削除、調整します。 エクセルでの名寄せ例 利用シーン ECショップを運営する小売店で、ECでの顧客情報と実店舗での顧客情報を統一し、顧客マスターを作成する。 データサンプル リストA：ECショップの顧客情報 リストB：小売店の顧客情報 名寄せ後リストイメージ 名寄せ方針 名寄せ対象：リストA,B 使用ファイル（ダウンロード） 同じ人の条件：氏名、生年月日が同じ場合 使用データ：双方にデータがある場合には、リストAのデータを残す その他： 名寄せ後のリストに統一会員番号を付与する。 また、会員番号A、会員番号Bも残すようにする。 また、データのない人は空白として作成する。 リストAにしかない「直近購入日」、リストBにだけある「性別」「保有ポイント」は、残さない。 リストBにある「姓（カタカナ）」、「名（カタカナ）」は、全角スペースを区切り文字とし、「氏名（カタカナ）」として残す。 エクセルの作業 データ確認 統合対象となる列の状況を確認します。 生年月日は、同じ用に見えますが、リストAの生年月日がシリアル値、リストBの生年月日が文字列として保存されています。 氏名は、リストAが1列、リストBが2列で作成されています。 生年月日：リストBの生年月日をシリアル値に変換 リストBの生年月日をシリアル値に変換 リストBの生年月日が文字列ですので、文字列として格納された日付を、Excel で日付として認識できるシリアル値に変換するDATEVALUE 関数を使用します。 1 データの右に「生年月日（結合用）」といった名前の列（S列、列名は任意）を作成します。 3 シートの設定によっては、シリアル値が表示されますので、その場合は、人の目でも分かるように、表示形式を日付形式に変更します。 4 関数を入力したセルS2をデータの一番下までコピーします。 1 データの右に「氏名（結合用）」といった項目名の列（T列、列名は任意）を作成します。 「重複の削除」で残るデータは一番上の行が残り、2番目以降のデータは削除されます。 今回の場合は、リストAのデータが残り、下にコピーしたリストBのうち重複したデータが削除されました。 会員番号Bの追加 今回の方針は「会員番号A、会員番号Bも残すようにする。 」でしたので、重複で、削除されたリストBの会員番号Bを追加します。 今回の追加の作業は、VOOLKUP関数を使用して行います。 まず、重複を削除した列「氏名」「生年月日」と「会員番号B」の入ったシートを作成します。 VOOLUP関数の参照値は、一番左の列のデータを参照する関数のため、「氏名」「生年月日」を結合した列を「会員番号B」の列より左に作成します。 今回の生年月日はシリアル値で、保存されているため、単純に列結合を行った場合は、シリアル値が表示されます。
次の
姓と名前を結合するには
名前などを分割するとき、ふりがな情報が必要なケースでは します。 関数で取り出したものにはふりがな情報が欠落します。 名前の例で姓と名がスペースで区切られているケース• 半角のスペースで区切られている場合• FIND " ",A1 と検索文字列に半角スペースを入力します。 半角スペースの位置は3となるので、左から半角スペースの前までの文字を取出せばよいので、FIND " ",A1 -1と1を引きます。 取出す文字数はLEN A1 -FIND " ",A1 と計算しても良いのですが、後ろの制限はないのでLEN A1 とA1の文字数を使っています。 全角のスペースで区切られている場合• 半角スペースのケースと同様です。 FIND " ",A1 と検索する文字列に全角スペースを入れます。 半角スペースと全角スペースが混在して区切られている場合• 半角スペースか全角スペースに統一すればよい。 SUBSTITUTE A1," "," " で半角スペースに統一します。 別解1：氏名が漢字とひらがなおよびカタカナの全角文字列であるのなら、ASC関数を使うことも出来ます。 A1:A4セルを選択して、[データ]タブのデータツール グループにある[区切り位置]を選択します。 A,B列に分割されました。 区切り位置では半角スペースと全角スペースは区別されないことが分かります。 関係ないことですがFIND関数で検索する文字列がない場合はエラーとなります。 エラーを返したくない時は被検索文字列に検索文字列を付加してやればよいでしょう。 フラッシュフィルはExcel2013以降で使用できる機能です。 分割したい文字 苗字 をH1セルに入力して、[データ]タブの[フラッシュフィル]をクリックします。 I2セルには名前を入力して、[データ]タブの[フラッシュフィル]をクリックします。 CONCATENATE関数を使う例です。 CONCAT関数を使うこともできます。 このCONCAT関数はExcel2016 Office365 ,Excel2019で使用することができます。
次の