タグ別アーカイブ: excel

【取得するファイルを自動で可変にする、究極のPower Query術です！】

この記事を見ている読者の方の中には、毎日システムから出力されるファイルを処理しているという方もいらっしゃると思います

この場合、Power Queryで出力ファイルを処理する場合は、既存のクエリのデータソースを変更するか、新規にクエリを作成するしかありません

実は、フォルダ取得のクエリ技術を利用すれば驚くほど簡単に「常に最新」のファイルを取得できます

フォルダ取得

下の画像のフォルダには拡張子が「xlsx」のファイルと「csv」のファイルが１つづつ格納されています

こちらのフォルダを取得先にしてクエリを作成してみます

上の画像では、3行目のcsvのファイルに対応する箇所がエラーになっています

ソースステップでフィルター

上の画像では最終ステップがエラーになっていました

ここで最初のステップ、「ソース」を選択してみます

そうすると、フォルダ内のファイルが一覧化されています

では、もっと右にスクロールしてみます

そうすると「Date created」という列があります

こちらの列では該当のフォルダにファイルが格納された時刻が記載されています

今、記事を書いている時間は「6月26日17時01分」なのですが、試しにフォルダに新たなファイルを格納してみます

そして、Power Queryエディターのプレビューの更新処理を行ってみます

すると新たなファイルが「17時01分」に追加されています

ですので、こちらの「Date created」の列でフィルターを行うと、常に最新のファイルだけを処理することができるのです

ここで「最も早い」「最も遅い」という日本語訳が分かりにくいかもしれません

あくまで「最も遅い」、つまりファイルが格納された時刻が最も遅いものが最新のものになります

これで最新のファイルを取得する仕組みは完成です

もし、エクセルファイル(xlsx)の中でフィルターしたいという場合には、先に拡張子でのフィルターを入れておきましょう

その後に「Data created」列のフィルターを入れます

では、もう一度試してみましょう

今、フォルダがこの状態です

このフォルダに「担当_田中.xlsx」ファイルを格納します

そうすると、エクセルファイルで最新の「担当_田中」のデータだけがクエリに反映されます

＜まとめ＞

今回は最新のファイルだけを反映するクエリを作成しました

ポイントは２つです

１．フォルダからファイルを取得すること

２．作成したクエリのソースステップでフィルタ処理をすること

驚くほど簡単ですよね

最後に、場合によってはファイルの名前、つまり「name」列でフィルターが必要なケースがあることもお伝えしておきます

にほんブログ村

虎の巻

エクセルVBA虎の巻

2024年7月20日 analytic-vba コメントをどうぞ

この記事では、私がこれまでVBAを使用してきた中で「これは使える」と思ったコードを抜粋したものです

ファイル保存

エクセルシートのCSV出力

出力したいシートをコピーして、新しいファイルを作成し、ファイル名を指定して保存します

ThisWorkbook.Worksheets(“出力”).Copy
ActiveWorkbook.SaveAs “D:\TEST.csv”, FileFormat:=xlCSV
ActiveWorkbook.Close

文字列操作

分割

文字列の分割にはSplitを使用します

Variant型で設定した変数をSplitで分割し、変数(0)、変数(1)、変数(2)で受け取ります

例えば、シートのB1セルに「600-1」の文字列があるとします

こちらの文字列を「-」で分割し、それぞれをメッセージで表示するとします

その場合は、次のコードで実現できます

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を取りだします

次にmyWORD(1)で1を取り出します

固定文字が含まれているかを判断する

Instr関数を使用し、０以上なら含まれていると判断します

Dim myCountWaritsuke As Long ‘固定の文字が含まれいる位置
myCountWaritsuke = InStr(“myTEST1”, “TEST”)

MsgBox myCountWaritsuke

上のコードの場合は、myTESTの文字列の3文字目にTESTの文字列が含まれているので3とメッセ―ジが出力されます

文字列への変換

数値の文字列への変換にはStr関数を使用します

Str関数を交えた下のコードを実行してみます

Dim myVal As Long

myVal = 1

Worksheets(“Sheet1”).Range(“B2”).Value = “A-” & Str(myVal)

するとB2セルに以下のような文字列が入力されます

ここで1点問題があります

半角の空白がStr関数で変換した文字列に入ってしまっています

こちらが気になる場合はCstr関数を使用します

Worksheets(“Sheet1”).Range(“B2”).Value = “A-” & CStr(myVal)

これで空白は入らないようになります

数値への変換

こちらはVal関数を使用します

Dim myVal As String

myVal = “1”

Worksheets(“Sheet1”).Range(“B2”).Value = Val(myVal) + 1

