Excel

みなさんこんにちは。胡田です。 皆さんExcel使ってますよね。私も頻繁に使ってます。そして、仕事で「毎月更新するExcel」があります。いや、PowerBIでレポート作っていて必要な情報はそこにあるのでそれを見てもらえばいいのです。自動更新ですし。 でも、Excelファイルを更新する必要…がありまして……。毎月頑張っております。クエリを書いたりうまくフォーマットを改善などしてかなり効率的に処理できるようにしたのですが、次の一手を行うためにどうしてもExcel VBAが必要となり10年ぶりくらいに書きました。 Excelもずいぶん進化してテーブルなども定義できるようになっているのでそのへんも加味して処理しようと思ったところ、結構サンプルを探すのに苦労してしまったので未来の自分(また10年後くらい？)のためにいくつかサンプルコードを書いておこうと思います。 テーブル内の情報を取得 まずテーブル内の情報の取得方法。 サンプルのテーブルはこちらです。シート名、テーブル名、列名を使ってテーブル内の情報を取得します。 サンプルコードはこちら https://gist.github.com/ebibibi/eac7525cd7e211a8e3602b45b7979eab.js きちんとすべて名前でアクセスしているのがポイントです。なお、For ~ Nextをつかっていて気持ちが悪いですが、Excelではforeach的なものを利用したときに順番が保証されないらしく(※ブログ情報であり動作未確認)このような方法で妥協しています。 テーブルにレコードを追加 テーブルにレコードを追加する方法について。サンプルコードは以下です。 https://gist.github.com/ebibibi/f3a056d5977522ff538c9c3cc26cc4bd.js これもすべて名前でアクセス…にしたかったのですがそれを実現しようとするとても大変そうだったので諦めました…。 そしてこの方法で列を追加するとテーブルの１番最後に列が追加される挙動になります。 サンプルのテーブルの状態から実行すると１行空の行ができてしまうので注意が必要です。予め消しておけばOKです。 本当はFlowを使ったほうが良い(と思う(ケースバイケースだけど)) 今回の件、本当はFlowを使いたかったところです。残念ながら私が対象としたExcelファイルに対してはエラーとなってしまい、Flowを適用できなかったのでExcel VBAに逃げました。 参考になるところがあると幸いです。 (2020/11/02 追記) またしてもExcel VBAをいじることがあり、自分で自分のブログを参考にしたので、貼り付けておきます。テーブル内の該当セルをみつけてほかの列を更新してます。 https://gist.github.com/ebibibi/2ae683dfa59736f31af6f14e70d51803.js

