エクセル（Excel）の技。知ってましたか・・・。

この広告は30日以上更新がないブログに表示されております。
新規記事の投稿を行うことで、非表示にすることが可能です。

マイクロソフトアクセスのまとめ記事はこちら

人気ブログランキングへ





四捨五入・切り捨て・切り上げ


・ROUND・RUUNDDOWN・ROUNDUP

四捨五入・切り捨て・切り上げを行います。引数は小数点以下の桁数です。



A3には=ROUND(A2,0)と入っています。結果は123です。

A4には=ROUND(A2,1)と入っています。結果は123.5です。

A5には=ROUND(A2,2)と入っています。結果は123.46です。

A6には=ROUND(A2,3)と入っています。結果は123.456です。

同じようにB3には=ROUNDDOWN(A2,0)が入っています。

小数点以下切り捨てです。

B4〜B6は引数が一つずつ増えています。

C3には=ROUNDUP(A2,0)が入っています。

小数点以下切り上げです。

B4〜B6は引数が一つずつ増えています。

いまA2というセルを指定していますが、ここに直接数字を入れても大丈夫です。

=ROUND(123.456,0)のように。

マイクロソフトアクセスのまとめ記事はこちら

人気ブログランキングへ




SUMPRODUCT

複数条件に合ったデータを計算する関数です。



【構文】

SUMPRODUCT((条件範囲1=条件1)*(条件範囲2=条件2),合計対象範囲1)

上の場合A37に2015/1/4とあります。

これが条件1でC37に封筒とあります、これが条件2です。

条件1と条件2にあった数量の合計を出します。

=SUMPRODUCT((A2:A35=A37)*(C2:C35=C37),E2:E35)

　(A2:A35=A37)が条件1の式

（C2:C35=C37)が条件2の式

E2:E35が合計する対象範囲です・・・2015/1/4の日付の

封筒の数量の合計という意味になりますね。



上記のように対象になる部分は黄色の部分で合計は460になります

マイクロソフトアクセスのまとめ記事はこちら

人気ブログランキングへ



PRODUCT

PRODUCT・・・掛け算の積を出します。




【構文】

PRODUCT（数値1,数値2,・・）

今H2に「=D2*E2*G2」という式が入力されています。

単純にD2とE2とG2の掛け算です。

しかし掛け算の項目が多くなってくると大変です。

そのようなときにこのPRODUCT関数を使用すると便利です。



=PRODUCT(D2,E2,G2)で同じ結果が得られます。

もし範囲が決まっていれば（D2:G2）のように範囲してすればその間の掛け算をします。

掛け算する回数が多い場合は便利です。

マイクロソフトアクセスのまとめ記事はこちら

人気ブログランキングへ




関数の使用方法


・SUMIF

範囲の中から「検索条件」に一致するセルを探してその列にある「合計範囲」の

数値の合計を求めます。

下の例で、商品名で検索して数量の合計を出してみましょう。

【構文】

SUMIF（範囲,検索条件,合計範囲）

I3に「消しゴム」（I2）の数量の合計を出してみましょう。



=SUMIF(C2:C35,I2,E2:E35)・・・C2:C35が「範囲」になります。

I2の「消しゴム」が「検索条件」。

E2:E35が「合計範囲」になります。

これで消しゴムだけの合計個数が表示されます。

マイクロソフトアクセスのまとめ記事はこちら

人気ブログランキングへ




最終のVBA


その後色々動作確認して最終が下のプログラムです。

Private Sub CommandButton1_Click()

Dim kai As Integer '会員一覧の行変数

Dim nyu As Integer '入場券の行変数

'発券日の有無をみて未発券ならば発券

kai = 2 '最初は2行目から

nyu = 8 '入場券の最初の名前は8行目なので

Sheets("入場券").Select

下はループに入る前に1回入場券の値を消去します。

Sheets("入場券").Range("e8:j8").Clear

Sheets("入場券").Range("n8:s8").Clear

Sheets("入場券").Range("e17:j17").Clear

Sheets("入場券").Range("n17:s17").Clear

Sheets("入場券").Range("e26:j26").Clear

Sheets("入場券").Range("n26:s26").Clear

Sheets("入場券").Range("e35:j35").Clear

Sheets("入場券").Range("n35:s35").Clear

Sheets("入場券").Range("e44:j44").Clear