上のコードを実行すると「２」がB2セルに入力されます

数値かどうかの判定

対象の’文字’が数値かどうかの判定は「IsNumeric」を使用します

試しに次のコードを実行してみます

MsgBox IsNumeric(123)

そうすると次の画像のようにTRUEが出力されます

次に以下のコードを実行してみます

MsgBox IsNumeric(“TEST”)

この場合はFALSEが出力されます

注意して頂きたいのは、次の場合はTRUEが出力されます

MsgBox IsNumeric(“１２３”)

これはあくまで１２３が数値文字だからです

メール関連

表をメールの本文に入れる

シート内の表の内容をHTMにするのがポイントです

＊＊＊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 & “”

＊＊＊＊＊＊＊＊＊＊＊＊

そして、作成した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

これでエクセルシート内の表がメールの本文に入ります

ファイルを添付して送付

.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列で重複削除を行うとします

この場合は、次のようなコードを書きます

Dim myLast As Long ‘最終行

myLast = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row

Range(“A1:C” & myLast).RemoveDuplicates (Array(1, 2))

これで次のように削除されます

シート操作

全ページ操作

ファイル内の全シートを一律に操作するには「For Each ~ in ~ Next」構文を使用します

次の画像では、各シートのA1セルに値が入っています

そして、シートB以外はA1セルの値を読んでメッセージを表示するようにします

コードは一度、シート変数：wsTESTを宣言した後に次のように書きます

For Each wsTEST In Worksheets

If wsTEST.Name <> “B” Then

wsTEST.Activate
MsgBox Range(“A1”).Value

End If

ちなみに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

にほんブログ村

虎の巻

Uploadしたエクセルファイルを直接コレクションにする～Power Apps～

2024年2月15日 analytic-vba コメントをどうぞ

【エクセルファイルをSharepointリストなどを経由せず、直接Power Apps内で編集できるようにします】

エクセルファイルの内容をSharepointリストに反映する前に、一旦編集したいケースもあると思います

その場合、一旦Sharepointリストに反映してしまうと「削除」を行う必要がでてくるなど、やたらと時間がかかることがあります

今回はJSON形式を経由して、直接エクセルファイルの内容をPower Appsに取り込みます

記事の解説は大きく分けると、次の３つになります

・エクセルを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’])

注意

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

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

・動的コンテンツの工夫

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

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

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

・式での指定

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

上の画像を見ると、開始の「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からコレクションを作成するには、２つの関数を使用します

「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段階で変換を行うので少し重い内容となっています

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

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

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

１．出力される行数が256行に限定される

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

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

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

２．DateTime形式

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

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

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

にほんブログ村

ガッツの日記

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

2023年11月27日 analytic-vba コメントをどうぞ

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

ここ数年で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リストの特徴の１つがあるからです

******

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はレコードを追加すると「１，２，３・・・」と自動採番されます

このIDは今回もSharepointリストの更新で使用します

ちなみに、使用した接続やレコード一式は必ず閉じる/Closeするようにします

データの取得

Sharepointリストからデータを取得するには、２つの方法があり、列別に取得する方法とレコードセットを丸ごと取得する方法です。

列別

前述の列名で取得した「列名」をここで使用します

*太字の部分です

******

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

******

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

＜まとめ＞

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

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

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

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

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

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

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

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

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

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

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

にほんブログ村

ガッツの日記

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

2023年9月21日 analytic-vba コメントをどうぞ

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

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

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

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

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

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

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

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

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

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

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

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

ChatGPTの答えは以下です

↓↓↓

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

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

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

↑↑↑

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

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

↓↓↓

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

＊一部省略

↑↑↑

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

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

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

＊一部省略

↑↑↑

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

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

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

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

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

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

＊一部省略

↑↑↑

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

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

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

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

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

＜まとめ＞

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

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

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

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

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

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

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

にほんブログ村

ガッツの日記

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

2023年9月20日 analytic-vba コメントをどうぞ

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

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

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

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

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

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

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

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

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

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

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

では、

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

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

組織の壁

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

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

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

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

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

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

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

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

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

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

大抵、

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

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

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

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

〇現場無視系

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

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

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

〇遅延系

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

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

（以降省略）

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

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

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

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

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

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

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

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

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

技術的対策の実際

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

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

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

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

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

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

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

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

ここがスタートです

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

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

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

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

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

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

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

＜まとめ＞

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

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

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

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

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

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

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

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

にほんブログ村

ガッツの日記

ガッツの日記第3回私が愛した残念なエクセルを紹介1

2023年9月20日 analytic-vba コメントをどうぞ

