Excelで消費税抜きの価格を計算する方法について

Excelで商品管理を行っています。
現在商品の価格設定が全て消費税込みなっています。
税込み価格から税抜き価格に変更する方法を教えて下さい。
いろいろインターネットを検索しましたがわかりやすい方法がございません。
Excelは不慣れなので今のところ入力しか出来ません。
アイテム数は５０００件あるので一括で変更をしたいと思っています。
価格はJ列に入っています。
J列の一行目には「商品価格」と入力してあります。
小数点以下は切り上げにします。
よろしくお願いいたします。

No.1 回答者： kagakusuki 回答日時： 2014/02/12 21:05

　まず、税抜き価格を表示させたい列の2行目の所のセルに、次の様な数式を入力して下さい。

(消費税が5％の場合)

=IF(ISNUMBER($J2),ROUNDUP($J2/1.05,0),"")

　次に、以下の様な操作を行って下さい。

その数式を入力したセルを右クリック
　　↓
現れた選択肢の中にある[コピー]をクリック
　　↓
税抜き価格を表示させたい列の3行目の所のセルを選択
　　↓
税抜き価格を表示させたい列の(表の枠線が引かれている範囲内における)一番下のセルにカーソルを合わせてから、[Shift]キーを押しながら、マウスの左ボタンをクリック
　　↓
選択範囲を示す黒い太枠の内側にカーソルを合わせてから、マウスを右クリック
　　↓
現れた選択肢の中にある[貼り付け](ExcelのバージョンがExcel2007以降の場合は、書類が張られたクリップボードのアイコン)をクリック

　これで、消費税抜きの価格が表示されます。

この回答への補足

早速の回答ありがとうございます。
試してみたのですが、「循環参照に関する警告」というのが出てしまい実行出来ません。

Excelのバージョンは2007です。
=IF(ISNUMBER($J2),ROUNDUP($J2/1.05,0),"")の括弧の部分は緑色、＄12の部分が青色になっております。
それ以外は黒で表示されております。

数式の入力に問題があるのでしょうか。

お手数でございますがご連絡をいただければ幸いです。
よろしくお願いいたします。

補足日時：2014/02/13 11:53

No.2 回答者： bunjii 回答日時： 2014/02/12 23:08

>税込み価格から税抜き価格に変更する方法を教えて下さい。

>小数点以下は切り上げにします。
消費税は円未満を切り捨てになりますので本体価格は繰り上げにする必要があります。
論理的には次のような計算です。

本体価格=税込価格-消費税
消費税=税込み価格*消費税率/（100+消費税率）

∴　本体価格=税込価格-（税込価格*消費税率/（100+消費税率））
∴　本体価格=税込価格*（100/（100+上皮税率）

Excelの関数はROUNDUP（計算式,0）とすれば小数点以下繰り上げになります。
尚、ROUNDDOWN（計算式,0）とすると小数点以下を切り捨てます。
基本的な条件を間違えないようにしてください。
端数と言えども重なれば大きな額になります。

No.3 回答者： kagakusuki 回答日時： 2014/02/13 04:42

>Excelは不慣れなので今のところ入力しか出来ません。

との事ですので、一応念のために申し上げておきますが、回答No.1に書かれている操作は、難しくて面倒に思われるかも知れませんが、最初に1回だけ済ませておくおくだけで、後はJ列に「商品価格」を入力（或いは変更）して行くだけで、税抜き価格が自動的に表示される様になるというものです。
　「商品価格」を入力するたびに同じ操作が必要になる訳では御座いません。
　尚、将来、アイテム数が現在よりも増えるなどして、表の行数(枠線が引かれている範囲)を増やす必要が生じた場合には、回答No.1の

>　次に、以下の様な操作を行って下さい。

という箇所以降の所に書かれている操作を追加で行って下さい。
　その際、

>税抜き価格を表示させたい列の3行目の所のセルを選択

という操作の所は、3行目から選択する必要は無く、新たに増やした行(未だ数式がコピーされていない行)の中で一番上の行の所を選択されるだけで結構です。

No.4 ベストアンサー 回答者： kagakusuki 回答日時： 2014/02/13 12:56

>試してみたのですが、「循環参照に関する警告」というのが出てしまい実行出来ません。

　もしかしますと、御教えした数式をJ2セルに入力されたのではないでしょうか？
　この数式は、J2セルに入力されている「商品価格」のデータを基にして、J2とは別のセルに「税抜き価格」を表示させるためのものです。
　ですから、J列の値はそのまま残しておいた上で、J列以外の列に「税抜き価格」を表示させるための欄を新たに設けて、その「税抜き価格」欄の所に回答No.1の数式を入力して頂かねばなりません。

　もし、どうしてもJ列の「商品価格」の所に入力されている価格を、税抜き価格に変更したいという事でしたら、又回答致しますので、補足欄等を使用して、その旨を御伝え願います。

この回答へのお礼

ご丁寧にありがとうございました。
「税抜き価格」を計算することが出来ました。
いろいろインターネットで検索をいたしましたが、教えていただいた数式が一番わかりやすかったです。
不慣れな者にも簡単に出来ました。
この度はお世話になりました
早急に対応していただきありがとうございました。

お礼日時：2014/02/13 18:52