この関数式の改良法について教えてください。

質問

旅費精算の事務をしているものでして、以下のような状況で急行券の代金を算出するために次の関数式を作りました。

H22繁忙・閑散：日にちが行でグループ分けして書いてあります。
運賃表：繁閑によってプラス200、マイナス200の調整をする駅の名称、路程、乗車賃、急行券等が書いてあります。
運賃表(2)：繁閑によってプラス400、マイナス400駅の名称、路程、乗車賃、急行券等が書いてあります。
I列：出発日です。
L列：到着駅です。

=IF(I5="","",IF(COUNTIF(H22繁忙・閑散!$A$1:$H$24,I5)>=1,IF(COUNTIF(運賃表!$A$2:$N$286,L5)>=1,VLOOKUP(L5,運賃表!$A$2:$N$286,5,0)-200,IF(COUNTIF(運賃表(2)!$A$2:$N$286,L5)>=1,VLOOKUP(L5,運賃表(2)!$A$2:$N$286,5,0)-400,"")),IF(COUNTIF(H22繁忙・閑散!$A$25:$H$46,I5)>=1,IF(COUNTIF(運賃表!$A$2:$N$286,L5)>=1,VLOOKUP(L5,運賃表!$A$2:$N$286,5,0),IF(COUNTIF(運賃表(2)!$A$2:$N$286,L5)>=1,VLOOKUP(L5,運賃表(2)!$A$2:$N$286,5,0),"")),IF(COUNTIF(H22繁忙・閑散!$A$47:$H$67,I5)>=1,IF(COUNTIF(運賃表!$A$2:$N$286,L5)>=1,VLOOKUP(L5,運賃表!$A$2:$N$286,5,0)+200,IF(COUNTIF(運賃表(2)!$A$2:$N$286,L5)>=1,VLOOKUP(L5,運賃表(2)!$A$2:$N$286,5,0)+400,""))))))

この関数でできることを変えずに、短くすることはできるでしょうか？
そして、2列に分けて処理せずにこの関数式から出された数字が200以下だった場合に、算出しているセルに何も表示させないようにするには、どうすればいいでしょうか？
officeのバージョンが2003なのでこれ以上長くするのには限界があるのです。（上記関数式（ここではAAAとします。）とすると、
IF(AAA<200,"",AAA)というような感じで入力しようとしましたが、長すぎてだめでした。）

詳しい方、アドバイスお願いいたします。

回答No.6

grumpy_the_dwarf · ベストアンサー

> 意味を教えていただけるとありがたいです。
=if(I5*countif(運賃表!$A$2:$O$286,L5),
vlookup(L5,運賃表!$A$2:$O$286,5,0)
+vlookup(L5,運賃表!$A$2:$O$286,15,0)*(countif(H22繁忙・閑散!
$A$1:$H$24,I5)-countif(H22繁忙・閑散!$A$47:$H$67,I5)),
"")

まずは
=COUNTIF(H22繁忙・閑散!$A$1:$H$24,I5)
-COUNTIF(H22繁忙・閑散!$A$47:$H$67,I5)

同じ日付が何度も現れるようなドジを踏んでない限り、繁忙期なら
COUNTIF(H22繁忙・閑散!$A$1:$H$24,I5)が1でCOUNTIF(H22繁忙・閑
散!$A$47:$H$67,I5)が0、閑散期ならその逆、通常期ならどちらも
0。ここまでは判りますか。そうすると、COUNTIF(H22繁忙・閑散!
$A$47:$H$67,I5)のところはマイナスになっているので、全体では
私の最初の回答に書いた通り、「繁忙期は1、閑散期は-1、通常期
は0」となるわけで、O列に書いてある調整額にかけ算するんです。

=if(I5*countif(運賃表!$A$2:$O$286,L5),
vlookup(L5,運賃表!$A$2:$O$286,5,0)
+調整額*(繁忙期は1、閑散期は-1、通常期は0),
"")

vlookup(L5,運賃表!$A$2:$O$286,5,0)
これは運賃表で乗車賃を検索しているだけだからいいですね。

=if(I5*countif(運賃表!$A$2:$O$286,L5),
乗車賃+調整額*(繁忙期は1、閑散期は-1、通常期は0),
"")

if(I5*countif(運賃表!$A$2:$O$286,L5),
I5は日付です。excelでは日付は基準日から何日目かという数値に
ほかなりません。また、何も入力されてないセルを四則演算に使う
と、ゼロとして扱います。countif(運賃表!$A$2:$O$286,L5)はもち
ろん運賃表に該当する駅があれば1でなければ0ですね。するとこの
かけ算は、日付が入力されていて行き先の駅が運賃表に載っている
ときだけ「ゼロではない値」になり、どちらか一方でも欠けるとゼ
ロになるんです。そしてExcelの関数では、if関数の条件式などの
論理値TRUE/FALSEがあるべきところに数値を入れた場合、ゼロなら
FALSEでそれ以外はTRUEとして扱うことになっています。よってこ
こは「日付が入力されていて行き先の駅が運賃表に載ってれば」と
いう条件式を不等号もand関数も使わずに書いたんです。

=日付が入力されていて行き先の駅が運賃表に載ってれば,
乗車賃+調整額*(繁忙期は1、閑散期は-1、通常期は0),
"")

というような式でした。

お礼

すばらしいですね。
できました。
ありがとうございました。

回答No.5

tom04

