![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
Excel を便利にする知識やテクニックに関する動画を投稿しています。
Microsoft 365 サブスクリプションに付属する Office for Mac は、新機能、セキュリティ更新プログラム、およびセキュリティ以外の更新プログラムを提供するために定期的に更新されます。
頻繁に利用するExcelの機能を取り上げて、操作をより効率化するテクニックや意外な裏技を紹介する。
仕事の引き継ぎで作成済みのファイルを使ったり、一から作成せずに再利用したりする機会も多いだろう。前任者のExcelの習熟度によって、使いやすさも違う。まずは、どんな設定がされているかを確認する方法を紹介しよう。
4.選択中の空白セルに「休」と入力して「Ctrl (Control)」＋「Enter」を押すと、選択エリア内全ての空白セルに「休」が入力されて完成。
「【財務モデルのコツ①】 A列は空けておき、適当に文字を打っておく（Xとか） あとはCtrl + 矢印で、表から表へスピード移動できます。 ちなみにこのA列を、ニューヨークでは Elevator Column（エレベーターの列）と呼びます。かっこええ。
Mordern Excelで利用するツール 具体的なツールは以下です。 - テーブル - Power Query - Power Pivot - Excel関数とVBA - Pythonと各種ライブラリー
役人でなくても為になる要素が少なからずあったので、かいつまんでご紹介したいと思います。
ワイルドカード記号のアスタリスク(*)を利用します。 =COUNTIF(A:A,"*東京*")
特に［IFS関数］は、［IF関数］の入れ子（ネスト）が分かりづらいと感じている方にはお勧めです。
UNIQUE関数に「ブランクセルは除外する」みたいなオプションがあればいいのに。どうしても空欄を除いたユニークデータが欲しいのなら、たとえば次のようにFILTER関数でブランクセルを取り除いてからUNIQUE関数に渡します。
=UNIQUE(FILTER(B5:B16,B5:B16<>""))
［集計方法］に従って、さまざまな集計値を求めます。指定した［参照］の範囲内に、ほかのSUBTOTAL関数を使って集計した小計が含まれている場合は、自動的にそれらの小計を除外して集計値を求めます。
NETWORKDAYS関数を使うと稼働日（土日+休日を除く）を出すことができます。
使用例1:プロジェクトごとの金土＋会社カレンダーの休日を除いた稼働日数を調べる
XLOOKUP関数の書式 ＝XLOOKUP（検索値, 検索範囲, 戻り範囲, 見つからない場合, 一致モード, 検索モード） この数式を確定すると、右セルにも自動的に単価が表示される。この機能は「スピル」といい、複数の項目がある場合、その項目を含む配列を返す。
1. 引数の指定方法が分かりやすい 2. 検索範囲が左端でなくてもOK 3. 複数のセルに計算結果を表示できる ［一致モード］と［検索モード］の種類 2 ...... ワイルドカード文字と一致
原因2．検索範囲の左端に検索値が見つからない
2番目の引数は「範囲」です。この引数は必須です。参照する表のセル範囲を指定します。 指定したセル範囲の左端の列（左から1列目）には、検索値が含まれている必要があります。またセル範囲内に検索する結果の値も含める必要があります。
INDEX関数とMATCH関数を組み合わせて実現する
MATCH関数で検索値の行を取得 ⇒取得した行に対して、INDEX関数で列を指定して値を取得する
この問題は、VLOOKUP関数の参照先としてテーブルを指定する「構造化参照」で解決できます
5つ目のエラーは、戻り値が”#N/A”というエラー値です。
4）取得範囲を関数IFで記述する方法 続いて紹介するのは、取得範囲を「関数IF」で記述する方法だ。この方法が最もトラブルが少なく、多くの場面で利用できるテクニックになると考えられる
このように可変リストのアドレスを指定するときこそ、OFFSET関数の出番なのです。
こちらの動画では、CELL関数でフォルダパス・ブック名・シート名を取得する方法と、その際に注意する点について解説しています。
IF関数を使った複雑な式を整理、すっきりさせてメンテもしやすく
AGGREGATE関数は、集計方法と集計オプションを指定することができ、集計方法に合計、集計オプションにエラーを無視を指定することで、SUM関数やAVERAGE関数の代わりに数値の合計を求めることができます。
AGGREGATE関数は、SUBTOTAL関数の機能を強化した関数です。
関数FVの書式 =FV(利率, 支払回数, 定期支払額, [現在価値], [支払期日])
Officeアプリの既定の保存先をパソコン内にするには、ExcelやWordなどのオプション画面から変更する。Excelで保存先を変更すれば、Wordなどの別のアプリの保存先も変更される
「C3#」のような動的配列を参照する表現は活用範囲が広く、スピルの結果のセル範囲が広がれば、自動的に参照する範囲も広がります。つまり、範囲が可変のデータにも対応可能なのです。
グラフを選択したら「ページレイアウト」タブの「ページ設定」の「→」をクリックして「ページ設定」ダイアログを開く。そうしたら、「グラフ」タブを選び、「白黒印刷」にチェックを入れよう（図4上）。そのあと、ダイアログの下部にある「印刷プレビュー」を押すと、ほら、パイはグレーではなく黒や白、異なる網掛けに置き換わったから違いが一目瞭然になる（図4下）。
Excelには、表形式のデータを扱うための「テーブル」という機能があり、これを使うことでセルの書式設定や色分けなどを自動化できる。
ベタ打ちは青、計算式は黒の数字に計算結果が表示されるセルの中に、ベタ打ち（直接入力）の数字が入るのは、混乱のもと。その2つの数字は色分けを。どれが変更可能な数字か、すぐわかる効果もある。
「名前の定義」を利用すると、関数の引数が分かりやすくなる。
単純な表のコピーと違い、「元表」とデータがリンクしており、作業中に「元表」が変更になっても、テーブル側をゼロから作り直す必要がないというメリットがある。
元のファイルの値が変わることに対応したいなとか、あとからファイルが増えるかもしれないし、なんていうときにも使えるように、先を見据えて使えるテーブルを、機能を使って作ってみます。
「デザイン」タブをクリックし、「集計行」にチェックを入れます。集計行が表の下端に挿入されます。表によっては合計が自動で計算されます。
［作成］ボタンをクリックすると、工程ごとにB列の開始日とC列の終了日に応じて、矢印線が自動で引かれます。
エクセルの集計方法には、以下の4つがあります。 - 集計：ワンクリックでかんたんな集計ができる機能 - 関数：指定した範囲の計算を自動的にしてくれる数式 - ピボットテーブル：必要なデータだけ抽出して集計、グラフなどもかんたんに作成 - 統合：複数のシートをまとめて計算する機能
表がテーブルになっていると、［小計］の挿入はできないので、テーブルを解除して通常の表にしましょう。
先ほどの操作で、4～6月分のデータを追加したのに元の数式のままなので、エラーインジケーターが表示されているのです。
このようにセルの分割を容易にできる。その快適度はひょっとすると「区切り位置」を利用するよりも上かもしれない
日付では、月ごとの月初めや月末の日付を連続して作成したいことがよくある。この場合、次の方法を取ればよい。
「グラフの書式」をコピペする
Excelには、グラフに「近似曲線」を追加できる機能が用意されている。この機能を使って「移動平均線」を描画することも可能だ。
「円グラフ」によく似たグラフとして、「ドーナツ」と呼ばれるグラフも用意されている。
左端・右端の「期首」「期末」の値は、数字の（増加、減少でなく）合計の値であるので、それぞれを「合計として設定」します。 まずは、グラフの左端の「期首」のデータをダブルクリックして（該当のデータだけが選択されていることを確認して）、「データの書式設定」の「系列のオプション」にある「合計として設定」にチェックを入れます。グラフの右端の「期末」のデータについても同様です。
系列のオプションをクリック
3. 「Ctrl＋1」でセルの書式設定を開く。
始点のセルを選択後、［Shift］キーと［Ctrl］キーを押したまま、矢印キーを押す
[Alt] → [A] → [H] [Alt] → [A] → [j]
行全体を選択した状態で"Ctrl + Shift + +（プラス）"を押下すると1行挿入されます。
SUM関数を入力したいセルを選択して、［Shift］＋［Alt］＋［=］キーを押します
[Ctrl]＋ [ を同時に押します。
仕事で「エクセルの案件表を別のエクセルにクエリで表示し、それをフィルター関数でシート毎に担当者別の表にし、VBAで条件付き書式を設定して罫線などで修飾し、定期的に遠隔地のプリンタから出力する」という課題をこなしたので、方法を記録しておきます。
シートの番号を指定してシート名を取得できます。これを使ってシート一覧を表示できます。
個人用マクロブックは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で表示されます。なお、日本語も問題ありません。コマンドオプションとして、無視するセルの範囲や、ワークシートなどを指定できます。
もしA1セルのデータをテキストボックスに表示したいのなら、数式バーに =A1 と入力する。
ssconvert unoconv catdoc
姓と名の間に空白文字が入力されていれば、［区切り位置指定ウィザード］を使って姓と名を分割できます。
このXMLファイルを「メモ帳」アプリなどで開き、「sheetProtection」を検索、「<sheetProtection …… />」の部分を削除する。これを上書き保存して、［worksheets］フォルダに戻せばよい。
