Q&A
excelによってコピーされた、work.xlsxをExcelで開くと値はどうなっていますか？
実現したいこと
★関数が含まれるExcelファイル[main]の特定の列に関数が入ったまま値を入力したい
⇒そのために[main]を値コピーしたクローンファイル[work]を作成する
①openpyxlでクローンファイル[work]を作成
②[work]の特定シート、特定列を読み取る
③②で読み取ったデータを[main]の特定シート、特定列に値貼り付け
[main]の関数を壊さずに、[main]のシート"display"に出力される関数の結果を、[main]のシート"past"に移動させたい
以下は全て自動で行われていることが理想です
①[main] シート”data”にデータが入力される
②[main] シート”display”の関数が更新("data"の値がvlookupで更新されます）
③vlookupで更新された値をシート"past"の右端+1行に記述したい
※ここでは関数を壊さないで関数の値だけシート移動、貼り付けしたいです。
※シート"past"の右端、右端+1行は関数が含まれていないセルになります。
前提
言語：Python
ライブラリ：openpyxl
関数が入ったファイルを用意します
ファイルA 以下のような関数が含まれたファイルです
ファイルAはシートが3枚あります
現状
理想
workが以下のように値が記載されれば、Noneにならずに値が出力されることがわかりました。
しかし、ファイルAをopenpyxlでコピーするとなぜか現状の通りに関数部分が真っ白になってしまうのです。
上書き保存後の該当ファイル
問題箇所
関数が含まれるExcelファイル[main]の特定の列に関数が入ったまま値を入力したいのですが、
②の段階でNoneと表示され、プログラムが評価されません。
該当のソースコード
Python
1import openpyxl 2 3# Excelファイルを開く 4#excel_file_pathは関数が含まれているファイルAです 5excel_file_path = f"\\\\任意のディレクトリ\\ファイルA.xlsx" 6excel_file_copy_path = f"\\\\任意のディレクトリ\\work.xlsx" 7workbookw = openpyxl.load_workbook(excel_file_path, data_only= True) 8workbookw.save(excel_file_copy_path) 9 10# workを開く 11workbook = openpyxl.load_workbook(excel_file_copy_path, data_only=True) 12 13# 過去分シートとレイアウトシートを取得 14past_sheet = workbook['past'] 15layout_sheet = workbook['display'] 16 17# 過去分シートの行数を取得 18row_count = past_sheet.max_row 19 20# 過去分シートの行をループ 21for row in range(2, row_count + 1): 22 # A列にNEWが入っている場合 23 # ----★この下のprintがNoneになっている★----------------------- 24 print(f"past⇒{past_sheet.cell(row=row, column=1).value}") 25 #----------------------------------------------------------------- 26 if past_sheet.cell(row=row, column=1).value == "NEW": 27 # D列の値を取得 28 search_value = past_sheet.cell(row=row, column=4).value 29 print(f"sv⇒{search_value}") 30 31 # レイアウトシートでVlookupを行う 32 for layout_row in range(2, layout_sheet.max_row + 1): 33 if layout_sheet.cell(row=layout_row, column=6).value == search_value: 34 # 取得した値を過去分シートの最後のセルに出力 35 past_sheet.cell(row=row, column=past_sheet.max_column).value = layout_sheet.cell(row=layout_row, column=29).value 36 break 37 38# Excelファイル(work.xlsx)を保存 39workbook.save(excel_file_copy_path) 40 41# workファイルからmainファイルへの転送 42# 転送元ファイルの設定 43sheet_from = workbookw["display"] 44row_from = sheet_from.max_row 45column_from = sheet_from.max_column 46print(column_from) 47 48# 転送先ファイルの設定 49workbook_to= openpyxl.load_workbook(excel_file_path) 50sheet_to = workbook_to["past"] 51row_to = sheet_to.max_row 52column_to = sheet_to.max_column 53 54# 転送のしくみ 55for j in range(1, row_from): 56 print(sheet_from.cell(row=j, column=column_from).value) 57 sheet_to.cell(row=j, column=column_to+1).value = sheet_from.cell(row=j, column=column_from).value 58print("時の流れはフシギダネ")
試したこと
workbook = openpyxl.load_workbook(excel_file_path, data_only=True)の場合、
★の部分が正しく表示されます。
補足情報（FW/ツールのバージョンなど）
もしかして、このサイトが言及する理由が原因ですか？
https://note.com/vanaya/n/nd46b4b7fc55e
よろしくお願いします。
もともと値として入力されているものは、そのまま表示されていますが、関数として入力されているものはなにも表示されていません。
0でも、""でも、N/Aでもなく、空白です。
> 関数として入力されているものはなにも表示されていません。
> 空白です。
ということは、そのExcelのそのセルには何も入っていないので、「Noneと表示され」るのは当然と思いますが、何が表示るべきだということでしょうか。
関数の結果の値を表示させたいです。
ファイルAのExcelのセルは関数が入っています。
そのファイルAをopenpyxlによってコピーしたものがworkなので、★に表示されるのはworkの値になるはずです。
日本語で正確に記述するのが困難なのであれば
ファイルA.xlsx を Excelで開いて A2 にカーソルがある状態で数式バーの内容が読めるスクリーショット
と
work.xlsx を Excelで開いて A2 にカーソルがある状態で数式バーの内容が読めるスクリーショット
と
こうなっていてほしい理想の結果が分かるスクリーンショット
を
質問に添付するのがよさそうです
'data_only=True'を指定することで、計算結果が取得できると想定しているのだと思いますが、補足にあるリンク先にも書いてあるとおり、openpyxlにはセルの数式を計算する能力は無く、Excelが裏でファイルに保存している計算結果の値を取得しているだけなのです。
なので、コピーしたwork.xlsxに値が無いのであれば、裏で保存されている値が無いということです。
元のファイルを一度Excelで開いて、再計算するなどしてセルに値が表示されるようにしてから試してみるとどうでしょうか。
「関数が入ったまま値を入力したい」というのが、いまいち何をしたいのか明確に理解できません。元のファイルについては言及されているので、どんな最終形にしたいのかを書けないでしょうか。(今の例だと複雑なのでシンプルな例にしてもらえるとベターです)
そもそもファイルを別名で保存して開き直す必要があるのか、data_only=True がいるのかというところも疑わしいように見えます。
ご指摘ありがとうございます。
関数が含まれているexcel、それを基にコピーしたexcelの現状と理想を画像で添付しました。
TakaiY様が仰るように、
openpyxlでいくら'data_only = True'で読み取っても
値を計算しないから値コピーは作成できないということなのでしょうか。
bsdfan様のご指摘の通り、
究極の理想を実現したいこと、に記載しました。
つまり、関数が入ったexcelファイルのデータを関数が入ったまま、
シートを変えてコピペしたいというのが本質になります。
関数が入ったexcelファイルの'data_only = True'を実行すると、関数が消えてしまうため
このように関数ありファイルのコピーとして関数なしファイルを作成することになりました。
こちらで、数個のデータの表と、vlookupで値を取り出すようにした簡単なものを作り、pythonで data_only= True で読み込み、それを保存してみると、ちゃんと値だけコピーされていました。
ということで、そういう動作になることを想定するのは間違えていないと思います。
再度確認ですが、「関数が含まれているファイルA」を開くと値が表示されていると思いますが「それを上書き保存」してからpythonで処理しても同様の結果になりますか？
先にも書いたとおり、ファイルAにはExcelで計算した結果が保存されている必要があるのです。
数式が入ったセルでは、Excelで保存した xlsx ファイルには「式」と計算結果の「値」が保存されていますが、openpyxl で作成した xlsx ファイルには「式」しか保存されません。(openpyxl はExcel の数式を計算できないので)
data_only=True はこのうちの「値」をとってくるという意味なので、openpyxl で作成した xlsx ファイルの数式が入っているセルには「値」が入っていないので、None になってしまいます。openpyxl で作ったファイルでも、いったん Excel で開いてそのまま保存すれば値が入って保存されます。
bsdfanさんの言うとおりなのですが、元のファイルである「関数が含まれるExcelファイル[main]」はpythonとかopenpyxlで作ったものではないと思うんですよね。 なので、原因ではないと思っているのすが、念の為、前の質問をしています。
TakaiY様が仰る通り、
Excelファイル[main]はもともと作成しているファイルです。openpyxlを用いて作ったものではありません。
bsdfan様、TakaiY様
ご指摘ありがとうございます。
[main]関数の値がvlookで表示されたあと、
上書き保存し、その後に上記のコードを回してみます。
bsdfan様、TakaiY様や、提示したブログの内容の通り、
一度関数の入ったファイルを開いて関数の値が更新されたのちに、
上書き保存をすればdata_only = trueで値が読み込めることは確認しました。
やはり、一度関数の入ったファイルを開いて、上書き保存する、という
自動化できない工程が含まれてしまうのでしょうか？
改めて確認ですが、その mainファイルに入っている数式は Excelで書いたものではないとか、入っている値をpythonで修正するなどして再計算が必要な状態ということですか？
Excelで開い（て再計算し）たらどうなってるのか
Excelで開いて再計算させる必要がある
は、なんどかコメントで言われているのにスルーされているので、今になってなにを聞いているのか?
となっていて
Python のコード以前の話として、本当は何をしたいのか? という「達成したいこと」を書いたほうがいい感じがします
質問のコードでは保存までされていませんが、いろいろ試しているうちに、もともとの Excel で作った xlsx が openpyxl で出力したファイルと置き換わってしまっている可能性があると思ってコメントしていました。
実際のケースでは初期はExcelで作成したファイルからスタートするということが確実なら気にしなくてもいいのかもしれません。そうでないのなら、このやり方では厳しいと思います。(マクロでやるかxlwingsを使うかですかね。)
quickquip様
ご指摘ありがとうございます。
Excelを一度開く⇒関数の更新を確認⇒上書き保存を
行なったファイルを添付しました。
displayの値をpastに移す作業は対象外ですが、
この「Excelを一度開く」～「上書き保存」の段を人の手介さずにできれば理想と考えています。
先の主題から質問の趣旨が変わってしまいました。
実現したいことを編集しておきます。
TakaiY様
コメントをありがとうございます。
インラインで返答いたします。
>その mainファイルに入っている数式は Excelで書いたものではない
→Excelに直書きしたものです。
＞入っている値をpythonで修正するなどして再計算が必要な状態
→pythonで修正することはありません。
mainはすでに関数が記載されており、pythonで修正する必要はありません。
一度、コード外でExcel関数が更新され、上書き保存されないとopenpyxlでは値を読み込んでくれないものなのでしょうか？
bsdfan様
コメントをありがとうございます。
もともとのExcelはバックアップを取っており、
コードを回すたびにバックアップデータを呼び起こして作業しております。
openpyxlで作成したデータと区別して提供しています。
もともと、スクレイピングで複数の企業データを取得することが要でしたので、
pythonで実行する必要があると判断しました。
代替案のご提示をありがとうございます。
試してみます。
すみません。やっと意味が通じた気がするので以下は無視してください。(削除はしないでおきます)
-- 8< -- 8< -- 8< --
Excelの設定で自動計算オフ、保存前再計算オフ とかしてますか?
今、質問者さんが何に困っているのか他の人が想像できてないです（おそらく）
→Excelに直書きしたものです。
→pythonで修正することはありません。
が正しいなら、Excelの中身を書きかえたい時は必ずExcelで開いて保存するという手順が存在するはずです。
そうであれば最後に書きかえた内容で計算されてデータがxlsx内に保管されるので、特に困ることがないはずです。
「Excelに直書きした」はどういうことを言っていますか? 「Excelで開いて書いて保存する」以外の何かのはずですが、それが想像できてません。（正確には、Excelで開かずになにかしたいんだろうな、と想像していたけれど否定されたからなにか分からなくなった、です）
一方で
> やはり、一度関数の入ったファイルを開いて、上書き保存する、という自動化できない工程が含まれてしまうのでしょうか？
> この「Excelを一度開く」～「上書き保存」の段を人の手介さずにできれば理想と考えています。
と言っています。つまり「Excelを開かずになにかしたい」と思っているはずです。
Excelを開かずにデータや式を編集したい、という欲求がなければ、こんな考えにはならないはずです。
「人の手を介さずにしたいと思っていること」ぜんぶを書いてください。
> もともとのExcelはバックアップを取っており、
> コードを回すたびにバックアップデータを呼び起こして作業しております。
> もともと、スクレイピングで複数の企業データを取得することが要でしたので、
ほら、「やりたいこと」で「書かれていないこと」が飛び出してきた
いま知りたい「やりたいこと」はこちらの話なのです
quickquip様
ご指摘ありがとうございます。
最終的にやりたいことは
・複数の企業のデータをスクレイピング
・スクレイピングしたデータを過去-1分と比較
★比較したデータをpastに累積させる
→関数が入っているのはpastの並び方を指定するためです
・displayのフォームにする
→pastのデータはdisplayの関数(今回の「特定列」ではない関数です)に反映されます
・displayのフォームを.txtにする
今回の質問は上記の★になります。
スクレイピング後、前日分と比較したデータはdataシートに記載されます。
スクレイピング、比較、displayフォーム、txtファイル出力のテストは問題ないことを確認しています。
回答書いているうちに、コメント進みましたが、やっぱりXY問題でしたね。
この問題、そもそもExcelにする必要あります？
結果をExcelにしたいとしても、今回問題の計算の部分もpythonでやればいいだけじゃないかと思うのですが。
TakaiY様
コメントありがとうございます。
連絡が遅くなりすみません。
絶対にExcelにする必要はないです。
担当社員はプログラムの修正はできないが関数を使えるので、関数を使いました。
回答2件
0
自己解決
win32comを用いた仕組みで解決することができました。
友人が作成したコードになります。
python
1import openpyxl 2import win32com.client 3 4# Excelファイルを開く 5excel_file_path = f"任意のファイルパス\\monthly_compare_main.xlsx" 6 7# win32comでExcelファイルを開く 8excel = win32com.client.Dispatch("Excel.Application") 9workbook = excel.Workbooks.Open(excel_file_path) 10 11workbook.Save() 12workbook.Close() 13excel.Quit() 14 15# 値を保持するためにdata_only=Trueで読み込む 16workbook_data_only = openpyxl.load_workbook(excel_file_path, data_only=True) 17 18# 関数を保持するために通常読み込む 19workbook = openpyxl.load_workbook(excel_file_path) 20 21# 過去分シートとレイアウトシートを取得 22past_sheet = workbook_data_only['past'] 23layout_sheet = workbook_data_only['display'] 24 25# 関数を保持したシートも取得 26past_sheet_function = workbook['past'] 27 28# 過去分シートの行数を取得 29row_count = past_sheet.max_row 30 31# 過去分シートの行をループ 32for row in range(2, row_count + 1): 33 # A列にNEWが入っている場合 34 #以下の部分に値が表示され、動作することが確認できました 35 print(f"past⇒{past_sheet.cell(row=row, column=1).value}") 36 if past_sheet.cell(row=row, column=1).value == "NEW": 37 # D列の値を取得 38 search_value = past_sheet.cell(row=row, column=4).value 39 print(f"sv⇒{search_value}") 40 41 # レイアウトシートでVlookupを行う 42 for layout_row in range(2, layout_sheet.max_row + 1): 43 if layout_sheet.cell(row=layout_row, column=6).value == search_value: 44 # 取得した値を関数を保持したシートの最後のセルに出力 45 past_sheet_function.cell(row=row, column=past_sheet_function.max_column).value = layout_sheet.cell(row=layout_row, column=29).value 46 past_sheet_function.cell(row=row, column=9).value = past_sheet_function.cell(row=row, column=past_sheet_function.max_column-1).value 47 past_sheet_function.cell(row=row, column=8).value = past_sheet_function.cell(row=row, column=past_sheet_function.max_column-2).value 48 past_sheet_function.cell(row=row, column=7).value = past_sheet_function.cell(row=row, column=past_sheet_function.max_column-3).value 49 past_sheet_function.cell(row=row, column=6).value = past_sheet_function.cell(row=row, column=past_sheet_function.max_column-4).value 50 past_sheet_function.cell(row=row, column=5).value = past_sheet_function.cell(row=row, column=past_sheet_function.max_column-5).value 51 break 52 53print("時の流れはフシギダネ") 54# Excelファイル(work.xlsx)を保存 55workbook.save(excel_file_path)
投稿2024/05/21 02:05
総合スコア13
0
①[main] シート”data”にデータが入力される
②[main] シート”display”の関数が更新("data"の値がvlookupで更新されます）
③vlookupで更新された値をシート"past"の右端+1行に記述したい
※ここでは関数を壊さないで関数の値だけシート移動、貼り付けしたいです。
※シート"past"の右端、右端+1行は関数が含まれていないセルになります。
1と2を自動でやることは、openpyxl ではできません。 ブックに含まれる計算式を(再)計算することができないためです。
pythonを使ってExcelブックを操作するモジュールに、xlwingsというのがあります。これは、pythonでxlsxファイルを操作するのではなく、pythonからExcelそのものを操作するモジュールです。実際にExcelを操作するので、再計算させることができます。ただし、Excelのインストールしてあるマシンでないと動きません。
投稿2024/05/17 11:49
総合スコア13337
あなたの回答
tips
プレビュー
