2020年09月の記事一覧 | ULALA'Sブログ

VBAスタンダード学習27[テーブル部位の特定(ListRows、ListRow)]

引き続き、今回はテーブルの行についての指定方法を記載する。--------------------------------------①Range("テーブル名").ListObject.ListRows②Range("テーブル名").ListObject.ListRow--------------------------------------下記画像の「テーブル行取得」ボタンを押下することで、テーブル6行目(=Excelの8行目)のデータを15行目に転記されるように作成してみる。ソースSub getTableRows() ' 6行目を取得し、セルに転記 Range("B15").Value = Range("テーブル1").ListObject.ListRows(6).Range(1).Value Range("C15").Value = Range("テーブル1").ListObject.ListRows(6).Range(2).Value Range("D15").Value = Range("テーブル1").ListObject.ListRows(6).Range(3).Value Range("E15").Value = Range("テーブル1").ListObject.ListRows(6).Range(4).Value ' 補足：6行目の開始から14個目のデータセルは？ Range("F15").Value = Range("テーブル1").ListObject.ListRows(6).Range(14).ValueEnd Sub結果上記画像の15行目を確認してみる。タイトルを除くデータ行の6行目について、それぞれデータが転記できたことを確認。.ListObject.ListRows(6).Range(14).Valueについてだが、これはテーブル内のデータセルについて転記されているので、6行目の1セル目から右へ数えていく(データがなくなったら次の行のひとつめから)と9行目の2列目が、14番目の値となり、それが転記されていることになる。また、.ListObject.ListRows(6).Range(0).Valueとすると、"左範囲外"が取得できる。列取得同様、どちらもあまり一般的な使用方法ではなさそうなので、範囲外を指定したらこうなるんだな、程度に抑えておくだけで良いと思う。こちらもテーブル列の時と同様、範囲とプログラムの指定方法を記載してみる。列の場合、DataBodyRangeというメンバは存在したが行には存在しないため、Rangeで取得することになる。また、列の場合DataBodyRange(0)とすると、タイトルが取得できたのに対し、行はタイトルを取得する方法が存在しない。※深く調査したらあるのかもしれないが本来の使い方とは異なるので割愛。次回は構造化参照というちょっと複雑なテーブルデータの指定方法について記載しようと思う。

2020年09月23日

コメント(0)

VBAスタンダード学習26[テーブル列の特定(ListColumn2、ListColumn)]

前回はタイトル行に絞って記載した。データ行、全体行の3パターンで記載してみるとのことで記載したが、ちょっと変更させて頂こうと思う。今回は前回伝えきれなかった、データ行、全体行という区分けではなく、列の指定方法について記載してみる。※必然的に次回は行の指定方法になるわけだが、、--------------------------------------①Range("テーブル名").ListObject.ListColumns②Range("テーブル名").ListObject.ListColumn--------------------------------------以下画像は前回のものから「テーブル列取得」というボタンを新たに追加してみた。ボタンを押下した時に14、15行目にデータを転記する、というシンプルなものを作成。転記する元データはテーブル1のタイトル部とデータ部の1行目を対象にしている。ソースSub getTableColumns() ' 列名をセルに表示 Range("B14").Value = Range("テーブル1").ListObject.ListColumns(1) Range("C14").Value = Range("テーブル1").ListObject.ListColumns(2).Name Range("D14").Value = Range("テーブル1").ListObject.ListColumns("値段") Range("E14").Value = Range("テーブル1").ListObject.ListColumns("日付").Name ' 列データをセルに表示 Range("B15").Value = Range("テーブル1").ListObject.ListColumns(1).Range(2) Range("C15").Value = Range("テーブル1").ListObject.ListColumns(2).Range(2).Value Range("D15").Value = Range("テーブル1").ListObject.ListColumns("値段").DataBodyRange(1) Range("E15").Value = Range("テーブル1").ListObject.ListColumns("日付").DataBodyRange(1).ValueEnd Sub結果.ListColumns→テーブルに存在する全列(データ部含む)を表す。.ListColumns(1)→テーブルの1列目を表す。(=.ListColumn)※この場合「お店」列.Nameはつけてもつけなくても良いがパターンとして記載してみた。.ListColumns(1).Range(2)→「お店」列の2つ目の値を表すので、上記画像の場合、　"八百屋A"となる。　ちなみに.ListColumns(1).Range(1)とすると　タイトルである"お店"が転記される。.ListColumns("値段").DataBodyRange(1)→「値段」列のデータ部の1つ目を表すので、　上記画像の場合、"150"となる。　同じく、.ListColumns("値段").DataBodyRange(0)とすると　データ部の0番目(そんなのないが、、)である、　"値段"、すなわちタイトル部が転記される。　※あまりこういう使い方することは無いと思うが、　エラーにはならず、値として取得できる為、一応記載。テーブルと列、データの関係を簡単に色分けして、範囲で表してみた。次回は、行についての指定方法を記載する予定。