【エクセルを見た途端に’絶望’したことはないですか？何回かに分けてこれまで出会った残念なエクセルを紹介していきたいです】

今回の記事では私が実際に作業したエクセルの中で、衝撃を受けた２つのエクセルを紹介します

最後まで記事を読んで頂いても参考になるかは分かりませんが、エクセルの面白さなどを感じて頂けたら幸いです

ちなみに最近、このチャネルが気に入っていて「絶望」という言葉に凝ってます

https://www.youtube.com/watch?v=rwBy8Z5-ONk

10万行にチャレンジ

まず最初は以前、ある会社で毎月作業をするように引継ぎを受けたエクセル表です

記憶も定かでないでないので、詳細は割愛しますがパズルのような作業を10万行分行います

次の画像をご覧ください

Cの列にまだ値は入力されていません

A列とB列の値を見てから、C列に入るものを判断して値を入力します

例えばこんな感じです

・A列：A、B列：A⇒C列：〇

・A列：A、B列：B⇒C列：△

この作業を10万行分行います

ちなみにこのA列とB列の組み合わせは20通りぐらいあります

もちろん、値が同じ行が連続してたり、空白が続く行があったりするので純粋に10万行入力するわけではないです

ただ、それでも物凄い量です

前任者は月に1回は丸一日、この作業に没頭していたようです

「月1回我慢すればいいよ・・・」

と言われても、この手の作業は苦手なので「絶望」の2文字が頭をよぎりました

もう考えるだけで憂鬱です・・・

後から振り返ると、前任者に「こうやっている」と断言されたので視野が狭くなっていたかもしれないですね・・・

しばらくするとすぐに解決策が思い浮かびました

複合キーを作成してマスタを作成することです

こんな感じです

マスタを作成したら元の表でも＆を使用して複合キーを作成します

最後にVLOOKUP関数で先ほどのマスタを参照します

IFERROR関数も組み合わせるとなおいいですね！

更にテーブル機能を活用すると、関数は１つのセルに入力するだけで済みます

関数の入力に苦しんでいる方にはぜひ活用して欲しい機能です

ということで、前任者が丸一日かかっていた作業を1分で行えるようになったのですが、何より絶望感から解放されたのがよかったですね

今はエクセルでもいろいろな機能があるので、残念なエクセルに出会っても必ずしも絶望する必要はないですね

セルの値が消えた！？

以前、勤めていた会社では長く受け継がれたエクセルがあり、予算を作成した後にその伝統のエクセルにもコピーし、毎月実績をメンテナンスする必要がありました

誰がどう見ているのかが不思議な内容でしたが、やはり伝統は守るためにあるものです

時間が限られている中で、その伝統のエクセルに予算をコピーする際、不思議なことが起こりました

上期の予算、4月～9月分を伝統エクセルにコピーしました

するとコピー先にコピーしたい内容が反映しないのです

9月まで反映せず、本来7月分の箇所に8月分が反映しているのです

とにかく時間が無いので焦りました

何度もコピーしたのですが、うまくコピーできません

実は

列が非表示になっていました

ここまで引っ張って「それだけ？」と思われた方もいらっしゃると思いますが、まだエクセルになれておらず、しかも時間が無い中なので全く気づけず焦るだけでした

伝統エクセルの非表示の箇所には四半期の数字が入っており、上期小計は１Q＋２Qを合計する数式が入っておりました

こうしておいてもらえれば、1年分を一括で入力できたんですがね・・・

後から

前任者から「俺も最初気づけなくて参ったよ・・・」と苦笑いされたことも付け加えておきます

＜まとめ＞

今回はこれまで出会った残念なエクセルの第一弾として２つの事例を紹介しました

今回の事例は残念なエクセルというより、残念なエクセルの使い方という方が正しいかもしれません

少なくとも１つ言えることは、セル結合もそうですが「非表示」は不用意に使わない方がいいですね

非表示にした本人も忘れていることもあります

話は一見変わりますが、本日、【Schoo(スクー)】で田中亨先生のエクセル授業を受けました

内容は、スピル関数からデータモデル、Pythonなど多岐にわたるものでした

本ブログでもPower Queryについてはかなり力を入れて紹介していますが、今のエクセルはかなり多機能です

絶望せずに解決策を探せば、案外簡単に解決策が見つかることもあります

新しいエクセル機能を研究してみると役に立つことも多いです

最後に、

エクセルの引継ぎは、常日頃からきっちり行えるようにしておきたいものです！

にほんブログ村

お仕事の依頼

