[[20050217153128]]　『VLOOKUP関数』（初級）　ページの最後に飛ぶ

『VLOOKUP関数』（初級）

　Sheet1に計画表を作り、Sheet2に点呼簿を作りました。

　計画表には、社長・・・公休　と入れますが、点呼簿には、公休以外の者の
　名前だけを表示したいのですが？
　
　=IF(COUNTIF($G$5:$H$26,$AJ5),VLOOKUP($AJ5,$G$5:$S$26,3,FALSE),IF(COUNTIF($V$5:$V$26,$AJ5),VLOOKUP($AJ5,$V$5:$AF$26,2,FALSE),""))

　とすると、公休の人の名前も表示されてしまいます。
　どう訂正したら良いですか？？

 さぁ、、残るは初級さんだけやね。。
 でも、わかりません。
 >社長・・・公休
 これはどういう意味？？
シートは二枚ある様だけど、式にシートの指定はないみたいだし。。。
もう少し詳しくかいてくださいませんか？
=IF(COUNTIF($G$5:$H$26,$AJ5),VLOOKUP($AJ5,$G$5:$S$26,3,FALSE),
IF(COUNTIF($V$5:$V$26,$AJ5),VLOOKUP($AJ5,$V$5:$AF$26,2,FALSE),""))
（SoulMan）

★衝突しました。

 式は具体的にあげていただいているのですが、
・シート１とシート２の関連性が判らない
・式に入っている各範囲の内容が判らない
ため、エラーの原因（なぜ公休の人が表示されるのか）にたどり着きません・・・
参照個所が数箇所ありますので、それぞれ何が入っているのかを教えてください。
(shouta)

 すみません・・・雑な説明で・・・
 Sheet1は毎日の計画表を作ります。
 　A　　　B　　　　C　　　　D　　　　　E　　　　F　　　　　G　　H　　　I　　～
 名前　　車番　　行き先　開始時刻　終了時刻　作業内容　　名前　車番　行き先　
 田中　　248　　　岡山　　　　　　　　　
  と50人分ほど入力していきます。（A3サイズの決まった様式です）
 Sheet2には、点呼簿があって
 　A　　　　B　　　　C　　　　　　D　
 名前　　　車番　　作業内容　　乗務点呼　　とあります。
 田中　　　248　　　岡山　　
　
 Sheet1で入力した"公休”以外の内容を表示したいのですが・・・
 Sheet1のMに、
=IF(COUNTIF($A$3:$F$26,$N3),VLOOKUP($N3,$A$3:$D$26,2,FALSE),IF(COUNTIF($G$5:$M$26,$N3),VLOOKUP($N3,$G$5:$M$26,2,FALSE),""))
と同じくSheet2に表示したい項目のBとFを纏めました。

 Mの列に　=IF(OR($Q3="公休",$Q5="年休"),"",1))))
として　Sheet2のAに
=IF(COUNTIF('計画表'!M:M,1),INDEX('計画表'!$A:$A,SMALL(IF(COUNTIF('計画表'!M:M,1),ROW('計画表'!$A$5:$A$50)),ROW(A1))))
ってしてみたけれど・・・全部表示されるので
 頭が？？？になってしまいました。
 関数が解ってないので、すみません。
　
 すみません・・・雑な説明で・・・
Sheet1は毎日の計画表を作ります。
　A　　　B　　　　C　　　　D　　　　　E　　　　F　　　　　G　　H　　　I　　～
名前　　車番　　行き先　開始時刻　終了時刻　作業内容　　名前　車番　行き先　
田中　　248　　　岡山　　　　　　　　　
と50人分ほど入力していきます。（A3サイズの決まった様式です）
Sheet2には、点呼簿があって
　A　　　　B　　　　C　　　　　　D　
名前　　　車番　　作業内容　　乗務点呼　　とあります。
田中　　　248　　　岡山　　　
Sheet1で入力した"公休”以外の内容を表示したいのですが・・・
Sheet1のMに、
=IF(COUNTIF($A$3:$F$26,$N3),VLOOKUP($N3,$A$3:$D$26,2,FALSE),IF(COUNTIF($G$5:$M$26,$N3),VLOOKUP($N3,$G$5:$M$26,2,FALSE),""))
と同じくSheet2に表示したい項目のBとFを纏めました。
Mの列に　=IF(OR($Q3="公休",$Q5="年休"),"",1))))
として　Sheet2のAに　
=IF(COUNTIF('計画表 (木)'!AI:AI,1),INDEX('計画表 (木)'!$AJ:$AJ,SMALL(IF(COUNTIF('計画表 (木)'!AI:AI,1),ROW('計画表 (木)'!$AJ$5:$AJ$50)),ROW(A1))))　
ってしてみたけれど・・・全部表示されるので
頭が？？？になってしまいました。
関数が解ってないので、すみません。
ちょと修正したけど、あってるかな？？
（SoulMan）

 すいません。Sheet2のAの式は