2020年09月22日

コメント(0)

余ったインク、これに使いませんか？[呉竹からっぽペン]

インクだけがどうしても残ってしまって、使い道が見つからない、という方に朗報。呉竹から発売されたこのからっぽペンはいわゆる中身が空っぽなので、自分の好きなインクを利用してペンにできる、という代物。安価だし、インクをガンガン吸ってくれるし書き心地もそこそこ良いので重宝している。ただし、使い切りなので万年筆と違って、一回インクを吸わせてしまったらそのインク色以外は恐らく使えないだろう。(わざと色を混ぜて楽しむ、というのもありかもしれないが)尚、結構売れているみたいで入手がなかなか困難な様子。自分もネット注文して2週間かかってやっと届いたので、もし在庫があるならば即買いしておいても良いかと思う。呉竹 Kuretake からっぽペンほそ芯5本セット ECF160-451価格：704円（税込、送料別) (2020/9/18時点)楽天で購入

2020年09月18日

コメント(0)

VBAスタンダード学習25[テーブル部位の特定(HeaderRowRange)]

前回に引き続き、テーブルについての内容を記載していこうと思う。--------------------------------------①Range("テーブル名").ListObject.HeaderRowRange--------------------------------------本当は、タイトル行、データ行、全体と3パターン記載したかったのだが、タイトル行だけでいろいろと罠？が仕掛けられているのが分かったため、今回はタイトル行に絞って記載してみる。前回の画像に「テーブル行取得」というボタンをつけて、タイトル行を取得してみる。ソースSub getTableHeader() ' ヘッダ行の選択 Range("テーブル1").ListObject.HeaderRowRange.Activate ' ヘッダ行から MsgBox (Range("テーブル1").ListObject.HeaderRowRange(1).Value) MsgBox (Range("テーブル1").ListObject.HeaderRowRange(2).Value) MsgBox (Range("テーブル1").ListObject.HeaderRowRange(3).Value) MsgBox (Range("テーブル1").ListObject.HeaderRowRange(4).Value) ' 範囲外の取得がどうなるか？ MsgBox (Range("テーブル1").ListObject.HeaderRowRange(0).Value) MsgBox (Range("テーブル1").ListObject.HeaderRowRange(26).Value)End Sub結果結果より、上記までは想像がつく。結果1枚目は、Range("テーブル1")..ListObject.HeaderRowRange.Activateテーブルのヘッダ行をアクティブにする。つまり選択状態にすることが確認できた。Range("テーブル1").ListObject.HeaderRowRange(1)～(4)はヘッダ行をひとつずつダイアログで出力している。ここまでは想定内の結果が得られた。となると、javaの試験のクセで、テーブルの範囲外を指定した場合、一体どうなるか？も見てみたくなる。プログラム上では既に範囲外を表示する2文を追記しているので、以下がその結果。.ListObject.HeaderRowRange(0).Valueなるほど、、テーブルの枠を超えて外側を指定することもできるようだ。ちなみにもう1列テーブルの左側に増やせば、.ListObject.HeaderRowRange(-1).Valueで値を取得することも可能。※今は存在しないので-1を指定した場合、エラーになる。では+方向に超えた場合はどうなるか？たぶん何もないメッセージが表示されるだろうと思ったのだが、、.ListObject.HeaderRowRange(26).Valueなんと、.HeaderRowRangeと銘打っているのに、データ部を表示することが判明。※これを書く前に発覚したので、　わかりやすいようにデータがひとつしかない「梨」　を指定してみた。テキストにはここまで記載されていないので、試験として出題されるかは不明だが、覚えておくと良いかと思う。Excel　VBAスタンダード VBAエキスパート公式テキスト [ 田中亨 ]

2020年09月17日

コメント(0)

