ecoslymeです。
エクセル（Excel）の数式を利用することは多くあると思いますが、別セルや別シートにある数式を他のセルに反映（転用）したいと思ったことはありませんか？
今回は結構マニアックな需要に対しての方法となります。
別シートに「○○マスタ」を作り、この場合はこの数式を使用したいという場合に利用することができます。
少し特殊なことをしなければなりませんが、以下の記事を参考にしてご利用頂ければと思います。
当記事の最後の方でエクセルのダウンロードが可能です。
元データと結果
＜商品の在庫明細＞
＜商品 品質保証期間マスタ＞
⇓
＜商品の在庫明細と品質保証期限＞
別セル・別シートの数式を他のセルに利用（転用）する方法の課題
それでは早速、別セル・別シートの数式を他のセルに利用（転用）する方法についてご紹介します。
マスタを作成して、別シートの明細にマスタで利用している数式を転用する方法をご紹介します。
言葉だけだと分かりづらいので、以下の例題を考えます。
・「商品明細」には、ロット番号の項目があります
・「品質保証期限マスタ」には、商品毎に異なる採番をしているロット番号があります
これが実は簡単に品質保証期限を出すことができません。
以下のような課題があります。
- 数式を使用したいが、商品コードによって使用する数式が異なる
- 用意している数式（品質保証期限マスタ）には既にセルの名前（例：C4）が書かれてしまっている
- 行が変わるため、セルの行を変更しなければいけない
- 数式をコピペしてきても、数式がそのセルで使えない
これらの意味と、課題の解決方法についてご紹介していきます。
数式を使用したいが、商品コードによって使用する数式が異なる
以下のL列（製造年）とO列（製造月）をに数式を入力したいですが、マスタを見れば分かるように3商品各々計算式が異なります。
そのため、特定の数式を入れて下にその数式をコピペしていくことができません。
用意している数式（品質保証期限マスタ）には既にセルの名前が書かれてしまっている
上で表示しているマスタには既に数式を入力しており、「セル」という言葉で置き換えていますが、実際には、C3、C4、C5というセルの名前が書かれてしまっています。
この数式を転用してしまうと、商品明細のどの商品の行でも全て「C4」セルを参照する、という数式になってしまいます。
行が変わるため、セルの行を変更しなければいけない
仮に、その商品に適切な数式をJ列に持ってきたとしても、C4（ロットNo）と書かれてしまうので、これをC5（810001）へと変更する必要があります。
数式をコピペしてきても、数式がそのセルで使えない
上図のように、J列に数式だけを持ってきましたが、「=J5」とやっても数式が反映されません。
「=J5」と入力しても同じ文字（LEFT(C5,1)+2000）が出てくるだけです。
以上で簡単に説明しましたが、今回のケースがどれほど難しいのか分かったかと思います。
別セル・別シートの数式を他のセルに利用（転用）する方法
では、別セル・別シートの数式を他のセルに利用（転用）することは出来ないのでしょうか？
そのようなことはありません。
少し面倒ですが、以下のステップを踏めば数式を利用することができます。
汎用性の高い方法なので、今回の例に限らず利用してもらえればと思います。
順を追って手順を説明していきます。
- 必要項目の列を追加する
- 数式で参照すべきセルを特定する（Row関数、Concatenate関数）
- 品質保証期間、製造年利用数式、製造月利用数式を特定する（Index関数、Match関数）
- セルを置換する（Substitute関数）
- 製造年、製造月を特定する（Evaluate関数）
- 製品製造日を特定する（結合）
- 品質保証期限を特定する（Date関数）
必要項目の列を追加する
以下のように、下記の列を作成してください。
＜追加する列＞ ※以下の図の黄緑色セル
- 列
- 行
- セル
- 製造年利用数式
- 置換（年）
- 製造月利用数式
- 置換（月）
数式で参照すべきセルを特定する（Row関数、Concatenate関数）
次に、参照すべきセルの「列、行、セル」を特定していきます。
C列のロットNoが今回のキー項目なので、参照すべきセルの列はロットNoのC列となります。
列（F列）には全て”C“と入力しましょう。
行（G列）は、以下の数式を入力します。
「=ROW()」
セルは、列（F列）と行（G列）の組み合わせなので、以下の数式を入力します。
「=CONCATENATE(F5,G5)」
あとは下へ数式をコピペしていけばOKです。
品質保証期間、製造年利用数式、製造月利用数式を特定する（Index関数、Match関数）
次に、別シート（品質保証期限マスタ）から、以下の項目を抽出してきます。
- 品質保証期間
- 製造年利用数式
- 製造月利用数式
これは、Index、Match関数を利用すればOKです。
例えば、I5セル（品質保証期間）には以下の数式を入力します。
「=INDEX(品質保証期限マスタ!$A$4:$D$56,MATCH(A5,品質保証期限マスタ!$A$4:$A$6,0),4)」
Index関数、Match関数について詳しく知りたい方は以下の記事をご覧ください。
ecoslymeです。エクセル（Excel）で、縦に項目（PL勘定など）、横に推移の数字（年月など）があり、特定の年月の数字だけ見たかったり、抽出したかったりする時ってありませんか？ 地道に探してコピペして[…]
同様に、J列とM列にもIndex関数、Match関数を使用します。
J4セル：「=INDEX(品質保証期限マスタ!$A$2:$F$6,MATCH(A5,品質保証期限マスタ!$A$2:$A$6,0),5)」
M4セル：「=INDEX(品質保証期限マスタ!$A$2:$F$6,MATCH(A5,品質保証期限マスタ!$A$2:$A$6,0),6)」
そして、下へ数式をコピペすれば、以下のようになります。
これで、利用すべき数式が適切な行に反映されました。
セルを置換する（Substitute関数）
次に、「セル」の文字を正しいセル（例：C5）へ置換します。
置換の列には、以下の数式を入力してください。
K5セル：「=SUBSTITUTE(J5,”セル”,H5)」
N5セル：「=SUBSTITUTE(M5,”セル”,H5)」
単に、「セル」という文字を、参照すべきセルへ変換しているだけです。
あとは数式を下にコピペして下さい。
製造年、製造月を特定する（Evaluate関数）
次に、一番の強敵である「数式を別セルから転用する」方法について説明します。
これは、エクセルのマクロ機能を利用する必要があります。
ですが、マクロコードを入力するものではなく、以下の手順に沿って行うだけで結構です。
エクセルの「数式」タブから「名前の定義」を選択し、「名前の定義」を選択する。
以下の通り入力する。
- データ範囲の名前を入力してください：「evaluate」
- セルの範囲を選んでください：=EVALUATE(INDIRECT(ADDRESS(ROW(),COLUMN()-1,4)))
上の赤文字の「-1」ですが、「左の数式を転用する」という意味であり、セルの右の数式を転用したい場合は「1」と入力します。
“4”は特に気にしないでください。
その後、転用したい数式の右に「=evaluate」と入力すれば良いだけです。
製造年と製造月に入力して下さい。
同様に数式を下へコピペして下さい。
製品製造日を特定する（結合）
次は簡単です。
上で、製品の製造年、製造月が出せたので、結合して製品製造日を特定するだけです。
製造日は今回は簡便のため全て”1日”に製造されたものとします。
強引に、年と月と日を結合します。
製造年月日のセルに以下の数式を入力して下さい。
「=L5&”/”&O5&”/1″」
また同様に数式を下へコピペします。
品質保証期限を特定する（Date関数）
ようやく最後になりました。
最後は、製造年月日に品質保証期間を足し算するだけです。
しかし、製造年月日は年月日の表記で、品質保障期間は月だけの表記です。
例えば、18ヶ月を2019/1/1に足し算して、2020/7/1にしなければなりません。
以下の数式を品質保証期限の列に入力して下さい。
「=DATE(L5,O5+I5,1)」
Date関数を利用すれば、「年、月、日」の順に表記して、足し算したい部分だけ足し算することができます。
数式を下へコピペすれば完成です。
結構面倒でしたが、1回作ってしまえば後は数式を下へコピペすることと、マスタの整備をすれば良いだけです。
エクセルのダウンロード
今回作成したエクセルをダンロードすることができます。
以下のリンクをクリックして下さい。
マクロ形式でのダウンロードができないため、一度ダウンロード後、エクセルを保存するときに「.xlsm」形式で保存し直して下さい。
最後に
いかがでしたでしょうか。
今回の例は非常に難しかったと思います。
ですが、エクセルをこちらで提供させて頂いているエクセルに少し修正を加えれば、便利に実務へ生かすことができると思います。
特にこの例の品質保証期限の管理に関しては、少なからず便利と思う営業マンはいるでしょう。
エクセル技術習得のススメ
エクセルに興味のある方は是非、ご覧ください。
エクセル技術習得にオススメの本
エクセルの技術を習得するのに非常にオススメの本をご紹介します。
手元に1冊あって損はない本です。
仕事効率が劇的に改善します。
パソコン講座
エクセルに技術力の資格があるのはご存知ですか？
MOS（Microsoft Office Specialist）と言いますが、社会的に非常に評価されている資格です。
資格を取得すれば社内で評価されることはもちろん、転職でも評価される場合があります。
ご興味ある方は以下の講座を受講してみてはいかがでしょうか。
＜ヒューマンアカデミー＞
MOSのテキストと問題集です。
マクロ
マクロを学びたい方は以下の参考書が非常に便利です。
本は太いですが、非常に広い範囲を網羅しています。紙の本の購入をオススメします。
エクセル勉強サイト
経理で利用するエクセルを学べるサイトをご紹介します。
初月無料で経理の仕事に役立つ150以上の動画が学べるサイト【Accountant’s library】 