=IF(COUNTIF('計画表'!M:M,1),INDEX('計画表'!$A:$A,SMALL(IF(COUNTIF('計画表'!M:M,1),ROW('計画表'!$A$5:$A$50)),ROW(A1))))
です！
=IF(COUNTIF('計画表 (木)'!AI:AI,1),INDEX('計画表 (木)'!$AJ:$AJ,SMALL(IF(COUNTIF('計画表 (木)'!AI:AI,1),ROW('計画表 (木)'!$AJ$5:$AJ$50)),ROW(A1))))
この式では、訳わかんないですよね・・・

 それで、「公休」というのは何列目に入れるの？？
http://ryusendo.no-ip.com/cgi-bin/upload/src/up0242.xls
独断と偏見で作ってみました。
A列の「公休」以外をSheet2に抽出します。どうでしょうか？
=IF(SUM((Sheet1!A2:A27<>"公休")*1)<ROW(A1:A26),"",
INDEX(Sheet1!A2:I27,SMALL(IF(Sheet1!A2:A27<>"公休",ROW(A1:A26)),ROW(A1:A26)),COLUMN(A2:I27)))
すみません。サンプルの式は直しませんが、↑が正解です。
（SoulMan）

 見づらかったので整形だけでも、っと思ったらSoulManさんが、修正を書いてたのね。
アハハ、ダブってるけどそのままにしときます。

 初級さんの書き込みで気になった点だけ書いておきます。
 >SMALL(IF(COUNTIF('計画表'!M:M,1),ROW('計画表'!$A$5:$A$50)),ROW(A1))
だと、計画表シートのM列に1が１個でもあれば、行No.を返す。となってます。
このため、全て表示されているのでは？
例えば、　SMALL(IF('計画表'!$M$5:$M$50=1,ROW('計画表'!$A$5:$A$50)),ROW(A1))
の様にすれば、M5:M50のセルが、1 のものだけ　行No.が返されます。
 >Mの列に　=IF(OR($Q3="公休",$Q5="年休"),"",1))))
Q3とQ5 って行が違いますが、一緒にしても良いのでしょうか？？？
ここが、一番の疑問点です。
あと、計画表のA列とG列の名前部分が、同一の人を表示するのならば、行き・帰り　等の使い方かな？
と、まだ納得(A列だけで良いのでは、と思いますが)できるのですが、
違う人が入るとするならば、M列の扱い方が？？？です。
何の解決にもならないと思いますが、整形のご報告+α　でした。(sin)

皆様、有難うございます。今日は時間が無いので明日ゆっくり見させていただきます。

 挑戦してはいるんですけど・・・？？？
まず
=IF(SUM((Sheet1!A2:A27<>"公休")*1)<ROW(A1:A26),"",
INDEX(Sheet1!A2:I27,SMALL(IF(Sheet1!A2:A27<>"公休",ROW(A1:A26)),ROW(A1:A26)),COLUMN(A2:I27)))
の内容が解らなくて・・・*1は何の為に？返す数字は、どうして一段変えてるの？
COLUMNってなんですか？エクセルのヘルプで探したけど無くて・・・
教えてください。

 SUM((Sheet1!A2:A27<>"公休")*1)
 これは、シート1のA列の公休じゃない数を数えてるんですね。
 <ROW(A1:A26),"",
 公休じゃない数より行が大きく（データがふえたら）なったら、空白にしなさい。
 といういみですね。
 COLUMN
 は列です。
 COLUMN(A2:I27)
 とすると、　
 {1,2,3,4,5,6,7,8,9}
 こんな配列が返るので私はよく使います。
 どうでしょうか？
一度↓ここをご覧になるのがよろしいかと思います。
http://pc21.nikkeibp.co.jp/special/hr/
（SoulMan）

 ご説明を有難うございました。何となく解る様な・・・
でも、公休を入力するのは　F列の作業内容の所です。
なので、F列でSheet1!F2:F27<>"公休")*1　にしたのですが、エラーが表示されます。

 Sheet2のA2からF30までを選択した状態で数式バーに