こんばんは！
色々アドバイスが出ていますので
重複すると思いますが・・・

数式を見させてもらうと他の方々が仰っていらっしゃるように「名前定義」すれば数式はもっと短くなると思います。

まず、運賃表Sheetの$A$2:$N$286セルを範囲指定し、
→　メニュー　→　挿入　→　名前　→　定義　→　仮に「運賃表1」と名前定義します。
（範囲指定後、名前ボックスに直接入力しても構いません）

同様に、運賃表(2)Sheetの$A$2:$N$286セルを範囲指定し、「運賃表2」と名前定義会います。

そして、数式内の「>=1」は省いても同じ結果になると思いますので、
質問そのままの数式は
=IF(I5="","",IF(COUNTIF(H22繁忙・閑散!$A$1:$H$24,I5),IF(COUNTIF(運賃表1,L5),VLOOKUP(L5,運賃表1,5,0)-200,IF(COUNTIF(運賃表2,L5),VLOOKUP(L5,運賃表2,5,0)-400,"")),IF(COUNTIF(H22繁忙・閑散!$A$25:$H$46,I5),IF(COUNTIF(運賃表1,L5),VLOOKUP(L5,運賃表1,5,0),IF(COUNTIF(運賃表2,L5),VLOOKUP(L5,運賃表2,5,0),"")),IF(COUNTIF(H22繁忙・閑散!$A$47:$H$67,I5),IF(COUNTIF(運賃表1,L5),VLOOKUP(L5,運賃表1,5,0)+200,IF(COUNTIF(運賃表2,L5),VLOOKUP(L5,運賃表2,5,0)+400,""))))))
と、少しですが短くできると思います。

また、検証したわけではありませんが、
IF関数の真の欄にIF関数をネストされていますので、
IF関数の関数の引数ダイアログボックスの理論式の欄に
「AND」を使用すればもう少し短くなるかと思います。

具体的に実際の表がどのようになっているか判断しかねますので
この程度しか書けませんが
No.3さんが仰っているように作業列を使い、200・400等を返すような感じにすれば
もっと簡素化できるような気がします。

この程度でごめんなさいね。m(__)m

お礼

丁寧な記述ありがとうございます。
ANDの使用も考えたのですが、ややこしくなって断念しました。
参考にさせていただきます。

回答No.4

MackyNo1

＞officeのバージョンが2003なのでこれ以上長くするのには限界があるのです。（上記関数式（ここではAAAとします。）とすると、
IF(AAA<200,"",AAA)というような感じで入力しようとしましたが、長すぎてだめでした。）

数式の長さやネストの制限にかかるような場合は、数式そのものを名前定義することで制限をこう得ることができます。

今回のケースでしたら、「AAA」にあたる部分を「挿入」「名前」「定義」で「AAA」という名前で元の式を定義して、数式を「=IF(AAA<200,"",AAA)」のようにしてみてください。

今回のように、同じ数式の中で何回も繰り返し出てくる数式や条件は、その数式部分を名前定義すると可読性がよく、式の修正がしやすい数式になります。

お礼

ありがとうございます。
そういう手があったのですね。

回答No.3

grumpy_the_dwarf

まず、運賃表を二つに分けるのをヤメます。で、運賃表にO列を新設
して、200または400と書いておきます。この列に、繁忙期は1、閑散
期は-1、通常期は0をかけ算して足せばいいわけですね。

で、繁忙・閑散表が適切に作ってあれば、countifの結果は0か1にし
かなりません。かけ算のためのパラメータは
=COUNTIF(H22繁忙・閑散!$A$1:$H$24,I5)-COUNTIF(H22繁忙・閑散!$A$47:$H$67,I5)
で求められます。

さらに、空白を返す条件(日付が入力されてない or 到着駅が運賃表
に載ってない)のチェックをまとめます。

=if(I5*countif(運賃表!$A$2:$O$286,L5),
vlookup(L5,運賃表!$A$2:$O$286,5,0)
+vlookup(L5,運賃表!$A$2:$N$286,15,0)*(countif(H22繁忙・閑散!$A$1:$H$24,I5)-countif(H22繁忙・閑散!$A$47:$H$67,I5)),
"")

こんな雰囲気になりますね。

お礼

ありがとうございます。

しかし、
=if(I5*countif(運賃表!$A$2:$O$286,L5),
vlookup(L5,運賃表!$A$2:$O$286,5,0)
+vlookup(L5,運賃表!$A$2:$N$286,15,0)*(countif(H22繁忙・閑散!$A$1:$H$24,I5)-countif(H22繁忙・閑散!$A$47:$H$67,I5)),
"")
が理解できませんでした。

=COUNTIF(H22繁忙・閑散!$A$1:$H$24,I5)-COUNTIF(H22繁忙・閑散!$A$47:$H$67,I5)
の部分も理解できません。

意味を教えていただけるとありがたいです。

回答No.2

passes

対象を範囲名にしたら？。

お礼

ありがとうございます。

回答No.1

mu2011

>この関数でできることを変えずに、短くすることはできるでしょうか？
⇒条件がよくわかりませんが、条件をｎ×ｎ表で整理してみては如何でしょうか。

>数字が200以下だった場合に、算出しているセルに何も表示させないようにするには
⇒単純に未表示にするならば、セルの書式設定→表示形式→ユーザ定義で[<200]"";G/標準で如何でしょうか

お礼

ありがとうございます。
しかし、ただの表示の問題ではないです。