不二 桜：大公開！Excel 達人の並べ替え・表の作り替えテクニック Bible |
作成日： 2015-03-22 最終更新日： |
名前は大仰だが、Excel で集計や抽出をするときの基本テクニックから応用テクニックまでが解説されている。
Excel 2013 で取り入れられた概念。 別のシートにしかない項目の見出しを使って集計するために、シートどうしの関連を明示するための概念および操作。
同じくExcel 2013 で取り入れられた概念。 データで認識したパターンを自動判別して、数式を使わずに残りのデータを自動入力してくれる機能。
p.334 現在の状況の終わりあたり、「～以上」を表す「>=」を関数の前に着けてもなぜが抽出できない。 とある。正しくは＜なぜか＞であろう。
p.520 上から5行目、Web 研修の成績表もとに、とあるが、Web 研修の成績表をもとに、が正しいだろう。
表 A と表 B がある。両者をマージして新しい表を作りたい。この表は A 1行目→表 B 1行目→表 A 1行目→表 B 2行目→の順番で交互に並ぶ。 同書では、2 つの表に1つづつ空白行を挿入し、2つめの表のデータを1つめの表に「空白を無視する」オプションを使って貼り付けることで実現する方法を紹介している。
ここからは私のアイディアだが、表A と 表B がそれぞれ属性でシリアル値を持っていてその値の昇順に沿ってマージするのであれば、 別の方法もある。それは、表属性の列を設け、そこにA と B を記入した、表Bを表Aの下につなげ、全体をソーティングする方法である。 第1キーはシリアル値で、第2キーは表属性（AまたはB）である。
なお、空白行を１行おきに作るには、1. 表の行のわきに連番をふる。2. 表の直下の空白行に 1. と同じように連番をふる。3. 連番を含む行で昇順にソートする。 という手続きを使う。これはおもしろい。
多数の表をマージするときに、クリップボードが使えることを初めて知った。
なかなか難しいが、こんなことのようだ。まず、表1として次のタイトルを用意する。
| 店名＼月 | 3月 |
|---|
次に、表Aを用意する。
| 店名＼月 | 1月 | 2月 | 3月 |
|---|---|---|---|
| 越谷 | 310 | 222 | 148 |
次に、表Bを用意する。
| 店名＼月 | 1月 | 2月 | 3月 |
|---|---|---|---|
| 草加 | 240 | 180 | 150 |
以上が完了したら、表1を選択する。そして、統合機能を選び、 [統合元範囲]に表Aと表Bを選択、追加する。すると、所望の表が得られる。
| 店名＼月 | 3月 | ||
|---|---|---|---|
| 越谷 | 310 | 222 | 148 |
| 草加 | 240 | 180 | 150 |
枝番がハイフンで表示されたコードがあるとする。 枝番つきのコードを並べ替えるには、ハイフンを substitute 関数でピリオドに置き換え、 数値 1 を乗じて数値化する。こうすれば、数値の並び替えに帰着される。これには感心した。 なお、この方法が適用できるのは枝番が一ケタのときに限られる。
カテゴリカルデータに順序数を割り当てて、並び替えに活用することがよく行われている。
この割り当てに使えるのが match 関数である。
= match(検査値, 検査範囲, FALSE)
である。FALSE 以外に 1 や -1 を使う場面に出会ったことは、私はまだない。
並び替えで苦慮するのが空白欄の扱いである。著者はさまざまなテクニックを紹介している。
こんな例がある。A 列に識別情報が１行おきに入っていて、情報のないセルはブランクである。 B 列には識別情報に対応する属性が２行にわたって入っている。 A 列の識別情報から抽出される二次情報をもとに並び替えたいのだが、ブランクが邪魔をしている。そこでどうするか。
まず、C 列に A 列の識別情報をブランクなしに展開する。そのために C2 セルに次のような関数を使う。
foo は情報抽出関数である。
=foo(index(A 列の範囲, int((row(a2)) / 2) * 2 - 1))
これを C 列すべてのセルに適用して、C 列をキーに並び替える。
int 関数で小数点が切り捨てられることを利用している。
都道府県が記載されている住所から都道府県を切り出すには次の式がある。住所は b3 セルにあるとする。
= left(b3, (mid(b3, 4, 1)="県") + 3)
これはトリッキーである。説明しよう。都道府県名は一都一道二府は必ず２文字、43 県は 2 文字か 3 文字である。
さて、mid(b3, 4, 1) で得られる値は b3 セルの左から 4 文字目の 1 文字であり、ここに文字"県"があれば 1 が、なければ 0 が返される。
そこで、3 文字の県（神奈川、和歌山、鹿児島など）は 左から 4 文字までの件名が、そうでなければ左から 3 文字までの件名が切り出せる。
小計をもとに並び替えるにはアウトラインを使う、別の場所のセルを参照したセルを並び替えるには indirect 関数を使うなど、 なるほど、と思わせる技術が公開されている。
| 書 名 | 大公開！Excel 達人の並べ替え・表の作り替えテクニック Bible |
| 著 者 | 不二 桜 |
| 発行日 | 2013 年 3 月 1 日 |
| 発行所 | 技術評論社 |
| 定 価 | 3500 円（本体） |
| サイズ | A5 変形版 |
| ISBN | 978-4-7741-5500-5 |
| その他 | 南越谷図書館で借りる |
| NDC | 007 |
まりんきょ学問所 ＞ 読んだ本の記録 ＞ 不二 桜：大公開！Excel 達人の並べ替え・表の作り替えテクニック Bible
