「エクセルの複数ファイルを読み込んで縦結合するクエリ」については、既に色んなところに記事や動画がアップされているので、いちいち書かなくてもいいかなと思っていたんですけど、先日質問掲示板の質問で紹介しようとしてネットを検索してみると、意外に…
時々、アルファベットを連番（「A」～「Z」）で欲しい時がないでしょうか。なんなら「Z」の後に「AA」～「ZZ」と続いて欲しい場合もあるかもしれません。 今回は色んなアプローチの方法で「A」～「Z」を作ってみます。 「A」～「Z」をスピルで表現する 「A」…
昨日の足し算引き算ランダム出題のついでに、リストの中から総当たりの組み合わせを作る方法についても考えてみました。 というのもこれ、やることはそんなに変わらないんですよね。 組み合わせ表を作る 不要な値を除外する リストを縦に組み替える 組み合わ…
小学１年生の息子が、夏休みの宿題で「足し算カード・引き算カード」を毎日やっているんですけど、あれってカードをめくるのも時間を測るのもめんどくさいんですよね。カードをめくる親の腕前次第でかなりタイムが変動しますし。エクセル脳の私としては、マ…
パワークエリ便利ですよね。ただ使っている内に、「セルの値を変数に使えたら便利なのに」と考えるようになるかもしれません。セル値をパラメータ（変数）として使う方法はいくつかありますが、単純にはクエリとして読み込めばいいだけなので、手順さえ覚え…
久々のパワークエリネタです。検索値が用意したリストとデータ一致するかをチェックする方法はいくつかあるんですけど、「～を含む」を検索する場合は方法が限られてきます。 上記の画像では、「電気・水道・ガス」のいずれかのワードを含む値をテーブル１か…
エクセルに限らず、案分（按分）計算では作業列を使うのが基本です。というのも場合によっては結果に端数が発生するからです。ただ、あえてスピルで案分計算をしてみるのもおもしろいかもしれませんね。 案分計算で誤差が起きる事例 最終行で案分金額の誤差…
今更なんですが、エクセルユーザーでは比較的多い要望なので、空白セルを参照した時に「0」を表示さえない方法をまとめました。 エクセルの数式は「空白」を返せない 実例：単純な参照の場合 実例：検索系の関数の結果の場合 「0」を表示させない方法（単純…
小計行を入れたい場合、ピボットテーブルやデータの「小計」機能を使えばいいんですが、末尾の行の右横に小計を表示させたい場合もあるかと思います。 単純には下のセルと比較する数式を組めばいいです。 C1：=IF(A2=A3,"",SUM(B$1:B2)-SUM(C$1:C1)) 下方向…
複数条件で合計を出すのは SUMIFS関数。複数条件で件数を出すのは COUNTIFS関数。では複数条件で検索をするのはなんでしょう。 別段、これと決まった関数があるわけではないんですが、スピルに対応しているバージョンなら FILTER関数か XLOOKUP関数を使うで…
本日３つ目。スピル配列の最終行列の値を取得する方法についてです。 わかる人からすると、「こんなことくらい」なんでしょうけど、時々「検索結果」に出てくるんですよね、これ。 Ecxel2021以降、数式の結果はスピル（動的配列）で１セルからはみ出して表示…
多分一番要望として多いからだと思うんですが、複数のファイルを一括で読み込むクエリの質問が増えてきています。 ただ、ヘルパークエリどころかパワークエリがどんなものかすら、よくわからないまま使っている人が多い印象です。 どうもネットを検索してみ…
今更なんですが、結構この説明をするのに時間を取られることがありまして…… 何もない状態から「空のクエリ」を作る方法を下記にまとめています。 エクセル上（ブック）からクエリを作成する方法 Power Queryエディタからクエリを作成する方法1 Power Queryエ…
先日の「年と月を指定して日付リストを作る方法」の続きというか別アプローチです。一から日付リストを作る場合は前回の方法でよいのですが、既に用意されているリストから特定の曜日だけを抽出する場合はどうすればいいでしょうか。 指定の曜日を抽出する場…
以前（「数式/関数：フィルタをかけた範囲から条件指定で集計する 数式／関数」）にも一度書いたんですが、定期的に質問が出るので記事も定期的にあげてみます。フィルタで抽出してから条件指定で計算する方法です。 フィルタをかけるなら全部フィルタでやれ…
セルに年と月を入力して、１ヶ月分の日付リストが欲しい場合、皆さんはどのようにされているでしょうか。 方法はたくさんありますし、スピルに対応していないバージョンでも上のセルと比較しながらやればいいんですが、最新の Excelなら１セルに数式を入れる…
Excel2021で追加された SORT関数なんですが、Googleスプレッドシートの SORT関数とは微妙に仕様が異なります。それどころか同じ Excelなのに、後で追加された GROUPBY関数や PIVOTBY関数の並べ替えの指定方法とも仕様が違います。実のところ、パワークエリの…
２つのテーブルを縦に結合する時は、列の名称とデータ型が揃っている必要がありますよね。データ型については、結合した時に揃っていない列が any型になるだけですみますが、列名が異なると別々の列として結合されてしまいます。 「テーブル1」・「テーブル2…
2025年は、基礎的な操作についても取り上げていきたいと考えています。というわけで今回は、古いテーブルと新しいテーブルを突き合わせて、古いテーブルにはない追加された行だけを抜き出す方法について考えてみます。 いわゆる差分抽出（不一致クエリ）とい…
先日、こんな方法もあったなと思い出して「数式/関数：文字列から数字だけを抜き出す」を書いたところなのに、REGEXEXTRACT関数が使えるようになったので必要なくなってしまいました。いえ、いいことなんですけど。 数字を含む文字列から数字だけを抜き出す…
あけましておめでとうございます。 既に三が日もあけてしまいましたが、今年もエクセルネタをあれこれと書いていきたいと考えています。Web検索をした時にピンポイントな記事を見つけられずに困ることがあるので、それを補完するつもりでやっていますが、先…
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が使えるので数式のほうが…
