エクセルの関数計算について。

質問

最近、闘病生活の関係でPCを触らなくなって自信が無いのでアドバイスをお願い致します。

関数計算したいのは、インチ to ミリ変換表の作成です。
１インチ以上は、整数での計算で良いのですが、1インチ未満の時に1/2inch・1/4inchと言うように、セルに書きたいのです。
（inchの文字が邪魔になるなら、諦めます。 ただし、inchの文字を入れないと1/2は0.5に自動変換されてしまいますので、「'1/2」と入力する事になります。）

一様、私が書いたのが、
A1：1/4inch　B1：=LEFT(A1,3)　C1：=LEFT(B1,1)　D1：=RIGHT(B1,1)　E1：=25.4*C1/D1
A2：15/16inch　B2：=LEFT(A2,5)　C2：=LEFT(B2,2)　D2：=RIGHT(B2,2)　E1：=25.4*C2/D2
こんな感じで、セルを複数使う事で計算自体は希望通り出来ています。

ただ見た目が悪いので、A列に＊＊inch（＊＊は分数）として、B列に一行で済む関数計算法はあるでしょうか？？

何卒、ご教授のほどよろしくお願い致します。
使用エクセル（MS office）は、2003・2007です。

インチ計算が必要な理由は、プリント基板のパターン作製と工作機械（旋盤・フライス盤など）のインチ部品・パーツ規格の作成など、早見表の作成をと考えています。

回答No.1

dogs_cats · ベストアンサー

B1式
=LEFT(A1,FIND("/",A1)-1)/MID(A1,FIND("/",A1)+1,LEN(A1)-FIND("/",A1)-4)*25.4

A列が半角英数字である事が前提です。
端数処理をしていませんので、round関数等で有効桁数の調整をして下さい。

FIND("/",A1)-1で/の位置を取得します。-1はスラッシュの前までが必要なので引いています。

