X

Excel総合相談所 126 [無断転載禁止]©2ch.net

■ このスレッドは過去ログ倉庫に格納されています
0001名無しさん＠そうだ選挙にいこう
垢版 |
2017/03/25(土) 19:50:44.73
▼━質問テンプレ (出来れば使ってね) ━━━
【1 OSの種類 　 　 　 　 .】 Windows**
【2 Excelのバージョン 　 】 Excel**
【3 VBAが使えるか　 　 .】 はい･いいえ
【4 VBAでの回答の可否】 可･否

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

前スレ
Excel総合相談所
http://sirtuin.me/index.php?word=Excel%E7%B7%8F%E5%90%88%E7%9B%B8%E8%AB%87%E6%89%80

Excel VBA 質問スレ
http://sirtuin.me/index.php?word=Excel%20VBA%20%E8%B3%AA%E5%95%8F%E3%82%B9%E3%83%AC%20

【質問不可】Excel総合相談所スレの雑談・議論スレ
http://sirtuin.me/index.php?word=Excel%20%E9%9B%91%E8%AB%87
0699名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/23(火) 23:29:37.10
こちらの理解が及んでいないのだと思いますが、行数を動的にしなければならず、全部のセルに数式を入れる〜という場合の対処法がよくわかりません
0700名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/23(火) 23:33:30.03
>>698
うーん・・・説明がめんどくさくなってきた、スマン

とりあえずまぁ、正解は無数にある
結果として早く正確に動けばいいので、手探りでいいから一度作って見ると良い
今まで10分かかる仕事が30秒になれば御の字だろう
これを20秒とか5秒とかまで切り詰める必要はないと思う。俺はやるけどね

vba高速化のセット置いとくんで、できる方法で頑張ってみ
http://15g.jp/post-314/
0702名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/23(火) 23:44:42.79
>>700
そうですか、すみませんでした…
何度も回答いただいてありがとうございました

それこそ現在30秒ほどなのですが、これが最良とは思わないので、更に高速化させたいとテコ入れしているところです
リンク先読んでみます、ありがとうございます
0707名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/24(水) 02:02:08.49
【1 OSの種類 　 　 　 　 .】 Windows2000
【2 Excelのバージョン 　 】 Excel97
【3 VBAが使えるか　 　 .】 いいえ
【4 VBAでの回答の可否】 否
=if(and(A3="",A4="",A5="",…,A22="",B3="",B4="",B5="",…,B22=""),"入力が一切ありません","OK")
（「…」はAやBの6〜21です）
というチェック用のifを簡単な式にする方法は無いでしょうか？
関数でand(XXXX(A3:A22)="",XXXX(B3:B22))や、XXXX(A3:A22,B3:B22)=""的に纏められると嬉しいのですが
0711名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/24(水) 02:16:28.94
気になっちゃったから色々テスト（10万セルにvlookup相当の結果を入れるコードを計測）してみたら意外な結果だった

dictionary > セル範囲に一括で数式 >>> vba内でifとloop > 数式を入れたセルをオートフィル >>>>> vba内でworksheetfunction

もちろんセルへのアクセス回数は最小限にしたりしてどのパターンも最適になるように頑張った結果がこれ
セルにformulaを放り込むのも意外といけると思った
0715名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/24(水) 15:32:52.76
>>687
ありがとうございました。
試してみたところ件名や本文の半角スペースより後ろの部分が表示されるようなりました。
中国語が文字化けするの自分もggrksしてみましたがこれではないでしょうか。
https://oshiete.goo.ne.jp/qa/5977178.html
ここの下記の部分が文字化けを防ぐ対策ではないかと思うのですが組み込むことできますでしょうか。

　If Flg Then
　　　　'thunderbirdだと文字化けしたのでUTFエンコード
　　　　With CreateObject("ScriptControl")
　　　　　　.Language = "JScript"
　　　　　　Arg = .CodeObject.encodeURI(Arg)
　　　　End With
　　Else
　　　　'簡易的にSJISエンコード
　　　　b = StrConv(Arg, vbFromUnicode)
　　　　Arg = ""
　　　　For i = 0 To UBound(b)
　　　　　　Arg = Arg & "%" & Right$("0" & Hex$(b(i)), 2)
　　　　Next
　　End If
0716名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/24(水) 19:44:51.75
>>715
687じゃないし試してもないけど
そこに貼ったコードは前半は687の方法、
後半のsjisはそもそもsjisに簡体字がないんだから問題外だとおもうんだよなあ
0717687
垢版 |
2017/05/24(水) 22:09:01.97
>>715
どうも　Beckyちゃんて BOM付けないとダメみたいね
これでどうでしょうか?(本文だけで Subjectは無理です)
Win10+Excel2016+Becky2.73 で確認したら OKだった

