[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『データー入力表から集計表への振り分け』（MAKO）
excel2003 XP
いつも勉強させていただいています。 過去の同様の質問&回答でいろいろ調べてみたのですけど、 どうも理解に及ばず、有識の皆様にお助けいただければ、と思いました。 運用方も含めてアドバイスをお願いいたします。
まず、したいことですが、セールスターゲットリスト(詳細情報有りで見積り依頼の状況と詳細情報の確認）と、 担当ごとのセールス状況表（会社名、収入、ﾀｲﾌﾟ）のリンクです。 現在、担当者ごとに、①ターゲットリストと②報告用シートを別々に入力して、 セールス状況のランクが変わる度に報告シートの位置をコピペして変更しているのですが、 構面倒なので皆の更新が遅々として進みません。 ちなみに、セールス状況リストから、更に数字のみをリンクして（担当者）全体のランクごと、タイプごとの集計表を作っています。
以下のような表にて、ターゲットリストに入力すると状況表にもリンクされて、 ランクを変更すると、状況表の表示位置も変わる･･･ということが出来ないかと、思っています。 （ﾗﾝｸは獲得までの可能性 D＝ターゲット、A＝決定） （ﾀｲﾌﾟは商品名とは関係なく、データーを取るためのものです。）
①ターゲットリスト
A B C D E F G H I j
5 入力日 担当 納品日 会社名 備考 商品名 数 収入 ﾗﾝｸ ﾀｲﾌﾟ･･･
6 7/15 相田 1/22 ○×商事 営業２課 バナナ 10 100 A M 7 7/21 井本 2/25 △△会社 式典用 イチゴ 30 1000 D E 8 8/ 5 上田 1/15 □▽会社 橋本氏 メロン 200 500 C M 9 8/10 相田 3/20 ××(株) 総務課 スイカ 40 800 B E ･ ･ ･ ･ ･ ･ ･ ･ ･ ･ ･ ･ ･ ･ ･ ･ ･ ･ ･ ･ ･ ･ ･ ･ ･ ･ ･ ･ ･ ･ ･ ･ ･ ②担当者別報告用シート A B C 相田社員
5 ﾗﾝｸ 1月 2月 3月 6 会社名 ﾀｲﾌﾟ 収入 会社名 ﾀｲﾌﾟ 収入 会社名 ﾀｲﾌﾟ 収入 8 A ○×商事 M 100 9 ↓ ･ A計 100 20 B ･ ↓ ･ B計 35 C ××(株) E 800 ･ ↓ ･ C計 800 50 D ･ ↓ ･ D計 合計 100 800
※だいぶ手を抜いちゃいましたが、各月、ランクは枠線で分けられて、それぞれ空欄15行あります。
似たような質問＆回答はたくさんありましたが、 とりあえず、振り分けの報告用シートの様式が複雑なのと、わが身の実力からすると VBAは難しいと考えてます。 関数も、いろいろご提示されているようなのですが、理解が及ばずなかなか応用が利きません。 例えば、データシートに入力されたものを項目別に他のシートに保存する例で (どこで提示されていたかわからなくなってしまいました。すいません）
=IF(COUNTIF(Sheet1!$A$2:$A$100,1)<ROW(A1),"",INDEX(名簿!C$2:C$100,SMALL(IF(1=Sheet1!$A$2:$A$100,ROW($A$1:$A$99)),ROW(A1))))
という関数で、「IF(1=Sheet1!$A$2:$A$100,ROW($A$1:$A$99))」の意味がつかめません。具体的にどういうことなのでしょうか？ なんかこのあたりの関数で、3つの条件（担当者と納品月とランク）を指定すれば何とかなるかな？と考えたのですが･･･。
わかりにくい説明で申し訳ありません。よろしくお願いいたします。
回答がつかないので、直接解決にならないかもしれませんが一言。
>なんかこのあたりの関数で、3つの条件（担当者と納品月とランク）を指定すれば何とかなるかな？と考えたのですが･･･。
考え方としてはMAKOさんの考え方は間違いではないと思います。 しかし、表の構成や3つの条件という複雑さを考えると、 私にはちょっと難しすぎますし、解答がつかないということは、 ほかの皆さんも手を出しづらいのだろうと思います。 (そうでなかったらごめんなさい。)
とりあえず、
>「IF(1=Sheet1!$A$2:$A$100,ROW($A$1:$A$99))」の意味がつかめません。
この部分だけ分かる範囲で説明しますと、 提示された数式は、Sheet1のA2:A100の範囲に 1 が入力されているセルを探し、 それに対応する名簿シートのC2:C100に入力されている内容を表示させるというもののようです。
「IF(1=Sheet1!$A$2:$A$100,ROW($A$1:$A$99))」の部分は、
Sheet1!$A$2:$A$100の中で 1 が入力されている場合はその行番号より 1 少ない数(Sheet1!$A$2:$A$100とROW($A$1:$A$99)の行が1行ずれているため） を返します。
例えばSheet1!$A$2:$A$100が「1;0;1;1;0; ・・・・」となっていれば、 IF(1=Sheet1!$A$2:$A$100,ROW($A$1:$A$99))」は「1;FALSE;3;4;FALSE;・・・」となります。
これにより、 SMALL(IF(1=Sheet1!$A$2:$A$100,ROW($A$1:$A$99)),ROW(A1)) はその中の一番小さい数、すなわち 1 となり、 SMALL(IF(1=Sheet1!$A$2:$A$100,ROW($A$1:$A$99)),ROW(A2)) は二番目に小さい 3 となり、・・・・ という具合になりますね。
この数を元に、 INDEX(名簿!C$2:C$100・・の部分で名簿シートのC$2:C$100の部分にあるデータを参照させています。
ちなみに最初のIF(COUNTIF(Sheet1!$A$2:$A$100,1)<ROW(A1),"", の部分は、 参照すべきデータがいくつあるかを調べて不要なエラーを表示させないための処理部分です。
なお、ご存知かもしれませんがこの関数を正常に表示させるためには、 入力後 Ctrl とShefキーを押しながらEnterで確定する必要があります。 これは配列数式という特殊な数式になります。 配列数式については、 http://pc.nikkeibp.co.jp/pc21/special/hr/ こちらのサイトがよく参照されるようです。 (sato)
Satoさん、ご親切にありがとうございます。 回答がなかなかつかなかったので、説明方がまずかったかな？と途方にくれていたところ、助かりました。
IF(1=Sheet1!$A$2:$A$100,ROW($A$1:$A$99))」の意味もわかりやすくご説明いただいたおかげで、 おおよそ理解（たぶん）できました。
そうすると、私の単純な頭で考えて、例えばとりあえず2つの条件で検索表示する場合は、AND関数で
{=IF(COUNTIF(Sheet1!$A$2:$A$100,1)<ROW(A1),"",INDEX(Sheet1!C$2:C$100,SMALL(IF(AND(1=Sheet1!$A$2:$A$100,"相田"=Sheet1!$B$2:$B$100),ROW($A$1:$A$99)),ROW(A1))))}
かな？と思ったりするのですが、サンプルを作って数式を入れたら「#NAME?｣のエラーが出てしまいます。 どこか（もしくは根本的に）間違っていますでしょうか？
※提示した数式中の「INDEX(名簿!C$2:C$100･･･」のシート名は数式コピーした際、わかり易い様に、名簿→Sheet1に 全部変更したつもりが抜けていました。スイマセン･･･。
(MAKO)
よく確認せずにコメントを入れましたが、 Satoさんのご提示いただいた、 http://pc.nikkeibp.co.jp/pc21/special/hr/に、私の今回の質問について説明があるようですね。
しっかり見てから、また不明な点があればコメント入れます!!
(MAKO)
改めて、質問です。
satoさんにご案内いただいたサイトで、配列式についておおよそ理解できました。 とりあえず、以下の数式で会社名のデータは、何とかコピー転送?出来そうな感じですが、
{=INDEX(Sheet1!D$6:D$100,SMALL(IF((1=MONTH(Sheet1!$C$6:$C$100))*("A"=Sheet1!$I$5:$I$100)
*("相田"=Sheet1!C$5:C$100),ROW($A$1:$A$94)),ROW($A1)))}
2個以上の条件の場合、以下の部分「対象がない場合空欄にする数式」は何か方法がありますでしょうか？ 「IF(COUNTIF(Sheet1!$A$2:$A$100,1)<ROW(A1),"",」 単純にISERRORを使ったらよいのかとも思ったのですが、そうだとしても式がすごく長くなってしまうので･･･。
もう一点、 これは私が以前、別件の質問にて半平太さんにご提示いただいた数式の一部で、VBAでダブルクリックの■を入力すると、文字列がコピー転送されるものなのですが･･･
（[[20080614120403]] 7895 『チェックボックスを使って行の挿入』）
=INDEX(AC$1:AC$5,1/LARGE(INDEX((AA$2:AA$5="■")/ROW(ZZ$2:ZZ$5),0),ROW(ZZ1)))
この数式が今回応用できないかと最初思ったのですが、頭をずいぶんひねりましたが高度すぎて理解が及びませんでした。 この式は配列式ではないのですけど、
1/LARGE(INDEX((AA$2:AA$5="■")/ROW(ZZ$2:ZZ$5),0)
この部分、どのような意味なのでしょうか･･･？
お分かりの方、お時間あればご教授ください!
(MAKO)
例えば 担当者別報告用シートは、一枚用意し 担当者名を入力するセルの値を変更しながら 全ての担当者の報告用シートを確認する で良いなら、 ターゲットリストに作業列を作成し 少しは簡単に作成出来そうに思います。
ターゲットリストの作業列には 担当名が一致したとき「月 ＆ ランク ＆ 同月同ランク内何番目か」 を表示させます。
担当名がA3セルに入っているなら （担当者別報告用シートのシート名は「報告」としています。） ターゲットリストのK6セルに =IF(B6=報告!$A$3,MONTH(C6)&"_"&I6&"_"&SUMPRODUCT(($B$6:B6=報告!$A$3)*(MONTH($C$6:C6)=MONTH(C6)*($I$6:I6=I6))),"") として、下にコピーします。
↓のデータなら、K部分の結果になります。
ターゲットリスト [B] [C] ・・・ [I] ・・・ [K] [5] 担当 納品日 ﾗﾝｸ ･･･ [6] 相田 1月22日 A 1_A_1 [7] 井本 2月25日 D [8] 相田 1月15日 A 1_A_2 [9] 相田 3月20日 B 3_B_1
報告 [A] [3] 相田
(HANA)
こんにちは～♪
HANAさんと衝突しちゃいましたけれど。。。♪
失礼しま～す。。。
担当者別のシートで集計するということですが 何人いるのでしょうか？ 少なければ、関数処理でも何とかなりますけれど。。。 多い場合は、この処理は各シートに関数式をたくさん入れておくので あまりお勧め出来ません。
途中のレスは、あまり読んでいませんので 混乱するようでしたら、私の回答は無視して結構です。。。
★シート名は、Sheet1 ↓
┌─┬────┬────┬────┬────┬────┬────┬────┬────┬────┬────┬────┐ │ │ A │ B │ C │ D │ E │ F │ G │ H │ I │ J │ K │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │ 4│ │ │ │ │ │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │ 5│入力日 │担当 │納品日 │会社名 │備考 │商品名 │数 │収入 │ﾗﾝｸ │ﾀｲﾌﾟ │作業列 │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │ 6│ 7月15日│相田 │ 1月22日│○×商事│営業２課│バナナ │ 10│ 100│A │M │相田1A │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │ 7│ 7月21日│井本 │ 2月25日│△△会社│式典用 │イチゴ │ 30│ 1000│D │E │井本2D │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │ 8│ 8月5日│上田 │ 1月15日│□▽会社│橋本氏 │メロン │ 200│ 500│C │M │上田1C │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │ 9│ 8月10日│相田 │ 3月20日│××(株)│総務課 │スイカ │ 40│ 800│C │E │相田3C │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │10│ │ │ │ │ │ │ │ │ │ │ │ └─┴────┴────┴────┴────┴────┴────┴────┴────┴────┴────┴────┘
★どんな式にしても、長くなりますから 短くするために、作業列を1列作ります。。
たとえば、K列を作業列にします。。。
K6セルへ =IF(COUNTA(B6:C6,I6)<3,"",B6&MONTH(C6)&I6) 下へコピー。。。
相田さんのシート ↓
┌─┬────┬────┬────┬────┬────┬────┬────┬────┬────┬────┐ │ │ A │ B │ C │ D │ E │ F │ G │ H │ I │ J │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │ 1│相田 │ │ │ │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │ 2│ │1月 │ │ │2月 │ │ │3月 │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │ 3│ランク │会社名 │ﾀｲﾌﾟ │収入 │会社名 │ﾀｲﾌﾟ │収入 │会社名 │ﾀｲﾌﾟ │収入 │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │ 4│A │○×商事│M │ 100│ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │ 5│A │ │ │ │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │ 6│A │ │ │ │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │ 7│A │ │ │ │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │ 8│A計 │ │ │ │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │ 9│B │ │ │ │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │10│B │ │ │ │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │11│B計 │ │ │ │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │12│C │ │ │ │ │ │ │××(株)│E │ 800│ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │13│C │ │ │ │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼────┼────┤ │14│C計 │ │ │ │ │ │ │ │ │ │ └─┴────┴────┴────┴────┴────┴────┴────┴────┴────┴────┘
相田さんのシートの作成順序
★順序その１ A1セルへ 相田 と入力します。
★順序その２ A列のランクは、表の様に空白なく埋めて下さい。。 式を短くするためです。。。 どうしても最初のランク以外は、空白にしたい場合は 条件付書式で、最初以外を白くすれば見えなくなります。。
★A列のランクは、ABCの行を15行取ると表が大きくなるので 便宜上少なくしてます。。
★式は。。。 B4セルへ =INDEX(Sheet1!$D:$D,SMALL(INDEX((Sheet1!$K$6:$K$100<>$A$1&INT(COLUMN(C1)/3)&$A4)* 10^4+ROW($6:$100),),COUNTIF($A$4:$A4,$A4)))&""
C4セルへ =INDEX(Sheet1!$J:$J,SMALL(INDEX((Sheet1!$K$6:$K$100<>$A$1&INT(COLUMN(C1)/3)&$A4)* 10^4+ROW($6:$100),),COUNTIF($A$4:$A4,$A4)))&""
D4セルへ =IF(COUNTBLANK(B4:C4)=2,"",SUMIF(Sheet1!$K:$K,$A$1&INT(COLUMN(E1)/3)&$A4,Sheet1!$H:$H))
そして、B4からD4を選択して３つの式をまとめて右と下へコピーです。。。
★最後に、各ランクの合計の式は、後から入れて下さいネ！！ 面倒でしたら、D4の式に追加すればいいです。。
ご参考にどうぞ。。。
。。。Ms.Rin～♪♪
HANAさん、Ms.Rin～♪♪さん、いつもご指導ありがとうございます。
ちょっと時間が無くて、サンプルで十分に確認まで出来ていないので中途半端なコメントになっていたらスイマセン。
まず、HANAさんからいただいたご回答ですが、
＞担当者別報告用シートは、一枚用意し ＞担当者名を入力するセルの値を変更しながら ＞全ての担当者の報告用シートを確認する
「担当者別報告シート(報告)は1枚だけにして、例えば報告シートのA1に担当者名を入れるとその担当者の 表数値&文字列が表示される」と、いうことでしょうか？ そうだとすると、ちょっとかっこいいですけどこちらの運用上ではいろいろ不都合が出てきてしまいます。
確かに作業列にご提示いただいた数式を入れると「月 ＆ ランク ＆ 同月同ランク内何番目か」が 表示され、この数値を利用すると、報告シートの数式が短くなりそうな感じはわかるのですが、 具体的な方法はもう少し勉強（時間）が必要のようです。
Ms.Rin～♪♪さん、丁寧にきれいな表まで作成していただき、ありがとうございます。
すごくわかりやすい説明なのですが、実際の書式は私から提示した例と若干違うので（手を抜いてしまいました。スイマセン） 数式の意味が十分理解できていない為、応用が出来ません。出来れば教えてください。
INDEX((Sheet1!$K$6:$K$100<>$A$1&INT(COLUMN(C1)/3)&$A4)*10^4+ROW($6:$100),)
の部分ですが、作業列の中の対象を検索してるのだと思うのですが、 "<>"と"10^4+ROW($6:$100)"の意味が全く理解できず･･･ 出来の悪い生徒で申し訳ありません。
でも、お二人ともコメントいただいているように「作業列」ですよねー。 単純なことですが慣れていないせいで頭が回りませんでした。
それと、作りながら考えたのですが私の作ろうとしている様式・運用だと、担当者がブックをあけて 入力・修正するタイミングがかぶっちゃうと、同時に出来ませんよねー。
これは今まで使っていた書式も同じだったのですが、これまではあまり活用できていなかったので･･･。
まだ、根本も解決していないのですが、当初質問のところはは同じ運用方にて 新たな各担当者ごとのブックで、それぞれ入力シートと報告シートを作成して、
別途、管理用ブックにて
1. (入力)一覧シート･･･担当者の入力シートと全く同じ書式で、担当者ごとに入力した情報を集約するもの。 表示は順不同（入力順？）で良いが、データーを出す為にマクロボタンで並び替えをする。 2．各担当者の報告シートを全くそのままの書式・数値で人数分のシート。 3．報告シートの集計（これは報告シートの数値のみ集計なので複雑な関数は必要なし。）
というのは、また高望みでしょうか？ ちなみに、入力する担当者（ブック）は約10名です。
こんにちは～♪
説明は、すごくヘタなんですが。。
>INDEX((Sheet1!$K$6:$K$100<>$A$1&INT(COLUMN(C1)/3)&$A4)*10^4+ROW($6:$100),)
>の部分ですが、作業列の中の対象を検索してるのだと思うのですが、
MAKOさんのおしゃる通りです。
報告シートの 担当者名＆月＆ランク をつなげて $A$1&INT(COLUMN(C1)/3)&$A4 検索値として 作業列のK列を検索します。
普通は Sheet1!$K$6:$K$100=$A$1&INT(COLUMN(C1)/3)&$A4 として同じ値を探して、検索列=検索値 で TRUE としますが
この式は、検索列<>検索値 として 検索値と違う値が TRUE となり同じ値が FALSE になって。 TRUE FALSE が逆になります。
★提示した式の範囲は、100行目と多いので少ない範囲で説明してみます。 範囲は6～10行目にしました。
B4セルへ 以下の式を入れた場合。。。
=INDEX(Sheet1!$D:$D,SMALL(INDEX((Sheet1!$K$6:$K$10<>$A$1&INT(COLUMN(C1)/3)&$A4)* 10^4+ROW($6:$10),),COUNTIF($A$4:$A4,$A4)))&""
の中の
INDEX((Sheet1!$K$6:$K$10<>$A$1&INT(COLUMN(C1)/3)&$A4)*10^4+ROW($6:$10),)
ですが。。。
(1) Sheet1!$K$6:$K$10<>$A$1&INT(COLUMN(C1)/3)&$A4
↓
{FALSE;TRUE;TRUE;TRUE;TRUE}
(2)これに、×10^4(10000)を掛けて +ROW($6:$10) を足します。
(Sheet1!$K$6:$K$10<>$A$1&INT(COLUMN(C1)/3)&$A4)*10^4+ROW($6:$10)
↓
{FALSE;TRUE;TRUE;TRUE;TRUE}*10000
↓
{0;10000;10000;10000;10000}
↓
{0;10000;10000;10000;10000}+ROW($6:$10)
↓
{6;10007;10008;10009;10010}
(3)この配列からSMALL関数で順番に、行番号を取り出します。
この場合、小さい方から1番目は 6 ですから
Sheet1!$D:$Dの行番号 6 の、 D6セル の値を参照するわけです。
(4)抽出のための配列数式を使った式は幾つか方法がありますが、
今回は、抽出するデータ行が D列 I列と両方文字列なので
この方法を使うと空白処理の式が短くなります。
(5)先の式で、小さい方から1番目は 6 ですが
2番目は、 10007 で D10007セルを参照することになります。
D10007セルは、未入力なら 0 を表示しますが
式の最後に &"" を付ける事で空白処理が出来るわけです。
今回のMAKOさんご希望の処理は、複雑みたいなので 私では、これ以上の回答はムリと思いますので。。。 どうか、頑張ってくださいネ！！。。。
。。。Ms.Rin～♪♪
Ms.Rin～♪♪さん
遅くなってすいません。丁寧なご説明、ありがとうございます。 説明、全然ヘタじゃないですよ！ こちらの頭の回転が遅いので、時間がかかりましたけど、ほぼ理解できました。たぶん･･･
なるほど！です。実際の書式にも当てはめることが出来ました。
ところで、（とりあえず追加で考えていることは置いておきまして）
Ms.Rin～♪♪さんにご提示いただいた数式の中で、D4セルへ入れる数式（収入）ですけど、
=IF(COUNTBLANK(B4:C4)=2,"",SUMIF(Sheet1!$K:$K,$A$1&INT(COLUMN(E1)/3)&$A4,Sheet1!$H:$H))
そのまま入れると、SUMIF関数で対象の合計を返してしまうのですが、それぞれ単体の収入を返す場合 どの関数を使えばよいのでしょうか？B4、C4と同様の式だと文字列で返してしまうので普通に合計出来ないみたいですし。 いろいろ調べてはみたのですけど、ありそうで見つからなくて･･･。
お手数ですが、どなたか教えてください。
(MAKO)
D4に入れる数式、とりあえずいろいろ試してみまして、 B4,C4と同様の式にて、最後の「&""」をはずせば、文字列にならないことがわかりました。
やっぱり、まだ十分には理解できてないですねー。
これでとりあえず当初の質問は解決です。 コメントいただいた皆さん、ありがとうございました。 追加の質問は再度整理＆検索をして、不明な点は改めて質問させていただきたいと思います。 そのときは、また皆さんどうぞよろしくお願いします。
(MAKO)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.