カテゴリー別アーカイブ: ガッツの日記

エクセル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へ

エクセルのような表をアプリで簡単に作成してPDF化する方法~ガッツの日記第11回

【エクセルでシート別にわざわざ表を作成しなくても、簡単に表は作成できるんです!】

先日、PDFでデータを送付するため、わざわざエクセルのシート別にデータを管理している事例をみました

確かに、エクセルだと罫線が入った表をきれいに作成できるので仕方ない部分もあります

ただPDFファイルを作成するためだけにデータを一元管理しないのはデメリットも大きいです

今回はPower Appsにてデータを一元管理しつつ、PDFファイルを「エクセルで作成したように」作成する方法を解説します

具体的にやりたい事

元データは下の画像のエクセル表になります

こちらをPower Appsでフィルターしながら、Galleryに表示します

合計金額は別途ラベルで計算しておきます

そして上記の内容をPDFで別途出力します

ポイント

Print関数

Power Appsにて、新しい画面をクリックすると「縦長で印刷」を選択できます

実際に「縦長で印刷」の画面を選択すると、「Print関数」が仕込まれた画面が設定されます

上の画像の「印刷」をクリックすると、PDFで画面を保存することができる画面に切り替わります

しかも「印刷」ボタン自体はPDF画面に反映されません

何故ならVisibleプロパティに以下の内容が仕込まれてるからです

ですので、戻るボタンを作成する場合には、印刷ボタンをコピーしてOnSelectプロパティを変えるか、Visibleプロパティを変えましょう!

CONCAT関数&HTML

前述のPrint関数でPower AppsからPDFファイルを作成する方法は解説しました

では、エクセルのような表形式はどのように行うのか?気になるところだと思います

こちらは、通常ではデータテーブル機能を使用する方法が一般的だとは思います

ところが、Power Appsのデータテーブルだとあまり「表」らしくありません

ですので、HTMLで表を作成します

表の可変行の箇所は、CONCAT関数を使うのも大きなポイントです

今回は、Galleryの内容をCONCAT関数で抽出します!

データ抽出画面の作成

前述のPrint関数はPDFを作成する出力画面で使用します

それとはまた別の画面を作成し、出力画面で使用するデータを抽出できるようにします

こちらは「具体的にやりたい事」で前述した内容ですので、割愛させて頂きます

HTML

今回の内容はPrint関数で出力するのもポイントなのですが、HTMLで出力するところが最大のポイントとなります

特に行数が可変になるGalleryの内容をCONCAT関数を通じてHTMLで表現するところが難所です

基本構文

専門的な事は、専門サイトの方をご参照して頂きたいのですが、基本的な内容だけは紹介したいと思います

今回使用したHTMLをメモ帳で添付したので、そちらを修正して使いまわすことも十分可能だと思います

構文は大きく分けて3つに分かれます

1.普遍的なHTMLの内容の宣言、2.テーブルの構造の指定、3.見出し・行列の内容の指定

1.の普遍的な内容(”<!DOCTYPE html>~<body>)についてはここでは割愛させて頂きます

2.のテーブルの構造の指定については以下のように指定します

<table border=1 cellspacing=0 cellpadding=10 width=700 table style=font-size:15pt;>

「tablre border=1~cellspacing=0」で一般的な罫線を指定しています

そして、widthで表の幅、font-sizeでフォントサイズを指定しています

3.の見出し・行列の内容の指定については、見出しから解説します

 <tr>     

<th width=25%>日付</th>

<th width=25%>商品</th>

<th width=25%>客先</th>

<th width=25%>売上金額</th>

</tr>

<tr>で始まり、</tr>で終了するのですが、<th>~</th>内に各見出しを指定します

widthは列幅の指定になります

次に一番肝心の行列の指定です

こちらは前述のようにCONCAT関数で指定します

引数は2つになるのですが、第一引数はGalleryの内容になります

Gallery1.AllItemsを第一引数にすることで第二引数内で、ThisRecordによりGalleryの内容をHTML内に抽出することができます

次に、行列の内容ですが、こちらは見た目は小難しそうですが、<tr>と<td>の出現位置に注目して頂くとかなりシンプルなのがお分かりいただけると思います

まず、<tr>で1行目が始まり、各列が<td>で始まります

ここで、ルールが1つあるのですが、</~>で終了します

繰り返しになりますが、<tr>と<td>の出現位置に注目すれば、かなりシンプルに行列が表現できます

それでは、

ここまでで基本的な内容が指定できたと思います

ここから応用ですが、空白行を追加します

