Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10全般 : Excel 2016)
関数の誤作動について
投稿日時: 21/03/26 16:40:54
投稿者: ぶくぶく

IF関数、INDEX関数、MATCH関数を組み合わせた関数を入れています。
 
別のセルの情報に合わせて、表示内容は変わっていくのですが
変わる時と変わらない時があります。
 
うまく結果を出してくれる時もあるので、数式が間違っているともいえず
検証の仕方が分かりません。
 
shift+F3で個々の関数を確認してもエラーは出ていません。
INDEXの参照先をに行っても、確かにデータ存在します。
きちんとそのデータを返す時もあれば、#VALUE!エラーが出る時もあります。
 
 
このような場合、どのような原因が考えられるのでしょうか?
対処法はあるのでしょうか?
 
よろしくお願いいたします
 

回答
投稿日時: 21/03/26 16:48:41
投稿者: WinArrow
投稿者のウェブサイトに移動

漠然とした説明では、回答者には何も伝わりません。
 
具体例を掲示しましょう。

投稿日時: 21/03/26 17:12:18
投稿者: ぶくぶく

勤怠シートに「有休」「在宅」等の情報が入っています。
勤務シートに「夜勤」「出張」等の情報が入っています。
 
現シートの4行目に日付が並んでおり、下記はセルD6に入れられた関数です。
別の入力シートに日付を入力すると、勤怠シートの日付を検索し、D4と合致していれば
D6にその情報を返します。
勤怠シートに情報がなければ、勤務シートを見に行き、1行目から50行目に情報があれば、それを返し、
なければ51行目から100行目の情報を返します。
 
=IF(INDEX(勤怠!$B:$H,MATCH(D$4,勤怠!$B:$B,0),$A2)<>"",INDEX(勤怠!$B:$H,MATCH(D$4,勤怠!$B:$B,0),$A2),IF(INDEX(勤務!D$1:D$50,勤務!D150,1)<>"",INDEX(勤務!D$1:D$50,勤務!D150,1),INDEX(勤務!D$51:D$100,勤務!D151,1)))
 
勤務シートの1行目から50行目までに情報は常に正しい情報を返します。
勤務シートの51行目から100行目の情報のみ、返したり、返さなかったりします。
 
IFの階層をもう一つ増やしてみましたが、同じでした。
 
うまく伝わりましたでしょうか?
 
よろしくお願いいたします

回答
投稿日時: 21/03/26 17:17:10
投稿者: Suzu

関数の引数として渡されるべきデータ型と、参照しているセルのデータ型が合致していない
のが主たる原因となります。
 
例えば、参照されるセルに、書式設定として「0個」を設定しており
表示上、10個 と表示されている。
 
でも、
数式バー を見ると 「10個」となっている場合「10」となっている場合 両方とも 10個と表示されている。
 
例えば、「=そのセル*10」 の数式を別のセルに割り当てていた場合、
 
数式バー 10の 場合は、数式の結果として、100
 〃  10個の場合は、数式の結果として、#VALUE!
 
後者は、セルの値に、「10個」と文字列が渡されているので、エラーになっている。
 
 
その他にも原因がありますので
 
エラー値 #VALUE! を修正する
https://support.microsoft.com/ja-jp/office/%E3%82%A8%E3%83%A9%E3%83%BC%E5%80%A4-value-%E3%82%92%E4%BF%AE%E6%AD%A3%E3%81%99%E3%82%8B-15e1b616-fbf2-4147-9c0b-0a11a20e409e
 
を確認し、対応してください。

回答
投稿日時: 21/03/26 17:31:55
投稿者: んなっと

もしもD51:D100が文字列になっていて、それを日付データに変換したいのなら
 
 D列を選択
→データ
→区切り位置
→次へ
→次へ
→日付 を選択して 完了

投稿日時: 21/03/26 17:36:42
投稿者: ぶくぶく

ありがとうございます。
 
確認してみます。
 
ただ
例えば、4/1と入力すると、D6に「夜勤」と表示されます。
その後、4/2と日付を変更すると、その横のE6に「夜勤」の表示が移ります。
そして又、4/1に日付を変更すると、D6に何も表示されなくなります。
 
これの規則性が掴めないのです。
 
宜しくお願いします。

回答
投稿日時: 21/03/26 17:39:32
投稿者: WinArrow
投稿者のウェブサイトに移動

数式を分解して、どこに問題があるかを調査することをお勧めします。

投稿日時: 21/03/26 17:39:34
投稿者: ぶくぶく

んなっと様
ありがとうございます。
方法も教えて下さり、助かります。
 
ただ、51〜100のデータも、セルD6のデータも標準に設定しています。
 
