Power Query（パワークエリ）で、空白の列を追加したい時があります。 例えば、他のExcelの表に貼り付けするなどの都合で、列の順序や数合わせののために項目名はあるが、データは空白にしたいケースなど。 初歩的なことですが、Power Queryを初めて使用したとき、その方法が分からずに悩みました。 空白の列を追加する方法は、次の「新しい列を追加して同じ値を入力」の方法のようにカスタム列で、”null”を入れるが一番簡単な印象。 null は、何もないという意味。 shikumika.org 簡単な手順ですが、参考にカスタム列の画面イメージは次のとおり。 カスタム列の式で、小文字で"nu…
【Excel】ピボットテーブルで、集計の軸（行や列）に影響しないデータ集計、表示の方法（2）
Excelのピボットテーブルで、集計の軸（行や列）に影響しないデータ集計、表示の基本は次のとおり。 shikumika.org 今回は、ピボットテーブルの軸で、一部はそのまま集計の軸（行や列）に利用するが、その他は利用しないなど、少し応用の方法。 実際に集計したい場面にならないとイメージが持ちづらいが、例えば次のアウトプットの結果（ピボットテーブル）で、「カウント／年代」は通常の集計。そのうち、ALL年代の場所は、「東京都」としての全体件数が表示されている状態。 要は、上記で紹介のデータ集計、表示の基本は、集計の軸（行や列）の絞り込み条件をすべて無視する方法で、今回の方法は、絞り込み条件の一部…
【Excel】ピボットテーブルで、集計の軸（行や列）に影響しないデータ集計、表示の方法
Excelのピボットテーブルで、集計の軸（行や列）に影響しないデータ集計、表示をしたいと思うことはありませんか？ 例えば、次のようにピボットテーブルの表内で、全データの件数と年代別の件数を集計したいなど。 通常のピボットテーブルでは、行や列に設定した軸に基づいた集計しかできません。 そのため、全体件数と年代別件数を並べた表示にするには、レイアウトの制限を大きく受けてしまい、そのままでは使いづらい表ができてしまいます。 ピボットテーブルで、集計の軸（行や列）に影響をうけない集計方法 その結果、最終アウトプットに近いピボットテーブルを作成した後、最後のひと手間でコピー、別表に貼り付けなどの作業が発…
Accessのマクロは、VBAのSub、関数の日本語名も使えない？
久しぶりにAccessを使ったデータ整理。 最近は、 Power Queryで近いことができるようになっていますが、リレーションを維持して入力や編集をする場合はAccessの方が、まだ便利です。 処理の一部を自動化したいと思い、VBAを使用しない人のために、全体の処理ステップをVBAではなくマクロで作成。一部、VBAで処理していた部分をマクロから呼び出して、そのまま利用したかったがマクロからSubが呼び出せずに苦戦。 Access マクロの設定画面 SubをFunctionにしないと呼び出せない仕様みたい。今更ですが。 また、プロシージャ名や、Functionの関数名も日本語だと呼び出せないみ…
Power Query（パワークエリ）を初めて利用するとき、色々な項目があって悩みます。しかし、列を追加する基本的な操作方法は、「列の追加」にある次の赤枠内を知っていれば十分に使える印象。 PowerQuery（パワークエリ）の基本的な操作方法 「列を追加」のポイント 上図の赤枠内をまず理解しておけば、列追加については概ね対応できそう。 個人的には、Excelの数式を利用しているなら、次の順番で理解すると業務で活用できるレベルの習熟は容易な印象。 「列を追加」のポイント 「重複する列」は、列のコピー 「カスタム列」の基本操作 「条件列」の基本操作 「例からの列」の基本操作 補足 そして、列の追…
【Power Query】数式がわからなくても、自動作成してくれる「例から列を追加」
Power Query（パワークエリ）の基本的な操作方法の備忘録。数式の書き方がわからない、数式のサンプルがみたいという場合は、「例からの列」が便利です。 求めるアウトプットのイメージをPower Queryに入力するだけで、必要な数式を自動作成してくれる便利な機能です。 例えば、条件等に応じた値を表示したい、日付データから月度のみ抽出したいなども、とりあえず「例からの列」を使って自動作成し、必要に応じて数式を修正するという使い方ができます。 PowerQuery（パワークエリ 数式の書き方 使い方 使い方 「列の追加」-「例から列」をクリック サンプル値を入力してみる（自分が欲しい結果） サ…
【Power Query】IF文でand条件を使った値の表示
Power Query（パワークエリ）の基本的な操作方法の備忘録。 メニューにある「条件列」を使っての「条件に応じた値表示」では、andやorの条件指定ができないので、カスタム列を使った基本的な設定の手順です。 メニューにある「カスタム列」を使って実施します。 PowerQuery 手順 「列の追加」-「カスタム列」をクリック 新しい列名と、条件を設定 ① 新しい列名は、設定したい列名を入力 ② 条件は、if A and B then else といった構文で記載。列名の指定は、「使用できる列」から挿入するか、”[]”の括弧を使って記入。 次の例は、「住所」という列が”岩手県”と同じ かつ 「…
Power Query（パワークエリ）の基本的な操作方法の備忘録。 新しい列を追加して同じ値が入力ができたら、次は条件に応じて値を変更したい時の手順です。 基本操作は、メニューにある「条件列」を使って実施します。 PowerQuery 手順 「列の追加」-「条件列」をクリック 新しい列名と、条件を設定 ① 新しい列名は、設定したい列名を入力 ② 条件は、プルダウンをクリックすると、設定できる項目が表示されます。 値は、文字を直接入力したり、比較対象となる列を入力することもできます。 次の例は、「住所」という列が、”岩手県”と同じだったら、”対象”と表示し、その他は何も表示しない設定。 OKで確…
Power Query（パワークエリ）でデータ整理時に、新しい列を追加して同じ値を入力したい時のメモ。 例えば、データを分類するために、同じ値を入力しておきたい時の方法です。利用していないと初歩的な操作方法を忘れてしまうので備忘録。 powerquery 手順 「列の追加」-「カスタム列」をクリック 新しい列名と固定値を設定 ① 新しい列名は、設定したい列名を入力 ②カスタム列の式に、固定値をダブルクオンテーションで囲み入力 OKで確定すると、次のように、新しい列と、値が入力される。 以上、Power Query（パワークエリ）でデータ整理時に、新しい列を追加して同じ値を入力したい時のメモでし…
Power Query（パワークエリ）を利用するときのヒントとテクニックについての備忘録。 後日のメンテナンスや、作業の効率化を考慮すると、次をしておくと良さそうです。 列の「移動」は、処理の最後の方にする。 ステップのプロパティで、「名前の変更」と「説明」を利用する データが多い時は、フィルターや不要な列削除で、データ量を減らす 参考 列の「移動」は、処理の最後の方にする。 並び替えをすると、全ての列名と順序のステップができる。 しかし、このステップの前で列を削除しても、このステップの列名は自動更新されないため、エラー修正が必要となる。必要な列が確定してから、列の「移動」をした方が効率的です…
Wordで、ファイル内の画像サイズを一括変更するVBAのサンプル
Wordファイルで、ファイル内にある数十個の図形サイズを一括で更新するVBA。 横幅サイズは、400ptに設定したサンプルです。 Sub 画像サイズの一括更新() Dim 画像 As Object For Each 画像 In ActiveDocument.InlineShapes If 画像.Type = wdInlineShapePicture Then '図形のみ抽出 画像.LockAspectRatio = msoTrue '高さと幅の比率を固定 画像.Width = 400 '任意に設定 End If Next 画像 End Sub 補足 図形の種類は、以下のとおり。 learn.m…
Excelで直前の操作を繰り返す「F4」。文字入力後も繰り返し可能。
一度設定したセルの書式を他のセルにも繰り返し設定したい時など、直前の操作を繰り返す方法として「F4キー」や「CTRL ＋ Y」があります。この直前の操作の繰り返しは、途中に文字入力後も繰り返し可能です。
Excelでセル内改行を一括で削除する方法。SUBSTITUTE関数や「Ctrl＋J」等。
Excelのデータ整理時、改行を一括で削除したい時の備忘メモ。「Ctrl＋J」か、SUBSTITUTE関数、もしくはCLEAN関数を使って置換可能。「○回目に出現する改行のみ削除したい」という場合は、SUBSTITUTE関数であれば指定可能。
Excelでセル内改行は「Alt＋Enter」。「Ctrl＋J」やExcel関数CHARの知識があると便利
Excelでセル内改行は「Alt＋Enter」を押します。スペースでレイアウト調整すると、列幅変更時や文章変更時に再調整が必要で手間です。改行に関する知識で、さらに「Ctrl＋J」やExcel関数を使用してCHAR(10)を使った改行の活用方法も知っておくと便利です。
Excelで電話番号データからハイフン等の数字以外を消すユーザー定義関数サンプル
Excelで、大量の電話番号リストからハイフン等の数字以外を効率的に削除するため、正規表現を用いたユーザー定義関数のサンプルを作成した備忘メモ。 作成の背景は顧客リストの整理。
PowerPointで蛍光ペン風アンダーラインの作成3パターン
PowerPointで、蛍光ペン風アンダーライン。販促用POPをパソコンで作成したく「太めの蛍光ペン風」にする方法についてまとめ。作成方法としては、大きく3種類でそれぞれ次のメリットやデメリットがあります。
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や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の機能の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さんをフォローしませんか？
Power Automate Desktopには「Webページのコンテンツを待機」アクションがあります。 このアクションは、特定のテキストまたはWebページの要素がWebページに表示されるまで（または非表示になるまで）フローを中断します。 他に同様な機能として、Power Automate Desktopには、 アクションの詳細設定にある「ページが読み込まれるまで待機します」 というオプションがあります。 詳細は、以下のページ内で「2．Webフォーム入力などの場合はページが読み込まれるまで待機の有効化」をご覧ください。 shikumika.org このオプションの有効化で必要なフローを作成でき…
ExcelのVLOOKUP関数とXLOOKUP関数で、複数条件による検索方法の事例です。スピルが利用できるExcel（Microsoft365など）では、シンプルな数式で複数条件による検索が可能です。 内容： 複数条件で検索する数式例 数式の説明 複数条件で検索する場合、検索条件にするセルを連結する 補足説明 スピルの説明 ”複数列”を検索範囲にする方法 （参考）XLOOKUP関数の特徴と活用事例 参考： 動的配列数式とスピル配列の動作 - Microsoft サポート 複数条件で検索する数式例 下図は検索対象の表（$B$3:$D$8）で「B列に合致、かつ、C列に合致」の複数条件で検索し、一致…
ExcelのVLOOKUP関数とXLOOKUP関数で複数列を検索範囲にして値を抽出する方法の事例です。1列の検索範囲で複数列の値を抽出したい場合は【Excel】VLOOKUPとXLOOKUPで複数列の値を抽出する方法の比較をご覧ください。 内容： 複数列を検索範囲にした数式例 VSTACKとHSTACKの計算結果例 その他、VSTACKとHSTACKを活用した事例 （参考）XLOOKUP関数の特徴と活用事例 HSTACK関数は、Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web で利用可能です。 詳…
ExcelのXLOOKUP関数はVLOOKUP関数の改良版で、検索列と結果列の選択がしやすいなど、とても便利な関数です。 活用の考え方は、基本はXLOOKUP関数を使用し、VLOOKUP関数は何かしらの目的があるときだけ利用が良いと思います。なお、VLOOKUP関数が必要な場面としては、XLOOKUP関数が使用できない人とのファイル共有や、テーブル内での利用などです。 VLOOKUP関数と比較した特徴と、活用事例をまとめました。 なお、XLOOKUP関数は、Excel 2016 および Excel 2019 では使用できず、新しいバージョンの Excelから使用可能です。 内容： XLOOKU…
ExcelのXLOOKUP関数は、抽出したい列数が多い場合（連続している場合）にVLOOKUP関数より便利です。抽出したい複数列が連続していない場合は、目的やデータの状況によりますがHSTACK関数と組み合わせる方法も有効です。 内容： VLOOKUPとXLOOKUPで複数列を抽出する方法の基本 離れた位置の複数列を抽出する方法 （参考）VLOOKUP関数の範囲にもHSTACK関数を使用可能 HSTACK関数は、Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web で利用可能です。 詳細：HSTACK…
ExcelのVLOOKUP関数と、その改良版であるXLOOKUP関数で複数列の値を抽出する方法の比較です。 抽出したい列数が多い場合（連続している場合）はXLOOKUP関数が便利です。なお、抽出したい複数列が連続していない場合は、目的やデータの状況によります。 内容： 連続した複数列の値を抽出する方法 VLOOKUP関数と配列数式の組み合わせ（非推奨） 連続しない複数列の値を抽出する方法 参考情報 複数列の抽出スピードは、XLOOKUP関数が遅い時あり XLOOKUP関数は、Excel 2016 および Excel 2019 では使用できず、新しいバージョンの Excelから使用可能です。 連…
ExcelのVLOOKUP関数やXLOOKUP関数で、単一の表（セル範囲）ではなく、複数シートのセル範囲から値を抽出したい場合はVSTACK関数が便利です。 VSTACK関数は複数のシートにある表を一つの表に結合できる関数です。 内容： アウトプットイメージ VSTACK関数の基本事例 数式の補足説明 （参考）Power Queryの活用 なお、VSTACK関数は、Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the webで利用可能です。 詳細：VSTACK 関数 - Microsoft サポート アウトプ…
ExcelのVLOOKUP関数で、右側から左側の値を取得することは不可能ですが、VLOOKUP関数の改良版であるXLOOKUP関数なら可能です。 XLOOKUP関数は、Excel 2016 および Excel 2019 では使用できず、新しいバージョンの Excelから使用可能です。 内容： 右側から左側の値を取得する方法 XLOOKUP関数とVLOOKUP関数の補足 右側から左側の値を取得する方法 VLOOKUP関数は、表の左端列を検索して右側の値は取得可能ですが、右側から左側の値は取得できません。 そのため、従来であればINDEX関数とMATCH関数を組み合わせる方法などで対応が必要でした…
Excelには、データの整理や抽出、集計業務を効率化させる「テーブル」機能があります。 便利な機能ですが不慣れな人も多い印象です。 以下では、Excelでテーブルを作成する方法と解除する方法、そしてVLOOKUP関数との組み合わせでの活用例を紹介します。 内容： Excelのテーブルとは テーブルの作成方法 数式でテーブルの活用方法 （1）数式での指定例 （2）VLOOKUP関数での活用例 テーブルにデータ追加・削除 テーブルの解除方法 （1）テーブル解除の基本操作 （2）テーブルの解除による数式への影響 （3）縞模様などの「テーブルスタイル」は「セルのスタイル」で標準に戻す Excelのテー…
ExcelのSUM関数やVLOOKUP関数などで「セル範囲を参照する数式」を作成した後、データ追加によりセル範囲を修正する作業は非効率です。 修正が漏れると、集計対象に含まれないなどのミスも発生します。 数式作成後のデータ追加に備えて「数式のセル範囲修正」を省力化するため、 セル範囲を「列」で指定 セル範囲を「テーブル」で指定 を活用できないか検討します。 内容： セル範囲を「列」で指定 VLOOKUP関数での事例 セル範囲を「列」で指定する場合の注意点 数式の仕様の一例 SUM関数は、範囲 (セルのグループ) の”数値”を合計する関数 AVERAGE 関数は、文字列や空白は無視されるが、値 …
ExcelのVLOOKUP関数で#REF!エラーが表示されることがあります。 この原因は、 VLOOKUP 関数の構文=VLOOKUP(検索値, 範囲, 列番号, [検索の型]) で、数式の作成時に「範囲」を超えて列番号を指定したことによるエラーが多いです。 なお、数式の作成後、検査値に指定していたセルを削除した場合も、次のように数式の検査値箇所が#REF!となり、エラーになります。 例） =VLOOKUP(#REF!,$B$3:$C$8,2,FALSE) と数式内に#REF!がある ただし、このケースは数式作成後に削除しており、原因が比較的わかりやすいと思います。 今回は数式の作成時に発生し…
ExcelのVLOOKUP関数で検索対象の表に重複データがある場合、先に一致した値の行を取得します。そのため、意図しない計算結果になることがあります。 内容： 検索対象の表に重複データがある場合の事例 VLOOKUP関数は先に一致のみだが、XLOOKUP関数は最後に一致した値も取得可能 検索対象の表に重複データがある場合の事例 具体的な事例は下図のとおりです。 セル範囲$B$3:$C$8（下図でマスタ表に該当）の左端列（B列）は重複した値があります。取得対象の値（C列）の値は全て違います。 このような場合、VLOOKUP関数の計算結果は先に一致した値の行を取得した結果となります（下図のセルF4…
ExcelのVLOOKUP関数は、表から特定の値を検索し、一致した値と同じ行にある値を取得する際にとても便利です。しかし、使い方を間違えると、思わぬ結果を引き起こすことがあるので、その基本的な使い方と計算ミスを防ぐポイントです。 内容： VLOOKUP関数の基本的な使い方 ミス防止のポイント 計算ミスを防ぐポイント 範囲を「絶対参照」で指定する理由 検索の型を「FALSE」で指定する理由 VLOOKUP関数のエラー対応例 #N/Aのエラーの場合 VLOOKUP関数の基本的な使い方 VLOOKUP関数は、次の構文です。 =VLOOKUP(検索値, 範囲, 列番号, [検索の型])VLOOKUP …
Power Automate Desktopで、Webページにファイルをアップロードする自動化事例です。kintoneを題材に、「添付ファイル」フィールドに指定のExcelファイルを保存します。 内容： 事例の前提 アウトプットイメージ 設定手順 1．Microsoft Edgeを起動、入力ページの表示 2．「添付ファイル」フィールドの”参照”をクリック 3．ファイル選択画面でファイル名を指定 （1）「ウィンドウ内のテキストフィールドに入力する」アクションの追加 window[Class="#32770"]の補足 （2）「ウィンドウ内のボタンを押す」アクションの追加 4．ファイル選択をWebペ…
Power Automate Desktopで、Webページの必要なHTML要素が読み込まれる前に処理が実行されると、エラーが発生したり、期待した動作にならないことがあります。 このような場合、以下の対応方法を検討します。 待機アクション「Wait」の追加指定した秒数だけフローの実行を中断する。 Webフォーム入力などの場合、各アクションの詳細設定で「ページが読み込まれるまで待機します」を有効化デフォルトは有効化のようだが確認する。 これにより、自動化対象が処理できる状態になってから実行することができ、エラー等の発生を防ぐことが可能です。 内容： 1．待機アクション「Wait」の追加 2．We…
Power Automate Desktopで、Webページにデータ登録等の操作を自動化する事例のまとめです。kintoneでの事例を中心にまとめています。 kintoneはAPIを利用することができますが、ライセンスやユーザー権限等でAPIが利用できない環境にある場合の自動化事例です。 内容： Webページにログインの自動化 Webページに入力・保存 WebページのUI要素確認と設定テクニック エラー対応例 Excelでのループ処理の事例 Webページにログインの自動化 仕事では、ログインが必要なページでの作業が多くあります。ログインの事例と、ログイン状態を判定する事例です。 ・Webページ…
Power Automate DesktopでWeb操作を自動化する際、「Webページでドロップダウンリストの値を設定します」アクションが期待通りに動作しないことがあります。 Webサービス（kintone）の「ドロップダウン」は、その一例です。 これは、ドロップダウンの選択肢の要素が適切に取得できないためで、「Webページのリンクをクリック」アクションに変更することで、期待した動作が実現できました。 内容： アウトプットイメージ 設定詳細 1．「ドロップダウンフィールドをクリック」で使用するUI要素取得例 2．「リストから選択肢をクリック」で使用するUI要素取得例 UI要素を指定するセレクタ…
PowerPointの「ノート」機能は、スライド毎にプレゼン時のメモを記載でき、「発表者ビューを表示」してノートを読むことができるなど便利です。 しかし、他者とファイル共有時には、ノートに記載したメモを削除したいことがあります。そのようなときにノートに記載したメモを一括削除する方法です。 内容： アウトプットイメージ 一括削除の方法 （1）「ドキュメント検査」の実施 （2）プレゼンテーションノートを「すべて削除」の実行 補足説明 アウトプットイメージ 次のように各スライドにあるノートのメモを全スライド一括で削除する。 「ノート」機能のメモ 一括削除の方法 （1）「ドキュメント検査」の実施 「フ…
Power Automate DesktopでUI要素を指定する際、設定条件の値に変数を使用することもできます。 変数を使用した汎用的なUI要素の指定方法にすることで、メンテナンス性の向上や維持コストの低下につながります。 内容： セレクタービルダーで値に変数を設定する基本 containsで文字列変数の活用例 （1）事例の前提と設定イメージ （2）containsで文字列変数の指定 変数設定の注意点（Power Fxが有効の場合） セレクタービルダーで値に変数を設定する基本 セレクタービルダーで、値を編集状態にすると、変数を表すxを指定可能です。 セレクタービルダーの値に変数を使用可能 co…
Power Automate Desktopのセレクタービルダーで"contains"を使用し、特定の文字列を含むUI要素を指定する方法です。 自動で設定されるUI要素の指定方法では安定した動作ができない場面で、「テキストエディター」に変更し、"contains"セレクターを使用した指定事例です。 内容： 事例の前提 設定手順 1．Microsoft Edgeの「開発者ツールで調査する」のクリック 2．DevToolsでUI要素を確認する 3．セレクタービルダーで「テキストエディター」の状態にして指定 補足説明 事例の前提 Webサービス（kintone）の入力フォームで、「複数選択フィールド…
Power Query（パワークエリ）で、空白の列を追加したい時があります。 例えば、他のExcelの表に貼り付けするなどの都合で、列の順序や数合わせののために項目名はあるが、データは空白にしたいケースなど。 初歩的なことですが、Power Queryを初めて使用したとき、その方法が分からずに悩みました。 空白の列を追加する方法は、次の「新しい列を追加して同じ値を入力」の方法のようにカスタム列で、”null”を入れるが一番簡単な印象。 null は、何もないという意味。 shikumika.org 簡単な手順ですが、参考にカスタム列の画面イメージは次のとおり。 カスタム列の式で、小文字で"nu…
Excelのピボットテーブルで、集計の軸（行や列）に影響しないデータ集計、表示の基本は次のとおり。 shikumika.org 今回は、ピボットテーブルの軸で、一部はそのまま集計の軸（行や列）に利用するが、その他は利用しないなど、少し応用の方法。 実際に集計したい場面にならないとイメージが持ちづらいが、例えば次のアウトプットの結果（ピボットテーブル）で、「カウント／年代」は通常の集計。そのうち、ALL年代の場所は、「東京都」としての全体件数が表示されている状態。 要は、上記で紹介のデータ集計、表示の基本は、集計の軸（行や列）の絞り込み条件をすべて無視する方法で、今回の方法は、絞り込み条件の一部…
Excelのピボットテーブルで、集計の軸（行や列）に影響しないデータ集計、表示をしたいと思うことはありませんか？ 例えば、次のようにピボットテーブルの表内で、全データの件数と年代別の件数を集計したいなど。 通常のピボットテーブルでは、行や列に設定した軸に基づいた集計しかできません。 そのため、全体件数と年代別件数を並べた表示にするには、レイアウトの制限を大きく受けてしまい、そのままでは使いづらい表ができてしまいます。 ピボットテーブルで、集計の軸（行や列）に影響をうけない集計方法 その結果、最終アウトプットに近いピボットテーブルを作成した後、最後のひと手間でコピー、別表に貼り付けなどの作業が発…
久しぶりにAccessを使ったデータ整理。 最近は、 Power Queryで近いことができるようになっていますが、リレーションを維持して入力や編集をする場合はAccessの方が、まだ便利です。 処理の一部を自動化したいと思い、VBAを使用しない人のために、全体の処理ステップをVBAではなくマクロで作成。一部、VBAで処理していた部分をマクロから呼び出して、そのまま利用したかったがマクロからSubが呼び出せずに苦戦。 Access マクロの設定画面 SubをFunctionにしないと呼び出せない仕様みたい。今更ですが。 また、プロシージャ名や、Functionの関数名も日本語だと呼び出せないみ…
Power Query（パワークエリ）を初めて利用するとき、色々な項目があって悩みます。しかし、列を追加する基本的な操作方法は、「列の追加」にある次の赤枠内を知っていれば十分に使える印象。 PowerQuery（パワークエリ）の基本的な操作方法 「列を追加」のポイント 上図の赤枠内をまず理解しておけば、列追加については概ね対応できそう。 個人的には、Excelの数式を利用しているなら、次の順番で理解すると業務で活用できるレベルの習熟は容易な印象。 「列を追加」のポイント 「重複する列」は、列のコピー 「カスタム列」の基本操作 「条件列」の基本操作 「例からの列」の基本操作 補足 そして、列の追…
Power Query（パワークエリ）の基本的な操作方法の備忘録。数式の書き方がわからない、数式のサンプルがみたいという場合は、「例からの列」が便利です。 求めるアウトプットのイメージをPower Queryに入力するだけで、必要な数式を自動作成してくれる便利な機能です。 例えば、条件等に応じた値を表示したい、日付データから月度のみ抽出したいなども、とりあえず「例からの列」を使って自動作成し、必要に応じて数式を修正するという使い方ができます。 PowerQuery（パワークエリ 数式の書き方 使い方 使い方 「列の追加」-「例から列」をクリック サンプル値を入力してみる（自分が欲しい結果） サ…
Power Query（パワークエリ）の基本的な操作方法の備忘録。 メニューにある「条件列」を使っての「条件に応じた値表示」では、andやorの条件指定ができないので、カスタム列を使った基本的な設定の手順です。 メニューにある「カスタム列」を使って実施します。 PowerQuery 手順 「列の追加」-「カスタム列」をクリック 新しい列名と、条件を設定 ① 新しい列名は、設定したい列名を入力 ② 条件は、if A and B then else といった構文で記載。列名の指定は、「使用できる列」から挿入するか、”[]”の括弧を使って記入。 次の例は、「住所」という列が”岩手県”と同じ かつ 「…
Power Query（パワークエリ）の基本的な操作方法の備忘録。 新しい列を追加して同じ値が入力ができたら、次は条件に応じて値を変更したい時の手順です。 基本操作は、メニューにある「条件列」を使って実施します。 PowerQuery 手順 「列の追加」-「条件列」をクリック 新しい列名と、条件を設定 ① 新しい列名は、設定したい列名を入力 ② 条件は、プルダウンをクリックすると、設定できる項目が表示されます。 値は、文字を直接入力したり、比較対象となる列を入力することもできます。 次の例は、「住所」という列が、”岩手県”と同じだったら、”対象”と表示し、その他は何も表示しない設定。 OKで確…
Power Query（パワークエリ）でデータ整理時に、新しい列を追加して同じ値を入力したい時のメモ。 例えば、データを分類するために、同じ値を入力しておきたい時の方法です。利用していないと初歩的な操作方法を忘れてしまうので備忘録。 powerquery 手順 「列の追加」-「カスタム列」をクリック 新しい列名と固定値を設定 ① 新しい列名は、設定したい列名を入力 ②カスタム列の式に、固定値をダブルクオンテーションで囲み入力 OKで確定すると、次のように、新しい列と、値が入力される。 以上、Power Query（パワークエリ）でデータ整理時に、新しい列を追加して同じ値を入力したい時のメモでし…
Power Query（パワークエリ）を利用するときのヒントとテクニックについての備忘録。 後日のメンテナンスや、作業の効率化を考慮すると、次をしておくと良さそうです。 列の「移動」は、処理の最後の方にする。 ステップのプロパティで、「名前の変更」と「説明」を利用する データが多い時は、フィルターや不要な列削除で、データ量を減らす 参考 列の「移動」は、処理の最後の方にする。 並び替えをすると、全ての列名と順序のステップができる。 しかし、このステップの前で列を削除しても、このステップの列名は自動更新されないため、エラー修正が必要となる。必要な列が確定してから、列の「移動」をした方が効率的です…
Wordファイルで、ファイル内にある数十個の図形サイズを一括で更新するVBA。 横幅サイズは、400ptに設定したサンプルです。 Sub 画像サイズの一括更新() Dim 画像 As Object For Each 画像 In ActiveDocument.InlineShapes If 画像.Type = wdInlineShapePicture Then '図形のみ抽出 画像.LockAspectRatio = msoTrue '高さと幅の比率を固定 画像.Width = 400 '任意に設定 End If Next 画像 End Sub 補足 図形の種類は、以下のとおり。 learn.m…
一度設定したセルの書式を他のセルにも繰り返し設定したい時など、直前の操作を繰り返す方法として「F4キー」や「CTRL ＋ Y」があります。この直前の操作の繰り返しは、途中に文字入力後も繰り返し可能です。
Excelのデータ整理時、改行を一括で削除したい時の備忘メモ。「Ctrl＋J」か、SUBSTITUTE関数、もしくはCLEAN関数を使って置換可能。「○回目に出現する改行のみ削除したい」という場合は、SUBSTITUTE関数であれば指定可能。
Excelでセル内改行は「Alt＋Enter」を押します。スペースでレイアウト調整すると、列幅変更時や文章変更時に再調整が必要で手間です。改行に関する知識で、さらに「Ctrl＋J」やExcel関数を使用してCHAR(10)を使った改行の活用方法も知っておくと便利です。
Excelで、大量の電話番号リストからハイフン等の数字以外を効率的に削除するため、正規表現を用いたユーザー定義関数のサンプルを作成した備忘メモ。 作成の背景は顧客リストの整理。