　実務を担う現場の方が使い慣れたエクセル。Excellent仕事術は、このエクセルを効果的に活用して、御社の業務改善やお困りごと解決の最適なスキームをご提案／ご提供します！

御社にこんな悩みはございませんでしょうか？

・エクセル作業がブラックボックス化しており、膨大な人手がかかっている

・分析資料は沢山あるが、業績改善に向けた課題や傾向が全く把握できない

・プレゼン資料にどこかインパクトが無い

　Excellent仕事術では、これらの悩みに対して「現場重視」で現場の声を反映させながら解決策を提案します！

　ご相談自体は無料でうけたまわります。エクセルに関することであれば、どんなことでも幅広く「Ｅｘｃｅｌｌｅｎｔ仕事術のガッツ鶴岡」へご相談ください！

プロトタイプ（試用版）の開発についても低価格で請負ます！

お問合先

Excellent仕事術の得意分野

VBA/マクロ開発

エクセル作業がブラックボックス化する理由は、大きく分けて３つあります。

・エクセルシートのスパゲティ化

・データのバケツリレー

・エクセルファイルの共有

　Excellent仕事術では従来のIT部門では対応できなかったこの３つの問題を、これまで培ってきたマクロ/VBA開発力で解決します。

エクセルシートのスパゲティ化

　エクセルシートのスパゲティ化は、1シートに「データ」「計算」「作業スペース」を詰め込むことで起こります。

　異なる内容が詰め込まれている為、「どこの範囲がどの内容」で、「どの範囲とどの範囲がどのように絡み合っているのか？」が第3者に分かりにくくなります。

　スパゲティ化したエクセルシートは、作成した担当者しか扱えないだけでなく、データが不規則に蓄積されているため有効活用ができず効率化が行えません。

　スパゲティ化したエクセルシートを改善するポイントは、「データ」「計算」「作業スペース」を1シートに混ぜず、それぞれシート毎に集約することです。

　Excellent仕事術ではスパゲティ化したエクセルシートを、３つのステップで劇的に改善します。

・Firstステップ

　　　➡ ヒアリング及びシートの中味の分析

・Secondステップ

　　　➡ シートを「データ」「計算」「作業スペース」などの目的別に作成

・Thirdステップ

　　　➡ 作業スペース・シートでの作業をVBAにより自動化

　改善したエクセルシートでの作業は効率化されているだけでなく、特定の担当者以外でも引継ぎの負担なく作業が行えるようになります。

データのバケツリレー

　エクセルファイルでデータを外部の会社や他部署とやり取りすることは、良く行われていると思います。

　データ活用の目的が外部の会社や他部署と違う為、「エクセルファイルの交換」はファイルやシート間での膨大な「データの転記処理」を発生させることになります。

　実は、VBA/マクロを活用すれば複数のファイルやシートの「データ取込み・変換処理」を、一括で行うことができます。

　一括で「データ取込み・変換処理」が行えれば、膨大な転記処理から解放されるだけでなく、取り込んだデータを活用して「周辺業務」の効率化も行えるようになります。

　Excellent仕事術では、現場の声をお聞きして「データ取込み・変換処理」から「周辺業務の改善」まで徹底的にサポート致します。

　エクセル1ファイルで大量のデータを扱う場合によく出てくる問題として「共有」の問題があります。

　エクセルは表計算ソフトであり、本来は大量のデータを扱うには不向きです。1シートに蓄積できる行数に制限があり、蓄積データが大量だと動きが重たくなります。

　しかも、 1ファイルを複数の担当者で同時に共有して使用できない為、担当者に待ち時間が発生してしまい、どうしても業務効率が上がらなくなります。

　Microsoft社の製品では、データベースソフトとして「Access」があります。Accessであれば、行数に関係なく大量のデータを扱うことができ、複数の担当者で共有して使うことも可能です。

　ところが、Accessだと現場では行えない業務も中にはあります。特に「大きなクロス表での作業」などは、エクセルのような表計算ソフトを業務用にカスタマイズした表でないと効率があがりません。

　こういったエクセルでないと行えない業務を、複数担当者でエクセルファイルを共有しながら作業したい場合には抜け道があります。

エクセルとAccessをVBA技術により連携して使うという方法です。

　この方法であればエクセルとAccess、互いのメリットを組み合わせて業務を行うことが可能です。

　このエクセルとAccessn連携する仕組みでは、データはAccessに一元管理し、エクセルには都度、必要なデータだけ呼び出して活用します。

　このエクセルとAccessの連携を行う場合にポイントとなるのは「エクセルの役割」と「Accessの役割」の切り分けです。

