Excel総合相談所 129
■ このスレッドは過去ログ倉庫に格納されています
▼━質問テンプレ (出来れば使ってね) ━━━ 【1 OSの種類 .】 Windows** 【2 Excelのバージョン 】 Excel** 【3 VBAが使えるか .】 はい・いいえ 【4 VBAでの回答の可否】 可・否 ▼━関連スレ━━━━━━━━━━━━ 前スレ Excel総合相談所 126(実質127) https://find.2ch.net/search?q=excel Excel VBA 質問スレ https://find.2ch.net/search?q=excel+vba 【質問不可】Excel総合相談所スレの雑談・議論スレ https://find.2ch.net/search?q=excel+%E3%80%80%E7%9B%B8%E8%AB%87%E6%89%80 【1 OSの種類 .】 Win10 【2 Excelのバージョン 】 Excel 2016 【3 VBAが使えるか .】 初心者です 【4 VBAでの回答の可否】 可 40列ほどの要素がある表から下の感じでユーザーフォームで検索した結果をリストボックス、リストビューに表示するように試行錯誤しています。 絞り込み自体はうまくいってるようなのですが、無駄な空白行が大量に出てきます。 たぶん ReDim myData2(1 To lastRow, 1 To 10)と最終行まで格納してるからなのかなと思うのですが、ここをどう直せばいいのかわかりません。 '検索を実行 Private Sub CommandButton1_Click() Dim lastRow As Long Dim myData, myData2(), myno Dim i As Long, cn As Long, r As Long '検索するデータを配列 myData に格納 With Worksheets("テストデータ") myData = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp).Offset(0, 39)).Value lastRow = .Cells(Rows.Count, 1).End(xlUp).Row End With '配列 myData の中で検索で一致したデータを配列 myData2 に格納 ReDim myData2(1 To lastRow, 1 To 10) For i = LBound(myData) To UBound(myData) If myData(i, 8) Like "*" & textbox1.Value & "*" And _ myData(i, 9) Like "*" & textbox2.Value & "*" And _ myData(i, 6) Like "*" & textbox3.Value & "*" Then cn = cn + 1 myData2(cn, 1) = myData(i, 1) myData2(cn, 2) = myData(i, 8) myData2(cn, 3) = myData(i, 9) myData2(cn, 4) = myData(i, 19) myData2(cn, 5) = myData(i, 10) myData2(cn, 6) = myData(i, 14) myData2(cn, 7) = myData(i, 33) myData2(cn, 8) = myData(i, 38) myData2(cn, 9) = myData(i, 39) myData2(cn, 10) = myData(i, 6) End If Next '検索で一致したデータをリストボックスに表示 With ListBox1 .ColumnCount = 10 .ColumnWidths = "50;70;70;70;70;70;70;70;70;70" .List = myData2 End With '同じくリストビューに表示 ListView1.ListItems.Clear For r = LBound(myData2) To UBound(myData2) ListView1.ListItems.Add = myData2(r, 1) With ListView1.ListItems(r) .SubItems(1) = myData2(r, 2) .SubItems(2) = myData2(r, 3) .SubItems(3) = myData2(r, 4) .SubItems(4) = myData2(r, 5) .SubItems(5) = myData2(r, 6) .SubItems(6) = myData2(r, 7) .SubItems(7) = myData2(r, 8) .SubItems(8) = myData2(r, 9) .SubItems(9) = myData2(r, 10) End With Next End Sub みんな、すごいなぁ。 なんでそんなにExcel詳しいの? すごいなあとか他人事のように思わないで自分で調べたり試行錯誤してるから なるほど、調べないといけないですね。 頑張らないといけないですね。 このスレの質問をもとにどうすればいいか調べて、入手した知識は割りと多かったりw Vlookupの使い方について質問です VLOOKUP(検索キー, 範囲, 番号, [並べ替え済み])の4項目のうち 後ろ3つの使い方はだいたいわかったのですが、検索キーの使い方だけよく分かりません 例えばセルのA2の文字列を検索キーに指定したい場合、どのように記入すればいいのでしょうか? それともA2の文字列は手動で直接入力するしかないのでしょうか? >>404 検索キーにA2と入れればいいはずだよね? それでうまくいくと思う でも自分の場合それでうまく行ってないシートがあって格闘中 >>405 vlookupは、検索先を昇順にソートしておかなきゃダメだとか、左側の列を検索出来ないだとかその他もろもろ面倒くさいクセがある それらを分かってる人は、indexとmutchを組み合わせて使うよ >>395 1.無いんだな、これが 2.shift+F10で右クリック、値貼り付けならVキー Vの代わりにFやRでもなんか色々貼り付けできるので覚えておくといいよ >>405 小数入れてないか?小数の検索はlookupは苦手だよ indexとmatchでやる方が変なエラー無いし良いよ >>406 >>408 どうやら昇順ソートできてなかったのが問題みたい 昇順ソートするのは検索先の(テーブルなど)だけでOK? 検索先のテーブルがあるシートとVLOOKUPの式を入れるシートは分けてるけど 式を入れる方は他に優先したいソートがあって昇順ソートにしたくない インデックスとマッチってのも調べてみるね >>396 全然読んでないけど、空白の要素を削除する処理をかませるのがシンプルだろうな ただ、vbaにn番目の要素だけ削除して詰める、という機能はないのですげーめんどくさい '配列 myData の中で検索で一致したデータを配列 myData2 に格納 'myData2で空白の要素を削除し、redimを掛けて要素数を減らす '検索で一致したデータをリストボックスに表示 要素数を減らすのは個々が参考になるだろう http://www.openreference.org/articles/view/580 vlookupは昇順ソートがめんどくさすぎる 多少めんどくさくても絶対にindex+matchの方が良い >>406 >>408 ありがとうございます Excel触り始めて3日目なのでのんびりいきます >>409 vlookup使うの?検索先だけでいいよ というか式いれてる表をソートなんてすると、場合によってはカオスな事になるし、やるべきじゃない ちなみにindexとmutchを勉強するなら、根幹部分のindexからどうぞ indexを使う時、何列目かは指定するとして、何行目かを調べる為にどうするか? →mutchで調べられるじゃん!何行目かを入れる所に入れ子にしちゃお! ・・・って流れだからね ◾️index【=INDEX(範囲,上から何行目,左から何列目)】 表の中で何行目(数字)と何列目(数字、省略可だけど基本的に入れるようにした方がいい)を指定して、対象のデータを取ってくる関数 ◾️mutch【=MATCH(検索値,範囲,検索方法)】 検索値が範囲内の上から何行目(数字)にあるかを調べる関数 検索方法は、基本は0。 0=完全一致 1=最大近似値(昇順ソート必要) -1=最小近似値(降順ソート必要) やたら難しく考える人いるけど、やってる事は単純だし、そのうち検索先をソートしたくない場合も出て来た時に便利だから覚えておいて損はないよ 色々やるようになったらsumproductも面白いんだけど、これはもっとややこしい部分があるから慣れたら・・・ねw スマホから書いたら盛大に文字コード出たけど ただ区切り用の四角書いただけだから気にしないで・・・orz INDEXとMATCHの合わせ技できましたー ひとつ質問なんですが、MATCHの項目外の文字列を入力すると#N/Aと表示されますよね? (例えばカツ丼、カレー、うどん、ラーメンの中から選ばないといけない場面で、そばを入力すると#N/Aになる) 「それ以外」の項目を入力した際0を返すことはできますか? >>413 ありがとうございますー >>415 iferror(indexmatch,0) 自分もINDEXとMATCHの勉強中なんですが教えてほしいことがあります あるセルから、2つ上のセルに2と書いてあったら、元のあるセルにも2、 5つ上のセルに5と書いてあったら、元のセルにも5って感じで 参照位置と数字が一致してたら、同じ数字を書き出したいんです 簡単なようでなんだか意外と難しく・・・ どうやったら実現できるでしょうか? >>410 なるほど、「'myData2で空白の要素を削除し、redimを掛けて要素数を減らす」というのは空白要素を除いて上書きしたデータにしてredimかけるって感じなんですかね? Dim j As Long, k As Long k = LBound(myData2) For j = LBound(myData2) To UBound(myData2) If myData2(j,1) <> "" Then myData2(k,1) = myData2(j,1) myData2(k,2) = myData2(j,2) myData2(k,3) = myData2(j,3) myData2(k,4) = myData2(j,4) myData2(k,5) = myData2(j,5) myData2(k,6) = myData2(j,6) myData2(k,7) = myData2(j,7) myData2(k,8) = myData2(j,8) myData2(k,9) = myData2(j,9) myData2(k,10) = myData2(j,10) k = k + 1 End If Next ReDim Preserve myData2(1 to k - 1,1 to 10) 手元にファイルがないので試せていないですが、こんな感じですかね? >>419 そうそう 1,2,3,4,5 の配列から2を取りたいなら 1,3,4,5,5 と一個ずらして(最後の5は変化していない5) 1,3,4,5 と要素数を減らす vbaの配列関係のコードは正直読みたくないので後は頑張れ >>418 レスありがとうございます もし重複していくつも数字が条件に当てはまるなら、一番下のセルの数字を、 例えば元の位置がセルA100で、セルA95とセルA80に条件が当てはまるなら 95とだけ表示したいです >>417 >>421 出来なくもないけどかなり変な設計だよ、それ しかも際限なく作業セルも必要になるからめちゃくちゃ重くなる そもそも何がしたいの? >>409 vlookupは、>>404 で言うところの(並べ替え済み)のところにfalseを入力すれば、完全一致検索になって並べ替えする必要がなくなるはず。 今まで一致してるように見えてたのが小数誤差で不一致になっちゃうんだよな どっちが幸せかは状況による >>417 それ、そもそも2つ上のセルだとか5つ上のセルだとかは、どこで指定するの? 固定?それとも隣の行とかに入ってるの? 具体的な表とかが見たい >>421 VBAならすごく簡単にできるんだけどなあ Sub foo() x = Cells(Rows.Count, 1).End(xlUp).Row For r = x To 1 Step -1 o = Cells(r, 1) If r + o <= x Then Cells(r + o, 1) = o Exit For End If Next End Sub >>375 だけど今日確認したらエラーはNAMEじゃなくてN/Aでした これはなんでしょうか? どうしてもVLOOKUPでやってみたいんだけど基本すら上手く動いてくれない・・・ >>429 2*3ぐらいの表で試してみ ID data 1 a 2 b 答えは色々予測できる 恐らく文字列と数値が違うか、書式設定に影響されている >>430-431 ありがとう 参照できてないんだ・・・ 小さい表を作ってやってみたら成功したので 普段処理してる大きめの表に同じように数式入れたらエラーになった とはいえ実物を貼れないのでこんなこと聞かれても困りますよね 書式設定は怪しいのでまた確認してみます ダメなら上で上がってるINDEXとか勉強してみる 例えば 1/1 と表示されてるのを検索しようとして =VLOOKUP("1/1",B1:C2,1) はダメだぞ 日付自国に関してはシリアル値という概念が必要 しかも少数誤差という概念も必要になる場合もある 【1 OSの種類 .】 Windows7 【2 Excelのバージョン 】 Excel2010 【3 VBAが使えるか .】 Yes 【4 VBAでの回答の可否】 Yes アドインのオンオフ(有効無効)をマクロで操作できますでしょうか 具体的にはRelaxToolsのオンオフがしたいのです 手動じゃなくマクロで 走っているときに容量がでかいデータをピボットテーブルで複雑な操作をすると かならずクラッシュするのです >>434 分からないが まずは、アドインを事前にオフにしておかないとアドインは必ず読み込まれる オーバーロードというfuntionの上書きはあるが、vbaにこの機能はない つまり ・マクロ実行 ・on error resume next ・アドインのfunctionをダミーで作る ・アドイン読み込み ・on error goto 0 こういう順序ならアドインをエラーでスルーできるかもしれない とにかくエラー処理で分岐させることができれば、うまくいくんじゃないかなぁ 2×2×3通り計12通りのパターンで3変数関数を計算するんだけど、これってvbaだと良い感じにできるの? その12通りのパターンはシートにすでに入力済。 エクセルだとif せる1が〜なら〜みたいにだらだらやることになる。 最初の枝と2つ目の枝は、シートで指定して、それに合わせて3通りの計算結果を返してほしいんだけど >>435 VBAで操作できるんですね!わーい というわけで気合を入れてググったらありましたわAddInオブジェクト ? addins.Count 6 ? addins(1).Name Relaxtools.xlam addins(1).installed=False addins(1).installed=true あざーっす >>420 >>396 の質問の件ですけど、redimは多次元の場合最後の次元?しか変えられないみたいでした。なので今回1次元目の要素を変えたいのでこのままじゃ無理そうです… >>440 transposeで入れ替え 空白を無視した上書き処理 redimかけて要素数を調整 transposeで入れ替えて元に戻す ということですか? 【1 OSの種類 .】 Windows7 【2 Excelのバージョン 】 Excel2010 【3 VBAが使えるか .】 いいえ 【4 VBAでの回答の可否】 否 A列に種数字、B1に処理用数字を入れて、C列で計算結果を出すシートなんですが (Aは60行あり、全てのAにB1ひとつを掛け合わせます) 必要な結果は、AがB1以下ならば、A=B1なら8、B1-1なら4、B1-2なら2、B1-3なら1、B1-4なら0.5、B1-5以下は0と言う結果が必要、つまり =2^(A-B+3)で、 逆にAがBを超えるなら、B+1なら16、B+2なら24、B+3なら32、B+4なら40・・・で青天井の結果が必要、つまり =(A-B)*8 これを、ifで切り替え C1=IF(A1<=B1,if(2^(A1-B$1+3)>.05,0,2^(A1-B$1+3)),(A1-B$1)*8) ここで、2^(A1-B$1+3)が2回出るので、2^(A1-B$1+3)を作業列Dに入れて、 C1=IF(A1<=B$1,if(D1>0.5,0,D1),(A1-B$1)*8) D1=2^(A1-B$1+3) としたんですが AとBの差分が少ないうちはいいのですが、差分が大きくなると、 Dが3.34846439745709E+229といった膨大すぎる数字を吐き出します (上の数字の場合差分992程度でこれだけの天文学的数字を1セルが吐く ただしC列はちゃんと正しい数字を返してくる。上の例なら7936 最悪60行全部がその数字になる) Dは非表示列にしてるとはいえ、数字が膨大すぎて精神衛生上非常に悪いのですが これは、計算結果や他のセルに実は悪影響があったり、シートの動作が重くなったり (Windows7+7時代の事務用PC+Excel2010は最初から動作がかなりもっさりしているので、 余計重くならないか心配)するものなのでしょうか? >>442 そもそも > B1-5以下は0と言う結果が必要 と言うなら > if(2^(A1-B$1+3)>.05,0,2^(A1-B$1+3)) なんて書かずに素直に if(A1<=B$1-5,0,2^(A1-B$1+3)) でいいように思う それも含めて俺なら C1=8* If(B$1<A1,A1-B$1, IF(B$1-5<A1,2^(A1-B$1), 0)) って書くかな 【1 OSの種類 .】 WindowsXP 【2 Excelのバージョン 】 Excel97 【3 VBAが使えるか .】 いいえ 【4 VBAでの回答の可否】 否 入力規則でのリストのドロップダウンメニューですけど VBAなしで多段階ドロップダウンメニューってできませんかね? WEBブラウザのフォルダ構造ブックマークみたいな、まず大分類をマウスでポイントするとサブメニューが開いて小分類から選べるようになるみたいな こんなかんじ >ダッチワイフ >バイブ >TENGAミカヅキ ←これ >ミカヅキ >ハイパーオルガマックス >ポルノホスピタル >ローター >オナホール >SM >>445 範囲を名前の定義で指定すれば可能 選んだものによって範囲が可変になるようにすればいい >>446 それが出来るのはExcel2000からだよ しかもそれじゃ質問者の望む「まず大分類をマウスで*ポイント*するとサブメニューが開いて小分類から選べる」リストにはならない >>445 普通にできるでしょ IFを多用することになるけど 範囲の可変だってOFFSET等で対応できる >>449 セルの範囲:INDIRECT(名前) に対応したのは2000からで、質問者のExcelは97な >>448-449 それで出来るのは、「A列のリストを*クリック*および*選択*して、B列のサブメニューを開く」までだよ 質問者の望む「WEBブラウザのような」「*ポイント*でサブメニューが出る」ではない >>451 出来るでしょ 97じゃダメみたいだけど 就職内定先でExcel2013のマクロを作成したりするので勉強しておいてと言われたのですが、Excelのバージョンで作成方法違いますか? またオススメの本などありますか? 井川はるきのそこが知りたいExcelVBAプロの技 ナツメ社が、おすすめですよ。 20年前からほとんど変わらない言語なのでバージョンはあまり気にしなくても大丈夫です。 Windows7 【2 Excelのバージョン 】 Excel2013,2010 【3 VBAが使えるか .】 はい 【4 VBAでの回答の可否】 可 1)同じフォルダにあるブックをリンク 2)違う場所にフォルダを移動 3)絶対パスになっている 相対パスにするにはどうすればいいか。 運用として、フォルダの絶対パス自体は良く変わる。 参照してるブックは常に同じフォルダ内にある というルールは徹底するんだけど、人に渡すからフォルダパスは変わる。 複数のブックを参照する予定。 よくわかんないけどどこかのセルにパスを入力して、 indirectで参照することは可能?そういう運用が良いのかなあ。 >>454 ブックオフだとVBA本は108円で意外と売ってる 基本的なことは殆ど変わらないから古本で大丈夫 まずエクセル持ってるなら入門書でも買って触ることが大事だと思う >>457 ありがとございます。Excel2003とかでも変わらないですかね? エクセル2000ですが 行列を入れ替えて値の貼付け をショートカットで出来ますか? >>460 VBA使ってもいいならonkey使って割り当てればなんとでもなるが‥ >>456 はレスもらえないほど的外れだったり、微妙な内容だったりします? >>461 そのブックごとにプログラム入れてやる必要があるってことであればダメなんです。 このPCで開くすべてのエクセルで使える方法を探してます。 ctrl+○○でできればその方法を、なければ(クイックアクセスツールバーのすべてのコマンドにもなさそうなので)クイックアクセスツールバーにコマンドを自作して登録する方法があればそれを教えていただけないでしょうか >>462 そんな使い方しないから調べたことがない たぶんExcel4.0使えばできそうだが >>463 全てのブックにマクロ仕込む必要はないよ? マクロ仕込んだエクセルをバックで開いたままにするだけ Application.onkey “^F1 “,”test” Ctrl+F1でtestプロシージャを実行 その中でご希望の処理をすれば良い >>465 実用性を考えるならアドイン化してエクセル起動したら自動で立ち上がるようにしておけば、機能が増えたかのようになると思う >>465 なるほど!それは考えつきませんでした。でも >>466 さんのアドイン化が希望に添いそうです。アドイン化ちょっと勉強してみます。ありがとうございました。 >>464 相対パスについて詳しい人いないかなあ。 4.0とはなんぞ >>456 >>464 のそもそもそんな使い方しない、には完全同意 まぁ暇なんで試してみた ・同じフォルダにブック2つ作成 ・ブック1のA1=ブック2のA1 この時点では相対パス ブック2を閉じると既に絶対パスになっている >>469 そんな使い方しないか。 フォルダを移動ではなく、ファイルを閉じる、でも絶対パスになるのか。なるほど。 他ブックを参照するってことはやめて、それをコピペしてくるマクロを作るって方針に変えます。 >>406 え?VLOOKUPで検索先を昇順ソートなんてしたことないよ 具体的にどういう場合に必要? >>471 第四引数が1かtrueの場合 近似値計算、まぁ源泉所得税の計算などで使う時 第四引数が0なら不要 >>472 あ、そういうことなのね 普段使わないけど覚えておくわ ありがと 【1 OSの種類 .】 Windows10 【2 Excelのバージョン 】 Excel2013 【3 VBAが使えるか .】 はい(基礎とコピペ程度) 【4 VBAでの回答の可否】 可 概要 エクセルで日付で2017年10月以降はA以前はBとしたい。 詳細 エクセルで1号機から10号機まであり1号機の機器を更新し2017年9月はB 2017年10月以降はAとしたいのですが一番いい方法はないでしょうか? >>474 IF関数で基準にしたい月の一日目のシリアル値以上か未満かで振り分けるのが一番簡単じゃ? 検索データが年月になってるなら、VALUE関数でシリアル値化しちゃえばいいわけだし 見た目わかりやすいのは日付のままだから ifで2017/9/30までならB、そうでなければAとすればいいんでは? まだ試してないけど日付でIF関数で<括りの計算できる? 【1 OSの種類 .】 Windows10 【2 Excelのバージョン 】 Excel2016 【3 VBAが使えるか .】 はい(すこし) 【4 VBAでの回答の可否】 否 今日が2017/10/2とする A1〜10は10/1 A11以降が10/2 日付と品番は最多で追加されても5000行(仮に) A列に日付 B列に品番 C列に品番重複を調べる式 C11=IF(COUNTIF($B$11:$B$5000,B2)>1,"重複あり","重複なし") D列に重複回数を調べる式 D11=COUNIF($B$11:B11,B11) 毎日A列とB列に日付と品番が追加されていきます。 調べたいのは今日の中での品番重複です。 そのため毎日C列とD列の重複を調べる始まりの列数(第一引数)を手動で直しています。 しかしここをTODAYなどを使って自動化したいです。 ちなみに品番追加はどんどん追加されるのでその日に何列増えるかはわかりません。 よろしくお願いいたします。 >>480 C11=IF(COUNTIF($B$11:$B$5000,B11)>1,"重複あり","重複なし") 数式の間違い直しました できたらcountifsの第2条件まで書いていただけると助かります。 重複ありなし表示はどうすればいいですか? なぜ重複回数だけでなく、重複ありなし表示をさせているかというと、重複回数だけだと1回のみカウントの1、と、重複カウントの1、が見分けられないからです。 E列に作業列増やすしかないかな。。 C1=contifs(a:a,today(),b:b,b1) 2013持ってないから試してないけど、だいたいこれで行けると思う 無理だったらまた教えて >>484 わかった どうもありがとう 後できちんと試してみる 現在エクセルで32Mあるファイルを使っています。 自動計算、自動取得で動かしていますが、重いです。 最近ゲーミングPCが流行っていると聞きましたが、 そのくらいハイスペックなPCを使えば長時間使ってもサクサク動きますか? >>483 >なぜ重複回数だけでなく、重複ありなし表示をさせているかというと、重複回数だけだと1回のみカウントの1、と、重複カウントの1、が見分けられないからです。 >>480 >A列に日付 >B列に品番 >C列に品番重複を調べる式 >C11=IF(COUNTIF($B$11:$B$5000,B2)>1,"重複あり","重複なし") >D列に重複回数を調べる式 >D11=COUNIF($B$11:B11,B11) ちょっとお節介的に気になったんだけど、これって 『重複品番全部(1カウント目を含む)』と『重複回数』両方の情報が必要って事? いや、もし重複してる分を取り除くとかの目的なら1カウント目の情報は要らないなって思ってさ >>486 そんなに簡単な話じゃない 今使ってるPCのスペックは? >>491 伊東四朗と笑うセールスマンぐらいの違い >>492 じゃたいしてかわらないじゃないか(笑) win7 excel2010で 同じブック内で、並べて表示するショートカットを教えて >>487 1カウント目も必要なのです。 1回目に出てきた品番と2回目以降に出てきた品番、それぞれに付随する属性を比較しなければならないのでどちらも必要という訳です。 重複削除が目的ではないのですね。 ■ このスレッドは過去ログ倉庫に格納されています
read.cgi ver 07.5.5 2024/06/08 Walang Kapalit ★ | Donguri System Team 5ちゃんねる