Microsoft365に新しく追加された GROUPBY関数を使うと、指定範囲を基準にしてグループ化した結果を返してくれます。利用用途は集計に限りません。パワークエリのグループ化と同じく、文字列の連結にも使えます。 GROUPBY関数の第３引数には「function」を指…

パワークエリでの文字列の変換には、「値の置換（Table.ReplaceValue）」を使いますよね。「A」を「B」に置き換えするような場面なら、これでできます。 ただパワークエリでは、今のところワイルドカードや正規表現が使えません。「A BCD」や「BCDA」のよう…

数式で「同じ行に０以外の数値が１つでもあれば」のような、「行全体」を条件にするのって以前は若干面倒でしたけど、BYROW関数とイータ縮小ラムダの組み合わせでとても簡単になりました。 件数=COUNT(1/BYROW(1/B2:F5,COUNT)) これだけ。抽出だったら下記の…

実のところ必要性はそれほど感じていないんですが、やれるかどうかに興味があったので「クエリ名の文字列からクエリを呼び出す方法」を考えてみました。 予め、上記の３つのテーブルは「テーブル1」「テーブル2」「テーブル3」として、クエリを作成済（接続…

前にどこかで書いた気がするんですけど、忘れてしまったので改めて。下の A列のような文字列と数字が混じった値から「数字」だけを抜き出す方法です。 「『数字以外』だったら、TEXTSPLITを使うだけなのに」と思った人は惜しい。というのも、その「数字以外…

慣れないと混乱すると思いますので、２つのリストを使って比較抽出する方法をまとめておきます。 「A」と「B」のリストがあるものとして、 ＡとＢのすべて = List.Distinct(A & B) もしくは = List.Union({A, B}) ＡとＢで重複するものだけ = List.Intersect…

銀行振込の際、いざ振り込もうと思ったら振込先名（受取人名）に「ｯ」や「ｬ」などの小さなカナ文字（半角）が混じっていてエラーを起こしたことはないでしょうか。拗音や促音を表現するための小さなカナ文字は、「捨て文字」とか「小書き文字」とかいうんで…

