2012年10月01日
リニューアルしたクラウド型表計算サービスZoho シート3.0を使ってみる(10) － ソルバー
今回｢ソルバー(solver)｣機能が追加になっています。Excelにはありますが、Excel Web App Previewには無い機能です。
以前の目標計算(ゴールシーク)では複数の条件下で1つの変数について最適解を求める例でしたが、今回の例のソルバーは2つの変数を持つ場合の組合せの最適解を求めます。
値段の違う2種類のケーキを組合せて、3000円以内の手土産にしたいとか、更に10個入りのケースに入れる組合せにしたいとかいった問題を扱えます。
まず1つの例から。こちらでは単価をベースにした表を作ります。その際に小計・合計数量と合計金額のセルには適切な算式を入れておきます。｢データ｣タブ→｢ソルバー｣としてセルに対する条件を設定します。合計金額が3000円以内、合計数量が10個という条件の下で、2種類のケーキの数量を決めて3000円にできるだけ近いようにしたいという意味になります。
この条件で実行した結果がこちら。
丁度3000円で5個づつの組合せで良い事になります。
今度はケーキの単価は変わりますが、その他の条件は変えずに(指定の様子はこちら)実行するとこちらになります。
小数点以下のケーキを注文する訳にいきません。恐らく、マロンロールが4個、チーズケーキが6個というのが最適解(合計金額は2960円)なのでしょう・・・
ここでの課題は(Excelでは可能な)数量を整数に制限できる条件を設定できない事にあります。今後の改善を望みたい。
合計金額だけ最大値を決めてケーキの組合せの合計数量の最大を求める場合(指定の様子はこちら)も同様な結果になります(こちら)。
2012年09月28日
リニューアルしたクラウド型表計算サービスZoho シート3.0を使ってみる(9) － 並べ替えや集計・分類機能(6)
前回と同じゴルフスコアの例で、ハーフコース毎のスコア平均を集計してみます。
こちらが前回と同じ例示で作ったスコアテーブルです。東コース・中コース・西コースという9ホールコースが3つある事を意味します。
ハーフコース別のスコアを集計します。例示のスコアテーブルでは全ての中コース・スコアだけを抽出するのが難しく、東中コースが1回しかないので、中西コースのデータだけを使います。
これまでと同様ですが、デザイン指定の｢Filter｣タブでコースを｢中西｣に限定します(こちら)。
｢Graph｣タブの指定を前半ハーフのスコアを使ってこちらのように設定して、「Click Here Generate Pivot」をクリックする事で実行します(結果がこちら)。
次にDataとして後半のスコアをセットして実行したのがこちら。
西コースでのスコアが貢献していると分ります。
2012年09月26日
リニューアルしたクラウド型表計算サービスZoho シート3.0を使ってみる(8) － 並べ替えや集計・分類機能(5)
ピボットテーブルの個人(ホーム)ユースの利用例で、今回はゴルフスコアの集計についてです。色々なコースでプレーする人もいれば、殆ど1つのゴルフコースでプレーするマイコース中心の人もいると思います。またコースもout/in 18ホールコースの場合もあれば、27ホールコース(ハーフコースが3本)の場合もあります。
今回はマイコースプレーで27ホールの場合を例とします。こちらは今回例示で作ったスコアテーブル(例示データ･･･)です。ホール毎にデータ入力していますからいずれはより深い分析に利用できるでしょうが、以下ではラウンドの合計スコアだけを使います。東コース・中コース・西コースという9ホールコースが3つある事を意味します。
月毎の集計をする為にこちらの設定をして実行したのがこちら。
中コース→西コースの順で回った回数の多いのが分りますが、合計スコアである為に数字の意味がありません。そこで｢デザインの編集｣をクリックしてこちらのように数値を平均スコア(=合計スコア÷回数)にして実行したのがこちら。
6月・7月で平均スコアが悪くなっていますが、8月から少し良くなり始めている様子を示しています。
2012年09月25日
リニューアルしたクラウド型表計算サービスZoho シート3.0を使ってみる(7) － 並べ替えや集計・分類機能(4)
今回はピボットテーブルの個人(ホーム)ユースの利用例をみてみます。
まず小遣い帳。こんなデータがあったとします。
支出について項目毎に月単位でまとめます。その為の指定をこのように項目・月日・支出をドラッグ&ドロップにより指定(こちら)して実行した所がこちら。
項目の数から右へ長くなっていて、1年間での集計となっているので、｢デザインの編集｣をクリックして指定(編集)画面に戻ります。Columnsにある項目とRowsにある月日を入替え、月日の項目を｢Month By Year｣に変更(こちら)して再度実行したのがこちら。
このような集計からこんなものとみるかどこかの項目は改善すべきと思うか・・・
勿論、収入･支出の両方で家計簿にも使えますネ。
2012年09月23日
リニューアルしたクラウド型表計算サービスZoho シート3.0を使ってみる(6) －並べ替えや集計・分類機能(3)
2012年09月19日
リニューアルしたクラウド型表計算サービスZoho シート3.0を使ってみる(5) －並べ替えや集計・分類機能(2)【改訂】
前回は並べ替えとフィルターでした。今回はクロス計算機能のピボット(Pivot)テーブルです。
こちらのようなテーブルの集計や分類・分析などをする事にします。
テーブルを指定して｢ピボットテーブルの作成｣をクリックします。こちらのようにレポートの形を指定します。
必要なのはタイトルだけでしょう。データの範囲はテーブルを選択していれば自動で入れられます。
次に｢ピボットのデザイン｣をクリックして実際のデータ操作の指定をします(こちら)。
殆ど英語記述になりますが、サンプルを見ながら｢Fields List｣にあるテーブルの見出し名をColumns・Rows・Dataの各ボックスにドラッグ&ドロップします。
分類すべき項目はColumnsかRowsに入れ、分類毎に集計したい数値の詳細はDataに入れるのが一般的(と限らない場合もありますが・・・)。
今回の場合、 id項目を商品(ID)、point項目を商品分類、value項目を単価と想定してピボットテーブルによる集計をした例がこちら。
この表の解釈として、
- 商品分類Xは比較的低価格のもので商品数の多い。
- 商品分類Ｙは高級品から普及品まで幅広い。
- 商品分類Zは高級品になっている。
これはデータ自体が少数な簡単な例になっていますが、データ量(例えば今回の例では商品数)が多い時には非常に効果があります(実際には販売量との関係で見ていくのでしょうが・・・)。
もう1つの分類例としてこれまでの例を販売実績(商品id、販売額、商品分類)としてみます。こちらのようにpoint項目をColumnsに入れ、valueをDataに入れて集計指定します。
こちらのような結果になります。
分類(X、Y、Z)毎の売上が分ります。
このように集計・分類作業は非常に容易なのですが、デジタルデータとして基本データを用意する段階が個人で利用する時は大変なのです・・・仕事の上では色々なデータがコンピュータ上で処理する事が多くなったので自動的に集まる事が多いのですが・・・
これは個人が切実な問題を解決したいと思わないと進まないでしょうが、次回には集計・分析をビジュアルに提示する事をしてみます。
ブラウザによる差異があります。確認できている点は、ピボットテーブル作成時のデザイン指定でIE9の場合にPreviewやHelpは中途半端な表示なります(こちら)。
できるピボットテーブルには問題はありません(こちら)。Google Chromeの場合はFirefoxと同じ表示でした。
【改訂】 2012年09月19日 21：45 ブラウザによる差異について追記
2012年09月18日
リニューアルしたクラウド型表計算サービスZoho シート3.0を使ってみる(4) －並べ替えや集計・分類機能(1)
今回はメニュータブ｢データ｣にある並べ替えや集計・分類機能を使ってみます。
最初に並び替え。特定列の昇降順並べ替えの他、こちらのようにテーブルを選択して3項目の条件について並べ替えができます(Excel 2003年相当)。
結果がこちら。
次がフィルター。こちらのようにテーブルを選択して｢フィルター｣をクリックすると見出し部分にフィルタ選択アイコンが出ます。
こちらのように並べ替えや特定の値の抽出ができます。
更に｢カスタムフィルター｣をクリックすると、こちらのように詳細な範囲指定ができます。
結果はこちら。直ぐ分るように左にある別のテーブルに影響を及ぼしている事に注意してください(Excel Web Appも同じ)。
テーブルからフィルターを解除するには、Excel Web Appの場合に取り消し(or戻る)処理をするのに対し、Zoho Sheetでは｢フィルター｣アイコンをクリックします。逆にいうと、Zoho Sheetでは同時に複数のテーブルフィルターを設定できないのに対し、Excel Web Appの場合は複数のテーブルにフィルターを設定した状態にしておけます。
次回にピボットテーブルを使ってみます。
2012年09月14日
リニューアルしたクラウド型表計算サービスZoho シート3.0を使ってみる(3) － 目標計算(ゴールシーク)
Zohoシートには｢目標計算(ゴールシーク)｣機能があります。例を作る事で試してみます。
まずBMI（Body Mass Index）による体重の管理について操作をしてみます。BMIを求めるには(体重kg)÷( (身長m)×(身長m) )です。例えば現状の身長と体重を入れて現状のBMIを計算した所がこちら。
目標計算では、目標としたいBMIを設定し、身長は変わらないとして目標体重を計算しようとします(目標値に対して1つの値を変化させて逆算)。その為に目標とするBMIのセルC6に計算式を入れて｢データ｣→｢目標計算｣をクリックした所がこちら。
目標としたいBMIを23として目標値のフィールドに、目標体重のエリアC5を｢変化させるセル｣に設定して｢OK｣をクリックすると、目標体重が69.64kgと計算されました(こちら)。｢OK｣をクリックすると目標体重とBMIの各セルに反映します。4kgの減量をせねば・・・
次の例は｢円周の長さに対して半径を計算｣します。上例と同じようにして円周の長さのセルC3に円周を計算する(半径)×2×πを入力し、円周の長さを100とする目標値を設定します(こちら)。
その結果がこちら。
3つ目の例が自動車ローンでの計算です。利率が一定とした場合の、元利均等の毎月の支払額を計算するにはPMT関数を使います。
計算式は
"返済額(月額)"=PMT("利率","ローン月数","借入額")
で、借入額150万円・年利3%・ローン36ヶ月とすると43,622円になります。
この式を返済額(月)のセルC6に入れ、年利3%・ローン36ヶ月の条件は変えずに、返済額(月)を35,000円にしようとする借り入れ可能額の計算がこちら。
結果がこちら。
2012年09月12日
リニューアルしたクラウド型表計算サービスZoho シート3.0を使ってみる(2) － ｢条件付き書式の設定｣の利用例
前回に｢条件付き書式の設定｣について前回に例を書いていますが、今回はその例を増やしてみる事にします。
ルールでは次の設定ができます。
- セルの値 小さい、大きい、以上、以下、等しい、等しくない、値の間、値の間でない
- テキスト 含む、含まない、始まる、終わる
- 日付 昨日、今日、明日、過去7日、・・・、今週、・・・、今月、・・・、今年、・・・、次の日付以降、・・・
- セルの状態 空白、空白でない、・・・
- 関数
｢セルの値｣を使うケースは前回に例示しました。
｢テキスト｣を使うケースがこちら。
郷土料理をリストしている中で、｢寿司｣という語が入っているセルに色付けをしています。
｢日付｣を使うケースはこちら。
ルールは2つ設定しています。｢今月｣の日付であれば茶色を、｢来週｣の日付であれば薄緑を色付けしています。この場合｢来週｣も｢今月｣の条件に入りますが、セルで複数のルールに適合した時には後の(ルール番号が大きい)条件が優先となります(Excelと逆?!)。
上掲の例ではこちらのように｢セルの状態｣のケースにもなっています。
ルール2で空白以外であれば色付けします。
｢セルの状態｣を使った例として、こちらのカレンダーの色付けもあります。
｢関数｣を使うケースがこちら。
ここではK12を含んだ数式になっています。このK12は相対参照になっていて、各セルに適用されます。また、ルール2よりルール3の方が優先度が高いという規則も暗黙に効いています。
Excelのように絶対参照($C2など)を使えるともっと適用範囲も広く融通の利く事ができるのですが、Zoho シートでは(現状は)利用できないようです。
2012年09月10日
リニューアルしたクラウド型表計算サービスZoho シート3.0を使ってみる(1) － 書式の設定
ゾーホージャパンのプレスリリースによると、クラウド型表計算サービスZoho シート3.0をリリースしました。Zoho シートについてはこちらやこちら(マクロ)で紹介しましたが、かなりの時間が経ったので再度使ってみる事にしました。
ブラウザはInternet Explorer8以降、または最新のFireFox、Google Chrome、Safariから利用する必要があります。
以下ではオンライン上単独で使えるサービスで利用が無料という意味で、(当面)SkyDrive上のMS Excel Web App Previewを意識しながらチェックします。
今回のバーションではユーザインターフェースが｢メニュータブ(MenuTab)｣と呼ばれるものになりました(こちらを参照)。
MSのExcelのリボン(メニューとツールバーの仕組み)とプルダウンメニューが混在しています。
まず｢書式設定｣のタブをチェックしてみます。
｢書式設定｣では表計算では基本的なので大きく違いはありませんが、日時と通貨の種類指定で多様な指定ができます(こちらとこちらを参照。但し、この多様性を日本においてよく使うかという点では確度が低い・・・Excel Web Appの方が用途が明確)。
もう1点は｢条件付き書式の設定｣。Excelにはありますが、Excel Web App Previewにはありません。
書式設定したいセル(群)をマークした上で、｢書式設定｣→｢条件付書式の設定｣とするかアイコンをクリックします(こちらを参照)。
ここでは営業益が赤字の商品に赤のマーク、営業益の40%以上の貢献商品には緑のマーク、5%未満の貢献商品には茶のマークをつける条件にしています。
尚、条件のプルダウンメニューに2つの｢次の値の間｣がありますが、下のものには｢でない｣が抜けていて、反対の条件をいっていますので注意してください(こちらを参照。改修済みとの連絡を受けているのでそのうちに直るでしょう・・・)。
