タグ別アーカイブ: エクセル

エクセルVBA~ユーザーフォームの不思議~ガッツの日記 第12回

【エクセルはとにかく機能が豊富です。その象徴がユーザーフォームです】

エクセルVBAの初級者か、そうでないかの境目の一つは「ユーザーフォームが使えるか?」だと思っています

ユーザーフォームを使うことで、エクセルファイル内の利便性は格段に向上します

ユーザーフォームを使えると、入力と表示を明確に切り分けることができるのでデータを有効活用できるからです

例えば、下の画像を見てください

これはG2セルとG7セルに移動して、会社名をそれぞれ入力しています

つまり、宛先を表示するために、宛先の表示場所に移動して入力作業をしています

これが、ユーザーフォームを使うとどうなるでしょう?

ユーザーフォーム内に表示されている内容から「選択」を行い、「登録」ボタンを押すだけです

これは、繰り返しになりますが、入力と表示が分かれているから行えます

便利なユーザーフォームですが、実際に活用するとなると最初はかなりとっつきにくいと思います

通常のエクセルと考え方が違うからです

後、とにかく機能が多いです

今回の記事ではユーザーフォームを使う中で、躓きやすい「不思議な箇所」、並びにこれは便利だと思った機能を解説したいと思います

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列超を表示できない

例えば、こんな横長な表があったとします

こちらの表をリストボックスで表示してみます

すると上の画像のような不思議なエラーになります

使用しているコードは「AddItem」になります

実はこの「AddItem」を使用する時は10列までしかリストボックスに表示できません

この場合は「RowSource」か「List」を使用し、直接エクセルの範囲を指定します

RowSource

RowSourceの場合は、関数で別シートを参照する時のような書き方で範囲を指定します

これで、B3セルからM列の最終行までをリストボックスで表示できます

しかも、ColumnHeadsを使用して指定範囲の1つ上の行を見出しとして表示できるのが便利です

List

Listで範囲指定する場合には、下のように通常のVBAの書き方をして、最後にValueを付け加えます

.List = Worksheets(“②”).Range(“B2:M” & myLast).Value

但し、RowSourceと違い、見出し設定はできませんのでご注意ください

リストボックスから行削除が行えない

これまた不思議なのですが、.RemoveItemを使用してもリストボックスの行を削除できないことがあります

しかもエラーメッセージが不思議な内容ででます

実はこれ、RowSourceを使用してリストボックスを表示すると起こる現象です

RowSourceを使用する場合であれば、削除はデータ元のエクセルで行う必要があります

他の方法でリストボックスを作成している場合はこの現象はありません

リストボックスを複数選択にする

これはユーザーフォームではこんな事もできるという紹介です

上の画像の「MultiSelect = fmMultiSelectMulti」を追加するだけで、リストボックスは複数選択にできます

これだとどこが選択されているか、どう選択したらいいか分かりにくい、という場合は次の一行を足します

そうすれば、次の画像のように各行にチェックボックスが表示されます

複数選択できるのは分かったけど、どう選択されたものを取得するの?

と思われた方も多いと思います

複数選択されたものを取得するには、リストボックスの各行を繰り返し処理で取得します

そしてポイントは「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

***

これで複数行を取得できます

テキストボックス内で文字列を折り返す

テキストボックス内でセルと同じように折り返せないか?と思った方もいらっしゃると思います

この場合はプロパティで「MultiLine」をFalseからTrueに変更します

後、もう一か所関係がある箇所があります

WordWrapがTrueに設定してある必要があります

こちらは通常はTrueに設定されています

これで文字列は折り返されます

入力位置遷移(カーソル遷移)

テキストボックス間での遷移を変更したい時があります

以下のGIF画像では横にカーソルが動いています

この遷移を変えたい時には「TabIndex」を変更します

今回の場合で言えば、下のテキストボックスを3から1に変更します

このようにTabIndexを変えれば、カーソル遷移は変わります

<まとめ>

今回はエクセルVBAのユーザーフォームについて記事を書きました

私は正しい脱エクセルをかかげていますが、最近、改めてエクセルの浸透の深さに驚いています

他の言葉で言い換えれば、様々な企業で貴重なエクセル資産があります

