Excel総合相談所 133
■ このスレッドは過去ログ倉庫に格納されています
▼━質問テンプレ (出来れば使ってね) ━━━ 【1 OSの種類 .】 Windows** 【2 Excelのバージョン 】 Excel** 【3 VBAが使えるか .】 はい・いいえ 【4 VBAでの回答の可否】 可・否 ▼━関連スレ━━━━━━━━━━━━ 前スレ Excel総合相談所 132 https://mevius.5ch.net/test/read.cgi/bsoft/1530415352/ Excel VBA 質問スレ Part57 https://mevius.5ch.net/test/read.cgi/tech/1536583359/ 【質問不可】Excel総合相談所スレの雑談・議論スレ4 https://mevius.5ch.net/test/read.cgi/bsoft/1489108851/ ▼━質問時の注意・決まり事━━━━━━━━━━━━━━━━━━━━ ・1 現行スレ内で既出の質問が無いか、ページ内検索をしよう。(Ctrl+F) ・2 図やコードを書く場合、TABや連続する半角スペースは無視されるので注意。 ・3 VBAコードを貼る場合は、部分的に伏せたり省略したりせずに全て貼りましょう。何レス使っても構いません。 ・4 2回目以降の質問では名前欄に初回質問の番号を入れよう。 ・5 回答内容でわからない部分があっても、すぐに聞き返さずにヘルプやGoogleなどで検索しましょう。 ・6 バグ・動作上の不都合・仕様に関する質問などはマイクロソフトのサポートを受けましょう。 ・7 VBAはプログラム言語のVisualBasic(6以前)に近い処理が可能で Excelに関係ないことも出来ます。 Excelの操作に関係ない部分は、スレ違いなのでこのスレでは回答が得られにくいです。 ここで聞くよりもVBスレやAPIスレなどの該当スレで質問しましょう。 但し向こうはプログラマのスレなので、構文規則などの最低限の事は覚えてから質問しましょう。 ここみたいに丸投げはダメですよ。 ・8 うまくいかなかったにしても自分でやってみたこと(組んだ数式やコード)は書きましょう。 例えエラーになる式やコードでも、何をやりたいのかを的確に把握する手がかりになります。 その上で、どううまくいかないのかを具体的に書きましょう。 エラーが出るなら、何処でどういうエラーが出るのか、 想定外の結果が出るなら、条件と想定上の結果、実際の結果などを詳しく書いてください。 ・9 マルチは嫌う人が多いのでなるべく避けましょう。マルチをすると、逆に回答は得られにくくなると思ってください。 ・A テキストボックス(エディトボックス)、コンボボックス(ドロップダウンリスト)、リストボックス、コマンドボタン、 チェックボックス、オプションボタン(ラジオボタン)、スピンボタン、スクロールバー、等の質問をするときは、 ユーザーフォーム、コントロールオブジェクト、フォームオブジェクトのどれなのかを必ず書くこと。 Mateだとfind.5chはエラーになるので、直リンクにしたが、不味ければ次は戻してください そうなんか、すまんかった それなら自家林の方がいいんかね 【1 OSの種類 .】 Windows10 【2 Excelのバージョン 】 Excel2016 A B 1 4 2 2 3 0 3 4 5 8 6 のような表を折れ線グラフにしてAとBの変動をみたいのですが、折れ線グラフにすると入力してない箇所(上記の3-4間のように)で線が切れ、グラフが途切れ途切れになってしまいます。 2-5間に線を繋げることはできないのでしょうか? >>8 ズレたので訂正 ㅤA B 1 4 2 2 3 0 3 4 5 8 6 2列目がA、3列目がBです。 >>8 折れ線グラフを途切れさせない Https://hamachan.info/win8/excel/plot.html バージョン違い(2016と2015)のエクセルシートを共有するとやっぱり不具合はでるのでしょうか? 上位から下位へはエクスポートで解決できるようですが、下位から上位とかも可能なんでしょうか? 2015がわかりませんが2013なら フォント・色、フォントに依存して行列の幅。あたりの見た目は2016以降と2013以前でがっつり変わります 2013で作ったファイルを2016で開くと、MSPゴシックが遊ゴシックに変わってレイアウトぶっ壊れ 新機能や特殊な処理してなければ、共有するだけであれば機能的な不備はあまりないかと思います >>13 フィルターをクリックしても動かなかったり、セルを選択して書き直そうとしても書き直せなかったりします。 他の原因とか考えられますか? Pゴシックで作ったのはPゴシックで表示されるだろ どこのバッタもんつかってんだそれ >>15 2013まででBook作ってテーマが"Office"だと、2016で"Office"が適用されると、"デフォルトフォント"が遊ゴシになるんだ 個別にフォント設定したことがあるセルはMSPゴシックで変わらないから混在する 今は"2013互換"ってテーマ設定して利用してる。 保護ビューだろうね 次点でファイルが壊れている オートフィルタが使えなくなるのは以前あった。セルが編集できないのは知らんけど 複数シートが選択されているときもオートフィルターは動かないね セル編集はできるけど 【1 OSの種類 .】 Windows 7,10 【2 Excelのバージョン 】 Excel2010 【3 VBAが使えるか .】 いいえ 【4 VBAでの回答の可否】 可 あるファイルの共有が出来ないようにする方法ありますか? 共有を解除しても、誰かが勝手に共有にしてしまいます。 これを防ぎたいのですが・・・ 社内周知する ファイルのプロパティから、前回保存者を調べてクビにする >>20 パスワードが例えば会社の創立記念日とかになってるんじゃ? 書き方が悪かったですね、共有で保存出来ないようにする方法です。 とりあえずちゃんと話してみろ 共有にしときたいファイルを勝手に解除する奴が居るとか 共有がいつのまにかはずれるバグがあるかもとか そう言ってる奴が居るかもしれない こっそり共有化して得するケースってなかなか考えにくい 共有化しないと開きっぱなしにする奴が居て更新できないとかぐらい 更新自体をさせなくていいなら普通に権限設定だな 共有にしときたいファイルを勝手に解除する奴が居る→その通り EXCELの共有重いからな スプレッドシートとかにしてみては それまで普段使いっているEXCEL2003のファイルを編集していたら、特に何も 操作した覚えがないに、そのファイルにある同じPCにある別のEXCELファ イルに張られたハイパークリックをクリックして開こうとしたら、以下の dialogue boxが現れて、いちいち「はい(Y)」を押下して開かないといけな いようになった。 「"C:\work\a12.xls"を開いてます ハイパーリンクにはコンピュータやデータに問題を起こす可能性のあるもの もあります。 お使いのコンピュータを保護するには、信頼できる発行元からのハイパーリ ンクのみをクリックしてください。続行しますか?」 この問題について検索してみると、このdialogue boxが表示されないように する対処法について、幾つかのWeb pageが出てきた。 https://support.microsoft.com/ja-jp/help/925757/how-to-enable-or-disable-hyperlink-warning-messages-in-2007-office-pro registoryを書き換えるなどの方法があるが、どれもそのPCにあるEXCELフ ァイル全体に対する対処法である。 そのファイル名を"a12.xls"とすると、そのファイルのみにこの現象が起きて それ以外のEXCELファイルではこのdialogue boxは出現しない。 この"a12.xls"のファイル名を例えば別の名前"a12a.xls"に変名して開いて ハイパーリンクを開こうとするとこの現象は生じない。 このファイル名を"a12.xls"に戻して開くと、同様の現象が生じる。 つまり、特定のファイル名のEXCELファイルにのみこの現象が生じる。 これについて、今の所、原因がわからない。 × registory ○ registry アメリカ語間違えるのかっこ悪いよぉ・・・ >>29 駄目です。 VPN使って拠点間で共有しているファイルなのですが、お互い書き込みが必要です。 特定ファイルのリンクが全てそうなるのか、特定リンクだけなのか? 例示ファイル名から類推すると後者? ファイル名を変えるというのはリンクとリンク先をそれぞれ変えたということか? リンク先ファイルを右クリック→プロパティ「全般」タブにセキュリティ文言が在ったりしない? >>20 常に共有にしたいなら以下 ブックを開けば毎回共有設定される Private Sub Workbook_Open() Application.DisplayAlerts = False ProtectSharing Application.DisplayAlerts = True End Sub >>28 >EXCELの共有重いからな >スプレッドシートとかにしてみては ??? 「スプレッドシート」だけだと通りが悪いのか? ちょっと意外だぞ スプレッドシートを固有名詞と勘違いしている人たまにいるよね エクセルとスプレッドシートって オセロとリバーシみたいな相対関係だと思ってるんだけど合ってる? vba使わないなら似たようなもん。むしろEXCEL買わなくていいし超便利 vba使うなら結構違う。場合によっては全く使えるなくなることも。jsで書くの結構めんどくさいんだよな 表自体もしくは表計算アプリ全般をSpreadSheetと呼ぶ、 VisiCalc、マルチプラン、花子以来の伝統さ 自分が最初に触った表計算は日立のOffice POL/2、配列数式がデフォルトで良くできたアプリだった。 無論Excelもスプレッドシートなので Googleスプレッドシートのつもりで「スプレッドシート」と言われても話が通じないわけ 【1 OSの種類 .】 Windows7 【2 Excelのバージョン 】 Excel2010 【3 VBAが使えるか .】 はい・ 【4 VBAでの回答の可否】 可・ B列(日付)とC列(時刻)を合わせてD列に表示、E列(日付)とF列(時刻)を合わせてG列に表示、とBF列まで繰り返しするのですが、あらかじめ数式を入力すると、数式のない行で苦情がきました。コピペで対処は無理との事でした。何か方法があればお寝します。 【1 OSの種類 .】 Windows10 【2 Excelのバージョン 】 Excel2010 【3 VBAが使えるか .】 いいえ 【4 VBAでの回答の可否】 否 2018/10/1 2018/11/1 ・ ・ ↑のように月を自動入力させる事は出来ないんですか? 分からないのと分かってないフリして薀蓄語りたいのは違う >>51 最初の2つだけは手動で入力して 2018/10/1 2018/11/1 2つのセルを選択した状態でフィルハンドルをドラッグすると月だけが進むよ >>49 ・数式のない行を作らない ・vbaで対処する >>49 苦情の内容次第だけど 「ウッセーバカ」っと返信する >>34 >>特定ファイルのリンクが全てそうなるのか、特定リンクだけなのか? 特定ファイルからのリンクが全てそうなった。 >>ファイル名を変えるというのはリンクとリンク先をそれぞれ変えたということか? リンク先のファイル名は変えていない。リンク先のファイル名を変えたのだ としたら、リンク元ファイルのハイパーリンクのアドレスのファイル名を変 えたら良い。 >>リンク先ファイルを右クリック→プロパティ「全般」タブにセキュリティ文言が在ったりしない? どのEXCELファイルのfile propertyのタブ「全般」にもセキュリティ文言は 無い。 流石に2007より古いverの不具合はどうしようもない。 ほとんどの人に環境がないから、試すこともできない。 >>58 共有状態でマクロは使えないので、 マクロで全自動で共有を解除する事はできない >>57 いいたい。相手が職場で屈指の癇癪持ちなので勘弁。 >>55 前スレでiとjをcells(i,j)にして計算させてたVBAがあったので、こちらで対応。 >>56 ifErrorの使い方がわからなかった。 >>50 配列数式だとBとCを合わせて日時にできましたが、最終列まで入力するとブックのサイズが大きくてレスポンスが低下しました。 皆様、ありがとうございました。 表のデータ回収で困ってます。 A B C D E F G ... 1内容 4月 5月 6月 7月… 2あれ 29 15 16 20… 3それ 18 12 12 18… 4… 5計 みたいな予算書をエクセルで使うことを強いられます。 1シート1科目で、ブックで40シートくらいになります。 営業所や課ごとにブックがあるので、全部で60ブックぐらいになります。 最後に集計を取るのも面倒ですが、 出来上がったあと差異要因分析に全く向かない仕組みです。 各シートのデータを回収する良い方法ありませんか? なお、各ブックにマクロを組むことはNGです。 よろしくお願いします。 マクロはマクロ用のブックにだけ書くのはOK?それならマクロがてっとりばやそう 営業所や課ごとに分析したいなら、ひとつのブックにデータを集める時にそれがわかる列を追加すればいい A列 営業所・課名 B列以降 各シートのデータ みたいに >>65 共有とかどうだろう ・EXCELの「共有」機能を使う 以下のように入力してもらう A B C D E F G ... 1 所属 内容 4月 5月 6月 7月… 2 A課 あれ 29 15 16 20… 3 A課 それ 18 12 12 18… 4 B課 あれ 5 B課 それ 計 ・googleスプレッドシートを使う googleアカウントなどが必要になるけど、web上で編集できる >>65 集計とか差異要因分析までしたいならBIツールとか入れた方がいいかも たいていExcelからの読み込みとかはサポートしてるし 指定フォルダー内の1枚目のシートをブック名列込みでコピーするならこんな感じ 見出しごとコピーしてるけどそのへんは適当に変えて Dim tgtFolder As Object, xlFile As Object Dim tgtBook As Workbook, curRange As Range, tgtRange As Range '新規ブック作ってコピー開始位置設定 Set curRange = Workbooks.Add.Worksheets(1).Range("B1") '集計したいブックがまとめてあるフォルダー指定 Set tgtFolder = CreateObject("Scripting.FileSystemObject").GetFolder(フォルダーのパス) 'コピー Application.ScreenUpdating = False For Each xlFile In tgtFolder.Files Set tgtBook = Workbooks.Open(xlFile.Path) tgtBook.Worksheets(1).UsedRange.Copy curRange Range(curRange, curRange.End(xlDown)).Offset(0, -1).Value = xlFile.Name Set curRange = curRange.End(xlDown).Offset(1) tgtBook.Close Next >>68 ありがとうございます。共有機能ということは、60ブックも作らずに、ということでしょうか? 1ブック=その営業所の予算書、みたいな運用なんです。後出しでごめんなさい。 加えて言えば、共有機能、使ったことがありません。2013ですが、多人数同時接続の場合、安定するのでしょうか? >>70 ありがとうございます。 指定フォルダ内のエクセルファイルを、個々に名前を指定しているわけではないようですが、順に読み込んでくれるのですか?だとすればスゴイ。 >>71 そう 1ブックをみんなで同時に編集する事が可能になる。今までみたいに誰かが開いていたら閉じてもらう必要がなくなる 経験上、VPN(支店間をネットで繋いでLANみたいに使えるようにするシステム)を使って10人ぐらいなら余裕で使える ただこれはEXCELのシステムではなく、 そちらの社内LANがどれだけ快適か(サーバーのスペックと回線速度)によるので、俺には分からない あとデメリットとして、共有機能を勝手に切る奴がいると同時編集できなくなってギスギスする 経験上、結構起こる そういう意味では最初から共有前提のgoogleスプレッドシートの方が楽で良い もしくは、頑張れるかだれかがマクロ作ってくれるならそれが一番楽といえば楽。特に今までとやり方を変えなくていいから、周りを動かす必要がない >>65 それって、マジExcelだけで処理しないで ツール買って処理した方が安く速くあがるよ。 Excelシートを使うことが前提としてのツールなら xoBlosをお勧めする。 複雑なレイアウトのExcelシートからでも マクロまるきり使わずそのまま読めるし、 Bookが60ブックあって、一つのBookに40シートなら おそらく20秒程度あれば処理が終わる。 データベースのようなものを介在させる必要もない。 もちろんそのまま1つのExcelシートに自由なレイアウトで まとめることもできる。 騙されたと思って調べてみて。 >>65 今ちょうど、公的機関の月ごと公開csvファイルの集計絡みで、 Power Queryを研究してた。 予算集計ではないけど、似ているので、 ・マクロは使わず、共有もせず、 ・なおかつ、原始的な切り貼りより、少しだけ楽したい としたら、 1. 科目別入力用sheetをテーブルで用意。(1次集計テーブル) ・行は数値を入れれば自動でテーブル範囲が伸びる。 ・1bookに複数科目sheetでも可。 ・ただし、後でクエリ合体させるので項目名や列変更は変更は禁止。 2. テーブル名は事前に科目数分を名付けておくか、 規則を作って、各部署で必要科目分の様式複製・入力時に名付けてもらう。 3. 締日までに集まった部署別bookを、集計者以外がアクセス出来ないフォルダに保存。 4. 集計者は、科目ごと元データ合体用bookを用意して、 5. 「データの取得」で、3.のフォルダから「book名(部署別)-テーブル名(科目別)」を指定して読み込む。 ・1book内に各課1sheetでそのまま転写。 ・クエリのステップ指定で、空行削除もスクリプト書かずに仕込むことも可能。 6. さらに、クエリの「追加」で、部署横断の合体版sheetを作る。(2次集計テーブル) 7. この合体版sheetを基礎データにして、あとは別bookに取り込んで、 ・テーブルの上に行挿入して月別合計するなり、 ・部署名をキーにしたINDEX(MATCH(),MATCH())照合で、月別sheet間の差を取る別sheetを作るなり、 ご自由に。 とかは? 少なくとも、いちいちbookを開いて範囲指定・コピペしなくても、 いっぺんに読み込み(更新も)とデータ追加(合体)は出来るかな?と。 共有ではないので、物理的に締日/バージョン管理も可能だと思う。 >>76 の補足 5.と 6.で、なんで2段階のクエリで読み込みが必要なのかは、 1つ目のクエリで複数ファイルを指定する方法が、 オレにはまだわからないから。 やり方はありそうな気もするんだけど・・・ 1次集計のクエリを作ると、 それを合体する2次クエリの作り方は、 簡単だった。 【1 OSの種類 .】 Windows7 【2 Excelのバージョン 】 Excel2013 【3 VBAが使えるか .】 いいえ 【4 VBAでの回答の可否】 否 別々のセルに変更日 削除日を設定して 最終履歴を表示するセルに最終が変更なら変更日、削除なら削除日を表示したい ここで、変更なら後ろに" 変更"、削除なら" 登録削除"と文字列を付けたいのですが 日付にならず、数値+文字列で表示されてしまいます text(変更日のセル,yyyy/m/d)&" 変更"では"text(変更日のセル,yyyy/m/d)&" 変更""とそのまま表示されてしまいます。 どのようにすれば日付+文字列でいけるでしょうか >>73 なるほど。ありがとうございます。 運用面で解決しなきゃいけないことも多いですが、実験してみます。 >>78 >日付にならず、数値+文字列で表示されてしまいます よーわからん。なると思うけどなー 解決方法としては A1:変更 B1:削除 として =TEXT(MAX(A1:B1),"yyyy/m/d")&" "&IF(A1>B1,"変更","削除") でいける >>69 >>74 BI=ビジネスインテリジェンスですね? こんなのあるんですね。特にゾブロスはホームページにピンポイントで予算のこと書いてますね。検討します。 >>77 パワークエリ。いい結果が得られそうです。やってみます。ありがとうございました。 >>83 自分でやってみて、注意点がもう1つ。 クエリでは、ソースに入力されているセル値を、列ごとに全行?を見て、 それに応じたデータ型式を自動で適用してくれる。 が、入力用テーブルで、 あらかじめ、Excelシートの書式設定を「数値で小数点以下第2位まで」とかにしていても、 デフォルトではデータ型式が「整数」にされてしまうみたい。 (クエリで生成されるテーブルの、個々のデータは、関数でなくて値。更新しないと変わらない。) 小数点以下も扱いたいのなら、 クエリのステップに、「型の変更」で列ごと「10進数」扱いにする行程を 入れる必要があるようだ。 あと、予算集計には関係ないけど、便利な点。 分単位でクエリの自動実行設定が出来るので、 ソースがWeb(URL指定)でもイントラ/ローカルデータ(ファイルのフルパス指定)でも、 定型様式(列数・見出し位置・見出し項目が不変)なら、 自動巡回の、ちょっとしたデータスクレイピングもどきができること。 例えばこれの10分値自動巡回を作ることが出来た。 国土交通省「川の防災情報」 河川の水位変化(10分毎の推移) http://www.river.go.jp/kawabou/ipSuiiKobetu.do?obsrvId=2132000400004& ;gamenId=01-1003&stgGrpKind=survForeKjExpl&fldCtlParty=no&fvrt=yes&timeType=10 >>81 地味なツールだけど、弄ってみるとわかるのですがマジで使えます。 同類のものはないと思います。 【1 OSの種類 .】 Windows10 【2 Excelのバージョン 】2013 【3 VBAが使えるか 】いいえ 【4 VBAでの回答の可否】 否 エクセルで偏微分をしたいのですが調べてもよく分かりません。 例えば∂y/∂xを入力する場合どのように入力したらいいのでしょうか? >>86 離散数学を勉強してから、あらためて質問して。 >>84 自動巡回いいですね。 別業務にも応用が効きそうです。 【1 OSの種類 .】 Windows7 【2 Excelのバージョン 】 Excel2016 【3 VBAが使えるか .】 いいえ 【4 VBAでの回答の可否】 否 入力規則のプルダウンリストでプルダウンリストの範囲に 同じデータがあった時1つにまとめることできますか?例えば プルダウンリストの範囲がD2〜D5として D2 みかん D3 りんご D4 めろん D5 りんご このような時プルダウンリストにりんごが2つ出ないように したいです >>89 基になるデータ(D2〜D5)には重複を残しておきたい、その状態で プルダウンリストには、重複を表示したくないという 質問ととらえていいのですか? それとも、単にD2〜D5から重複を削除したい? >>90 データを元にしてプルダウンリストを作りたいイメージです 大量のデータの特定列にあるデータを元に重複の無いプルダウンリストにできないかと >>89 ,90 自分も元データからプルダウンリスト作るときは、 手動で「データ−重複の削除」でやってた。 Accessではクエリで簡単にできたけど、 ExcelではVBAでは出来るのだろうけど・・・ じゃ、またまたExcelのPowerQueryでは? と思って今画面見たら、 あるわ、「重複の削除」。 ただし、さらにVBAのアクションクエリでも仕込まないと、 ・入力のたびに、手動で「全てのデータ更新」ボタン押すか、 ・タイムラグ承知の上で「接続のプロパティ」でクエリを1分単位の自動更新する しかなさそう。 関数で出来るといいんだけど。 俺、動的なのは使わないせいで、ずっと手作業だ。月次の集計程度なんで 1.サクラエディタに貼る 2.ソートマージで一意リストにする 自前でフォーム作るかな もしくはvbaでsheetactiveか何かで毎回作り直す 関数で重複削除はちと辛い 計算結果がマイナスの場合*0.8の計算をしないみたいな式を書く場合はどうしたらいいんですか? >>97 A1で計算するとして =A1*IF(A1>0,0.8,1) 計算をしないならこっちの方が分かりやすいか どっちゃでもええけど =IF(A1>0,A1*0.8,a1) >>93-96 難しそうですね 作業列を作って一旦重複の無いリストを作る方法は知ってるので 諦めることにします ご相談にのっていただきありがとうございました ■ このスレッドは過去ログ倉庫に格納されています
read.cgi ver 07.5.1 2024/04/28 Walang Kapalit ★ | Donguri System Team 5ちゃんねる