通話履歴、一番速く整理する方法は？

質問

通話履歴、一番速く整理する方法は？
会社でSOFTBANKの携帯を営業に持たせており、毎月通話履歴の中から、
プライベート通話分を天引きしているのですが、
通話履歴のデータが「090-***-***（発信者）」「090-○○○-○○○（通話先）」「通話時間（都度）」「料金（都度）」の羅列データになっています。
これを発信者毎に「090-○○○-○○○（通話先）」「料金（累計）」という集計をしたいのです。
現在はエクセルを使用して
　(1)発信者毎にデータを分ける
　(2)分けたデータを料金で降順でソート
　(3)無料通話のデータを削除
　(4)オートフィルタで通話先を指定
　(5)表示された「料金（都度）」を選択範囲にして、累計の料金を一番上の行に手入力し、
　　以下の行のデータを削除
　(6)(4)-(5)の作業を繰り返し
という作業を行なっており、それだけで２～３時間を要しています。
本来は整理した形でデータをもらえればいいのですが、対応が悪くて・・・。

上記よりも速く整理できる方法をご存知の方、是非ご教導願います。
条件としては
　(1)オフィス以外のソフトを使用する場合、なるべくフリーで。
　(2)マクロはなるべく使わないに越したことはないです。
　　（苦手なので・・。どうしても必要なら勉強します。）

回答No.3

kagakusuki · ベストアンサー

申し訳御座いません。
　同じ番号が表示される件とは無関係ですが、Sheet2のC5セルに入力する数式に誤りが御座いました。
　IF関数の論理式部分で、B5セルの参照形式を、行が絶対参照になっていましたが、正しくは、行と列の両方共に相対参照としなければなりませんでした。

〔誤〕
=IF(B$5="","",INDEX(Sheet3!$A:$A,SUMPRODUCT(ROW(Sheet3!$B$2:$B$999)*(Sheet1!$B$2:$B$999=B$3)*(Sheet3!$B$2:$B$999=ROWS($4:4)))))

〔正〕
=IF(B5="","",INDEX(Sheet3!$A:$A,SUMPRODUCT(ROW(Sheet3!$B$2:$B$999)*(Sheet1!$B$2:$B$999=B$3)*(Sheet3!$B$2:$B$999=ROWS($4:4)))))

>なぜかSheet2の通話先電話番号（挙げていただいた例でいいますと、B列上で）同じ通話先番号が被って表示されたり、金額が空欄（多分0？）の表示が出たりします。

　私のパソコン上では、Sheet2のB5セルに入力する数式の通りで、正常に動作しております。
　怪しいと思われる事は、IF関数の論理式部分使っている、先頭から15文字目の所にある、

ROWS($4:4)

の部分を、写される際に間違われて、

ROW($4:4)

又は

ROWS($4:$4)

或いは

ROWS(4:4)

にされてはいないでしょうか。

お礼

度々のご教導ありがとうございます。
あれから、ご指摘いただいた箇所の確認他、色々とやってみたのですがやはり上手くいきませんでした。
何かいい手はないものかと調べていたところ、「ピポットテーブル」を使うやり方で
要望しておりましたデータ内容にかなり簡単に対応できたので、そちらの方で対応します。
ありがとうございました。

回答No.2

kagakusuki

Excelの関数と作業列を使用する方法です。

　今仮に、Sheet1の

B1セルに「発信者」
C1セルに「通話先」
D1セルに「通話時間」
E1セルに「料金」

と入力されていて、2行目から999行目にかけての

B列に発信者の電話番号、
C列に通話先の電話番号、
D列に通話時間、
E列に料金、

のデータが並んでいるものとし、
Sheet3のA列とB列を作業列として使用して、
Sheet2に、同じ発信者が同じ通話先相手に使用した電話料金の合計額を、発信者毎に、通話先別に集計した結果を表示させるものとします。

　まず、Sheet3のA2セルに次の数式を入力して下さい。

