サクサク読めて、アプリ限定の機能も多数!
トップへ戻る
猫
officetanaka.net
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
UserFormで入力(または選択)された情報を、別のUserFormに反映させる方法です。これには、2つのやり方があります。なお、ここでは、UserForm1のTextBoxに入力された文字列を、UserForm2のLabelに表示するという動作を例にします。 【UserForm1】 【UserForm2】 直接別のコントロールに設定する 難しく考えることはありません。UserForm1のコードから、UserForm2のコントロールを直接操作してやればいいんです。 【UserForm1】のコード Private Sub CommandButton1_Click() ''[UserForm2を開く]ボタン UserForm2.Label1.Caption = Me.TextBox1.Text UserForm2.Show End Sub 【UserForm2】のコード Private Su
VBAの関数 Home Excel VBA VBAの関数一覧 Abs Array Asc AscB AscW Atn CallByName CBool CByte CCur CDate CDbl CDec Choose Chr、ChrB、ChrW CInt CLng Cos CreateObject CSng CStr CurDir CVar CVDate CVErr Date DateAdd DateDiff DatePart DateSerial DateValue Day DDB Dir DoEvents EnViron EOF Error Exp FileAttr FileDateTime FileLen Filter Fix Format FormatCurrency FormatDateTime FormatNumber FormatPercent FreeFile FV
次のページ
このページを最初にブックマークしてみませんか?
『Office TANAKA』の新着エントリーを見る
j次のブックマーク
k前のブックマーク
lあとで読む
eコメント一覧を開く
oページを開く