エクセルの関数の意味を日本語で知りたい

例えばIF関数なら、'=IF(B2>=80,"合格","不合格") <セルB2はA君の得点>
　　『「もしA君が８０点以上なら合格、そうでないなら不合格」と表示をする』と
このように誰が見ても分かるような解説が欲しいのです。

知りたいのは次の関数の組み合わせです。
なるべく細かく知りたい。例えば（　）ごとに何が言いたいのか知りたい。
　　'=INDEX(シート1!$D:$D,SMALL(INDEX((OFFSET(シート1!$D8,,MATCH
　　　($E$2,シート1!$E5:$AI5,0),11)<>H5)*100+ROW(D8:D18),),COLUMN(A1)))
　　シート1の各セルは　D列に氏名（11人）、D8は一番最初の人の氏名、
　　　　　　　　　　E5:AI５は日付、D8:D13は氏名　（縦軸が氏名、横軸が日付）
　　シート2の各セルは　H6に本関数が入っている。E2は日付、H5は漢字で「夜勤」
　　　　　　　　　（E2の日付の時は誰が夜勤かをH６に自動表示される）
　　特に、「<>H5)*100+ROW(D8:D18),)」の所が分からない。

宜しくお願いします。

No.4 ベストアンサー 回答者： yos1912 回答日時： 2020/01/22 10:39

この関数には理解しがたいところがたくさんあって、どうなっているか知りたく回答が寄せられてくるのを期待していました。

試しに書かれているような表を実際に作ったところ正しく動くのが不思議なくらいです
　なかなか次の解答がでないので、もう限界かなということでわかる範囲と一部想像で書かせてもらいます。想像したところ、わからないところははっきりそう書きます。できればわかる方のフォローをお願いします。

まず特にと書かれている部分から説明します。
○○<>H5　は条件式です。セルの値が｢夜勤｣でないかどうかをチェックしています
　　式はチェックし結果を、真(TRUE)偽(FALSE)かで返答します。
()*100　　 で()の中を１００倍します。ここで奇妙なのは()の中は真か偽しかありません
　　ここでは裏技が使われています、真か偽は計算する上で数値で管理しています。
　　一般的には－１と０の組み合わせで使われますが、エクセルでは１と０です。
　　どこかのセルで=true*2を入れてみると２が返されてくることからわかります
　　従って式の意味は()の中の評価によって0か100のどちらかという意味になります

式の中にこのような裏技が使われているところがあるのをみると、知らなかった裏技がたくさんあるようです。式の続きです。
+ROW(D8:D18)　()のセルの行番号を加えよです
　　試しにどこかのセルに式を入れたところ８が返されてきました
　　なぜ８にしないのかは疑問です(さいごにあるCOLUMN(A1)もおなじです)
　　間に行が挿入されたときの対策なら、ROW(シート1!D8:D18)です
　　－－　追加解釈があります　後ほど該当の所で　－－
,)　　　　引数はここまでで、次の引数に０を入れて(または規定値にして)計算せよ

特に以外の部分の説明に入ります。シート１の表を日勤表とさせてもらいます。ネストの一番深いところからみていきます。
MATCH($E$2,シート1!$E5:$AI5,0)
　　そのままなので理解できると思います。日勤表に$E$2(日付)がるのは何列目か。
OFFSET(シート1!$D8,,該当日のある列,11)
　　日勤表の左上角から０行目、該当日のある列のデータを縦に１１個取り出せ
　　要するに該当日の各人の出勤形態を順番に(配列で)答えよという意味です
　　これを該当日の出勤形態とします(次からは断らなくてもいろいろ使います)
この次に奇妙な式がでてきます。ここからは想像が入ってきます。
(該当日の出勤形態<>H5)*100+ROW(D8:D18)
　　出勤形態は数値でなく配列(数字の並び)です。どうやって計算するのか？
　　ROW(:)を加えています。これも8 9 10 11と並んだ配列です
　　想像になります。配列どうし計算は順番にしてくれるのでは(行列計算と同じ)
　　こうだとして話を続けます
INDEX(出勤形態の値,)
　　,の後が省略されていますから、そこを全部(想像です)取り出せという意味です
　　何もしていないように見えます。
　　想像ですが次のSMALLの引数に計算式が使えないため配列に変換しているのかも
　　ならINDEXでは第１引数に計算式がどうして使えるのかという疑問もでてきます
SMALL(出勤形態の値,COLUMN(A1))
　　出勤形態の値一覧で、COLUMN(A1)の値(１)番目に小さい値(最小値)はいくらか
　　出勤形態で夜勤ならその人の行番号、でなければ100+行番号が入っています
　　計算結果は、夜勤の人の行番号になります。
　　奇妙なところはCOLUMN(A1)です。なぜ１にしなかったのか？不明です
INDEX(シート1!$D:$D,夜勤の人の行番号)
　　この部分だけみると簡単です。名列表から該当者の名前を取り出せになります

以上まともに動くためにはどうなっていないといけないなどかの想像を加えて解説しました。想像と書いたところが正しいのかどうか。できればフォローお願いしたいところです。

この回答へのお礼

大変大変大変有難うございました。
それしか言えません。
あなたの回答を紐解きとしてまた勉強します。
関数には色んなやり方、裏技等があることは分かりました。
有難うございました。

お礼日時：2020/01/23 09:14

No.3 回答者： mike_g 回答日時： 2020/01/20 08:48

それから、「…の所が分からない」は扨て置き、貴方のシート2のセル H6 は何と表示されてゐるのですか？結果を検証する爲に教へてください。

この回答へのお礼

H6の表示（求めたい表示）は氏名です。
シート2に「その日の夜勤は誰か」を表示します。
従って、１日～３１日の夜勤は誰か？（A君～K君の内誰か）を
表示させたいのです。
実際にはH6だけではなく、H16,H26・・・と31日分あります。
また、今回は夜勤のみ質問しましたがその他（日勤、早出等）も
あります。（シート2は日付ごとの簡易な表が31個ある）
つまり、シート１の月間勤務表からシート2にその日ごとの勤務を
反映するということです。

お礼日時：2020/01/20 19:28

No.2 回答者： mike_g 回答日時： 2020/01/20 08:38

それから、シート1において、何日に「誰が夜勤かを」だうやつて知ることができるのですか？

この回答へのお礼

ｼｰﾄ1の表の横軸が日付となっていて31日分の日が入っています。
E５が1日、F5が2日、・・・AI５が31日です。
その表に「夜勤」と手入力します。例えば10にB君が夜勤だと
N9が「夜勤」と記載されています。
ﾏﾄﾘｯｸｽの交点で誰が何日に「夜勤」と分かります。
（先ほどのお礼文書で縦軸が氏名）

お礼日時：2020/01/20 19:12

No.1 回答者： mike_g 回答日時： 2020/01/20 08:27

後になって「それはミスでした」なんてシャーシャーとされないために、確認させてください。

「D列に氏名(11人)」と宣わつてをられるのに、「D8:D13は氏名」とも書かれたのはだういふわけですか？

この回答へのお礼

回答（親身な確認）有難うございます。
言葉足らずですみません。
ｼｰﾄ1は表のD7に「氏名」とあり、D8が「A君」、D9が「B君」と
いうように順番に行き、最後のD18が「K君」です。（縦軸が氏名）
あとの確認事項はその回答に記載します。
すみません。宜しくご指導下さい。

お礼日時：2020/01/20 19:00