VBAスタンダード学習24[テーブル(ListObjects、ListObject)の操作]

今回からテーブルについて記載していこうと思う。テーブルと言っているが、具体的には、ListObjects(ListObject)のことを言う。こちらも並び替え同様、新しく追加された項目で、量が多いため、何回かに分けて記載してみようと思う。今回は表からテーブルの作成方法および、ListObjectからのテーブル名取得＆表示を実施してみる。--------------------------------------①ListObjects--------------------------------------まずは、表形式のデータをテーブル形式のデータに変換してみる。下記画像の通り、表を作成したのち、「Ctrl」＋「T」と入力すると「テーブルの作成」ダイアログが表示される。見出し込みで範囲を合わせてOKボタンを押下する。綺麗に色分けされ、「テーブル」として設定された。※この段階でVBA的にもListObjectとして扱えることになるが、今まで通りRange("B2").Value等での値の取得も可能。(メリットは無いだろうが、、)テーブルにはテーブル名がつけられる。ルールとしては、記載したExcelブック内で唯一の名前となる。確認の為、追加で2つほど表を作り、それらをテーブル化し、それぞれ、テーブル2、テーブル3、という名前を付けた。テーブル右下にテーブルの名前の画像を証拠として貼り付け。ソースSub getTableNames() ' シートに存在するテーブルの数を表示 MsgBox ("テーブルの数は" & Sheets(4).ListObjects.Count & "です") ' テーブル名を表示 Dim i As Long For i = 1 To Sheets(4).ListObjects.Count Step 1 MsgBox (Sheets(4).ListObjects(i)) Next i ' テーブル名は以下の方法でも取得可 'Sheets(4).ListObjects(2).Name 'Sheets(4).ListObjects(3).DisplayNameEnd Sub結果Sheet(4)は今回利用したシートの番号。本シートにはSheets(4).ListObjects.Countにより、3つのListObjectつまりテーブルが設定されており、きちんとテーブル名が表示されたことが確認できた。また、ListObjectsは、0番目ではなく、1番目から始まるところに注意。今回は簡単にテーブルの名称を表示する処理を記載してみた。次回からはデータ部についての操作方法などを記載しようと思う。

2020年09月16日

コメント(0)

VBAスタンダード学習23[データの並べ替え(漢字)]

引き続き、データの並び替えを記載。今回はExcel2003での簡易的な並び替えでの記述方法と、漢字による並び替えの挙動を見ていこうと思う。--------------------------------------①Range.Sort--------------------------------------下記画像は苗字による並び替えを行おうと思う。感のいい方は既にこの段階で疑問が出てきていると思うが、とりあえずボタン押下で、どのように並び変わるかを見てみる。ソースSub sortFamilyName() Range("B19").Sort key1:=Range("B19"), order1:=xlAscending, Header:=xlYesEnd Sub結果皆さんの思った通りに並び変わっただろうか？漢字で並び替えを行った場合、元の情報がどのようになっているかによって並び替えが変わる。つまり、漢字変換前に入力した"読み"がセル内に登録されている為、上記のような並びとなる。ふりがなを表示してみる。苗字を選択した状態で、ホーム→下記画像の「ア亜」アイコン？より、ふりがなの表示を押下。苗字(漢字)の上にフリガナが表示された。画像の通り、ひとつめの新垣はアラガキふたつめの新垣はニイガキと入力して漢字変換したため、それぞれ、同一漢字にも関わらず、アラガキ、ニイガキ、のように異なるフリガナでセルに登録されている為、ふたつめの新垣がいちばん最後に並び変わっていることになる。テストとして出題されそうなのは、①漢字はふりがなで並び替えられる②ふりがなが設定されないケース3種　②－１：他のアプリケーションからコピーしてきた時　②－２：マクロでセルに漢字を代入した時　②－３：CSVファイルを読み込んだ時これくらいかと思う。

2020年09月15日

コメント(0)

VBAスタンダード学習22[データの並べ替え(ソート)]

