Excel:ピボットテーブルの使い道と作り方(テストデータ付き)

ピボットテーブルとは、集計作業を行う機能のことです。
自力で集計用の表を作成して関数を書くことでも集計作業は可能ですが、ピボットテーブルを用いればマウス操作だけで簡単に集計作業を行うことができます。

今回の記事では、架空の体力測定結果を集計する例を挙げて、ピボットテーブルの使い方を説明します。
この例では、握力について、男女で差があるのか、測定月で差があるのかを、平均値を出すことで確認します。

なお、使用するデータは以下からダウンロードできます。手を動かしながら覚えたい方はダウンロードしてみてください。
https://cyzennt.co.jp/blog/wp-content/uploads/2021/10/f0baae9405ab2b4cf966538a631c041e.xlsx


【手順】

1.集計作業を行う表をドラッグで選択し、挿入>ピボットテーブルを選択する。

2.ドラッグした範囲が自動的に選択されているので、OKボタンを押下する。

3.ピボットテーブルのシートが生成されるので、列に測定月、行に性別、値に握力(kg)をドラッグして選択する。

4.「合計 / 握力(kg)」の右にある矢印を選択し、値フィールドの設定を選択する。

5.デフォルトでは合計が選択されているので、平均を選択し、OKボタンを押下する。

6.性別ごと、また測定月ごとで、握力(kg)の平均値が算出される。

これで、男女では大きな差があるが、測定月では大きな差がないことが一目でわかるようになりました。


いかがでしたでしょうか。

実務でも集計作業を行うことはあり、例えば性能試験の結果の分析で集計作業が必要になります。
そのような場合に、ピボットテーブルで作業を効率化することができるので、覚えておいて損はない機能だと思います。

ExcelでINSERT文を作成

Excelの機能を使うことで、簡単な手順で大量のSQLのINSERT文を作成することができます。
臨時の運用対応ではデータが公表されてから30分以内にDBに反映させなければならない等、限られた時間でのデータ補正が必要になるため、プログラムを事前に用意できない場合はExcelでの対応が有効になります。
運用対応だけでなく、開発時のテストデータ作成も効率的に行うことができます。

以下で、例を挙げて説明していきます。

【INSERT対象のテーブル定義】

【INSERT文の作成】

①下記のように入力する。

G2セルについては、下記の式を入力する。

=A$2&”000000″

この式によりA2セルと文字列”000000″を結合する。

参照時には$マークをつけて絶対参照とする。

(後の手順でコピーする時に参照先がずれないようにするため)

②B2~H2セルを3~6行目にコピーする。

③C3セルに2を入力してから、C2~C3セルを選択し、4~6行目までオートフィルする。

④B2~H6セルをコピーし、サクラエディタ等のテキストエディタに張り付ける。

⑤セルを区切るtabを削除する。

サクラエディタの場合は、”\t”を””に置換することで削除できる。

【完成したINSERT文】


なお、UPDATE文やDELETE文等も、同じ要領で作成することができます。
また、今回は紹介していませんが、区切り位置、重複の排除、フィルタ等も使用頻度が高い機能であり、これらの機能を使えばフォーマット変換やデータ抽出等も可能になります。


いかがでしたでしょうか。

今回紹介したようなテクニックは技術的には難しいことはしていませんが、実務で良く使う便利なテクニックです。
このようなテクニックはまた紹介していきたいと思います!

Excelで文字の一括置換を行う方法(外部プログラムからの操作)

Excelファイル(.xlsx)は複数のxmlファイルを圧縮して構成されています。
解凍することでxmlファイルの形式として開けるようになり、通常のテキストエディタで編集することが可能になります。
これを利用することで、全てのシートに対して、オブジェクト内(例えば吹き出し内)の文字も含めた文字の一括置換が可能になります。
(Excelの機能では、オブジェクト内の文字を検索したり置換したりすることができません)

以下、手順例です。

1.Excel形式のファイルを用意する

2.拡張子を「.xlsx」から「.zip」へ変更する

