X

Excel総合相談所 136

■ このスレッドは過去ログ倉庫に格納されています
0001名無しさん＠そうだ選挙にいこう
垢版 |
2019/07/27(土) 17:47:29.09
【1 OSの種類 　 　 　 　 .】 Windows**
【2 Excelのバージョン 　 】 Excel**
【3 VBAが使えるか　 　 .】 はい･いいえ
【4 VBAでの回答の可否】 可･否

▼━関連スレ━━━━━━━━━━━━

前スレ
Excel総合相談所
https://find.5ch.net/search?q=excel

Excel VBA 質問スレ
https://find.5ch.net/search?q=excel+vba

【質問不可】Excel総合相談所スレの雑談・議論スレ
https://find.5ch.net/search?q=excel+%E3%80%80%E7%9B%B8%E8%AB%87%E6%89%80
0766765
垢版 |
2019/09/16(月) 19:20:12.43
>>729
(続き)
※ Power Query内の、1つのクエリー内であれば、
列を追加、番号を振る、重複を削除、値の置換、列を追加しての簡単な計算・・・
等をそれぞれマクロのようにして順番に処理することが可能。
2つ以上のクエリーやテーブル(範囲)を合体させることも可能。

※ Power Queryの関数は "M function" まだ、英語版の説明しかない模様。
https://docs.microsoft.com/ja-jp/powerquery-m/power-query-m-function-reference

・PQエディターを開いたところ
https://i.imgur.com/0pC3TPn.png
右側の「適用したステップ」の一覧が、個別のマクロみたいなもの。

・「詳細クエリー」画面の中身
https://i.imgur.com/vBHPEEr.png
エディターのボタンメニューで操作した内容が、スクリプトになっている。
ステップの途中で列名を変更を挿入したりするとエラーが出るので、
ここで直接修正したり、とか。
(続く)
0767765
垢版 |
2019/09/16(月) 19:20:54.76
>>729
(続き)
C. この表の場合は、「地区」と「停電軒数」を1セットとして2列になっているので、
Excelのsheetの範囲を元にして再度PQで分割・合体したテーブルを作ってみた。

D. 後は文字列を数値化するのにはExcelの文字列関数の方が得意そうなので、
sheet内のC.のテーブルの右に列を追加してExcel関数処理とか。
こんな感じ。(深緑の列がsheet内で作業列を追加してExcel関数で処理した部分)
https://i.imgur.com/1eQdwu2.png

後はExcelの3Dマップ使って棒グラフ作るとか。
こんなのとか。
https://i.imgur.com/Lj9hgtb.png
3Dマップも脚注などはまだ手動入力するしかなさそうだけど。
(続く)
0768765
垢版 |
2019/09/16(月) 19:27:16.33
>>729
(続き)
Power Pivotの方はまだ使ってみていないけれど
https://i.imgur.com/Izmfl2c.png
自己流の手探りでPQだけ使ってみた
Power Queryの泣き所は、

1. Webスクレイピング機能はあくまで簡易で、html記述まで入り込んでの指定は出来なさそう。
東電停電情報の場合は、一覧表の外の「データ更新日時」や「合計停電軒数」を指定出来ないため、手入力した。
→ このあたりはPythonの方が得意か？

2. 1つのbook内で複数のクエリーがある場合、シーケンシャルに更新できない。
作った各クエリーは計算手順上、順番に再計算させたいのに、
個別のクエリーを指定して更新するか、全てのクエリーを更新するか、しかない。
後者の場合は同時並行で再計算してしまう模様。
→ 確実にシーケンシャルに再計算させるには、今のところVBAかますしかないのかな？
(終わり)
0776名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/17(火) 00:31:00.75
SUMPRODUCTって便利だってよく聞くから覚えようと思いつつ応用のさせ方がイマイチ分からず後回しになってる
この機に覚えます！
0777名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/17(火) 00:56:38.02
>>775
うーん・・・

VBAが出来ること全てをPQで出来るわけではなさそうだけど、
「VBAのようなプログラミング言語の文法を覚えなくても、作れるマクロ／いじれるマクロ」
という意味では、いけるかも？