わざわざ資産を他に移し替えるのはもったいないケースも多々ありますので、エクセルの研究は引き続き続けていきたいと思います

その研究テーマの一つが今回のユーザーフォームですね

ユーザーフォームは本当奥深いですね

今後も研究は続けていきたいものです

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ 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するエクセルファイルの列は固定であること

ファイルのアップロード及びPower Automateの起動

フォームのパーツを使用してファイルをUploadする方法は過去の記事を参照してください

今回はファイルのアップロードしてコレクションを作成するまでの間は「処理中」と表示します

この「処理中」を表示するためには、ボタンの「OnSelect」プロパティにてUpdateContext関数を組み込みます

UpdateContext関数で更新する変数はラベルのVisibleプロパティに組み込んでおきます

そして、Power Automateフローを起動する時には変数:myflowで受け取れるようにします

UpdateContext({myflow: 

             エクセルインポート.Run(

                                         {

                                         contentBytes: First(DataCardValue.Attachments).Value,                                                              

             name: First(DataCardValue.Attachments).Name

                                    }

              )

});

Power Automate内でのJSON形式への変換

ファイルの作成

Power Automateフロー内ではPower Appsから受け取ったファイルの内容を基にして、まずはOneDrive内に新たなファイルを作成します

作成されるファイル名は過去に作成したものと同じにならないように、現在時刻をファイル名に入れます

*OneDrive内では作成したファイルを削除したり、ファイル自体を上書き更新するには一定期間を空けなくてはなりません。その為、このような処理を行います

JSON形式への変更

選択アクション

UploadしたエクセルファイルをJSON形式に変換するには、データ操作コネクタの選択アクションを使用します

「表内に存在する行を一覧表示」アクションで読み込んだ内容を「選択」アクションでJSON形式に当てはめていきます

この時に「日時」と「数値」形式の内容については、関数を使用してきちんと該当の形式に変換しておきます

・日時形式 ⇒例:formatDateTime(item()[‘Date’],’yyyy/MM/dd’)

・数値 ⇒例:int(item()?[‘Sales’])

注意

「選択」アクション内で、うまく動的コンテンツでエクセルの内容を拾えないことがあります

この時は2つ対応方法があります

・動的コンテンツの工夫

通常「表内に存在する行を一覧表示」アクション内では下の画像のように、ファイルの指定内容は動的コンテンツにて指定します

選択アクションにて動的コンテンツを使用するまでは「パス」ではなく、実際のファイルパスを指定しておきます

選択アクション内で動的コンテンツを設定したら、元通り「パス」を設定し直しておきます

・式での指定

一度、コードのプレビューを見てみましょう

上の画像を見ると、開始の「value」で処理する内容は<item()?[‘列の中身’]>で抽出できることがわかります

実際に<item()?[‘列の中身’]>を使用する時は、下の画像のように「式」の中で@を入れないことも大きなポイントです

JSONの解析

JSONの形式変更の最後では「JSONの解析」アクションを使用します

スキーマは「サンプルから生成」から作成しますが、こちらは一旦「””」で設定してフローを動かして設定します

*あまりUploadするファイルの行数が多いと取得できませんので、その場合は行数が少ないサンプルファイルを使用します

フロー動かした後の出力結果をコピーし、どこかメモ帳にでもコピーしておきます

その後、サンプルから生成をクリックし、次に表示される画面にコピーしてスキーマを作成します

Power Appsへの受け渡し

作成したJSONはPower Appsに受け渡します

上の画像では「msg」としてPower Appsに受け渡しますが、Power Apps内ではそのまま「msg」では抽出できませんので注意が必要です

Power Apps内でのコレクションの作成

Power Automateから受け取ったJSONの内容は、次の画像のようになっています

コンテキスト変数内で更に「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の内容を全てコレクションにします

<まとめ>

今回はエクセルファイルをアップロードしてコレクションにする方法を解説しました

具体的な内容としては①エクセル⇒JSON、②JSON⇒コレクションというように2段階で変換を行うので少し重い内容となっています

但し、意外と躓きやすい箇所が他にあります

「表内に存在する行を一覧表示」のアクションです

既存の設定だと2つ注意点があります

1.出力される行数が256行に限定される

こちらは設定を変えておきましょう!

上の画像の設定をクリックすると次の画面が開きます

