【Excel】ドロップダウンリストの設定セルを明示するため、当該セルにコメントを表示

(1) やりたいこと (2) 方法 (1) やりたいこと 人に入力してもらうためのExcelフォーマットにドロップダウンリストを設定してリストから選択してもらいたい場合の話です。 下図の例はD4セルにデータの入力規則のリストを設定しています。 ただ、その…

【Excel】ピボットテーブルで挿入したスライサーで選択中の項目を取得する。

(1) やりたいこと (2) 事前準備 (3) スライサー選択項目の取得方法 (4) 最後に (1) やりたいこと データテーブルからピボットテーブルで集計し、ピボットグラフを作成。これにスライサーを設定して項目の絞込みと連動するグラフを作る時の話…

【Excel】2段階ドロップダウンリストの設定

2段階のドロップダウンリストの設定方法について説明します。様々なやり方があると思いますが、個人的には今回紹介する方法が一番スッキリしました。 (1) やりたいこと (2) 方法 (3) 補足:一つ目の項目が増えた場合 (1) やりたいこと 下図に記…

【Excel】LET関数を使った計算の事例

Microsoft 365の新関数「LET関数」を使った事例をいくつか紹介したいと思います。 (1) LET関数について (2) LET関数の使用例① 経営指標の計算 (3) LET関数の使用例② 費用の配賦計算 (3) LET関数の使用例③ 年間カレンダーデータの作成 (1) LET…

【Excel関数/VBA】データ手入力時、 既入力済データをドロップダウンリストで選択して入力する

(1) やりたいこと (2) 実践: ドロップダウン用のリストを関数で作成 (3) 実践: VBA Worksheet_Changeイベントでセルの値を分割 (4) 終わりに (1) やりたいこと 何でもよいのですが、例えば下図のようなExcelの手入力シートがあります。 部…

【PowerQuery】生年月日から基準日時点の年齢を出力するカスタム関数

PowerQueryカスタム関数の練習で、生年月日から基準日(又は現在日付)時点の年齢を出力するものを考えてみました。 (1) 条件 (2) 作成手順(要約) (3) コード (4) 関数の実行 (5) 実際の使用例 (6) さいごに (1) 条件 ExcelのDATEDIF関数…

【PowerQuery】大量データから特定の項目毎の金額上位○件のみ抽出する

(1) やりたいこと (2)コード (3)適用したステップ毎の説明 (1) やりたいこと 下図例の通り、元データから特定の項目(勘定科目)毎に金額上位3件の明細を抽出します。今回は金額上位3件としましたが、絞り込む条件は金額下位〇件や金額〇〇円…

【PowerQuery】フォルダパス列から最終保存フォルダを抜き出した列を作成

大した内容ではないのですが、備忘も含め書いておこうと思います。 (1) やりたいこと (2) 実践 (3) 解説 (1) やりたいこと 下図の通り、フォルダからPowerQueryエディターへファイルを読み込んだ後、そのファイルの一覧のフォルダパスから最終保…

【PowerQuery】更新するたびにランダムな出社パターンの勤務表を作成

最初に言っておくと、今回の記事は例によって本来PowerQueryでやるようなものではありません。元々Excelワークシート関数でやったことをPowerQueryでもできるかなと思い試しました。ただ、試行錯誤しながらやった中でM関数の使い方等勉強になったところが色…

【Excel】RANDARRAY関数等を使ってランダムな配列を作成

RANDARRAY関数(Microsoft365で使用可)を使ってみました。実務で乱数を使うことはあまりないのですが、テスト用のデータを作成する時などに役立ちそうです。 support.microsoft.com 以下、それぞれ20行くらいで作成することとします。 (1) 1,000~10,000 …

【PowerQuery】二つの日付間の経過月数を算出する

大した内容ではありませんが、計算方法の備忘として。 1.やりたいこと 2.前提条件 3.実践 1.やりたいこと 2.前提条件 基データの中に基準日が含まれていないものとして、入力用のテーブルを用意する。(基データに基準日がある場合は不要) 経過月…

【Power Automate Desktop】選択した期間・決算月からカレンダーデータ(Excelファイル)を作成する

1.やりたいこと 2.フロー作成の要件(概要) 3.実践① フローの作成 ~ ダイアログの設定等 4.実践② Excelの起動 ~ 列見出しの書き込み 5.実践③ ループ処理/Excelワークシートへの書き込み 6.実践④ Excelファイルの保存 7.フローの実行 8.さ…

【PowerQuery】フォルダからのデータ取得で列名が変動するデータを取込む方法

1.やりたいこと 2.列名が変動するとなぜ取込めなくなるか 3.どうすればよいか 4.実践 1.やりたいこと 下図の通り、「月別データ」フォルダに保管されている月別のファイルをPowerQueryで取込みたいのですが、このデータは月によって列の部門が変動…

【Power Automate Desktop】選択したフォルダ内のファイル情報一覧をエクセルへ書き出す(+各ファイル名にハイパーリンクを設定)

1.やりたいこと 2.実践① フロー作成 ~ エクセル見出し行の書き込み・変数の設定 3.実践② ループ処理: ファイル情報の書き込みとハイパーリンク設定 4.実践③ キーの送信:エクセルシートの書式調整他 5.実践④ エクセル名前を付けて保存:パスから…