大容量データを全て関数処理したら重くなる。
PQだと、小分けにしてステップを切れるのが良いところ。
ただし、今のところ更新は、手動で1つ1つ実行だし、
各ステップの分岐判断やループは出来なさそう。

他のbookからリンク参照するのは、ファイルのディレクトリや名前をいじるとすぐリンク切れるから、
それよりはPQでの修正はしやすい。

リンクだと、基本1セル。
PQだと、他のbookのテーブル/範囲まるごと、コピーしてくる感じ。

ただし、参照先のbookの最終更新時刻なんかのファイル属性を参照する関数が、
PQのそれには見当らないのは頂けない。
0778名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/17(火) 13:02:47.77
【1 OSの種類 　 　 　 　 .】 Windows10
【2 Excelのバージョン 　 】 Excel2013
【3 VBAが使えるか　 　 .】 はい
【4 VBAでの回答の可否】 可

seleniumVBAを使ってchromeを操作しようとしたのですが、あるところでNot found elementエラーが起き、原因が不明です。
基本的に
0779名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/17(火) 13:07:21.65
【1 OSの種類 　 　 　 　 .】 Windows10
【2 Excelのバージョン 　 】 Excel2013
【3 VBAが使えるか　 　 .】 はい
【4 VBAでの回答の可否】 可

seleniumVBAを使ってchromeを操作しようとしているのですが、あるところでNot found elementエラーが起き、原因が不明です
・F12でデベロッパーツールを表示して、操作したい要素のidやnameやXpathをコピーしているので、ここの誤入力はないと思います
・iframeやframeの中の要素でないことも確認しました
・フォーカスしているchromeのタブが間違っていないことも確認しました
・.wait()メソッドでwebページが表示しきるまで待機しました

何かアドバイスを頂ければと思います。
0781名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/17(火) 14:36:36.79
上記の質問に関連してなのですが、

Aframe
Bframe-Cframe
　　　　 -Dframe

以上のような階層のframeがあったとして、A,B,Cframeの要素は、SwitchToFrameメソッドでframeを指定して取得することができました。
しかし、Dframe内の要素が取得できません。（element not found for・・・）
このページに遷移する前に、ほぼ同じことをして、そこではうまく取得できているのですが・・・　何故でしょうか

連投申し訳ございません。
0786名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/17(火) 19:12:53.52
今時ネットで検索の仕方判らないってやばいぞ…
つーかみんなで編集してるっぽいQ&Aってネットにあるやつじゃないのか
0787765
垢版 |
2019/09/17(火) 19:33:27.07
>>768
> 1. Webスクレイピング機能はあくまで簡易で、html記述まで入り込んでの指定は出来なさそう。
> 東電停電情報の場合は、一覧表の外の「データ更新日時」や「合計停電軒数」を指定出来ないため、手入力した。
> → このあたりはPythonの方が得意か？

このあたり、Power Queryの「HTML ページ」形式での読込み設定では
「地区ごとの一覧表」しか読み込めなかったんだけど、
あれから「もしや？」と思って、
PQの「Webから」の読込みで
・URLを指定するのは同じで
・「形式を指定してファイルを開く」を「テキスト ファイル」
・「元のファイル」はどうやらエンコード指定みたいで、東電停電情報の場合、「10001: 日本語 (Mac)」だと、漢字が文字化けせず、よさげ。
https://i.imgur.com/JHHgcOm.png

で取り込むと、htmlのスクリプトを各行Text形式で読めた。
https://i.imgur.com/9zYJHsP.png

スクリプトの行番号等が改変されなければ、Pythonまで使わなくても、PQで同時に取り込めるかも。
0788名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/17(火) 21:22:51.80
Ｂ２〜Ｌ２を結合させたセルがあり、シートのＦ列だけを削除することはできるのですが、
マクロ記録で作った構文

Columns("F:F").Select
Selection.Delete Shift:=xlToLeft

を実行させると、Ｂ列からＬ列が消えていしまいます。
Ｆ列だけ削除するにはどうすればよいでしょうか
0790788
垢版 |
2019/09/17(火) 21:46:13.28
>>789

ありがとうございます<(_ _)>
0792名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/18(水) 00:36:57.35
自動的にセル名を定義する方法はありますか。

