サクサク読めて、アプリ限定の機能も多数！
トップへ戻る
ドラクエ3
officetanaka.net
先日、VBAのセミナーを受講された山内さんと、セミナーが終わってから雑談していたのですが、その中でPower Queryの話題になりました。実務でPower Queryを便利に使っているのですが、ときどき、あるいは定期的に、対象のファイル名や、保存されているフォルダ名などが変更されると。それを毎回手動で修正しなければならないので、なんとかなりませんかね？みたいな。これ、実現可能だということは知っていました。パラメータクエリという仕組みを使えば、セルの中に入力してある値によって、クエリの内容を自動的に変化させることができます。対象のファイル名やパスが変わったら、セルの内容を変えることで対応できるんです。知ってはいましたが、この手のやり方は、セミナーでは解説してきませんでした。理由は「そのためには、自分でM言語を書き換える」必要があるからです。私は、「ExcelユーザーはM言語まで手を出さない
ここで解説する IFS関数 と SWITCH関数 は、Microsoft 365からインストールするProPlusで使えます。あるいは、永続ライセンス版でしたら、Excel 2019以降で使用可能です。 本稿の内容や、使い方のサンプルなどは、下記の動画でも解説しています。 また、本稿の最後に、追記もしてありますので、ぜひご覧ください。 条件分岐の動作 まず、昔からある IF関数 の動きを確認しましょう。 ○が1だったら、"田中"を表示します。そうでなかったら"×"を表示します。"田中"が表示されるのは、○が1のときだけです。"×"が表示されるのは、○が1ではないときです。2だろうが1024だろうが、"Excel"だろうが空欄だろうが何でも、とにかく1でなければ、すべて"×"です。これが IF関数 の動きです。このように「■なのか、そうでないのか」という値を「二値(にち)」と呼びます。最もよ
InsiderのExcelに、LAMBDA関数が追加されました。まだ、正式に実装されたわけではありませんが、食いつきの早いWebサイトでも紹介されています。そうした、"追加された"という情報をいち早く伝えているWebページなどをご覧になって「何やら、すごい関数が追加されたんだなぁ」と期待に胸を震わせている方がいらっしゃるかもしれませんので、このLAMBDA関数って、どういう関数なのかを解説します。ちなみに「ラムダ関数」と読みます。 セルの中で使う関数ではありません まず、初っぱなから、みなさんの期待を裏切るような事実をお伝えしますが、このLAMBDA関数は、SUM関数やVLOOKUP関数のようにセルの中に入力して使う関数ではありません。まずは、そこです。まぁ、実際には、セルの中に直接LAMBDA関数を入力しても、動くっちゃ動きますが、そんな使い方をする人は皆無でしょう。なぜなら、まったくメ
ここで解説する FILTER関数 は、Office InsiderのProPlusに追加された関数です。執筆時点(2019年8月)で、製品版のExcelにはまだ実装されていません。ただし、Microsoftがこの関数を実装すると公にアナウンスしたのは、2018年の11月です。おそらく現在は、さまざまな動作検証をしたり、細かい修正などを行っているところでしょうけど、近い将来Excelに実装されるのは間違いありません。そうなったら、もうExcelの使い方が大きく変わります。そんな画期的な関数です。なお、Office Insiderに関しては、ご自身でググってください。また、この関数では「スピル」という機能が使われています。スピルに関しては「Excel 2016レビュー[Excelの使い方が激変する「スピル」]」をご覧ください。 FILTER関数 リストに対して条件を指定して絞り込んだ結果を返す
「VLOOKUP関数と同じことをする」で解説したように、「取得と変換」のマージ機能を使うと、VLOOKUP関数と同じようなことができます。しかし、マージ機能は決してVLOOKUP関数の代用として実装されているのではありません。「マージ＝VLOOKUP」とは思わないでください。ただ、似たようなことも実現できるというだけです。そもそもデータベースの世界では、複数のテーブルを結合(マージ)することが多いです。それがマージ機能です。実を言えばVLOOKUP関数だって、立派なデータベース機能であり、やっていることは「別のテーブルからデータを取り出して結合している」ようなものです。ただExcelは、純粋なデータベースソフトではなく、表計算ソフトですから、やり方や考え方や見せ方などが表計算的なだけです。ここでは「取得と変換」のマージ機能で、どんなことができるのかを解説します。ただ、はじめにお断りしておき
ListObjectでの特定 Range("A1").ListObjectがテーブル全体を表します。テーブル内の特定部位は、次のように指定します。 見出しを含むテーブル全体 Range("A1").ListObject.Range 今までセルを操作するマクロに慣れている方が、もっとも戸惑うのがこのRangeです。よろしいですか、ここは頑張って"考え方"を変えてください。セルを指し示すとき「Range("A1")」のように使うRangeと、ここで使うRangeはまったく意味が異なります。Range("A1").ListObject.RangeのRangeは、テーブル内の全セルを表します。したがって、全セル内の"何番目のセル"というのを、次のように表します。 Range("A1").ListObject.Range(3) Range("A1").ListObject.Range(6) ええっ！
ややこしい！ まずは、衝撃的なお話から。Office 2016というのは、大きく分けて3種類あります。 ひとつは昔ながらの「DVDなどからインストール」する種類のOffice 2016です。企業内で使われている"ボリュームライセンス"というのも、このタイプの場合があります。ふたつめは「Office 365からインストール」するタイプのOffice 2016です。Office 365というのは、Microsoftが提供しているクラウドサービスの名称で、OneDriveが使えたり、メールアカウントを作れたり、SharePoitやFlowを使えたりします。そして、ExcelやWordなどの、いわゆる「オフィス製品」をインストールして使うこともできます。 さあ、ここからがややこしいのですけど。まず、Office 2016というのは、発売以来毎月機能拡張やバグの修正などが行われています。毎月です。よ
セルに数式が入力されていて。その数式をセルごとコピーするのではなく、数式の計算結果だけを別のセルに代入することがよくあります。もう、実務では超"あるある"の話です。これ、もし手動でやるのなら、おそらく日本国民全員が「形式を選択して貼り付け」の、いわゆる"値貼り付け"を実行します。ああ、もちろん、[貼り付けオプション]の「値」を実行するのも、やってることは同じですよ。 さて、この"値貼り付け"が遅いってご存じでしたか？実務で何回も値貼り付けを繰り返していたら、そりゃマクロも遅くなりますよ。やってみましょうか。 セル範囲A1:A500に適当な数値を入れました。隣のセル範囲B1:B500に「=A1*2」的な数式を入れました。このセル範囲B1:B500の計算結果(値)を、1つずつ隣のC列に"値貼り付け"していきます。本当は、このように連続した範囲でしたら、複数のセルを一気にやるんですけど、今回はそ
本稿は、ProPlusに関する内容です。永続ライセンス版のExcel 2016やExcel 2019には実装されていません。このへんの意味が分からない人は、ぜひ下の動画をご覧ください。最新版のExcelは、2019ではなくProPlusだという件を解説しています。なお、再生すると音が出ますので、会社や電車の中でご覧になる場合は、ご注意ください。 また、本稿の内容を動画でも解説しています。動画でご覧になりたい方はどうぞ。Youtubeでは、ほかにもたくさんの動画を公開しています。チャンネル登録をお忘れなく！ Office TANAKAチャンネル 表計算ソフト暗黙のルール Excelに限らず、今までの表計算ソフトでは、暗黙のルールがありました。それは「値や数式を入力したセルにだけ結果を表示して、何も入力されていないセルに、プログラムが勝手に何かを代入しない」ということです。 たとえば上図のよう
実務では、特定のシートだけを別ブックとして保存することが多いです。たとえば、Sheet1とSheet2があったとして。このSheet2の名前を"田中"に変え、"田中"シートだけを「田中.xlsx」として保存する。みたいな。 このとき、ほとんどの方が次のように考えます。 まずSheet2の名前を"田中"に変える 新しいブックを挿入する [田中]シートを、挿入した新しいブックにコピーする 新しいブックで、"田中"以外のシートを削除する 挿入したブックを"田中.xlsx"で保存する この考え方でマクロを作ってみましょう。こうなります。 Sub Sample1() Dim ws As Worksheet Sheets("Sheet2").Name = "田中" ''1. Workbooks.Add ''2. ThisWorkbook.Sheets("田中").Copy After:=ActiveW
これ、ほとんど知られていないのですけど、Excel 2016は"みんな大好き"VLOOKUP関数とMATCH関数の計算速度が、約400倍速くなりました。実際に速度を計測してみました。もうね、笑っちゃいますw 速度計測は、次のようにしました。まず、A列とB列に元データを作りました。10万件です。 この、セル範囲A2:B100001に対してVLOOKUPします。D列には、VLOOKUP関数で調べる"A-100000"を1万個入れてあります。ワークシートの再計算を"手動"にしてから、隣のE列(セル範囲E2:E10001)に「=VLOOKUP(D2,$A$2:$B$100001,2,FALSE)」的な数式を1万個入れます。もちろん、VLOOKUP関数の第1引数は、D2・D3・D4・・・となるようにしています。ちなみに、VLOOKUP関数の第4引数に"FALSE"を指定すると"完全一致"で表引きしま
私のセミナーを受講された方から質問されました。VBAでCSVを読み込むときの話です。 CSVを読み込むとき、Line Inputを使っているんですけど、CSVの行数が多いと時間がかかります。ネットで調べたら、Line InputよりQueryTableを使った方が速いって書いてあったんですけど、どうなんでしょう？ こうした誤解をしている方がとても多いので、ここに書いておきます。 まず、読み込むCSVをご覧ください。 こんな感じのCSVです。これ100,000行あります。まずはLine Inputで読み込んでみましょう。ここでは、次のコードでやってみます。 Sub Macro1() Dim buf As String, A As Variant, i As Long, j As Long Open "C:\Data\Work\sample.csv" For Input As #1 Do Un
Excel VBA Tips マクロとVBAの、さまざまなテクニックなどをご紹介します。ただ「こうすれば、こうできる」だけでなく、なぜそうなるのか、あるいは、してはいけないのか。ポイントや注意点なども詳細にご紹介します。何しろ私、自分で使うだけでなく、"教える"プロですから。ただね、ひとこと言わせていただければ、大事なことは「どうすればできるか」ではなく「なぜそうするか」なんですよ。 詳しくはこちら テーブルの操作 テーブルは、これからのExcelユーザーにとって避けては通れない機能です。しかたありません、Microsoftがそう決めたのですから。これからはテーブルでいくと。テーブルは表を装飾する機能ではありません。テーブルになるとそこは、もう今までのワークシートではありません。Excelが特別に管理するデータベース領域です。当然VBAからの操作も、今までとは考え方や方法が異なります。 詳
VBAでは、うまくいきそうに見えて、実は失敗するケースがあります。たとえば、アクティブシートがSheet1のとき、次のコードを実行するとSheet2のセルB3が選択されます。あ、もちろん、Sheet2は存在するとします。 Sub Sample1() Sheets("Sheet2").Select Range("B3").Select End Sub また、アクティブシートではないシートのセルを、直接操作することもできます。アクティブシートがSheet2のとき、次のコードを実行すると、アクティブシートは移動せずに、Sheet1のセルC4に数値を入力できます。、 Sub Sample2() Sheets("Sheet1").Range("C4") = 123 End Sub Sample2のように、別シートのセルを直接操作できるのなら、次のように書いたとしても、正常に動作するような気がします。
本稿執筆時点で、最も最新のExcelは2016です。 前バージョンのExcel 2013に比べて、機能的にはほとんど変わっていません。ただひとつ、見た目的に大きく変化したところがあります。ブックで使用される標準のフォントが、今までの「MS Pゴシック」から「游ゴシック」になりました。 ちょっと「MS Pゴシック」と「游ゴシック」の違いをご紹介しましょう。 どちらが見やすいかは、それぞれの好みによりますので、何とも言えません。ただ、一般的に人は、変化を嫌います。今はまだ、それほどExcel 2016が普及していませんが、今後多くの現場にExcel 2016が導入されるようになると「何だよ～勝手にフォント変えるなよ～元に戻せよ～何やってんだよMS」みたいなことになるのは、火を見るよりも明らかです。なので、標準のフォントを「MS Pゴシック」にする方法をご紹介します。 ちなみに、たとえばExce
本稿で紹介する「テキストファイルの操作」は、UTF-8形式のテキストファイルを対象にしています。Shift-JIS形式のファイルを扱うときは、下記ページをご覧ください。 テキストファイルを操作する 正確には「UTF-8形式のファイル」ってのも、ちょっと変な表現なんですけどね。ファイルの形式じゃなくて、そこで使われている文字コードが"UTF-8"だよってことなんですが。まぁ、細かいことはいいとして。ここでは面倒なので「UTF-8形式のファイル」「Shift-JIS形式のファイル」という呼び方をします。 UTF-8の文字コードが記録されたテキストファイルを、上記「テキストファイルを操作する」で解説した方法で読み込むと、望んだ結果になりません。ちょっと、やってみましょう。 UTF-8の文字コードで書かれたデータを読み込むときは、ADO(ActiveX Data Objects)という仕組みを使い
本稿で紹介する「テキストファイルの操作」は、UTF-8形式のテキストファイルを対象にしています。Shift-JIS形式のファイルを扱うときは、下記ページをご覧ください。 テキストファイルを操作する 正確には「UTF-8形式のファイル」ってのも、ちょっと変な表現なんですけどね。ファイルの形式じゃなくて、そこで使われている文字コードが"UTF-8"だよってことなんですが。まぁ、細かいことはいいとして。ここでは面倒なので「UTF-8形式のファイル」「Shift-JIS形式のファイル」という呼び方をします。 UTF-8の文字コードでファイルに書き込むには、次のようにします。 Sub Sample1() Dim Target As String Target = "D:\Work\Sample.txt" With CreateObject("ADODB.Stream") .Charset = "UT
Sub Sample() With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(3) .HasDataLabels = True .DataLabels.ShowSeriesName = True .DataLabels.ShowCategoryName = True .DataLabels.ShowValue = True End With End Sub データラベルに複数の内容を表示するとき、それぞれの内容を区切る記号を指定できます。区切り記号は、DataLabelsコレクションのSeparatorプロパティに文字列形式で指定します。次のコードは、データラベルをスペースで区切ります。 Sub Sample() With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(3)
下図は超簡単な出納帳です。前日の残高に入金を加えて、出金を引いたのが新しい残高です。 計算式も簡単ですね。よくExcel入門とかで「お小遣い帳を作ってみよう」みたいなネタで登場します。まぁ、Excelの参照式を理解するには悪くないテーマだと思いますが、実際に使っていると困ったことがあります。たとえば、すでに入力したデータの途中に、セルを挿入するとどうなるでしょう。 挿入したいセル範囲を選択します。 Ctrl+Shift+[＋]キーを押します。 挿入したセルにデータを入力します。ところが、下に移動したセルD5の数式は、よく見るとおかしいです。 こうした参照式は、セルの挿入や削除に弱いです。逆に、参照しているセルを削除すると・・・ エラーになっちゃいます。 表の途中でセルを挿入したり削除しても、常に同じ位置のセルを参照するには、少し工夫が必要です。INDIRECT関数でやる手もありますが、ここ
たとえば、条件に一致するセルを、次々と処理するのは簡単です。たとえば、選択したセル範囲内で、入力されている数値が100より大きかったら文字色を赤にするとします。次のようなコードで可能ですね。 Sub Sample1() Dim c As Range For Each c In Selection If c > 100 Then c.Font.ColorIndex = 3 Next c End Sub これは簡単ですね。 では、条件に一致したセルを選択するには、どうしたらいいでしょう。 セルを選択するには、たとえば「Range("A1").Select」とします。連続していない複数のセルを選択するには「Range("A1,B2").Select」のように、セルのアドレスをカンマで区切って指定します。先のコードでは、条件に一致したセルをすぐ処理していますが、アドレスを「○○,△△,××,…」の
VBAのコードで、ときどき「:=」という記号を見かけます。たとえば次のようなコードです。 Sub Sample1() Workbooks.Open Filename:="Book1.xls" End Sub これは、Book1.xlsを開くマクロですが、これを次のように書くとエラーになります。 Sub Sample2() Workbooks.Open Filename = "Book1.xls" End Sub 上記のコードは、引数Filenameに"Book1.xlsx"という文字列を設定しているので、何となく正しいようにも思えます。だって、変数に値を代入するときは「=」を使うのですから。 Sub Sample3() Dim Target As String Target = "Book1.xlsx" End Sub 値を代入(設定)するとき「=」と「:=」の違いは何なのでしょう。 「:
変数に値を入れるには「=」記号を使います。次のマクロは、文字列型の変数tmpに"tanaka"という文字列を格納します。 Sub Sample13() Dim tmp As String tmp = "tanaka" MsgBox tmp End Sub このように、変数に値を格納するときは「変数名 = 値」とします。これは、右辺を左辺に代入するという意味で「変数名 ← 値」のようなイメージです。 ところが、ときおり「Set 変数名 = 値」のように、Setという命令を使って変数に"何か"を代入しているコードがあります。 Sub Sample14() Dim ws As Worksheet Set ws = Sheets("Sheet1") MsgBox ws.Name End Sub Set ws = Sheets("Sheet1") は、変数wsにシート[Sheet1]を代入する命令で
主な実行時エラーです。気がついたら追加します。なお、エラーメッセージは、Excelの(というかVBAの)バージョンによって変わることがあります。
セルに数式を入力するときは、先頭に「=」を付けます。 このように、セルに数式を入力すると、セルには数式の計算結果が表示されます。これが表計算ソフトです。ところが、ヒトは贅沢な生き物です。時には、次のようなわがままを言うこともあります。 セルには数式を文字列として入れたいんだ。 たとえば「2+3-1」とかさ。 で、別のセルに、その数式を計算した結果を表示したいんだよね。 数式を直接入れるんじゃなくて。 あ、もちろん「2+3-1」の方を変更したら、ちゃんと再計算してくれないと困るよ。 つまり、こういうことです。 上図のように、任意のセル(ここではセルB2)に文字列で「2+3-1」などと入力します。で、たとえば右隣のセルに、セルB2に入力した「2+3-1」を計算した結果の「4」を表示したいと。 もちろん、セルB2を変更したら、ちゃんとセルC2も自動的に再計算されて欲しいと。 セルに数式を入力する
下図のような表があったとします。本当はもっと、横長の表をイメージしてください。ここでは画面の都合上「4月」と「5月」のデータしかありませんが、実際はこんな感じで12ヶ月分あるような。 このような表で、「前期」「今期」「伸び率」の3項目を常に表示させるのではなく、「各月の伸び率だけ表示させたい」というようなとき、よくExcelの解説本では、アウトライン機能が紹介されます。この表にアウトラインを設定すると、次のようになります。 確かにアウトラインを設定すれば、下図のように、特定の行や列を折りたたむことで、表示を切り替えることが可能です。 アウトラインを手動で設定するには、Excel 2003までは[データ]-[グループとアウトラインの設定]の[グループ化]や[グループ解除]を実行します。Excel 2007以降なら[データ]タブ[アウトライン]グループの[グループ化]ボタンや[グループ解除]ボ
よく使う型 最もよく使われる型は、なんといっても長整数型(Long)と文字列型(String)でしょう。整数を格納する型は、長整数型(Long)のほかにInteger(整数型)もありますが、Integer(整数型)は最大で32,767までしか入りませんから、特別な事情がある場合を除いて、整数を入れる変数は長整数型(Long)で宣言すればいいでしょう。また、たとえば月を表す数値(1～12)のように、非常に小さい整数を格納する変数としてバイト型(Byte)を使っているケースをときおり見かけますが、これは相応しくありません。バイト型(Byte)は、バイナリデータという特別な値を格納するために用意された型です。もちろん、単純な数値を格納することも可能ですが、すごく変です。 整数を入れる変数 → 長整数型(Long) 文字列を入れる変数 → 文字列型(String) と覚えておきましょう。ほとんどの
なんか・・・最初に「xl*」を指定してるんなら、後の「xlsm」とか「xls」とかは不要じゃん、と思いますし、「htm」とか「html」を含めるなら、もっと利用頻度の高い「csv」も入れとけよって。ちなみに、上図のフォルダには「Sample.csv」が保存されていますが、アイコンは表示されていませんよね。 FindFileメソッドは、同じように[ファイルを開く]ダイアログボックスを開きますが[開く]ボタンをクリックすると、ただちに選択したファイルを開きます。[キャンセル]ボタンがクリックされたときは何もしませんので、そのための処理を記述する必要はありません。ちなみに、選択したファイルが正常に開かれると、FindFileメソッドはTrueを返し、[キャンセル]ボタンがクリックされるとFalseを返します。また、FindFileメソッドで表示されるダイアログボックスでは、標準で複数のファイルを
大量のデータを絞り込むには、オートフィルタが便利です。ここでは、VBAからオートフィルタを使い倒すテクニックをご紹介します。なお、ボリュームがありますので、以下の項目にページを分けて解説します。なお、ここで解説するオートフィルタは、通常のワークシートに設定されているとします。テーブルでオートフィルタを操作するときは、また別の考え方が必要です。VBAでテーブルを扱う方法に関しては「VBAでテーブルの操作」をご覧ください。 書き方の基本 文字列で絞り込む 数値で絞り込む 日付で絞り込む 色で絞り込む 作業列で絞り込む 絞り込んだ結果をコピーする (←このページ) 絞り込んだ結果を集計する 絞り込んだ結果の行を操作する オートフィルタの状況を判定する 数式のエラーで絞り込む 既存の別シートへコピーする 下図のような表を例に解説します。 表はアクティブシートにあります。この表を「A列が"田中"であ
Excel 2007から、文字やセルの背景色などに使用できる色が増えました。リボンのボタンをクリックすると、今までにない淡い中間色を選択できます。 ここ、よく見ると「テーマの色」と書かれています。実は、Excel 2007から「テーマ」という機能が追加されました。「テーマ」は、いわば"見た目に関するテンプレート"のようなものです。ブックに適用するテーマを変更すると「テーマの色」も変わります。 テーマを切り替えるには、[ページレイアウト]タブ左端にある[テーマ]ボタンをクリックします。 標準は、左上の[Office]です。テーマを変更すると、「テーマの色」は次のように変わります。 各テーマの配色は、[ページレイアウト]タブ[テーマ]グループの[配色]ボタンをクリックすると表示されます。 このテーマの色を使う操作は、マクロ記録で次のように記録されます。 Sub Macro1() With Se
次のページ
このページを最初にブックマークしてみませんか？
『Office TANAKA』の新着エントリーを見る
j次のブックマーク
k前のブックマーク
lあとで読む
eコメント一覧を開く
oページを開く