【Power Automate Desktop】特定のフォルダとそのサブフォルダ(任意の複数階層)をコピーする

1.やりたいこと 2.実践① フロー作成 ~ 新しいフォルダの作成 3.実践② Switch条件の設定 ~ 各階層サブフォルダの作成ループ処理 4.実践③ コピー階層の選択2と1の場合(それぞれサブフローを設定) 5.フローの実行/確認 1.やりたいこと みなさ…

【Power Automate Desktop】置換リストからテキストを一括置換処理

今回の変換リストからテキストを一括置換処理する方法となりますが、これは実は、鷹尾祥さんの著書「Excelパワークエリ データ収集・整形を自由自在にする本」の第4章7『文字列変換リストを使った一括文字列置換』と同じことをPowerAutomateDesktopを使っ…

【Power Automate Desktop】エクセルワークシートの値リストからフォルダを作成

1.やりたいこと 2.事前準備 3.実践(1)Excelの起動 ~ Excelワークシートdataの読み取り 4.補足 ~3.(1)実行動作確認~ 5.実践(2)ループ処理 For each でフォルダ作成 6.フロー実行 1.やりたいこと 下図の通り、エクセルのワークシート A…

【パワークエリ】一定期間の予算データから月割計上用データを作成する

1.やりたいこと 2.実践(1)基dataの取込み ~ 「分割月数」「基準年月日」の設定 3.実践(2)予算dataから月割計上dataを作成 4.動作確認等 1.やりたいこと 下図に記載の通りです。今回は一般的に年2回支給される賞与とそれに伴う社会保険料の個…

【Power Automate Desktop(無償版)】Outlookで受信したメールの添付ファイルを定期的にPCの特定フォルダに保存する。

1.やりたいこと 2.実践① フロー作成~Outlookメール取得・添付ファイル保存 3.実践② 保存先フォルダの重複ファイル削除 4.実践③ ループの次回実行時間設定〜現在時刻の取得、トリガー設定 5.フローの実行 6.さいごに 1.やりたいこと 全体とし…

【パワークエリ】部門費実績配賦を都度変動する配賦基準で実行する。

1.やりたいこと 2.エクセルワークシートで関数等により計算する場合(参考) 3.実践 4.おまけ 1.やりたいこと 下図BEFOREのような基データがあります。部門A、B、C で発生した部門費は共通費用となるため、売上高(実発生額)を配賦基準とし…

【パワークエリ】データに小計・合計行を追加し、更に列のピボット、合計列を追加する。

以前、「データに小計・合計行を追加する。」記事を書きましたが、今回はそれの続編で更に列にピボット展開し、列の合計までをパワークエリで作る方法について書いてみます。ただ、やることはさほど難しくはないのですが、ワークシート上でピボットテーブル…

【パワークエリ】複数月の債権残高データから最新月時点の滞留月数列を追加する。

1.やりたいこと 2.実践 3.懸念点 1.やりたいこと 下図左のように基データとして各月(基準日列=勘定月)毎の債権残高データがあります。どの債権であるかは請求番号で判別できますが、計上月は持っていないデータです。これを下図右のように最新月の…

【パワークエリ】ワークシート上のマトリックス表をピボット解除する際に元の表を残したままエディターに取込む方法

【目次】 1.やりたいこと 2.実践 1.やりたいこと 現在開いているエクセルのワークシートに次のようなマトリックス表(見出し等がセル結合されている)があります。これをエディターに取り込んでピボット解除して一覧データ化したい場合に、このマトリ…

【エクセル】FILTER関数等を使ってひとつのデータからグループ毎にシート分割する。

今回は、ひとつのデータを一定のグループ毎にシート分割する方法について考えてみました。VBA・パワークエリは使わずにワークシート関数のみを使用して処理します。(但し、Microsoft365以降で使用可能な関数を含みます) 【目次】 1.やりたいこと 2.FIL…

【パワークエリ】データに小計・合計行を追加する

今回は基のデータに簡単なピボットテーブルで集計したような小計・合計行をクエリ上で追加する方法について書いてみます。前回のBS作成時と似たような内容ですが、少し違った方法でやってみました。 【目次】 1.やりたいこと 2.作成のポイント 3.実践…

【パワークエリ】試算表データから貸借対照表を作成する。

今回はパワークエリを使って会計システム等の試算表データから貸借対照表(B/S)を作成してみたいと思います。貸借対照表の書式は経団連のひな型ベースで作成します。科目・金額は適当なものを使用しました。 【目次】 1.やりたいこと 2.作成のポイント…

【パワークエリ】各列のデータ型をワークシートを経由して一括で変更する方法

パワークエリでデータ型をぽちぽち変更するのが面倒だと思っており、別の方法を考えてみました。 【目次】 1.やりたいこと 2.クエリをワークシートへ 読み込み~変換用シートの準備 3.変換用ワークシートの数式と各項目の説明 4.変更後の数式の作成…

【パワークエリ】試算表データから損益計算書を作成する。

パワークエリを使って会計システム等の試算表科目別のデータから損益計算書(PL)の体裁の表を作成する方法を書いてみたいと思います。科目や金額については適当に割り当てたものを使用しています。 【目次】 1.やりたいこと 2.基データと科目マスタの…