Excel VBAエキスパート試験スタンダード学習_3
続きまして
VBA学習のおさらいで
WorksheetFunctionの引数を暗記してみようと思います
暗記に意味はないですが、
普段のコーディングの速度アップにもよろしいかというモチベでやってみます
WorksheetFunction
出題範囲 WorksheetFunction.関数名(引数)
| 関数名 | 機能 | 引数 |
|---|---|---|
| SUM | 合計 | Sum(Range("A1:C5")) |
| COUNTIF | カウント | CountIF(Range("A1:A5"), "佐々木") |
| SUMIF | 特定のデータ合計 | SumIf(Range("A1:A5"), "佐々木", Range("C1:C5")) |
| LARGE | 数値の大きい順 | Large(Range("A1:C5"), 1) '1番目に大きい ex) Max(Range("A1:C5")) Large(Range("A1:C5"), 2) '2番目に大きい |
| SMALL | 数値の小さい順 | Small(Range("A1:C5"), 1) '1番目に小さい ex) Min(Range("A1:C5")) Small(Range("A1:C5"), 2) '2番目に小さい |
| VLOOKUP | 検索 | Vlookup(Range("D1"), Range("A1:A5"), 2, False) D1セルの文字列をA1:A5から完全一致検索しB列の値を返します |
| MATCH+INDEX | 検索 | Match(Range("D1"), Range("B2:B7"), 0) 上から何番目かを返す Index(Range("A2:A7"), 5) 上から何番目の値を取得する With WorksheetFunction .Index(Range("A2:A7"), .Match(Range("D1"), Range("B2:B7"), 0)) End With |
| EOMONTH | 月末の日を返す | EoMonth(Range("A1"), 0) 引数2=何ヶ月後か 過去日は負の数を入力 |
Excel VBAエキスパート試験スタンダード学習_2
おはようございます
子どもの習い事の試合が続き、久しぶりの週末学習です
今日は学習が足りていなかったテーブルについてまとめてから問題にチャレンジします
テーブル値操作には大きく分けて3通りある
うろ覚えでそんなことは覚えているのですが、
具体的にコーディングしたこともないのでやってみよう！
テーブル操作
テーブの特定
ListObjectオブジェクト
| 方法 | 基本文法 |
|---|---|
| テーブルのセルから特定 | テーブル内のセル.ListObject |
| テーブルが存在するセルから特定 | 対象のシート.ListObjects(インデックス/テーブル名) |
| Rangeとテーブルの名前で特定 | Range(テーブル名) |
☆Range(テーブル名)の書き方では、できない操作もある
Range("A1").ListObject
特定
'タイトル行を含むテーブル全体
Range("A1").ListObject.Range
'タイトル行を含まない
Range("A1").ListObject.DataBodyRange
'タイトル行
Range("A1").ListObject.HeaderRowRange
'列
Range("A1").ListObject.ListColumns(インデックス/項目名)
Range("A1").ListObject.ListColumns(1)
Range("A1").ListObject.ListColumns("商品名")
Range("A1").ListObject.ListColumns(1).Range 'セル全体
Range("A1").ListObject.ListColumns(1).DataBodyRange 'タイトル行含まないセル全体
Range("A1").ListObject.ListColumns(1).HeaderRowRange 'タイトル行
'行
Range("A1").ListObject.ListRows(インデックス) 'インデックスにタイトル行は含まない
Range("A1").ListObject.ListRows(1).Range 'セル全体
操作
'データを探す=AutoFilter
Range("A1").ListObject.Range.AutoFilter 2, "みかん"
Range("A1").ListObject.DataBodyRange.AutoFilter 2, "みかん"
'ex.絞り込んだ範囲をコピー(アクティブセルがテーブル外だと不具合有り)
With Range("A1").ListObject.Range 'タイトル行含む
.AutoFilter 2, "みかん"
.Copy Sheets("Sheet2").Range("A1")
End With
With Range("A1").ListObject.DataBodyRange 'タイトル行以外
.AutoFilter 2, "みかん"
.Copy Sheets("Sheet2").Range("A1")
End With
'ex.絞り込んだ特定列のコピー
With Range("A1").ListObject
Range.AutoFilter 2, "みかん"
.ListColumns(1).Range.Copy Sheets("Sheet2").Range("A1")
.ListColumns(3).Range.Copy Sheets("Sheet2").Range("B1")
End With
'ex.絞り込んだ特定列に書式設定
With Range("A1").ListObject
.Range.AutoFilter 2, "みかん"
.ListColumns(2).DataBodyRange.Font.Bold = True
.Range.AutoFilter 2
End With
'ex.絞り込んだ行を削除
With Range("A1").ListObject.DataBodyRange
.AutoFilter 2, "みかん"
.EntireRow.Delete
.AutoFilter 2
End With
挿入
'テーブルの右隣に列を挿入
Range("A1").ListObject.ListColumns.Add
'ex.追加した列に数値列の値の2倍の値を入力する
Range("A1").ListObject.ListColumns.Add
n = Range("A1").ListObject.ListColumns.Count
Range("A1").ListObject.ListColumns(n).DataBodyRange = "=[@数値]*2"
Range("テーブル1")
特定
構造化参照を使用
テーブル名[[特殊項目指定子], [列指定子]]
特殊項目指定子
[#All] #すべて
[#Data] #データ
[#Headers] #見出し
[#Totals] #集計
'タイトル行を含むテーブル全体
Range("テーブル1[#All]")
'タイトル行を含まない
Range("テーブル1")
Range("テーブル1[#Data]")
'列
Range("テーブル1[[#All], [商品名]]") 'セル全体
Range("テーブル1[商品名]") 'タイトル行含まないセル全体
Range("テーブル1[[#Data], [商品名]]")
'行選択不可
操作
'データを探す=AutoFilter
Range("テーブル1").AutoFilter 2, "みかん"
'ex.絞り込んだ範囲をコピー(アクティブセルがテーブル外だと不具合有り)
Range("テーブル1[#All]").Copy Sheets("Sheet2").Range("A1") 'タイトル行含む
Range("テーブル1[#Data]").Copy Sheets("Sheet2").Range("A1") 'データ行のみ
'ex.絞り込んだ行を削除
Range("テーブル1[#Data]").EntireRow.Delete
挿入
'列を挿入=右隣にデータを入力すれば、Excelが自動でテーブル範囲を広げることを利用
'ex.追加した列に数値列の値の2倍の値を入力する
Range("テーブル1[[#Data], [数値]]").Offset(0, 1) = "=[@数値]*2"
Excel VBAエキスパート試験スタンダード学習
元気ですか？
またまたお久しぶりの投稿です
Python勉強の余裕が無くなってしまったのと
今の仕事で得たスキルをちゃんと明確なものしないといけないと思い
なにかに活かす目的ではなく
自分のスキルの証明材料として資格を取得することにいたしました
それがExcel VBA エキスパート試験 スタンダード ^^
学習にはアウトプットが必要なので、
今回も必要と思ったものを記録していきます
一貫しているのはただ、利己的ブログということですw
学習内容
開発中にあったSortの謎が解決
Add2メソッドが使用できないわけ
開発中Office 2016だったのにも関わらずAdd2メソッドが使用できなかったのは どうやら拡張機能を設定していなかったのが原因だということ！！
ひとつスッキリしました=3
Findメソッドの引数忘れすぎ
そんなの覚えてね〜よ、ということで備忘録
object.Find What:=hoge, LookAt:=fuga ' このLookAtをすぐ忘れる(何故記述式)
他のメソッドもちらほら
object.AutoFilter 配列, フィールド数字, xlFilterValues ' VBAで3つ以上でフィルターしたことないから慣れないなぁ...
並べ替えは文字色でもできる
背景色だけでなく文字色でもOKなんですってよ
Sortのおさらいをしておきたい
'ex)
With ActiveSheet.Sort
.SortFields.Add2 Key:= Range("B1")
.SetRange Range("A2:C4")
.Apply
End With
引数を記述・選択させる問題があるがいちいち覚えてなかったのでメモ
Add2メソッド引数SortOnの定数
| 定数 | 意味 |
|---|---|
| xlSortOnValues | セル内のデータで並び替える |
| xlSortOnCellColor | セルの背景色で並び替える |
| xlSortOnFontColor | セルの文字色で並び替える |
| xlSortOnIcon | 条件付き書式のアイコンで並び替える |
Add2メソッド引数Orderの定数
| 定数 | 意味 |
|---|---|
| xlAscending | 昇順 |
| xlDescending | 降順 |
Add2メソッド引数DataOptionの定数
| 定数 | 意味 |
|---|---|
| xlSortNormal | 数値と文字列別々に並び替える |
| xlSortTextAsNumbers | 文字列を数値のみなし並び替える |
SortオブジェクトHeaderプロパティの定数(記述でひっかかった)
| 定数 | 意味 |
|---|---|
| xlYes | 先頭行をタイトル行とする |
| xlNo | 先頭行をタイトル行としない |
| xlGuess | Excelが自動判定 |
SortオブジェクトSortMethodプロパティの定数(めったに使わないと思って読み飛ばしたやつ)
| 定数 | 意味 |
|---|---|
| xlPinYin | 日本語をふりがなで並び替える(PinYin:中国語のローマ字による表音表記) |
| xlStroke | 日本語を文字コードで並び替える(Stroke:字画) |
これも使わねーだろなんで試験範囲なんだ
'ふりがな取得時の記述 Range(hoge).Phonetic.Text
ふりがなで並び替えるとき
昇順した場合、ふりがな未設定のものは設定済のものより後ろに並ぶ
ふりがな未設定のセルのふりがなを取得したら
そのままの文字列を返しちゃう
空のふりがなは設定できない
ひっかけ問題があった
Sortのキー数の上限
- Sortメソッド:3個
- Sortオブジェクト( Excel2007以降):64個
テーブル操作は読んだだけで覚えていなかったのでテキスト再学習決定
え！そんなエラー表記の記述問題まであるの？！ひどい
セルの書式設定
プロパティ NumberFormat
シートの保護
プロパティ ProtectStructure
変数やオブジェクトの種類を取得する
TypeName(obj)関数
Python DAY3-2:Excelデータ取得サンプルコード解読
Windowsにインストール
Windows10にインストールしてみた！
公式リファにもあった通り、
そもそもインストールしなくても使用できるそうですが無視。
ほんでインストールするにしても、
pyenv無くてよい、のだから時短しようぜ！！
インストールも、
コマンドラインじゃなくて
インストーラーDLで秒じゃん、秒！！
まあすべては結果論さ。
pyenvとにらめっこできたおかげで
こうして学習意欲の火が再燃したのだから。
たかが、
時間が1/6になっただけよ。。。泣
サンプルファイルDL
早速見せつけられたサンプルコード
Excel形式の伝票からデータを一覧形式で取得しcsvファイルに書き起こすというシナリオ
まぁ解説なくともある程度読むことはできたよね
VBAやってるから似てるところはすっと入ってくるか
やはりPythonのほうが扱いやすい印象ではあるなーっ
読めてもかけなきゃ意味ないやい！！
# ライブラリをインポート # ここでは3種のライブラリを使用 import pathlib #標準ライブラリで、pathをオブジェクト化できる優れもの import openpyxl #外部ライブラリ Excelを扱うライブラリは複数あるがより機能豊富なものらしい import csv #csvにはきだすための標準ライブラリ
はい続きまして、処理についてのメモ
# pathオブジェクト作成 path = pathlib.Path("フォルダパス") # 新しいファイルwbオブジェクトを作成 wb = openpyxl.Workbook() # shオブジェクトの取得方法 sh = wb.active # for文にてpath(フォルダ)オブジェクトのTIPS for path_obj in path.iterdir(): # iterdir()メソッドでpathオブジェクト配下のパスを順番に取り出せる # ファイルパスを拡張子フィルタ検索 if path_obj.match("*.xlsx"): # ファイルパスを指定してオブジェクトwbに読み込む wb = openpyxl.load_workbook(パスオブジェクト) # wbオブジェクトの全てのshに対するfor文がVBAより簡単 for sh in wb: # 「セルの値がなければ」は、 # VBA: sh.Cells(1, 1).Value = "" 等で表す # Pythonでは、 sh.cell(1, 1).value != None # csvに書き出すところが少し慣れが必要かも with open("書き出すパス", "w", encoding="utf_8_sig") as fp: # "w": 書き込み、"utf_8_sig":BOM付きUTF-8 # withから始めると、終了後自動で閉じてくれる # fp は「ファイルポインタ」 csv.writerで書き込めるもの(?) writer = csv.writer(fp, lineterminator="\n") # 行終わりを改行コードを指定してファイルポインタ書込かな # ここでまたもや優れた書き方 for row in sh.rows: # 全行に対して繰り返し # 1行ずつ書き込む記述をリスト内包表記とやらで記述 writer.writerow([col.value for col in row]) # 多分、「1行のうちの列数分の要素をもつリストで、要素はその列の値」という意味なんだろう…
さすがに転記部分はVBAとほぼ同じなので無視
Cellsがcellなだけですたい
最後は少し難読だなー…
慣れだな、うん慣れ！
明日は外部ライブラリをインストールしてみようの巻！
Python DAY3:テキストGET
課題
こんにちは
学習するにあたり、
学習方法に問題を感じました
手を動かしながら
なにかを創作しながらでないと
習得できない！！！
基本でした。。
そこで書籍を探したのですが
このところ技術本ばっか買って
積ん読になっているものも増えてきてしまってたので
なかなかポチれず。
なんとタイムリーに、マコなり社長のこの動画！！
このタイミングはずるい、神様ｗｗｗｗｗｗ
Amazonは翌日
なんとか日付が変わる前にポチれたのがこちら↓
翌日中に届くといえど、
流石に朝イチというわけにはいかず
時間がもったいないので
仕事で必修のkintoneにJavaScriptが使えるとキキ
JSの復習じゃーん！！
JavaScript
ProgateでJS見たことあるかもないかも忘れたかもなので
トライ！
やっぱりかつて抱いていた苦手意識が根強く
恐る恐るでしたがー！
さすがProgate!
難しいことは載っていない！！w
のか、
初学時よりもVBAを使えるように地味な進化をしていたからか
コールバック関数もなんのそのでした。
そういう意味だったのか！
それにPythonを先にやってたのもあり
共通点あるじゃんあるじゃんおもしろーーー！
今まで気づいたことなかったけど、
どんどん違う言語吸収していくの
楽しそーーー＊＊＊
ほんで夕方ようやく書籍を入手
一旦家事に戻るだーっ
Python DAY2:学習サイトで基礎編までを履修
火がついてるうちに
せっかくインストールしたPythonの学習を
少しずつ進めようと思います！
サクサク学習できるサイトを探sou！！
やはり無能には難読ーーー＞＜
よくよく噛み砕いたらわからなくもないですが
今回のテーマは「サクッと」w
↓こちらに決めました↓
ちなみに、選定に使用したサイトはこちら
【決定版】Pythonの基礎を1から学べるチュートリアル20選 | 侍エンジニアブログ
入門編
Progateとほぼ同じなのでスキッッッッッッップ！！！！！！！！！！
基礎編
Progate + α が載っているイメージ
なんし、読むだけだとつまんなーい
ワークしないと身にならないにゃーっ
感想
明日は実際にExcelファイルを触る〜っていうのをやりたい