こちらで「しきい値」を変えると出力する行数の上限を引き上げられます

2.DateTime形式

こちらは「ISO 8601」にしておかないと、日付形式の箇所はエラーになります

「ISO 8601」の他に「Serial Number」というのもありますが、その場合は日付形式の内容を設定するには「addDays関数」を使用します

こちらについては後日、別途詳細な記事を書きたいと思います

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

Power Automate Desktopでメールに表を入れて送る方法

【HTMLを使用して、とにかく手っ取り早く表をメールの中に入れて送ります】

添付のメールだと見てもらえるかわからないので、メールの中に表を入れて送るケースも結構あるようですね

但し、これはエクセルの表を貼り付けるなどの手間があるので、すごく面倒です

RPAで送るにしても、貼り付けだと安定性があるとは言えません

ですので、Power Automate Desktopで表をメールに入れるHTMLコードを自動で作成してメールを送ってしまおうというのが今回の趣旨です

メールで送る表

今回メールで送る表はエクセルで作成します

行数は可変という想定です

但し、列数は固定という想定になります

表を入れる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・・・と表現します

使用するアクション

主に「テキストに行を追加」アクションを使うとフローが分かりやすくなると思います

追加した後は変数:Result(上の画像の場合)に格納するのですが、下の画像のような形にすると前述のHTMLコードが作られていく様子がイメージしやすいと思います

もちろん、こちらの変数:Resultはメールの本文に入れて送ります

繰り返し処理

まずは「テキストに行を追加」を使用して、表の見出し部分を作成します

その後は繰り返し処理を行列双方向で行い、表の中身を追加してきます

この時、行方向の繰り返し処理は最終行まで行うようにします

そして、1行の処理の開始及び終了時には<tr>と</tr>を追加します

開始
終了

表の中身を追加する際には<td></td>を組み合わせます

表の中身を追加し、繰り返し処理が終了した後は</table>を組み入れます

メールの送信

メールの送信時は、HTMLコードを設定した変数を組み込みます

そして、ここが最後のポイントですが、下の画像の「本文はHTMLです」をオンにしましょう

これで行数可変でメールの中に表を入れて送ることができます

<まとめ>

今回は手っ取り早く、コードを使用してメールの中に表を入れて送る方法を解説しました

今回紹介した内容では、罫線の形は下の画像の形でしか送れません

ぜひ、HTMLを解説する他サイトなどを参照して、罫線の形はご自身の好みに合うように変えてみてください

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

脱エクセルについて考える~資金繰り表をPower Platformで作成~第9回

【エクセルは本当に便利なツール。但し、効率と効果を上げるには限界があります】

資金繰り表はエクセルに向いている業務の典型です

それは何故か?

上の画像の黄色のセルには数式が入っており、預金残高の予想残高を計算しています

但し、この数式を必要に応じて実際の預金残高「数値」で上書きすることができます

実はこれってエクセル以外のソフトだと難しいんです

「数式」と「数値」を共存できるエクセルは本当に便利なんです

ではエクセルで業務を続けていたらそれでいいのか?

それはそれで問題があります

下の画像の「2023/1/5」の売上高の欄には120と入っています

入力数字は120でも、実は120という数字を別な表で複数の顧客の数字を積み上げて入力されているかもしれません

それに、表に存在する日付/列単位が膨大になってくると、入力箇所を見つけるのも面倒です

つまり量が膨大になってくると、この資金繰り表の1セルを入力する作業がかなり面倒になってきます

その理由は一般的なエクセルの資金繰り表では「データ蓄積」「作業」「計算」「表示」が分かれていないからです

ですので、作業や計算スペースを作る関係上、データの並びが不規則になってしまったりしますし、入力しやすいようなスペースを作ることも困難です

更に言えば、資金繰り表のシートのデータを活用して他のシートで別の目的の表を作成しようとするとかなり複雑なものになってしまいます

実は、Power BIのDAXを使用すれば預金口座の残高も、実残高と予想残高を共存させながら計算できます

今回の記事ではPower BIPower Appsを使用して、エクセルの資金繰り表の「データ蓄積」「作業」「計算」「表示」を分けて管理できる仕組みの概要を紹介します

そして、Power Platformの便利さに触れて頂けたら幸いです

ポイント

