8月23日に佐藤嘉浩のExcel書籍が発売になりました。
「Excelで”時短”システム構築術――案件管理の効率化を簡単に実現しよう！」
Excelで”時短”システム構築術――案件管理の効率化を簡単に実現しよう！：書籍案内｜技術評論社
この書籍は、一冊まるごとを使って、Excelで案件管理の仕組みを作る本です。
この書籍では、最新機能であるスピルを使うことによって、より簡単で、短時間での仕組み作りを体験することができます。
しかし、スピルのできない前バージョンのExcelの場合、この本の便利さを体験できないので、ものすごくそこが気がかりでいました。
そこで、その部分をなんとかできないか考えてみました。
Microsoft365など、書籍通りに操作できる方でも、このような方法でもできるという参考にしていただくと力が付くと思います。
書籍を持っていないと、何を言っているのかわからない内容ですので、ぜひ、書籍をご購入下さい。
※免責
この内容を利用して起きた不利益の一切の責任は取りません。
この内容は佐藤嘉浩個人が発信しているもので、出版社の技術評論社とは関係のない内容ですので、技術評論社への質問や問い合わせはご遠慮ください。
前バージョンでできない機能
Excel2016、Excel2019では関数が圧倒的に少ないです。それ以降でスピル機能ができるようになり、関数でフィルターを行うことができるようになりました。その他にもできない関数があります。書籍の中で使っている機能に対してできない機能は４つです
FILTER関数：関数によってフィルターできるので元のデータが変更になったらすぐにフィルターの結果も再計算される
MAXIFS関数：条件付き最大値を求める関数。最新日付を求めるのに使う
IFS関数：IFを数珠つなぎにできる関数。
スピル範囲演算子：FILTER関数などスピルで求めた範囲をF2:J10のような範囲ではなく、F2#だけで指定できる
FILTER関数の代替
FILTER関数の代替手段は、次の記事で詳しく紹介しています。
Excel2016以前のExcelでFILTER関数を使う方法
FILTER関数の代替手段は、元データがテーブルだとものすごくやりやすくなります。ラッキーなことにこの書籍で扱う元データはすべてテーブル化しています。
この方法は、まずテーブルに対して、検索する値と同じかどうかを調べる数式を入れます。その後に、実際にフィルターの結果を表示したいセルに数式を設定します。
次のテーブルでは、C列がメイン画面シートのセルH1と一致しているかどうか調べています。一致していたら連番を振ります。一致していなければ上と同じ数値にします。C2からC2という範囲ですが、片方が絶対参照となっているため、下にコピーされると、「Ｃ２からその計算式が入っている行まで」の範囲になってくれるところが連番となるポイントです。
こうすることで、フィルターを出力するセルに連番を振っておいて、その連番と一致する列を求めればよいですね。それは最新のExcelであればXLOOKUP関数が使えましたが、前のバージョンでは、INDEX関数とMATCH関数で作るとよいでしょう。
まずB列に連番を振っておきます。
Ｃ列には、検索結果を連番にした「対象案件1」項目の中で連番と一致するものを探し、見つかった列の「商品」の項目を出すという数式になります。D列には同様に「個数」の項目を出すというようにします。
計算結果がエラーになりますがその部分はIFERROR関数で処理するとよいでしょう。また、C列の連番はROW関数で行数を調べることによって求めることができます。
この方法の難点は、フィルターされる個数が何個かわからないため、フィルターの結果を求める箇所には余計に数式を入れておく必要があります。その分、計算に負荷がかかるので動作が遅くなることが考えられます。
MAXIFS関数の代替
MAXIFS関数はExcel2016にはありません。そこで、いろいろな計算をすることができるAGGREGATE関数があります。この関数は引数によって合計や平均といった集計方法を変更できる関数で、書式は次の通りです。
=AGGREGATE(計算方法,計算対象,計算範囲,順番)
計算方法は19種類から選ぶことができます。この中の上から何番目の数値を求める14番のLARGEという種類を使って1番目に大きい値を求めます。
計算対象は、よく勘違いしてしまうのですが、「検索するものと一致しているものを指定する」のではなく、「エラー値を含めるか」「非表示セルを含めるか」などの8種類から選択するもので、今回は何も無視せずすべてを計算対象とするので4を指定します。
計算の範囲は日付の範囲を指定しますが、併せて、検索対象かどうかも含めます。もしもその値の案件番号が指定したい案件番号ならば、その日付に0をかけて、日付の値を0にする、そうではなければ日付に1をかけて日付の値そのままにする、その変換した日付の中で1番目の大きいものを求める、ということにします。
IF関数で案件番号と一致していたら日付に1をかけてそうではなければ0をかけるとした場合は、次のような式になります。
=AGGREGATE(14,4,(tbl処理[日付]*IF(tbl処理[案件番号]=F2,1,0)),1)
実はこの一致しているかどうかのIF関数ですが、「IF(tbl処理[案件番号]=F2,1,0)」は「tbl処理[案件番号]=F2」のように短く書くことができます。ExcelではTrueを1、Falseを0として扱うことができるのです。
つまり、この場合の計算式は、次のようになります。
=AGGREGATE(14,4,(tbl処理[日付]*(tbl処理[案件番号]=F2),1)
また、条件が2個ある場合は、次のように条件を「*」で繋いでいくことによって、どこか一つでも成立しない場合にはその日付を0にするAND（なおかつ）条件にすることができます。
=AGGREGATE(14,4,(tbl処理[日付] *(tbl処理[案件番号]=F2) *(tbl処理[処理名]=”請求”),1)
このAGGREGATE関数はMAXIFS関数のように条件付き集計関数が用意されていない、分散、標準偏差、中央値などの統計で使う計算方法にも対応しているので、ビジネスで月次報告書などを元データから作成する場合、とても便利な関数になっているのです。また、一致しているかの判定は今回の真か偽かのTrueが1、Falseが0という考え方で式を作成します。
IFS関数の代替
IFS関数はExcel2016では使えません。そのため、IF関数のネストが面倒になっています。しかし、IF関数をネストにすれば同じことができます。
書籍ではIFS関数は次のように使用しています。
これはこのままIF関数のネストにすることは簡単でしょう。
スピル範囲演算子の代替
フィルターの結果、FILTER関数ならば「F2#」のように数式が入力されているセル参照に「#」を付けるだけでフィルターされている範囲全体を選択することができました。
しかし、Excel2019まではFILTER関数が使えず、またスピルが使えないため、この方法を取ることができません。
そこで、書籍の中でも紹介したOFFSET関数と、数値のセルを数えるCOUNT関数の組み合わせで、フィルターされた範囲を表してみましょう。
次のようにフィルターを計算式で求めた範囲があります。結果は何行かわからないので余計目に出てくるように13行にわたり入れてあります。
この場合のフィルターの範囲は、次のOFFSET関数とCOUNT関数の組み合わせで表現できます。
=OFFSET($I$10,0,0,COUNT(J:J),2)
この範囲を、名前機能で登録し、その名前で範囲を指定するようにすれば、同じようなことが可能になります。
この書籍の場合、見積書、納品書、請求書の各シートで、このOFFSET関数を名前で登録する必要があります。
代替手段の変更点
書籍の中での代替手段の変更点は次の通りです。
見積テーブルの変更・P139
- P139の最後に追加
テーブル《tbl見積》のH列に「検索対象」項目を追加、セルH2の計算式は「=COUNTIF(見積一覧!$C$2:C2,メイン画面!$H$1)」を入力し、テーブルの機能により計算式は「検索対象」項目全体に反映。
見積書シートのFILTER関数の代替・P140
- P140の1行目の計算式
「=IFERROR(INDEX(tbl見積[商品],MATCH(ROW()-9,tbl見積[検索対象],0)),”×”)」
検索される最大は21件と考え、この計算式をI11からI30までのセル範囲にコピー。
さらに、セルJ10に「=IFERROR(INDEX(tbl見積[個数],MATCH(ROW()-9,tbl見積[検索対象],0)),”×”)」の式を追加。
検索される最大は21件と考え、この計算式をJ11からJ30までのセル範囲にコピー。
名前機能で「読み出し」という名前で「=OFFSET($I$10,0,0,COUNT($J:$J),2)」の計算式を、範囲を「見積書」として登録。
見積日を計算するMAXIFS関数の代替・P156
- P156の最終行の計算式の変更
「=AGGREGATE(14,4,tbl見積[日付]*(tbl見積[案件番号]=[@案件番号]),1)」
注文日を計算するMAXIFS関数の代替・P158
- P158の3行目の計算式の変更
「=AGGREGATE(14,4,tbl処理[日付]*(tbl処理[案件番号]=[@案件番号])*(tbl処理[処理名]=”注文”),1)」
P159～P161の「納品日」から「入金日」までの処理も書籍の通り、コピーして、「注文」の部分をそれぞれの列に対応した文字に書き換える。
状態を計算するIFS関数の代替・P163
- P163の9行目の計算式の変更
「=IF([@入金日]>0,”完了”,IF([@請求日]>0,”入金前”,IF([ @納品日 ]>0,”請求前”,IF([ @注文日 ]>0,”納品前”,IF([ @見積日 ]>0,”注文前”,”見積前”)))))」
案件テーブルの変更・P163
- P163の最後に追加
テーブル《tbl案件》のL列に「対象案件1」項目を追加、セルL2の計算式は「=IF(メイン画面!$I$1=””,ROW()-1,COUNTIF($K$2:K2,メイン画面!$I$1))」を入力し、テーブルの機能により計算式は「対象案件1」項目全体に反映。L列全体の表示形式を標準にする。
これは、メイン画面のボタンを押して状態を切り替える動作で、もしも検索する状態がリセットされて空白だった場合はすべての案件を出すため、ただ行番号を表示するというIF関数を入れている。
M列に「対象案件2」項目を追加、セルM2の計算式は「=COUNTIFS($K$2:K2,”請求前”,$H$2:H2,”>=”&月次請求!$B$6,$H$2:H2,”<=”&月次請求!$D$6)」を入力し、テーブルの機能により計算式は「対象案件2」項目全体に反映。M列全体の表示形式を標準にする。
これは、月次請求書において、対象となる案件を取り出すのに使う。状態と日付でフィルターするが、日付は開始と終了の２つになるので全部で３つの条件が入る。
月次請求書シートのFILTER関数の代替・P166
- P166最終行の計算式
「=IFERROR(INDEX(tbl案件[案件番号],MATCH(ROW()-8,tbl案件[対象案件2],0)),”なし”)」
検索される最大は30件と考え、この計算式をB10からB29までのセル範囲にコピー。
- P167の8行目のCOUNT関数の変更
「=COUNT(B9:B29)」
メイン画面シートのFILTER関数の代替・P169
- P169最終行の計算式
「=IFERROR(INDEX(tbl案件[案件番号],MATCH(ROW()-4,tbl案件[対象案件1],0)),””)」
最大で案件数が100だとして、この計算式をセルA104までコピー。
- P169の直後に追加の計算式
セルB5に次の計算式。
「=IF($A5=””,””,INDEX(tbl案件,MATCH($A5,tbl案件[案件番号],0),MATCH(B$4,tbl案件[#見出し],0)))」
この計算式をセルK104までコピー（フィルではなくコピーして貼り付けすること）
データ更新サブルーチンの読み出しセルの変更・P258
- P258のVBAの変更
前略 Sheets(書類名).Range(テーブル名).ListObject.DataBodyRange.Delete If Sheets(書類名).Range("I10").Value <> "×" Then Sheets(書類名).Range("読み出し").Copy Sheets(書類名).Range("D10").PasteSpecial xlPasteValues End If End Function 後略
VBA見積データ更新の中のFILTER関数の代替・P282
ここでは、VBAで処理する内容としてFILTER関数をセルに書きこむ処理をしていますが、VBAによる自動化なので関数ではなく、「詳細フィルター」の機能を使って処理することも可能で、その方が面倒ではありません。他のFILTER関数の入る箇所は検索値を変更したと同時にフィルターの結果が欲しかったのですが、ここでは「見積データを更新する」タイミングだけフィルターをかけたいので、この場合は、関数ではない方が適切でシンプルにできます。
書籍では、フィルター作業はFILTER関数で揃えたかったので、この部分もFILTER関数を使っていますが、本来は詳細フィルターを使った方がよいでしょう。
「詳細フィルター」を使わない場合には「tbl案件」に新たな検索用の項目が必要になります。「詳細フィルター」を使う場合には、元データ範囲を指定、検索条件範囲、抽出範囲を作成し指定する必要があります。一見、「詳細フィルター」を使う場合の方が手間に思えますが、この方法だとフィルター機能なのでいくつ抽出されるかわからないために計算式を余計な範囲まで指定する必要はありません。
抽出する範囲は、書籍だとI列なのですが、この仕組みをする場合、H列は見積書を作成するフィルター計算式のための計算式が入るため、I列になにかを入れるとそこがテーブルの範囲に含まれてしまう可能性があるので、ひとつ隣のJ列に作成します。
- P282のVBAの前処理
テーブル《tbl見積》のセルJ1から右に「通し番号」「日付」「案件番号」「商品」「個数」と入力、セルP1に「案件番号」、セルP2に計算式「=”<>”&メイン画面!H1」を入力。
- P282のVBAの変更
詳細フィルターはAdvancedFilterです。
変更箇所を赤で表示します。
Sub 見積データ更新()
With Sheets("見積一覧")
.Select
.Range("I2").Formula2 = "=FILTER(tbl見積[[通し番号]:[個数]],tbl見積[案件番号]<>見積書!I2,0)"
.Range("tbl見積[#All]").AdvancedFilter xlFilterCopy, .Range("P1:P2"), .Range("J1:N1"), False
If .Range("J2").Value <> 0 Then
.Range("I2#").Copy
.Range("I2").PasteSpecial xlPasteValues
.Range("A2").ListObject.DataBodyRange.Delete
.Range("I2").CurrentRegion.Copy
.Range("J2").CurrentRegion.Offset(1).Resize(.Range("J2").CurrentRegion.Rows.Count-1).Copy
.Range("A2").PasteSpecial xlPasteValues
中略
End If
.Range("I2").CurrentRegion.ClearContents
.Range("H2").Formula=" =COUNTIF(見積一覧!$C$2:C2,メイン画面!$H$1)"
End With
後略