例えばA1セルに「野菜」と入力したら、
自動的にA1：A10の範囲が「野菜」セルと
してセル名設定され、A1を「果物」変更
するとセル名も自動で変わるようにしたいです。
0793名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/18(水) 13:09:29.17
ブックAのシート1〜iまでのA1セルの数字を合計した数字をブックBのA1セルに転記したいです
vbaでなんとかならないでしょうか？
0795名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/18(水) 18:57:04.69
Ｄ２〜Ｇの４列中に含まれる"株式会社"・"有限会社"・"支店"を消す下の構文を作ったのですが
効果が同じシートの全てのセルに適応されてしまいます。Ｄ〜Ｇの範囲内に収めるにはどう書くのが正しいのでしょうか

i = Cells(Rows.Count, 1).End(xlUp).Row

Range("D2:G" & i).Select
Cells.Replace What:="株式会社", Replacement:=""

Range("D2:G" & i).Select
Cells.Replace What:="有限会社", Replacement:=""

Range("D2:G" & i).Select
Cells.Replace What:="有限会社", Replacement:=""
0797796
垢版 |
2019/09/18(水) 19:39:02.28
>>796

勉強になりました　ありがとうございます
0799名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/19(木) 18:15:05.74
Ａ列〜Ｊ列に空白のセルがあれば、そのセルの１つ上のセルをコピーする
（Ａ列の最終行まで繰り返す）ができるコードを教えてください。

＊１行目と２行めは必ず値が入っています
0800名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/19(木) 20:02:01.15
a列だけhttps://docs.microsoft.com/ja-jp/office/vba/excel/concepts/cells-and-ranges/fill-a-value-down-into-blank-cells-in-a-column

Sub FillCellsFromAbove()
' Turn off screen updating to improve performance
Application.ScreenUpdating = False
On Error Resume Next
' Look in column A
With Columns(1)
' For blank cells, set them to equal the cell above
.SpecialCells(xlCellTypeBlanks).Formula = "=R[-1]C"
'Convert the formula to a value
.Value = .Value
End With
Err.Clear
Application.ScreenUpdating = True
End Sub
0802名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/19(木) 20:19:08.89
>>801

できました。ありがとうございます
0803名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/19(木) 21:12:47.03
>>799
もう1列使えば、数式だけでいける
A列が元データで、B列が空白を埋めたデータの場合
b2に"=if(IsEmpty(a2),b1,a2)"を入力して下向きにフィル
0805名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/19(木) 22:18:42.68
https://dotup.org/uploda/dotup.org1948859.zip.html
このファイルでマクロを実行すると、自分の理解では「if k=3 and m=1 and Range(E10).Value <= m(=1)」となる瞬間が来て、、
Ｇ１０セルはＡになるはずなのですが、どうしてもすべてＢになります
どこが間違っているのでしょうか？
0807名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/19(木) 23:35:19.41
【1 OSの種類 　 　 　 　 .】 Windows１０
【2 Excelのバージョン 　 】 Excel２０１９
【3 VBAが使えるか　 　 .】 コピペだけできます。
【4 VBAでの回答の可否】 可

"元帳"というマクロを実行するＢｏｏｋの中に"請求一覧"というシートがあります

そしてそのＢｏｏｋ"元帳"と同じフォルダ内に、さらに"請求書"と言うフォルダがあり、その中に複数の.XLSファイルがあります。
その複数の.XLSファイルのそれぞれの"Sheet1"のA列〜K列の２行目から最終行（Ａ列）までをコピーして
シート"請求一覧"の最終行（Ａ列）に貼り付けて追加していくコードをおしえてください。
また、マクロを実行する"元帳"ｂｏｏｋが入っているフォルダアドレスは変動しますので、自動取得できるようにしていただけると助かります。
0808名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/20(金) 00:43:19.56
>>807
コピペだけを「使える」とは言わない
Dim path As String
Dim ws1 As Worksheet
Dim wb As Workbook
Dim ws2 As Worksheet
With ThisWorkbook
　path = Dir(.path & "\請求書\*.xls")
　Set ws1 = .Worksheets("請求一覧")
End With
Do While path <> ""
　Set wb = Workbooks.Open(path)
　set ws2 = wb.Sheets("Sheet1")
　ws2.Range("a2:k" & ws2.Cells(Rows.Count, 1).End(xlUp).Row).Copy ws1.Range("a" & ws1.Cells(Rows.Count, 1).End(xlUp).Row + 1)
　End With
　wb.Close (False)
　path = Dir()
Loop
0810名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/20(金) 13:07:32.49
>>808

