今回は、 Excelのxlookup関数 について書きます。
目次
- Excelのxlookup関数
- 賃金台帳の作成
- 給与明細の作成
Excelのxlookup関数
Excelのxlookup関数は、Excel2019以降のバージョンで使用可能なようですが、とても便利です。look upには、見上げるとか、検索するという意味があります。
たとえば、1人社長が給与計算をExcelで計算し、賃金台帳と給与明細もExcelで作成するとします。
その場合、データを入力するのは、データシートの一ヶ所だけで、自動的に賃金台帳と給与明細も作成できるようにしたいです。同じデータを複数の欄に入力するのは避けましょう。入力ミスの可能性もあり時間もかかります。
こんなイメージです。（データはすべて架空のものです。）これを３つのシートに作成します。
データ
賃金台帳
給与明細
Xlookupを使っても時間はかかりますが、最初だけです。
たとえば、給与計算のように継続して作成することが予想されるものなら、その時間や手間は十分に回収できると思います。
賃金台帳の作成
まずは、データを作ります。入力するのは黒字部分だけです。赤字は計算式です。今回は、別シートに賃金台帳を作成します。データのシートは、Excelのテーブルという機能を使うと便利です。
まずは、セルをクリックして、「=」「x」「l」と入力します。すべて直接入力です。すると上の図のようにXLOOKUP関数が現れますので、「tab」キーを押します。
すると、Excel上で、入れるべき値や範囲がでてきますので、その通り入力します。
まずは検索値です。今回は、年月を検索します。上の図の赤い矢印です。
検索するセルをクリックします。そしてカンマで区切ります。
次に検索する範囲を指定します。データシートの上の図のA列です。上のAの部分をクリックします。
すると上の様になります。シートは、「シート名!」となります。上の図では、賃金台帳のシートを指定していますので、これをデータのシートに変更します。手入力で上書きします。この時、シート名がかな入力となっている場合は、かなで上書きします。
そして、カンマで区切ります。もう一息です。次は、戻り範囲を選択します。
役員報酬は、データシートの上図の矢印の部分です。よって、上の図のBの部分をクリックします。
同様に、「賃金台帳!」の部分を「データ!」に上書きし、最後に「)」と入力して、「Enter」を押せば完成です。戻り範囲より後は、省略しても通常は問題ありません。）
コピペで範囲がずれないようにするには
次に2025年2月も同様にやります。普通にコピペするとこうなります。A列の範囲を検索して、B列の範囲を戻してほしいのにずれてしまいます。B列の範囲を検索して、C列の範囲を戻す式になっています。
そこで、下の図のように、「A:A」を「$A:$A」と書き換え,「B:B」を「$B:$B」と書き換えます。
すると範囲や戻り値がずれることなくコピーされます。
給与明細の作成
給与明細でも、検索値は、年月日です。
たとえば、上の図のように給与明細を作成するなら、上図の矢印部分となります。