51〜100の中に 「出張」の情報が入っており
D4の日付とマッチングさせて、D6に表示させます。

投稿日時: 21/03/26 17:42:30
投稿者: ぶくぶく

WinArrow 様
 
何度もありがとうございます。
 
全ての関数にShift+F3を当てて、確認したのですが、エラーが発見されません。
勤務シートの該当セルも確認しますが、そこにはデータが存在します。
 
八方塞がりになってきました‥…

投稿日時: 21/03/26 17:46:27
投稿者: ぶくぶく

すみません
補足します。
 
勤務シートの51行目〜100行目に「出張」と表示されたデータがあり、
D4(=4/1)に出張していれば、D6に「出張」と表示されるようにしています。
 

回答
投稿日時: 21/03/26 17:53:44
投稿者: WinArrow
投稿者のウェブサイトに移動

分解して取り出す数式
 
@INDEX(勤怠!$B:$H,MATCH(D$4,勤怠!$B:$B,0),$A2)
AINDEX(勤怠!$B:$H,MATCH(D$4,勤怠!$B:$B,0),$A2)
 
BINDEX(勤務!D$1:D$50,勤務!D150,1)
CINDEX(勤務!D$1:D$50,勤務!D150,1)
 
DINDEX(勤務!D$51:D$100,勤務!D151,1)
 
@とAは同じです。
BトCは同じです。
 
Dの「勤務!D151」は? この数式が怪しい?

回答
投稿日時: 21/03/26 18:05:22
投稿者: WinArrow
投稿者のウェブサイトに移動

Dの数式を検証
第2引数(D151)の値で、結果が変わる
 
第2引数のセルが空白の時・・・エラーにはならず、意図しない結果が返る
第2引数のセルに数値が入っているが、第1引数の範囲を超えている時・・・#REF!エラー
第2引数のセルに文字列が入っている場合・・・#VALUE!エラー
 
これを参考に調査してみてください。

投稿日時: 21/03/26 18:09:25
投稿者: ぶくぶく

 WinArrow 様
ありがとうございます。
 
また、報告させていただきます。
 
よろしくお願いいたします

回答
投稿日時: 21/03/26 18:18:20
投稿者: WinArrow
投稿者のウェブサイトに移動

追加レス
 
Dが怪しいとレスしていましたが
BCの
第2引数もも同じですから同様の調査をしてください。

回答
投稿日時: 21/03/26 18:22:31
投稿者: WinArrow
投稿者のウェブサイトに移動

追加の質問
 
この数式は、下へフィルドラッグするんですよね?
とすると
BCDの第2引数が絶対参照しなくてよいのかな?

回答
投稿日時: 21/03/26 20:17:57
投稿者: んなっと

たしかに勤務!D150とか勤務!D151は怪しいですね。
とにかく情報不足なので、表形式で具体例を書いてください。
以下のように。
 
●現在のシート
 
  A B C   D   E   F   G   H
2 2                  
3                    
4      2/1  2/2  2/3  2/4  2/5
5                    
6      出張 夜勤 出張 出張 待機
 
D6
=TRIM(LEFT(TEXTJOIN(REPT(" ",100),TRUE,INDEX(勤怠!$B:$H,MATCH(D$4,勤怠!$B:$B,0),$A2),INDEX(勤務!D$1:D$50,MATCH(D$4,勤務!$B1:$B50,0)),INDEX(勤務!D$51:D$100,MATCH(D$4,勤務!$B$51:$B$100,0))),100))
右方向・→
 
●勤怠シート
 
    B   C
 1 1/25 夜勤
 2 1/26   
 3 1/27   
 4 1/28   
 5 1/29 夜勤
 6 1/30   
 7 1/31   
 8  2/1   
 9  2/2 夜勤
10  2/3   
11  2/4   
12  2/5   
13  2/6 夜勤
14  2/7   
15  2/8   
16  2/9   
 
●勤務シート
 
    B C   D   E
25 1/25   出張 出張
26 1/26        
27 1/27   待機 待機
28 1/28   出張 出張
29 1/29        
30 1/30   待機 待機
31 1/31   出張 出張
32  2/1        
33  2/2   待機 待機
34  2/3   出張 出張
35  2/4        
36  2/5   待機 待機
37  2/6   出張 出張
38  2/7        
39  2/8   待機 待機
40  2/9   出張 出張

回答
投稿日時: 21/03/27 15:05:28
投稿者: WinArrow
投稿者のウェブサイトに移動

完全に情報不足の感あり
  
>日付を入力すると勤務形態が表示される
というからには、それ相応のリストがあるものと推測はできるが、
回答者側には、再現テストができません。
人に関するデータが見えない。
  
回答者側で再現できるだけの情報は提供しないと、
意図する回答は得られない。
  
