Excelを使用してSVG形式で通話をグラフ化する

この記事では、Microsoft Excelを使用して通話の詳細からの情報を処理する方法について説明します。その結果、これらの通話を時間の経過と日ごとにグラフィカルに表すベクトル図が作成されます。このチャート自体は、プロジェクトの作業計画を説明するために最もよく使用されるガントチャートに似ています。



ガントチャートは、平面上の水平バーのコレクションです。水平方向は時間の値に対応し、この値は、一般的な場合、連続することができます。そして、垂直方向では、この平面は固定幅の多くの水平ゾーンに分割されます。作業スケジュールを反映した古典的なガントチャートの場合、そのような各ゾーンは特定のタイプの作業に対応します（図1）。チャートバーはこれらのゾーン内にプロットされます。特定のゾーンに描かれているストリップは、このゾーンに対応する作業のタイプを特徴づけ、ストリップの左と右の境界線は、それぞれ、この作業の開始時間と終了時間を特徴づけます。したがって、ストリップの長さは、与えられた作業の期間を特徴づけます。





図：1.作業スケジュールを説明するガントチャート。



この記事で説明されている電話の図の場合、垂直方向のゾーンは日（日）を特徴づけます。この場合、図の水平方向の時間スケールは、1日の長さである0〜24時間の間隔に対応します。このような図の各バーは、1回の通話に対応します。レーンの左右の境界は通話の開始時刻と終了時刻であり、ゾーン番号（垂直方向）は通話が行われた日です。このような構成の図を使用すると、呼び出しが行われる頻度を視覚的に示して評価し、平均継続時間、時刻ごとの分布などを見積もることができます。さらに、この図にもう1つのプロパティを追加できます。それはバーの色です。さまざまな基準に従ってストライプに色を付けることができます。まず、通話の種類（着信または発信）によって。第二に-通話の電話番号による。最初のケースでは、2色で十分です。 2番目の例では、はるかに多くなりますが、原則として、通話に最も頻繁に表示される最も人気のある電話番号には、12色以下で十分です。この記事では、2つのモバイルオペレーター（2つのSIM電話）の存在を考慮して、5暦月のチャートの作成について説明します。図のバーの色は、「SIM1 / SIM2着信/発信」に基づいて選択されます。つまり、4つの異なる色が必要です。この記事では、2つのモバイルオペレーター（2つのSIM電話）の存在を考慮して、5暦月のチャートの作成について説明します。図のバーの色は、「SIM1 / SIM2着信/発信」に基づいて選択されます。つまり、4つの異なる色が必要です。この記事では、2つのモバイルオペレーター（2つのSIM電話）の存在を考慮して、5暦月のチャートの作成について説明します。図のバーの色は、「SIM1 / SIM2着信/発信」に基づいて選択されます。つまり、4つの異なる色が必要です。



ダイアグラムの形成は、構築とは対照的に、特定のダイアグラムを使用して出力ファイルを生成します。プロットに関しては、原則として、Excelでチャートを作成することは、標準ツールの1つであるExcelでの対応する操作を意味します。このような操作が可能であっても（ガントチャート）、大量の入力データを表示およびスケーリングするのは便利ではありません。同様の図でSVGベクトル形式ファイルを生成する場合、表形式のデータを操作するのに便利なソフトウェアツールとしてExcelが使用されます。 Excelの代わりに、サードパーティの個別のプログラムを作成し、それを使用してSVGファイルを生成することができます。しかし、この場合のExcelは、偶然ではなく選択しました。まず、ある意味で、情報処理の特定の視覚化があります。第二に、SVG出力フォーマットの特異性。



この形式はスケーラブルなベクトルグラフィック形式であり、内部にXML形式のテキストデータが含まれています。これは一種のマークアップ言語であり、特定のグラフィック要素を描画するための一般的なコマンドとパラメーターの特定のセットが含まれています。たとえば、コマンドは次のようになります。線、ポリゴン、円を描画し、テキストを書き込みます。また、パラメータは、ポリゴンの角の座標、塗りつぶしの色、テキストのサイズとフォントなどです。実際、SVGマークアップ言語を知っていると、通常のテキストエディター（メモ帳）を使用して、最も単純なカテゴリから1つまたは別の画像を手動で作成できます。表示用のSVGファイルは、一般的なインターネットブラウザで開くことができます。



SVGダイアグラムの作成を進める前に、モバイルオペレーターのサイトから通話の詳細をダウンロードするだけでなく、それらを前処理する必要があります。すでに述べたように、2つのモバイルオペレーターが検討されます。それらの1つはTele2で、もう1つはMegafonです。対応するウェブサイトの個人アカウントからダウンロードできるTele2通話の詳細は、ページに分割された大きなテーブルを備えたPDFドキュメントです（図2）。





