![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
Excel を便利にする知識やテクニックに関する動画を投稿しています。
頻繁に利用するExcelの機能を取り上げて、操作をより効率化するテクニックや意外な裏技を紹介する。
仕事の引き継ぎで作成済みのファイルを使ったり、一から作成せずに再利用したりする機会も多いだろう。前任者のExcelの習熟度によって、使いやすさも違う。まずは、どんな設定がされているかを確認する方法を紹介しよう。
Mordern Excelで利用するツール 具体的なツールは以下です。 - テーブル - Power Query - Power Pivot - Excel関数とVBA - Pythonと各種ライブラリー
役人でなくても為になる要素が少なからずあったので、かいつまんでご紹介したいと思います。
特に［IFS関数］は、［IF関数］の入れ子（ネスト）が分かりづらいと感じている方にはお勧めです。
UNIQUE関数に「ブランクセルは除外する」みたいなオプションがあればいいのに。どうしても空欄を除いたユニークデータが欲しいのなら、たとえば次のようにFILTER関数でブランクセルを取り除いてからUNIQUE関数に渡します。
=UNIQUE(FILTER(B5:B16,B5:B16<>""))
［集計方法］に従って、さまざまな集計値を求めます。指定した［参照］の範囲内に、ほかのSUBTOTAL関数を使って集計した小計が含まれている場合は、自動的にそれらの小計を除外して集計値を求めます。
AGGREGATE関数は、SUBTOTAL関数の機能を強化した関数です。
NETWORKDAYS関数を使うと稼働日（土日+休日を除く）を出すことができます。
使用例1:プロジェクトごとの金土＋会社カレンダーの休日を除いた稼働日数を調べる
XLOOKUP関数の書式 ＝XLOOKUP（検索値, 検索範囲, 戻り範囲, 見つからない場合, 一致モード, 検索モード） この数式を確定すると、右セルにも自動的に単価が表示される。この機能は「スピル」といい、複数の項目がある場合、その項目を含む配列を返す。
1. 引数の指定方法が分かりやすい 2. 検索範囲が左端でなくてもOK 3. 複数のセルに計算結果を表示できる
2番目の引数は「範囲」です。この引数は必須です。参照する表のセル範囲を指定します。 指定したセル範囲の左端の列（左から1列目）には、検索値が含まれている必要があります。またセル範囲内に検索する結果の値も含める必要があります。
INDEX関数とMATCH関数を組み合わせて実現する
MATCH関数で検索値の行を取得 ⇒取得した行に対して、INDEX関数で列を指定して値を取得する
この問題は、VLOOKUP関数の参照先としてテーブルを指定する「構造化参照」で解決できます
5つ目のエラーは、戻り値が”#N/A”というエラー値です。
このように可変リストのアドレスを指定するときこそ、OFFSET関数の出番なのです。
こちらの動画では、CELL関数でフォルダパス・ブック名・シート名を取得する方法と、その際に注意する点について解説しています。
IF関数を使った複雑な式を整理、すっきりさせてメンテもしやすく
「C3#」のような動的配列を参照する表現は活用範囲が広く、スピルの結果のセル範囲が広がれば、自動的に参照する範囲も広がります。つまり、範囲が可変のデータにも対応可能なのです。
グラフを選択したら「ページレイアウト」タブの「ページ設定」の「→」をクリックして「ページ設定」ダイアログを開く。そうしたら、「グラフ」タブを選び、「白黒印刷」にチェックを入れよう（図4上）。そのあと、ダイアログの下部にある「印刷プレビュー」を押すと、ほら、パイはグレーではなく黒や白、異なる網掛けに置き換わったから違いが一目瞭然になる（図4下）。
Excelには、表形式のデータを扱うための「テーブル」という機能があり、これを使うことでセルの書式設定や色分けなどを自動化できる。
ベタ打ちは青、計算式は黒の数字に計算結果が表示されるセルの中に、ベタ打ち（直接入力）の数字が入るのは、混乱のもと。その2つの数字は色分けを。どれが変更可能な数字か、すぐわかる効果もある。
「名前の定義」を利用すると、関数の引数が分かりやすくなる。
単純な表のコピーと違い、「元表」とデータがリンクしており、作業中に「元表」が変更になっても、テーブル側をゼロから作り直す必要がないというメリットがある。
元のファイルの値が変わることに対応したいなとか、あとからファイルが増えるかもしれないし、なんていうときにも使えるように、先を見据えて使えるテーブルを、機能を使って作ってみます。
「デザイン」タブをクリックし、「集計行」にチェックを入れます。集計行が表の下端に挿入されます。表によっては合計が自動で計算されます。
［作成］ボタンをクリックすると、工程ごとにB列の開始日とC列の終了日に応じて、矢印線が自動で引かれます。
ピボットテーブルを作成して、累計を求めることもできるので、ここではその方法を紹介します。
複数の条件設定で集計する際には、「ピボットテーブル」の利用がオススメです。関数での集計と比をするため、先ほどと同じデータでピボットテーブルの使い方を見ていきましょう。
ピボットテーブルとは、未整理の「生データ」の表を整理された表に変換する機能である。
今回はとってもカンタンな事例でピボットテーブルの操作を説明するので、その便利さを実感してもらえると思う。
Excel 2016でもOfficeアクセスキー [Alt] [D] [P] とキーを打つことで、ピボットテーブルウィザードを呼び出すことができます。
つまり、まとめると。。。 複数のシートを統合したピボットテーブルでは、、、 データソースの一番左側のフィールドが1つの"行"というフィールドになり、それ以外は全て"列"というフィールドになってしまいます。
Excelのシートに２つ以上のテーブルがあっても、そのすべてをデータソースにしてピボットテーブルを作成する方法を解説します。
4月から6月までを第一四半期にしたいのに・・・そんな設定はありません。では、どうするかというと、ピボットの元データにフィールドを追加するといいんですね
この見た目日付の文字列を［日付型］にする方法です。区切り位置指定ウィザードを使っていきます。
元データが2ヶ月分程度のデータであれば、四半期にまたがっているわけでも、年にまたがっているわけでもないので、自動的にできる単位は「月」、「申込日」の単位は「日」となります。
合計/合計にはフィルターボタンがありません。赤枠の範囲の並び替えをしたいと思います。
集計した値を降順で並べ替えした方が、どのアイテムの内訳が大きいかを把握しやすいため、並び順に指定がない場合、原則はこちらで対応しましょう。
つまりピボットテーブルには、通常の方法では行や列を挿入できない仕様になっている。こうしたケースで利用したいのが「集計フィールド」という機能だ。
エクセルの集計方法には、以下の4つがあります。 - 集計：ワンクリックでかんたんな集計ができる機能 - 関数：指定した範囲の計算を自動的にしてくれる数式 - ピボットテーブル：必要なデータだけ抽出して集計、グラフなどもかんたんに作成 - 統合：複数のシートをまとめて計算する機能
表がテーブルになっていると、［小計］の挿入はできないので、テーブルを解除して通常の表にしましょう。
先ほどの操作で、4～6月分のデータを追加したのに元の数式のままなので、エラーインジケーターが表示されているのです。
このようにセルの分割を容易にできる。その快適度はひょっとすると「区切り位置」を利用するよりも上かもしれない
日付では、月ごとの月初めや月末の日付を連続して作成したいことがよくある。この場合、次の方法を取ればよい。
「グラフの書式」をコピペする
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で表示されます。なお、日本語も問題ありません。コマンドオプションとして、無視するセルの範囲や、ワークシートなどを指定できます。
ssconvert unoconv catdoc
姓と名の間に空白文字が入力されていれば、［区切り位置指定ウィザード］を使って姓と名を分割できます。