Dim path As String
Dim ws1 As Worksheet
Dim wb As Workbook
Dim ws2 As Worksheet
With ThisWorkbook
path = Dir(.path & "\請求書\*.xls")
Set ws1 = .Worksheets("請求一覧")
End With
Do While path <> ""
Set wb = Workbooks.Open(path)
Set ws2 = wb.Sheets("Sheet1")
ws2.Range("a2:k" & ws2.Cells(Rows.Count, 1).End(xlUp).Row).Copy ws1.Range("a" & ws1.Cells(Rows.Count, 1).End(xlUp).Row + 1)
wb.Close (False)
path = Dir()
Loop

試しに"請求書"フォルダに"20"・"25"・"30"というXLSファイルを入れて試したのですが

実行時エラー'1004':
申し訳ございません。20.XLSが見つかりません。名前が変更されたか、移動や削除が行われた可能性があります。

がでて処理が止まります。
0813名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/20(金) 19:59:57.36
xlsxは圧縮されているので、速度的に複数ファイルの読み取りには向いてないと思うんすよ。
代わりにxmlが良いんじゃないかと思うんすけど、どうっすかねぇ?
そんなの気にするのは少数派?
0814765
垢版 |
2019/09/20(金) 21:21:57.67
>>768 の
> 2. 1つのbook内で複数のクエリーがある場合、シーケンシャルに更新できない。
について、どうやらそれらしい設定方法が分かったので報告。

各クエリーに設定される「接続のプロパティ (クエリ プロパティ)」のうち、
　「バックグランドで更新する」
がデフォではONになっているので、
これをクエリーそれぞれで外せば、上から順番に計算しているっぽい。
https://i.imgur.com/K2oCmYx.png

メニューの「データ−クエリと接続−すべての更新▼」リボンボタンの中にある
「接続のプロパティ」からアクセス。

紛らわしいが、「データ−プロパティ」からでない。
(こっちは「列幅を調整する」「新しいデータのセルを挿入し、使用されていないセルを削除する」などの設定)

これで「すべての更新」ボタン押し一発(記録されたマクロ一回)で、
Excel関数で追加した計算も含めて、希望どおりシーケンシャルに結果が出た。
0817685
垢版 |
2019/09/21(土) 13:53:58.48
>>816
うるさい、このハゲ！
0820685
垢版 |
2019/09/21(土) 16:21:02.96
>>819
そんなハゲは発言をひかえていただきたい
0821名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/21(土) 17:10:05.60
質問者は基本的に言葉足らずなので関数を知らないのか敢えてのマクロなのか他人には判らない
回答者が多少気を遣ってアドバイスするとそうなってしまうのは仕方がない
0823765
垢版 |
2019/09/21(土) 19:35:43.66
蛇足だけど、
>>787 の
「Webから」データ取得して、html自体をテキストで取得する
には、ちょっとコツが必要だった。

「Webから」で素直に表示されるダイアログ画面は、imgur画像の1枚目のような、
・「形式を指定してファイルを開く」
・「元のファイル」 (実質「エンコード」)
のプルダウンメニューは含まれていない。
ところが、

1. 一度URLだけ入力して、PQが自動認識出来るクエリーをそのまま作らせる。
2. このクエリーのステップのうち、一番上の「ソース」を再度「編集」で開くと、1枚目の画像のようなダイアログが現れる。