　Excellent仕事術では、連携の仕組みを構築するだけでなく、業務を理解させて頂いた上で「連携のポイント」についても提案させて頂きます。

➡事例①

データ分析/統計処理

「数値/分析資料は沢山あるが、業績改善に向けた課題や傾向が全く把握できない」ことの主な原因は、鳥の目で全体像を捉えることができていないことが考えられます。

　例えば「前期と比べて注文単価が何故下がったか？」について、注文単価自体の推移を見ても、単位が荒すぎて「何故下がった」かはよく分からないはずです。

　逆に注文台帳を見ても、今度は単位が細かすぎて「注文単価が前期と比べて何故下がったか？」はよく分からないはずです。

　では下に記述したように、注文を単価別にグループ分けして件数の推移を確認した場合はどうでしょう？

・低単価の注文件数の推移

・中単価の注文件数の推移

・高単価の注文件数の推移

　低単価の注文内容と高単価の注文内容では、注文に含まれる商品の違いや、注文した会員の行動や属性に違いが必ずあるはずです。

　低単価の注文件数の構成が高くなっているのか、もしくは高単価の注文件数の構成が低くなっているのか、を注視すれば「注文単価が下がった原因」はある程度見えてくるはずです。

　このように業績改善に向けた課題や傾向を捉えるには、必ず鳥の目でグループ別の推移を捉えることが必要です。

　このグループ別の推移を捉えるのに、必ずしも「データアナリスト」や「CRMソフト」が必要とは限りません。エクセルの集計機能と統計機能を駆使すれば、エクセルでも対応は可能です。

　Excellent仕事術では、これまで培ってきたエクセル集計技術と統計処理・技術を活用して、御社の数字分析のお手伝いをします。

会員分析

会員分析のポイントは大きく分けて3つあります。

・新規会員の獲得状況　➡会員数自体の分母を増やせているか？

・新規会員の2回目のリピート状況　➡新規会員が定着しているか？

・既存会員のリピート状況　➡優良会員が確実に育成できているか？

　つまり、まずは「新規・既存」と「優良・非優良」の２つの切り口で会員のグループ分け（セグメント）を行った上で、会員のグループ間の移行状況を把握することが必要です。

　Excellent仕事術では上記の２つのグループの切り口から、更に深堀した切り口でグループ分けを行います。

そして、基本の「新規・既存」「優良・非優良」のグループに加えて、更に深堀りしたグループ間の移行率を把握するためのツールを提供します！

➡事例②

その他の分析

　Excellent仕事術では、発注・在庫分析や予算作成・中期計画のシミュレーションなども得意分野としております。

ぜひ、この分野についての悩み事もExcellent仕事術にお問い合わせしてみてください！

ダッシュボード化

　数字分析を行った内容については、社内で共有し、次の行動につなげていくことも大事なことです。

　Excellent仕事術では社内の効率的な業績報告、情報共有のために一目で現状を掴むことができるダッシュボードの作成も行います。

プレゼン・資料作成

　競合他社とのプレゼン競争に勝つためには、「数字の魅せ方」に突破口があります。海外のエクセル活用事例をうまく取り入れた「数字の魅せ方」こだわりましょう。「数字なんてただの数でしょ？」。そんなことはありません。「目で見て、数字の変化がリアルにわかる」効率的な資料作成術を、Ｅｘｃｅllent仕事術/ガッツ鶴岡がご提供します

-シンプルで誰の目も釘付けにするグラフ関連資料の作成をいたします。

Excellent仕事術/ガッツ鶴岡の強み

業務分析の経験が豊富

-上場企業から創業期のベンチャー企業まで、様々な事業規模での業務分析の経験が豊富

-内部監査も含めた内部統制対策をとおして、業務フロー図等の作成・業務分析の経験多数あり。

数値管理の経験が豊富

-銀行や証券会社等の金融機関への数値説明・交渉の経験が豊富

-EC通販での予算管理や中期計画作成、各種分析などの経験が豊富

事例①

＜商品管理システム/エクセルとAccessの連携＞

　業務メンバー各自が業務に即したエクセルのクロス表を使用しながら、エクセルへの入力データはAccessファイルに蓄積して各自で共有する仕組みを構築

＜改善前・課題＞

★１つのエクセルシートに様々な業務データと入出力表が混在

・商品の特性上、入荷した商品を手動で分割・並べ替えて管理する必要があった

・商品の並べ替え順を各業務で使い回すため、1シート上に様々なデータが混在

・データ蓄積型式が不規則になり、VBAなどの自動化手段の活用が不可能

・1ファイルをメンバー全員で共有して使用するため、業務メンバーに待ち時間が発生

