タグ別アーカイブ: エクセル
XLOOKUP関数_ドリル
意外にも知られていないエクセルの便利機能・便利技~ガッツの日記 第13回
【エクセルはあまりに機能が多すぎるので、知られていない便利機能・便利技があります】
仕事柄、色々なところで色々なエクセルの活用の仕方に出会います
エクセルの活用者と話していて気づくのは、意外にも知られていない機能や便利技が結構あることです
今回の記事では「便利」だけど意外に「あまり知られていない」機能や便利技について、私の独断と偏見でピックアップして紹介します
計算の手動化
ワークシート内に数式を膨大に使用している場合、セルに入力するたびに膨大な計算が走ります
ですので、ワークシートが所謂「重たい」という状態になります
この場合、解決方法としては本来はデータベース等を導入することが考えられます
ただすぐに導入できる方法もあります
それが「計算の手動化」です
文字通り、数式の計算を止めます
そして、必要な時だけ計算します
![](https://analytic-vba.com/wp/wp-content/uploads/2024/07/image-3.png)
数式タブには「計算方法の設定」というメニューがあり、こちらで数式の計算を止めることができます
ちなみに、通常は「自動」になっています
そして必要な時だけ「F9」ボタンを押せばファイル内(Shiftを押しながらだとシート内)の関数が計算されます
いや、入力するたびに計算結果が見たいんです、もしくは数式の計算をシート内に絞っても計算が遅いんです、という場合もあると思います
この場合は、VBAを使う方法があります
詳細はこの記事では書きませんが、VBAコードを入力というイベントに応じて動かす方法があります(イベントプロシージャーと言います)
![](https://analytic-vba.com/wp/wp-content/uploads/2024/07/image-4.png)
イベントプロシージャーは、該当シートで右クリックすると表示される「コードの表示」で設定できます
次に開いた画面では、下の画像のように画面上の2つの欄を「Worksheet」「Change」に設定します
![](https://analytic-vba.com/wp/wp-content/uploads/2024/07/image-5-644x102.png)
これで、入力したセルの行位置及び列位置を取得できます
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRow As Long
Dim myCol As Long
myRow = Target.Row ←行位置
myCol = Target.Column ←列位置
下の画像の事例では「手動で計算」の設定のままC列に入力した場合、1つ右のセルだけ計算されます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/07/a2f24bffd096340c90b169ec2efd2b1e.gif)
実際のVBAコードは次のようになります
If myCol = 3 Then
ActiveSheet.Cells(myRow, myCol + 1).Calculate
End If
入力位置が3列目(C列)だった場合、該当シートの入力位置がの一つ右を再計算(Calculate)
するわけです
これであれば、入力位置の行だけを計算できますので、かなり計算は軽くなります
テーブル機能
この機能を知ったのは恐らく5年ぐらい前だと思いますが、意外と浸透していないという実感があります
データが集まった表をテーブルにすると、何かと表内のデータを便利に扱えるようになります
下の画像では、1つのセルに関数を入力したことで、全ての行に関数が反映されています
![](https://analytic-vba.com/wp/wp-content/uploads/2024/07/e065a3155328ead2005eda4668ba5783.gif)
つまり、データを扱う単位がセルではなく行単位になっています
テーブル機能には、上記のような便利な機能が満載なのです
この「満載」を具体的な一言で言い換えると、位置から名前への変換ということかもしれません
従来、エクセルを使おうとしたらセル単位という縛りの中でしか動けません
A列の5行目、C列の10行目といった感じでセルの位置をいちいち指定するしかありません
例えば、以下の画像のB列の合計を計算しようとしたら、マウスを動かしてC列の該当範囲を選択するしかありません
![](https://analytic-vba.com/wp/wp-content/uploads/2024/07/image-7-644x317.png)
しかも、C列の範囲が拡大したら選択し直す必要があります
ところが、テーブル機能を使えば名前でセル位置を指定できます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/07/image-8-644x276.png)
テーブル機能では、作成したテーブルに名前を付けることができ、テーブル内の列名も自動的に活用できるからです
実は列名の指定もワンクリックでできます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/07/d3dcac97a92e5ec8c17f53064a16b9c9.gif)
上の画像のように、テーブルの該当「列名」の上にカーソルを置き、すっと上に少し動かすと、下向きの矢印がでます
これでテーブルの列名が指定できます
テーブルの列名で指定すれば、列を移動しようが、行の範囲が拡大しようが適切な範囲を合計できます
このようにテーブル機能は便利な機能が満載ですが、表をテーブルにするのは「Ctrl+T」のショートカットキー一つだけで行えます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/07/2c63c216d9fd9bb6666e4dc1d11d1e9c.gif)
とにかく簡単にテーブル機能は活用できますので、ぜひ今日から使用してみてください(→詳細記事)
表をテーブルにしたら、テーブルに名前を付けるのもお忘れなく!
![](https://analytic-vba.com/wp/wp-content/uploads/2024/07/image-9.png)
最後に、もう一点だけ付け加えさせてください
表をテーブルにするとMicrosoft のメニュー自体をうまく扱えるようになるという点です
これは具体的にはどういうことかというと、Microsoft のPower Platformではエクセルのデータをテーブルで使用するように設計されているからです
Power Appsなんかもテーブルからアプリがワンクリックで作れるようになっています→関連記事
![](https://analytic-vba.com/wp/wp-content/uploads/2024/07/image-10-644x239.png)
ぜひテーブル機能は使いこなしていきましょう!
クエリの関数化
こちらはPower Queryの活用の仕方の話です
関数というと、SUM関数とかVLOOKUP関数を思い浮かべる方が多いと思います
数学でいうと、Xを使った(X+1)x(X+2)などの公式が関数と言えます
Xの値が何になっても一度作成した関数を使いまわして計算が行えます
クエリの関数化とは、一度作成したクエリをXを絡める関数にして使いまわすという意味です
Power Queryは大分、浸透したと思いますが、こちらのクエリの関数化はまだ知名度はいまいちという感じです
どういう場面で使うかというと、一番使えるのがシート別もしくはファイル別にデータが散らばっているケースです
例えば、以下のようにシート別にデータが散らばっていたとします
![](https://analytic-vba.com/wp/wp-content/uploads/2024/07/image-12.png)
しかも、欲しいデータの上に無駄なデータがあります
![](https://analytic-vba.com/wp/wp-content/uploads/2024/07/image-13.png)
これがもし、100シートあったとしたらデータをまとめあげる作業はどう行いますか?
手動であればお手上げだと思います
ところが、
クエリの関数化であれば1シートに対して行った処理を、ほぼワンクリックで複数シートに使いまわすことにより、簡単にデータの一元化が行えます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/07/image-19.png)
詳細についてはこちらの記事(→クエリの関数化)を参照して頂くとして、今回の記事では概念だけ解説いたします
![](https://analytic-vba.com/wp/wp-content/uploads/2024/07/image-14.png)
上の画像は1シートからデータを取得し、無駄な2行を取り除いてテーブルを出力するクエリのソースコードです
実は黄色の箇所に「A」というシート名が入っています
![](https://analytic-vba.com/wp/wp-content/uploads/2024/07/image-15.png)
つまり、ファイルの中のシートを取得した中からAシートを処理しているという訳です
この黄色の箇所を数学の関数でいうXに代えるわけです
以下の画像は関数化したクエリの一部です
![](https://analytic-vba.com/wp/wp-content/uploads/2024/07/image-16.png)
このXにどういう風に各シートを連続的に割り当てるかというと、このソースステップの列を使用します
![](https://analytic-vba.com/wp/wp-content/uploads/2024/07/image-17-644x184.png)
今回の事例では、実際にはItem列にある各シート名をフィルターして使用します
![](https://analytic-vba.com/wp/wp-content/uploads/2024/07/image-18.png)
これでシート別などにデータが分散している問題をVBAなしで一気に解決できます
フリーアドイン
フリーアドイン機能もあまり知られていないと思います
![](https://analytic-vba.com/wp/wp-content/uploads/2024/07/image-20.png)
スマホの便利アプリを利用するような形で、外部から便利機能を補って使用できます
こちらについては、アドインが沢山あるので「Formula Forge」というアプリの概要だけ今回は紹介します
例えば、こんな複雑なIF関数があったとします
![](https://analytic-vba.com/wp/wp-content/uploads/2024/07/image-21.png)
読者の皆さんの中には「こんなの序の口だよ」と思われた方もいらっしゃると思います
Formula Forgeを使うと複雑な数式でも図解を一瞬でしてくれます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/07/image-22.png)
前任者の複雑なエクセルを引きついで苦しんでいる方にはおすすめのアドインです
<まとめ>
今回は意外と知られていない便利機能・便利技を大きく分けて4種類を紹介しました
とにかく知っているか知っていないかでこの辺りは差が大きく出ますのでぜひ押さえておいて欲しいところです
ところで、
私が思うに、エクセルの機能を最大限使いこなすために一番大事なことは「エクセルの限界」を知ることだと思います
どうしてもエクセルの方が相対的に劣る機能があります
例えば、エクセルは表計算ソフトなので大量のデータを扱うのはあまり得意でないですし、Power Platformなどかなり便利なクラウド機能も出てきています
ですので、最終的にはエクセルの機能を深堀するだけでなく「正しい脱エクセル」も意識する必要があると思っています
この点はまた別途詳しく書きたいと思います
フォルダ内の最新ファイルのみを処理して出力するクエリを作成する
【取得するファイルを自動で可変にする、究極のPower Query術です!】
この記事を見ている読者の方の中には、毎日システムから出力されるファイルを処理しているという方もいらっしゃると思います
この場合、Power Queryで出力ファイルを処理する場合は、既存のクエリのデータソースを変更するか、新規にクエリを作成するしかありません
実は、フォルダ取得のクエリ技術を利用すれば驚くほど簡単に「常に最新」のファイルを取得できます
フォルダ取得
下の画像のフォルダには拡張子が「xlsx」のファイルと「csv」のファイルが1つづつ格納されています
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-81.png)
こちらのフォルダを取得先にしてクエリを作成してみます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-82-644x271.png)
上の画像では、3行目のcsvのファイルに対応する箇所がエラーになっています
ソースステップでフィルター
上の画像では最終ステップがエラーになっていました
ここで最初のステップ、「ソース」を選択してみます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-94.png)
そうすると、フォルダ内のファイルが一覧化されています
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-84-644x182.png)
では、もっと右にスクロールしてみます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-85.png)
そうすると「Date created」という列があります
こちらの列では該当のフォルダにファイルが格納された時刻が記載されています
今、記事を書いている時間は「6月26日17時01分」なのですが、試しにフォルダに新たなファイルを格納してみます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-86.png)
そして、Power Queryエディターのプレビューの更新処理を行ってみます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-87.png)
すると新たなファイルが「17時01分」に追加されています
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-88.png)
ですので、こちらの「Date created」の列でフィルターを行うと、常に最新のファイルだけを処理することができるのです
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-89-644x436.png)
ここで「最も早い」「最も遅い」という日本語訳が分かりにくいかもしれません
あくまで「最も遅い」、つまりファイルが格納された時刻が最も遅いものが最新のものになります
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-90.png)
これで最新のファイルを取得する仕組みは完成です
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-91.png)
もし、エクセルファイル(xlsx)の中でフィルターしたいという場合には、先に拡張子でのフィルターを入れておきましょう
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-93-644x226.png)
その後に「Data created」列のフィルターを入れます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-95.png)
では、もう一度試してみましょう
今、フォルダがこの状態です
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-96.png)
このフォルダに「担当_田中.xlsx」ファイルを格納します
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-97.png)
そうすると、エクセルファイルで最新の「担当_田中」のデータだけがクエリに反映されます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/Untitled-2024-06-26-17-33-48-1.gif)
<まとめ>
今回は最新のファイルだけを反映するクエリを作成しました
ポイントは2つです
1.フォルダからファイルを取得すること
2.作成したクエリのソースステップでフィルタ処理をすること
驚くほど簡単ですよね
最後に、場合によってはファイルの名前、つまり「name」列でフィルターが必要なケースがあることもお伝えしておきます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-98.png)
エクセルVBA虎の巻
この記事では、私がこれまでVBAを使用してきた中で「これは使える」と思ったコードを抜粋したものです
ファイル保存
エクセルシートのCSV出力
出力したいシートをコピーして、新しいファイルを作成し、ファイル名を指定して保存します
ThisWorkbook.Worksheets(“出力”).Copy
ActiveWorkbook.SaveAs “D:\TEST.csv”, FileFormat:=xlCSV
ActiveWorkbook.Close
文字列操作
分割
文字列の分割にはSplitを使用します
Variant型で設定した変数をSplitで分割し、変数(0)、変数(1)、変数(2)で受け取ります
例えば、シートのB1セルに「600-1」の文字列があるとします
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-75.png)
こちらの文字列を「-」で分割し、それぞれをメッセージで表示するとします
その場合は、次のコードで実現できます
Dim mySTRING As Variant
Dim myWORD As Variant
Dim myYEAR As String
Dim myMONTH As String
mySTRING = Worksheets(“Sheet1”).Range(“B2”).Value
myWORD = Split(mySTRING, “-“)
myYEAR = myWORD(0)
MsgBox myYEAR
myMONTH = myWORD(1)
MsgBox myMONTH
まず、myWORD(0)で600を取りだします
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-76.png)
次にmyWORD(1)で1を取り出します
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-77.png)
固定文字が含まれているかを判断する
Instr関数を使用し、0以上なら含まれていると判断します
Dim myCountWaritsuke As Long ‘固定の文字が含まれいる位置
myCountWaritsuke = InStr(“myTEST1”, “TEST”)
MsgBox myCountWaritsuke
上のコードの場合は、myTESTの文字列の3文字目にTESTの文字列が含まれているので3とメッセ―ジが出力されます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-22.png)
文字列への変換
数値の文字列への変換にはStr関数を使用します
Str関数を交えた下のコードを実行してみます
Dim myVal As Long
myVal = 1
Worksheets(“Sheet1”).Range(“B2”).Value = “A-” & Str(myVal)
するとB2セルに以下のような文字列が入力されます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-78.png)
ここで1点問題があります
半角の空白がStr関数で変換した文字列に入ってしまっています
こちらが気になる場合はCstr関数を使用します
Worksheets(“Sheet1”).Range(“B2”).Value = “A-” & CStr(myVal)
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-79.png)
これで空白は入らないようになります
数値への変換
こちらはVal関数を使用します
Dim myVal As String
myVal = “1”
Worksheets(“Sheet1”).Range(“B2”).Value = Val(myVal) + 1
上のコードを実行すると「2」がB2セルに入力されます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-80.png)
数値かどうかの判定
対象の’文字’が数値かどうかの判定は「IsNumeric」を使用します
試しに次のコードを実行してみます
MsgBox IsNumeric(123)
そうすると次の画像のようにTRUEが出力されます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/07/image-55.png)
次に以下のコードを実行してみます
MsgBox IsNumeric(“TEST”)
この場合はFALSEが出力されます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/07/image-56.png)
注意して頂きたいのは、次の場合はTRUEが出力されます
MsgBox IsNumeric(“123”)
これはあくまで123が数値文字だからです
メール関連
表をメールの本文に入れる
シート内の表の内容をHTMにするのがポイントです
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-23.png)
***HTMLの作成***
With Worksheets(“MAIL”)
myLast = .Cells(Rows.Count, 2).End(xlUp).Row
For i = 6 To myLast
myProduct = .Cells(i, 2).Value
myQuantity = .Cells(i, 3).Value
myPrice = .Cells(i, 4).Value
myData = myData & “” & “” & myProduct & “” & myQuantity & “” & myPrice & “”
Next
************
そして、作成したHTMLをメールの本文に入れます(メールは送付せず、表示のみにしています)
*正確には事前に見出しを作成しておき、中身を変数で付け加える形です
‘メールの作成
'OutlookのMailitemオブジェクトを取得する
Dim objOutlook As New Outlook.Application
Dim objMailitem As Outlook.MailItem
Set objMailitem = objOutlook.CreateItem(olMailItem)
'メールの各種設定をする
With objMailitem
.To = "analytic@analytic-vba.com" '宛先
'.CC = myCc 'CC
.Subject = "TEST"
'.SentOnBehalfOfName = mySenderAddress
.HTMLBody = "<font face=""遊ゴシック""><font size=""2.5"">" & _
"<body><table border=1><tr>" & "<th>Product</th><th>Quantity</th><th>Price</th>" & "
</tr>" & _
myData & "</font></table></body>"
.Display '新規メール画面を表示
End With 'objMailitem
これでエクセルシート内の表がメールの本文に入ります
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-24.png)
ファイルを添付して送付
.Attachments.Add+ファイル名の一文を上記のコードに追加します
Dim objMailitem As Outlook.MailItem
Set objMailitem = objOutlook.CreateItem(olMailItem)
'メールの各種設定をする
With objMailitem
.To = "analytic@vba.com" '宛先
(省略)
.Attachments.Add "C:\Users\***\Downloads\download.csv"
.Display '新規メール画面を表示
End With 'objMailitem
ユーザーフォーム
ユーザーフォームについてはこちらの記事もご参考にしてください
処理中表示
マクロの処理に時間がかかる場合は「処理中」の表示をしておきたい場合があります
こちらの表示にはユーザーフォームを使いたいところですが、ユーザーフォームを表示すると後続処理が止まってしまいます
ですので、以下のように「vbModeless」「Repaint」を使用します
Sub test()
UserForm1.Show vbModeless
UserForm1.Repaint
MsgBox “TEST”
Unload UserForm1
End Sub
上記のコードであれば、ユーザーフォームを表示しながらメッセージボックスも表示されます
ファイルの扱い
ファイルパスを指定して開く
Openメソッドを使用します
Dim myFileNameBefore As String
myFileNameBefore = “D:\Copy元.xlsx”
Workbooks.Open myFileNameBefore
ファイルを別名保存(コピー)する
SaveAsメソッドを使用します
Dim myFileNameBefore As String
Dim myFileNameAfter As String
myFileNameBefore = “D:\Copy元.xlsx”
myFileNameAfter = “D:\Copy先.xlsx”
Workbooks.Open myFileNameBefore
ActiveWorkbook.SaveAs Filename:=myFileNameAfter
ActiveSheet.Range(“B2”).Value = “TEST”
重複削除
重複削除を行うにはRemoveDuplicatesを使用します
複数列をキーに重複削除を行う際には、第二引数のArrayの中に列番号を複数指定します
例えば、次の表を「A」「B」の2列で重複削除を行うとします
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-58.png)
この場合は、次のようなコードを書きます
Dim myLast As Long ‘最終行
myLast = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
Range(“A1:C” & myLast).RemoveDuplicates (Array(1, 2))
これで次のように削除されます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-59.png)
シート操作
全ページ操作
ファイル内の全シートを一律に操作するには「For Each ~ in ~ Next」構文を使用します
次の画像では、各シートのA1セルに値が入っています
そして、シートB以外はA1セルの値を読んでメッセージを表示するようにします
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-74.png)
コードは一度、シート変数:wsTESTを宣言した後に次のように書きます
For Each wsTEST In Worksheets
If wsTEST.Name <> “B” Then
wsTEST.Activate
MsgBox Range(“A1”).Value
End If
Next
ちなみにSheet変数.nameでシート名が取得できます
印刷設定
印刷範囲設定
Dim myLast As Long
‘最終行取得
myLast = Worksheets(“Sheet4”).Cells(Rows.Count, 1).End(xlUp).Row
With Worksheets(“Sheet4”).PageSetup
‘印刷範囲設定
.PrintArea = “A1:M” & myLast
End with
全列を全て印刷
Application.PrintCommunication = False
With Worksheets(“Sheet4”).PageSetup
‘*全ての列を印刷
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
Application.PrintCommunication = True
改ページプレビューに設定
ActiveWindow.View = xlPageBreakPreview
エクセルVBA~ユーザーフォームの不思議~ガッツの日記 第12回
【エクセルはとにかく機能が豊富です。その象徴がユーザーフォームです】
エクセルVBAの初級者か、そうでないかの境目の一つは「ユーザーフォームが使えるか?」だと思っています
ユーザーフォームを使うことで、エクセルファイル内の利便性は格段に向上します
ユーザーフォームを使えると、入力と表示を明確に切り分けることができるのでデータを有効活用できるからです
例えば、下の画像を見てください
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image.png)
これはG2セルとG7セルに移動して、会社名をそれぞれ入力しています
つまり、宛先を表示するために、宛先の表示場所に移動して入力作業をしています
これが、ユーザーフォームを使うとどうなるでしょう?
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/a2f24bffd096340c90b169ec2efd2b1e.gif)
ユーザーフォーム内に表示されている内容から「選択」を行い、「登録」ボタンを押すだけです
これは、繰り返しになりますが、入力と表示が分かれているから行えます
便利なユーザーフォームですが、実際に活用するとなると最初はかなりとっつきにくいと思います
通常のエクセルと考え方が違うからです
後、とにかく機能が多いです
今回の記事ではユーザーフォームを使う中で、躓きやすい「不思議な箇所」、並びにこれは便利だと思った機能を解説したいと思います
VBAを使った事が無い方、VBAを使ったことがあるけどユーザーフォームを使ったことが無い方も、これを機会にユーザーフォームの魅力、エクセルの奥深さを感じて頂けたら幸いです
ちなみに上記のユーザーフォームのコードは以下の通りとなります
初期表示
***
With ListBox1 ’リストボックスのオブジェクト名
.Font.Size = 9
.ColumnCount = 3
.ColumnWidths = “20;50;100”
.TextAlign = fmTextAlignCenter
‘リストボックスに情報を追加
Dim i As Long
Dim myLast As Long
myLast = Worksheets(“①”).Cells(Rows.Count, 2).End(xlUp).Row
For i = 3 To myLast
.AddItem
.List(.ListCount – 1, 0) = Worksheets(“①”).Cells(i, 2).Value
.List(.ListCount – 1, 1) = Worksheets(“①”).Cells(i, 3).Value
.List(.ListCount – 1, 2) = Worksheets(“①”).Cells(i, 4).Value
Next
End With
***
登録ボタン
***
With ListBox1
Dim myValue As String
myValue = .List(.ListIndex, 1)
Worksheets("①").Range("G2").Value = .List(.ListIndex, 2)
Worksheets("①").Range("G7").Value = .List(.ListIndex, 2)
End With
***
リストボックスの10列超を表示できない
例えば、こんな横長な表があったとします
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-1-644x133.png)
こちらの表をリストボックスで表示してみます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-2-644x350.png)
すると上の画像のような不思議なエラーになります
使用しているコードは「AddItem」になります
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-3-644x145.png)
実はこの「AddItem」を使用する時は10列までしかリストボックスに表示できません
この場合は「RowSource」か「List」を使用し、直接エクセルの範囲を指定します
RowSource
RowSourceの場合は、関数で別シートを参照する時のような書き方で範囲を指定します
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-4-644x141.png)
これで、B3セルからM列の最終行までをリストボックスで表示できます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-5-644x146.png)
しかも、ColumnHeadsを使用して指定範囲の1つ上の行を見出しとして表示できるのが便利です
List
Listで範囲指定する場合には、下のように通常のVBAの書き方をして、最後にValueを付け加えます
.List = Worksheets(“②”).Range(“B2:M” & myLast).Value
但し、RowSourceと違い、見出し設定はできませんのでご注意ください
リストボックスから行削除が行えない
これまた不思議なのですが、.RemoveItemを使用してもリストボックスの行を削除できないことがあります
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-6.png)
しかもエラーメッセージが不思議な内容ででます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-7.png)
実はこれ、RowSourceを使用してリストボックスを表示すると起こる現象です
RowSourceを使用する場合であれば、削除はデータ元のエクセルで行う必要があります
他の方法でリストボックスを作成している場合はこの現象はありません
リストボックスを複数選択にする
これはユーザーフォームではこんな事もできるという紹介です
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-8-644x208.png)
上の画像の「MultiSelect = fmMultiSelectMulti」を追加するだけで、リストボックスは複数選択にできます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-9.png)
これだとどこが選択されているか、どう選択したらいいか分かりにくい、という場合は次の一行を足します
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-10.png)
そうすれば、次の画像のように各行にチェックボックスが表示されます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-11.png)
複数選択できるのは分かったけど、どう選択されたものを取得するの?
と思われた方も多いと思います
複数選択されたものを取得するには、リストボックスの各行を繰り返し処理で取得します
そしてポイントは「Selected」を使用して、選択行を特定することです
***
With ListBox1
For i = 0 To .ListCount – 1 ‘ListBoxの各行
If .Selected(i) Then ‘行が選択されている場合
MsgBox .List(i, 0)
.Selected(i) = False
End If
Next i
End With
***
これで複数行を取得できます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/c62c3f828abdb4fcf7061797d85d5de9.gif)
テキストボックス内で文字列を折り返す
テキストボックス内でセルと同じように折り返せないか?と思った方もいらっしゃると思います
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-12.png)
この場合はプロパティで「MultiLine」をFalseからTrueに変更します
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-13.png)
後、もう一か所関係がある箇所があります
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-14.png)
WordWrapがTrueに設定してある必要があります
こちらは通常はTrueに設定されています
これで文字列は折り返されます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-15.png)
入力位置遷移(カーソル遷移)
テキストボックス間での遷移を変更したい時があります
以下のGIF画像では横にカーソルが動いています
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/204597927a5e0a587fc4e3889788118b.gif)
この遷移を変えたい時には「TabIndex」を変更します
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-16.png)
今回の場合で言えば、下のテキストボックスを3から1に変更します
このようにTabIndexを変えれば、カーソル遷移は変わります
![](https://analytic-vba.com/wp/wp-content/uploads/2024/06/image-17.png)
<まとめ>
今回はエクセルVBAのユーザーフォームについて記事を書きました
私は正しい脱エクセルをかかげていますが、最近、改めてエクセルの浸透の深さに驚いています
他の言葉で言い換えれば、様々な企業で貴重なエクセル資産があります
わざわざ資産を他に移し替えるのはもったいないケースも多々ありますので、エクセルの研究は引き続き続けていきたいと思います
その研究テーマの一つが今回のユーザーフォームですね
ユーザーフォームは本当奥深いですね
今後も研究は続けていきたいものです
Uploadしたエクセルファイルを直接コレクションにする~Power Apps~
【エクセルファイルをSharepointリストなどを経由せず、直接Power Apps内で編集できるようにします】
エクセルファイルの内容をSharepointリストに反映する前に、一旦編集したいケースもあると思います
その場合、一旦Sharepointリストに反映してしまうと「削除」を行う必要がでてくるなど、やたらと時間がかかることがあります
今回はJSON形式を経由して、直接エクセルファイルの内容をPower Appsに取り込みます
記事の解説は大きく分けると、次の3つになります
・エクセルをPower Appsから始動して一旦、OneDriveに名前を変えて保存する
・エクセルファイルをJSON形式に変えてPower Appsに送信する
・Power AppsでJSON形式を通常のコレクションにする
上記のうち「エクセルをPower Appsから始動して一旦、OneDriveに名前を変えて保存する」については、お手数ですが過去の記事をご参照願います
前提条件
・Uploadするエクセルファイルの中身は最初からテーブル形式になっていること
*OfficeScriptでPower Automateのフロー内で自動的にテーブル形式にすることもできます⇒過去記事
・Uploadするエクセルファイルの列は固定であること
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-17.png)
ファイルのアップロード及びPower Automateの起動
フォームのパーツを使用してファイルをUploadする方法は過去の記事を参照してください
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-18.png)
今回はファイルのアップロードしてコレクションを作成するまでの間は「処理中」と表示します
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/e065a3155328ead2005eda4668ba5783.gif)
この「処理中」を表示するためには、ボタンの「OnSelect」プロパティにてUpdateContext関数を組み込みます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-19.png)
UpdateContext関数で更新する変数はラベルのVisibleプロパティに組み込んでおきます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-20.png)
そして、Power Automateフローを起動する時には変数:myflowで受け取れるようにします
UpdateContext({myflow:
エクセルインポート.Run(
{
contentBytes: First(DataCardValue.Attachments).Value,
name: First(DataCardValue.Attachments).Name
}
)
});
Power Automate内でのJSON形式への変換
ファイルの作成
Power Automateフロー内ではPower Appsから受け取ったファイルの内容を基にして、まずはOneDrive内に新たなファイルを作成します
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-21-644x443.png)
作成されるファイル名は過去に作成したものと同じにならないように、現在時刻をファイル名に入れます
*OneDrive内では作成したファイルを削除したり、ファイル自体を上書き更新するには一定期間を空けなくてはなりません。その為、このような処理を行います
JSON形式への変更
選択アクション
UploadしたエクセルファイルをJSON形式に変換するには、データ操作コネクタの選択アクションを使用します
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-22-644x396.png)
「表内に存在する行を一覧表示」アクションで読み込んだ内容を「選択」アクションでJSON形式に当てはめていきます
この時に「日時」と「数値」形式の内容については、関数を使用してきちんと該当の形式に変換しておきます
・日時形式 ⇒例:formatDateTime(item()[‘Date’],’yyyy/MM/dd’)
・数値 ⇒例:int(item()?[‘Sales’])
注意
「選択」アクション内で、うまく動的コンテンツでエクセルの内容を拾えないことがあります
この時は2つ対応方法があります
・動的コンテンツの工夫
通常「表内に存在する行を一覧表示」アクション内では下の画像のように、ファイルの指定内容は動的コンテンツにて指定します
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-23.png)
選択アクションにて動的コンテンツを使用するまでは「パス」ではなく、実際のファイルパスを指定しておきます
選択アクション内で動的コンテンツを設定したら、元通り「パス」を設定し直しておきます
・式での指定
一度、コードのプレビューを見てみましょう
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-24-644x253.png)
上の画像を見ると、開始の「value」で処理する内容は<item()?[‘列の中身’]>で抽出できることがわかります
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-25.png)
実際に<item()?[‘列の中身’]>を使用する時は、下の画像のように「式」の中で@を入れないことも大きなポイントです
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-26.png)
JSONの解析
JSONの形式変更の最後では「JSONの解析」アクションを使用します
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-27.png)
スキーマは「サンプルから生成」から作成しますが、こちらは一旦「””」で設定してフローを動かして設定します
*あまりUploadするファイルの行数が多いと取得できませんので、その場合は行数が少ないサンプルファイルを使用します
フロー動かした後の出力結果をコピーし、どこかメモ帳にでもコピーしておきます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-28.png)
その後、サンプルから生成をクリックし、次に表示される画面にコピーしてスキーマを作成します
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/2c63c216d9fd9bb6666e4dc1d11d1e9c.gif)
Power Appsへの受け渡し
作成したJSONはPower Appsに受け渡します
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-29.png)
上の画像では「msg」としてPower Appsに受け渡しますが、Power Apps内ではそのまま「msg」では抽出できませんので注意が必要です
Power Apps内でのコレクションの作成
Power Automateから受け取ったJSONの内容は、次の画像のようになっています
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-30.png)
コンテキスト変数内で更に「msg」を経由することで「JSONの内容」を抽出できるようになっています
このJSONからコレクションを作成するには、2つの関数を使用します
「ParseJSON関数」と「ForAll関数」です
最初のParseJSON関数では、受け取ったJSON形式の内容を解析します
解析した内容には形式がありませんので、次の画像のようにTable関数でTable形式にします
Table(ParseJSON(myflow.msg))
Table関数で作成したテーブルの内容は、ForAll関数でコレクションにします
ここで注意点です
ParseJSONで解析した内容はValueで抽出します
しかも、Valueで抽出しても形式が無いので、次のようにText関数やValue関数で形式を付与します
{
Product:Text(Value.Product),
Date:Text( Value.Date),
Customer:Text( Value.Customer),
Sales: Value(Value.Sales)
}
ここで、JSON形式をコレクションにする内容を全てみてみましょう
Clear(myJson);
ForAll(
Table(ParseJSON(myflow.msg)),
Collect(
myJson,
{
Product:Text(Value.Product),
Date:Text( Value.Date),
Customer:Text( Value.Customer),
Sales: Value(Value.Sales)
}
)
)
まず最初に作成するコレクション:myJSONをクリアしておきます
その後に、前述のようにParseJSON関数で解析しながら、JSONの内容を全てコレクションにします
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-32-644x303.png)
<まとめ>
今回はエクセルファイルをアップロードしてコレクションにする方法を解説しました
具体的な内容としては①エクセル⇒JSON、②JSON⇒コレクションというように2段階で変換を行うので少し重い内容となっています
但し、意外と躓きやすい箇所が他にあります
「表内に存在する行を一覧表示」のアクションです
既存の設定だと2つ注意点があります
1.出力される行数が256行に限定される
こちらは設定を変えておきましょう!
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-33-644x176.png)
上の画像の設定をクリックすると次の画面が開きます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-34.png)
こちらで「しきい値」を変えると出力する行数の上限を引き上げられます
2.DateTime形式
こちらは「ISO 8601」にしておかないと、日付形式の箇所はエラーになります
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-35.png)
「ISO 8601」の他に「Serial Number」というのもありますが、その場合は日付形式の内容を設定するには「addDays関数」を使用します
こちらについては後日、別途詳細な記事を書きたいと思います
Power Automate Desktopでメールに表を入れて送る方法
【HTMLを使用して、とにかく手っ取り早く表をメールの中に入れて送ります】
添付のメールだと見てもらえるかわからないので、メールの中に表を入れて送るケースも結構あるようですね
但し、これはエクセルの表を貼り付けるなどの手間があるので、すごく面倒です
RPAで送るにしても、貼り付けだと安定性があるとは言えません
ですので、Power Automate Desktopで表をメールに入れるHTMLコードを自動で作成してメールを送ってしまおうというのが今回の趣旨です
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/dedd286733e8758a1e129979494930fe-644x362.png)
メールで送る表
今回メールで送る表はエクセルで作成します
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image.png)
行数は可変という想定です
但し、列数は固定という想定になります
表を入れるHTMLコード
下が表をメールに入れるHTMLコードは以下のようになります
<table border="1"> <tr> <td>部門</td> <td>売上</td> </tr> <tr> <td>1行目部門</td> <td>1行目部門</td> </tr> </table>
これを見ても、HTMLコードを見たことが無い読者の方からすると、何のことか分からない・・・という風になるかもしれません
但し、<tr>x</tr>という「/」を介した組み合わせと、<tr></tr>の間に挟む内容が1行を表していることに注目して頂くと分かりやすいと思います
つまり、一旦、見出しを最初に<td></td>を介しながら設定した後は、エクセルの最終行まで読込処理を繰り返しながら<tr></tr>の間に読込内容を組み込んでいきます
なお、表を罫線で囲むには<table border・・・と表現します
使用するアクション
主に「テキストに行を追加」アクションを使うとフローが分かりやすくなると思います
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-2.png)
追加した後は変数:Result(上の画像の場合)に格納するのですが、下の画像のような形にすると前述のHTMLコードが作られていく様子がイメージしやすいと思います
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-3.png)
もちろん、こちらの変数:Resultはメールの本文に入れて送ります
繰り返し処理
まずは「テキストに行を追加」を使用して、表の見出し部分を作成します
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-4.png)
その後は繰り返し処理を行列双方向で行い、表の中身を追加してきます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-5.png)
この時、行方向の繰り返し処理は最終行まで行うようにします
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-6.png)
そして、1行の処理の開始及び終了時には<tr>と</tr>を追加します
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-7.png)
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-8.png)
表の中身を追加する際には<td></td>を組み合わせます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-9.png)
表の中身を追加し、繰り返し処理が終了した後は</table>を組み入れます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-11.png)
メールの送信
メールの送信時は、HTMLコードを設定した変数を組み込みます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-10.png)
そして、ここが最後のポイントですが、下の画像の「本文はHTMLです」をオンにしましょう
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-12.png)
これで行数可変でメールの中に表を入れて送ることができます
<まとめ>
今回は手っ取り早く、コードを使用してメールの中に表を入れて送る方法を解説しました
今回紹介した内容では、罫線の形は下の画像の形でしか送れません
![](https://analytic-vba.com/wp/wp-content/uploads/2024/02/image-13.png)
ぜひ、HTMLを解説する他サイトなどを参照して、罫線の形はご自身の好みに合うように変えてみてください
脱エクセルについて考える~資金繰り表をPower Platformで作成~第9回
【エクセルは本当に便利なツール。但し、効率と効果を上げるには限界があります】
資金繰り表はエクセルに向いている業務の典型です
それは何故か?
![](https://analytic-vba.com/wp/wp-content/uploads/2024/01/image-644x449.png)
上の画像の黄色のセルには数式が入っており、預金残高の予想残高を計算しています
但し、この数式を必要に応じて実際の預金残高「数値」で上書きすることができます
実はこれってエクセル以外のソフトだと難しいんです
「数式」と「数値」を共存できるエクセルは本当に便利なんです
ではエクセルで業務を続けていたらそれでいいのか?
それはそれで問題があります
下の画像の「2023/1/5」の売上高の欄には120と入っています
![](https://analytic-vba.com/wp/wp-content/uploads/2024/01/image-1-644x136.png)
入力数字は120でも、実は120という数字を別な表で複数の顧客の数字を積み上げて入力されているかもしれません
それに、表に存在する日付/列単位が膨大になってくると、入力箇所を見つけるのも面倒です
つまり量が膨大になってくると、この資金繰り表の1セルを入力する作業がかなり面倒になってきます
その理由は一般的なエクセルの資金繰り表では「データ蓄積」「作業」「計算」「表示」が分かれていないからです
ですので、作業や計算スペースを作る関係上、データの並びが不規則になってしまったりしますし、入力しやすいようなスペースを作ることも困難です
更に言えば、資金繰り表のシートのデータを活用して他のシートで別の目的の表を作成しようとするとかなり複雑なものになってしまいます
実は、Power BIのDAXを使用すれば預金口座の残高も、実残高と予想残高を共存させながら計算できます
今回の記事ではPower BIとPower Appsを使用して、エクセルの資金繰り表の「データ蓄積」「作業」「計算」「表示」を分けて管理できる仕組みの概要を紹介します
そして、Power Platformの便利さに触れて頂けたら幸いです
ポイント
Power Platformを使って資金繰り表を作成するには、分解が結合がポイントです
データの分解・結合
預金残高は数式と数値を共存できるように、以下にデータを分けて管理します
・預金残高(実残高)
・入金
・出金
更にカレンダーテーブル
上記の4つをDAXで結合して使用します
今回はSharepointリストを使用します
入出力と表示の分解
今回は入出力はPower Appsで行います
![](https://analytic-vba.com/wp/wp-content/uploads/2024/01/image-2.png)
Power Appsならページを分けて、売掛金や経費などの入出力専用ページを作成できます
そして、表示や主な計算はPower BIで行います
![](https://analytic-vba.com/wp/wp-content/uploads/2024/01/image-3.png)
Power BIは集約計算の簡単にできるし、DAXで複雑な計算も行えますので、Power BIの機能もフル活用します
エクセルで日付毎に複数の顧客の売掛金を合計していた作業は、前述のテーブルを作成しておけば勝手にPower BIが行ってくれます
入出力と表示が分かれていると不便だろうという場合は、後述するPower BI内でPower Appsを使う方法もあります
![](https://analytic-vba.com/wp/wp-content/uploads/2024/01/image-4.png)
預金残高を表示するDAXの作成
ここがエクセルの資金繰り表を他のソフトで行う最大の難所です
日付の管理がとても重要です
・実際に預金残高を入力した日付の残高⇒そのまま残高を表示
・預金残高を入力していない日付の残高⇒実際に預金口座を入力した日付以降の入出金を加味して計算
例えば、1月1日の残高が100、1月2日の入金が100、1月3日の出金が100だったとします
この場合は
1月1日残高⇒100/実績残高
1月2日残高⇒200(100+100)/予想残高
1月3日残高⇒100(100+100ー100)/予想残高
となります
こちらを実際にDAX式に直したのが以下です
*CashBalance:実際の預金残高、Receipt:入金、Disbursement:出金
預金残高 =
VAR currentDay=MAX(‘Calendar'[Date])
VAR maxDay=CALCULATE(MAX(‘CashBalance'[Date]),FILTER(All(CashBalance),’CashBalance'[Date]<=currentDay))
VAR cashAmount=SUMX(FILTER(ALL(‘CashBalance’),’CashBalance'[Date]=maxDay),’CashBalance'[CashBalance])
VAR cashReceipt=CALCULATE(SUM(‘Receipt'[Amount]),FILTER(All(‘Receipt’),’Receipt'[Date]>maxDay &&’Receipt'[Date]<=currentDay))
VAR CashDisbursement=CALCULATE(SUM(‘Disbursement'[Amount]),FILTER(All(‘Disbursement’),’Disbursement'[Date]>maxDay &&’Disbursement'[Date]<=currentDay))
RETURN cashAmount+cashReceipt-cashDisbursement
今回の記事では詳細には解説を行いませんが、一見複雑なこの式の最大のポイントは「預金残高の最大日付:maxDay」を算出することです
冒頭のたとえを使って説明すると、今日が1月3日だとしたらその前に預金残高がテーブル:CashBalanceに貴重された日付の1月1日を算出するということになります
最大日付が算出できれば、おのずと入金金額を算出する期間と出金金額を算出する期間が求まります
ただ実際には、フィルターを意識してAll関数をうまく組み合わせていく必要があります
専用アプリによる預金残高表示
Power BIには様々なアプリ/ビジュアルがあります
今回は資金繰り表に活用できるビジュアルを1つ紹介します
その他のビジュアルの取得をクリックして「Calendar」と検索してください
![](https://analytic-vba.com/wp/wp-content/uploads/2024/01/image-6.png)
すると「Beyondsoft Calendar」というビジュアルをダウンロードできます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/01/image-7-644x235.png)
こちらのビジュアルは日々の預金残高をカレンダー上で表示できるという優れものです
残高の状態によって色を変えたりもできます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/01/image-8.png)
エクセルではなくPower BIを使用することで、専用アプリを活用することもできるんです
![](https://analytic-vba.com/wp/wp-content/uploads/2024/01/image-12-644x462.png)
Power BIとPower Appsの連携
Power AppsをPower BI内で使用したい場合は、こちらをクリックするところからはじめます
![](https://analytic-vba.com/wp/wp-content/uploads/2024/01/image-9.png)
すると、Power Appsに使用するフィールドの入力ができるようになります
![](https://analytic-vba.com/wp/wp-content/uploads/2024/01/image-10-644x304.png)
実は、こちらはなんでも構わないんです。取り合えば何か設定すればPower BI内で表示したいPower Appsのアプリを選択できるようになります
![](https://analytic-vba.com/wp/wp-content/uploads/2024/01/image-11-644x298.png)
<まとめ>
今回は脱エクセルについて考えると称して、エクセルの資金繰り表をPower AppsとPower BIを連携させて作成する方法の概要を紹介しました
紹介したのはあくまで概要ですが、エクセル作業が改善されていく可能性を感じて頂けたら幸いです
現実に、エクセルは表計算ソフトですので大量のデータを処理するには向いていません
なので、Power PlatFormの仕組みを利用して「データ蓄積」「作業」「計算」「表示」を分解・結合できるようにすることはとても意義があります
そうは言っても、誰でも手軽に使えるエクセルと違ってPower BIのDAXなどが複雑であったりするので専門知識が必要です
それに当然、手間暇も必要です
ですので、最終的にはどの程度の量の業務をどんな風に変えたいかが一番のポイントなのかもしれませんね
ガッツの日記 第7回 「エクセルとSharepointリスト」それぞれのいいとこどりをする方法
【今回の記事はデスクトップのエクセルの新たな活用方法の提案です】
ここ数年でSharePointリストなどのクラウドサービスを利用する人はかなり多くなってきています
クラウドはネットさえつながればどこでも使うことができ、特にSharepointリストは複数人でデータを共有して仕事するのに便利な機能が満載です
履歴の復元が行えたり、登録者や更新日時が確認できます
何より3000万行を蓄積できるのがいいです
エクセルはあくまで表計算ソフトであり、大量のデータを蓄積していくのには本来は向いてません
蓄積できる行数も1シート100万前後です
一方、使い慣れたデスクトップのエクセルをこれからも使用していきたいという方も多いと思います
特にVBAを使って業務を回してきたケースの場合は、そのままデスクトップのエクセルを継続的に使用せざる負えないです
ここからが本題です
実は、
Sharepointリストとデスクトップのエクセルを組み合わせて使用する方法があります
![](https://analytic-vba.com/wp/wp-content/uploads/2023/11/Purple-Neon-Bright-Emoji-Surprise-YouTube-Thumbnail-2-644x362.png)
ADOという仕組みを使うと「Sharepointリストからエクセルへのデータ取得」「エクセルからSharepointリストへのデータ追加・更新」が自動で行えます
以前、私はこのADOを使用して「Accessとエクセル」を連携するシステムを開発したことがあります
この方法ならばAccessにデータを一元管理しながら、利用者各自のエクセルファイルにて作業を行うことができます
何が一番素晴らしいかというと、売上管理や商品管理などの各種エクセルにてマスタを共通利用できる点です
エクセルでデータ管理を行っていると、複数ファイルでのマスタの追加や更新などの処理が煩雑になりがちです
ADOを使用した仕組みの場合は、Accessにてマスタ管理を行えば、利用者の各種エクセルファイルにてマスタを自動でダウンロードして使いまわすことができます
今回の記事では、Accessの代わりにSharepointリストを使用するわけですが、クラウドで利用できる点なども含めてかなり効率的な仕組みができると考えています
VBAを使い込んでいた組織では、VBAを引き続き有効利用しながらクラウドを活用できるのも大きいです
以降の記事では、Sharepointリストの特徴なども交えながらデータ取得・追加・更新のサンプルコードを解説していきます
尚、今回の記事はVBA経験者を主な対象としていますのでその点は予めご了承ください
事前準備
ライブラリーの追加
ADOをVBAで使用する場合には、Visual Basicのツールタブ⇒参照設定からMicrosoft ActiveX Data Objectsライブラリを追加する必要があります
![](https://analytic-vba.com/wp/wp-content/uploads/2023/11/image-17-644x158.png)
ここが抜けてしまうと、コードをいくら書いても動きませんので注意が必要です
![](https://analytic-vba.com/wp/wp-content/uploads/2023/11/image-18.png)
サイトURL及びリストID
ADOを使用してSharepointリストをデータソースとして使用する場合は「サイトURL」と「リストID」を使用します
サイトURLについては分かりやすいと思います
![](https://analytic-vba.com/wp/wp-content/uploads/2023/11/image-19.png)
上記画像の「sites」の後と「Lists」の前までがサイトURLです
リストIDは普段聞きなれないと思いますが、Sharepointリストの内部名のことです
今回の記事で使用するSharepointリストのリスト名は「Sales_Test」になりますが、別の名前/IDをADO内では使用します
![](https://analytic-vba.com/wp/wp-content/uploads/2023/11/image-22.png)
リストIDの取得はこちらから行います
![](https://analytic-vba.com/wp/wp-content/uploads/2023/11/image-21.png)
上の画像の「リストの設定」をクリックすると、次に開く画面でリストIDが取得できます
![](https://analytic-vba.com/wp/wp-content/uploads/2023/11/Purple-Neon-Bright-Emoji-Surprise-YouTube-Thumbnail-644x362.png)
こちらの「%7B」と「%7D」の間がリストIDになります
![](https://analytic-vba.com/wp/wp-content/uploads/2023/11/Purple-Neon-Bright-Emoji-Surprise-YouTube-Thumbnail-1-644x362.png)
共通構文
以下、サンプルコードを実際に解説していくわけですが、共通の部分があります
******
Dim objConnect As Object ‘ADODB.Connection
Dim recordSet As Object ‘ADODB.Recordset
Const Sharepointurl As String = ①”https://xxxx5.sharepoint.com/sites/TEST“
Const listid As String = ②”b24faf63-1f23-xxxx-858e-278ead83f878“
③Set objConnect = CreateObject(“ADODB.Connection”)
④objConnect.Open “Provider=Microsoft.ACE.OLEDB.16.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=” & Sharepointurl & “;LIST=” & listid & “;”
⑤Set recordSet = CreateObject(“ADODB.Recordset”)
******
①②ではサイトURL、リストIDを設定します
③では、ADOとの接続を設定し、④で実際に①②のサイトURLとリストIDを通じて接続を行います
⑤では取得したレコード/行一式を格納する箱を設定します
Sharepointリストの列名を取得
本来であれば、早速データの取得のサンプルコードから紹介したいところなのですが、Sharepointリストの列名の取得から始めたいと思います
何故かと言うと、ここにSharepointリストの特徴の1つがあるからです
******
Sub getFields()
(共通)
With recordSet
①.Open “SELECT * FROM [Sales_TEST];”, objConnect
②.MoveFirst
Dim i As Long
③For i = 0 To recordSet.Fields.Count - 1
④Debug.Print recordSet.Fields(i).Name
⑤Next
.Close
End With
objConnect.Close
End Sub
******
①にてレコードを該当Sharepointリストから取得し、②でレコードの最初に移動します
③~⑤でレコードの列名を繰り返し、イミディエイトウィンドウに表示します
![](https://analytic-vba.com/wp/wp-content/uploads/2023/11/image-25.png)
取得した列名を見て頂くと、設定していない列が取得されます
ここがAccessと違うところです
一番上に出ているIDはレコードを追加すると「1,2,3・・・」と自動採番されます
このIDは今回もSharepointリストの更新で使用します
ちなみに、使用した接続やレコード一式は必ず閉じる/Closeするようにします
データの取得
Sharepointリストからデータを取得するには、2つの方法があり、列別に取得する方法とレコードセットを丸ごと取得する方法です。
列別
前述の列名で取得した「列名」をここで使用します
*太字の部分です
******
With recordSet
.Open “SELECT * FROM [Sales_TEST];”, objConnect
.MoveFirst
Dim i As Long
Do Until .EOF
Debug.Print .Fields.Item("注文NO").Value
.MoveNext
Loop
.Close
End With
objConnect.Close
******
一括
こちらは一括でSharepointリストからレコード一式をワークシートにコピーします
下のコードではSheet5のA2セルに一括でコピーしています
******
With recordSet
.Open “SELECT * FROM [Sales_TEST];”, objConnect
Worksheets("Sheet5").Range("A2").CopyFromRecordset recordSet
.Close
End With
objConnect.Close
******
もし、データが一行もない場合は「データが無かった旨」を表示できるようにすると便利です
.Open “SELECT * FROM [Sales_TEST];”, objConnect
If recordSet.BOF = True And recordSet.EOF = True Then
recordSet.Close
objConnect.Close
MsgBox "該当するデータはありません。"
Exit Sub
End If
データの追加
以下の①から⑧までを一式で使います
******
①recordSet.Open “[Sales_Test]”, objConnect, adOpenKeyset, adLockOptimistic
②objConnect.BeginTrans
③recordSet.AddNew
④recordSet.Fields("注文NO").Value = Worksheets("Sheet6").Cells(2, 1).Value
⑤recordSet.Fields("SalesAmount").Value = Worksheets("Sheet6").Cells(2, 2).Value
⑥recordSet.Update
⑦objConnect.CommitTrans
⑧recordSet.Close
******
①のadOpenKeysetやadoLockOptimisticは他に指定する方法がありますが、こちらの方法であれば大概は問題ありません
④⑤で追加データをワークシートから読み込んでいます
追加データが複数行ある場合は「For ~ Next」内に③~⑥を入れてコードを書きます
そして最後に⑦を実行します
データの更新
前述のようにSharepointリストのIDを使用し、更新するレコードを指定するのがポイントです
ですので実際には、事前にレコードの取得をしておいてIDを特定できるようにする必要があります
******
objConnect.BeginTrans
①recordSet.Filter = "ID = " & Worksheets("Sheet7").Cells(2, 1).Value
recordSet.Fields("SalesAmount").Value = Worksheets("Sheet7").Cells(2, 2).Value
recordSet.Update
******
データの追加も同様ですが、接続が行えない場合は「ロールバック処理/もとに戻す」を行えるようようにしておくのがよいです
![](https://analytic-vba.com/wp/wp-content/uploads/2023/11/image-26.png)
<まとめ>
今回はADOの仕組みにより、Sharepointリストからエクセルにデータ取得したり、エクセルからデータ追加・更新を行う方法を解説しました
今回紹介した内容は基本的な内容であり、実際には条件式を組み合わせるなど様々なパターンが考えられます
実はSharepointリストとADOにて接続する場合、1点厄介なことがあります
ADOで扱うSharepointリストに参照列が存在する場合です
![](https://analytic-vba.com/wp/wp-content/uploads/2023/11/image-27-644x254.png)
上の画像の一番右、CustomerName列は参照列です
他のSharepointリストから参照しています
![](https://analytic-vba.com/wp/wp-content/uploads/2023/11/image-28.png)
参照列をADOで取得すると、次の画像のように参照しているSharepointリストのIDに置き換わってしまいます
![](https://analytic-vba.com/wp/wp-content/uploads/2023/11/image-29.png)
ですので、IDを変換する仕組みが必要になります
データ追加の時も、参照列がある場合には同様にIDを変換する仕組みが必要です
とにかくSharepointリストを扱う上では「ID」が非常に重要です
この点は強く意識しておきましょう!