皆さんこんにちは。胡田です。 今回は珍しくPowerBIのお話です。私はPowerBIはデータ可視化ツールとしては以前からずっと便利に使っているのですが、PowerBI内でデータを加工したりすることはこれまで避けていました。なぜかというと「良くわからなくて、新しい方法を学ぶ時間が取れなかったから」です。 これは単純に言い訳なのですが、私はプログラムが書けるし書くことが好きなので、なにかデータ加工的な事をする必要がある場合にはデータベースの外側でプログラムで書いてしまいます。そして、データベースをプログラムで更新しちゃう。そして出来上がったデータベースに対してPowerBIを接続してしまえば必要な可視化が簡単にできてしまいます。なので、PowerBIの中でテーブルを定義して、データを操作して…ということはしなくてもどうにでもなったのです。 ですが、他の人と一緒にトレーニングも兼ねてちょっとした複数のcsvファイルを元にしたレポートを作成する機会があり、PowerBIで全部やりたいという希望がその一緒に作業する人にあったので、一緒に付き合うことにしました。 いや、勝手がわからなくて結構苦労しました（笑 というわけで、私のような「昔ExcelでVLookup関数を駆使して頑張ったことあったけどもうやりたくないなぁ…」と言っているような知識レベルの人に向けてPowerBIで戸惑ったところと理解したところを少し書いておきたいと思います。 理想的には「(昔の)Excelしか使ったことがない人に向けたPowerBIの導入記事」的な感じになるといいのですが、はて、さて。 私はこのあたり本気で初心者かつまともに勉強もせずいじって直感で理解しているだけなのでPowerBIをガリガリやっている人からするとレベルが低かったり効率が悪かったり、他にもっと適切なやり方があったりするのではと思いますが、そこは生暖かい目で見守りつつ、コメントで優しくアドバイスいただければと思います。 とりあえず好き勝手にデータを編集できない Excelでは新規にファイルを作るとシートがあり、セルがあり、適当な場所にいきなり値を書き込んだり出来るわけですが、PowerBIではそうはいきません。保持できるデータはすべてきちんと定義されたテーブルの形になります。データベースですね。 とりあえずまずは感覚を掴むためにテスト的にテーブルを作ってみて、データを書き込んで…と試してみたかったので、「データの入力」を行いました。 出てくるのは「テーブルの作成」画面です。このあたりはExcelよりもAccessに近いんですかね?すいません、Accessもほとんど使ったことなくてですね…。 で、この画面でテーブルを作成するのは特に違和感ありませんでした。 列を追加したり、複数レコードにしてみたり、はい。「読み込み」をクリックするとテーブルが作成されます。 ここまではまぁ違和感ないのですが、じゃぁ、このテーブルを編集したい！と思うと早速やり方がよくわからなくなりました。直接この画面では編集できないのですね。 これ、裏側ではすべて「クエリ」が作成され、それによってデータが読み込まれたということになっているようですね。 「クエリの編集」をクリックすると、このテーブルを生成したクエリを確認、編集することができます。 全部クエリで表現されるというのは面白いですね。Excelとは全く異なるアプローチです。 「ソース」をクリックすると再度テーブル作成のGUIが表示されました。なるほど。 １つのセルに「関数を書く」というアプローチではない このテーブルの作成画面は感覚的にはExcelに近い感じで操作出来るのですが、別に関数が書けるわけではないです。 こんな感じにExcelでの関数っぽい書き方をしてもたんに文字列になっています。 このあたりきちんとテーブルとして列の定義が厳密という感じでしょうか。Excelよりも列の型が厳密なSharePointの感覚に近いという印象です。 で、なにか関数を使って計算等したければ、新しい「列」を定義してその中で列の定義として関数を書いたり、テーブル全体に対して「メジャー」を定義するというアプローチになるようです。（間違ってたら突っ込みください。） 「新しい列」をクリックするとこのように列の定義を書くことができます。 これはセル１つに対して定義しているのではなくて、列全体の定義です。Excelとは違います。 ためしに”あ”と入力してみます。 今は２レコードしか無いですが、すべてのレコードの「列」には「あ」と入力されます。列の定義が「あ」なので当然ですね。あくまでも定義したが「列」であって、Excelのように１セルに入力したのでは無いことがわかります。 この列の定義の中では「同じレコードの他の列」を参照することができます。 ※「テーブル１」だと補完してくれなくて面倒だったのでテーブル名を「Table1」に変更しました。 補完してくれますね！ 「列」の定義をTable1の列１にしたところ、きちんと同じレコードの列１の値が入力されました。Excelだと他のセルの値を相対参照、絶対参照で自由に参照する事ができますが、PowerBI内のテーブルの定義ではそういうアプローチでは無いことがわかります。 で、この列の定義の中では「DAX式」が書けるのだそうです。なぜなら、そうやって怒られたから…。 DAX式を列の定義として入力してあげればいろいろと他の列の値なんかを使いながらデータ加工ができそうです。 リファレンスはこちらにあります！ - [Data Analysis Expressions (DAX) Reference - DAX | Microsoft Docs](https://docs.microsoft.com/ja-jp/dax/data-analysis-expressions-dax-reference) たとえば「列」に対して「列１」のテキストの左から３文字を入力してみます。 こんな感じに簡単に行なえます。 列の定義に書けるのがDAX式(DAX functions)ということを理解しておけばまずOKですね。 DAX式とPowerQueryは違うものなので注意！ ちなみに、DAX式とクエリの中で記載できるクエリの文法は全く別物なので注意が必要です。というか私は当初混乱しました。 こちらPower Query。「クエリ」の中で使うものです。 Power Query M function reference - PowerQuery M | Microsoft Docs ...

皆さんこんにちは胡田です。 CSVファイルを開くと日本語が文字化けしてしまっていること、よくありますよね。会社で質問をもらったのですが、あまりにもよくあるケースで困っている人も多いと思いますのでブログに書いておきたいと思います。何度かMicrosoftさんにも報告させてもらっている気がするのですが、なかなか難しいところです。 いくつかパターンがあると思いますが今回対象にするのは下記のケースです。 - ファイルの拡張子は.csv - 文字コードはUTF-8 UTF-8の文字コードで日本語が含まれているケースは非常に多いと思います。というか当たり前にこういう選択肢になると思うのですがこのファイルを単純にダブルクリックからExcelで開くと文字化けします…。BOMあり/無し等で挙動の違いはあるかもしれませんが、今回は文字化けするケースのお話です。 対処方法1　文字コードをSJISに変換してから開く ExcelはSJISが大好きです。CSVファイル自体をSJISに変換してしまいましょう。 ツールはどれを使ってもいいのですが、今だと個人的には専用ツールを使うよりもVisual Studio Codeをおすすめします。 これで文字コードがSJISになってます。 このファイルを普通にExcelで開けばきちんと日本語が文字化けしない状態で開くことができます。 対処方法2　Excel上で文字コードを指定して読み込む Excel上で読み込む時に対処することもできます。 まずExcelのみを立ち上げます。 文字化けしている様子も確認できますね。文字コードを正しく指定します。 ……すいません、サンプルファイルが仕事関連のものできちんと文字化けが直ったスクリーンショットをお見せすることができませんでした。ですが「(株)」と日本語がきちんと文字化けせずに表示できていることが見えると思います。 あとは「読み込み」ボタンを押せば大丈夫です。 今回はCSVファイルを文字化けせずにExcelに読み込ませる方法を2つ紹介しました。でも、毎回対応する必要があるのが難点です…。 結局Excelが文字コードの自動判別をうまくしてくれないことが原因であり技術的には簡単に解決可能であるはずだと思っています。ですが、この挙動は何年たっても変わりません…。 私はもう自分で慣れちゃってるので何も意識せずに文字コードをコントロールしちゃうのであまり気にすらしていなかったのですが、改めてMicrosoftさんにもフィードバックして改善をお願いしたいと思いました。やります…。 もしも私がしらないだけでもっと簡単に都度対応ではなく対応できるワークアラウンドを知っている方がいたらコメント、チャット等で教えていただければと思います。 (追記)対処方法3 メモ帳で一度開いて上書きする(BOM付きにする) Twitterにて@hdk_2さんに以下教えてもらいました。 確か BOM ありだと大丈夫ですよね。メモ帳で保存し直すだけでも。 — Hideki EIRAKU (@hdk_2) 2018年8月27日 https://platform.twitter.com/widgets.js 教えてもらったとおり単にメモ帳でファイルを開いて何も編集せず「上書き保存」するだけで文字化けを回避できることを確認しました。なるほど！簡単ですね！ 確認するとメモ帳で保存すると必ずBOM付きで保存してくれるようです。そして、BOM付きであればきちんとExcelも文字化けせずにUTF-8のファイルの日本語を取り扱ってくれました。 メモ帳だと容量の大きなファイルを開くとかなり時間がかかったりメモリ消費が大きかったり、そもそも開けないケースもありますが、容量が相対的に小さいファイルであればこれが一番お手軽かもしれないですね。 @hdk_2さんありがとうございます！ 他にも方法ありましたら教えてくださいませ！ マイクロソフトさんに対処してもらおう！(投票のお願い) https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/14154036-automatically-recognize-unicode-csv-file-automatic この問題への対処を求める要望がすでに何年も前からUserVoiceに上がっています。投票が多くなれば対処される可能性も高くなりますので、この問題に遭遇してこのブログにたどり着いた方は是非上記リンクより投票(Vote)お願いします！

おそらく先日Microsoft Projectを追加インストールしたのが原因だと思うのですが、TeamsよりExcelが開けなくなってしまいました。 プロトコルへの関連付けをしないといけないです。 Excelもきちんと関連付けされていないことは認識しているようです。起動時に教えてくれます。 でも、ここで「はい」を押すと、自分で設定してねと言われます。 でも、[設定] > [システム]の中にはその項目はありません。(環境によります) 私の環境だと、[設定] > [アプリ]が設定の場所ですね。 「プロトコルごとに規定のアプリを選ぶ」を選択します。 すると、そもそも「MS-EXCEL」がありませんでした。 コントロールパネル側から見ても、プログラムにExcelが表示されません。 仕方がないので、Office 365 ProPlusを修復することにしました。 修復完了。 これで、きちんと「MS-EXCEL」プロトコルが登録され、Excelが起動するようになりました。 この結果からすると、おかしくなったら、あまり悩まずにとりあえず修復をかけてしまってそれでもうまく行かなかったら、そこからトラブルシューティングを開始するという手順が良さそうです。 プロトコルの関連付けをデスクトップ Office に切り替える - Office サポート

メモリまたはディスクの空き容量が不足しているため、ドキュメントを開いたり、保存したりできません。 不要なブックやプログラムを終了して、メモリの空き容量を増やしてください。 ディスクに保存されている不要なファイルを削除して、ディスクの空き容量を増やしてください。 こんなメッセージがある時から頻繁にでるようになってしまいました。特に - Outlookで貰ったメールについていた添付ファイルをそのまま開こうとした時 - ブラウザでダウンロードしたOfficeファイルを開こうとした時 に、このメッセージがでて開けず困っていました。はじめはOutlookだけで事象を確認していて、iPhoneアプリからは閲覧できたので、Outlookのダウンロード周りの中で何かファイルが壊れるようなバグが紛れ込んでしまったのかな？そのうち治るだろう…なんて思っていました。Outlookの添付ファイルを開く操作は以前からずっと問題なくできていたので。 でも、今日、ブラウザからのダウンロードしたファイルでも同様の症状となったので、Outlookのせいではないなと気が付きまして…。 結局、ダウンロードしたファイルがセキュリティ的に危ないから開けないようにOffice側でブロックしているのが原因だったようです。(マニアックな方向け：NTFS代替えストリームの中身を見て、開いて良いファイルかどうかの判別が入るように何処かでOffice側に変更入った模様です。) ファイルのプロパティを開いて、「ブロックの解除」をしてあげればファイルが開けるようになることを確認しました。 (追記)単に「ブロックの解除」だけではだめで、ファイル名を変更してあげる必要があるものもありました。規則性不明です。 でも、これだと、Outlookの添付ファイルをそのまま開くアクションはできないよなぁ…と思います。Office側のセキュリティ設定を緩めれば開けるようになりそうですがとりあえず回避策はわかったのでそのまま運用してみようと思ってます。

皆さんよくExcel上でのデータ操作はするだろうと思います。各種の情報を結合したり、編集結果をプログラムに流しこんだりなども。 私はExcel上でガリガリと手で操作をするのは極力避けています。その代わりにプログラムあるいはマクロを書きます。なぜそうしているのかというと結局Excel上で人が手で操作をすると時間もかかるし間違えるし、一度で済めばよいですが何度も繰り返し同じ作業をするはめになることが多いからです。プログラムを書いておけば何度でも同じプロセスを繰り返せますし、ミスや処理の追加があってもその箇所だけ修正、追加できます。 と、思っていたのですが、最近Power Queryの事を知りました。Power Queryを使えば複数のソースから情報を持ってきて目的に合うように加工してシートに収めることをGUIで簡単に行え、しかもそれを何度でも実行できます。例えば私が今回おこなった処理は以下の様なものです。 - 他のExcelファイルからシートを読み込む - 先頭の不要な行を削除する - 一意のIDとなる列を先頭列に移動する - 一意のIDとなる列の形式を数値にする - 一意のIDとなる列の重複を排除する 上記の操作を3つのExcelファイルに対して行い3つのシートに情報を格納しました。 このプロセスは非常に簡単に行なえ、ボタン一発で何度でも再実行可能になります。Power Queryいいですね。 今回はさらにそれぞれの情報を一意のIDを元に結合して集計しました。そこに対しては、Power Pivotを使ってGUI上でテ－ブル同士のリレーションシップを定義し、あとはピボットテーブルで簡単に集計できました。 また、抜粋した情報をまとめた別の表を作成したく、それにはVLOOKUP関数を使ったのですが(リレーションを元にフラットなピポッドテーブルを使って作れそうだったけど意図した形にならず断念)、最新のEXCELではセルの範囲指定(絶対参照とか相対参照とか)ではなくテーブル自体を範囲として指定したり、テーブル内の列を指定したりすることが出来るようになっており大変作業がはかどりました。最近といっても2007のころからの機能らしいですが。 というわけで、ここまで簡単に繰り返しに耐えられる形でのデータ操作ができるなら、もっと積極的にExcelを使うのもありだなといまさらながら思いました。

Managing Your Datacenter with Microsoft System Center Configuration Manager | Microsoft Ignite 2015 | Channel 9 IgniteのSCCMでのデータセンター管理に関するセッション。かなり良い内容でした。 SCCMをこのくらい素敵に使いこなせると色々なことが相当楽になるな～という感じです。技術的に実現させることはある程度簡単に可能でも運用に載せるのは難しいんですよね…。 DBに格納されているデータに対してデータセットだけ作っておいてあげればあとはExcelなどで簡単にレポートがかなり多くの人が作成可能である…というのはまさにその通り。 System Center製品群で多数集まったデータを素敵にレポーティングしてあげることで色々と可視化可能になる…というのは技術的に簡単にできるのですが中々広められていないところなので、このあたりも力を入れつつ、OMSのようなクラウドベースの方面にも手を伸ばさないとな…という感じです。 話がそれましたが、こちら、お勧めのIgniteセッションの1つです。

PowerShellからCSVファイルを作成し、それをExcelで開いて…という操作は結構需要があり、頻繁に行われるのではないかと思います。ちょっとしたはまりポイントがありますので、そのあたりのTipsを紹介しようと思います。 Export-Csv PowerShellの素敵な点の1つはオブジェクトを簡単にCSVに出力できるコマンドレットが標準で用意されているところだと思います。 ただし、日本語が含まれている場合に、そのまま何も考えずにExport-Csvを実行すると文字化けが発生してしまいます。 これは、Excelで読み込むときにエンコーディングの判定に失敗して文字化けしている…という事ではなく、PowerShellでファイルを吐き出した時に????に変換されてしまっています。なので、出力時にきちんとエンコーディングを指定してあげる必要があります。 どのエンコーディングを使うか、というのは人によって意見が違うかもしれませんが、私はいつもUTF-8を使います。これであればきちんと日本語も出力され、Excelで開いた時にも文字化けもなく意図した通りに開きます。 ExcelでShift-JIS以外の文字コードでは文字化けが発生する問題 実は先ほどの例ではうまくいっていますが、ExcelはCSVファイルはShift-JISにしておかないと文字化けすることが多い困ったやつです。回避策はいくつかあります。以下のあたりが参考になります。 ExcelでUTF-8のCSVを開く方法 (CodeZine編集部ブログ) ExcelでUTF-8エンコーディングされたCSVファイルを開く方法 - 大人になったら肺呼吸 先ほどの例ではBOM付きのUTF-8であったためうまく表示されたわけです。Stirlingでファイルを確認するとファイルの先頭に「EFBBBF」が不可されていることがわかります。 スクリプト内でファイルに出力する場合 Export-Csvを使う場合には-Encodingオプションを付ければよいことはわかりましたが、foreachの中などで、自分で文字列をファイルに出力したいようなときには残念ながら別の問題が発生します。 このようにA列にすべて表示されてしまいます。(※文字化けをしていないのはExcel2010だからなのではないかと思います。) これも回避策としてはいくつかあると思いますがShift-JISで出力するにはAdd-Contentコマンドレットで-EncodingにStringを指定することができます。 UTF-8でBOM付きにしようと思ったら、先にファイルを作成しておくと良いようです。

大量に何かを操作するときには、やはりプログラムを書くなり、WSH、PowerShellなどでスクリプトを書くなりしてしまうのがいいです。でも、プログラムやスクリプトは敷居が高くて･･･という人も多いと思います。その考え自体は直したほうがいいと思いますが、気持ちはわかります。でも、だからといって、GUIで全部操作･･･というのは現実的に無理な規模があります。何とか手作業でがんばれるのはユーザー数100人前後まででしょうか。 で、そんな人にお勧めできるのがバッチファイルをExcelで作成する方法です。「Excelって表計算ソフトじゃないの?」と思うと思いますが、うまく使ってあげればコマンドを簡単に作成してあげることができます。 例えばユーザーを大量に作成する必要があるとして、そのユーザーのリストをExcelファイルでお客さんに作成してもらう、ということはありがちです。そうしたら、その1行が1ユーザーに対応しているわけですから、コマンドを作ってしまえばいいんです。/p pユーザーを作成するコマンドは別途調べないといけませんが、windowsであればnet userコマンド、ADであればdsaddコマンドあたりでいいでしょう。 Excelで相対参照とamp;での文字連結を使ってあげるだけでもこのくらいであれば非常に簡単にできちゃいます。サンプルは。 これをプログラムでやろうと思ったら、ファイルをオープンして、CSV形式を解釈して、ADに接続して･･･と結構手間がかかります。単純にコマンドを実行するだけでできることをやるのであればExcelを有効活用すると視野が広がると思います。 もちろん限界はありますが、プログラミングが苦手な人でもGUIで操作する以外の方法を考えるいいきっかけにはなるのではないでしょうか。

yaneuraoさんのところでExcelで行列を追加、削除してもきちんと相対的にセルを指定する方法が紹介されています。 普通に相対参照で関数を作成すると、あとから行、列を削除、挿入した際に山荘の関係がくずれてしまい毎回関数を貼り付けなおすようなことを私もしていましたが、Offset関数を使うことで無駄な作業をなくすことが出来ます。なかなか便利です。 http://d.hatena.ne.jp/yaneurao/20060106