chevron_left

メインカテゴリーを選択しなおす

cancel
shikumika’s diary https://shikumika.org/

日々の事務作業で手間なことを簡単にできる仕組み(自動化、方法など)、困ったことの解決方法を調べた備忘録

ycloud
フォロー
住所
未設定
出身
未設定
ブログ村参加

2022/09/06

1件〜100件

  • PDFをExcelにインポート、繰り返し業務ならPower Query(パワークエリ)が便利

    毎月、同じレイアウトのPDFファイルをExcelに転記するなど、「繰り返し、PDFからExcelへのデータ抽出業務」がある場合、Power Query(パワークエリ)が便利だったので備忘のメモ。具体的には、 行政が毎月更新、公開しているPDF形式(透明テキストPDFの状態)の一覧データがある。 分析のためにPDFファイルから必要箇所を毎月データ抽出し、Excelで編集する。という業務で、Power Queryを利用。

  • 【Excel】ピボットテーブルの更新時に列幅の自動調整が不便なので、既定の設定を変更

    Excelのピボットテーブルは、既定のレイアウトオプションを変更できます。 ピボットテーブルはデータを更新すると、自動的に列幅が調整されて便利な機能かもしれませんが、せっかくレイアウト調整しても勝手に列幅が変わってしまいます。 便利な機能かもしれませんが、私にとっては不便なケースが多いです。 自分の作業用途にあわせて、ピボットテーブルの既定のレイアウトオプションを変更したので備忘のメモ。 設定はインポートしたり、リセットしたりも簡単にできます。 既定の変更内容と目的 1. 「更新時に列幅を自動調整する」のチェックボックスをオフ 列幅の自動調整は、データ更新の都度変わるのでレイアウト変更が手間。…

  • Excelの印刷時、セル内の文字が「ずれる」「末尾が途切れる」という問題の解消

    Excelで、セル内に長文の文字列があるデータを印刷すると、画面上では収まっていたのに印刷物では「ずれる」「末尾が途切れる」ということがあります。 原因の一つである「標準フォント」の設定を知らないと、余分な手修正が発生するので、その解決方法の備忘録。 セル内の長文を複数行の表示とするために「折り返して全体を表示する」という設定でと良く言われます。 しかし、行の高さの自動調整をしていても、印刷結果が思うようにならない時があります。 例えば、次のように最後の行が全て表示されていないような状態となり、画面通りの印刷とならないなど。 その解決方法として「ページレイアウトの表示」にして自動調整すれば良い…

  • Excelで特定の文字より後ろにある文字列を抽出するユーザー定義関数サンプル

    Excelで少し複雑な文字列抽出をしたく、Excelのユーザー定義関数を作成してみたので備忘メモ。 簡単なものであれば、LEFT関数、MID関数、FIND関数で抽出できると思いますが、少し複雑で、大量なデータの場合は正規表現が便利です。 なお、ExcelやVBAの標準機能では正規表現が使用できませんので、次のようなユーザー定義関数を作る必要があります。 shikumika.org ただし、上記のVBScript.RegExpの正規表現では、 特定の文字より前(もしくは後ろ)にある数字の抽出 特定の文字と文字の間にある数字の抽出 といったことが簡単にはできなさそうです。 前述のユーザー定義関数サ…

  • Excelで正規表現を使うためのユーザー定義関数サンプル

    ExcelやVBAの標準機能では正規表現が使用できません。VBScript.RegExpを使えば可能とのことで、Excelのユーザー定義関数にした処理をしてみたので備忘メモ。 EXCELのワークシート関数としても使用できます。 そもそもの背景は、Power Query(パワークエリ)で正規表現を利用したかった。調べてみると、Web.Pageの機能を使ってJavaScriptの正規表現でする方法がありました。「なるほど、と思う使い方でとても参考になったのですが、私の設定方法が悪いのか、処理速度が遅かったのでVBAですることにしました。 アウトプットイメージ 今回実施したかったことは、次のような文…

  • VBAでSubとFunctionの違い、使い分けに悩んだ場合の対応

    VBAで「Subプロシージャ」と「Functionプロシージャ」の違い、使い分けの説明に悩んだので、考え方のメモ。 個人的な結論として、悩んだときは全て「Functionプロシージャ」で良いになりました。 一般論としては、「Functionプロシージャ」と「Subプロシージャ」の主な違いは、次のとおりです。 項目 Function Sub 「マクロの表示」から実行できるか (単独で実行できるか) × 〇 戻り値があるか 〇 × ユーザー定義関数として、EXCELワークシートから使用できるか 〇 × 「マクロの表示」から実行できるかというのは、Excelの画面から「マクロの表示」で、次にマクロが…

  • Power QueryでCOUNTIF関数のように各行の値の重複を数える方法(List編)

    前回、Power QueryでCOUNTIF関数のように各行の値の重複を数える方法として、グループ化の機能を使用した方法を紹介しました。今回はListを使った方法です。 ただし、前提として、今回の方法は使用できる場面が少ない印象です。 大量データには不向きなので、その場合は前回の「グループ化」を使う方法が最適と思います。 shikumika.org 今回の方法だと数千件程度まででないと、処理時間がとても長くなる印象です。 (私の環境で、3000件のサンプルデータで試したところ、グループ化なら1秒程度の計算時間が、今回の方法で30秒ほどかかり、処理時間が大きく異なりました) アウトプットイメージ…

  • Power QueryでExcelのCOUNTIF関数のように、各行の値の重複を数える方法(グループ化編)

    Power Query(パワークエリ)に、Excelの数式でいうCOUNTIF関数がありません。 名簿の名寄せなど、各行の値の重複を数えたいときに悩んだので解決方法のメモ。 良さそうな方法は次の2つ 「グループ化」を使う方法 List.Count関数、List.FindText関数を組み合わせて使う方法 ※ ただし、こちらは大量データには不向きな印象 今回は、「グループ化」を使う方法について説明します。 アウトプットイメージ 「名前」という列にある人と同じ名前の件数を「カウント」に表示するイメージ 作成手順 「テーブルまたは範囲から」で、クエリの作成 次のような、顧客ID、名前、都道府県、年代…

  • Microsoft 365 Familyの商用利用、 別居の家族の利用に関する規約等

    マイクロソフトのオフィスの費用を安くしたいと考えていたところ、「Microsoft 365 Family」が、2022年7 月19 日より日本国内向けに提供開始されました。 家族用のお得なサブスクリプションで、ExcelやWordなどのOffice アプリ、1TB のクラウド ストレージの OneDriveなどのサービスを、最大 6 人まで利用可能とのこと。 家庭で利用するOfficeのライセンス費用を安くできるのか、商用利用や同居でない家族での利用について規約等を調べてみました。 商用利用について 「Microsoft 365 Family」を仕事用で使用してよいかについては、以下の規約をみ…

  • Power Query(パワークエリ)でフォルダ指定を相対パスにする方法

    Power Query(パワークエリ)のデータソース場所の指定は、標準機能では絶対パスでの指定しかできないようです。 そうなると、データソースとなるファイルの場所を変更した場合、都度変更の手間が発生します。 社外など別環境の人に渡したり、毎月などデータソースの場所を都度変更したい場合など、どのようにデータソースを変更するかで悩みます。 特に、Power Queryに慣れていない人に渡すには、データソースの変更は難易度が若干高い。 その解決方法の一つとして、データソースを相対パスにする方法があります。 なお、相対パスでのフォルダ指定は、セキュリティ面でのリスクも検討必要らしいですが、使い方を限定…

  • Excelで「値のみ貼り付けのショートカットキー」の代用(Windows)

    Excelで値のみ貼り付けの作業を効率化したいが、なぜかWindowsでは「値のみ貼り付けするショートカットキーがない」とのこと。 その代用(Windows)として、個人的には、次の方法が最適と思う。 コピー後、 Ctrl+V Ctrl V の順で、キーをおす。 Windowsの場合で、「Ctrl+Alt+V」キーを含めた方法など、他の方法もあるが、自分の指ではどうも押しづらくて苦手。 具体的な手順 コピーする(Ctrl+C) 例えば、次のような書式情報を含んだ表。 Ctrl+Vで、貼り付けする 書式情報も含んで、一旦貼り付けされる。 すると、[貼り付けオプション]ボタンが表示される。 「Ct…

  • ピボットテーブルの並び替えが思いどおりにならない時のチェックポイント

    Excelピボットテーブルの並び替えが思うようにいかず、出来ないものと勘違いしていたので、その解決方法のメモ。 並び替えができないと思っていたのは、 行フィールドに2つ以上の項目あり 列フィールドにある総計以外 といったパターン。この場合も、降順など並び替えがきちんとできた。単に操作方法が誤っていた。 (Excelをよく利用している人でも、勘違いしている人が多いのでは?) 手順 行フィールドに2つ以上の項目をいれた場合、値の昇順などが思うようにできずに困っていた。 例えば、このようなピボットで、並び替えをしたい値(この場合は「$C$7」)をクリック後、値順に並び替えしようとしたとき、思うように…

  • Excelピボットテーブルでの一覧表は、「表形式」「小計を表示しない」の設定で文字列をそのまま表示した一覧になる

    Excelピボットテーブルで、次のように属性として文字列をそのまま表示した一覧を作りたいときのメモ。「表形式」「小計を表示しない」の設定方法を知っておくと、一覧表が作成しやすい。 ピボットテーブルのアウトプットイメージ(文字列を行フィールドで一覧表示) ピボットテーブルの表示方法の設定を変えることで、このような表示が可能。なお、この方法は簡易的な方法で、レイアウトの自由度が低い。 慣れていないと少し手間だが「レイアウトの自由度等が高い方法」で文字列を表示したいという場合は次の方法が最適。 shikumika.org 作成手順 次の購買履歴のような元データを事例に説明する。 購入の日付、顧客ID…

  • PDFの表をExcelに貼り付けして編集する方法

    PDFにある表(透明テキストつき)を編集できるようにエクセルに貼り付けする方法についてのメモ。 Excelの機能の1つであるPowerQueryでうまく取り込めました。 WordでPDFを開き、Excelに貼付する方法 adobeのページで変換 との違いについては、ページ下部に記載。 なお、PowerQuery は、最近のExcelに標準で含まれている機能。PowerQuery が含まれるバージョンの詳細はこちら。 PDF化された次のような表をExcelに取り込みたい 前提:PDF上でテキストは選択できる(透明テキストPDFの状態) PDFにある表 実施手順 ExcelのPowerQueryで…

  • Accessで最適化しようとしたら「既に使用されているので、使用できませんでした」のエラー対応

    いつも使用しているAccessのファイルで、先週から突如、最適化できなくなったので対応方法の調査。 結果、セキュリティソフト(McAfee マカフィー)を一時停止したら最適化できるようになった。 容量が増えて困っていたので、備忘のメモ。 使用しているAccessは、Microsoft® Access® for Microsoft 365 MSO 。 最適化をしようとすると、次のように「・・・.accdbは既に使用されているので、使用できませんでした。」と表示されて、最適化ができなかった。 Access最適化時、既に使用されているので使用できませんでしたのエラー 原因不明だが、今回は、 セキュリ…

  • Excelで都道府県の並び替えをする方法(北海道から沖縄の順番通り)

    Excelの並び替えで、北海道から沖縄への順で並び替えが出来ないと困った時の対応方法のメモ。 結局のところ、最もシンプルな方法は、並び替えのユーザー設定リストに以下を登録する。 <そのまま、コピー、貼り付け用> 北海道青森県岩手県宮城県秋田県山形県福島県茨城県栃木県群馬県埼玉県千葉県東京都神奈川県新潟県富山県石川県福井県山梨県長野県岐阜県静岡県愛知県三重県滋賀県京都府大阪府兵庫県奈良県和歌山県鳥取県島根県岡山県広島県山口県徳島県香川県愛媛県高知県福岡県佐賀県長崎県熊本県大分県宮崎県鹿児島県沖縄県 support.microsoft.com 他の方法との比較 VLOOKUP等で、北海道は1、青森…

  • Excelピボットテーブルの値フィールドに、文字列をそのまま表示する方法

    ピボットテーブルにあるPower Pivot なら次のようにピボットテーブルの値に、文字列が表示できました。

ブログリーダー」を活用して、ycloudさんをフォローしませんか?

ハンドル名
ycloudさん
ブログタイトル
shikumika’s diary
フォロー
shikumika’s diary

にほんブログ村 カテゴリー一覧

商用

フォローできる上限に達しました。

新規登録/ログインすることで
フォロー上限を増やすことができます。

フォローしました

リーダーで読む

フォロー