なんでもエクセル(Nandemo Excel)

何でもエクセルでやってしまう そこのあなた。ようこそ

Power Query:セル内改行された値を行単位で分割する

表計算の世界で「やったら後で後悔することランキング」をとったら、絶対上位3以内に入ってくるのが「セルに複数の情報を入力する」だと思う。因みに後の2つは「同じ形式の表をシート単位で量産する」と「セル結合で入力を省略する」で間違いない。 都道府…

数式/関数:マージと展開を数式でやってみる

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

数式/関数:空白行で仕切って連番を入れる

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

Power Query:マージ列の展開時に行の順番がずれないようにする

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

数式/関数:月齢を指定してカウプ指数を計算する

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

数式/関数:住所を分割する

住所から郵便番号を検索したり、郵便番号から住所を検索したりは、WebAPIの力を借りれば比較的簡単にできるようになった。今回はそれらの合わせ技。手入力された住所情報を「都道府県名」「市区町村名」「地名」「番号」「それ以外」に分割する。 まずはテー…

Power Query:要素数の異なる配列からテーブルを作る

個数の合わない配列(ジャグ配列)を、単純に1テーブルに変換しようとすると、エラーを起こしてしまう。例えば要素数が 10個の配列(例えば「{1..10}」)を 3分割した場合、最後の配列の要素数は 1つになる。これを Table.FromRowsでテーブルに変換しようと…

Power Query:カタカナをローマ表記に変換する

「カタカナをローマ字表記に変換する」ネタは VBAでよく見かける。数式でも WebAPIの力を借りれば超絶簡単にできる。これをクエリでやってみよう。 単純には置換リストを作って置き換えればいいので、例外処理を除けばそんなに難しい話ではない。ただし、何…

Power Query:重複している行を抽出する

簡単かと思ってやってみたら、意外と苦戦するかもしれない。「重複を省いて抽出」ではなく「重複している行を抽出」したい場合、どうすればいいか。 こういう時、エクセル感覚だと「それぞれの行に、一つ上までの行を参照する COUNTIF関数を仕込んで……」みた…

Power Query:リストを使って抽出する

