皆様はじめまして。いつも参考にさせてもらってます。
当方仕事でExcelを使う機会が多く、関数やマクロなどもチョコチョコ使っていますが、数ある機能のうちの限られたものしか使えていないのが現状です。
さて、先日このような仕事がありました。
あるシステムの顧客ごとの利用件数とそれに応じた請求額をまとめた一覧表があります。
表は上期の集計分と下期の集計分の２種類ありますが、形状は全く同じです。
列Ａは顧客ＩＤ・列Ｂは顧客名でユニーク値です。(図１)
この２つの表をまとめて、図２のように年間の一覧表を作成する仕事がありました。
各顧客は、そのシステムを上期しか利用していない顧客もあれば、下期のみ、上下両方の顧客があります。図２で黄色くハイライトしている部分が、利用実績の無かった部分です。
はじめは漠然と上期か下期の表をベースにVLOOKUPを使ってやろうと思っていたのですが、考えたら顧客ごとに上期のみ・下期のみの条件があり、VLOOKUPではうまくいきません。関数の本なんかも読んでみたんですが、どうもこれというのが見当たらず、資料の提出期限もあって、結局上期の表をベースに、上期に利用実績の無い顧客のデータは下期の表からコピペをしながら作ったのですが、それぞれに400件以上のデータがあり、それだけで休日出勤して数時間を要してしまいました。
こういう場合、何か上手く二つの表をひとつにまとめる方法はあるのでしょうか。
皆様、よろしくご教授をお願いします。
ちなみに使用環境は
OS:Windows XP Professional SP2
アプリ：Office2000
です。
当方仕事でExcelを使う機会が多く、関数やマクロなどもチョコチョコ使っていますが、数ある機能のうちの限られたものしか使えていないのが現状です。
さて、先日このような仕事がありました。
あるシステムの顧客ごとの利用件数とそれに応じた請求額をまとめた一覧表があります。
表は上期の集計分と下期の集計分の２種類ありますが、形状は全く同じです。
列Ａは顧客ＩＤ・列Ｂは顧客名でユニーク値です。(図１)
この２つの表をまとめて、図２のように年間の一覧表を作成する仕事がありました。
各顧客は、そのシステムを上期しか利用していない顧客もあれば、下期のみ、上下両方の顧客があります。図２で黄色くハイライトしている部分が、利用実績の無かった部分です。
はじめは漠然と上期か下期の表をベースにVLOOKUPを使ってやろうと思っていたのですが、考えたら顧客ごとに上期のみ・下期のみの条件があり、VLOOKUPではうまくいきません。関数の本なんかも読んでみたんですが、どうもこれというのが見当たらず、資料の提出期限もあって、結局上期の表をベースに、上期に利用実績の無い顧客のデータは下期の表からコピペをしながら作ったのですが、それぞれに400件以上のデータがあり、それだけで休日出勤して数時間を要してしまいました。
こういう場合、何か上手く二つの表をひとつにまとめる方法はあるのでしょうか。
皆様、よろしくご教授をお願いします。
ちなみに使用環境は
OS:Windows XP Professional SP2
アプリ：Office2000
です。
|
|
|
|
コメント(8)
統合 機能で可能では？
一時的にでもよいので、見出しをそれぞれ１行で表現し
上期基本料、上期件数１…、下期基本料、下期件数１…
など固有の見出しにして
結果の表の左上の位置になる位置にフォーカスをおいた状態で
データ−統合
集計の方法 合計（見出しが固有なので合計されることはありません）
統合元範囲 上期分の表のid、見出し（1行）を含む表全体 を選択し 追加
統合元範囲 下期分の表のid、見出し（1行）を含む表全体 を選択し 追加
統合の基準 上端行、左端行 ともにチェック。
ＯＫで。
統合して後で必要があれば見出しを直して、年間の表もつけ足せば。
作業頻度にもよりますが、全体を考えればそれほどの手間でもないと
思います。
見出し部分をどこかに保管しておけばコピペするだけですし。
また、統合された結果の列数は決まっているので、あらかじめ「年間」の部分に
数式を入れておいたり体裁を整えておくこともできますよね。
レコード件数までは事前にわからないかもしれませんが…。
一時的にでもよいので、見出しをそれぞれ１行で表現し
上期基本料、上期件数１…、下期基本料、下期件数１…
など固有の見出しにして
結果の表の左上の位置になる位置にフォーカスをおいた状態で
データ−統合
集計の方法 合計（見出しが固有なので合計されることはありません）
統合元範囲 上期分の表のid、見出し（1行）を含む表全体 を選択し 追加
統合元範囲 下期分の表のid、見出し（1行）を含む表全体 を選択し 追加
統合の基準 上端行、左端行 ともにチェック。
ＯＫで。
統合して後で必要があれば見出しを直して、年間の表もつけ足せば。
作業頻度にもよりますが、全体を考えればそれほどの手間でもないと
思います。
見出し部分をどこかに保管しておけばコピペするだけですし。
また、統合された結果の列数は決まっているので、あらかじめ「年間」の部分に
数式を入れておいたり体裁を整えておくこともできますよね。
レコード件数までは事前にわからないかもしれませんが…。
ご返事が遅くなりました。
->JOKER-25さん
回答ありがとうございます。
なるほど。まず顧客名の重複しない一覧表を作り、それを基準にVLOOKUPを使って上期の列は上期の表から、下期の列は下期の表から引っ張ってくるということなのですね。
いつも思うのですが、関数をどう利用するかというのは、まるでパズルを解いているようで、私立文系コースだった私にはハードルが高いです(^_^;)
->takecさん
回答ありがとうございます。
こんな機能があったのですね！
あとで一度試してみます。
こっちのやり方のほうが関数を使うよりもすっきりしているような気がします。
まだまだ奥が深いなぁ、Excel…
->JOKER-25さん
回答ありがとうございます。
なるほど。まず顧客名の重複しない一覧表を作り、それを基準にVLOOKUPを使って上期の列は上期の表から、下期の列は下期の表から引っ張ってくるということなのですね。
いつも思うのですが、関数をどう利用するかというのは、まるでパズルを解いているようで、私立文系コースだった私にはハードルが高いです(^_^;)
->takecさん
回答ありがとうございます。
こんな機能があったのですね！
あとで一度試してみます。
こっちのやり方のほうが関数を使うよりもすっきりしているような気がします。
まだまだ奥が深いなぁ、Excel…
統合機能でテストしてみました。
この機能は要するに、｢複数の表の見出しを参照しながら同じ見出しの列は1列にまとめ、見出しの異なる列はまとめた項目との紐付け関係を保ちつつ、統合結果のそれぞれの見出しの列に配置する｣という機能なわけですね。
ところで、実は統合自体は上手く行き、ひとつの表が出来上がったのですが、列Bの社名の欄が空白になるのです。
実際には皆さんに理解していただきやすいように図の表は加工をしていまして、実際の表には社名の後に購入･未購入という欄が存在します。ここには｢済｣or｢未｣の２値の文字データが入力されているのですが、この列も丸々空白になっています。
統合機能では数値データしか引っ張れないとかいう仕様になっているのでしょうか？
MicrosoftのHPもみてみたのですが、そのようなことは書いていないし…
あ！今気づきましたが、ひょっとして、上期から下期に移行する間に社名変更してる会社があるんですが、そのせいでしょうか？
購入･未購入の状態も、上期と下期で異なっている顧客がありますし。
そんな気がしてきました。うーむ、厳しい。
まぁ、この表さえ出来てしまえば、二つの表から｢社名｣欄を参照する関数を組めばいいのか･･･JOKER-25さんの方法でIDのリストを作ったあとの作業と同じですよね。
二つのvlookupをifでネストすればいいのかな…？
この機能は要するに、｢複数の表の見出しを参照しながら同じ見出しの列は1列にまとめ、見出しの異なる列はまとめた項目との紐付け関係を保ちつつ、統合結果のそれぞれの見出しの列に配置する｣という機能なわけですね。
ところで、実は統合自体は上手く行き、ひとつの表が出来上がったのですが、列Bの社名の欄が空白になるのです。
実際には皆さんに理解していただきやすいように図の表は加工をしていまして、実際の表には社名の後に購入･未購入という欄が存在します。ここには｢済｣or｢未｣の２値の文字データが入力されているのですが、この列も丸々空白になっています。
統合機能では数値データしか引っ張れないとかいう仕様になっているのでしょうか？
MicrosoftのHPもみてみたのですが、そのようなことは書いていないし…
あ！今気づきましたが、ひょっとして、上期から下期に移行する間に社名変更してる会社があるんですが、そのせいでしょうか？
購入･未購入の状態も、上期と下期で異なっている顧客がありますし。
そんな気がしてきました。うーむ、厳しい。
まぁ、この表さえ出来てしまえば、二つの表から｢社名｣欄を参照する関数を組めばいいのか･･･JOKER-25さんの方法でIDのリストを作ったあとの作業と同じですよね。
二つのvlookupをifでネストすればいいのかな…？
- mixiユーザー
- ログインしてコメントしよう！
|
|
|
|