LEN(A1)-FIND("/",A1)-4は
LEN(A1)でA1の文字数取得
FIND("/",A1でスラッシュまでの文字数
-4はinchの文字数
文字数からスラッシュまでの文字数と4文字引いた数値が分母の桁数となり、MID関数の取得文字数の指定値としています。

お礼

早々の回答有難う御座います。
シンプルかつ部分的に関数式を抜き出して検証でき、全体の式がどうなているのかとても分かり易いです。

フィートやヤード・ポンドやガロンなど、応用がとてもしやすい式で助かりました。

有難う御座いました。

早速教えて頂いた式を使って、インチtoミリの早見表を作りました。

表には1インチ以上は0.5インチステップで書きましたが、プリント基板のサイズ自体が1インチ未満で、インチねじも1/2インチ以下のネジしか使わないと言っても過言でないので、十分です。

回答No.8

mshr1962

まずはinchの入力セルの表示形式を、分数の2桁増加にしてみては？
そうすれば、1以上は整数で、0.5は1/2、0.25は1/4と表示されます。

それなら計算は単純に
=A1*25.4
で済みます。

現状のままなら
=LEFT(A2,FIND("/",A2)-1)/SUBSTITUTE(SUBSTITUTE(A2,"inch",""),LEFT(A2,FIND("/",A2)),"")*25.4
のようにすれば計算できますけど。。。

補足

回答有難う御座います。

1/2inch・1/4inch・1/8inchとか簡単な分数なら良いのですが、実際入力する分数は、1/32～31/32inchとなります。
当然2/32は1/16で入力しますが、1/32～31/32の分数をエクセルの機能で小数点表示されても、元々の分数値がすぐには分かりません。

セルA1に「'1/2inch」・B1に「0.5」・C1に「=B1*25.4」っと書けば、暗算出来ない小数点でも一目で分かりますが、B1セルは一覧表の邪魔になります。
B列を非表示にする方法もありますが、それなら私が書いた方法でセル数を沢山使い、不要部分を非表示にすれば良いだけですが、私が書いた欠点は、分母・分子共に桁数を調整しないといけません。
その為、いちいち非表示にした部分を再表示させて調整し、また非表示にしないといけません。

mshr1962 さんの回答も同じで、B列を非表示にしても奇数/64を入れる際は、再表示させて奇数/64をエクセルに計算させて、また非表示にしないといけません。

なので、あまり実用的ではありません。

最後に、今までの回答者様のアドバイスで書いた関数計算で早見表をPDFにしました。

エクセルで、一様1/32～31/32inchまでを書きましたが、奇数/64にも使える関数計算式でした。

でも「SUBSTITUTE」も今まで使った事が無いような気がしますので、再勉強いたします。
（昔使っていても、すでに忘れています。　(^^ゞ　）

回答No.7

bunjii

>A1が1/2と表示されたので、B1に=A1*25.4っと入れてみたら、「1076223.4」っと表示されました。
その計算は次のように解釈されますので目的に合いません。
="1/2"*25.4 → "2016/1/2"*25.4 → 42371*25.4 → 1076223.4
つまり、文字列の 1/2 は日付の 2016/1/2 と解釈されるため分数として扱うことがありません。

>「＊/＊inch」っと自分で入れてセル書式に文字列と認識させる事で、使う側のご認識も防げます。
ユーザー定義の表示形式で ＊/＊"inch" と言うパターンは登録できませんでした。
代案としては #0/#0"inch" と言う表示形式を定義されると良いでしょう。
この場合は分数として数値になるようです。

お礼

何度も回答有難う御座います。

セルの書式設定で、下から3っ目の文字列で認識させられますし、分数の後に自分で「inch」と入れる事で、エクセルは文字列と認識しています。

しかし、ユーザー定義の代案として、前回は「@"inch"」を教えて頂き、今回は「#0/#0"inch"」を教えて頂きました。

こちらのユーザー定義を使うと、関数計算が普通の計算式で出来るのは第３者にも分かりやすいと思いました。
ただ個人的にはユーザー定義を「#?/#?"inch"」にしました。

結果は同じなのですが、分数の「/」文字が入っているせいか「０」にする必要がありませんでした。

計算結果の「mm」のセルには、インチ値を入れていない時（もしくは０の時）に「0.mm」と表示させる為にユーザー定義を「#0.####"mm"」としています。
「##.####"mm"」とした場合は、インチ値を入れていない時は「.mm」と表示される為に「#」を「0」にしています。
「#0/#0"inch"」の「0」にする必要は無いみたいで「#?/#?"inch"」・「#0/#0"inch"」の結果は同じでした。

ちなみに、「##/##"inch"」でも良いみたいです。
「#?/#?"inch"」にした理由は、標準のユーザー定義に「# ??/??」と言うのがあったので、まず「??/??"inch"」にしてみましたが、分数値の表示と「inch」の表示位置が微妙に違ったので、今回は「#?/#?"inch"」を選んでみました。

でも1つ欠点があります。
3/32を3/64とか3/16を5/16と書きかえる時にセルを選択すると小数点以下全部が表示されて、分数の一部分を変えると言う感じでは無かったですね。

凄い良い回答と思ったのですが、オートフィルの連続データでも分子が加算されていきます。
文字列で認識していると分母は加算していきます。

なかなか・まだまだ、エクセルも難しですね！！
PCを使う頻度が減るにつれて、どんどん忘れて行ってしまいます。

また違う方法を思い付きましたら教えて下さい。

回答No.6

msMike

以下のステップを忠実に試してみて、感想をお聞かせください。
１．列Ｂを“ユーザー定義”で
　　　　　G/標準"mm"
　　に書式設定
２．セル B1 に式 =A1*25.4 を入力して、此れを下方(セル B2)にオートフィル
　　（→何れも 0mm と表示されるが、最終的に表示値が変化するのが楽しみ）
２．セル A1 を“ユーザー定義”で
　　　　　# ?/4"inch"
　　に書式設定
３．セル A2 を“ユーザー定義”で
　　　　　# ?/16"inch"
　　に書式設定
４．セル A1、A2 にそれぞれ 1/4、15/16 と入力

お礼

まだまだ、アレンジが必要です。

計算結果が小数点位置に関係なく4桁しか数字が表示されない。
他多数の問題点あり。

小数点以下4桁以上必要なのは、旋盤でネジ切りをする際にギア（歯車）の組み合わせでインチねじ用に調整します。
しかし、日本で売られている大半の旋盤は厳密なインチねじ用の山数に出来ません。
それは、ネジ切りに使う部品の中にはインチねじを使っておらず、またギアの組み合わせに「0.254の倍数：１」のギア比が無いからです。

例えば、24山のねじピッチはミリ換算で25.4/24=1.0583333333333333333333333333333
となります。
私の旋盤で作れる一番近いギア比は、ピッチ幅1.0546875mmです。

だいぶズレていると思う方もいるかも知れませんが、ナットの幅が10mm位ならネジとナットには必ずあそび（バックラッシュ）があるので、普通に使えます。
ただし、長ネジに長ナットという組み合わせでは、長ナットの長さとネジ径の太さに比例してつっかえて使えない事があります。

旋盤でインチねじ用ギアセットと言う物があっても、個人向で購入できる物は大半が厳密なインチねじを作れません。
工場向けの数百万円以上する物は、きっちりインチねじ山が切れるオプションがあります。

個人向で「0.254の倍数：１」のギア比を作ろうと思うとかなりの費用がかかります。
旋盤を2台買ってミリねじ用と厳密なインチねじ用を使い分けた方が、良いかもしれません。

旋盤ミリねじタイプを改造してインチねじに付け替えるか、ギア（歯車）の構造からタイミングベルト及びプーリーに変えないといけません。
タイミングベルトなら、例えば25.4：20という特殊サイズのプーリーを作る必要があります。
プーリーの作成は手持ちの旋盤で作ろうと思えば作れますが、普通のゴムバンドプーリー３：１用でもなかなか難しいです。（厳密には）
それを、タイミングベルト用プーリーで、25.4：20という小数点以下の調整もしないといけないのです。

補足

もしかして私の質問の仕方が悪かたのでしょうか？？
msMike さんには、分かりにくい書き方をしたでしょうか？？

回答No.5

bunjii

>ただ見た目が悪いので、A列に＊＊inch（＊＊は分数）として、B列に一行で済む関数計算法はあるでしょうか？？
A列は1インチ未満の値を分数で文字列として入力されていることが条件とすれば次の下記の数式で良いでしょう。
但し、A列の表示形式はユーザー定義で @"inch" としてA1へ 1/2 と入力してください。
B1=LEFT(A1,FIND("/",A1)-1)/MID(A1,FIND("/",A1)+1,LEN(A1))*25.4
A1が 1/2inch のとき
B1=LEFT(A1,FIND("/",A1)-1)/MID(A1,FIND("/",A1)+1,LEN(A1))*25.4 → "1"/"2"*25.4 → 12.7
A2が 15/16 のとき
B2=LEFT(A2,FIND("/",A2)-1)/MID(A2,FIND("/",A2)+1,LEN(A2))*25.4 → "15"/"16"*25.4 → 23.8125
計算結果を小数点以下何桁で丸めるかを考えるときはROUND関数で四捨五入してください。
小数点以下3桁目を四捨五入して有効桁数を小数点以下2桁するときは以下のようになります。
B2=ROUND(LEFT(A2,FIND("/",A2)-1)/MID(A2,FIND("/",A2)+1,LEN(A2))*25.4,2) → 23.81

お礼

回答有難う御座います。
No1.のdogs_catsさんと同じ回答ですが、書式設定をいじったパターンですね！！

でも、ユーザー定義で @"inch" としてA1へ 1/2 と入力した後すぐに、A1が1/2と表示されたので、B1に=A1*25.4っと入れてみたら、「1076223.4」っと表示されました。
A2に書式設定をいじらずに1/2と入れてみると、「2016/1/2」となりました。

計算式は、書式設定をいじった分少し短いですが、自動的に日付として認識されるので、他人が触る事がある場合は勘違いを招く可能性がありますね！！
「＊/＊inch」っと自分で入れてセル書式に文字列と認識させる事で、使う側のご認識も防げます。
（面倒くさいけど、初歩的ミスよりかは良いです。）

ROUND関数の方は逆に書式設定をいじりました。
ユーザー定義を「#0.####"mm"」として、小数点以下5桁目を四捨五入で「mm」を追加しました。
やっぱり「inch」を入れるのだったら、「mm」も入れないとね！！

でも、有難う御座いました。
セル内文字の自動認識による計算ミスが起こりえる事を、再確認出来ました。

回答No.4

noname#228894

小難しく考えず、至ってシンプルにセルの書式設定で「分数」を選んでおけばそれで済む話ではないのでしょうか？以下URLのように。これなら換算式は整数分数を問わず、全て25.4を掛けるだけで済んでしまいます。わざわざinchと入れる必要もなければ、分母の桁数が違う時の場合分けを考える必要もありません。

http://kokodane.com/tec1_13.htm

唯一困るのが、約分されちゃうこと。配管で「はいはい6分ね6/8…あれ？」なんてことになります。そこだけ注意していれば。

補足

プリント基板のパターン作製では、最少0.1mmの管理が必要で、IC用の基板穴は0.1インチステップが一番多く、プリント基板用CADでは2.54mm（0.1インチ）を２のx条で割った数値で作画しています。

また、インチねじはタップを買う時はインチサイズで購入するのですが、タップ用の下穴をあけるドリルはミリ単位ドリルを使います。
インチ単位ドリルなんて国内ではなかなか手に入りませんし、売っていても半端なく高いです。

Webではインチねじを、ミリねじに見たって紹介されている方もいるくらい、大雑把な方もいます。

例：5/16インチ-24山ネジを、M8-P1.0とほぼ同じ。

旋盤でインチねじを自作したりする時の、早見表を分数で31パターン書くのに、よりシンプルに計算できる筈だけど、日々忘れて行くので教えを乞うたのです。

そして、一度教えて頂いた情報をしっかり勉強していく事で回答者さんへの感謝の気持ちとしています。

私も数年前までは、回答者側で質問者の納得のいく回答を必死になって探した物です。

OKwaveはそういう場所だと私は思っています。

回答No.3

kagakusuki

回答No.2です。
　先程の回答では書き忘れておりましたが、先程の回答で提示させて頂いた関数を用いる場合には、整数値でのインチサイズを入力する場合にも、

1inch

や

12inch

等の様に、末尾に"inch"を忘れずに付ける様にして下さい。

回答No.2

kagakusuki

「1と1/4インチ」(1.25インチ)や「11と3/8インチ」(11.375インチ)の場合に、どの様な文字列を入力するおつもりなのか解りませんでしたので、「整数＋分数」インチの場合には対応させてはおりませんが、

>１インチ以上は、整数での計算で良いのですが

という事であれば、その様なパターンは存在しないものと考えて宜しい訳ですね。
　それで宜しければ、インチでの表記を表す文字列が入力されているセルが、例えばA1セルであった場合には、次の様な関数となります。

=IF(ISNUMBER(1/LEFT($A1,FIND("/",$A1)-1)/REPLACE(LEFT($A1,FIND("inch",$A1)-1),1,FIND("/",$A1),))+ISNUMBER(1/LEFT($A1,FIND("inch",$A1)-1)),25.4*IF(ISNUMBER(FIND("/",$A1)),LEFT($A1,FIND("/",$A1)-1)/REPLACE(LEFT($A1,FIND("inch",$A1)-1),1,FIND("/",$A1),),LEFT($A1,FIND("inch",$A1)-1)),"")

お礼

早々の回答誠に有難うございます。
分数 数値だけでなく、小数点以下の数字が含まれた数値にも対応しているのには驚きました。

ただ、IS関数式を自分で書いた事が無いので、これから勉強させて頂きます。

今回は１インチ以上は普通の掛け算にしました。
でも今後10インチ以上で、切の悪い長さの時は今回の式を使わせて頂くと思いますので、本当に有難う御座いました。
大事に、データ保管しておきます。

今回作りましたインチtoミリの早見表です。

実は、「シンワ」と言うメーカーのステンレス定規の裏に早見表が刻印されているのですが、老眼のせいで虫眼鏡が無いと読めないので、自分で早見表を書く事にしました。

補足

＞「整数＋分数」インチの場合には対応させてはおりませんが、、、
でも、整数＋分数＝分数で入れてみた所、問題無く計算されました。
有難う御座いました。