[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『最大行数を拡張する方法』（電話番子）
エクセルの操作方法(?)につき、どなたかお分かりになれば教えてください。
通常、エクセルの最大行数は6万行強だと思うのですが、これを100万行辺りまで拡張する方法をご存知でないでしょうか。
過去にそんな質問がされていたようにも記憶しているのですが、見付け切れませんでした。
（以下、補足）
業務で、日々データを格納しているマスタのようなものがあり、そのマスタからＳＱＬ文によりエクセル形式でデータを抽出することがあります。
非常に膨大な量のデータを格納したマスタであり、抽出対象が10万件を超えることなどがあるのですが、6万5千件程度を抽出したところで抽出が中断されてしまいます。
これを、どうにか全件抽出できるようにしたいのです。
かつて同僚が拡張していたのですが、その者は異動してしまい、方法を知っている者がいなくなってしまいました。
ＣＳＶファイルとしてダウンロードし、エクセルに変換することは可能なのですが、可能であればＣＳＶ形式を噛まずに一発でエクセル形式に抽出することができたらいいなと思っております。
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
（chiki） 2018/02/05(月) 11:16
補足すると最大行数はブックの形式に依存するため2007以降のExcelでも拡張子がXLS(2003以前のブック形式)のブックを開く、 あるいはExcelのオプションの保存-ブックの保存-ファイルの保存形式がExcel97-2003ブックになっていると最大行数が65536になる。 （ねむねむ） 2018/02/05(月) 11:45
ご返信ありがとうございます。
会社から貸与されたパソコンを使っており、なぜかエクセルを新規に立ち上げると
「Excel 97-2003 ブック(*.xls)」形式で立ち上がります。
皆さんのアドバイス通り一旦「Excel ブック(*.xlsx)」形式で保存し、閉じ、改めてひらいたとしても、同様に抽出が 65,536行で中断してしまうのです。
保存の仕方かなにかが良くないのでしょうか。
（電話番子） 2018/02/05(月) 11:52
>あるいはExcelのオプションの保存-ブックの保存-ファイルの保存形式がExcel97-2003ブックになっていると最大行数が65536になる。 ここを変更してくれ。
（ねむねむ） 2018/02/05(月) 11:59
> 会社から貸与されたパソコンを使っており、なぜかエクセルを新規に立ち上げると > 「Excel 97-2003 ブック(*.xls)」形式で立ち上がります。
「ファイル」-「オプション」-「保存」と進み、「ファイルの保存形式」が「Excel 97-2003」に なっていませんか？ ここを「Excel ブック(*.xlsx)」に変更してみてください。 （ろっくん） 2018/02/05(月) 12:30
ご返信ありがとうございます。
確認したところ、「ファイルの保存形式」が「Excel 97-2003 ブック(.xls)」になっていたため、「Excel ブック(.xlsx)」に変更し、保存しました。
（『既定のファイル形式を Office Open XML に変更します。この形式をサポートするすべての Microsoft Office アプリケーションでも、この設定を変更しますか？』というダイアログに対し、「はい」と答えました）
その上で一度「名前を付けて保存」で「Excel ブック(*.xlsx)」形式で保存して閉じ、再度ひらき直してみても、やはり6万強行で打ち止めとなってしまいました。
加えて、同ファイルの「オプション」－「ファイルの保存形式」を確認したところ、
「Excel 97-2003 ブック(.xls)」に戻ってしまっていました。
何度か試しましたが、「ファイルの保存形式」の設定を変更した事実が保存されません。
（電話番子） 2018/02/05(月) 13:12
> ＳＱＬ文によりエクセル形式でデータを抽出することがあります。
このSQL文にファイル名が含まれたりしていませんか。
あるいは接続設定等があれば、そちらにファイル名指定となっているとか。
拡張子まで指定されていたら新しく保存したファイルを参照していないのかもしれません？
（ご近所PG） 2018/02/05(月) 13:23
ご返信ありがとうございます。
エクセルＳＱＬというものが一般的なのか分かりませんが、私の知見で見る限り、ファイル名の指定はありません。
データ内容ごとに格納するマスタが異なるため、
抽出元マスタ → マスタ内抽出項目 → 抽出条件 を指定する形式の文となっています。
（電話番子） 2018/02/05(月) 16:11
> 業務で、日々データを格納しているマスタのようなものがあり、 > そのマスタからＳＱＬ文によりエクセル形式でデータを抽出することがあります。
「日々データを格納しているマスタ」は、Excelファイルですか？
「ＳＱＬ文によりエクセル形式でデータを抽出」するのは
「Excel」で作成されたシステムですか？
あるいは何かしらの市販アプリとか専用に開発された
「Excel以外」のシステムですか？
最初の文面にある
> ＣＳＶファイルとしてダウンロードし、エクセルに変換することは可能なのですが、
から推察すると、
Excel以外に何かしらのシステムがあり、
そこからExcelファイルを生成して出力しているのでは？と思えたので……
もしそうなら、Excelの設定をいくらいじった所で意味はありません。
もう少し、操作手順や環境といった情報を書けますか？
（ご近所PG） 2018/02/05(月) 16:33
同僚の方がいたころに、65536件を超えるデータを出力したことはありますか？
（ご近所PG） 2018/02/05(月) 16:35
マスタそのものは、エクセルファイルではないと思います。
データとして存在することを知っているだけで、どんな形式のものか見たことがないので、確かなことは言えませんが・・・
なお、調べたところ下記URLに辿り着きました。
「Excellent（エクセレント）」という市販ツールのようです。
http://www.ksc.co.jp/home/bi/excellent/
操作手順は エクセルを起動する（新規ブックでも既存のエクセルファイルでもよい）→ 「アドイン」リボンから「Excellent」を起動する → ＳＱＬ文を作成し抽出を実行する → エクセル上にデータが抽出される です。
同僚の方がいたころに、65536件を超えるデータを出力したことはありますか？ NOです。私はその壁を突破できたことはありません。
というのは、実はその同僚とは入れ替わりで私が着任し、部所に残っている他の同僚は「その人に拡張してもらった」と言い10万行を超えるデータ抽出を行います。
ただ、異動した同僚とは連絡が取れない状態にあり、部所に残っている同僚は拡張方法を知らないとのことです。
ご近所PGさまのおっしゃるよう、エクセルの設定でどうこうできる問題ではないのでしょうか・・・
（電話番子） 2018/02/05(月) 17:40
とりあえず、CSVで出力することが出来ているのであれば、【「Excellent」でCSVとして出力する～CSVをブックとして開く～xlsxとして保存する】という一連の流れを、マクロの記録でコード化して、ちょっと整理してマクロ作っちゃうってのはだめですかね？
ちなみに、今のエクセル君の最大行（＝csvをブックとして開く際の最大行）は、2の20乗（1,048,576行）ですけど足りますか？（Excel2007より前だと、2の16乗（65,536行））
（もこな２） 2018/02/05(月) 18:15
ご返信ありがとうございます。
なるほど、Excellent側の設定をいじるというのは気付かない視点でした。
それは明日にでも、有効そうな設定変更を探してみたいと思います。
ところで副案としてご提案いただいたマクロの件ですが、もしそれが可能なのであればとても嬉しいです。
ですが、なにぶん私はマクロを使ったことがないもので、いまいちぴんと来ません。「マクロを記録」という機能自体は一度だけ使ったことがありますが…
一つだけ確認させていただきたいのですが、Excellentの抽出条件はマクロを実行するごとに指定できるものなのでしょうか。それとも、抽出条件をもマクロに取り込むことができるのでしょうか。
月ごとにデータを抽出したり、分析のためにあらゆる角度からデータを抽出したりするので、正直に言えばそのどちらともをできると助かるのですが…
なお最大行数は100万行もあれば充分に足ります。
ご配慮いただき、ありがとうございます。
（電話番子） 2018/02/05(月) 21:41
今朝、「Excellent」内の設定を少々いじってみました。
すると、エクセルの最大行数自体は皆さんのおっしゃる100万強行に拡張されました。（ありがとうございます!）
しかしながら、その状態で抽出を行なったところデータ抽出は6万強行で打ち止めとなりました・・・
抽出対象のデータが10万行より多いのは間違いありません。
さらに、「Excellent」内の設定で「抽出が最終行を超える場合、新規シートに貼り付ける」にもチェックを入れてみたのですが、やはり上記の現象が起こりました。（データは6万強行で抽出が止まり、その下から100万強行まで空白状態のセルが並んでいる状態です）
さすがにお手上げですよね。
せっかく最大行数が拡張されたというのに、Excellent の問題のようです・・・
（電話番子） 2018/02/06(火) 09:14
そこそこの規模の製品と見受けられますので、
企業として契約されたものならば、カスタマーサポートからの問合せが出来るのではないかなと想像します。
そちらから問い合わせてみられては如何でしょう。
この学校に同製品を持っている方がいればアドバイスも出て来るかもしれませんが、
可能性は低いかなと。
（ご近所PG） 2018/02/06(火) 09:21
一つ、直接は関係ないのですが質問を…
先にも記載したのですが、「ファイルの保存形式」の変更が保存されません。
この原因、また解決法をご存知の方はいらっしゃいますか?
新規にエクセルを起動する(必ず「Excel 97-2003 ブック(.xls)」かつ「互換モード」で立ち上がる) → 「ファイルの保存形式」を「Excel ブック(.xlsx)」に変更する → 「名前を付けて保存」で拡張子を「Excel ブック(.xlsx)」に変更し保存する
という手順を踏むと、さすがに保存したファイルは「Excel ブック(.xlsx)」形式のままですが、新規にエクセルを起動すると、「ファイルの保存形式」が「Excel 97-2003 ブック(.xls)」に戻っており、「互換モード」で立ち上がります。
なにをするにも私はまず「Excel ブック(.xlsx)」に変更して「名前を付けて保存」するので、構わないといえば構わないのですが…
（電話番子） 2018/02/06(火) 11:14
たぶん変更には管理者権限が必要なのだと思うがレジストリにポリシー設定を作成できるようだ。 (確認だけであれば管理者権限は必要なし？) 下記のページを参考にしてみてくれ。 https://getadmx.com/?Category=Office2010&Policy=excel14.Office.Microsoft.Policies.Windows::L_SaveExcelfilesas&Language=ja-jp
（ねむねむ） 2018/02/06(火) 11:26
レジストリの「HKEY_CURRENT_USER」内に software\policies\microsoft\office\14.0\excel\options があり、そこに「Name defaultformat」があってその値が「56」ならばファイルの規定値がExcel97-2003 ブック形式に設定されるようだ。 (ただ、私は管理者権限を持っていないため実際に上記を設定して電話番子さんの状況のようになるかは確認できていない) （ねむねむ） 2018/02/06(火) 11:35
皆さま
非常に個人的な質問にお付き合いくださいまして、本当にありがとうございました。
この質問は、以上で解決済みとさせていただきます。
（この掲示板の機能上、「解決済み」マークのようなものがあるのでしょうか？ ・・・って、さらに質問をしては意味がありませんが）
（電話番子） 2018/02/06(火) 12:17
私も最近は年1～2回くらいしか訪れていないので本サイトの動向に自信はないですが、
[[20050206180741]] 『[雑談]未解決過去ログ掘り起こし企画』
上記の中に書かれている以下の内容が自分的にはしっくりきています。
> 解決が表示されないので、便乗質問できたりして > 他の掲示板と違ってそれも良いかと。
検索したらもうずいぶん前の話だった
（ご近所PG） 2018/02/07(水) 10:29
Excellentを導入している場合、個々の端末にエグゼファイルが保管されるらしく、その設定を変更することで最大行数と同件数のデータ抽出が可能となりました。
以下、拡張手順
C:\Xlt32\Init にアクセスし、「XltMnt.EXE」ファイルを起動する。
環境設定の「ユーザー・インターフェース」リボンから、「貼り付け最大件数」を変更する。
（初期値は 65,535件となっている）
非常に簡単ですが、以上です。
「最大値」を指定することで 1,048,575件とすることができ、csvダウンロードを経由することなく、エクセルブックにダイレクトに抽出することが可能となりました。
ご協力くださった皆さまに、改めて感謝を申し上げます。
これをもって、今度こそ「解決済み」とさせていただきます。
（電話番子） 2018/02/08(木) 18:12
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.