Sheets("入場券").Range("n44:s44").Clear

Sheets("入場券").Range("e53:j53").Clear

Sheets("入場券").Range("n53:s53").Clear


Do While Sheets("会員一覧").Cells(kai, 1) <> ""

If Sheets("会員一覧").Cells(kai, 5) <> "" Then
'2行目の申込日に記載があるかどうか

If Sheets("会員一覧").Cells(kai, 6) = "" Then
'2行目の発券日に記載があるかどうか

Sheets("入場券").Cells(nyu, 5).Value = Sheets("会員一覧").Cells(kai, 2)

'発券日に記載がない場合は入場券の1枚目の名前欄に最初の名前を入力

If Sheets("会員一覧").Cells(kai, 4) = "幼児" Then

'会員一覧の年齢欄に「幼児」となっている場合は

Sheets("入場券").Cells(nyu, 14).Value = Sheets("会員一覧").
　_Cells(kai, 4) & "・保護者必要"

Sheets("会員一覧").Cells(kai, 6) = Date

'幼児の幼児の場合は年齢欄に「幼児・保護者必要」と記載

ElseIf Sheets("会員一覧").Cells(kai, 4) = "小*" Then

'小学生の場合

Sheets("入場券").Cells(nyu, 14).Value = Sheets("会員一覧").Cells(kai, 4) &
　　　_"・保護者必要"

Sheets("会員一覧").Cells(kai, 6) = Date

'小学生も保護者必要

Else

Sheets("入場券").Cells(nyu, 14).Value = Sheets("会員一覧").Cells(kai, 4)
'上記以外は年齢コピー

Sheets("会員一覧").Cells(kai, 6) = Date

End If

If Sheets("会員一覧").Cells(kai + 1, 1) = "" Then

Sheets("入場券").PrintOut

Exit Sub

End If

nyu = nyu + 9

End If

End If

kai = kai + 1

If nyu = 62 Then

Sheets("入場券").PrintOut

nyu = 8

Sheets("入場券").Select

下の消去は印刷後の消去です。

Sheets("入場券").Range("e8:j8").Clear

Sheets("入場券").Range("n8:s8").Clear

Sheets("入場券").Range("e17:j17").Clear

Sheets("入場券").Range("n17:s17").Clear

Sheets("入場券").Range("e26:j26").Clear

Sheets("入場券").Range("n26:s26").Clear

Sheets("入場券").Range("e35:j35").Clear

Sheets("入場券").Range("n35:s35").Clear

Sheets("入場券").Range("e44:j44").Clear

Sheets("入場券").Range("n44:s44").Clear

Sheets("入場券").Range("e53:j53").Clear

Sheets("入場券").Range("n53:s53").Clear


End If

Loop

End Sub



以上が今回作成した入場券発券システムです。

参考になれば幸いです。

また、何か参考になるようなものが出来ましたらアップします。

マイクロソフトアクセスのまとめ記事はこちら

人気ブログランキングへ





発券日とディバック


発券日と日を入れるのを忘れています。



赤枠で会員一覧の発券日に今日の日付を入力します。今日の日付を入力する場合は=Dateです。



少し動作確認していると何か所か不具合が見つかったので少し

修正したのが上の赤枠内です。

幼児・小学生の場合は発券日を記載することなく次に行くので

この2か所に日付を追加しました。

それから発券日が記載されているところをパスした場合も入場券の

記載位置が次に進むのでnyu=nyu+9の記載位置を変更しました。


動作確認する場合赤矢印の「ステップイン」というのを押すと一行ずつ

プログラムが進んでいき動作確認が出来ます。

またその左に手のひらマークがあります。

これはブレークポイントと言ってこの手のひらを押してから、

コードが書かれている画面の左枠で押すと茶色の丸が出来、

プログラムをスタートさせるとその場所まで進むと止まります。

この「ステップイン」と「ブレークポイント」はプログラムの確認に対して非常に

便利です。



上の黄色になっている部分が「ステップイン」でボタンを押すごとに一行ずつ

進んで行きます。

茶色の部分が「ブレークポイント」を設定した場所です。

下に「ローカル」という枠がありkai　2　nyu　8という記載があります。

ここはプログラムが進んで行くと変数がどう変化するかを確認出来ます。

ここも非常に参考になります。

あと検討する項目としては、6枚セットで印刷していますが、途中、

