■
べったら漬け cookpad.com
■
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("H:H") '変更を監視するセル範囲を指定
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
'H列のセルが変更されたときに実行
If Target.Value = "○" Then
Range("A" & Target.Row & ":L" & Target.Row).Interior.ColorIndex = 15 'グレーアウト
Else
Range("A" & Target.Row & ":L" & Target.Row).Interior.ColorIndex = xlNone 'グレーアウト解除
End If
End If
End Sub
Excel×Python 基本、Excelを読み込む、列のを取り出して、ファイルを分けたり
環境
Anaconda Jupyter
!pip install openpyxl
必要なライブラリをインポートする
import openpyxl import pandas as pd import glob
変数を入れる
import_file_path = '/〜/sample-1.xlsx' #ファイルのパスを作成 excel_sheet_name = '発注管理表' #編集するシート名 export_file_path = '/〜/output' #作成したファイルの格納石のディレクトリ
※Jupyterのコピー機能を使ってもパスがうまく取れなかったら、Termのpwdから取得してしまった方が早い！
df_order = pd.read_excel(import_file_path, sheet_name = excel_sheet_name) #Excelファイルを読み込む
df_order #中身を見る
出力
ユニークな会社名を取り出す
company_name = df_order['会社名'].unique()
出力（重複を除く会社名を取得）
company_name
会社名という列の中で、指定した会社名だけ取得するように指定する
df_order['会社名'] == '株式会社A' #会社名という列の中で、指定した会社名だけ取得するように指定する
df_order[df_order['会社名'] == '株式会社A'] #こうすることでTrueの行だけ抽出できる
#先程取得した会社名が先頭から順にiに代入される。
for i in company_name:
print(i)
for i in company_name:
df_order_company = df_order[df_order['会社名'] == i]
print(df_order_company)
#会社ごとのデータが分かれる
#会社ごとに分けたデータをファイルに書き込む
for i in company_name:
df_order_company = df_order[df_order['会社名'] == i]
df_order_company.to_excel(export_file_path+'/'+i+'.xlsx')
筋トレまとめ
腹筋
座って行う
www.youtube.com
www.youtube.com
* スパイダープランク
* ニートゥチェスト
* クランチ
* 踵タッチ
* 足バタバタ
* 足で円を書く
* 片足ずつ膝を胸に引きつける
* 肘をついて両足を胸に引きつける
* シザーズレッグレイズ（肘をついて片足ずつ交互に上げる）
* 肘ついて両足を開く閉じる
* ヒップロール
※頭を上げて膝を見る
https://www.youtube.com/watch?v=64qsroYqaRE
立って行う腹筋トレーニング
www.youtube.com * 体を捻って膝上げ * 膝げり * スクワットして踵タッチ
腕
www.youtube.com * プッシュアップ ⇨体は真っ直ぐに。脇は閉める * リバースプッシュアップ ⇨肘は後ろに曲げる * レッグプル・アームカール ⇨膝の裏を持って胸に引きつける * キックバック * グーを重ねる ⇨上腕三頭筋と上腕二頭筋を同時に鍛えられる、肘固定、しっかり伸ばししっかり曲げる
見直すべきVBAの設定
画面の配色やフォントの変更
メニュー「Excel」⇨「環境設定」⇨タブ「エディターの設定」
好みの設定をする
以下のように一通り設定する（色は好みで）。以下は一例
標準コード：前景⇨黄色、背景⇨黒
コメント：前景⇨濃い緑、背景⇨白
キーワード：前景⇨水色、背景⇨黒
識別子：前景⇨黄色、背景⇨黒
フォント：Meiryo UI
サイズ：12
自動構文チェックの無効化
VBAにはリアルタイムに構文チェックをする「自動構文チェック」機能があるが、構文エラーが発生するたびにエラーのダイアログが表示されると煩わしいため、オフにすると良い。
※自動構文チェックを無効化しても、構文エラーはコードウィンドウ上の表示色で把握できるので問題ないはず
1. メニュー「Excel」⇨「環境設定」⇨タブ「編集」
- 「自動構文チェック」をオフにする。
変数の宣言を強制する
余計なエラーを防ぐために変数の宣言を忘れないように設定するとよい
上記操作と同じ画面で「変数宣言が必要です」にチェックを入れる
参考資料
スケジュール表の描画（ステップ２）月と日と曜日を別々の列にわけて描画
参考文献
最終イメージ
・同じA列に描画していた月と日と曜日を仕様通りA〜C列に別々にわけて描画するようコードを変更する
まずは罫線と列幅を使用通りに描画
・A~Dの４列の表にする
・前の回で学んだ列幅を設定するColumnWidthプロパティを用いてA~D列の列幅を使用通り設定、罫線も使用通りひく
（作成イメージ）
※A列は「8/26(水)」という形式を表示するには幅が足りなくなり、
＃＃＃と表示されているが、問題ない
（コード）
Public Sub スケジュール表描画()
Workbooks.Add '新規ブック追加
Range("A1").Value = "2009/8/26" ’A1セルに2009年8月26日の文字列を入力
Range("A1").NumberFormatLocal = "m/d(aaa)" ’表示形式を設定
'オートフィルで連続した月日曜日を描画
Range("A1").AutoFill Destination:=Range("A1:A32"), Type:=xlFillSeries
Range("A1:D32").Borders.Weight =xlThin '格子状の細線を引く
Range("A1:D32").BorderAround Weight:=xlThick '周囲に太線をひく
Range("A1").ColumnWidth = 3 'A列の列幅を3に設定
Range("B1").ColumnWidth = 3 'B列の列幅を3に設定
Range("C1").ColumnWidth = 3 'C列の列幅を3に設定
Range("D1").ColumnWidth = 40 'D列の列幅を40に設定
End Sub
変数のより高度な使い方にチャレンジ
・次に月と日と曜日をA〜C列に分けて描画するようコードを追加・変更する
⇨それぞれの数値や文字列を日付から取得する必要がある
⇨日付データから月の数値を取り出すにはMonth関数を使う。同様にDay関数、Weekday関数とWeekdayName関数を組み合わせることで描画できるが、オートフィルで一旦入力した日付データを取り出し関数で処理した後、月の数値で上書きすることになる。そうなると日付データを各列に入力する処理は全く無駄になる
⇨A列に各セルに入力した日付データを一度どこか別の列に取り出しておく手法でも、できるが無駄があり処理が面倒になる
⇨そこで、今回はオートフィルではなく、変数とループを組み合わせた手法で、月と日と曜日を列を分けて描画する
（ポイント）
・日付データはセルに一旦入力せず、変数としてプログラム内で直接扱う
⇨その変数は日付データを扱うということで日付型Date型として宣言して用意する
Date型変数を宣言
👇
Date型変数に2009年8月26日を代入
👇
Do Until…Loopステートメントにて繰り返す中で、Date型変数を1日ずつ進めながら、スケジュール表のA〜C列に月と日と曜日を1行ずつ入力
👇
2009年9月26日に達したらループ終了
コードの変更
Option Explicit
Public Sub スケジュール表描画()
Dim myDate As Date '処理中の日付を表す変数
Workbooks.Add '新規ブック追加
'Range("A1").Value = "2009/8/26" ’A1セルに2009年8月26日の文字列を入力
'Range("A1").NumberFormatLocal = "m/d(aaa)" ’表示形式を設定
'オートフィルで連続した月日曜日を描画
'Range("A1").AutoFill Destination:=Range("A1:A32"), Type:=xlFillSeries
myDate = #8/26/2009# 'myDateを2009年8月26日にセット
Range("A1").Value = Month(myDate) 'A列に月を入力
Range("B1").Value = Day(myDate) 'B列に日を入力
Range("C1").Value = WeekdayName(Weekday(myDate),True) 'C列に曜日を入力
Range("A1:D32").Borders.Weight =xlThin '格子状の細線を引く
Range("A1:D32").BorderAround Weight:=xlThick '周囲に太線をひく
Range("A1").ColumnWidth = 3 'A列の列幅を3に設定
Range("B1").ColumnWidth = 3 'B列の列幅を3に設定
Range("C1").ColumnWidth = 3 'C列の列幅を3に設定
Range("D1").ColumnWidth = 40 'D列の列幅を40に設定
End Sub
（ポイント）
・曜日の文字の取得は日付データから取得できるVBA関数はないので、２段階を踏む
⇨Weekday関数で曜日を表す整数値を取得『Weekday(日付データ)』、
取得した曜日を表す数値からWeekdayName関数を使って曜日名の文字列に変換する『WeekDayName(曜日を表す整数値), 曜日名の省略』
⇨第２引数には、省略した曜日名の文字列を取得するならTrueを指定
⇨Weekday(myDate),True
コードの整理・改良
（ポイント）
数値や文字列などを直接記述している箇所を呈すに置き換える
セルのアドレスを直接記述している箇所を、基点セルを基準に指定するよう変更
（例） ・英数字記号のみで命名 ・英単語で命名 ・変数と定数の区別 ①プロシージャレベル変数 ⇨アルファベット小文字で始まり、複数の単語で構成される定数名は、当たらな単語の冒頭１文字のみ大文字 （例）：myDate ②モジュールレベル変数 ⇨アルファベット小文字の「m」を必ず頭につける （例）：mMyDate ③定数 ⇨全てアルファベット大文字、複数の単語で構成される定数名は、単語の区切り毎に「_（アンダースコア）」を入れる （例）：MONTH_WIDTH
Option Explicit
Const ORG_DATE As Date = #8/26/2009# '開始年月日
Const DST_DATE As Date = #9/26/2009# '終了年月日
Const DAY_OFST As Integer = 1 '日を描画する列の位置
Const WEEKDAY_OFST As Integer = 2 '曜日を描画する列の位置
Const MONTH_WIDTH As Integer = 3 '月の列幅
Const DAY_WIDTH As Integer = 3 '日の列幅
Const WEEKDAY_WIDTH As Integer = 3 '曜日の列幅
Const SCHDL_WIDTH As Integer = 40 'スケジュール欄の列幅
Public Sub スケジュール表描画()
Dim myDate As Date '処理中の日付を表す変数
Workbooks.Add '新規ブック追加
myDate = ORG_DATE 'myDateを開始年月日にセット
Range("A1").Activate 'A1セルをアクティブセル化
Do Until myDate > DST_DATE '処理中の日が終了年月日に達するまでループを回す
ActiveCell.Value = Month(myDate) '左端の列に月を入力
ActiveCell.Offset(0, DAY_OFST).Value = Day(myDate) '月の1列右隣に日を入力
ActiveCell.Offset(0, WEEKDAY_OFST).Value _
= WeekdayName(Weekday(myDate), True) '月の2列右隣に曜日を入力
myDate = DateAdd("d", 1, myDate) '処理中の日付を1日進める
ActiveCell.Offset(1, 0).Activate 'アクティブセルを1行進める
Loop
Range("A1:D32").Borders.Weight = xlThin '格子状の細線を引く
Range("A1:D32").BorderAround Weight:=xlThick '周囲に太線を引く
Range("A1").ColumnWidth = MONTH_WIDTH '月の列幅を設定
Range("B1").ColumnWidth = DAY_WIDTH '日の列幅を設定
Range("C1").ColumnWidth = WEEKDAY_WIDTH '曜日の列幅を設定
Range("D1").ColumnWidth = SCHDL_WIDTH 'スケジュール欄の列幅を設定
End Sub
開始/終了年月日から日数を求め、スケジュール等のセル範囲を取得するよう改良
Withステートメントでまとめる
Option Explicit
Const BASE_ADRS As String = "A1" '基点セルのアドレス
Const ORG_DATE As Date = #8/26/2009# '開始年月日
Const DST_DATE As Date = #9/26/2009# '終了年月日
Const DAY_OFST As Integer = 1 '日の列の位置
Const WEEKDAY_OFST As Integer = 2 '曜日の列の位置
Const SCHDL_OFST As Integer = 3 'スケジュール欄の列の位置
Const MONTH_WIDTH As Integer = 3 '月の列幅
Const DAY_WIDTH As Integer = 3 '日の列幅
Const WEEKDAY_WIDTH As Integer = 3 '曜日の列幅
Const SCHDL_WIDTH As Integer = 40 'スケジュール欄の列幅
Const SCHDL_DAYS As Integer = 32 'スケジュール表の期間の日数
Public Sub スケジュール表描画()
Dim myDate As Date '処理中の日付を表す変数
Dim baseCell As Range '基点セル
Dim tableArea As Range 'スケジュール表の範囲
Workbooks.Add '新規ブック追加
Set baseCell = Range(BASE_ADRS) '基点セルをセット
myDate = ORG_DATE 'myDateを開始年月日にセット
'------------- 月と日と曜日を描画 -------------
baseCell.Activate '基点セルをアクティブセル化
Do Until myDate > DST_DATE '処理中の日が終了年月日に達するまでループを回す
With ActiveCell
.Value = Month(myDate) '左端の列に月を入力
.Offset(0, DAY_OFST).Value = Day(myDate) '月の1列右隣に日を入力
.Offset(0, WEEKDAY_OFST).Value _
= WeekdayName(Weekday(myDate), True) '月の2列右隣に曜日を入力
myDate = DateAdd("d", 1, myDate) '処理中の日付を1日進める
.Offset(1, 0).Activate 'アクティブセルを1行進める
End With
Loop
'------------- 罫線を描画 -------------
Set tableArea = Range(baseCell, _
baseCell.Offset(SCHDL_DAYS - 1, SCHDL_OFST)) 'スケジュール表の範囲を設定
tableArea.Borders.Weight = xlThin '格子状の細線を引く
tableArea.BorderAround Weight:=xlThick '周囲に太線を引く
'------------- 列幅を設定 -------------
With baseCell
.ColumnWidth = MONTH_WIDTH '月の列幅を設定
.Offset(0, DAY_OFST).ColumnWidth = DAY_WIDTH '日の列幅を設定
.Offset(0, WEEKDAY_OFST).ColumnWidth = WEEKDAY_WIDTH '曜日の列幅を設定
.Offset(0, SCHDL_OFST).ColumnWidth = SCHDL_WIDTH 'スケジュール欄の列幅を設定
End With
End Sub
次は
月の変わり目の日のみ、月と罫線を描画するように変更します！
実際のWebページから情報を読み取る！
参考文献
今度はインターネット上のWebページからCSSセレクタを考え、情報を読み取ります
情報が日付ごとに並んでいるものを参照し、最新の日付の新着情報だけを検索します
CSSセレクタを考える（最新日付の要素を取り出す）
まずこれらの要素を検索するためのCSSセレクタを考える
ここでは、最新の日付の箇所を右クリックして、「検証」を選択し、Chromeでデベロッパーツールを画面表示させる
すると、Elementsタブの画面の要素から、id属性がnewsのdiv要素のなかにdt要素が並んでいて、その中の１番目が最新日付に該当することがわかる
このようにおねじ種類の要素が複数並んでいて、そこから「n番目」の要素を取得するには、CSSセレクタは「要素名：nth-of-type(n)」と指定する。
今回は１番目のdt要素なので、dt:nth-of-type(1)で取得できる
したがって、Ctrl＋Fキーで表示される検索BOXにCSSセレクタを入力すれば、この要素が1つだけ検索されるのを確認できる
次に新着情報のリンクの部分のCSSセレクタを調べる
同じくデベロッパーツールを使って、要素を見ると、id属性が"news"のdiv要素のなかにdd要素が並んでいて、最新情報はその１番目の中にあるのがわかる。
その１番目のdd要素は日付の要素と同様でCtrl＋Fで表示される表示される検索BOXに#news dd:nth-of-type(1) li a を指定するば最新の新着情報のa要素を特定できる
要素から情報を読み取る
取得したCSSセレクタを使って、要素を取得して情報を読み取る！
（web_gaimu_news.py コード前半）※Mac使用
from selenium import webdriver
from selenium.webdriver.common.by import By
import openpyxl
# chromedriverがある場所
driver_path = "driver/chromedriver"
# webdriverの作成
driver = webdriver.Chrome(executable_path=driver_path)
# 要素が見つからない場合は10秒待つように設定
driver.implicitly_wait(10)
# 総務省の新着情報を開く
driver.get("https://www.mofa.go.jp/mofaj/shin/index.html")
# 最新の日付
date_elem = driver.find_element(By.CSS_SELECTOR, "#news dt:nth-of-type(1)")
date_text = date_elem.text
# 最新の新着情報のa要素
links = driver.find_elements(By.CSS_SELECTOR, "#news dd:nth-of-type(1) li a")
# 読み取り結果のリスト（ここに読み取り結果を入れる）
data_list = []
for link in links:
# テキスト
link_text = link.text
# リンク先
link_url = link.get_attribute("href")
# リストに登録
data_list.append([date_text, link_text, link_url])
# 確認表示
print(date_text, link_text, link_url)
# ブラウザを閉じる
driver.quit()
▼結果
すごい！ちゃんと、Chrome表示され、データの読み取りもできた！
（ポイント）
最初に外務省の新着情報のページを開く
1から、find_elements()でCSSセレクタを用いて、最新の日付の要素を取得して、日付のテキストを.textで読み取る
次に、最新の新着情報のa要素は複数の場合があるので、find_elements()で取得し、その結果をfor文で繰り返し処理し、a要素からテキスト（内容）とリンク先（href属性の値）を読み取り、日付と一緒にdata_listに追加する。同時に確認のために、追加した内容をprint()で出力
※今回はインターネット上のWebページを読み取るため、読み込み2時間がかかる場合を考慮して、driver.implicitlly_wait(10)により、要素が見つかるまでの待機時間を10秒に設定しておく読み取った情報をExcelに保存する
（ポイント）
openpyxl.Workbook()で新規に作成したブックに保存
Excelファイルに1行ずつ書き込めるよう、行番号のrow_numをループ毎に1つづつ増やすことで、1行ずつ「日付、テキスト、リンク先」を書き込む
全てのデータを書き込んだらファイル名をつけて保存
（web_gaimu_news.py コード全体）※Mac使用
from selenium import webdriver
from selenium.webdriver.common.by import By
import openpyxl
# chromedriverがある場所
driver_path = "driver/chromedriver"
# webdriverの作成
driver = webdriver.Chrome(executable_path=driver_path)
# 要素が見つからない場合は10秒待つように設定
driver.implicitly_wait(10)
# 総務省の新着情報を開く
driver.get("https://www.mofa.go.jp/mofaj/shin/index.html")
# 最新の日付
date_elem = driver.find_element(By.CSS_SELECTOR, "#news dt:nth-of-type(1)")
date_text = date_elem.text
# 最新の新着情報のa要素
links = driver.find_elements(By.CSS_SELECTOR, "#news dd:nth-of-type(1) li a")
# 読み取り結果のリスト（ここに読み取り結果を入れる）
data_list = []
for link in links:
# テキスト
link_text = link.text
# リンク先
link_url = link.get_attribute("href")
# リストに登録
data_list.append([date_text, link_text, link_url])
# 確認表示
print(date_text, link_text, link_url)
# ブラウザを閉じる
driver.quit()
# 新しいブックに保存
wb_new = openpyxl.Workbook()
ws_new = wb_new.worksheets[0]
row_num = 1
for data in data_list:
# 日付
ws_new.cell(row_num, 1).value = data[0]
# テキスト
ws_new.cell(row_num, 2).value = data[1]
# リンク先
ws_new.cell(row_num, 3).value = data[2]
row_num = row_num + 1
wb_new.save("外務省新着情報.xlsx")
▼結果
ちゃんと出力され、Excelにも書き込みと保存がされてた🎶😄
珍しくスムーズに行って怖いくらいｗｗ
requestsとBeautifulSoupによる方法
今回はSeleniumを使っているが、Webから情報を収集するには、requestsとBeautifulSoupという2つのモジュールを使う方法の方が一般によく使われる
これらの方法は、Webブラウザを使わないで、直接WebサーバーからWebページのHTMLをrequestsを用いて受信する。その内容を
BeautifulSoupで解析して情報を収集する。
しかし、Webページにはコンテンツを部分的に読み込みながら表示するものもあるため、Webブラウザを使うのがいちばん確実。
ただし、SeleniumのWebブラウザを操作すれば、表示できるWebページなら基本的にページ内のどこからでも情報を読み取れるが、Webページを毎回表示するため、requestsに比べて処理が遅くなる。それを解消するために、画面なしで動作ヘッドレスブラウザをSeleniumで操作する方法もあるとのこと
次は・・・
次はWebからの情報収集を自動化するを学びます！
この投稿がお役に立てたら、★ポチッとお願いします😁
