[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『データ抽出（関数）について』（カルロ）
Excel2002,WindowsXP こんにちわ。何時もお世話になります。 こんな処理を関数でできるかわからないのですが、もしできましたら 教えて下さい。 例えば、 (番地は、飛んでいますが、その途中には項目があるので１つの表になっています)
＜リストシート＞
A B C D E
1 氏名 | 順位 作業1 作業2 日付
-------------------------------------------------------
2 みどり | 1 AA --- 7/1
3 みどり | 2 BB B-1 7/2
4 みどり | 2 BB B-2 7/3
5 みどり | 2 BB B-3 7/4
6 ゆみ | 1 AA --- 7/10
＜表シート＞
A B C D E F
1 | | AA BB BB BB →作業1
2 氏名 | 順位 | --- B-1 B-2 B-3 →作業2
-------------------------------------------------------
3 みどり | 1 | 7/1
4 みどり | 2 | 7/2 7/3 7/4
5 ゆみ | 1 | 7/10
リストシートのデータを参照して表シートの該当欄に日付を抽出する。 関数を利用して日付を抽出することができますか？ やはりマクロでなくてはできませでんか？ 教えて下さい。
表シートのC3からF5を選択してセルの書式設定の「表示形式」で「分類」に「ユーザー定義」、「種類」に「m/d;;;」と設定しておきます。
表シートのC3セルに =SUMPRODUCT((リストシート!$A$2:$A$6=$A3)*(リストシート!$B$2:$B$6=$B3)*(リストシート!$C$2:$C$6=C$1)*(リストシート!$D$2:$D$6=C$2),リストシート!$E$2:$E$6) と入力して右および下へコピー、でどうでしょうか？
ただし、該当無しの場所は空セルのように見えますが実際は「0」が入っています。 「表示形式」で「0」を見えなくしているだけなのでその点はお気をつけを。 (独覚)
しっかり被ってますがせっかく書いたので そのまま載せたい！！
それぞれの組み合わせの日付が一つしかないのなら 「表」シートのC3セルに =SUMPRODUCT((リスト!$A$1:$A$10=$A3)*(リスト!$B$1:$B$10=$B3)*(リスト!$C$1:$C$10=C$1)*(リスト!$D$1:$D$10=C$2),リスト!$E$1:$E$10) 表示形式を「m/d;;;」
SUMPRODUCT関数についてはこちらをご参考に。 http://www.excel.studio-kazu.jp/lib/e3h/e3h.html
(HANA)
独覚さん、HANAさん回答有難うございます。 こんな関数があったのですね。すごい。ホント感動しました。 すみません。もう１つ教えて下さい。 表シートに日付を抽出する場合の条件で、リストシートの順位１の場合は、作業１の名前を参照する リストシートの順位２の場合は、作業２の名前を参照する。というようにすると計算式は、どのように 変えればよいのですか？教えて下さい（カルロ）
こういうことかな? 順位に「1」「2」以外はないものとして。
=SUMPRODUCT((リストシート!$A$2:$A$6=$A3)*(リストシート!$B$2:$B$6=$B3)*IF($B3="1",リストシート!$C$2:$C$6=C$1,リストシート!$D$2:$D$6=C$2),リストシート!$E$2:$E$6) (独覚)
独覚さん回答有難うござます。 Ｃ３セルに上記関数を入力したら、０（ゼロ）が表示されました。 どうしたらよいのでしょうか？（カルロ）
失礼しました。
=SUMPRODUCT((リストシート!$A$2:$A$6=$A3)*(リストシート!$B$2:$B$6=$B3)*IF($B3="1",INDEX(リストシート!$C$2:$C$6=C$1,0),INDEX(リストシート!$D$2:$D$6=C$2,0)),リストシート!$E$2:$E$6) で試してみてください。 (独覚)
独覚さん回答有難うござます。 今度は、バッチリでした。 みんな すごいですね。尊敬します。有難うございました。 今後共 よろしくお願いします（カルロ）
すみません。今 SUMPRODUCT関数を教えていただき入力したのですが、 日付を抽出できているところとできていないところが発生しました。 条件の文字とリストの文字は、コピーして同一なのですが？？？ リストシートのデータ数は、100件ほどです。 何故でしょうか？（カルロ）
日付のほうは全て日付データでしょうか？ 文字列になっている箇所はありませんか？ (独覚）
独覚さん回答有難うございます。 今 確認しましたら日付が文字列になっていたようです。 "2008/7/1"：表示形式は、日付となっていましたが、一旦 日付をクリア して再度 入力したらＯＫでした。何故でしょう？ 文字列の日付を日付に訂正するには、どのような関数を利用したらよいのでしょうか？ 何度も申し訳ありません。よろしくお願いします（カルロ）
ほかからコピーしてきたりした場合、セルの表示形式にかかわらず文字列になってしまう場合があります。
で、日付の列を選択して「データ」―「区切り位置」を選択してそのまま「完了」を押してみてください。 これで文字列から日付データに変わります。 (独覚)
独覚さん回答有難うございます。 おっしゃるとうり実行したらバッチリ日付が反映しました。 ホントにすごいですね。尊敬します。 有難うございます。今後共 よろしくお願いします（カルロ）
すみません。もう１つ教えて下さい。 SUMPRODUCT関数を利用して日付を抽出できるようになりましたが、 空白に見えるセルにも日付(1900/1/0)が入っています。 実は、"日付の入っているセル"で"黄色セル"のみ何件あるかカウントしたら 全てのセルに日付が入っているので空白（黄色セルのみ）に見えるセルまで数えてしまいます。 どうしたらよいのでしょうか？（カルロ）
>実は、"日付の入っているセル"で"黄色セル"のみ何件あるかカウントしたら これがどうやっているのか判りませんが条件を"日付の入っているセル"ではなく"0でないセル"にしてみてはどうでしょうか? （「1900/1/0」=「0」です） (独覚)
独覚さんいつも有難うございます。
Function 色付文字入力セル(adrs As Range)
Dim c As Range, Cnt As Long
Application.Volatile '自動再計算
For Each c In adrs
If c.Interior.ColorIndex = 6 And IsDate(c) Then '黄色セル＋日付 件数
Cnt = Cnt + 1
End If
Next c
色付文字入力セル = Cnt
End Function
>If c.Interior.ColorIndex = 6 And IsDate(c) Then '黄色セル＋日付 件数 0でないセル： ここのところ(IsDate(c))をどのように書き換えたらよいのでしょうか？(カルロ)
If c.Interior.ColorIndex = 6 And c.Value <> 0 And IsDate(c) Then '黄色セル＋日付(Not 0) 件数
でどうでしょうか？
(独覚)
独覚さん回答有難うございました。 バッチリＯＫです。すごいです(^_^)(^^♪ いつも有難うございます。 今後共 よろしくお願いします（カルロ）
すみません。もう少し教えて下さい。 リストシートのE列に日付だけではなく、文字も入っているとしたら どのような関数がよいのでしょうか？ もし、わかりましたら教えて下さい（カルロ）
＜リストシート＞
A B C D E
1 氏名 | 順位 作業1 作業2 日付
-------------------------------------------------------
2 みどり | 1 AA --- 7/1
3 みどり | 2 BB B-1
4 みどり | 2 BB B-2 バナナ
5 みどり | 2 BB B-3 7/4
6 ゆみ | 1 AA --- リンゴ
＜表シート＞
A B C D E F
1 | | AA BB BB BB →作業1
2 氏名 | 順位 | --- B-1 B-2 B-3 →作業2
-------------------------------------------------------
3 みどり | 1 | 7/1 1900/1/0
4 みどり | 2 | 7/2 バナナ 7/4
5 ゆみ | 1 | リンゴ
C3:F5の「表示形式」は「m/d」で。 もっと、簡単な式がありそうですが…
=IF(SUMPRODUCT((リスト!$A$2:$A$6=$A3)*(リスト!$B$2:$B$6=$B3)*IF($B3-1=0,INDEX(リスト!$C$2:$C$6=C$1,0),INDEX(リスト!$D$2:$D$6=C$2,0))),INDEX(リスト!$E$2:$E$6,MATCH($A3&$B3&IF($B3-1=0,C$1,C$2),INDEX(リスト!$A$2:$A$6&リスト!$B$2:$B$6&IF($B3-1=0,リスト!$C$2:$C$6,リスト!$D$2:$D$6),0),0),),"") (独覚)
独覚さん回答有難うございます。 教えて頂いたとおり関数を入力したらＯＫでした。 ホントすごいですね。私なんか足元にも及びません。 テストで入力した時は、ＯＫなのですが、実際に表に入力して複写すると 全て空白になってしまいます。もし、原因がわかりましたら教えて下さい。 よろしくお願いします（カルロ）
エラーではなく空白になるということはリストシートと表シートで対応するデータが異なっている、ということだと思いますが…
たとえば氏名や作業1、作業2の前後にスペースがあったりとか順位が数値と文字列というように異なっていたりとか。 (独覚)
回答有難うございます。リストデータをTRIM関数で空白を除いたら ちゃんと表示できました。ご教授有難うございます。 ついでに教えて下さい。この関数をマクロ記録しようとしても長すぎる為か？ 記録できません。いつもは、リストデータの最終位置は、毎回変化するので関数中の 最終行は、マクロ上で変数を入力します。 マクロ記録できないときは、関数中の最終行を余分にとりシートのどこかに複写しておいて それを表へ複写して利用する。という方法がよいのでしょうか？ それとも他に良い方法があれば教えて下さい。よろしくお願いします（カルロ）
えっと、基本的に数式はセルに入れておく物だと思うのですが 事前に範囲を広くとっておくと重くなる・・・とか言うことですかね？
でも、どこかに複写して於いて 表へ複写して利用する と言う方法も 範囲が広くとってあって同じだけの計算が必要 って事なんですよね？
(HANA)
HANAさん回答有難うございます。 >どこかに複写して於いて 表へ複写して利用する そのようにします。有難うございました（カルロ）
ちょっと困ったことが。。。 独覚さんの教えて頂いた計算式を入力するとバッチシＯＫなのですが、 リストシートのE3が空白だった場合、表シートのD3は、"1900/1/0"が 表示されてしまいます（「表示形式」は「m/d」） リストシートのE列に空白があれば表シートの該当するセルにも空白を表示したいのです。 それで、ツール→オプション→表示タブ→ゼロ値(Z)のチェックを外したら、"1900/1/0" のセルは、空白になったのですが、同じシート内に％を計算する計算式があって０％に なったら空白になってしまいます。０％は、表示したいのです。 私のレベルでは、どうしてよいのか？わかりません。 知恵を貸して下さい。よろしくお願いします（カルロ）
こんにちは〜♪
横から失礼しま〜す。。。
＜表シート＞ ↓
┌─┬────┬────┬────┬────┬────┬────┐ │ │ A │ B │ C │ D │ E │ F │ ├─┼────┼────┼────┼────┼────┼────┤ │ 1│ │ │AA │BB │BB │BB │ ├─┼────┼────┼────┼────┼────┼────┤ │ 2│氏名 │順位 │--- │B-1 │B-2 │B-3 │ ├─┼────┼────┼────┼────┼────┼────┤ │ 3│みどり │ 1│ 7月1日│ │ │ │ ├─┼────┼────┼────┼────┼────┼────┤ │ 4│みどり │ 2│ │ │バナナ │ 7月4日│ ├─┼────┼────┼────┼────┼────┼────┤ │ 5│ゆみ │ 1│リンゴ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┤ │ 6│ │ │ │ │ │ │ └─┴────┴────┴────┴────┴────┴────┘
C3セルへ
=IF(COUNTA($A3:$B3)<2,"",INDEX(リスト!$E:$E,LOOKUP(10^7,IF({1,0},10^4,INDEX(MATCH($A3&$B3&
INDEX(C$1:C$2,$B3),リスト!$A$1:$A$10&リスト!$B$1:$B$10&CHOOSE($B3,リスト!$C$1:$C$10,リスト!$D$1:$D$10),0),)))))
右と下へコピーです。。。
★リストシートのシート名は→ リスト! になってます。。
式を入れた範囲の書式設定は
ユーザー定義で ↓
m"月"d"日";;;@
としてください。。。
>表シートのD3は、"1900/1/0"が >表示されてしまいます（「表示形式」は「m/d」）
途中省略。。。
>同じシート内に％を計算する計算式があって０％に >なったら空白になってしまいます。０％は、表示したいのです。
独覚さんの式の場合も 私と同じ書式設定にすれば大丈夫ですネ！！。。。
ご参考にどうぞ。。。
。。。Ms.Rin〜♪♪
Ms.Rinさん回答有り難うございます。
なんか。すごいですね。私にはどうしてあの関数が該当データを抽出
できるのか？よくわかりません。でも、ちゃんと回答になっています。
どんな頭脳をしているのですか？
>LOOKUP(10^7,IF({1,0},10^4 ですが、
"10~7" や "{1.0}" や "10^4"はどんな意味ですか？
もし良かったら教えて下さい。
今後ともよろしくお願いします。(カルロ）
もう１つ教えて下さい。順位が1,2,3-1,3-2,3-3(文字列)となった場合 は、表シートには、どのような関数を入力すればよいのでしょうか？
>=IF(COUNTA($A3:$B3)<2,"",INDEX(リスト!$E:$E,LOOKUP(10^7,IF({1,0},10^4,INDEX(MATCH($A3&$B3&
>INDEX(C$1:C$2,$B3),リスト!$A$1:$A$10&リスト!$B$1:$B$10&CHOOSE($B3,リスト!$C$1:$C$10,リスト! $D$1:$D$10),0),)))))
教えて頂いた関数を入力しましたがうまくゆきません（カルロ）
＊順位1は、"作業１"名を参照する。順位2,3-1,3-2,3-3は、"作業２"名を参照する。
＜リストシート＞ ↓ ┌─┬────┬────┬────┬────┬────┬────┐ │ │ A │ B │ C │ D │ E │ F │ ├─┼────┼────┼────┼────┼────┼────┤ │ 1│ 氏名 │順位 │作業１ │作業２ │日付 │ │ ├─┼────┼────┼────┼────┼────┼────┤ │ 2│みどり │ '1 │AA │'--- │2008/7/1│ │ ├─┼────┼────┼────┼────┼────┼────┤ │ 3│みどり │ '3-3 │BB │ BB-3-3 │ │ │ ├─┼────┼────┼────┼────┼────┼────┤ │ 4│みどり │ '3-1 │BB │ BB-3-1 │バナナ │ │ ├─┼────┼────┼────┼────┼────┼────┤ │ 5│ゆみ │ '1 │AA │ '--- │リンゴ │ │ ├─┼────┼────┼────┼────┼────┼────┤ │ 6│ みどり '3-2 │BB │ BB-3-2 │2008/7/7│ │ └─┴────┴────┴────┴────┴────┴────┘ │ 7│ みどり '2 │BB │ BB-2 │2008/7/2│ │ └─┴────┴────┴────┴────┴────┴────┘
＜表シート＞ ↓ ┌─┬────┬────┬────┬────┬────┬────┐────┐ │ │ A │ B │ C │ D │ E │ F │ G │ ├─┼────┼────┼────┼────┼────┼────┤────┤ │ 1│ │ │AA │BB │BB │BB │BB │ ├─┼────┼────┼────┼────┼────┼────┤────┤ │ 2│氏名 │順位 │--- │BB-2 │BB-3-1 │BB-3-2 │BB-3-3 │ ├─┼────┼────┼────┼────┼────┼────┤────┤ │ 3│みどり │ '1 │2008/7/1│ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┤────┤ │ 4│みどり │ '3-3 │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┤────┤ │ 5│ゆみ │ '1 │リンゴ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┤────┤ │ 6│みどり │ '2 │ │2008/7/2│ │ │ │ └─┴────┴────┴────┴────┴────┴────┘────┘ │ 7│みどり │ '3-2 │ │ │ │2008/7/7│ │ ├─┼────┼────┼────┼────┼────┼────┤────┤ │ 8│みどり │ '3-1 │ │ │バナナ │ │ │ ├─┼────┼────┼────┼────┼────┼────┤────┤
こんにちは〜♪
>もう１つ教えて下さい。順位が1,2,3-1,3-2,3-3(文字列)となった場合
>＊順位1は、"作業１"名を参照する。順位2,3-1,3-2,3-3は、"作業２"名を参照する。
のご説明なら。。。
＜表シート＞の
C3セルへ
=IF(COUNTA($A3:$B3)<2,"",INDEX(リスト!$E:$E,LOOKUP(10^7,IF({1,0},10^4,INDEX(MATCH($A3&$B3&
INDEX(C$1:C$2,(LEFT($B3)*1>1)+1),リスト!$A$1:$A$10&リスト!$B$1:$B$10&CHOOSE((LEFT($B3)*1>1)+1,リスト!$C$1:$C$10,リスト!$D$1:$D$10),0),)))))
右と下へコピーで良さそうです。。。
それから、きょうは時間がありませんので 式の説明は又、改めてレスします。。ゴメンナサイ！！
。。。Ms.Rin〜♪
Ms.Rinさん回答有難うございます。 上記 関数を試してみましたらバッチシＯＫでした。(^^♪
今、実際のデータに適用してみましたが、 日付は抽出するのですが、適切な場所に値を抽出していません。 名前、順位に対する日付が対応しません。 何処が悪いのでしょうか？教えて頂いた関数にある番地を変えて同じように 範囲指定してみたのですが？該当データをうまく抽出できません。何故でしょうか？
実際の表は、 ＜表シート＞ ↓ ┌─┬────┬────┬────┬────┬────┬────┐────┐ │ │ L │ M │ N │ S │ T │ U │ V │ ├─┼────┼────┼────┼────┼────┼────┤────┤ │17│ │ │ │AA │BB │BB │BB │ ├─┼────┼────┼────┼────┼────┼────┤────┤ │18│氏名 │ 班 │順位 │--- │BB-2 │BB-3-1 │BB-3-2 │ ├─┼────┼────┼────┼────┼────┼────┤────┤ │19│みどり │ '2 │ '1 │2008/7/1│ │ │ │
＜リストシート＞ ↓ ┌─┬────┬────┬────┬────┬────┬────┐ │ │ BF │ BH │ BJ │ BK │ BN │ F │ ├─┼────┼────┼────┼────┼────┼────┤ │15│ 氏名 │順位 │作業１ │作業２ │日付 │ │ ├─┼────┼────┼────┼────┼────┼────┤ │16│みどり │ '1 │AA │'--- │2008/7/1│ │ ├─┼────┼────┼────┼────┼────┼────┤
こんばんは〜♪
リストシートも表シートも、列が連続じゃなくて 間隔が空いてるんですね。。。
表シート ↓
┌─┬────┬───┬────┬──┬──┬──┬──┬────┬────┬────┬────┬────┐ │ │ L │ M │ N │ O │ P │ Q │ R │ S │ T │ U │ V │ W │ ├─┼────┼───┼────┼──┼──┼──┼──┼────┼────┼────┼────┼────┤ │17│ │ │ │ │ │ │ │AA │BB │BB │BB │BB │ ├─┼────┼───┼────┼──┼──┼──┼──┼────┼────┼────┼────┼────┤ │18│氏名 │班 │順位 │ │ │ │ │--- │BB-2 │BB-3-1 │BB-3-2 │BB-3-3 │ ├─┼────┼───┼────┼──┼──┼──┼──┼────┼────┼────┼────┼────┤ │19│みどり │ │ 1│ │ │ │ │ 7月1日│ │ │ │ │ ├─┼────┼───┼────┼──┼──┼──┼──┼────┼────┼────┼────┼────┤ │20│みどり │ │ 3-3│ │ │ │ │ │ │ │ │ │ ├─┼────┼───┼────┼──┼──┼──┼──┼────┼────┼────┼────┼────┤ │21│ゆみ │ │ 1│ │ │ │ │リンゴ │ │ │ │ │ ├─┼────┼───┼────┼──┼──┼──┼──┼────┼────┼────┼────┼────┤ │22│みどり │ │ 2│ │ │ │ │ │ 7月2日│ │ │ │ ├─┼────┼───┼────┼──┼──┼──┼──┼────┼────┼────┼────┼────┤ │23│みどり │ │ 3-2│ │ │ │ │ │ │ │ 7月7日│ │ ├─┼────┼───┼────┼──┼──┼──┼──┼────┼────┼────┼────┼────┤ │24│みどり │ │ 3-1│ │ │ │ │ │ │バナナ │ │ │ └─┴────┴───┴────┴──┴──┴──┴──┴────┴────┴────┴────┴────┘
S19セルへ
=IF(COUNTA($L19,$N19)<2,"",INDEX(リスト!$BN:$BN,LOOKUP(10^7,IF({1,0},10^4,INDEX(MATCH($L19&$N19&INDEX(S$17:S$18,
(LEFT($N19)*1>1)+1),リスト!$BF$16:$BF$100&リスト!$BH$16:$BH$100&CHOOSE((LEFT($N19)*1>1)+1,リスト!$BJ$16:$BJ$100,リスト!$BK$16:$BK$100),0)+15,)))))
右と下へコピーです。。。
。。。Ms.Rin〜♪♪
Ms.Rinさん回答有り難うございます。 >リストシートも表シートも、列が連続じゃなくて >間隔が空いてるんですね。。。 間隔は空いていません。項目とデータが入っています。 今すぐ確認したいけど会社にデータがあるので、お盆休み明けに 確認します。お手数をお掛けして申し訳ありません。 今後ともよろしくお願いします（カルロ）
Ms.Rinさん回答有り難うございます。 今日 上記 関数を入力してみたらバッチシでした(^_^) 有難うございました。今後共 よろしくお願いします（カルロ）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.