大体、何をしようとしているかも想像できない。
  
先入観を捨てるところから始めるとよい。

投稿日時: 21/03/27 19:38:34
投稿者: ぶくぶく

皆様
ありがとうございます。
本当に説明不足で失礼を感じます。
 
シートの構成は、んなっと様の示していただいたものとほぼ同じですが少し修正します。
 
●現在のシート
  
  A B C   D   E   F   G   H  I
1
2                  
3                    
4      2/1  2/2  2/3  2/4  2/5  2/6
5                    
6      出張 出張 有休 夜勤 夜勤  午後半休
    
 ●勤怠シート
  
    B   C  
 1  2/1   
 2  2/2 
3  2/3   有休   
4  2/4 
5 2/5   
6 2/6  午後半休  
  
●勤務シート
ここに「夜勤」と「出張」の2種類の情報が入っています。
勤務!D151 はMATCH関数を入れています。
この「出張」情報だけが反映されたり、されなかったりします。
勤怠情報、夜勤情報は100%表示されます。
 
IF(INDEX(勤怠!$B:$H,MATCH(D$4,勤怠!$B:$B,0),$A2)<>"",INDEX(勤怠!$B:$H,MATCH(D$4,勤怠!$B:$B,0),$A2),IF(INDEX(勤務!D$1:D$50,勤務!D150,1)<>"",INDEX(勤務!D$1:D$50,勤務!D150,1),INDEX(勤務!D$51:D$100,MATCH($B6,勤務!D$200:D$250,0))))
 
  A   B  C   D   E
1    夜勤 夜勤
2    夜勤 夜勤
3    夜勤 夜勤  夜勤
4
5



50    夜勤 夜勤 
51 出張 出張 出張
52       出張 出張 出張
53
 
下記の関数も検証してみます。
 
=TRIM(LEFT(TEXTJOIN(REPT(" ",100),TRUE,INDEX(勤怠!$B:$H,MATCH(D$4,勤怠!$B:$B,0),$A2),INDEX(勤務!D$1:D$50,MATCH(D$4,勤務!$B1:$B50,0)),INDEX(勤務!D$51:D$100,MATCH(D$4,勤務!$B$51:$B$100,0))),100))
 
 
 よろしくお願いいたします

回答
投稿日時: 21/03/27 20:12:42
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:
ここに「夜勤」と「出張」の2種類の情報が入っています。
勤務!D151 はMATCH関数を入れています。
この「出張」情報だけが反映されたり、されなかったりします。
勤怠情報、夜勤情報は100%表示されます。

肝心な方を説明しないで、
お願いしますと言われてもね〜〜

回答
投稿日時: 21/03/27 20:16:47
投稿者: WinArrow
投稿者のウェブサイトに移動

現在のシート
というのは、数式を入力しているシートということですよね?
そして、この数式は横方法に複写すると思いますが、
右セルに数式を複写した時
勤務!D150,勤務!D151
は、どのようになりますか?

投稿日時: 21/03/27 20:30:46
投稿者: ぶくぶく

WinArrow 様
すみません‥
 
D150、D151は、E150、E151のように列に連動してずれていきます。
 
それぞれ氏名に連動した行番号に当たります。

回答
投稿日時: 21/03/27 21:18:09
投稿者: WinArrow
投稿者のウェブサイトに移動

ぶくぶく さんの引用:
WinArrow 様
すみません‥
 
D150、D151は、E150、E151のように列に連動してずれていきます。
 
それぞれ氏名に連動した行番号に当たります。

 
分かりました。
いままで出てこなかった「氏名」は
勤怠シートのどこにあるんですか?
 
回答者が再現できる情報を提供できないのでしたら、
私は、これ以上、きゃちボールには付き合いできません。

投稿日時: 21/03/27 21:23:31
投稿者: ぶくぶく

すみません
 
報告の内容の入力が消えていました。
 
関数を個別に取り出し検証したところ、個別では機能していました。
 
 
D151
第2引数のセルが空白の時・・・
第2引数のセルに数値が入っているが、第1引数の範囲を超えている時
第2引数のセルに文字列が入っている場合
全て#VALUE!エラーが出ます
 
 

投稿日時: 21/03/27 21:48:16
投稿者: ぶくぶく

何度も回答のお手数を増やしてすみません。
整理し直します。
 
● 工程シート(入力専用シート)
 
  A   B  C    D     E    D     E
1 案件 氏名 開始日 終了日 夜勤の有無 出張(始) 出張(終)
2 
3
4
 
● 現シート(管理用)
      A  B   C  D  E  F  G
