Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2019)
OFFSETとHLOOKUP
投稿日時: 22/05/06 17:17:54
投稿者: ラングドシャ

お願いします。
 
=COUNTIF(OFFSET(sheet1!DD17,,,,-20),"*休*")
こちらの関数の、OFFSETの基準セル(sheet1!DD17)を、HLOOKUPで検索した値に
したいのですがうまくいきません。
 
=COUNTIF(OFFSET(HLOOKUP($B$1,shhet1!$2:$504,ROW(A16),0),,,,-20),"*休*")
 
B1セルに日付があり、SHEET1の2行目に入った日付のセルを検索し、
その列の16行目の値を基準にしたく上記のように関数を作成したのですが
誤りがありますとエラーになってしまいます。
どのように修正すればエラーが出なくなりますでしょうか?
 
よろしくお願いいたします。

投稿日時: 22/05/06 17:26:11
投稿者: ラングドシャ

誤字がありました。
 
=COUNTIF(OFFSET(HLOOKUP($B$1,sheet1!$2:$504,ROW(A16),0),,,,-20),"*休*")
 
太字部分のシート名が間違っておりました。
申し訳ありません。

回答
投稿日時: 22/05/06 18:10:37
投稿者: WinArrow
投稿者のウェブサイトに移動

>HLOOKUP($B$1,sheet1!$2:$504,ROW(A16),0)
  
「HLOOKUP」と「VLOOKUP」と間違えていませんか?
  
「HLOOKUP」だとすると
第2引数は「列番号」ですよね?
列番号の場合は、$B:$SJ と指定しないといけないと思いますが・・・・
  
それから
OFFSET関数の第1引数には、セルのアドレスを指定することになっていますが、
HLOOKUPの戻り値がセルアドレスになっていますか?
 
OFFSET関数の第4引数、第5引数は、セル範囲を指定することになっているので
第4引数なし、第5引数:-20というのは、どの様な範囲を想定しているのですか?

回答
投稿日時: 22/05/06 21:52:53
投稿者: んなっと

以下のような具体例を添えた質問を心掛けてください。
 
【質問例】
 
5/8から遡って1週間の期間に、備考に「休」が含まれている日数を調べようとしています。
 
   A   B  C   D  E   F  G  H  I  J   K
1 日付  5/1 5/2  5/3 5/4  5/5 5/6 5/7 5/8 5/9 5/10
2 備考 あ休  い う休  え お休  か  き  く  け こ休
3                              
4  5/8   2 
 
B4
=COUNTIF(OFFSET(I2,,,,-7),"*休*") で正しい結果の2が返ります。
隣のA4の日付を検索して結果を返すように、I2の部分を置き換えて               
=COUNTIF(OFFSET(HLOOKUP($A4,$1:$2,2,FALSE),,,,-7),"*休*")
としました。ところが「この数式には問題があります」と表示されてしまいます。
どうすればいいですか。
 
【回答】
 
B4
=COUNTIF(OFFSET(INDEX($2:$2,MATCH($A4,$1:$1,0)),,,,-7),"*休*")
でどうでしょうか。
 
HLOOKUP($A4,$1:$2,2,FALSE)では、
残念ながらI2のセル内の値である、ただの文字列"く"しか返しません。
I2というセルそのものを参照したいときは、
INDEX($2:$2,MATCH($A4,$1:$1,0))などとしましょう。

回答
投稿日時: 22/05/07 08:52:35
投稿者: WinArrow
投稿者のウェブサイトに移動

私の
投稿日時: 22/05/06 18:10:37
のレスは、勘違いした部分gあります。
無視してください。

投稿日時: 22/05/07 09:34:34
投稿者: ラングドシャ

WinArrow様
 
ありがとうございます。
まず、WinArrow様のコメントで私がすごく勘違いをしていたことに気づきました。
OFFSET関数で、セルの場所を指定しなければならないのにHLOOKUPでセルの場所が
指定できると思い込んでいました。
WinArrow様の勘違いではなく私の間違いでした。すみません。
 
んなっと様
ありがとうございます。
私の勘違いによる間違いが含まれる質問をしてしまったのに、質問したいことをを
推理してくださってありがとうございます。
INDEXとMATCHでセルの位置を導けばよいのですね。試してみたら問題なく結果が
表示されました。
HLOOKUPでできるはずなのにできないと思い込んで、ずっと悩んでいて
もしかして自分の考えが間違っているのかも?という思考になれていませんでした。
本当にありがとうございました。