エクセルの文字列向けの関数を用いて指定文字列を探索し、文字列の組み合わせに応じて自動決定するシートの作成方法です。
在庫の管理やシフト配置の決定など応用することができ、管理するときに役立つ関数のまとめです。
作成した対応表に基づきセルに自動入力させる
エクセルの関数で「勤務表や在庫の一覧から特定ワードのみを抽出する」や「抽出した文字列を認識し、あらかじめ作成した対応表から新しいシートへ自動入力する」という機能を紹介していきます。単一の関数のみでは困難ですが、少しずつ活用していくことで面倒な操作も簡単に自動入力することができます。
休日など特定の必要データを抽出する
シフトや在庫の残数など決められたものが割り振られている場合、まずそのシートから何を抽出するのか洗い出します。ここではシフト配置の決定を例に見ていきます。
先にメンバーを個別のセルに入力すると作業しやすくなります。文字列の抽出はIFERROR関数、IF関数、FIND関数を組み合わせて実行します。
注意点はFIND関数の検索範囲は単一セルを参照することです。複数セルを探索する場合は、個別に抽出して結果を結合するようにします。
たとえば抽出したい範囲が2つのセルであれば以下のように書きます。
同一の文字を探すのであれば「”キーワード”」は全て同じセルを参照してください。もし3つ以上なら「&」とIFERRORの条件式を追加していきます。
セルの中から文字列を抽出する方法については詳しくはこちらで解説しています。
抽出したリストと組み合わせの対応表を作成する
抽出したメンバーから、パターン的に配置を決定します。まず振り分けをする前に「この文字列の組み合わせのとき、この結果を出力する」というパターン対応表を作成します。
上の例では1番左の列に抽出した文字列の候補を入力し、右に対応した組み合わせを入力してます。対応表を作るとき、文字列を抽出する条件式の「&” ”」によって名前の後ろに全角スペースがあるので名前の後ろに必ず全角スペースを挿入しましょう。（例：表の1列目は「いちろう じろう 」と入力しています。）
VLOOKUP関数で対応表から必要情報を得る
VLOOKUP関数で、文字列の組み合わせから対応表のどこに一致するのか探索し条件にあったものを検出します。ここでは「いないメンバー」を元に対応表からパターンを識別し水色部分に自動入力するためにVLOOKUP関数を使用します。
VLOOKUP関数の解説
検索値
条件があるか検証したいセルを表します。この場合、いない人のセルを選択してます。
対応表の範囲
見つけたいデータの一覧を指します。この場合、VLOOKUP対応表を全部選択します。
列番号
対応表から表示したい列番号です。担当者1の列を表示するとき、対応表の左から2列目なので、「2」と入力します。
最後のFALSEは完全一致のみ見つけるための検索方法です。
対応表が無く手動で入力する場合
ここまでに書いた方法の欠点は、対応表へパターン化するときに項目数が多すぎると、構築までに労力がかかってしまうことです。やむを得ず手入力で入力する場合は操作ミスを減らすために入力した人数が合ってるか、重複がないかチェックしておきましょう。入力内容の確認方法はこちらを参照してください。
手動で入力を続ければデータとして蓄積され対応表を作れるかと思うのでうまく活用できれば幸いです。