クエリの中で複数のキーを指定して抽出したい時、抽出用リストがある場合はそれを利用するにこしたことはない。 例えば「リストの値と等しい行を抽出したい」場合は、Table.SelectRowsの条件を下記のように記述すればいい。 each List.Contains( {"中央区", …

Power Query:上と同じ値を非表示にする(グループ表示)

空いている行を上の値で埋めるのは「下方向にフィル」で簡単にできるけど、その逆はどうだろうか。もちろん「上方向にフィル」という意味ではなくて。 つまり先頭の値以外を表示しない。「ピボットテーブルでやれ」とか「クエリでやることか」とかいわれると…

数式/関数/Power Query:指定行数分だけ繰り返し表示させる

指定行数分だけ繰り返し表示をしたい場合。それも「1」「2」「3」「1」「2」「3」……ではなく「1」「1」「1」「2」「2」「2」……としたい時はどうするか。 これが1列しかない場合はそれほど難しくはない。例えば4行繰り返したい場合なら、 =TOCOL(INDEX(FILT…

Power Query:複数のシートのデータを縦結合する(ヘルパークエリ編)

複数のブックを統合するのに標準機能でやると、出現するのがヘルパークエリなんだけども、これが苦手な人も多いのではないかと思う。でも慣れれば超便利なので、是非とも仕組みを理解して活用して欲しい。もちろん他の用途にも応用できる。 今回は「複数のシ…

数式/関数:MMULTを使わずに複数条件で集計する

複数条件の配列計算に MMULTを使う人は時々いるかと思う(いる……よね?)。使った方がすっきりすることも多いのだが、いかんせん知らない人が見てもチンプンカンプンになること請け合いの方法なのが玉に瑕である。 こういう表で、「区分」と「補助区分」を使…

Power Query:ソースの読み込みを条件式で切り替える

ソースの読み込みを条件式で切り替えた場合、該当しないほうのデータは読み込まれるか。答えはノー。つまり読み込む情報を小分けにしておいて、必要に応じて切り替えて使ったほうが処理は断然早くなる。 例えば A列が1行、B列が100万行のテーブルがあるとす…

数式/関数:年月表記の文字列を月単位表記に変換する

勤続年数を「何年何ヶ月」と表示したいからと、DATEDIF関数の「Y」と「YM」で計算したものを「&」で繋いで文字列で表現する人が時々いる。 =DATEDIF(入社日,現在,"Y")&"年"&DATEDIF(入社日,現在,"YM")&"ヶ月" やんのはいいけど、こんなものどうやって計算に…

Power Query:ソースの読み込みエラーを回避する(DataSource.Error)

皆さんは、クエリでソースを読み込もうとしたらエラーが起きたことはないだろうか。私はよくある。 固定のパスでフォルダを参照していると、ふとした拍子にリンクが切れてしまうことがあるからだ。 こんな時は、慌てずに右上の「設定の編集」ボタン、もしく…

数式/関数:UNIXタイムスタンプをシリアル値に変換する

「10桁以上の数値からなる日時を表現した数値」があったら、それは「UNIX時間(タイムスタンプ)」で間違いない。桁数が多い場合もあるが気にする必要はない。この仕組みは単純なもので、「1970年1月1日」を「0」として経過秒数を協定世界時(UTC)で表現し…

Power Query:1セルに入力したアンケート結果から集計する

Excelを利用している人の中には、「入力で楽して、計算で困る」人が結構な数いる。月ごとにシートを分けて、Excel方眼紙で表を作っちゃうような人はその部類である。 これは、とある人が入力したアンケート結果の記録である。 何かというと「問1は『1』、問…

Power Query:親フォルダのパスを指定してファイルリストを作成する

Power Queryは、ファイル内のデータを読み込む以外にも色んな使い道がある。例えばフォルダコネクタも便利な機能のひとつだ。フォルダのパスを指定して、サブフォルダを含めたファイルのリストを作成したい場合に使える。 例えば、Cドライブ直下に「親フォル…

Power Query:日付列と時刻行の一覧表を組み替える

画像で見た方が分かりやすいと思うので、早速だが下のような表があるものとする。 「日」の列と「時刻」の行で構成されたマトリクス表から「日時」と「名前」の表に組み替えしたい場合。何となく「列のピボット解除」をすれば良さそうなのは分かると思う。 …

Power Query:2つのCSVファイルを比較する

以前とある業務で、引き継ぎ相手に Web送信用のCSVファイルを作らせて、自作ファイルと比較しようと思った時のこと。チェックしようとして気付いたのだが、何故か引き継ぎ相手が作ったファイルは私のものとは行の並びが違っていた。送信するデータの順番自体…

数式/関数:B列と比較してA列にしかない値を抽出する

2列の比較ってよくある話なんだけど、COUNTIF関数を入れた作業セルをずらっと並べる方法はさすがにもうやめたほうがいい。 添付のように、A・B列を比較して、A列にしかない「10」と「14」を抜き出す場合。絶対にやってはいけないのが、FILTER関数で条件指定…

Power Query:複数行ある見出しに列のピボット解除を実行する

ピボット解除は便利なんだけど、それはあくまで見出しが1行の場合に限られる。元の表が一覧表の場合、得てして見出しが複数行にわたる場合もあるだろう。そういう場合、どうすればまともな形に組み替えできるか。 2行とは限らず、3~4行に分かれている場…

数式/関数:ドロップダウンリストを連動させる

昔から、PC用語を日本語的な発想で省略表現する人が好きになれない。 「ドロップダウンリスト」を「ドロップダウン」や「プルダウン」のように略されると気持ちが悪くなる。わざわざ重要なほうの言葉を略してしまうのは、いかにも日本的だなと思う。こういう…

数式/関数:画像を検索する(IMAGE関数)

数式で画像を検索したいという要望は昔からあるけど、表計算ソフトでこれを実現するのはなかなか手間である。 マクロは抜きにして、昔の Excelだと図形にリンクした番地のセルを表示させる方法 や、あるいはグラフの系列に画像を埋め込んで該当するものだけ…

Power Query:複数のシートをまとめて集計する

表計算の世界で、同じ形式の表をシート単位で分けてしまうのは悪手以外の何ものでもない。ところが、Excelに不慣れな人は必ずといっていいほどこれをやってしまう。 例えば、A列に「名前」、B列に「値」が入力されている表が「Sheet1」から「Sheet10」まで並…

Power Query:グループごとに横に並んだデータを縦に並べる

前回の逆。横並びのデータを縦に組み替える。 とはいえ、これクエリだと超簡単。何故なら「ピボット解除」があるから。 まずはテーブルを読み込む。 作った表にテーブル書式を設定(テーブル名「テーブル1」)。 「テーブル1」を選択した状態で [データ]→…

Power Query:縦に並んだデータをグループごとに横に並べる

縦並びのデータを区分を指定して横方向に組み替えしたいという質問は、度々目にするんだけど、グループ化したテーブルに連番列を追加する操作が必要なので、マウスを使った操作だけでは難しい。マウス操作中心でやる方法は、以前に書いたので割愛。 というこ…

Power Query:条件テーブルを使って抽出する

検索用の条件テーブルを使って一致するデータを抜き出す場合、単純にはテーブル同士をマージしてやればいい……んだけども、マージが絶対に必要という訳ではない。 というわけで、まずはマージを使って「表1」から「表2」と複数条件で一致するデータを抜き出し…