例えば4枚目あたりで名簿が終わった場合どうするのか。

まず現在は上書きをしているので途中で終わった場合、

それ以降は前回の名簿の方が残っているはずですね。

そうなると同じ方の入場券が2回発行されることになります。

ですから1回印刷した後に名前と年齢部分を消去する必要があります。

また途中で終わった場合プログラムもそこで終わってしまうので、

会員名簿が無くなった段階で印刷をする必要があります。



まずは入場券の消去です。



上のように範囲を選択してClearをします。

ここでClearだと「値」と「書式」を消去

ClearContentsだと「値」のみ消去

ClearFormatsだと「書式」のみ消去

また単純に値だけ消したい場合は「=””」でもClearContentsと同じ結果が

得られます。

ここも範囲を選択してまとめて「Clear」してやれば行数も減ってきます。



途中で終わった場合は上のように印刷のIf文を追加してkai+1つまり現在の

会員一覧の次の行が空白だったら印刷してこのプログラムは終了するという

VBAです。

Exit　Subというのは、このプログラムから出なさい（終了しなさい）という

意味です。

マイクロソフトアクセスのまとめ記事はこちら

人気ブログランキングへ



2枚目以降の入場券と印刷



赤枠内で「nyu = nyu + 9」とあります。

これは入場券の2枚目に行くためには9行先だから+9にしています。

If nyu = 62 Then

Sheets("入場券").PrintOut

nyu = 8

End If

nyu = 62というのは6枚目まで行くとnyuは53になります。

それに+9すると62なので6枚入力した段階（nyuが62）で1回印刷するという意味です。

nyu = 8は印刷後再度1枚目から入力していくために1枚目である8行目に戻しています。

マイクロソフトアクセスのまとめ記事はこちら

人気ブログランキングへ



入場券のVBA



VBAの内容ですが

kai = 2 ・・・会員一覧の最初は2行目なので「2」に設定

nyu = 8 ・・・入場券の最初の名前の位置は8行目なので「8」に設定


If Sheets("会員一覧").Cells(kai, 6) = "" Then ・・・2行目の発券日に記載があるかどうか

Sheets("入場券").Cells(nyu, 5).Value = Sheets("会員一覧").Cells(kai, 2)

・・・発券日に記載がない場合は入場券の1枚目の名前欄に最初の名前を入力

If Sheets("会員一覧").Cells(kai, 4) = "幼児" Then

・・・会員一覧の年齢欄に「幼児」となっている場合は

Sheets("入場券").Cells(nyu, 14).Value = Sheets("会員一覧").Cells(kai, 4) & "・保護者必要"

・・・幼児の幼児の場合は年齢欄に「幼児・保護者必要」と記載

ElseIf Sheets("会員一覧").Cells(kai, 4) = "小*" Then

・・・小学生の場合

Sheets("入場券").Cells(nyu, 14).Value = Sheets("会員一覧").Cells(kai, 4) & "・保護者必要"

・・・小学生も保護者必要

Else

Sheets("入場券").Cells(nyu, 14).Value = Sheets("会員一覧").Cells(kai, 4)

・・・上記以外は年齢コピー

End If

End If

上のVBAの流れは分かると思います。

会員一覧の名簿の1行目（エクセルシート上は2行目）の発券日を確認して、

発券日が記載されている場合はこの「If」は終了して一番下の「End　If」まで飛び終了します。

発券日が記載されていない場合は次の行へ進み、名簿の1番目の名前を入場券の1枚目にコピーします。

次に年齢欄を見ていき、「幼児」の場合は、入場券の年齢欄に「幼児」＋「・保護者必要」と記載する。

小学生の場合は学年と保護者必要となる。

幼児と小学生以外は年齢がそのまま記載される。

という流れです。分かりますか。


これが基本のVBAです。

入場券が1ページの6枚あるので同じようなVBAを6回書けばいいのですが、

それではあまりにも芸がないので、「繰り返し」（ループ）を使ってみましょう。


以前For〜NextとかDo〜Loopとか説明したと思います。

繰り返す回数が決まっている場合はFor〜Nextが良いのですが、

今回の場合は会員数が増える場合も想定するとDo〜Loopの方が

条件的には好ましいと思います。

別にFor〜Nextで繰り返す回数を多めに見積もって使用しても問題ないです。

私は比較的For〜Nextが好きなので良くこちらを利用します。