図： 2.「Tele2」の詳細を示す通話の種類。



Megafonの場合、詳細がXLS（Excel）ファイルに表示されることを除いて、すべてがほぼ同じです（図3）。





図： 3.「メガフォン」の詳細を示す通話の種類。



一方と他方の詳細は、異なる方法で処理し、不要なものを取り除き、整理する必要があります。このテキストには一定の「規則性」があるため、簡単に自動処理されます。 Excel関数（式）を使用して別のドキュメントで作成しました。この問題について詳しく説明する価値はないと思います。この処理の結果、日付、時刻、期間、通話の種類、電話番号、simカードなどの最小限の必須フィールドを備えたすっきりとした大きなテーブルが得られました（図4）。合計2102件の通話記録が得られました。ちなみに、元の詳細テキストを含むExcelシートを示す図3では、他のシートの存在を確認できます。これらのシートは、元のドキュメントの続きとして、処理の中間段階を実装するためだけに追加しました。





図：4.混合ディテール、整理。



結果のテーブルをシート「A」の新しいドキュメントにコピーし、すぐに追加のフィールドを追加しました。ストライプの色のアドレス、ストライプの左の境界線（a）（1日の始まりから秒単位）、ストライプの右の境界線（b）（図5）。





図：5.最初のシートの追加パラメーター。



これらのフィールドは、Excelの式を使用して簡単に計算できます。カラーアドレスは、構成シート「C」のセルの4つのアドレスの1つを示し、HEX-RGB形式で記述されています。このシートには、色だけでなく、SVGドキュメントのすべての追加パラメーター（座標、オフセット、スケールなど）も含まれています。（図6）。





図： 6.パラメータ付きのシート。



バーに加えて、図には追加のデータが表示されます。バーに個別のラベルが付いた最も頻度の高い4つの電話番号の割り当て、時間内の電話の頻度の分布のヒストグラム、および図に関する情報です。



将来的には、図は4420 x1800ピクセルです。実際、ベクトルグラフィックスでピクセルについて話すのは難しいですが、SVG形式の説明には、離散座標系があり、その数をピクセルと呼びます。一般に、省略形に基づいていても、このグラフィックはスケーラブルです。すでに書いたように、この図には5か月間、つまり5月から9月までの通話が反映されます。あなたがそれを数えるならば、これは153日に相当します。図のバーには正確にゾーンの数が必要です。事前に規模を決めました。垂直方向では、ゾーンごとに10ピクセルを割り当てることにしました。この場合、ゾーン内のストリップの幅は8ピクセルになります（上下に1ピクセルのギャップがあります）。シート「C」のセルB8のギャップ（くぼみ）のサイズは、ゾーンのストライプの幅を調整できます。水平スケールは、原則として、任意のスケールを選択できます。ただし、図の実用的な明確さ、許容可能なアスペクト比および容量があります。結局、1分間に3ピクセル、つまり1ピクセルあたり20秒を取ることにしました。



合計で、チャートのアクティブエリアには次の次元があります。水平：24 * 60 * 3 = 4320;垂直：153 * 10 = 1530。図の左側で、各ゾーンの反対側にその名前を記入する必要があります。ゾーン名は日付と完全に一致しています。この目的のために、私は100pxの広い領域を確保することにしました。図の上では、（便宜上）少なくとも数時間はタイムスタンプを書き込むことが望ましいです。そしてその下のチャートの下に、私が上で書いたヒストグラムと追加情報があります。これらの目的のために、私は270ピクセルを割り当て、図全体の高さを1800に丸めました。これまでの説明に加えて、図では、ゾーン（日）間の明るい水平線、週間のわずかに暗い線、および月間の黒い線を反映することにしました。水平線に加えて、1時間ごとに配置される垂直線もあります-時間の境界のために。そしてもう1つの重要な詳細。表示されている各カラーストリップの左側の境界線には、その始まりの黒いマークが正方形のオープニングブラケットの形で表示されます。これは、連続する電話に対応する可能性のある2つのレーンのマージを防ぐために必要です。



情報の主な処理はシート「B」で行われます（図7）。そこには、「余分な」中間の柱がたくさんあり、そのセルの値は「念頭に置いて」計算するか、最終的な式ですぐに考慮することができます。これは、各ストリップのコーナーの座標に関係します。しかし、最初は混乱する可能性があるので、とても面倒に思えました。





図： 7.基本的な計算を含むシート。



