エクセルの関数式で・・・

質問

このような表を作りました

　 　Ａ　　 Ｂ　　 Ｃ　　 Ｄ　　 Ｅ・・・
１　　a　　 a 　　　b 　　a 　　　c・・・
２　12:00 8:00 11:00 4:00 12:30・・・
３　(空白)PM (空白) AM (空白)・・・

Ａ１にaと入力すれば12:00。Ｂ１にaと入力し、Ｂ３にPMと入力すれば8:00と計算できる表をVLOOKUP関数で作りました。
これに加えて、例えばＡ４にaを入力したらＡ２の値(時間です)から２時間引いた値をＡ２に、Ｃ４にbを入力したらＣ２の値から２時間引いた値をＣ２に表示するためにはどうしたら良いでしょうか？
Ａ４に入力したものに対応するＡ１の値から時間を引いていきたいのです。
よろしく御指導下さい。

回答No.5

noname#262398

どのセルにどういう数式が入力されていて、どのセルにはどんな値や文字を入力するのか、
そういうことは最初に質問する時に書いて頂かないと、回答してもエラーが出たとか、思うような結果にならないとかで、回答が無駄になってしまいますし、解決するまで時間がかかってしまいます。
最初のご質問と実際の不明な点とは違う内容のように見受けられます。
わたしは別に構いませんが、No.1の方の回答が結局無駄になってしまいます。
もし新規に別の質問なさることがあれば、その点ぜひお気を付けください。
苦言めいた話ですいません…

さてさて、=IF(CODE(A4)-A1=11552,"2:00",0)
この式は6～9というのは考慮してません。5と丸5の組み合わせでも2時間引かれます。念のため、申し添えておきます。

> 上手く"ＯＲ関数"をネストできれば完成するような気がするのですが
その通りですよ。複数条件の時はネストさせれば良いです。難しいですか？
では、一例を…

=IF(AND(A1=6,A4="(6)"),"2:00",0)
=IF(A1=6,IF(A4="(6)","2:00",0),0)
この2つの式の結果は、まったく同じです。下がIFをネストさせた例です。

ネストさせると式が複雑になって分かりにくくなるなら、別セルに途中の計算結果を出してしまえば、分かりやすくなります。
A2=VLOOKUP(…)-VLOOKUP(???)-IF(…)のように、一辺に答えを出さずに、
6行目から下を使ってなければ、
A6=VLOOKUP(…)　最初の式
A7=VLOOKUP(???)　2番目の式
A8=IF(…)
とすれば、A2=A6-A7-A8という簡単な式になります。
同じように=IF(…)が複雑な式なら、
A8=IF(AND(A1=6,A4="(6)"),"2:00",0)
A9には7の判定、A10は8、A11は9、それぞれ判定して、
A2=A6-A7-A8-A9-A10-A11
で完成となります。
慣れればAND関数とOR関数を組み合わせて、ひとつの式で出来るようになります。それまでは、回答者の式を丸写しするよりも、tatsudaさんがご自身で応用できる形にしておいた方が良いと思います。

長くなりましたが、今回はこれにて…
最初にあんなこと書きましたが、もし新規に別の質問する場合は、という意味なので、この回答で何かあればご遠慮なさらずにどうぞ。

回答No.4

noname#262398

> ＆と言うのがカッコの役目なんですね
違います^^
& は文字をつなげる役目です。
A1に「123」B1に「ABC」と入力して
=A1&B1
という式を作ると、結果は「123ABC」となります。
"("&A1&")"
この部分の意味は「 ( 」と「A1セルの値」と「 ) 」をつなげる、ということです。
「 ( 」は文字なのでエクセルの式の中に文字を組み込む場合 " "で囲まなければなりません。

さて、追加質問の件ですが、丸で囲んだ数字はそれだけでひとつの文字なので、エクセルは値として処理出来ません。
=IF(AND(A1=6,A4="(6)"),"2:00",0)　とか
(丸囲み数字は投稿するとカッコ付きに変換されてしまうので、「 (6) 」は「丸囲み 6」のことです)
=IF(CODE(A4)-A1=11552,"2:00",0)
めったに使わないCODE関数を使わなければならないですね。

4行目が、単に2時間引くか引かないかの判断なら
4行目に直接「2:00」と入力するのはだめですか？
それなら　=VLOOKUP(…)-VLOOKUP(…)-A4
で済みますし、他にも判断することがあるなら、
5行目、6行目…、を使えばよいと思うのですが。

お礼

度々本当にありがとうございます。
"＆"は勘違いいたしておりました。御指導ありがとうございます。

さて、=IF(AND(A1=6,A4="(6)"),"2:00",0)の関数式ですが、Ａ１に入るのは「６」だけでなく、「７」、「８」、「９」などの数字。他にも「休」、「会議」等の文字も入ります。
Ａ４には(6)、(7)、(8)、(9)（丸囲みです）の他、「休」等の文字が入ります。
Ａ１が「６」でＡ４が(6)の時、２時間引く。「９」で(9)の時も２時間引く。「休」が重なった時はそのまま「0:00」の表示のままでいきたいのですが・・・
上記の式では「６」と(6)は出来ますが、「７」以降ができません。

また=IF(CODE(A4)-A1=11552,"2:00",0)の関数式でいきますと、６，７，８，９全てで２時間引く事が出来るのですが、「休」を入力したら####となり、表示したい「0:00」になりません。

上の関数式に上手く"ＯＲ関数"をネストできれば完成するような気がするのですが、分かりません。（違うのかも知れませんが…）

またお時間のある時で結構ですので、御指導下さい。

回答No.3

noname#262398

「(6)」と入力すると「-6」に自動で変換されませんか？

1行目の値が6と7に限った場合なのか、
1でも2でも4行目にカッコを付ければ、ということなのか分かりませんが、
=IF(AND(OR(A1=6,A1=7),A4="("&A1&")"),"2:00",0)
=IF(A4="("&A1&")","2:00",0)
（VLOOKUPの式は省略）
上が6,7限定・下が限定なしで、カッコが付けば、です。

こんな感じで後は応用できますよね？

お礼

ありがとうございます。
＆と言うのがカッコの役目なんですね。
凄く勉強になりました。
本当にありがとうございます。

暇な時でいいんでもう一点お伺いしてよろしいですか？
カッコでくくった数字ではなく、丸で囲んだ数字の場合は＆ではダメですよね？
何か方法ありますか？？

回答No.2

noname#262398

1行目と4行目が同じなら、ということですか？
ならば、
=VLOOKUP(…)-VLOOKUP(…)-IF(A1=A4,"2:00",0)

お礼

なるほど！ありがとうございます。
早速やってみました。

追加でもう一点教えて下さい。
１行目が６で４行目が(6)、１行目が７で４行目が(7)というの時に２時間引くことは可能ですか？

回答No.1

noname#123709

具体的な数式がでてないのでわかりませんが・・。
=IF(A4="",A2に入ってる式,A2に入ってる式-2時間）で可能だとは思います。

補足

=VLOOKUP(C23,sheet2!$C$2:$D$25,2,FALSE)-VLOOKUP(C25,sheet2!$G$2:$H$7,2,FALSE)
というような関数で時間を出しています。