これで無理ならどうにも ならんかもね

>>687のコードを修正

"&body=" & URLEncode(Range("B7").Value)
↓
"&body=%FF%FE" & URLEncode(Range("B7").Value)
に修正

※Beckyちゃんの言語設定で
"送信メールは全てUTF-8(多言語混在可)で扱う"
をチェック ON してください(しないと文字化けします)

PS.
BOMのエンディアンは適当です(どっちでもBeckyちゃんはOKだったので)
0718名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/24(水) 22:26:18.69
【1 OSの種類 　 　 　 　 .】 Windows2000
【2 Excelのバージョン 　 】 2010
【3 VBAが使えるか　 　 .】 いいえ
【4 VBAでの回答の可否】 否

A列に人名が並んでいます(重複あり)
ここから名簿を作ろうと思います。

A列の名前データは定期的に変わります。
元データの総数は1万〜2万件で名簿リストは想定500〜1,000人です。
B列には初登場となる名前のインデックス用の番号を1、2、3…と振り、
最終的には別シートでindex matchで空白行を詰めた名簿にする予定です。
他のワークシートとのデータ連携の兼ね合いもあるためピボットではなく関数で自動化できないでしょうか。
0719名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/24(水) 22:32:16.45
>>714
聞いてくれて良かったわ　結果を入れる行数を変えてやってみたら
行数によってだいぶ違う傾向が出た
左から順に100行、1000行、10000行、100000行、1000000行
vlookupの第2引数部分の大きさは全て50×2、最後は全て値にして終わらしてる

オートフィル　0.0546875秒　0.078125秒　0.21875秒　1.984375秒　29.92969秒
数式一括　0.03125秒　0.046875秒　0.203125秒　1.6875秒　26.14063秒
WorksheetFunction　0.015625秒　0.0390625秒　0.28125秒　2.773438秒　27.75781秒
Dictionary　0.0234375秒　0.0390625秒　0.171875秒　1.625秒　16.09375秒
loop探索　0.015625秒　0.03125秒　0.203125秒　1.96875秒　19.19531秒
0721718
垢版 |
2017/05/24(水) 22:39:03.17
>>720
全部真っ白です(>Д<)
0725名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/24(水) 23:34:44.40
2ちゃんだし仕方ないけど、説明めんどくさいと言われたネタを元に時間計測をそこまで詳細にされるという…
人のふんどし、ってのでもないけど、なんとも言えない感じだ
要はvlookupのFor nextなんざありふれてて目もくれないってことだ…
0726718
垢版 |
2017/05/24(水) 23:37:44.42
>>723
できました。ありがとうございます。
>>724
一回一回やるその作業が手間なので自動化したかったんですよ
0727名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/25(木) 00:01:47.83
>>725
むしろワークシート関数も結構使えるってことがわかって良かった
数式一括のやつはこんなコードで計測したけど
範囲が広くなったときに時間が延びてるのは主に.Value = .Value のとこっぽいから
そこを割り引けば俺様愛用のDictionaryにほとんど引けをとってない
状況に応じてってことだな

Dim t As Single
Const endRow As Long = 1000000
t = Timer
Range("B1").Resize(endRow).Formula = "=VLOOKUP(A1,$G$1:$H$50,2,FALSE)"
Range("B1").Resize(endRow).Value = Range("B1").Resize(endRow).Value
Debug.Print "数式一括", (Timer - t) & "秒"
0730名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/25(木) 06:11:19.81
>>728
はいよ再計算と描画の抑止と再開で挟んで1000000行の奴で2回やった

オートフィル　21.25977秒　17.83789秒
数式一括　42.61133秒　57.04297秒
WorksheetFunction　26.33594秒　28.00977秒
Dictionary　14.58789秒　16.20313秒
loop探索　17.76563秒　19.36523秒