3.2のファイルをLhaplus等で解凍する

4.サクラエディタのgrep置換等の機能で文字の一括置換をかける

※本文中の文字は xl\sharedStrings.xml に定義されます。
※図形中の文字は xl\drawings\drawing*.xml に定義されます。
※drawing*.xml に関しては、半角文字と全角文字が入れ替わる箇所で、xmlファイル上で別々の定義になるので注意が必要です。例えば、「hogeほげ」という文字については、xmlファイル上で「hoge」と「ほげ」という文字に分割されます。

5.Lhaplus等で再度圧縮する

※1のファイル名のフォルダの直下で作業を行う必要があることに注意

6.圧縮されたファイルについて、拡張子を「.zip」から「.xlsx」へ変更する

7.6のファイルを開いて置換されたことを確認

※「~の一部の内容に問題が見つかりました。可能な限り内容を回復しますか?」と聞かれるかもしれませんが、「はい」で良いです。


なお、7-Zipを用いれば、解凍や圧縮をコマンドラインから行うことが可能になります。 また、サクラエディタにはコマンドラインからマクロを読みこませる機能もあります。 これらの機能を組み合わせれば、バッチファイルから複数のExcelファイルに対して一括置換を行うことも可能になります。


いかがでしたでしょうか。

このように、xlsxファイルは複数のxmlファイルで構成されているため、解凍・再圧縮を行うことで外部プログラムからの操作が可能になり、作業を効率化できる可能性があります。
少々強引な方法にはなりますが、知っておくと使える場面があるかもしれません。

ドキュメント作成時の基本的な心構え

ドキュメント作成のコツについて一般的なことを書きます。
ドキュメントの良し悪しで意図が早く正確に伝わるかどうかが変わってくるので、スムーズに仕事をこなすには欠かせないスキルです。

ドキュメントには設計書からユーザ向けのマニュアル、運用手順等、色々種類がありますが、今回はどのようなドキュメントについても通じることを書きます。

【根→葉の流れで構造的に書く】

小説のように文章をつらつらと書くのではなく、適度に見出しを付けることが重要です。
また、その見出しは、根→葉の流れで構造的に書くことが重要で、見出しだけを見れば何がどこに書いてあるのかがわかるようにすることが理想です。
プログラム改修の設計書で言うと、以下のように書くのが良いでしょう。
 
 1.背景
  ……
 2.リリース日
  ……
 3.修正箇所
  3.1.プログラムA
   3.1.1.hoge対応
    ……
   3.1.2.fuga対応
    ……
  3.2.プログラムB
   ……
  3.3.プログラムC
   ……

【箇条書きや表や図で完結に書く】

・箇条書きの使用

並列である複数の事柄を書く時は、箇条書きを用いると文章が見やすくなります。
例えば、
「条件Aの時か、条件Bの時か、条件Cの場合に、hoge処理をする」
という文章は、以下のように書くとわかりやすくなります。

以下の何れかの条件に当てはまる時に、hoge処理をする。
 ・条件A
 ・条件B
 ・条件C

・表の使用

2×2かそれ以上の項目・条件が絡み合う事象を書く場合は、表を用いると状況をわかりやすく整理できます。

例えば、下記はリスクの対応策を表にまとめたものです。

これを表を使わずに表現すると、
「脅威に対しては回避と転嫁と軽減の戦略があり、回避とは…」
といういかにも冗長な文章になってしまいます。

・図の使用

複雑な事象を言葉で説明するのは難しいですが、図解するとわかりやすく説明できることが多いです。

例えば、理解が難しい概念である「モジュール強度」と「モジュール結合度」を図解した記事がこちらです。
「モジュール強度とモジュール結合度」の図解

日本語を読んでも何が言いたいのかわかりにくいと思いますが、図解することで意図を理解しやすくなると思います。

【誤解のない表現を使う】

・客観的な数値を示す

「性能が大幅に良くなった」「キャパシティに与える影響は軽微である」
といった主観的な表現だと、それぞれの受け手毎に異なる解釈をされる可能性があります。