今回からデータの並び替えについて記載。尚、テキストを持っている方は分かると思うが、Excel2007からは以前(2003)よりも記載内容が多く、複雑になったので少し整理しながら記載したいと思う。--------------------------------------①Worksheets.Sort(設定)②Worksheets.Sort.Apply(実行)--------------------------------------下記画像にて、並替対象欄：お店、商品、値段、日付で指定可能順欄：昇順か降順で指定可能とした。なお、並び替え前の状態を保持しておくため、G列以降に並べ替える前の状態を記載しておく。ソースSub setSortBasic() ' 初期設定 Worksheets(3).Sort.SortFields.Clear ' 並替対象 Dim sortPoint As String Select Case Range("C2").Value Case "お店" sortPoint = "B5" Case "商品" sortPoint = "C5" Case "値段" sortPoint = "D5" Case "日付" sortPoint = "E5" Case Else ' 取得できなかった時のデフォルト(値段) sortPoint = "D5" End Select ' 順取得 Dim sortOrder As Integer Select Case Range("E2").Value Case "昇順" sortOrder = 1 ' xlAscending Case "降順" sortOrder = 2 ' xlDescending Case Else ' 取得できなかった時のデフォルト(昇順) sortOrder = 1 End Select ' ソート条件設定 Worksheets(3).Sort.SortFields.Add Key:=Range(sortPoint), _ SortOn:=xlSortOnValues, Order:=sortOrder, DataOption:=xlSortNormal ' ソート実行 With Worksheets(3).Sort .SetRange Range("B5:E14") .Header = xlNo .Orientation = xlTopToBottom .Apply End WithEnd Sub結果値段の昇順で並び替えられたことが確認できた。ソースが長くなってしまったが、Excel2007以降は、ソート条件設定とソート実行の2つで構成されるようになった。ソート条件の方から分解してみていく。と、その前に初期設定のWorksheets(3).Sort.SortFields.Clearについてだが、これはマクロ実行前にソートされていた際ソート条件が追加されてしまうため、念のためソート条件をクリアしておこう、ということ。では気を取り直して、、Worksheets(3).Sort.SortFields.Add→並び替えの設定開始Key:=Range(sortPoint)→どの列を基準に並び替えを行うかを指定　この場合、値段を基準とするため、　case式の"D5"が設定されることになる。SortOn:=xlSortOnValues, →何をもとに並び替えをするか？ということ。　上記指定の場合、セル内の値を元に並び替えをするということ。　ほかにも、セル内の文字の色(xlSortOnFontColor)や　セルの背景色(xlSortOnCellColor)で　並び替えを行う指定が可能。(今回は割愛)Order:=sortOrder,→今回はソート順をExcelのセル内で指定できるように　したため、必然的に変数を利用することになる。　テキストや一般的なサイトには、　xlAscending：昇順　xlDescending：降順　で記載されることが多いが、この値自体が定数なので、　F2を押下し、ライブラリ検索してみれば実際の値がみられる。昇順の定数）降順の定数）　ここだけに限った話ではないが、　定数指定されている箇所はライブラリ検索でどういう値が　設定されているかを確認すれば、変数として設定する　ことも可能なのでF2のクセを付けておくと良いかと思う。DataOption:=xlSortNormal→並び替え対象のデータに数値と文字列が混在していた場合　の並べ替え方法を指定。　xlSortNormal：文字列と数値を別々に並び替える　xlSortTextAsNumbers：文字列を数値として並び替える　上記の画像例だと、値段欄の書式に文字列が混ざって　いた場合に、それを加味して並び替える(xlSortTextAsNumbers)か　数値と文字列は別々に並び替える(xlSortNormal)かということ。続いて、ソート実行の方を見ていく。With Worksheets(3).Sort　→Sort実行するための条件を設定する為、With句で宣言.SetRange Range("B5:E14")　→並び替えるデータの範囲を指定.Header = xlNo　→上記で指定した範囲にヘッダ(タイトル)行が含まれるかどうか？　　　xlNo：ヘッダ行含まないので上記範囲をそのまま並び替え　　　xlYes：ヘッダ行含むので上記範囲の2行目から並び替え.Orientation = xlTopToBottom　→並び替える方向をどうするか？　　xlTopToBottom：上から下、つまり行を並び替える　　xlLeftToRight：左から右、列を並び替える.Apply　→並び替え実行以上、並び替えの基本的な部分について記載。

2020年09月12日

コメント(0)

オンライン会議が主流になりつつあるわけだが、

来週、とあるイベントでzoomを使用する為、出来ればきちんとしたイヤホンとマイクをそろえておきたい。とりあえずヘッドセットを購入しようと思っているのだが、完全初心者の為、何を買ったらよいかわからんので、予算は10,000円前後くらいでよさそうなのを選んでみようと思う。その後もヘッドホンとして自室で通常利用できればいいかな。。購入後、レビューを兼ねて日記を更新したいと思う。

