Excel覚書 パチンコ・スロット収支表作成

コンピューター

パチンコ・スロット収支表作成

  • 1店舗、1か月を1シートとする
  • 店舗名+西暦年月(yymm)でシート名を作成する(ex. 店舗ぱちぱち2105)
  • 貯玉、貯メダルは等価にて換算する

店舗毎収支表作成

定義情報

最終的に定義情報は、左にずらして見えない状態にし、ウインドウ枠の固定をする事で非表示にします。

※ 縦長画像の為、横にしてあります。

シート名 ・・・ ①
=RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-FIND("]",CELL("filename",$A$1)))

長いので分割して説明します。

⑴ ・・・ CELL("filename",$A$1)

CELL関数でファイルのフルパスを取得します。
今回は、C:\[パチンコ・スロット収支表.xlsx]店舗ぱちぱち2105

⑵ ・・・ LEN(CELL("filename",$A$1))

CELL関数で取得したフルパスの文字数を取得します。
今回は、32

⑶ ・・・ FIND("]",CELL("filename",$A$1))

CELL関数で取得したフルパスの中から文字列 “]” の位置を検索します。
今回は、22

⑷ ・・・ RIGHT( ⑴ , ⑵ - ⑶ )

⑴で取得した文字列の内、右から、⑵-⑶で求めた文字列 “]” 以降の文字数分取得する。
今回は、RIGHT(“C:\[パチンコ・スロット収支表.xlsx]店舗ぱちぱち2105″,32-22)となり、
結果、”店舗ぱちぱち2105” が取得できる。

店舗 ・・・ ②
=LEFT($A$5,LEN($A$5)-4)

シート名として「店舗名+西暦年月(yymm)でシート名を作成する」としているので、西暦年月の4文字を除く事で店舗名となる。

対象年月(前月) ・・・ ③
=EOMONTH($A$13,-1)

対象年月(今月)を基準に前月末を取得。日付は使用していないので月末日のままにしている。
月初日が良ければ、以下の関数でも良い。

=EOMONTH($A$13,-2)+1

対象年月(今月)を基準に2か月前の月末日翌日(1か月前の月初日)を取得。

最終行(前月) ・・・ ④
=IFERROR(INDIRECT($A$7 & TEXT($A$9,"yymm")&"!A15"),0)

対象年月(前月)を使って前月シートの最終行(今月)を取得。対象シートが無い等、エラーが発生した場合、0を返す。

対象年月(今月) ・・・ ⑤
=DATEVALUE("20" & LEFT(RIGHT($A$5,4),2) & "/" &  RIGHT($A$5,2) & "/01")

シート名として「店舗名+西暦年月(yymm)でシート名を作成する」としているので、西暦年月の4文字を使い、日付文字列を作成し、シリアル化する。

最終行(今月) ・・・ ⑥
=XMATCH("?*",B:B&"", 2,-1)

ワイルドカード ”?*” で1文字以上の文字列を検索します。
検索範囲 B:B&”” で文字列とみなして検索します。(B:Bのみでは数値が一致しない)
一致モード : 2 でワイルドカード検索を行います。
検索モード : -1 で末尾から先頭へ検索します。

対象年月(翌月) ・・・ ⑦
=EOMONTH($A$13,1)

対象年月(今月)を基準に翌月末を取得。日付は使用していないので月末日のままにしている。
月初日が良ければ、以下の関数でも良い。

=EOMONTH($A$13,0)+1

対象年月(今月)を基準に今月末日翌日(翌月の月初日)を取得。

最終行(翌月) ・・・ ⑧
=IFERROR(INDIRECT($A$7 & TEXT($A$17,"yymm")&"!A15"),0)

対象年月(翌月)を使って翌月シートの最終行(今月)を取得。対象シートが無い等、エラーが発生した場合、0を返す。

パチンコ交換率 ・・・ ⑨

等価交換で、0.25

スロット交換率 ・・・ ⑩

等価交換で、0.05

パチンコ・スロット収支表

店舗名 ・・・ ①
=$A$7

定義情報の店舗名から転記。

対象年月 ・・・ ②
=$A$13

定義情報の対象年月(今月)から転記。

繰越貯玉 ・・・ ③
=IF($A$11>3,INDIRECT($A$7 & TEXT($A$9,"yymm")&"!E"&$A$11),0)

定義情報の最終行(前月)が3より大きい場合(データ有)、前月シート貯玉欄の最終行を取得する。定義情報の最終行(前月)が3以下の場合、0とする。

パチンコ計 ・・・ ④
=IF($A$15>3,INDIRECT("F" & $A$15),0)

定義情報の最終行(今月)が3より大きい場合(データ有)、今月シート小計欄の最終行を取得する。定義情報の最終行(今月)が3以下の場合、0とする。

繰越貯メダル ・・・ ⑤
=IF($A$11>3,INDIRECT($A$7 & TEXT($A$9,"yymm")&"!I"&$A$11),0)

定義情報の最終行(前月)が3より大きい場合(データ有)、前月シート貯メダル欄の最終行を取得する。定義情報の最終行(前月)が3以下の場合、0とする。

スロット計 ・・・ ⑥
=IF($A$15>3,INDIRECT("J" & $A$15),0)

定義情報の最終行(今月)が3より大きい場合(データ有)、今月シート小計欄の最終行を取得する。定義情報の最終行(今月)が3以下の場合、0とする。

全体計 ・・・ ⑦
=$F$1+$J$1

パチンコ計+スロット計。

完成

最後に、設定した関数が、誤操作で上書きされないようにシート全体のロックを掛けます。

日、入金(パチンコ)、出金(パチンコ)、貯玉(パチンコ)、入金(スロット)、出金(スロット)、貯メダル(スロット)欄はロック解除する。

最後にシートの保護を掛けます。デフォルトで良いと思います。
パスワードは機密性が必要でなければ設定しない方が使い勝手が良いと思います。

A列を左にスライドして見えない状態で、セルB4の位置でウィンドウ枠の固定を行う。

コメント

タイトルとURLをコピーしました