色々とグぐってみたら、どうやら、日付のフィルターには癖がある。というのは、例えば、2024/4/7と2024/04/27は別物になるようだ。 これを回避するには、検索の対象列の書式に応じたフィルターをかけるということらしい。 つまり、検索の列nの書式に応じて、AutoFilter n,Format(検索日,"yyyy/m/d")か、AutoFilter n,Format(検索日,"yyyy/mm/dd")にするを選択するわけだ。 あるいは、AutoFilter n,Format(検索日,Cells(2,n).NumberForamatLocal)とする。
2024年4月27日土曜日
2024年3月23日土曜日
ドコモ光の回線でIP電話のオプションをやめたら、回線がリセットされて、復旧するまでとても大変だった話
ドコモ回線に自前のWiFiルータを付けインターネットに接続しているが、先日、ひかり電話(IP電話)のオプション契約を解約した。 すると、NTTはその回線の設定を全て、リセットしたようだ。電話のオプションだけをナシにしてくれればいいのに、全部の登録情報をリセットしたようなので、利用者のワタシは、WiFi側からドコモ回線に接続のためのログインidやパスワードを再度、登録するといった初期設定をやり直すことになった。具体的にはブラウザのアドレスに192.168.2.1を打ち込んで、Wifiの管理画面に入り、勇気を持って、設定のボタンを押して、次々と出てくる画面に適当にかつ適切に答えるだけである。すると、なんとか、その回線は、ワタシのWifiからの接続を許可してくれたのだった。 こう書くと、簡単な話に聴こえるかもですが、ここにたどり着くまでに、自分で色々と試行錯誤し、かつ、インターネットの接続業者のトラブル対応窓口に相談したりと、それはもう、大変な半日を過ごしたのでした。この初期設定のボタンのクリックは、相当の覚悟が必要だった。だって、これまで動いていたのだから。なんでこちらがリセットしなければならないんだと。やれやれ、それそれ。
2024年3月14日木曜日
excelのシートに関数を詰め込み過ぎて、再計算の砂時計に占領された。もはや、vbaを書くしかない。
複数の列の値が一致したら、その行の特定の列を抽出するために、ユーザー定義の関数をvbaのfunctionで書いてみた。 autofilterで複数の列をフィルターし、特定の列を取得するコードを書いてみた。まずは、debugで1行ずつ、動かして、OKだった。 次にシートにその自作関数を埋め込むと、#VALUE!エラーとなった。どうも関数を埋め込んだシートとは異なるシートにautofilterでフィルターをかけるのが無理なようだ。 そこで、AutoFilterでの自作関数は諦めて、index関数とsumproduct関数、row関数のみで作ってみた。例えば、こんな感じだ。 =INDEX(Z:Z,SUMPRODUCT((X:X= Tn )*( Y:Y= Sn ), ROW(Z:Z))) X列の値がTnで、Y列の値がSnの行を見つけて、その行番号のZ列の値を抽出するという式である。 埋め込みの箇所が少ないうちは、快調に動いてくれたのが、excelのシートに関数をたくさん埋め込むと、やたらと再計算で、砂時計がワタシのやりたいことを阻む。 やむを得ず、関数で実現していた処理をvbaで書いた。まず、Autofilterを使わず、まず、X列の値がTn,Y列の値がSnの行を求め、Z列の値を取得するコードを書いた。そして、実行ボタンで必要な時に動かすようにした。この方式でも数が増えると、砂時計が出てきて、時間がかかるようだ。AutoFilterで複数の列をフィルターし、特定の列を取得するコードにすると、数が増えても砂時計は現れず、スーと終わった。AutoFilterは高速だな。シート内関数としては、呼べないけど。やれやれ。それそれ。
Function GetValue(Tn as long,Sn as Long) as String With thisworkbook.worksheets("SearchList") With .Range("A1") AotoFilter ColumnNoForX,Tn AutoFilter ColumnNoForY,Sn If WorksheetFunction.Subtotal(3,.Range("A:A")) > 1 then With .Range("A1").CurrentRegion.Offset(1,0) For Each R in .Resize(.Rows.Count-1).SpecialCells(xlCellTypeVisible).Rows GetValue = R.Range("Z1").Value Next R End With End If End With End With End Function
2024年3月6日水曜日
excelでセルのn文字目が数字か否かを判定するとか、その類(たぐい)の話
Anセルのn文字目を抽出するには Right(Left(An,n),1)だ。 それが数字か否かは Isnumber(Value(Right(Left(An,n),1))))でいい。 ちなみにValueを挟んでいるのがミソだ。 これでセルのn文字目が数字か否かを判定することができる訳だが、いろんな付帯条件が付くことがある。例えば、3文字目が@の場合は除外するなら =IF(Left(An,3)="@",False,If(Isnumber(Value(Right(Left(An,n),1)))),True,False) そして、特定の文字Xが含まれるか否かは COUNTIF(An,"*X*")を使えばいい。
2024年1月30日火曜日
vbaでmenuシートを作成したときにパラメタのセルが指定されている(空白でない)かを判定する方法
vbaでmenuシートのセルA19にパラメタのセルが指定されていると仮定し、そのセルが空白でないか(指定されているか)を判定する方法は、こんな感じだ。
Dim paramSpecified As integer paramSpecified = 0 If WorkSheets("menu).Range("A10").Formula <>"" then pramSpecified = 1
2024年1月7日日曜日
vbaでsqlを使う時の小さいけれども大事なこと。
vbaでsqlを使う時の小さいけれども大事なこと。
- sqlのキーワードの前後、または片方に半角の空白を入れておく。
- 例えば、SELECT*FROMMEMBERS;では、キーワードが認識できず、エラーとなる。SELECT * FROM MEMBERS;とするため、キーワードのSELECTの後やFROMの前後に半角の空白を意識して入れてあげるのだ。文字列の連結を繰り返していると、つい、忘れがちだ。意識して半角の空白を入れること、これが大事だ。
- sql文の記述にはダブルクウォート(")を使い、その中の文字列にはシングルクウォート(’)を使う。
- テーブルの内容といった結果、即ちrecordsetを返すsql(例えば SELECT)にはrs.open strSQLを使い、そうでなければ(例えばINSERT)cm.execute strSQLを使う。
'(1) strSQL = "" strSQL = strSQL & " SELECT " strSQL = strSQL & "*" strSQL = strSQL & " FROM MEMBERS;" '(2) strSQL = "" strSQL = strSQL + " INSERT " strSQL = strSQL & " (NO,NAME) " strSQL = strSQL & " VALUES(1,'田中');"