2020年09月11日

コメント(0)

VBAスタンダード学習21[AutoFilter 切り替え]

前回でAutoFilterは終了、と記載したが、書き忘れたものがあったので追記。--------------------------------------①Range.AutoFilter--------------------------------------画像はオートフィルタを設定⇔解除をボタンで交互に行う方法。ソースSub setFilter() ' オートフィルタが設定されていた場合 If ActiveSheet.AutoFilterMode = True Then ' オートフィルタクリア Range("B127").AutoFilter Else ' オートフィルタ設定 Range("B127").AutoFilter 1 End IfEnd Sub結果Range("B127").AutoFilter 1のように、AutoFilterの後に絞り込みの列番号だけを指定すると、フィルタ機能だけを付けることができる。※第2引数以降は記入しないこの状態で再度ボタンを押下すると、今度はフィルタ機能を解除することができる。オートフィルタが設定されているかどうかの条件式はテキストには出てきていない為、参考程度でどうぞ。

2020年09月10日

コメント(0)

VBAスタンダード学習20[AutoFilter 絞り込み後一括編集]

今回は絞り込んだデータを一括で編集する方法を記載する。--------------------------------------①Range.AutoFilter②.End(xlUp)、.End(xlDown)--------------------------------------112行目の指定商品で記入された商品を絞り込んで、その横の金額で一括設定し絞り込みを解除して表示する、というような動きにしてみた。※メロンが大豊作だったという設定のもと、全ての店で売られるメロンを無料にしてみる。ソースSub setAlllPrice() ' オートフィルタクリア Range("B114").AutoFilter ' 指定商品による絞り込み Range("B114").AutoFilter 2, Range("C112").Value ' 絞り込み後データを全て指定の値段にする Range(Range("D115"), Cells(Rows.Count, 4).End(xlUp)) = Range("E112").Value ' オートフィルタクリア Range("B114").AutoFilterEnd Sub結果全てのメロンの値段が無料になっていることが確認できた。Range(Range("D115"), Cells(Rows.Count, 4).End(xlUp))について、Rangeが入れ子になっており、少し複雑なので分解して書いてみると、、Rangeは、Range(行,列)で指定するので、Range(Range("D115"), Cells(Rows.Count, 4).End(xlUp))Range("D115")は　結局の所、115行目というだけのこと。Cells(Rows.Count, 4).End(xlUp)は、　Rows.Countがエクセルの最終行を表す関数　(すなわち1048576行)なのでCells(1048576, 4)となる。　さらにその場所から、.Endプロパティによりデータの終端を探す。　終端といっても下に行くだけでなく、この場合は、　.End(xlUp)の通り、UPということなので、　Cells(1048576, 4)からデータのある個所まで上へさかのぼれということになる。※.End(xlDown)だったら逆に、データのある個所まで下へ降りろ、ということ。さかのぼった結果、見つかったデータが最終データとなるので、上記画像の場合、D124が最終データとなる。よってRange(Range("D115"), Cells(Rows.Count, 4).End(xlUp))はRange("D115:D124")ということになる。こうして晴れて絞り込みを行ったデータのみを一括で変更することができた。今回で一通り絞り込みについての内容が記載できたので、次回からはデータの並べ替えについて記載してみようと思う。

2020年09月09日

コメント(0)

VBAスタンダード学習19[AutoFilter 絞り込み後集計]

