[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『セルの検索』（りんご）
お世話になります。 表1のように日付、作業項目、進捗率を記入していく表があります。 表2に作業項目を昇順に並べて、完了日を示したいのです。 F列に ･作業項目がE列と一致して進捗率が100%だった場合そのときの日付 ･90%以下だった場合"未完了" ･B列にE列の項目と一致するものが無かった場合"未着手" =if(not(countif(B1:B4,E2)),"未着手","") と値をかえす関数を組みたいのですがなかなかうまくいきません。 lookup関数やmatch関数を組み合わせればできますか??
どなたか詳しいかたご教授ください。(windows XP office2003)
A B C D E F
<表1> <表2>
1 日付 作業 進捗率 作業 完了日
2 2/5 a 50% a 未完了
3 2/6 b 100% b 2/6
4 2/7 d 40% c 未着手
5 d 未完了
着手・未着手の判定は、COUNTIF関数で 完了・未完了の判定は、VLOOKUP関数で 完了の時、日付を参照するのはMATCH関数とINDEX関数を組み合わせて こんな感じの式ではどうでしょう。
F2セルに =IF(E2="","",IF(COUNTIF(B$2:B$5,E2),IF(VLOOKUP(E2,B$2:C$5,2,FALSE)=1, INDEX(A$2:A$5,MATCH(E2,B$2:B$5,0)),"未完了"),"未着手")) （２行で一つの式です。）
(HANA)
HANAさん できました＞＜ 完璧です(*＾_＾*)/ほんとありがとうございました!!
(りんご)
たびたびお世話になります>< =IF(E2="","",IF(COUNTIF(B$2:B$5,E2),IF(VLOOKUP(E2,B$2:C$5,2,FALSE)=1, INDEX(A$2:A$5,MATCH(E2,B$2:B$5,0)),"未完了"),"未着手")) でうまくいっていたんですが、2/5にaの作業が50%のときF2には未完了となりますが、2/8に作業が完了して100%となったときF2は未完了となったままでした。日付が新しいものを参照させるにはどうしたらいいですか?? (りんご)
A B C D E F
<表1> <表2>
1 日付 作業 進捗率 作業 完了日
2 2/5 a 50% a 2/8
3 2/6 b 100% b 2/6
4 2/7 d 40% c 未着手
5 2/8 a 100% d 未完了
えっと・・・こんなので上手く行きますかね。。。 =IF(E2="","",IF(COUNTIF(B$2:B$10,E2),IF(SUMPRODUCT((B$2:B$10=E2)*(C$2:C$10=1)), SUMPRODUCT((B$2:B$10=E2)*($C$2:$C$10=1)*A$2:A$10),"未完了"),"未着手"))
(HANA)
HANAさん できました!!ありがとうございます。(りんご)
何度もすみません＞＜
今度は逆のパターンなんですが。。。
2/5に100%となりその後やり直しが入って2/8に50%となった場合、2/5で完了となったままになります。
日付の一番新しい部分を参照することってできますか？
100%のときは日付で未完了の場合は進捗率を%で表示できればうれしいです＞＜(りんご)
A B C D E F
<表1> <表2>
1 日付 作業 進捗率 作業 完了日
2 2/5 a 100% a 2/5 (ここを50%と表示したい)
3 2/6 b 100% b 2/6
4 2/7 d 40% c 未着手
5 2/8 a 50% d 40%
作業列＆TEXT関数を使います。
G列を作業列にしました。 [A] [B] [C] [D] [E] [F] [G] [1] 日付 作業 進捗率 作業 完了日 作業列 [2] 2月5日 a 100% a 50% 5 [3] 2月6日 b 100% b 2月6日 3 [4] 2月7日 d 40% c 未着手 0 [5] 2月8日 a 50% d 40% 4 F2 =IF(E2="","",IF(G2,IF(INDEX(C$1:C$10,G2)=1,TEXT(INDEX(A$1:A$10,G2),"m月d日"), TEXT(INDEX(C$1:C$10,G2),"0%")),"未着手")) G2 =IF(E2="","",MAX(INDEX((B$1:B$10=E2)*ROW(C$1:C$10),,)))
(HANA)
HANAさん できました！！ 作業列を参照して切り替えてるんですね!勉強になりました。 たびたび面倒な要求すみません＞＜ ほんと助かりました。ありがとうございます。(りんご)
出来ましたか。良かったです。
F列の表示形式を [=0]"未""着""手";[<1]0%;m"月"d"日" にしておけば、F2は =IF(E2="","",IF(G2,IF(INDEX(C$1:C$10,G2)=1,INDEX(A$1:A$10,G2),INDEX(C$1:C$10,G2)),)) で良さそうです。 TEXT関数を使うのが素直だと思いますが。
上記式の場合、作業セルのG2を参照している箇所が４箇所出てきますが この４箇所全てに「MAX(INDEX((B$1:B$10=E2)*ROW(C$1:C$10),,))」を入れると 式が長く成るので、取り出しました。
切り換えるのはやはり、「INDEX(C$1:C$10,G2)=1」この部分の C列の戻り値に依って切り換えています。
着手・未着手の判定は、「G2」 完了・未完了の判定は、「INDEX(C$1:C$10,G2)=1」 日 付の参照は、「INDEX(A$1:A$10,G2)」 進捗率の参照は、「INDEX(C$1:C$10,G2)」 です。 上側の式は、さらに TEXT関数を使って書式を整えています。
(HANA)
HANAさん 説明までっ！！ ほんとありがとうございます。 エクセルって奥が深いですね。。。 長くなると式が複雑になってしまうから書くのが大変だったんですよ～。こんな感じで切り替えるとすっきりしてていいですね＞＜ 初心者なんでなかなか理解するのに時間がかかりますけど他にも応用できるように勉強しようと思います。 表示形式のほうもやってみますね。(りんご)
ほんと何度もすみません＞＜ この式って表が一番上の行じゃないと使えないとかありますか？？
実際のフォームがA1:G10まで別の表があってその下にこの関数をいれた表があるんです。 そこに関数をいれてもうまくいかなくて。。。 A1:G10を削除したらうまくいくんです。(りんご)
A B C D E F G
1 <表1>
2 作業予定 完了予定日 実績 作業No. 作業者 提出先 責任者
3 a～d 2/10 2/8 A11-1 田中 1F倉庫 鈴木
4 e～i 2/25 2/24 B21-4 田中 1F倉庫 鈴木
<表2> <表3>
11 日付 作業 進捗率 作業 完了日 作業列
12 2/5 a 100% a 2/5
13 2/6 b 100% b 2/6
14 2/7 d 40% c 未着手
15 2/8 a 50% d 40%
変更点を簡単に書くと作業列用の式の内
=IF(E12="","",MAX(INDEX((B$11:B$20=E12)*ROW(C$1:C$10),,)))
ここは変えない。~~~~~~~~
です。
B11:B20の範囲を考えた時に、
11行目が 1番目
12行目が 2番目
13行目が 3番目・・・・
と成るようにして下さい。
F12の式の一部は「INDEX(A$11:A$20,G12)」になっていると思いますが
A11:A20の範囲の内、2番目のセルはA12です。
A11:A20の範囲の内、5番目のセルがA15です。
現在は =IF(E12="","",MAX(INDEX((B$11:B$20=E12)*ROW(C$11:C$20),,))) と成っているのではないかと思います。 これでは 11行目が 11番目 12行目が 12番目 13行目が 14番目・・・ と番号が振られてしまいます。
作業セルの式をこのままにするなら 完了日に入れた式を「INDEX(A$1:A$20,G12)」 の様にすれば良いです。 A1:A20の範囲の内、12番目のセルはA12ですから。
ただ、この変更では A1:A10迄は無駄な範囲なので やはり、作業列に入れたROW関数の中を変更するのが良いと思います。
(HANA)
HANAさん まいどまいどありがとうございます＞＜
完了日のほうを IF(E2="","",IF(G2,IF(INDEX(C$1:C$20,G2)=1,TEXT(INDEX(A$1:A$20,G2),"m月d日"), TEXT(INDEX(C$1:C$20,G2),"0%")),"未着手")) と変えてみたらうまくいきました!!
作業列を =IF(E12="","",MAX(INDEX((B$11:B$20=E12)*ROW(C$1:C$20),,))) としてみましたがN#Aが表示されてしまいます。
(りんご)
作業列の方は、 「B$11:B$20」は、10行分参照していて 「ROW(C$1:C$20)」は、20行参照して居ますね。 ROWの中は変えないのなら「ROW(C$1:C$10)」です。 これで、10行分。
「B$11:B$20」の方を20行分参照するなら「B$11:B$40」 ROWの中も20行分に変更する必要が有りますが「ROW(C$1:C$20)」
D11セルに =IF(B11=$E$12,ROW(C1),"") として、15行目までコピー H12セルに =MAX(D11:D15) I12セルに =INDEX(A11:A15,H12)
＞MAX(INDEX((B$11:B$20=E12)*ROW(C$1:C$20),,)) この部分が、D11:D15 と H12 セルに入れた計算をして 返された値をINDEX関数の引数として利用しています。
なので、ROW関数の中は INDEX関数の参照範囲の 先頭から数えての番号と同じ番号が返されるように 設定しておく必要が有ります。
(HANA)
HANAさん いつも詳しくありがとうごさいます。 参照範囲をよく考えて式を組まないといけないんですね～。 なんか関数によっていろいろルールがあるんですね。 もっと勉強します＞＜
(りんご)
そうですね「*ROW(C$1:C$20)」とかやっている式は 仕組みを理解するのに 少し大変だと思います。
この数式って、掛け算が使ってありますよね。 掛け算って事は、掛ける数と掛けられる数があって出来ます。 単純に考えて（数式として機能させるには、少し手が必要ですが） A1*B1 + A2*B2 + A3*B3 + A4*B4 + A5*B5 を計算させようと思ったときに SUM((A1:A5)*(B1:B5))と書いたら計算してくれそうですよね。 それぞれ行毎にA列とB列の二つのセルが対応して、最後に足し算。
この時に前半のセルの範囲と、後半のセルの範囲の個数が違っていたら SUM((A1:A10)*(B1:B5)) 「A6:A10はB列の誰とペアに成れば良いのよ～！！」 って成ります。 成るでしょ？成るんです！！ だから 駄目なんです。（涙）
「SUM((A1:A5)*(B1:B5))」の式をセルにいれて、【Ctrl + Shift + Enter】で確定すると それぞれ行毎にA列とB列の二つのセルを掛け算して、最後に足し算した結果を 返してくれます。 しかし、「SUM((A1:A10)*(B1:B5))」【Ctrl + Shift + Enter】だと「#N/A」が出ます。 ペアになっていないので掛け算出来ない部分があったのです。
今回はINDEX関数の中に入っていますが、中の部分だけを見ると (B$11:B$20の判定)*(行番号)と（複数*複数）の掛け算に成っていますよね。 だから、数をそろえておかないと エラー値が返されます。 （正確には INDEX関数の中に入ってるから ですが。）
数式を【Ctrl + Shift + Enter】で確定すると
数式の前後が大括弧{ }で囲われます。
もしもSUMを使った式が
「行毎の掛け算の足し算をしてくれないんだけど」って場合は
数式バーで 数式の前後が{ }で囲われているか確認して下さい。
(HANA)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.