空白行は「”&nbsp;”」で表現します

“<tr><td>” & “&nbsp;” & “</td><td>” & “&nbsp;” & “</td><td>” & “&nbsp;”& “</td><td>” & “&nbsp;”

更に合計行は「”&nbsp;”」を交えながら表現します

 & “</td><td>” & “&nbsp;” & “</td><td>” & “&nbsp;”& “</td><td align=right>” & Label_合計.Text &  “</table>

これで、表を更にエクセルライクにすることができました

注意点

注意点は2つあります

1つは、HTMLはラベル内に記述するのではない点です

記述は下の画像のHTMLテキスト内で行います

次に注意点の2点目です

記述するHTMLはあくまで文字列で指定します

ですので、”ではじまり”で終わります

途中でも「文字列」が成立するように”の位置と&の位置には注意する必要があります

上記の内容に注意すれば、エクセルライクな表をPDFファイル内に設定できるようになります

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

<まとめ>

今回は、Power Appsにてエクセルのような表をPDFファイルで出力する方法について解説しました

今回の紹介した仕組みを有効活用すれば、エクセルの過度なシート別の使用も減らすことができます

尚、

今回はHTMLについては簡単な内容だけ紹介しました

ぜひ、専門的なサイトの内容も参考にして頂きたいと存じます

個人的には「SAMURAI ENGINEER BLOG」様を参考にさせて頂きました

https://www.sejuku.net/blog/49377

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

Power Queryに強くなる~M言語/コードに慣れましょう~ 第10回

【Power Queryはあくまでコードでできています。これが今回のポイントです】

Power Queryはとても便利です。メニューをクリック操作していくだけでかなりのことができます

但し、メニュー操作に慣れてしまうと、エラーが起こった時に対処方法が分からなかったりします

そして、もう一つ問題があります

Power Queryが登場してから数年が経ちました

他の方が作成したPower Queryをメンテナンスする機会も増えていくと思います

この時、コード自体を理解できないとメンテナンスは行いにくいです

ですので、これからはPower Queryで書かれているMコードを理解できることも重要になっていくと思います

私も他の方が作成したPower Queryを修正する仕事をしていますが、Mコードを直接理解できないと仕事になりません

今回の記事を通じて、Mコードに慣れる機会にして頂けると嬉しいです

但し、今回の記事ではMコードを書くことは意図していません

あくまで、Mコードを読めるように訓練していく必要性と、Mコードを読む勘所を掴んで頂きたいと思います

今回のポイント

Power QueryのMコードはStepが連なってできています

ここがM言語の最大のポイントです

しかも前後左右、Step間で整合性が取られています

このStepに慣れるとそのままM言語に慣れることができます

Stepの中身の確認

ここからは実際に同じように手を動かしてみて頂くと分かりやすいと思います

たとえば、以下の画像のような平均金額を計算したPower Queryがあるとします

もちろんStepは「ソース」からはじまっています

Stepの中身は2つの方法で見ることができます

一つは数式バーです

二つめは詳細エディターです

こちらはホームタブから見ることができます

詳細エディターをクリックするとコードを丸ごと見ることができます

コードの中身を見て頂くと前後、Stepがつながっているのが良くわかります

let
①ソース = Excel.CurrentWorkbook(){[Name=”売上データ”]}[Content],
②変更された型 = Table.TransformColumnTypes(①ソース,{{“日付”, type datetime}, {“客先”, type text}, {“商品”, type text}, {“売上金額”, Int64.Type}}),
③計算された平均 = List.Average(②変更された型[売上金額])
in
③計算された平均

Stepの意味合い

試しにこちらの数式の中身を「ソース」に変えてみます

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

そうするとどうなるでしょう?

表示はソースStepの内容になります

つまり、Step名自体に意味があります

Step名そのものが「データテーブル」を意味します

よくあるエラー

ここからは実践的な内容として、よく起こるエラーと対策について紹介します

ソースの変更

ソースステップの数式を見てみましょう!

数式の中身は次のようになっています

= Excel.CurrentWorkbook(){[Name=”売上データ”]}[Content]

試しにデータソースとなっている「売上データ」を「売上データ2」に変えてみましょう!

すると次の画像のようなエラーが発生します

この手のエラーはよく発生します

この場合、元のテーブル名に戻すか次のように数式を変更します

そうすればエラーは解消されます

変更された型

今度はソースステップで列名を変更してみます

そうすると以下の画像のようなエラーがでます

こちらはよく起こるタイプのエラーです

ところで「変更された型」Stepとはなんでしょう?