列「A」は、呼び出し日から日（ゾーン）番号を取得します。列「B」-一日の始まりからの秒単位の呼び出し時間。これは、シート「C」の列「I」と同じ値です。列「C」-通話時間を分単位で切り上げます。ここでは、そのような「不正確さ」が導入された予約をする価値があります。通話時間は20秒の精度、つまり、図の最大1ピクセル（受け入れられたスケールに基づく）で取得する必要があるように思われます。ただし、幅が1〜2ピクセルの非常に短いストライプは、チャートにうまく表示されないことは明らかです。したがって、最小ストライプ長は少なくとも3ピクセルに対応します。そして一般的に、ストリップの長さは3の倍数になります。通話時間が上方に丸められるため（1分の精度で）、実際の状況と比較して、図はわずかに「混雑」します。ただし、このオーバーフローはごくわずかです。式「INDIRECT」を使用した列「D」では、シート「A」の計算されたアドレスのパラメーター（シート「C」）から色の値が抽出されます。次に、ストリップコーナーの座標が計算されます。すでに書いたように、不必要な中間計算がたくさんありますが、やり直しませんでした。列「U」は、現在の通話の電話番号が強調表示用に指定された4つの電話番号の1つと一致する場合、強調表示の存在と帯域境界の色を計算します（シート「C」）。選択した呼び出しで、ラベルが対応するストリップに重ねられるだけでなく、ストリップの境界線の灰色もその上に書くのを忘れました（この色はパラメーターを使用してシート上で変更することもできます）。通常の場合、ストリップには縁取りがありません。最後に、次の3列では、SVGグラフィックマークアップ言語でのテキストの最終的な形成が行われます。この記事では、この言語の説明と構文については考慮しません。実際、これは難しいことではありません、私は数分でそれを理解しました。 「V」列には、境界線のあるストリップを描画するコードが生成されます。



例：

<path fill="#FF5050" stroke="#808080" d="M1598,51L1598,59L1601,59L1601,51L1598,51" style="stroke-width: 1px;" stroke-width="1" stroke-dasharray="0"></path>.

列「W」には、ストリップの左端のコードが含まれています。



例：

<path fill="none" stroke="black" d="M1599,52L1598,52L1598,58L1599,58" style="stroke-width: 1px;" stroke-width="1" stroke-dasharray="0"></path>.

列「X」には、必要な場合にのみラベルテキスト（番号1、2、3、または4）を表示するためのコードが含まれています。この選択性は、式「IF（U2 <> "none"; ...; ...）」を使用して実行されます。



サンプルテキスト「3」：

<text x="1601" y="58" style="text-anchor: middle; font-family: times; font-weight: bolder; font-size: 8px;" stroke="none" fill="black"><tspan>3</tspan></text>.

図8は、これら3つの列のスクリーンショットを非常に小さいスケールで示しています。それ以外の場合は、テキストの大部分のためにデモンストレーションすることがほとんど不可能です。また、すべての引数を使用して式「CONNECT」を記述するのがいかに面倒であるかを確認できます。





図： 8.基本的な計算の結果を含む列。



「碑文」シートでは、碑文は図の上（時間マーカー）と図の左側（日付）に形成されます（図9）。式には、サイズ、スタイル、フォントの色、境界線などのフォントパラメータが含まれています。計算の主な焦点は、日付と時間によるセルの自動入力、均一なステップによるテキストの位置の座標の計算です。





図： 9.碑文を形成するシート。



「境界線」シートには、ゾーン（日付）と時間の境界として機能する、図のすべての補助線が形成されます。図10は、ゾーンごとの水平線の形成を示すスクリーンショットを示しています。最初の2列には、ゾーン番号（ゼロから開始）とその相対的な垂直座標が含まれています。 3番目の列は、線を引くSVGコードを生成します。ここで、コードの作成では、おなじみの式「CONNECT」だけでなく、2つの式「IF」も使用され、一方が他方の中にネストされています。これは、状況に応じて3つの異なる色の線画を実装するために必要です。上で述べたように、黒い線は月、灰色-週、および薄い灰色-日を区切ります。最後の2色は、セルB17およびC17のシート「C」で指定されています。式「IF」の引数には、式「DAY」と「OSTAT」があります。最初の式は、整数として指定された日付からの数値を認識します。これは、ゾーン番号の値を（最初の列から）事前に選択された定数42491だけシフトすることによって取得されます。