Power Platformを使って資金繰り表を作成するには、分解が結合がポイントです

データの分解・結合

預金残高は数式と数値を共存できるように、以下にデータを分けて管理します

・預金残高(実残高)

・入金

・出金

更にカレンダーテーブル

上記の4つをDAXで結合して使用します

今回はSharepointリストを使用します

入出力と表示の分解

今回は入出力はPower Appsで行います

Power Appsならページを分けて、売掛金や経費などの入出力専用ページを作成できます

そして、表示や主な計算はPower BIで行います

Power BIは集約計算の簡単にできるし、DAXで複雑な計算も行えますので、Power BIの機能もフル活用します

エクセルで日付毎に複数の顧客の売掛金を合計していた作業は、前述のテーブルを作成しておけば勝手にPower BIが行ってくれます

入出力と表示が分かれていると不便だろうという場合は、後述するPower BI内でPower Appsを使う方法もあります

預金残高を表示する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」と検索してください

すると「Beyondsoft Calendar」というビジュアルをダウンロードできます

こちらのビジュアルは日々の預金残高をカレンダー上で表示できるという優れものです

残高の状態によって色を変えたりもできます

エクセルではなくPower BIを使用することで、専用アプリを活用することもできるんです

Power BIとPower Appsの連携

Power AppsをPower BI内で使用したい場合は、こちらをクリックするところからはじめます

すると、Power Appsに使用するフィールドの入力ができるようになります

実は、こちらはなんでも構わないんです。取り合えば何か設定すればPower BI内で表示したいPower Appsのアプリを選択できるようになります

<まとめ>

今回は脱エクセルについて考えると称して、エクセルの資金繰り表をPower AppsとPower BIを連携させて作成する方法の概要を紹介しました

紹介したのはあくまで概要ですが、エクセル作業が改善されていく可能性を感じて頂けたら幸いです

現実に、エクセルは表計算ソフトですので大量のデータを処理するには向いていません

なので、Power PlatFormの仕組みを利用して「データ蓄積」「作業」「計算」「表示」を分解・結合できるようにすることはとても意義があります

そうは言っても、誰でも手軽に使えるエクセルと違ってPower BIのDAXなどが複雑であったりするので専門知識が必要です

それに当然、手間暇も必要です

ですので、最終的にはどの程度の量の業務をどんな風に変えたいかが一番のポイントなのかもしれませんね

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

ガッツの日記 第7回 「エクセルとSharepointリスト」それぞれのいいとこどりをする方法

【今回の記事はデスクトップのエクセルの新たな活用方法の提案です】

ここ数年でSharePointリストなどのクラウドサービスを利用する人はかなり多くなってきています

クラウドはネットさえつながればどこでも使うことができ、特にSharepointリストは複数人でデータを共有して仕事するのに便利な機能が満載です

履歴の復元が行えたり、登録者や更新日時が確認できます

何より3000万行を蓄積できるのがいいです

エクセルはあくまで表計算ソフトであり、大量のデータを蓄積していくのには本来は向いてません

蓄積できる行数も1シート100万前後です

一方、使い慣れたデスクトップのエクセルをこれからも使用していきたいという方も多いと思います

特にVBAを使って業務を回してきたケースの場合は、そのままデスクトップのエクセルを継続的に使用せざる負えないです

ここからが本題です

実は、

Sharepointリストとデスクトップのエクセルを組み合わせて使用する方法があります

ADOという仕組みを使うと「Sharepointリストからエクセルへのデータ取得」「エクセルからSharepointリストへのデータ追加・更新」が自動で行えます

以前、私はこのADOを使用して「Accessとエクセル」を連携するシステムを開発したことがあります

この方法ならばAccessにデータを一元管理しながら、利用者各自のエクセルファイルにて作業を行うことができます

何が一番素晴らしいかというと、売上管理や商品管理などの各種エクセルにてマスタを共通利用できる点です

エクセルでデータ管理を行っていると、複数ファイルでのマスタの追加や更新などの処理が煩雑になりがちです

ADOを使用した仕組みの場合は、Accessにてマスタ管理を行えば、利用者の各種エクセルファイルにてマスタを自動でダウンロードして使いまわすことができます

今回の記事では、Accessの代わりにSharepointリストを使用するわけですが、クラウドで利用できる点なども含めてかなり効率的な仕組みができると考えています