= Table.TransformColumnTypes(#”名前が変更された列 “,{{“日付”, type datetime}, {“客先”, type text}, {“商品”, type text}, {“売上金額”, Int64.Type}})

この「変更された型」Stepでは、各列の形式を設定しています

ここで1つ前にStepも含めて「変更された型」Stepを見てみましょう!

①名前が変更された列 = Table.RenameColumns(ソース,{{“商品”, “商品2”}}),
変更された型 = Table.TransformColumnTypes(名前が変更された列,{{“日付”, type datetime}, {“客先”, type text}, {“商品”, type text}, {“売上金額”, Int64.Type}}),

①名前が変更された列Stepでは「商品」列を「商品2」列に列名を変更しています

次の①変更された型では「Table.TransformColumnTypes(名前が変更された列」となっているので、①の名前が変更された列のテーブルデータを引き継いでます

つまり、「商品2」の列名を持つテーブルデータを引き継いでます

でも、②変更された型Stepでは商品列を「type text」、文字列形式にしています

{“商品”, type text}

ということは、既に存在しない列名を文字列形式に変更しようとしています

ですので、前述のエラーメッセージが出るわけです

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

ではどうやってエラーを解消したらいいでしょう?

一番シンプルなのは変更された型Stepを削除することです

これで、Step間の整合性が取れるからです

let
ソース = Excel.CurrentWorkbook(){[Name=”売上データ2″]}[Content],
名前が変更された列 = Table.RenameColumns(ソース,{{“商品”, “商品2”}}),
計算された平均 = List.Average(名前が変更された列[売上金額])
in
計算された平均

では、どうやって形式変更を行ったらいいでしょうか?

手動で各列を変更するか、すべての列を選択した上で「データ型の検出」を行います

ちなみに、「変更された型」の自動設定を解除したかったら、ファイルタブから「クエリのオプション」を選択します

次に開く画面で「データの読み込み」から「非構造化ソース・・・を検出しない」を設定しておきます

そうすれば、次から「変更された型」Stepは自動で設定されなくなります

<まとめ>

今回の記事では、M言語/コードに慣れるきっかけになるような事例を幾つか紹介させて頂きました

この記事だけで飛躍的にPower Queryの技術が向上するわけではないですが、コードに注目していけば継続的に技術が向上していくはずです

今回の記事は本当に触りだけですが、本格的にM言語を学びたい方はこちらの記事をぜひご利用ください

尚、Udemyで動画コース「Power Queryのメニューにはない裏技ができる! M関数を学んで使えるようになる講座」をリリースしています

期間限定ですが、無料クーポンを提供させて頂きますので、ぜひご利用ください!

終了したら、レビュー投稿の程よろしくお願いします!

今後の参考にさせて頂きます!!

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

にほんブログ村

にほんブログ村 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へ

ガッツの日記 第8回 Microsoft社のAI Builderを使用してみた

【AI Builderがどれだけ手軽か試してみた!やはりかなり手軽に活用できそうです!!】

ChatGPTが登場してAIも身近にはなってきていますが、AIの魅力を知るのに一番AI Builderが手軽だとおもっています

今回の記事ではPDFの請求書から自動で情報を抽出してみます

そして、

抽出した情報はPower AppsもしくはPower Automateで利用できるようにします

大まかな流れとしては以下の3つの事を順に行います

①請求書情報/学習用データの準備

②請求書情報の学習

③Power AppsもしくはPower Automateで情報を利用できるようにする

①~③の流れの中で「コード」は一切書かかないのが大きな特徴です

②の請求書情報の学習では、読者の多くの方が利用されたことがあると思いますが「タグ付け」も行います

記事の最後にはAIが身近、手軽になったことを実感してもらえたら幸いです

請求書情報/学習用データの準備

PDFの請求書を5つ用意します

全て同じフォーマットで揃えます

別途、学習後のテストデータも準備しておきましょう

請求書情報の学習

AI Builderの該当画面を開く

Power AutomateからAIモデルを開きます

こちらの画面には様々なメニューがあります

こちらの中から「請求書から情報を抽出する」というメニューをクリックします

すると次に開いた画面の左下に「新しいアップロード」というボタンがあります

こちらのボタンから学習用データの1つをAI Builderに取り込めます

取り込んだデータはしばらくこんな感じです

この揺れ方がとてもリアルです

いかにも学習しているという感じです

学習が終わると画面右横に情報が抽出されています

これで請求書から抽出する項目がリストされた形になります

ちなみにこれはまだ1段階目の学習です

また次で学習用データの追加が出てくるので違和感を感じるかもしれませんが、1段階目では単に項目を抽出したにすぎません

学習用データの追加

1つ目のデータの学習が終わったら画面右下から「カスタムモデルを作成する」をクリックします

次に開く画面では、リストアップされた項目に対して項目の追加を行います

ただ、今回は解説をスキップして次に行きます

次に開いた画面ではコレクションに学習用データを全て追加します

ここでいうコレクションとは学習用データ一式という意味合いになります

上記の作業を繰り返して学習用データ一式を揃えると画面は次のような形になります

それでは次に行きます

項目調整

次に開いた画面では、追加したデータに赤い印が表示されていました

これは一旦設定した項目がうまく拾えていないことを意味していますので調整が必要です

うまく拾えている項目は「緑」の線で囲まれ、画面上にエラー表示*がありません

*肌色の部分

うまく抽出できていない項目は、もしも必要がなければ取り消すこともできます

もし、うまく抽出できていない項目がある場合は「タグ付け」処理を行います

*上記処理は1つ前の画像と違う学習データで処理しています

では、表となっている各項目の場合はどうするのか?と思われる方もいらっしゃると思います

この場合は、このマークをクリックします

そして、抜けいている部分をクリックしてからタグ付けを行います

タグ付け等の抜けがなくなり、コレクションの赤いマークが消えたら次に行きます

モデルの公開

次は「モデルの公開」なのですが、その前にモデルをトレーニングします

トレーニングには数分かかる場合もあります

トレーニングが終了すると、モデルの精度が確認できるとともに「公開」処理が行えます

Power AppsもしくはPower Automateで情報を利用できるようにする

モデルを公開すると「モデルの使用」と表示されますので、こちらをクリックします

すると、モデルの使用方法が複数表示されます

Power Apps

次に「・・・アプリを構築する」をクリックします

これだけでPower Appsで「フォームプロセッサ」が設定されたアプリが自動構築されます

このフォームプロセッサには新たな請求書をアップロードできます

但し、これだけだとアップロードしてAIで処理した内容が見れません

ですので、ギャラリーを設置します

ギャラリーのItemsにはフォームプロセッサ名と「Results」を設定します

ラベルには「ThisItem.XXX」に加えて「.Value」を加えると正しく表示されます

これでAIで抽出した項目はギャラリーで表示できるようになりました

ただ、以下のようなテーブルの内容はどうするか?という問題がまだ残ります

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

テーブルの内容を表示するには、ギャラリー内に更にギャラリーを追加します

入れ子のギャラリーのItemsには「ThisItem.items」を設定します

ラベルの中身には「ThisItem.~.Value」を前述と同じように使用します

Power Automate

前述の「モデルの使用法を選択してください」にて、「インテリジェントな自動化を構築する」を選択するとPower Automateで作成したAIモデルを活用できます

Power Appsを使用した時には請求書のPDFをアップロードしましたが、今回は指定のアドレスに指定の件名でPDFを添付ファイルとして送ります

そして、AIモデルのから出力された内容が、これまた指定のメールアドレスにメール本文に含まれて返ってくるようにします

前述の「インテリジェントな自動化を構築する」をクリックしたら、次の画面では受け取り方法を選択します

そして上記画像のように「Microsoft365Outlook作業アカウント」を選択してください

すると、途中で「続行」の指定をする画面が出てきますが、そちらで「続行」を指定するともうPower Automateフローができています

デフォルトではトリガーでは件名を次のように指定するようになっていますので、必ずテストする際にはご確認ください

Power Automateフローの最後の「メール送信」アクションは本文を丸ごと修正して、受け取りたい項目を動的コンテンツから指定してください

これで添付ファイルをメールすれば、添付ファイルの請求書の内容を受け取れます

仮に、複数行が出現するテーブルの項目を受け取る項目に指定した場合はメールが複数送信されるようになります

<まとめ>

今回はAI Builderの「請求書から情報を抽出する」メニューで請求書のPDFから情報を取り出しました

学習のプロセスが意外と簡単だと感じられたのではないかと思います!

AIモデルを学習したら、簡単にPower AppsやPower Automateで結果を出力できるのも大きなポイントです

とうとうAIも本当に身近になったと、今回の記事を通じて感じて頂けたら嬉しいです

実際にAI Builderを使用するにはライセンスが必要です

決してべらぼうに高額というわけではありません

もし、一定期間試してみたいという場合には期間限定の「試用版」もありますで、ぜひご利用してみてはいかがでしょうか?

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

にほんブログ村

にほんブログ村 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へ

ガッツの日記 第6回 Power Apps内の地図で個人のフランス旅行の思い出を語る

Power Appsの登場により、地図を使ったアプリを簡単に作成できるようになりました

今回は個人的なフランス旅行の思い出を、Power Appsで作成した地図アプリを使用しながら一方的に語りたいと思います

フランスは楽しかったですね

コロナも一段落しましたし、なんとかまた一度行きたいものです

Bing MapsのAPIキーを取得

Power AppsでBing Mapsを使用するにはAPIキーが必要です

Bing Maps Dev Center にアクセスしてアカウントを作り、キーを取得しておきます

取得した「APIキー」はBingMapsをデータ追加する時に入力する必要があります

ピンデータの作成・反映

まず最初にエクセルで地図内で配置するピンデータを作成します

*必ずテーブル化が必要です

テーブル名:France

経度と緯度を事前に取得しておくのがポイントです

次にPower Appsのキャンバス内にマップを追加します

そして、作成してあるエクセルテーブルを場所/Itemsに設定します

後は詳細設定を行う必要があります

アプリ内に取得したテーブルの列名をそれぞれ以下のように配置します

・ItemsLabels ⇒件名

・ItemsLatitudes ⇒緯度

・ItemsLongitudes ⇒経度

これで地図上にピンが設定されます

モン・サン=ミッシェル

ここから私の個人的な旅行記です

モン・サン=ミッシェルはパリから何気に距離があります

私はパリから日帰りで行きました

なおかつ、最寄駅からバスの便があまりないのでじっくり見れなかったのが残念です

次回はぜひ現地に宿泊したいですね

https://www.youtube.com/watch?v=JzAaFVPajbo

モン・サン=ミッシェルはとにかく壮大で、本当に圧倒されます

周りの自然も豊かなので、日帰りの観光は本当にもったいなかったです

何かモン・サン=ミッシェルを見ていると、本当に人間て小さい存在なんだと思い知らされます

死ぬまでにぜひもう一度行きたいものです

スミュール=アン=オーソワ

この村は日本人で行ったことがある人は多くないのでないでしょうか?

2日ほど宿泊しましたが、日本人には一度もお会いしませんでした

https://www.youtube.com/watch?v=K4W9bDWBW2Q

交通の便もあまり良くなく、リヨンからバスで行くのですが、一日に1~2便だった記憶があります

私は友人に勧められて訪問したのですが、行って本当に良かったです

まるで中世に迷い込んだような感覚になります

後、ホテルの近くに小川が流れていたのですが、リスが歩く姿が本当にほほえましかったです

現代は便利な時代のはずですが、本当にそれが幸せなのか考えさせられます

2日間滞在した後、ホテルの人がわざわざリヨンまで送ってくれたのですが、途中の風景も、のどかでとても良かったです

本当にこんな感じでした

https://www.youtube.com/watch?v=VWe8aDnaxOc

アヌシー

アヌシーはスイス国境近くに位置するせいか、やはりフランスで訪問した他の街とは少し趣が違った街でした

とにかく湖がきれいだったのをよく覚えています

https://www.youtube.com/watch?v=GQ2If_HeWdc

後、このサムネの場所が夜になるとライトアップされていてとても奇麗でした

https://www.youtube.com/watch?v=ppxKxdkGSGA

夜、気持ちよくこの辺りを歩いていたのですが、少し酒が入っていたのもあり、水の中に落ちてしまいました

冬だったのでとても寒かったのと、ズボンも一着しかもっていなかったのでその後は大変でしたね

<まとめ>

今回はPower AppsでBingMapsを使用した地図を作成した上で、フランスでの思い出をこちらから一方的に楽しく語らせて頂きました

本当にもう一度フランスには行きたいものです

ところで、Power Appsは本当に便利ですね

地図用の機能が簡単に扱えるようになっています

上のGIF画像では、GetLocationByAddress関数により住所から緯度経度を自動算出しています

私は今、東京に住んでいますが現在地の表示も簡単です

こちらをオンにするだけです

これならあまりコードの知識がなくても、本格的な地図アプリが作成できますね!

個人が自分自身で業務に必要なアプリを作成する時代が、本当に近いのだととかんじさせられます!!

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

にほんブログ村

にほんブログ村 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へ

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

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

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

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

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

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つのセルに入力するだけで済みます

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

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

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

セルの値が消えた!?

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

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

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

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

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

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

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

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

実は

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

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

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

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

後から

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

<まとめ>

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

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

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

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

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

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

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

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

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

最後に、

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

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

にほんブログ村

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