こんな関数つくれますか？

質問

ＥＸＣＥＬはやっと初心者を脱したかな、という感じです。
ＥＸＣＥＬで出来た表に手を加えたいのですが、こんな関数作れるのでしょうか？

シート１と２があります。２の中のあるセルに、シート１のＡ１～Ｇ１に入力された文字の略称を表示させたいのです。ただしＡ１～Ａ１０には、赤・青・黄・緑・茶といったように５つくらいの値が入力され、その中の一つを選んで表示させたいです。当然あまりの５つくらいのセルは空欄のままです。実際にはＡ１～Ｎ１くらいの範囲で、入力される色の種類も１０以上、そのなかで６つくらいが入力される中で条件にあった一つを選び出させたいです。該当する値がないときはシート２のセルは空欄にしたいですし、できれば該当データが複数ある時はエラーを出させたいのです。

「ＶＬＯＯＫＵＰ」なる関数も発見したのですが、難しくてよくわかりません。どうかお知恵を。

回答No.3

Raistlin · ベストアンサー

こんにちは　私も数時間頭を悩ませていた一人です。
補足の後もっと考えてしまいましたが。

補足文中でもところどころ項目の始まりのセルや、データが列方向なのか行方向なのかが抜けていて解読に苦労しました。

こういう表でよろしいのでしょうか?
(シート1,2とも左上のセルがA1になります。CSVで読みこんで正確な配置を確認してください。)

シート1
＝＝＝＝＝＝＝＝＝＝＝
　　,　　,英　,英　,数　,数　,国　,国　,理　,理　,社　,社　
　　,　　,許斐,尾田,岸本,澤井,高橋,鈴木,島袋,冨樫,武井,小畑
月　,１　,1A英,3B英,2A数,＿＿,3A国,＿＿,＿＿,1B理,＿＿,2B社
月　,２　,＿＿,2B英,1A数,3B数,2A国,＿＿,3A理,＿＿,＿＿,1B社
月　,３　,＿＿,1B英,＿＿,2B数,1A国,3B国,2A理,＿＿,3A社,＿＿
月　,４　,3A英,＿＿,＿＿,1B数,＿＿,2B国,1A理,3B理,2A社,
火　,１　,3A英,＿＿,＿＿,1B数,＿＿,2B国,1A理,3B理,2A社,
火　,２　,＿＿,1B英,＿＿,2B数,1A国,3B国,2A理,＿＿,3A社,＿＿
火　,３　,＿＿,2B英,1A数,3B数,2A国,＿＿,3A理,＿＿,＿＿,1B社
火　,４　,1A英,3B英,2A数,＿＿,3A国,＿＿,＿＿,1B理,＿＿,2B社
水　,１　　　　　　　　(これ以降省略)
水　,２　

シート2　(火曜1時間目1A教室、火曜2時間目2B教室が変更)
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
　　,　,１　,１　,２　,２　,３　,３　,４　,４　
月　,1A,1A英,　　,1A数,　　,1A国,　　,1A理,
月　,1B,1B理,　　,1B社,　　,1B英,　　,1B数,
月　,2A,2A数,　　,2A国,　　,2A理,　　,2A社,
月　,2B,2B社,　　,2B英,　　,2B数,　　,2B国,
月　,3A,3A国,　　,3A理,　　,3A社,　　,3A英,
月　,3B,3B英,　　,3B数,　　,3B国,　　,3B理,
火　,1A,1A理,社会,1A国,　　,1A数,　　,1A英,
火　,1B,1B数,　　,1B英,　　,1B社,　　,1B理,
火　,2A,2A社,　　,2A理,　　,2A国,　　,2A数,
火　,2B,2B国,　　,2B数,理科,2B英,　　,2B社,
火　,3A,3A英,　　,3A社,　　,3A理,　　,3A国,
火　,3B,3B理,　　,3B国,　　,3B数,　　,3B英,

あっているという前提で以下、力技回答です。
シート1の名前を"1"にします。
シート2のL,N,P,R列をそれぞれ1,2,3,4時間目の途中計算に使います。

L1,N1,P1,R1にそれぞれ、1,2,3,4を入力、時間のラベル兼演算用に使用します。