VBAを使い込んでいた組織では、VBAを引き続き有効利用しながらクラウドを活用できるのも大きいです

以降の記事では、Sharepointリストの特徴なども交えながらデータ取得・追加・更新のサンプルコードを解説していきます

尚、今回の記事はVBA経験者を主な対象としていますのでその点は予めご了承ください

事前準備

ライブラリーの追加

ADOをVBAで使用する場合には、Visual Basicのツールタブ⇒参照設定からMicrosoft ActiveX Data Objectsライブラリを追加する必要があります

ここが抜けてしまうと、コードをいくら書いても動きませんので注意が必要です

サイトURL及びリストID

ADOを使用してSharepointリストをデータソースとして使用する場合は「サイトURL」と「リストID」を使用します

サイトURLについては分かりやすいと思います

上記画像の「sites」の後と「Lists」の前までがサイトURLです

リストIDは普段聞きなれないと思いますが、Sharepointリストの内部名のことです

今回の記事で使用するSharepointリストのリスト名は「Sales_Test」になりますが、別の名前/IDをADO内では使用します

リストIDの取得はこちらから行います

上の画像の「リストの設定」をクリックすると、次に開く画面でリストIDが取得できます

こちらの「%7B」と「%7D」の間がリストIDになります

共通構文

以下、サンプルコードを実際に解説していくわけですが、共通の部分があります

******

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リストから取得し、②でレコードの最初に移動します

③~⑤でレコードの列名を繰り返し、イミディエイトウィンドウに表示します

取得した列名を見て頂くと、設定していない列が取得されます

ここが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

******

①のadOpenKeysetadoLockOptimisticは他に指定する方法がありますが、こちらの方法であれば大概は問題ありません

④⑤で追加データをワークシートから読み込んでいます

追加データが複数行ある場合は「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

******

データの追加も同様ですが、接続が行えない場合は「ロールバック処理/もとに戻す」を行えるようようにしておくのがよいです

<まとめ>

今回はADOの仕組みにより、Sharepointリストからエクセルにデータ取得したり、エクセルからデータ追加・更新を行う方法を解説しました

今回紹介した内容は基本的な内容であり、実際には条件式を組み合わせるなど様々なパターンが考えられます

実はSharepointリストとADOにて接続する場合、1点厄介なことがあります

ADOで扱うSharepointリストに参照列が存在する場合です

上の画像の一番右、CustomerName列は参照列です

他のSharepointリストから参照しています

参照列をADOで取得すると、次の画像のように参照しているSharepointリストのIDに置き換わってしまいます

ですので、IDを変換する仕組みが必要になります

データ追加の時も、参照列がある場合には同様にIDを変換する仕組みが必要です

とにかくSharepointリストを扱う上では「ID」が非常に重要です

この点は強く意識しておきましょう!

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

TOCOL関数とTOROW関数で縦横を変換

【実は関数1つで複数データの並びを変えることができるようになりました】

エクセルで行列を入れ替える作業を伴う転記作業はなにより苦痛なものです

関数1つで複数の値の行列を入れ替えられるのはとてもありがたいです

内容もとても簡単なものです

第二引数や第三引数を指定するパターンがあるので、そこだけは複雑ですがぜひ押さえておきましょう!

*注意点

既にTRANSPOSE関数という行列入れ替え、縦横入れ替えるという関数があります
今回は幅広い意味で記事内で「行列入れ替え、縦横入れ替え」については表現させて頂いております。明確な違いとしては、今回紹介するTOCOL関数とTOROW関数は1列もしくは1行に入れ替えます。ご理解のほど何卒よろしくお願いします

TOCOL/列に変換

第二引数無

TOCOL関数の中に行列を入れ替えたい範囲を指定します

=TOCOL(D5:F8)

TOCOL関数により、指定した範囲が1列に変換されます

読込順番は1行目⇒2行目⇒となります

値が無いセルについては0が出力されます

指定範囲の最終行まで繰り返し処理をしているのもポイントです

第二引数を1で指定

第二引数を1で指定すると空欄は無視して出力されます

更に第三引数をTRUEで指定

第三引数を指定すると、読み込みの方向が横方向から縦方向に変ります

TOROW/列に変換