1 
2
3
4      2/1 2/2  2/3  2/4 2/5  2/6
5 
6 氏名A  夜勤 夜勤 有休 出張 出張 半日有給
7 氏名B
 
● 勤怠シート
 A B   C   D   E    F
1     氏名A 氏名B 氏名C
2  2/1   
3  2/2
4  2/3  有休
5  2/4
6  2/5
7  2/6  半日有給
 
● 勤務シート(工程シートの内容を関数で移しています)
 
  A   B  C   D   E
 1          夜勤 夜勤
2           夜勤 夜勤
3              夜勤  夜勤
4
 5

 ・
 ・
50              夜勤 夜勤 
51          出張 出張 出張
52          出張 出張 出張
53
   
101      氏名A 1   1
102      氏名B 2   2
103      氏名C    3   3
 

回答
投稿日時: 21/03/27 22:20:47
投稿者: WinArrow
投稿者のウェブサイトに移動

あれっ?D150,D151はどこに?
  
101行の
氏名A 1
は、夜勤の場合、「1」で正解だけど、
出張の場碧は、+50しないといけないのでは?
 

投稿日時: 21/03/27 22:36:05
投稿者: ぶくぶく

回答頂け感謝します。
遅い時間に私が更新してしまい、申し訳ありません。
又説明が不足していたのですね。
 
● 勤務シート(工程シートの内容を関数で移しています)
  
  A   B  C   D   E    F   G   H
  1          夜勤 夜勤
2           夜勤 夜勤
3              夜勤  夜勤
4
  5

 ・
 ・
50              夜勤 夜勤 
51                     出張 出張 
52         
53
     
 101      氏名A 1   1
 102      氏名B 2   2
 103      氏名C    3   3
 
110                    氏名A 氏名A
111
112
   
現シートの氏名と紐づけて行番号を取得しています。
=MATCH(氏名A,勤務シート!D$110:D$130,0)

投稿日時: 21/03/27 22:58:29
投稿者: ぶくぶく

これも後出しなのですが、年間管理のため、また条件分岐が多すぎて、容量が11Mほどになっています。
 
容量のせいかと思い、半期、クォーターにしてみましたが、あまり効果はなかったので
そのまま処理を続けています。

回答
投稿日時: 21/03/27 23:15:50
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:
現シートの氏名と紐づけて行番号を取得しています。
=MATCH(氏名A,勤務シート!D$110:D$130,0)

 
この数式はなんですか?
どこのセルに入っているんですか?
どうも変化球が多くて、話し合うのが難しい・・・・・
あとから、意味不明な情報が出てきますね?
 
ご存知と思いますが、
Match関数で取得しているのは「行番号」ではないですよ!
この数式の場合、110〜130の相対番号ですよ
結果に+109で行番号になります。

投稿日時: 21/03/27 23:35:14
投稿者: ぶくぶく

実際には現シートのB6に氏名Aが表示されていて、
 
=MATCH($B6,勤務!D$110:D$130,0) 
 
行番号1を取得しています。

回答
投稿日時: 21/03/28 06:34:07
投稿者: WinArrow
投稿者のウェブサイトに移動

勤務!D150とか勤務!D151
  
を解明する必要があると考え、レスしていましたが、
  
いつの間にか
D150,D151
が消えてしまい。
D101・・・・D110
にすり替わっているのか、新しいものなのかがわかりません。
 
ファイル容量に影響するような数式の多用と思います。
 
何をしようとしているかもわからないし、
勤怠シート、勤務シートの位置づけもわかりません、
氏名Aさんという観点では、
夜勤と出張が両立することは無いと思いますう。
ですから、別々のシートで管理するのではなく、1つのシートで間に合うのではないでしょうか?
 
これ以上、キャッチボールしても、無限ループになってしまいそうな気がします。
 
 

投稿日時: 21/03/28 11:13:32
投稿者: ぶくぶく

 WinArrow 様
 
切羽詰まっているとはいえ、失礼な質問内容になってしまい、本当に申し訳ありませんでした。
長時間にわたり回答いただき、諦めかけていたところを背中を押していただきました。
 
まだすっきりと解決できていないのですが、これ以上はご迷惑なので
一旦締めさせていただきます。
 
回答いただいたお時間を無駄にしないよう、検証はまだ続けていきます。
 
回答下さった皆様も本当にありがとうございました。

回答
投稿日時: 21/03/28 12:42:28
投稿者: WinArrow
投稿者のウェブサイトに移動

最後に
  
他人に説明するときは、
一旦、自分を他人に置き換えて、
分かってもらえるかな?と考えて資料を作成してください。
  
回答者には、あなたのPCの画面は見えませんから
見えるような説明を心がけていただきたいです。
  
こうすることで、あなた自身で気が付くこともあります。

トピックに返信