１．はじめに
業務でExcelファイルから会社情報を抽出することになりました。このExcelファイルは100Sheetを超えており、各Sheetのセル(B2)に会社名、セル(B3)に担当者が記載されています。この環境はマクロが禁止されており、VBAが使用できません。こんな場合に、他の開発言語を使用せず、Excel関数で簡単に会社情報を抽出する方法の備忘録です。
２．会社情報の取得元のExcelファイル
今回は５Sheetを準備し各Sheetのセル(B2)に会社名、セル(B3)に担当者が入力されています。
それでは、Excel関数でデータを取得します。
３．手順の流れ
下記の手順で会社情報を取得します。
３－１．作業用のsheetを追加
３－２．Sheet名を取得
３－３．会社名を取得
３－４．担当者を取得
３－１．Excelファイルに作業用のシートを追加
「⊕」をクリックし作業用のSheetを追加します。
後続の手順でこのSheetにExcel関数を入力し会社情報を取得します。
シート：Sheet6が追加されました。
今回、データ取得する下記ヘッダー項目を入力します。
セル(A1)に「sheet名」
セル(A2)に「会社名」
セル(A3)に「担当者」
を入力します。
３－２．Sheet名を取得
「名前の定義」を使用してSheet名を取得します。
「数式」タブ→「名前の定義」をクリックします。
各項目に入力します。
・名前に「sName」を記入します。（今回はsNameを記入しましたが任意の文字でOKです）
・参照範囲に「=GET.WORKBOOK(1)」を入力します。
設定したSheet名を取得する変数(sName)を使用しsheet名一覧を取得するためセル(A2)に「=REPLACE(INDEX(sName,ROW(A1)),1,FIND(“]”,INDEX(sName,ROW(A1))),””)」を入力します。
「Sheet6」が表示されるまでオートフィルします。
※マウスポインターをセル(A2)の右下に合わせ、マウスを右クリックしたまま下に引っ張ります。
「Sheet6」は作業用Sheetで不要になり、「#REF!」はSheetが存在せずエラーが発生しているため削除します。
３－３．会社名を取得
各Sheetの会社名を取得するためセル(B2)に「=INDIRECT(“‘”&A2&”‘!B2”)」を入力します。
上記関数は、「C01」Sheetのセル(B2)の値を取得しています。
セル(B2)を6行目までオートフィルします。
※マウスポインターをセル(B2)の右下に合わせ、マウスを右クリックしたまま下に引っ張ります。
３－４．担当者を取得
３－３．会社名を取得と同様に各Sheetの担当者を取得するためセル(C2)に「=INDIRECT(“‘”&A2&”‘!B3”)」を入力します。
上記関数は、「C01」Sheetのセル(B3)の値を取得しています。
セル(C2)を6行目までオートフィルします。
※マウスポインターをセル(C2)の右下に合わせ、マウスを右クリックしたまま下に引っ張ります。
４．まとめ
Excel関数を使用し複数Sheetの同一項目の値を取得しました。この方法であれば、多くのSheetが存在しても時間が掛からず、簡単に値を取得できますね。これでマクロが禁止されている環境でも問題ないですね。
コメント