第二引数無

TOROW関数の中に行列を入れ替えたい範囲を指定します

=TOROW(D26:F29)

TOROW関数により、指定した範囲が1行に変換されます

今回は第二引数を指定してないので、空欄は0で出力されます

第二引数を1で指定

第二引数を1で指定すると空欄は無視して出力されます

これはTOCOL関数と同様です

更に第三引数をTRUEで指定

TOCOL関数と同様に、三引数をTRUEで指定すると、データを横方向ではなく縦方向に読み込みます

<まとめ>

今回は行列を入れ替えるTOCOL関数、TOROW関数を解説しました

従来はVBAでないと最終行まで繰り返すような処理が行えなかったので、本当便利になったものです

また後日、詳細を解説しますが今回のような複数出力する関数は他の関数と組み合わせると、もっと威力を発揮します

ぜひ続編をお楽しみにしていてください

参考までに実際のデータも添付させて頂きます

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

ガッツの日記 第5回 ChatGPTと残念なエクセルについて議論してみた

【ChatGPTを活用して、日常的なエクセル業務の問題を解決する糸口にしよう!】

今日、【Schoo(スクー)】のライブ授業「議論のプロと考える ChatGPTのプロンプト」を受講しました

ChatGPTの素早い回答を糸口に議論をすすめるのは、なかなか有効だなと感じました

もちろん全てがChatGPTで解決するわけではないですが、ChatGPTとの壁打ちをすることで自分の頭の中もかなり整理されていくのがわかります

完璧にこだわらずにChatGPTと対話を続けるのが有効活用のポイントのようです!

今回は早速、ChatGPTと「残念なエクセル」について議論しました

議論の様子を皆さんにも共有したいと思います

ちなみに本日の講師の方のプロフィールがとてもユニークでした

やはり優秀な方は色々な仕事を生み出していけるのですね!

残念なエクセルの定義について聞いてみた

以下がChatGPTに投げたプロンプトです

箇条書きで・と指定すると個人的には分かりやすい答えが返ってきやすいです

ChatGPTの答えは以下です

↓↓↓

業務効率を下げるような残念なエクセルの定義を以下に箇条書きで示します。これらの要因は、エクセルの不適切な使用や設計に起因するものです。

  1. 複雑な数式と関数:
    • 複雑で理解しにくい数式や関数が多用され、他の人がシートを理解しにくくなる。
  2. 使い捨てのデータ入力:
    • 一時的なデータをエクセルに保存してしまい、データベースなどの適切なツールを使わない。
  3. 繰り返しの手作業:
    • 自動化できる作業が手作業で行われ、時間の無駄になる。
  4. 不十分なデータ整理:
    • データの整理やクリーニングが不十分で、エラーや不正確な情報が含まれる。
  5. 複数人の同時編集の混乱:
    • 複数の人が同時にエクセルファイルを編集し、競合や混乱が発生する。
  6. ファイルの適切なバージョン管理の欠如:
    • 適切なバージョン管理が行われず、誤ったデータや情報が広まる。
  7. セキュリティの脆弱性:
    • 機密情報が適切に保護されておらず、セキュリティの脆弱性が存在する。
  8. 標準化の不足:
    • 統一されたフォーマットやスタイルガイドが不足し、一貫性が欠ける。
  9. バグやエラーの無視:
    • ファイル内のバグやエラーが無視され、問題の修正が行われない。
  10. 過度な依存:
    • エクセルに過度に依存し、他の有用なソフトウェアやツールの活用が不足している。

これらの要因がエクセルの不適切な使用に関連しており、業務効率を低下させる可能性があります。効果的なエクセルの使用には、適切なトレーニング、データ管理、セキュリティ対策、標準化などが必要です。

↑↑↑

2番目に「使い捨てのデータ入力」とあるのですが、これは良く残念なエクセルの特徴を拾い上げているな、と思いました

こちらをChatGPTに再度、聞いてみます

↓↓↓

  1. 会議の出席者リスト:
    • 会議の出席者リストをエクセルに入力し、会議後に使わなくなる。
  2. 電話メッセージの記録:
    • 受けた電話のメモや伝言をエクセルに一時的に記録し、後で削除する。
  3. 一時的な計算結果:
    • 特定の計算を行い、その結果をエクセルに入力して後で必要なくなる。