=IF(SUM((Sheet1!F2:F30<>"公休")*1)<ROW(A1:A29),"",
INDEX(Sheet1!A2:I30,SMALL(IF(Sheet1!F2:F30<>"公休",ROW(A1:A29)),ROW(A1:A29)),COLUMN(A1:I27)))
と入力してCtrlキーとShiftキーを押しながらEnterキーで確定です。
どうでしょうか？
（SoulMan）

 いえエラー表示が出ます。
ROW(A1:A29)とありますが、セルを結合してるから？？関係ないですよね・・・

 結合？
元のデータにですか？転記先にですか？
元を結合しても「0」になるだけでしたけど、、転記先に結合セルがあると
入力自体が出来なかったですね。でも、以前は配列で表示出来ていたんでしょ？
（SoulMan）

 元のデータに計算式が入ってると駄目なのでしょうか？
結合を解いてもエラー表示　#N/A　なんです。以前は配列で表示出来てました。

 INDEXでみてますから関係ないと思います。
元のデータの番地と転記先の番地を教えてください。
（SoulMan）

 元のデータは、計画表の入力が1～5列で一段落として3段落ぐらいあるので、
別に　
=IF(COUNTIF($A$3:$F$26,$N3),VLOOKUP($N3,$A$3:$D$26,2,FALSE),IF(COUNTIF($G$5:$M$26,$N3),VLOOKUP($N3,$G$5:$M$26,2,FALSE),""))
と同じくSheet2に表示したい項目を元データのAJ～AO列に纏めてます。
それをSheet2のA13～D42,E13～G42,H13～N42（列は結合）と項目別に表示したいのです。

 すみません。私は、あくまで例を提示しているのです。
 >AJ～AO列に纏めてます。
 これは5列ですよね？
 >A13～D42,E13～G42,H13～N42（列は結合）と項目別に表示したいのです。
 これは14列ですよね？？
 私が提示したサンプルはご覧になりましたか？
http://ryusendo.no-ip.com/cgi-bin/upload/src/up0242.xls
 応用できませんか？
（SoulMan）

　すみません。サンプルは見ました。
　=IF(SUM(('計画表 (木)'!AM5:AM44<>"")*1)<ROW(A13:A42),"",INDEX('計画表 (木)'!AJ5:AO44,SMALL(IF('計画表 (木)'!AM5:AM44<>"",ROW(A13:A42),ROW(A13:A42)),COLUMN('計画表 (木)'!AJ5:AO44))))
　で、配列にしましたが#N/Aとなりました。

 配列という概念を少し誤解されている様です。
INDEXは実際の行や列番号ではなくて
配列の何番目か？なのです。
だから、元のデータがSheet1の
Sheet1!AJ5:AO44
にあるとして
表示したいシートの5列×40段の範囲を選択した状態で
数式バーに
=IF(SUM((Sheet1!AM5:AM44<>"")*1)<ROW(A1:A40),"",
INDEX(Sheet1!AJ5:AO44,SMALL(IF(Sheet1!AM5:AM44<>"",ROW(A1:A40)),ROW(A1:A40)),COLUMN(A1:F40)))
と入力してCtrl+Shift+Enterで確定します。
この式の意味は、Sheet1のAM5からAM44の間の空白ではない行に一致する
データを抽出するものです。
どうでしょうか？
http://ryusendo.no-ip.com/cgi-bin/upload/src/up0244.xls
簡単なサンプルを作ってみましたので、お確かめ下さい。
わちゃ、自分でいといて式を間違ってますね。（￣□￣；）！！ごめんなさい。m(__)m
=IF(SUM((Sheet1!AM5:AM44<>"")*1)<ROW(A1:A40),"",
INDEX(Sheet1!AJ5:AO44,SMALL(IF(Sheet1!AM5:AM44<>"",ROW(A1:A40)),ROW(A1:A40)),COLUMN(A1:E40)))
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　↑ここ
誠に申し訳ありません。もう修正しませんが
40段
ROW(A1:A40)
5列
COLUMN(A1:E40)
40段
ROW(A1:A40)
6列
COLUMN(A1:F40)))
ということです。。ではでは、
（SoulMan）

　長々とすみませんでした。サンプル有難うございました。
　解りやすくて理解できました。
　

コメント返信：

[ 一覧(最新更新順) ]

[[20050217153128]] 『VLOOKUP関数』（初級） ページの最後に飛ぶ

[[20050217153128]]　『VLOOKUP関数』（初級）　ページの最後に飛ぶ