Introduction
Ver. all 2010 2013 2016 2019 2021 365
エクセルで送られてきたファイルに、付いているはずの通し番号が付いていなかった・・・。そんな時の対処法です。出席番号や商品番号を元のデータから取得して付けてしまいましょう。
下の画像をご覧ください。
左側の表が元の名簿です。右側が送られてきたファイルだとしましょう。
本来あったはずの番号が削除されてしまっています・・・。
これを元の名簿を参照して付け直しましょう。
関数を使って何とかするにゃ
XLOOKUP関数を使用して番号を振る方法
Office 2021、365 を使用している方は、新しく登場した「XLOOKUP関数」を使用しましょう。
表の加工もほとんど必要なしで簡単に番号を追加できます。
バージョン2019以前を使用している方は、この次の説明「VLOOKUP関数を使用して番号をふる方法」を参照してください。
下のような名簿があったとします。
ある日、他から送られてきたデータが下の状態だったとしましょう。
番号が付いていません・・・。
並べると違いがハッキリ分かりますね。
では、右側のデータに左の名簿を参照して「番号」を付けてみましょう。
まず、「番号」の列が必要です。
列番号の上で右クリック →
［挿入］を選びます。
新しい列が挿入されました。
※右クリックした列番号の左側に挿入されます。
１番上には「番号」などの見出し入力しておきましょう。
ここからが本番です。
新しく挿入した列の名前の左のセルを選択 → ［関数の挿入］ボタンをクリック。
［関数の挿入］画面では、［関数の分類］を［検索/行列］か［すべて表示］に変更して「XLOOKUP」を選びます。
XLOOKUP関数・・・他の表から対応データを探し出し、一致したデータを求める関数
XLOOKUP（ 検索値 , 検索範囲 , 戻り範囲 , 見つからない場合 , 一致モード , 検索モード ）
- 検索値・・・検索する値（一致させたい場所のセル参照や値、文字列）を指定
- 検索範囲・・・検索する（一致させる）相手のデータ全体を指定
- 戻り範囲・・・取り出したい値の範囲を指定
- 見つからない場合・・・一致するデータが見つからなった時に表示したい文字列を入力［省略可］
- 一致モード・・・一致した際の種類を指定［省略可］
- 「0」 一致データが見つからない場合、「#N/A」エラー、もしくは［みつからない場合］で指定した文字列が表示される（既定の設定）
- 「-1」 一致データが見つからない場合、次に小さい値を表示
- 「1」 一致データが見つからない場合、次に大きい値を表示
- 「2」 * 、? 、˜ をワイルドカードとして使用できる
- 検索モード・・・検索の種類を指定［省略可］
- 「1」 データの先頭から検索する（既定の設定）
- 「-1」 データの後ろから検索する
- 「2」 データの先頭から高速な検索を実行する（昇順で並べ替えが行われていないデータの場合はエラーが表示される）
- 「-2」 データの後ろから高速な検索を実行する（降順で並べ替えが行われていないデータの場合はエラーが表示される）
引数（括弧の中身）が６つもある関数なので難しそうなイメージがありますが、後半３つは省略できてあまり使用しない設定なので、実質３つ指定するだけで大丈夫です。
では、その引数の指定です。
最初の［検索値］の場所には、番号を付けたい名前が入っているセルを指定します。
次の［検索範囲］には、今の名前と一致させたい名簿データの名前全体をマウスで引っ張って指定します。
［戻り範囲］は、結果として欲しい「番号」のデータ範囲全体を指定します。
この３つで準備完了。［OK］をクリックします。
名前に対応した出席番号が取り出されました。
後はオートフィル機能を使用して下にコピー。
データを番号順に並べ替えたいときは、
今表示さた番号のどこか１セルだけを選択した状態で、
［ホーム］タブ → ［並べ替えとフィルター］ →
［昇順］を押しましょう。
作業終了です。
最近登場したXLOOKUPを使用することで、簡単に別の表から必要なデータを取り出すことができました。
VLOOKUP関数を使用して番号を振る方法
Excelバージョン2019以前を使用している方は最初に説明した「XLOOKUP関数」が存在しないので、「VLOOKUP関数」を使用して番号を付けましょう。
↓のような名簿があったとします。
他から送られてきたデータを見てみると番号が付いてない・・・。
並べると違いがハッキリ分かりますね。
では、右側のデータに元の名簿から「番号」を付けてみましょう。
まず、準備。「番号」の列が必要です。
列番号の上で右クリック →
［挿入］を選びます。
新しい列が挿入されました。
※右クリックした列番号の左側に挿入されます。
１番上には「番号」などの見出しを入力しておきましょう。
次に、元の名簿データにも少し加工が必要です。
これから使用する「VLOOKUP関数」は参照先データの左端列をデータの一致に使用することになっています。
今回の場合だと「氏名」を一致させたいので、「氏名」が左端にないといけません。
ということで、「番号列」を右側に移動して順番を入れ替えましょう。
まず、移動したA列の列番号をクリックして列全体を選択します。
列の間の緑線の上にマウスを移動して十字矢印が表示されたら、【Shift】キーを押しながら右へ引っ張ります。
これで列全体が右へ移動します。
「氏名」列が左端になりました。
ここからが本番です。「VLOOKUP関数」を使用して名前に一致した番号を引っ張ってきます。
新しく挿入した列の名前の左のセルを選択 → ［関数の挿入］ボタンをクリック。
［関数の挿入］画面では、［関数の分類］を［検索/行列］か［すべて表示］に変更して「VLOOKUP」を選びます。
VLOOKUP関数・・・他の表から対応データを探し出し、一致した行中のデータを求める関数
VLOOKUP（検索値,範囲,列番号,検索の型［省略可］）
- 検索値・・・検索する値（一致させたい場所のセル参照や値、文字列）を指定
- 範囲・・・検索する（一致させる）相手のデータ全体を指定
- 列番号・・・「範囲」で指定したデータの、左から何列目と「検索値」を一致させるかを指定
- 検索の型・・・完全一致させたい場合は「FALSE」、近似値で一致させたい場合は「TRUE」を入力。省略した場合は「TRUE」になる。（FALSEは「0」、TRUEは「1」でも可）
最初の［検索値］の場所には、番号を付けたい名前が入っているセルを指定します。
次の［範囲］は、元の名簿のデータ部分全体を選択して【F4】キーを押して絶対参照にします。
絶対参照にする理由は、後でオートフィル機能を使用する際にセル参照がずれないようにするためです。
※絶対参照の説明が必要な方はこちらをご覧ください。
［列番号］の場所は、今回左から２番目の「番号」のデータが欲しいので「2」と入力します。
最後の［検索方法］は、名前を完全一致させたいので「false」と入力。
※「0」と入力しても「false」の扱いになります。
［OK］を押せば、番号が取り出されます。
後はオートフィル機能を使用して下にコピー。
データを番号順に並べ替えたいときは、
今表示さた番号のどこか１セルだけを選択した状態で、
［ホーム］タブ → ［並べ替えとフィルター］ →
［昇順］を押しましょう。
作業終了です。
別の表から必要なデータを取り出すことができました。
まとめ
他の表から対応データを探し出て取り出したい時は、VLOOKUP関数が活躍します。
最近のバージョンでは、より使いやすくなったXLOOKUP関数が使用できるようになりました。
他のPCなどで使用する可能性のある表の場合は、互換性を考えるとまだXLOOKUP関数は使用しない方が良いかもしれません。
しかし、今回のように自分の次の作業がやり易いようにデータを加工したい場合などは積極的に使用したいですね。
もし、今回使用したVLOOKUP関数を苦手としている方がいらっしゃったら下のリンク先をご覧ください。
Excel必須の関数の一つなので覚えておくのがお勧めですよ。
関数で取り出した後は「値」としてデータを貼り付け直すにゃ