＜改善後・効果＞

★業務データはAccessに一元管理し、エクセルの入出力画面を業務毎に分散

・データはAccessに一元管理し、ADOという技術を使用して、都度、必要なデータのみエクセルファイルに呼び出す仕組みを構築。このADOの技術により、１つ１つの業務に即した画面作成が可能になった。さらに他の業務メンバーがファイルを使っている間に発生していた待ち時間も削減できた。

・Accessに一元管理したデータを、ＲＰＡに連携して会計システムへの入力を自動化した。これにより、残業時間が1月あたり２００時間減らすことに成功

事例②

＜EC通販における会員のセグメンテーション＞

　会員の1年間の購買履歴とアンケートの集計結果を分析し、会員のセグメンテーションを実施。それまで一律にマーケティング施策を行っていたが、会員のセグメント毎の施策（例：セグメント別の割引率設定など）を実施できるようになった

実績

-商社・畜産部門向け商品管理システムの開発

　➡エクセルとAccess及びRPAと連携処理

-客先マスタ登録業務用システム開発/エクセル・RPA連携

　➡エクセルVBAにてRPA入力用データを成型

-EC通販会社での会員分析・マーケティング施策立案

　➡エクセル分析ツールにて会員の購買履歴を統計処理にて分析

-EC通販会社向け在庫管理システム

　➡適性発注率をエクセルを使用して分析

☆ストリートアカデミーで講師もしています

経歴

Excellent仕事術運営者：鶴岡　敦（ガッツ鶴岡）

　新卒で総合商社に入社後、ユニクロに転職。更にEC通販会社に経営企画室長として入社。

　EC通販会社にて膨大なデータ量に苦戦。必要に迫られて自らエクセル学習を重ねるうちに、海外の活用例などを知るようになり、エクセルの可能性に目覚める

1993年慶應義塾大学商学部卒

2002年USCPA合格

大学在学中にプロボクサーとしてデビュー

現在もボクシングジムで練習を続けている

Power Automate Desktop虎の巻

POWER AUTOMATE DESKTOPでエクセルをうまく活用する方法～

2023年4月21日 analytic-vba コメントをどうぞ

Power Automate Desktopをうまく活用すれば、VBAを覚えるよりも簡単にエクセル操作を自動化することができます

今回はPower Automate Desktop（以降、PAD）でエクセル操作をうまく行う方法を紹介します

ショートカットキーの操作

一般操作

PADのエクセルメニューは実際の操作内容と比べると豊富とは言えません

但し、ショートカットキーをうまく使うことでカバーが可能です

例として列の並び替えを行ってみましょう

下の画像のA列を降順にします

降順にするショートカットキーを確認するため、まずは「Altキー」を押します

すると、キー操作でエクセルを操作できるようになります

そのまま並び替えを行うと「Alt⇒A⇒SD」というショートカットキーであることが分かります

このショートカットキーを「キー送信アクション」に設定します

設定したキー送信前に並び替え範囲上にカーソルを置くことも重要です

これでショートカットキーで並び替えが行われるようになります

SUM関数

SUM関数をPADで操作する場合「=SUM()」とセルに入力する方法があります

実はPADの「キー送信」アクションでも行えます

ショートカットキーでは「Alt⇒Shift⇒=」ですが、PADでは＝ではなく「OemMinus」と記述します

変数によるシート名設定

PADでは、操作するシートを選択する際には「アクティブなエクセルワークシートの設定」アクションを使用します

シート名の指定が頻繁に起こるようであれば、フローの最初で変数で指定できるようにしましょう

そうすれば、後でエクセル上でシート名を変更した際にPADの修正が楽になります

繰り返し処理

シート分割/ワークシート関数で余りを計算

下のGIF画像では1シートの内容を指定行数分で区切り、新シートに追加しています

最初はAllシートに18行あります

こちらのAllシートの18行をそれぞれ3行に分けて新規シートを追加することで、結果的に6シート追加されます

仕組みとしては繰り返し処理する行数（行位置）をカウントアップする際に、NO（１，２，３）を別シートに書き込んでいます

そしてMOD関数により3で割った余りを計算しています

つまり、繰り返し処理の際に次のような公式が成り立ちます

余り１（例：１や４）⇒別シートへのコピー開始行

余り３（例：３や７）⇒別シートへのコピー終了行

ですので、次の画像のような条件分岐処理を行えば指定行単位でのシート分割が行えます

上記画像の「intMod」は算出した余りの値です

intModが１の時には、コピー開始行の変数：rowStartに行位置を割り当てます