頭にDo While Sheets("会員一覧").Cells(kai, 1) <> ""を追記して

End　Ifの後に

kai = kai + 1

Loopを追記しました。


Cells(kai, 1) <> ""は条件式で会員一覧の１列目の会員番号が記載されているところまで

ループするということです。

「””」は空白とか未記入とかという意味です。


但し上のVBAを実行すると入場券の1枚目だけが変化するだけで2枚目以降に入力されません。

マイクロソフトアクセスのまとめ記事はこちら

人気ブログランキングへ



入場券発券の流れ


・最初に発券日に日付の記載があるかどうか。
　
　記載があれば発券済ということでパスする

・申込日に日付の記載があるかどうか

・申込日に記載があれば「氏名」「年齢」をコピー、

　発券日に本日の日付を入力していく。

・入場券に６名分の記載が終われば一旦印刷する。

・入場券の氏名欄と年齢欄を消去する

・次の６名分の発券

・申込者が無くなればその時点で印刷して終了する

上記の流れで作って行きたいと思います。

ＶＢＡの記載ですが「標準モジュール」を作って登録してもいいですし、

「発券」ボタンを作ってそのボタンに登録してもいいと思います。


今回は入場券シートに「発券」ボタンを配置しましょう。



「開発」タブのActiveXコントロールから「コマンドボタン」を選択してシート状に配置します。

名前を「発券」にしましょう。

「発券」ボタンをダブルクリックするとVBA画面が立ち上がります。

Private Sub CommandButton1_Click()の下にVBAを書いて行きます。



まずは「変数」の宣言をしたいと思います。

「変数」として使用するのは

・会員一覧シートの行変数。

・入場券シートの行変数



上記のように

Dim kai As Integer '会員一覧の行変数

Dim nyu As Integer '入場券の行変数

と宣言します。「‘」の後はコメント部分なのでプログラムには影響ありません。

このようにコメントを残しておくと後で見直す場合に分かりやすいです。

特に大掛かりなVBAになると、一体ここでどのような処理をなんのために

しているのか分からなくなります。

私もマメにコメント残す癖をつけるように意識しています。

マイクロソフトアクセスのまとめ記事はこちら

人気ブログランキングへ




エクセルで入場券発行

以前職場で入場券の発券アプリを作りました。

最初にエクセルで作成しましたが、そのころはエクセルに関する知識が乏しくて、

発券済分の処理がうまく行かず、使い勝手が良くなかったのでアクセスで

作り替えました。

しばらくアクセスで運用していましたが、アクセスも印刷設定で思うようにいかず

再度エクセルで作り替えるという結果になりました。


全く同じものはアップ出来ないですが、何かの参考になればと思うので、

ＶＢＡを使用した簡単な入場券発券システムを作ってみたいと思います。

以前にも記載したと思いますが、ＶＢＡの書き方というのは色々あります。

ここでは本当に初歩的なレベルで記載していこうと思っています。

ある程度のレベルの方でしたら「もっと簡単に出来るのに」とか

「もっとまとめてしまえばいいのに」とか思われるでしょうが、

とにかく分かりやすく初歩的なレベルでという前提で

記載していきますので宜しくお願い致します。


まずどのような入場券なのかというと下記のような架空の入場券を作ってみました。




上記のような内容で、Ａ４シートに６枚取れるようにします。

実際に職場でアクセスを使用して作ったときはＡ４シートで

１０枚取れるように配置しました。

ただカットする際に券と券の間にスペースがあり、カットするのに手間がかかる。

エクセルだとスペースなしで１２枚取れるのではないかという同僚からの

意見もありエクセルに戻しました。

上記もスペースなしで６枚取れるように配置しております。


今度は参加者の情報です。



上記のように「会員番号」「氏名」「年齢」「申込日」「発券日」という

項目があります。

年齢で「小2」とか「幼児」とあるのは小学生以下に関しては

保護者同伴になります。

ですから年齢欄に「保護者必要」と記載します。

申込日が未入力のところは申し込みが無かった場合です。

本来の物はもう少し項目が増え、券の種類・入場料もあります。

また、通年でも使用できるように申込日は年月日で記載して申込日が

本年以外は発券しないという仕組みになっています。


イメージとしては「会員一覧」の申込日が入力済で発券日が

未記入の方に対して入場券を発券していきます。