下3つはそもそも再計算関係ないし値も100万セルにまとめて出力してるから
719の時とほとんど変わらない
上2つは逆に値にする前に再計算させないと意味がないわけだけど
（初心者の頃これでたまに失敗してた）
1番目のが大幅短縮したのはは描画停止の効果がでかいんだと思う
2番目のやつはいきなり計算させられて驚いた感じなのかな
0735名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/25(木) 19:24:53.80
>>717
ありがとうございます。
中国語の本文だけでなく件名も文字化けしなくなりました。
件名の部分は変更されてないようですので
件名が文字化けしたのは勘違いだったかもしれません。
0739名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/26(金) 01:07:31.81
VBA 可(というか望ましい)
excel2010


要素aの番号と要素bの番号が対応しているとき、
要素bの番号だけ変わった際に、元もと対応していた要素aの番号を引っ張ってくるにはどうすればいいんでしょう

a1=1 b1=5
a2=2 b2=10
a3=3 b3=6
で、bが変わって
b1=6
b2=5
b3=10
となったとき、aの配列を並び替えたいんです
0740名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/26(金) 02:41:49.35
やりたい事がいまいちわからんのだが
元々の対応を　どっかに対応表として保存しとく(例えば別シートとか)ってのが定番だと思うが
それじゃダメなん?
0741名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/26(金) 04:16:15.80
>>739
VLOOKUP関数だけで出来るよ

Sheet2に対応表作成
a1=1 b1=5　
a2=2 b2=10
a3=3 b3=6
D1:=A1　下にコピー

Sheet1
A1:=VLOOKUP(B1,Sheet2!$B$1:$D$3,3,0)　下にコピー

B列の値（5,6,10）に対応した値がA列に表示される
0745名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/26(金) 21:59:37.61
【1 OSの種類 　 　 　 　 .】 Windows7
【2 Excelのバージョン 　 】 2010
【3 VBAが使えるか　 　 .】 いいえ
【4 VBAでの回答の可否】 否

A列に動物名のリスト(数不定)があって、B1〜B4にはデータの入力規則(A列のリスト)を適用して動物を選べるようにします。
この際、プルダウンのリストを被らない候補のみ出るようにしたいのですがどうすればできますか？
0748名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/27(土) 02:24:39.25
a列に入ってる項目の、最後の1文字を取ってb列に格納したいんだけどどうやるんでしょうか

最後の1文字は決まって「点」なんです
0750名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/27(土) 03:25:41.06
>>748
B1
=LEFT(A1,LEN(A1)-1)
0752名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/27(土) 10:42:49.77
【1 OSの種類 　 　 　 　 .】 Windows10
【2 Excelのバージョン 　 】 2010
【3 VBAが使えるか　 　 .】 いいえ
【4 VBAでの回答の可否】 可(否の方法があれば優先してお願いします)

例えばセルに"20"を入力した場合
その入力されたセルに"20(28)"のように表示させることが出来るなら方法を教えてください
(28)に関しては「入力された値+8」で8は固定です
0753名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/27(土) 10:51:12.09
>>752
関数や条件付き書式では無理

簡単なのは別セルにする方法
A1=5
B1=a1&"("&a1+8&")"

同じセルが必須ならvbaしかない。該当シートに記述すること
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Row = 3 And Target.Column = 2 Then
Application.EnableEvents = False
Cells(3, 2) = Cells(3, 2) & "(" & Cells(3, 2) + 8 & ")"
Application.EnableEvents = True
End If
End Sub
0755名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/27(土) 16:31:42.45
【1 OSの種類 　 　 　 　 .】 Windows10
【2 Excelのバージョン 　 】 2010
【3 VBAが使えるか　 　 .】 いいえ
【4 VBAでの回答の可否】
1行に70〜100セル程度にわたり数値が空白なしに横方向に羅列されてて
右に数値を追加していってるのですが最新50回分をグラフ表示するようにしたいと思っています。

数字を追加するたびにいちいちグラフの方の範囲を書き換えるのは面倒なので、その行の右側の50セル分の範囲を指定できるようにするにはどうすればいいですか？