特に、日付からの数値が単位と等しいかどうかのチェックが行われ、それによって新しい月の始まりが認識されます。 「OSTAT」式は、新しい週の始まりを認識するために使用されます（従来のアルゴリズム）。この式の2番目の引数は7です。これは、週に7日あるためです。特に、分割の残りの部分は値1と比較されます。この値（0から6）は、図の曜日のシフトを調整するために使用でき、実際のカレンダーと一致するように選択されます。水平線が形成された後、25本の垂直線がより簡単な方法で形成されます（1時間ごとに23本の線とさらに2本の境界線）。





図： 10.境界を形成するシート。



シート「ささいなこと」（図11）には、図の特性に関する追加情報の形成が登録されています。列「B」と「C」には、各要素のオフセット座標が含まれています。





図： 11.シート形成の追加情報。



[占有率]タブでは、時間の経過に伴う通話密度の分布のヒストグラムが作成されます（図12）。これは、さまざまな長さの垂直線のコレクションであり、互いに近接しており、図の真下に配置されています。このような行の数は、時間要素の数（それぞれ20秒）に対応します。つまり、24 * 60 * 3 = 4320です。





図： 12.通話密度のヒストグラムを形成するシート。



線の長さ（ヒストグラムのバーの高さ）は、153日間すべての「占有された」時間要素の合計に正確に対応します。つまり、電話が当日の現在の時刻要素に該当する場合、それはヒストグラムで考慮されます。別の単純なCプログラムを使用して、このような数値配列を計算しました。 Excelセルの助けを借りて、操作の多次元性のためにそのような計算を行うことはできません。対応するプログラムコードをそこに配置することでVBAを使用することは可能でしたが、当時私はこのツールをまったく所有していませんでした。ヒストグラム値の配列を計算するためのプログラムコードを以下に示します。

#include <stdio.h>
#include <windows.h>

int main(){
	int a,b,n,c,k;
	int q[4320];
	for(n=0;n<4320;n++){
		q[n]=0;
	}
	FILE *f,*f1;
	f=fopen("ab.txt","r");
	f1=fopen("Out.txt","w");
	for(c=0;c<2102;c++){
		fscanf(f,"%i\t%i\n",&a,&b);
		for(k=a;k<b;k++){
			q[k/20]+=1;
		}
	}
	for(n=0;n<4320;n++){
		fprintf(f1,"%i\n",q[n]);
	}
	fclose(f);
	fclose(f1);
	system("PAUSE");
	return 0;
}

プログラムの入力データは、テキストファイル「ab.txt」です。各呼び出しの開始と終了の秒の値のシート「A」からの2つの列がこのファイルにコピーされています（これについてはすでに上記で説明しました。図5を参照してください）。計算された配列値は、「Out.txt」出力ファイルに出力されます。計算アルゴリズムは単純なので、説明する必要はありません。出力ファイルのデータは、「雇用」ワークシートの「D」列にコピーされます。最初の3列は、時間間隔の要素とその数の凡例です。列「E」-ヒストグラムの同じ値ですが、5倍にスケーリングされ、最も近い整数に丸められます。これは、ヒストグラムの便利な配置、明瞭さ、およびかさばりの排除のために行われます。さらに、各値は1つオフセットされます。これは、水平軸の疑似描画に必要です。ヒストグラムの値がゼロ（夜間に一般的）であっても、ヒストグラムの1ピクセルは引き続き表示されます。したがって、横軸が描画されます。



最後に、結果シートは、ドキュメントの各シートに対して生成されたすべてのSVGコードを特定の順序で結合します（最初にラベルと境界線）。私は通常の手動の列コピーを使用してこの結合を作成しました（図13）。必要に応じて、すべてのシートの結果の列を調べて、SVGファイルを自動的にエクスポートする関数をVBAに書き込むことができます。最初の行にはファイルヘッダーが含まれています。まず第一に、画像の幅と高さが含まれています。手作業で追加された最後の行は、ドキュメント、つまりメインのsvgブロックを閉じます。全部で約6800行ありました。





図： 13.結果を統合したワークシート。



次に、このシートの内容全体をテキストエディタ（私はAkelPadプログラムを使用しました）にコピーし、ドキュメントをUTF-8エンコーディングのsvg拡張子を持つファイルに保存する必要があります。その後、エラーがなければ、ファイルをインターネットブラウザで開いて表示します。以下の図は、結果の画像のさまざまな領域をさまざまな縮尺で表示したものです。





図：14.Chromeで結果の図の全体図。





図：15.図の左上隅（さまざまな境界のタイプとゾーンの名前）。





図：16.ラベル付きのチャートバー。





図：17.チャートのバーとその下のバーグラフ。





図：18.図の追加情報。





図：19.チャートバーとその上の時間マーカー。