![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
頻繁に利用するExcelの機能を取り上げて、操作をより効率化するテクニックや意外な裏技を紹介する。
仕事の引き継ぎで作成済みのファイルを使ったり、一から作成せずに再利用したりする機会も多いだろう。前任者のExcelの習熟度によって、使いやすさも違う。まずは、どんな設定がされているかを確認する方法を紹介しよう。
Mordern Excelで利用するツール 具体的なツールは以下です。 - テーブル - Power Query - Power Pivot - Excel関数とVBA - Pythonと各種ライブラリー
役人でなくても為になる要素が少なからずあったので、かいつまんでご紹介したいと思います。
先ほどの操作で、4～6月分のデータを追加したのに元の数式のままなので、エラーインジケーターが表示されているのです。
UNIQUE関数に「ブランクセルは除外する」みたいなオプションがあればいいのに。どうしても空欄を除いたユニークデータが欲しいのなら、たとえば次のようにFILTER関数でブランクセルを取り除いてからUNIQUE関数に渡します。
=UNIQUE(FILTER(B5:B16,B5:B16<>""))
［集計方法］に従って、さまざまな集計値を求めます。指定した［参照］の範囲内に、ほかのSUBTOTAL関数を使って集計した小計が含まれている場合は、自動的にそれらの小計を除外して集計値を求めます。
AGGREGATE関数は、SUBTOTAL関数の機能を強化した関数です。
NETWORKDAYS関数を使うと稼働日（土日+休日を除く）を出すことができます。
使用例1:プロジェクトごとの金土＋会社カレンダーの休日を除いた稼働日数を調べる
XLOOKUP関数の書式 ＝XLOOKUP（検索値, 検索範囲, 戻り範囲, 見つからない場合, 一致モード, 検索モード） この数式を確定すると、右セルにも自動的に単価が表示される。この機能は「スピル」といい、複数の項目がある場合、その項目を含む配列を返す。
2番目の引数は「範囲」です。この引数は必須です。参照する表のセル範囲を指定します。 指定したセル範囲の左端の列（左から1列目）には、検索値が含まれている必要があります。またセル範囲内に検索する結果の値も含める必要があります。
INDEX関数とMATCH関数を組み合わせて実現する
MATCH関数で検索値の行を取得 ⇒取得した行に対して、INDEX関数で列を指定して値を取得する
この問題は、VLOOKUP関数の参照先としてテーブルを指定する「構造化参照」で解決できます
5つ目のエラーは、戻り値が”#N/A”というエラー値です。
このように可変リストのアドレスを指定するときこそ、OFFSET関数の出番なのです。
「C3#」のような動的配列を参照する表現は活用範囲が広く、スピルの結果のセル範囲が広がれば、自動的に参照する範囲も広がります。つまり、範囲が可変のデータにも対応可能なのです。
グラフを選択したら「ページレイアウト」タブの「ページ設定」の「→」をクリックして「ページ設定」ダイアログを開く。そうしたら、「グラフ」タブを選び、「白黒印刷」にチェックを入れよう（図4上）。そのあと、ダイアログの下部にある「印刷プレビュー」を押すと、ほら、パイはグレーではなく黒や白、異なる網掛けに置き換わったから違いが一目瞭然になる（図4下）。
ベタ打ちは青、計算式は黒の数字に計算結果が表示されるセルの中に、ベタ打ち（直接入力）の数字が入るのは、混乱のもと。その2つの数字は色分けを。どれが変更可能な数字か、すぐわかる効果もある。
「名前の定義」を利用すると、関数の引数が分かりやすくなる。
単純な表のコピーと違い、「元表」とデータがリンクしており、作業中に「元表」が変更になっても、テーブル側をゼロから作り直す必要がないというメリットがある。
元のファイルの値が変わることに対応したいなとか、あとからファイルが増えるかもしれないし、なんていうときにも使えるように、先を見据えて使えるテーブルを、機能を使って作ってみます。
「デザイン」タブをクリックし、「集計行」にチェックを入れます。集計行が表の下端に挿入されます。表によっては合計が自動で計算されます。
［作成］ボタンをクリックすると、工程ごとにB列の開始日とC列の終了日に応じて、矢印線が自動で引かれます。
ピボットテーブルを作成して、累計を求めることもできるので、ここではその方法を紹介します。
複数の条件設定で集計する際には、「ピボットテーブル」の利用がオススメです。関数での集計と比をするため、先ほどと同じデータでピボットテーブルの使い方を見ていきましょう。
ピボットテーブルとは、未整理の「生データ」の表を整理された表に変換する機能である。
今回はとってもカンタンな事例でピボットテーブルの操作を説明するので、その便利さを実感してもらえると思う。
合計/合計にはフィルターボタンがありません。赤枠の範囲の並び替えをしたいと思います。
Excel 2016でもOfficeアクセスキー [Alt] [D] [P] とキーを打つことで、ピボットテーブルウィザードを呼び出すことができます。
つまり、まとめると。。。 複数のシートを統合したピボットテーブルでは、、、 データソースの一番左側のフィールドが1つの"行"というフィールドになり、それ以外は全て"列"というフィールドになってしまいます。
ExcelのPower Queryを使って、指定したフォルダー内の複数ファイルのデータを1つのテーブルに結合できます。
しかし、ピボットテーブルで扱う情報は、合計や平均を計算できる数値に限られる。例えば、機能の有無などの非数値の情報を扱うことはできない。こうした非数値のマトリックス表は、Power Query（パワークエリ）で自動作成することが可能だ。
この見た目日付の文字列を［日付型］にする方法です。区切り位置指定ウィザードを使っていきます。
元データが2ヶ月分程度のデータであれば、四半期にまたがっているわけでも、年にまたがっているわけでもないので、自動的にできる単位は「月」、「申込日」の単位は「日」となります。
本書は、さまざまなデータをワンクリックで取り込み、相応しい形に加工し、一つのExcelファイルにまとめ上げる作業を自動化する方法を紹介します。
日本で初めてとなるMicrosoft Power Query（パワークエリ）専門の書籍です。
エクセルの集計方法には、以下の4つがあります。 - 集計：ワンクリックでかんたんな集計ができる機能 - 関数：指定した範囲の計算を自動的にしてくれる数式 - ピボットテーブル：必要なデータだけ抽出して集計、グラフなどもかんたんに作成 - 統合：複数のシートをまとめて計算する機能
表がテーブルになっていると、［小計］の挿入はできないので、テーブルを解除して通常の表にしましょう。
「グラフの書式」をコピペする
Excelには、グラフに「近似曲線」を追加できる機能が用意されている。この機能を使って「移動平均線」を描画することも可能だ。
「円グラフ」によく似たグラフとして、「ドーナツ」と呼ばれるグラフも用意されている。
3. 「Ctrl＋1」でセルの書式設定を開く。
始点のセルを選択後、［Shift］キーと［Ctrl］キーを押したまま、矢印キーを押す
[Alt] → [A] → [H] [Alt] → [A] → [j]
行全体を選択した状態で"Ctrl + Shift + +（プラス）"を押下すると1行挿入されます。
SUM関数を入力したいセルを選択して、［Shift］＋［Alt］＋［=］キーを押します
[Ctrl]＋ [ を同時に押します。
シートの番号を指定してシート名を取得できます。これを使ってシート一覧を表示できます。
個人用マクロブックはPERSONAL.XLSというファイルで、Excelを起動すると常に裏で開いているけど、表に表示されないファイルです。ただ裏で開いているのでマクロは普通に使えるし、他のExcelファイルでも使えるようになります。
マクロを作成すれば、Excel上からそのままメールを配信できます。一部の人だけ配信先から除外したいという場合も、 Excel上で「○」「×」のフラグを切り替えるだけで簡単に対応できます。
複数のメールをDisplayメソッドでチェックしたい場合
下書きフォルダに保存する場合 mailItemObj.Display → mailItemObj.Save
ruby の gem(spreadsheet) だけが有れば利用可能ですので Linuxなどでも利用することが可能です。
RailsにはExcelを扱うGemがいくつかあり、それぞれ特徴があるのでまとめてみる。
シゴト高速化プログラムを集大成!!Excel操作の基本―行列、セル、ブックをPythonで操作する。日常業務の効率化―フォーマットを統一、データ集計に活用。ライブラリを便利に使う―データ収集や分析も自動処理でラクラク。
「種類」の下にあるテキストボックスの値を「＃,＃＃0,」と書き換える。ポイントは「0」の後に付けた「,（カンマ）」だ。「OK」ボタンを押すと、「千円」単位で値を表示できた。
「新しいウィンドウを開く」を選ぶと、現在のシートを新しいウィンドウに表示できる。「ウィンドウの切り替え」で表示を切り替えられる。
Office Professional PlusのExcelならば、付属ツールのInquireアドインで簡単に比較分析できる。
シート毎、セル毎に違う部分が抽出されます。ビジュアル的に出るのではなく、CUIで表示されます。なお、日本語も問題ありません。コマンドオプションとして、無視するセルの範囲や、ワークシートなどを指定できます。
1回Queryを行うと、そのときのソースや読み込みに関する情報を記憶するため、2回目以降はQueryの「更新」を行うだけで、最新情報（例えば、上書や追記きされたファイル）に更新できる。
ssconvert unoconv catdoc
姓と名の間に空白文字が入力されていれば、［区切り位置指定ウィザード］を使って姓と名を分割できます。