=IF(OR(Sheet1!$B2="",Sheet1!$E2=0),"",IF(SUMPRODUCT((Sheet1!$B$2:$B2=Sheet1!$B2)*(Sheet1!$C$2:$C2=Sheet1!$C2)*(Sheet1!$E$2:$E2>0))=1,SUMPRODUCT((Sheet1!$B$2:$B$999=Sheet1!$B2)*(Sheet1!$C$2:$C$999=Sheet1!$C2)*Sheet1!$E$2:$E$999),""))

　次に、Sheet3のB2セルに次の数式を入力して下さい。

=IF(ISNUMBER($A2),SUMPRODUCT((Sheet1!$B$2:$B$999=Sheet1!$B2)*($A$2:$A$999>=$A2)*ISNUMBER($A$2:$A$999)),"")

　そして、Sheet3のA2～B2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　次に、Sheet2の

B2セルに「発信者電話番号」、
C2セルに「通話料合計」、
B4セルに「通話先電話番号」、
C4セルに「通話料金」、

と入力して下さい。
　次に、Sheet2のB5セルに次の数式を入力して下さい。

=IF(OR(B$3="",ROWS($4:4)>SUMPRODUCT((Sheet1!$B$2:$B$999=B$3)*ISNUMBER(Sheet3!$A$2:$A$999))),"",INDEX(Sheet1!$C:$C,SUMPRODUCT(ROW(Sheet3!$B$2:$B$999)*(Sheet1!$B$2:$B$999=B$3)*(Sheet3!$B$2:$B$999=ROWS($4:4)))))

　次に、Sheet2のC5セルに次の数式を入力して下さい。

=IF(B$5="","",INDEX(Sheet3!$A:$A,SUMPRODUCT(ROW(Sheet3!$B$2:$B$999)*(Sheet1!$B$2:$B$999=B$3)*(Sheet3!$B$2:$B$999=ROWS($4:4)))))

　次に、Sheet2のC3セルに次の数式を入力して下さい。

=SUM(C$5:C$999)

　続いて、Sheet2のB5～C5の範囲をコピーして、同じ列の6行目以下に貼り付けて下さい。
　そして、Sheet2のB列～C列の範囲をコピーして、D列以降に人数分だけ貼り付けて下さい。
　最後に、Sheet2の「発信者電話番号」と表示されている各セルの直下にある、空欄のセルに、各発信者の電話番号を入力して下さい。

　これで、Sheet1に発信者の電話番号と、通話先電話番号、及び通話料金を入力すると、Sheet2に集計結果が表示される筈です。
　尚、Sheet1のリストが999行目を上回る可能性がある場合には、各数式中の

999

と入力する部分を、必要となる可能性のある行数に応じて調整して下さい。

補足

回答ありがとうございます。
早速各数式を入力してみたところ、ちゃんと理解して入力できれば、
各通話先番号に対する料金が降順で表示されそうで、バッチリなんですが、
なぜかSheet2の通話先電話番号（挙げていただいた例でいいますと、B列上で）同じ通話先番号が
被って表示されたり、金額が空欄（多分0？）の表示が出たりします。
数式が理解できてれば、修正の使用もあると思うんですが・・正直2割くらいしか分かりません。
お時間があるときでかまいませんので、ヒントだけでも頂戴できると・・・。

こんな感じの表示になります。
080-RF?F-Y?KQ　　2860
090-ZHY?-RHHH　　1780
080-QYDH-HY?M　　1140
090-ZR?K-?MF?　　940
090-Q?MD-ZMFH　　480
090-H?HM-ZFRM　　420
090-YM?Q-H?FY　　360
080-D?MQ-YZKK　　300
080-DQZ?-RQFK
090-KH?Q-YYYY
090-Q?MD-ZMFH　　　　←同じ番号が表示される
090-KH?Q-YYYY　　240
06-Q?QF-HHHR　　　220
090-Q?MD-ZMFH　　　　←同じ番号が表示される
080-QRZY-KZYH
090-Q?MD-ZMFH　　　　←同じ番号が表示される
080-QRZY-KZYH

※999では収まらないので、19999にしています。

回答No.1

e0_0e_OK

excel のマクロ使えばキー１発(or２発)で済みそうです。職場でマクロが出来る人に応援してもらえば。

お礼

回答ありがとうございます。
やっぱりマクロですか・・・・。もう少しあがいてみます。