ここは、異なる解釈をされないように、
「性能が10倍になった」「ディスク容量が100GBであるがデータ増加量は10MBである」
といった形で、客観的な数字で表すことが重要です。

・複数の意味で取られる日本語を避ける

例えば、「私は何度もコンソールがエラーを出力する所を見ました」という文章の場合、「何度も」の係り受けが曖昧であるため、以下の2つの意味で取られる可能性があります。
 ・私は「コンソールがエラーを出力する所」を何度も見た
 ・私は「何度もコンソールがエラーを出力する所」を見た

このようなことが設計書や手順書を書く中でも起こり得ます。

出来上がった文章を読み返して、複数の意味に取られないか考える癖を身に付けることが重要です。複数の意味に取られかねない時は、上手く書き換える必要があります。
特に、文章を短く区切る書き換えは、文章が分かりやすくなる可能性が高くお勧めです。
例えば、上記の例の場合、「コンソールは何度もエラーを出力しました。私はそれを見ました。」と書き変えると、意味が明確になります。

・要件、仕様等を明確にする

要件定義書なら誰がいつどのようにシステムを使うのか定義する必要がありますし、画面設計書なら画面項目のフォーマットや桁数等を定義する必要があります。

ドキュメントによって絶対に定義しなければならないポイントがあるので、そのポイントを落とさないようにすることが重要です。
もし定義が不十分だと、情報の受け手に自分の意図とは異なる解釈をされてしまう恐れがあります。

【情報の受け手に合わせて粒度を変える】

システムの利用者にとっては、システムをどのように操作すれば良いのかがわかれば良いので、詳しい仕様は冗長な情報になります。
逆に、システムの開発者にとっては詳しい仕様こそが重要になります。

このように、情報の受け手によって、どのような粒度で情報を求めているかが異なります。
受け手にとって粒度が細かすぎる場合は知りたい情報を誤解なく手早く知ることが困難になりますし、粗すぎる場合は知りたい情報を十分に知ることができなくなります。
ドキュメントを作成する際は、情報の受け手が誰であるかを想像して、適切な粒度で情報を提供することが重要になります。


いかがでしたでしょうか。

今回は、私がドキュメントを書く時に意識していることを列挙してみました。
ドキュメントはチームで仕事を進めていく上では欠かせないものですし、未来の自分が内容を思い出すためにドキュメントを読むこともあるので、今までドキュメントを重視してこなかった方は是非意識して書いてみてください。

なお、Slackのようなチャットツールでの会話でも、この記事で挙げたドキュメント作成のスキルを活かすことができます。
特に昨今はテレワークが普及しており、チャットツールでの会話も増えているかと思いますので、チャットでの意思疎通がスムーズにできるだけでも仕事ができる人だとみなされやすくなると思います。


アジャイル開発の概要

プロセスモデルの一つとして、「アジャイル開発」というものがあります。
ウォーターフォールモデルと比較すると新しいモデルです。

アジャイル開発はその名の通り迅速に開発を行うもので、短い期間(数週間~1ヶ月程度)毎にユーザにとって重要な機能から順番にリリースを行います。
一つの期間はイテレーションと呼ばれ、計画・設計・実装・テストがこの期間の間に行われます。
このように開発を行うことで、ユーザは少ない期間・コストで重要な機能を使用することができるようになります。ユーザの要求の変更にもいち早く対応することができます。
また、システムを実際に使うと新たな要望が浮かぶものなのですが、システムを早い段階で手に取ることができるので新たな要望を早く出すことができます。仮に、せっかくリリースした機能がユーザに響くものでなかったとしても、その問題に少ない期間・コストで気付き、軌道修正することができます。
これらの利点により、重厚長大なウォーターフォールモデルを採用した場合に比べて、ビジネス上で優位に立つことができるようになります。
(特にスタートアップ企業にとってはこれらの利点が重要)

