Excelで「=8.45-INT(8.45)」を計算すると「0.45」になります。
8.45を切り捨てると8、これを元の数から引くと0.45となります。
これは小数部分だけを取り出す方法です。「=MOD(0.45,1)」とする方法もあります。
しかし表示桁数を増やしてみると「0.449999999999999」になります(Excel2007で確認しました)。
つまりExcelは内部的には「0.45」ではなく「0.449999999999999」で保持していると思われます。有効桁数は15桁だそうです。
通常はこれでも問題ないのですが、切り捨てるときに不具合が生じます。
例えば「=ROUNDDOWN(8.45-INT(8.45)+0.55,0)」は「=ROUNDDOWN(8.45-8+0.55,0)」すなわち「=ROUNDDOWN(1,0)」と同じですから、結果は「1」となるはずですが、実際は「0」となってしまいます。
Excelで計算が合わない場合は、このような小数が原因かもしれません。
対策としては式の途中で適当な桁で丸めてしまう方法が考えられます。
「=ROUNDDOWN(ROUND(8.45-INT(8.45)+0.55,6),0)」
切り捨てる直前で適当な桁数で四捨五入します。
この「適当な桁数」は、最後に切り捨てるときの桁数よりも大きく、15桁より小さければよいと思います。
Excelの条件付き書式を使って日曜日だけを赤く表示する方法です。Excel2007で確認しました。
Excelでは日付をシリアル値(1900年1月1日がシリアル値1)で管理しています。
MOD関数を使い、7で割った余りから曜日を知ることができます。0ならば土曜日、1ならば日曜日となります。
これを利用します。
- セルに日付を入力。ここでは「A1」セルとします。
- ホーム-スタイル-条件付き書式-新しいルールを選択し「新しい書式ルール」ダイアログを表示。
- ルールの種類で「数式を使用～」を選択。
- 「次の数式～」に「=IF(MOD(A1,7)=1,TRUE,FALSE)」と入力。
- 「書式」をクリックし、フォントを赤にするなどセルの書式を適当に設定。
- 「OK」をクリック。
条件付き書式で数式を使用する場合は、最初に「=」を使い、TRUEかFALSEが返るように式を作ります。
つまりIF関数を使えばいいのです。
MOD関数を使って曜日をチェックしましたが、WEEKDAY関数を使ってもよいです。
もし土曜日と日曜日を赤くしたければ次のようにします。
数式に「=IF(MOD(A1,7)<=1,TRUE,FALSE)」と入力すれば、余りが0または1の場合となり、土曜日と日曜日のとき書式が適用されます。
文化祭などでチケットを手作りすることはよくあると思います。
これをExcelで作り、1枚ずつ通し番号を付けたいとします。
A4の紙を使うとして紙1枚からチケット4枚を印刷することにします。
Excel2007で確認しましたが別のバージョンでも動くと思います。
Excelでチケットを作る
- Excelで1枚分のチケットを作ります。
- 通し番号をセットするセルを決めます。ここでは「C2」とします。
- 1枚分の範囲を3回コピーしてA4版に4枚分のチケットを作ります。
- 2枚目の通し番号のセルに「=C2+1」と入力します。
- 3枚目の通し番号のセルに「=C2+2」と入力します。
- 4枚目の通し番号のセルに「=C2+3」と入力します。
マクロを設定する
Visual Basic Editorを開き、次のマクロをセットします。
Sub 連続印刷()
myrow = 2 'セットするセルの行(C2ならば「2」)
mycol = 3 'セットするセルの列(C2ならば「3」)
numberfrom = 1 '最初の番号
numberto = 1000 '最後の番号
numberstep = 4 '1ページあたりの番号
For i = numberfrom To numberto Step numberstep
Cells(myrow, mycol) = i
Sheets.PrintOut
Next i
End Sub
状況に応じて書き換えます。
myrowとmycolは通し番号をセットするセルの行と列です。「C2」ならば、myrowに「2」、mycolに「3」とします。
numberfromは最初の番号です。
numbertoは最後の番号です。チケットを1000枚作るならば「1000」とします。
numberstepは1ページあたりのチケットの枚数です。
マクロを実行する
このマクロを実行します。
通し番号を順々にセットして連続して印刷します。
半角英数字では半角スペースが単語を区切るので、ないと困りますが、日本語間の半角スペースは不要である場合があります。
日本語(半角英数字以外)動詞に挟まれた半角スペースだけを削除するExcelのマクロを考えてみました。
Like演算子を使っています。
3文字ずつチェックし、「日本語」「半角スペース」「日本語」と並んでいる場合、「半角スペース」を取り除き、「日本語」「日本語」とします。
「Like “[!0-9a-zA-Z]“」とすることで、半角数字と半角英字以外を調べることができます。
Sub 選択範囲の全角文字間のスペースを削除する()
Const an = "[!0-9a-zA-Z]"
Dim c As Object
Dim s, a As String
Dim i As Long
For Each c In Selection
v = c.Value
s = ""
For i = 1 To Len(v)
a = Mid(v, i, 3)
If a Like an & " " & an Then
i = i + 1
End If
s = s + Left(a, 1)
Next i
c.Value = s
Next c
End Sub
複数のExcelファイルに同じパスワードがセットされている場合に、それを一括して解除して保存するマクロです。
逆に一括してパスワードをセットすることもできます。
最初にダイアログでパターンを訊かれますので、解除ならば「はい」、セットならば「いいえ」を選択します。
次にダイアログでパスワードをセットします。
最後にファイルが保存されているフォルダを指定します。下位階層には対応していません。
「”\*.xls”」の部分を適宜、変更してください。
Excel2007で確認しました。
Sub 複数ファイルパスワード操作()
Dim myfolder, myfn, myword, pwopen, pwclose
Dim pattern
'操作を選択
pattern = MsgBox("パスワード解除ならば「はい」、セットならば「いいえ」", vbYesNo)
If pattern = vbCancel Then
Exit Sub
End If
'パスワードをセット
myword = InputBox("パスワードを入力。")
If pattern = vbYes Then
pwopen = myword
pwclose = ""
ElseIf pattern = vbNo Then
pwopen = ""
pwclose = myword
End If
'フォルダを選択
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "解除したいファイルのあるフォルダを選択"
.AllowMultiSelect = False
If .Show = -1 Then
myfolder = .SelectedItems(1)
Else
Exit Sub
End If
End With
'ファイルを操作
myfn = Dir(myfolder & "\*.xls", vbNormal)
Do Until myfn = ""
Call ファイル開閉(myfn, pwopen, pwclose)
myfn = Dir
Loop
End Sub
Function ファイル開閉(myfn, pwopen, pwclose)
Workbooks.Open Filename:=myfn, Password:=pwopen
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=myfn, Password:=pwclose, WriteResPassword:=""
Application.DisplayAlerts = True
ActiveWorkbook.Close
End Function
ExcelのVBAで半角を全角に変換するのはとても簡単です。
「StrConv」を使うといろいろな変換ができます。
引数に「vbWide」を指定すると半角を全角に変換します。
使用例は次の通りです。
Function han2zen(t)
han2zen = StrConv(t, vbWide)
End Function
次は半角カタカナを全角に変換する方法です。
カタカナは全角に変換するが、英数字は半角のままにしたい、というようなケースに使います。
Function han2zen_kana(t)
Dim u, v As String
Dim i As Long
u = StrConv(t, vbWide)
For i = 1 To Len(u)
If Mid(u, i, 1) Like "[!ァ-ヶー]" Then
v = v & StrConv(Mid(u, i, 1), vbNarrow)
Else
v = v & Mid(u, i, 1)
End If
Next i
han2zen_kana = v
End Function
「Like」を使っています。
これはVBAでワイルドカードを使う場合に用いられます。
You Look Too Cool » VBAでワイルドカードを使う
「Like “[!ァ-ヶー]“」とすることで全角カタカナ以外の場合のみを取り出すことができます。
上の例では全角カタカナ以外の場合のみ半角に変換しています。
VBAでは「Like」を使ってワイルドカード的な使い方ができます。
If myword Like "[a-z]" Then
使い方は次の通りです。
【構文】
文字列がパターンに合致するとTrueを返します。
【パターン】
| 文字パターン |
引数stringの中の一致する文字 |
| ? |
任意の1文字 |
| * |
任意の数の文字 |
| # |
任意の1文字の数字(0-9) |
| [charlist] |
文字リストcharlistに指定した文字の中の任意の1文字 |
| [!charlist] |
文字リストcharlistに指定した文字以外の任意の1文字 |
次にパターンに文字リストを使う場合の例を示します。
半角カタカナ
半角カタカナはコード順で「ｦｧｨｩ…ﾚﾛﾜﾝ」と並ぶので「[ｦ-ﾝ]」とします。
全角カタカナ
全角カタカナはコード順で「ァアィイ…ンヴヵヶ」と並ぶので「[ァ-ヶ]」とします。
ただし長音が含まれないので、これを含めるため「[ァ-ヶー]」とします。
全角ひらがな
全角ひらがなはコード順で「ぁあぃい…ゐゑをん」と並ぶので「[ぁ-ん]」とします。
ただし長音が含まれないので、これを含めるため「[ぁ-んー]」とします。
半角英小文字
半角英大文字
半角数字
「[0-9]」としてもよいのですが半角数字に関しては上述の通り「#」という記号が用意されています。
Excelで数値を全角で表示するにはセルの書式設定で表示形式に「DBNum」を使います。
「DB」はダブルバイトで全角という意味だそうです。
具体的には次のようにセットすることでいろいろなパターンが使えます。
| 表示形式 |
サンプル |
| G/標準 |
123456789 |
| [DBNum1]G/標準 |
一億二千三百四十五万六千七百八十九 |
| [DBNum2]G/標準 |
壱億弐阡参百四拾伍萬六阡七百八拾九 |
| [DBNum3]G/標準 |
１億２千３百４十５万６千７百８十９ |
| [DBNum1]0 |
一二三四五六七八九 |
| [DBNum2]0 |
壱弐参四伍六七八九 |
| [DBNum3]0 |
１２３４５６７８９ |
| [DBNum1]#,##0 |
一二三,四五六,七八九 |
| [DBNum2]#,##0 |
壱弐参,四伍六,七八九 |
| [DBNum3]#,##0 |
１２３,４５６,７８９ |
Windowsにはファイルの内容を検索する機能があります。
しかしExcelを検索する場合、どのファイルにあるかは表示されますが、どのシートにあるかは表示されません。
シートが多いと、そのファイルを開いてから、また検索する必要があります。
そこで、ある文字列について、特定のフォルダにある、すべてのExcelファイルのすべてのシートを検索し、どのシートにあるかを表示するマクロを考えました。
検索結果を表示するだけでなく、該当のセルにハイパーリンクを張るようになっています。
Excel2007で確認しました。 続きを読む…
ある日からその日を含んでX日を経過した日をExcelで求めるには「始期となる日+X-1」とすればOKです。
では日曜日を含まないで計算するにはどうしたらよいでしょうか。
たとえば2011年12月8日(木)の7日経過した日は単純に計算すると14日(水)になります。
しかし日曜日を含まない場合、8日(木)の7日経過した日は15日(木)になります。
「木、金、土、月、火、水、木」と日曜日をとばすからです。
この日曜日をとばすという処理が面倒です。 続きを読む…
最近のコメント