LOOKUP(10^10,A1:CZ1)で一番右の数値一個を取ることはできるのですが
0756名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/27(土) 17:29:30.19
【1 OSの種類 　 　 　 　 .】 Windows10
【2 Excelのバージョン 　 】 2013
【3 VBAが使えるか　 　 .】 いいえ
【4 VBAでの回答の可否】 否


今まで範囲指定した部分を160%に拡大してA4に印刷していたのですが、
Windows 10 Creators Update後に同じ作業をしたところ拡大率がおかしいです。
100%で9cmくらいの範囲が160%で9.5cmくらい、試しに400%にしたところ11cmくらいにしかなりません。
今までは160%ならば1.6倍に印刷出来ていたのですが・・・
0757名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/27(土) 17:31:41.70
>>755
まずそれ、横→縦にして方がいいね
新しい数字は下に追加していく
そしたらいらない古いところはオートフィルタでチェックを外してくだけ
0758名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/27(土) 17:34:30.49
>>755
オレだったら作業セルに最新50の数値をリンクさせそこをグラフ範囲にする

最新50のデータを作業セルにリンクさせるには、最終列番号を取得すればいい
例えば、１行の最終列番号（最新データの列番号）は、=MATCH(0,1:1, -1)
0760名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/27(土) 17:50:15.54
>>756
エクセルの印刷には期待するな
倍率とかはテキトーに調整して、うまくいったら次からそれで

もしかすると新しいブックで設定し直すと、色々と変わるかもしれない
0761名無しさん＠そうだ選挙にいこう (ﾜｯﾁｮｲ)
垢版 |
2017/05/27(土) 18:30:50.880
>>759
レスどうもです。戻すしかないかあ
ちなみにXP機では今まで通り印刷できました。
>>760
レスどうもです。新しいブックでも他のファイルでも同じでした。400%でも小さすぎて・・・
プレビュー表示でも同じなんです。
0763名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/27(土) 19:03:34.06
>>761
記事のURL出てこないんで示せないけど
MSも手動のupdateは推奨してなかったと思うぜ
ハードウェアまたはソフトウェアの互換性チェックがバイパスされるとかで
0765756
垢版 |
2017/05/27(土) 19:40:40.52
>>763
今戻しました。
業務時間に降ってくると困るので暇な土曜日に手動でと思ったんですが甘かったです。
今、範囲指定＆拡大印刷試してみたところ問題なしです。まったくなんなんでしょう
本番で不具合ないこと祈ります。
>>764
WIN10でもCreators Update後でだけです。
0768名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/28(日) 00:27:52.27
【1 OSの種類 　 　 　 　 .】 Windows7
【2 Excelのバージョン 　 】 Excel2010
【3 VBAが使えるか　 　 .】 はい
【4 VBAでの回答の可否】 否

indirectで困ってます
a1セルでシート名を入力
b1セルでそのシート内で検索したい言葉を入力
c1セルにその言葉が何列目にあるかを計算させる
=match(b1,indirect(a1&"!5:5"),0)
→正しい計算結果

これを2列めへコピーして、c2セルにペースト
→REF

indirectを使わずシート名で直接入力
→正しい計算結果

なぜでしょうか？ちょっと困惑しています
0769名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/28(日) 00:39:11.71
>>768
Q1. C2セルの数式は
　　どうなってる? それは　想定してるセル参照になってるの?

Q2. 想定してるセル参照になってるとして　そのセルの値は想定してる値になってる?
0770名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/28(日) 00:45:34.45
>>769
今は自宅なので思い出して書いちゃってます10分程度格闘したのであっていると思います
c2セルの数式は
match(b2,indirect(a2&"!5:5),0)です

Q2
参照および内容は、あっていると思います。
具体的にやったことを言いますと、
シート名は、シートのタブをクリックしてコピーペーストしたもので
項目名は、その検索するシートから当該項目をコピーペーストして入力したものです

ちなみに、a2,b2にa1,b1と同じものを入力すると計算は正しく行われます
0771名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/28(日) 00:53:58.58
>>770
=MATCH(B2,INDIRECT(A2&"!5:5"),0)
として #REF! になる可能性があるのは　存在しないシート名をA2に指定した
だと思うが　そこ確認した?

"Sheet1" なのに　"Sheet1 " とか　" Sheet1"　とかも×よ
0773名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/28(日) 01:24:27.65
>>771
1)シートのタブをクリックして、シート名をコピーペースト
2)手打ち
両方試みたんですがREFでしたね