しかし、アジャイル開発はユーザの要望が頻繁に変わる小規模システムの開発には向くものの、要望が固定されているミッションクリティカルな大規模システムの開発には向かないとされています。
アジャイル開発ではシステムを小出しで開発するので、システム全体の整合性や最終製品の品質を上手く管理しないと、システムの品質が低下する問題やシステムのスケーラビリティが損なわれる問題が発生してしまいます。
そのため、教科書的には、要望が固定されているミッションクリティカルな大規模システムの開発はウォーターフォールモデルの方が向いているとされています。

ユーザーの要件を満たすプロダクトを迅速に開発するためには、それを実現するための手法や考え方が必要です。
アジャイル開発を実現するための手法、またアジャイル開発と親和性の高い考え方としては、以下のようなものがあります。

・アジャイルソフトウェア開発宣言

アジャイル開発の価値観を端的に言い表した宣言である。
原文はこちら。

・エクストリームプログラミング(XP)

迅速にプログラミングを行うための手法。
リファクタリング(保守性を高めるためのコード見直し)、ペアプログラミング(一人がコーディング、もう一人がリアルタイムにコーディングを見ることで、即座にレビュー・知識共有を行う)、テスト駆動開発(テストケース・テストコードを先に設定してからプログラミングを行う手法で、テスト自動化が伴う場合が多い)、等により実現する。

・プロトタイピング

ユーザからのフィードバックを早期に得るために試作品を作成して提供する。
試作品はコーディングして作成するとは限らず、紙芝居のような形でコンセプトを伝えるペーパープロトタイピングと呼ばれる手法もある。

・スクラム

チーム一体となってプロジェクトを遂行して行くことに重点を置くプロセスモデル。
デイリースクラム(始業時に今日の予定と問題点を共有する)、プロダクトバックログ(システムの要求一覧)、スプリントバックログ(イテレーション内で対応する要求一覧)、イテレーション、スプリントレビュー(イテレーション内で開発した機能のユーザとのレビュー)、ふりかえり(イテレーション終了時の改善点の共有)等により実現する。

・バーンダウンチャート

縦軸に「残作業量」、横軸に「時間」を割り当て、残作業量を折れ線グラフにより記述したチャート。
ガントチャート等と比べて、プロジェクトの進捗状況をいち早く把握することができる。
誰もが見える場所に張り出すと効果が高い。

・リーン生産方式

トヨタ生産方式を元に編み出された方式。
開発工程におけるムダを排除することを目的として、製品および開発工程の全体にわたって、トータルコストを系統的に減らそうとするのが狙い。「ムダをなくせ(顧客に価値を与えない作業を無くす)」「学習を強化せよ(反復型開発)」「決断を遅らせよ(状況の変化に合わせた決断)」「できる限り早く出荷せよ(機会損失防止)」「チームに権限を(チームの手で最良のプロセスを採用)」「摺り合わせて作り込め(ユーザとのトライ&エラーの繰り返し)」「全体を見よ(全体最適化)」という7つの原則によって成り立っている。

・チケット駆動開発

スプリントバックログを約2~3時間のタスクである「チケット」に分割し、チケットを管理することで、メンバーの作業把握と成果物の更新理由の把握を容易にする。
チケットによる管理を行う場合は、チケットの完了(Done)の定義を明確にし、完了基準を満たすまでは完了とみなさないようにすることも重要である。

・ストーリーポイント

案件の規模や複雑性を「ストーリーポイント」と呼ばれる直感的・相対的な基準で測ることにより、俯瞰的な視点で直感的に迅速に見積もりを行う。


いかがでしたでしょうか。

日本ではアジャイル開発はあまり普及しておらず、アジャイル開発を採用しているプロジェクトには巡り合えないかもしれませんが、それでも顧客の要望に沿ったシステム開発を行う必要があることには変わりありません。
そのため、ウォーターフォールの開発だとしても、アジャイル開発の手法や考え方は部分的にでも取り入れることは有効ですし、実際に部分的に取り入れているプロジェクトも少なくありません。
開発者としても、アジャイル開発の手法について知っておくことに越したことは無いでしょう。