*一部省略

↑↑↑

この回答だと意味合いは分かるのですが、何が問題かが明確に分からないので再度問いかけてみます

↓↓↓
はい、正確です。使い捨てのデータ入力は、データの規則性や整合性を損なう可能性があります。これは、一時的なデータがエクセルに入力されたり、削除されたりすることで、データの一貫性や整合性が維持されなくなることを指します。具体的には以下の点に注意が必要です:

  1. フォーマットの一貫性の喪失: 使い捨てのデータ入力が行われると、データのフォーマットや構造が一貫性を失い、後でデータを分析や処理する際に問題が生じる可能性があります。
  2. 不正確なデータの記録: 一時的なデータが正確に記録されなかったり、誤った情報が入力されたりすると、データの信頼性が低下します。

*一部省略

↑↑↑

何度か問いかけを繰り返すことで、なかなかシャープな表現が返ってきますね

「表現を変えながら何度も問いかけること」これがChatGPTとの対話のコツですね

解決策について聞いてみた

次に具体策を聞いてみます

↓↓↓
データの規則性を維持し、使い捨てのデータ入力に関連する問題を解決するための具体的な解決策を以下に箇条書きで示します:

  1. データ入力の標準化:
    • データ入力のフォーマットや構造を標準化し、一貫性を保つためのガイドラインを策定します。
  2. 自動化:
    • 一時的なデータ入力が自動化できる場合、スクリプトやツールを使用して手作業を最小限に抑えます。
  3. データベースの活用:
    • 使い捨てのデータ入力よりも長期的なデータ管理のためにデータベースを活用します。データベースはデータの整合性を維持しやすく、クエリや分析にも適しています。

*一部省略

↑↑↑

かなり具体的に聞かないとやはり明確な答えはかえってこないですね

ただ、解決の糸口はそれなりに網羅した答えは返ってきます

ちなみに「ウナギの寝床」横に長いエクセルについても聞いてみました

やはりピボットというキーワードはきちんと入っていますね

結論から言うと、どう解決策を聞くかによるのですが、議論のための資料作りとかには十分役に立つと思います

<まとめ>

今回はChatGPTと「残念なエクセル」について議論してみました

今回はテーマが大きすぎましたが、壁打ちのごとく対話をすればそれなりの答えが返ってくると思います

少なくとも、会議資料みたいなものを作るには十分役に立つと思います

ChatGPTから返ってくる表現は非常にシャープで、網羅性が高いです

会議資料を作成する時には、キーワードを紙に書き出したりするのですが、そのキーワードの網羅作業はChatGPTで十分です!

個人的にはもっとChatGPT、AIを活用していきたいです

【Schoo(スクー)】では「その作業はAIにやらせてみよう」をシリーズで授業がありますので、ぜひスケジュールを確認してみてください!

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

ガッツの日記 第4回 残念なエクセルに出会ったら

【上司はあなたが毎日、エクセルの最終列を探す旅に出ているのを知っていますか?】

私が出会ったエクセルの中で、残念なものの1つが「最終列を探す旅」をするエクセルです

別名は「The ウナギの寝床」です

横長になっている表はいくらでもありますが、私が出会った中で1番凄いものは列がALLを超えてきていました

何故、ここまで横長になるかというと、クロス表の信仰があるからです

表を作る方からすればクロス表にすれば見やすくなるような気がするし、横に延ばすだけで設計が要らず、作るのも楽です

ただ、その後に検索したりするのは大変です

エクセルは列方向にフィルター処理が行えないからです

ですので、最終列を探す旅になります

もちろん、この手の表で共有の管理表を作成して、後で集計するという場合はもっと大変です

各自に口頭で聞いて、集計した方が早いかもしれません

では、

この手の残念なエクセルに実際に出会った時にはどうしたらいいかでしょうか?

それが、この記事のテーマです

組織の壁

他の部署の人がこの手の表を見たら「変えたらいいじゃん」と思います

ところが、一度回りした仕事のツールを変えるのは容易ではありません

ましてや、皆で入力して管理しているエクセル表なら尚更です

「自分が変えます」と手を挙げた途端、以下のようにババを引くような形になるからです

・表を変えて万が一、うまく行かなかったら全て責任を負うような形になる