今回は絞り込んだデータを元に合計値を算出する方法を記載する。--------------------------------------①Range.AutoFilter②WorksheetFunction.Subtotal--------------------------------------指定商品に商品名を入力し、「絞り込み＆合計算出」ボタンを押下すると絞り込みされた商品の合計値を算出し合計欄に記載する。ソースSub setAutoFilterAndSubtotal() ' 合計金額格納用変数 Dim sumPrice As Long ' オートフィルタクリア Range("B87").AutoFilter ' 指定商品による絞り込み Range("B87").AutoFilter 2, Range("C85").Value ' 絞り込み後合計値算出 sumPrice = WorksheetFunction.Subtotal(9, Range("D88:D97")) ' 合計値セット Range("E85").Value = sumPriceEnd Sub結果WorksheetFunction.Subtotalの引数は、、第1引数：集計方法第2引数：範囲となっており、第1引数は以下のように指定する数値で集計方法が決まっている。1：平均値2：数値の個数の合計3：データの個数の合計4：最大値5：最小値6：積(掛け算)7：不偏標準偏差8：標本標準偏差9：合計値10：不偏分散11：標本分散※1の代わりに101、2の代わりに102、、と、+100した値を引数に指定しても同じ結果が返却される。1番台は、VBAからではなくExcelを手作業で非表示にした場合は計算対象にする。100番台は、手作業で非表示した分も計算対象外にする。という違いがあるが、試験にはまず出ないと思うので覚えなくて良いかと。Excel関数として設定した場合の話だし、そもそも手作業はVBAプログラムの範囲外だから。全ての集計方法で表示してみる。※数値の個数とデータの個数の違いを見るためりんごに"無料"と記載したデータを用意している。ソース(抜粋)Range("B100").Value = WorksheetFunction.Subtotal(1, Range("D88:D97")) ' 平均Range("C100").Value = WorksheetFunction.Subtotal(2, Range("D88:D97")) ' 数値の個数Range("D100").Value = WorksheetFunction.Subtotal(3, Range("D88:D97")) ' データの個数Range("E100").Value = WorksheetFunction.Subtotal(4, Range("D88:D97")) ' 最大値Range("F100").Value = WorksheetFunction.Subtotal(5, Range("D88:D97")) ' 最小値Range("B104").Value = WorksheetFunction.Subtotal(6, Range("D88:D97")) ' 積Range("C104").Value = WorksheetFunction.Subtotal(7, Range("D88:D97")) ' 不偏標準偏差Range("D104").Value = WorksheetFunction.Subtotal(8, Range("D88:D97")) ' 標本標準偏差Range("E104").Value = WorksheetFunction.Subtotal(9, Range("D88:D97")) ' 合計値Range("F104").Value = WorksheetFunction.Subtotal(10, Range("D88:D97")) ' 不偏分散Range("G104").Value = WorksheetFunction.Subtotal(11, Range("D88:D97")) ' 標本分散結果りんごひとつに"無料"と記載したことで、数値の個数が2、データの個数が3と、差異が出ていることが確認できた。恐らく試験には、3、9あたりしか出ないと思うので、標準偏差などは特に覚えなくても良いかと思う。

2020年09月08日

コメント(0)

VBAスタンダード学習18[AutoFilter 絞り込み後コピペ]

今回は絞り込んだデータをコピペする方法を記載する。--------------------------------------①Range.AutoFilter②CurrentRegion--------------------------------------長い名前のボタンで恐縮だが、、ボタン名の通りバナナorメロンで絞り込みした後、B79セル以下に絞り込みしたデータを張り付ける。良くやってしまいがちなNGパターンを先に見てみる。ソース(NG例)Sub setAutoFilterAndRangeCopy() ' オートフィルタクリア Range("B66").AutoFilter ' バナナ or メロンで絞り込み Range("B66").AutoFilter 2, "バナナ", xlOr, "メロン" ' 範囲コピー 'Range("B66").CurrentRegion.Copy Range("B79")End Sub結果(NG例)抽出対象のデータのタイトル部を元に、CurrentRegionを利用することで見えている範囲(=アクティブセルの範囲)を指定することができる。見てわかる通り、79行目にタイトル部が入ってしまっているためデータ部だけを範囲としてコピーする必要があるのでプログラムを修正する。※データ部を開始行とすると、上記の場合、B67セルがリンゴの為、絞り込みの非表示部に入ってしまうので、場合によっては正常な範囲データが取得できなくなる可能性があるので、タイトル行を範囲取得の開始行としている。ソース(OK例)Sub setAutoFilterAndRangeCopy() ' オートフィルタクリア Range("B66").AutoFilter ' バナナ or メロンで絞り込み Range("B66").AutoFilter 2, "バナナ", xlOr, "メロン" ' 範囲コピー(NG) 'Range("B66").CurrentRegion.Copy Range("B79") ' 範囲コピー(OK) Range("B66").CurrentRegion.Offset(1, 0).Copy Range("B79")End Sub結果(OK)ソースよりRange("B66").CurrentRegion.Offset(1, 0)のようにCurrentRegionにoffsetを利用することでタイトル行のひとつ下の見えている行を範囲の開始行としたため、データ部から範囲コピーできるようになる。次回は絞り込んだデータを元に計算処理を行う方法を記載しようと思う。