intModが０の時には、コピー終了行の変数：rowEndに行位置を割り当て、サブフローでシートを追加しコピーを実行します

この処理がそのまま他の業務で使えるとは思いませんが、役に立つと思える点が２つあります

１つはPower Automate DesktopではVBAのようにコード内での計算が行えません

ですので、今回のようにワークシート関数を埋め込んだ別シートで計算するのがとても有効なケースがあるはずです

2点目は、何かと余りを計算するMOD関数は使用機会が多いです

余りを活用すれば、思わぬ形で業務の定型化が行えます

下からの繰り返し処理

繰り返し処理は通常、上の行から行います

実は下から処理を行った方がうまく行くケースも多いです

一番分かりやすいのは、行削除を伴う繰り返し処理を行うケースです

上の画像で列１の値が「a」の場合に行を削除するとします

問題は5行目と6行目です

仮に繰り返し処理を行う際の行位置（行数）を変数：myRowで表現するとします

変数：myRowが５になった際には、行削除が行われます

＊2行目が削除されるので、実際には4の時です

ここで、行６にあったaは行５に自動的に繰り上がります

一方、myRowは６になり、行６の処理に移ります

ですので、結果的に６行目（下のGIF画像では最初５行目＊）のaが未処理で残ります

*上のGIF画像は2行目が削除された状態から始まっています

上のGIF画像では下のaが削除されてもbとcの下のaが残っているのがよくわかると思います

これが、繰り返し処理を下から行うと問題はなくなります

画像に alt 属性が指定されていません。ファイル名: image-54.png

行位置を表すmyRowが6になった時に行削除行われ、次にmyRowが5になった時にも削除が行われるからです

つまり、行削除を行った時の以降の行の動きと繰り返し処理の方向が一致するからです

実際にフローについて解説します

下から繰り返し処理を行うために「変数を小さくする」アクションをうまく活用します

行位置の初期値は列の最終行にし、繰り返し変数を小さくしていき、変数が２未満になったら終了するようにします

繰り返し処理がうまく行かないケースは、この下からの繰り返し処理もぜひ検討してみてください

にほんブログ村

クラウド活用

OneDriveのフォルダ内の全てのファイル・シートを自動変換する

2023年2月1日 analytic-vba コメントをどうぞ

今回はクラウド上のエクセルファイル内で使える、オフィススクリプト（旧VBA）とPower Automateの組み合わせで自動化処理を行います

やりたい事

OneDriveのフォルダに入れた「すべて」のエクセルファイルの「すべて」のシートを、事前に決められた内容に従って変換処理を行うことです

上の画像では、B2セルの①フォント文字を強調、②背景の色を黄色に、の２種類の変換が行われています

この①②の処理をすべてのファイル、すべてのシートで行います

ポイント

読者の皆様の中には、エクセルの記録マクロを使用した方が多くいらっしゃると思います

今回はエクセルマクロのクラウド版、オフィススクリプトで記録した内容を修正して活用します

修正した内容は、Power Automate/クラウド版RPAで動かします

Power AutomateでOneDriveのあるフォルダから全てのファイルの内容を取得し、エクセルファイルのみを選別して前述のオフィススクリプトを動かします

オフィススクリプト

記録操作

オフィススクリプトは「自動化」のタブから行います

自動化のタブをクリックすると「操作を記録」が行えるようになります

「操作を記録」を押した後に①フォント文字を強調、②背景の色を黄色、を行い「停止」を押します

「停止」を押すと、記録されたコードが「編集」から見れるようになります

コードで注目して頂きたいのは２つの点です

一つ目は「波括弧」です

{と}の間にB2セルを選択した処理が２つ書かれています

特に注目して頂きたいのが２点目です

こちらの「let」です

こちらはプログラミングの「変数」と同じ役割をします

「workbook.getActiveWorksheet()」すなわちファイル内の「アクティブ＝選択・処理しているシート/単数」を「selectedSheet」に当てはめています

ですので、次に続くコードはselectedSheetすなわち「選択・処理しているシート/単数」　の　「getRange(“B2”)=B2セルの内容」　の「getFormat=書式」 の　「getFont=フォント」 を　「setBold(true)＝太くする」になります

次はこのletで設定する変数と、変数を使用していたコードの内容を変更します

コードの変更

この時点では、letで設定する「selectedSheet」は選択されているシートのみが対象になります

こちらを次のように変更します

「workbook.getWorksheets()」とすることで、ファイル内の全てのシート（sがつく複数形）にします

そして、次に取得したシートの全てに前述の①②の処理が行えるように修正を行います

要は繰り返し処理を行うのですが、繰り返し処理の構文*は次の通りとなります