・必ず誰かは「前の方が使い慣れててよかった」と言う人がいる

・そもそも現在の表のデータを新しい表にどう移行するか?自分が手動で移行することになりそう

・そもそも本来の業務で忙しい

そうして、放置した結果、ますます皆が使い慣れていき、表を変えにくくなるという悪循環が起こります

しかも使い慣れるといっても、業務のスピードがそんなに上がるわけではないのです

大抵、

こういった「残念なエクセル」が業務効率を下げているケースでは、上司がよくそのことを認識していないケースがあります

本日、【Schoo(スクー)】の講座で「ルール(組織のしがらみ)がイノベーションの壁」を受講しました

DX推進者を物語の中の「勇者」の置き換え、DXの目的を「ラスボスを倒すこと」に置き換えて解説しているのですが、これがよく分かりやすい内容になっていました

詳細はぜひ【Schoo(スクー)】のページで確認していきたいのですが、秀逸だと思ったのはDXの推進を阻む「組織の壁」の表現です

〇現場無視系

王様/上司が現場を見ていない

無駄な気飾り/本質とかけ離れた指示

予算がおりず武器が買えない

〇遅延系

え?道具屋のチェックが必要なんですか?

ネットで武器を買おうと思ったらサイトがブロックされている

(以降省略)

この上の「上司が現場を見ていない」というケースは、表現をこの記事の趣旨に沿って変えると「たかがエクセルの表一つで業務が停滞している」ことが伝わっていないケースです

このケースは良くみかけます

先日もある方から「長年、部下がまさかこんなエクセル処理を行ってるとは思ってもみなかった」ということで相談を受けました

よく話を聞いたら、基本的なVBAの処理で済むものでした

場合によっては、管理部門から「残業が多いと注意された」、では「早く帰れ!」と上司が指示を出すこともあるでしょう

ババを引かないためにも、上司に問題の所在を説明して組織全体を巻き込んでいくのがやはり第一歩になるべきです

そうでないと、日常業務がこれまでと同じように降ってきて「業務改善」どころではありません

「そうはいっても、問題の深刻さを分かってもらえない」というケースもあるでしょう

そんな時にはIT部門の力を借りてでも粘り強く説明することが必要かもしれません

技術的対策の実際

では、実際に対策をどうたてたらいいか?

あくまで私がこれまで見てきたケースですが、残念なエクセルの根本的な問題の多くは「データそのもの」にあります

冒頭で紹介した「ウナギの寝床」はまさに典型的です

見やすさや作りやすさを優先して作られています

他の言葉で言い換えると、データの蓄積画面と作業画面を一緒に考えてしまっています

一緒にすると見やすいし、作りやすいような気がします

本来は、データの蓄積と作業は別画面にすべきです

データの蓄積であれば、ウナギの寝床を列数を固定にし、「縦縦の繰り返し」に並べ替えて蓄積します

ここがスタートです

そして作業画面を別途作成します

例えば、データを閲覧するだけであれば、ピボットテーブルで変換することも「データがきちんと蓄積」されていれば可能です

入力が伴う作業画面を別途作成する作業はAccessを使用したりするなど、技術が必要な時もあります

ただ、今ではローコードの技術もあるのでハードルが下がっています

Power Queryで各自が入力したエクセルファイルを、フォルダを通じて集約する技術なんかはオススメです

更新ボタンを押せば、ファイル内の複数ファイルが1つの場所に集約されます

あくまで、入力は「クロス表」で縦横並びにしたいという場合も、ピボット解除行列入れ替えの技術があります

<まとめ>

今回の記事では「ウナギの寝床」のような残念なエクセルに出会った場合の対策について書きました

とにかくこの手の業務を著しく停滞させるエクセルに出会った場合には、組織を巻き込んで対処するのが先決です

放置をすると、どんどん悪循環が起きます

技術的には、まずデータを規則正しく蓄積することを考えるのが先決です

Sharepointリストを活用することもかなりオススメです

3000万行保管でき、行単位でバージョン管理や変更者の特定も行えます

いや、どうしてもクロス表でしか管理できないです、ですから「エクセルじゃなきゃダメです」というケースでもPower Appsという方法があります

詳細はこちらの記事で紹介しています⇒詳細

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