スペースなかったと思いますが確認してみます
アンダーバー _があるとダメなのかなあ
0775名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/28(日) 08:47:53.97
>>768
indirectで別シートを参照する時は、
・別ブックの参照を自動的に取得する
ブック二枚同時に開き、
a1に「=」だけ入力して参照先のセルをクリック
こんなのが自動的に入力される
='C:\Users\a\Documents\[Book2.xlsx]Sheet1'!$A$1
indirectに入れる
=indirect("'C:\Users\a\Documents\[Book2.xlsx]Sheet1'!$A$1",1)
この順番で作業をすれば別ブック、シートの指定で間違うことはない
0777名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/28(日) 12:02:06.38
>>776
あぁ、わかった
シート名が「1-3」とかだろ
A列ではそれが計算されて「-2」になっている
1-3!A1
↓
-2!A1
これを避けたければA列、ついでにB1も文字列にしておくと良い
0778名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/28(日) 14:38:17.59
【1 OSの種類 　 　 　 　 .】 Windows10
【2 Excelのバージョン 　 】 Excel2013
【3 VBAが使えるか　 　 .】 はい　（超初心者）
【4 VBAでの回答の可否】 可

ピポットテーブルを使っているのですが絞り込み昨日を使うとA列とB列の幅が
絞り込み機能と表が連動してしまい表が不格好になってしまいます。
絞り込み機能の幅と表の幅をべつにすることはできませんか？
0781名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/28(日) 17:55:21.98
質問です、excel2016でデータタブにあった外部データの読み込みが表示されなくなってしまっているのですが 再表示の方法わかりますでしょうか？
0785名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/29(月) 08:49:07.25
>>777
まさにそういうつけ方をしていました
-はシート名に使わない、をルールにしようと思います
回避策はダブルクォーテーションで囲うことですが、ルール化しておこうと思おます
きちんとそのまま書くべきでした
0787名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/29(月) 23:38:56.50
よく、プログラムの出力結果csvをエクセルに取り込んで計算をするというか流れがあるけど、
その出力をindexとか使って参照するのか、手入力しちゃった方が早いのかどっちがいいのか悩む
参照があってるか確認しながら都度やるのもまた手間なのかなあと
0788名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/29(月) 23:51:36.19
その辺は慣れだなぁ
俺はたまに正規化する
　　　　　　　　　　4月　5月
A支店　売上　　1　　　2
A支店　経費　　3　　　4
B支店　売上　　5　　　6
B支店　経費　　7　　　8

↓
A支店　売上　４月　1　A支店_売上_４月
A支店　売上　5月　2　A支店_売上_5月
A支店　経費　４月　3　A支店_経費_４月
A支店　経費　5月　4　A支店_経費_5月
B支店　売上　4月　5　B支店_売上_４月
B支店　売上　5月　6　B支店_売上_5月
B支店　経費　4月　7　B支店_経費_４月
B支店　経費　5月　8　B支店_経費_5月

一回この正規化し直すマクロ組めば、index+matchで楽にどんなデータでも取得できる
正規化と言えないというツッコミはやめて下さい死んでしまいます
0789名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/30(火) 00:08:06.38
出力形式が変わらないなら計算式の入ったテンプレを作成しておいてcsvだけ入れ替えるのがいいと思う
0790名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/30(火) 00:37:53.55
>>789
いやそれがケースバイケースでおおむね一致しているけど例外ってパターンが多い
例外ってどんなのよ、っていうと
例えば１列目をピックアップするってルールだけど、あるシートでは２列目にしなきゃとかそんなの
0791名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/30(火) 00:43:07.12
>>790
マスターとなる大データを入れてそこからindex/matchで引っこ抜けるんジャマイカ？
0794名無しさん＠そうだ選挙にいこう
垢版 |
2017/05/30(火) 08:15:04.66
CSVファイルをExcelで開いたときに、勝手に値が変わっちゃうことがあって、気づかずに保存しちゃう人がいるんだけど、これってなんとかならないんですかねぇ…
いちいちPC音痴の人に説明するのがめんどくさい
そもそも誰が得する機能なんだろう
■ このスレッドは過去ログ倉庫に格納されています
ニューススポーツなんでも実況