タグ別アーカイブ: エクセル
XLOOKUP関数_ドリル
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関数」を使用します
こちらについては後日、別途詳細な記事を書きたいと思います
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を解説する他サイトなどを参照して、罫線の形はご自身の好みに合うように変えてみてください
脱エクセルについて考える~資金繰り表をPower Platformで作成~第9回
【エクセルは本当に便利なツール。但し、効率と効果を上げるには限界があります】
資金繰り表はエクセルに向いている業務の典型です
それは何故か?
上の画像の黄色のセルには数式が入っており、預金残高の予想残高を計算しています
但し、この数式を必要に応じて実際の預金残高「数値」で上書きすることができます
実はこれってエクセル以外のソフトだと難しいんです
「数式」と「数値」を共存できるエクセルは本当に便利なんです
ではエクセルで業務を続けていたらそれでいいのか?
それはそれで問題があります
下の画像の「2023/1/5」の売上高の欄には120と入っています
入力数字は120でも、実は120という数字を別な表で複数の顧客の数字を積み上げて入力されているかもしれません
それに、表に存在する日付/列単位が膨大になってくると、入力箇所を見つけるのも面倒です
つまり量が膨大になってくると、この資金繰り表の1セルを入力する作業がかなり面倒になってきます
その理由は一般的なエクセルの資金繰り表では「データ蓄積」「作業」「計算」「表示」が分かれていないからです
ですので、作業や計算スペースを作る関係上、データの並びが不規則になってしまったりしますし、入力しやすいようなスペースを作ることも困難です
更に言えば、資金繰り表のシートのデータを活用して他のシートで別の目的の表を作成しようとするとかなり複雑なものになってしまいます
実は、Power BIのDAXを使用すれば預金口座の残高も、実残高と予想残高を共存させながら計算できます
今回の記事ではPower BIとPower 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などが複雑であったりするので専門知識が必要です
それに当然、手間暇も必要です
ですので、最終的にはどの程度の量の業務をどんな風に変えたいかが一番のポイントなのかもしれませんね
ガッツの日記 第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
******
①の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」が非常に重要です
この点は強く意識しておきましょう!
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でないと最終行まで繰り返すような処理が行えなかったので、本当便利になったものです
また後日、詳細を解説しますが今回のような複数出力する関数は他の関数と組み合わせると、もっと威力を発揮します
ぜひ続編をお楽しみにしていてください
参考までに実際のデータも添付させて頂きます
TOCOL関数、TOROW関数
ガッツの日記 第5回 ChatGPTと残念なエクセルについて議論してみた
【ChatGPTを活用して、日常的なエクセル業務の問題を解決する糸口にしよう!】
今日、【Schoo(スクー)】のライブ授業「議論のプロと考える ChatGPTのプロンプト」を受講しました
ChatGPTの素早い回答を糸口に議論をすすめるのは、なかなか有効だなと感じました
もちろん全てがChatGPTで解決するわけではないですが、ChatGPTとの壁打ちをすることで自分の頭の中もかなり整理されていくのがわかります
完璧にこだわらずにChatGPTと対話を続けるのが有効活用のポイントのようです!
今回は早速、ChatGPTと「残念なエクセル」について議論しました
議論の様子を皆さんにも共有したいと思います
ちなみに本日の講師の方のプロフィールがとてもユニークでした
やはり優秀な方は色々な仕事を生み出していけるのですね!
残念なエクセルの定義について聞いてみた
以下がChatGPTに投げたプロンプトです
箇条書きで・と指定すると個人的には分かりやすい答えが返ってきやすいです
ChatGPTの答えは以下です
↓↓↓
業務効率を下げるような残念なエクセルの定義を以下に箇条書きで示します。これらの要因は、エクセルの不適切な使用や設計に起因するものです。
- 複雑な数式と関数:
- 複雑で理解しにくい数式や関数が多用され、他の人がシートを理解しにくくなる。
- 使い捨てのデータ入力:
- 一時的なデータをエクセルに保存してしまい、データベースなどの適切なツールを使わない。
- 繰り返しの手作業:
- 自動化できる作業が手作業で行われ、時間の無駄になる。
- 不十分なデータ整理:
- データの整理やクリーニングが不十分で、エラーや不正確な情報が含まれる。
- 複数人の同時編集の混乱:
- 複数の人が同時にエクセルファイルを編集し、競合や混乱が発生する。
- ファイルの適切なバージョン管理の欠如:
- 適切なバージョン管理が行われず、誤ったデータや情報が広まる。
- セキュリティの脆弱性:
- 機密情報が適切に保護されておらず、セキュリティの脆弱性が存在する。
- 標準化の不足:
- 統一されたフォーマットやスタイルガイドが不足し、一貫性が欠ける。
- バグやエラーの無視:
- ファイル内のバグやエラーが無視され、問題の修正が行われない。
- 過度な依存:
- エクセルに過度に依存し、他の有用なソフトウェアやツールの活用が不足している。
これらの要因がエクセルの不適切な使用に関連しており、業務効率を低下させる可能性があります。効果的なエクセルの使用には、適切なトレーニング、データ管理、セキュリティ対策、標準化などが必要です。
↑↑↑
2番目に「使い捨てのデータ入力」とあるのですが、これは良く残念なエクセルの特徴を拾い上げているな、と思いました
こちらをChatGPTに再度、聞いてみます
↓↓↓
- 会議の出席者リスト:
- 会議の出席者リストをエクセルに入力し、会議後に使わなくなる。
- 電話メッセージの記録:
- 受けた電話のメモや伝言をエクセルに一時的に記録し、後で削除する。
- 一時的な計算結果:
- 特定の計算を行い、その結果をエクセルに入力して後で必要なくなる。
*一部省略
↑↑↑
この回答だと意味合いは分かるのですが、何が問題かが明確に分からないので再度問いかけてみます
↓↓↓
はい、正確です。使い捨てのデータ入力は、データの規則性や整合性を損なう可能性があります。これは、一時的なデータがエクセルに入力されたり、削除されたりすることで、データの一貫性や整合性が維持されなくなることを指します。具体的には以下の点に注意が必要です:
- フォーマットの一貫性の喪失: 使い捨てのデータ入力が行われると、データのフォーマットや構造が一貫性を失い、後でデータを分析や処理する際に問題が生じる可能性があります。
- 不正確なデータの記録: 一時的なデータが正確に記録されなかったり、誤った情報が入力されたりすると、データの信頼性が低下します。
*一部省略
↑↑↑
何度か問いかけを繰り返すことで、なかなかシャープな表現が返ってきますね
「表現を変えながら何度も問いかけること」これがChatGPTとの対話のコツですね
解決策について聞いてみた
次に具体策を聞いてみます
↓↓↓
データの規則性を維持し、使い捨てのデータ入力に関連する問題を解決するための具体的な解決策を以下に箇条書きで示します:
- データ入力の標準化:
- データ入力のフォーマットや構造を標準化し、一貫性を保つためのガイドラインを策定します。
- 自動化:
- 一時的なデータ入力が自動化できる場合、スクリプトやツールを使用して手作業を最小限に抑えます。
- データベースの活用:
- 使い捨てのデータ入力よりも長期的なデータ管理のためにデータベースを活用します。データベースはデータの整合性を維持しやすく、クエリや分析にも適しています。
*一部省略
↑↑↑
かなり具体的に聞かないとやはり明確な答えはかえってこないですね
ただ、解決の糸口はそれなりに網羅した答えは返ってきます
ちなみに「ウナギの寝床」横に長いエクセルについても聞いてみました
やはりピボットというキーワードはきちんと入っていますね
結論から言うと、どう解決策を聞くかによるのですが、議論のための資料作りとかには十分役に立つと思います
<まとめ>
今回はChatGPTと「残念なエクセル」について議論してみました
今回はテーマが大きすぎましたが、壁打ちのごとく対話をすればそれなりの答えが返ってくると思います
少なくとも、会議資料みたいなものを作るには十分役に立つと思います
ChatGPTから返ってくる表現は非常にシャープで、網羅性が高いです
会議資料を作成する時には、キーワードを紙に書き出したりするのですが、そのキーワードの網羅作業はChatGPTで十分です!
個人的にはもっとChatGPT、AIを活用していきたいです
【Schoo(スクー)】では「その作業はAIにやらせてみよう」をシリーズで授業がありますので、ぜひスケジュールを確認してみてください!
ガッツの日記 第4回 残念なエクセルに出会ったら
【上司はあなたが毎日、エクセルの最終列を探す旅に出ているのを知っていますか?】
私が出会ったエクセルの中で、残念なものの1つが「最終列を探す旅」をするエクセルです
別名は「The ウナギの寝床」です
横長になっている表はいくらでもありますが、私が出会った中で1番凄いものは列がALLを超えてきていました
何故、ここまで横長になるかというと、クロス表の信仰があるからです
表を作る方からすればクロス表にすれば見やすくなるような気がするし、横に延ばすだけで設計が要らず、作るのも楽です
ただ、その後に検索したりするのは大変です
エクセルは列方向にフィルター処理が行えないからです
ですので、最終列を探す旅になります
もちろん、この手の表で共有の管理表を作成して、後で集計するという場合はもっと大変です
各自に口頭で聞いて、集計した方が早いかもしれません
では、
この手の残念なエクセルに実際に出会った時にはどうしたらいいかでしょうか?
それが、この記事のテーマです
組織の壁
他の部署の人がこの手の表を見たら「変えたらいいじゃん」と思います
ところが、一度回りした仕事のツールを変えるのは容易ではありません
ましてや、皆で入力して管理しているエクセル表なら尚更です
「自分が変えます」と手を挙げた途端、以下のようにババを引くような形になるからです
・表を変えて万が一、うまく行かなかったら全て責任を負うような形になる
・必ず誰かは「前の方が使い慣れててよかった」と言う人がいる
・そもそも現在の表のデータを新しい表にどう移行するか?自分が手動で移行することになりそう
・そもそも本来の業務で忙しい
そうして、放置した結果、ますます皆が使い慣れていき、表を変えにくくなるという悪循環が起こります
しかも使い慣れるといっても、業務のスピードがそんなに上がるわけではないのです
大抵、
こういった「残念なエクセル」が業務効率を下げているケースでは、上司がよくそのことを認識していないケースがあります
本日、【Schoo(スクー)】の講座で「ルール(組織のしがらみ)がイノベーションの壁」を受講しました
DX推進者を物語の中の「勇者」の置き換え、DXの目的を「ラスボスを倒すこと」に置き換えて解説しているのですが、これがよく分かりやすい内容になっていました
詳細はぜひ【Schoo(スクー)】のページで確認していきたいのですが、秀逸だと思ったのはDXの推進を阻む「組織の壁」の表現です
〇現場無視系
王様/上司が現場を見ていない
無駄な気飾り/本質とかけ離れた指示
予算がおりず武器が買えない
〇遅延系
え?道具屋のチェックが必要なんですか?
ネットで武器を買おうと思ったらサイトがブロックされている
(以降省略)
この上の「上司が現場を見ていない」というケースは、表現をこの記事の趣旨に沿って変えると「たかがエクセルの表一つで業務が停滞している」ことが伝わっていないケースです
このケースは良くみかけます
先日もある方から「長年、部下がまさかこんなエクセル処理を行ってるとは思ってもみなかった」ということで相談を受けました
よく話を聞いたら、基本的なVBAの処理で済むものでした
場合によっては、管理部門から「残業が多いと注意された」、では「早く帰れ!」と上司が指示を出すこともあるでしょう
ババを引かないためにも、上司に問題の所在を説明して組織全体を巻き込んでいくのがやはり第一歩になるべきです
そうでないと、日常業務がこれまでと同じように降ってきて「業務改善」どころではありません
「そうはいっても、問題の深刻さを分かってもらえない」というケースもあるでしょう
そんな時にはIT部門の力を借りてでも粘り強く説明することが必要かもしれません
技術的対策の実際
では、実際に対策をどうたてたらいいか?
あくまで私がこれまで見てきたケースですが、残念なエクセルの根本的な問題の多くは「データそのもの」にあります
冒頭で紹介した「ウナギの寝床」はまさに典型的です
見やすさや作りやすさを優先して作られています
他の言葉で言い換えると、データの蓄積画面と作業画面を一緒に考えてしまっています
一緒にすると見やすいし、作りやすいような気がします
本来は、データの蓄積と作業は別画面にすべきです
データの蓄積であれば、ウナギの寝床を列数を固定にし、「縦縦の繰り返し」に並べ替えて蓄積します
ここがスタートです
そして作業画面を別途作成します
例えば、データを閲覧するだけであれば、ピボットテーブルで変換することも「データがきちんと蓄積」されていれば可能です
入力が伴う作業画面を別途作成する作業はAccessを使用したりするなど、技術が必要な時もあります
ただ、今ではローコードの技術もあるのでハードルが下がっています
Power Queryで各自が入力したエクセルファイルを、フォルダを通じて集約する技術なんかはオススメです
更新ボタンを押せば、ファイル内の複数ファイルが1つの場所に集約されます
あくまで、入力は「クロス表」で縦横並びにしたいという場合も、ピボット解除や行列入れ替えの技術があります
<まとめ>
今回の記事では「ウナギの寝床」のような残念なエクセルに出会った場合の対策について書きました
とにかくこの手の業務を著しく停滞させるエクセルに出会った場合には、組織を巻き込んで対処するのが先決です
放置をすると、どんどん悪循環が起きます
技術的には、まずデータを規則正しく蓄積することを考えるのが先決です
Sharepointリストを活用することもかなりオススメです
3000万行保管でき、行単位でバージョン管理や変更者の特定も行えます
いや、どうしてもクロス表でしか管理できないです、ですから「エクセルじゃなきゃダメです」というケースでもPower Appsという方法があります
詳細はこちらの記事で紹介しています⇒詳細
ガッツの日記 第3回 私が愛した残念なエクセルを紹介1
【エクセルを見た途端に’絶望’したことはないですか?何回かに分けてこれまで出会った残念なエクセルを紹介していきたいです】
今回の記事では私が実際に作業したエクセルの中で、衝撃を受けた2つのエクセルを紹介します
最後まで記事を読んで頂いても参考になるかは分かりませんが、エクセルの面白さなどを感じて頂けたら幸いです
ちなみに最近、このチャネルが気に入っていて「絶望」という言葉に凝ってます
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についてはかなり力を入れて紹介していますが、今のエクセルはかなり多機能です
絶望せずに解決策を探せば、案外簡単に解決策が見つかることもあります
新しいエクセル機能を研究してみると役に立つことも多いです
最後に、
エクセルの引継ぎは、常日頃からきっちり行えるようにしておきたいものです!