[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『横に何セル移動したか求めるには』（おはな）
お世話になります。
自身の問題切り分けのため、
またうまくアウトプットできるように
なるべく端的になるように質問させていただきます。
変な試験みたいな文章になったらごめんなさい。
進捗を記録しているのですが
最終ステータスになったのが
どのタイミングなのかを数えてセルに記載したいです。
ステータスは以下の5項目あります。
準備中 待ち 確認中 OK NG
具体的にいうと
O列からZ列の間で基準のセル(準備中,待ち,確認中)から
横に何セル移動したところに
"OK"または"NG"の文字が来るのかを数えM列に記載したいです。
※その月を含めての結果にしたいため移動数+1の数
↓空白↓ ↓VLOOKUPで別シートから結果を引っ張ってきてます↓
|[L] |[M]|[N]|[O] |[P] |[Q] |[R] |[S] |[T] |[U] |[V] |[W] |[X] |[Y] |[Z]
[2]|研究タスク | | |4月 |5月 |6月 |7月 |8月 |9月 |10月 |11月 |12月 |1月 |2月 |3月
[3]|a | | |準備中 |準備中 |準備中 |準備中 |準備中 |準備中 |準備中 |準備中 |準備中 |準備中 |準備中 |準備中
[4]|b | | |準備中 |準備中 |準備中 |準備中 |準備中 |準備中 |NG |NG |NG |NG |NG |NG
[5]|c | | |準備中 |OK |OK |OK |OK |OK |OK |OK |OK |OK |OK |OK
[6]|d | | |#N/A |準備中 |確認中 |準備中 |#N/A |#N/A |#N/A |#N/A |#N/A |#N/A |#N/A |#N/A
[7]|e | | |#N/A |#N/A |#N/A |準備中 |準備中 |準備中 |準備中 |準備中 |準備中 |準備中 |準備中 |NG
[8]|f | | |#N/A |#N/A |#N/A |準備中 |#N/A |確認中 |確認中 |NG |NG |NG |NG |NG
[9]|g | | |#N/A |#N/A |待ち |待ち |待ち |確認中 |確認中 |確認中 |OK |OK |OK |OK
上記の判断の仕方です。
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
タスクa 4月から始まっていて最終ステータス["OK" / "NG"]になっていないため「12」
タスクb 4月から始まっていて最終ステータス["NG"]に10月になっているため 「7」
タスクc 4月から始まっていて最終ステータス["OK"]に5月になっているため 「2」
タスクd 5月から始まっていて最終ステータス["OK" / "NG"]になっていないため「11」
タスクe 7月から始まっていて最終ステータス["NG"]に3月になっているため「9」
タスクf 7月から始まっていて最終ステータス["NG"]に11月になっているため「5」
タスクg 6月から始まっていて最終ステータス["OK"]に12月になっているため「7」
↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑
だいたいこれが2500行ほどあります。
タスクが増えた分、縦に行が増え、
3月以降の分(4月.5月)と横に列が増えていきます。
行は一月にたくさん増える可能性がありますが
横は一月に1セルずる増えていくため
横への可変は手動で対応するつもりです。
非常に困っているので助けていただけないでしょうか。。。
＜ 使用 Excel：Excel2013、使用 OS：Windows7 ＞
M3=MATCH(Z3,O3:Z3,0)
（通りすがり） 2019/04/02(火) 11:32
#NAに関する条件または抽出結果の表現に工夫できれば入れ子が減るのですが。
（通りすがり） 2019/04/02(火) 11:50
タスクa〜cまでは、「OK」または「NG」が初めて登場する列から求めれば良さそうに見えるのですが、タスクd以降がそれでは全く通じないようです。
判定条件と結果を、もう一度詳細に教えてください。 ルールが判らないと、誰も手助けできませんよ。
書き込みありがとうございます。
判定条件についてもう少し詳細に書いてみようと思います。
|[L] |[M]|[N]|[O] |[P] |[Q] |[R] |[S] |[T] |[U] |[V] |[W] |[X] |[Y] |[Z] [2]|研究タスク | | |4月 |5月 |6月 |7月 |8月 |9月 |10月 |11月 |12月 |1月 |2月 |3月 [6]|d | | |#N/A |準備中 |確認中 |準備中 |#N/A |#N/A |#N/A |#N/A |#N/A |#N/A |#N/A |#N/A
＃N/AはVLOOKUPの検索結果でエラーになっているため
タスクがその月までなかったことを表しています。
4月はそのタスクがなかったが5月から新しく入ったタスクです。
数え初めの判断ワード後に＃N/Aが入ってきた場合にはそのままカウントしています。
数え始めの判断は(準備中 待ち 確認中)のいずれかが表示されたセルを
当月からカウントするため1とし
そこから何行移動したかで数を数えてました。
なのでタスクdは5月のP列から数え初めて、
3月まで、"OK"または"NG"]がないため「11」になります。
セルの動いた数で何ヶ月かかっているかを表してましたので
5月から3月までの11ヶ月かかっているという読み取り方です。
|[L] |[M]|[N]|[O] |[P] |[Q] |[R] |[S] |[T] |[U] |[V] |[W] |[X] |[Y] |[Z] [2]|研究タスク | | |4月 |5月 |6月 |7月 |8月 |9月 |10月 |11月 |12月 |1月 |2月 |3月 [7]|e | | |#N/A |#N/A |#N/A |準備中 |準備中 |準備中 |準備中 |準備中 |準備中 |準備中 |準備中 |NG [8]|f | | |#N/A |#N/A |#N/A |準備中 |#N/A |確認中 |確認中 |NG |NG |NG |NG |NG [9]|g | | |#N/A |#N/A |待ち |待ち |待ち |確認中 |確認中 |確認中 |OK |OK |OK |OK
タスクe タスクfも同様に
7月準備中から数え始めて3月に最終ステータス["NG"]になっているためタスクeは「9」 (7月から3月の9ヶ月)
7月準備中から数え始めて11月に最終ステータス["NG"]になっているためタスクfは「5」 (7月から11月の5ヶ月)
タスクgは
6月待ちから数え始めて12月に最終ステータス["OK"]になっているためタスクgは「7」 (6月から12月の7ヶ月)
うまく説明できているでしょうか。
離席するため返事遅れます(汗)
（おはな） 2019/04/02(火) 11:52
無理やりだけど、これで配列数式として確定でどうでしょう！？ =MIN(IFERROR(IF(FIND(O3:Z3,"OKNG")>0,COLUMN($A:$L)),12))-(MIN(IFERROR(IF(FIND(O3:Z3,"待ち準備中")>0,COLUMN($A:$L),""),""))-1) （稲葉） 2019/04/02(火) 12:05
1
＞MATCH関数で照合の種類を0にしてはどうでしょうか。
＞M3=MATCH(Z3,O3:Z3,0)
2
＞すみません。
＞質問を勘違いしていました。
＞条件判断文をものすごく入れ子にする必要がありそうですね。
＞最終判定がOKならOKの数を数える。
＞最初にOKの文字が出てきた位置を調べる
＞最終判定がNGの場合も同じ。
＞最終判定がOKの場合とNGの場合の場合分け式
＞最終判定がOKでもNGでも無い場合は#NAでなくなる番を数える
＞（UNMATCHなんて関数は無いので頑張って作る）
＞差分を求める。
/
書き込みありがとうございます。
1 上記試したところうまくいかず、、、
タスクaを「11」にしたいのですが
「1」と表示されてしまいました。
その他タスクも同様になっていて、苦戦しています(汗)
2 条件の整理ありがとうございます。
おおよそそんなところであっているはずです。
やはりかなり難しいのですね。
作業セルなども用意できるのでなんとかなればと思いつつ
他の方のご回答も試してみようと思います。
/
★？？？さん★
＞現在の数式を変えて、IFERROR関数で#N/Aならば別の文字とか空欄には変えられませんか？ #N/A が混じると、ちょっと面倒かと思います。
/
書き込みありがとうございます。
IFERROR関数で空欄に変更すること全然可能です！
IFNA関数で処理分岐していたので頭に付け足して空欄になるように処理してみました。
/
/
★稲葉さん★
＞=MIN(IFERROR(IF(FIND(O3:Z3,"OKNG")>0,COLUMN($A:$L)),12))-(MIN(IFERROR(IF(FIND(O3:Z3,"待ち準備中")>0,COLUMN($A:$L),""),""))-1)
/
書き込みありがとうございます。
上記試してみました。
結果が＃VALUEになってしました。。。
A：Kまではタスクに関しての情報やVLOOKUPで引っ掛けるときの検索ワードが入っています。
式の意味をきちんと理解できていなくて
とりあえず念の為 COLUMN($A:$L))をCOLUMN($O:$Z))にしてみましたが
同様にエラーでした。。。。
/
/
/
沢山の人に考えていただけて大変うれしく思います。
引き続きみなさまお力お貸しください、、、
（おはな） 2019/04/02(火) 13:27
配列数式にしました？ Ctrl+Shift+Enterで確定です。 https://www.forguncy.com/blog/20170110_arrayformula （稲葉） 2019/04/02(火) 13:30
＞配列数式にしました？
＞Ctrl+Shift+Enterで確定です。
＞https://www.forguncy.com/blog/20170110_arrayformula
失礼！！
配列数式見逃してました(汗)
配列数式にしてみたところ、
タスクa,ｂ,ｃは「13」
それ以外は「1」と表示されています。
何が起こっているのかイマイチわかってません()
（おはな） 2019/04/02(火) 13:40
こっちの環境だと、最初に提示いただいたデータで問題ないことを確認しています。 ちょっと分解して確認させてください AA3に=MIN(IFERROR(IF(FIND(O3:Z3,"OKNG")>0,COLUMN($A:$L)),12)) AB3に=MIN(IFERROR(IF(FIND(O3:Z3,"待ち準備中")>0,COLUMN($A:$L),""),""))-1 上記いずれも配列数式で AC3に=AA3-AB3
としてみてください。
AA列は O列からOK又はNGが見つかった列までの列数 AB列は O列から準備中又は待ちが見つかった列までの列数マイナス1 AC列は AB列−AA列 としています。
（稲葉） 2019/04/02(火) 13:54
追記 13になってしまう計算を再現するとすれば、 「OK」が「ＯＫ」（全角）とか「OK 」（空白）のようになっていたりしませんか？ （その他文字も準じる）
（稲葉） 2019/04/02(火) 13:57
＞稲葉さん案で良いと思いますね。 なお、稲葉さん案の場合、空欄にせず、元の #N/A のままにしましょう。
＞（私は、文字列の先頭を取り出し、NはOに置換するとかして、Oの位置から準の位置を引くとどうかな…、とか考えてました）
/
なるほど！と思い、関数式をVLOOKUPで
エラーがでるものに直してみたところ無事反映されました！
ありがとうございます。
/
★稲葉さん★
試してみました！
エラーになったのはコピペミスと上記のIFERROR、
それから"確認中"の項目が抜けていたので変な数字になってしまったみたいです。
少し手直しをしたら無事計算されました！！
本当に助かりました！
また何かありましたらよろしくお願いいたします。
/
★書き込みしてくださったみなさま★
本件無事解決です！みなさまありがとうございます。
（おはな） 2019/04/02(火) 14:11
＞無理やりだけど、これで配列数式として確定でどうでしょう！？
＞=MIN(IFERROR(IF(FIND(O3:Z3,"OKNG")>0,COLUMN($A:$L)),12))-(MIN(IFERROR(IF(FIND(O3:Z3,"待ち準備中")>0,COLUMN($A:$L),""),""))-1)
＞（稲葉） 2019/04/02(火) 12:05
＞配列数式にしました？
＞Ctrl+Shift+Enterで確定です。
＞https://www.forguncy.com/blog/20170110_arrayformula
＞（稲葉） 2019/04/02(火) 13:30
＞追記
＞13になってしまう計算を再現するとすれば、
＞「OK」が「ＯＫ」（全角）とか「OK 」（空白）のようになっていたりしませんか？
＞（その他文字も準じる）
＞（稲葉） 2019/04/02(火) 13:57
本当にありがとうございました。
（おはな） 2019/04/02(火) 14:12
あー、エラー値じゃなくてもたぶん空白以外なら何でもいけるから VLOOKUPで持ってくるセル値がエラーの場合は、0とかにして、ゼロ値を非表示にするとかのほうが見栄えが良いかと・・・ O3=IFERROR(VLOOKUP(・・・),0)
???さん フォローありがとうございました。
（稲葉） 2019/04/02(火) 14:26
＞あー、エラー値じゃなくてもたぶん空白以外なら何でもいけるから
＞VLOOKUPで持ってくるセル値がエラーの場合は、0とかにして、ゼロ値を非表示にするとかのほうが見栄えが良いかと・・・
＞O3=IFERROR(VLOOKUP(・・・),0)
/
アフターケアもありがとうございます。
自分で見やすくできるように
IFERRORで0にしたあと、条件付き書式で
0の場合は背景色と同じ色になるようにカスタムしてみました。
このほうが見やすいですね＼(^o^)／
（おはな） 2019/04/02(火) 14:37
セルの書式設定を「0;-0;;@」とするだけでも十分だと思いますが・・・
（稲葉） 2019/04/02(火) 14:46
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.