2020年09月06日

コメント(0)

VBAスタンダード学習17[AutoFilter xlFilterValues]

引き続きAutoFilterについてだが、今回はひとつの列に3つ以上を条件として指定したい場合に、配列を指定する方法を記載する。--------------------------------------①Range.AutoFilter--------------------------------------下記画像の通り、「指定商品」欄に絞り込みしたい商品を3つ記入して「絞り込み」ボタンを押下する。ソースSub setAutoFilterArray() ' オートフィルタクリア Range("B50").AutoFilter ' 配列を用意しシートから絞り込み条件を設定 Dim fil(2) As String fil(0) = Range("C48").Value fil(1) = Range("D48").Value fil(2) = Range("E48").Value ' 配列を元に絞り込み Range("B50").AutoFilter 2, fil, xlFilterValuesEnd Sub結果バナナ、梨、りんごで絞り込みされ、メロンは表示されていないことが確認できた。引数について、、第1引数：絞り込みさせたい列番号第2引数：配列の変数名第3引数：値(※)※複数の値で絞り込みする場合「xlFilterValues」と記入する配列については3つ以上に限った話ではなく、2つでも1つでも可能。※その場合わざわざ配列指定しないだろうが実務で動的配列を扱う場合等が考えられるため、試験用に覚えておくと良いかと思う。

2020年09月05日

コメント(0)

VBAスタンダード学習16[AutoFilter xlOr、xlAnd]

前回に引き続ぎ、AutoFilterについて記載していこうと思う。今回は複数条件による絞り込みを行ってみる。--------------------------------------①Range.AutoFilter--------------------------------------前回のAutoFilter画像をそのまま流用し、ボタンを少し改造して、商品を複数条件（バナナとメロン）で絞り込みを行うようにした。※フィルタマークついているが、絞り込みはしていない状態ソースSub setAutoFilter() ' オートフィルタクリア Range("B49").AutoFilter ' バナナ or メロンで絞り込み Range("B49").AutoFilter 2, "バナナ", xlOr, "メロン"End Sub結果前回記載から、絞り込みの条件を変更しただけとなる。上記はOR条件(xlOr)で絞り込みしているが、AND条件(xlAnd)も可能。例えば、値段が150円以上、500円以下の行を絞り込みたい場合、Range("B49").AutoFilter 3, ">=150", xlAnd, "<=500"と記載することで絞り込み可能。※セルの書式が文字列だとうまく絞り込みできないので、標準や、数値などにしておくこと。新しいボタンを用意して確認してみる。ソースSub setAutoFilterForPrice() ' オートフィルタクリア Range("B49").AutoFilter ' 150円～500円で絞り込み Range("B49").AutoFilter 3, ">=150", xlAnd, "<=500"End Sub一応記載しておくが、、演算子の間に半角スペースが入っていても問題なく絞り込みできる。※分かりずらくて申し訳ないが<=の前後にスペース挿入している。Range("B49").AutoFilter 3, " >= 150", xlAnd, " <= 500"「> =」や「< =」は当然ダメ。エラーにはならず絞り込みで何も表示されなくなる。結果次回は抽出条件を配列で指定する方法を記載しようと思う。

2020年09月04日

コメント(0)

VBAスタンダード学習15[AutoFilter 予約語付き絞り込み]

今回より数回に分けて絞り込み（AutoFilter）について記載していこうと思う。--------------------------------------①Range.AutoFilter--------------------------------------ソースコードを簡潔にするため、「絞り込み」ボタン押下で「八百屋B」での絞り込みに限定して記載してみる。と、その前にオートフィルタを設定する際、絞り込みがきちんとできる範囲になっているかをCtrl＋「A」で確認しておく。以下だと57行目が丸ごと空欄の為、58行目以降がうまく絞り込みされないことになる。どれかひとつでも列に値が無いと範囲として設定されない。以下はきちんと絞り込みができる状態※行まるごと空欄でなければ良いので列に空欄と値が混じっていても絞り込みは可能。これで絞り込みができる状態になったので、「絞り込み」ボタンを押下ソースSub setAutoFilter() Range("B49").AutoFilter 1, "八百屋B" ' または Range("B49").AutoFilter field:=1, Criteria1:="八百屋B"End Sub結果オートフィルタが設定され、八百屋Bで絞り込みができていることが確認できた。.AutoFilterの引数は、、、第1引数：指定範囲の何列目を絞り込みするかを数値で指定第2引数：絞り込みの条件を記入(1個目)第3引数：xlOrやxlAnd等、2個目の条件を設定する際の条件を指定第4引数：絞り込みの条件を記入(2個目)引数について、上記ソースでは、第1引数にfield:=1第２引数にCriteria1:="八百屋B"のように、予約語を利用した記述も可能だが、後々の複雑な絞り込みの際には、これらの予約語を付けているとエラーになるものがあるので試験対策として覚えておく程度にした方が良いかと思う。基本的には予約語無しで記入。試験用に、、予約語は以下となる。第1引数：field:=数値第2引数：Criteria1:="絞り込みしたい文字"第3引数：xlOr、xlAnd第4引数：Criteria2:="絞り込みしたい文字"引き続き、オートフィルタについて次回以降も記載していこうと思う。

