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

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

Poewr Query:フォルダを指定して複数のブックを結合して読み込む

これは今回の完成目標。 前回書いたけれども、セルに参照元となるパスを入力しておくと色々便利。 Power Query:セル値をパラメータにしてファイルを参照する フォルダパス =LET(_fn,CELL("filename",A1),LEFT(_fn,FIND("[",_fn)-1)) こんな形で同階層のフォ…

Power Query:セル値をパラメータにしてファイルを参照する

クエリで自ブックを参照したい時や、自ブックと同階層のファイルを可変で参照したい時、セルに入力された値が使えると便利だ。というのも「当該ブック(保存済)のファイルパス」なら数式で求められるからだ。 A1:=CELL("filename",A1) これで上記の画像の…

Power Query:縦長のデータを指定の行数で分けて横に並べる

列数が少なくて行数が多いデータの場合、そのまま印刷すると余白だらけでページ数がかさむので、適当な行数で横に表示したいという要望は結構あるんだろうと思う。 INDEX/SEQUENCEだったり、MAKEARRAYだったり、古いバージョンでもINDEX/ROW/COLUMN/MOD/INT…

Power Query:検索リストを使って部分一致で抽出する

社名リストの中から検索リストに該当する会社名だけを抽出する時、完全一致の場合はクエリのマージを使って一致するものだけ抽出すればいいので簡単。ただ上のような部分一致で検索して抽出したい場合、マージではうまくいかない。こういう場合はカスタム関…

Power Query:「ブックから」で複数のシートを一括で読み込んで結合する

[データの取得]→[ファイルから]→[Excel ブックから]でファイルを指定した場合、ブックの中でシートが分かれていると、シートの数だけクエリを作らないとといけなくなってしまう。これをマージしようと思ったら更にクエリの追加も必要。というかシート…

Power Query:ロットを指定して梱包ラベルを作る

梱包数に合わせてラベルを作る時に必要かもしれない処理。例えば「55個」の商品を「10個単位」の梱包で分けると「5梱包と余り5個」なので、「10個入梱包×5」と「5個入梱包×1」のラベルが必要。これをクエリでやるとどうなるか。 ロット = 10, 個数リスト = T…

Power Query:縦に並んだ n行一組の項目別データを組み替える

他のアプリからデータを読み込んだら、こんな形だったってことが時々ある。 いわゆる n行一組のデータ(画像は6行)。これを項目別に組み換える場合、「数式かマクロか」になりがちだけど、クエリでももちろんできる。 よく見かける記事はインデックス列を追…

Power Query:列名を統一してからクエリの追加(縦結合)を実行する

複数のテーブルをまとめる際、「クエリの追加(Table.Combine)」を使う前に必ずやっておかないといけないことがある。それは列名の統一。 例えば、下記のような列名が統一されていないテーブルが3つあるとする。 テーブル1 = #table( type table [名前=tex…

Power Query:「年」を入力したら「1月~12月」の月初日が並ぶようにする

まずは A1に「年」を数値で入力したら、その年の「1月~12月」がテーブルで読み込まれるようにする場合。これは簡単。 ひとまず A1を「年」と名前定義しておいて、空のクエリの詳細エディタに下記を入力。 let 年 = Excel.CurrentWorkbook(){[Name="年"]}[Co…