*こちらのページのコレクションの箇所になります

for(let 変数2 of 変数1){

・繰り返し処理する内容1

・繰り返し処理する内容2

}

こちらの内容は英語の「All of ~」と同じになります

All of ～とすることで～の内容の全てが対象になります

この仕組みと同じです

ですので、letで取得したSheetsの全ての各シート：Sheetを変数２として設定します

ここで、では「Sheets」で設定した内容と「Sheet」の内容とは何が違うのか？と疑問に思われる方もいらっしゃると思います

対象は同じですが、Sheetsの方は複数形で全体そのものを指しています

一方、Sheetの方はあくまで全体の構成要素の一つ一つを指します

では、コードの修正の最後として「selectedSheet」を「Sheet」に変更します

こちらは「Ctrl＋H」によって一気に変更することも可能です

コードの修正が完成したらコードの保存を行い、名称（allSheets）を付けます

Power Automate

Power Automateでは、フォルダ内にある全てのファイルを取得しつつ、各ファイルに対してオフィススクリプトを実行します

Power Automateではシナリオを実行するトリガー（起動条件*）をまず最初に選ぶのですが、今回は「インスタントクラウドフロー」すなわち手動でトリガーを選択します

*メールが届いた時、〇時に起動などを本来は選択できます

次にフロー名を付けて「手動でフローをトリガーします」を選択します

次の画面にて新しいステップを追加します

そして「OneDrive」を検索します

次にOneDrive関連のアクションから「フォルダー内のファイルリスト」を選択します

「フォルダー内のファイルのリスト」のアクションでは、該当のフォルダを設定します

この「フォルダー内のファイルのリスト」アクションにより、該当フォルダ内のファイルが一覧になります

ファイルの一覧は後述する「ID」という形で次のアクションに受け渡します

これで２つアクションが設定できました

次は「スクリプト（オフィススクリプト）の実行」アクションを設定します

スクリプトの実行アクションでは、前述のIDを指定します

上のGIF画像では、ファイル欄にIDを設定した途端に「Apply to each」というものが適用されています

これはeach/それぞれのファイルにスクリプトを実行するという意味になります

スクリプトは前述の「allSheets」を選択します

付け加えてスクリプトを実行するファイルは、拡張子が「.xlsx」に限定できるようにします

この限定作業には「コントロール」から「条件」を使用します

次に展開する画面で実際の条件を指定します

条件を設定したら、「スクリプトの実行」アクションは「はい」の下に移動します

これで「フォルダーから全てのファイルを取得」⇒「拡張子が.xlsxのファイルにてスクリプトを実行」のシナリオが完成しました

＜まとめ＞

今回はオフィススクリプトとPower Automateを組み合わせ、フォルダ内のエクセルファイルの全てのシートを変更する方法を解説しました

これは従来のVBAやRPAでは行えない処理です

クラウドの醍醐味がある処理だと思います

ぜひ試してみてください

にほんブログ村

フォルダ取得

ソースステップでフィルター

＜まとめ＞

ファイル保存

エクセルシートのCSV出力

文字列操作

分割

固定文字が含まれているかを判断する

文字列への変換

数値への変換

数値かどうかの判定

メール関連

表をメールの本文に入れる

ファイルを添付して送付

ユーザーフォーム

処理中表示

ファイルの扱い

ファイルパスを指定して開く

ファイルを別名保存（コピー）する

重複削除

シート操作

全ページ操作

印刷設定

印刷範囲設定

全列を全て印刷

改ページプレビューに設定

前提条件

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

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

ファイルの作成

JSON形式への変更

選択アクション

注意

JSONの解析

Power Appsへの受け渡し

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

＜まとめ＞

事前準備

ライブラリーの追加

サイトURL及びリストID

共通構文

Sharepointリストの列名を取得

データの取得

列別

一括

データの追加

データの更新

＜まとめ＞

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

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

＜まとめ＞

組織の壁

技術的対策の実際

＜まとめ＞

10万行にチャレンジ

セルの値が消えた！？

＜まとめ＞

Excellent仕事術の得意分野

VBA/マクロ開発

エクセルシートのスパゲティ化

データのバケツリレー

エクセルの共有

データ分析/統計処理

会員分析

その他の分析

ダッシュボード化

プレゼン・資料作成

Excellent仕事術/ガッツ鶴岡の強み

業務分析の経験が豊富

数値管理の経験が豊富

最新のエクセル技術

事例①

＜商品管理システム/エクセルとAccessの連携＞

事例②

実績

経歴

ショートカットキーの操作

一般操作

SUM関数

変数によるシート名設定