Excel365でイータ縮小ラムダが使えるようになった記念ということで。これまで、複数条件で抽出する方法としては、MMULT関数を使うのが一般的でしたが（嘘つけ）、この方法を使えば複数条件での抽出の記述がすっきりまとめられます。 E2：=FILTER(A2:C9,BYROW…

会社名（法人組織名）から、法人格（「株式会社」など）とそれ以外を分ける方法について。 関数を使って数式でやるにしても、パワークエリでやるにしても、法人格のリストは用意したほうがいいです。 最新のエクセルなら、TEXTSPLITが使えるので数式のほうが…

なんとなく思いついたので、備忘録として残しておきます。数式を使って、縦に並んだデータにグループごとの連番を振る方法を考えてみました。データはグループ列でソートされている前提です。というかそうしておかないと、何がなんだか分からなくなります。 …

今回は「複数のブックの複数のシートを結合して読み込む」クエリの作り方を説明します。ただし、このクエリはマウス操作だけではできません。Power Query エディタを開いて編集する必要があります。 「複数ブックを読み込んで縦に結合する」とか「複数シート…

以前に一回やったんですが、もう少し手軽にできないかとやってみました。複数の CSVファイルを、なるべく手間をかけずに結合して読み込んでみます。 指定フォルダに接続する CSVファイルを縦に結合する式を入力する おまけ：ファイル名列を追加したい場合 指…

金種計算（紙幣や硬貨などの通貨の枚数を計算する）については、昔から色んなやり方がありますし、条件を絞れば金種表は比較的簡単に計算できます。ただ、金額範囲と通貨範囲を指定してスピルで計算しようとすると、少し面倒になります。 金種自体は「金額を…

当番表やローテーションが被らないよう、ランダムに組み合わせたいと考えた時、従来であれば数式でやるのはそれなりに大変だったんですが、Microsoft365ならそうでもないなと思った次第です。 例えば、５×５の枠に１～５の５つの数字を、縦横（斜めは除外）…

購入履歴のデータを読み込むと、途中で間違いに気付いてキャンセルされた履歴も混じってきますよね。集計する際にはいらない情報なんで、今回はこれを取り除いてみようと思います。 今回の購入履歴データについては、下記を前提としています。 当日中のキャ…

パワークエリでファイルを読み込むと、ソースの下に「変更された型（Changed Type）」が自動的に作成されます。この機能、邪魔だなと思うことないでしょうか。 もちろん判定してくれることに否やはないんですが、必要ない時に勝手に追加されると、思いもよら…

基本的な機能なので、普通に使う分にはなんてことのない話なんですが。区切り文字を使って列を分割する際の話です。 機能としては至極単純で、区切りたい列を選択して「列の分割」を選ぶだけです。 区切りたい列を選択した状態で右クリック →［列の分割］→［…

「自分にとっての備忘録を、ですます調で書くなんて変だ」との思いで、断定調で２年ほど続けてみたんですが、既に内容が自分宛の記事ではなくなりつつあります。すごく書きづらくなってきたので、本日を機に常体から敬体に変えていきたいと思います。 古い記…

何かしらの項目を基準にして集計する際、「A」「B」「C」……とある項目の内、「B」のデータが一つもないと、集計結果のテーブルは「A」「C」……と、歯抜け状態になってしまいます。そういう場合は、予め用意した「A」「B」「C」……テーブルに、集計結果を入れ込…

先日、数式の回で書きましたが、ピボットテーブルのほうが簡単なので、改めて書き直します。ひとまず「電子データの現金出納帳（家計簿・小遣い帳／おこづかい帳）に残高列はいりませんよ」ということだけは繰り返しておきます。日計も月計もいりません。 ど…

指定フォルダに入っている Excelファイルで、単純に「ブックの中のこのセルだけ」を読み込んで結合したい場合の手順をまとめてみました。 まずは参照用のフォルダに同形式のファイルを保存します。全部同じシート名・同じ配置が前提ですのでご注意を。 今回…

いきなりですが、家計簿（現金出納帳／小遣い帳／おこづかい帳も同じ）に「残高列」って必要でしょうか。ネットでテンプレートを検索すると、必ずといっていいほど「残高/差引残高」列が右端にひっついています。以前にも同じ突っ込みを入れたことがあります…

Power Queryで開始日から終了日までの連続している日付リストを作る場合、多分 List.Datesを使んじゃないでしょうか。 = List.Dates( 開始日, Duration.Days(終了日-開始日)+1, #duration(1, 0, 0, 0) ) learn.microsoft.com もちろんこれでいいんですけど、…

文章で説明するのが難しいんですが、「項目名1・値1・項目名2・値2……」のように、２行１組で縦一列になっているデータを組み替えたい時の話です。 １組あたりの項目数が固定の場合は比較的簡単です（ただし、Microsoft365なら）。 =LET( _wc,WRAPCOLS(TOROW(…

画像の通りなんですが、元のテーブルに値のない日付を追加して表示したい時は、どうすればいいでしょうか。 この場合、まずは「4/1」から「4/7」までが連番になっている日付テーブルを新規に作ったほうがいいです。元のテーブルは「テーブル1」として読み込…

表計算の世界で「やったら後で後悔することランキング」をとったら、上位３以内に入ってくるのは「セルに複数の情報を入力する」だと思います。因みに他の２つは「同じ形式の表をシート単位で量産する」と「セル結合で入力を省略する」です。異論は認めます…

「２つのテーブルをマージして必要な値を展開する」って、クエリでやれば超簡単なんですが、これをあえて数式でやってみました。 因みにクエリでやるなら「大分類」でマージして「値」を展開するだけです。マウスの操作だけで読み込みまでいけます。書くまで…

やってみて「あれ？」と思ったので記事にすることにしました。画像のように空白行で区切られたデータの横に連番を振りたい時、 先頭が必ず空白行で、範囲に予め数式を入れておけばいいのなら =IF(A1="","",SUM(INDEX(B:B,ROW()-1),1)) 数式を下方向にコピー …

クエリのマージを実行した後で、結合した列を展開（Table.ExpandTableColumn）した時に、行の順番がずれてしまったことはないでしょうか。 例えば下のようにテーブル1とテーブル2をマージして、「テーブル2」列から必要な列を展開すると、 「名前」列が元の…

子どもが産まれたばかりなもので、カウプ指数から肥満度を計算するのに数式でどうやるか考えてみました。 カウプ指数の計算は、BMI値と同じなんですが判定基準が異なります。なので月齢に応じた対応表がまずは必要になります。 こういう時に注意したいのは、…