2020年09月03日

コメント(0)

VBAスタンダード学習14[Resizeによる範囲コピー]

前回の記事ではFindで見つかったセルに対して範囲コピーを行い、所定のセルへ張り付けるということを行ったが、今回はResizeによる範囲コピーを行う方法について記載しようと思う。--------------------------------------①Range.Resize--------------------------------------ほぼ前回と同様の画像だが検索する文字を記入し「検索＆コピペ」ボタンを押下するソースSub findCopyAndPaste() Dim result As Range Set result = Range("B36:E40").Find(LookAt:=xlWhole, What:=Range("G37").Value) If result Is Nothing Then MsgBox ("見つかりませんでした") End End If ' 見つかったセルを範囲拡張してコピー＆ペースト 'Range(result, result.End(xlToRight)).Copy Range("B43") '※ 前回ソース result.Resize(3, 4).Copy Range("B43")End Sub結果.Resizeの引数は、、第1引数：相対行数第2引数：相対列数となっており、今回の場合だと見つかったセル位置から行数を相対的に3行(自分自身含む)列数を相対的に4列に範囲を拡張してコピーした内容を貼り付け位置配下にペーストする、といった内容にしている。別にFindしなくても、セルを直接指定して範囲をResizeすることも可能Range("B36").Resize(3, 4).Copy Range("B43")この場合「１－１」～「３－４」がセルにペーストされることになる。ちなみに、Resizeの引数には行、列ともにマイナス値は指定できない。Offsetは引数にマイナス指定可能だったが、Resizeは引数にマイナス指定不可能なので覚えておいた方が良いかと思う。Offsetの記事はこちら

2020年09月02日

コメント(0)

VBAスタンダード学習13[Rangeによる範囲コピー]

前回、Find関数による検索を記載したので、今度はそれを応用して範囲コピーを実施してみる。--------------------------------------①Range.End(xlToRight)--------------------------------------以下図のように、検索する文字を記入し「検索＆コピペ」ボタンを押下するソースSub findCopyAndPaste() Dim result As Range Set result = Range("B36:E40").Find(LookAt:=xlWhole, What:=Range("G37").Value) If result Is Nothing Then MsgBox ("見つかりませんでした") End End If ' 見つかったセルを範囲拡張してコピー＆ペースト Range(result, result.End(xlToRight)).Copy Range("B43")End Sub結果Range(result, result.End(xlToRight)).Copy Range("B43")が若干わかりずらいと思うが、第1引数のresult　→検索結果として返されたRange("B38")第2引数のresult.End(xlToRight)　→B38と同一行でデータが存在する最終列のRangeとなる。よって、検索で見つかったB38の値から、B38と同一行で最終列のE38までをコピーしてB43(を開始セルとして)張り付けろ、ということ。「xlTo～」という指定は、実務でも頻繁に利用するので覚えておいた方が良いかと思う。あと何回か先の記事で記載しようとおもう。ここからは余談だが、、実務ではCopy関数なんて使ってたら、パフォーマンスはおろか、正常に動かないことも度々起こるので、自分は使わないようにしている。細かい話になるが、CopyはExcelの枠を超えて、Windowsのクリップボードを利用してしまうので、Copyを使わずに、配列に変換してからセルへ値として格納する方がパフォーマンス的にも良い。この辺は本当に試験用としてだけ一時的に覚えておくだけにしよう。。

2020年09月01日

コメント(0)