一応、MSには「最初から選ばせてくれ」との改善要望を出しといた。
0824名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/21(土) 20:13:22.37
しつけーなPQが想定してる形態じゃねーんだよ
自分でコードかけクソが
0825名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/21(土) 20:15:13.46
なんでweb上からテキストデータ取って来て解析までしなきゃなんねーんだよアホか
0826名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/21(土) 20:19:34.99
本当にこういう自分の思う通りじゃないからクソとかいってクレーム入れてくるクソ全員死なねーかな
クソテキストでデータ提供する奴と一緒にはやく死滅してくれ
0827名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/21(土) 20:24:39.15
クソに対応するためにMSに要望送れるとか神経疑うわ
自分がやべーことに全然気づいてないクソ中のクソ
0828807
垢版 |
2019/09/21(土) 20:27:52.78
>>808
>>811

どの部分を直せばよいでしょうか？
0831名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/21(土) 20:52:52.30
出版がどうこうとか完全に頭湧いてるな
出版社に迷惑かける前に病院行ってこい。精神科のあるデカいとこいけよ
0839名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/22(日) 10:34:03.66
「みんなのためになる」だろうと思って
PQ話をしたつもりだったんだけどな。
別に既存のVBAや関数と排他ではない、追加機能だし。
0842名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/22(日) 10:42:37.61
ああ、萎えたわ。
たかが5chのスレの中で、
マウンティングしてポジション取りしか考えてない
田吾作相手じゃ、やってられんわ。
0847名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/22(日) 18:26:01.92
【1 OSの種類】 Windows10
【2 Excelのバージョン 】 Excel 2016
【3 VBAが使えるか】いいえ
【4 VBAでの回答の可否】 否(VBAが必要そうなら勉強します)

セルに4桁の銘柄コードを入力すると、別のセルに株価が表示できるようにしたいです
http://enjoyjob.net/wp/2018/04/27/excelformula01/
が使えるかと思ったのですが、
このサイトの通りA2に銘柄コード、B2〜D2に数式をいれても、
「この数式には問題があります」というメッセージが出るだけでした
0849名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/22(日) 20:04:26.17
>>848
ありがとうございます
まさか例文が間違っていたとは…

B列、C列については、ご指摘の部分の修正で動作しました
肝心のD列はやはり「この数式には問題があります」と出ます
ダブルクォーテーションマークは全てチェックしたつもりですが…
正直、関数の内容はあまり理解出来てないのですが、そもそもB列で所得した情報にsummary-numberが含まれていない気がします
0852847
垢版 |
2019/09/22(日) 20:53:36.28
すいません、自己解決しました
C列のやり方を参考に、株価の現在値の前後の固有の単語から何文字を抜き出す、
というやり方で出来ました
教えて頂いた方、ありがとうございました
0853名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/22(日) 23:08:26.90
ヘッダーに日付を入れたいのですが、このページ数の文字を大きくする（２０くらい）
にはどう書けばよいでしょうか？

.LeftHeader = "&P/&N"
0858名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/23(月) 22:31:25.52
あるブックの「＝A1&"aaa"」と入ったセルをvba で別ブックにcopyするとaaaと転記されてしまいます
＝A1の部分も反映させるにはどうしたらいいでしょうか？
0860名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/24(火) 09:22:26.19
ＧＩ列の最終行の値を、下に向かってコピー
Ａ列の最終行と同じ行までコピーするＶＢＡをお願いします。


Ａ ＧＩ
***.....**
***.....**
***.....**
***.....
***.....
***.....
***.....
***.....

↓↓↓↓↓↓↓↓

Ａ ＧＩ
***.....**
***.....**
***.....**
***.....**
***.....**
***.....**
***.....**
***.....**
0861名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/24(火) 18:52:51.39
>>860
ＧＩ列の最終行の値を、下に向かってコピー
Ａ列の最終行と同じ行までコピーする
dim cnt
cnt =[a10000].end(xlup).row-[GI10000].end(xlup).row
if cnt>1 then

[GI10000].end(xlup).offset(1).resize(cnt).value = _
[GI10000].end(xlup).value
end if
0863名無しさん＠そうだ選挙にいこう
垢版 |
2019/09/24(火) 20:28:01.19
すでにIEのダウンロードダイアログが開いた状態で、VBAで任意のフォルダを選択して保存するにはどうしたらいいでしょうか？
■ このスレッドは過去ログ倉庫に格納されています
ニューススポーツなんでも実況