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

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

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

人気ブログランキングへ




にほんブログ村


WEEKNUM

指定した日付がその年の何週目に当たるかを返します。



【構文】

WEEKNUM(シリアル値,週の基準)

週の基準とは「週の始まりを何曜日にするか」を指定する・・・

省略可（省略の場合は「1」になる）

1：日曜日始まり

2：月曜日始まり

上図でB1に「=WEEKNUM(A1,1)」と入力し戻り値が「1」です。

第1週ということですね

B2には「=WEEKNUM(B1,1)」と入力し戻り値が「49」です。

第49週目ということです。

使用例）

例えば指定した日がその月の第何週かということがWEEKNUMを

使用すると出せます。

= WEEKNUM（シリアル値）−WEEKNUM（その月の1日）+1

例えば上の例で12/1は49週目になっています。

「WEEKNUM（シリアル値）」は49で「WEEKNUM（その月の1日）+1」が50なので

12/1は12月の第1週目ということが分かります。

もしA2というセルを基準にした場合は

= WEEKNUM(A2,1)- WEEKNUM(DATE(YEAR(A2),MONTH(A2),1,2)+1

という式で出せます。




【閑話休題】

この記事を書いているときに少し面白いことを見つけましたので記載しておきます。

国際基準として「ISO8601：週番号」という規定があります。

・「週」は月曜日から始める

・「1月の第一木曜日が含まれる週」の7日間が、その年の第一週になる。

（週と認識されるには４日以上必要という意味）

・すべての週は「7日間」

上記のような規定があります。

その規定に当てはめると今回のWEEKNUM関数の最初の表の2016/1/1は

第1週となっていますが、実は金曜日です。

ISOの規定で言うと2016/1/4〜2016/1/10が第一週ということになります。

ですからこのWEEKNUM関数は「ISO8601：週番号」の考慮がありませんので、

ご注意ください。

それ以外に「ISO8601：週番号」を考慮した計算をする方法もあるようですが、

ここでは取り上げることはしません。

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

人気ブログランキングへ




にほんブログ村



WEEKDAY

【構文】

　WEEKDAY(シリアル値,種類)・・・種類は省略できます。

WEEKDAY関数は指定した日付のシリアル値から曜日を返す関数です。

戻り値は１〜７です。

引数が「1」又は省略の場合

1：日曜日、2：月曜日、3：火曜日、4：水曜日、5：木曜日、6：金曜日、7：土曜日

引数が「2」の場合

1：月曜日、2：火曜日、3：水曜日、4：木曜日、5：金曜日、6：土曜日、7：日曜日

引数が「3」の場合

0：月曜日、1：火曜日、2：水曜日、3：木曜日、4：金曜日、5：土曜日、6：日曜日



今、B1に「=WEEKDAY(A1)」と入力されています。

引数省略なので「5」が表示されます。

B2は引数「2」にしています。

B3は引数「3」です。

C1は「=B1」にして書式設定で「aaaa」を設定しております。



D1には「=TEXT(WEEKDAY(A1),”aaaa”)という関数で

日付からいきなり曜日を表示しています。

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

人気ブログランキングへ




にほんブログ村


RIGHT・LEFT・MID




この関数も非常に便利でよく使用します。

RIGHT関数は右から引数分の文字を抽出します。

今A3に「=RIGHT(A2,2)」と入力されています。

A2の右から2文字を抽出せよという関数です。

半角・全角は区別されません。

結果として「56」が抽出されます。


また、A4には「=LEFT(A2,2)」と入力されています。

これは左から2文字抽出せよという関数です。

結果として「12」が抽出されます。


A5には「=MID(A2,3,3)」と入力されています。

これはA2の3つ目から3文字抽出せよという関数です。




「あけましておめでとう」という文字列がA2に入っているとして

A3に「=MID(A2,4,5)」と入力した場合、A3には「しておめで」という結果が出力されます。


これらの関数は意外と便利です。覚えておいて損はないです。

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

人気ブログランキングへ




INT・TRUNC

前回と同じような関数で「INT」・「TRUNC」というのがあります。



【INT】

小数点以下を切り捨てて、数値をより小さいほうに丸めようとする関数。

正の数字の場合は問題ないですが、負の数字の場合は注意が必要。

例）−123.456をINT(-123.456)とすると結果は-124になります。

【TRUNC】

TRUNCは単純に小数点以下を取り除いて整数部だけにする。

ROUNDDOWN関数と同じ働きをします。

上図B7には=TRUNC(B2)と入力されています。


ROUNDDOWN関数とINT関数の使い分けですが、ROUNDDOWN関数の

引数を「０」にした時とINT関数は同じ結果になります。

ただし「正」の数字の場合です。

「負」の数字の場合は結果が違ってきます。

例）「−１．５」の場合

=ROUNDDOWN(-1.5,0)の場合は「-1」ですが

=INT(-1.5)の場合は「-2」になります。

正の数字だけ扱う場合は「INT」の方が使い勝手がいいと思います。

あとはそれ以外の関数でROUNDDOWN関数の引数ありが多数使用

されている場合はそちらを使用する方が後で確認する場合に見やすいでしょうね。



簡単・シンプル・使いやすい！月間100万人が使うブログサービス【JUGEM】

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

人気ブログランキングへ





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


・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というのは、このプログラムから出なさい（終了しなさい）という

意味です。