L2に次の無駄に長い?式を入力、必要なだけ下方向にコピーします。
=INDEX(OFFSET('1'!$C$2,L$1+(ROW()-1-MOD(ROW()-1,7))/7*4,0,1,10),MATCH($B2&"*",OFFSET('1'!$C$2,L$1+(ROW()-1-MOD(ROW()-1,7))/7*4,0,1,10),0))
N,P,R列の2行目以降はL列のコピーでOKです。

D2に次の長い式を入力、必要なだけ下方向にコピーします。
=IF(L2=C2,"　　",RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(L2,"英","英語"),"数","数学"),"国","国語"),"理","理科"),"社","社会"),2))
F,H,J列の2行目以降はD列のコピーでOKです。

注意
１．セル参照の雨嵐なので、例に挙げたアドレスからずれる可能性があるときは、細心の注意を払って式を修正してください。特にROW()を使用しているため、行がずれるといちころです。
２．MATCH()は、最初に見つかったセルしか返さないので、シート1で教室がダブルブッキングしていてもエラーを返しません。
３．私ことRaistlinは、この回答を運用した結果について完全な責任はもてません。自己責任でお願いします。

提案
＞それらがシート２の月１覧左にも入力されています。（ここは変わることはないので手入力です）
もし、シート1で時間割をアレンジした結果をシート2に転記するという手順であれば、はじめにC,E,G,I列にL,N,P,R列をコピーすると早いのでは?

これで外してたらどうしよう・・・。

お礼

すみません、お礼遅れました。メール届かなかったもので。
まだ試していないのですが、前提はあっているので試してみたいと思います。

皆さんありがとうございました。

回答No.2

paku_i

PANCHOさんと同意見です。
昨日の夜からず～っと考えていますが、やはり意味が良くわかりません。
私はEXCELの関数が比較的得意なので、答えられるようでしたら答えたいと考えております。

補足などがメールで届くように私も細く要求させていただきます。

補足

すみませんでした。自分の余計なたとえが、お二人を悩ませてしまったようです。でも、結構複雑なんです…

実は、時間割表を作りたいのです。シート１のＡ列３から、月曜日～金曜日までの日付があり、さらにＢ列には各曜日とも１～４時間目まで入力されています。各曜日とも４セル分ずつになります。また１行目には科目名が５つ（英数国理社）、２行目には担当者名が１０名分入っています。１科目２名（２セル、氏名は適当にしてください）です。１時間に行われる講義は６つ（１Ａ／１Ｂ／２Ａ／２Ｂ／３Ａ／３Ｂ教室において）ですので、１０名中６名の覧が常に埋まります。６名の覧に入る文字は、２Ｂ社、３Ａ国などです。各教科２名いますが、左側の人がＡ教室担当、右がＢです。

シート２は、教室ごとの時間割です。タテに１Ａ～３Ｂ、ヨコに１時間目～４時間目（各２セル分）、それが一日分ですから、その表が月～金の分５つあります。ちなみに各時間２セル作ってあるのは、左に既定の基本時間割、右には変更があった場合の科目名が入力されます。この変更覧セルに関数を入力したいのです。

シート１の月１時間目（Ｃ３～Ｌ３）には基本時間割の６講義が入力してあります（例えば１Ａ国、１Ｂ社、２Ａ社、２Ｂ英、３Ａ理、３Ａ英など）。それらがシート２の月１覧左にも入力されています。（ここは変わることはないので手入力です）ここで、１Ｂ社の担当者が休んだ場合、シート１の月１覧では１Ｂ社を消去し、１Ｂ数などに振り替えます。それを自動的にシート２の月曜表・１Ｂ１時間目右セルに表示させたいのです。しかも（わがまま行ってスミマセン）シート２に表示された時点で「社会」という文字にしたい（これで充分なので）のです。

その関数をオートフィルできるようなものにしないと入力が大変そうです。長い補足で恐縮ですが、是非よいお知恵をいただきたいと思っています。

回答No.1

pancho

申し訳ありませんが、何回読んでも質問の内容が理解できません。

もう一度、シート１には「どのくらいの範囲に、どんな値が設定されているのか」、シート２には「何がどこに入力され、それを元にどこに値を持ってきたいのか」、「シート２に入力された値と、シート１のどの値をどう比較して選択するのか」などを整理して補足してください。

具体的な例が書ければ、もっと参考になります。（多分、Ｖｌｏｏｋｕｐで出来そうですが ．．．。）

以上。